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

namespace Bailun.DC.LogicWareHouse
{
    public class Services : BackgroundService
    {
        public Services()
        {
            Dapper.SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        }

        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 == 6 && now.Minute == 03)
                {
                    Console.WriteLine("时间 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    Save();
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    
        public bool Save()
        {
            Console.WriteLine("进去方法，" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;");
            var cn_read = new MySqlConnection(Common.GlobalConfig.ConnectionString_read + "Allow User Variables=True;");

            try
            {
                
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }
                    
                cn.Execute("TRUNCATE dc_skuwarehouse_stock_sales", 20 * 60);

                var page = 1;
                var pagesize = 5000;
                //var listcount = cn.QueryFirstOrDefault<int>("select count(id) from dc_base_stock");
                var resultcount = 0;

                while (page == 1 || (resultcount > 0))
                {
                    
                    if(cn_read.State== System.Data.ConnectionState.Closed)
                    {
                        cn_read.Open();
                    }

                    var strlimit = (((page - 1) * pagesize) + "," + pagesize);
                    var sql = $@"select t1.bailun_sku,t1.warehouse_code,t4.warehouse_name,t4.hq_type,(t1.usable_stock) usable_stock,((t1.usable_stock)*t1.unit_price) amount_stock,(t2.quantity_purchase) quantity_purchase,(t2.quantity_purchase*t1.unit_price) purchase_amount,(t2.quantity_transfer) quantity_transfer,(t2.quantity_transfer*t1.unit_price) amount_transit,(t5.oneday_total_sales) oneday_total_sales,(t5.sevenday_total_sales) sevenday_total_sales,(t5.fourteenday_total_sales) fourteenday_total_sales,(t5.thirtyday_total_sales) thirtyday_total_sales,t6.quantity_transfer as quantity_tuneout_onway,t6.quantity_transfer_delivery as quantity_tuneout_30days,(t6.quantity_transfer_delivery*t1.unit_price) as amount_tuneout_30days,t7.quantity_transfer_inbound as quantity_tunein_30days,(t7.quantity_transfer_inbound*t1.unit_price) as amount_tunein_30days,(t5.thirtyday_total_sales/(t1.usable_stock)) as pin_rate_stock,t5.oneday_total_sales_amount as amount_onedaysale,t5.sevenday_total_sales_amount as sevenday_total_sales_amount,t5.fourteenday_total_sales_amount as fourteenday_total_sales_amount,t5.thirtyday_total_sales_amount as thirtyday_total_sales_amount
                            from (select t01.bailun_sku,t01.warehouse_code,(t01.usable_stock+t01.occupy_stock) as usable_stock,t02.unit_price from dc_base_stock t01 
                            join dc_base_sku t02 on t01.bailun_sku=t02.bailun_sku and t02.company_id=1 order by t01.id
                            limit {strlimit}) t1
                            left join dc_daily_transit t2 on t1.warehouse_code=t2.warehouse_code and t1.bailun_sku=t2.bailun_sku and t2.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}'
                            left join dc_daily_sales t5 on t5.record_date = '{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.warehouse_code=t5.warehouse_code and t1.bailun_sku =t5.bailun_sku 
                            join dc_base_warehouse t4 on t1.warehouse_code=t4.warehouse_code
                            left join (select t0.bailun_sku,t0.source_warehouse_code,sum(quantity_transfer_delivery) as quantity_transfer_delivery,sum(quantity_transfer) as quantity_transfer from dc_daily_transit_from t0 where t0.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' group by t0.bailun_sku,t0.source_warehouse_code) t6 on t1.warehouse_code=t6.source_warehouse_code and t1.bailun_sku=t6.bailun_sku
                            left join (select t0.bailun_sku,t0.warehouse_code,sum(quantity_transfer_delivery) as quantity_transfer_delivery,sum(quantity_transfer_inbound) as quantity_transfer_inbound from dc_daily_transit_from t0 where t0.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' group by t0.bailun_sku,t0.warehouse_code) t7 on t1.warehouse_code=t7.warehouse_code and t1.bailun_sku=t7.bailun_sku
                            ";


                    var liststock = cn_read.Query<DC.Models.Warehouse.dc_skuwarehouse_stock_sales>(sql, null, null, true, 30 * 60);

                    resultcount = liststock.AsList().Count;

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

                    var str = @"insert dc_skuwarehouse_stock_sales (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,fourteenday_total_sales,thirtyday_total_sales,quantity_tuneout_onway,quantity_tuneout_30days,quantity_tunein_30days,pin_rate_stock,sevenday_total_sales_amount,fourteenday_total_sales_amount,thirtyday_total_sales_amount,amount_tuneout_30days,amount_tunein_30days) values "; 
                    foreach (var item in liststock)
                    {
                        str += $@"('{item.bailun_sku}','{item.warehouse_code}','{item.warehouse_name}','{item.hq_type}',{item.usable_stock ?? 0},{item.amount_stock ?? 0},{(item.quantity_purchase ?? 0)},{item.purchase_amount ?? 0},{item.quantity_transfer ?? 0},{item.amount_transit ?? 0},{(item.oneday_total_sales ?? 0)},{item.amount_onedaysale ?? 0},{item.sevenday_total_sales ?? 0},{item.fourteenday_total_sales ?? 0},{item.thirtyday_total_sales ?? 0},{item.quantity_tuneout_onway??0},{item.quantity_tuneout_30days??0},{item.quantity_tunein_30days??0},{item.pin_rate_stock??0},{(item.sevenday_total_sales_amount)},{(item.fourteenday_total_sales_amount??0)},{(item.thirtyday_total_sales_amount??0)},{(item.amount_tuneout_30days??0)},{(item.amount_tunein_30days??0)}),";
                    }
                    if (resultcount > 0)
                    {
                        str = str.Substring(0, str.Length - 1);

                        cn.Execute(str, null, null, 5 * 60);
                        Console.WriteLine("当前页：" + page + ",每页记录：" + pagesize + "；" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+",当前记录数："+ resultcount);
                    }

                    page++;
                }
                    
                Console.WriteLine("成品运行成功，时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    
                

                //半成品库存
                SemiStock();
                
                //半成品销量
                UpdateSemiSale();

                //更新半成品销量和现金流
                UpdateLogicWareHouseSemiSale();

                // 更新缺货数和待发货数据
                UpdateOutStockAndNoShipping();

                //更新30天现金流
                UpdateCash30Days();

                //更新逻辑仓的可用天数
                UpdateAvailableDays();

                //更新半年以上库龄的sku数量
                UpdateStockAge();

                //更新sku产品分类
                UpdateSkuCategory();

                //保存每日数据快照
                SaleWHDailySnaps();

                return true;
                
            }
            catch (Exception ex)
            {
                Console.WriteLine("出现异常：" + ex.Message);
                return false;
            }
            
        }


        /// <summary>
        /// 半成品
        /// </summary>
        /// <returns></returns>
        public void SemiStock()
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var page = 1;
                    var pagesize = 5000;
                    var listcount = cn.QueryFirstOrDefault<int>("select count(id) from dc_semi_daily_stock where `day`= '" + DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + "'");
                    var resultcount = 0;

                    while (page == 1 || (resultcount == pagesize && resultcount != 0))
                    {
                        var strlimit = (((page - 1) * pagesize) + "," + pagesize);
                        var sql = $@"select sku_code as bailun_sku,warehouse_id as warehouse_code,warehouse_name,'半成品仓' as hq_type,stock_quantity as usable_stock,inventory_amount as amount_stock from dc_semi_daily_stock where `day`= '{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' limit " + strlimit;


                        var liststock = cn.Query<DC.Models.Warehouse.dc_skuwarehouse_stock_sales>(sql, null, null, true, 30 * 60);

                        resultcount = liststock.AsList().Count;

                        var str = @"insert dc_skuwarehouse_stock_sales (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,fourteenday_total_sales,thirtyday_total_sales) values ";
                        foreach (var item in liststock)
                        {
                            str += $@"('{item.bailun_sku}','{item.warehouse_code}','{item.warehouse_name}','{item.hq_type}',{item.usable_stock ?? 0},{item.amount_stock ?? 0},{(item.quantity_purchase ?? 0)},{item.purchase_amount ?? 0},{item.quantity_transfer ?? 0},{item.amount_transit ?? 0},{(item.oneday_total_sales ?? 0)},{item.amount_onedaysale ?? 0},{item.sevenday_total_sales ?? 0},{item.fourteenday_total_sales ?? 0},{item.thirtyday_total_sales ?? 0}),";
                        }
                        if (resultcount > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

                            cn.Execute(str, null, null, 5 * 60);
                            Console.WriteLine("当前页：" + page + ",每页记录：" + pagesize + "；" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        }

                        page++;
                    }

                    Console.WriteLine("半成品 运行成功，时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"重跑SemiStock，异常信息：" + ex.Message);
                SemiStock();
            }
            
        }

        /// <summary>
        /// 同步半成品销量
        /// </summary>
        public void UpdateSemiSale()
        {
            try
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 执行同步半成品销量任务...");

                var _url = "http://mjzz.bailuntec.com/api/StockReport?rows=1000";

                int page = 1;
                int totalpage = 0;

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

                    //删除旧数据
                    cn.Execute("truncate dc_semi_stock_sales_shipping;");
                }

                while (page == 1 || (page <= totalpage))
                {
                    var result = Bailun.DC.Common.HttpHelper.NetHelper.Request(_url + "&page=" + page);

                    if (!string.IsNullOrEmpty(result))
                    {
                        var json = Newtonsoft.Json.JsonConvert.DeserializeObject<mResponse>(result);

                        if (json.success)
                        {
                            Console.WriteLine("总页数：" + json.data.TotalPages + ",当前页：" + json.data.CurrentPage);

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

                                //保存数据
                                foreach (var item in json.data.Items)
                                {
                                    try
                                    {
                                        var m = new dc_semi_stock_sales_shipping()
                                        {
                                            material_id = item.material_id,
                                            available_days = item.available_days,
                                            buyer_price = item.buyer_price,
                                            material_data_center = item.material_data_center,
                                            need_quantity = item.need_quantity,
                                            on_the_way_amount = item.on_the_way_amount,
                                            on_the_way_count = item.on_the_way_count,
                                            out_of_stock = item.out_of_stock,
                                            out_stock_30 = item.out_stock_30,
                                            put_stock_30 = item.put_stock_30,
                                            redundancy_stock = item.redundancy_stock,


                                            sales_1 = item.sales_1,
                                            sales_14 = item.sales_14,
                                            sales_30 = item.sales_30,
                                            sales_7 = item.sales_7,
                                            sku = item.sku,
                                            sku_name = item.sku_name,
                                            stock = item.stock,
                                            stock_amount = item.stock_amount,


                                            sales_1_amount = item.sales_1_amount,
                                            warehouse_id = item.warehouse_id,
                                            warehouse_name = item.warehouse_name,


                                            sales_7_amount = item.sales_7_amount,
                                            sales_14_amount = item.sales_14_amount,
                                            sales_30_amount = item.sales_30_amount,
                                            out_stock_30_amount = item.out_stock_30_amount,
                                            put_stock_30_amount = item.put_stock_30_amount,

                                        };

                                        cn.Insert<dc_semi_stock_sales_shipping>(m);
                                    }
                                    catch (Exception ex)
                                    {
                                        Console.WriteLine("保存半成品销量数据"+item.warehouse_name+"&"+item.sku+" 出现异常："+ex.Message);
                                    }
                                }

                            }

                            totalpage = json.data.TotalPages;
                            page++;
                        }
                        else
                        {
                            Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 接口返回错误信息：" + json.message);
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "重跑UpdateSemiSale，异常信息：" + ex.Message);
                UpdateSemiSale();
            }
        }

        /// <summary>
        /// 更新半成品销量和现金流
        /// </summary>
        public void UpdateLogicWareHouseSemiSale()
        {
            try
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 启动更新半成品销量和现金流任务");
                var sql = @"update dc_skuwarehouse_stock_sales t1
                join dc_semi_stock_sales_shipping t2 on t2.warehouse_name=t1.warehouse_name and t1.bailun_sku=t2.sku
                set
                t1.quantity_purchase=t2.on_the_way_count,
                t1.purchase_amount=t2.on_the_way_amount,
                t1.oneday_total_sales=t2.sales_1,
                t1.amount_onedaysale=t2.sales_1_amount,
                t1.sevenday_total_sales=t2.sales_7,
                t1.fourteenday_total_sales=t2.sales_14,
                t1.thirtyday_total_sales=t2.sales_30,
                t1.nostockcount=t2.out_of_stock,
                t1.noshippingcount=t2.need_quantity,
                t1.quantity_tuneout_30days=t2.out_stock_30,
                t1.quantity_tunein_30days=t2.put_stock_30,

                t1.sevenday_total_sales_amount=t2.sales_7_amount,
                t1.fourteenday_total_sales_amount=t2.sales_14_amount,
                t1.thirtyday_total_sales_amount=t2.sales_30_amount,
                t1.amount_tuneout_30days=t2.out_stock_30_amount,
                t1.amount_tunein_30days=t2.put_stock_30_amount";

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

                    var result = cn.Execute(sql);

                    Console.WriteLine("更新逻辑仓库半成品销量成功，受影响行数：" + result);
                }

                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 结束更新半成品销量和现金流任务");
            }
            catch (Exception ex)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "重跑UpdateLogicWareHouseSemiSale，异常信息：" + ex.Message);
                UpdateLogicWareHouseSemiSale();
            }
            
        }

        /// <summary>
        /// 更新缺货数和待发货数据
        /// </summary>
        public void UpdateOutStockAndNoShipping()
        {
            try
            {
                var sql = @"update dc_skuwarehouse_stock_sales t1
                        join dc_mid_transit t2 on (t2.quantity_unshipped>0 or t2.quantity_out_stock>0) and t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code
                        set t1.noshippingcount=t2.quantity_unshipped,t1.nostockcount=t2.quantity_out_stock";

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

                    cn.Execute("update dc_skuwarehouse_stock_sales set noshippingcount=0,nostockcount=0");
                    cn.Execute(sql);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "重跑UpdateOutStockAndNoShipping，异常信息：" + ex.Message);
                UpdateOutStockAndNoShipping();
            }
            
        }
        

        /// <summary>
        /// 更新30天现金流
        /// </summary>
        public void UpdateCash30Days()
        {
            try
            {
                Console.WriteLine("开始执行更新现金流方法 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }
                    
                    cn.Execute("update dc_skuwarehouse_stock_sales set cash_in_30days=0,cash_out_30days=0,cash_net = 0");

                    cn.Execute("TRUNCATE dc_base_stock_cash;");

                    cn.Execute(@"insert into dc_base_stock_cash(bailun_sku, warehouse_code, pay_amount_30, income_amount_30)
                    select t2.bailun_sku,t2.warehouse_code,t2.pay_amount_30,t2.income_amount_30 from dc_base_stock t2 where(t2.pay_amount_30 > 0 or t2.income_amount_30 > 0);");

                    var result = cn.Execute(@"update dc_skuwarehouse_stock_sales t1
                    join dc_base_stock_cash t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
                    set t1.cash_in_30days = t2.income_amount_30,t1.cash_out_30days = t2.pay_amount_30,t1.cash_net = (t2.income_amount_30 - t2.pay_amount_30);",null,null,6*60);
                    
                    Console.WriteLine("更新30天现金流的记录数有：" + result);
                }

                Console.WriteLine(" 更新现金流方法 已完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            catch (Exception ex)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "重跑UpdateCash30Days，异常信息：" + ex.Message);
                UpdateCash30Days();
            }
        }

        /// <summary>
        /// 更新逻辑仓库表的可用天数
        /// </summary>
        public void UpdateAvailableDays()
        {
            Console.WriteLine("【启动】更新逻辑仓库表的可用天数服务 "+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

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

                    var sql = "TRUNCATE dc_mid_logicwh_availabledays;";

                    cn.Execute(sql);

                    //半成品的可用天数
                    sql = @"insert into dc_mid_logicwh_availabledays (bailun_sku,warehouse_code,hq_type,available_days)
                            select sku as bailun_sku,warehouse_id as warehouse_code,'半成品仓' as hq_type,available_days from dc_semi_stock_sales_shipping where available_days>0;";
                    cn.Execute(sql,null,null,6*60);

                    //成品的可用天数
                    sql = @"insert into dc_mid_logicwh_availabledays (bailun_sku,warehouse_code,hq_type,available_days)
                            select bailun_sku,t1.warehouse_code,t2.hq_type,available_days from dc_base_stock t1 
                            left join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                            where available_days>0;";
                    cn.Execute(sql, null, null, 6 * 60);

                    //更新逻辑仓的可用天数
                    sql = @"update dc_skuwarehouse_stock_sales t1 
                            join dc_mid_logicwh_availabledays t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code
                            set t1.available_days=t2.available_days";
                    cn.Execute(sql, null, null, 6 * 60);
                    
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("更新逻辑仓库表的可用天数服务失败，异常信息：" + ex.Message);
                UpdateAvailableDays();
            }

            Console.WriteLine("【完成】更新逻辑仓库表的可用天数服务 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        /// <summary>
        /// 保存每日数据快照
        /// </summary>
        public void SaleWHDailySnaps()
        {
            try
            {
                Console.WriteLine("开始执行保存逻辑仓库的每日快照信息 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                var sql = @"insert into dc_daily_logic_wh_statistic (day,hq_type,warehouse_code,warehouse_name,usable_stock,amount_stock,quantity_purchase,purchase_amount,quantity_transfer,amount_transit,oneday_total_sales,amount_onedaysale,sevenday_total_sales,fourteenday_total_sales,thirtyday_total_sales,quantity_tuneout_onway,quantity_tuneout_30days,quantity_tunein_30days,nostockcount,noshippingcount,cash_in_30days,cash_out_30days,cash_net,pin_rate_stock,sevenday_total_sales_amount,fourteenday_total_sales_amount,thirtyday_total_sales_amount,amount_tuneout_30days,amount_tunein_30days,skucategory)
select DATE_SUB(curdate(),INTERVAL 1 DAY) as day,t1.* from (select t1.hq_type,t1.warehouse_code,t1.warehouse_name,sum(t1.usable_stock) usable_stock,sum(t1.amount_stock) amount_stock,sum(t1.quantity_purchase) quantity_purchase,sum(t1.purchase_amount) purchase_amount,sum(t1.quantity_transfer) quantity_transfer,sum(t1.amount_transit) amount_transit,sum(t1.oneday_total_sales) oneday_total_sales,sum(t1.amount_onedaysale) amount_onedaysale,sum(t1.sevenday_total_sales) sevenday_total_sales,sum(t1.fourteenday_total_sales) fourteenday_total_sales,sum(t1.thirtyday_total_sales) thirtyday_total_sales,sum(quantity_tuneout_onway) quantity_tuneout_onway,sum(t1.quantity_tuneout_30days) quantity_tuneout_30days,sum(t1.quantity_tunein_30days) quantity_tunein_30days,sum(nostockcount) as nostockcount,sum(noshippingcount) as noshippingcount,sum(cash_in_30days) as cash_in_30days,sum(cash_out_30days) as cash_out_30days,sum(cash_net) as cash_net,(sum(thirtyday_total_sales)/sum(usable_stock)) as pin_rate_stock,sum(sevenday_total_sales_amount) as sevenday_total_sales_amount,sum(fourteenday_total_sales_amount) as fourteenday_total_sales_amount,sum(thirtyday_total_sales_amount) as thirtyday_total_sales_amount,sum(amount_tuneout_30days) as amount_tuneout_30days,sum(amount_tunein_30days) as amount_tunein_30days,skucategory from dc_skuwarehouse_stock_sales t1 group by warehouse_code,skucategory) t1";

                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_logic_wh_statistic where day='"+DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")+"'");
                    cn.Execute(sql, null, null, 6 * 60);

                }

                Console.WriteLine("结束 保存逻辑仓库的每日快照信息 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            catch (Exception ex)
            {
                Console.WriteLine("保存逻辑仓库的每日快照信息失败，异常信息：" + ex.Message);
                if (ex.Message.IndexOf("Duplicate entr") >= 0)
                {
                    Console.WriteLine("保存逻辑仓库的每日快照信息失败，异常信息：" + ex.Message);
                }
                else
                {
                    SaleWHDailySnaps();
                }
            }
        }

        public void UpdateStockAge()
        {
            Console.WriteLine("【开始】成品更新库龄超过6个月的库存数据 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            var sql = $@"update (select bailun_sku,warehouse_code,sum(stock) stock from dc_mid_stock_age where day<'{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' group by bailun_sku,warehouse_code) t1
                    join dc_skuwarehouse_stock_sales t2 on t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code and t2.hq_type!='半成品仓'
                    join dc_base_sku t3 on t1.bailun_sku = t3.bailun_sku
                    set t2.stock_age_6months=t1.stock,t2.stock_age_6months_amount=t1.stock*t3.unit_price";


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

                    cn.Execute(sql, null, null, 6 * 60);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("成品更新库龄超过6个月的库存数据服务失败，异常信息：" + ex.Message);
                UpdateStockAge();
            }

            Console.WriteLine("【完成】成品更新库龄超过6个月的库存数据 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        public void UpdateSkuCategory()
        {
            Console.WriteLine("【开始】成品更新sku分类数据 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            var sql = $@"update dc_skuwarehouse_stock_sales 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.skucategory=t3.financecategoryname
                    where hq_type!='半成品仓'";

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

                    cn.Execute(sql, null, null, 6 * 60);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("成品更新sku分类数据服务失败，异常信息：" + ex.Message);
                UpdateStockAge();
            }

            Console.WriteLine("【完成】成品更新sku分类数据数据 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));


        }
    }
}
