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

namespace Bailun.DC.DailyInventorySummary
{
    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 == 8 && now.Minute == 1)  //每天8点1分启动
                {
                    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)
        {
            //数据来源取自逻辑仓库

            var sql = "";

            var listWarehouse = new List<string>();
            var list = new List<Models.Warehouse.dc_skuwarehouse_stock_sales>();

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

            listWarehouse = cn.Query<string>("select warehouse_code from dc_skuwarehouse_stock_sales group by warehouse_code").ToList();
            var obj = new List<dc_daily_inventory_summary>();

            foreach (var item in listWarehouse)
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                sql = $"select warehouse_code,warehouse_name,hq_type,usable_stock,amount_stock,quantity_purchase,purchase_amount,quantity_transfer,amount_transit,oneday_total_sales,amount_onedaysale from dc_skuwarehouse_stock_sales where warehouse_code='{item}';";
                list = cn.Query<Models.Warehouse.dc_skuwarehouse_stock_sales>(sql).ToList();

                if(list.Count>0)
                {
                    var m = new dc_daily_inventory_summary();
                    m.warehousecode = list.FirstOrDefault().warehouse_code;
                    m.warehousename = list.FirstOrDefault().warehouse_name;
                    m.warehousetype = list.FirstOrDefault().hq_type;
                    m.createtime = DateTime.Now;
                    m.day = day;

                    m.purchase_amount = list.Sum(a => a.purchase_amount??0);
                    m.quantity_purchase = list.Sum(a => a.quantity_purchase??0);
                    m.quantity_purchase_product = list.Where(a=>a.quantity_purchase>0).Count();

                    m.transfer_amount = list.Sum(a => a.amount_transit ?? 0);
                    m.quantity_transfer = list.Sum(a=>a.quantity_transfer??0);
                    m.quantity_transfer_product = list.Where(a=>a.quantity_transfer>0).Count();

                    m.usable_stock = list.Sum(a => a.usable_stock??0);
                    m.amount_stock = list.Sum(a => a.amount_stock ?? 0);

                    //日销大于10
                    m.sale10_count = list.Where(a => a.oneday_total_sales > 10).Sum(a => a.usable_stock ?? 0);
                    m.sale10_rate = (m.usable_stock > 0?((decimal)m.sale10_count/(decimal)m.usable_stock):0);
                    m.sale10_amount = list.Where(a => a.oneday_total_sales > 10).Sum(a => a.amount_stock ?? 0);
                    m.sale10_amount_rate = (m.amount_stock > 0 ? (m.sale10_amount / m.amount_stock) : 0);

                    //日销5-10
                    m.sale5_10_count = list.Where(a => a.oneday_total_sales > 5 && a.oneday_total_sales<=10).Sum(a => a.usable_stock ?? 0);
                    m.sale5_10_rate = (m.usable_stock > 0 ? ((decimal)m.sale5_10_count / (decimal)m.usable_stock) : 0);
                    m.sale5_10_amount = list.Where(a => a.oneday_total_sales > 5 && a.oneday_total_sales <= 10).Sum(a => a.amount_stock ?? 0);
                    m.sale5_10_amount_rate = (m.amount_stock > 0 ? (m.sale5_10_amount / m.amount_stock) : 0);

                    //日销1-5
                    m.sale1_5_amount = list.Where(a => a.oneday_total_sales > 1 && a.oneday_total_sales <= 5).Sum(a => a.amount_stock ?? 0);
                    m.sale1_5_amount_rate = (m.amount_stock > 0 ? (m.sale1_5_amount / m.amount_stock) : 0);


                    //日销 0-1
                    m.sale1_amount = list.Where(a => a.oneday_total_sales > 0 && a.oneday_total_sales <= 1).Sum(a => a.amount_stock ?? 0);
                    m.sale1_amount_rate = (m.amount_stock > 0 ? (m.sale1_amount / m.amount_stock) : 0);

                    //日销为0
                    m.sale0_amount = list.Where(a => a.oneday_total_sales == 0).Sum(a => a.amount_stock ?? 0);
                    m.sale0_count = list.Where(a => a.oneday_total_sales == 0).Sum(a => a.usable_stock ?? 0);
                    m.sale0_count_rate = (m.usable_stock > 0 ? ((decimal)m.sale0_count / (decimal)m.usable_stock) : 0);  //销量为0的sku数占比

                    obj.Add(m);
                }

            }

            cn.Close();
            cn.Dispose();

            if(obj.Count>0)
            {
                using (var cnn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if(cnn.State== System.Data.ConnectionState.Closed)
                    {
                        cnn.Open();
                    }

                    foreach (var item in obj)
                    {
                        cnn.Execute($"delete from dc_daily_inventory_summary where day='{day.ToString("yyyy-MM-dd")}' and warehousecode='{item.warehousecode}'");

                        sql = $"insert dc_daily_inventory_summary (warehousecode,warehousename,warehousetype,usable_stock,amount_stock,quantity_purchase,purchase_amount,quantity_purchase_product,quantity_transfer,transfer_amount,quantity_transfer_product,sale10_count,sale10_rate,sale10_amount,sale10_amount_rate,sale5_10_count,sale5_10_rate,sale5_10_amount,sale5_10_amount_rate,sale1_5_amount,sale1_5_amount_rate,sale1_amount,sale1_amount_rate,sale0_amount,sale0_count_rate,day,sale0_count) value ('{item.warehousecode}','{item.warehousename}','{item.warehousetype}',{item.usable_stock},{item.amount_stock},{item.quantity_purchase},{item.purchase_amount},{item.quantity_purchase_product},{item.quantity_transfer},{item.transfer_amount},{item.quantity_transfer_product},{item.sale10_count},{item.sale10_rate},{item.sale10_amount},{item.sale10_amount_rate},{item.sale5_10_count},{item.sale5_10_rate},{item.sale5_10_amount},{item.sale5_10_amount_rate},{item.sale1_5_amount},{item.sale1_5_amount_rate},{item.sale1_amount},{item.sale1_amount_rate},{item.sale0_amount},{item.sale0_count_rate},'{item.day.ToString("yyyy-MM-dd")}',{item.sale0_count});";
                        cnn.Execute(sql);
                    }

                    //保存sku明细
                    cnn.Execute($"delete from dc_skuwarehouse_stock_sales_daily where day='{day.ToString("yyyy-MM-dd")}'");
                    cnn.Execute($@"insert into dc_skuwarehouse_stock_sales_daily (day,bailun_sku,warehouse_code,warehouse_name,hq_type,usable_stock,amount_stock,quantity_purchase,purchase_amount,quantity_transfer,amount_transit,oneday_total_sales,amount_onedaysale,sevenday_total_sales,sevenday_total_sales_amount,fourteenday_total_sales,fourteenday_total_sales_amount,thirtyday_total_sales,thirtyday_total_sales_amount,quantity_tuneout_onway,quantity_tuneout_30days,amount_tuneout_30days,quantity_tunein_30days,amount_tunein_30days,createtime,nostockcount,noshippingcount,cash_in_30days,cash_out_30days,cash_net,pin_rate_stock,available_days)
select '{day.ToString("yyyy-MM-dd")}' as day, bailun_sku, warehouse_code, warehouse_name, hq_type, usable_stock, amount_stock, quantity_purchase, purchase_amount, quantity_transfer, amount_transit, oneday_total_sales, amount_onedaysale, sevenday_total_sales, sevenday_total_sales_amount, fourteenday_total_sales, fourteenday_total_sales_amount, thirtyday_total_sales, thirtyday_total_sales_amount, quantity_tuneout_onway, quantity_tuneout_30days, amount_tuneout_30days, quantity_tunein_30days, amount_tunein_30days, createtime, nostockcount, noshippingcount, cash_in_30days, cash_out_30days, cash_net, pin_rate_stock, available_days from dc_skuwarehouse_stock_sales where (usable_stock + amount_stock + quantity_purchase + purchase_amount + quantity_transfer + amount_transit + oneday_total_sales + amount_onedaysale + sevenday_total_sales + sevenday_total_sales_amount + fourteenday_total_sales + fourteenday_total_sales_amount + thirtyday_total_sales + thirtyday_total_sales_amount + quantity_tuneout_onway + quantity_tuneout_30days + amount_tuneout_30days + quantity_tunein_30days + amount_tunein_30days + nostockcount + noshippingcount + cash_in_30days + ABS(cash_out_30days)) > 0",null,null,2*60);

                }
            }

        }

    }
}
