using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using AutoTurnOver.Models;
using Dapper;
using System.Linq;
using AutoTurnOver.Models.Base;
using AutoTurnOver.DB.Base;
using AutoTurnOver.Models.stock;
using AutoTurnOver.Utility;
using AutoTurnOver.Models.Report;
using System.Threading;

namespace AutoTurnOver.DB
{
    public class dc_auto_turnover : connectionHelper
    {
        public dc_auto_turnover()
        {
            Dapper.SimpleCRUD.SetDialect(SimpleCRUD.Dialect.MySQL);
        }

        /// <summary>
        /// 获取自动周转分页列表
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <param name="total"></param>
        /// <param name="order">排序类型</param>
        /// <param name="sort">排序字段</param>
        /// <returns></returns>
        public static List<Models.dc_auto_turnover_list_dto> List(Condition_AutoTurnOver m, int offset, int limit, ref int total, string order = "", string sort = "", bool isSum = false)
        {
            try
            {
                var sql = "";
                if (isSum)
                {
                    sql = @"select 
sum(dat.quantity_inventory) as 'quantity_inventory',
sum(dat.quantity_out_stock) as 'quantity_out_stock',
sum(dat.quantity_purchase) as 'quantity_purchase',
sum(dat.quantity_transfer) as 'quantity_transfer',
-- sum(t2.oneday_sales) as 'oneday_sales',
-- sum(t2.forecast_oneday_sales) as 'forecast_oneday_sales2',
sum(dat.quantity_final_advise) as 'quantity_final_advise',
sum(dat.quantity_promotion) as 'quantity_promotion',
sum(dat.quantity_safe_inventory) as 'quantity_safe_inventory'
from dc_auto_turnover as dat
left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code
left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code
left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku
where 1=1 ";
                }
                else
                {
                    sql = @"select (t3.warehouse_name)  as 'db_warehouse_name', dat.*,t2.oneday_sales,dat.forecast_oneday_sales as 'forecast_oneday_sales2',
t4.bailun_category_name,
t4.product_type_desc,
t3.hq_type as 'warehouse_type',
( case when t5.`status`=0 or t5.`status` is null then 0 else 1 end ) as 'monitor_status',
( case when t12.`status`=0 or t12.`status` is null then 0 else 1 end ) as 'returngoodspush_state',
t4.suppliers_link,
(t4.supplier_delivery - t6.virtual_delivery_days) as 'delivery_days_error',t4.weight,t4.unit_price,t7.usable_stock,t7.shipped_stock,
t4.develop_time,(case when t8.id is null then '未侵权' else  '侵权' end) as 'has_tort',
( case when t5.`status`=0 or t5.`status` is null then null else t5.gmt_create end ) as 'stop_monitor_create',
t4.category_simple_name,
t4.brand as 'brand',
t7.first_inbound_date,

t3.area_name,
-- ifnull(t15.count,0) as 'not_trans_count',

t13.quantity_transfer_order as 'quantity_transfer_order',
t13.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t13.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage',


t13.quantity_land_purchase as 'quantity_land_purchase',
t13.quantity_ocean_purchase as 'quantity_ocean_purchase',
t13.quantity_air_purchase as 'quantity_air_purchase',
t13.quantity_railway_purchase as 'quantity_railway_purchase',
t13.quantity_land_transfer as 'quantity_land_transfer',
t13.quantity_ocean_transfer as 'quantity_ocean_transfer',
t13.quantity_air_transfer as 'quantity_air_transfer',
t13.quantity_railway_transfer as 'quantity_railway_transfer',
dat.dc_auto_sales_forecast_title,
-- t14.title as 'dc_auto_sales_forecast_title',
t4.`status` as 'sku_status'-- ,
-- t11.label as 'sku_label'
-- ,t10.`name` as 'group_name'
from dc_auto_turnover as dat
left join dc_auto_sales as t2 on t2.bailun_sku=dat.bailun_sku and dat.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code
left join dc_base_sku as t4 on dat.bailun_sku = t4.bailun_sku
left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code
left join dc_average_purchase as t6 on dat.bailun_sku = t6.bailun_sku and dat.warehouse_code  =t6.warehouse_code and t4.suppliers_id = t6.supplier_id
left join dc_base_stock as t7 on dat.bailun_sku = t7.bailun_sku and dat.warehouse_code = t7.warehouse_code
left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku
-- left join dc_auto_stock_up_range as t9 on dat.bailun_sku = t9.bailun_sku and dat.warehouse_code = t9.warehouse_code
-- left join dc_auto_jit_tag_group as t10 on t9.group_id = t10.id
-- left join dc_auto_config_sku_label as t11 on dat.bailun_sku = t11.bailun_sku
left join dc_return_goods_not_push as t12 on dat.bailun_sku = t12.bailun_sku and dat.warehouse_code = t12.warehouse_code
left join dc_mid_transit as t13 on dat.bailun_sku = t13.bailun_sku and dat.warehouse_code = t13.warehouse_code
-- left join dc_auto_sales_forecast_config as t14 on dat.dc_auto_sales_forecast_config_id = t14.id 
-- left join not_trans_view as t15 on dat.bailun_sku = t15.bailun_sku and dat.warehouse_code = t15.warehouse_code
where 1=1 ";
                }

                string sqlCount = @"select count(1)
from dc_auto_turnover as dat

";

                DynamicParameters parameters = new DynamicParameters();


                if (string.IsNullOrWhiteSpace(m.sku_label))
                {
                    sqlCount += " left join dc_auto_config_sku_label as t11 on dat.bailun_sku = t11.bailun_sku ";
                }
                if (m.warehousearea > 0)
                {
                    sqlCount += " left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code ";
                }
                if (m.categoryModels != null || (!string.IsNullOrWhiteSpace(m.product_type)))
                {
                    sqlCount += " left join dc_base_sku as t4 on dat.bailun_sku = t4.bailun_sku ";
                }
                if (m.monitor_status != null)
                {
                    sqlCount += " left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code ";
                }
                if (m.returngoodspush_state != null)
                {
                    sqlCount += " left join dc_return_goods_not_push as t12 on dat.bailun_sku = t12.bailun_sku and dat.warehouse_code = t12.warehouse_code ";
                }
                if (m.has_tort != null)
                {
                    sqlCount += " left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku ";
                }
                if (m.oneday_sales_min > 0 || m.oneday_sales_max > 0)
                {
                    sqlCount += " left join dc_auto_sales as t2 on t2.bailun_sku=dat.bailun_sku and dat.warehouse_code = t2.warehouse_code ";
                }
                if (m.not_trans_count_max > 0 || m.not_trans_count_min > 0)
                {
                    sqlCount += " left join not_trans_view as t15 on dat.bailun_sku = t15.bailun_sku and dat.warehouse_code = t15.warehouse_code ";
                }
                sqlCount += " where 1=1 ";



                if (m.has_tort != null)
                {
                    if (m.has_tort == 0)
                    {
                        sql += " and t8.id is null ";
                        sqlCount += " and t8.id is null ";
                    }
                    else if (m.has_tort == 1)
                    {
                        sql += " and t8.id is not null ";
                        sqlCount += " and t8.id is not null ";
                    }
                }

                if (m.categoryModels != null)
                {
                    sql += " and t4.category_simple_id in ( " + string.Join(",", m.categoryModels.Select(s => s.id).Distinct()) + ")";
                    sqlCount += " and t4.category_simple_id in ( " + string.Join(",", m.categoryModels.Select(s => s.id).Distinct()) + ")";
                }
                if (!string.IsNullOrWhiteSpace(m.sku_label))
                {
                    sql += " and t11.label = " + $"'{m.sku_label}'";
                    sqlCount += " and t11.label = " + $"'{m.sku_label}'";
                }
                if (!string.IsNullOrWhiteSpace(m.buyer_name))
                {
                    sql += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                    sqlCount += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                }
                if (!string.IsNullOrWhiteSpace(m.product_type))
                {
                    sql += " and t4.product_type = " + $"'{m.product_type}'";
                    sqlCount += " and t4.product_type = " + $"'{m.product_type}'";
                }
                if (m.monitor_status == 1)
                {
                    sql += " and t5.`status`=1 ";
                    sqlCount += " and t5.`status`=1 ";
                }
                if (m.monitor_status == 0)
                {
                    sql += " and ( t5.`status`=0 or t5.`status` is null ) ";
                    sqlCount += " and ( t5.`status`=0 or t5.`status` is null ) ";
                }

                if (m.returngoodspush_state == 1)
                {
                    sql += " and t12.`status`=1 ";
                    sqlCount += " and t12.`status`=1 ";
                }
                if (m.returngoodspush_state == 0)
                {
                    sql += " and ( t12.`status`=0 or t12.`status` is null ) ";
                    sqlCount += " and ( t12.`status`=0 or t12.`status` is null ) ";
                }

                if (m.hasAadvise == true)
                {
                    sql += " and dat.quantity_final_advise>0 ";
                    sqlCount += " and dat.quantity_final_advise>0 ";
                }
                if (m.hasAadvise == false)
                {
                    sql += " and dat.quantity_final_advise<=0 ";
                    sqlCount += " and dat.quantity_final_advise<=0 ";
                }
                if (!string.IsNullOrWhiteSpace(m.key_words) && m.searchType > 0)
                {
                    switch (m.searchType.Value)
                    {
                        case 1:
                            sql += " and dat.bailun_sku in @bailun_skus ";
                            sqlCount += " and dat.bailun_sku in @bailun_skus ";
                            parameters.Add("bailun_skus", m.key_words.Split(',').ToArray());
                            break;
                        case 2:
                            sql += " and dat.bailun_sku like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.bailun_sku =" + $"'%{m.key_words}%'";
                            break;
                        case 3:
                            sql += " and dat.product_inner_code in @product_inner_code ";
                            sqlCount += " and dat.product_inner_code in @product_inner_code ";
                            parameters.Add("product_inner_code", m.key_words.Split(',').ToList());
                            break;
                        case 4:
                            sql += " and dat.product_inner_code like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.product_inner_code like" + $"'%{m.key_words}%'";
                            break;
                        case 5:
                            sql += " and dat.product_code =" + $"'{m.key_words}'";
                            sqlCount += " and dat.product_code =" + $"'{m.key_words}'";
                            break;
                        case 6:
                            sql += " and dat.product_code like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.product_code like" + $"'%{m.key_words}%'";
                            break;
                        case 7:
                            sql += " and dat.sku_title like " + $"'%{m.key_words}%'";
                            sqlCount += " and dat.sku_title like" + $"'%{m.key_words}%'";
                            break;
                        default: break;
                    }

                }

                if (m.hasDefectConfig == true)
                {
                    sql += " and ( dat.abroad_inbound_delivery <=0  or dat.transfer_bale_delivery <=0 or dat.transfer_delivery <=0   )  ";
                    sqlCount += " and ( dat.abroad_inbound_delivery <=0  or dat.transfer_bale_delivery <=0 or dat.transfer_delivery <=0   )  ";
                }
                if (m.hasDefectConfig == false)
                {
                    sql += " and ( dat.abroad_inbound_delivery >0  and dat.transfer_bale_delivery >0 and dat.transfer_delivery >0   )  ";
                    sqlCount += " and ( dat.abroad_inbound_delivery >0  and dat.transfer_bale_delivery >0 and dat.transfer_delivery >0   )  ";
                }
                if (!string.IsNullOrEmpty(m.warehouse_code))
                {
                    sql += " and dat.warehouse_code='" + m.warehouse_code + "'";
                    sqlCount += " and dat.warehouse_code='" + m.warehouse_code + "'";
                }
                else
                {
                    if (!string.IsNullOrWhiteSpace(m.warehousetype))
                    {
                        sql += " and dat.hq_type=" + $"'{m.warehousetype}'";
                        sqlCount += " and dat.hq_type=" + $"'{m.warehousetype}'";
                    }
                    if (m.warehousearea > 0)
                    {
                        sql += " and t3.area_id=" + m.warehousearea;
                        sqlCount += " and t3.area_id=" + m.warehousearea;
                    }
                }

                if (m.out_of_stock.HasValue)
                {
                    sql += " and dat.status=1";
                    sqlCount += " and dat.status=1";
                }
                if (!string.IsNullOrWhiteSpace(m.supplier_name))
                {
                    sql += " and dat.suppliers_name = '" + m.supplier_name + "'";
                    sqlCount += " and dat.suppliers_name = '" + m.supplier_name + "'";
                }

                if (m.quantity_inventory_min != null)
                {
                    sql += " and dat.quantity_inventory>=@quantity_inventory_min ";
                    sqlCount += " and dat.quantity_inventory>=@quantity_inventory_min ";
                    parameters.Add("quantity_inventory_min", m.quantity_inventory_min);
                }
                if (m.quantity_inventory_max != null)
                {
                    sql += " and dat.quantity_inventory<=@quantity_inventory_max ";
                    sqlCount += " and dat.quantity_inventory<=@quantity_inventory_max ";
                    parameters.Add("quantity_inventory_max", m.quantity_inventory_max);
                }
                if (m.not_trans_count_min != null)
                {
                    sql += " and t15.count>=@not_trans_count_min ";
                    sqlCount += " and t15.count>=@not_trans_count_min ";
                    parameters.Add("not_trans_count_min", m.not_trans_count_min);
                }

                if (m.quantity_out_stock_max != null)
                {
                    sql += " and dat.quantity_out_stock>=@quantity_out_stock_max ";
                    sqlCount += " and dat.quantity_out_stock>=@quantity_out_stock_max ";
                    parameters.Add("quantity_out_stock_max", m.quantity_out_stock_max);
                }
                if (m.oneday_sales_min != null)
                {
                    sql += " and t2.oneday_sales>=@oneday_sales_min ";
                    sqlCount += " and t2.oneday_sales>=@oneday_sales_min ";
                    parameters.Add("oneday_sales_min", m.oneday_sales_min);
                }
                if (m.oneday_sales_max != null)
                {
                    sql += " and t2.oneday_sales<=@oneday_sales_max ";
                    sqlCount += " and t2.oneday_sales<=@oneday_sales_max ";
                    parameters.Add("oneday_sales_max", m.oneday_sales_max);
                }
                if (m.avg_type > 0)
                {
                    var field_str = "";
                    switch (m.avg_type)
                    {
                        case 1: field_str = "dat.history_sevenday_sales"; break;
                        case 2: field_str = "dat.history_fourteenday_sales"; break;
                        case 3: field_str = "dat.history_thirtyday_sales"; break;
                        case 4: field_str = "dat.forecast_sevenday_sales"; break;
                        case 5: field_str = "dat.forecast_fourteenday_sales"; break;
                        case 7: field_str = "dat.history_sevenday_sales_ebay"; break;
                        case 8: field_str = "dat.history_fourteenday_sales_ebay"; break;
                        case 9: field_str = "dat.history_thirtyday_sales_ebay"; break;
                        case 10: field_str = "dat.history_sevenday_sales_aliexpress"; break;
                        case 11: field_str = "dat.history_fourteenday_sales_aliexpress"; break;
                        case 12: field_str = "dat.history_thirtyday_sales_aliexpress"; break;
                        case 13: field_str = "dat.history_sevenday_sales_amazon"; break;
                        case 14: field_str = "dat.history_fourteenday_sales_amazon"; break;
                        case 15: field_str = "dat.history_thirtyday_sales_amazon"; break;

                        default: break;
                    }
                    if (!string.IsNullOrWhiteSpace(field_str))
                    {
                        if (m.avg_sales_min != null)
                        {
                            sql += $" and {field_str}>=@avg_sales_min ";
                            sqlCount += $" and {field_str}>=@avg_sales_min ";
                            parameters.Add("avg_sales_min", m.avg_sales_min);
                        }
                        if (m.avg_sales_max != null)
                        {
                            sql += $" and {field_str}<=@avg_sales_max ";
                            sqlCount += $" and {field_str}<=@avg_sales_max ";
                            parameters.Add("avg_sales_max", m.avg_sales_max);
                        }
                    }

                }

                if (isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.QuerySingleOrDefault<int>(sqlCount, parameters, commandTimeout: 0);
                    //Console.WriteLine(sqlCount);
                    //设置默认排序字段
                    if (string.IsNullOrWhiteSpace(sort)) sort = "dat.id";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        sql += " order by " + sort;

                        if (!string.IsNullOrEmpty(order))
                        {
                            sql += " " + order;
                        }
                        else
                        {
                            sql += " asc";
                        }
                    }

                    sql += " limit " + offset + "," + limit;
                }




                var obj = _connection.Query<Models.dc_auto_turnover_list_dto>(sql, parameters, commandTimeout: 0).AsList();
                //Console.WriteLine(sql);
                foreach (var item in obj)
                {
                    item.history_sevenday_sales = item.history_sevenday_sales.Round1();
                    item.history_fourteenday_sales = item.history_fourteenday_sales.Round1();
                    item.history_thirtyday_sales = item.history_thirtyday_sales.Round1();
                    item.history_sevenday_sales_ebay = item.history_sevenday_sales_ebay.Round1();
                    item.history_fourteenday_sales_ebay = item.history_fourteenday_sales_ebay.Round1();
                    item.history_thirtyday_sales_ebay = item.history_thirtyday_sales_ebay.Round1();
                    item.history_sevenday_sales_aliexpress = item.history_sevenday_sales_aliexpress.Round1();
                    item.history_fourteenday_sales_aliexpress = item.history_fourteenday_sales_aliexpress.Round1();
                    item.history_thirtyday_sales_aliexpress = item.history_thirtyday_sales_aliexpress.Round1();
                    item.history_sevenday_sales_amazon = item.history_sevenday_sales_amazon.Round1();
                    item.history_fourteenday_sales_amazon = item.history_fourteenday_sales_amazon.Round1();
                    item.history_thirtyday_sales_amazon = item.history_thirtyday_sales_amazon.Round1();
                }
                return obj;
            }
            catch (Exception ex)
            {

                throw;
            }

        }

