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

namespace AutoTurnOver.DB
{
    public class db_config : connectionHelper
    {
        #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_safe_inventoryDto> InventoryList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_safe_inventoryDto>();

            try
            {
                var sql = @"select dacp.* from dc_auto_config_safe_inventory as 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.status=1 ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.variable_code 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_safe_inventoryDto>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

            obj.id = m.id;
            obj.variable_code = m.variable_code;
            obj.variable_name = m.variable_name;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.remark = m.remark;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.status = m.status;
            obj.weighting_coefficient_fourteen = m.weighting_coefficient_fourteen;
            obj.weighting_coefficient_seven = m.weighting_coefficient_seven;
            obj.weighting_coefficient_thirty = m.weighting_coefficient_thirty;
            obj.type = m.type;
            obj.param = m.param;

            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_config_safe_inventory where variable_code=@variable_code and warehouse_code=@warehouse_code and type=@type and id!=@id ", new { m.variable_code, m.warehouse_code, m.id, m.type });
            if (oldData != null)
            {
                return $" variable_code:【{m.variable_code}】,仓库:【{m.variable_name}】 已经配置过了 ";
            }

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

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_safe_inventory>(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_safe_inventory GetInventoryById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_safe_inventory>("select * from dc_auto_config_safe_inventory where id=" + id);
        }

        #endregion

        #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_sales_upper_limitDto> SalesUpperLimitList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_sales_upper_limitDto>();

            try
            {
                var sql = @"select dacp.* from dc_auto_config_sales_upper_limit as 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.status=1 ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.variable_code 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_sales_upper_limitDto>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

            obj.id = m.id;
            obj.variable_code = m.variable_code;
            obj.variable_name = m.variable_name;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.remark = m.remark;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.status = m.status;
            obj.weighting_coefficient_fourteen = m.weighting_coefficient_fourteen;
            obj.weighting_coefficient_seven = m.weighting_coefficient_seven;
            obj.weighting_coefficient_thirty = m.weighting_coefficient_thirty;
            obj.type = m.type;
            obj.param = m.param;

            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_config_sales_upper_limit where variable_code=@variable_code and warehouse_code=@warehouse_code and type=@type and id!=@id ", new { m.variable_code, m.warehouse_code, m.id, m.type });
            if (oldData != null)
            {
                return $" variable_code:【{m.variable_code}】,仓库:【{m.variable_name}】 已经配置过了 ";
            }

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

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_sales_upper_limit>(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_sales_upper_limit GetSalesUpperLimitById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_sales_upper_limit>("select * from dc_auto_config_sales_upper_limit where id=" + id);
        }

        #endregion

        #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_delivery_dto> ConfigDeliveryList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_delivery_dto>();

            try
            {
                var sql = @"select dacp.* from dc_auto_config_delivery as dacp 
left join dc_base_warehouse as dbw on dacp.variable_code = dbw.warehouse_code and dacp.type = 3
where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and  ( dbw.hq_type=" + $"'{m.warehousetype}' or ( dacp.type=4 and dacp.variable_code='" + m.warehousetype + "' ) ) ";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and dbw.area_id=" + m.warehousearea;
                }
                if (m.IsEffective == true)
                {
                    sql += $" and dacp.status=1 ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and ( ( dacp.type = 1 or dacp.type=2 ) and dacp.bailun_sku like '%" + m.sku + "%' )";
                }

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

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

            obj.id = m.id;
            obj.variable_code = m.variable_code;
            obj.variable_name = m.variable_name;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.remark = m.remark;
            obj.status = m.status;
            obj.type = m.type;
            obj.tranfer_bale = m.tranfer_bale;
            obj.tranfer_head = m.tranfer_head;
            obj.abroad_inbound = m.abroad_inbound;

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

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_delivery>(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_delivery GetConfigDeliveryById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_delivery>("select * from dc_auto_config_delivery where id=" + id);
        }

        #endregion

        #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_stock_up_days> StockUpList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_config_stock_up_days>();

            try
            {
                var sql = @"select dacp.* from dc_auto_config_stock_up_days as 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.status=1 ";
                }
                if (!string.IsNullOrWhiteSpace(m.sku))
                {
                    sql += " and dacp.variable_code 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_stock_up_days>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

            obj.id = m.id;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.remark = "";
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.status = 1;
            obj.stock_up_days = m.stock_up_days;


            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_config_stock_up_days where warehouse_code=@warehouse_code  and id!=@id ", new { m.warehouse_code, m.id });
            if (oldData != null)
            {
                return $" 仓库:【{m.warehouse_name}】 已经配置过了 ";
            }

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

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_config_stock_up_days>(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_stock_up_days GetStockUpById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_config_stock_up_days>("select * from dc_auto_config_stock_up_days where id=" + id);
        }

        #endregion

        /// <summary>
        /// 修改监控状态
        /// </summary>
        /// <param name="bailun_sku"></param>
        /// <param name="warehouse_code"></param>
        /// <param name="monitor_status"></param>
        /// <returns></returns>
        public static string MonitorStatus(string bailun_sku, string warehouse_code, int monitor_status, UserData user)
        {

            if (monitor_status != 1 && monitor_status != 0)
            {
                throw new Exception("状态异常");
            }
            // 先检查当前状态
            var oldData = _connection.QueryFirstOrDefault<dc_auto_config_sku_warehouse>($"select * from dc_auto_config_sku_warehouse where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new { bailun_sku, warehouse_code });
            if (oldData == null || oldData.status == 0)
            {
                if (monitor_status == 0)
                {
                    return "该数据当前已经正常监控,请勿重复操作";
                }

            }

            if (oldData != null && oldData.status == 1)
            {
                if (monitor_status == 1)
                {
                    return "该数据当前已经停止监控,请勿重复操作";
                }

            }

            DateTime now = DateTime.Now;

            if (oldData == null)
            {
                var newData = new dc_auto_config_sku_warehouse
                {
                    bailun_sku = bailun_sku,
                    warehouse_code = warehouse_code,
                    status = monitor_status,
                    gmt_create = now,
                    bl_operator = user.UserName,
                    sku_title = "",
                    warehouse_name = "",
                    gmt_modified = now

                };
                _connection.Insert<dc_auto_config_sku_warehouse>(newData);
            }
            else
            {
                _connection.Execute(" update  dc_auto_config_sku_warehouse set status=@monitor_status where id=@id", new { monitor_status, id = oldData.id });
            }
            return "";


        }

        /// <summary>
        /// 批量修改监控状态
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="monitor_status"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public static string BatchMonitorStatus(string ids, int monitor_status, UserData user)
        {

            if (monitor_status != 1 && monitor_status != 0)
            {
                throw new Exception("状态异常");
            }
            var datas = _connection.Query<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where id in @ids ", new { ids = ids.Split(',') }).AsList();
            foreach (AutoTurnOver.Models.dc_auto_turnover turnover_item in datas)
            {
                // 先检查当前状态
                var oldData = _connection.QueryFirstOrDefault<dc_auto_config_sku_warehouse>($"select * from dc_auto_config_sku_warehouse where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new { turnover_item.bailun_sku, turnover_item.warehouse_code });
                if (oldData == null || oldData.status == 0)
                {
                    if (monitor_status == 0)
                    {
                        continue;
                    }

                }

                if (oldData != null && oldData.status == 1)
                {
                    if (monitor_status == 1)
                    {
                        continue;
                    }

                }

                DateTime now = DateTime.Now;

                if (oldData == null)
                {
                    var newData = new dc_auto_config_sku_warehouse
                    {
                        bailun_sku = turnover_item.bailun_sku,
                        warehouse_code = turnover_item.warehouse_code,
                        status = monitor_status,
                        gmt_create = now,
                        bl_operator = user.UserName,
                        sku_title = "",
                        warehouse_name = "",
                        gmt_modified = now

                    };
                    _connection.Insert<dc_auto_config_sku_warehouse>(newData);
                }
                else
                {
                    _connection.Execute(" update  dc_auto_config_sku_warehouse set status=@monitor_status,bl_operator=@bl_operator where id=@id", new { monitor_status, id = oldData.id, bl_operator = user.UserName });
                }
            }

            return "";


        }

        public static string BatchReturnGoodsStatus(string ids, int monitor_status, UserData user)
        {

            if (monitor_status != 1 && monitor_status != 0)
            {
                throw new Exception("状态异常");
            }
            var datas = _connection.Query<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where id in @ids ", new { ids = ids.Split(',') }).AsList();
            foreach (AutoTurnOver.Models.dc_auto_turnover turnover_item in datas)
            {
                _connection.Insert(new dc_return_goods_not_push_log
                {
                    bailun_sku = turnover_item.bailun_sku,
                    warehouse_code = turnover_item.warehouse_code,
                    status = monitor_status,
                    create_user_name = user.UserAccount,
                    create_time = DateTime.Now,
                });

                // 先检查当前状态
                var oldData = _connection.QueryFirstOrDefault<dc_return_goods_not_push>($"select * from dc_return_goods_not_push where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code ", new { turnover_item.bailun_sku, turnover_item.warehouse_code });
                if (oldData == null || oldData.status == 0)
                {
                    if (monitor_status == 0)
                    {
                        continue;
                    }

                }

                if (oldData != null && oldData.status == 1)
                {
                    if (monitor_status == 1)
                    {
                        continue;
                    }

                }

                DateTime now = DateTime.Now;

                if (oldData == null)
                {
                    var newData = new dc_return_goods_not_push
                    {
                        bailun_sku = turnover_item.bailun_sku,
                        warehouse_code = turnover_item.warehouse_code,
                        status = monitor_status

                    };
                    _connection.Insert<dc_return_goods_not_push>(newData);
                }
                else
                {
                    _connection.Execute(" update  dc_return_goods_not_push set status=@monitor_status where id=@id", new { monitor_status, id = oldData.id, bl_operator = user.UserName });
                }
            }

            return "";


        }

        public static List<dc_return_goods_not_push> ReturnGoodsList(List<string> skus)
        {
            if (skus == null || skus.Count <= 0) {
                return new List<dc_return_goods_not_push>();
            }
           return  _connection.Query<dc_return_goods_not_push>(" select * from dc_return_goods_not_push where status=1 and bailun_sku in @skus ",new { skus  = skus }).ToList();
        }

        public static string BatchTortStatus(string ids, int tort_status, UserData user)
        {

            if (tort_status != 1 && tort_status != 0)
            {
                throw new Exception("状态异常");
            }
            var datas = _connection.Query<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where id in @ids ", new { ids = ids.Split(',') }).AsList();
            foreach (AutoTurnOver.Models.dc_auto_turnover turnover_item in datas)
            {
                // 先检查当前状态
                var oldData = _connection.QueryFirstOrDefault<dc_base_tort>($"select * from dc_base_tort where bailun_sku=@bailun_sku ", new { turnover_item.bailun_sku });
                if (oldData == null)
                {
                    if (tort_status == 0)
                    {
                        continue;
                    }

                }

                if (oldData != null)
                {
                    if (tort_status == 1)
                    {
                        continue;
                    }

                }

                DateTime now = DateTime.Now;

                if (oldData == null)
                {
                    var newData = new dc_base_tort
                    {
                        bailun_sku = turnover_item.bailun_sku,
                        gmt_create = now,
                        create_user_name = user.UserName
                    };
                    _connection.Insert<dc_base_tort>(newData);
                }
                else
                {
                    if (tort_status == 0)
                    {
                        _connection.Execute(" delete from  dc_base_tort  where id=@id", new { id = oldData.id });
                    }
                    else
                    {

                    }

                }
            }

            return "";


        }
        #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_base_stock_config_fba> StockFbaList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_base_stock_config_fba>();

            try
            {
                var sql = @"select dacp.* from dc_base_stock_config_fba as 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 (!string.IsNullOrWhiteSpace(m.platform_sku))
                {
                    sql += " and dacp.platform_sku like '%" + m.platform_sku + "%'";
                }
                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.bailun_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_base_stock_config_fba>(sql + " limit " + offset + "," + limit);

                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_base_stock_config_fba_lose> StockFbaLoseList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_base_stock_config_fba_lose>();

            try
            {
                var sql = @"select dacp.* from dc_base_stock_config_fba_lose as dacp";
                if (!string.IsNullOrWhiteSpace(m.platform_sku))
                {
                    sql += " and dacp.platform_sku like '%" + m.platform_sku + "%'";
                }
                total = _connection.ExecuteScalar<int>("select count(0) from (" + sql + ") tb1");

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        /// <summary>
        /// 平台共享仓配置
        /// </summary>
        /// <param name="m">销售设置model</param>
        /// <param name="username">当前操作人名称</param>
        /// <returns></returns>
        public static string SaveStockFba(Models.dc_base_stock_config_fba m, string username)
        {
            // 查询百伦sku
            var dc_base_stock_fba_share = _connection.QueryFirstOrDefault<dc_base_stock_fba_share>(" select * from dc_base_stock_fba_share where platform_sku=@platform_sku limit 1 ", new { platform_sku = m.platform_sku });
            if (dc_base_stock_fba_share == null)
            {
                throw new Exception(" 未找到与之对应的百伦sku数据 ");
            }



            var obj = _connection.QueryFirstOrDefault<Models.dc_base_stock_config_fba>("select * from dc_base_stock_config_fba where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_base_stock_config_fba
                {
                    gmt_create = DateTime.Now,
                    gmt_modified = DateTime.Now
                };
            }


            obj.bailun_sku = dc_base_stock_fba_share.bailun_sku;
            obj.id = m.id;
            obj.bl_operator = username ?? "";
            obj.gmt_modified = DateTime.Now;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;
            obj.platform_sku = m.platform_sku;


            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_base_stock_config_fba where platform_sku=@platform_sku  and id!=@id ", new { m.platform_sku, m.id });
            if (oldData != null)
            {
                return $" 平台sku:【{m.platform_sku}】 已经配置过了 ";
            }

            try
            {
                _connection.Execute(" delete from dc_base_stock_config_fba_lose where platform_sku=@platform_sku ", new { platform_sku = obj.platform_sku });
                if (obj.id > 0)
                {
                    var result = _connection.Update<Models.dc_base_stock_config_fba>(obj);

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

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }


            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// 获取平台共享仓配置详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static dc_base_stock_config_fba GetStockFbaById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_base_stock_config_fba>("select * from dc_base_stock_config_fba where id=" + id);
        }

        #endregion

        #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_sales_config> SalesConfigList(dc_auto_sales_config_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_sales_config>();

            try
            {
                var sql = @"select t1.* from dc_auto_sales_config as t1  where 1 = 1 ";

                if (!string.IsNullOrWhiteSpace(m.platform_type))
                {
                    sql += " and t1.platform_type=" + $"'{m.platform_type}'";
                }
                if (!string.IsNullOrWhiteSpace(m.web_site))
                {
                    sql += " and t1.web_site=" + $"'{m.web_site}'";
                }
                if (m.start_date != null)
                {
                    sql += " and t1.start_date<=" + $"'{m.start_date}'";
                }
                if (m.end_date != null)
                {
                    sql += " and t1.end_date>=" + $"'{m.end_date.Value}'";
                }

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

            obj.id = m.id;
            obj.platform_type = m.platform_type;
            obj.web_site = m.web_site;
            obj.multiple = m.multiple;
            obj.start_date = m.start_date;
            obj.end_date = m.end_date;

            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_sales_config where platform_type=@platform_type and web_site=@web_site and id!=@id ", new { m.platform_type, m.web_site, m.id });
            if (oldData != null)
            {
                return $" 平台:【{m.platform_type}】,站点:【{m.web_site}】 已经配置过了 ";
            }

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

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_sales_config>(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_sales_config GetSalesConfigById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_sales_config>("select * from dc_auto_sales_config where id=" + id);
        }

        /// <summary>
        /// 获取所有平台
        /// </summary>
        /// <returns></returns>
        public static List<string> ListPlatformType()
        {
            return _connection.Query<string>(" select DISTINCT platform_type from dc_base_oms_order  ").AsList();
        }

        /// <summary>
        /// 获取所有站点
        /// </summary>
        /// <returns></returns>
        public static List<string> ListWebSite()
        {
            return _connection.Query<string>(" select DISTINCT website from dc_base_oms_order  ").AsList();
        }

        #endregion


        #region 动态备货sku

        public static List<dc_auto_stock_up_range_dto> StockUpRangeList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_stock_up_range_dto>();

            try
            {
                var sql = @"select dacp.*,t3.`name` as 'group_name' from dc_auto_stock_up_range as dacp
left join dc_base_warehouse as dbw on dacp.warehouse_code = dbw.warehouse_code 
left join dc_auto_jit_tag_group as t3 on dacp.group_id = t3.id
where 1 = 1 ";
                if (m.group_id > 0)
                {
                    sql += " and dacp.group_id=" + m.group_id;
                }
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and dbw.hq_type=" + $"'{m.warehousetype}'";
                }
                if (m.warehousearea > 0)
                {
                    sql += " and dbw.area_id=" + m.warehousearea;
                }
                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and dacp.bailun_sku like '%" + m.bailun_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_stock_up_range_dto>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        public static string SaveStockUpRange(Models.dc_auto_stock_up_range m, string username)
        {
            if (m.group_id == null || m.group_id <= 0)
            {
                throw new Exception("规则组必选");
            }
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_stock_up_range>("select * from dc_auto_stock_up_range where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_stock_up_range
                {

                };
            }


            obj.bailun_sku = m.bailun_sku;
            obj.id = m.id;
            obj.warehouse_code = m.warehouse_code;
            obj.warehouse_name = m.warehouse_name;


            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_stock_up_range where bailun_sku=@bailun_sku and  warehouse_code=@warehouse_code and id!=@id ", new { m.bailun_sku, m.warehouse_code, m.id });
            if (oldData != null)
            {
                return $" 仓库skusku:【{m.bailun_sku}】 已经配置过了 ";
            }

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

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    var result = _connection.Insert<Models.dc_auto_stock_up_range>(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_stock_up_range GetStockUpRangeById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_stock_up_range>("select * from dc_auto_stock_up_range where id=" + id);
        }

        #endregion

        #region 动态规则分组

        public static List<dc_auto_jit_tag_group> JitTagGroupList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_jit_tag_group>();

            try
            {
                var sql = @"select dacp.* from dc_auto_jit_tag_group as dacp ";


                if (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and dacp.name like '%" + m.bailun_sku + "%'";
                }



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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        public static string SaveJitTagGroup(Models.dc_auto_jit_tag_group m, string username)
        {
            if (m.id == 1)
            {
                throw new Exception("该规则组为jit专用,不可修改");
            }
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_jit_tag_group>("select * from dc_auto_jit_tag_group where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_jit_tag_group
                {

                };
            }


            obj.name = m.name.Trim();
            obj.id = m.id;


            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_jit_tag_group where `name`=@name and id!=@id ", new { obj.name, m.id });
            if (oldData != null)
            {
                return $" 规则名称:【{m.name}】 已存在 ";
            }

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

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

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }


            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public static dc_auto_jit_tag_group GetJitTagGroupById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_jit_tag_group>("select * from dc_auto_jit_tag_group where id=" + id);
        }

        public static List<dc_auto_jit_tag_group> JitTagGroupAll()
        {
            return _connection.Query<dc_auto_jit_tag_group>(" select * from dc_auto_jit_tag_group ").AsList();
        }

        /// <summary>
        /// 批量设置规则组
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="group_id"></param>
        public static void BatchStockUp(batch_stock_up_input_dto data)
        {
            if (string.IsNullOrWhiteSpace(data.ids))
            {
                throw new Exception("请选择数据");
            }
            if (data.group_id <= 0)
            {
                throw new Exception("请选择规则组");
            }
            var datas = _connection.Query<AutoTurnOver.Models.dc_auto_turnover>(" select * from dc_auto_turnover where id in @ids ", new { ids = data.ids.Split(',').Select(s => int.Parse(s)).ToList() });
            foreach (var item in datas)
            {
                // 检查数据是否存在
                var old_data = _connection.QueryFirstOrDefault<dc_auto_stock_up_range>(" select * from dc_auto_stock_up_range where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ",
                    new { bailun_sku = item.bailun_sku, warehouse_code = item.warehouse_code });
                if (old_data == null)
                {
                    _connection.Insert(new dc_auto_stock_up_range
                    {
                        bailun_sku = item.bailun_sku,
                        group_id = data.group_id,
                        warehouse_code = item.warehouse_code,
                        warehouse_name = item.warehouse_name
                    });
                }
                else
                {
                    old_data.group_id = data.group_id;
                    _connection.Update(old_data);
                }
            }

        }
        #endregion

        #region 销量预测   人工配置

        public static List<dc_config_forecast_sales> ForecastSalesConfigList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_config_forecast_sales>();

            try
            {
                var sql = @"select t1.* from dc_config_forecast_sales as t1  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 (!string.IsNullOrWhiteSpace(m.bailun_sku))
                {
                    sql += " and dacp.bailun_sku =" + $"'{m.bailun_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_config_forecast_sales>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

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

            obj.id = m.id;
            obj.bailun_sku = m.bailun_sku;
            obj.warehouse_code = m.warehouse_code;
            obj.start_date = m.start_date;
            obj.end_date = m.end_date;
            obj.warehouse_name = m.warehouse_name;
            obj.create_user = username;

            // 检查该数据是否有存在
            var oldData = _connection.QueryFirstOrDefault(@" select * from dc_config_forecast_sales 
where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and id!=@id
and start_date<=@end_date and end_date>=@start_date
", new { m.bailun_sku, m.warehouse_code, m.id, end_date = m.end_date, start_date = m.start_date });
            if (oldData != null)
            {
                return $" sku:【{m.bailun_sku}】,仓库:【{m.warehouse_name}】 已经配置过了 ";
            }

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

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

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

        public static dc_config_forecast_sales GetForecastSalesConfigById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_config_forecast_sales>("select * from dc_config_forecast_sales where id=" + id);
        }

        #endregion

        #region 暴增配置

        public static List<dc_auto_uprush_config_dto> UprushConfigList(dc_auto_uprush_config_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_uprush_config_dto>();

            try
            {
                var sql = @"select t1.*,t3.warehouse_name from dc_auto_uprush_config as t1  left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code where 1 = 1 ";

                if (!string.IsNullOrEmpty(m.warehouse_code))
                {
                    sql += " and t1.warehouse_code='" + m.warehouse_code + "'";
                }
                else
                {
                    if (!string.IsNullOrWhiteSpace(m.warehousetype))
                    {
                        sql += " and t3.hq_type=" + $"'{m.warehousetype}'";
                    }
                    if (m.warehousearea > 0)
                    {
                        sql += " and t3.area_id=" + m.warehousearea;
                    }
                }

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        public static string SaveUprushConfig(Models.dc_auto_uprush_config m, string username)
        {
            if (string.IsNullOrWhiteSpace(m.warehouse_code))
            {
                m.warehouse_code = "";
            }
            var obj = _connection.QueryFirstOrDefault<Models.dc_auto_uprush_config>("select * from dc_auto_uprush_config where id=" + m.id);
            if (obj == null)
            {
                obj = new dc_auto_uprush_config
                {
                };
            }

            obj.id = m.id;
            obj.warehouse_code = m.warehouse_code;
            obj.percentage = m.percentage;
            obj.hq_type = m.hq_type;


            // 检查该数据是否有存在
            if (!string.IsNullOrWhiteSpace(m.warehouse_code))
            {
                var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_uprush_config where warehouse_code=@warehouse_code and id!=@id ", new { m.warehouse_code, m.id });
                if (oldData != null)
                {
                    return $" 仓库:【{m.warehouse_code}】已经配置过了 ";
                }
            }

            // 检查该数据是否有存在
            if (!string.IsNullOrWhiteSpace(m.hq_type))
            {
                var oldData = _connection.QueryFirstOrDefault(" select * from dc_auto_uprush_config where hq_type=@hq_type and id!=@id ", new { m.hq_type, m.id });
                if (oldData != null)
                {
                    return $" 仓库类型:【{m.hq_type}】已经配置过了 ";
                }
            }


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

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

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

        public static dc_auto_uprush_config GetUprushConfigById(int id)
        {
            return _connection.QueryFirstOrDefault<dc_auto_uprush_config>("select * from dc_auto_uprush_config where id=" + id);
        }

        #endregion
    }
}
