/// <summary> /// TwdPartBoxInfo -> TwdPullOrderInfo /// </summary> /// <param name="twdPartBoxInfo"></param> /// <param name="info"></param> public static void GetTwdPullOrderInfo(TwdPartBoxInfo twdPartBoxInfo, ref TwdPullOrderInfo info) { if (twdPartBoxInfo == null) { return; } ///PART_BOX_CODE,零件类代码 info.PartBoxCode = twdPartBoxInfo.PartBoxCode; ///PART_BOX_NAME,零件类名称 info.PartBoxName = twdPartBoxInfo.PartBoxName; ///PLANT,工厂代码 info.Plant = twdPartBoxInfo.Plant; ///WORKSHOP,车间代码 info.Workshop = twdPartBoxInfo.Workshop; ///ASSEMBLY_LINE,生产线代码 info.AssemblyLine = twdPartBoxInfo.AssemblyLine; ///ROUTE_CODE,物流路径 info.RouteCode = twdPartBoxInfo.RouteCode; ///SUPPLIER_NUM,供应商代码 info.SupplierNum = twdPartBoxInfo.SupplierNum; ///S_ZONE_NO,来源存储区 info.SZoneNo = twdPartBoxInfo.SZoneNo; ///S_WM_NO,来源仓库 info.SWmNo = twdPartBoxInfo.SWmNo; ///T_ZONE_NO,目标存储区 info.TZoneNo = twdPartBoxInfo.TZoneNo; ///T_WM_NO,目标仓库 info.TWmNo = twdPartBoxInfo.TWmNo; ///DOCK,道口代码 info.Dock = twdPartBoxInfo.Dock; ///PLAN_SHIPPING_TIME,预计发货时间 info.PlanShippingTime = info.PlanDeliveryTime.GetValueOrDefault().AddMinutes(0 - twdPartBoxInfo.TransportTime.GetValueOrDefault() - twdPartBoxInfo.LoadTime.GetValueOrDefault()); }
/// <summary> /// TwdPartBoxInfo -> TwdCounterInfo /// </summary> /// <param name="twdPartBoxInfo"></param> /// <param name="twdCounterInfo"></param> public static void GetTwdCounterInfo(TwdPartBoxInfo twdPartBoxInfo, ref TwdCounterInfo twdCounterInfo) { if (twdPartBoxInfo == null) { return; } twdCounterInfo.RequirementAccumulateMode = twdPartBoxInfo.RequirementAccumulateMode; twdCounterInfo.RoundnessMode = twdPartBoxInfo.RoundnessMode; }
/// <summary> /// TwdPartBoxInfo -> TwdCounterLogInfo /// </summary> /// <param name="twdPartBoxInfo"></param> /// <param name="twdCounterLogInfo"></param> public static void GetTwdCounterLogInfo(TwdPartBoxInfo twdPartBoxInfo, ref TwdCounterLogInfo twdCounterLogInfo) { if (twdPartBoxInfo == null) { return; } ///REQUIREMENT_ACCUMULATE_MODE twdCounterLogInfo.RequirementAccumulateMode = twdPartBoxInfo.RequirementAccumulateMode; }
/// <summary> /// LogicDeleteInfo /// </summary> /// <param name="id"></param> /// <param name="loginUser"></param> /// <returns></returns> public bool LogicDeleteInfo(long id, string loginUser) { TwdPartBoxInfo info = dal.GetInfo(id); if (info == null) { throw new Exception("MC:0x00000084");///数据有误 } if (info.Status != (int)BasicDataStatusConstants.Created) { throw new Exception("MC:0x00000415");///已创建状态才可进行删除 } List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("" + "[INHOUSE_PART_CLASS] = N'" + info.PartBoxCode + "' and " + "[INHOUSE_SYSTEM_MODE] in ('" + (int)PullModeConstants.Twd + "','" + (int)PullModeConstants.Pcs + "')", string.Empty); StringBuilder @string = new StringBuilder(); @string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] " + "set [VALID_FLAG] = 0," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + info.Id + ";"); foreach (var maintainInhouseLogisticStandardInfo in maintainInhouseLogisticStandardInfos) { if (maintainInhouseLogisticStandardInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Enable) { throw new Exception("MC:0x00000423");///零件类下存在已启用状态的物料拉动信息 } @string.AppendLine("update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " + "set [VALID_FLAG] = 0," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + maintainInhouseLogisticStandardInfo.Id + ";"); } ///执行 using (TransactionScope trans = new TransactionScope()) { if (@string.Length > 0) { CommonDAL.ExecuteNonQueryBySql(@string.ToString()); } 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) { DateTime dtDate; for (int i = 0; i < dataTable.Rows.Count; i++) { if (!DateTime.TryParse(dataTable.Rows[i]["WorkDay"].ToString(), out dtDate)) { throw new Exception("MC:0x00000393");///日期格式不正确 } if (!DateTime.TryParse(dataTable.Rows[i]["WindowTime"].ToString(), out dtDate)) { throw new Exception("MC:0x00000393");///日期格式不正确 } } ///导入数据 List <TwdWindowTimeInfo> twdWindows = CommonDAL.DatatableConvertToList <TwdWindowTimeInfo>(dataTable).ToList(); if (twdWindows.Count == 0) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } ///比对数据 List <TwdWindowTimeInfo> timeInfos = dal.GetList("[PART_BOX_CODE] in ('" + string.Join("','", twdWindows.Select(d => d.PartBoxCode).ToArray()) + "') and [WORK_DAY] in ('" + string.Join("','", twdWindows.Select(d => d.WorkDay).ToArray()) + "') and [WINDOW_TIME] in ('" + string.Join("','", twdWindows.Select(d => d.WindowTime).ToArray()) + "')", string.Empty).ToList(); ///零件类数据 List <TwdPartBoxInfo> twdParts = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("','", twdWindows.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty).ToList(); if (twdParts.Count() != twdWindows.Count()) { throw new Exception("MC:0x00000225");///拉动零件类数据错误 } List <string> fields = new List <string>(fieldNames.Keys); StringBuilder stringBuilder = new StringBuilder(); foreach (var item in twdWindows) { TwdWindowTimeInfo timeInfo = timeInfos.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode && d.WorkDay == item.WorkDay && d.WindowTime == item.WindowTime); TwdPartBoxInfo boxInfo = twdParts.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode); ///发单时间 = 工作日年月日 + 窗口时间时分秒 - 提前时间 int advanceTime = boxInfo.RequirementAccumulateTime.GetValueOrDefault() + ///需求累积时间 boxInfo.LoadTime.GetValueOrDefault() + ///装货时间 boxInfo.TransportTime.GetValueOrDefault() + ///运输时间 boxInfo.UnloadTime.GetValueOrDefault(); ///卸货时间 item.SendTime = item.WindowTime.GetValueOrDefault().AddMinutes(-advanceTime); if (timeInfo == null) { if (item.WindowTime == null || item.WorkDay == null || item.PartBoxCode == null) { throw new Exception("MC:0x00000510");///零件类工作日窗口时间不可为空 } stringBuilder.Append("insert into [LES].[TT_MPM_TWD_WINDOW_TIME]([FID],[PART_BOX_FID],[PART_BOX_CODE],[PART_BOX_NAME],[PLANT],[WORKSHOP],[ASSEMBLY_LINE],[SUPPLIER_NUM],[WORK_DAY],[SEND_TIME],[WINDOW_TIME],[SEND_TIME_STATUS],[TIME_ZONE],[COMMENTS],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])values("); stringBuilder.Append("newid(),N'" + boxInfo.Fid + "',N'" + item.PartBoxCode + "',N'" + boxInfo.PartBoxName + "',N'" + boxInfo.Plant + "',N'" + boxInfo.Workshop + "',N'" + boxInfo.AssemblyLine + "',N'" + boxInfo.SupplierNum + "',N'" + item.WorkDay + "',N'" + item.SendTime + "',N'" + item.WindowTime + "'," + (int)SendTimeStatusConstants.NoSend + ",N'" + item.TimeZone + "',N'" + item.Comments + "',1,GETDATE(),N'" + loginUser + "'"); stringBuilder.Append(");"); timeInfos.Add(item); continue; } if (timeInfo.SendTimeStatus != (int)SendTimeStatusConstants.NoSend) { throw new Exception("MC:0x00000311");///发单状态⑪为10未发单才允许被修改 } if (item.WindowTime == null || item.WorkDay == null || item.PartBoxCode == null) { throw new Exception("MC:0x00000510");///零件类工作日窗口时间不可为空 } stringBuilder.Append("update [LES].[TT_MPM_TWD_WINDOW_TIME] set [PLANT] = N'" + boxInfo.Plant + "',[WORKSHOP] = N'" + boxInfo.Workshop + "',[ASSEMBLY_LINE] = N'" + boxInfo.AssemblyLine + "',[SUPPLIER_NUM] = N'" + boxInfo.SupplierNum + "',[SEND_TIME] = N'" + item.SendTime + "',[COMMENTS] = N'" + item.Comments + "',[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] = " + timeInfo.Id + ";"); } if (string.IsNullOrEmpty(stringBuilder.ToString())) { throw new Exception("MC:0x00000283");///没有可导入更新的数据 } return(CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString())); }
/// <summary> /// Handler /// </summary> public void Handler() { ///获取发单状态⑪为10未发单,且发单时间⑨已小于等于当前时间的时间窗窗口时间数据 ///按发单时间⑨从早到晚进行排序,若发单时间⑨相同的情况下按ID排序,如果其中有相同零件类的多条窗口时间数据则以发单时间⑨最晚的一条作为有效数据 List <TwdWindowTimeInfo> twdWindowTimeInfos = new TwdWindowTimeBLL().GetList("" + "[SEND_TIME_STATUS] = " + (int)SendTimeStatusConstants.NoSend + " and " + "[SEND_TIME] <= GETDATE() and " + "[SEND_TIME] = (select max([SEND_TIME]) from [LES].[TT_MPM_TWD_WINDOW_TIME] a with(nolock) " + "where [LES].[TT_MPM_TWD_WINDOW_TIME].[PART_BOX_FID] = a.[PART_BOX_FID])", "[ID]"); if (twdWindowTimeInfos.Count == 0) { return; } ///根据窗口时间中的零件类外键①获取时间窗零件类、同时获取对应的物料拉动信息、注意此处的数据都需要为已启用状态 List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxBLL().GetList("" + "[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " + "[FID] in ('" + string.Join("','", twdWindowTimeInfos.Select(d => d.PartBoxFid.GetValueOrDefault()).ToArray()) + "')", string.Empty); if (twdPartBoxInfos.Count == 0) { return; } ///物料拉动信息 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardBLL().GetList("" + "[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " + "[INHOUSE_PART_CLASS] in ('" + string.Join("','", twdPartBoxInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " + "[INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Twd + "'", string.Empty); if (maintainInhouseLogisticStandardInfos.Count == 0) { return; } ///供应商信息 List <string> supplierNums = twdPartBoxInfos.Where(d => !string.IsNullOrEmpty(d.SupplierNum)).Select(d => d.SupplierNum).ToList(); supplierNums.AddRange(maintainInhouseLogisticStandardInfos.Where(d => !string.IsNullOrEmpty(d.SupplierNum)).Select(d => d.SupplierNum).ToList()); List <SupplierInfo> supplierInfos = new SupplierBLL().GetList("" + "[SUPPLIER_NUM] in ('" + string.Join("','", supplierNums.ToArray()) + "')", string.Empty); ///逐条进行处理 foreach (TwdWindowTimeInfo twdWindowTimeInfo in twdWindowTimeInfos) { ///获取未发单时间窗对应的零件类 TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.Fid.GetValueOrDefault() == twdWindowTimeInfo.PartBoxFid.GetValueOrDefault()); if (twdPartBoxInfo == null) { continue; } ///获取零件类对应的物料拉动信息 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = maintainInhouseLogisticStandardInfos.Where(d => d.InhousePartClass == twdWindowTimeInfo.PartBoxCode).ToList(); if (maintainInhouseLogisticStandards.Count == 0) { continue; } ///根据已获得的物料拉动信息获取对应的计数器,此处可以直接过滤出其当前计数⑮大于零的数据 List <TwdCounterInfo> twdCounterInfos = new TwdCounterBLL().GetList("" + "[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " + "[PART_PULL_FID] in ('" + string.Join("','", maintainInhouseLogisticStandards.Select(d => d.Fid).ToArray()) + "') and " + "isnull([CURRENT_QTY],0) > 0", string.Empty); if (twdCounterInfos.Count == 0) { continue; } ///生成拉动单 StringBuilder @string = new StringBuilder(); @string.AppendLine(CreateTwdPullOrder(twdCounterInfos, twdPartBoxInfo, supplierInfos, twdWindowTimeInfo, maintainInhouseLogisticStandards)); ///数据库语句执行 using (TransactionScope trans = new TransactionScope()) { if (@string.Length > 0) { CommonBLL.ExecuteNonQueryBySql(@string.ToString()); } trans.Complete(); } } }
/// <summary> /// 生成拉动单 /// </summary> /// <param name="twdPartBoxInfo"></param> /// <param name="twdWindowTimeInfo"></param> /// <param name="supplierInfo"></param> /// <param name="twdCounterInfos"></param> /// <param name="maintainInhouseLogisticStandardInfos"></param> /// <returns></returns> private string CreateTwdPullOrder(List <TwdCounterInfo> twdCounterInfos, TwdPartBoxInfo twdPartBoxInfo, List <SupplierInfo> supplierInfos, TwdWindowTimeInfo twdWindowTimeInfo, List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos) { /// StringBuilder @string = new StringBuilder(); ///Create TwdPullOrderInfo TwdPullOrderInfo twdPullOrderInfo = null; ///Create PcsPullOrderInfo PcsPullOrderInfo pcsPullOrderInfo = null; ///仓储衔接主表 MaterialPullingOrderInfo materialPulling = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo(); if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() == (int)TwdPullModeConstants.Pcs) { pcsPullOrderInfo = PcsPullOrderBLL.CreatePcsPullOrderInfo(loginUser); ///TwdWindowTimeInfo -> PcsPullOrderInfo PcsPullOrderBLL.GetPcsPullOrderInfo(twdWindowTimeInfo, ref pcsPullOrderInfo); ///TwdPartBoxInfo -> PcsPullOrderInfo PcsPullOrderBLL.GetPcsPullOrderInfo(twdPartBoxInfo, ref pcsPullOrderInfo); ///SupplierInfo -> PcsPullOrderInfo SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == twdPartBoxInfo.SupplierNum); PcsPullOrderBLL.GetPcsPullOrderInfo(supplierInfo, ref pcsPullOrderInfo); /// @string.AppendLine(PcsPullOrderDAL.GetInsertSql(pcsPullOrderInfo)); ///PcsPullOrderInfo -> MaterialPullingOrderInfo MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(pcsPullOrderInfo, ref materialPulling); } else { twdPullOrderInfo = TwdPullOrderBLL.CreateTwdPullOrderInfo(loginUser); ///TwdWindowTimeInfo -> TwdPullOrderInfo TwdPullOrderBLL.GetTwdPullOrderInfo(twdWindowTimeInfo, ref twdPullOrderInfo); ///TwdPartBoxInfo -> TwdPullOrderInfo TwdPullOrderBLL.GetTwdPullOrderInfo(twdPartBoxInfo, ref twdPullOrderInfo); ///SupplierInfo -> TwdPullOrderInfo SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == twdPartBoxInfo.SupplierNum); TwdPullOrderBLL.GetTwdPullOrderInfo(supplierInfo, ref twdPullOrderInfo); /// @string.AppendLine(TwdPullOrderDAL.GetInsertSql(twdPullOrderInfo)); ///TwdPullOrderInfo -> MaterialPullingOrderInfo MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(twdPullOrderInfo, ref materialPulling); } ///行号 int rowNo = 0; ///循环计数器 foreach (TwdCounterInfo twdCounterInfo in twdCounterInfos) { ///物料需求数量 decimal requiredPartQty = 0; ///物料需求箱数 int packageQty = 0; #region 圆整方式 ///向上圆整 if (twdCounterInfo.RoundnessMode == (int)RoundnessModeConstants.Upward) { if (twdCounterInfo.Package.GetValueOrDefault() > 0) { ///根据计数器中的圆整方式⑨,当圆整方式⑨为20.向上时以Math.Ceiling(当前计数⑮/箱内数量⑯)*箱内数量⑯作为本次的物料需求数量 requiredPartQty = Math.Ceiling(twdCounterInfo.CurrentQty.GetValueOrDefault() / twdCounterInfo.Package.GetValueOrDefault()) * twdCounterInfo.Package.GetValueOrDefault(); packageQty = Convert.ToInt32(Math.Ceiling(requiredPartQty / twdCounterInfo.Package.GetValueOrDefault())); } } ///向下圆整 if (twdCounterInfo.RoundnessMode == (int)RoundnessModeConstants.Downward) { if (twdCounterInfo.Package.GetValueOrDefault() > 0) { ///根据计数器中的圆整方式⑨,当圆整方式⑨为20.向上时以Math.Ceiling(当前计数⑮/箱内数量⑯)*箱内数量⑯作为本次的物料需求数量 requiredPartQty = Math.Floor(twdCounterInfo.CurrentQty.GetValueOrDefault() / twdCounterInfo.Package.GetValueOrDefault()) * twdCounterInfo.Package.GetValueOrDefault(); packageQty = Convert.ToInt32(Math.Ceiling(requiredPartQty / twdCounterInfo.Package.GetValueOrDefault())); } } ///按需求数量 if (twdCounterInfo.RoundnessMode == (int)RoundnessModeConstants.Ondemand) { ///当圆整方式⑨为按需时则当前计数⑮直接作为本次的物料需求数量 requiredPartQty = twdCounterInfo.CurrentQty.GetValueOrDefault(); packageQty = Convert.ToInt32(Math.Ceiling(requiredPartQty / twdCounterInfo.Package.GetValueOrDefault())); } #endregion ///获取物料拉动信息 MaintainInhouseLogisticStandardInfo logisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == twdCounterInfo.PartPullFid.GetValueOrDefault()); if (logisticStandardInfo == null) { continue; } ///若计数器对应的物料拉动信息中最小起订包装数为一个大于零的数字,则需要在此处比对物料需求箱数是否大于等于最小起订包装数,若不满足则不产生拉动 if (logisticStandardInfo.MinPullBox.GetValueOrDefault() > 0 && packageQty < logisticStandardInfo.MinPullBox.GetValueOrDefault()) { continue; } ///若计数器对应的物料拉动信息中批量包装数为一个大于零的数字,则需要Math.Floor(物料需求箱数 / 批量包装数) * 批量包装数,作为新的物料需求箱数,同时* 箱内数量⑯获得新的物料需求数量 if (logisticStandardInfo.BatchPullBox.GetValueOrDefault() > 0 && twdCounterInfo.Package.GetValueOrDefault() > 0) { packageQty = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(packageQty) / logisticStandardInfo.BatchPullBox.GetValueOrDefault())) * logisticStandardInfo.BatchPullBox.GetValueOrDefault(); requiredPartQty = packageQty * twdCounterInfo.Package.GetValueOrDefault(); } ///仓储衔接明细表 MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo(); if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() == (int)TwdPullModeConstants.Pcs) { /// PcsPullOrderDetailInfo pullOrderDetailInfo = PcsPullOrderDetailBLL.CreatePcsPullOrderDetailInfo(loginUser); ///MaintainInhouseLogisticStandardInfo -> TwdPullOrderDetailInfo PcsPullOrderDetailBLL.GetPcsPullOrderDetailInfo(logisticStandardInfo, ref pullOrderDetailInfo); ///TwdPullOrderInfo -> TwdPullOrderDetailInfo PcsPullOrderDetailBLL.GetPcsPullOrderDetailInfo(pcsPullOrderInfo, ref pullOrderDetailInfo); ///ROW_NO,行号 pullOrderDetailInfo.RowNo = ++rowNo; ///REQUIRED_PACKAGE_QTY,需求包装数 pullOrderDetailInfo.RequiredPackageQty = packageQty; ///REQUIRED_PART_QTY,需求物料数量 pullOrderDetailInfo.RequiredPartQty = requiredPartQty; /// @string.AppendLine(PcsPullOrderDetailDAL.GetInsertSql(pullOrderDetailInfo)); ///TwdPullOrderDetailInfo -> MaterialPullingOrderDetailInfo MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(pullOrderDetailInfo, ref detailInfo); } else { /// TwdPullOrderDetailInfo pullOrderDetailInfo = TwdPullOrderDetailBLL.CreateTwdPullOrderDetailInfo(loginUser); ///MaintainInhouseLogisticStandardInfo -> TwdPullOrderDetailInfo TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(logisticStandardInfo, ref pullOrderDetailInfo); ///TwdPullOrderInfo -> TwdPullOrderDetailInfo TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(twdPullOrderInfo, ref pullOrderDetailInfo); ///ROW_NO,行号 pullOrderDetailInfo.RowNo = ++rowNo; ///REQUIRED_PACKAGE_QTY,需求包装数 pullOrderDetailInfo.RequiredPackageQty = packageQty; ///REQUIRED_PART_QTY,需求物料数量 pullOrderDetailInfo.RequiredPartQty = requiredPartQty; /// @string.AppendLine(TwdPullOrderDetailDAL.GetInsertSql(pullOrderDetailInfo)); ///TwdPullOrderDetailInfo -> MaterialPullingOrderDetailInfo MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(pullOrderDetailInfo, ref detailInfo); } /// materialPulling.MaterialPullingOrderDetailInfos.Add(detailInfo); #region 扣减计数器 ///最后根据物料需求数量扣减对应的计数器的当前计数⑮ @string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] " + "set [CURRENT_QTY] = isnull([CURRENT_QTY],0) - " + requiredPartQty + "," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID]= " + twdCounterInfo.Id + ";"); /// TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser); ///TwdCounterInfo -> TwdCounterLogInfo TwdCounterLogBLL.GetTwdCounterLogInfo(twdCounterInfo, ref twdCounterLogInfo); ///PART_QTY,物料数量 twdCounterLogInfo.PartQty = 0 - requiredPartQty; ///SOURCE_DATA,目视来源数据 twdCounterLogInfo.SourceData = twdPullOrderInfo.OrderCode; ///SOURCE_DATA_FID,数据来源外键 twdCounterLogInfo.SourceDataFid = twdPullOrderInfo.Fid; ///SOURCE_DATA_TYPE,数据来源类型 twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.CreateRunsheet; /// @string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo)); #endregion } ///更新时间窗窗口时间为已发单状态 @string.AppendLine("update [LES].[TT_MPM_TWD_WINDOW_TIME] " + "set [SEND_TIME_STATUS]= " + (int)SendTimeStatusConstants.Sent + "," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID]= " + twdWindowTimeInfo.Id + ";"); ///拉动单生成后需要调用拉动仓储衔接函数获取语句 @string.AppendLine(MaterialPullingCommonBLL.Handler(materialPulling, loginUser)); return(@string.ToString()); }
/// <summary> /// 层级拉动 /// </summary> /// <param name="maintainInhouseLogisticStandardInfo"></param> /// <param name="requireQty"></param> /// <param name="loginUser"></param> /// <param name="counterFid"></param> /// <param name="partBoxCode"></param> /// <returns></returns> public static string LevelPullCounter(MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo, decimal requireQty, string loginUser, Guid counterFid, TwdPartBoxInfo twdPartBoxInfo) { ///未能成功获取零件类信息 if (twdPartBoxInfo == null) { return(string.Empty); } ///零件类未启用 if (twdPartBoxInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable) { return(string.Empty); } ///根据物料拉动信息外键获取计数器,未能成功获取时需要创建 TwdCounterInfo twdCounterInfo = new TwdCounterDAL().GetInfoByPartPullFid(maintainInhouseLogisticStandardInfo.Fid.GetValueOrDefault()); if (twdCounterInfo == null) { ///创建计数器 twdCounterInfo = CreateTwdCounterInfo(loginUser); ///以物料拉动信息填充计数器 GetTwdCounterInfo(maintainInhouseLogisticStandardInfo, ref twdCounterInfo); ///以零件类信息填充计数器 GetTwdCounterInfo(twdPartBoxInfo, ref twdCounterInfo); /// twdCounterInfo.Id = new TwdCounterDAL().Add(twdCounterInfo); if (twdCounterInfo.Id == 0) { throw new Exception("MC:0x00000453");///时间窗计数器创建失败 } } ///计数器状态未处于启用 if (twdCounterInfo.Status != (int)BasicDataStatusConstants.Enable) { return(string.Empty); } /// StringBuilder stringBuilder = new StringBuilder(); /// stringBuilder.AppendLine(UpdateTwdCounter(maintainInhouseLogisticStandardInfo, twdPartBoxInfo, requireQty, twdCounterInfo.Id, loginUser)); ///创建计数器日志 TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser); ///以物料拉动信息填充计数器日志 TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandardInfo, ref twdCounterLogInfo); ///以零件类信息填充计数器日志 TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo); ///PART_QTY twdCounterLogInfo.PartQty = requireQty; ///SOURCE_DATA_FID twdCounterLogInfo.SourceDataFid = counterFid; ///SOURCE_DATA_TYPE twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Calculator; ///SOURCE_DATA twdCounterLogInfo.SourceData = twdPartBoxInfo.PartBoxCode; /// stringBuilder.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo)); ///触发层级拉动 stringBuilder.AppendLine(LevelPullCounter( maintainInhouseLogisticStandardInfo, requireQty, loginUser, twdCounterInfo.Fid.GetValueOrDefault(), twdPartBoxInfo)); return(stringBuilder.ToString()); }
/// <summary> /// 获取更新计数器的语句 /// </summary> /// <param name="maintainInhouseLogisticStandardInfo"></param> /// <param name="twdPartBoxInfo"></param> /// <param name="requireQty"></param> /// <param name="twdCounterId"></param> /// <param name="loginUser"></param> /// <returns></returns> public static string UpdateTwdCounter(MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo, TwdPartBoxInfo twdPartBoxInfo, decimal requireQty, long twdCounterId, string loginUser) { return("update [LES].[TT_MPM_TWD_COUNTER] set " + "[PART_BOX_CODE] = N'" + maintainInhouseLogisticStandardInfo.InhousePartClass + "'," + "[PLANT] = N'" + maintainInhouseLogisticStandardInfo.Plant + "'," + "[PLANT_ZONE] = N'" + maintainInhouseLogisticStandardInfo.PlantZone + "'," + "[WORKSHOP] = N'" + maintainInhouseLogisticStandardInfo.Workshop + "'," + "[ASSEMBLY_LINE] = N'" + maintainInhouseLogisticStandardInfo.AssemblyLine + "'," + "[WORKSHOP_SECTION] = N'" + maintainInhouseLogisticStandardInfo.WorkshopSection + "'," + "[LOCATION] = N'" + maintainInhouseLogisticStandardInfo.Location + "'," + "[SUPPLIER_NUM] = N'" + maintainInhouseLogisticStandardInfo.SupplierNum + "'," + "[REQUIREMENT_ACCUMULATE_MODE] = " + twdPartBoxInfo.RequirementAccumulateMode.GetValueOrDefault() + "," + "[ROUNDNESS_MODE] = " + twdPartBoxInfo.RoundnessMode + "," + "[PART_NO] = N'" + maintainInhouseLogisticStandardInfo.PartNo + "'," + "[PART_CNAME] = N'" + maintainInhouseLogisticStandardInfo.PartCname + "'," + "[PACKAGE] = " + maintainInhouseLogisticStandardInfo.InboundPackage.GetValueOrDefault() + "," + "[PACKAGE_MODEL] = N'" + maintainInhouseLogisticStandardInfo.InboundPackageModel + "'," + "[CURRENT_QTY] = isnull([CURRENT_QTY],0) + " + requireQty + "," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' where " + "[ID] = " + twdCounterId + ";"); }
/// <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 <TwdCounterInfo> twdCounterExcelInfos = CommonDAL.DatatableConvertToList <TwdCounterInfo>(dataTable).ToList(); if (twdCounterExcelInfos.Count == 0) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } List <TwdCounterInfo> twdCounterInfos = dal.GetList("" + "[PART_BOX_CODE] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " + "[PART_NO] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartNo).ToArray()) + "') and " + "[PACKAGE_MODEL] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PackageModel).ToArray()) + "')", string.Empty); List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("" + "[INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Twd + "' and " + "[INHOUSE_PART_CLASS] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " + "[PART_NO] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty); ///获取零件类信息 List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("" + "[PART_BOX_CODE] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty); StringBuilder @string = new StringBuilder(); foreach (TwdCounterInfo twdCounterExcelInfo in twdCounterExcelInfos) { /// 零件类代码②、工厂③车间⑤生产线⑥工段⑬工位⑭、物料号⑩、物料版本⑫、包装容器⑰为联合主键,差异数量DiffQty作为累加当前计数⑮的依据,备注直接更新 TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel && d.Plant == twdCounterExcelInfo.Plant && d.Workshop == twdCounterExcelInfo.Workshop && d.AssemblyLine == twdCounterExcelInfo.AssemblyLine && d.WorkshopSection == twdCounterExcelInfo.WorkshopSection && d.Location == twdCounterExcelInfo.Location && d.PartVersion == twdCounterExcelInfo.PartVersion); #region 逐步减低维度处理 if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel && d.Plant == twdCounterExcelInfo.Plant && d.Workshop == twdCounterExcelInfo.Workshop && d.AssemblyLine == twdCounterExcelInfo.AssemblyLine && d.WorkshopSection == twdCounterExcelInfo.WorkshopSection && d.Location == twdCounterExcelInfo.Location); } if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel && d.Plant == twdCounterExcelInfo.Plant && d.Workshop == twdCounterExcelInfo.Workshop && d.AssemblyLine == twdCounterExcelInfo.AssemblyLine && d.WorkshopSection == twdCounterExcelInfo.WorkshopSection); } if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel && d.Plant == twdCounterExcelInfo.Plant && d.Workshop == twdCounterExcelInfo.Workshop && d.AssemblyLine == twdCounterExcelInfo.AssemblyLine); } if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel && d.Plant == twdCounterExcelInfo.Plant && d.Workshop == twdCounterExcelInfo.Workshop); } if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel && d.Plant == twdCounterExcelInfo.Plant); } if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo && d.PackageModel == twdCounterExcelInfo.PackageModel); } if (twdCounterInfo == null) { twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartBoxCode == twdCounterExcelInfo.PartBoxCode && d.PartNo == twdCounterExcelInfo.PartNo); } if (twdCounterInfo == null) { throw new Exception("MC:0x00000255");///数据格式不符合导入规范 } #endregion if (twdCounterInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled) { throw new Exception("MC:0x00000455");///计数器已作废不能修改数量 } @string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] set " + "[CURRENT_QTY] = isnull([CURRENT_QTY],0) + " + twdCounterExcelInfo.DiffQty + "," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() where " + "[ID] = " + twdCounterInfo.Id + ";"); ///根据计数器的物料拉动信息外键获取物料拉动信息 MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == twdCounterInfo.PartPullFid.GetValueOrDefault()); if (maintainInhouseLogisticStandardInfo == null) { throw new Exception("MC:0x00000213");///物料拉动信息数据错误 } if (maintainInhouseLogisticStandardInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable) { throw new Exception("MC:0x00000233");///没有已启用的物料拉动信息 } ///获取零件类信息 TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass); ///未能成功获取零件类信息 if (twdPartBoxInfo == null) { throw new Exception("MC:0x00000225");///拉动零件类数据错误 } ///零件类未启用 if (twdPartBoxInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable) { throw new Exception("MC:0x00000456");///零件类未启用 } ///创建计数器日志 TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser); ///以物料拉动信息填充计数器日志 TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandardInfo, ref twdCounterLogInfo); ///以零件类信息填充计数器日志 TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo); ///PART_QTY twdCounterLogInfo.PartQty = twdCounterExcelInfo.DiffQty; ///SOURCE_DATA_FID twdCounterLogInfo.SourceDataFid = twdCounterInfo.Fid; ///SOURCE_DATA_TYPE twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Manual; ///SOURCE_DATA twdCounterLogInfo.SourceData = twdCounterInfo.PartBoxCode; ///Comments twdCounterLogInfo.Comments = twdCounterExcelInfo.Comments; /// @string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo)); ///触发层级拉动 @string.AppendLine(LevelPullRequirementCounter( maintainInhouseLogisticStandardInfo, twdCounterExcelInfo.DiffQty, loginUser, twdCounterInfo.Fid.GetValueOrDefault(), twdCounterInfo.PartBoxCode)); } ///执行 using (var trans = new TransactionScope()) { if (@string.Length == 0) { throw new Exception("MC:0x00000283");///没有可导入更新的数据 } CommonDAL.ExecuteNonQueryBySql(@string.ToString()); trans.Complete(); } /// return(true); }
/// <summary> /// 对于MPM-036逻辑进行函数封装 /// </summary> /// <param name="emergencyPullingCartInfos"></param> /// <param name="loginUser"></param> public static string Handler(List <EmergencyPullingCartInfo> emergencyPullingCartInfos, string loginUser) { ///执行语句 StringBuilder @string = new StringBuilder(); ///获取系统配置是否允许过量提前拉动 string allowedFlag = new ConfigDAL().GetValueByCode("ENABLE_EXCEED_COUNTER_QTY_WHEN_ADVANCE_PULL"); ///接收紧急拉动购物车对象集合进行处理 if (emergencyPullingCartInfos.Count == 0) { throw new Exception("MC:0x00000084");///数据错误 } ///对应的物料拉动信息 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardBLL().GetList("" + " and [STATUS] =" + (int)BasicDataStatusConstants.Enable + "" + " and [FID] in ('" + string.Join("','", emergencyPullingCartInfos.Select(d => d.LogisticStandardFid).ToArray()) + "')", string.Empty); if (maintainInhouseLogisticStandardInfos.Count == 0) { throw new Exception("MC:0x00000213");///物料拉动信息数据错误 } ///首先需要将其按照拉动方式、零件类进行分组,每个分组产生一张紧急拉动单 var cartGroups = emergencyPullingCartInfos.GroupBy(d => new { d.PullMode, d.PartBoxCode }).ToList(); #region 手工拉动 PLAN var planCartGroups = cartGroups.Where(d => d.Key.PullMode == (int)PullModeConstants.Plan).ToList(); ///计划拉动零件类 List <PlanPartBoxInfo> planPartBoxInfos = new PlanPartBoxBLL().GetList("" + " and [STATUS] = " + (int)BasicDataStatusConstants.Enable + "" + " and [PART_BOX_CODE] in ('" + string.Join("','", planCartGroups.Select(d => d.Key.PartBoxCode).ToArray()) + "')", string.Empty); foreach (var planCartGroup in planCartGroups) { ///从参数集合从提取对应的紧急拉动购物车集合 List <EmergencyPullingCartInfo> pullingCartInfos = emergencyPullingCartInfos.Where(d => d.PullMode == planCartGroup.Key.PullMode && d.PartBoxCode == planCartGroup.Key.PartBoxCode).ToList(); if (pullingCartInfos.Count == 0) { continue; } ///计划零件类 PlanPartBoxInfo planPartBoxInfo = planPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == planCartGroup.Key.PartBoxCode); if (planPartBoxInfo == null) { continue; } ///计划拉动-->仓储衔接主表 MaterialPullingOrderInfo materialPulling = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo(); ///计划拉动单主表 PlanPullOrderInfo planPullOrderInfo = PlanPullOrderBLL.CreatePlanPullOrder(loginUser); ///OrderType 拉动单类型 planPullOrderInfo.OrderType = (int)PullOrderTypeConstants.Emergency; ///PlanPartBoxInfo-->PlanPullOrderInfo PlanPullOrderBLL.GetPlanPullOrder(planPartBoxInfo, ref planPullOrderInfo); ///计划拉动单主表sql @string.AppendLine(PlanPullOrderDAL.GetInsertSql(planPullOrderInfo)); ///PlanPullOrderInfo -> MaterialPullingOrderInfo MaterialPullingCommonBLL.GetMaterialPlanPullingOrderInfo(planPullOrderInfo, ref materialPulling); foreach (EmergencyPullingCartInfo planCartInfo in pullingCartInfos) { ///物料拉动信息 MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == planCartInfo.LogisticStandardFid); if (maintainInhouseLogisticStandardInfo == null) { continue; } ///物料包装数量在此需要根据单包装数量以及物料需求数量进行向上圆整计算 ///REQUIRED_BOX_QTY:物料包装数量 PULL_PACKAGE_QTY:单包装数量 REQUIRED_PART_QTY:物料需求数量 if (planCartInfo.PullPackageQty.GetValueOrDefault() == 0) { continue; ///O不能做被除数 } planCartInfo.RequiredBoxQty = Convert.ToInt32(Math.Ceiling(planCartInfo.RequiredPartQty.GetValueOrDefault() / planCartInfo.PullPackageQty.GetValueOrDefault())); ///仓储衔接明细表 MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo(); ///计划拉动单明细 PlanPullOrderDetailInfo planPullOrderDetailInfo = PlanPullOrderDetailBLL.CreatePlanPullOrderDetail(loginUser); ///MaintainInhouseLogisticStandardInfo -> PlanPullOrderDetailInfo PlanPullOrderDetailBLL.GetPlanPullOrderDetail(maintainInhouseLogisticStandardInfo, ref planPullOrderDetailInfo); ///TwdPullOrderInfo -> TwdPullOrderDetailInfo PlanPullOrderDetailBLL.GetPlanPullOrderDetailInfo(planPullOrderInfo, ref planPullOrderDetailInfo); ///RequiredPackageQty planPullOrderDetailInfo.RequiredPackageQty = planCartInfo.RequiredBoxQty.GetValueOrDefault(); ///RequiredPartQty planPullOrderDetailInfo.RequiredPartQty = planCartInfo.RequiredPartQty.GetValueOrDefault(); ///计划拉动明细sql @string.AppendLine(PlanPullOrderDetailDAL.GetInsertSql(planPullOrderDetailInfo)); ///PlanPullOrderDetailInfo -> MaterialPullingOrderDetailInfo MaterialPullingCommonBLL.GetMaterialPullingOrderDetail(planPullOrderDetailInfo, ref detailInfo); ///仓储明细集合Add materialPulling.MaterialPullingOrderDetailInfos.Add(detailInfo); } ///拉动单生成后需要调用拉动仓储衔接函数获取语句 @string.AppendLine(MaterialPullingCommonBLL.Handler(materialPulling, loginUser)); } #endregion #region 提前拉动 TWD var twdCartGroups = cartGroups.Where(d => d.Key.PullMode == (int)PullModeConstants.Pcs || d.Key.PullMode == (int)PullModeConstants.Twd).ToList(); ///TWD计数器 List <TwdCounterInfo> twdCounterInfos = new TwdCounterBLL().GetList("" + "[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " + "[PART_PULL_FID] in ('" + string.Join("','", maintainInhouseLogisticStandardInfos.Select(d => d.Fid).ToArray()) + "') and " + "isnull([CURRENT_QTY],0) > 0", string.Empty); ///TWD零件类 List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxBLL().GetList("" + "[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " + "[PART_BOX_CODE] in ('" + string.Join("','", twdCartGroups.Select(d => d.Key.PartBoxCode).ToArray()) + "')", string.Empty); foreach (var twdCartGroup in twdCartGroups) { ///从参数集合从提取对应的紧急拉动购物车集合 List <EmergencyPullingCartInfo> pullingCartInfos = emergencyPullingCartInfos.Where(d => d.PullMode == twdCartGroup.Key.PullMode && d.PartBoxCode == twdCartGroup.Key.PartBoxCode).ToList(); if (pullingCartInfos.Count == 0) { continue; } ///TWD零件类 TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == twdCartGroup.Key.PartBoxCode); if (twdPartBoxInfo == null) { continue; } ///触发层级拉动的集合 List <EmergencyPullingCartInfo> pullingLevelCartInfos = pullingCartInfos.Where(d => d.TriggerPullFlag == true).ToList(); foreach (EmergencyPullingCartInfo pullingLevelCartInfo in pullingLevelCartInfos) { ///需要根据其物料拉动信息外键获取对应的计数器数据(状态必须为已启用) TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartPullFid == pullingLevelCartInfo.LogisticStandardFid.GetValueOrDefault() && d.Status == (int)BasicDataStatusConstants.Enable); if (twdCounterInfo == null) { continue; } ///在此之前需要根据物料图号、供应商、层级仓库、层级存储区在物料拉动信息中获取匹配的目标仓库、目标存储区数据 MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.PartNo == pullingLevelCartInfo.PartNo && d.SupplierNum == pullingLevelCartInfo.SupplierNum && d.WmNo == pullingLevelCartInfo.TriggerWmNo && d.ZoneNo == pullingLevelCartInfo.TriggerZoneNo); ///触发层级拉动 @string.AppendFormat(TwdCounterBLL.LevelPullCounter(maintainInhouseLogisticStandardInfo, pullingLevelCartInfo.RequiredPartQty.GetValueOrDefault(), loginUser, twdCounterInfo.Fid.GetValueOrDefault(), twdPartBoxInfo)); } ///提前拉动的集合 List <EmergencyPullingCartInfo> advancePullCartInfos = pullingCartInfos.Where(d => d.TriggerPullFlag == false && d.EmergencyPullMode == (int)EmergencyPullModeConstants.AdvancePull).ToList(); if (advancePullCartInfos.Count == 0) { continue; } ///仓储衔接主表 MaterialPullingOrderInfo materialPulling = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo(); ///TWD拉动单主表 TwdPullOrderInfo twdPullOrderInfo = TwdPullOrderBLL.CreateTwdPullOrderInfo(loginUser); ///TwdPartBoxInfo -> TwdPullOrderInfo TwdPullOrderBLL.GetTwdPullOrderInfo(twdPartBoxInfo, ref twdPullOrderInfo); ///TWD拉动单主表sql @string.AppendLine(TwdPullOrderDAL.GetInsertSql(twdPullOrderInfo)); ///TwdPullOrderInfo-->MaterialPullingOrderInfo MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(twdPullOrderInfo, ref materialPulling); int rowNo = 0; ///行号 ///逐条循环每个购物车 foreach (EmergencyPullingCartInfo advancePullCartInfo in advancePullCartInfos) { ///需要根据其物料拉动信息外键获取对应的计数器数据(状态必须为已启用) ///TT_MPM_TWD_COUNTER TWD计数器 TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d => d.PartPullFid == advancePullCartInfo.LogisticStandardFid.GetValueOrDefault() && d.Status == (int)BasicDataStatusConstants.Enable); if (twdCounterInfo == null) { continue; } ///物料拉动信息 MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == advancePullCartInfo.LogisticStandardFid); if (maintainInhouseLogisticStandardInfo == null) { continue; } ///物料包装数量在此需要根据单包装数量以及物料需求数量进行向上圆整计算 ///REQUIRED_BOX_QTY:物料包装数量 PULL_PACKAGE_QTY:单包装数量 REQUIRED_PART_QTY:物料需求数量 if (advancePullCartInfo.PullPackageQty.GetValueOrDefault() == 0) { continue; ///O不能做被除数 } advancePullCartInfo.RequiredBoxQty = Convert.ToInt32(Math.Ceiling(advancePullCartInfo.RequiredPartQty.GetValueOrDefault() / advancePullCartInfo.PullPackageQty.GetValueOrDefault())); ///若该系统配置标记为false时、计数器当前累计数量不允许小于购物车物料需求数量, if (allowedFlag.ToLower() == "false" && twdCounterInfo.CurrentQty.GetValueOrDefault() < advancePullCartInfo.RequiredPartQty.GetValueOrDefault()) { continue; } ///仓储衔接明细表 MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo(); ///TWD明细表 TwdPullOrderDetailInfo pullOrderDetailInfo = TwdPullOrderDetailBLL.CreateTwdPullOrderDetailInfo(loginUser); ///MaintainInhouseLogisticStandardInfo -> TwdPullOrderDetailInfo TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(maintainInhouseLogisticStandardInfo, ref pullOrderDetailInfo); ///TwdPullOrderInfo -> TwdPullOrderDetailInfo TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(twdPullOrderInfo, ref pullOrderDetailInfo); ///ROW_NO,行号 pullOrderDetailInfo.RowNo = ++rowNo; ///REQUIRED_PACKAGE_QTY,需求包装数 pullOrderDetailInfo.RequiredPackageQty = advancePullCartInfo.RequiredBoxQty.GetValueOrDefault(); ///REQUIRED_PART_QTY,需求物料数量 pullOrderDetailInfo.RequiredPartQty = advancePullCartInfo.RequiredPartQty.GetValueOrDefault(); ///TWD明细表sql @string.AppendLine(TwdPullOrderDetailDAL.GetInsertSql(pullOrderDetailInfo)); ///TwdPullOrderDetailInfo -> MaterialPullingOrderDetailInfo MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(pullOrderDetailInfo, ref detailInfo); ///仓储衔接集合Add materialPulling.MaterialPullingOrderDetailInfos.Add(detailInfo); ///否则直接对计数器的当前累计数量按购物车物料需求数量进行扣减 @string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] " + "set [CURRENT_QTY] = isnull([CURRENT_QTY],0) - " + advancePullCartInfo.RequiredPartQty.GetValueOrDefault() + "," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID]= " + twdCounterInfo.Id + ";"); ///同时记录计数器日志并标记其类型为提前拉动 TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser); ///TwdCounterInfo -> TwdCounterLogInfo TwdCounterLogBLL.GetTwdCounterLogInfo(twdCounterInfo, ref twdCounterLogInfo); ///PART_QTY,物料数量 twdCounterLogInfo.PartQty = 0 - advancePullCartInfo.RequiredPartQty.GetValueOrDefault(); ///SOURCE_DATA,目视来源数据 twdCounterLogInfo.SourceData = twdPullOrderInfo.OrderCode; ///SOURCE_DATA_FID,数据来源外键 twdCounterLogInfo.SourceDataFid = twdPullOrderInfo.Fid; ///SOURCE_DATA_TYPE,数据来源类型 twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.AdvancePull; ///计数器日志sql @string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo)); } ///拉动单生成后需要调用拉动仓储衔接函数获取语句 @string.AppendLine(MaterialPullingCommonBLL.Handler(materialPulling, loginUser)); } #endregion return(@string.ToString()); }
/// <summary> /// InsertInfo /// </summary> /// <param name="info"></param> /// <returns></returns> public long InsertInfo(TwdPartBoxInfo info) { ValidTwdPartBoxInfo(info); info.Status = (int)BasicDataStatusConstants.Created; return(dal.Add(info)); }
/// <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 <TwdPartBoxInfo> twdPartBoxExcelInfos = CommonDAL.DatatableConvertToList <TwdPartBoxInfo>(dataTable).ToList(); if (twdPartBoxExcelInfos.Count == 0) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } ///获取业务表中要变更的数据集合,准备对比 List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("', '", twdPartBoxExcelInfos.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", ""); ///执行的SQL语句 StringBuilder @string = new StringBuilder(); List <string> fields = new List <string>(fieldNames.Keys); ///逐条处理中间表数据 foreach (var twdPartBoxExcelInfo in twdPartBoxExcelInfos) { TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == twdPartBoxExcelInfo.PartBoxCode); ///需要新增 if (twdPartBoxInfo == null) { ///校验 ValidTwdPartBoxInfo(twdPartBoxExcelInfo); ///字段 string insertFieldString = string.Empty; ///值 string insertValueString = string.Empty; for (int i = 0; i < fields.Count; i++) { string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]); if (string.IsNullOrEmpty(valueStr)) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } insertFieldString += "[" + fieldNames[fields[i]] + "],"; insertValueString += valueStr + ","; } ///判断业务主键是否重复,以防止EXCEL中有重复数据,适用于基础数据导入 @string.AppendLine("if not exists (select * from LES.TM_MPM_TWD_PART_BOX with(nolock) " + "where [PART_BOX_CODE] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and [VALID_FLAG] = 1)" + " insert into [LES].[TM_MPM_TWD_PART_BOX] (" + "[FID]," + insertFieldString + "[STATUS]," + "[CREATE_USER]," + "[CREATE_DATE]," + "[VALID_FLAG]" + ") values (" + "NEWID(),"///FID + insertValueString + (int)BasicDataStatusConstants.Created + "," + "N'" + loginUser + "'," ///CREATE_USER + "GETDATE()," ///CREATE_DATE + "1" ///VALID_FLAG + ");"); ///防止EXCEL中有重复项 twdPartBoxInfos.Add(twdPartBoxExcelInfo); continue; } /// if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled) { continue; } /// twdPartBoxExcelInfo.Id = twdPartBoxInfo.Id; ///校验 ValidTwdPartBoxInfo(twdPartBoxExcelInfo); ///是否更新了拉动模式,需要同步更新物料拉动信息中的拉动模式 if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() != twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault()) { @string.AppendLine("update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " + "set [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault() + "'," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxInfo.TwdPullMode.GetValueOrDefault() + "' and " + "[INHOUSE_PART_CLASS] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and " + "[VALID_FLAG] = 1;"); } ///已创建状态的数据可以全信息更新 if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Created) { ///值 string valueString = string.Empty; for (int i = 0; i < fields.Count; i++) { string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]); if (string.IsNullOrEmpty(valueStr)) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ","; } @string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] set " + valueString + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + twdPartBoxInfo.Id + ";"); continue; } /// @string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] " + "set [PART_BOX_NAME] = N'" + twdPartBoxExcelInfo.PartBoxName + "'," + "[REQUIREMENT_ACCUMULATE_TIME] = " + (twdPartBoxExcelInfo.RequirementAccumulateTime == null ? "NULL" : "" + twdPartBoxExcelInfo.RequirementAccumulateTime.GetValueOrDefault() + "") + "," + "[LOAD_TIME] = " + (twdPartBoxExcelInfo.LoadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.LoadTime.GetValueOrDefault() + "") + "," + "[TRANSPORT_TIME] = " + (twdPartBoxExcelInfo.TransportTime == null ? "NULL" : "" + twdPartBoxExcelInfo.TransportTime.GetValueOrDefault() + "") + "," + "[UNLOAD_TIME] = " + (twdPartBoxExcelInfo.UnloadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.UnloadTime.GetValueOrDefault() + "") + "," + "[DELAY_TIME] = " + (twdPartBoxExcelInfo.DelayTime == null ? "NULL" : "" + twdPartBoxExcelInfo.DelayTime.GetValueOrDefault() + "") + "," + "[ONLINE_TIME] = " + (twdPartBoxExcelInfo.OnlineTime == null ? "NULL" : "" + twdPartBoxExcelInfo.OnlineTime.GetValueOrDefault() + "") + "," + "[REQUIREMENT_ACCUMULATE_MODE] = " + twdPartBoxExcelInfo.RequirementAccumulateMode.GetValueOrDefault() + "," + "[STATUS_POINT_CODE] = N'" + twdPartBoxExcelInfo.StatusPointCode + "'," + "[ROUNDNESS_MODE] = " + twdPartBoxExcelInfo.RoundnessMode.GetValueOrDefault() + "," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + twdPartBoxInfo.Id + ";"); } ///执行 using (TransactionScope trans = new TransactionScope()) { if (@string.Length > 0) { CommonDAL.ExecuteNonQueryBySql(@string.ToString()); } trans.Complete(); } return(true); }
/// <summary> /// 校验规则 /// </summary> /// <param name="info"></param> private void ValidTwdPartBoxInfo(TwdPartBoxInfo info) { int cnt = 0; ///PART_BOX_CODE if (info.Id == 0) { cnt = dal.GetCounts("[PART_BOX_CODE] = N'" + info.PartBoxCode + "'"); } else { cnt = dal.GetCounts("[PART_BOX_CODE] = N'" + info.PartBoxCode + "' and [ID] <> " + info.Id + ""); } if (cnt > 0) { throw new Exception("MC:0x00000302");///零件类代码重复 } if (string.IsNullOrEmpty(info.PartBoxName)) { throw new Exception("MC:0x00000299");///零件类名称不可为空 } ///PART_BOX_NAME if (info.Id == 0) { cnt = dal.GetCounts("[PART_BOX_NAME] = N'" + info.PartBoxName + "'"); } else { cnt = dal.GetCounts("[PART_BOX_NAME] = N'" + info.PartBoxName + "' and [ID] <> " + info.Id + ""); } if (cnt > 0) { throw new Exception("MC:0x00000303");///零件类名称重复 } if (string.IsNullOrEmpty(info.TWmNo)) { throw new Exception("MC:0x00000514");///目标仓库不能为空 } if (string.IsNullOrEmpty(info.TZoneNo)) { throw new Exception("MC:0x00000515");///目标存储区不能为空 } if (info.SZoneNo == info.TZoneNo) { throw new Exception("MC:0x00000293");///来源存储区与目标存储区不能一致 } if (info.RequirementAccumulateTime.GetValueOrDefault() < 0) { throw new Exception("MC:0x00000314");///需求累积时间必须大于等于零 } if (info.LoadTime.GetValueOrDefault() < 0) { throw new Exception("MC:0x00000315");///装货时间必须大于等于零 } if (info.TransportTime.GetValueOrDefault() < 0) { throw new Exception("MC:0x00000316");///运输时间必须大于等于零 } if (info.UnloadTime.GetValueOrDefault() < 0) { throw new Exception("MC:0x00000317");///卸货时间必须大于等于零 } if (info.DelayTime.GetValueOrDefault() < 0) { throw new Exception("MC:0x00000725");///延迟时间必须大于等于零 } if (info.OnlineTime.GetValueOrDefault() < 0) { throw new Exception("MC:0x00000319");///取货时间必须大于等于零 } if (info.RequirementAccumulateMode == null) { throw new Exception("MC:0x00000516");///需求累计为必选项 } if (info.RequirementAccumulateMode == (int)RequirementAccumulateModeConstants.PassSpot) { if (string.IsNullOrEmpty(info.StatusPointCode)) { throw new Exception("MC:0x00000294");///当需求累计方式为过点时状态点不能为空 } } if (info.TwdPullMode == null) { throw new Exception("MC:0x00000517");///拉动模式为必选项 } if (info.TwdPullMode.GetValueOrDefault() == (int)TwdPullModeConstants.Pcs) { if (info.RoundnessMode.GetValueOrDefault() != (int)RoundnessModeConstants.Downward) { throw new Exception("MC:0x00000512");///整包消耗拉动时圆整方式只能选择向下圆整 } } }
/// <summary> /// UpdateInfo /// </summary> /// <param name="fields"></param> /// <param name="id"></param> /// <returns></returns> public bool UpdateInfo(string fields, long id) { TwdPartBoxInfo info = dal.GetInfo(id); if (info == null) { throw new Exception("MC:0x00000084");///数据错误 } string partBoxName = CommonBLL.GetFieldValue(fields, "PART_BOX_NAME"); info.PartBoxName = partBoxName; string sZoneNo = CommonBLL.GetFieldValue(fields, "S_ZONE_NO"); string tZoneNo = CommonBLL.GetFieldValue(fields, "T_ZONE_NO"); string prevSZoneNo = info.SZoneNo; string prevTZoneNo = info.TZoneNo; info.SZoneNo = sZoneNo; info.TZoneNo = tZoneNo; string requirement_accumulate_time = CommonBLL.GetFieldValue(fields, "REQUIREMENT_ACCUMULATE_TIME"); int.TryParse(requirement_accumulate_time, out int requirementAccumulateTime); info.RequirementAccumulateTime = requirementAccumulateTime; string load_time = CommonBLL.GetFieldValue(fields, "LOAD_TIME"); int.TryParse(load_time, out int loadTime); info.LoadTime = loadTime; string transport_time = CommonBLL.GetFieldValue(fields, "TRANSPORT_TIME"); int.TryParse(transport_time, out int transportTime); info.TransportTime = transportTime; string unload_time = CommonBLL.GetFieldValue(fields, "UNLOAD_TIME"); int.TryParse(unload_time, out int unloadTime); info.UnloadTime = unloadTime; string delay_time = CommonBLL.GetFieldValue(fields, "DELAY_TIME"); int.TryParse(delay_time, out int delayTime); info.DelayTime = delayTime; string online_time = CommonBLL.GetFieldValue(fields, "ONLINE_TIME"); int.TryParse(online_time, out int onlineTime); info.OnlineTime = onlineTime; string requirement_accumulate_mode = CommonBLL.GetFieldValue(fields, "REQUIREMENT_ACCUMULATE_MODE"); int.TryParse(requirement_accumulate_mode, out int requirementAccumulateMode); info.RequirementAccumulateMode = requirementAccumulateMode; string status_point_code = CommonBLL.GetFieldValue(fields, "STATUS_POINT_CODE"); info.StatusPointCode = status_point_code; string twd_pull_mode = CommonBLL.GetFieldValue(fields, "TWD_PULL_MODE"); int.TryParse(twd_pull_mode, out int twdPullMode); int prevTwdPullMode = info.TwdPullMode.GetValueOrDefault(); info.TwdPullMode = twdPullMode; string roundness_mode = CommonBLL.GetFieldValue(fields, "ROUNDNESS_MODE"); int.TryParse(roundness_mode, out int roundnessMode); info.RoundnessMode = roundnessMode; ValidTwdPartBoxInfo(info); ///校验通过后将拉动模式\地点回复 info.TwdPullMode = prevTwdPullMode; info.SZoneNo = prevSZoneNo; info.TZoneNo = prevTZoneNo; if (info.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled) { throw new Exception("MC:0x00000422");///已作废状态的零件类不能修改 } string loginUser = CommonBLL.GetFieldValue(fields, "MODIFY_USER"); info.ModifyUser = loginUser; info.ModifyDate = DateTime.Now; ///已发布状态的零件类只能更新部分信息 if (info.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Enable) { return(dal.Update(info) > 0 ? true : false); } StringBuilder @string = new StringBuilder(); ///是否更新了拉动模式,需要同步更新物料拉动信息中的拉动模式 if (info.TwdPullMode.GetValueOrDefault() != twdPullMode) { @string.AppendLine("update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " + "set [INHOUSE_SYSTEM_MODE] = N'" + twdPullMode + "',[MODIFY_USER] = N'" + loginUser + "',[MODIFY_DATE] = GETDATE() " + "where [INHOUSE_SYSTEM_MODE] = N'" + info.TwdPullMode.GetValueOrDefault() + "' and [INHOUSE_PART_CLASS] = N'" + info.PartBoxCode + "' and [VALID_FLAG] = 1;"); } ///执行 using (TransactionScope trans = new TransactionScope()) { if (dal.UpdateInfo(fields, id) == 0) { return(false); } if (@string.Length > 0) { CommonDAL.ExecuteNonQueryBySql(@string.ToString()); } trans.Complete(); } return(true); }