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)
        {
            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`,`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`)
(
	select * from (
select 
	t1.bailun_sku,
	t1.warehouse_code,
	t1.quantity_init_advise as 'quantity_init_advise', -- 原始采购建议数量 new
	(
	case 
	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
	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
	where t6.`status`=2 and  t1.warehouse_code!='BLGZ03' 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') )
	) 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;
                            }
                        }
                    }
                }
            }
            ImportFuZhuangShortageDetailed(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>
        /// 导入缺货明细
        /// </summary>
        /// <param name="mainID"></param>
        public static int ImportJITShortageDetailed(int mainID)
        {
            if (mainID <= 0)
            {
                mainID = purchase_advise.Add(new dc_auto_purchase_advise { create_time = DateTime.Now, no = purchase_advise.GenerateOrderNo() }) ?? 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`)
(
	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 )) + 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
	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_transfer) + sum(t7.quantity_purchase) < sum(t7.quantity_out_stock) )
	
	) ", new { main_id = mainID }, commandTimeout: 0);

            return mainID;
        }

        /// <summary>
        /// 导入缺货明细 (服装)
        /// </summary>
        /// <param name="mainID"></param>
        public static void ImportFuZhuangShortageDetailed(int mainID)
        {
            _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`)
(
	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
	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','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);
        }


        /// <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 });
                        }
                    }
                }
            }
        }

        /// <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 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,
(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_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=@product_inner_code ";
                    parameters.Add("product_inner_code", m.product_inner_code);
                }
                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 00:00: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 23:59: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=@bailun_sku ";
                    parameters.Add("bailun_sku", m.sku);
                }
                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.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' 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
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);
        }
    }
}
