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



namespace Bailun.DC.SkuStatistics
{
    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.Minute == 1)  //每1小时同步一次
                {
                    UpdateStock();
                    UpdateOnWay();
                }

                if (now.Hour == 9 && now.Minute == 3)
                {
                    UpdateShipping();
                }

                if (now.Hour == 6 && now.Minute == 3)
                {
                    UpdateSale();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }


        public Services()
        {
            Dapper.SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        }
        
        
        /// <summary>
        /// 更新库存信息
        /// </summary>
        public void UpdateStock()
        {
            Console.WriteLine("启动更新库存信息服务 "+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            var sql = $@"select t1.bailun_sku,t1.usable_stock as count_stock,t2.unit_price*t1.usable_stock as amount_stock from (select t1.bailun_sku,sum(t1.usable_stock+t1.occupy_stock) as usable_stock from dc_base_stock t1 
                        where (t1.usable_stock+t1.occupy_stock)>0
                        group by t1.bailun_sku) t1
                        join dc_base_sku t2 on t1.bailun_sku=t2.bailun_sku";

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

                var list = cn.Query<Models.dc_mid_sku_statistics>(sql, null, null, true, 6 * 60);

                if (list.AsList().Count > 0)
                {
                    cn.Execute("update dc_mid_sku_statistics set count_stock=0,amount_stock=0;");

                    foreach (var item in list)
                    {
                        var result = cn.Execute("update dc_mid_sku_statistics set count_stock=" + item.count_stock + ",amount_stock=" + item.amount_stock + " where bailun_sku='" + item.bailun_sku + "'");
                        if (result == 0)
                        {
                            cn.Insert(new Models.dc_mid_sku_statistics {
                                bailun_sku = item.bailun_sku,
                                count_stock = item.count_stock,
                                amount_stock = item.amount_stock,
                                lastupdatetime = DateTime.Now,


                                amount_onway_allot = 0,
                                amount_onway_purchase = 0,
                                count_onway_allot = 0,
                                one_day_sale_amount = 0,
                                count_onway_purchase = 0,
                                count_outstock = 0,
                                count_unshipping = 0,
                                fourteen_day_sale_count = 0,
                                one_day_sale_count = 0,
                                seven_day_sale_count = 0,
                                thirty_day_sale_count = 0,
                            });
                        }
                    }
                }
            }

            Console.WriteLine("更新库存信息服务完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        /// <summary>
        /// 更新在途信息
        /// </summary>
        public void UpdateOnWay()
        {
            Console.WriteLine("启动更新在途服务 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            var sql = $@"select t1.bailun_sku,t1.quantity_purchase as count_onway_purchase,t1.quantity_transfer as count_onway_allot,(t1.quantity_purchase*t2.unit_price) as amount_onway_purchase,(t1.quantity_transfer*t2.unit_price) as amount_onway_allot from (select t1.bailun_sku,sum(t1.quantity_purchase) as quantity_purchase,sum(t1.quantity_transfer) as quantity_transfer from dc_mid_transit t1 
                        where(t1.quantity_purchase + t1.quantity_transfer) > 0
                        group by t1.bailun_sku) t1
                        join dc_base_sku t2 on t1.bailun_sku = t2.bailun_sku";

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

                var list = cn.Query<Models.dc_mid_sku_statistics>(sql, null, null, true, 6 * 60);

                if (list.AsList().Count > 0)
                {
                    cn.Execute("update dc_mid_sku_statistics set count_onway_purchase=0,count_onway_allot=0,amount_onway_purchase=0,amount_onway_allot=0;");

                    foreach (var item in list)
                    {
                        var result = cn.Execute("update dc_mid_sku_statistics set count_onway_purchase=" + item.count_onway_purchase + ",count_onway_allot=" + item.count_onway_allot + ",amount_onway_purchase="+item.amount_onway_purchase+ ",amount_onway_allot="+item.amount_onway_allot + " where bailun_sku='" + item.bailun_sku + "'");
                        if (result == 0)
                        {
                            cn.Insert(new Models.dc_mid_sku_statistics
                            {
                                bailun_sku = item.bailun_sku,
                                count_stock = 0,
                                amount_stock = 0,
                                lastupdatetime = DateTime.Now,
                                
                                amount_onway_allot = item.amount_onway_allot,
                                amount_onway_purchase = item.amount_onway_purchase,
                                count_onway_allot = item.count_onway_allot,
                                count_onway_purchase = item.count_onway_purchase,
                                count_outstock = 0,
                                count_unshipping = 0,
                                one_day_sale_amount = 0,
                                fourteen_day_sale_count = 0,
                                one_day_sale_count = 0,
                                seven_day_sale_count = 0,
                                thirty_day_sale_count = 0,
                            });
                        }
                    }
                }
            }

            Console.WriteLine("更新在途服务完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        /// <summary>
        /// 更新发货信息，待发货和缺货数据
        /// </summary>
        public void UpdateShipping()
        {
            Console.WriteLine("启动更新未发货、缺货服务 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            //未发货的
            var sql = $@"select t1.bailun_sku,sum(t1.bailun_sku_quantity_ordered-t1.bailun_sku_quantity_shipped) as count_unshipping from dc_base_oms_sku t1 
                        where t1.bailun_sku_quantity_ordered>t1.bailun_sku_quantity_shipped and t1.bailun_order_status='Handling' and t1.has_delete = 0 and t1.has_scalp=0 and t1.platform_order_status!='Completed' and bailun_interception_status='None' and t1.purchase_create_time>'2020-01-01'
                        group by t1.bailun_sku;";

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

                var list = cn.Query<Models.dc_mid_sku_statistics>(sql, null, null, true, 6 * 60);

                if (list.AsList().Count > 0)
                {
                    cn.Execute("update dc_mid_sku_statistics set count_unshipping=0");

                    foreach (var item in list)
                    {
                        var result = cn.Execute("update dc_mid_sku_statistics set count_unshipping=" + item.count_unshipping + " where bailun_sku='" + item.bailun_sku + "'");
                        if (result == 0)
                        {
                            cn.Insert(new Models.dc_mid_sku_statistics
                            {
                                bailun_sku = item.bailun_sku,
                                count_stock = 0,
                                amount_stock = 0,
                                lastupdatetime = DateTime.Now,

                                amount_onway_allot = 0,
                                amount_onway_purchase = 0,
                                count_onway_allot = 0,
                                count_onway_purchase = 0,
                                count_outstock = 0,
                                count_unshipping = item.count_unshipping,
                                one_day_sale_amount = 0,
                                fourteen_day_sale_count = 0,
                                one_day_sale_count = 0,
                                seven_day_sale_count = 0,
                                thirty_day_sale_count = 0,
                            });
                        }
                    }
                }

            }

            //缺货的
            sql = $@"select t1.bailun_sku,sum(t1.quantity_out_stock) as count_outstock from dc_auto_turnover t1 where t1.status=1 and t1.quantity_out_stock>0 group by t1.bailun_sku";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<Models.dc_mid_sku_statistics>(sql, null, null, true, 6 * 60);

                if (list.AsList().Count > 0)
                {
                    cn.Execute("update dc_mid_sku_statistics set count_outstock=0");

                    foreach (var item in list)
                    {
                        var result = cn.Execute("update dc_mid_sku_statistics set count_outstock=" + item.count_outstock + " where bailun_sku='" + item.bailun_sku + "'");
                        if (result == 0)
                        {
                            cn.Insert(new Models.dc_mid_sku_statistics
                            {
                                bailun_sku = item.bailun_sku,
                                count_stock = 0,
                                amount_stock = 0,
                                lastupdatetime = DateTime.Now,

                                amount_onway_allot = 0,
                                amount_onway_purchase = 0,
                                count_onway_allot = 0,
                                count_onway_purchase = 0,
                                count_outstock = item.count_stock,
                                count_unshipping = 0,
                                one_day_sale_amount = 0,
                                fourteen_day_sale_count = 0,
                                one_day_sale_count = 0,
                                seven_day_sale_count = 0,
                                thirty_day_sale_count = 0,
                            });
                        }
                    }
                }

            }
            Console.WriteLine("更新未发货、缺货服务完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        }

        /// <summary>
        /// 更新销量信息
        /// </summary>
        public void UpdateSale()
        {
            Console.WriteLine("启动更新销量服务 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            var sql = $@"select t1.bailun_sku,sum(t1.oneday_total_sales) as one_day_sale_count,sum(t1.oneday_total_sales_amount) as one_day_sale_amount,sum(t1.sevenday_total_sales) as seven_day_sale_count,sum(t1.fourteenday_total_sales) as fourteen_day_sale_count,sum(t1.thirtyday_total_sales) as thirty_day_sale_count from dc_daily_sales t1 where t1.record_date='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' group by t1.bailun_sku";

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

                var list = cn.Query<Models.dc_mid_sku_statistics>(sql, null, null, true, 6 * 60);

                if (list.AsList().Count > 0)
                {
                    cn.Execute("update dc_mid_sku_statistics set one_day_sale_count=0");

                    foreach (var item in list)
                    {
                        var result = cn.Execute("update dc_mid_sku_statistics set one_day_sale_count=" + item.one_day_sale_count + ",one_day_sale_amount="+item.one_day_sale_amount + ",seven_day_sale_count=" + item.seven_day_sale_count + ",fourteen_day_sale_count="+item.fourteen_day_sale_count+ ",thirty_day_sale_count="+item.thirty_day_sale_count+  " where bailun_sku='" + item.bailun_sku + "'");
                        if (result == 0)
                        {
                            cn.Insert(new Models.dc_mid_sku_statistics
                            {
                                bailun_sku = item.bailun_sku,
                                count_stock = 0,
                                amount_stock = 0,
                                lastupdatetime = DateTime.Now,

                                amount_onway_allot = 0,
                                amount_onway_purchase = 0,
                                count_onway_allot = 0,
                                count_onway_purchase = 0,
                                count_outstock = 0,
                                count_unshipping = 0,
                                one_day_sale_amount = item.one_day_sale_amount,
                                fourteen_day_sale_count = item.fourteen_day_sale_count,
                                one_day_sale_count = item.one_day_sale_count,
                                seven_day_sale_count = item.seven_day_sale_count,
                                thirty_day_sale_count = item.thirty_day_sale_count,
                            });
                        }
                    }
                }

            }

            Console.WriteLine("更新销量服务完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

        }

    }
}
