using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Dapper;
using Bailun.DC.DailyLogisticSupplierTransaction.Models;
using System.Linq;

namespace Bailun.DC.DailyLogisticSupplierTransaction
{
    public class Services : BackgroundService
    {
        private Timer _timer;

        protected override Task ExecuteAsync(CancellationToken stoppingToken)
        {
            _timer = new Timer(DoWork, null, TimeSpan.Zero, TimeSpan.FromMinutes(1));
            return Task.CompletedTask;
        }

        private void DoWork(object state)
        {
            try
            {
                var now = DateTime.Now;

                if (now.Hour == 11 && now.Minute == 57)  //11:57分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var day = DateTime.Parse(now.ToShortDateString());

                    Init(day);
                    

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }
                else if (now.Hour == 18 && now.Minute == 1)
                {
                    var day = DateTime.Parse(now.AddMonths(-1).ToShortDateString());
                    while (day.AddDays(1) < DateTime.Now)
                    {
                        Console.WriteLine(day);
                        Init(day);
                        Console.WriteLine("重跑 " + day.ToString("yyyy-MM-dd HH:mm:ss") + " 完成");
                        day = day.AddDays(1);
                    }
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        List<string> arrSanTong = new List<string> { "SPRING", "华得士国际货运代理有限公司", "DHL eCommerce (HK) Ltd", "Globavend", "华得士国际货运代理有限公司(中法)", "DHL  ECOMMERCE", "中国邮政集团公司广州市分公司", "华得士国际货运代理有限公司(西班牙)", "华得士国际货运代理有限公司(意大利)", "VALUE ADD SERVICE LIMITED（D）", "金羊城国际物流代理有限公司（T）", "深圳大森林国际货运代理有限公司（T）", "深圳市乐递供应链有限公司（T）", "深圳昊泽货运代理有限公司（T）", "深圳市天纵供应商管理有限公司（T）", "中外运－敦豪广东分公司（T）", "优比速包裹运送（广东）有限公司（T）", "天地国际运输代理（中国）有限公司广州分公司（T）", "HK TWELVE INTERNATIONAL CO., LIMITED（T）", "香港宇通供应链科技有限公司", "深圳市天纵供应商管理有限公司", "香港杜斯塔克科技有限公司", "深圳昊泽货运代理有限公司（T)" };
        List<string> arrMidland = new List<string> { "Midlands Associates Ltd", "Midlands Associates Ltd（B）" };
        List<string> arrHuaYu = new List<string> { "橙联平台充值", "江苏天地华宇物联科技有限公司" };
        List<string> arrRongHeng = new List<string> { "深圳荣恒国际物流有限公司（美金）", "深圳荣恒国际物流有限公司（人民币）", "深圳荣恒国际物流有限公司" };

        public void Init(DateTime day)
        {
            //业务逻辑
            var listHappen = new List<Models.dc_base_logistics>();
            var listPay = new List<dc_base_logistics>();
            var listRecevice = new List<dc_base_logistics>();

            var list = new List<dc_daily_logistics_supplier_transaction>();

            
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                
                //发生额
                var sql = $"select t1.company_name,sum(t1.merchant_shipment_cost_cny) as merchant_shipment_cost_cny from dc_base_logistics t1 where ((t1.reconciliation_type='跟踪号对账' and t1.diff_state_type!=0) or (t1.reconciliation_type='单独费用')) and t1.merchant_reconciliation_time>='{day.ToString("yyyy-MM-dd")}' and t1.merchant_reconciliation_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' and t1.company_name!='广州嘉和货运代理有限公司' group by t1.company_name";
                listHappen = cn.Query<Models.dc_base_logistics>(sql).AsList();

                //付款额
                sql = $@"select sum(cashier_paymoneyrmb) as merchant_shipment_cost_cny,tradeb_bjectname as company_name from dc_base_finance_cashier t1 where t1.cashier_status=1 and t1.cashier_type=1 and t1.type_name='销售费用/物流费' and t1.sourcecode='newCost' and t1.cashier_time>='{day.ToString("yyyy-MM-dd")}' and t1.cashier_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' group by tradeb_bjectname";
                listPay = cn.Query<dc_base_logistics>(sql).AsList();

                //收款额
                sql = $@"select sum(cashier_paymoneyrmb) as merchant_shipment_cost_cny,tradeb_bjectname as company_name from dc_base_finance_cashier t1 where t1.cashier_status=1 and t1.cashier_type=2 and t1.type_name='销售费用/物流费' and t1.sourcecode='newCost' and t1.cashier_time>='{day.ToString("yyyy-MM-dd")}' and t1.cashier_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}' group by tradeb_bjectname";
                listRecevice = cn.Query<dc_base_logistics>(sql).AsList();


                var listSupplier = new List<string>();
                listSupplier.AddRange(listHappen.Select(a => a.company_name));
                listSupplier.AddRange(listPay.Select(a => a.company_name));
                listSupplier.AddRange(listRecevice.Select(a => a.company_name));

                listSupplier = listSupplier.Distinct().ToList();

                foreach (var item in listSupplier)
                {
                    var m = new dc_daily_logistics_supplier_transaction() {
                        amount_end = 0,
                        amount_happen = 0,
                        amount_other = 0,
                        amount_pay = 0,
                        amount_receipt = 0,
                        amount_start = 0,

                        createtime = DateTime.Now,
                        day = day,
                        lastupdatetime = DateTime.Now,
                        lastupdateuserid = 0,
                        lastupdateusername = "admin",
                        supplierid = 0,
                        suppliername = item,
                       
                    };

                    var objHappen = listHappen.Where(a => a.company_name == item).FirstOrDefault();
                    if (objHappen != null)
                    {
                        m.amount_happen = objHappen.merchant_shipment_cost_cny;
                    }

                    var objPay = listPay.Where(a => a.company_name == item).FirstOrDefault();
                    if (objPay != null)
                    {
                        m.amount_pay = objPay.merchant_shipment_cost_cny;
                    }

                    var objReceive = listRecevice.Where(a => a.company_name == item).FirstOrDefault();
                    if (objReceive != null)
                    {
                        m.amount_receipt = objReceive.merchant_shipment_cost_cny;
                    }

                    list.Add(m);
                }

            }

            Save(list);

        }

        private void Save(List<dc_daily_logistics_supplier_transaction> list)
        {
            if (list.Count == 0)
            {
                return;
            }

            var _sql = "insert dc_daily_logistics_supplier_transaction (day,supplierid,suppliername,amount_start,amount_happen,amount_pay,amount_receipt,amount_other,amount_end,createtime,lastupdatetime,lastupdateuserid,lastupdateusername) values ";
            var s = "";

            //合并三通的数据
            var objSanTong = list.Where(a => arrSanTong.Contains(a.suppliername)).ToList();
            var tempSanTong = new dc_daily_logistics_supplier_transaction
            {
                amount_happen = objSanTong.Count > 0 ? objSanTong.Sum(a => a.amount_happen) : 0,
                amount_pay = objSanTong.Count > 0 ? objSanTong.Sum(a => a.amount_pay) : 0,
                amount_receipt = objSanTong.Count > 0 ? objSanTong.Sum(a => a.amount_receipt) : 0,
                amount_other = objSanTong.Count > 0 ? objSanTong.Sum(a => a.amount_other) : 0,
                suppliername = "三通",
                supplierid = 0,
                amount_end = 0,
                amount_start = 0,
                createtime = DateTime.Now,
                day = list.FirstOrDefault().day,
                lastupdatetime = DateTime.Now,
                lastupdateuserid = 0,
                lastupdateusername = "admin"
            };

            //合并Midland国际的数据
            var objMidland = list.Where(a => arrMidland.Contains(a.suppliername)).ToList();
            var tempMidland = new dc_daily_logistics_supplier_transaction
            {
                amount_happen = arrMidland.Count > 0 ? objMidland.Sum(a => a.amount_happen) : 0,
                amount_pay = arrMidland.Count > 0 ? objMidland.Sum(a => a.amount_pay) : 0,
                amount_receipt = arrMidland.Count > 0 ? objMidland.Sum(a => a.amount_receipt) : 0,
                amount_other = arrMidland.Count > 0 ? objMidland.Sum(a => a.amount_other) : 0,
                suppliername = "Midlands Associates",
                supplierid = 0,
                amount_end = 0,
                amount_start = 0,
                createtime = DateTime.Now,
                day = list.FirstOrDefault().day,
                lastupdatetime = DateTime.Now,
                lastupdateuserid = 0,
                lastupdateusername = "admin"
            };

            //合并华宇的数据
            var objHuaYu = list.Where(a => arrHuaYu.Contains(a.suppliername)).ToList();
            var tempHuaYu = new dc_daily_logistics_supplier_transaction
            {
                amount_happen = objHuaYu.Count > 0 ? objHuaYu.Sum(a => a.amount_happen) : 0,
                amount_pay = objHuaYu.Count > 0 ? objHuaYu.Sum(a => a.amount_pay) : 0,
                amount_receipt = objHuaYu.Count > 0 ? objHuaYu.Sum(a => a.amount_receipt) : 0,
                amount_other = objHuaYu.Count > 0 ? objHuaYu.Sum(a => a.amount_other) : 0,

                suppliername = "江苏天地华宇物联科技有限公司",
                supplierid = 0,
                amount_end = 0,
                amount_start = 0,
                createtime = DateTime.Now,
                day = list.FirstOrDefault().day,
                lastupdatetime = DateTime.Now,
                lastupdateuserid = 0,
                lastupdateusername = "admin"

            };

            var objRongHeng = list.Where(a => arrRongHeng.Contains(a.suppliername)).ToList();
            var tempRongHeng = new dc_daily_logistics_supplier_transaction
            {
                amount_happen = objRongHeng.Count > 0 ? objRongHeng.Sum(a => a.amount_happen) : 0,
                amount_pay = objRongHeng.Count > 0 ? objRongHeng.Sum(a => a.amount_pay) : 0,
                amount_receipt = objRongHeng.Count > 0 ? objRongHeng.Sum(a => a.amount_receipt) : 0,
                amount_other = objRongHeng.Count > 0 ? objRongHeng.Sum(a => a.amount_other) : 0,

                suppliername = "深圳荣恒国际物流有限公司",
                supplierid = 0,
                amount_end = 0,
                amount_start = 0,
                createtime = DateTime.Now,
                day = list.FirstOrDefault().day,
                lastupdatetime = DateTime.Now,
                lastupdateuserid = 0,
                lastupdateusername = "admin"

            };

            //去除三通、华宇、荣恒的子公司数据
            list = list.Where(a => !arrSanTong.Contains(a.suppliername) && !arrMidland.Contains(a.suppliername) && !arrHuaYu.Contains(a.suppliername) && !arrRongHeng.Contains(a.suppliername)).ToList();

            //增加三通、华宇、荣恒数据到列表
            list.Add(tempSanTong);
            list.Add(tempMidland);
            list.Add(tempHuaYu);
            list.Add(tempRongHeng);


            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                foreach (var item in list)
                {
                    //获取期初
                    var sql = $"select * from dc_daily_logistics_supplier_transaction where suppliername='{item.suppliername}' and day<'{item.day.ToString("yyyy-MM-dd")}' order by day desc limit 1";
                    var objStart = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);
                    if (objStart != null)
                    {
                        item.amount_start = objStart.amount_end;
                    }

                    //查看是否有本期数据
                    sql = $"select * from dc_daily_logistics_supplier_transaction where suppliername='{item.suppliername}' and day='{item.day.ToString("yyyy-MM-dd")}'";
                    var obj = cn.QueryFirstOrDefault<dc_daily_logistics_supplier_transaction>(sql);
                    if (obj != null)
                    {
                        item.amount_other = obj.amount_other;
                    }

                    cn.Execute($"delete from dc_daily_logistics_supplier_transaction where day='{item.day.ToString("yyyy-MM-dd")}' and suppliername='{item.suppliername}'");

                    //期末 = 期初+发生额-付款额+收款额（绝对值）+其他金额（手工导入数据）
                    item.amount_end = item.amount_start + item.amount_happen - item.amount_pay + System.Math.Abs(item.amount_receipt) + item.amount_other;

                    s += ($"('{item.day.ToString("yyyy-MM-dd")}',{item.supplierid},'{item.suppliername}',{item.amount_start},{item.amount_happen},{item.amount_pay},{item.amount_receipt},{item.amount_other},{item.amount_end},'{item.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.lastupdatetime.ToString("yyyy-MM-dd HH:mm:ss")}',{item.lastupdateuserid},'{item.lastupdateusername}'),");
                }

                if (s.Length > 0)
                {
                    s = s.Substring(0, s.Length - 1);

                    cn.Execute(_sql + s);
                }
            }
        }

    }
}
