using AutoTurnOver.DB.Base;
using AutoTurnOver.Models;
using AutoTurnOver.Models.auto;
using AutoTurnOver.Models.Base;
using AutoTurnOver.Models.Report;
using Bailun.ServiceFabric;
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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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)
        {
            throw new Exception("功能停用，请使用批量修改状态");
            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)
        {
            var conn = _connection;
            if (monitor_status != 1 && monitor_status != 0)
            {
                throw new Exception("状态异常");
            }
            var datas = conn.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 = conn.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

                    };
                    conn.Insert<dc_auto_config_sku_warehouse>(newData);
                }
                else
                {
                    conn.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 });
                }

                conn.Insert(new dc_base_monitor_status_log { 
                bailun_sku = turnover_item.bailun_sku,
                warehouse_code = turnover_item.warehouse_code,
                date =DateTime.Now,
                monitor_status = monitor_status.ToString(),
                user_name  = user.UserName
                });
            }



            _connection.Execute(@"delete from dc_auto_monitor_sku_type where bailun_sku in @bailun_skus;
INSERT into dc_auto_monitor_sku_type(`bailun_sku`,`warehouse_type`) (

	SELECT DISTINCT `t1`.`bailun_sku` AS `bailun_sku` ,t3.hq_type as 'warehouse_type'
FROM  `dc_base_stock` `t1` 
LEFT JOIN `dc_auto_config_sku_warehouse` `t2` ON  `t1`.`bailun_sku` = `t2`.`bailun_sku`  
		AND `t1`.`warehouse_code` = `t2`.`warehouse_code` 
		left join dc_base_warehouse as t3 on t1.warehouse_code =t3.warehouse_code
		WHERE
(
	isnull( `t2`.`bailun_sku` ) 
	OR ( `t2`.`status` = 0 )
	)
	and t1.bailun_sku in @bailun_skus
    and t1.warehouse_code!='GZBLZZG'
GROUP BY t1.bailun_sku,t3.hq_type
);", new
            {
                bailun_skus = datas.Select(s => s.bailun_sku).ToList()
            }); ;
            return "";


        }

        private static void ResetMonitorStatus()
        {
            // 会与改零任务互锁，停用任务
            //                _connection.Execute(@"	-- 刷新 订单 dc_auto_monitor_sku_type_temp 映射表
            //set session transaction isolation level read uncommitted;
            //start transaction;

            //-- 清空视图表的数据
            //Truncate table dc_auto_monitor_sku_type_temp;

            //INSERT into dc_auto_monitor_sku_type_temp(`bailun_sku`,`warehouse_type`) (

            //	SELECT DISTINCT `t1`.`bailun_sku` AS `bailun_sku` ,t3.hq_type as 'warehouse_type'
            //FROM  `dc_base_stock` `t1` 
            //LEFT JOIN `dc_auto_config_sku_warehouse` `t2` ON  `t1`.`bailun_sku` = `t2`.`bailun_sku`  
            //		AND `t1`.`warehouse_code` = `t2`.`warehouse_code` 
            //		left join dc_base_warehouse as t3 on t1.warehouse_code =t3.warehouse_code
            //		WHERE
            //	(
            //	isnull( `t2`.`bailun_sku` ) 
            //	OR ( `t2`.`status` = 0 )
            //	)
            //	GROUP BY t1.bailun_sku,t3.hq_type


            //);

            //alter table dc_auto_monitor_sku_type rename dc_auto_monitor_sku_typeTemp; 
            //alter table dc_auto_monitor_sku_type_temp rename dc_auto_monitor_sku_type; 
            //alter table dc_auto_monitor_sku_typeTemp rename dc_auto_monitor_sku_type_temp; 
            //truncate table dc_auto_monitor_sku_type_temp; ", commandTimeout: 0);
        }

        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)
        {
            var conn = MyMySqlConnection._connection;
            conn.Open();
            if (tort_status != 1 && tort_status != 0)
            {
                throw new Exception("状态异常");
            }
            var datas = conn.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 = conn.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
                    };
                    conn.Insert<dc_base_tort>(newData);
                }
                else
                {
                    if (tort_status == 0)
                    {
                        conn.Execute(" delete from  dc_base_tort  where id=@id", new { id = oldData.id });
                    }
                    else
                    {

                    }

                }

                _connection.Execute("update dc_base_sku set  gmt_modified=now() where bailun_sku = @bailun_sku ", new { bailun_sku = turnover_item.bailun_sku });
            }

            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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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.QuerySingleOrDefault<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

        #region 销量预测配置

        public static Page<dc_auto_sales_forecast_config_dto> SalesForecastConfigList(dc_auto_sales_forecast_config_search_dto m)
        {
            var list = new List<dc_auto_sales_forecast_config_dto>();

            try
            {
                var sql = @"select t1.*,t3.warehouse_name from dc_auto_sales_forecast_config as t1  left join dc_base_warehouse as t3 on t1.warehouse_code = t3.warehouse_code where 1 = 1 ";
                DynamicParameters parameters = new DynamicParameters();
                if (m.is_delete != null)
                {
                    sql += " and t1.is_delete=@is_delete ";
                    parameters.Add("is_delete", m.is_delete);
                }
                if (!string.IsNullOrEmpty(m.warehouse_code))
                {
                    sql += " and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                else
                {
                    if (!string.IsNullOrWhiteSpace(m.warehousetype))
                    {
                        sql += " and t3.hq_type=@warehousetype ";
                        parameters.Add("warehouse_code", m.warehousetype);
                    }
                    if (m.warehousearea > 0)
                    {
                        sql += " and t3.area_id=@area_id ";
                        parameters.Add("area_id", m.warehousearea);
                    }
                }

                var obj = _connection.Page<dc_auto_sales_forecast_config_dto>(sql, m, parameters);

                return obj;

            }
            catch (Exception ex)
            {
                return new Page<dc_auto_sales_forecast_config_dto>();
            }
        }

        public static void SaveSalesForecastConfig(Models.dc_auto_sales_forecast_config_dto input_data, string username)
        {
            var m = input_data.ToJson().ToObject<dc_auto_sales_forecast_config>();
            using (var conn = MyMySqlConnection._connection)
            {
                conn.Open();
                using (var t = conn.BeginTransaction())
                {
                    if (m.id > 0)
                    {
                        //throw new Exception("数据不可修改，请作废重建");
                        var oldData = conn.QueryFirstOrDefault<Models.dc_auto_sales_forecast_config>(" select * from dc_auto_sales_forecast_config where id=@id ", new { id = m.id });
                        oldData.is_delete = 1;
                        oldData.delete_time = DateTime.Now;
                        var result = conn.Update<Models.dc_auto_sales_forecast_config>(oldData, t);
                        m.id = 0;
                    }


                    m.create_date = DateTime.Now;
                    var new_id = conn.Insert<Models.dc_auto_sales_forecast_config>(m, t);
                    if (input_data.bailun_skus != null)
                    {
                        foreach (var item in input_data.bailun_skus.Split(','))
                        {
                            conn.Insert(new dc_auto_sales_forecast_sku_config
                            {
                                bailun_sku = item,
                                config_id = new_id ?? 0
                            });
                        }
                    }

                    if (input_data.product_codes != null)
                    {
                        foreach (var item in input_data.product_codes.Split(','))
                        {
                            conn.Insert(new dc_auto_sales_forecast_product_code_config
                            {
                                product_code = item,
                                config_id = new_id ?? 0
                            });
                        }
                    }

                    if (input_data.stages != null)
                    {
                        foreach (var item in input_data.stages)
                        {
                            item.config_id = new_id ?? 0;
                            conn.Insert(item);
                        }
                    }

                    t.Commit();
                }
            }

        }

        public static dc_auto_sales_forecast_config_dto GetSalesForecastConfigById(int id)
        {
            var data = _connection.QueryFirstOrDefault<dc_auto_sales_forecast_config_dto>("select * from dc_auto_sales_forecast_config where id=@id ", new { id = id });
            data.bailun_skus = string.Join(",", _connection.Query<string>(" select bailun_sku from dc_auto_sales_forecast_sku_config where config_id=@config_id ", new { config_id = id }).ToList());
            data.product_codes = string.Join(",", _connection.Query<string>(" select product_code from dc_auto_sales_forecast_product_code_config where config_id=@config_id ", new { config_id = id }).ToList());
            data.stages = _connection.Query<dc_auto_sales_forecast_stage_config>(" select * from dc_auto_sales_forecast_stage_config where config_id=@config_id ", new { config_id = id }).OrderBy(s => s.index).ToList();
            return data;
        }


        /// <summary>
        /// 作废
        /// </summary>
        /// <param name="id"></param>
        public static void DelSalesForecastConfig(int id)
        {
            _connection.Execute(" update dc_auto_sales_forecast_config set is_delete=1,delete_time=now() where id=@id and is_delete=0 ", new { id = id });
        }
        #endregion

        #region 现金流结算方式


        public static List<dc_report_cash_flow_config_dto> CashFlowList(dc_report_cash_flow_config_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_report_cash_flow_config_dto>();

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

                if (!string.IsNullOrWhiteSpace(m.data_type))
                {
                    sql += " and t1.data_type=" + $"'{m.data_type}'";
                }


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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        public static string SaveCashFlow(Models.dc_report_cash_flow_config m)
        {

            try
            {
                m.platform_type = (m.platform_type ?? "");
                m.web_stite = (m.web_stite ?? "");
                if (m.id > 0)
                {
                    var result = _connection.Update<Models.dc_report_cash_flow_config>(m);

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

                    return result.HasValue && result.Value > 0 ? "" : "提交异常，请重试！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public static string SaveSalesDayConfig(sales_day_config_input_dto m, UserData user,bool is_reset_data = true)
        {

            try
            {
                var data = new sales_day_config()
                {
                    sales = m.sales,
                    create_date = DateTime.Now,
                    date_str = m.date.ToString("yyyy-MM-dd"),
                    bailun_sku = m.bailun_sku,
                    bailun_sku_warehouse_code = $"{m.bailun_sku}{m.warehouse_code}",
                    warehouse_code = m.warehouse_code,
                    create_user = user.UserName,
                    date = m.date,
                    update_date = DateTime.Now,
                    update_user = user.UserName
                };
                var odlData = _connection.QueryFirstOrDefault<sales_day_config>(" select * from sales_day_config where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and date_str=@date_str ", new
                {
                    bailun_sku_warehouse_code = data.bailun_sku_warehouse_code,
                    date_str = data.date_str
                });
                if (odlData != null)
                {
                    data.id = odlData.id;
                    data.create_user = odlData.create_user;
                    data.create_date = odlData.create_date;
                    _connection.Update(data);
                }
                else
                {
                    _connection.Insert(data);
                }

                if (is_reset_data)
                {
                    _connection.Insert(new dc_base_queue { 
                        submit_date = DateTime.Now,
                        error_message  ="",
                        consume_date = null,
                        error_stack_trace  ="",
                        message = new { bailun_sku= data.bailun_sku, warehouse_code= data.warehouse_code }.ToJson(),
                        type = "刷新周转表"
                    });
                }
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public static string BatchSaveSalesDayConfig(batch_sales_day_config_input_dto m, UserData user)
        {
            foreach (var item in m.datas)
            {
                SaveSalesDayConfig(new sales_day_config_input_dto()
                {
                    bailun_sku = m.bailun_sku,
                    warehouse_code = m.warehouse_code,
                    date = item.date,
                    sales = item.sales
                }, user);
            }
            return "";
        }

        /// <summary>
        /// 删除配置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string SaveSalesDayConfigDelete(int id)
        {
            _connection.Execute(" delete from sales_day_config where id=@id ", new { id = id });
            return "";
        }
        /// <summary>
        /// 删除配置
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string BatchSaveSalesDayConfigDelete(string bailun_sku, string warehouse_code)
        {
            _connection.Execute(" delete from sales_day_config where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = bailun_sku, warehouse_code = warehouse_code });
            return "";
        }

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

        #endregion

        #region 物流供应商账期

        public static List<dc_report_logistics_company_config_dto> LogisticsCompanyConfigPage(dc_report_logistics_company_config_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_report_logistics_company_config_dto>();

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

                if (!string.IsNullOrWhiteSpace(m.company_code))
                {
                    sql += " and t1.company_code=" + $"'{m.company_code}'";
                }


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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        public static string SaveLogisticsCompanyConfig(dc_report_logistics_company_config m)
        {

            try
            {
                var companyList = ApiUtility.RealTimeShipLogisticsCompanyList();
                if (string.IsNullOrWhiteSpace(m.company_code))
                {
                    throw new Exception("请选择物流供应商");
                }
                var commData = companyList.SingleOrDefault(s => m.company_code.Equals(s.Company_Code));
                if (commData == null)
                {
                    throw new Exception("系统异常 供应商不存在");
                }
                m.company_name = commData.Company_Name;
                if (m.id > 0)
                {
                    var result = _connection.Update<dc_report_logistics_company_config>(m);

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

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


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


        #endregion

        #region 退货规则配置

        public static List<dc_auto_return_goods_config_dto> ReturnGoodsConfigPage(dc_auto_return_goods_config_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_auto_return_goods_config_dto>();

            try
            {
                var sql = @"select t1.*,t2.warehouse_name from dc_auto_return_goods_config as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
where 1 = 1 ";
                DynamicParameters parameters = new DynamicParameters();
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and t2.hq_type=@warehousetype";
                    parameters.Add("warehousetype", m.warehousetype);
                }
                if (m.warehousearea > 0)
                {
                    sql += " and t2.area_id=" + m.warehousearea;
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                if (!string.IsNullOrWhiteSpace(m.supplier_name))
                {
                    sql += " and t3.suppliers_name =@suppliers_name ";
                    parameters.Add("suppliers_name", m.supplier_name);
                }
                if (m.has_no_library != null)
                {
                    sql += " and t1.has_no_library=" + m.has_no_library;
                }
                if (m.has_return > 0)
                {
                    sql += " and t1.has_return=" + m.has_return;
                }
                if (m.status > 0)
                {
                    sql += " and t1.status=" + m.status;
                }

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

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        public static string SaveReturnGoodsConfig(dc_auto_return_goods_config m, UserData user)
        {

            try
            {

                if (!string.IsNullOrWhiteSpace(m.suppliers_name))
                {
                    var supplierData = _connection.QueryFirstOrDefault<dc_base_supplier>(" select * from dc_base_supplier where `name`=@name ", new { name = m.suppliers_name });
                    if (supplierData == null)
                    {
                        throw new Exception("系统异常 供应商不存在");
                    }
                    m.suppliers_id = supplierData.sup_value;
                }
                else
                {
                    m.suppliers_id = 0;
                }


                if (m.id > 0)
                {
                    var old_data = _connection.QueryFirstOrDefault<dc_auto_return_goods_config>("select * from dc_auto_return_goods_config where id=@id ", new { id = m.id });
                    if (old_data == null)
                    {
                        throw new Exception("数据不存在");
                    }
                    m.create_date = old_data.create_date;
                    m.create_user = old_data.create_user;
                    var result = _connection.Update<dc_auto_return_goods_config>(m);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    m.create_date = DateTime.Now;
                    m.create_user = user.UserName;
                    var result = _connection.Insert<dc_auto_return_goods_config>(m);

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


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


        #endregion

        /// <summary>
        /// 批量设置默认头程渠道
        /// </summary>
        public static void BatchSetHeadTransport(batch_set_head_transport_input_dto input_data, UserData user)
        {
            foreach (var id in input_data.ids)
            {
                var dc_auto_turnover_data = _connection.QuerySingleOrDefault<Models.dc_auto_turnover>(" select * from dc_auto_turnover where id=@id  ", new { id });
                // 查询是否已经有数据
                var dc_base_head_transport_config_old_data = _connection.QuerySingleOrDefault<dc_base_head_transport_config>(" select * from dc_base_head_transport_config where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and is_delete=0 limit 1 ", new
                {
                    bailun_sku = dc_auto_turnover_data.bailun_sku,
                    warehouse_code = dc_auto_turnover_data.warehouse_code
                });
                if (dc_base_head_transport_config_old_data != null)
                {
                    dc_base_head_transport_config_old_data.is_delete = 1;
                    dc_base_head_transport_config_old_data.delete_date = DateTime.Now;
                    dc_base_head_transport_config_old_data.delete_user = user.UserAccount;
                    _connection.Update(dc_base_head_transport_config_old_data);
                }

                _connection.Insert(new dc_base_head_transport_config
                {
                    create_date = DateTime.Now,
                    delete_user = "",
                    delete_date = null,
                    bailun_sku = dc_auto_turnover_data.bailun_sku,
                    warehouse_code = dc_auto_turnover_data.warehouse_code,
                    create_user = user.UserAccount,
                    head_transport = input_data.head_transport ?? "",
                    is_delete = 0,
                    logistics_code = input_data.logistics_code
                });


            }

            var logistics_name = "";
            if (!string.IsNullOrWhiteSpace(input_data.logistics_code))
            {
                logistics_name = ApiUtility.ShipLogisticsListHead().FirstOrDefault(s => s.Line_Code == input_data.logistics_code).Line_Name;
            }

            _connection.Execute(" update dc_auto_turnover set head_transport_type=@head_transport_type,head_transport_logistics_code=@head_transport_logistics_code where id in @ids ", new
            {
                head_transport_type = input_data.head_transport ?? "",
                head_transport_logistics_code = logistics_name,
                ids = input_data.ids
            });
        }

        /// <summary>
        /// 在调拨系统批量设置默认调拨方式
        /// </summary>
        /// <param name="input"></param>
        public static void ApiBatchHeadTransport(api_batch_head_transport_input_dto input)
        {
            foreach (var item in input.bailun_skus)
            {
                // 查询是否已经有数据
                var dc_base_head_transport_config_old_data = _connection.QuerySingleOrDefault<dc_base_head_transport_config>(" select * from dc_base_head_transport_config where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and is_delete=0 limit 1 ", new
                {
                    bailun_sku = item,
                    warehouse_code = input.warehouse_code
                });
                if (dc_base_head_transport_config_old_data != null)
                {
                    dc_base_head_transport_config_old_data.is_delete = 1;
                    dc_base_head_transport_config_old_data.delete_date = DateTime.Now;
                    dc_base_head_transport_config_old_data.delete_user = input.user_name ?? "trans-sys (user null)";
                    _connection.Update(dc_base_head_transport_config_old_data);
                }

                _connection.Insert(new dc_base_head_transport_config
                {
                    create_date = DateTime.Now,
                    delete_user = "",
                    delete_date = null,
                    bailun_sku = item,
                    warehouse_code = input.warehouse_code,
                    create_user = input.user_name ?? "trans-sys (user null)",
                    head_transport = input.head_transport ?? "",
                    is_delete = 0,
                    logistics_code = ""
                });
            }

            _connection.Execute(" update dc_auto_turnover set head_transport_type=@head_transport_type,head_transport_logistics_code=@head_transport_logistics_code where warehouse_code = @warehouse_code and bailun_sku in @bailun_skus ", new
            {
                head_transport_type = input.head_transport ?? "",
                head_transport_logistics_code = "",
                bailun_skus = input.bailun_skus,
                warehouse_code = input.warehouse_code,
            });
        }


        #region 月度销量配置

        public static List<dc_report_sales_trend_year_config_dto> SalesTrendYearConfigPage(dc_auto_return_goods_config_search_dto m, int offset, int limit, ref int total)
        {
            var list = new List<dc_report_sales_trend_year_config_dto>();

            try
            {
                var sql = @"select t1.*,t2.warehouse_name,t3.area_name as 'warehous_country_name' from dc_report_sales_trend_year_config as t1 
left join dc_base_warehouse as t2 on t1.warehouse_code = t2.warehouse_code
left join ( select DISTINCT area_id,area_name from dc_base_warehouse  ) as t3 on t1.warehous_country = t3.area_id
where 1 = 1 ";
                DynamicParameters parameters = new DynamicParameters();
                if (!string.IsNullOrWhiteSpace(m.warehousetype))
                {
                    sql += " and ( t2.hq_type=@warehousetype or t1.warehouse_type =@warehousetype ) ";
                    parameters.Add("warehousetype", m.warehousetype);
                }
                if (m.warehousearea > 0)
                {
                    sql += " and ( t2.area_id=@warehousearea or t1.warehous_country = @warehousearea ) ";
                    parameters.Add("warehousearea", m.warehousearea);
                }
                if (m.warehouse_code != null)
                {
                    sql += $" and t1.warehouse_code=@warehouse_code ";
                    parameters.Add("warehouse_code", m.warehouse_code);
                }
                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1", parameters);

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

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }


        public static string SaveSalesTrendYearConfig(dc_report_sales_trend_year_config m, UserData user)
        {

            try
            {

                if (m.id > 0)
                {
                    var old_data = _connection.QueryFirstOrDefault<dc_report_sales_trend_year_config>("select * from dc_auto_return_goods_config where id=@id ", new { id = m.id });
                    if (old_data == null)
                    {
                        throw new Exception("数据不存在");
                    }
                    m.create_date = old_data.create_date;
                    m.create_user = old_data.create_user;
                    var result = _connection.Update<dc_report_sales_trend_year_config>(m);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
                else
                {
                    m.create_date = DateTime.Now;
                    m.create_user = user.UserName;
                    var result = _connection.Insert<dc_report_sales_trend_year_config>(m);

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


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


        #endregion

    }
}
