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

namespace Bailun.DC.SkuDataWareHouse
{
    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 == 12 && now.Minute == 1)
                {
                    Save();

                    UpdateAllotOnWay();
                }

                if (now.Minute % 30==0) //每30分钟更新一次
                {
                    //UpdateAllotOnWay();
                }

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

        public void Save()
        {
            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();
                    }

                    cn.Execute("truncate dc_datawarehouse;", 5 * 60);

                    cn.Execute("truncate dc_datawh_temp_stock;", 5 * 60);
                }



                var page = 1;
                var pagesize = 8000;
                var listcount = 0; 
                var resultcount = 0;

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

                    listcount = cn.QueryFirstOrDefault<int>("select count(id) from dc_datawh_temp_stock");
                }

                while (page == 1 || (resultcount == pagesize && resultcount != 0))
                {
                    var liststock = new List<dc_datawarehouse>();
                    using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString_read))
                    {
                        if(cn.State== System.Data.ConnectionState.Closed)
                        {
                            cn.Open();
                        }

                        var strlimit = (((page - 1) * pagesize) + "," + pagesize);
                        var sql = $@"select t2.hq_type as warehousetype,t2.warehouse_name as warehousename,t2.warehouse_code,t1.bailun_sku as sku,t1.category_id,t1.categoryname,t1.sku_title_cn as skuname,(case when t4.status=1 then 0 else 1 end) monitorstatus,t5.clearancestatus,(case when t8.id is not null then 1 else 0 end) as tortstatus,t5.str_skutag,t1.unit_price as buyprice,t1.usable_stock,(t1.usable_stock*t1.unit_price) amount_stock,t6.quantity_purchase,(t6.quantity_purchase*t1.unit_price) as purchase_amount,t6.quantity_transfer,(t6.quantity_transfer*t1.unit_price) amount_transit,t6.quantity_out_stock,t7.oneday_total_sales,(t7.oneday_total_sales*t1.unit_price) as amount_onedaysale,t7.sevenday_total_sales,(t7.sevenday_total_sales*t1.unit_price) amount_sevendaysale,t7.fourteenday_total_sales,t7.thirtyday_total_sales,t7.sevenday_average_sales,t7.fourteenday_average_sales,t7.thirtyday_average_sales,t1.create_time as skucreatetime,t2.area_id,t2.area_name,t1.brand as skubrand,(case when t9.id is not null then 1 else 0 end) as is_redundancy
                                from (select t01.bailun_sku,t01.warehouse_code,t01.usable_stock,t02.unit_price,t02.create_time,t02.bailun_category_id as category_id,t02.bailun_category_name as categoryname,t02.sku_title_cn,t02.brand 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
                                join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code
                                left join dc_auto_config_sku_warehouse t4 on t4.bailun_sku=t1.bailun_sku and t4.warehouse_code=t1.warehouse_code
                                left join dc_sku_monitor t5 on t5.sku=t1.bailun_sku and t5.warehousecode=t1.warehouse_code
                                left join dc_mid_transit t6 on t1.bailun_sku=t6.bailun_sku and t1.warehouse_code=t6.warehouse_code
                                left join dc_daily_sales t7 on t7.record_date = DATE_SUB(curdate(),INTERVAL 1 DAY) and t1.bailun_sku=t7.bailun_sku and t7.warehouse_code=t1.warehouse_code
                                left join dc_base_tort t8 on t1.bailun_sku=t8.bailun_sku
                                left join dc_auto_forecast_fluctuation t9 on t1.bailun_sku=t9.bailun_sku and t1.warehouse_code=t9.warehouse_code and t9.type=3
                                ";


                       liststock = cn.Query<dc_datawarehouse>(sql, null, null, true, 30 * 60).AsList();
                    }
                    

                    resultcount = liststock.AsList().Count;

                    var str = @"insert dc_datawarehouse (warehousetype,area_id,area_name,warehousename,warehouse_code,sku,category_id,categoryname,skuname,monitorstatus,clearancestatus,tortstatus,str_skutag,buyprice,usable_stock,amount_stock,quantity_purchase,purchase_amount,quantity_transfer,amount_transit,quantity_out_stock,oneday_total_sales,amount_onedaysale,sevenday_total_sales,amount_sevendaysale,fourteenday_total_sales,thirtyday_total_sales,sevenday_average_sales,fourteenday_average_sales,thirtyday_average_sales,skucreatetime,lastupdatetime,skubrand,is_redundancy) values ";
                    foreach (var item in liststock)
                    {
                        if (!string.IsNullOrEmpty(item.categoryname))
                        {
                            item.categoryname = item.categoryname.Replace("'", "");
                        }

                        str += $@"('{item.warehousetype}','{item.area_id ?? 0}','{item.area_name}','{item.warehousename}','{item.warehouse_code}','{item.sku}',{item.category_id ?? 0},'{(!string.IsNullOrEmpty(item.categoryname) ? item.categoryname : "")}','{item.skuname.Replace("'", "")}',{item.monitorstatus ?? 1},{item.clearancestatus ?? 0},{item.tortstatus ?? 0},'{item.str_skutag}',{item.buyprice ?? 0},{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.quantity_out_stock ?? 0},{item.oneday_total_sales ?? 0},{item.amount_onedaysale ?? 0},{item.sevenday_total_sales ?? 0},{item.amount_sevendaysale ?? 0},{item.fourteenday_total_sales ?? 0},{item.thirtyday_total_sales ?? 0},{item.sevenday_average_sales ?? 0},{item.fourteenday_average_sales ?? 0},{item.thirtyday_average_sales ?? 0},'{(item.skucreatetime.HasValue ? item.skucreatetime.Value.ToString("yyyy-MM-dd HH:mm:ss") : "1970-01-01")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{item.skubrand.Replace("'", "''")}',{(item.is_redundancy ?? 0)}),";
                    }

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

                            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") +" 运行失败，异常信息："+ex.Message);
            }
            
        }


        /// <summary>
        /// 更新调拨在途数据
        /// </summary>
        public void UpdateAllotOnWay()
        {
            Console.WriteLine("启动更新在途数 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            var sql = @"select t1.*,t2.unit_price from (SELECT bailun_sku,warehouse_code,sum(count+countplan) as `count` FROM (
                 SELECT t1.targetwareno                                                                      as warehouse_code,
                        t2.sku                                                                               as bailun_sku,
                        t1.transporttype                                                                     as transport_type,
                        if(t2.deliverycount - t2.storagecount < 0, 0, t2.deliverycount - t2.storagecount) as `count`,
                        0                                                                                    as countplan
                 FROM dc_base_transfer_info t1
                          LEFT JOIN dc_base_transfer_info_skus t2 on t1.dataid = t2.dataid
                 where t1.stateid < 5
                   and t1.isdeleted = 0
                   and t1.isexception = 0
                 UNION ALL
                 SELECT bailun_sku,
                        warehouse_code,
                        `count`,
                        `count` AS countplan,
                        transport_type
                 FROM dc_base_transfer_plan
                 WHERE STATUS = 1
                   AND deleted = 0
             ) t
            where t.bailun_sku is not null
            GROUP BY bailun_sku, warehouse_code) t1
				    left 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();
                }

                cn.Execute("update dc_datawarehouse set is_update_allot_onway=0 where is_update_allot_onway=1");
                var list = cn.Query<Models.mAllotOnWay>(sql).AsList();

                var s = "";
                int count = 0;
                foreach (var item in list)
                {
                    s += "update dc_datawarehouse set is_update_allot_onway=1,update_allot_onway_time='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',quantity_transfer=" + item.count + ",amount_transit=" + (item.count * item.unit_price) + " where sku='" + item.bailun_sku + "' and warehouse_code='" + item.warehouse_code + "';";
                    count++;

                    //Console.WriteLine("sku:" + item.bailun_sku + ",warehousecode：" + item.warehouse_code);
                    if ((count != 0 && count % 1000 == 0) || count == list.Count)
                    {
                        Console.WriteLine("执行更新数据库操作 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 总记录数：" + list.Count + "  本次索引为：" + count);
                        cn.Execute(s);
                        s = "";
                    }
                    
                }


                cn.Execute("update dc_datawarehouse set is_update_allot_onway=0,quantity_transfer=0,amount_transit=0 where quantity_transfer>0 and is_update_allot_onway=0");
                cn.Execute("update dc_datawarehouse set is_update_allot_onway=0 where is_update_allot_onway=1");
            }


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

        }
    }
}
