using AutoTurnOver.Models;
using AutoTurnOver.Models.auto;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using AutoTurnOver.Utility;

namespace AutoTurnOver.DB
{
    public class purchase_advise : connectionHelper
    {
        /// <summary>
        /// 获取当前最大的流水号
        /// </summary>
        /// <returns></returns>
        public static string MaxNo()
        {
            return _connection.QueryFirstOrDefault<string>(" select max(`No`) from dc_auto_purchase_advise where create_time>=@create_time_begin and create_time<=@create_time_end ", new
            {
                create_time_begin = DateTime.Now.ToString("yyyy-MM-dd 00:00:00"),
                create_time_end = DateTime.Now.ToString("yyyy-MM-dd 23:59:59")
            });
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <returns></returns>
        public static int? Add(dc_auto_purchase_advise data)
        {
            if (string.IsNullOrWhiteSpace(data.buy_sys_plan_no))
            {
                data.buy_sys_plan_no = "";
            }
            data.push_time = null;
            return _connection.Insert<dc_auto_purchase_advise>(data);
        }

        /// <summary>
        /// 切换物流商
        /// </summary>
        public static List<dc_auto_replace_logistics_task> ReplaceLogistics(DateTime date, string bailun_sku = null, string warehouse_code = null)
        {
            List<dc_auto_replace_logistics_task> cahnge_datas = new List<dc_auto_replace_logistics_task>();
            Console.WriteLine("查询需要切换的数据");
            // 查询需要切换的数据
            List<replace_logistics_outofstock_dto> ouDatas = GetOutOfStock(date, bailun_sku, warehouse_code);
            Console.WriteLine($" 查询需要切换的数据 :共：{ouDatas.Count} 条 ");
            int count = 1;
            // 寻找有没有满足条件的物流方案
            foreach (var item in ouDatas)
            {
                try
                {
                    //Console.WriteLine($"要切换的数据：第{count} 条 {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")} ");
                    count++;
                    // 查询物流方案
                    var logisticsDatas = ApiUtility.RealTimeShipLogisticsList(new Models.ApiDto.LmsShipLogisticsRequstDto
                    {
                        endCountries = item.country_code,
                        character_skus = item.bailun_sku,
                        startPoint = "3",
                    });

                    if (logisticsDatas != null && logisticsDatas.Count >= 1)
                    {
                        // 查询最快的物流渠道
                        var avgs = ApiUtility.GetLmsOrderTransferAvg(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto {transfer_type_val  =3, warehouse_code = item.warehouse_code, country = item.country_code, logistics_code = string.Join(",", logisticsDatas.Select(s => s.Line_Code)) }).Where(s => s.avg_put_days != null).ToList();
                        if (avgs != null)
                        {
                            var selectItem = avgs.Where(s => s.avg_put_days != null)
                                //.Where(s => s.avg_put_days + s.avg_sign_days <= item.surplus_allocation_days)
                                .OrderByDescending(s => s.avg_sign_days + s.avg_put_days).FirstOrDefault();
                            // 有时效符合条件的物流可用
                            if (selectItem != null)
                            {
                                var default_head_transport_config = _connection.QueryFirstOrDefault<dc_base_head_transport_config>(" select * from dc_base_head_transport_config where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                                {
                                    bailun_sku = item.bailun_sku,
                                    warehouse_code = item.warehouse_code
                                });
                                var remarks = $" 预计于：{item.start_time.ToString("yyyy-MM-dd")} 将缺货, 预计采购到货：{(DateTime.Now.AddDays(item.payment_before_delivery + item.inspection_delivery + item.supplier_delivery).ToString("yyyy-MM-dd"))} ，{ item.quantity }个 。\n 默认物流方案： ";
                                if (default_head_transport_config != null)
                                {
                                    remarks += $"{default_head_transport_config.head_transport}-{default_head_transport_config.logistics_name} ( {default_head_transport_config.logistics_code} )";
                                }
                                else
                                {
                                    remarks += " 未配置 ";
                                }
                                remarks += $" \n 默认时效 调拨天数: {item.transfer_delivery} , 入库天数：{item.abroad_inbound_delivery} 预计入库：{DateTime.Now.AddDays(item.payment_before_delivery + item.inspection_delivery + item.supplier_delivery + (int)Math.Round(item.transfer_delivery) + (int)Math.Round(item.abroad_inbound_delivery, 0)).ToString("yyyy-MM-dd")}";
                                remarks += $" \n 当前推荐物流平均时效 调拨天数: {Math.Round(selectItem.avg_sign_days)} , 入库天数：{Math.Round(selectItem.avg_put_days.Value, 0)} \n 预计入库 ：{DateTime.Now.AddDays(item.payment_before_delivery + item.inspection_delivery + item.supplier_delivery + (int)Math.Round(selectItem.avg_sign_days) + (int)Math.Round(selectItem.avg_put_days.Value, 0)).ToString("yyyy-MM-dd")} ";
                                var item_task = new dc_auto_replace_logistics_task
                                {
                                    bailun_sku = item.bailun_sku,
                                    abroad_inbound_delivery = selectItem.avg_put_days.Value,
                                    create_date = DateTime.Now,
                                    error_message = "",
                                    error_stack_trace = "",
                                    status = 0,
                                    logistics_code = selectItem.logistics_code,
                                    old_transfer_delivery = item.transfer_delivery,
                                    old_transfer_bale_delivery = item.transfer_bale_delivery,
                                    old_abroad_inbound_delivery = item.abroad_inbound_delivery,
                                    transfer_bale_delivery = 1,
                                    transfer_delivery = selectItem.avg_sign_days,
                                    warehouse_code = item.warehouse_code,
                                    update_date = DateTime.Now,
                                    logistics_name = selectItem.logistics_name,
                                    transport_type = selectItem.transport_type,
                                    remarks = remarks,
                                };
                                cahnge_datas.Add(item_task);
                                _connection.Insert(item_task);

                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("查询需要切换的数据 : " + ex.Message);
                    _connection.Insert(new dc_task_error_log
                    {
                        date = DateTime.Now,
                        message = item.ToJson() + " --- " + ex.Message,
                        stack_trace = ex.StackTrace,
                        task_name = "空运切换异常"
                    });
                }

            }

            return cahnge_datas;
        }

        /// <summary>
        /// 等待 后台跑切换物流的任务
        /// </summary>
        public static void WaitReplaceLogisticsTask()
        {
            // 每秒检查一次
            Thread.Sleep(1 * 1000);
            var count = _connection.QueryFirstOrDefault<int>(" select count(1) from dc_auto_replace_logistics_task where status=0 ", commandTimeout: 0);
            if (count > 0)
            {
                WaitReplaceLogisticsTask();
            }
        }

        /// <summary>
        /// 获取供应链内有缺货的数据
        /// </summary>
        /// <returns></returns>
        public static List<replace_logistics_outofstock_dto> GetOutOfStock(DateTime date, string bailun_sku = null, string warehouse_code = null)
        {
            var now = DateTime.Now;
            List<replace_logistics_outofstock_dto> datas = new List<replace_logistics_outofstock_dto>();
            var sql = @"
select 
t1.bailun_sku,
t1.warehouse_code,
t1.start_time,
t3.supplier_delivery,
t3.inspection_delivery,
t3.payment_before_delivery,
t3.turnover_days,
t2.area_name,
t3.abroad_inbound_delivery,
t3.transfer_bale_delivery,
t3.transfer_delivery,
t1.quantity
from dc_auto_forecast_fluctuation as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code =t2.warehouse_code
left join dc_auto_turnover as t3 on t1.warehouse_code =t3.warehouse_code and t1.bailun_sku = t3.bailun_sku
left join dc_base_head_transport_config as t4 on t1.bailun_sku = t4.bailun_sku and t1.warehouse_code = t4.warehouse_code
where t1.type=2 and t2.hq_type in ('第三方仓库','FBA仓') and t1.start_time>=now()  and t1.start_time<=DATE_ADD(now(),INTERVAL t3.turnover_days day)
and t1.gmt_modified>=@btime and ifnull(t4.head_transport,'')!='空运'
";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("btime", date);
            if (!string.IsNullOrWhiteSpace(bailun_sku))
            {
                sql += $" and t1.bailun_sku='{bailun_sku}' ";
            }
            if (!string.IsNullOrWhiteSpace(warehouse_code))
            {
                sql += $" and t1.warehouse_code='{warehouse_code}' ";
            }
            sql += " order by t1.start_time desc ;";
            List<replace_logistics_outofstock_dto> ouDatas = _connection.Query<replace_logistics_outofstock_dto>(sql, parameters).ToList();
            foreach (var item in ouDatas)
            {
                // 如果采购都没到货就已经缺货了，调拨天数切换已经没有意义了
                item.surplus_allocation_days = (decimal)(item.start_time.AddDays(0 - (item.inspection_delivery + item.payment_before_delivery + item.supplier_delivery)) - now).TotalDays;
                if (item.surplus_allocation_days >= 1)
                {
                    item.country_code = _connection.QueryFirstOrDefault<string>(" select `code` from dc_base_country where `name`=@name limit 1 ", new { name = item.area_name });
                    if (!string.IsNullOrWhiteSpace(item.country_code))
                    {
                        datas.Add(item);
                    }

                }
            }
            return datas;


        }

        /// <summary>
        /// 导入明细数据
        /// 张莹霞1的要减去缺货数才下单
        /// </summary>
        /// <param name="mainID"></param>
        public static void ImportDetailed_bak(int mainID, DateTime date)
        {

            //RedundancyDeduction("QYBLZZ", "GZBLWH");

            //RedundancyDeduction("GZBLWH", "QYBLZZ");

            // 生成采购建议 (只补缺货的不管)
            _connection.Execute(@"
INSERT into dc_auto_purchase_advise_detailed(`bailun_sku`,`source_warehouse_code`,`warehouse_code`,`quantity_init_advise`,`quantity_final_advise`,
`product_inner_code`,`goods_quantity_init_advise`,`goods_moq`,`good_sku_codes`,`goods_history_fourteenday_sales`,`history_fourteenday_sales`,
`quantity_actual`,`main_id`,
`forecast_formula`,`fit_forecast_formula`,`turnover_days`,`supplier_delivery`,`inspection_delivery`,`transfer_delivery`,`sales_upper_limit`,`ispush`,`type`
,`suppliers_id`,`quantity_out_stock`,`purchase_type_jit`,`auto_replace_logistics_task_id`,`logistics_code`,`task_transport_type`)
(
	select * from (
select 
	t1.bailun_sku,
    t1.warehouse_code as 'source_warehouse_code',
	(case when t8.group_id=2 then 'GZBLWH' else t1.warehouse_code end ) as 'warehouse_code',
	t1.quantity_init_advise as 'quantity_init_advise', -- 原始采购建议数量 new
	(
	case 
	when t6.buyer_name in ('张莹霞') then ( case when t1.quantity_init_advise>0 then greatest(t1.quantity_init_advise,t6.moq) else 0 end) -- (张莹霞的sku取moq下单)
	when t7.sum_quantity_init_advise>=t7.max_moq then t1.quantity_init_advise-- 如果商品的下单总量达到moq了,就直接下单 
	when t7.sku_count=1 then t7.max_moq -- 如果该商品只有一个sku需要下单,则直接下一个moq
	else
		FLOOR(
		GREATEST(t1.quantity_init_advise,0) +
		(
		(t7.max_moq-GREATEST(t7.sum_quantity_init_advise,0)) 
		* 
		(t1.history_fourteenday_sales / t7.sum_history_fourteenday_sales ) 
		)
		)-- 如果以上条件都不满足，则计算该sku需要摊分多少moq余下的任务
	end
	) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t7.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	t7.sum_quantity_init_advise as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	t7.max_moq as 'goods_moq', -- 商品的moq new
	t7.sku_codes as 'good_sku_codes', -- 商品涉及的下单所有sku new
	t7.sum_history_fourteenday_sales as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	t1.history_fourteenday_sales as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	t1.turnover_days,
	t1.supplier_delivery,
	t1.inspection_delivery,
	t1.transfer_delivery,
    t1.sales_upper_limit,
    0 as 'ispush',
    (case when t1.quantity_out_stock>0 then 2 when t1.`status` then 1 else 3 end) as 'type',
    t6.suppliers_id,
    t1.quantity_out_stock,
		(
		case when t6.buyer_name in ('张莹霞','张莹霞1') and t1.warehouse_code='GZBLWH' then 1
		else 0
		end 
		) as 'purchase_type_jit',
t1.auto_replace_logistics_task_id,
tr_task.logistics_code,
tr_task.transport_type as 'task_transport_type'
	from dc_auto_turnover as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
    left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join view_goods_moq as t7 on t6.product_inner_code = t7.product_inner_code and t1.warehouse_code = t7.warehouse_code and t6.suppliers_id = t7.suppliers_id
	left join dc_auto_jit_tag as t8 on t1.tag_id = t8.id
    left join dc_auto_replace_logistics_task as tr_task on t1.auto_replace_logistics_task_id = tr_task.id
	where t1.gmt_modified>=@btime
    and t6.`status`=2  and (t1.history_fourteenday_sales>0 or t1.quantity_out_stock>0 )  and ( t5.`status`=0 or t5.`status` is null ) and t7.sum_quantity_init_advise>0 
   and ( 
		( ( t6.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1','冯兆欣') or t1.warehouse_code not in ('GZBLWH','GZBLYS') ) and  t1.warehouse_code!='BLGZ03' )
		or
		( t1.tag_id > 0 and t8.is_eliminate = 0 )
		) -- 非jit  或者 jit 备货
	) as t1 where t1.quantity_final_advise>0
	
	)
", new { main_id = mainID, btime = date }, commandTimeout: 0);


            // 凑单sku(初始建议数没有) 如果下单数只有一个，改为2 （ 只补缺货的不管 ）
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_base_sku as t2 
set t1.quantity_final_advise=2  
where t2.`status`=2 and t1.bailun_sku = t2.bailun_sku and t1.quantity_final_advise=1 and t1.quantity_init_advise<=0 and ( t2.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1') or t1.warehouse_code!='GZBLWH' ) and  t1.main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);

            // 初始建议数为1，,系统建议数为1时 改为3 （ 只补缺货的不管 ）
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_base_sku as t2 
set quantity_final_advise=3  
where t2.`status`=2 and t1.bailun_sku = t2.bailun_sku and quantity_final_advise=1 and quantity_init_advise=1 and ( t2.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1') or t1.warehouse_code!='GZBLWH' ) and  main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);

            //张莹霞1 and 广州01 去掉缺货数
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_mid_transit as t2 ,
dc_base_sku as t3
set t1.quantity_final_advise=GREATEST(0,t1.quantity_final_advise-t2.quantity_out_stock)  
where t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code and t1.bailun_sku=t3.bailun_sku
and t1.quantity_init_advise>=0 and ( t3.buyer_name in ('张莹霞1') and t1.warehouse_code='GZBLWH' ) and  t1.main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);


            // 调整因为舍弃小数导致的moq不足
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(" select * from dc_auto_purchase_advise_detailed where main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0).AsList();
            if (datas != null && datas.Count > 0)
            {
                foreach (var itemGoods in datas.GroupBy(s => new { s.suppliers_id, s.product_inner_code, s.warehouse_code }))
                {

                    // 判断总建议采购数是否达到了moq
                    var difference = itemGoods.Max(s => s.goods_moq) - itemGoods.Sum(s => s.quantity_final_advise);
                    if (difference > 0)
                    {
                        // 把最大的那条记录找出来，加给他
                        var max_data = itemGoods.OrderByDescending(s => s.history_fourteenday_sales).FirstOrDefault();
                        _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_final_advise=@quantity_final_advise where id=@id ", new
                        {
                            quantity_final_advise = max_data.quantity_final_advise + difference,
                            id = max_data.id
                        });
                    }
                    else if (difference < 0)
                    {
                        // 由于上面的补单规则，会导致超出moq . 有超出的部分，把补单的（初始建议数为负数）按销量排序，逐个消减
                        var abatement = 0 - difference;
                        foreach (var item in itemGoods.Where(s => s.quantity_init_advise < 0).OrderBy(s => s.goods_history_fourteenday_sales))
                        {
                            if (abatement >= item.quantity_final_advise)
                            {
                                _connection.Execute(" delete from dc_auto_purchase_advise_detailed  where id=@id ", new { id = item.id });
                                abatement -= item.quantity_final_advise;
                            }
                            else
                            {
                                _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_final_advise=@quantity_final_advise where id=@id ", new { id = item.id, quantity_final_advise = item.quantity_final_advise - abatement });
                                abatement = 0;
                            }

                            if (abatement <= 0)
                            {
                                break;
                            }
                        }
                    }
                }
            }



            //ImportShoujiShortageDetailed(mainID);



        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mainID"></param>
        /// <param name="date"></param>
        /// <param name="is_clear_data">是否清理历史数据</param>
        public static void ImportDetailed(int mainID, DateTime date, bool is_clear_data = false)
        {
            // 刷新商品moq汇总数据
            _connection.Execute(@" -- 刷新 刷新商品moq汇总数据
-- 清空视图表的数据
Truncate table dc_aims_goods_moq;

INSERT into dc_aims_goods_moq(`product_inner_code`,`suppliers_id`,`warehouse_code`,`sum_quantity_init_advise`,`max_moq`
,`sum_history_fourteenday_sales`,`sku_count`,`sku_codes`,`u_id`) 
	( 
	SELECT
	`t6`.`product_inner_code` AS `product_inner_code`,
	`t6`.`suppliers_id` AS `suppliers_id`,
	`t1`.`warehouse_code` AS `warehouse_code`,
	sum(
	greatest( `t1`.`quantity_init_advise`, 0 )) AS `sum_quantity_init_advise`,
	max( `t6`.`moq` ) AS `max_moq`,
	sum( `t1`.`history_fourteenday_sales` ) AS `sum_history_fourteenday_sales`,
	count( 1 ) AS `sku_count`,
	group_concat( `t6`.`bailun_sku` SEPARATOR ',' ) AS `sku_codes`,
	CONCAT(`t6`.`product_inner_code`,`t6`.`suppliers_id`,`t1`.`warehouse_code`) as 'u_id'
	FROM
		(((
					`dc_auto_turnover` `t1`
					LEFT JOIN `dc_auto_config_sku_warehouse` `t5` ON (((
								`t1`.`bailun_sku` = `t5`.`bailun_sku` 
								) 
						AND ( `t1`.`warehouse_code` = `t5`.`warehouse_code` ))))
				LEFT JOIN `dc_base_sku` `t6` ON ((
						`t1`.`bailun_sku` = `t6`.`bailun_sku` 
					)))
			LEFT JOIN `dc_auto_jit_tag` `t8` ON ((
					`t1`.`tag_id` = `t8`.`id` 
				))) 
	WHERE
		((
				`t1`.`gmt_modified` >= '2021-01-20' )
			AND ((
					`t5`.`status` = 0 
					) 
			OR isnull( `t5`.`status` )) 
			AND ( `t6`.`status` = 2 ) 
			AND ((
					`t6`.`buyer_name` NOT IN ( '赵美聪', '张莹霞', '张莹霞1' )) 
				OR (
				`t1`.`warehouse_code` NOT IN ( 'GZBLWH', 'GZBLYS' )) 
				OR ((
						`t1`.`tag_id` > 0 
						) 
				AND ( `t8`.`is_eliminate` = 0 )))) 
	GROUP BY
		`t6`.`product_inner_code`,
	`t6`.`suppliers_id`,
	`t1`.`warehouse_code`);
;  ", new { time = date }, commandTimeout: 0);

            _connection.Execute(@" update dc_auto_turnover as t1,
dc_base_sku as t2
set t1.adv_uid = CONCAT(t2.product_inner_code,t2.suppliers_id,t1.warehouse_code)
where t1.bailun_sku = t2.bailun_sku and t1.gmt_modified>=@btime ;
 ", new { btime = date }, commandTimeout: 0);
            //RedundancyDeduction("QYBLZZ", "GZBLWH");

            //RedundancyDeduction("GZBLWH", "QYBLZZ");

            Console.WriteLine("开始出建议");
            // 生成采购建议 (只补缺货的不管)
            var datas1 = MyMySqlConnection._connection.Query<dc_auto_purchase_advise_detailed>(@"select * from (
select 
	t1.bailun_sku,
    CONCAT(t1.bailun_sku,(case when t8.group_id=2 then 'GZBLWH' else t1.warehouse_code end )) as 'bailun_sku_warehouse_code',
    t1.warehouse_code as 'source_warehouse_code',
	(case when t8.group_id=2 then 'GZBLWH' else t1.warehouse_code end ) as 'warehouse_code',
	t1.quantity_init_advise as 'quantity_init_advise', -- 原始采购建议数量 new
	(
	case 
	when t6.buyer_name in ('张莹霞') then ( case when t1.quantity_init_advise>0 then greatest(t1.quantity_init_advise,t6.moq) else 0 end) -- (张莹霞的sku取moq下单)
	when t7.sum_quantity_init_advise>=t7.max_moq then t1.quantity_init_advise-- 如果商品的下单总量达到moq了,就直接下单 
	when t7.sku_count=1 then t7.max_moq -- 如果该商品只有一个sku需要下单,则直接下一个moq
    when ifnull( t7.sum_history_fourteenday_sales,0)<=0 then t1.quantity_init_advise
	else
		FLOOR(
		GREATEST(t1.quantity_init_advise,0) +
		(
		(t7.max_moq-GREATEST(t7.sum_quantity_init_advise,0)) 
		* 
		(t1.history_fourteenday_sales / t7.sum_history_fourteenday_sales ) 
		)
		)-- 如果以上条件都不满足，则计算该sku需要摊分多少moq余下的任务
	end
	) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t7.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	t7.sum_quantity_init_advise as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	t7.max_moq as 'goods_moq', -- 商品的moq new
	t7.sku_codes as 'good_sku_codes', -- 商品涉及的下单所有sku new
	t7.sum_history_fourteenday_sales as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	t1.history_fourteenday_sales as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	t1.turnover_days,
	t1.supplier_delivery,
	t1.inspection_delivery,
	t1.transfer_delivery,
    t1.sales_upper_limit,
    0 as 'ispush',
    (case when t1.quantity_out_stock>0 then 2 when t1.`status` then 1 else 3 end) as 'type',
    t6.suppliers_id,
    t1.quantity_out_stock,
		(
		case when t6.buyer_name in ('张莹霞','张莹霞1') and t1.warehouse_code='GZBLWH' then 1
		else 0
		end 
		) as 'purchase_type_jit',
t1.auto_replace_logistics_task_id,
tr_task.logistics_code,
tr_task.logistics_name,
tr_task.transport_type as 'task_transport_type',
tr_task.remarks as 'replace_logistics_remarks',
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage',
t1.first_order_date as 'first_order_date',
t1.first_order_bailun_order_id as 'first_order_bailun_order_id',
t1.is_mother_sku as 'is_mother_map_sku'
	from (select * from dc_auto_turnover where gmt_modified>=@btime ) as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
    left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_aims_goods_moq as t7 on t1.adv_uid = t7.u_id
	left join dc_auto_jit_tag as t8 on t1.tag_id = t8.id
    left join dc_auto_replace_logistics_task as tr_task on t1.auto_replace_logistics_task_id = tr_task.id
    left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where 1=1
	and ( t1.is_mother_sku =1 or (
     t6.`status`=2  
-- and (t1.history_fourteenday_sales>0 or t1.quantity_out_stock>0 )  
and ( t5.`status`=0 or t5.`status` is null ) and t7.sum_quantity_init_advise>0 
   and ( 
		( ( t6.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1','冯兆欣') or t1.warehouse_code not in ('GZBLWH','GZBLYS') ) and  t1.warehouse_code!='BLGZ03' )
		or
		( t1.tag_id > 0 and t8.is_eliminate = 0 )
		) -- 非jit  或者 jit 备货
	)
	)
	) as t1 where t1.quantity_final_advise>0
", new { main_id = mainID, btime = date }, commandTimeout: 0).ToList();
            Console.WriteLine("开始出建议 完");

            if (is_clear_data)
            {
                var initDate = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
                // 清理历史数据
                _connection.Execute(@"update dc_auto_purchase_advise_detailed set `status`=-1 where main_id in (
	select id from dc_auto_purchase_advise where buy_sys_plan_no in ('Generate')
)
and `status`=0 and bailun_sku_warehouse_code not in @bailun_sku_warehouse_codes
", new { bailun_sku_warehouse_codes = datas1.Select(s => $"{s.bailun_sku}{s.warehouse_code}") });
            }

            if (datas1 != null && datas1.Count >= 1)
            {
                var datas2 = SetMotherSkuStorageCapacity(datas1);
                WritePurchaseAdvise(datas2);
            }

            // 凑单sku(初始建议数没有) 如果下单数只有一个，改为2 （ 只补缺货的不管 ）
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_base_sku as t2 
set t1.quantity_final_advise=2  
where t2.`status`=2 and t1.bailun_sku = t2.bailun_sku and t1.quantity_final_advise=1 and t1.quantity_init_advise<=0 and ( t2.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1') or t1.warehouse_code!='GZBLWH' ) and  t1.main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);

            // 初始建议数为1，,系统建议数为1时 改为3 （ 只补缺货的不管 ）
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_base_sku as t2 
set quantity_final_advise=3  
where t2.`status`=2 and t1.bailun_sku = t2.bailun_sku and quantity_final_advise=1 and quantity_init_advise=1 and ( t2.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1') or t1.warehouse_code!='GZBLWH' ) and  main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);

            //张莹霞1 and 广州01 去掉缺货数
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_mid_transit as t2 ,
dc_base_sku as t3
set t1.quantity_final_advise=GREATEST(0,t1.quantity_final_advise-t2.quantity_out_stock)  
where t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code and t1.bailun_sku=t3.bailun_sku
and t1.quantity_init_advise>=0 and ( t3.buyer_name in ('张莹霞1') and t1.warehouse_code='GZBLWH' ) and  t1.main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);


            // 调整因为舍弃小数导致的moq不足
            var datas = MyMySqlConnection._connection.Query<dc_auto_purchase_advise_detailed>(" select * from dc_auto_purchase_advise_detailed where main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0).AsList().Where(s=>s.storage_capacity_lack_quantity<=0).ToList();
            if (datas != null && datas.Count > 0)
            {
                foreach (var itemGoods in datas.GroupBy(s => new { s.suppliers_id, s.product_inner_code, s.warehouse_code }))
                {

                    // 判断总建议采购数是否达到了moq
                    var difference = itemGoods.Max(s => s.goods_moq) - itemGoods.Sum(s => s.quantity_final_advise);
                    if (difference > 0)
                    {
                        // 把最大的那条记录找出来，加给他
                        var max_data = itemGoods.OrderByDescending(s => s.history_fourteenday_sales).FirstOrDefault();
                        _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_final_advise=@quantity_final_advise where id=@id ", new
                        {
                            quantity_final_advise = max_data.quantity_final_advise + difference,
                            id = max_data.id
                        });
                    }
                    else if (difference < 0)
                    {
                        // 由于上面的补单规则，会导致超出moq . 有超出的部分，把补单的（初始建议数为负数）按销量排序，逐个消减
                        var abatement = 0 - difference;
                        foreach (var item in itemGoods.Where(s => s.quantity_init_advise < 0).OrderBy(s => s.goods_history_fourteenday_sales))
                        {
                            if (abatement >= item.quantity_final_advise)
                            {
                                _connection.Execute(" delete from dc_auto_purchase_advise_detailed  where id=@id ", new { id = item.id });
                                abatement -= item.quantity_final_advise;
                            }
                            else
                            {
                                _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_final_advise=@quantity_final_advise where id=@id ", new { id = item.id, quantity_final_advise = item.quantity_final_advise - abatement });
                                abatement = 0;
                            }

                            if (abatement <= 0)
                            {
                                break;
                            }
                        }
                    }
                }
            }

            //ImportShoujiShortageDetailed(mainID);

            //备份周转数据
            CopyTurnoverData(mainID);

        }

        /// <summary>
        /// 根据库容动态调配
        /// </summary>
        /// <returns></returns>
        public static List<dc_auto_purchase_advise_detailed> SetMotherSkuStorageCapacity(List<dc_auto_purchase_advise_detailed> originalDatas)
        {
            List<dc_auto_purchase_advise_detailed> datas = new List<dc_auto_purchase_advise_detailed>();

            var mother_datas = originalDatas.Where(s => s.is_mother_map_sku == 1).ToList();
            var not_mother_datas = originalDatas.Where(s => s.is_mother_map_sku == 0).ToList();
            //先清洗一波子母sku
            if (mother_datas != null && mother_datas.Count >= 1)
            {
                // 再查询需要监控库容的母sku
                var mother_storage_capacity_configs = _connection.Query<dc_base_storage_capacity_config>(" select * from dc_base_storage_capacity_config where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
                {
                    bailun_sku_warehouse_codes = mother_datas.Select(s => s.bailun_sku_warehouse_code).ToList()
                }).ToList();

                //只计算有监控库容的母sku
                mother_datas = mother_datas.Where(s=> mother_storage_capacity_configs.Any(v=> s.bailun_sku_warehouse_code==s.bailun_sku_warehouse_code)).ToList();

                if(mother_datas!=null && mother_datas.Count >= 1)
                {
                    // 查询母sku锁包含的子sku全部清除
                    var maps = _connection.Query<dc_base_mother_sku_map>(" select * from dc_base_mother_sku_map where mother_sku_warehouse_code in @mother_sku_warehouse_codes ", new
                    {
                        mother_sku_warehouse_codes = mother_datas.Select(s => s.bailun_sku_warehouse_code).ToList()
                    });

                    if (maps != null && maps.Count() >= 1)
                    {
                        foreach (var item in mother_datas)
                        {
                            var itemMaps = maps.Where(s => s.mother_sku_warehouse_code == item.bailun_sku_warehouse_code).OrderByDescending(s => s.is_main).ToList();
                            if (itemMaps != null && itemMaps.Count >= 1)
                            {
                                // 查询这些子sku的库容
                                var skuStorageCapacityList = _connection.Query<dc_base_storage_capacity>(" select * from dc_base_storage_capacity where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
                                {
                                    bailun_sku_warehouse_codes = itemMaps.Select(s => s.bailun_sku_warehouse_code).ToList()
                                }).Where(s => s.stock > 0).ToList();


                                var new_datas = new List<dc_auto_purchase_advise_detailed>();
                                var sum_stock = item.quantity_final_advise;
                                //根据每个有库容的sku分摊需求
                                foreach (var itemMap in itemMaps)
                                {
                                    if (sum_stock <= 0) break;

                                    var skuTransit = _connection.QueryFirstOrDefault<dc_mid_transit>(" select * from dc_mid_transit where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                                    {
                                        bailun_sku = itemMap.bailun_sku,
                                        warehouse_code = itemMap.warehouse_code
                                    }) ?? new dc_mid_transit();

                                    var itemStorageCapacity = (skuStorageCapacityList.FirstOrDefault(s => s.bailun_sku_warehouse_code == itemMap.bailun_sku_warehouse_code) ?? new dc_base_storage_capacity() { });

                                    // 扣除采购在途，调拨采购临时，调拨暂存，调拨计划
                                    itemStorageCapacity.stock = Math.Max(0, (itemStorageCapacity.stock - skuTransit.quantity_purchase - skuTransit.quantity_transfer_temporary_storage - skuTransit.quantity_transfer_temp_schedule - skuTransit.quantity_transfer_plan));

                                    var use_quantity = 0;
                                    if (itemStorageCapacity.stock >= sum_stock)
                                    {
                                        use_quantity = sum_stock;
                                        sum_stock = 0;
                                    }
                                    else
                                    {
                                        use_quantity = (int)Math.Ceiling(itemStorageCapacity.stock);
                                        sum_stock -= use_quantity;
                                    }

                                    var new_data = item.ToJson().ToObj<dc_auto_purchase_advise_detailed>();
                                    new_data.storage_capacity_quantity = itemStorageCapacity.stock;
                                    new_data.quantity_init_advise = item.quantity_final_advise;
                                    new_data.quantity_final_advise = use_quantity;
                                    new_data.mother_sku = item.bailun_sku;
                                    new_data.product_inner_code = "";
                                    new_data.good_sku_codes = "";
                                    new_data.is_mother_map_sku = 1;
                                    new_data.mother_sku_quantity = item.quantity_final_advise;
                                    new_data.mother_sku_warehouse_code = item.bailun_sku_warehouse_code;
                                    new_datas.Add(new_data);
                                }

                                if (new_datas != null && new_datas.Count >= 1)
                                {
                                    // 写入子sku分摊结果
                                    var storage_capacity_lack_quantity = item.quantity_final_advise - new_datas.Sum(s => s.quantity_final_advise);
                                    foreach (var itemNewData in new_datas)
                                    {
                                        itemNewData.storage_capacity_lack_quantity = storage_capacity_lack_quantity;
                                        itemNewData.quantity_final_advise = Math.Max(0, itemNewData.quantity_final_advise);
                                        datas.Add(itemNewData);
                                    }
                                }
                            }
                        }
                    }
                }
                
            }

            // 再查询需要监控库容的非子母sku
            var dc_base_storage_capacity_configs = _connection.Query<dc_base_storage_capacity_config>(" select * from dc_base_storage_capacity_config where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
            {
                bailun_sku_warehouse_codes = not_mother_datas.Select(s => s.bailun_sku_warehouse_code).ToList()
            }).ToList();

            if (dc_base_storage_capacity_configs != null && dc_base_storage_capacity_configs.Count >= 1)
            {
                // 查询这些sku的库容
                // 查询这些子sku的库容
                var skuStorageCapacityList = _connection.Query<dc_base_storage_capacity>(" select * from dc_base_storage_capacity where bailun_sku_warehouse_code in @bailun_sku_warehouse_codes ", new
                {
                    bailun_sku_warehouse_codes = dc_base_storage_capacity_configs.Select(s => s.bailun_sku_warehouse_code).ToList()
                }).Where(s => s.stock > 0).ToList();

                // 把需要监控库容的按库容调整
                foreach (var item in not_mother_datas.Where(s => dc_base_storage_capacity_configs.Any(v => s.bailun_sku_warehouse_code == v.bailun_sku_warehouse_code)))
                {

                    var storageCapacity = (int)Math.Ceiling((skuStorageCapacityList.FirstOrDefault(s => s.bailun_sku_warehouse_code == item.bailun_sku_warehouse_code) ?? new dc_base_storage_capacity()).stock);
                    var skuTransit = _connection.QueryFirstOrDefault<dc_mid_transit>(" select * from dc_mid_transit where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                    {
                        bailun_sku = item.bailun_sku,
                        warehouse_code = item.warehouse_code
                    }) ?? new dc_mid_transit();
                    // 扣除采购在途，调拨采购临时，调拨暂存，调拨计划
                    storageCapacity = Math.Max(0, (storageCapacity - skuTransit.quantity_purchase - skuTransit.quantity_transfer_temporary_storage - skuTransit.quantity_transfer_temp_schedule - skuTransit.quantity_transfer_plan));

                    var quantity_final_advise = item.quantity_final_advise;
                    item.storage_capacity_quantity = storageCapacity;
                    item.quantity_final_advise = Math.Min(quantity_final_advise, storageCapacity);
                    item.storage_capacity_lack_quantity = Math.Max(0, quantity_final_advise - item.quantity_final_advise);
                    datas.Add(item);
                }

                // 把不需要监控库容的补回去
                datas.AddRange(not_mother_datas.Where(s => !dc_base_storage_capacity_configs.Any(v => s.bailun_sku_warehouse_code == v.bailun_sku_warehouse_code)));
            }
            else
            {
                datas.AddRange(not_mother_datas);
            }


            return datas;
        }

        public static void CopyTurnoverData(int advId)
        {
            _connection.Execute(@"update dc_auto_purchase_advise_detailed as t1,
dc_auto_turnover as t2,
dc_base_sku as t3
set t1.`fixed_unit_price` = t3.unit_price,
  t1.`fixed_history_sevenday_sales`=t2.history_sevenday_sales ,
  t1.`fixed_history_fourteenday_sales`=t2.history_fourteenday_sales,
  t1.`fixed_history_thirtyday_sales`=t2.history_thirtyday_sales,
  t1.`fixed_daily_weighted_sales` =t2.daily_weighted_sales,
  t1.`fixed_quantity_safe_inventory`=t2.quantity_safe_inventory ,
  t1.`fixed_quantity_out_stock` =t2.quantity_out_stock,
  t1.`fixed_turnover_sales`=t2.turnover_sales ,
  t1.`fixed_turnover_date_sales`=0,
  t1.`fixed_sales_explain_details` =t2.sales_details,
  t1.`fixed_turnover_inbound` =t2.turnover_inbound,
  t1.`fixed_quantity_inventory`=t2.quantity_inventory ,
  t1.`fixed_moq`= t2.quantity_minimum_order,
  t1.`fixed_stock_up_days`=t2.stock_up_days ,
  t1.`fixed_stock_up_sales`=t2.stock_up_sales ,
  t1.`fixed_dc_auto_sales_forecast_title` = t2.dc_auto_sales_forecast_title
	where t1.main_id=@advId and t1.bailun_sku = t2.bailun_sku and t1.warehouse_code =t2.warehouse_code 
	and t1.bailun_sku =t3.bailun_sku
", new { advId });

            // 周转期的预测销量需要自行计算
            var datas = MyMySqlConnection._connection.Query<dc_auto_purchase_advise_detailed>(" select * from dc_auto_purchase_advise_detailed where main_id=@main_id ", new { main_id = advId }, commandTimeout: 0).AsList();
            if (datas != null)
            {
                foreach (var item in datas)
                {
                    try
                    {
                        item.fixed_stock_up_days = Math.Max((int)(item.fixed_stock_up_days ?? 0M) - 1, 0);
                        item.fixed_turnover_date_sales = (int)item.fixed_sales_explain_details.ToObj<List<decimal>>()[item.turnover_days + 7 - 1];
                        _connection.Execute("update dc_auto_purchase_advise_detailed set fixed_turnover_date_sales=@fixed_turnover_date_sales,fixed_stock_up_days=@fixed_stock_up_days where id=@id ", new
                        {
                            id = item.id,
                            fixed_turnover_date_sales = item.fixed_turnover_date_sales,
                            fixed_stock_up_days = item.fixed_stock_up_days
                        });
                    }
                    catch (Exception)
                    {
                    }
                }
            }

        }

        public static void ImportDetailedTemp(int mainID, DateTime date)
        {

            //RedundancyDeduction("QYBLZZ", "GZBLWH");

            //RedundancyDeduction("GZBLWH", "QYBLZZ");

            // 生成采购建议 (只补缺货的不管)
            var datas1 = _connection.Query<dc_auto_purchase_advise_detailed>(@"select * from (
select 
	t1.bailun_sku,
    t1.warehouse_code as 'source_warehouse_code',
	(case when t8.group_id=2 then 'GZBLWH' else t1.warehouse_code end ) as 'warehouse_code',
	t1.quantity_init_advise as 'quantity_init_advise', -- 原始采购建议数量 new
	(
	case 
	when t6.buyer_name in ('张莹霞') then ( case when t1.quantity_init_advise>0 then greatest(t1.quantity_init_advise,t6.moq) else 0 end) -- (张莹霞的sku取moq下单)
	when t7.sum_quantity_init_advise>=t7.max_moq then t1.quantity_init_advise-- 如果商品的下单总量达到moq了,就直接下单 
	when t7.sku_count=1 then t7.max_moq -- 如果该商品只有一个sku需要下单,则直接下一个moq
	else
		FLOOR(
		GREATEST(t1.quantity_init_advise,0) +
		(
		(ifnull(t7.max_moq,0)-GREATEST(ifnull(t7.sum_quantity_init_advise,0),0)) 
		* 
		((case when t1.history_fourteenday_sales>0 then t1.history_fourteenday_sales else 1 end ) / (case when ifnull(t7.sum_history_fourteenday_sales,0) >0 then t7.sum_history_fourteenday_sales else 1 end) ) 
		)
		)-- 如果以上条件都不满足，则计算该sku需要摊分多少moq余下的任务
	end
	) as 'quantity_final_advise', -- 计算moq之后的数量 new
	ifnull(t7.product_inner_code,'') as 'product_inner_code', -- 内部商品编码 new
	t7.sum_quantity_init_advise as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	t7.max_moq as 'goods_moq', -- 商品的moq new
	ifnull(t7.sku_codes,'') as 'good_sku_codes', -- 商品涉及的下单所有sku new
	t7.sum_history_fourteenday_sales as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	t1.history_fourteenday_sales as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	t1.turnover_days,
	t1.supplier_delivery,
	t1.inspection_delivery,
	t1.transfer_delivery,
    t1.sales_upper_limit,
    0 as 'ispush',
    (case when t1.quantity_out_stock>0 then 2 when t1.`status` then 1 else 3 end) as 'type',
    t6.suppliers_id,
    t1.quantity_out_stock,
		(
		case when t6.buyer_name in ('张莹霞','张莹霞1') and t1.warehouse_code='GZBLWH' then 1
		else 0
		end 
		) as 'purchase_type_jit',
t1.auto_replace_logistics_task_id,
tr_task.logistics_code,
tr_task.logistics_name,
tr_task.transport_type as 'task_transport_type',
tr_task.remarks as 'replace_logistics_remarks',
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage',
t1.first_order_date as 'first_order_date',
t1.first_order_bailun_order_id as 'first_order_bailun_order_id'

	from dc_auto_turnover as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
    left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join view_goods_moq as t7 on t6.product_inner_code = t7.product_inner_code and t1.warehouse_code = t7.warehouse_code and t6.suppliers_id = t7.suppliers_id
	left join dc_auto_jit_tag as t8 on t1.tag_id = t8.id
    left join dc_auto_replace_logistics_task as tr_task on t1.auto_replace_logistics_task_id = tr_task.id
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t1.gmt_modified>=@btime
   and t6.`status`=2  
		-- and (t1.history_fourteenday_sales>0 or t1.quantity_out_stock>0 )  
		and ( t5.`status`=0 or t5.`status` is null ) 
		-- and t7.sum_quantity_init_advise>0 
   and ( 
		( ( t6.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1','冯兆欣') or t1.warehouse_code not in ('GZBLWH','GZBLYS') ) and  t1.warehouse_code!='BLGZ03' )
		or
		( t1.tag_id > 0 and t8.is_eliminate = 0 )
		) -- 非jit  或者 jit 备货
	) as t1 where t1.quantity_final_advise>0
", new { main_id = mainID, btime = date }, commandTimeout: 0).ToList();

            foreach (var item in datas1)
            {
                item.bailun_sku_warehouse_code = $"{item.bailun_sku}{item.warehouse_code}";
                _connection.Insert(item);
            }

            // 凑单sku(初始建议数没有) 如果下单数只有一个，改为2 （ 只补缺货的不管 ）
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_base_sku as t2 
set t1.quantity_final_advise=2  
where t2.`status`=2 and t1.bailun_sku = t2.bailun_sku and t1.quantity_final_advise=1 and t1.quantity_init_advise<=0 and ( t2.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1') or t1.warehouse_code!='GZBLWH' ) and  t1.main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);

            // 初始建议数为1，,系统建议数为1时 改为3 （ 只补缺货的不管 ）
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_base_sku as t2 
set quantity_final_advise=3  
where t2.`status`=2 and t1.bailun_sku = t2.bailun_sku and quantity_final_advise=1 and quantity_init_advise=1 and ( t2.buyer_name not in ('赵美聪','甄杰靖','张莹霞','张莹霞1') or t1.warehouse_code!='GZBLWH' ) and  main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);

            //张莹霞1 and 广州01 去掉缺货数
            _connection.Execute(@" update dc_auto_purchase_advise_detailed as t1,
dc_mid_transit as t2 ,
dc_base_sku as t3
set t1.quantity_final_advise=GREATEST(0,t1.quantity_final_advise-t2.quantity_out_stock)  
where t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code and t1.bailun_sku=t3.bailun_sku
and t1.quantity_init_advise>=0 and ( t3.buyer_name in ('张莹霞1') and t1.warehouse_code='GZBLWH' ) and  t1.main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0);


            // 调整因为舍弃小数导致的moq不足
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(" select * from dc_auto_purchase_advise_detailed where main_id=@main_id ", new { main_id = mainID }, commandTimeout: 0).AsList();
            if (datas != null && datas.Count > 0)
            {
                foreach (var itemGoods in datas.GroupBy(s => new { s.suppliers_id, s.product_inner_code, s.warehouse_code }))
                {

                    // 判断总建议采购数是否达到了moq
                    var difference = itemGoods.Max(s => s.goods_moq) - itemGoods.Sum(s => s.quantity_final_advise);
                    if (difference > 0)
                    {
                        // 把最大的那条记录找出来，加给他
                        var max_data = itemGoods.OrderByDescending(s => s.history_fourteenday_sales).FirstOrDefault();
                        _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_final_advise=@quantity_final_advise where id=@id ", new
                        {
                            quantity_final_advise = max_data.quantity_final_advise + difference,
                            id = max_data.id
                        });
                    }
                    else if (difference < 0)
                    {
                        // 由于上面的补单规则，会导致超出moq . 有超出的部分，把补单的（初始建议数为负数）按销量排序，逐个消减
                        var abatement = 0 - difference;
                        foreach (var item in itemGoods.Where(s => s.quantity_init_advise < 0).OrderBy(s => s.goods_history_fourteenday_sales))
                        {
                            if (abatement >= item.quantity_final_advise)
                            {
                                _connection.Execute(" delete from dc_auto_purchase_advise_detailed  where id=@id ", new { id = item.id });
                                abatement -= item.quantity_final_advise;
                            }
                            else
                            {
                                _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_final_advise=@quantity_final_advise where id=@id ", new { id = item.id, quantity_final_advise = item.quantity_final_advise - abatement });
                                abatement = 0;
                            }

                            if (abatement <= 0)
                            {
                                break;
                            }
                        }
                    }
                }
            }



            //ImportShoujiShortageDetailed(mainID);



        }

        /// <summary>
        /// 把流水号装换成4位数字符串形式
        /// </summary>
        /// <param name="num"></param>
        /// <returns></returns>
        public static string GetIntStr(int num)
        {
            var intLength = num.ToString().Length;
            var intStr = num.ToString();
            for (int i = intLength; i < 3; i++)
            {
                intStr = "0" + intStr;
            }
            return intStr;
        }
        /// <summary>
        /// 生成一个流水号
        /// </summary>
        public static string GenerateOrderNo()
        {
            var now = DateTime.Now;
            string dataStr = now.ToString("yyyyMMdd");
            //查找当前最大的订单号
            var maxNo = purchase_advise.MaxNo();

            //取出订单号的流水部分
            if (string.IsNullOrWhiteSpace(maxNo))
                return dataStr + "001";

            return dataStr + GetIntStr(int.Parse(maxNo.Substring(8, 3)) + 1);
        }

        /// <summary>
        /// 导入缺货明细 张莹霞
        /// 至少下5个
        /// </summary>
        /// <param name="mainID"></param>
        public static int ImportJITShortageDetailed(int mainID)
        {
            var time = DateTime.Now;

            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = time, no = purchase_advise.GenerateOrderNo(), buy_sys_plan_no = "ImportJITShortageDetailed", title = $" jit 缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }

            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@"select 
	t1.bailun_sku,
	'GZBLWH' as 'warehouse_code',
	( sum(t7.quantity_out_stock) - ( sum((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + sum(t1.quantity_transfer) + sum(t7.quantity_purchase) )   ) as 'quantity_init_advise', -- 原始采购建议数量 new
    GREATEST(5,
	( sum(t7.quantity_out_stock) - ( sum((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + sum(t1.quantity_transfer) + sum(t7.quantity_purchase) )   )
    ) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
    2 as 'purchase_type_jit',
    sum(t7.quantity_out_stock_aliexpress) as 'quantity_out_stock_aliexpress',
    sum(t7.quantity_out_stock_amazon) as 'quantity_out_stock_amazon',
    sum(t7.quantity_out_stock_other) as 'quantity_out_stock_other',
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t6.`status`=2 and  ( t6.buyer_name in ('张莹霞') and t1.warehouse_code in ('GZBLWH','GZBLYS') ) 
    and ( t5.`status`=0 or t5.`status` is null )
	GROUP BY t1.bailun_sku
	HAVING ( sum((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + sum(t7.quantity_transfer) + sum(t7.quantity_purchase) < sum(t7.quantity_out_stock) )
", new { main_id = mainID }, commandTimeout: 0).ToList();


            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }

        /// <summary>
        /// 写入采购建议
        /// </summary>
        public static void WritePurchaseAdvise(List<dc_auto_purchase_advise_detailed> datas)
        {
            foreach (var item in datas)
            {
                item.bailun_sku_warehouse_code = $"{item.bailun_sku}{item.warehouse_code}";
                item.create_time = DateTime.Now;
                // 查询是否有未过期 未推送的数据
                var oldCreateDate = _connection.QuerySingleOrDefault<DateTime?>(" select create_time from dc_auto_purchase_advise_detailed  where bailun_sku_warehouse_code = @bailun_sku_warehouse_code and  status=0 and ispush=0 limit 1 ", new
                {
                    bailun_sku_warehouse_code = item.bailun_sku_warehouse_code
                }, commandTimeout: 0);
                if (oldCreateDate != null)
                {
                    item.create_time = oldCreateDate;
                }

                _connection.Execute(" update  dc_auto_purchase_advise_detailed set status=-1 where bailun_sku_warehouse_code = @bailun_sku_warehouse_code ", new
                {
                    bailun_sku_warehouse_code = item.bailun_sku_warehouse_code
                }, commandTimeout: 0);
                _connection.Insert(item);
            }
        }

        public class old_purchase_advise_detailed
        {
            public int id { get; set; }
            public int main_id { get; set; }
        }

        /// <summary>
        /// 导入缺货明细 张莹霞1
        /// </summary>
        /// <param name="mainID"></param>
        public static int ImportJITShortageDetailed1(int mainID)
        {
            var time = DateTime.Now;

            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = time, no = purchase_advise.GenerateOrderNo(), buy_sys_plan_no = "ImportJITShortageDetailed1", title = $" jit 缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }


            // 采购员为 张莹霞 的商品 按缺货数量下单
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	'GZBLWH' as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	( sum(t7.quantity_out_stock) - ( sum((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) )   ) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
    2 as 'purchase_type_jit',
sum(t7.quantity_out_stock_aliexpress) as 'quantity_out_stock_aliexpress',
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t6.`status`=2 and  ( t6.buyer_name in ('张莹霞1') and t1.warehouse_code in ('GZBLWH','GZBLYS') ) 
    and ( t5.`status`=0 or t5.`status` is null )
	GROUP BY t1.bailun_sku
	HAVING ( sum((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) < sum(t7.quantity_out_stock) ) ", new { main_id = mainID }, commandTimeout: 0).ToList();


            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }

        /// <summary>
        /// 导入缺货明细 (服装)
        /// 01 and 03 
        /// </summary>
        /// <param name="mainID"></param>
        public static int ImportFuZhuangShortageDetailed(int mainID)
        {
            var now = DateTime.Now;

            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = now, no = purchase_advise.GenerateOrderNo(), type = 1, buy_sys_plan_no = "ImportFuZhuangShortageDetailed", title = $" 无库仓缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	t1.warehouse_code as 'source_warehouse_code',
    t1.warehouse_code as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	( (t7.quantity_out_stock) - ( ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + (t1.quantity_transfer) + (t7.quantity_purchase) )   ) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t7.bailun_order_ids,
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t6.`status`=2 and  ( t6.buyer_name in ('赵美聪','甄杰靖','赵美聪1','秦振荣','李华娟') and t1.warehouse_code in ('BLGZ03','GZBLWH') ) 
    and ( t5.`status`=0 or t5.`status` is null )
	and ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end ) + t7.quantity_transfer + t7.quantity_purchase < t7.quantity_out_stock) ", new { main_id = mainID }, commandTimeout: 0).ToList();

            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }

        /// <summary>
        /// 义乌无库仓
        /// </summary>
        /// <param name="mainID"></param>
        /// <returns></returns>
        public static int ImportYiWuShortageDetailed(int mainID)
        {
            var now = DateTime.Now;

            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = now, no = purchase_advise.GenerateOrderNo(), type = 1, buy_sys_plan_no = "ImportYiWuShortageDetailed", title = $" 义乌仓缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	t1.warehouse_code as 'source_warehouse_code',
    t1.warehouse_code as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	( (t7.quantity_out_stock) - ( ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + (t1.quantity_transfer) + (t7.quantity_purchase) )   ) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t7.bailun_order_ids,
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t6.`status`=2 and  ( t1.warehouse_code in ('YWWH01') ) 
    and ( t5.`status`=0 or t5.`status` is null )
	and ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end ) + t7.quantity_transfer + t7.quantity_purchase < t7.quantity_out_stock)", new { main_id = mainID }, commandTimeout: 0).ToList();

            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }

        /// <summary>
        /// 赵美聪 独立站
        /// 推送到广州01仓
        /// </summary>
        /// <param name="mainID"></param>
        /// <returns></returns>
        public static int ImportDuLiShortageDetailed(int mainID)
        {
            var now = DateTime.Now;

            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = now, no = purchase_advise.GenerateOrderNo(), type = 0, buy_sys_plan_no = "ImportDuLiShortageDetailed", title = $"赵美聪独立站缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	t1.warehouse_code as 'source_warehouse_code',
    'GZBLWH' as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	( (t7.quantity_out_stock) - ( ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + (t1.quantity_transfer) + (t7.quantity_purchase) )   ) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t7.bailun_order_ids,
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t6.`status`=2 and  ( t6.buyer_name in ('赵美聪_独立站') ) 
    and ( t5.`status`=0 or t5.`status` is null )
	and ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end ) + t7.quantity_transfer + t7.quantity_purchase < t7.quantity_out_stock)", new { main_id = mainID }, commandTimeout: 0).ToList();

            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }

        /// <summary>
        /// 刷新缺货字段
        /// </summary>
        public static void ResetShortageField()
        {
            try
            {
                _connection.Execute(@"
set session transaction isolation level read uncommitted;
start transaction;

insert into dc_mid_transit(bailun_sku, warehouse_code, quantity_out_stock,gmt_out_stock_modified, quantity_out_stock_aliexpress,quantity_out_stock_amazon,quantity_out_stock_other)
SELECT
      t1.bailun_sku as bailunSku,
      t1.warehouse_code as warehouseCode,
    t1.bailun_sku_quantity_ordered - t1.bailun_sku_quantity_pushed - t2.usable_stock - t2.occupy_stock + ifnull(t3.safe_stock, 0) as quantityOutStock,
		now(),
    t1.aliexpress_quantity as aliexpressQuantity,
     t1.amazon_quantity as amazonQuantity,
		 t1.other_quantity as other_quantity
      FROM
      (
      SELECT
      bailun_sku,warehouse_code,SUM(bailun_sku_quantity_ordered) AS bailun_sku_quantity_ordered,SUM(bailun_sku_quantity_pushed) AS bailun_sku_quantity_pushed,
      sum(case when platform_type = 'Aliexpress' then bailun_sku_quantity_ordered - bailun_sku_quantity_pushed else 0 end) as aliexpress_quantity,
      sum(case when platform_type = 'Amazon' then bailun_sku_quantity_ordered - bailun_sku_quantity_pushed else 0 end) as amazon_quantity,
			sum(case when platform_type not in ( 'Amazon','Aliexpress') then bailun_sku_quantity_ordered - bailun_sku_quantity_pushed else 0 end) as other_quantity
      FROM
      dc_base_oms_sku_30
      WHERE
          bailun_order_status = 'Handling'
          and bailun_interception_status in('None','Failed')
          and warehouse_code != ''
          and platform_type != 'FBA'
          and company_id = 1
          and has_delete = 0
          and has_scalp = 0
          and has_buyer_remark = 0
          and has_platsku_remark = 0
          and has_fba_s = 0
          and has_innersale = 0
          and paid_time >= '2019-03-01 12:00:00'
          and bailun_sku_quantity_ordered > bailun_sku_quantity_shipped
      GROUP BY
      bailun_sku,
      warehouse_code
      ) t1 INNER JOIN dc_base_stock t2 ON t1.bailun_sku=t2.bailun_sku and t1.warehouse_code=t2.warehouse_code
      LEFT JOIN dc_auto_jit_inventory t3 ON t1.bailun_sku=t3.bailun_sku and t1.warehouse_code=t3.warehouse_code and t3.status = 1
    WHERE (t1.bailun_sku_quantity_ordered - t1.bailun_sku_quantity_pushed - t2.usable_stock - t2.occupy_stock + ifnull(t3.safe_stock, 0)) >= 0
                
                ON DUPLICATE KEY  UPDATE  quantity_out_stock = values(quantity_out_stock) ,gmt_out_stock_modified =  values(gmt_out_stock_modified), quantity_out_stock_amazon = values(quantity_out_stock_amazon),quantity_out_stock_other = VALUES(quantity_out_stock_other)
", commandTimeout: 0);

                var time = _connection.QueryFirstOrDefault<DateTime>(" select  max(gmt_out_stock_modified) from  dc_mid_transit ");
                _connection.Execute(@" set session transaction isolation level read uncommitted;
start transaction; 
update  dc_mid_transit set quantity_out_stock =0,quantity_out_stock_aliexpress = 0,quantity_out_stock_other = 0,quantity_out_stock_amazon= 0 where gmt_out_stock_modified < @time ", new
                {
                    time = time
                });
            }
            catch (Exception ex)
            {

                throw new Exception("缺货计算异常：" + ex.Message);
            }

        }

        /// <summary>
        /// 服装增量采购
        /// 03
        /// 改为全量推
        /// </summary>
        /// <param name="mainID"></param>
        /// <returns></returns>
        public static int ImportFuZhuangShortageIncreaseDetailed_bak(int mainID)
        {
            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            // 查询上一次推送建议的时间
            var last_date = _connection.QueryFirstOrDefault<DateTime>(" select create_time from dc_auto_purchase_advise where type=1 order by create_time desc limit 1 ");
            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = DateTime.Now, no = purchase_advise.GenerateOrderNo(), type = 1, buy_sys_plan_no = "ImportFuZhuangShortageIncreaseDetailed" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	t1.warehouse_code as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	t8.quantity as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t8.bailun_order_ids
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
	left join ( select bailun_sku,sum(bailun_sku_quantity_ordered) as 'quantity',GROUP_CONCAT(bailun_order_id) as 'bailun_order_ids' from  dc_base_oms_sku where warehouse_code in ('BLGZ03') and website='us' and gmt_create>@time and has_scalp=0 and has_buyer_remark = 0
          and has_platsku_remark = 0 GROUP BY bailun_sku ) as t8  on t1.bailun_sku  =t8.bailun_sku
	where t6.`status`=2 and  ( t6.buyer_name in ('赵美聪','甄杰靖','赵美聪1','秦振荣','李华娟') and t1.warehouse_code in ('BLGZ03') ) 
    -- and t6.suppliers_name not in ('深圳市龙岗区平湖欧妮菲制衣厂','深圳市福田区曼戈制衣厂')
    and ( t5.`status`=0 or t5.`status` is null ) and t8.quantity>0 ", new { main_id = mainID, time = last_date }, commandTimeout: 0).ToList();

            if (datas != null)
            {
                foreach (var item in datas)
                {
                    item.bailun_sku_warehouse_code = $"{item.bailun_sku}{item.warehouse_code}";
                    _connection.Insert(item);
                }
            }
            return mainID;
        }

        /// <summary>
        /// 不能之下新订单了
        /// </summary>
        /// <param name="mainID"></param>
        /// <returns></returns>
        public static int ImportFuZhuangShortageIncreaseDetailed_bak2(int mainID)
        {
            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            // 查询上一次推送建议的时间
            var last_date = _connection.QueryFirstOrDefault<DateTime>(" select create_time from dc_auto_purchase_advise where type=1 order by create_time desc limit 1 ");
            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = DateTime.Now, no = purchase_advise.GenerateOrderNo(), type = 1, buy_sys_plan_no = "ImportFuZhuangShortageIncreaseDetailed" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	t1.warehouse_code as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	t8.quantity as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t8.bailun_order_ids
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
	left join ( select bailun_sku,sum(bailun_sku_quantity_ordered) as 'quantity',GROUP_CONCAT(bailun_order_id) as 'bailun_order_ids' from  dc_base_oms_sku where warehouse_code in ('BLGZ03') and gmt_create>@time and has_scalp=0 and has_buyer_remark = 0
          and has_platsku_remark = 0 GROUP BY bailun_sku ) as t8  on t1.bailun_sku  =t8.bailun_sku
	where t6.`status`=2 and  ( t6.buyer_name in ('赵美聪','甄杰靖','赵美聪1','秦振荣','李华娟') and t1.warehouse_code in ('BLGZ03') ) 
    -- and t6.suppliers_name not in ('深圳市龙岗区平湖欧妮菲制衣厂','深圳市福田区曼戈制衣厂')
    and ( t5.`status`=0 or t5.`status` is null ) and t8.quantity>0
	 ", new { main_id = mainID, time = last_date }, commandTimeout: 0).ToList();
            if (datas != null)
            {
                foreach (var item in datas)
                {
                    item.bailun_sku_warehouse_code = $"{item.bailun_sku}{item.warehouse_code}";
                    _connection.Insert(item);
                }
            }
            return mainID;
        }

        /// <summary>
        /// 服装增量采购
        /// </summary>
        /// <param name="mainID"></param>
        /// <returns></returns>
        public static int ImportFuZhuangShortageIncreaseDetailed(int mainID)
        {
            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            // 查询上一次推送建议的时间
            var last_date = _connection.QueryFirstOrDefault<DateTime>(" select create_time from dc_auto_purchase_advise where type=1 order by create_time desc limit 1 ");
            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = DateTime.Now, no = purchase_advise.GenerateOrderNo(), type = 1, buy_sys_plan_no = "ImportFuZhuangShortageIncreaseDetailed", title = $" 无库存缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@"select 
	t1.bailun_sku,
	t1.warehouse_code as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	t8.quantity as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t8.bailun_order_ids,
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	left join ( select bailun_sku,sum(bailun_sku_quantity_ordered) as 'quantity',GROUP_CONCAT(bailun_order_id) as 'bailun_order_ids' from  dc_base_oms_sku where warehouse_code in ('BLGZ03') and gmt_create>@time and has_scalp=0 and has_buyer_remark = 0
          and has_platsku_remark = 0 GROUP BY bailun_sku ) as t8  on t1.bailun_sku  =t8.bailun_sku
	where t6.`status`=2 and  ( t6.buyer_name in ('赵美聪','甄杰靖','赵美聪1','秦振荣','李华娟') and t1.warehouse_code in ('BLGZ03') ) 
    -- and t6.suppliers_name not in ('深圳市龙岗区平湖欧妮菲制衣厂','深圳市福田区曼戈制衣厂')
    and ( t5.`status`=0 or t5.`status` is null ) and t8.quantity>0
	", new { main_id = mainID, time = last_date }, commandTimeout: 0).ToList();

            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }

        /// <summary>
        /// 导入缺货明细 (手机)
        /// </summary>
        /// <param name="mainID"></param>
        public static int ImportShoujiShortageDetailed(int mainID)
        {
            var time = DateTime.Now;

            // 计算之前先刷新一遍缺货，一面产生间隙（8点56进系统的单，没有算到缺货里面，下午1点那波也不会算上他）
            //ResetShortageField();

            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = time, no = purchase_advise.GenerateOrderNo(), buy_sys_plan_no = "ImportShoujiShortageDetailed", title = $" 手机缺货 {DateTime.Now.ToString("HH:mm")}" }) ?? 0;
            }
            var datas = _connection.Query<dc_auto_purchase_advise_detailed>(@" select 
	t1.bailun_sku,
	t1.warehouse_code as 'warehouse_code',
	-1 as 'quantity_init_advise', -- 原始采购建议数量 new
	( (t7.quantity_out_stock) - ( ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end )) + (t1.quantity_transfer) + (t7.quantity_purchase) )   ) as 'quantity_final_advise', -- 计算moq之后的数量 new
	t6.product_inner_code as 'product_inner_code', -- 内部商品编码 new
	0 as 'goods_quantity_init_advise', -- 商品本次的建议下单数量 new
	0 as 'goods_moq', -- 商品的moq new
	0 as 'good_sku_codes', -- 商品涉及的下单所有sku new
	0 as 'goods_history_fourteenday_sales', -- 商品的最近14日总日均销量
	-1 as 'history_fourteenday_sales', -- sku的最近14日日均
	0 as 'quantity_actual', -- 真实下单数量
	@main_id as 'main_id',
	t2.forecast_formula as 'forecast_formula',
	t2.fit_forecast_formula as 'fit_forecast_formula',
	0 as 'turnover_days',
	0 as 'supplier_delivery',
	0 as inspection_delivery,
	0 as transfer_delivery,
  0 as sales_upper_limit,
   0 as 'ispush',
   2 as 'type',
   t6.suppliers_id,
    t7.quantity_out_stock,
0 as 'purchase_type_jit',
t9.quantity_transfer_order as 'quantity_transfer_order',
t9.quantity_transfer_temp_schedule as 'quantity_transfer_temp_schedule',
t9.quantity_transfer_temporary_storage as 'quantity_transfer_temporary_storage'
	from dc_base_stock as t1
	left join dc_auto_sales as t2 on t1.bailun_sku = t2.bailun_sku and t1.warehouse_code = t2.warehouse_code
  left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
	left join dc_base_sku as t6 on t1.bailun_sku = t6.bailun_sku
	left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_mid_transit as t9 on t1.bailun_sku = t9.bailun_sku and t1.warehouse_code = t9.warehouse_code
	where t6.`status`=2 and  ( t6.buyer_name in ('陈嘉雯1','黎艳漫') and t1.warehouse_code in ('BLGZ03') ) 
    and ( t5.`status`=0 or t5.`status` is null )
	and ((case when t7.quantity_out_stock>0 then 0 else  (t1.usable_stock+t1.occupy_order) end ) + t7.quantity_transfer + t7.quantity_purchase < t7.quantity_out_stock)", new { main_id = mainID }, commandTimeout: 0).ToList();
            if (datas != null && datas.Count >= 1)
            {
                WritePurchaseAdvise(datas);
            }
            return mainID;
        }


        /// <summary>
        /// 冗余扣减
        /// </summary>
        public static void RedundancyDeduction(string warehouse_code_form, string warehouse_code_to)
        {
            var gzblwh_turnover_list = _connection.Query<Models.dc_auto_turnover>(@"
select t1.* from dc_auto_turnover as t1
left join dc_auto_config_sku_warehouse as t5 on t1.bailun_sku = t5.bailun_sku and t1.warehouse_code = t5.warehouse_code
where ( t5.`status`=0 or t5.`status` is null ) and t1.quantity_begin_advise>0 and t1.warehouse_code=@warehouse_code_to
", new { warehouse_code_to }, commandTimeout: 0);

            if (gzblwh_turnover_list != null && gzblwh_turnover_list.Count() >= 1)
            {
                foreach (var item in gzblwh_turnover_list)
                {
                    Models.dc_auto_turnover qyblzz_data = _connection.QuerySingleOrDefault<Models.dc_auto_turnover>(" select * from dc_auto_turnover where warehouse_code=@warehouse_code_form and bailun_sku=@bailun_sku ", new { warehouse_code_form, item.bailun_sku });
                    if (qyblzz_data != null && qyblzz_data.quantity_begin_advise < 0)
                    {
                        // 清远03的冗余够用了
                        if ((0 - qyblzz_data.quantity_begin_advise) >= item.quantity_begin_advise)
                        {
                            _connection.Execute(" update dc_auto_turnover set is_modify_quantity_init_advise=1,original_quantity_init_advise=@original_quantity_init_advise,quantity_init_advise=@quantity_init_advise where id=@id",
                                new { original_quantity_init_advise = item.quantity_init_advise, quantity_init_advise = 0, id = item.id });
                        }
                    }
                }
            }
        }

        public class shortage_sku_list_dto
        {
            public string bailun_sku { get; set; }
            public string buyer_name { get; set; }
        }
        /// <summary>
        /// 特定平台的缺货sku
        /// </summary>
        /// <param name="platform_types">平台</param>
        /// <param name="not_buyer_names">指定采购员</param>
        /// <returns></returns>
        public static List<string> ShortageSkuList(List<string> platform_types, List<string> buyer_names)
        {
            var datas = _connection.Query<shortage_sku_list_dto>(@"
select t1.bailun_sku,t2.buyer_name from dc_base_oms_sku_30 as t1 
left join dc_base_sku as  t2 on t1.bailun_sku = t2.bailun_sku
where 1=1
and t1.company_id=1 
and  t1.has_innersale=0 
and t1.create_time>'2019-03-01 12:00:00' 
and t1.bailun_picking_status!='TotalPicked' 
and t1.bailun_order_status = 'Handling' 
and t1.has_delete=0 
and t1.has_scalp=0 
and t1.has_buyer_remark = 0
and t1.has_platsku_remark = 0 
and t1.bailun_interception_status not in ('Success','Part') and t1.platform_type!='FBA'
and platform_type in @platform_types
", new { platform_types = platform_types }, commandTimeout: 0).AsList();

            if (buyer_names != null && buyer_names.Count >= 1)
            {
                return datas.Where(s => buyer_names.Any(v => v == s.buyer_name)).Select(s => s.bailun_sku).ToList();
            }
            else
            {
                return datas.Select(s => s.bailun_sku).ToList();
            }
        }

        /// <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_purchase_advise> List(dc_auto_purchase_advise_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_purchase_advise>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();

                var sql = @"select * from dc_auto_purchase_advise as t1 where 1=1 ";

                if (m.has_push == true)
                {
                    sql += $" and t1.push_time is not null ";
                }
                if (m.has_push == false)
                {
                    sql += $" and t1.push_time is null ";
                }

                if (m.is_outofstock != null)
                {
                    sql += $" and t1.is_outofstock = " + (m.is_outofstock == true ? 1 : 0);
                }



                if (m.start_date != null)
                {
                    sql += " and t1.create_time >= @start_date ";
                    parameters.Add("start_date", m.start_date.Value);
                }

                if (m.end_date != null)
                {
                    sql += " and t1.create_time<= @end_date ";
                    parameters.Add("end_date", m.end_date.Value);
                }

                sql += " order by id desc ";

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <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_purchase_advise_detailed_dto> DetailList(dc_auto_purchase_advise_detailed_search_dto m, int offset, int limit, ref int total, string order = "", string sort = "")
        {
            var list = new List<dc_auto_purchase_advise_detailed_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();
                string sql = "";
                if (m.isSum)
                {
                    sql = @"
select
sum(t5.quantity_safe_inventory) as 'quantity_safe_inventory', -- 安全库存
sum(t1.quantity_out_stock) as 'quantity_out_stock', -- 缺货 
sum(t5.quantity_transfer) as 'quantity_transfer', -- 调拨在途 
sum(t5.quantity_purchase) as 'quantity_purchase', -- 采购在途 
sum(t5.quantity_inventory) as 'quantity_inventory', -- 库存数
sum(t3.unit_price * (case when t1.quantity_actual>0 then t1.quantity_actual else t1.quantity_final_advise end ) ) as 'quantity_final_advise_price', -- 采购金额 
sum(t5.turnover_days) as 'turnover_days', -- 周转天数

sum(t1.quantity_init_advise) as 'quantity_init_advise', 
sum(t1.quantity_final_advise) as 'quantity_final_advise', 
sum(t1.quantity_actual) as 'quantity_actual', 
sum(t1.history_fourteenday_sales) as 'history_fourteenday_sales', 
sum(t1.goods_quantity_init_advise) as 'goods_quantity_init_advise', 
sum(t1.goods_history_fourteenday_sales) as 'goods_history_fourteenday_sales'
from dc_auto_purchase_advise_detailed as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_auto_purchase_advise as t4 on t1.main_id = t4.id
left join dc_auto_turnover as t5 on t1.warehouse_code = t5.warehouse_code and t1.bailun_sku = t5.bailun_sku 
where t1.status=0 ";
                }
                else
                {
                    sql = @"
select 
t1.*,(case when t2.hq_type='fba仓' then t2.bailun_account else t2.warehouse_name end) as 'warehouse_name',t3.sku_title_cn as 'sku_name',t3.buyer_name,
t4.create_time as 'calculation_time',
(t5.quantity_safe_inventory) as 'quantity_safe_inventory', -- 安全库存 
(t5.daily_weighted_sales) as 'daily_weighted_sales', -- 日均加权 
(t7.quantity_out_stock) as 'realtime_quantity_out_stock', -- 实时缺货 
(t5.quantity_transfer) as 'quantity_transfer', -- 调拨在途 
(t5.quantity_purchase) as 'quantity_purchase', -- 采购在途 
(t5.quantity_inventory) as 'quantity_inventory', -- 库存数
t3.suppliers_link,
(t3.unit_price * (case when t1.quantity_actual>0 then t1.quantity_actual else t1.quantity_final_advise end ) ) as 'quantity_final_advise_price', -- 采购金额
t3.suppliers_name,
(case when t2_s.hq_type='fba仓' then t2_s.bailun_account else t2_s.warehouse_name end) as 'source_warehouse_code_name',
t5.history_sevenday_sales as 't_history_sevenday_sales',
t5.history_fourteenday_sales as 't_history_fourteenday_sales',
t5.history_thirtyday_sales as 't_history_thirtyday_sales',

t2.hq_type,

t5.abroad_inbound_config_delivery,

t8.from_warehouse_code as 'transfer_warehouse_from_code',
t8.from_warehouse_name as 'transfer_warehouse_from_name',

(t1.history_fourteenday_sales - t5.forecast_fourteenday_sales) as 'sudden_increase' -- 突增关注
from dc_auto_purchase_advise_detailed as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join dc_base_warehouse as t2_s on t1.source_warehouse_code = t2_s.warehouse_code
left join dc_base_sku as t3 on t1.bailun_sku = t3.bailun_sku
left join dc_auto_purchase_advise as t4 on t1.main_id = t4.id
left join dc_auto_turnover as t5 on t1.warehouse_code = t5.warehouse_code and t1.bailun_sku = t5.bailun_sku 
left join dc_mid_transit as t7 on t1.bailun_sku = t7.bailun_sku and t1.warehouse_code = t7.warehouse_code
left join dc_aims_transfer_warehouse_log as t8 on t1.bailun_sku = t8.bailun_sku and t1.warehouse_code = t8.to_warehouse_code
where 1=1 ";
                }

                if (!string.IsNullOrWhiteSpace(m.product_inner_code))
                {
                    sql += " and t3.product_inner_code in @product_inner_codes ";
                    parameters.Add("product_inner_codes", m.product_inner_code.Split(',').ToList());
                }
                if (m.type != null)
                {
                    sql += " and t1.`type`= " + m.type;
                }
                if (m.ispush != null)
                {
                    sql += " and t1.ispush=" + (m.ispush == true ? 1 : 0);
                }
                if (m.is_storage_capacity_lack == 1)
                {
                    sql += " and t1.storage_capacity_lack_quantity>0 ";
                }
                if (m.is_storage_capacity_lack == 0)
                {
                    sql += " and t1.storage_capacity_lack_quantity<=0 ";
                }
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t2.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t2.area_id=" + m.warehousearea;
                }
                if (m.start_date != null)
                {
                    sql += " and t4.create_time >= @start_date ";
                    parameters.Add("start_date", m.start_date.Value.ToString("yyyy-MM-dd HH:mm:00"));
                }

                if (m.end_date != null)
                {
                    sql += " and t4.create_time<= @end_date ";
                    parameters.Add("end_date", m.end_date.Value.ToString("yyyy-MM-dd HH:mm:59"));
                }
                if (m.main_id > 0)
                {
                    sql += $" and t1.main_id = @main_id ";
                    parameters.Add("main_id", m.main_id);
                }
                if (!string.IsNullOrWhiteSpace(m.main_ids))
                {
                    sql += $" and t1.main_id in @main_ids ";
                    parameters.Add("main_ids", m.main_ids.Split(',').Select(s => int.Parse(s)).ToList());
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += $" and t1.bailun_sku in @bailun_skus ";
                    parameters.Add("bailun_skus", m.sku.Split(',').ToList());
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                if (!string.IsNullOrWhiteSpace(m.supplier_name))
                {
                    sql += " and t3.suppliers_name =@suppliers_name ";
                    parameters.Add("suppliers_name", m.supplier_name);
                }
                if (!string.IsNullOrWhiteSpace(m.order_no))
                {
                    sql += " and t4.`no` =@order_no ";
                    parameters.Add("order_no", m.order_no);
                }
                if (m.is_delete > -2)
                {
                    sql += " and t1.`status` =@is_delete ";
                    parameters.Add("is_delete", m.is_delete);
                }

                if (!string.IsNullOrWhiteSpace(m.purchase_user))
                {
                    sql += " and t3.buyer_name =@buyer_name ";
                    parameters.Add("buyer_name", m.purchase_user);
                }
                if (m.quantity_final_advise_price_min > 0 && m.quantity_final_advise_price_max > 0)
                {
                    sql += @" and EXISTS (
select * from view_purchase_advise_supplier_price as t_price where t_price.main_id=t1.main_id and t1.suppliers_id=t_price.suppliers_id and t1.warehouse_code = t_price.warehouse_code and t_price.price>@quantity_final_advise_price_min and t_price.price<=@quantity_final_advise_price_max
) ";
                    parameters.Add("quantity_final_advise_price_min", m.quantity_final_advise_price_min);
                    parameters.Add("quantity_final_advise_price_max", m.quantity_final_advise_price_max);
                }
                else
                {
                    if (m.quantity_final_advise_price_max > 0)
                    {
                        sql += @" and EXISTS (
select * from view_purchase_advise_supplier_price as t_price where t_price.main_id=t1.main_id and t1.suppliers_id=t_price.suppliers_id and t1.warehouse_code = t_price.warehouse_code  and t_price.price<=@quantity_final_advise_price_max
) ";
                        parameters.Add("quantity_final_advise_price_max", m.quantity_final_advise_price_max);
                    }
                    if (m.quantity_final_advise_price_min > 0)
                    {
                        sql += @" and EXISTS (
select * from view_purchase_advise_supplier_price as t_price where t_price.main_id=t1.main_id and t1.suppliers_id=t_price.suppliers_id and t1.warehouse_code = t_price.warehouse_code and t_price.price>@quantity_final_advise_price_min 
) ";
                        parameters.Add("quantity_final_advise_price_min", m.quantity_final_advise_price_min);
                    }
                }
                if (m.purchase_type_jit != null)
                {
                    sql += " and t1.purchase_type_jit =@purchase_type_jit ";
                    parameters.Add("purchase_type_jit", m.purchase_type_jit);
                }
                if (m.is_replace_logistics != null)
                {
                    if (m.is_replace_logistics == 1)
                    {
                        sql += " and t1.auto_replace_logistics_task_id >0 ";
                    }
                    else if (m.is_replace_logistics == 0)
                    {
                        sql += " and ( t1.auto_replace_logistics_task_id <=0 or t1.auto_replace_logistics_task_id is null ) ";
                    }

                }

                if (m.is_multiple_warehouse)
                {
                    sql += @"
and EXISTS (
	select * from (
	select t1.bailun_sku from dc_auto_purchase_advise_detailed as t1 
	left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
	GROUP BY t1.bailun_sku
	HAVING count(DISTINCT t2.area_name)>1
	) as t_temp where t_temp.bailun_sku=t1.bailun_sku
)";
                }



                if (m.isSum || limit > 10000)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1", parameters);

                    //设置默认排序字段
                    if (string.IsNullOrWhiteSpace(sort)) sort = "t1.quantity_final_advise";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        sql += " order by " + sort;

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

                    sql += " limit " + offset + "," + limit;
                }
                List<dc_auto_purchase_advise_detailed_dto> obj = new List<dc_auto_purchase_advise_detailed_dto>();
                if (m.data_base_type == 1)
                {
                    obj = MyMySqlConnection._connection.Query<dc_auto_purchase_advise_detailed_dto>(sql, parameters, buffered: false, commandTimeout: 0).ToList();
                }
                else
                {
                    obj = _connection.Query<dc_auto_purchase_advise_detailed_dto>(sql, parameters, buffered: false, commandTimeout: 0).ToList();
                }
                return obj.AsList();

            }
            catch (Exception)
            {
                throw;
            }
        }

        public static IEnumerable<dc_auto_purchase_advise_detailed> DetailList(int mainid)
        {
            return _connection.Query<dc_auto_purchase_advise_detailed>("select * from dc_auto_purchase_advise_detailed where main_id=@mainid ", new { mainid = mainid });
        }

        /// <summary>
        /// 采购建议明细
        /// </summary>
        /// <param name="mainid"></param>
        /// <returns></returns>
        public static IEnumerable<dc_auto_purchase_advise_detailed_buy_dto> BuyDetailList(List<int> ids, bool? ispush = null)
        {
            if (ids == null || ids.Count <= 0)
            {
                return new List<dc_auto_purchase_advise_detailed_buy_dto>();
            }
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("ids", ids);
            string sql = @" select t1.*,t2.unit_price,t3.hq_type as 'warehouse_type',t4.sendtype,t3.area_name from dc_auto_purchase_advise_detailed as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code
left join dc_auto_purchase_advise  as t4 on t1.main_id = t4.id 
where t1.id in @ids  ";
            if (ispush != null)
            {
                sql += " and ispush=@ispush ";
                parameters.Add("ispush", ispush.Value ? 1 : 0);
            }
            return MyMySqlConnection._connection.Query<dc_auto_purchase_advise_detailed_buy_dto>(sql, parameters);
        }

        /// <summary>
        /// 查询未来指定时间范围内的特殊销量数据
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="days"></param>
        /// <returns></returns>
        public static IEnumerable<dc_auto_purchase_advise_detailed_time_dto> DetailList(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_purchase_advise_detailed_time_dto>(@"select t1.*,t2.create_time from dc_auto_purchase_advise_detailed as t1 
left join dc_auto_purchase_advise as t2 on t1.main_id = t2.id where t1.bailun_sku=@bailun_sku and t1.warehouse_code=@warehouse_code and t2.create_time>=@b_promotion_time and t2.create_time<=@e_promotion_time ", parameters);
        }

        /// <summary>
        /// 设置采购数量
        /// </summary>
        /// <param name="id"></param>
        public static void SetQuantityActual(int id, int quantity_actual)
        {
            // 检查该采购单是否已推送
            dc_auto_purchase_advise_detailed oldData = _connection.QueryFirstOrDefault<dc_auto_purchase_advise_detailed>(" select * from dc_auto_purchase_advise_detailed where id=@id ", new { id });

            if (oldData == null) throw new Exception("数据异常");

            if (oldData.ispush == 1) throw new Exception($"【{oldData.bailun_sku}】该数据已推送,不可修改");

            _connection.Execute(" update dc_auto_purchase_advise_detailed set quantity_actual=@quantity_actual where id=@id ", new { quantity_actual = quantity_actual, id = id });
        }

        /// <summary>
        /// 批量设置
        /// </summary>
        /// <param name="datas"></param>
        public static void BatchSetQuantityActual(List<BatchSetQuantityActualInputDto> datas)
        {
            if (datas != null)
            {
                foreach (var item in datas)
                {
                    SetQuantityActual(item.id, item.quantity_actual);
                }
            }
        }

        /// <summary>
        /// 查询历史采购建议
        /// </summary>
        /// <param name="sku"></param>
        /// <param name="warehousecode"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public static dc_auto_purchase_advise_detailed DetailInfo(string sku, string warehousecode, DateTime dateTime)
        {
            string sql = @" select t1.* from dc_auto_purchase_advise_detailed as t1
left join dc_auto_purchase_advise as t2 on t1.main_id = t2.id
where t1.warehouse_code=@warehousecode and t1.bailun_sku=@sku and t2.create_time>=@b_create_time and t2.create_time<=@e_create_time order by t2.create_time desc limit 1 ";
            return _connection.QueryFirstOrDefault<dc_auto_purchase_advise_detailed>(sql, new { sku, warehousecode, b_create_time = dateTime.ToString("yyyy-MM-dd 00:00:00"), e_create_time = dateTime.ToString("yyyy-MM-dd 23:59:59") });
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="id"></param>
        public static void Del(int id)
        {
            _connection.Delete<dc_auto_purchase_advise_detailed>(id);
        }


        /// <summary>
        /// 退货汇总
        /// </summary>
        /// <param name="search_data"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public static List<dc_aims_return_goods_dto> ReturnGoodsList(dc_aims_return_goods_search_dto search_data, int offset, int limit, ref int total)
        {
            var sql = @"select t1.*
from dc_aims_return_goods as t1 
left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code
where 1=1 ";
            var countSql = " select count(1) from dc_aims_return_goods as t1 left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(search_data.warehousecode))
            {
                sql += " and t1.warehouse_code=@warehouse_code ";
                countSql += " and t1.warehouse_code=@warehouse_code ";
                parameters.Add("warehouse_code", search_data.warehousecode);
            }
            if (!string.IsNullOrWhiteSpace(search_data.warehousetype))
            {
                sql += " and t3.hq_type = @hq_type ";
                countSql += " and t3.hq_type = @hq_type ";
                parameters.Add("hq_type", search_data.warehousetype);
            }
            if (search_data.warehousearea > 0)
            {
                sql += " and t3.area_id = @area_id ";
                countSql += " and t3.area_id = @area_id ";
                parameters.Add("area_id", search_data.warehousearea);
            }
            if (search_data.start_date != null)
            {
                sql += " and t1.`date`>=@btime ";
                countSql += " and t1.`date`>=@btime ";
                parameters.Add("btime", search_data.start_date);
            }
            if (search_data.end_date != null)
            {
                sql += " and t1.`date`<=@etime ";
                countSql += " and t1.`date`<=@etime ";
                parameters.Add("etime", search_data.end_date);
            }
            if (!string.IsNullOrWhiteSpace(search_data.bailun_sku))
            {
                sql += " and t1.bailun_sku in @bailun_sku ";
                countSql += " and t1.bailun_sku in @bailun_sku ";
                parameters.Add("bailun_sku", search_data.bailun_sku.Split(','));
            }

            sql += " order by t1.id desc  ";
            total = _connection.QueryFirst<int>(countSql, parameters, commandTimeout: 0);
            sql += " limit " + offset + "," + limit;
            var obj = _connection.Query<dc_aims_return_goods_dto>(sql, parameters, buffered: false, commandTimeout: 0);
            return obj.AsList();
        }

        /// <summary>
        /// 查询今日的周转数据
        /// </summary>
        /// <returns></returns>
        public static List<dc_auto_purchase_advise> GetToDayDatas()
        {
            return _connection.Query<dc_auto_purchase_advise>(" select * from dc_auto_purchase_advise where create_time>=@bdate ", new
            {
                bdate = DateTime.Now.ToDayHome()
            }).Where(s => s.title != null && (!s.title.Contains("刷新"))).OrderByDescending(s => s.create_time).ToList();
        }

    }
}