        /// <summary>
        /// 查询历史节点趋势图
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static charts_line_dto GetHistoryDataNodes(string sku, string warehousecode)
        {
            var btime = DateTime.Now.AddDays(-30).ToDayHome();
            var base_datas = _connection.Query<dc_base_forecast_history>(" select * from dc_base_forecast_history where `date`>=@btime and bailun_sku_warehouse_code=@bailun_sku_warehouse_code ", new
            {
                bailun_sku_warehouse_code = $"{sku}{warehousecode}",
                btime = btime
            }).ToList();

            List<string> date_list = new List<string>();
            List<charts_line_dto.data_dto> datas = new List<charts_line_dto.data_dto>();
            var names = new List<string> { "供应链长度", "供应商交期", "质检入库天数", "调拨头程天数", "调拨打包天数", "海外仓入库天数" };
            foreach (var itemName in names)
            {
                datas.Add(new charts_line_dto.data_dto
                {
                    name = itemName,
                    type = "line",
                    data = new List<decimal> { }
                });
            }

            var nowDate = DateTime.Now;
            var this_date = btime;
            while (this_date.Date.ToDayHome() <= nowDate)
            {
                date_list.Add(this_date.ToString("yyyy-MM-dd"));
                var this_date_item = (base_datas.FirstOrDefault(s => s.date_str == this_date.ToString("yyyy-MM-dd")) ?? new dc_base_forecast_history());
                datas.FirstOrDefault(s => s.name == "供应链长度").data.Add(this_date_item.turnover_days);
                datas.FirstOrDefault(s => s.name == "供应商交期").data.Add(this_date_item.supplier_delivery);
                datas.FirstOrDefault(s => s.name == "质检入库天数").data.Add(this_date_item.inspection_delivery);
                datas.FirstOrDefault(s => s.name == "调拨头程天数").data.Add(this_date_item.transfer_delivery);
                datas.FirstOrDefault(s => s.name == "调拨打包天数").data.Add(this_date_item.transfer_bale_delivery);
                datas.FirstOrDefault(s => s.name == "海外仓入库天数").data.Add(this_date_item.abroad_inbound_delivery);
                this_date = this_date.AddDays(1);
            }

            return new charts_line_dto
            {
                y = datas,
                x = date_list
            };

        }

