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

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

        private decimal hl_amount_pay = 0;

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

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

            if (now.Hour == 3 && now.Minute == 5)  //每天 3：05分启动
            {
                Init(DateTime.Parse(now.AddDays(-1).ToString("yyyy-MM-dd")));
            }
        }



        public void Init(DateTime day)
        {
            Console.WriteLine("开始启动 >>" + DateTime.Now);
            var cn_update = new MySqlConnection(Common.GlobalConfig.ConnectionString);
            if (cn_update.State == System.Data.ConnectionState.Closed)
            {
                cn_update.Open();
            }
            cn_update.Execute("TRUNCATE dc_mid_stock_age;");


            #region 成品

            //获取当前的库存记录
            var liststock = new List<mSkuStock>();
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                liststock = cn.Query<mSkuStock>("select bailun_sku,warehouse_code,usable_stock from dc_base_stock where usable_stock>0").AsList();

                Console.WriteLine("库存大于0的记录数："+ liststock.Count+ " >>" + DateTime.Now);

                Console.WriteLine("开始计算库龄数据... >>"+DateTime.Now);
                //对比库存和入库数据，记录库龄数据
                foreach (var item in liststock)
                {
                    CalculateAging(day, item.bailun_sku, item.warehouse_code, item.usable_stock, cn, cn_update);
                    Console.WriteLine($"Sku：{item.bailun_sku}，WareHouseCode：{item.warehouse_code}，Count：{item.usable_stock} >>" + DateTime.Now);
                }

                Console.WriteLine("服务结束 " + " >>" + DateTime.Now);
            }


            //更新成品库龄数据的仓库类型字段
            var sql = @"update dc_mid_stock_age t1
                        join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        set t1.warehouse_type = t2.hq_type,t1.warehouse_name=t2.warehouse_name
                        where t1.warehouse_type='';";
            cn_update.Execute(sql, null, null, 2 * 60);

            Console.WriteLine("更新成品仓库类型字段成功 >>" + DateTime.Now);

            //更新成品库龄数据的sku产品类型字段
            sql = @"update dc_mid_stock_age t1
                    join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku
                    join dc_base_sku_finance_category t3 on t3.bailuncategoryid=t2.bailun_category_id
                    set t1.sku_category=t3.financecategoryname
                    where t1.warehouse_type!='半成品仓'";
            cn_update.Execute(sql, null, null, 2 * 60);

            Console.WriteLine("更新成品Sku产品类型字段成功 >>" + DateTime.Now);

            #endregion

            #region 半成品



            #endregion

            cn_update.Close();
            cn_update.Dispose();

        }

        public void CalculateAging(DateTime day, string sku, string warehousecode, int stock,MySqlConnection cn,MySqlConnection cn_update)
        {
            if (stock <= 0)  //更新历史的库龄数据为0
            {
                UpdateHistoryToZero(sku, warehousecode);
            }
            else
            {
                //获取当天的入库数，包括非调拨采购入库和调拨入库
                var dailyCountPurchasePutIn = 0;

                var dailyCountAllotPutIn = 0;

                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                dailyCountPurchasePutIn = cn.QueryFirstOrDefault<int?>($"select sum(count) from dc_base_purchase_inbound where bailun_sku='{sku}' and warehouse_code='{warehousecode}' and status=1 and has_transfer=0 and update_time>='{day.ToString("yyyy-MM-dd")}' and update_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}'")??0;

                dailyCountAllotPutIn = cn.QueryFirstOrDefault<int?>($"select sum(t1.count) from dc_base_transfer_complete t1 where t1.deleted=0 and t1.bailun_sku='{sku}' and t1.warehouse_code='{warehousecode}' and t1.create_time>='{day.ToString("yyyy-MM-dd")}' and t1.create_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}'")??0;

                //记录推算结果的json
                var json = new Models.jsondata() { 
                    stock = stock,

                    purchasecount = 0,
                    allotcount = 0,
                    listallot = new List<string>(),
                    listpurchase = new List<string>()
                };

                if (stock > (dailyCountPurchasePutIn + dailyCountAllotPutIn)) //当前库存大于当天的入库记录
                {
                    //获取历史入库记录数据
                    //对比当前库存数据更新库龄数据
                    var listPurchasePutIn = cn.Query<dc_base_purchase_inbound>($"select * from dc_base_purchase_inbound where bailun_sku='{sku}' and warehouse_code='{warehousecode}' and status=1 and has_transfer=0 order by update_time desc").AsList();

                    var listAllotPutIn = cn.Query<dc_base_transfer_complete>($"select * from dc_base_transfer_complete t1 where t1.deleted=0 and t1.bailun_sku='{sku}' and t1.warehouse_code='{warehousecode}' order by create_time desc").AsList();


                    if (listPurchasePutIn.Count == 0 && listAllotPutIn.Count == 0)  //没有找到入库记录
                    {
                        //把库龄都记录到2019-01-01的日期上

                        json.agecount = stock;

                        if (cn_update.State == System.Data.ConnectionState.Closed)
                        {
                            cn_update.Open();
                        }

                        cn_update.Execute($"insert dc_mid_stock_age (createtime,warehouse_code,bailun_sku,day,stock,warehouse_name,basisdata_json) value ('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{warehousecode}','{sku}','{"2019-01-01"}','{stock}','','{Newtonsoft.Json.JsonConvert.SerializeObject(json)}');");

                        //var m = new dc_mid_stock_age
                        //{
                        //    createtime = DateTime.Now,
                        //    warehouse_code = warehousecode,
                        //    bailun_sku = sku,
                        //    day = DateTime.Parse("2019-01-01"),
                        //    stock = stock,
                        //    warehouse_name = "",
                        //};

                    }
                    else
                    {
                        var listdate = listPurchasePutIn.Select(a => a.update_time.ToString("yyyy-MM-dd")).ToList();
                        listdate.AddRange(listAllotPutIn.Select(a => a.create_time.ToString("yyyy-MM-dd")).ToList());

                        listdate = listdate.Distinct().OrderByDescending(a => a).ToList();


                        var count = 0;
                        foreach (var item in listdate)
                        {
                            var purchaseCount = listPurchasePutIn.Where(a => a.update_time.ToString("yyyy-MM-dd") == item)?.Sum(a => a.count)??0;
                            var allotCount = listAllotPutIn.Where(a => a.create_time.ToString("yyyy-MM-dd") == item)?.Sum(a => a.count) ?? 0;

                            var putinCount = purchaseCount + allotCount;

                            var key = item;
                            if (item == "0001-01-01")
                            {
                                key = "2019-01-01";
                            }

                            if ((putinCount + count) <= stock)
                            {
                                json.agecount = putinCount;
                                json.purchasecount = purchaseCount;
                                json.allotcount = allotCount;

                                json.listpurchase = listPurchasePutIn.Where(a => a.update_time.ToString("yyyy-MM-dd") == item).Select(a => a.purchase_id + "*" + a.count).ToList();
                                json.listallot = listAllotPutIn.Where(a => a.create_time.ToString("yyyy-MM-dd") == item).Select(a => a.transfer_order_id + "*" + a.count).ToList();

                                if (cn_update.State == System.Data.ConnectionState.Closed)
                                {
                                    cn_update.Open();
                                }

                                cn_update.Execute($"insert dc_mid_stock_age (createtime,warehouse_code,bailun_sku,day,stock,warehouse_name,basisdata_json) value ('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{warehousecode}','{sku}','{key}','{putinCount}','','{Newtonsoft.Json.JsonConvert.SerializeObject(json)}');");

                                //var m = new dc_mid_stock_age
                                //{
                                //    createtime = DateTime.Now,
                                //    warehouse_code = warehousecode,
                                //    bailun_sku = sku,
                                //    day = DateTime.Parse(item),
                                //    stock = putinCount,
                                //    warehouse_name = "",
                                //};

                                count += putinCount;
                            }
                            else
                            {
                                //var m = new dc_mid_stock_age
                                //{
                                //    createtime = DateTime.Now,
                                //    warehouse_code = warehousecode,
                                //    bailun_sku = sku,
                                //    day = DateTime.Parse(item),
                                //    stock = stock-(putinCount + count),
                                //    warehouse_name = "",
                                //};

                                json.agecount = (stock - count);
                                json.purchasecount = purchaseCount;
                                json.allotcount = allotCount;

                                json.listpurchase = listPurchasePutIn.Where(a => a.update_time.ToString("yyyy-MM-dd") == item).Select(a => a.purchase_id + "*" + a.count).ToList();
                                json.listallot = listAllotPutIn.Where(a => a.create_time.ToString("yyyy-MM-dd") == item).Select(a => a.transfer_order_id + "*" + a.count).ToList();


                                if (cn_update.State == System.Data.ConnectionState.Closed)
                                {
                                    cn_update.Open();
                                }

                                cn_update.Execute($"insert dc_mid_stock_age (createtime,warehouse_code,bailun_sku,day,stock,warehouse_name,basisdata_json) value ('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{warehousecode}','{sku}','{key}','{(stock-count)}','','{Newtonsoft.Json.JsonConvert.SerializeObject(json)}');");

                                count = stock;

                                break;
                            }

                        }

                        if (count < stock)
                        {
                            json.agecount = stock - count;


                            cn_update.Execute($"insert dc_mid_stock_age (createtime,warehouse_code,bailun_sku,day,stock,warehouse_name,basisdata_json) value ('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{warehousecode}','{sku}','{"2019-01-01"}','{(stock-count)}','','{Newtonsoft.Json.JsonConvert.SerializeObject(json)}');");
                        }
                    }

                }
                else
                {
                    //计算差值为今天的库龄数据
                    //var m = new dc_mid_stock_age { 
                    //    createtime = DateTime.Now,
                    //    warehouse_code = warehousecode,
                    //    bailun_sku = sku,
                    //    day = day,
                    //    stock = (dailyCountPurchasePutIn+dailyCountAllotPutIn)-stock,
                    //    warehouse_name = "",
                    //};

                    json.agecount = stock;
                    json.purchasecount = dailyCountPurchasePutIn;
                    json.allotcount = dailyCountAllotPutIn;


                    json.listpurchase = cn.Query<dc_base_purchase_inbound>($"select * from dc_base_purchase_inbound where bailun_sku='{sku}' and warehouse_code='{warehousecode}' and status=1 and has_transfer=0 and update_time>='{day.ToString("yyyy-MM-dd")}' and update_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}'").Select(a => a.purchase_id + "*" + a.count).ToList();
                    json.listallot = cn.Query<dc_base_transfer_complete>($"select * from dc_base_transfer_complete t1 where t1.deleted=0 and t1.bailun_sku='{sku}' and t1.warehouse_code='{warehousecode}' and t1.create_time>='{day.ToString("yyyy-MM-dd")}' and t1.create_time<'{day.AddDays(1).ToString("yyyy-MM-dd")}'").Select(a => a.transfer_order_id + "*" + a.count).ToList();


                    cn_update.Execute($"insert dc_mid_stock_age (createtime,warehouse_code,bailun_sku,day,stock,warehouse_name,basisdata_json) value ('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{warehousecode}','{sku}','{day.ToString("yyyy-MM-dd")}','{(stock)}','','{Newtonsoft.Json.JsonConvert.SerializeObject(json)}');");

                }

            }




        }

        private string UpdateHistoryToZero(string sku, string warehousecode)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                cn.Execute("update dc_mid_stock_age set stock=0 where bailun_sku='" + sku + "' and warehouse_code='" + warehousecode + "'");

                return "";
            }
        }

    }
}
