using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：移动单管理
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class MovementManageRepository : BaseRepository<WmsMoveHeader>, IMovementManageRepository
    {
        public MovementManageRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getMoveHeaderListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", "wms_move_header a (nolock) left join sys_enum b on b.enum_type='wms_move_order_type' and a.order_type=b.enum_value left join sys_enum c on c.enum_type='wms_move_header_order_status' and a.order_status=c.enum_value LEFT join sys_enum d on d.enum_type='wms_move_order_FedbatchType' and a.FedbatchType=d.enum_value");
                Params.Add("@Column", " a.ruid,a.order_no,a.order_type,b.enum_desc as order_type_desc,d.enum_desc as FedbatchType_desc,a.order_status,a.remarks,c.enum_desc as order_status_desc,a.ref_order_no,a.factory_id,a.factory_code,a.enabled,a.create_time ");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

                List<WmsMoveHeader> dataList = dbConn.Query<WmsMoveHeader>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", Params.Get<int>("@TotalCount"));
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getMoveDetailListByPage(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.ruid,a.order_no,a.item_no,a.part_id,a.part_no,a.part_spec,a.qty,a.pick_qty,a.out_qty,a.rec_qty,a.unit,a.plan_date,  ");
                SqlStringBuilder.Append(" a.plan_time,b.enum_desc as item_status_desc,item_status,a.src_warehouse_id,a.src_warehouse_name,a.dest_warehouse_id,a.dest_warehouse_name,a.src_zone_id,  ");
                SqlStringBuilder.Append(" a.src_zone_name,a.dest_zone_id,a.dest_zone_name,a.src_erp_warehouse,a.dest_erp_warehouse,a.factory_id,a.factory_code,a.enabled ");
                SqlStringBuilder.Append("from wms_move_detail a (nolock) left join sys_enum b on b.enum_type='wms_move_detail_item_status' and a.item_status=convert(int,b.enum_value)  ");
                SqlStringBuilder.Append(" where " + strWhere);

                List<WmsMoveDetail> dataList = dbConn.Query<WmsMoveDetail>(SqlStringBuilder.ToString()).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", dataList.Count());
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getMoveSnListByPage(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" select  a.ruid,a.order_no,a.item_no,a.part_id,a.part_no,a.part_spec,a.carton_no,a.qty,a.unit,b.enum_desc as statusDesc,status,a.factory_id, ");
                SqlStringBuilder.Append("  a.factory_code,a.enabled from wms_move_sn a (nolock) left join sys_enum b on b.enum_type='wms_move_sn_status' and a.status=convert(int,b.enum_value)  ");
                SqlStringBuilder.Append("  where " + strWhere);

                List<WmsMoveSn> dataList = dbConn.Query<WmsMoveSn>(SqlStringBuilder.ToString()).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", dataList.Count());
                return result;
            }
        }

        public List<KeyValueResult> GetErpwarehouse()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "select distinct erp_warehouse as [key] , erp_warehouse as value from sys_zone where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetMoveOrderType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum  where enum_type='wms_move_order_type' and enabled='Y'";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> getSelectRefOrderNo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = " select distinct ref_order_no  as [value], ref_order_no as [key]  from wms_move_header   where ref_order_no !=''";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetFedbatchType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum  where enum_type='wms_move_order_FedbatchType' and enabled='Y' ORDER BY value ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetFedbatchMethods()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum  where enum_type='wms_Move_Header_Prepare_Material' and enabled='Y' ORDER BY value ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetMoveOrderStatus()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum where enum_type ='wms_move_header_order_status' and enabled='Y' order by enum_value";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 生成条码
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public Hashtable onBarcodeGenerator(string orderNo)
        {
            Hashtable hashtable = new Hashtable();

            return hashtable;
        }

        /// <summary>
        /// 获取下拉框菜单数据   这里显示的是待添加的厂区信息，厂区名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectFactory()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select factory_id as [value],factory_name as [key] from sys_factory where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<KeyValueResult> getSelectWarehouse()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select warehouse_id as [value],warehouse_desc as [key] from sys_warehouse where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<SysWarehouse> getSelectWarehouse(string warehouseid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_warehouse where Enabled = 'Y' and warehouse_id='" + warehouseid + "'");
                List<SysWarehouse> result = dbConn.Query<SysWarehouse>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<SysZone> getSelectZone(string zoneid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_zone where Enabled = 'Y' and zone_id='" + zoneid + "'");
                List<SysZone> result = dbConn.Query<SysZone>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<KeyValueResult> getSelectZone()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select zone_id as [value],zone_name as [key] from sys_zone where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<KeyValueResult> getSelectVendor()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select vendor_id as [value],vendor_name as [key] from sys_Vendor where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<SysVendor> getSelectVendor(string vendorId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_Vendor where Enabled = 'Y' and vendor_id='" + vendorId + "'");
                List<SysVendor> result = dbConn.Query<SysVendor>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetPart(int type)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id as [value],part_no as [key] FROM sys_part (NOLOCK) WHERE enabled='Y'  ORDER BY part_no";
                DynamicParameters param = new DynamicParameters();
                param.Add("@part_type", type);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql, param).ToList();
                return result;
            }
        }
        public List<SysPart> GetPart(int type, string PartNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM sys_part (NOLOCK) WHERE enabled='Y'  and part_no='" + PartNo + "' ORDER BY part_no";
                DynamicParameters param = new DynamicParameters();
                param.Add("@part_type", type);
                List<SysPart> result = dbConn.Query<SysPart>(sql, param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取订单编号
        /// </summary>
        /// <returns></returns>
        public string GetOrderNo(string stockOrder, string p)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", stockOrder);
                list.Add("@order_prefix", p);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

                var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public bool saveMovementManage(WmsMoveHeader htParams, IList<WmsMoveDetail> htDetailParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                string orderNo = GetOrderNo("ShiftProduction", "P");
                if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.PICK)
                {
                    orderNo = GetOrderNo("ShiftProduction", "P");//生产领料单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.EXCEPTION_IN_STOCK)
                {
                    orderNo = GetOrderNo("ShiftSporadicEntry", "I");//零星入库单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.EXCEPTION_OUT_STOCK)
                {
                    orderNo = GetOrderNo("ShiftOutgoing", "O");//零星出库单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.NC_IN_STOCK)
                {
                    orderNo = GetOrderNo("ShiftWarehouse", "M");//NC入库
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.NC_CONCESSION)
                {
                    orderNo = GetOrderNo("ShiftNCOutbound", "R");//NC出库单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.NC_SCRAP)
                {
                    orderNo = GetOrderNo("ShiftNCScrap", "S");//NC报废单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.NO_JIS_SHIPPING)
                {
                    orderNo = GetOrderNo("ShiftNonJis", "F");//非JIS发运单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.NC_MOVE)
                {
                    orderNo = GetOrderNo("ShiftWarehouse", "M");//NC移库单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.FEDBATCH)
                {
                    orderNo = GetOrderNo("ShiftWarehouse", "B");//生产补料单
                }
                else if (htParams.OrderType == (int)WmsEnumUtil.MoveOrderType.STORESRETURNED)
                {
                    orderNo = GetOrderNo("ShiftWarehouse", "T");//生产退料单
                }

                htParams.OrderNo = orderNo;

                dbConn.Open();
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                DynamicParameters dynamic = new DynamicParameters();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("INSERT INTO dbo.wms_move_header(order_no,order_type,order_status,remarks,ref_order_no,factory_id,factory_code,enabled,create_userid,create_time,guid )");
                SqlStringBuilder.Append("VALUES(@orderNo, @orderType, @orderStatus,@remarks,@refOrderNo,@factoryId, @factoryCode, @enabled, @createUserid, CONVERT(varchar(50), GETDATE(), 21), newid() ) ");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                //dynamic.Add("@prepare_material", htParams.prepareMaterial);
                //dynamic.Add("@FedbatchType", htParams.fedbatchType);
                dynamic.Add("@orderNo", htParams.OrderNo);
                dynamic.Add("@orderType", htParams.OrderType);
                dynamic.Add("@orderStatus", 10);
                dynamic.Add("@remarks", htParams.Remarks);
                dynamic.Add("@refOrderNo", htParams.RefOrderNo);
                dynamic.Add("@factoryId", htParams.FactoryId);
                dynamic.Add("@factoryCode", htParams.FactoryCode);
                dynamic.Add("@enabled", "Y");
                dynamic.Add("@createUserid", htParams.CreateUserId);
                parameters.Add(dynamic);

                for (int i = 0; i < htDetailParams.Count; i++)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.wms_move_detail(order_no,item_no,part_id,part_no,part_spec,qty,pick_qty,out_qty,rec_qty,unit,plan_date,plan_time,item_status");
                    SqlStringBuilder.Append(", src_warehouse_id, src_warehouse_name, dest_warehouse_id, dest_warehouse_name, src_zone_id, src_zone_name, dest_zone_id");
                    SqlStringBuilder.Append(", dest_zone_name, src_erp_warehouse, dest_erp_warehouse, factory_id, factory_code, enabled, create_userid, create_time, guid)");
                    SqlStringBuilder.Append("VALUES(@orderNo, @itemNo, @partId, @partNo, @partSpec, @qty, @pickQty, @outQty, @recQty, @unit, @planDate, @planTime, @itemStatus, @srcWarehouseId");
                    SqlStringBuilder.Append(", @srcWarehouseName, @destWarehouseId, @destWarehouseName, @srcZoneId, @srcZoneName, @destZoneId, @destZoneName, @srcErpWarehouse, @destErpWarehouse");
                    SqlStringBuilder.Append(", @factoryId, @factoryCode, @enabled, @createUserid, CONVERT(varchar(50), GETDATE(), 21), newid())");
                    sqlLists.Add(SqlStringBuilder.ToString());
                    dynamic = new DynamicParameters();
                    dynamic.Add("@orderNo", orderNo);
                    dynamic.Add("@itemNo", htDetailParams[i].ItemNo);
                    dynamic.Add("@partId", htDetailParams[i].PartId);
                    dynamic.Add("@partNo", htDetailParams[i].PartNo);
                    dynamic.Add("@partSpec", htDetailParams[i].PartSpec);
                    dynamic.Add("@qty", htDetailParams[i].Qty);
                    dynamic.Add("@pickQty", htDetailParams[i].PickQty);
                    dynamic.Add("@outQty", htDetailParams[i].OutQty);
                    dynamic.Add("@recQty", htDetailParams[i].RecQty);
                    dynamic.Add("@unit", htDetailParams[i].Unit);
                    dynamic.Add("@planDate", htDetailParams[i].PlanDate);
                    dynamic.Add("@planTime", htDetailParams[i].PlanTime);
                    dynamic.Add("@itemStatus", "10");
                    dynamic.Add("@srcWarehouseId", htDetailParams[i].SrcWarehouseId);
                    dynamic.Add("@srcWarehouseName", htDetailParams[i].SrcWarehouseName);
                    dynamic.Add("@destWarehouseId", htDetailParams[i].DestWarehouseId);
                    dynamic.Add("@destWarehouseName", htDetailParams[i].DestWarehouseName);
                    dynamic.Add("@srcZoneId", htDetailParams[i].SrcZoneId);
                    dynamic.Add("@srcZoneName", htDetailParams[i].SrcZoneName);
                    dynamic.Add("@destZoneId", htDetailParams[i].DestZoneId);
                    dynamic.Add("@destZoneName", htDetailParams[i].DestZoneName);
                    dynamic.Add("@srcErpWarehouse", htDetailParams[i].SrcErpWarehouse);
                    dynamic.Add("@destErpWarehouse", htDetailParams[i].DestErpWarehouse);
                    dynamic.Add("@factoryId", htDetailParams[i].FactoryId);
                    dynamic.Add("@factoryCode", htDetailParams[i].FactoryCode);
                    dynamic.Add("@enabled", "Y");
                    dynamic.Add("@createUserid", htDetailParams[i].CreateUserId);
                    parameters.Add(dynamic);
                }

                bool flag = ExecuteTransaction(sqlLists, parameters);
                return flag;
            }
        }
        public bool ExecuteTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList, int? commandTimeout = null)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                bool isClosed = dbConn.State == ConnectionState.Closed;
                if (isClosed)
                {
                    dbConn.Open();
                }
                //开启事务
                using (var transaction = dbConn.BeginTransaction())
                {
                    try
                    {
                        bool successCount = true;
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction, commandTimeout);
                        }
                        //提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        //回滚事务
                        transaction.Rollback();
                        dbConn.Close();
                        dbConn.Dispose();
                        throw ex;
                    }
                    finally
                    {
                        dbConn.Close();
                        dbConn.Dispose();
                    }
                }
            }
        }


        /// <summary>
        /// 获得零件信息
        /// </summary>
        /// <returns></returns>
        public List<SysPart> GetPartNoInfo(string part_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_no LIKE '%" + part_no + "%' ORDER BY part_no";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得零件信息
        /// </summary>
        /// <returns></returns>
        public List<SysPart> GetPartNoInfoByPartNo(string part_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_no = '" + part_no + "'";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得零件简码信息
        /// </summary>
        /// <returns></returns>
        public List<SysPart> GetPartSpecInfo(string partSpec)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_spec LIKE '%" + partSpec + "%' ORDER BY part_no";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }
        public List<SysPart> GetPartSpecInfoByPartSpec(string partSpec)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_spec = '" + partSpec + "'";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }


        /// <summary>
        /// 模糊查询工单号
        /// </summary>
        /// <param name="refOrderNo"></param>
        /// <returns></returns>
        public List<MesWorkOrder> GetWorkOrderInfo(string refOrderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * FROM mes_work_order WHERE order_no like '%" + refOrderNo + "%'  AND enabled='Y' AND order_status=30 ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@order_no", refOrderNo);
                List<MesWorkOrder> result = dbConn.Query<MesWorkOrder>(sql.ToString(), parameters).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得工单中关联单号
        /// </summary>
        /// <returns></returns>
        public List<MesWorkOrder> GetWorkRefOrderNo(string refOrderNo, string orderType)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT * FROM mes_work_order WHERE order_type=@order_type AND order_no=@order_no  AND enabled='Y' AND order_status=30 ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@order_type", orderType);
                parameters.Add("@order_no", refOrderNo);
                List<MesWorkOrder> result = dbConn.Query<MesWorkOrder>(sql.ToString(), parameters).ToList();
                return result;
            }
        }


        //关闭
        public bool onClose(String ids, String empId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                DynamicParameters dynamic = new DynamicParameters();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("update wms_move_header set order_status='60',update_userid=@updateUserid,update_time = CONVERT(varchar(50), GETDATE(), 21)  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@updateUserid", empId);
                parameters.Add(dynamic);

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update wms_move_detail set item_status='100',update_userid=@updateUserid,update_time = CONVERT(varchar(50), GETDATE(), 21)  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@updateUserid", empId);
                parameters.Add(dynamic);

                string sql = "SELECT * FROM wms_move_header (NOLOCK) WHERE order_no in (" + ids + ")";
                List<WmsMoveHeader> wmsMoveHeaders = dbConn.Query<WmsMoveHeader>(sql).ToList();
                for (int i = 0; i < wmsMoveHeaders.Count; i++)
                {
                    if (wmsMoveHeaders[i].OrderType == 10 && wmsMoveHeaders[i].RefOrderNo == "安全库存拉动")//生产领料单
                    {
                        //string sqldetail = "SELECT * FROM wms_move_detail (NOLOCK) WHERE order_no ='" + wmsMoveHeaders[i].OrderNo + "'";
                        //List<WmsMoveDetail> wmsMoveDetails = dbConn.Query<WmsMoveDetail>(sqldetail).ToList();
                        //for (int j = 0; j < wmsMoveDetails.Count; j++)
                        //{
                        SqlStringBuilder = new StringBuilder(1024);//关闭，重置默认库存拉动0
                        SqlStringBuilder.Append("update sys_locate_part set is_safety_pull=0,update_userid=@updateUserid,update_time=@updateTime  where safety_pull_order = @ref_order_no");
                        sqlLists.Add(SqlStringBuilder.ToString());
                        dynamic = new DynamicParameters();
                        dynamic.Add("@ref_order_no", wmsMoveHeaders[i].OrderNo);
                        dynamic.Add("@updateUserid", empId);
                        dynamic.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                        parameters.Add(dynamic);
                        //}
                    }
                }

                bool flag = ExecuteTransaction(sqlLists, parameters);
                return flag;
            }
        }

        //启用
        public bool EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                DynamicParameters dynamic = new DynamicParameters();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("update wms_move_header set Enabled='Y'  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@orderNo", ids);
                parameters.Add(dynamic);

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update wms_move_detail set Enabled='Y'  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@orderNo", ids);
                parameters.Add(dynamic);

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update wms_move_sn set Enabled='Y'  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@orderNo", ids);
                parameters.Add(dynamic);

                bool flag = ExecuteTransaction(sqlLists, parameters);
                return flag;
            }
        }

        //禁用
        public bool DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> sqlLists = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                DynamicParameters dynamic = new DynamicParameters();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("update wms_move_header set Enabled='N'  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@orderNo", ids);
                parameters.Add(dynamic);

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update wms_move_detail set Enabled='N'  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@orderNo", ids);
                parameters.Add(dynamic);

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update wms_move_sn set Enabled='N'  WHERE order_no in (" + ids + ")");
                sqlLists.Add(SqlStringBuilder.ToString());
                dynamic = new DynamicParameters();
                dynamic.Add("@orderNo", ids);
                parameters.Add(dynamic);

                bool flag = ExecuteTransaction(sqlLists, parameters);
                return flag;
            }
        }

        #endregion  成员方法
    }
}