        /// <summary>
        /// 刷新历史数据
        /// </summary>
        public static void ResetHistory()
        {
            var now = DateTime.Now;
            var init_date = DateTime.Parse(now.ToString("yyyy-MM-dd 05:00:00"));
            if(now> init_date)
            {
                var init_now = DateTime.Now.ToDayHome();
                var end_date = DateTime.Parse(now.ToString("yyyy-MM-dd 23:00:00"));
                var total = _connection.QueryFirstOrDefault<int>(" select count(1) from dc_auto_turnover where gmt_modified<@init_now ",new { init_now });
                var rows = (int)Math.Floor(total / ((end_date- init_now).TotalMinutes  / 11 ));
                var datas = _connection.Query<ResetHistoryDto>(" select bailun_sku,warehouse_code from dc_auto_turnover where gmt_modified<@init_now limit @rows ", new { init_now,rows }).ToList();
                if (datas != null)
                {
                    foreach (var item in datas)
                    {
                        _connection.Insert(new dc_base_queue
                        {
                            submit_date = DateTime.Now,
                            error_message = "",
                            consume_date = null,
                            error_stack_trace = "",
                            message = new { bailun_sku = item.bailun_sku, warehouse_code = item.warehouse_code }.ToJson(),
                            type = "刷新周转表"
                        });
                    }
                }
            }
            
        }
        public class ResetHistoryDto
        {
            public string bailun_sku { get; set; }
            public string warehouse_code { get; set; }
        }


