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;

namespace Bailun.DC.LogicWareHouse
{
    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 == 6 && now.Minute == 23)
                {
                    Save();
                }

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

            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString+ "Allow User Variables=True;"))
                {
                    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))
                    {
                        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.oneday_total_sales*t1.unit_price) amount_onedaysale,(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,t7.quantity_transfer_inbound as quantity_tunein_30days
                                from (select t01.bailun_sku,t01.warehouse_code,t01.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_mid_transit t2 on t1.warehouse_code=t2.warehouse_code and t1.bailun_sku=t2.bailun_sku 
                                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 dc_daily_transit_from t6 on t1.warehouse_code=t6.source_warehouse_code and t1.bailun_sku=t6.bailun_sku and t6.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}'
                                left join dc_daily_transit_from t7 on t1.warehouse_code=t7.warehouse_code and t1.bailun_sku=t7.bailun_sku and t7.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}'
                                ";


                        var liststock = cn.Query<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,quantity_tuneout_onway,quantity_tuneout_30days,quantity_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}),";
                        }
                        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"));

                    //半成品
                    var result = SemiStock(cn);

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


        /// <summary>
        /// 半成品
        /// </summary>
        /// <returns></returns>
        private bool SemiStock(MySqlConnection cn)
        {
            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<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"));

            return true;

        }
    }
}
