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 == 4 && now.Minute == 1)
                {
                    Save();
                }

                if (now.Minute % 20==0) //每20分钟更新一次
                {
                    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);

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

                    while (page == 1 || (resultcount == pagesize && resultcount != 0))
                    {
                        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
                                    limit {strlimit}) t1
                                left 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
                                ";
                                

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

                        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)
                        {
                            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"));

        }
    }
}