        public static IEnumerable<string> GetLabelList()
        {
            return _connection.Query<string>(" select DISTINCT label from dc_auto_config_sku_label  ");
        }

        #region 特殊销售设置

        /// <summary>
        /// 获取特殊销售设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_auto_config_promotion> ListConfigPromotion(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_promotion>();

            try
            {
                var sql = "select dacp.id,dacp.bailun_sku,dacp.warehouse_code,dbw.warehouse_name,dacp.promotion_time,dacp.count,dacp.bl_auditor,dacp.remark,dacp.status from dc_auto_config_promotion dacp " +
                            "left join dc_base_warehouse as dbw on dacp.warehouse_code=dbw.warehouse_code where 1 =1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and dbw.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and dbw.area_id=" + m.warehousearea;
                }
                if (m.IsEffective == true)
                {
                    sql += $" and dacp.promotion_time>= '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.sku + "%'";
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " and dacp.warehouse_code='" + m.warehousecode + "'";
                }

                if (m.status.HasValue)
                {
                    sql += " dacp.and status=" + m.status.Value;
                }

                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1");

                var obj = _connection.Query<dc_auto_config_promotion>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 查询未来指定时间范围内的特殊销量数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static IEnumerable<dc_auto_config_promotion> ListConfigPromotion(string sku, string warehousecode, DateTime btime, DateTime etime)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehousecode);
            parameters.Add("b_promotion_time", btime.ToString("yyyy-MM-dd 00:00:00"));
            parameters.Add("e_promotion_time", etime.ToString("yyyy-MM-dd 23:59:59"));
            return _connection.Query<Models.dc_auto_config_promotion>(@"select * from dc_auto_config_promotion where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and promotion_time>=@b_promotion_time and promotion_time<=@e_promotion_time ", parameters);
        }

        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveSaleSetting(Models.dc_auto_config_promotion m, string username)
        {
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_config_promotion>("select * from dc_auto_config_promotion where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_config_promotion
                {
                    gmt_create = DateTime.Now,
                };
            }
            else
            {
                if (obj.status != 0)
                {
                    return "该销售设置不能修改，目前状态为：" + Enum.GetName(typeof(EnumSaleSettingStatus), obj.status);
                }
            }

            obj.id = m.id;
            obj.bailun_sku = m.bailun_sku;
            obj.bl_operator = username ?? "";
            obj.bl_auditor = "";
            obj.gmt_modified = DateTime.Now;
            obj.promotion_time = m.promotion_time;
            obj.remark = m.remark;
            obj.status = m.status;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.count = m.count;

            try
            {
                if (obj.id > 0)
                {
                    var result = _connection.Update<Models.dc_auto_config_promotion>(obj);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_promotion>(obj);

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 批量追加
        /// </summary>
        /// <param name="datas"></param>
        public static void AddsSaleSetting(List<dc_auto_config_promotion_input> datas)
        {
            var _con = MyMySqlConnection._connection;
            _con.Open();
            IDbTransaction transaction = _con.BeginTransaction();
            try
            {
                foreach (var item in datas)
                {
                    var obj = new dc_auto_config_promotion();
                    obj.bailun_sku = item.sku;
                    obj.gmt_modified = DateTime.Now;
                    obj.promotion_time = item.time;
                    obj.bl_auditor = "";
                    obj.bl_operator = "";
                    obj.remark = item.note;
                    obj.status = 0;
                    obj.warehouse_code = item.warehousecode;
                    obj.warehouse_name = item.warehouse_name;
                    obj.count = item.count;

                    _con.Insert(obj, transaction);
                }

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                _con.Close();
                throw;
            }
        }

        /// <summary>
        /// 获取特殊销售设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_auto_config_promotion GetSaleSettingById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_promotion>("select * from dc_auto_config_promotion where id=" + id);
        }

        #endregion

        #region 预估库存

        /// <summary>
        /// 获取预估库存详情
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_auto_inventory GetAutoInventoryBySkuWH(string sku, string warehousecode)
        {
            var sql = "select * from dc_auto_inventory where warehouse_code='" + warehousecode + "' and bailun_sku='" + sku + "'";

            return _connection.QueryFirstOrDefault<dc_auto_inventory>(sql);
        }

        /// <summary>
        /// 历史库存
        /// </summary>
        /// <returns></returns>
        public static List<dc_daily_stock_dto> GetDailyStock(string bailun_sku, string warehouse_code, DateTime date)
        {
            return _connection.Query<dc_daily_stock_dto>(" select * from dc_daily_stock where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and record_time>=@record_time ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code,
                record_time = date
            }).ToList();
        }

        #endregion

        #region 预测入库

        /// <summary>
        /// 获取预测入库数详情
        /// </summary>
        /// <param name="sku">sku编码</param>
        /// <param name="warehousecode">库存编码</param>
        /// <param name="type">入库类型，1是采购入库, 2是调拨入库 </param>
        /// <returns></returns>
        public static dc_auto_inbound GetAutoInboundBySkuWH(string sku, string warehousecode, int type)
        {
            var sql = "select * from dc_auto_inbound where bailun_sku='" + sku + "' and warehouse_code='" + warehousecode + "'";
            if (type > 0)
            {
                sql += $" and type={type} ";
            }
            return _connection.QueryFirstOrDefault<dc_auto_inbound>(sql);
        }

        #endregion

        #region 预测断货

        /// <summary>
        /// 获取预测断货数详情
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <returns></returns>
        public static dc_auto_short_supply GetAutoShortSupplyBySkuWH(string sku, string warehousecode)
        {
            var sql = "select * from dc_auto_short_supply where bailun_sku='" + sku + "' and warehouse_code='" + warehousecode + "'";

            return _connection.QueryFirstOrDefault<dc_auto_short_supply>(sql);
        }

        #endregion

        /// <summary>
        /// 查询周转数据
        /// </summary>
        /// <param name="sku">产品SKU</param>
        /// <param name="wCode">仓库编码</param>
        /// <returns></returns>
        public static Models.dc_auto_turnover GetModel(string sku, string wCode)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", wCode);
            return _connection.QueryFirst<Models.dc_auto_turnover>(" select * from dc_auto_turnover where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", parameters);
        }


        #region 市场因素设置

        /// <summary>
        /// 获取市场因素设置分页列表
        /// </summary>
        /// <param name="m"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_auto_config_correction> ListCorrection(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_correction>();

            try
            {
                var sql = @"select dacp.id,dacp.bailun_sku,dacp.warehouse_code,dbw.warehouse_name,dacp.start_time,dacp.end_time,dacp.param,dacp.remark from dc_auto_config_correction dacp 
                            left join dc_base_warehouse dbw on dacp.warehouse_code = dbw.warehouse_code where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and dbw.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and dbw.area_id=" + m.warehousearea;
                }
                if (m.IsEffective == true)
                {
                    var nowStr = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    sql += $" and dacp.start_time<= '{nowStr}' and dacp.end_time>={nowStr} ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.sku + "%'";
                }

                if (!string.IsNullOrWhiteSpace(m.warehousecode))
                {
                    sql += " and dacp.warehouse_code='" + m.warehousecode + "'";
                }

                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1");

                var obj = _connection.Query<dc_auto_config_correction>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 查询未来指定时间范围内的市场因素数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static IEnumerable<dc_auto_config_correction> ListCorrection(string sku, string warehousecode, DateTime btime, DateTime etime)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("bailun_sku", sku);
            parameters.Add("warehouse_code", warehousecode);
            parameters.Add("b_time", btime.ToString("yyyy-MM-dd 00:00:00"));
            parameters.Add("e_time", etime.ToString("yyyy-MM-dd 23:59:59"));
            return _connection.Query<Models.dc_auto_config_correction>(@"select * from dc_auto_config_correction where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and ( end_time>=@b_time or start_time<=@e_time ) ", parameters);
        }

        /// <summary>
        /// 保存销售设置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveCorrection(Models.dc_auto_config_correction m, string username)
        {
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_config_correction>("select * from dc_auto_config_correction where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_config_correction
                {
                    gmt_create = DateTime.Now,
                };
            }

            obj.id = m.id;
            obj.bailun_sku = m.bailun_sku;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.end_time = m.end_time;
            obj.start_time = m.start_time;
            obj.remark = m.remark;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.param = m.param;

            // 检查该数据在时间段上是否有交叉重复
            var oldDataList = ListCorrection(m.bailun_sku, m.warehouse_code, obj.start_time, obj.end_time);
            if (oldDataList != null && oldDataList.Any(s => s.id != obj.id))
            {
                return $" SKU:【{m.bailun_sku}】,仓库:【{m.warehouse_code}】 在在所选时间范围已经有配置了数据，请勿重复配置 ";
            }

            try
            {
                if (obj.id > 0)
                {
                    var result = _connection.Update<Models.dc_auto_config_correction>(obj);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_correction>(obj);

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }





        /// <summary>
        /// 获取特殊销售设置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_auto_config_correction GetCorrectionById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_correction>("select * from dc_auto_config_correction where id=" + id);
        }




        #endregion

        /// <summary>
        /// 查询SKU基础配置数据
        /// </summary>
        /// <returns></returns>
        public static List<dc_sku_config_dto> SkuConfigList()
        {
            var sql = @"
-- select * from view_estimated_arrival_time

select
 dbw.hq_type as 'warehouse_type',
 t_base.warehouse_code as 'warehouse_code',
 (case when dbw.hq_type='fba仓' then dbw.bailun_account else dbw.warehouse_name end)  as warehouse_name,
 t3.bailun_sku as 'bailun_sku',
 t3.sku_title_cn as 'sku_title',
 t3.product_code as 'product_code',
 t3.product_inner_code as 'product_inner_code',
 t3.buyer_name as 'buyer_name',

 t5.transfer_config_delivery as 'transfer_config_delivery', -- 头程调拨初始值
 t6.transfer_delivery as 'transfer_delivery', -- 头程调拨平均值
 t5.abroad_inbound_config_delivery as 'abroad_inbound_config_delivery', -- 海外仓入库天数初始值
 t6.abroad_inbound_delivery as 'abroad_inbound_delivery', -- 海外仓入库天数平均值
 t5.transfer_bale_config_delivery as 'transfer_bale_config_delivery', -- 海外仓打包天数初始
 t6.transfer_bale_delivery as 'transfer_bale_delivery', -- 海外仓打包天数平均值
 
 t3.supplier_delivery as 'supplier_delivery', -- 初始采购交期
 (case when t7.new_supplier_delivery >0 then t7.new_supplier_delivery else 0 end  ) as 'new_supplier_delivery', -- 最近一个单的预计到货时间
 
 ( case when t5.average_overdue> 0 then t5.average_overdue else null end ) as 'average_overdue', -- 安全库存波峰天数(平均逾期天数),
 t3.moq as 'moq',
 (( case when t5.average_overdue> 0 then t5.average_overdue else 1 end ) * t5.quantity_safe_inventory ) as 'quantity_safe', -- 安全库存数量 
 t9.delivery_days as 'average_arrival' ,-- '实际到货平均值',
 t1.usable_stock as 'stock', -- 实际库存
 t5.quantity_safe_inventory as 'quantity_safe_inventory', -- 日均加权销量
 t5.forecast_sevenday_sales as 'forecast_sevenday_sales', -- 未来7天日均
 t5.turnover_days as 'turnover_days', -- 周转天数（供应商长度）
 t4.quantity_purchase as 'realtime_quantity_purchase', -- 采购在途
 t4.quantity_transfer as 'realtime_quantity_transfer', -- 调拨在途
 t5.quantity_out_stock as 'quantity_out_stock', -- 缺货数量
 ( case when t8.`status`=0 or t8.`status` is null then '正常监控' else '停止监控' end ) as 'monitor_status'
from 
(SELECT
 t1.warehouse_code,t1.bailun_sku 
FROM
 dc_mid_transit AS t1
 
union

SELECT
  t1.warehouse_code,t1.bailun_sku 
FROM
 dc_base_stock AS t1
 ) as t_base
left join dc_base_stock as t1 on t_base.bailun_sku = t1.bailun_sku and t_base.warehouse_code = t1.warehouse_code
left join dc_base_sku as t3 on t_base.bailun_sku = t3.bailun_sku
left join dc_base_warehouse as dbw on t_base.warehouse_code = dbw.warehouse_code
left join dc_mid_transit as t4  on t_base.bailun_sku = t4.bailun_sku and t_base.warehouse_code = t4.warehouse_code
left join dc_auto_turnover as t5 on t_base.bailun_sku = t5.bailun_sku and t_base.warehouse_code = t5.warehouse_code
left join dc_average_warehouse as t6 on t_base.bailun_sku = t6.bailun_sku and t_base.warehouse_code = t6.warehouse_code
left join view_estimated_arrival_time as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_auto_config_sku_warehouse as t8 on t_base.bailun_sku = t8.bailun_sku and t_base.warehouse_code = t8.warehouse_code
left join dc_average_purchase as t9 on t1.bailun_sku  = t9.bailun_sku and t3.suppliers_id = t9.supplier_id
";

            var datas = _connection.Query<dc_sku_config_dto>(sql, commandTimeout: 0).ToList();

            return datas;
        }

        /// <summary>
        /// 查询SKU最近一个单的供应商交期
        /// </summary>
        /// <returns></returns>
        public static List<new_supplier_delivery_dto> SkuNewSupplierDeliveryList()
        {


            var sql = @"
select t7_1.warehouse_code,t7_1.bailun_sku,(to_days(t7_1.estimated_arrival_time) - ( case when t7_1.pay_type=1 then to_days(t7_1.pay_time) else to_days(t7_1.confirm_time) end ) ) as 'new_supplier_delivery' from dc_base_purchase  as t7_1
 	where t7_1.id in (
 		select max(id) from dc_base_purchase as t7_2 where t7_2.estimated_arrival_time is not null GROUP BY t7_2.warehouse_code,t7_2.bailun_sku
 	)
";
            var datas = _connection.Query<new_supplier_delivery_dto>(sql).ToList();


            return datas;
        }

        /// <summary>
        /// 备份预测值
        /// </summary>
        public static void CopyForecast()
        {
            _connection.Execute(@"REPLACE INTO dc_base_forecast_history(`bailun_sku`,`warehouse_code`,`bailun_sku_warehouse_code`,`date`,`date_str`,`create_date`,`sales`,`turnover_days`,`quantity_safe_inventory`,`supplier_delivery`,`transfer_config_delivery`,`transfer_bale_config_delivery`,`abroad_inbound_config_delivery`,`inspection_config_delivery`,`inspection_delivery`,`transfer_delivery`,`daily_weighted_sales`,`transfer_bale_delivery`,`abroad_inbound_delivery`)
(
select 
bailun_sku,
warehouse_code,
CONCAT(bailun_sku,warehouse_code) as 'bailun_sku_warehouse_code',
gmt_modified as 'date',
DATE_FORMAT(gmt_modified,'%Y-%m-%d') as 'date_str',
now() as 'create_date',
forecast_oneday_sales as 'sales',
turnover_days as 'turnover_days',
quantity_safe_inventory as 'quantity_safe_inventory',
supplier_delivery as 'supplier_delivery',
transfer_config_delivery as 'transfer_config_delivery',
transfer_bale_config_delivery as 'transfer_bale_config_delivery',
abroad_inbound_config_delivery as 'abroad_inbound_config_delivery',
inspection_config_delivery as 'inspection_config_delivery',
inspection_delivery as 'inspection_delivery',
transfer_delivery as 'transfer_delivery',
daily_weighted_sales as 'daily_weighted_sales',
transfer_bale_delivery as 'transfer_bale_delivery',
abroad_inbound_delivery as 'abroad_inbound_delivery'
from dc_auto_turnover where gmt_modified>=@btime and gmt_modified<=@etime
)
", new { btime = DateTime.Now.ToDayHome(), etime = DateTime.Now.ToDayEnd() });
        }

        /// <summary>
        /// 获取数据来源
        /// </summary>
        /// <returns></returns>
        public static List<dc_base_order_data_source_dto> GetOrderDataSource(dc_base_order_data_source_search_dto searchData, int offset, int limit, ref int total)
        {
            var sql = " select * from dc_base_order_data_source as t1 where 1=1 ";
            var countSql = " select count(1) from dc_base_order_data_source as t1 where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if(!string.IsNullOrWhiteSpace(searchData.bailun_sku))
            {
                sql += " and t1.bailun_sku=@bailun_sku ";
                countSql += " and t1.bailun_sku=@bailun_sku ";
                parameters.Add("bailun_sku",searchData.bailun_sku);
            }
            if (!string.IsNullOrWhiteSpace(searchData.warehouse_code))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", searchData.warehouse_code);
            }
            if (!string.IsNullOrWhiteSpace(searchData.order_type_str))
            {
                sql += " and t1.order_type=@order_type ";
                countSql += " and t1.order_type=@order_type ";
                parameters.Add("order_type", searchData.order_type_str);
            }
            sql += " limit " + offset + "," + limit;
            total = _connection.QueryFirstOrDefault<int>(countSql, parameters);
            return _connection.Query<dc_base_order_data_source_dto>(sql, parameters).AsList();
        }

        /// <summary>
        /// 更新采购建议
        /// </summary>
        public static void ResetPurchaseAdviseSingle(string bailun_sku,string warehouse_code)
        {
            _connection.Insert(new dc_base_queue
            {
                submit_date = DateTime.Now,
                error_message = "",
                consume_date = null,
                error_stack_trace = "",
                message = new { bailun_sku = bailun_sku, warehouse_code = warehouse_code }.ToJson(),
                type = "刷新周转表"
            });
        }

       

        public static void WaitTurnoverQueueTask()
        {
            // 每秒检查一次
            Thread.Sleep(10 * 1000);
            var count = _connection.QueryFirstOrDefault<int>(" select count(1) from dc_base_queue where consume_date is null ", commandTimeout: 0);
            if (count > 0)
            {
                WaitTurnoverQueueTask();
            }
        }
    }


}