/// <summary> /// /// </summary> /// <param name="inhouseSystemMode"></param> /// <param name="inhousePartClass"></param> /// <param name="partsBoxInfo"></param> /// <returns></returns> public bool UpdatePartsBoxInfo(PartsBoxInfo partsBoxInfo) { string sql = "update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " + "set [PLANT] = @PLANT,[WORKSHOP] = @WORKSHOP,[ASSEMBLY_LINE] = @ASSEMBLY_LINE " + "where [INHOUSE_SYSTEM_MODE] =@INHOUSE_SYSTEM_MODE and [INHOUSE_PART_CLASS] = @INHOUSE_PART_CLASS and [VALID_FLAG] = 1;"; Database db = DatabaseFactory.CreateDatabase(); DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@PLANT", DbType.AnsiString, partsBoxInfo.Plant); db.AddInParameter(cmd, "@WORKSHOP", DbType.AnsiString, partsBoxInfo.Workshop); db.AddInParameter(cmd, "@ASSEMBLY_LINE", DbType.AnsiString, partsBoxInfo.AssemblyLine); db.AddInParameter(cmd, "@INHOUSE_SYSTEM_MODE", DbType.AnsiString, partsBoxInfo.PullMode.ToString()); db.AddInParameter(cmd, "@INHOUSE_PART_CLASS", DbType.AnsiString, partsBoxInfo.BoxParts); return(db.ExecuteNonQuery(cmd) > 0 ? true : false); }
/// <summary> /// /// </summary> /// <param name="fields"></param> /// <param name="id"></param> /// <returns></returns> public bool UpdateInfo(string fields, long id) { int cnt = dal.GetCounts("[ID] = " + id + " and [STATUS] = " + (int)BasicDataStatusConstants.Disabled + ""); if (cnt > 0) { throw new Exception("MC:0x00000742");///已作废的零件类不能进行修改 } ///来源存储区⑧与目标存储区⑩不能为同一存储区 string sourceZoneNo = CommonBLL.GetFieldValue(fields, "SOURCE_ZONE_NO"); string targetZoneNo = CommonBLL.GetFieldValue(fields, "TARGET_ZONE_NO"); if (sourceZoneNo == targetZoneNo) { throw new Exception("MC:0x00000402");///来源存储区与目标存储区不可一致,请重新选取 } string partBoxCode = CommonBLL.GetFieldValue(fields, "PART_BOX_CODE"); string plant = CommonBLL.GetFieldValue(fields, "PLANT"); string workshop = CommonBLL.GetFieldValue(fields, "WORKSHOP"); string assemblyLine = CommonBLL.GetFieldValue(fields, "ASSEMBLY_LINE"); PartsBoxInfo partsBoxInfo = new PartsBoxInfo(); partsBoxInfo.PullMode = (int)PullModeConstants.Plan; partsBoxInfo.BoxParts = partBoxCode; partsBoxInfo.Plant = plant; partsBoxInfo.Workshop = workshop; partsBoxInfo.AssemblyLine = assemblyLine; using (var trans = new TransactionScope()) { new MaintainInhouseLogisticStandardDAL().UpdatePartsBoxInfo(partsBoxInfo); if (dal.UpdateInfo(fields, id) == 0) { return(false); } trans.Complete(); } return(true); }
/// <summary> /// 执行导入EXCEL数据 /// </summary> /// <param name="dataTable"></param> /// <param name="fieldNames"></param> /// <returns></returns> public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser) { List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardExcelInfos = CommonDAL.DatatableConvertToList <MaintainInhouseLogisticStandardInfo>(dataTable).ToList(); if (maintainInhouseLogisticStandardExcelInfos.Count == 0) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } ///获取业务表中要变更的数据集合,准备对比 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList()); List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList()); List <PartsStockInfo> partsStockInfos = new PartsStockDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList()); List <PartsBoxInfo> partsBoxInfos = new PartsBoxDAL().GetList("", string.Empty); ///执行的SQL语句 string sql = string.Empty; List <MaintainInhouseLogisticStandardInfo> standardInfos = new List <MaintainInhouseLogisticStandardInfo>(); fieldNames.Add("SWmNo", "S_WM_NO"); fieldNames.Add("SZoneNo", "S_ZONE_NO"); fieldNames.Add("TWmNo", "T_WM_NO"); fieldNames.Add("TZoneNo", "T_ZONE_NO"); fieldNames.Add("Plant", "PLANT"); fieldNames.Add("Workshop", "WORKSHOP"); fieldNames.Add("AssemblyLine", "ASSEMBLY_LINE"); List <string> fields = new List <string>(fieldNames.Keys); ///逐条处理中间表数据 foreach (var maintainInhouseLogisticStandardExcelInfo in maintainInhouseLogisticStandardExcelInfos) { /// MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo); if (maintainPartsInfo == null) { throw new Exception("MC:0x00000224");///物料基础信息数据错误 } maintainInhouseLogisticStandardExcelInfo.Status = (int)BasicDataStatusConstants.Created; ///物料简称、物料中文描述、物料英文描述由基础数据中同步 maintainInhouseLogisticStandardExcelInfo.PartCname = maintainPartsInfo.PartCname; maintainInhouseLogisticStandardExcelInfo.PartEname = maintainPartsInfo.PartEname; maintainInhouseLogisticStandardExcelInfo.PartNickname = maintainPartsInfo.PartNickname; /// PartsBoxInfo partsBoxInfo = partsBoxInfos.FirstOrDefault(d => d.PullMode.ToString() == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode && d.BoxParts == maintainInhouseLogisticStandardExcelInfo.InhousePartClass); if (partsBoxInfo == null) { throw new Exception("MC:0x00000225");///拉动零件类数据错误 } maintainInhouseLogisticStandardExcelInfo.SWmNo = partsBoxInfo.SWmNo; maintainInhouseLogisticStandardExcelInfo.SZoneNo = partsBoxInfo.SZoneNo; maintainInhouseLogisticStandardExcelInfo.TWmNo = partsBoxInfo.TWmNo; maintainInhouseLogisticStandardExcelInfo.TZoneNo = partsBoxInfo.TZoneNo; maintainInhouseLogisticStandardExcelInfo.Plant = partsBoxInfo.Plant; maintainInhouseLogisticStandardExcelInfo.Workshop = partsBoxInfo.Workshop; maintainInhouseLogisticStandardExcelInfo.AssemblyLine = partsBoxInfo.AssemblyLine; ///目标地点 PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo && d.WmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo && d.ZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo); ///无维护先后要求 if (partsStockInfo != null) { maintainInhouseLogisticStandardExcelInfo.InboundPackageModel = partsStockInfo.InboundPackageModel; maintainInhouseLogisticStandardExcelInfo.InboundPackage = partsStockInfo.InboundPackage; } ///当所选拉动零件类⑥的拉动方式⑤为10时间窗且其配置为库存当量拉动时,MIN⑯和MAX⑰允许维护大于零的数据,且MIN⑯小于MAX⑰ if (int.Parse(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) == (int)PullModeConstants.Twd)///TODO:缺少库存当量拉动的判断,等TWD表结构 { if (maintainInhouseLogisticStandardExcelInfo.Min.GetValueOrDefault() > maintainInhouseLogisticStandardExcelInfo.Max.GetValueOrDefault()) { throw new Exception("MC:0x00000404");///MIN值必须小于MAX } } if (maintainInhouseLogisticStandardExcelInfo.IsTriggerPull.GetValueOrDefault() == true) { if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.WmNo)) { throw new Exception("MC:0x00000405");///层级拉动仓库不允许为空 } if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.ZoneNo)) { throw new Exception("MC:0x00000406");///层级拉动存储区不允许为空 } } /// MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo && d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode && d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass); if (maintainInhouseLogisticStandardInfo == null) { if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.PartNo) || string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) || string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhousePartClass)) { throw new Exception("MC:0x00000226");///物料号、拉动模式、零件类为必填项 } ///相同目标仓库存储区,同物料号同供应商,即使跨拉动方式也需要唯一 int cnt = maintainInhouseLogisticStandardInfos.Where(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo && d.TWmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo && d.TZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo && d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum).Count(); if (cnt > 0) { throw new Exception("MC:0x00000408");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一 } ///物料号①、拉动方式⑤、拉动零件类⑥、供应商代码⑦组合唯一 cnt = maintainInhouseLogisticStandardInfos.Where(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo && d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode && d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass && d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum).Count(); if (cnt > 0) { throw new Exception("MC:0x00000407");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一 } ///字段 string insertFieldString = string.Empty; ///值 string insertValueString = string.Empty; for (int i = 0; i < fields.Count; i++) { string valueStr = CommonDAL.GetFieldValueForSql <MaintainInhouseLogisticStandardInfo>(maintainInhouseLogisticStandardExcelInfo, fields[i]); if (string.IsNullOrEmpty(valueStr)) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } insertFieldString += "[" + fieldNames[fields[i]] + "],"; insertValueString += valueStr + ","; } sql += "if not exists (select * from LES.TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD with(nolock) " + "where [PART_NO] = N'" + maintainInhouseLogisticStandardExcelInfo.PartNo + "' and [INHOUSE_SYSTEM_MODE] = N'" + maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode + "' and [INHOUSE_PART_CLASS] = N'" + maintainInhouseLogisticStandardExcelInfo.InhousePartClass + "' and [VALID_FLAG] = 1) " + "insert into [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] (" + "[FID]," + insertFieldString + "[CREATE_USER]," + "[CREATE_DATE]," + "[VALID_FLAG]" + ") values (" + "NEWID()," ///FID + insertValueString + "N'" + loginUser + "'," ///CREATE_USER + "GETDATE()," ///CREATE_DATE + "1" ///VALID_FLAG + ");"; maintainInhouseLogisticStandardInfos.Add(maintainInhouseLogisticStandardExcelInfo); continue; } /// if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.PartNo) || string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) || string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhousePartClass)) { throw new Exception("MC:0x00000226");///物料号、拉动模式、零件类为必填项 } ///相同目标仓库存储区,同物料号同供应商,即使跨拉动方式也需要唯一 int count = maintainInhouseLogisticStandardInfos.Where(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo && d.TWmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo && d.TZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo && d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum && d.Id != maintainInhouseLogisticStandardInfo.Id).Count(); if (count > 0) { throw new Exception("MC:0x00000408");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一 } ///物料号①、拉动方式⑤、拉动零件类⑥、供应商代码⑦组合唯一 count = maintainInhouseLogisticStandardInfos.Where(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo && d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode && d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass && d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum && d.Id != maintainInhouseLogisticStandardInfo.Id).Count(); if (count > 0) { throw new Exception("MC:0x00000407");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一 } ///值 string valueString = string.Empty; for (int i = 0; i < fields.Count; i++) { string valueStr = CommonDAL.GetFieldValueForSql <MaintainInhouseLogisticStandardInfo>(maintainInhouseLogisticStandardExcelInfo, fields[i]); if (string.IsNullOrEmpty(valueStr)) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ","; } sql += "update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] set " + valueString + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + maintainInhouseLogisticStandardInfo.Id + ";"; } /// if (string.IsNullOrEmpty(sql)) { return(false); } return(CommonDAL.ExecuteNonQueryBySql(sql)); }