using AutoTurnOver.Models;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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>
        /// <param name="mainID"></param>
        public static void ImportDetailed(int mainID)
        {

            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`)
(
	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'
	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
	where 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 }, 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);

            // 调整因为舍弃小数导致的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" }) ?? 0;
            }
            // 采购员为 张莹霞 的商品 按缺货数量下单
            _connection.Execute(@" INSERT into dc_auto_purchase_advise_detailed(`bailun_sku`,`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`,`quantity_out_stock_aliexpress`,`quantity_out_stock_amazon`,`quantity_out_stock_other`)
(
	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'
	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
	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);

            return mainID;
        }

        /// <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" }) ?? 0;
            }
            // 采购员为 张莹霞 的商品 按缺货数量下单
            _connection.Execute(@" INSERT into dc_auto_purchase_advise_detailed(`bailun_sku`,`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`,`quantity_out_stock_aliexpress`)
(
	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'
	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
	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);

            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" }) ?? 0;
            }
            _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`,`bailun_order_ids`)
(
	select 
	t1.bailun_sku,
	t1.warehouse_code as 'source_warehouse_code',
    'BLGZ03' 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
	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
	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);
            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" }) ?? 0;
            }
            _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`,`bailun_order_ids`)
(
	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
	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
	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);
            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" }) ?? 0;
            }
            _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`,`bailun_order_ids`)
(
	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
	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
	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);
            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
      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(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;
            }
            _connection.Execute(@" INSERT into dc_auto_purchase_advise_detailed(`bailun_sku`,`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`,`bailun_order_ids`)
(
	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);
            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" }) ?? 0;
            }
            _connection.Execute(@" INSERT into dc_auto_purchase_advise_detailed(`bailun_sku`,`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`)
(
	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'
	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
	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);
            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 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.ExecuteScalar<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 1=1 ";
                }
                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',t4.create_time,t3.buyer_name,
(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',
(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
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 (!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.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.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_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.ExecuteScalar<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;
                }
                var obj = _connection.Query<dc_auto_purchase_advise_detailed_dto>(sql, parameters, buffered: false, commandTimeout: 0);
                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 _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);
        }
    }
}
