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

namespace Bailun.DC.MonthSalePutin
{
    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.Day == 1 && now.Hour == 0 && now.Minute == 1)  //每个月的1日0点1分启动
                {
                    Console.WriteLine("开始启动 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    var start = DateTime.Parse(now.AddMonths(-1).ToShortDateString());

                    Init(start);

                    Console.WriteLine("完成销进比报表生成 "+DateTime.Now);

                    UnReasonable(start.ToString("yyyy-MM"));

                    Console.WriteLine(start.ToString("yyyy-MM")+" 完成不合理数据生成 " +DateTime.Now);

                    Analyze(start.ToString("yyyy-MM"));

                    Console.WriteLine(start.ToString("yyyy-MM")+ " 完成不合理数据分析 " +DateTime.Now);

                    Console.WriteLine("任务运行完成 " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                }

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


        public void Init(DateTime day)
        {
            var start = DateTime.Parse(day.Year + "-" + day.Month + "-01");
            var end = start.AddMonths(1);


            var sql = $@"select t0001.warehouse_code,t04.warehouse_name,t04.hq_type as warehouse_type,t0001.bailun_sku,t03.category_simple_name,t01.count as count_outbound,t02.count as count_putin,(t01.count-t02.count) as count_diff,t03.unit_price as buyprice from (select DISTINCT tt01.warehouse_code,tt01.bailun_sku from (
                            select DISTINCT t1.bailun_sku,t1.warehouse_code from dc_base_oms_pick t1 
                            where t1.shipping_time>='{start.ToString("yyyy-MM-dd")}' and t1.shipping_time<'{end.ToString("yyyy-MM-dd")}' and t1.shipping_status='TotalShipping' and t1.has_delete=0
                            union all
                            select DISTINCT t1.bailun_sku,t1.warehouse_code from dc_base_oms_sku t1 
                            where t1.platform_type='FBA' and t1.bailun_order_status!='Canceled' and t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.ToString("yyyy-MM-dd")}' and t1.has_delete=0
                            union all
                            select DISTINCT t1.bailun_sku,t1.warehouse_code from dc_base_purchase_inbound t1
                            join dc_base_purchase_details t2 on t1.purchase_id=t2.purchase_id and t1.bailun_sku=t2.bailun_sku and t2.isallot=0
                            where t1.`status`=1 and t1.update_time>='{start.ToString("yyyy-MM-dd")}' and t1.update_time<'{end.ToString("yyyy-MM-dd")}'
                            union all
                            select DISTINCT t1.bailun_sku,t1.warehouse_code from dc_base_transfer_complete t1
                            where t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.ToString("yyyy-MM-dd")}' and t1.deleted=0 and warehouse_code!='QYBLZZ'
                            ) tt01
                            ) t0001
                            left join 
                            (select tb1.warehouse_code,tb1.bailun_sku,sum(count) count from (
                            select t1.bailun_sku,t1.warehouse_code,sum(t1.quantity_shipped) count from dc_base_oms_pick t1 
                            where t1.shipping_time>='{start.ToString("yyyy-MM-dd")}' and t1.shipping_time<'{end.ToString("yyyy-MM-dd")}' and t1.shipping_status='TotalShipping' and t1.has_delete=0
                            group by t1.bailun_sku,t1.warehouse_code 
                            union all
                            select t1.bailun_sku,t1.warehouse_code,sum(t1.bailun_sku_quantity_ordered) count from dc_base_oms_sku t1 
                            where t1.platform_type='FBA' and t1.bailun_order_status!='Canceled' and t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.ToString("yyyy-MM-dd")}' and t1.has_delete=0
                            group by t1.bailun_sku,t1.warehouse_code
                            ) tb1 
                            group by tb1.warehouse_code,tb1.bailun_sku
                            ) t01 on t0001.warehouse_code=t01.warehouse_code and t0001.bailun_sku=t01.bailun_sku
                            left join 
                            (
                            select tb2.warehouse_code,tb2.bailun_sku,sum(tb2.count) count from (
                            select t1.bailun_sku,t1.warehouse_code,sum(t1.count) count from dc_base_purchase_inbound t1
                            join dc_base_purchase_details t2 on t1.purchase_id=t2.purchase_id and t1.bailun_sku=t2.bailun_sku and t2.isallot=0
                            where t1.`status`=1 and t1.update_time>='{start.ToString("yyyy-MM-dd")}' and t1.update_time<'{end.ToString("yyyy-MM-dd")}'
                            group by t1.bailun_sku,t1.warehouse_code
                            union all
                            select t1.bailun_sku,t1.warehouse_code,sum(count) count from dc_base_transfer_complete t1
                            where t1.create_time>='{start.ToString("yyyy-MM-dd")}' and t1.create_time<'{end.ToString("yyyy-MM-dd")}' and t1.deleted=0 and warehouse_code!='QYBLZZ'
                            group by t1.bailun_sku,t1.warehouse_code
                            ) tb2
                            group by tb2.bailun_sku,tb2.warehouse_code
                            ) t02 on t0001.warehouse_code=t02.warehouse_code and t0001.bailun_sku=t02.bailun_sku
                            left join dc_base_sku t03 on t0001.bailun_sku=t03.bailun_sku
                            left join dc_base_warehouse t04 on t0001.warehouse_code=t04.warehouse_code
                            ";

            var list = new List<dc_mid_month_sale_putin>();
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                list = cn.Query<dc_mid_month_sale_putin>(sql, null, null, true, 6 * 60).AsList();

                cn.Execute("delete from dc_mid_month_sale_putin where month = '" + start.ToString("yyyy-MM")+"'");
            }

            if (list.Count > 0)
            {
                
                var total = 0;
                var sql_head = "insert dc_mid_month_sale_putin (warehouse_code,warehouse_name,warehouse_type,bailun_sku,category_simple_name,count_outbound,count_putin,count_diff,buyprice,month,createtime) values ";
                var str = "";
                foreach (var item in list)
                {
                    str += $" ('{item.warehouse_code}','{item.warehouse_name}','{item.warehouse_type}','{item.bailun_sku}','{item.category_simple_name}','{item.count_outbound}','{item.count_putin}','{item.count_diff}','{item.buyprice}','{start.ToString("yyyy-MM")}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'),";

                    if ((total != 0 && total % 5000 == 0) || (total+1) == list.Count)
                    {
                        if (str.Length > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

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

                                cn.Execute(sql_head + str);

                                str = "";
                            }
                        }
                    }

                    total++;
                }
            }

        }

        //不合理数据
        public void UnReasonable(string month)
        {
            var sql = "";

            var sql_head = "insert dc_mid_month_sale_putin_report (sale_putin_id,count_usable_stock,avg_sevenday_sales,avg_fourteenday_sales,avg_thirtyday_sales,avg_sales,type,createtime,avg_weighting_day_sales,is_reasonable) values ";
            var str = "";
            var total = 0;

            //FBA 不合理数据,消耗天数>69 or 30天内没销量
            sql = $@"select tb.*,(tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)) avg_sales,((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3) avg_weighting_day_sales from (					
                    select t1.id as sale_putin_id,if(t3.usable_stock is null,0,t3.usable_stock) count_usable_stock,if(t4.sevenday_average_sales is null,0,t4.sevenday_average_sales) avg_sevenday_sales,if(t4.fourteenday_average_sales is null,0,t4.fourteenday_average_sales) avg_fourteenday_sales,if(t4.thirtyday_average_sales is null,0,thirtyday_average_sales) avg_thirtyday_sales from (select t1.* from dc_mid_month_sale_putin t1
                    join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='FBA仓'
                    where t1.`month`='{month}' and t1.warehouse_type='FBA仓'
                    ) t1
                    left join dc_daily_stock t3 on t1.warehouse_code=t3.warehouse_code and t1.bailun_sku=t3.bailun_sku and t3.record_time='{DateTime.Parse(month+"-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    left join dc_daily_sales t4 on t1.warehouse_code=t4.warehouse_code and t1.bailun_sku=t4.bailun_sku and t4.record_date='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    ) tb
                    where tb.count_usable_stock>0 and (tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)>69 or tb.avg_thirtyday_sales=0)";

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

                var list = cn.Query<dc_mid_month_sale_putin_report>(sql, null, null, true, 3 * 60).AsList();

                cn.Execute($@"delete t1 from dc_mid_month_sale_putin_report t1
                              join dc_mid_month_sale_putin t2 on t1.sale_putin_id=t2.id and t2.warehouse_type='FBA仓' and t2.`month`='{month}'");

                foreach (var item in list)
                {
                    str += $" ({item.sale_putin_id},{item.count_usable_stock},{item.avg_sevenday_sales},{item.avg_fourteenday_sales},{item.avg_thirtyday_sales},{item.avg_sales},1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{item.avg_weighting_day_sales},0),";

                    if ((total != 0 && total % 5000 == 0) || (total + 1) == list.Count)
                    {
                        if (str.Length > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

                            if (cn.State == System.Data.ConnectionState.Closed)
                            {
                                cn.Open();
                            }

                            cn.Execute(sql_head + str);

                            str = "";
                        }
                    }

                    total++;
                }

            }


            //第三方仓不合理数据,消耗天数>45 or 30天内没销量
            total = 0;
            str = "";
            sql = $@"select tb.*,(tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)) avg_sales,((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3) avg_weighting_day_sales from (					
                    select t1.id as sale_putin_id,if(t3.usable_stock is null,0,t3.usable_stock) count_usable_stock,if(t4.sevenday_average_sales is null,0,t4.sevenday_average_sales) avg_sevenday_sales,if(t4.fourteenday_average_sales is null,0,t4.fourteenday_average_sales) avg_fourteenday_sales,if(t4.thirtyday_average_sales is null,0,thirtyday_average_sales) avg_thirtyday_sales from (select t1.* from dc_mid_month_sale_putin t1
                    join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='第三方仓库'
                    where t1.`month`='{month}' and t1.warehouse_type='第三方仓库'
                    ) t1
                    left join dc_daily_stock t3 on t1.warehouse_code=t3.warehouse_code and t1.bailun_sku=t3.bailun_sku and t3.record_time='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    left join dc_daily_sales t4 on t1.warehouse_code=t4.warehouse_code and t1.bailun_sku=t4.bailun_sku and t4.record_date='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    ) tb
                    where  tb.count_usable_stock>0 and (tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)>45 or tb.avg_thirtyday_sales=0) ";

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

                var list = cn.Query<dc_mid_month_sale_putin_report>(sql, null, null, true, 3 * 60).AsList();

                cn.Execute($@"delete t1 from dc_mid_month_sale_putin_report t1
                              join dc_mid_month_sale_putin t2 on t1.sale_putin_id=t2.id and t2.warehouse_type='第三方仓库' and t2.`month`='{month}'");

                foreach (var item in list)
                {
                    str += $" ({item.sale_putin_id},{item.count_usable_stock},{item.avg_sevenday_sales},{item.avg_fourteenday_sales},{item.avg_thirtyday_sales},{item.avg_sales},1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{item.avg_weighting_day_sales},0),";

                    if ((total != 0 && total % 5000 == 0) || (total + 1) == list.Count)
                    {
                        if (str.Length > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

                            if (cn.State == System.Data.ConnectionState.Closed)
                            {
                                cn.Open();
                            }

                            cn.Execute(sql_head + str);

                            str = "";
                        }
                    }

                    total++;
                }

            }


            //国内仓不合理数据,消耗天数>14 or 30天内没销量
            total = 0;
            str = "";
            sql = $@"select tb.*,(tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)) avg_sales,((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3) avg_weighting_day_sales from (					
                    select t1.id as sale_putin_id,if(t3.usable_stock is null,0,t3.usable_stock) count_usable_stock,if(t4.sevenday_average_sales is null,0,t4.sevenday_average_sales) avg_sevenday_sales,if(t4.fourteenday_average_sales is null,0,t4.fourteenday_average_sales) avg_fourteenday_sales,if(t4.thirtyday_average_sales is null,0,thirtyday_average_sales) avg_thirtyday_sales from (select t1.* from dc_mid_month_sale_putin t1
                    join dc_base_warehouse t2 on t1.warehouse_code=t2.warehouse_code and t2.hq_type='国内仓'
                    where t1.`month`='{month}' and t1.warehouse_type='国内仓'
                    ) t1
                    left join dc_daily_stock t3 on t1.warehouse_code=t3.warehouse_code and t1.bailun_sku=t3.bailun_sku and t3.record_time='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    left join dc_daily_sales t4 on t1.warehouse_code=t4.warehouse_code and t1.bailun_sku=t4.bailun_sku and t4.record_date='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    ) tb
                    where  tb.count_usable_stock>0 and (tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)>14 or tb.avg_thirtyday_sales=0)";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<dc_mid_month_sale_putin_report>(sql, null, null, true, 10 * 60).AsList();

                cn.Execute($@"delete t1 from dc_mid_month_sale_putin_report t1
                              join dc_mid_month_sale_putin t2 on t1.sale_putin_id=t2.id and t2.warehouse_type='国内仓' and t2.`month`='{month}'");


                foreach (var item in list)
                {
                    str += $" ({item.sale_putin_id},{item.count_usable_stock},{item.avg_sevenday_sales},{item.avg_fourteenday_sales},{item.avg_thirtyday_sales},{item.avg_sales},1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{item.avg_weighting_day_sales},0),";

                    if ((total != 0 && total % 5000 == 0) || (total + 1) == list.Count)
                    {
                        if (str.Length > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

                            if (cn.State == System.Data.ConnectionState.Closed)
                            {
                                cn.Open();
                            }

                            cn.Execute(sql_head + str);

                            str = "";
                        }
                    }

                    total++;
                }

            }

            //Add by Allan at 20200225,For 添加合理sku的数据
            total = 0;
            str = "";
            sql = $@"select tb.*,(tb.count_usable_stock/((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3)) avg_sales,((tb.avg_sevenday_sales+tb.avg_fourteenday_sales+tb.avg_thirtyday_sales)/3) avg_weighting_day_sales from (
                    select t1.id as sale_putin_id,if(t3.usable_stock is null,0,t3.usable_stock) count_usable_stock,if(t4.sevenday_average_sales is null,0,t4.sevenday_average_sales) avg_sevenday_sales,if(t4.fourteenday_average_sales is null,0,t4.fourteenday_average_sales) avg_fourteenday_sales,if(t4.thirtyday_average_sales is null,0,thirtyday_average_sales) avg_thirtyday_sales from (select t1.* from dc_mid_month_sale_putin t1
                    left join dc_mid_month_sale_putin_report t2 on t1.id=t2.sale_putin_id
                    where t1.month='{month}' and t2.id is null) t1
                    left join dc_daily_stock t3 on t1.warehouse_code=t3.warehouse_code and t1.bailun_sku=t3.bailun_sku and t3.record_time='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    left join dc_daily_sales t4 on t1.warehouse_code=t4.warehouse_code and t1.bailun_sku=t4.bailun_sku and t4.record_date='{DateTime.Parse(month + "-1").AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd")}'
                    ) tb";


            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<dc_mid_month_sale_putin_report>(sql, null, null, true, 10 * 60).AsList();

                cn.Execute($@"delete t1 from dc_mid_month_sale_putin_report t1
                              join dc_mid_month_sale_putin t2 on t1.sale_putin_id=t2.id and t2.`month`='{month}'
                              where t1.is_reasonable=1
                              ");


                foreach (var item in list)
                {
                    str += $" ({item.sale_putin_id},{item.count_usable_stock},{item.avg_sevenday_sales},{item.avg_fourteenday_sales},{item.avg_thirtyday_sales},{item.avg_sales},1,'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{item.avg_weighting_day_sales},1),";

                    if ((total != 0 && total % 5000 == 0) || (total + 1) == list.Count)
                    {
                        if (str.Length > 0)
                        {
                            str = str.Substring(0, str.Length - 1);

                            if (cn.State == System.Data.ConnectionState.Closed)
                            {
                                cn.Open();
                            }

                            cn.Execute(sql_head + str);

                            str = "";
                        }
                    }

                    total++;
                }

            }


        }

        /// <summary>
        /// 分析不合理原因，是否清货，是否新品，是否aims下单
        /// </summary>
        /// <param name="month">月份</param>
        public void Analyze(string month)
        {
            var sql = @"";

            //是否清货
            sql = $@"select t1.id from dc_mid_month_sale_putin_report t1
                    join dc_mid_month_sale_putin t2 on t1.sale_putin_id = t2.id
                    join dc_base_sku t3 on t2.bailun_sku=t3.bailun_sku and t3.status=1
                    where t2.`month`='{month}' -- and t3.create_time is null";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<int>(sql, null, null, true, 10 * 60).AsList();
                if (list.Count > 0)
                {
                    cn.Execute("update dc_mid_month_sale_putin_report set is_clear=1 where id in (" + string.Join(",", list) + ")");
                }
                
            }


            //是否新品
            sql = $@"select t1.id from dc_mid_month_sale_putin_report t1
                    join dc_mid_month_sale_putin t2 on t1.sale_putin_id = t2.id
                    left join dc_base_sku t3 on t2.bailun_sku=t3.bailun_sku and t3.create_time>'{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd HH:mm:ss")}'
                    where t2.month='{month}' and t3.create_time is not null";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<int>(sql, null, null, true, 10 * 60).AsList();

                cn.Execute("update dc_mid_month_sale_putin_report set is_new=1 where id in (" + string.Join(",", list) + ")");
            }

            //是否aims下单  AIMS（机器下单）（1、FBA仓，托管仓，15天前2个月内AIMS下单总数；2、国内仓3天前，一个月内AIMS下单总数）   BI创建（手工单）（1、FBA仓，托管仓，15天前2个月内BI手工下单总数；2、国内仓3天前，一个月内BI手工下单总数）
            //优先判断 aims下单

            //国内仓
            sql = $@"select DISTINCT t1.id from dc_mid_month_sale_putin_report t1
                    join dc_mid_month_sale_putin t2 on t1.sale_putin_id = t2.id and t2.warehouse_type='国内仓'
                    join dc_base_purchase_details t3 on t2.bailun_sku=t3.bailun_sku and t3.warehouse_from_code=t2.warehouse_code and t3.isallot=0 and t3.isaims=1 and t3.status!=-1 and t3.create_time>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and t3.create_time<'{DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd HH:mm:ss")}'
                    where t2.month='{month}'
                    union all
                    select DISTINCT t1.id from dc_mid_month_sale_putin_report t1
                    join dc_mid_month_sale_putin t2 on t1.sale_putin_id = t2.id and t2.warehouse_type='国内仓'
                    join dc_base_purchase_details t3 on t2.bailun_sku=t3.bailun_sku and t3.warehouse_into_code=t2.warehouse_code and t3.isallot=1 and t3.isaims=1 and t3.status!=-1 and t3.create_time>='{DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd HH:mm:ss")}' and t3.create_time<'{DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd HH:mm:ss")}'
                    where t2.month='{month}'";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<int>(sql, null, null, true, 10 * 60).AsList();

                cn.Execute("update dc_mid_month_sale_putin_report set is_aimsorder=1 where id in (" + string.Join(",", list) + ")");
            }

            //非国内仓
            sql = $@"select DISTINCT t1.id from dc_mid_month_sale_putin_report t1
                    join dc_mid_month_sale_putin t2 on t1.sale_putin_id = t2.id and t2.warehouse_type!='国内仓'
                    join dc_base_purchase_details t3 on t2.bailun_sku=t3.bailun_sku and t3.warehouse_from_code=t2.warehouse_code and t3.isallot=0 and t3.isaims=1 and t3.status!=-1 and t3.create_time>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd HH:mm:ss")}' and t3.create_time<'{DateTime.Now.AddDays(-15).ToString("yyyy-MM-dd HH:mm:ss")}'
                    where t2.month='{month}'
                    union all
                    select DISTINCT t1.id from dc_mid_month_sale_putin_report t1
                    join dc_mid_month_sale_putin t2 on t1.sale_putin_id = t2.id and t2.warehouse_type!='国内仓'
                    join dc_base_purchase_details t3 on t2.bailun_sku=t3.bailun_sku and t3.warehouse_into_code=t2.warehouse_code and t3.isallot=1 and t3.isaims=1 and t3.status!=-1 and t3.create_time>='{DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd HH:mm:ss")}' and t3.create_time<'{DateTime.Now.AddDays(-15).ToString("yyyy-MM-dd HH:mm:ss")}'
                    where t2.month='{month}'";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString + "Allow User Variables=True;"))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<int>(sql, null, null, true, 10 * 60).AsList();

                cn.Execute("update dc_mid_month_sale_putin_report set is_aimsorder=1 where id in (" + string.Join(",", list) + ")");
            }

        }

    }
}
