/// <summary>
/// TwdWindowTimeInfo -> TwdPullOrderInfo
/// </summary>
/// <param name="twdWindowTimeInfo"></param>
/// <param name="info"></param>
public static void GetTwdPullOrderInfo(TwdWindowTimeInfo twdWindowTimeInfo, ref TwdPullOrderInfo info)
{
if (twdWindowTimeInfo == null)
{
return;
}
///PLAN_DELIVERY_TIME,预计到厂时间
info.PlanDeliveryTime = twdWindowTimeInfo.WindowTime;
///TIME_ZONE,时区
info.TimeZone = twdWindowTimeInfo.TimeZone;
}
/// <summary>
/// LogicDeleteInfo
/// </summary>
/// <param name="id"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool LogicDeleteInfo(long id, string loginUser)
{
TwdWindowTimeInfo info = dal.GetInfo(id);
if (info == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
if (info.SendTimeStatus != (int)SendTimeStatusConstants.NoSend)
{
throw new Exception("MC:0x00000668");///发单状态为未发单才允许被删除
}
return(dal.LogicDelete(id, loginUser) > 0 ? true : false);
}
/// <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>
/// 执行导入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="twdPartBoxInfo"></param>
/// <param name="twdWindowTimeInfo"></param>
/// <param name="supplierInfo"></param>
/// <param name="twdCounterInfos"></param>
/// <param name="maintainInhouseLogisticStandardInfos"></param>
/// <returns></returns>
private string CreateTwdPullOrder(List <TwdCounterInfo> twdCounterInfos, TwdPartBoxInfo twdPartBoxInfo, List <SupplierInfo> supplierInfos, TwdWindowTimeInfo twdWindowTimeInfo,
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos)
{
///
StringBuilder @string = new StringBuilder();
///Create TwdPullOrderInfo
TwdPullOrderInfo twdPullOrderInfo = null;
///Create PcsPullOrderInfo
PcsPullOrderInfo pcsPullOrderInfo = null;
///仓储衔接主表
MaterialPullingOrderInfo materialPulling = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() == (int)TwdPullModeConstants.Pcs)
{
pcsPullOrderInfo = PcsPullOrderBLL.CreatePcsPullOrderInfo(loginUser);
///TwdWindowTimeInfo -> PcsPullOrderInfo
PcsPullOrderBLL.GetPcsPullOrderInfo(twdWindowTimeInfo, ref pcsPullOrderInfo);
///TwdPartBoxInfo -> PcsPullOrderInfo
PcsPullOrderBLL.GetPcsPullOrderInfo(twdPartBoxInfo, ref pcsPullOrderInfo);
///SupplierInfo -> PcsPullOrderInfo
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == twdPartBoxInfo.SupplierNum);
PcsPullOrderBLL.GetPcsPullOrderInfo(supplierInfo, ref pcsPullOrderInfo);
///
@string.AppendLine(PcsPullOrderDAL.GetInsertSql(pcsPullOrderInfo));
///PcsPullOrderInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(pcsPullOrderInfo, ref materialPulling);
}
else
{
twdPullOrderInfo = TwdPullOrderBLL.CreateTwdPullOrderInfo(loginUser);
///TwdWindowTimeInfo -> TwdPullOrderInfo
TwdPullOrderBLL.GetTwdPullOrderInfo(twdWindowTimeInfo, ref twdPullOrderInfo);
///TwdPartBoxInfo -> TwdPullOrderInfo
TwdPullOrderBLL.GetTwdPullOrderInfo(twdPartBoxInfo, ref twdPullOrderInfo);
///SupplierInfo -> TwdPullOrderInfo
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == twdPartBoxInfo.SupplierNum);
TwdPullOrderBLL.GetTwdPullOrderInfo(supplierInfo, ref twdPullOrderInfo);
///
@string.AppendLine(TwdPullOrderDAL.GetInsertSql(twdPullOrderInfo));
///TwdPullOrderInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(twdPullOrderInfo, ref materialPulling);
}
///行号
int rowNo = 0;
///循环计数器
foreach (TwdCounterInfo twdCounterInfo in twdCounterInfos)
{
///物料需求数量
decimal requiredPartQty = 0;
///物料需求箱数
int packageQty = 0;
#region 圆整方式
///向上圆整
if (twdCounterInfo.RoundnessMode == (int)RoundnessModeConstants.Upward)
{
if (twdCounterInfo.Package.GetValueOrDefault() > 0)
{
///根据计数器中的圆整方式⑨，当圆整方式⑨为20.向上时以Math.Ceiling(当前计数⑮/箱内数量⑯)*箱内数量⑯作为本次的物料需求数量
requiredPartQty = Math.Ceiling(twdCounterInfo.CurrentQty.GetValueOrDefault() / twdCounterInfo.Package.GetValueOrDefault()) * twdCounterInfo.Package.GetValueOrDefault();
packageQty = Convert.ToInt32(Math.Ceiling(requiredPartQty / twdCounterInfo.Package.GetValueOrDefault()));
}
}
///向下圆整
if (twdCounterInfo.RoundnessMode == (int)RoundnessModeConstants.Downward)
{
if (twdCounterInfo.Package.GetValueOrDefault() > 0)
{
///根据计数器中的圆整方式⑨，当圆整方式⑨为20.向上时以Math.Ceiling(当前计数⑮/箱内数量⑯)*箱内数量⑯作为本次的物料需求数量
requiredPartQty = Math.Floor(twdCounterInfo.CurrentQty.GetValueOrDefault() / twdCounterInfo.Package.GetValueOrDefault()) * twdCounterInfo.Package.GetValueOrDefault();
packageQty = Convert.ToInt32(Math.Ceiling(requiredPartQty / twdCounterInfo.Package.GetValueOrDefault()));
}
}
///按需求数量
if (twdCounterInfo.RoundnessMode == (int)RoundnessModeConstants.Ondemand)
{
///当圆整方式⑨为按需时则当前计数⑮直接作为本次的物料需求数量
requiredPartQty = twdCounterInfo.CurrentQty.GetValueOrDefault();
packageQty = Convert.ToInt32(Math.Ceiling(requiredPartQty / twdCounterInfo.Package.GetValueOrDefault()));
}
#endregion
///获取物料拉动信息
MaintainInhouseLogisticStandardInfo logisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == twdCounterInfo.PartPullFid.GetValueOrDefault());
if (logisticStandardInfo == null)
{
continue;
}
///若计数器对应的物料拉动信息中最小起订包装数为一个大于零的数字，则需要在此处比对物料需求箱数是否大于等于最小起订包装数，若不满足则不产生拉动
if (logisticStandardInfo.MinPullBox.GetValueOrDefault() > 0 && packageQty < logisticStandardInfo.MinPullBox.GetValueOrDefault())
{
continue;
}
///若计数器对应的物料拉动信息中批量包装数为一个大于零的数字，则需要Math.Floor(物料需求箱数 / 批量包装数) * 批量包装数，作为新的物料需求箱数，同时* 箱内数量⑯获得新的物料需求数量
if (logisticStandardInfo.BatchPullBox.GetValueOrDefault() > 0 && twdCounterInfo.Package.GetValueOrDefault() > 0)
{
packageQty = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(packageQty) / logisticStandardInfo.BatchPullBox.GetValueOrDefault())) * logisticStandardInfo.BatchPullBox.GetValueOrDefault();
requiredPartQty = packageQty * twdCounterInfo.Package.GetValueOrDefault();
}
///仓储衔接明细表
MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() == (int)TwdPullModeConstants.Pcs)
{
///
PcsPullOrderDetailInfo pullOrderDetailInfo = PcsPullOrderDetailBLL.CreatePcsPullOrderDetailInfo(loginUser);
///MaintainInhouseLogisticStandardInfo -> TwdPullOrderDetailInfo
PcsPullOrderDetailBLL.GetPcsPullOrderDetailInfo(logisticStandardInfo, ref pullOrderDetailInfo);
///TwdPullOrderInfo -> TwdPullOrderDetailInfo
PcsPullOrderDetailBLL.GetPcsPullOrderDetailInfo(pcsPullOrderInfo, ref pullOrderDetailInfo);
///ROW_NO,行号
pullOrderDetailInfo.RowNo = ++rowNo;
///REQUIRED_PACKAGE_QTY,需求包装数
pullOrderDetailInfo.RequiredPackageQty = packageQty;
///REQUIRED_PART_QTY,需求物料数量
pullOrderDetailInfo.RequiredPartQty = requiredPartQty;
///
@string.AppendLine(PcsPullOrderDetailDAL.GetInsertSql(pullOrderDetailInfo));
///TwdPullOrderDetailInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(pullOrderDetailInfo, ref detailInfo);
}
else
{
///
TwdPullOrderDetailInfo pullOrderDetailInfo = TwdPullOrderDetailBLL.CreateTwdPullOrderDetailInfo(loginUser);
///MaintainInhouseLogisticStandardInfo -> TwdPullOrderDetailInfo
TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(logisticStandardInfo, ref pullOrderDetailInfo);
///TwdPullOrderInfo -> TwdPullOrderDetailInfo
TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(twdPullOrderInfo, ref pullOrderDetailInfo);
///ROW_NO,行号
pullOrderDetailInfo.RowNo = ++rowNo;
///REQUIRED_PACKAGE_QTY,需求包装数
pullOrderDetailInfo.RequiredPackageQty = packageQty;
///REQUIRED_PART_QTY,需求物料数量
pullOrderDetailInfo.RequiredPartQty = requiredPartQty;
///
@string.AppendLine(TwdPullOrderDetailDAL.GetInsertSql(pullOrderDetailInfo));
///TwdPullOrderDetailInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(pullOrderDetailInfo, ref detailInfo);
}
///
materialPulling.MaterialPullingOrderDetailInfos.Add(detailInfo);
#region 扣减计数器
///最后根据物料需求数量扣减对应的计数器的当前计数⑮
@string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] " +
"set [CURRENT_QTY] = isnull([CURRENT_QTY],0) - " + requiredPartQty + "," +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "' " +
"where [ID]= " + twdCounterInfo.Id + ";");
///
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///TwdCounterInfo -> TwdCounterLogInfo
TwdCounterLogBLL.GetTwdCounterLogInfo(twdCounterInfo, ref twdCounterLogInfo);
///PART_QTY,物料数量
twdCounterLogInfo.PartQty = 0 - requiredPartQty;
///SOURCE_DATA,目视来源数据
twdCounterLogInfo.SourceData = twdPullOrderInfo.OrderCode;
///SOURCE_DATA_FID,数据来源外键
twdCounterLogInfo.SourceDataFid = twdPullOrderInfo.Fid;
///SOURCE_DATA_TYPE,数据来源类型
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.CreateRunsheet;
///
@string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
#endregion
}
///更新时间窗窗口时间为已发单状态
@string.AppendLine("update [LES].[TT_MPM_TWD_WINDOW_TIME] " +
"set [SEND_TIME_STATUS]= " + (int)SendTimeStatusConstants.Sent + "," +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "' " +
"where [ID]= " + twdWindowTimeInfo.Id + ";");
///拉动单生成后需要调用拉动仓储衔接函数获取语句
@string.AppendLine(MaterialPullingCommonBLL.Handler(materialPulling, loginUser));
return(@string.ToString());
}