/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(TwdWindowTimeInfo info)
{
if (string.IsNullOrEmpty(info.PartBoxCode))
{
throw new Exception("MC:0x00000298");///零件类代码不可为空
}
if (string.IsNullOrEmpty(info.PartBoxName))
{
throw new Exception("MC:0x00000299");///零件类名称不可为空
}
///窗口时间
DateTime windowTime = info.WindowTime.GetValueOrDefault();
DateTime workDay = info.WorkDay.GetValueOrDefault();
info.WindowTime = new DateTime(workDay.Year, workDay.Month, workDay.Day, windowTime.Hour, windowTime.Minute, 0);
int cnt = dal.GetCounts("[PART_BOX_CODE] = N'" + info.PartBoxCode + "' and [WINDOW_TIME] = N'" + info.WindowTime.GetValueOrDefault() + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000313");///相同零件类代码②、工作日⑧、窗口时间⑩的数据不允许重复
}
TwdPartBoxInfo twdPartBoxInfo = new TwdPartBoxDAL().GetInfo(info.PartBoxCode);
if (twdPartBoxInfo == null)
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
info.PartBoxFid = twdPartBoxInfo.Fid;
if (info.WindowTime == null)
{
throw new Exception("MC:0x00000508");///窗口时间不允许为空
}
if (info.WorkDay == null)
{
throw new Exception("MC:0x00000509");///工作日不允许为空
}
///发单时间 = 工作日年月日 + 窗口时间时分秒 - 提前时间
int advanceTime = twdPartBoxInfo.RequirementAccumulateTime.GetValueOrDefault() + ///累积时间
twdPartBoxInfo.LoadTime.GetValueOrDefault() + ///装货时间
twdPartBoxInfo.TransportTime.GetValueOrDefault() + ///运输时间
twdPartBoxInfo.UnloadTime.GetValueOrDefault(); ///卸货时间
info.SendTime = info.WindowTime.GetValueOrDefault().AddMinutes(0 - advanceTime);
return(dal.Add(info));
}
/// <summary>
/// UpdateInfo
/// </summary>
/// <param name="fields"></param>
/// <param name="id"></param>
/// <returns></returns>
public bool UpdateInfo(string fields, long id)
{
TwdWindowTimeInfo info = dal.GetInfo(id);
if (info == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
if (info.SendTimeStatus.GetValueOrDefault() != (int)SendTimeStatusConstants.NoSend)
{
throw new Exception("MC:0x00000311");///发单状态为未发单才允许被修改
}
string partBoxCode = CommonBLL.GetFieldValue(fields, "PART_BOX_CODE");
string workDay = CommonBLL.GetFieldValue(fields, "WORK_DAY");
string windowTime = CommonBLL.GetFieldValue(fields, "WINDOW_TIME");
///窗口时间
DateTime dateWindowTime = CommonBLL.TryParseDatetime(windowTime, "yyyy-MM-dd HH:mm:ss");
DateTime dateWorkDay = CommonBLL.TryParseDatetime(workDay, "yyyy-MM-dd HH:mm:ss");
dateWindowTime = new DateTime(dateWorkDay.Year, dateWorkDay.Month, dateWorkDay.Day, dateWindowTime.Hour, dateWindowTime.Minute, 0);
int cnt = dal.GetCounts("[PART_BOX_CODE] = N'" + info.PartBoxCode + "' and [WINDOW_TIME] = N'" + info.WindowTime.GetValueOrDefault() + "' and [ID] <> " + id + "");
if (cnt > 0)
{
throw new Exception("MC:0x00000313");///相同零件类代码②、工作日⑧、窗口时间⑩的数据不允许重复
}
fields = CommonBLL.SetFieldValue(fields, "WINDOW_TIME", dateWindowTime.ToString("yyyy-MM-dd HH:mm:ss"));
TwdPartBoxInfo twdPartBoxInfo = new TwdPartBoxDAL().GetInfo(partBoxCode);
if (twdPartBoxInfo == null)
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
///发单时间 = 工作日年月日 + 窗口时间时分秒 - 提前时间
int advanceTime = twdPartBoxInfo.RequirementAccumulateTime.GetValueOrDefault() + ///累积时间
twdPartBoxInfo.LoadTime.GetValueOrDefault() + ///装货时间
twdPartBoxInfo.TransportTime.GetValueOrDefault() + ///运输时间
twdPartBoxInfo.UnloadTime.GetValueOrDefault(); ///卸货时间
DateTime sendTime = dateWindowTime.AddMinutes(0 - advanceTime);
fields = CommonBLL.SetFieldValue(fields, "SEND_TIME", sendTime.ToString("yyyy-MM-dd HH:mm:ss"));
return(dal.UpdateInfo(fields, id) > 0 ? true : false);
}
/// <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)
{
DateTime dtDate;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (!DateTime.TryParse(dataTable.Rows[i]["WorkDay"].ToString(), out dtDate))
{
throw new Exception("MC:0x00000393");///日期格式不正确
}
if (!DateTime.TryParse(dataTable.Rows[i]["WindowTime"].ToString(), out dtDate))
{
throw new Exception("MC:0x00000393");///日期格式不正确
}
}
///导入数据
List <TwdWindowTimeInfo> twdWindows = CommonDAL.DatatableConvertToList <TwdWindowTimeInfo>(dataTable).ToList();
if (twdWindows.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///比对数据
List <TwdWindowTimeInfo> timeInfos = dal.GetList("[PART_BOX_CODE] in ('" + string.Join("','", twdWindows.Select(d => d.PartBoxCode).ToArray()) + "') and [WORK_DAY] in ('" + string.Join("','", twdWindows.Select(d => d.WorkDay).ToArray()) + "') and [WINDOW_TIME] in ('" + string.Join("','", twdWindows.Select(d => d.WindowTime).ToArray()) + "')", string.Empty).ToList();
///零件类数据
List <TwdPartBoxInfo> twdParts = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("','", twdWindows.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty).ToList();
if (twdParts.Count() != twdWindows.Count())
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
List <string> fields = new List <string>(fieldNames.Keys);
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in twdWindows)
{
TwdWindowTimeInfo timeInfo = timeInfos.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode && d.WorkDay == item.WorkDay && d.WindowTime == item.WindowTime);
TwdPartBoxInfo boxInfo = twdParts.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode);
///发单时间 = 工作日年月日 + 窗口时间时分秒 - 提前时间
int advanceTime = boxInfo.RequirementAccumulateTime.GetValueOrDefault() + ///需求累积时间
boxInfo.LoadTime.GetValueOrDefault() + ///装货时间
boxInfo.TransportTime.GetValueOrDefault() + ///运输时间
boxInfo.UnloadTime.GetValueOrDefault(); ///卸货时间
item.SendTime = item.WindowTime.GetValueOrDefault().AddMinutes(-advanceTime);
if (timeInfo == null)
{
if (item.WindowTime == null || item.WorkDay == null || item.PartBoxCode == null)
{
throw new Exception("MC:0x00000510");///零件类工作日窗口时间不可为空
}
stringBuilder.Append("insert into [LES].[TT_MPM_TWD_WINDOW_TIME]([FID],[PART_BOX_FID],[PART_BOX_CODE],[PART_BOX_NAME],[PLANT],[WORKSHOP],[ASSEMBLY_LINE],[SUPPLIER_NUM],[WORK_DAY],[SEND_TIME],[WINDOW_TIME],[SEND_TIME_STATUS],[TIME_ZONE],[COMMENTS],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])values(");
stringBuilder.Append("newid(),N'" + boxInfo.Fid + "',N'" + item.PartBoxCode + "',N'" + boxInfo.PartBoxName + "',N'" + boxInfo.Plant + "',N'" + boxInfo.Workshop + "',N'" + boxInfo.AssemblyLine + "',N'" + boxInfo.SupplierNum + "',N'" + item.WorkDay + "',N'" + item.SendTime + "',N'" + item.WindowTime + "'," + (int)SendTimeStatusConstants.NoSend + ",N'" + item.TimeZone + "',N'" + item.Comments + "',1,GETDATE(),N'" + loginUser + "'");
stringBuilder.Append(");");
timeInfos.Add(item);
continue;
}
if (timeInfo.SendTimeStatus != (int)SendTimeStatusConstants.NoSend)
{
throw new Exception("MC:0x00000311");///发单状态⑪为10未发单才允许被修改
}
if (item.WindowTime == null || item.WorkDay == null || item.PartBoxCode == null)
{
throw new Exception("MC:0x00000510");///零件类工作日窗口时间不可为空
}
stringBuilder.Append("update [LES].[TT_MPM_TWD_WINDOW_TIME] set [PLANT] = N'" + boxInfo.Plant + "',[WORKSHOP] = N'" + boxInfo.Workshop + "',[ASSEMBLY_LINE] = N'" + boxInfo.AssemblyLine + "',[SUPPLIER_NUM] = N'" + boxInfo.SupplierNum + "',[SEND_TIME] = N'" + item.SendTime + "',[COMMENTS] = N'" + item.Comments + "',[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] = " + timeInfo.Id + ";");
}
if (string.IsNullOrEmpty(stringBuilder.ToString()))
{
throw new Exception("MC:0x00000283");///没有可导入更新的数据
}
return(CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString()));
}
/// <summary>
/// 获取层级拉动需求累计语句
/// </summary>
/// <param name="maintainInhouseLogisticStandardInfo"></param>
/// <param name="requireQty"></param>
/// <param name="loginUser"></param>
/// <param name="counterFid"></param>
/// <param name="partBoxCode"></param>
/// <returns></returns>
public static string LevelPullRequirementCounter(MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo, decimal requireQty, string loginUser, Guid counterFid, string partBoxCode)
{
///当物料拉动信息中的是否层级拉动标记 =False或未设置时函数直接返回空
if (!maintainInhouseLogisticStandardInfo.IsTriggerPull.GetValueOrDefault())
{
return(string.Empty);
}
///根据物料拉动信息中的层级拉动仓库存储区以及物料号、供应商获取物料拉动信息表中目标仓库存储区对应的物料号、供应商数据
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("" +
"[T_WM_NO] = N'" + maintainInhouseLogisticStandardInfo.WmNo + "' and " +
"[T_ZONE_NO] = N'" + maintainInhouseLogisticStandardInfo.ZoneNo + "' and " +
"[PART_NO] = N'" + maintainInhouseLogisticStandardInfo.PartNo + "' and " +
"[STATUS] = " + (int)BasicDataStatusConstants.Enable + " and " +
"[INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Twd + "'", string.Empty);
///若获取不成功则将供应商条件去除重新获取，此处在程序执行时可以先根据物料号获取，再过滤供应商
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandard = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.SupplierNum == maintainInhouseLogisticStandardInfo.SupplierNum);
if (maintainInhouseLogisticStandard == null)
{
maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault();
}
///未能成功获取物料拉动信息
if (maintainInhouseLogisticStandard == null)
{
return(string.Empty);
}
///获取零件类信息
TwdPartBoxInfo twdPartBoxInfo = new TwdPartBoxDAL().GetInfo(maintainInhouseLogisticStandard.InhousePartClass);
///未能成功获取零件类信息
if (twdPartBoxInfo == null)
{
return(string.Empty);
}
///零件类未启用
if (twdPartBoxInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
return(string.Empty);
}
///根据物料拉动信息外键获取计数器，未能成功获取时需要创建
TwdCounterInfo twdCounterInfo = new TwdCounterDAL().GetInfoByPartPullFid(maintainInhouseLogisticStandard.Fid.GetValueOrDefault());
if (twdCounterInfo == null)
{
///创建计数器
twdCounterInfo = CreateTwdCounterInfo(loginUser);
///以物料拉动信息填充计数器
GetTwdCounterInfo(maintainInhouseLogisticStandard, ref twdCounterInfo);
///以零件类信息填充计数器
GetTwdCounterInfo(twdPartBoxInfo, ref twdCounterInfo);
///
twdCounterInfo.Id = new TwdCounterDAL().Add(twdCounterInfo);
if (twdCounterInfo.Id == 0)
{
throw new Exception("MC:0x00000453");///时间窗计数器创建失败
}
}
///计数器状态未处于启用
if (twdCounterInfo.Status != (int)BasicDataStatusConstants.Enable)
{
return(string.Empty);
}
///
StringBuilder stringBuilder = new StringBuilder();
///
stringBuilder.AppendLine(UpdateTwdCounter(maintainInhouseLogisticStandard, twdPartBoxInfo, requireQty, twdCounterInfo.Id, loginUser));
///创建计数器日志
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以物料拉动信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandard, ref twdCounterLogInfo);
///以零件类信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo);
///PART_QTY
twdCounterLogInfo.PartQty = requireQty;
///SOURCE_DATA_FID
twdCounterLogInfo.SourceDataFid = counterFid;
///SOURCE_DATA_TYPE
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Calculator;
///SOURCE_DATA
twdCounterLogInfo.SourceData = partBoxCode;
///
stringBuilder.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
///触发层级拉动
stringBuilder.AppendLine(LevelPullRequirementCounter(
maintainInhouseLogisticStandard,
requireQty,
loginUser,
twdCounterInfo.Fid.GetValueOrDefault(),
twdCounterInfo.PartBoxCode));
return(stringBuilder.ToString());
}
/// <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 <TwdCounterInfo> twdCounterExcelInfos = CommonDAL.DatatableConvertToList <TwdCounterInfo>(dataTable).ToList();
if (twdCounterExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
List <TwdCounterInfo> twdCounterInfos = dal.GetList("" +
"[PART_BOX_CODE] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " +
"[PART_NO] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartNo).ToArray()) + "') and " +
"[PACKAGE_MODEL] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PackageModel).ToArray()) + "')", string.Empty);
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("" +
"[INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Twd + "' and " +
"[INHOUSE_PART_CLASS] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " +
"[PART_NO] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
///获取零件类信息
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("" +
"[PART_BOX_CODE] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty);
StringBuilder @string = new StringBuilder();
foreach (TwdCounterInfo twdCounterExcelInfo in twdCounterExcelInfos)
{
/// 零件类代码②、工厂③车间⑤生产线⑥工段⑬工位⑭、物料号⑩、物料版本⑫、包装容器⑰为联合主键，差异数量DiffQty作为累加当前计数⑮的依据，备注直接更新
TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine &&
d.WorkshopSection == twdCounterExcelInfo.WorkshopSection &&
d.Location == twdCounterExcelInfo.Location &&
d.PartVersion == twdCounterExcelInfo.PartVersion);
#region 逐步减低维度处理
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine &&
d.WorkshopSection == twdCounterExcelInfo.WorkshopSection &&
d.Location == twdCounterExcelInfo.Location);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine &&
d.WorkshopSection == twdCounterExcelInfo.WorkshopSection);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo);
}
if (twdCounterInfo == null)
{
throw new Exception("MC:0x00000255");///数据格式不符合导入规范
}
#endregion
if (twdCounterInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled)
{
throw new Exception("MC:0x00000455");///计数器已作废不能修改数量
}
@string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] set " +
"[CURRENT_QTY] = isnull([CURRENT_QTY],0) + " + twdCounterExcelInfo.DiffQty + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = " + twdCounterInfo.Id + ";");
///根据计数器的物料拉动信息外键获取物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == twdCounterInfo.PartPullFid.GetValueOrDefault());
if (maintainInhouseLogisticStandardInfo == null)
{
throw new Exception("MC:0x00000213");///物料拉动信息数据错误
}
if (maintainInhouseLogisticStandardInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
throw new Exception("MC:0x00000233");///没有已启用的物料拉动信息
}
///获取零件类信息
TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass);
///未能成功获取零件类信息
if (twdPartBoxInfo == null)
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
///零件类未启用
if (twdPartBoxInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
throw new Exception("MC:0x00000456");///零件类未启用
}
///创建计数器日志
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以物料拉动信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandardInfo, ref twdCounterLogInfo);
///以零件类信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo);
///PART_QTY
twdCounterLogInfo.PartQty = twdCounterExcelInfo.DiffQty;
///SOURCE_DATA_FID
twdCounterLogInfo.SourceDataFid = twdCounterInfo.Fid;
///SOURCE_DATA_TYPE
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Manual;
///SOURCE_DATA
twdCounterLogInfo.SourceData = twdCounterInfo.PartBoxCode;
///Comments
twdCounterLogInfo.Comments = twdCounterExcelInfo.Comments;
///
@string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
///触发层级拉动
@string.AppendLine(LevelPullRequirementCounter(
maintainInhouseLogisticStandardInfo,
twdCounterExcelInfo.DiffQty,
loginUser,
twdCounterInfo.Fid.GetValueOrDefault(),
twdCounterInfo.PartBoxCode));
}
///执行
using (var trans = new TransactionScope())
{
if (@string.Length == 0)
{
throw new Exception("MC:0x00000283");///没有可导入更新的数据
}
CommonDAL.ExecuteNonQueryBySql(@string.ToString());
trans.Complete();
}
///
return(true);
}
/// <summary>
/// 提交
/// </summary>
/// <param name="info"></param>
/// <param name="rowsKeyValues"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool EntitySubmitInfos(TwdCounterUpdateInfo info, List <string> rowsKeyValues, string loginUser)
{
List <TwdCounterInfo> twdCounterInfos = new TwdCounterDAL().GetList("[ID] in (" + string.Join(",", rowsKeyValues.ToArray()) + ")", string.Empty);
if (twdCounterInfos.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
string sql = string.Empty;
foreach (var twdCounterInfo in twdCounterInfos)
{
if (twdCounterInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled)
{
throw new Exception("MC:0x00000455");///计数器已作废不能修改数量
}
sql += "update [LES].[TT_MPM_TWD_COUNTER] set " +
"[CURRENT_QTY] = isnull([CURRENT_QTY],0) +" + info.SubmitQty.GetValueOrDefault() + "," +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "' where " +
"[ID] = " + twdCounterInfo.Id + ";";
///根据计数器的物料拉动信息外键获取物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = new MaintainInhouseLogisticStandardDAL().GetInfoByFid(twdCounterInfo.PartPullFid.GetValueOrDefault());
if (maintainInhouseLogisticStandardInfo == null)
{
throw new Exception("MC:0x00000213");///物料拉动信息数据错误
}
if (maintainInhouseLogisticStandardInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
throw new Exception("MC:0x00000233");///没有已启用的物料拉动信息
}
///获取零件类信息
TwdPartBoxInfo twdPartBoxInfo = new TwdPartBoxDAL().GetInfo(maintainInhouseLogisticStandardInfo.InhousePartClass);
///未能成功获取零件类信息
if (twdPartBoxInfo == null)
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
///零件类未启用
if (twdPartBoxInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
throw new Exception("MC:0x00000456");///零件类未启用
}
///创建计数器日志
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以物料拉动信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandardInfo, ref twdCounterLogInfo);
///以零件类信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo);
///PART_QTY
twdCounterLogInfo.PartQty = info.SubmitQty.GetValueOrDefault();
///SOURCE_DATA_FID
twdCounterLogInfo.SourceDataFid = twdCounterInfo.Fid;
///SOURCE_DATA_TYPE
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Manual;
///SOURCE_DATA
twdCounterLogInfo.SourceData = twdCounterInfo.PartBoxCode;
///Comments
twdCounterLogInfo.Comments = info.Comments;
///
sql += TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo);
///触发层级拉动
sql += TwdCounterBLL.LevelPullRequirementCounter(
maintainInhouseLogisticStandardInfo,
info.SubmitQty.GetValueOrDefault(),
loginUser,
twdCounterInfo.Fid.GetValueOrDefault(),
twdCounterInfo.PartBoxCode);
}
///
using (var trans = new TransactionScope())
{
if (!string.IsNullOrEmpty(sql))
{
CommonDAL.ExecuteNonQueryBySql(sql);
}
trans.Complete();
}
///
return(true);
}
/// <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 <TwdPartBoxInfo> twdPartBoxExcelInfos = CommonDAL.DatatableConvertToList <TwdPartBoxInfo>(dataTable).ToList();
if (twdPartBoxExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("', '", twdPartBoxExcelInfos.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", "");
///执行的SQL语句
StringBuilder @string = new StringBuilder();
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var twdPartBoxExcelInfo in twdPartBoxExcelInfos)
{
TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == twdPartBoxExcelInfo.PartBoxCode);
///需要新增
if (twdPartBoxInfo == null)
{
///校验
ValidTwdPartBoxInfo(twdPartBoxExcelInfo);
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
@string.AppendLine("if not exists (select * from LES.TM_MPM_TWD_PART_BOX with(nolock) " +
"where [PART_BOX_CODE] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_MPM_TWD_PART_BOX] ("
+ "[FID],"
+ insertFieldString
+ "[STATUS],"
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID(),"///FID
+ insertValueString
+ (int)BasicDataStatusConstants.Created + ","
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");");
///防止EXCEL中有重复项
twdPartBoxInfos.Add(twdPartBoxExcelInfo);
continue;
}
///
if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled)
{
continue;
}
///
twdPartBoxExcelInfo.Id = twdPartBoxInfo.Id;
///校验
ValidTwdPartBoxInfo(twdPartBoxExcelInfo);
///是否更新了拉动模式,需要同步更新物料拉动信息中的拉动模式
if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() != twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault())
{
@string.AppendLine("update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " +
"set [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault() + "'," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxInfo.TwdPullMode.GetValueOrDefault() + "' and " +
"[INHOUSE_PART_CLASS] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and " +
"[VALID_FLAG] = 1;");
}
///已创建状态的数据可以全信息更新
if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Created)
{
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
@string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + twdPartBoxInfo.Id + ";");
continue;
}
///
@string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] " +
"set [PART_BOX_NAME] = N'" + twdPartBoxExcelInfo.PartBoxName + "'," +
"[REQUIREMENT_ACCUMULATE_TIME] = " + (twdPartBoxExcelInfo.RequirementAccumulateTime == null ? "NULL" : "" + twdPartBoxExcelInfo.RequirementAccumulateTime.GetValueOrDefault() + "") + "," +
"[LOAD_TIME] = " + (twdPartBoxExcelInfo.LoadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.LoadTime.GetValueOrDefault() + "") + "," +
"[TRANSPORT_TIME] = " + (twdPartBoxExcelInfo.TransportTime == null ? "NULL" : "" + twdPartBoxExcelInfo.TransportTime.GetValueOrDefault() + "") + "," +
"[UNLOAD_TIME] = " + (twdPartBoxExcelInfo.UnloadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.UnloadTime.GetValueOrDefault() + "") + "," +
"[DELAY_TIME] = " + (twdPartBoxExcelInfo.DelayTime == null ? "NULL" : "" + twdPartBoxExcelInfo.DelayTime.GetValueOrDefault() + "") + "," +
"[ONLINE_TIME] = " + (twdPartBoxExcelInfo.OnlineTime == null ? "NULL" : "" + twdPartBoxExcelInfo.OnlineTime.GetValueOrDefault() + "") + "," +
"[REQUIREMENT_ACCUMULATE_MODE] = " + twdPartBoxExcelInfo.RequirementAccumulateMode.GetValueOrDefault() + "," +
"[STATUS_POINT_CODE] = N'" + twdPartBoxExcelInfo.StatusPointCode + "'," +
"[ROUNDNESS_MODE] = " + twdPartBoxExcelInfo.RoundnessMode.GetValueOrDefault() + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + twdPartBoxInfo.Id + ";");
}
///执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
return(true);
}
