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


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

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

                string sqlCount = @"select count(1)
from dc_auto_turnover as dat
left join dc_base_warehouse as t3 on dat.warehouse_code = t3.warehouse_code
left join dc_base_tort as t8  on dat.bailun_sku = t8.bailun_sku

";

                DynamicParameters parameters = new DynamicParameters();

               
                if (m.monitor_status != null)
                {
                    sqlCount += " left join dc_auto_config_sku_warehouse as t5 on dat.bailun_sku = t5.bailun_sku and dat.warehouse_code = t5.warehouse_code ";
                }
                sqlCount += " where 1=1 ";

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

                if (m.categoryModels != null)
                {
                    sql += " and dat.bailun_category_id in ( " + string.Join(",", m.categoryModels.Select(s => s.C_ID).Distinct()) + ")";
                    sqlCount += " and dat.bailun_category_id in ( " + string.Join(",", m.categoryModels.Select(s => s.C_ID).Distinct()) + ")";

                }
                if (!string.IsNullOrWhiteSpace(m.buyer_name))
                {
                    sql += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                    sqlCount += " and dat.buyer_name = " + $"'{m.buyer_name}'";
                }
                if (m.monitor_status == 1)
                {
                    sql += " and t5.`status`=1 ";
                    sqlCount += " and t5.`status`=1 ";
                }
                if (m.monitor_status == 0)
                {
                    sql += " and ( t5.`status`=0 or t5.`status` is null ) ";
                    sqlCount += " and ( t5.`status`=0 or t5.`status` is null ) ";
                }

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

                }

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

                if (!string.IsNullOrEmpty(m.warehouse_code))
                {
                    sql += " and dat.warehouse_code='" + m.warehouse_code + "'";
                    sqlCount += " and dat.warehouse_code='" + m.warehouse_code + "'";
                }

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

                if (isSum)
                {
                    total = 0;
                }
                else
                {
                    total = _connection.ExecuteScalar<int>(sqlCount, parameters);

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

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

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




                var obj = _connection.Query<Models.dc_auto_turnover_Extend>(sql, parameters, commandTimeout: 0);

                return obj.AsList();
            }
            catch (Exception ex)
            {

                throw;
            }

        }


        #region 特殊销售设置

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

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

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

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

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

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

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

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

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

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

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

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

                    _con.Insert(obj, transaction);
                }

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

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




        #endregion

        #region 预估库存

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

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

        #endregion

        #region 预测入库

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

        #endregion

        #region 预测断货

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

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

        #endregion

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


        #region 市场因素设置

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

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

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

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

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

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

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

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

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

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

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





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




        #endregion

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

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

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

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

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

            return datas;
        }

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


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


            return datas;
        }
    }


}
