/// 同步工厂布局 /// </summary> /// <returns></returns> public static void Sync(string loginUser) { ///从中间表提取未处理工厂布局数据 List<SapPlantStructureInfo> sapPlantStructureInfos = new SapPlantStructureBLL().GetListByPage("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", "[ID] asc", 1, 1000, out int dataCnt); if (dataCnt == 0) return; StringBuilder stringBuilder = new StringBuilder(); ///获取所有有效工厂信息 List<PlantInfo> plantInfos = new PlantBLL().GetList(string.Empty, "ID"); ///获取所有有效车间信息 List<WorkshopInfo> workshopInfos = new WorkshopBLL().GetList(string.Empty, "ID"); ///获取所有有效生产线信息 List<AssemblyLineInfo> assemblyLineInfos = new AssemblyLineBLL().GetList(string.Empty, "ID"); ///获取所有有效工段信息 List<WorkshopSectionInfo> workshopSectionInfos = new WorkshopSectionBLL().GetList(string.Empty, "ID"); ///获取所有有效工位信息 List<LocationInfo> locationInfos = new LocationBLL().GetList(string.Empty, "ID"); ///已处理完成的ID List<long> dealedIds = new List<long>(); ///逐条处理中间表数据 foreach (var sapPlantStructureInfo in sapPlantStructureInfos) { #region 工厂 if (string.IsNullOrEmpty(sapPlantStructureInfo.Werks)) { stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PLANT_STRUCTURE] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'3x00000019'," +///工厂信息不存在 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPlantStructureInfo.Id + ";"); continue; } /// PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.Plant == sapPlantStructureInfo.Werks); ///如果业务表工厂中不存在,就添加 if (plantInfo == null) { #region TM_BAS_PLANT ///将这样的数据更新为挂起状态 stringBuilder.AppendFormat("insert into [LES].[TM_BAS_PLANT] (" + "[FID] ," + "[PLANT] ," + "[PLANT_NAME] ," + "[VALID_FLAG] ," + "[CREATE_USER] ," + "[CREATE_DATE]," + "[SAP_PLANT_CODE]) values (" + "NEWID() ," //// FID - uniqueidentifier + "N'{0}' ," //// PLANT - nvarchar(5) + "N'{1}' ," //// PLANT_NAME - nvarchar(100) + "{2} ," //// VALID_FLAG - bit + "N'{3}' ," //// CREATE_USER - nvarchar(50) + "GETDATE(),"//// CREATE_DATE - datetime + "N'{4}'); ",///SAP_PLANT_CODE sapPlantStructureInfo.Werks,//// PLANT - nvarchar(5),0 sapPlantStructureInfo.Name1, //// PLANT_NAME - nvarchar(100),1 1, //// VALID_FLAG - bit,2 loginUser,//// CREATE_USER - nvarchar(50),3 sapPlantStructureInfo.Werks);///SAP_PLANT_CODE,4 #endregion ///添加到工厂集合 PlantInfo plant = new PlantInfo(); plant.Plant = sapPlantStructureInfo.Werks; plantInfos.Add(plant); } else { ///更新工厂名称 stringBuilder.AppendFormat("update [LES].[TM_BAS_PLANT] " + "set [PLANT_NAME] = N'" + sapPlantStructureInfo.Name1 + "'," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID] = " + plantInfo.Id + ";"); } #endregion #region 车间 if (string.IsNullOrEmpty(sapPlantStructureInfo.Zbm)) { stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PLANT_STRUCTURE] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'0x00000079'," +///车间代码不能为空 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPlantStructureInfo.Id + ";"); continue; } ///如果存在工厂, 判断(工厂-部门) WorkshopInfo workshopInfo = workshopInfos.FirstOrDefault(d => d.Workshop == sapPlantStructureInfo.Zbm && d.Plant == sapPlantStructureInfo.Werks); ///如果部门不存在进行添加 if (workshopInfo == null) { #region TM_BAS_WORKSHOP stringBuilder.AppendFormat("insert into [LES].[TM_BAS_WORKSHOP] (" + "[FID] ," + "[PLANT] ," + "[WORKSHOP] ," + "[WORKSHOP_NAME] ," + "[COMMENTS] ," + "[VALID_FLAG] ," + "[CREATE_USER] ," + "[CREATE_DATE] )VALUES ( " + "NEWID() ," //// FID - uniqueidentifier + "N'{0}' ," //// PLANT - nvarchar(20) + "N'{1}' ," //// WORKSHOP - nvarchar(20) + "N'{2}' ," //// WORKSHOP_NAME - nvarchar(100) + "NULL ," //// COMMENTS - nvarchar(200) + "{3} ," //// VALID_FLAG - bit + "N'{4}' ," //// CREATE_USER - nvarchar(50) + "GETDATE()) ;", //// CREATE_DATE - datetime sapPlantStructureInfo.Werks,//// PLANT - nvarchar(20),0 sapPlantStructureInfo.Zbm,//// WORKSHOP - nvarchar(20),1 sapPlantStructureInfo.Zbmms,//// WORKSHOP_NAME - nvarchar(100),2 1,//// VALID_FLAG - bit,3 loginUser);//// CREATE_USER - nvarchar(50),4 #endregion WorkshopInfo workshop = new WorkshopInfo(); workshop.Plant = sapPlantStructureInfo.Werks; workshop.Workshop = sapPlantStructureInfo.Zbm; workshopInfos.Add(workshop); } else { ///更新车间名称 stringBuilder.AppendFormat("update [LES].[TM_BAS_WORKSHOP] " + "set [WORKSHOP_NAME] = N'" + sapPlantStructureInfo.Zbmms + "'," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID] = " + workshopInfo.Id + ";"); } #endregion #region 生产线 if (string.IsNullOrEmpty(sapPlantStructureInfo.Zcj)) { stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PLANT_STRUCTURE] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'0x00000105'," +///生产线代码不能为空 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPlantStructureInfo.Id + ";"); continue; } ///如果存在工厂,部门, 判断(工厂-部门-生产线) AssemblyLineInfo assemblyLineInfo = assemblyLineInfos.FirstOrDefault(d => d.AssemblyLine == sapPlantStructureInfo.Zcj && d.Workshop == sapPlantStructureInfo.Zbm && d.Plant == sapPlantStructureInfo.Werks); ///如果生产线不存在进行添加 if (assemblyLineInfo == null) { #region TM_BAS_ASSEMBLY_LINE stringBuilder.AppendFormat("insert into [LES].[TM_BAS_ASSEMBLY_LINE](" + "[FID] ," + "[PLANT] ," + "[WORKSHOP] ," + "[ASSEMBLY_LINE] ," + "[ASSEMBLY_LINE_NAME] ," + "[VALID_FLAG] ," + "[CREATE_USER] ," + "[CREATE_DATE]) values (" + "NEWID() ," //// FID - uniqueidentifier + "N'{0}' ," //// PLANT - nvarchar(20) + "N'{1}' ," //// WORKSHOP - nvarchar(20) + "N'{2}' ," //// ASSEMBLY_LINE - nvarchar(20) + "N'{3}' ," //// ASSEMBLY_LINE_NAME - nvarchar(100) + "{4} ," //// VALID_FLAG - bit + "N'{5}' ," //// CREATE_USER - nvarchar(50) + "GETDATE()) ;", //// CREATE_DATE - datetime sapPlantStructureInfo.Werks,//// PLANT - nvarchar(20),0 sapPlantStructureInfo.Zbm,//// WORKSHOP - nvarchar(20),1 sapPlantStructureInfo.Zcj,//// ASSEMBLY_LINE - nvarchar(20),2 sapPlantStructureInfo.Zcjms,//// ASSEMBLY_LINE_NAME - nvarchar(100),3 1,//// VALID_FLAG - bit,4 loginUser);//// CREATE_USER - nvarchar(50),5 #endregion AssemblyLineInfo assemblyLine = new AssemblyLineInfo(); assemblyLine.Plant = sapPlantStructureInfo.Werks; assemblyLine.Workshop = sapPlantStructureInfo.Zbm; assemblyLine.AssemblyLine = sapPlantStructureInfo.Zcj; assemblyLineInfos.Add(assemblyLine); } else { ///更新生产线名称 stringBuilder.AppendFormat("update [LES].[TM_BAS_ASSEMBLY_LINE] " + "set [ASSEMBLY_LINE_NAME] = N'" + sapPlantStructureInfo.Zcjms + "'," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID] = " + assemblyLineInfo.Id + ";"); } #endregion #region 工段 if (string.IsNullOrEmpty(sapPlantStructureInfo.LineNo)) { stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PLANT_STRUCTURE] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'0x00000088'," +///工段代码不能为空 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPlantStructureInfo.Id + ";"); continue; } ///如果存在 工厂-部门-生产线,判断工段 WorkshopSectionInfo workshopSectionInfo = workshopSectionInfos.FirstOrDefault(d => d.WorkshopSection == sapPlantStructureInfo.LineNo && d.AssemblyLine == sapPlantStructureInfo.Zcj && d.Workshop == sapPlantStructureInfo.Zbm && d.Plant == sapPlantStructureInfo.Werks); ///如果工段不存在进行添加 if (workshopSectionInfo == null) { #region TM_BAS_WORKSHOP_SECTION stringBuilder.AppendFormat("insert into [LES].[TM_BAS_WORKSHOP_SECTION] (" + "[FID] ," + "[PLANT] ," + "[WORKSHOP] ," + "[ASSEMBLY_LINE] ," + "[WORKSHOP_SECTION] ," + "[WORKSHOP_SECTION_NAME] ," + "[VALID_FLAG] ," + "[CREATE_USER] ," + "[CREATE_DATE] )VALUES (" + "NEWID() ," //// FID - uniqueidentifier + "N'{0}' ," //// PLANT - nvarchar(20) + "N'{1}' ," //// WORKSHOP - nvarchar(20) + "N'{2}' ," //// ASSEMBLY_LINE - nvarchar(20) + "N'{3}' ," //// WORKSHOP_SECTION - nvarchar(20) + "N'{4}' ," //// WORKSHOP_SECTION_NAME - nvarchar(200 + "{5} ," //// VALID_FLAG - bit + "N'{6}' ," //// CREATE_USER - nvarchar(50) + "GETDATE());",//// CREATE_DATE - datetime sapPlantStructureInfo.Werks,//// PLANT - nvarchar(20),0 sapPlantStructureInfo.Zbm,//// WORKSHOP - nvarchar(20),1 sapPlantStructureInfo.Zcj, //// ASSEMBLY_LINE - nvarchar(20),2 sapPlantStructureInfo.LineNo,//// WORKSHOP_SECTION - nvarchar(20),3 sapPlantStructureInfo.LineNoms,//// WORKSHOP_SECTION_NAME - nvarchar(200),4 1,//// VALID_FLAG - bit,5 loginUser);//// CREATE_USER - nvarchar(50),6 #endregion WorkshopSectionInfo workshopSection = new WorkshopSectionInfo(); workshopSection.Plant = sapPlantStructureInfo.Werks; workshopSection.Workshop = sapPlantStructureInfo.Zbm; workshopSection.AssemblyLine = sapPlantStructureInfo.Zcj; workshopSection.WorkshopSection = sapPlantStructureInfo.LineNo; workshopSectionInfos.Add(workshopSection); } else { ///更新工段名称 stringBuilder.AppendFormat("update [LES].[TM_BAS_WORKSHOP_SECTION] " + "set [WORKSHOP_SECTION_NAME] = N'" + sapPlantStructureInfo.LineNoms + "'," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID] = " + workshopSectionInfo.Id + ";"); } #endregion #region 工位 if (string.IsNullOrEmpty(sapPlantStructureInfo.Vlsch)) { stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PLANT_STRUCTURE] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'0x00000172'," +///工位代码不能为空 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPlantStructureInfo.Id + ";"); continue; } ///如果存在 工厂-部门-生产线-工段,判断工位 LocationInfo locationInfo = locationInfos.FirstOrDefault(d => d.Location == sapPlantStructureInfo.Vlsch && d.WorkshopSection == sapPlantStructureInfo.LineNo && d.AssemblyLine == sapPlantStructureInfo.Zcj && d.Workshop == sapPlantStructureInfo.Zbm && d.Plant == sapPlantStructureInfo.Werks); if (locationInfo == null) { #region TM_BAS_LOCATION stringBuilder.AppendFormat("insert into [LES].[TM_BAS_LOCATION] (" + "[FID] ," + "[PLANT] ," + "[WORKSHOP] ," + "[ASSEMBLY_LINE] ," + "[WORKSHOP_SECTION] ," + "[LOCATION] ," + "[LOCATION_NAME] ," + "[VALID_FLAG] ," + "[CREATE_USER] ," + "[CREATE_DATE]," + "[SEQUENCE_NO]) values ( " + "NEWID() ," //// FID - uniqueidentifier + "N'{0}' ," //// PLANT - nvarchar(20) + "N'{1}' ," //// WORKSHOP - nvarchar(20) + "N'{2}' ," //// ASSEMBLY_LINE - nvarchar(20) + "N'{3}' ," //// WORKSHOP_SECTION - nvarchar(20) + "N'{4}' ," //// LOCATION - nvarchar(20) + "N'{5}' ," //// LOCATION_NAME - nvarchar(50) + "{6} ," //// VALID_FLAG - bit + "N'{7}' ," //// CREATE_USER - nvarchar(50) + "GETDATE()," //// CREATE_DATE - datetime + "{8}) ;", /// sapPlantStructureInfo.Werks, //// PLANT - nvarchar(20),0 sapPlantStructureInfo.Zbm,//// WORKSHOP - nvarchar(20),1 sapPlantStructureInfo.Zcj,//// ASSEMBLY_LINE - nvarchar(20),2 sapPlantStructureInfo.LineNo,//// WORKSHOP_SECTION - nvarchar(20),3 sapPlantStructureInfo.Vlsch,//// LOCATION - nvarchar(20),4 sapPlantStructureInfo.Txt,//// LOCATION_NAME - nvarchar(50),5 1,//// VALID_FLAG - bit,6 loginUser,//// CREATE_USER - nvarchar(50),7 sapPlantStructureInfo.Zsx); ///xsx nvarchar(20) #endregion LocationInfo location = new LocationInfo(); location.Plant = sapPlantStructureInfo.Werks; location.Workshop = sapPlantStructureInfo.Zbm; location.AssemblyLine = sapPlantStructureInfo.Zcj; location.WorkshopSection = sapPlantStructureInfo.LineNo; location.Location = sapPlantStructureInfo.Vlsch; locationInfos.Add(location); } else { ///更新工位名称 stringBuilder.AppendFormat("update [LES].[TM_BAS_LOCATION] " + "set [LOCATION_NAME] = N'" + sapPlantStructureInfo.Txt + "'," + "[SEQUENCE_NO]=N'" + sapPlantStructureInfo.Zsx + "', " + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "' " + "where [ID] = " + locationInfo.Id + ";"); } #endregion dealedIds.Add(sapPlantStructureInfo.Id); } if (dealedIds.Count > 0) ///已处理的中间表数据更新为已处理状态 工厂布局中间表 TI_IFM_SAP_PLANT_STRUCTURE stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PLANT_STRUCTURE] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = NULL," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] in (" + string.Join(",", dealedIds.ToArray()) + ");"); using (var trans = new TransactionScope()) { if (stringBuilder.Length > 0) BLL.SYS.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString()); trans.Complete(); } }
/// <summary> /// SAP供应商配额基础数据同步 /// </summary> /// <returns></returns> public static void Sync(string loginUser) { List <SapSupplierQuotaInfo> sapSupplierQuotaInfos = new SapSupplierQuotaBLL().GetListByPage("" + "[PROCESS_FLAG] in (" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Resend + ")", "[ID]", 1, 1000, out int dataCnt); if (dataCnt == 0) { return; } ///执行的SQL语句 StringBuilder @string = new StringBuilder(); ///是否启用SRM系统标记 string enable_srm_flag = new ConfigDAL().GetValueByCode("ENABLE_SRM_FLAG"); ///是否启用WMS系统标记 string enable_vmi_flag = new ConfigDAL().GetValueByCode("ENABLE_VMI_FLAG"); ///同步供应商基础数据 @string.AppendLine(GetSyncSupplierSql(sapSupplierQuotaInfos, loginUser)); ///获取业务表中要变更的数据集合,准备对比 List <SupplierPartQuotaInfo> supplierPartQuotaInfos = new SupplierPartQuotaBLL().GetListForInterfaceDataSync(sapSupplierQuotaInfos.Select(d => d.PartNo).ToList()); ///物料信息 List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(sapSupplierQuotaInfos.Select(d => d.PartNo).ToList()); ///VMI供应商关系 List <VmiSupplierInfo> vmiSupplierInfos = new VmiSupplierBLL().GetList("" + "[SUPPLIER_NUM] in ('" + string.Join("','", sapSupplierQuotaInfos.Select(d => d.Lifnr).ToArray()) + "'", string.Empty); ///VMI仓库 List <WarehouseInfo> warehouseInfos = new List <WarehouseInfo>(); if (vmiSupplierInfos.Count > 0) { warehouseInfos = new WarehouseBLL().GetList("" + "[WAREHOUSE] in ('" + string.Join("','", vmiSupplierInfos.Select(d => d.WmNo).ToArray()) + "') and " + "[WAREHOUSE_TYPE] = " + (int)WarehouseTypeConstants.VMI + "", string.Empty); } ///获取工厂信息 List <PlantInfo> plantInfos = new PlantBLL().GetListForInterfaceDataSync(); ///已处理完成的ID List <long> dealedIds = new List <long>(); ///逐条处理中间表数据 foreach (var sapSupplierQuotaInfo in sapSupplierQuotaInfos) { PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.SapPlantCode == sapSupplierQuotaInfo.Werks); if (plantInfo == null) { ///将这样的数据更新为挂起状态 @string.AppendLine("update [LES].[TI_IFM_SAP_SUPPLIER_QUOTA] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'3x00000019'," +///工厂信息不存在 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapSupplierQuotaInfo.Id + ";"); continue; } MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.Plant == plantInfo.Plant && d.PartNo == sapSupplierQuotaInfo.PartNo); if (maintainPartsInfo == null) { ///将这样的数据更新为挂起状态 @string.AppendLine("update [LES].[TI_IFM_SAP_SUPPLIER_QUOTA] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'0x00000417'," +///物料信息错误 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapSupplierQuotaInfo.Id + ";"); continue; } ///VMI供应商关系,且未启用LES的VMI模块的 List <VmiSupplierInfo> vmiSuppliers = vmiSupplierInfos.Where(d => d.SupplierNum == sapSupplierQuotaInfo.Lifnr && !d.VmiFlag.GetValueOrDefault()).ToList(); List <WarehouseInfo> warehouses = new List <WarehouseInfo>(); if (vmiSuppliers.Count > 0) { warehouses = warehouseInfos.Where(d => vmiSuppliers.Select(v => v.WmNo).Contains(d.Warehouse)).ToList(); } ///当前业务数据表中无此工厂代码+物料编号+供应商信息时需要新增 SupplierPartQuotaInfo supplierPartQuotaInfo = supplierPartQuotaInfos.FirstOrDefault(d => d.PartNo == maintainPartsInfo.PartNo && d.Plant == plantInfo.Plant && d.SupplierNum == sapSupplierQuotaInfo.Lifnr); ///标识该配额需要删除 ///停供作为删除处理,ZSTOP = X时标识停供 if (sapSupplierQuotaInfo.Flag.ToUpper() == "D" || sapSupplierQuotaInfo.Zstop.ToUpper() == "X") { ///根据工厂代码+物料编号+供应商对配额信息进行逻辑删除 @string.AppendLine("update [LES].[TM_BAS_SUPPLIER_PART_QUOTA] " + "set [VALID_FLAG] = 0," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [PART_NO] = N'" + sapSupplierQuotaInfo.PartNo + "' and " + "[PLANT] = N'" + plantInfo.Plant + "' and " + "[SUPPLIER_NUM] = N'" + sapSupplierQuotaInfo.Lifnr + "' and " + "[VALID_FLAG] = 1;"); dealedIds.Add(sapSupplierQuotaInfo.Id); if (supplierPartQuotaInfo == null) { supplierPartQuotaInfo = SupplierPartQuotaBLL.CreateSupplierPartQuotaInfo(loginUser); } ///SapSupplierQuotaInfo -> SupplierPartQuotaInfo SupplierPartQuotaBLL.GetSupplierPartQuotaInfo(sapSupplierQuotaInfo, ref supplierPartQuotaInfo); @string.AppendLine(SupplierPartQuotaBLL.GetSyncVmiSupplierPartSql( sapSupplierQuotaInfo.SupplierName, maintainPartsInfo.PartCname, warehouses, supplierPartQuotaInfo, enable_srm_flag, enable_vmi_flag, true, loginUser)); continue; } /// if (supplierPartQuotaInfo == null) { supplierPartQuotaInfo = SupplierPartQuotaBLL.CreateSupplierPartQuotaInfo(loginUser); ///SapSupplierQuotaInfo -> SupplierPartQuotaInfo SupplierPartQuotaBLL.GetSupplierPartQuotaInfo(sapSupplierQuotaInfo, ref supplierPartQuotaInfo); /// @string.AppendLine(SupplierPartQuotaDAL.GetInsertSql(supplierPartQuotaInfo)); ///加入后以免影响下次判断 supplierPartQuotaInfos.Add(supplierPartQuotaInfo); /// dealedIds.Add(sapSupplierQuotaInfo.Id); /// @string.AppendLine(SupplierPartQuotaBLL.GetSyncVmiSupplierPartSql( sapSupplierQuotaInfo.SupplierName, maintainPartsInfo.PartCname, warehouses, supplierPartQuotaInfo, enable_srm_flag, enable_vmi_flag, false, loginUser)); continue; } /// if (supplierPartQuotaInfo.Id == 0) { continue; } /// @string.AppendLine("update [LES].[TM_BAS_SUPPLIER_PART_QUOTA] set " + "[EFFECTIVE_DATE] = N'" + sapSupplierQuotaInfo.IDate + "'," + "[INVALID_DATE] = N'" + sapSupplierQuotaInfo.EDate + "'," + "[QUOTE] = " + sapSupplierQuotaInfo.Quote.GetValueOrDefault() + "," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + " where [FID] = N'" + supplierPartQuotaInfo.Fid.GetValueOrDefault() + "';"); /// @string.AppendLine(SupplierPartQuotaBLL.GetSyncVmiSupplierPartSql( sapSupplierQuotaInfo.SupplierName, maintainPartsInfo.PartCname, warehouses, supplierPartQuotaInfo, enable_srm_flag, enable_vmi_flag, false, loginUser)); dealedIds.Add(sapSupplierQuotaInfo.Id); } /// if (dealedIds.Count > 0) { ///已处理的中间表数据更新为已处理状态 @string.AppendLine("update [LES].[TI_IFM_SAP_SUPPLIER_QUOTA] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = NULL," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] in (" + string.Join(",", dealedIds.ToArray()) + ");"); } using (var trans = new TransactionScope()) { if (@string.Length > 0) { BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString()); } trans.Complete(); } }
/// <summary> /// SyncWorkSchedule /// </summary> /// <param name="loginUser"></param> public static void Sync(string loginUser) { List <SapWorkCalendarInfo> sapWorkCalendarInfos = new SapWorkCalendarBLL().GetListByPage("" + "[PROCESS_FLAG] in (" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Resend + ")", "[ID]", 1, 1000, out int dataCnt); if (dataCnt == 0) { return; } ///获取业务表中需要修改的数据 List <WorkScheduleInfo> workScheduleInfos = new WorkScheduleBLL().GetListForInterfaceDataSync(sapWorkCalendarInfos.Select(d => d.ProductionDate.GetValueOrDefault()).ToList()); StringBuilder stringBuilder = new StringBuilder(); ///获取工厂信息,准备对比 List <PlantInfo> plantInfos = new PlantBLL().GetListForInterfaceDataSync(); ///获取车间信息,准备对比 List <WorkshopInfo> workshopInfos = new WorkshopBLL().GetListForInterfaceDataSync(); ///获取生产线信息,准备对比 List <AssemblyLineInfo> assemblyLineInfos = new AssemblyLineBLL().GetListForInterfaceDataSync(); ///已处理完成的ID List <long> dealedIds = new List <long>(); ///逐条处理中间表数据 foreach (var sapWorkCalendarInfo in sapWorkCalendarInfos) { ///处理工厂不对等的数据 PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.SapPlantCode == sapWorkCalendarInfo.Dwerk); if (plantInfo == null) { ///将这样的数据更新为挂起状态 stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_WORK_CALENDAR] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'3x00000019'," +///工厂信息不存在 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapWorkCalendarInfo.Id + ";"); continue; } ///处理车间不对等的数据 WorkshopInfo workshopInfo = workshopInfos.FirstOrDefault(d => d.Plant == plantInfo.Plant && d.Workshop == sapWorkCalendarInfo.Zcj); if (workshopInfo == null) { ///将这样的数据更新为挂起状态 stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_WORK_CALENDAR] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'7x00000015'," +///车间信息不存在 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapWorkCalendarInfo.Id + ";"); continue; } ///处理生产线不对等的数据 AssemblyLineInfo assemblyLineInfo = assemblyLineInfos.FirstOrDefault(d => d.Plant == plantInfo.Plant && d.Workshop == workshopInfo.Workshop && d.AssemblyLine == sapWorkCalendarInfo.LineNo); if (assemblyLineInfo == null) { ///将这样的数据更新为挂起状态 stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_WORK_CALENDAR] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'7x00000016'," +///流水线信息不存在 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapWorkCalendarInfo.Id + ";"); continue; } ///当前业务数据表中无此工厂代码+车间+生产线+日期+班次 即为新增 ///TODO: SAP班次的枚举? int lesShift = Convert.ToInt32(sapWorkCalendarInfo.Shift); /// WorkScheduleInfo workScheduleInfo = workScheduleInfos.FirstOrDefault(d => d.Plant == plantInfo.Plant && d.Workshop == sapWorkCalendarInfo.Zcj && d.AssemblyLine == sapWorkCalendarInfo.LineNo && d.Date == sapWorkCalendarInfo.ProductionDate && d.Shift == lesShift); ///如果为空 进行新增 if (workScheduleInfo == null) { /// 工厂, 日期,班次是必填项 if (string.IsNullOrEmpty(sapWorkCalendarInfo.Dwerk) || sapWorkCalendarInfo.ProductionDate == null || string.IsNullOrEmpty(sapWorkCalendarInfo.Shift)) { ///将这样的数据更新为挂起状态 stringBuilder.Append("update [LES].[TI_IFM_SAP_WORK_CALENDAR] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'7x00000014'," +///工厂, 日期,班次是必填项 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapWorkCalendarInfo.Id.ToString() + ";"); continue; } /// 新增 stringBuilder.AppendFormat("insert into [LES].[TM_BAS_WORK_SCHEDULE] (" + "[FID]" + "[PLANT] ," + "[WORKSHOP] ," + "[ASSEMBLY_LINE] ," + "[DATE] ," + "[SHIFT] ," + "[BEGIN_TIME] ," + "[END_TIME] ," + "[VALID_FLAG] ," + "[CREATE_USER] ," + "[CREATE_DATE]) values (" + "N'{0}' ," /// 工厂 - nvarchar(8) + "N'{1}' ," /// 车间 - nvarchar(32) + "N'{2}' ," /// 生产线 - nvarchar(32) + "N'{3}' ," /// 日期 - datetime + "{4} ," /// 班次 - int + "N'{5}' ," /// 开始时间 - datetime + "N'{6}' ," /// 结束时间 - datetime + "{7} ," /// VALID_FLAG - bit + "N'{8}' ," /// CREATE_USER - nvarchar(32) + "GETDATE() ) ;", /// CREATE_DATE - datetime plantInfo.Plant, /// 工厂 - nvarchar(8),0 workshopInfo.Workshop, /// 车间 - nvarchar(32),1 assemblyLineInfo.AssemblyLine, /// 生产线 - nvarchar(32),2 sapWorkCalendarInfo.ProductionDate, /// 日期 - datetime,3 lesShift, /// 班次 - int,4, sapWorkCalendarInfo.BeginTime, /// 开始时间 - datetime,5 sapWorkCalendarInfo.EndTime, /// 结束时间 - datetime,6 1, /// VALID_FLAG - bit,7 loginUser); /// CREATE_USER - nvarchar(32),8 workScheduleInfos.Add(new WorkScheduleInfo() { Plant = plantInfo.Plant, Workshop = sapWorkCalendarInfo.Zcj, AssemblyLine = sapWorkCalendarInfo.LineNo, Date = sapWorkCalendarInfo.ProductionDate, Shift = lesShift }); } else { ///此次循环的数据在业务表中存在,进行修改 stringBuilder.AppendLine("update [LES].[TM_BAS_WORK_SCHEDULE] " + "set [BEGIN_TIME] = N'" + workScheduleInfo.BeginTime.GetValueOrDefault() + "'," + "[END_TIME] =N'" + workScheduleInfo.EndTime.GetValueOrDefault() + "'," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [PLANT] = N'" + plantInfo.Plant + "' and " + "[WORKSHOP] = N'" + workshopInfo.Workshop + "' and " + "[ASSEMBLY_LINE] = N'" + assemblyLineInfo.AssemblyLine + "' and " + "[DATE] = N'" + sapWorkCalendarInfo.ProductionDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "' and " + "[SHIFT] = N'" + lesShift + "';"); } dealedIds.Add(sapWorkCalendarInfo.Id); } if (dealedIds.Count > 0) { ///已处理的中间表数据更新为已处理状态 stringBuilder.Append("update [LES].[TI_IFM_SAP_WORK_CALENDAR] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = NULL," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] in (" + string.Join(",", dealedIds.ToArray()) + ");"); } using (var trans = new TransactionScope()) { if (stringBuilder.Length > 0) { BLL.SYS.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString()); } trans.Complete(); } }
/// <summary> /// SAP物料基础数据同步 /// </summary> /// <returns></returns> public static void Sync(string loginUser) { ///从中间表提取未处理数据集合 List <SapPartsInfo> sapPartsInfos = new SapPartsBLL().GetListByPage("[PROCESS_FLAG] in(" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Resend + ")", "[ID]", 1, 1000, out int dataCnt); if (dataCnt == 0) { return; } ///获取业务表中要变更的数据集合,准备对比 List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(sapPartsInfos.Select(d => d.Matnr).ToList()); ///执行的stringBuilder语句 StringBuilder stringBuilder = new StringBuilder(); ///获取工厂信息 List <PlantInfo> plantInfos = new PlantBLL().GetListForInterfaceDataSync(); ///已处理完成的ID List <long> dealedIds = new List <long>(); ///逐条处理中间表数据 foreach (var sapPartsInfo in sapPartsInfos) { PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.SapPlantCode == sapPartsInfo.Werks); if (plantInfo == null) { ///将这样的数据更新为挂起状态 stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PARTS] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'3x00000019'," + ///工厂信息不存在 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPartsInfo.Id + ";"); continue; } ///标识该物料需要删除 if (sapPartsInfo.Mstae == "1") { ///根据工厂代码+物料编号对物料信息进行逻辑删除 stringBuilder.AppendLine(" update [LES].[TM_BAS_MAINTAIN_PARTS] " + "set [VALID_FLAG] = 0,[MODIFY_USER] = N'" + loginUser + "',[MODIFY_DATE] = GETDATE() " + "where [PART_NO] = N'" + sapPartsInfo.Matnr + "' and [PLANT] = N'" + plantInfo.Plant + "' and [VALID_FLAG] = 1;"); dealedIds.Add(sapPartsInfo.Id); continue; } ///物料号①、物料中文名称②为必填项 if (string.IsNullOrEmpty(sapPartsInfo.MaktxZh) || string.IsNullOrEmpty(sapPartsInfo.Matnr)) { ///将这样的数据更新为挂起状态 stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PARTS] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = N'3x00000020'," +///物料号、物料中文名称为必填项 "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapPartsInfo.Id + ";"); continue; } ///当前业务数据表中此工厂的该物料信息时需要新增 MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == sapPartsInfo.Matnr && d.Plant == plantInfo.Plant); if (maintainPartsInfo == null) { #region TM_BAS_MAINTAIN_PARTS string partCname = string.Empty; string partEname = string.Empty; string partDname = string.Empty; ///BFDA、1中文、E英文、D德文 switch (sapPartsInfo.Spras.ToUpper()) { case "1": partCname = sapPartsInfo.MaktxZh.Replace("'", "''"); break; case "E": partEname = sapPartsInfo.MaktxZh.Replace("'", "''"); break; case "D": partDname = sapPartsInfo.MaktxZh.Replace("'", "''"); break; } stringBuilder.AppendLine("insert into [LES].[TM_BAS_MAINTAIN_PARTS] (" + "[FID]," + "[PART_NO]," + "[PLANT]," + "[PART_CLS]," + "[PART_CNAME]," + "[PART_ENAME]," + "[PART_DNAME]," + "[PART_UNITS]," + "[MRP_CONTROL]," + "[MRP_TYPE]," + "[CREATE_USER]," + "[CREATE_DATE]," + "[VALID_FLAG]," + "[PART_PURCHASER]" + ") values (" + "NEWID()," ///FID + "N'" + sapPartsInfo.Matnr.Replace("'", "''") + "'," ///PART_NO + "N'" + plantInfo.Plant + "'," ///PLANT + "N'" + sapPartsInfo.Mtart + "'," ///PART_CLS + "N'" + partCname + "'," ///PART_CNAME + "N'" + partEname + "'," ///PART_ENAME + "N'" + partDname + "'," ///PART_DNAME + "N'" + sapPartsInfo.Meins + "'," ///PART_UNITS + "N'" + sapPartsInfo.Dispo + "'," ///MRP_CONTROL + "N'" + sapPartsInfo.Dismm + "'," ///MRP_TYPE + "N'" + loginUser + "'," ///CREATE_USER + "GETDATE()," ///CREATE_DATE + "1" ///VALID_FLAG + ",N'" + sapPartsInfo.Ekgrp + "'" ///PART_PURCHASER + ");"); #endregion dealedIds.Add(sapPartsInfo.Id); maintainPartsInfos.Add( new MaintainPartsInfo() { Plant = plantInfo.Plant, PartNo = sapPartsInfo.Matnr }); continue; } else { string partCname = string.Empty; string partEname = string.Empty; string partDname = string.Empty; ///BFDA、1中文、E英文、D德文 switch (sapPartsInfo.Spras.ToUpper()) { case "1": partCname = sapPartsInfo.MaktxZh.Replace("'", "''"); break; case "E": partEname = sapPartsInfo.MaktxZh.Replace("'", "''"); break; case "D": partDname = sapPartsInfo.MaktxZh.Replace("'", "''"); break; } stringBuilder.AppendLine("update [LES].[TM_BAS_MAINTAIN_PARTS] set " + "[PART_CLS] = N'" + sapPartsInfo.Mtart + "'," + (string.IsNullOrEmpty(partCname) ? string.Empty : "[PART_CNAME] =N'" + partCname + "',") + (string.IsNullOrEmpty(partEname) ? string.Empty : "[PART_ENAME] =N'" + partEname + "',") + (string.IsNullOrEmpty(partDname) ? string.Empty : "[PART_DNAME] =N'" + partDname + "',") + "[PART_UNITS] = N'" + sapPartsInfo.Meins + "'," + "[MRP_CONTROL] = N'" + sapPartsInfo.Dispo + "'," + "[MRP_TYPE] = N'" + sapPartsInfo.Dismm + "'," + "[PART_PURCHASER]=N'" + sapPartsInfo.Ekgrp + "'," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [PART_NO] = '" + maintainPartsInfo.PartNo + "' AND [PLANT]='" + maintainPartsInfo.Plant + "';"); dealedIds.Add(sapPartsInfo.Id); } } /// if (dealedIds.Count > 0) { ///已处理的中间表数据更新为已处理状态 stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_PARTS] " + "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," + "[PROCESS_TIME] = GETDATE()," + "[COMMENTS] = NULL," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] in (" + string.Join(",", dealedIds.ToArray()) + ");"); } using (var trans = new TransactionScope()) { if (stringBuilder.Length > 0) { BLL.SYS.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString()); } trans.Complete(); } }
/// <summary> /// Handler /// </summary> public void Handler() { InitConfigFlag(); ///获取未同步的交易记录 List <TranDetailsInfo> tranDetailsInfos = new TranDetailsBLL().GetList("[TRAN_STATE] = " + (int)WmmTranStateConstants.Created + "", "[ID]"); if (tranDetailsInfos.Count == 0) { return; } ///仓库 List <string> wmNos = tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.WmNo)).Select(d => d.WmNo).ToList(); wmNos.AddRange(tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.TargetWm)).Select(d => d.TargetWm).ToList()); List <WarehouseInfo> warehouseInfos = new WarehouseBLL().GetList("[WAREHOUSE] in ('" + string.Join("','", wmNos.ToArray()) + "')", string.Empty); ///存储区 List <string> zoneNos = tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.ZoneNo)).Select(d => d.ZoneNo).ToList(); zoneNos.AddRange(tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.TargetZone)).Select(d => d.TargetZone).ToList()); List <ZonesInfo> zonesInfos = new ZonesBLL().GetList("[ZONE_NO] in ('" + string.Join("','", zoneNos.ToArray()) + "')", string.Empty); ///工厂 List <PlantInfo> plantInfos = new PlantBLL().GetListForInterfaceDataSync(); ///供应商 List <SupplierInfo> supplierInfos = new SupplierBLL().GetListForInterfaceDataSync(tranDetailsInfos.Select(d => d.SupplierNum).ToList()); ///物料 List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(tranDetailsInfos.Select(d => d.PartNo).ToList()); ///逐条处理 foreach (var tranDetailsInfo in tranDetailsInfos) { StringBuilder stringBuilder = new StringBuilder(); ///工厂 PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.Plant == tranDetailsInfo.Plant); ///供应商 SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == tranDetailsInfo.SupplierNum); ///目标仓库 WarehouseInfo targetWarehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == tranDetailsInfo.TargetWm); ///目标存储区 ZonesInfo targetZonesInfo = zonesInfos.FirstOrDefault(d => d.ZoneNo == tranDetailsInfo.TargetZone); ///目标工厂 PlantInfo targetPlantInfo = targetZonesInfo == null ? null : plantInfos.FirstOrDefault(d => d.Plant == targetZonesInfo.Plant); ///来源 ZonesInfo sourceZonesInfo = zonesInfos.FirstOrDefault(d => d.ZoneNo == tranDetailsInfo.ZoneNo); ///来源工厂 PlantInfo sourcePlantInfo = sourceZonesInfo == null ? null : plantInfos.FirstOrDefault(d => d.Plant == sourceZonesInfo.Plant); ///物料信息 MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == tranDetailsInfo.PartNo && d.Plant == tranDetailsInfo.Plant); ///创建库存对象 StocksInfo stocksInfo = null; /// switch (tranDetailsInfo.TranType.GetValueOrDefault()) { ///物料入库 case (int)WmmTranTypeConstants.Inbound: ///目标可用库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///撤销入库 case (int)WmmTranTypeConstants.UndoInbound: stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); UndoStock(ref stocksInfo); stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///物料出库 case (int)WmmTranTypeConstants.Outbound: ///来源可用库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///撤销出库 case (int)WmmTranTypeConstants.UndoOutbound: stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); UndoStock(ref stocksInfo); stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///冻结入库 case (int)WmmTranTypeConstants.FrozenInbound: ///目标冻结库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///冻结出库 case (int)WmmTranTypeConstants.FrozenOutbound: ///来源冻结库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///物料冻结 case (int)WmmTranTypeConstants.MaterialFreezing: ///来源可用库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); ///目标冻结库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///状态冻结 case (int)WmmTranTypeConstants.StateFreezing: ///来源可用库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); ///来源冻结库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///物料解冻 case (int)WmmTranTypeConstants.MaterialThawing: ///来源冻结库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); ///目标可用库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///状态解冻 case (int)WmmTranTypeConstants.StateThawing: ///来源可用库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); ///来源冻结库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///冻结移动 case (int)WmmTranTypeConstants.FrozenMovement: ///来源冻结库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); ///目标冻结库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; ///物料移动 case (int)WmmTranTypeConstants.Movement: ///来源可用库存减少 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true); stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser)); ///目标可用库存增加 stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false); stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser)); break; default: continue; } ///SAP移动数据 stringBuilder.AppendFormat(CreateSapTranData(tranDetailsInfo, sourceZonesInfo, targetZonesInfo, sourcePlantInfo, targetPlantInfo)); ///SRM入库数据 stringBuilder.AppendFormat(CreateSrmTranData(tranDetailsInfo, supplierInfo, targetWarehouseInfo)); ///WMS入库数据 stringBuilder.AppendFormat(CreateVmiTranData(tranDetailsInfo, supplierInfo, targetWarehouseInfo)); #region 执行 using (TransactionScope trans = new TransactionScope()) { if (stringBuilder.Length > 0) { BLL.LES.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString()); } trans.Complete(); } #endregion Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":" + stocksInfo.PartNo + "|" + tranDetailsInfo.TranType.GetValueOrDefault() + "|" + stocksInfo.WmNo + "|" + stocksInfo.ZoneNo + "|" + stocksInfo.Dloc + "|"); } }