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

namespace Bailun.DC.DailySemiPurchaseSellStock
{
    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 == 7 && now.Minute == 18)  //每天 04：18分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(now.AddDays(-1).ToShortDateString());

                    Init(start);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }

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


        public void Init(DateTime day, int count = 0)
         {
            try
            {
                var sql = "";
                var list = new List<dc_semi_daily_purchase_sell_stock>();
                var temparr = new List<Tuple<string, string, string>>();
                var objPreStock = new List<mStock>();
                var objStock = new List<mStock>();
                var objPreOnWay = new List<mStock>();
                var objOnWay = new List<mStock>();



                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var page = 1;
                    var pagesize = 5000;


                    //昨日库存

                    sql = $@"select sku_code as bailun_sku,warehouse_id as warehouse_code,warehouse_name,sum(stock_quantity) as count,sum(inventory_amount) as amount from dc_semi_daily_stock where day='{day.AddDays(-1).ToString("yyyy-MM-dd")}' group by sku_code,warehouse_id";
                    var temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    while (temp.Count() > 0)
                    {
                        objPreStock.AddRange(temp);
                        page++;
                        temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    }
                    Console.WriteLine("昨日库存done," + DateTime.Now);

                    //在库库存
                    page = 1;
                    pagesize = 5000;

                    sql = $@"select sku_code as bailun_sku,warehouse_id as warehouse_code,warehouse_name,sum(stock_quantity) as count,sum(inventory_amount) as amount from dc_semi_daily_stock where day='{day.ToString("yyyy-MM-dd")}' group by sku_code,warehouse_id";
                    temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    while (temp.Count() > 0)
                    {
                        objStock.AddRange(temp);
                        page++;
                        temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    }
                    Console.WriteLine("在库库存done," + DateTime.Now);

                    //昨日在途库存

                    page = 1;
                    sql = $@"select sku_code as bailun_sku,warehouse_id as warehouse_code,warehouse_name,sum(transfer_quantity) as count,sum(transfer_quantity*unit_price) as amount from dc_semi_daily_transfer where day='{day.AddDays(-1).ToString("yyyy-MM-dd")}' group by sku_code,warehouse_id";
                    temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    while (temp.Count() > 0)
                    {
                        objPreOnWay.AddRange(temp);
                        page++;
                        temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    }
                    Console.WriteLine("昨日在途库存done," + DateTime.Now);

                    //在途库存

                    page = 1;
                    sql = $@"select sku_code as bailun_sku,warehouse_id as warehouse_code,warehouse_name,sum(transfer_quantity) as count,sum(transfer_quantity*unit_price) as amount from dc_semi_daily_transfer where day='{day.ToString("yyyy-MM-dd")}' group by sku_code,warehouse_id";
                    temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    while (temp.Count() > 0)
                    {
                        objOnWay.AddRange(temp);
                        page++;
                        temp = cn.Query<mStock>(sql + " limit " + (page - 1) * pagesize + "," + pagesize, null, null, true, 2 * 60);
                    }
                    Console.WriteLine("在途库存done," + DateTime.Now);

                    temparr.AddRange(objPreStock.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code, p.warehouse_name)));
                    temparr.AddRange(objStock.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code, p.warehouse_name)));
                    temparr.AddRange(objPreOnWay.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code, p.warehouse_name)));
                    temparr.AddRange(objOnWay.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code, p.warehouse_name)));

                    temparr = temparr.Distinct().ToList();
                }

                foreach (var item in temparr)
                {
                    var m = new dc_semi_daily_purchase_sell_stock()
                    {
                        bailun_sku = item.Item1,
                        warehouse_code = item.Item2,
                        warehouse_name = item.Item3,
                        createtime = DateTime.Now,
                        record_time = day,

                        end_onway_amount = 0,
                        end_onway_count = 0,
                        end_stock_amount = 0,
                        end_stock_count = 0,
                        start_onway_amount = 0,
                        start_onway_count = 0,
                        start_stock_amount = 0,
                        start_stock_count = 0,
                    };

                    var p = objPreStock.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_name == item.Item3);
                    if (p.Count()>0)
                    {
                        m.start_stock_count = p.Sum(a=>a.count);
                        m.start_stock_amount = p.Sum(a=>a.amount);
                    }

                    p = objStock.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_name == item.Item3);
                    if (p.Count()>0)
                    {
                        m.end_stock_count = p.Sum(a=>a.count);
                        m.end_stock_amount = p.Sum(a=>a.amount);
                    }

                    p = objPreOnWay.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_name == item.Item3);
                    if (p.Count()>0)
                    {
                        m.start_onway_count = p.Sum(a=>a.count);
                        m.start_onway_amount = p.Sum(a=>a.amount);
                    }

                    p = objOnWay.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_name == item.Item3);
                    if (p.Count()>0)
                    {
                        m.end_onway_count = p.Sum(a=>a.count);
                        m.end_onway_amount = p.Sum(a=>a.amount);
                    }

                    if (m.end_onway_amount>0||m.end_onway_count>0||m.end_stock_amount>0||m.end_stock_count>0 || m.start_onway_amount>0||m.start_onway_count>0 || m.start_stock_amount>0 || m.start_stock_count>0)
                    {
                        list.Add(m);
                    }
                    
                }

                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True"))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    cn.Execute($"delete from dc_semi_daily_purchase_sell_stock where record_time='{day.ToString("yyyy-MM-dd")}'", null, null, 2 * 60);
                    sql = "insert dc_semi_daily_purchase_sell_stock (bailun_sku,warehouse_code,warehouse_name,createtime,record_time,end_onway_amount,end_onway_count,end_stock_amount,end_stock_count,start_onway_amount,start_onway_count,start_stock_amount,start_stock_count) values ";
                    var resultcount = 0;

                    while (resultcount < list.Count)
                    {
                        var s = "";

                        var temp1 = list.Skip(resultcount).Take(5000);

                        foreach (var item in temp1)
                        {
                            s += $"('{item.bailun_sku}','{item.warehouse_code}','{item.warehouse_name}','{item.createtime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.record_time.ToString("yyyy-MM-dd")}',{item.end_onway_amount},{item.end_onway_count},{item.end_stock_amount},{item.end_stock_count},{item.start_onway_amount},{item.start_onway_count},{item.start_stock_amount},{item.start_stock_count}),";
                        }
                        resultcount += temp1.Count();

                        Console.WriteLine("保存" + resultcount + "记录，" + DateTime.Now);

                        if (s.Length > 0)
                        {
                            cn.Execute(sql + s.Substring(0, s.Length - 1), null, null, 2 * 60);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("出现异常，异常信息：" + ex.Message);
                if (count > 2)
                {
                    Init(day, count++);
                    Console.WriteLine("===============================重跑" + day + "的数据" + DateTime.Now);
                }
                else
                {
                    Console.WriteLine("出现异常次数大于" + count + "次，服务关闭重跑");
                }
            }
        }
    
    }

    class mStock
    {
        /// <summary>
        /// sku编码
        /// </summary>
        public string bailun_sku { get; set; }

        /// <summary>
        /// 仓库编码
        /// </summary>
        public string warehouse_code { get; set; }

        /// <summary>
        /// 仓库名称
        /// </summary>
        public string warehouse_name { get; set; }

        /// <summary>
        /// 数量
        /// </summary>
        public decimal count { get; set; }

        /// <summary>
        /// 金额
        /// </summary>
        public decimal amount { get; set; }
    }
}
