/// 同步工厂布局
/// </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 + "|");
}
}
