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

namespace SkuWHRedundancyService
{
    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 == 5 && now.Minute == 20) 
                {
                    SaveRedundancy(DateTime.Now.AddDays(-1));
                }
                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public bool SaveRedundancy(DateTime date)
        {

            Console.WriteLine("进去方法，"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

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

                    cn.Execute("delete from dc_mid_skuwarehouse_redundancy where `day`='" + date.ToString("yyyy-MM-dd") + "'",2*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 == pagesize && resultcount != 0))
                    {
                        var strlimit = (((page - 1) * pagesize) + "," + pagesize);
                        var liststock = cn.Query<mWarehouseRedundancy>($@"select DISTINCT t1.bailun_sku,t1.warehouse_code,t1.usable_stock,t1.sku_title_cn,t1.unit_price,t3.warehouse_name,t4.quantity,t5.quantity_out_stock,t6.oneday_total_sales,t6.threeday_total_sales,t6.sevenday_total_sales,t6.fourteenday_total_sales,t6.thirtyday_total_sales,t6.threeday_average_sales,t6.sevenday_average_sales,t6.fourteenday_average_sales,t6.thirtyday_average_sales,t5.quantity_purchase,t5.quantity_transfer,t1.product_code,t1.bailun_category_id,t1.bailun_category_name,t7.status as 'monitorstatus',t8.tortstatus 
                                                                        from (select t01.bailun_sku,t01.warehouse_code,t01.usable_stock,t02.sku_title_cn,t02.unit_price,t02.product_code,t02.bailun_category_id,t02.bailun_category_name 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 t3 on t1.warehouse_code=t3.warehouse_code
                                                                        left join dc_auto_daily_redundance t4 on t4.record_time='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.bailun_sku=t4.bailun_sku and t1.warehouse_code=t4.warehouse_code -- 仓库冗余
                                                                        left join dc_mid_transit t5 on t1.bailun_sku=t5.bailun_sku and t1.warehouse_code=t5.warehouse_code
                                                                        left join dc_daily_sales t6 on t1.bailun_sku=t6.bailun_sku and t6.record_date='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' and t1.warehouse_code=t6.warehouse_code
                                                                        left join dc_auto_config_sku_warehouse t7 on t7.bailun_sku=t1.bailun_sku and t7.warehouse_code=t1.warehouse_code
                                                                        left join dc_sku_monitor t8 on t8.sku=t1.bailun_sku and t8.warehousecode=t1.warehouse_code
                                                                        ", null, null, true, 30 * 60).AsList();

                        resultcount = liststock.Count();
                        

                        var str = @"insert dc_mid_skuwarehouse_redundancy (bailun_sku,bailun_sku_name,warehouse_code,warehouse_name,productcount,amount_total,daily_productsale_count,dailysale_amount,productsale_rate,saleamount_rate,redundancy_product_count,redundancy_product_rate,redundancy_amount,redundancy_amount_rate,yesterdaysale_amount,yesterdaysale_count,sale_3d_avg,sale_7d_avg,sale_14d_avg,sale_30d_avg,day_turnover_rate,week_turnover_rate,month_turnover_rate,stockout,day,createtime,onway_count,sku_product_code,bailun_category_id,bailun_category_name,monitorstatus,tortstatus) values ";
                        foreach (var item in liststock)
                        {
                            var onwaycount = (item.quantity_purchase ?? 0 + item.quantity_transfer ?? 0);
                            var stockcount = (item.usable_stock + onwaycount);  //可用库存+在途数

                            str += $@"('{item.bailun_sku}','{item.sku_title_cn.Replace("'","")}','{item.warehouse_code}','{item.warehouse_name}',{stockcount},{stockcount * item.unit_price},{(item.sevenday_average_sales ?? 0)},{((item.sevenday_total_sales ?? 0) * item.unit_price)},{(stockcount > 0 ? decimal.Round(((item.sevenday_average_sales ?? 0) / (decimal)stockcount),4) : 0)},{(stockcount > 0 ? decimal.Round((item.sevenday_average_sales ?? 0) / (decimal)stockcount,4) : 0)},{(item.quantity ?? 0)},{(stockcount > 0 ? decimal.Round((decimal)(item.quantity ?? 0) / (decimal)stockcount,4) : 0)},{((item.quantity ?? 0) * item.unit_price)},{(stockcount > 0 ? decimal.Round((item.quantity ?? 0) / (decimal)stockcount,4): 0)},{((item.oneday_total_sales ?? 0) * item.unit_price)},{(item.oneday_total_sales ?? 0)},{decimal.Round(((item.threeday_average_sales ?? 0) / 3),4)},{decimal.Round(((item.sevenday_average_sales ?? 0) / 7),4)},{decimal.Round(((item.fourteenday_average_sales ?? 0) / 14),4)},{decimal.Round(((item.thirtyday_average_sales ?? 0) / 30),4)},{(stockcount > 0 ? decimal.Round(item.oneday_total_sales ?? 0 / (decimal)stockcount,4) : 0)},{(stockcount > 0 ? decimal.Round((item.sevenday_average_sales ?? 0) / (decimal)stockcount * 7,4) : 0)},{(stockcount > 0 ? decimal.Round((item.thirtyday_average_sales ?? 0) / (decimal)stockcount * 30,4) : 0)},{(item.quantity_out_stock ?? 0)},'{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{onwaycount},'{item.product_code}',{item.bailun_category_id},'{item.bailun_category_name.Replace("'", "")}',{(item.monitorstatus ?? 0)},{(item.tortstatus ?? 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++;
                    }

                    //生成仓库维度的冗余报告
                    cn.Execute($@"delete from dc_mid_warehouse_redundancy where `day`='{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}';
                                INSERT INTO dc_mid_warehouse_redundancy(warehouse_code, warehouse_name, skucount, productcount, amount_total, skusales_count, skusales_rate, daily_productsale_count, productsale_rate, dailysale_amount, saleamount_rate, redundancy_sku_count, redundancy_sku_rate, redundancy_product_count, redundancy_product_rate, redundancy_amount, redundancy_amount_rate,`day`, onway_count)
                                select warehouse_code, warehouse_name, SUM(productcount) as skucount, count(bailun_sku) as productcount, sum(amount_total) as amount_total, sum(daily_productsale_count) as skusales_count, (sum(daily_productsale_count) / sum(productcount)) as skusales_rate, Count(if (daily_productsale_count > 0,1,null)) as daily_productsale_count,(count(if (daily_productsale_count > 0,1,null))/ count(bailun_sku)) as productsale_rate,sum(dailysale_amount) as dailysale_amount,(sum(dailysale_amount) / sum(amount_total)) as saleamount_rate,sum(redundancy_product_count) as redundancy_sku_count,(sum(redundancy_product_count) / SUM(productcount)) as redundancy_sku_rate,count(if (redundancy_product_count > 0,1,null)) as redundancy_product_count,(count(if (redundancy_product_count > 0,1,null))/ count(bailun_sku)) as redundancy_product_rate,sum(redundancy_amount) as redundancy_amount,(sum(redundancy_amount) / sum(amount_total)) as redundancy_amount_rate,`day`,sum(onway_count) as onway_count
                                from dc_mid_skuwarehouse_redundancy where `day`= '{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}' group by warehouse_code; ", null, null, 6 * 60);

                    Console.WriteLine("运行成功，时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    return true;

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