using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.ExcelModel;
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 PullGroupRepository : BaseRepository<WmsPullingGroup>, IPullGroupRepository
    {
        public PullGroupRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法
        /// <summary>
        /// 获得菜单列表数据
        /// </summary>
        public List<WmsPullingGroup> getList(string ruid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from wms_pulling_group (nolock) ");
                if (!ruid.Trim().Equals(""))
                {
                    strSql.Append(" where ruid=" + ruid);
                }

                List<WmsPullingGroup> result = dbConn.Query<WmsPullingGroup>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getPullGroupListByPage(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_pulling_group a (nolock) ");
                Params.Add("@Column", "*");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

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

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getPullGroupDetailListByPage(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.* from wms_pulling_group_part a (nolock)   ");
                SqlStringBuilder.Append("where " + strWhere);

                List<WmsPullingGroupPart> dataList = dbConn.Query<WmsPullingGroupPart>(SqlStringBuilder.ToString()).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", dataList.Count());
                return result;
            }
        }
        public List<WmsPullingGroupPart> getPullGroupDetailList(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.* from wms_pulling_group_part a (nolock)  ");
                SqlStringBuilder.Append("where " + strWhere);
                List<WmsPullingGroupPart> dataList = dbConn.Query<WmsPullingGroupPart>(SqlStringBuilder.ToString()).ToList();
                return dataList;
            }
        }

        public List<SysPart> GetPartInfo(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;
            }
        }

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

        /// <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<KeyValueResult> GetOrderType()
        {
            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_PullGroup_order_type' and enabled='Y'";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).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;
            }
        }

        public List<SysPdline> GetPdlineByPdlineId(int pdlineId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM sys_pdline (NOLOCK) WHERE enabled='Y' and pdline_id='" + pdlineId + "'";
                List<SysPdline> result = dbConn.Query<SysPdline>(sql).ToList();
                return result;
            }
        }
        public SysPdline GetPdlineByPdlineCode(string pdlineCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM sys_pdline (NOLOCK) WHERE pdline_code='" + pdlineCode + "'";
                SysPdline result = dbConn.Query<SysPdline>(sql).FirstOrDefault();
                return result;
            }
        }
        public List<KeyValueResult> GetPdlineByKey()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT distinct pdline_code as [value],pdline_code as [key] FROM sys_pdline (NOLOCK) WHERE enabled='Y'  ORDER BY pdline_code";
                DynamicParameters param = new DynamicParameters();
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(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;
            }
        }

        public bool saveHandPullGroup(string groupCode, string handPullQty)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();
                DynamicParameters parameters = new DynamicParameters();
                #region
                string checkPullingGroupSql = @"select * from [dbo].[wms_pulling_group] where group_code = '" + groupCode + "'";
                List<WmsPullingGroup> wmsPulling = dbConn.Query<WmsPullingGroup>(checkPullingGroupSql).ToList();
                for (int j = 0; j < wmsPulling.Count; j++)
                {
                    string getPullingGroupCumulativeValueSql = @"select * from [dbo].[wms_pulling_group] (nolock) where group_code='" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "'";
                    List<WmsPullingGroup> wmsPullingGroup = dbConn.Query<WmsPullingGroup>(getPullingGroupCumulativeValueSql).ToList();

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("		select * from mes_work_order_part k (nolock)  where k.order_no in (select distinct top " + handPullQty + " a.order_no from mes_work_order_part a (nolock) left join mes_work_order b (nolock) on a.order_no = b.order_no and a.parent_part_no = b.part_no   ");
                    SqlStringBuilder.Append("	where a.part_no in (select part_no from[dbo].[wms_pulling_group_part] a (nolock) left join[dbo].[wms_pulling_group] b (nolock) on a.group_code = b.group_code       ");
                    SqlStringBuilder.Append("	where b.group_code = '" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "' and a.enabled='Y' and b.enabled='Y') and a.pull_status = 10 and pdline_code = '" + wmsPulling[j].PdlineCode + "'  order by a.order_no )  ");
                    SqlStringBuilder.Append("	and k.part_no in (select part_no from[dbo].[wms_pulling_group_part] a (nolock) left join[dbo].[wms_pulling_group] b (nolock) on a.group_code = b.group_code       ");
                    SqlStringBuilder.Append("	where b.group_code = '" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "' and a.enabled='Y' and b.enabled='Y') ");

                    List<MesWorkOrderPart> mesWorkOrderParts = dbConn.Query<MesWorkOrderPart>(SqlStringBuilder.ToString()).ToList();
                    if (mesWorkOrderParts.Count > 0)
                    {
                        List<MesWorkOrderPart> storeInfoList = mesWorkOrderParts.GroupBy(x => new { x.PartNo, x.FactoryId, x.FactoryCode })
                     .Select(g => new MesWorkOrderPart { PartNo = g.Key.PartNo, FactoryId = g.Key.FactoryId, FactoryCode = g.Key.FactoryCode, Qty = g.Sum(b => b.Qty) }).ToList();

                        var orderNo = GetOrderNo("ShiftProduction", "P");//生产领料
                        for (int i = 0; i < storeInfoList.Count; i++)
                        {
                            SqlStringBuilder = new StringBuilder(1024);
                            SqlStringBuilder.Append("INSERT INTO dbo.wms_move_detail ");
                            SqlStringBuilder.Append("           (order_no,item_no,part_id,part_no,part_spec,qty,pick_qty,out_qty,rec_qty ");
                            SqlStringBuilder.Append("           ,unit,plan_date,plan_time,item_status,src_warehouse_id,src_warehouse_name,dest_warehouse_id ");
                            SqlStringBuilder.Append("           ,dest_warehouse_name,src_zone_id,src_zone_name,dest_zone_id,dest_zone_name,src_erp_warehouse,dest_erp_warehouse,factory_id ");
                            SqlStringBuilder.Append("           ,factory_code,enabled,create_userid,create_time,guid) ");
                            SqlStringBuilder.Append("     VALUES(@orderNo,@itemNo,@partId,@partNo,@partSpec,@qty,@pickQty,@outQty,@recQty,@unit,@planDate ");
                            SqlStringBuilder.Append("           ,@planTime,@itemStatus,@srcWarehouseId,@srcWarehouseName,@destWarehouseId,@destWarehouseName ");
                            SqlStringBuilder.Append("		   ,@srcZoneId,@srcZoneName,@destZoneId,@destZoneName,@srcErpWarehouse,@destErpWarehouse ");
                            SqlStringBuilder.Append("           ,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@guid) ");
                            sqlList.Add(SqlStringBuilder.ToString());
                            parameters = new DynamicParameters();
                            parameters.Add("@orderNo", orderNo);//生产领料单
                            parameters.Add("@itemNo", (i + 1) * 10);//序号

                            StringBuilder partNoStringBuilder = new StringBuilder(1024);
                            partNoStringBuilder.Append("select * from sys_part (nolock) where part_no='" + storeInfoList[i].PartNo + "'");//零件号ID
                            List<SysPart> sysParts = dbConn.Query<SysPart>(partNoStringBuilder.ToString()).ToList();
                            if (sysParts.Count > 0)
                            {
                                parameters.Add("@partId", sysParts[0].PartId);//
                                parameters.Add("@partNo", sysParts[0].PartNo);//零件号
                                parameters.Add("@partSpec", sysParts[0].PartSpec);//简码
                                parameters.Add("@unit", sysParts[0].Unit);//单位
                            }
                            else
                            {
                                parameters.Add("@partId", 0);//零件号ID
                                parameters.Add("@partNo", storeInfoList[i].PartNo);//零件号
                                parameters.Add("@partSpec", "");//简码
                                parameters.Add("@unit", "");//单位
                            }

                            parameters.Add("@qty", storeInfoList[i].Qty);//数量
                            parameters.Add("@pickQty", 0);//
                            parameters.Add("@outQty", 0);//
                            parameters.Add("@recQty", 0);//

                            parameters.Add("@planDate", "");//计划日期
                            parameters.Add("@planTime", "");//计划时间
                            parameters.Add("@itemStatus", "10");//状态
                            parameters.Add("@srcWarehouseId", 0);//源仓库ID
                            parameters.Add("@srcWarehouseName", "");//源仓库
                            parameters.Add("@srcZoneId", 0);//源库区ID
                            parameters.Add("@srcZoneName", "");//源库区
                            parameters.Add("@srcErpWarehouse", "");//源ERP库存地

                            string sqlPull = "SELECT * FROM  dbo.wms_pulling_group (nolock) where group_code = '" + wmsPulling[j].GroupCode + "'";
                            WmsPullingGroup pullingGroup = dbConn.QueryFirstOrDefault<WmsPullingGroup>(sqlPull);
                            if (pullingGroup != null)
                            {
                                parameters.Add("@destWarehouseId", 0);//目标仓库ID
                                parameters.Add("@destWarehouseName", "");//目标仓库
                                parameters.Add("@destZoneId", 0);//目标库区ID
                                parameters.Add("@destZoneName", pullingGroup.GroupName);//目标库区
                                parameters.Add("@destErpWarehouse", "");//目标ERP库存地
                            }
                            else
                            {
                                parameters.Add("@destWarehouseId", 0);//目标仓库ID
                                parameters.Add("@destWarehouseName", "");//目标仓库
                                parameters.Add("@destZoneId", 0);//目标库区ID
                                parameters.Add("@destZoneName", "");//目标库区
                                parameters.Add("@destErpWarehouse", "");//目标ERP库存地
                            }

                            parameters.Add("@factoryId", storeInfoList[0].FactoryId);//工厂ID
                            parameters.Add("@factoryCode", storeInfoList[0].FactoryCode);//工厂代码
                            parameters.Add("@enabled", "Y");
                            parameters.Add("@createUserid", 0);
                            parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                            parameters.Add("@guid", Guid.NewGuid().ToString());//
                            parametersList.Add(parameters);
                        }

                        StringBuilder stringBuilderSQL = new StringBuilder(1024);
                        stringBuilderSQL.Append("INSERT INTO dbo.wms_move_header ");
                        stringBuilderSQL.Append("           (order_no,order_type,order_status,ref_order_no,factory_id,factory_code ");
                        stringBuilderSQL.Append("           ,enabled,create_userid,create_time,guid) ");
                        stringBuilderSQL.Append("     VALUES(@orderNo,@orderType,@orderStatus,@refOrderNo ");
                        stringBuilderSQL.Append("           ,@factoryId,@factoryCode,@enabled,@createUserid ");
                        stringBuilderSQL.Append("           ,@createTime,@guid) ");
                        sqlList.Add(stringBuilderSQL.ToString());

                        parameters = new DynamicParameters();
                        parameters.Add("@orderNo", orderNo);//生产领料单
                        parameters.Add("@orderType", (int)WmsEnumUtil.MoveOrderType.PICK);//生产领料单
                        parameters.Add("@orderStatus", (int)WmsEnumUtil.MoveOrderStatus.CREATE);//已创建
                        parameters.Add("@refOrderNo", "成品下线大件拉动");//

                        parameters.Add("@factoryId", mesWorkOrderParts[0].FactoryId);//
                        parameters.Add("@factoryCode", mesWorkOrderParts[0].FactoryCode);//
                        parameters.Add("@enabled", "Y");//
                        parameters.Add("@createUserid", 0);//
                        parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                        parameters.Add("@guid", Guid.NewGuid().ToString());//
                        parametersList.Add(parameters);

                        for (int i = 0; i < mesWorkOrderParts.Count; i++)
                        {
                            for (int k = 0; k < storeInfoList.Count; k++)
                            {
                                stringBuilderSQL = new StringBuilder(1024);
                                stringBuilderSQL.Append("update mes_work_order_part set pull_status =@pullStatus where order_no=@order_no and part_no=@part_no");
                                sqlList.Add(stringBuilderSQL.ToString());
                                parameters = new DynamicParameters();
                                parameters.Add("@order_no", mesWorkOrderParts[i].OrderNo);//id
                                parameters.Add("@part_no", storeInfoList[k].PartNo);//id
                                parameters.Add("@pullStatus", "20");//
                                parametersList.Add(parameters);
                            }
                        }
                    }
                }
                return InsertData(sqlList, parametersList);
                #endregion
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int savePullGroup(WmsPullingGroup htParams, IList<WmsPullingGroupPart> htDetailParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                for (int i = 0; i < htDetailParams.Count; i++)
                {
                    htDetailParams[i].GroupCode = htParams.GroupCode;
                    htDetailParams[i].GroupName = htParams.GroupName;
                }
                dbConn.Open();
                List<string> SqlStrings = new List<string>();
                List<object> Parameters = new List<object>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_pulling_group ");
                SqlStringBuilder.Append("           (group_code,group_name,pulling_type,pdline_code,pdline_name,kitting_qty,triggerValue,pullValue,is_auto_print,print_copies ");
                SqlStringBuilder.Append("		   ,printer,print_template,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
                SqlStringBuilder.Append("     VALUES(@groupCode,@groupName,@pullingType,@pdlineCode,@pdlineName,@kittingQty,@triggerValue,@pullValue,@isAutoPrint,@printCopies ");
                SqlStringBuilder.Append("           ,@printer,@printTemplate,@factoryId,@factoryCode,@enabled,@createUserid,CONVERT(varchar(50), GETDATE(), 21),newid()) ");

                SqlStrings.Add(SqlStringBuilder.ToString());
                Parameters.Add(htParams);

                StringBuilder SqlDetailStringBuilder = new StringBuilder(1024);
                SqlDetailStringBuilder.Append("INSERT INTO dbo.wms_pulling_group_part ");
                SqlDetailStringBuilder.Append("           (group_code,group_name,part_id,part_no,part_spec,pull_qty,factory_id ");
                SqlDetailStringBuilder.Append("           ,factory_code,enabled,create_userid,create_time,guid) ");
                SqlDetailStringBuilder.Append("     VALUES(@groupCode,@groupName,@partId,@partNo,@partSpec,@pullQty,@factoryId ");
                SqlDetailStringBuilder.Append("           ,@factoryCode,@enabled,@createUserid,CONVERT(varchar(50), GETDATE(), 21), newid()) ");

                SqlStrings.Add(SqlDetailStringBuilder.ToString());
                Parameters.Add(htDetailParams);
                IDbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    for (int i = 0; i < SqlStrings.Count; i++)
                    {
                        dbConn.Execute(SqlStrings[i], Parameters[i], transaction);
                    }
                    transaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return 0;
                }
            }
        }

        public int updatePullGroup(WmsPullingGroup htParams, IList<WmsPullingGroupPart> htDetailParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                for (int i = 0; i < htDetailParams.Count; i++)
                {
                    htDetailParams[i].GroupCode = htParams.GroupCode;
                    htDetailParams[i].GroupName = htParams.GroupName;
                }
                dbConn.Open();
                List<string> SqlStrings = new List<string>();
                List<object> Parameters = new List<object>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE dbo.wms_pulling_group ");
                SqlStringBuilder.Append("   SET group_code = @groupCode,group_name = @groupName ");
                SqlStringBuilder.Append("      ,triggerValue = @triggerValue,pullValue = @pullValue ");
                SqlStringBuilder.Append("      ,pulling_type = @pullingType,pdline_name = @pdlineName ");
                SqlStringBuilder.Append("      ,pdline_code = @pdlineCode,kitting_qty = @kittingQty ");
                SqlStringBuilder.Append("      ,is_auto_print = @isAutoPrint,print_copies = @printCopies ");
                SqlStringBuilder.Append("      ,factory_id = @factoryId,factory_code = @factoryCode ");
                SqlStringBuilder.Append("      ,enabled = @enabled,update_userid = @updateUserid ");
                SqlStringBuilder.Append("      ,update_time = @updateTime ");
                SqlStringBuilder.Append(" WHERE group_code=@groupCode ");

                SqlStrings.Add(SqlStringBuilder.ToString());
                Parameters.Add(htParams);

                for (int i = 0; i < htDetailParams.Count; i++)
                {
                    string sql = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE  group_code='" + htDetailParams[i].GroupCode + "' and part_no='" + htDetailParams[i].PartNo + "' ";
                    List<WmsPullingGroupPart> pullingGroupParts = dbConn.Query<WmsPullingGroupPart>(sql).ToList();
                    if (pullingGroupParts.Count > 0)
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("UPDATE dbo.wms_pulling_group_part ");
                        SqlStringBuilder.Append("   SET group_code = @groupCode,group_name = @groupName ");
                        SqlStringBuilder.Append("      ,part_id = @partId,part_no = @partNo ");
                        SqlStringBuilder.Append("      ,part_spec = @partSpec,pull_qty = @pullQty ");
                        SqlStringBuilder.Append("      ,factory_id = @factoryId,factory_code = @factoryCode ");
                        SqlStringBuilder.Append("      ,enabled = @enabled,update_userid = @updateUserid ");
                        SqlStringBuilder.Append("      ,update_time = @updateTime ");
                        SqlStringBuilder.Append(" WHERE group_code = @groupCode and part_no=@partNo ");

                        SqlStrings.Add(SqlStringBuilder.ToString());
                        Parameters.Add(htDetailParams[i]);
                    }
                    else
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.wms_pulling_group_part ");
                        SqlStringBuilder.Append("           (group_code,group_name,part_id,part_no,part_spec,pull_qty,factory_id ");
                        SqlStringBuilder.Append("           ,factory_code,enabled,create_userid,create_time,guid) ");
                        SqlStringBuilder.Append("     VALUES(@groupCode,@groupName,@partId,@partNo,@partSpec,@pullQty,@factoryId ");
                        SqlStringBuilder.Append("           ,@factoryCode,@enabled,@createUserid,CONVERT(varchar(50), GETDATE(), 21), newid()) ");

                        SqlStrings.Add(SqlStringBuilder.ToString());
                        Parameters.Add(htDetailParams[i]);
                    }
                }

                string sqlStr = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE  group_code='" + htParams.GroupCode + "' and factory_code='" + htParams.FactoryCode + "' ";
                List<WmsPullingGroupPart> wmsPullingGroupParts = dbConn.Query<WmsPullingGroupPart>(sqlStr).ToList();

                StringBuilder stringBuilderDel = new StringBuilder(1024);
                for (int i = 0; i < wmsPullingGroupParts.Count; i++)
                {
                    WmsPullingGroupPart wmsPullingGroupPart = htDetailParams.Where(a => a.PartNo == wmsPullingGroupParts[i].PartNo).FirstOrDefault();
                    if (wmsPullingGroupPart == null)
                    {
                        stringBuilderDel.Append("delete from dbo.wms_pulling_group_part (nolock) where  group_code=@groupCode and part_no=@partNo ");

                        SqlStrings.Add(stringBuilderDel.ToString());
                        DynamicParameters dynamicParameters = new DynamicParameters();
                        dynamicParameters.Add("@groupCode", htParams.GroupCode);
                        dynamicParameters.Add("@partNo", wmsPullingGroupParts[i].PartNo);
                        Parameters.Add(dynamicParameters);
                    }
                }

                IDbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    for (int i = 0; i < SqlStrings.Count; i++)
                    {
                        dbConn.Execute(SqlStrings[i], Parameters[i], transaction);
                    }
                    transaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return 0;
                }
            }
        }

        /// <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> 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 int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_pulling_group set Enabled='Y'  WHERE group_code in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                String detailStr = "update wms_pulling_group_part set Enabled='Y'  WHERE group_code in (" + ids + ")";
                int datailResult = dbConn.Execute(detailStr);
                return datailResult;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_pulling_group set Enabled='N'  WHERE group_code in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                String detailStr = "update wms_pulling_group_part set Enabled='N'  WHERE group_code in (" + ids + ")";
                int datailResult = dbConn.Execute(detailStr);
                return datailResult;
            }
        }
        public PullGroup ifExistsPullGroup(string groupCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM wms_pulling_group (NOLOCK) WHERE group_code = '" + groupCode + "'";
                PullGroup result = dbConn.Query<PullGroup>(sql).FirstOrDefault();
                return result;
            }
        }

        public PullGroup ifExistsPullGroupPart(string groupCode, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE group_code = '" + groupCode + "' and part_no='" + partNo + "'";
                PullGroup result = dbConn.Query<PullGroup>(sql).FirstOrDefault();
                return result;
            }
        }

        public List<PullGroup> getExportList(string strWhere, string orderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = @"select b.group_code,b.group_name,case when b.pulling_type=10 then 'JIT蓝卡队列' when b.pulling_type=20 then 'JIT指示票队列' end pulling_type
                              ,b.pdline_code,b.kitting_qty,b.triggerValue,b.pullValue,a.part_id,part_no,part_spec,a.pull_qty 
                               from wms_pulling_group_part a (nolock) left join wms_pulling_group b (nolock) on a.group_code=b.group_code 
                              where " + strWhere + orderBy + " ";
                List<PullGroup> result = dbConn.Query<PullGroup>(sql).ToList();
                return result;
            }
        }

        //事务批量执行添加、修改
        public bool InsertData(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }




        #endregion  成员方法
    }
}