/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(ScanPointInfo info)
{
int cnt = dal.GetCounts("[SCAN_POINT_CODE] = N'" + info.ScanPointCode + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000280");///扫描点代码不能重复
}
cnt = dal.GetCounts("[SCAN_POINT_NAME] = N'" + info.ScanPointName + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000281");///扫描点名称不能重复
}
return(dal.Add(info));
}
/// <summary>
/// LogicDeleteInfo
/// </summary>
/// <param name="id"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool LogicDeleteInfo(long id, string loginUser)
{
ScanPointInfo info = dal.GetInfo(id);
if (info == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
int cnt = new StatusPointDAL().GetCounts("[SCAN_POINT_CODE] = N'" + info.ScanPointCode + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000284");///扫描点已被状态点设置，不允许进行删除
}
return(dal.LogicDelete(id, loginUser) > 0 ? true : false);
}
/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <ScanPointInfo> scanPointExcelInfos = CommonDAL.DatatableConvertToList <ScanPointInfo>(dataTable).ToList();
if (scanPointExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <ScanPointInfo> scanPointInfos = dal.GetList("[SCAN_POINT_CODE] in ('" + string.Join("','", scanPointExcelInfos.Select(d => d.ScanPointCode).ToArray()) + "') ", string.Empty);
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var scanPointExcelInfo in scanPointExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
ScanPointInfo scanPointInfo = scanPointInfos.FirstOrDefault(d => d.ScanPointCode == scanPointExcelInfo.ScanPointCode);
if (scanPointInfo == null)
{
if (string.IsNullOrEmpty(scanPointExcelInfo.ScanPointCode) ||
string.IsNullOrEmpty(scanPointExcelInfo.ScanPointName) ||
string.IsNullOrEmpty(scanPointExcelInfo.Plant) ||
string.IsNullOrEmpty(scanPointExcelInfo.Workshop) ||
string.IsNullOrEmpty(scanPointExcelInfo.AssemblyLine))
{
throw new Exception("MC:0x00000282");///扫描点代码、扫描点名称、工厂、车间、生产线不能为空
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <ScanPointInfo>(scanPointExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_BAS_SCAN_POINT with(nolock) where [SCAN_POINT_CODE] = N'" + scanPointExcelInfo.ScanPointCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_BAS_SCAN_POINT] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
///为防止EXCEL中数据有重复
scanPointInfo = new ScanPointInfo();
scanPointInfo.ScanPointCode = scanPointExcelInfo.ScanPointCode;
scanPointInfos.Add(scanPointInfo);
///
continue;
}
if (string.IsNullOrEmpty(scanPointExcelInfo.ScanPointCode) ||
string.IsNullOrEmpty(scanPointExcelInfo.ScanPointName) ||
string.IsNullOrEmpty(scanPointExcelInfo.Plant) ||
string.IsNullOrEmpty(scanPointExcelInfo.Workshop) ||
string.IsNullOrEmpty(scanPointExcelInfo.AssemblyLine))
{
throw new Exception("MC:0x00000282");///扫描点代码、扫描点名称、工厂、车间、生产线不能为空
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <ScanPointInfo>(scanPointExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_SCAN_POINT] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + scanPointInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
throw new Exception("MC:0x00000283");///:没有可导入更新的数据
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// 车辆过点信息处理
/// </summary>
/// <param name="mesVehiclePointScanInfo"></param>
/// <param name="scanPointInfos"></param>
/// <param name="statusPointInfos"></param>
/// <param name="intLesVehicleSeqStep"></param>
/// <returns></returns>
public string VehicleCrossingScanSql(MesVehiclePointScanInfo mesVehiclePointScanInfo, List <ScanPointInfo> scanPointInfos, List <StatusPointInfo> statusPointInfos, int intLesVehicleSeqStep, PullOrdersInfo pullOrdersInfo, List <PlantInfo> plantInfos)
{
///sql
StringBuilder stringBuilder = new StringBuilder();
///根据采集点编号④ = MES扫描点③获取到唯一的扫描点代码①，此处需要匹配工厂编号① = 工厂④
PlantInfo plantInfo = plantInfos.Where(d => d.SapPlantCode == mesVehiclePointScanInfo.Enterprise).First();
ScanPointInfo scanPointInfo = scanPointInfos.Where(d => d.MesScanPointCode == mesVehiclePointScanInfo.UnitNo && d.Plant == plantInfo.Plant).FirstOrDefault();
if (scanPointInfo == null)
{
return(string.Empty);
}
///并根据唯一的扫描点代码①=④从状态点表中获取对应的状态点集合
List <StatusPointInfo> statusPointByscanPointInfos = statusPointInfos.Where(d => d.ScanPointCode == scanPointInfo.ScanPointCode).ToList();
if (statusPointByscanPointInfos.Count == 0)
{
return(string.Empty);
}
///处理状态点集合
foreach (StatusPointInfo statusPointInfo in statusPointByscanPointInfos)
{
///当状态点数据中是否匹配生产线⑪=true时、需要根据计划订单号匹配生产订单⑥中对应的生产线、若是否匹配生产线⑪=false则无需进行匹配
string assemblyLine = statusPointInfo.AssemblyLineFixedFlag.GetValueOrDefault() ? statusPointInfo.AssemblyLine : string.Empty;
///车辆状态点信息
List <VehiclePointStatusInfo> vehiclePointStatusInfos = new VehiclePointStatusBLL().GetList("" +
"[PLANT] = N'" + statusPointInfo.Plant + "' and " +
"[WORKSHOP] = N'" + statusPointInfo.Workshop + "' and " +
(string.IsNullOrEmpty(assemblyLine) ? string.Empty : "[ASSEMBLY_LINE] = N'" + assemblyLine + "' and ") +
"[ORDER_NO] = N'" + mesVehiclePointScanInfo.DmsNo + "'", string.Empty);
///扫描点基础数据
List <StatusPointInfo> statusPoints = new StatusPointBLL().GetList("" +
"[PLANT] = N'" + statusPointInfo.Plant + "' and " +
(string.IsNullOrEmpty(assemblyLine) ? string.Empty : "[ASSEMBLY_LINE] = N'" + assemblyLine + "' and ") +
"[WORKSHOP] = N'" + statusPointInfo.Workshop + "'", string.Empty);
///若没有数据则需要根据该维度获取状态点基础数据中对应的数据集合
///并以此集合为基础将信息匹配完成后批量写入车辆状态点信息
long currentSeqNo = 0;
#region 横向维度
if (vehiclePointStatusInfos.Count == 0)
{
///首次批量插入一个生产订单在工厂⑨车间⑧或工厂⑨车间⑧生产线⑦维度范围内数据时
foreach (var statusPoint in statusPoints.OrderBy(d => d.StatusPointSeq.GetValueOrDefault()).ToList())
{
///StatusPointInfo ->VehiclePointStatusInfo
VehiclePointStatusInfo pointStatusInfo = new VehiclePointStatusInfo();
///ORDER_NO,生产订单号
pointStatusInfo.OrderNo = mesVehiclePointScanInfo.DmsNo;
///STATUS_POINT_CODE,状态点代码
pointStatusInfo.StatusPointCode = statusPoint.StatusPointCode;
///PLANT
pointStatusInfo.Plant = statusPoint.Plant;
///Workshop
pointStatusInfo.Workshop = statusPoint.Workshop;
///ASSEMBLY_LINE
pointStatusInfo.AssemblyLine = statusPoint.AssemblyLine;
///SPJ
pointStatusInfo.Spj = pullOrdersInfo.Spj;
///KNR
pointStatusInfo.Knr = pullOrdersInfo.Knr;
///Schicht
pointStatusInfo.Schicht = null;
///Shift
pointStatusInfo.Shift = null;
///Vin
pointStatusInfo.Vin = pullOrdersInfo.Vin;
///RunningNo
pointStatusInfo.RunningNo = mesVehiclePointScanInfo.DmsSeq.ToString();
///PassTime，过点时间
pointStatusInfo.PassTime = null;
///VehicleStatus，初始化
pointStatusInfo.VehicleStatus = (int)VehicleStatusTypeConstants.Initializtion;
///ProcessFlag
pointStatusInfo.ProcessFlag = (int)ProcessFlagConstants.Untreated;
///SeqNo，LES序号
pointStatusInfo.SeqNo = GetcurrentSeqNo(statusPoints, statusPointInfo, intLesVehicleSeqStep);
stringBuilder.AppendLine(VehiclePointStatusDAL.GetInsertSql(pointStatusInfo));
}
///LES排序号
currentSeqNo = GetcurrentSeqNo(statusPoints, statusPointInfo, intLesVehicleSeqStep);
///更新生产订单状态为已上线
stringBuilder.AppendFormat("update [LES].[TT_BAS_PULL_ORDERS] set " +
"[ORDER_STATUS]=" + (int)OrderStatusConstants.AlreadOnline + "" +
",[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE() " +
"where [ID] = " + pullOrdersInfo.Id + ";");
}
///若已存在数据则首先需要将本采集点编号④对应的状态点代码②数据的过点时间⑦=⑦、车辆状态⑨更新
///车辆状态的更新目前已知的类型有初始化、正常过点、校验补入、车辆离队、车辆归队，需要枚举项VEHICLE_STATUS_TYPE支持
else
{
VehiclePointStatusInfo pointStatusInfo = vehiclePointStatusInfos.FirstOrDefault(d => d.StatusPointCode == statusPointInfo.StatusPointCode);
if (pointStatusInfo == null)
{
continue;
}
///当车辆离队时，更新LES排序号⑬为零，车辆归队时重新计算车辆所在位置对应的LES排序号⑬，规则依照之前逻辑
///离队标记
bool leaveFlag = false;
if (leaveFlag)
{
List <StatusPointInfo> statuses = statusPoints.Where(d => d.StatusPointSeq.GetValueOrDefault() >= statusPointInfo.StatusPointSeq.GetValueOrDefault()).ToList();
List <VehiclePointStatusInfo> infos = vehiclePointStatusInfos.Where(d => statuses.Select(s => s.StatusPointCode).Contains(d.StatusPointCode)).ToList();
foreach (var info in infos)
{
stringBuilder.AppendLine("update [LES].[TT_BAS_VEHICLE_POINT_STATUS] " +
"set [VEHICLE_STATUS] = " + (int)VehicleStatusTypeConstants.VehicleLeave + ",[SEQ_NO] = 0,[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE() " +
"where [ID] = " + info.Id + ";");
}
///更新生产订单状态为已离队
stringBuilder.AppendFormat("update [LES].[TT_BAS_PULL_ORDERS] set " +
"[ORDER_STATUS]=" + (int)OrderStatusConstants.Bryan + "" +
",[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE() " +
"where [ID] = " + pullOrdersInfo.Id + ";");
continue;
}
///车辆归队
if (pointStatusInfo.VehicleStatus == (int)VehicleStatusTypeConstants.VehicleLeave)
{
stringBuilder.AppendLine("update [LES].[TT_BAS_VEHICLE_POINT_STATUS] " +
"set [PASS_TIME] = N'" + mesVehiclePointScanInfo.SendTime.GetValueOrDefault() + "'," +
"[VEHICLE_STATUS] = " + (int)VehicleStatusTypeConstants.VehicleReturn + " ," +
"[SEQ_NO] = " + GetcurrentSeqNo(statusPoints, statusPointInfo, intLesVehicleSeqStep) + ",[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE()" +
"where [ID] = " + pointStatusInfo.Id + ";");
List <StatusPointInfo> statuses = statusPoints.Where(d => d.StatusPointSeq.GetValueOrDefault() > statusPointInfo.StatusPointSeq.GetValueOrDefault()).ToList();
List <VehiclePointStatusInfo> infos = vehiclePointStatusInfos.Where(d => statuses.Select(s => s.StatusPointCode).Contains(d.StatusPointCode)).ToList();
foreach (var info in infos)
{
stringBuilder.AppendLine("update [LES].[TT_BAS_VEHICLE_POINT_STATUS] " +
"set [VEHICLE_STATUS] = " + (int)VehicleStatusTypeConstants.Initializtion + " ," +
"[SEQ_NO] = " + GetcurrentSeqNo(statuses, statusPointInfo, intLesVehicleSeqStep) + " ,[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE()" +
"where [ID] = " + info.Id + ";");
}
///更新生产订单状态为已归队
stringBuilder.AppendFormat("update [LES].[TT_BAS_PULL_ORDERS] set " +
"[ORDER_STATUS]=" + (int)OrderStatusConstants.ComeBack + "" +
",[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE() " +
"where [ID] = " + pullOrdersInfo.Id + ";");
}
///LES序号
currentSeqNo = pointStatusInfo.SeqNo.GetValueOrDefault();
DateTime currentTime = mesVehiclePointScanInfo.SendTime.GetValueOrDefault();
stringBuilder.AppendLine("update [LES].[TT_BAS_VEHICLE_POINT_STATUS] " +
"set [PASS_TIME] = N'" + currentTime + "',[VEHICLE_STATUS] = " + (int)VehicleStatusTypeConstants.NormalPoint + " ,[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE()" +
"where [ID] = " + pointStatusInfo.Id + ";");
///对于状态点顺序在本次更新的车辆状态点信息之前的且车辆状态为初始化的数据需要更新其过点时间⑦、车辆状态⑨为校验补入
///过点时间根据获取最近一条有过点时间⑦到本条车辆状态数据过点时间⑦的时间差按车辆状态⑨初始化的状态点数量平均分配计算赋值
List <VehiclePointStatusInfo> vehiclePointStatuses = vehiclePointStatusInfos.
Where(d => d.VehicleStatus == (int)VehicleStatusTypeConstants.VehicleLeave && d.StatusPointCode != statusPointInfo.StatusPointCode).
OrderByDescending(d => d.Id).
ToList();
if (vehiclePointStatuses.Count == 0)
{
continue;
}
DateTime?lastTime = new VehiclePointStatusDAL().GetLastTime(
statusPointInfo.Plant,
statusPointInfo.Workshop,
statusPointInfo.StatusPointCode,
statusPointInfo.AssemblyLine);
TimeSpan timeSpan = mesVehiclePointScanInfo.SendTime.GetValueOrDefault() - lastTime.GetValueOrDefault();
int deductionSecondsPer = Convert.ToInt32(timeSpan.TotalSeconds) / (vehiclePointStatuses.Count - 1);
foreach (var vehiclePointStatuse in vehiclePointStatuses)
{
currentTime = currentTime.AddSeconds(0 - deductionSecondsPer);
stringBuilder.AppendLine("update [LES].[TT_BAS_VEHICLE_POINT_STATUS] set " +
"[PASS_TIME] = N'" + currentTime + "'," +
"[VEHICLE_STATUS] = " + (int)VehicleStatusTypeConstants.CheckAndFill + ",[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE() " +
"where [ID] = " + vehiclePointStatuse.Id + ";");
}
///TODO:
///当前过点类型为正常过点、车辆离队时触发以上逻辑
///过点类型目前接口中未体现，后期需要增加，预先考虑逻辑放置位置，类型为车辆归队时只需更新本条记录即可
///若接口中车辆归队与正常过点无法区分，则需要在车辆离队时将其状态标记在生产订单上，以便区别正常过点与车辆归队
}
///以上为横向维度（同生产订单号）的处理逻辑
#endregion
///以下为纵向维度（同状态点）的处理逻辑
stringBuilder.AppendFormat(LongitudinalDimension(statusPoints, statusPointInfo, mesVehiclePointScanInfo, assemblyLine, currentSeqNo));
}
return(stringBuilder.ToString());
}
