using AutoTurnOver.Models;
using Dapper;
using System;
using System.Collections.Generic;
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 ");
        }

        /// <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)
        {
            _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`)
(
	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
		CEILING(
		t1.quantity_init_advise +
		(
		(t7.max_moq-t7.sum_quantity_init_advise) 
		* 
		(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'
	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 (t1.history_fourteenday_sales>0 or t1.quantity_out_stock>0 ) and t1.quantity_init_advise>0 and ( t5.`status`=0 or t5.`status` is null )
	
	)
", new { main_id = mainID });
        }

        /// <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)
        {
            var list = new List<dc_auto_purchase_advise_detailed_dto>();

            try
            {
                DynamicParameters parameters = new DynamicParameters();

                var sql = @"select t1.*,t2.warehouse_name,t3.sku_title_cn as 'sku_name',t4.create_time 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
where 1=1 ";

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

                sql += " order by t1.quantity_final_advise desc ";

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        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)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("ids", ids);
            string sql = @" select t1.*,t2.unit_price from dc_auto_purchase_advise_detailed as t1
left join dc_base_sku as t2 on t1.bailun_sku = t2.bailun_sku
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("数据异常");

            dc_auto_purchase_advise oldMainData = _connection.QueryFirstOrDefault<dc_auto_purchase_advise>(" select * from dc_auto_purchase_advise where id=@id ", new { id = oldData.main_id });

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

            if (oldMainData.push_time != null) throw new Exception("该采购计划已推送,不可修改");

            _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="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") });
        }
    }
}
