/// <summary>
/// 计算排序号
/// </summary>
/// <param name="currentSeqNo"></param>
/// <param name="statusPoints"></param>
/// <param name="statusPointInfo"></param>
/// <param name="intLesVehicleSeqStep"></param>
/// <returns></returns>
public long GetcurrentSeqNo(List <StatusPointInfo> statusPoints, StatusPointInfo statusPointInfo, int intLesVehicleSeqStep)
{
long currentSeqNo = 0;
///若本次采集点编号④对应的状态点代码②是该维度下状态点顺序号③最小的一个
if (statusPoints.Min(d => d.StatusPointSeq.GetValueOrDefault()) == statusPointInfo.StatusPointSeq.GetValueOrDefault())
{
///则根据当前该维度下最大的LES排序号⑬
///系统配置步长作为本次的LES排序号⑬
currentSeqNo = new VehiclePointStatusDAL().GetMaxSeqNo(
statusPointInfo.Plant,
statusPointInfo.Workshop,
statusPointInfo.StatusPointCode,
statusPointInfo.AssemblyLine) + intLesVehicleSeqStep;
}
else
{
///否则需要获取状态点代码①过点时间距离当前时间最近的前辆车以及状态点顺序③距离当前最近后辆车的LES排序号⑬
currentSeqNo = new VehiclePointStatusDAL().GetLastTimeSeqNo(
statusPointInfo.Plant,
statusPointInfo.Workshop,
statusPointInfo.StatusPointCode,
statusPointInfo.AssemblyLine);
List <StatusPointInfo> statuses = statusPoints.
Where(d => d.StatusPointSeq.GetValueOrDefault() < statusPointInfo.StatusPointSeq.GetValueOrDefault()).
OrderByDescending(d => d.StatusPointSeq.GetValueOrDefault()).
ToList();
long nextSeqNo = 0;
foreach (var statuse in statuses)
{
nextSeqNo = new VehiclePointStatusDAL().GetLastTimeSeqNo(
statuse.Plant,
statuse.Workshop,
statuse.StatusPointCode,
statuse.AssemblyLine);
if (nextSeqNo > 0)
{
break;
}
}
///求其平均值作为本次的LES排序号⑬
currentSeqNo += nextSeqNo == 0 ? 100 : nextSeqNo;
currentSeqNo = nextSeqNo == 0 ? currentSeqNo : currentSeqNo / 2;
}
return(currentSeqNo);
}
/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(StatusPointInfo info)
{
int cnt = dal.GetCounts("[STATUS_POINT_CODE] = N'" + info.StatusPointCode + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000285");///状态点代码重复
}
cnt = dal.GetCounts("[STATUS_POINT_NAME] = N'" + info.StatusPointName + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000286");///状态点名称重复
}
///TODO:工位⑤在同一生产线⑦下的状态点不能重复设置
cnt = dal.GetCounts("[ASSEMBLY_LINE] = N'" + info.AssemblyLine + "' and [STATUS_POINT_SEQ] = N'" + info.StatusPointSeq.GetValueOrDefault() + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000287");///同一生产线范围内不能重复填写状态点顺序
}
return(dal.Add(info));
}
/// <summary>
/// 纵向维度
/// </summary>
/// <param name="statusPoints"></param>
/// <param name="statusPointInfo"></param>
/// <param name="mesVehiclePointScanInfo"></param>
/// <param name="assemblyLine"></param>
/// <param name="currentSeqNo"></param>
/// <returns></returns>
public string LongitudinalDimension(List <StatusPointInfo> statusPoints, StatusPointInfo statusPointInfo, MesVehiclePointScanInfo mesVehiclePointScanInfo, string assemblyLine, long currentSeqNo)
{
///sql
StringBuilder stringBuilder = new StringBuilder();
///以下开始为纵向维度（同状态点）的处理逻辑
///以本次采集点编号④对应状态点代码①的状态点顺序③为基准
List <StatusPointInfo> pointInfos = statusPoints.
Where(d => d.StatusPointSeq.GetValueOrDefault() > statusPointInfo.StatusPointSeq.GetValueOrDefault()).
OrderBy(d => d.StatusPointSeq.GetValueOrDefault()).
ToList();
List <VehiclePointStatusInfo> pointStatusInfos = new VehiclePointStatusBLL().GetList("" +
"[PLANT] = N'" + statusPointInfo.Plant + "' and " +
"[WORKSHOP] = N'" + statusPointInfo.Workshop + "' and " +
(string.IsNullOrEmpty(assemblyLine) ? string.Empty : "[ASSEMBLY_LINE] = N'" + assemblyLine + "' and ") +
"[SEQ_NO] < " + currentSeqNo + " and " +
"[VEHICLE_STATUS] =" + (int)VehicleStatusTypeConstants.VehicleLeave + "", string.Empty);
///依次将状态点顺序③比参照数据大的数据 且非本次计划订单号⑥中车辆状态⑨为初始化数据的过点时间⑦、车辆状态⑨进行更新
///直至状态点顺序③最大值，理论上当状态点顺序号③逐渐变大时更新数据的数量始终唯一，过点时间⑦按本次发送时间⑦进行更新，车辆状态⑨为正常过点
foreach (var pointInfo in pointInfos)
{
VehiclePointStatusInfo pointStatusInfo = pointStatusInfos.
Where(d => d.StatusPointCode == pointInfo.StatusPointCode).
OrderByDescending(d => d.SeqNo.GetValueOrDefault()).
FirstOrDefault();
if (pointStatusInfo == null)
{
continue;
}
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 + ";");
}
return(stringBuilder.ToString());
}
/// <summary>
/// 在线替换
/// </summary>
/// <param name="bomRepleaceConditionInfos"></param>
/// <param name="loginUser"></param>
public void OnlineReplacement(List <BomRepleaceConditionInfo> bomRepleaceConditionInfos, string loginUser)
{
if (bomRepleaceConditionInfos.Count == 0)
{
return;
}
///生产订单
List <PullOrdersInfo> pullOrdersInfos = new PullOrdersDAL().GetList("" +
" and [CHANGE_FLAG]=" + (int)ChangeFlagConstants.NotReplaced + "", string.Empty);
if (pullOrdersInfos.Count == 0)
{
return;
}
///车辆状态点信息集合
List <VehiclePointStatusInfo> vehiclePointStatusInfos = new VehiclePointStatusDAL().GetList("" +
"and [ORDER_NO] in ('" + string.Join("','", bomRepleaceConditionInfos.Select(d => d.StartPorderCode).ToArray()) + "')", string.Empty);
if (vehiclePointStatusInfos.Count == 0)
{
return;
}
///状态点集合
List <StatusPointInfo> statusPointInfos = new StatusPointDAL().GetList("" +
"[STATUS_POINT_CODE] in ('" + string.Join("','", vehiclePointStatusInfos.Select(d => d.StatusPointCode).ToArray()) + "')", string.Empty);
if (statusPointInfos.Count == 0)
{
return;
}
///时间窗（过点累计方式）零件类
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("" +
" and [STATUS] =" + (int)BasicDataStatusConstants.Enable + " " +
" and [REQUIREMENT_ACCUMULATE_MODE]=" + (int)RequirementAccumulateModeConstants.PassSpot + "" +
" and [STATUS_POINT_CODE] in ('" + string.Join("','", statusPointInfos.Select(d => d.StatusPointCode).ToArray()) + "')", string.Empty);
///排序拉动方式 零件类
List <JisPartBoxInfo> jisPartBoxInfos = new JisPartBoxDAL().GetList("" +
" and [STATUS] =" + (int)BasicDataStatusConstants.Enable + "" +
" and [STATUS_POINT_CODE] in ('" + string.Join("','", statusPointInfos.Select(d => d.StatusPointCode).ToArray()) + "')", string.Empty);
///相应的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("" +
" and [STATUS] =" + (int)BasicDataStatusConstants.Enable + "" +
" (and [INHOUSE_PART_CLASS] in ('" + string.Join("','", twdPartBoxInfos.Select(d => d.PartBoxCode).ToArray()) + "')" +
" or [INHOUSE_PART_CLASS] in ('" + string.Join("','", jisPartBoxInfos.Select(d => d.PartBoxCode).ToArray()) + "'))", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
return;
}
foreach (BomRepleaceConditionInfo bomRepleaceConditionInfo in bomRepleaceConditionInfos)
{
///有效时间内
if (!(bomRepleaceConditionInfo.ExecuteStartTime <= DateTime.Now) || !(DateTime.Now <= bomRepleaceConditionInfo.ExecuteEndTime))
{
continue;
}
///根据起始生产订单号，获取车辆状态点信息判断其是否在线，若未上线则不执行以下逻辑
PullOrdersInfo pullOrdersInfo = pullOrdersInfos.FirstOrDefault(d => d.OrderNo == bomRepleaceConditionInfo.StartPorderCode);
if (pullOrdersInfo == null || pullOrdersInfo.OrderStatus != (int)OrderStatusConstants.AlreadOnline)
{
continue;
}
///若已上线或已下线则需要根据其获取顺序号之后的所有在线生产订单，依次循环进行逻辑处理 TODO:已下线的逻辑？
///同一起始生产订单号可能出现在多条生产线的状态点上，以下为单生产订单处理逻辑 TODO:多条生产线的逻辑？
///已上线的生产订单：
///本生产订单对应的车辆状态点信息
List <VehiclePointStatusInfo> vehiclePointStatuss = vehiclePointStatusInfos.Where(d => d.OrderNo == pullOrdersInfo.OrderNo).ToList();
if (vehiclePointStatuss.Count == 0)
{
continue;
}
///当前车辆最大状态点信息
VehiclePointStatusInfo vehiclePointStatusInfo = vehiclePointStatuss.Where(d => d.OrderNo == pullOrdersInfo.OrderNo).OrderByDescending(d => d.PassTime).FirstOrDefault();
if (vehiclePointStatusInfo == null)
{
continue;
}
///当前顺序号之后的所有的车辆状态点信息
List <VehiclePointStatusInfo> vehiclePoints = vehiclePointStatusInfos.Where(d => d.SeqNo >= vehiclePointStatusInfo.SeqNo).ToList();
///当前顺序号之后的所有在线生产订单
pullOrdersInfos = (from p in pullOrdersInfos
join v in vehiclePoints
on p.OrderNo equals v.OrderNo
select p).Distinct().ToList();
///依次循环进行逻辑处理
foreach (PullOrdersInfo pullOrder in pullOrdersInfos)
{
///根据生产订单号获取其物料清单，作为后续匹配更改单的源数据
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomDAL().GetList("" +
"and [ORDERFID]='" + pullOrdersInfo.Fid + "'", string.Empty);
if (pullOrderBomInfos.Count == 0)
{
continue;
}
///本生产订单对应的车辆状态点信息
List <VehiclePointStatusInfo> vehicles = vehiclePointStatusInfos.Where(d => d.OrderNo == pullOrdersInfo.OrderNo).ToList();
if (vehicles.Count == 0)
{
continue;
}
///本产生订单对应的所有状态点信息
List <StatusPointInfo> statusPoints = statusPointInfos.Where(d => vehicles.Select(v => v.StatusPointCode).Contains(d.StatusPointCode)).ToList();
if (statusPoints.Count == 0)
{
continue;
}
///已在线的生产订单在此时，需要根据所在状态点位置
VehiclePointStatusInfo vehiclePointStatus = vehiclePointStatusInfos.Where(d => d.OrderNo == pullOrder.OrderNo).OrderByDescending(d => d.PassTime).FirstOrDefault();
if (vehiclePointStatus == null)
{
continue;
}
StatusPointInfo statusPointInfo = statusPointInfos.FirstOrDefault(d => d.StatusPointCode == vehiclePointStatus.StatusPointCode);
if (statusPointInfo == null)
{
continue;
}
///将物料拉动的结果集分为三个部分，其一为未累计、其二为已累计未拉动、其三为已拉动
///该生产订单对应的其后状态点
List <StatusPointInfo> notStatusPoints = statusPoints.Where(d => d.StatusPointSeq > statusPointInfo.StatusPointSeq).ToList();
///该生产订单对应的状态点及之前的状态点
List <StatusPointInfo> yesStatusPoints = statusPoints.Where(d => d.StatusPointSeq <= statusPointInfo.StatusPointSeq).ToList();
///其后的状态点
if (notStatusPoints.Count > 0)
{
///其一为当前状态点位置之后的状态点对应的时间窗（过点累计方式）、排序拉动方式相应的物料拉动信息物料、供应商、工位
///该逻辑获取的数据在此不做后续处理，但此逻辑请事先在程序中实现，将会到离队归队时使用
List <TwdPartBoxInfo> twdPartBoxs = twdPartBoxInfos.Where(d => notStatusPoints.Select(s => s.StatusPointCode).Contains(d.StatusPointCode)).ToList();
List <JisPartBoxInfo> jisPartBoxs = jisPartBoxInfos.Where(d => notStatusPoints.Select(s => s.StatusPointCode).Contains(d.StatusPointCode)).ToList();
///零件类对应的物料拉动信息
if (twdPartBoxs.Count != 0 || jisPartBoxs.Count != 0)
{
///生产订单的产线下的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = maintainInhouseLogisticStandardInfos.
Where(d => d.AssemblyLine == pullOrder.AssemblyLine).Where(d =>
(twdPartBoxs.Select(t => t.PartBoxCode).Contains(d.InhousePartClass)) ||
(jisPartBoxs.Select(j => j.PartBoxCode).Contains(d.InhousePartClass))).ToList();
if (maintainInhouseLogisticStandards.Count != 0)
{
///拉动信息对应的Bom清单
List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d =>
maintainInhouseLogisticStandards.Select(m => m.PartNo).Contains(d.Zcomno) &&
maintainInhouseLogisticStandards.Select(m => m.SupplierNum).Contains(d.SupplierNum)).ToList();
}
}
}
///其二、其三目前没有较理想的方式区分开，暂时以已拉动处理、当前状态点位置之前包括该状态点位置对应的拉动方式相关物料拉动信息
if (yesStatusPoints.Count > 0)
{
List <TwdPartBoxInfo> twdPartBoxs = twdPartBoxInfos.Where(d => yesStatusPoints.Select(s => s.StatusPointCode).Contains(d.StatusPointCode)).ToList();
List <JisPartBoxInfo> jisPartBoxs = jisPartBoxInfos.Where(d => yesStatusPoints.Select(s => s.StatusPointCode).Contains(d.StatusPointCode)).ToList();
///零件类对应的物料拉动信息
if (twdPartBoxs.Count != 0 || jisPartBoxs.Count != 0)
{
///生产订单的产线下的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = maintainInhouseLogisticStandardInfos.
Where(d => d.AssemblyLine == pullOrder.AssemblyLine).Where(d =>
(twdPartBoxs.Select(t => t.PartBoxCode).Contains(d.InhousePartClass)) ||
(jisPartBoxs.Select(j => j.PartBoxCode).Contains(d.InhousePartClass))).ToList();
maintainInhouseLogisticStandards = maintainInhouseLogisticStandards.Where(d => d.PartNo == bomRepleaceConditionInfo.OldPartNo).ToList();
///根据已拉动的物料拉动信息，与替换条件中的旧物料号对比
///若不存在于已拉动物料中，则只需要执行生产订单物料清单替换逻辑即可
///否则需要进行新物料号的自动紧急拉动且生成旧物料号的余料退库单（退库地点为物料拉动信息中的来源库存地点）
///同时也需要执行生产订单物料清单替换逻辑
if (maintainInhouseLogisticStandards.Count > 0)
{
///拉动信息对应的Bom清单
List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d =>
maintainInhouseLogisticStandards.Select(m => m.PartNo).Contains(d.Zcomno) &&
maintainInhouseLogisticStandards.Select(m => m.SupplierNum).Contains(d.SupplierNum)).ToList();
foreach (PullOrderBomInfo pullOrderBom in pullOrderBoms)
{
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandards.FirstOrDefault(d =>
d.PartNo == pullOrderBom.Zcomno && d.SupplierNum == pullOrderBom.SupplierNum);
if (maintainInhouseLogisticStandardInfo == null)
{
continue;
}
///进行新物料号的自动紧急拉动
///生成旧物料号的余料退库单（退库地点为物料拉动信息中的来源库存地点）
}
}
new BomRepleaceConditionBLL().ReplacementCriteria(pullOrder, loginUser);
}
}
}
}
}
/// <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 <StatusPointInfo> statusPointExcelInfos = CommonDAL.DatatableConvertToList <StatusPointInfo>(dataTable).ToList();
if (statusPointExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <StatusPointInfo> statusPointInfos = dal.GetList("[STATUS_POINT_CODE] in ('" + string.Join("','", statusPointExcelInfos.Select(d => d.StatusPointCode).ToArray()) + "') ", string.Empty);
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var statusPointExcelInfo in statusPointExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
StatusPointInfo statusPointInfo = statusPointInfos.FirstOrDefault(d => d.StatusPointCode == statusPointExcelInfo.StatusPointCode);
if (statusPointInfo == null)
{
if (string.IsNullOrEmpty(statusPointExcelInfo.StatusPointCode) ||
string.IsNullOrEmpty(statusPointExcelInfo.StatusPointName) ||
statusPointExcelInfo.StatusPointSeq != null ||
string.IsNullOrEmpty(statusPointExcelInfo.AssemblyLine) ||
string.IsNullOrEmpty(statusPointExcelInfo.Workshop) ||
string.IsNullOrEmpty(statusPointExcelInfo.Plant))
{
throw new Exception("MC:0x00000368");///状态点代码、名称、顺序、工厂、车间、生产线不能为空
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <StatusPointInfo>(statusPointExcelInfo, 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_STATUS_POINT with(nolock) where [STATUS_POINT_CODE] = N'" + statusPointExcelInfo.StatusPointCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_BAS_STATUS_POINT] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
///为防止EXCEL中数据有重复
statusPointInfo = new StatusPointInfo();
statusPointInfo.StatusPointCode = statusPointExcelInfo.StatusPointCode;
statusPointInfos.Add(statusPointInfo);
///
continue;
}
if (string.IsNullOrEmpty(statusPointExcelInfo.StatusPointCode) ||
string.IsNullOrEmpty(statusPointExcelInfo.StatusPointName) ||
statusPointExcelInfo.StatusPointSeq != null ||
string.IsNullOrEmpty(statusPointExcelInfo.AssemblyLine) ||
string.IsNullOrEmpty(statusPointExcelInfo.Workshop) ||
string.IsNullOrEmpty(statusPointExcelInfo.Plant))
{
throw new Exception("MC:0x00000368");///状态点代码、名称、顺序、工厂、车间、生产线不能为空
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <StatusPointInfo>(statusPointExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_STATUS_POINT] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + statusPointInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
throw new Exception("MC:0x00000283");///:没有可导入更新的数据
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
