public void GetWorkshopsTest()
{
WorkshopBLL target = new WorkshopBLL(); // TODO: Initialize to an appropriate value
List <Workshop> expected = null; // TODO: Initialize to an appropriate value
List <Workshop> actual;
actual = target.GetWorkshops();
Assert.AreEqual(expected, actual);
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void DeleteWorkshopTest()
{
WorkshopBLL target = new WorkshopBLL();
Workshop workshop = new Workshop {
WorkshopID = 24
};
target.DeleteWorkshop(workshop);
Assert.Inconclusive("TODO: Implement code to verify target");
}
public void UpdateWorkshopTest()
{
WorkshopBLL target = new WorkshopBLL();
Workshop workshop = new Workshop {
WorkshopID = 21, Plant = new Plant {
PlantID = 6
}, Available = true, WorkshopCode = "dd", WorshopName = "ddd"
};
target.UpdateWorkshop(workshop);
Assert.Inconclusive("TODO: Implement code to verify target");
}
public void WorkshopBLLConstructorTest()
{
WorkshopBLL target = new WorkshopBLL();
Assert.Inconclusive("TODO: Implement code to verify target");
}
/// 同步工厂布局
/// </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>
/// 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();
}
}
