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();
                }

            }
            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("delete from 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.category_name,t1.sku_title_cn as skuname,(case when t4.status=1 then 0 else 1 end) monitorstatus,t5.clearancestatus,t5.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
                                from (select t01.bailun_sku,t01.warehouse_code,t01.usable_stock,t02.unit_price,t02.create_time,t02.category_simple_id as category_id,t02.category_simple_name as category_name,t02.sku_title_cn 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";


                        var liststock = cn.Query<Models.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) values ";
                        foreach (var item in liststock)
                        {
                            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.Replace("'", "") : "")}','{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")}'),";
                        }
                        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);
            }
            
        }

    }
}
