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

namespace Bailun.DC.DailyPurchaseSellStock
{
    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 == 5 && now.Minute == 01)  //每天 23：59分启动
                {
                    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_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_read + "Allow User Variables=True;"))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var page = 1;
                    var pagesize = 5000;


                    //昨日库存
                    sql = $@"select t1.bailun_sku,t1.warehouse_code,t2.warehouse_name,(t1.usable_stock+t1.occupy_stock) count,((t1.usable_stock+t1.occupy_stock)*t1.unit_price) as amount from dc_daily_stock t1 
                        left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        where t1.record_time='{day.AddDays(-1).ToString("yyyy-MM-dd")}'";
                    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 t1.bailun_sku,t1.warehouse_code,t2.warehouse_name,(t1.usable_stock+t1.occupy_stock) count,((t1.usable_stock+t1.occupy_stock)*t1.unit_price) as amount from dc_daily_stock t1 
                        left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        where t1.record_time='{day.ToString("yyyy-MM-dd")}'";
                    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 t1.bailun_sku,t1.warehouse_code,t2.warehouse_name,sum(t1.quantity_purchase+t1.quantity_transfer) count,sum((t1.quantity_purchase+t1.quantity_transfer)*t1.unit_price) as amount from dc_daily_transit t1 
                        left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        where t1.record_time='{day.AddDays(-1).ToString("yyyy-MM-dd")}' and (t1.quantity_purchase>0 or t1.quantity_transfer>0) group by t1.bailun_sku,t1.warehouse_code";
                    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 t1.bailun_sku,t1.warehouse_code,t2.warehouse_name,sum(t1.quantity_purchase+t1.quantity_transfer) count,sum((t1.quantity_purchase+t1.quantity_transfer)*t1.unit_price) as amount from dc_daily_transit t1 
                        left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                        where t1.record_time='{day.ToString("yyyy-MM-dd")}' and (t1.quantity_purchase>0 or t1.quantity_transfer>0) group by t1.bailun_sku,t1.warehouse_code
                        ";
                    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.ToUpper(), p.warehouse_name)));
                    temparr.AddRange(objStock.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code.ToUpper(), p.warehouse_name)));
                    temparr.AddRange(objPreOnWay.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code.ToUpper(), p.warehouse_name)));
                    temparr.AddRange(objOnWay.Select(p => new Tuple<string, string, string>(p.bailun_sku.ToUpper(), p.warehouse_code.ToUpper(), p.warehouse_name)));

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

                foreach (var item in temparr)
                {
                    var m = new dc_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_code.ToUpper() == item.Item2).FirstOrDefault();
                    if (p != null)
                    {
                        m.start_stock_count = p.count;
                        m.start_stock_amount = p.amount;
                    }

                    p = objStock.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_code.ToUpper() == item.Item2).FirstOrDefault();
                    if (p != null)
                    {
                        m.end_stock_count = p.count;
                        m.end_stock_amount = p.amount;
                    }

                    p = objPreOnWay.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_code.ToUpper() == item.Item2).FirstOrDefault();
                    if (p != null)
                    {
                        m.start_onway_count = p.count;
                        m.start_onway_amount = p.amount;
                    }

                    p = objOnWay.Where(a => a.bailun_sku.ToUpper() == item.Item1 && a.warehouse_code.ToUpper() == item.Item2).FirstOrDefault();
                    if (p != null)
                    {
                        m.end_onway_count = p.count;
                        m.end_onway_amount = p.amount;
                    }

                    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_daily_purchase_sell_stock where record_time='{day.ToString("yyyy-MM-dd")}'", null, null, 2 * 60);
                    sql = "insert dc_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 int count { get; set; }

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