/// <summary>
/// 看板生成拉动单
/// </summary>
/// <param name="cardNos"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool CreateKanbanPullOrder(List <String> cardNos, string loginUser, bool isFilterLoginUser = false)
{
if (cardNos.Count == 0)
{
throw new Exception("MC:3x00000011");///看板卡数据不能为空
}
#region 初始化SQL脚本
///主表字符串
string sqlKanbanPullOrder = @"insert into [LES].[TT_MPM_KANBAN_PULL_ORDER] (
FID,
ORDER_CODE,
PART_BOX_CODE,
PART_BOX_NAME,
ROUTE_CODE,
ROUTE_NAME,
SOURCE_ZONE_NO,
SOURCE_WM_NO,
TARGET_ZONE_NO,
TARGET_WM_NO,
PICK_UP_FINISH_TIME,
PICK_UP_USER,
DELIVERY_FINISH_TIME,
DELIVERY_USER,
PRINT_CNT,
PRINT_TIME,
PRINT_USER,
STATUS,
VALID_FLAG,
CREATE_DATE,
CREATE_USER) VALUES ({0});" ;
///明细表字符串
string sqlKanbanPullOrderDetail = @"insert into [LES].[TT_MPM_KANBAN_PULL_ORDER_DETAIL] (
FID,
ORDER_FID,
ORDER_CODE,
ORDER_STATUS,
CARD_NO,
PART_NO,
PART_NAME,
SUPPLIER_CODE,
SUPPLIER_NAME,
PART_QTY,
PACKAGE_CODE,
PACKAGE_QTY,
VALID_FLAG,
CREATE_DATE,
CREATE_USER) VALUES ({0});" ;
#endregion
///获取所有看板卡信息
List <KanbanCardInfo> kanbanCardInfos = GetKanBanCardsListIsByLoginUser(cardNos, loginUser, isFilterLoginUser);
if (kanbanCardInfos.Count == 0)
{
throw new Exception("MC:3x00000011");///看板卡数据不能为空
}
if (cardNos.Count != kanbanCardInfos.Count)
{
throw new Exception("MC:0x00000257");///所提交的看板卡状态必须为已启用且不能为已扫描
}
///根据看板卡信息中的零件类代码②对物料需求进行分组
var groupbyPartboxcode = kanbanCardInfos.GroupBy(ml => new { ml.PartBoxCode }).Select(w => w.Key).ToList();
#region 根据零件类获取看板拉动物料拉动对应拉动零件
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos
= new MaintainInhouseLogisticStandardBLL().GetList(
"[INHOUSE_PART_CLASS] in ('"
+ string.Join("','", groupbyPartboxcode.Select(w => w.PartBoxCode))
+ "') and [STATUS] = " + (int)BasicDataStatusConstants.Enable
+ " AND [INHOUSE_SYSTEM_MODE]=N'"
+ (int)PullModeConstants.Kanban
+ "'", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
throw new Exception("0x00000233");///没有已启用的物料拉动信息
}
#endregion
#region 获取所有看板零件类以分组方式去重
List <KanbanPartBoxInfo> kanbanPartBoxInfos = new KanbanPartBoxDAL().GetList(
string.Format("[STATUS] = "
+ (int)BasicDataStatusConstants.Enable
+ " and [PART_BOX_CODE] in ('{0}') ", string.Join("','", groupbyPartboxcode.Select(w => w.PartBoxCode))), string.Empty);
if (kanbanPartBoxInfos.Count == 0)
{
throw new Exception("MC:3x00000014");///看板零件类数据错误
}
#endregion
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("update [LES].[TM_MPM_KANBAN_CARD] " +
"set [USED_STATUS] = " + (int)KanbanCardUseStatusConstants.Scaned + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] in (" + string.Join(",", kanbanCardInfos.Select(d => d.Id).ToArray()) + ");");
#region 遍历看板卡信息
foreach (var partboxcode in groupbyPartboxcode)
{
///获取看板零件类信息
KanbanPartBoxInfo kanbanPartBoxInfo = kanbanPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == partboxcode.PartBoxCode);
///TT_MPM_KANBAN_PULL_ORDER.FID
Guid pullOrderFid = Guid.NewGuid();
///看板拉动单号②依据序列号规则中的KANBAN_PULL_ORDER_CODE进行生成，明细中拉动单号③相同于主表
string orderCode = new SeqDefineDAL().GetCurrentCode("KANBAN_PULL_ORDER_CODE", "KB");///TODO:看板拉动单号生成
///看板拉动单外键①为NEW.GUID，同时作为看板拉动单明细的主表外键②，明细中外键①为NEW.GUID
///物流路线代码⑤名称⑥、来源仓库⑧存储区⑦、目标仓库⑩存储区⑨从零件类代码③对应的看板零件类TM_MPM_KANBAN_PART_BOX信息中获取
///创建时间获取数据库服务器时间，创建人取自于参数操作用户
///创建后看板拉动单状态⑱为20.已发布，数据有效标记⑲为true，看板拉动单其它字段在生成时留空
#region TT_MPM_KANBAN_PULL_ORDER
string sqlKanbanPullOrderValue = "N'" + pullOrderFid.ToString() + "'" ///FID
+ ",N'" + orderCode + "'" ///ORDER_CODE
+ ",N'" + kanbanPartBoxInfo.PartBoxCode + "'" ///PART_BOX_CODE
+ ",N'" + kanbanPartBoxInfo.PartBoxName + "'" ///PART_BOX_NAME
+ ",N'" + kanbanPartBoxInfo.RouteCode + "'" ///ROUTE_CODE
+ ",N'" + kanbanPartBoxInfo.RouteName + "'" ///ROUTE_NAME
+ ",N'" + kanbanPartBoxInfo.SourceZoneNo + "'" ///SOURCE_ZONE_NO
+ ",N'" + kanbanPartBoxInfo.SourceWmNo + "'" ///SOURCE_WM_NO
+ ",N'" + kanbanPartBoxInfo.TargetZoneNo + "'" ///TARGET_ZONE_NO
+ ",N'" + kanbanPartBoxInfo.TargetWmNo + "'" ///TARGET_WM_NO
+ ",NULL" ///PICK_UP_FINISH_TIME
+ ",NULL" ///PICK_UP_USER
+ ",NULL" ///DELIVERY_FINISH_TIME
+ ",NULL" ///DELIVERY_USER
+ ",NULL" ///PRINT_CNT
+ ",NULL" ///PRINT_TIME
+ ",NULL" ///PRINT_USER
+ "," + (int)PullOrderStatusConstants.Released ///STATUS
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'"; ///CREATE_USER
#endregion
stringBuilder.AppendLine(string.Format(sqlKanbanPullOrder, sqlKanbanPullOrderValue));
List <KanbanCardInfo> kanbanCards = kanbanCardInfos.Where(d => d.PartBoxCode == partboxcode.PartBoxCode).ToList();
#region 根据看板卡信息获取所有零件物料拉动信息
var maintainInhouseLogisticStandardInfosparts = from a in maintainInhouseLogisticStandardInfos
join b in kanbanCards.Where(w => w.PartBoxCode == partboxcode.PartBoxCode).GroupBy(w => new { w.PartBoxCode, w.PartNo }).Select(w => new { w.Key.PartBoxCode, w.Key.PartNo })
on new { a.InhousePartClass, a.PartNo } equals new { InhousePartClass = b.PartBoxCode, b.PartNo }
select a;
#endregion
foreach (var kanbanCard in kanbanCards)
{
///看板卡号①=④、物料号④=⑤、物料名称⑤=⑥、物料数量⑧=⑨、包装型号⑨=⑩
///包装数量⑪默认为1，数据有效标记⑫为true
///若看板卡信息中指定了供应商代码⑥名称⑦，则看板拉动单明细中供应商代码⑦名称⑧从看板卡信息中获取
#region TT_MPM_KANBAN_PULL_ORDER_DETAIL
string sqlKanbanPullOrderDetailValue = "NEWID()" ///FID
+ ",N'" + pullOrderFid.ToString() + "'" ///ORDER_FID
+ ",N'" + orderCode + "'" ///ORDER_CODE
+ "," + (int)PullOrderStatusConstants.Released ///ORDER_STATUS
+ ",N'" + kanbanCard.CardNo + "'" ///CARD_NO
+ ",N'" + kanbanCard.PartNo + "'" ///PART_NO
+ ",N'" + kanbanCard.PartName + "'" ///PART_NAME
+ ",N'" + kanbanCard.SupplierCode + "'" ///SUPPLIER_CODE
+ ",N'" + kanbanCard.SupplierName + "'" ///SUPPLIER_NAME
+ "," + kanbanCard.PartQty.GetValueOrDefault() ///PART_QTY
+ ",N'" + kanbanCard.PackageCode + "'" ///PACKAGE_CODE
+ ",1" ///PACKAGE_QTY
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'"; ///CREATE_USER
#endregion
stringBuilder.AppendLine(string.Format(sqlKanbanPullOrderDetail, sqlKanbanPullOrderDetailValue));
}
#region 单据衔接
MaterialPullingOrderInfo mpOrder = new MaterialPullingOrderInfo();
mpOrder.OrderNo = orderCode;
mpOrder.PartBoxCode = kanbanPartBoxInfo.PartBoxCode; ///零件类2
mpOrder.PartBoxName = kanbanPartBoxInfo.PartBoxName; ///零件类名称3
mpOrder.Plant = string.Empty; ///工厂4
mpOrder.Workshop = string.Empty; ///车间5
mpOrder.AssemblyLine = string.Empty; ///流水线6
mpOrder.SupplierNum = string.Empty; ///供应商代码7
mpOrder.SupplierName = string.Empty; ///供应商名称
mpOrder.SourceZoneNo = kanbanPartBoxInfo.SourceZoneNo; ///来源存储区8
mpOrder.SourceWmNo = kanbanPartBoxInfo.SourceWmNo; ///来源仓库9
mpOrder.TargetZoneNo = kanbanPartBoxInfo.TargetZoneNo; ///目标存储区10
mpOrder.TargetWmNo = kanbanPartBoxInfo.TargetWmNo; ///目标仓库11
mpOrder.TargetDock = string.Empty; ///道口12
mpOrder.PlanShippingTime = DateTime.Now.AddMinutes(kanbanPartBoxInfo.PickUpTime.GetValueOrDefault()); ///建议交货时间
mpOrder.PlanDeliveryTime = mpOrder.PlanShippingTime.GetValueOrDefault().AddMinutes(kanbanPartBoxInfo.DeliveryTime.GetValueOrDefault()); ///预计到厂时间14 = 建议交货时间 – 送货时间
mpOrder.PublishTime = DateTime.Now;
mpOrder.AsnFlag = false;
mpOrder.OrderType = 0;///TODO:对于看板拉动单应该的值，如果后续用到则需要定义枚举
mpOrder.MaterialPullingOrderDetailInfos = (from m in maintainInhouseLogisticStandardInfosparts
select new MaterialPullingOrderDetailInfo
{
OrderNo = orderCode, ///拉动单号1
SupplierNum = m.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
//Uom = m.PartUnits,///计量单位6
PackageQty = m.InboundPackage.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.InboundPackageModel, ///入库包装编号8
RequirePackageQty = kanbanCards.Where(w => w.PartNo == m.PartNo).Count(), ///需求包装数量9
RequirePartQty = (kanbanCards.Where(w => w.PartNo == m.PartNo).Sum(w => w.PartQty) ?? 0), ///需求物料数量10
TargetZoneNo = kanbanPartBoxInfo.TargetZoneNo,
TargetWmNo = kanbanPartBoxInfo.TargetWmNo
}).ToList();
///执行单据衔接
stringBuilder.AppendLine(MaterialPullingCommonBLL.Handler(mpOrder, loginUser));
#endregion
}
#endregion
///数据保存时使用SQL拼接多条insert语句方式一次提交执行，执行失败需要同步返回至客户端
using (TransactionScope trans = new TransactionScope())
{
CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString());
trans.Complete();
}
return(true);
}
/// <summary>
/// 同步
/// </summary>
/// <param name="loginUser"></param>
public static void Sync(string loginUser)
{
///获取没有处理的送货单数据
List <SrmDeliveryNoteInfo> srmDeliveryNoteInfos = new SrmDeliveryNoteBLL().GetList("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", string.Empty);
if (srmDeliveryNoteInfos.Count == 0)
{
return;
}
///获取没有处理的送货单详情数据- 没有数据主表报错
List <SrmDeliveryNoteDetailInfo> srmDeliveryNoteDetailInfos = new SrmDeliveryNoteDetailBLL().GetList("[ORDER_FID] in ('" + string.Join("','", srmDeliveryNoteInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "')", string.Empty);
if (srmDeliveryNoteDetailInfos.Count == 0)
{
return;
}
///获取相关物料供应商信息
List <SupplierInfo> supplierInfos = new SupplierBLL().GetList("" +
"[SUPPLIER_NUM] in ('" + string.Join("','", srmDeliveryNoteInfos.Select(d => d.Suppliercode).ToArray()) + "') and " +
"[SUPPLIER_TYPE] = " + (int)SupplierTypeConstants.MaterialSupplier + "", string.Empty);
///
StringBuilder @string = new StringBuilder();
if (supplierInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000475' where " +///送货单中供应商信息无效
"[ID] in (" + string.Join(",", srmDeliveryNoteInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取相关存储区信息
List <string> zoneNos = srmDeliveryNoteInfos.Where(d => !string.IsNullOrEmpty(d.Targetzoneno)).Select(d => d.Targetzoneno).ToList();
zoneNos.AddRange(srmDeliveryNoteInfos.Where(d => !string.IsNullOrEmpty(d.Sourcezoneno)).Select(d => d.Sourcezoneno).ToList());
List <ZonesInfo> zonesInfos = new ZonesBLL().GetList("" +
"[ZONE_NO] in ('" + string.Join("','", zoneNos.ToArray()) + "') and " +
"[PLANT] in ('" + string.Join("','", srmDeliveryNoteInfos.Select(d => d.Plant).ToArray()) + "')", string.Empty);
if (zonesInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000485' where " +///送货单中存储区信息不存在
"[ID] in (" + string.Join(",", srmDeliveryNoteInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///
List <long> dealedIds = new List <long>();
///循环写入数据
foreach (SrmDeliveryNoteInfo srmDeliveryNoteInfo in srmDeliveryNoteInfos)
{
///获取送货单明细
List <SrmDeliveryNoteDetailInfo> srmDeliveryNoteDetails = srmDeliveryNoteDetailInfos.Where(d => d.OrderFid.GetValueOrDefault() == srmDeliveryNoteInfo.Fid.GetValueOrDefault()).ToList();
if (srmDeliveryNoteDetails.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000473' where " +///送货单无物料明细
"[ID] = " + srmDeliveryNoteInfo.Id + ";");
continue;
}
///供应商信息
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == srmDeliveryNoteInfo.Suppliercode);
if (supplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000484' where " +///该送货单中供应商信息不存在
"[ID] = " + srmDeliveryNoteInfo.Id + ";");
continue;
}
///校验供应商是否启用了ASN功能
if (!supplierInfo.AsnFlag.GetValueOrDefault())
{
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000486' where " +///该供应商未启用ASN功能
"[ID] = " + srmDeliveryNoteInfo.Id + ";");
continue;
}
///目标存储区
ZonesInfo zonesInfo = zonesInfos.FirstOrDefault(d => d.ZoneNo == srmDeliveryNoteInfo.Targetzoneno && d.Plant == srmDeliveryNoteInfo.Plant);
if (zonesInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000246' where " +///存储区数据错误
"[ID] = " + srmDeliveryNoteInfo.Id + ";");
continue;
}
///单据衔接对象创建
MaterialPullingOrderInfo materialPullingOrderInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
///SrmDeliveryNoteInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(srmDeliveryNoteInfo, ref materialPullingOrderInfo);
///SupplierInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(supplierInfo, ref materialPullingOrderInfo);
///
materialPullingOrderInfo.TargetWmNo = zonesInfo.WmNo;
///PART_BOX_CODE,TODO:考虑由SRM增加字段？
materialPullingOrderInfo.PartBoxCode = string.Empty;
///PART_BOX_NAME,11
materialPullingOrderInfo.PartBoxName = string.Empty;
///
materialPullingOrderInfo.Route = null;
///
foreach (SrmDeliveryNoteDetailInfo srmDeliveryNoteDetail in srmDeliveryNoteDetails)
{
///单据衔接明细对象创建
MaterialPullingOrderDetailInfo materialPullingOrderDetailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
///SrmDeliveryNoteDetailInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(srmDeliveryNoteDetail, ref materialPullingOrderDetailInfo);
///SRM拉动单明细中的供应商必须为单据的供应商
materialPullingOrderDetailInfo.SupplierNum = srmDeliveryNoteInfo.Suppliercode;
///
materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Add(materialPullingOrderDetailInfo);
}
///生成创建入库单的语句
@string.Append(MaterialPullingCommonBLL.CreateReceiveSql(materialPullingOrderInfo, new List <PartsStockInfo>(), loginUser));
///执行成功的ID
dealedIds.Add(srmDeliveryNoteInfo.Id);
}
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
@string.AppendLine("update [LES].[TI_IFM_SRM_DELIVERY_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," +
"[PROCESS_TIME] = GETDATE() where " +
"[ID] in (" + string.Join(",", dealedIds.ToArray()) + ");");
}
///执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
}
/// <summary>
/// Sync
/// </summary>
public static void Sync(string loginUser)
{
///获取未处理的中间表数据
List <WmsVmiAsnRunsheetInfo> wmsVmiAsnRunsheetInfos = new WmsVmiAsnRunsheetBLL().GetList("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", string.Empty);
if (wmsVmiAsnRunsheetInfos.Count == 0)
{
return;
}
List <WmsVmiAsnRunsheetDetailInfo> wmsVmiAsnRunsheetDetailInfos = new WmsVmiAsnRunsheetDetailBLL().GetList("[LOG_FID] in ('" + string.Join("','", wmsVmiAsnRunsheetInfos.Select(d => d.LogFid.GetValueOrDefault()).ToArray()) + "')", string.Empty);
if (wmsVmiAsnRunsheetDetailInfos.Count == 0)
{
return;
}
///获取相关物料供应商信息
List <SupplierInfo> supplierInfos = new SupplierBLL().GetList("" +
"[SUPPLIER_NUM] in ('" + string.Join("','", wmsVmiAsnRunsheetInfos.Select(d => d.Suppliercode).ToArray()) + "') and " +
"[SUPPLIER_TYPE] = " + (int)SupplierTypeConstants.MaterialSupplier + "", string.Empty);
///
StringBuilder @string = new StringBuilder();
if (supplierInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000475' where " +///送货单中供应商信息无效
"[ID] in (" + string.Join("','", wmsVmiAsnRunsheetInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取相关存储区信息
List <string> zoneNos = wmsVmiAsnRunsheetInfos.Where(d => !string.IsNullOrEmpty(d.Targetzoneno)).Select(d => d.Targetzoneno).ToList();
zoneNos.AddRange(wmsVmiAsnRunsheetInfos.Where(d => !string.IsNullOrEmpty(d.Sourcezoneno)).Select(d => d.Sourcezoneno).ToList());
List <ZonesInfo> zonesInfos = new ZonesBLL().GetList("" +
"[ZONE_NO] in ('" + string.Join("','", zoneNos.ToArray()) + "') ", string.Empty);
if (zonesInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000485' where " +///送货单中存储区信息不存在
"[ID] in (" + string.Join(",", wmsVmiAsnRunsheetInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取相关仓库信息
List <WarehouseInfo> warehouseInfos = new WarehouseBLL().GetList("[WAREHOUSE] in ('" + string.Join("','", zonesInfos.Select(d => d.WmNo).ToArray()) + "')", string.Empty);
if (warehouseInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +///仓库信息不存在
"[ID] in (" + string.Join("','", wmsVmiAsnRunsheetInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///供应商VMI关系
List <VmiSupplierInfo> vmiSupplierInfos = new VmiSupplierBLL().GetList("" +
"[SUPPLIER_NUM] in ('" + string.Join("','", supplierInfos.Select(d => d.SupplierNum).ToArray()) + "') and " +
"[WM_NO] in ('" + string.Join("','", warehouseInfos.Select(d => d.Warehouse).ToArray()) + "')", string.Empty);
if (vmiSupplierInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000429' where " +///VMI供应商信息未维护
"[ID] in (" + string.Join("','", wmsVmiAsnRunsheetInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///
List <long> dealedIds = new List <long>();
///循环写入数据
foreach (WmsVmiAsnRunsheetInfo wmsVmiAsnRunsheetInfo in wmsVmiAsnRunsheetInfos)
{
///获取送货单明细
List <WmsVmiAsnRunsheetDetailInfo> wmsVmiAsnRunsheetDetails = wmsVmiAsnRunsheetDetailInfos.Where(d => d.OrderFid.GetValueOrDefault() == wmsVmiAsnRunsheetInfo.LogFid.GetValueOrDefault()).ToList();
if (wmsVmiAsnRunsheetDetails.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000473' where " +///送货单无物料明细
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
///供应商信息
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == wmsVmiAsnRunsheetInfo.Suppliercode);
if (supplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000484' where " +///该送货单中供应商信息不存在
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
var sourceZoneInfo = zonesInfos.FirstOrDefault(fd => fd.ZoneNo == wmsVmiAsnRunsheetInfo.Sourcezoneno);
if (sourceZoneInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000500' where " +///存储区不存在
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
///来源仓库
WarehouseInfo sourceWarehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == sourceZoneInfo.WmNo);
if (sourceWarehouseInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +///仓库信息不存在
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
if (sourceWarehouseInfo.WarehouseType.GetValueOrDefault() != (int)WarehouseTypeConstants.VMI)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000219' where " +///仓库类型错误
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
var targetZoneInfo = zonesInfos.FirstOrDefault(fd => fd.ZoneNo == wmsVmiAsnRunsheetInfo.Targetzoneno);
if (targetZoneInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000500' where " +///存储区不存在
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
///来源仓库
WarehouseInfo targetWarehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == targetZoneInfo.WmNo);
if (targetWarehouseInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +///仓库信息不存在
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
if (targetWarehouseInfo.WarehouseType.GetValueOrDefault() != (int)WarehouseTypeConstants.RDC)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000219' where " +///存储区不存在
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
///VMI供应商关系
VmiSupplierInfo vmiSupplierInfo = vmiSupplierInfos.FirstOrDefault(d => d.SupplierNum == supplierInfo.SupplierNum && d.WmNo == sourceWarehouseInfo.Warehouse);
if (vmiSupplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000429' where " +///VMI供应商信息未维护
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
if (!vmiSupplierInfo.AsnFlag.GetValueOrDefault())
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000486' where " +///该供应商未启用ASN功能
"[ID] = " + wmsVmiAsnRunsheetInfo.Id + ";");
continue;
}
///单据衔接对象创建
MaterialPullingOrderInfo materialPullingOrderInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
///WmsVmiAsnRunsheetInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(wmsVmiAsnRunsheetInfo, ref materialPullingOrderInfo);
///
materialPullingOrderInfo.SourceWmNo = sourceWarehouseInfo.Warehouse;
///
materialPullingOrderInfo.TargetWmNo = targetWarehouseInfo.Warehouse;
///
materialPullingOrderInfo.AsnFlag = true;
///PART_BOX_CODE,TODO:考虑由WMS增加字段？
materialPullingOrderInfo.PartBoxCode = string.Empty;
///PART_BOX_NAME,11
materialPullingOrderInfo.PartBoxName = string.Empty;
///
materialPullingOrderInfo.Route = null;
///
foreach (WmsVmiAsnRunsheetDetailInfo wmsVmiAsnRunsheetDetail in wmsVmiAsnRunsheetDetails)
{
///单据衔接明细对象创建
MaterialPullingOrderDetailInfo materialPullingOrderDetailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
///WmsVmiAsnRunsheetDetailInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(wmsVmiAsnRunsheetDetail, ref materialPullingOrderDetailInfo);
///WMS拉动单明细中的供应商必须为单据的供应商
materialPullingOrderDetailInfo.SupplierNum = wmsVmiAsnRunsheetInfo.Suppliercode;
///
materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Add(materialPullingOrderDetailInfo);
}
///生成创建入库单的语句
@string.Append(MaterialPullingCommonBLL.CreateReceiveSql(materialPullingOrderInfo, new List <PartsStockInfo>(), loginUser));
///执行成功的ID
dealedIds.Add(wmsVmiAsnRunsheetInfo.Id);
}
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_ASN_RUNSHEET] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," +
"[PROCESS_TIME] = GETDATE() where " +
"[ID] in (" + string.Join(",", dealedIds.ToArray()) + ");");
}
///执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
}
/// <summary>
/// Handler
/// </summary>
public void Handler()
{
///获取已启用计划零件类集合
List <PlanPartBoxInfo> planPartBoxInfos = new PlanPartBoxBLL().GetList("[STATUS] = " + (int)BasicDataStatusConstants.Enable + "", string.Empty);
if (planPartBoxInfos.Count == 0)
{
throw new Exception("3x00000023");///没有已启用的计划拉动零件类
}
///获取零件类对应物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos
= new MaintainInhouseLogisticStandardBLL().GetList("[INHOUSE_PART_CLASS] in ('" + string.Join("','", planPartBoxInfos.Select(d => d.PartBoxCode).ToArray()) + "') and [STATUS] = " + (int)BasicDataStatusConstants.Enable + " and [INHOUSE_SYSTEM_MODE]=N'60'", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
throw new Exception("0x00000233");///没有已启用的物料拉动信息
}
///循环零件类集合
foreach (PlanPartBoxInfo planPartBoxInfo in planPartBoxInfos)
{
#region 基础变量
///窗口时间
string windowTimes = string.Empty;
///当前窗口时间
DateTime currentWindowTime = DateTime.MinValue;
///下一窗口时间
DateTime nextWindowTime = DateTime.MaxValue;
///时区
string timeZone = string.Empty;
List <long> planTimeIds = new List <long>();
///范围 工厂、车间、产线
string sqlwhere = string.Empty;
if (!string.IsNullOrEmpty(planPartBoxInfo.AssemblyLine))
{
sqlwhere = "and [ASSEMBLY_LINE] = N'" + planPartBoxInfo.AssemblyLine + "' ";
}
else
{
sqlwhere = "and [WERK] = N'" + planPartBoxInfo.Plant + "' ";
}
#endregion
#region 获取生产订单BOM集合
///通过这些生产订单号①=①获取订单BOM(TT_BAS_PULL_ORDER_BOM)
List <PullOrderBomInfo> pullOrderBomInfos = new List <PullOrderBomInfo>();
///生产订单集合
List <PullOrdersInfo> pullOrdersInfos = new List <PullOrdersInfo>();
///获取零件类对应的最近一次未发单窗口时间
///当此参数为true时以计划拉动窗口时间TT_MPM_PLAN_WINDOW_TIME中发单状态⑥为10.未发单且发单时间④已早于当前时间作为当前执行依据
if (planPullWindowTimeEnable.ToLower() == "true")
{
PlanWindowTimeInfo planWindowTimeInfo = new PlanWindowTimeBLL().GetLastNoSendTimeInfo(planPartBoxInfo.Fid.GetValueOrDefault());
///并根据窗口时间⑤匹配生产订单计划执行时间同时核对未生成计划拉动单的生产订单(先以小于等于窗口时间作为条件再加上TT_MPM_PLAN_PULL_CREATE_STATUS表的SEQID not exist条件进行过滤)
///如果没有符合条件的窗口时间，则执行下一个零件类
if (planWindowTimeInfo == null)
{
continue;
}
if (planWindowTimeInfo.WindowTime == null)
{
continue;
}
planTimeIds.Add(planWindowTimeInfo.Id);
currentWindowTime = planWindowTimeInfo.WindowTime.GetValueOrDefault();
///窗口时间
windowTimes = currentWindowTime.ToString("MM:dd");
///时区为窗口时间中设定的时间
timeZone = planWindowTimeInfo.TimeZone;
nextWindowTime = new PlanWindowTimeBLL().GetNextWindowTime(planPartBoxInfo.Fid.GetValueOrDefault(), currentWindowTime);
///获取未处理生产订单对应的BOM
pullOrderBomInfos = new PullOrderBomBLL().GetUnPlanPullingOrders(currentWindowTime, nextWindowTime, sqlwhere, planPartBoxInfo.Fid.GetValueOrDefault());
}
///若为false时以计划当日零点作为交付时间依据并根据零件类中设定的时间合计⑫+⑬+⑭扣减后作为发单时间
///并根据计划当日零点(大于等于)与计划次日零点(小于)之间同时核对未生成计划拉动单的生产订单
else if (planPullWindowTimeEnable.ToLower() == "false")
{
///获取计划拉动提前期(分钟)
int sumLeadTime = planPartBoxInfo.DelayTime.GetValueOrDefault()
+ planPartBoxInfo.DeliveryTime.GetValueOrDefault()
+ planPartBoxInfo.PickUpTime.GetValueOrDefault();
///根据提前期用当前时间向后推得窗口时间的零点
currentWindowTime = DateTime.Parse(DateTime.Now.AddMinutes(sumLeadTime).ToShortDateString());
///下一窗口时间为次日零点
nextWindowTime = currentWindowTime.AddDays(1);
///获取未处理生产订单对应的BOM
pullOrderBomInfos = new PullOrderBomBLL().GetUnPlanPullingOrders(currentWindowTime, nextWindowTime, sqlwhere, planPartBoxInfo.Fid.GetValueOrDefault());
///拼接窗口时间
List <DateTime> planWindowTimes = new PlanWindowTimeBLL().GetWindowTimesByWorkDay(planPartBoxInfo.Fid.GetValueOrDefault(), currentWindowTime);
if (planWindowTimes.Count > 0)
{
windowTimes = string.Join(",", planWindowTimes.Select(d => d.ToString("HH:mm")).ToArray());
}
///拼接时区
List <PlanWindowTimeInfo> PlanWindowTimeInfos = new PlanWindowTimeBLL().GetList("", "WINDOW_TIME");
if (PlanWindowTimeInfos.Count > 0)
{
foreach (var p in PlanWindowTimeInfos)
{
planTimeIds.Add(p.Id);
}
timeZone = string.Join(",", PlanWindowTimeInfos.Select(d => d.TimeZone).ToArray());
}
}
else
{
throw new Exception("1x00000045");///系统配置参数错误
}
#endregion
#region 处理集合取交集
///根据物料号、供应商、工厂将BOM分组并计算数量
var pullOrderBomQuery = pullOrderBomInfos
.GroupBy(b => new { b.Zcomno, b.SupplierNum, b.Zkwerk })
.Select(p => new { PartNo = p.Key.Zcomno, p.Key.SupplierNum, Plant = p.Key.Zkwerk, Zqty = p.Sum(x => x.Zqty.GetValueOrDefault()) }).ToList();
///获取零件类对应的物料拉动信息
var maintainInhouseLogisticStandardQuery = maintainInhouseLogisticStandardInfos.Where(d => d.InhousePartClass == planPartBoxInfo.PartBoxCode).ToList();
///校验是否全部维护了入库单包装数量，否则除数为零时程序会报错
int cnt = maintainInhouseLogisticStandardQuery.Count(d => d.InboundPackage.GetValueOrDefault() == 0);
if (cnt > 0)
{
throw new Exception("3x00000024");///物料拉动信息中入库单包装数量未维护
}
///并将计划零件类对应的物料拉动信息与订单BOM的交集部分作为计划拉动物料需求（比对依据为物料号②、供应商代码③、工厂）
var materialRequirementInfos = (from m in maintainInhouseLogisticStandardQuery
join b in pullOrderBomQuery
on new { m.PartNo, m.SupplierNum, m.Plant }
equals new { b.PartNo, b.SupplierNum, b.Plant }
select new
{
m.PartNo, ///物料号
m.SupplierNum, ///供应商代码
m.Plant, ///工厂代码
m.PartCname, ///物料中文描述
m.PartEname, ///物料英文描述
m.PartUnits, ///计量单位
m.InboundPackage, ///入库单包装数量
m.InboundPackageModel, ///入库包装型号
RequirePackageQty = Math.Ceiling((b.Zqty * 1.0) / m.InboundPackage.GetValueOrDefault()), ///需求包装数
b.Zqty ///需求物料数量
}).ToList();
#endregion
///生成单据时为了保障效率需要拼接整个insert语句后提交至数据库执行
StringBuilder sql = new StringBuilder();
///检验模式
if (inspectionModeDistinguishOrderFlag.ToLower() == "true")
{
///获取所有涉及的检验模式
List <PartInspectionModeInfo> partInspectionModeInfos
= new PartInspectionModeBLL().GetList("" +
"[PART_NO] in ('" + string.Join("','", materialRequirementInfos.Select(d => d.PartNo).ToArray()) + "') and " +
"[SUPPLIER_NUM] in ('" + string.Join("','", materialRequirementInfos.Select(d => d.SupplierNum).ToArray()) + "')", string.Empty);
///给物料需求的集合赋予检验模式
var partInspectionmaterialRequirementInfos = (from m in materialRequirementInfos
join p in partInspectionModeInfos
on new { m.PartNo, m.SupplierNum }
equals new { p.PartNo, p.SupplierNum } into g
select new
{
m.PartNo, ///物料号
m.SupplierNum, ///供应商代码
m.Plant, ///工厂代码
m.PartCname, ///物料中文描述
m.PartEname, ///物料英文描述
m.PartUnits, ///计量单位
m.InboundPackage, ///入库单包装数量
m.InboundPackageModel, ///入库包装型号
m.RequirePackageQty, ///需求包装数
m.Zqty, ///需求物料数量
Inspection = g.Select(d => d.InspectionMode.GetValueOrDefault()).FirstOrDefault() ///物料检验
}).ToList();
#region 免检
///免检
var exemptionInspectionMaterialRequirementInfos = partInspectionmaterialRequirementInfos.Where(d => d.Inspection == (int)InspectionModeConstants.ExemptionInspection).ToList();
///生成计划拉动单同时记录该生产订单该计划零件类已生成单据
///当有物料需求时，才会生成计划拉动单
if (exemptionInspectionMaterialRequirementInfos.Count > 0)
{
///实例化主表Guid
Guid planPullOrderFid = Guid.NewGuid();
///主表计划拉动单号
string orderCode = new SeqDefineBLL().GetCurrentCode("PLAN_PULL_ORDER_CODE", planPartBoxInfo.PartBoxCode.Substring(0, 1).ToUpper());///TODO:计划拉动单编号规则可能还会更改，待客户确认
MaterialPullingOrderInfo materialPullingOrderInfo = CreateMaterialPullingOrderInfo(planPartBoxInfo, orderCode, currentWindowTime, true);
materialPullingOrderInfo.MaterialPullingOrderDetailInfos = (from m in exemptionInspectionMaterialRequirementInfos
select new MaterialPullingOrderDetailInfo
{
OrderNo = orderCode, ///拉动单号1
SupplierNum = m.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
Uom = m.PartUnits, ///计量单位6
PackageQty = m.InboundPackage.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.InboundPackageModel, ///入库包装编号8
RequirePackageQty = Convert.ToInt32(m.RequirePackageQty), ///需求包装数量9
RequirePartQty = m.Zqty, ///需求物料数量10
TargetZoneNo = planPartBoxInfo.TargetZoneNo,
InspectMode = m.Inspection ///校验模式
}).ToList();
sql.AppendLine(CreatePlanPullOrderSql(planPullOrderFid, materialPullingOrderInfo, windowTimes, timeZone, (int)InspectionFlagConstants.Exemption));
sql.AppendLine(CreatePlanPullOrderDetailSql(planPullOrderFid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
sql.AppendFormat(MaterialPullingCommonBLL.Handler(materialPullingOrderInfo, loginUser));
}
#endregion
#region 免检
///不免检
var inspectionmaterialRequirementInfos = partInspectionmaterialRequirementInfos.Where(d => d.Inspection != (int)InspectionModeConstants.ExemptionInspection).ToList();
///生成计划拉动单同时记录该生产订单该计划零件类已生成单据
///当有物料需求时，才会生成计划拉动单
if (inspectionmaterialRequirementInfos.Count > 0)
{
///实例化主表Guid
Guid planPullOrderFid = Guid.NewGuid();
///主表计划拉动单号
string orderCode = new SeqDefineBLL().GetCurrentCode("PLAN_PULL_ORDER_CODE", planPartBoxInfo.PartBoxCode.Substring(0, 1).ToUpper());///TODO:计划拉动单编号规则可能还会更改，待客户确认
MaterialPullingOrderInfo materialPullingOrderInfo = CreateMaterialPullingOrderInfo(planPartBoxInfo, orderCode, currentWindowTime, false);
materialPullingOrderInfo.MaterialPullingOrderDetailInfos = (from m in inspectionmaterialRequirementInfos
select new MaterialPullingOrderDetailInfo
{
OrderNo = orderCode, ///拉动单号1
SupplierNum = m.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
Uom = m.PartUnits, ///计量单位6
PackageQty = m.InboundPackage.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.InboundPackageModel, ///入库包装编号8
RequirePackageQty = Convert.ToInt32(m.RequirePackageQty), ///需求包装数量9
RequirePartQty = m.Zqty, ///需求物料数量10
TargetZoneNo = planPartBoxInfo.TargetZoneNo,
InspectMode = m.Inspection ///校验模式
}).ToList();
///
sql.AppendLine(CreatePlanPullOrderSql(planPullOrderFid, materialPullingOrderInfo, windowTimes, timeZone, (int)InspectionFlagConstants.Inspect));
sql.AppendLine(CreatePlanPullOrderDetailSql(planPullOrderFid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
sql.AppendFormat(MaterialPullingCommonBLL.Handler(materialPullingOrderInfo, loginUser));
}
#endregion
}
else
{
///生成计划拉动单同时记录该生产订单该计划零件类已生成单据
///当有物料需求时，才会生成计划拉动单
if (materialRequirementInfos.Count > 0)
{
///实例化主表Guid
Guid planPullOrderFid = Guid.NewGuid();
///主表计划拉动单号
string orderCode = new SeqDefineBLL().GetCurrentCode("PLAN_PULL_ORDER_CODE", planPartBoxInfo.PartBoxCode.Substring(0, 1).ToUpper());///TODO:计划拉动单编号规则可能还会更改，待客户确认
MaterialPullingOrderInfo materialPullingOrderInfo = CreateMaterialPullingOrderInfo(planPartBoxInfo, orderCode, currentWindowTime, false);
materialPullingOrderInfo.MaterialPullingOrderDetailInfos = (from m in materialRequirementInfos
select new MaterialPullingOrderDetailInfo
{
OrderNo = orderCode, ///拉动单号1
SupplierNum = m.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
Uom = m.PartUnits, ///计量单位6
PackageQty = m.InboundPackage.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.InboundPackageModel, ///入库包装编号8
RequirePackageQty = Convert.ToInt32(m.RequirePackageQty), ///需求包装数量9
RequirePartQty = m.Zqty, ///需求物料数量10
TargetZoneNo = planPartBoxInfo.TargetZoneNo
}).ToList();
///生成计划拉动单
sql.AppendLine(CreatePlanPullOrderSql(planPullOrderFid, materialPullingOrderInfo, windowTimes, timeZone, null));
///生成计划拉动单明细
sql.AppendLine(CreatePlanPullOrderDetailSql(planPullOrderFid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
///单据衔接
sql.AppendLine(MaterialPullingCommonBLL.Handler(materialPullingOrderInfo, loginUser));
}
}
///订单BOM存在时才需要更新
if (pullOrderBomInfos.Count > 0)
{
///更新中间表处理状态 = 已处理
sql.AppendFormat("update [LES].[TT_MPM_PLAN_PULL_CREATE_STATUS] set " +
"[STATUS] = " + (int)ProcessFlagConstants.Processed + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() where " +
"[VALID_FLAG] = 1 and " +
"[PART_BOX_FID] = N'{0}' and " +
"[ORDER_FID] in ('{1}');",
planPartBoxInfo.Fid.GetValueOrDefault(),
string.Join("','", pullOrderBomInfos.Select(d => d.Orderfid.GetValueOrDefault()).ToArray()));
}
#region 执行
if (sql.Length > 0)
{
///更改窗口时间发单状态
if (planPullWindowTimeEnable.ToLower() == "true")
{
sql.AppendFormat("update [LES].[TT_MPM_PLAN_WINDOW_TIME] set " +
"[SEND_TIME_STATUS] = " + (int)SendTimeStatusConstants.Sent + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() where [ID] in (" + string.Join(",", planTimeIds.ToArray()) + ");");
}
using (TransactionScope trans = new TransactionScope())
{
if (!BLL.LES.CommonBLL.ExecuteNonQueryBySql(sql.ToString()))
{
throw new Exception("0x00000736");///写入数据库失败
}
trans.Complete();
}
}
#endregion
}
#endregion
}
/// <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());
}
/// <summary>
/// Jis拉动
/// </summary>
/// <param name="jisPartBoxInfo"></param>
/// <param name="jisCounterInfo"></param>
/// <param name="jisCounterLogs"></param>
/// <param name="maintainInhouseLogistics"></param>
/// <param name="JisPullGuid"></param>
/// <param name="orderCode"></param>
/// <param name="pullOrdersInfos"></param>
/// <param name="isJis"></param>
/// <returns></returns>
public string JisPull(JisPartBoxInfo jisPartBoxInfo, JisCounterInfo jisCounterInfo, List <JisCounterLogInfo> jisCounterLogs, List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogistics, List <PullOrdersInfo> pullOrdersInfos, bool isJis)
{
StringBuilder stringBuilder = new StringBuilder();
///拉动单Guid
Guid JisPullGuid = Guid.NewGuid();
///拉动单号
string orderCode = new SeqDefineBLL().GetCurrentCode("JIS_PULL_ORDER_CODE");
///单据衔接
MaterialPullingOrderInfo materialPullingOrderInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
///jisPartBoxInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetJisMaterialPullingOrderInfo(jisPartBoxInfo, ref materialPullingOrderInfo);
///orderCode -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetorderCodeMaterialPullingOrderInfo(orderCode, ref materialPullingOrderInfo);
foreach (JisCounterLogInfo jisCounterLog in jisCounterLogs)
{
///物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogistics.Where(d => d.PartNo == jisCounterLog.PartNo).FirstOrDefault();
if (maintainInhouseLogisticStandardInfo == null)
{
continue;
}
///生产订单信息
PullOrdersInfo pullOrdersInfo = pullOrdersInfos.FirstOrDefault(d => d.OrderNo == jisCounterLog.SourceData);
if (pullOrdersInfo == null)
{
continue;
}
///仓储衔接明细表
MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
///maintainInhouseLogisticStandardInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMainMaterialPullingOrderDetailInfo(maintainInhouseLogisticStandardInfo, jisCounterLog.PartQty.GetValueOrDefault(), ref detailInfo);
///jisPartBoxInfo-> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetJisMaterialPullingOrderDetailInfo(jisPartBoxInfo, ref detailInfo);
///orderCode-> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetOrderCodeMaterialPullingOrderDetailInfo(orderCode, ref detailInfo);
///pullOrdersInfo-> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetPullOrderMaterialPullingOrderDetailInfo(pullOrdersInfo, ref detailInfo);
///Add
materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Add(detailInfo);
if (isJis)
{
///排序拉动明细
stringBuilder.AppendFormat(CreateJisPullOrderDetailSql(JisPullGuid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
}
else
{
///物料成套拉动明细
stringBuilder.AppendFormat(CreateCompletePullOrderDetailSql(JisPullGuid, materialPullingOrderInfo.MaterialPullingOrderDetailInfos));
}
if (stringBuilder.Length == 0)
{
continue;
}
}
if (isJis)
{
///开始车号
materialPullingOrderInfo.StartVehicheNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Min(d => d.DayVehicheSeqNo.GetValueOrDefault());
///结束车号
materialPullingOrderInfo.EndVehicheNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Max(d => d.DayVehicheSeqNo.GetValueOrDefault());
///排序拉动主表
stringBuilder.AppendFormat(CreateJisPullOrderSql(JisPullGuid, materialPullingOrderInfo));
}
else
{
///车辆编号
materialPullingOrderInfo.VehicheModelNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Select(d => d.VehicheModelNo).FirstOrDefault();
///生产订单号
materialPullingOrderInfo.OrderNo = materialPullingOrderInfo.MaterialPullingOrderDetailInfos.Select(d => d.OrderNo).FirstOrDefault();
///物料成套拉动主表
stringBuilder.AppendFormat(CreateCompletePullOrderSql(JisPullGuid, materialPullingOrderInfo));
}
///仓储衔接
stringBuilder.AppendFormat(MaterialPullingCommonBLL.Handler(materialPullingOrderInfo, loginUser));
return(stringBuilder.ToString());
}
/// <summary>
/// SyncMaterialReservation
/// </summary>
/// <param name="loginUser"></param>
public static void Sync(string loginUser)
{
List <SapMaterialReservationInfo> sapMaterialReservationInfos = new SapMaterialReservationBLL().GetListByPage("" +
"DATEDIFF(MINUTE,[CREATE_DATE],GETDATE()) > " + sapMaterialReservationSyncDelayMinute + " and " +
"[PROCESS_FLAG] in (" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Resend + ")", "[ID]", 1, int.MaxValue, out int dataCnt);
if (dataCnt == 0)
{
return;
}
///带有库存地点信息的存储区数据
///TODO:存储区基础数据维护时限制SAP库存地点编号不能重复
List <ZonesInfo> zonesInfos = new ZonesBLL().GetList("len([STOCK_PLACE_NO]) > 0", string.Empty);
///供应商信息
List <SupplierInfo> supplierInfos = new SupplierBLL().GetListForInterfaceDataSync(sapMaterialReservationInfos.Select(d => d.Lifnr).ToList());
///物料基础信息
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(sapMaterialReservationInfos.Select(d => d.Matnr).ToList());
///物料仓储信息
List <PartsStockInfo> partsStockInfos = new PartsStockBLL().GetList("[PART_NO] in ('" + string.Join("','", sapMaterialReservationInfos.Select(d => d.Matnr).ToArray()) + "')", string.Empty);
///已处理完成的ID
List <long> dealedIds = new List <long>();
StringBuilder stringBuilder = new StringBuilder();
///Bwart-移动类型
///Kostl-成本中心
///Lgort-中转库存地点
///Umlgo-接收库存地点
///Wempf-收货方
///Lifnr-供应商
///Rsnum-预留单号
///Ebeln-采购订单号
///Bdter-需求日期
var gSapMaterialReservationInfos = from p in sapMaterialReservationInfos
group p by new { p.Bwart, p.Kostl, p.Lgort, p.Umlgo, p.Wempf, p.Lifnr, p.Rsnum, p.Ebeln, p.Bdter } into g
select new { g.Key };
foreach (var gSapMaterialReservationInfo in gSapMaterialReservationInfos)
{
///校验接收库存地点
ZonesInfo tZone = zonesInfos.FirstOrDefault(d => d.StockPlaceNo == gSapMaterialReservationInfo.Key.Umlgo);
if (!string.IsNullOrEmpty(gSapMaterialReservationInfo.Key.Umlgo) && tZone == null)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000418'," +///库存地点不存在
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [EBELN] = '" + gSapMaterialReservationInfo.Key.Ebeln + "';");
continue;
}
///校验中转库存地点
ZonesInfo sZone = zonesInfos.FirstOrDefault(d => d.StockPlaceNo == gSapMaterialReservationInfo.Key.Lgort);
if (!string.IsNullOrEmpty(gSapMaterialReservationInfo.Key.Lgort) && sZone == null)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000418'," +///库存地点不存在
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [EBELN] = '" + gSapMaterialReservationInfo.Key.Ebeln + "';");
continue;
}
///校验供应商
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == gSapMaterialReservationInfo.Key.Lifnr);
if (!string.IsNullOrEmpty(gSapMaterialReservationInfo.Key.Lifnr) && supplierInfo == null)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000239'," +///供应商数据错误
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [EBELN] = '" + gSapMaterialReservationInfo.Key.Ebeln + "';");
continue;
}
///是否存在单据明细数据
List <SapMaterialReservationInfo> sapMaterialReservations = sapMaterialReservationInfos.Where(d => d.Ebeln == gSapMaterialReservationInfo.Key.Ebeln).ToList();
if (sapMaterialReservations.Count == 0)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000084'," +///数据错误
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [EBELN] = '" + gSapMaterialReservationInfo.Key.Ebeln + "';");
continue;
}
#region TT_MPM_SAP_PURCHASE_ORDER
///采购订单号默认为SAP采购订单号
string orderCode = gSapMaterialReservationInfo.Key.Ebeln;
///如果采购订单号为空，则以预留单号作为采购订单号
///TODO:后期考虑这种形式直接写入供应商退货单表
if (string.IsNullOrEmpty(orderCode))
{
orderCode = gSapMaterialReservationInfo.Key.Rsnum;
}
SapPurchaseOrderInfo sapPurchaseOrderInfo = new SapPurchaseOrderInfo();
sapPurchaseOrderInfo.Fid = Guid.NewGuid();
sapPurchaseOrderInfo.OrderCode = orderCode;
sapPurchaseOrderInfo.SWmNo = sZone == null ? string.Empty : sZone.WmNo;
sapPurchaseOrderInfo.SZoneNo = sZone == null ? string.Empty : sZone.ZoneNo;
sapPurchaseOrderInfo.TWmNo = tZone == null ? string.Empty : tZone.WmNo;
sapPurchaseOrderInfo.TZoneNo = tZone == null ? string.Empty : tZone.ZoneNo;
sapPurchaseOrderInfo.RequireDate = gSapMaterialReservationInfo.Key.Bdter;
sapPurchaseOrderInfo.SupplierNum = supplierInfo == null ? string.Empty : supplierInfo.SupplierNum;
sapPurchaseOrderInfo.SupplierSname = supplierInfo == null ? string.Empty : supplierInfo.SupplierSname;
sapPurchaseOrderInfo.SupplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName;
sapPurchaseOrderInfo.CustCode = gSapMaterialReservationInfo.Key.Wempf;
sapPurchaseOrderInfo.SapBwart = gSapMaterialReservationInfo.Key.Bwart;
sapPurchaseOrderInfo.SapKostl = gSapMaterialReservationInfo.Key.Kostl;
sapPurchaseOrderInfo.SapLgort = gSapMaterialReservationInfo.Key.Lgort;
sapPurchaseOrderInfo.SapUmlgo = gSapMaterialReservationInfo.Key.Umlgo;
sapPurchaseOrderInfo.SapWempf = gSapMaterialReservationInfo.Key.Wempf;
sapPurchaseOrderInfo.SapLifnr = gSapMaterialReservationInfo.Key.Lifnr;
sapPurchaseOrderInfo.SapRsnum = gSapMaterialReservationInfo.Key.Rsnum;
sapPurchaseOrderInfo.SapEbeln = gSapMaterialReservationInfo.Key.Ebeln;
sapPurchaseOrderInfo.Status = (int)PullOrderStatusConstants.Released;
sapPurchaseOrderInfo.CreateUser = loginUser;
stringBuilder.AppendLine(SapPurchaseOrderDAL.GetInsertSql(sapPurchaseOrderInfo));
#endregion
List <SapPurchaseOrderDetailInfo> sapPurchaseOrderDetailInfos = new List <SapPurchaseOrderDetailInfo>();
foreach (var sapMaterialReservation in sapMaterialReservations)
{
///校验物料基础信息
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == sapMaterialReservation.Matnr);
if (maintainPartsInfo == null)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000417', " +///物料信息数据错误
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = '" + sapMaterialReservation.Id + "';");
continue;
}
///中转库存地点的物料仓储信息
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d =>
d.PartNo == maintainPartsInfo.PartNo &&
d.SupplierNum == supplierInfo.SupplierNum &&
d.ZoneNo == sZone.ZoneNo &&
d.WmNo == sZone.WmNo);
if (!string.IsNullOrEmpty(gSapMaterialReservationInfo.Key.Lgort) && partsStockInfo == null)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000241'," + ///物料仓储信息数据错误
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + sapMaterialReservation.Id + ";");
continue;
}
///接收库存地点的物料仓储信息
if (tZone != null)
{
PartsStockInfo tPartsStockInfo = partsStockInfos.FirstOrDefault(d =>
d.PartNo == maintainPartsInfo.PartNo &&
d.SupplierNum == supplierInfo.SupplierNum &&
d.ZoneNo == tZone.ZoneNo &&
d.WmNo == tZone.WmNo);
if (tPartsStockInfo == null)
{
stringBuilder.AppendLine("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000241'," +///物料仓储信息数据错误
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + sapMaterialReservation.Id + ";");
continue;
}
}
#region TT_MPM_SAP_PURCHASE_ORDER_DETAIL
SapPurchaseOrderDetailInfo sapPurchaseOrderDetailInfo = new SapPurchaseOrderDetailInfo();
sapPurchaseOrderDetailInfo.OrderFid = sapPurchaseOrderInfo.Fid;
sapPurchaseOrderDetailInfo.PartNo = maintainPartsInfo.PartNo;
sapPurchaseOrderDetailInfo.PartCname = maintainPartsInfo.PartCname;
sapPurchaseOrderDetailInfo.PartEname = maintainPartsInfo.PartEname;
sapPurchaseOrderDetailInfo.PartQty = sapMaterialReservation.Menge;
sapPurchaseOrderDetailInfo.PartPurchaseUom = maintainPartsInfo.PartUnits;
sapPurchaseOrderDetailInfo.PartUom = maintainPartsInfo.PartUnits;
sapPurchaseOrderDetailInfo.Package = partsStockInfo.InboundPackage;
sapPurchaseOrderDetailInfo.PackageModel = partsStockInfo.InboundPackageModel;
if (partsStockInfo.InboundPackage.GetValueOrDefault() > 0)
{
sapPurchaseOrderDetailInfo.RequirePackageQty = Convert.ToInt32(Math.Ceiling(decimal.Parse(sapMaterialReservation.Menge.GetValueOrDefault().ToString()) / partsStockInfo.InboundPackage.GetValueOrDefault()));
}
sapPurchaseOrderDetailInfo.SapMenge = sapMaterialReservation.Menge;
sapPurchaseOrderDetailInfo.SapRsnum = sapMaterialReservation.Rsnum;
if (!int.TryParse(sapMaterialReservation.Rspos, out int converintRspos))
{
throw new Exception("MC:0x00000397");///预留行号错误
}
sapPurchaseOrderDetailInfo.SapRspos = converintRspos;
sapPurchaseOrderDetailInfo.SapEbeln = sapMaterialReservation.Ebeln;
sapPurchaseOrderDetailInfo.SapEbelp = sapMaterialReservation.Ebelp;
sapPurchaseOrderDetailInfo.SapBwart = sapMaterialReservation.Bwart;
sapPurchaseOrderDetailInfo.SapKostl = sapMaterialReservation.Kostl;
sapPurchaseOrderDetailInfo.SapLgort = sapMaterialReservation.Lgort;
sapPurchaseOrderDetailInfo.SapUmlgo = sapMaterialReservation.Umlgo;
sapPurchaseOrderDetailInfo.SapWempf = sapMaterialReservation.Wempf;
sapPurchaseOrderDetailInfo.SapLifnr = sapMaterialReservation.Lifnr;
sapPurchaseOrderDetailInfo.Status = (int)PullOrderStatusConstants.Released;
sapPurchaseOrderDetailInfo.CreateUser = loginUser;
stringBuilder.AppendLine(SapPurchaseOrderDetailDAL.GetInsertSql(sapPurchaseOrderDetailInfo));
#endregion
sapPurchaseOrderDetailInfos.Add(sapPurchaseOrderDetailInfo);
dealedIds.Add(sapMaterialReservation.Id);
}
#region 单据衔接
if (sapPurchaseOrderDetailInfos.Count > 0)
{
int orderType = (int)SapPurchaseOrderTypeConstants.PurchaseOrder;
///若SAP预留单号不为空时，系统认为是预留订单
//todo 0是否为空
if (!string.IsNullOrEmpty(sapPurchaseOrderInfo.SapRsnum) && sapPurchaseOrderInfo.SapRsnum.ToString() != "0")
{
orderType = (int)SapPurchaseOrderTypeConstants.ReservationOrder;
}
///若SAP采购订单号为空时，系统认为是物料退货
if (string.IsNullOrEmpty(sapPurchaseOrderInfo.SapEbeln))
{
orderType = (int)SapPurchaseOrderTypeConstants.ReturnOrder;
}
MaterialPullingOrderInfo mpOrder = new MaterialPullingOrderInfo();
mpOrder.OrderNo = sapPurchaseOrderInfo.OrderCode;
mpOrder.PartBoxCode = string.Empty; ///零件类2
mpOrder.PartBoxName = string.Empty; ///零件类名称3
mpOrder.Plant = (sZone == null ? string.Empty : sZone.Plant); ///工厂4,TODO:是否增加工厂字段
mpOrder.Workshop = string.Empty; ///车间5
mpOrder.AssemblyLine = string.Empty; ///流水线6
mpOrder.SupplierNum = sapPurchaseOrderInfo.SupplierNum; ///供应商代码7
mpOrder.SupplierName = sapPurchaseOrderInfo.SupplierName; ///供应商名称
mpOrder.SourceZoneNo = sapPurchaseOrderInfo.TZoneNo; ///接收存储区
mpOrder.SourceWmNo = sapPurchaseOrderInfo.TWmNo; ///接收仓库
mpOrder.TargetZoneNo = sapPurchaseOrderInfo.SZoneNo; ///中转存储区
mpOrder.TargetWmNo = sapPurchaseOrderInfo.SWmNo; ///中转仓库
mpOrder.TargetDock = string.Empty; ///道口12,TODO:是否增加道口字段
mpOrder.PlanShippingTime = sapPurchaseOrderInfo.RequireDate; ///建议交货时间
mpOrder.PlanDeliveryTime = sapPurchaseOrderInfo.RequireDate; ///预计到厂时间
mpOrder.PublishTime = DateTime.Now;
mpOrder.OrderType = orderType; ///SAP订单类型
mpOrder.PullMode = (int)PullModeConstants.PurchaseOrder;
mpOrder.MaterialPullingOrderDetailInfos = (from m in sapPurchaseOrderDetailInfos
select new MaterialPullingOrderDetailInfo
{
OrderNo = sapPurchaseOrderInfo.OrderCode, ///拉动单号1
SupplierNum = sapPurchaseOrderInfo.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
Uom = m.PartUom, ///计量单位6
PackageQty = m.Package.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.PackageModel, ///入库包装编号8
RequirePackageQty = m.RequirePackageQty.GetValueOrDefault(), ///需求包装数量9
RequirePartQty = m.PartQty.GetValueOrDefault(), ///需求物料数量10
SourceWmNo = sapPurchaseOrderInfo.TWmNo, ///接收仓库
SourceZoneNo = sapPurchaseOrderInfo.TZoneNo, ///接收存储区
TargetWmNo = sapPurchaseOrderInfo.SWmNo, ///中转仓库
TargetZoneNo = sapPurchaseOrderInfo.SZoneNo ///中转存储区
}).ToList();
///执行单据衔接
stringBuilder.AppendLine(MaterialPullingCommonBLL.Handler(mpOrder, loginUser));
}
#endregion
}
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
stringBuilder.Append("update [LES].[TI_IFM_SAP_MATERIAL_RESERVATION] " +
"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>
/// 发布(提交)
/// </summary>
/// <param name="id"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool ReleaseInfos(List <string> rowsKeyValues, string loginUser)
{
///入库单
List <VmiReceiveInfo> vmiReceiveInfos = dal.GetList("[ID] in (" + string.Join(",", rowsKeyValues.ToArray()) + ")", "[ID]");
if (vmiReceiveInfos.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
///入库单明细
List <VmiReceiveDetailInfo> vmiReceiveDetailInfos = new VmiReceiveDetailDAL().GetList("[RECEIVE_FID] in ('" + string.Join("','", vmiReceiveInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "')", "[ID]");
if (vmiReceiveDetailInfos.Count == 0)
{
throw new Exception("MC:0x00000367");///入库单没有明细
}
///获取系统配置
Dictionary <string, string> configs = new ConfigDAL().GetValuesByCodes(new string[] {
"RELEASE_VMI_RECEIVE_ACTUAL_QTY_EQUALS_REQUIRED",
"MANUAL_VMI_RECEIVE_ORDER_RELEASE_CREATE_BARCODE",
"RELEASE_VMI_RECEIVE_LOAD_PART_INSPECTION_MODE"
});
///已生成的标签
List <BarcodeInfo> barcodeInfos = new BarcodeDAL().GetList("" +
"[BARCODE_STATUS] = " + (int)BarcodeStatusConstants.Created + " and " +
"[CREATE_SOURCE_FID] in ('" + string.Join("','", vmiReceiveDetailInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "')", string.Empty);
///执行语句
StringBuilder @string = new StringBuilder();
foreach (var vmiReceiveInfo in vmiReceiveInfos)
{
///一般手工创建入库单会用到此功能，入库单必须为10.已创建状态⑫
if (vmiReceiveInfo.Status.GetValueOrDefault() != (int)WmmOrderStatusConstants.Created)
{
throw new Exception("MC:0x00000683");///状态必须为已创建
}
///VMI入库单明细
List <VmiReceiveDetailInfo> vmiReceiveDetails = vmiReceiveDetailInfos.Where(d => d.ReceiveFid.GetValueOrDefault() == vmiReceiveInfo.Fid.GetValueOrDefault()).ToList();
///VMI入库单对应的标签数据
List <BarcodeInfo> barcodes = barcodeInfos.Where(d => vmiReceiveDetails.Select(f => f.Fid.GetValueOrDefault()).Contains(d.CreateSourceFid.GetValueOrDefault())).ToList();
///将VMI入库单明细对象转换为普通入库单对象，方便后续函数处理
List <ReceiveDetailInfo> receiveDetailInfos = ReceiveDetailBLL.GetReceiveDetailInfos(vmiReceiveDetails);
///手工创建VMI入库单时创建条码标签
configs.TryGetValue("MANUAL_VMI_RECEIVE_ORDER_RELEASE_CREATE_BARCODE", out string manual_vmi_receive_order_release_create_barcode);
if (!string.IsNullOrEmpty(manual_vmi_receive_order_release_create_barcode) && manual_vmi_receive_order_release_create_barcode.ToLower() == "true")
{
@string.AppendLine(MaterialPullingCommonBLL.GetCreateBarcodesSql(receiveDetailInfos, barcodes, loginUser));
}
///
ReceiveInfo receiveInfo = ReceiveBLL.CreateReceiveInfo(loginUser);
///VmiReceiveInfo -> ReceiveInfo
ReceiveBLL.GetReceiveInfo(vmiReceiveInfo, ref receiveInfo);
///VMI入库单发布时是否加载物料检验模式,TODO:此处函数内有与入库单共用的系统配置
configs.TryGetValue("RELEASE_VMI_RECEIVE_LOAD_PART_INSPECTION_MODE", out string release_vmi_receive_load_part_inspection_mode);
if (!string.IsNullOrEmpty(release_vmi_receive_load_part_inspection_mode) && release_vmi_receive_load_part_inspection_mode.ToLower() == "true")
{
@string.AppendLine(PartInspectionModeBLL.LoadInspectionMode(ref receiveInfo, ref receiveDetailInfos, loginUser));
}
///行号更新
int rowNo = 0;
///发布VMI入库单时实收数量默认等于需求数量
configs.TryGetValue("RELEASE_VMI_RECEIVE_ACTUAL_QTY_EQUALS_REQUIRED", out string release_vmi_receive_actual_qty_equals_required);
foreach (var receiveDetailInfo in receiveDetailInfos)
{
if (!string.IsNullOrEmpty(release_vmi_receive_actual_qty_equals_required) && release_vmi_receive_actual_qty_equals_required.ToLower() == "true")
{
if (receiveDetailInfo.ActualBoxNum == null)
{
receiveDetailInfo.ActualBoxNum = receiveDetailInfo.RequiredBoxNum;
}
if (receiveDetailInfo.ActualQty == null)
{
receiveDetailInfo.ActualQty = receiveDetailInfo.RequiredQty;
}
}
///更新入库单明细需要注意不能覆盖明细中原内容
@string.AppendLine("update [LES].[TT_WMM_VMI_RECEIVE_DETAIL] set " +
"[ROW_NO] = " + ++rowNo + "," +
(receiveDetailInfo.ActualBoxNum == null ? string.Empty : "[ACTUAL_BOX_NUM] = " + receiveDetailInfo.ActualBoxNum.GetValueOrDefault() + ",") +
(receiveDetailInfo.ActualQty == null ? string.Empty : "[ACTUAL_QTY] = " + receiveDetailInfo.ActualQty.GetValueOrDefault() + ",") +
(receiveDetailInfo.InspectionMode == null ? string.Empty : "[INSPECTION_MODE] = " + receiveDetailInfo.InspectionMode.GetValueOrDefault() + ",") +
(string.IsNullOrEmpty(receiveDetailInfo.SupplierNum) ? "[SUPPLIER_NUM] = N'" + receiveInfo.SupplierNum + "'," : string.Empty) +
"[WM_NO] = N'" + receiveInfo.WmNo + "'," +
"[ZONE_NO] = N'" + receiveInfo.ZoneName + "'," +
"[TARGET_WM] = N'" + receiveInfo.WmNo + "'," +
"[TARGET_ZONE] = N'" + receiveInfo.ZoneNo + "'," +
(string.IsNullOrEmpty(receiveDetailInfo.RunsheetNo) ? "[RUNSHEET_NO] = N'" + receiveInfo.RunsheetNo + "'," : string.Empty) +
"[MODIFY_DATE] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "' where " +
"[ID] = " + receiveDetailInfo.Id + ";");
}
///更新入库单
@string.AppendLine("update [LES].[TT_WMM_VMI_RECEIVE] set " +
"[SUM_PART_QTY] = " + vmiReceiveDetails.Sum(d => d.RequiredQty.GetValueOrDefault()) + "," +
"[SUM_OF_PRICE] = " + vmiReceiveDetails.Sum(d => d.PartPrice.GetValueOrDefault()) + "," +
"[SUM_WEIGHT] = " + vmiReceiveDetails.Sum(d => d.SumWeight.GetValueOrDefault()) + "," +
"[SUM_VOLUME] = " + vmiReceiveDetails.Sum(d => d.SumVolume.GetValueOrDefault()) + "," +
"[SUM_PACKAGE_QTY] = " + vmiReceiveDetails.Sum(d => d.RequiredBoxNum.GetValueOrDefault()) + "," +
//"[INSPECTION_FLAG] = " + (receiveInfo.InspectionFlag.GetValueOrDefault() ? 1 : 0) + "," +
"[STATUS] = " + (int)WmmOrderStatusConstants.Published + "," +
"[MODIFY_USER] = N'" + loginUser + "' ," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = " + vmiReceiveInfo.Id + ";");
}
///删除已删除VMI入库单明细的标签
@string.AppendLine("update [LES].[TT_WMM_BARCODE] " +
"set [VALID_FLAG] = 0 " +
"where [CREATE_SOURCE_FID] in (select [FID] from [LES].[TT_WMM_VMI_RECEIVE_DETAIL] with(nolock) " +
"where [VALID_FLAG] = 0 and [RECEIVE_FID] in ('" + string.Join("','", vmiReceiveInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "'));");
///执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
return(true);
}
/// <summary>
/// 对于MPM-036逻辑进行函数封装
/// </summary>
/// <param name="emergencyPullingCartInfos"></param>
/// <param name="loginUser"></param>
public static string Handler(List <EmergencyPullingCartInfo> emergencyPullingCartInfos, string loginUser)
{
///执行语句
StringBuilder @string = new StringBuilder();
///获取系统配置是否允许过量提前拉动
string allowedFlag = new ConfigDAL().GetValueByCode("ENABLE_EXCEED_COUNTER_QTY_WHEN_ADVANCE_PULL");
///接收紧急拉动购物车对象集合进行处理
if (emergencyPullingCartInfos.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
///对应的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardBLL().GetList("" +
" and [STATUS] =" + (int)BasicDataStatusConstants.Enable + "" +
" and [FID] in ('" + string.Join("','", emergencyPullingCartInfos.Select(d => d.LogisticStandardFid).ToArray()) + "')", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
throw new Exception("MC:0x00000213");///物料拉动信息数据错误
}
///首先需要将其按照拉动方式、零件类进行分组，每个分组产生一张紧急拉动单
var cartGroups = emergencyPullingCartInfos.GroupBy(d => new { d.PullMode, d.PartBoxCode }).ToList();
#region 手工拉动 PLAN
var planCartGroups = cartGroups.Where(d => d.Key.PullMode == (int)PullModeConstants.Plan).ToList();
///计划拉动零件类
List <PlanPartBoxInfo> planPartBoxInfos = new PlanPartBoxBLL().GetList("" +
" and [STATUS] = " + (int)BasicDataStatusConstants.Enable + "" +
" and [PART_BOX_CODE] in ('" + string.Join("','", planCartGroups.Select(d => d.Key.PartBoxCode).ToArray()) + "')", string.Empty);
foreach (var planCartGroup in planCartGroups)
{
///从参数集合从提取对应的紧急拉动购物车集合
List <EmergencyPullingCartInfo> pullingCartInfos = emergencyPullingCartInfos.Where(d =>
d.PullMode == planCartGroup.Key.PullMode && d.PartBoxCode == planCartGroup.Key.PartBoxCode).ToList();
if (pullingCartInfos.Count == 0)
{
continue;
}
///计划零件类
PlanPartBoxInfo planPartBoxInfo = planPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == planCartGroup.Key.PartBoxCode);
if (planPartBoxInfo == null)
{
continue;
}
///计划拉动-->仓储衔接主表
MaterialPullingOrderInfo materialPulling = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
///计划拉动单主表
PlanPullOrderInfo planPullOrderInfo = PlanPullOrderBLL.CreatePlanPullOrder(loginUser);
///OrderType 拉动单类型
planPullOrderInfo.OrderType = (int)PullOrderTypeConstants.Emergency;
///PlanPartBoxInfo-->PlanPullOrderInfo
PlanPullOrderBLL.GetPlanPullOrder(planPartBoxInfo, ref planPullOrderInfo);
///计划拉动单主表sql
@string.AppendLine(PlanPullOrderDAL.GetInsertSql(planPullOrderInfo));
///PlanPullOrderInfo -> MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPlanPullingOrderInfo(planPullOrderInfo, ref materialPulling);
foreach (EmergencyPullingCartInfo planCartInfo in pullingCartInfos)
{
///物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == planCartInfo.LogisticStandardFid);
if (maintainInhouseLogisticStandardInfo == null)
{
continue;
}
///物料包装数量在此需要根据单包装数量以及物料需求数量进行向上圆整计算
///REQUIRED_BOX_QTY:物料包装数量 PULL_PACKAGE_QTY:单包装数量 REQUIRED_PART_QTY:物料需求数量
if (planCartInfo.PullPackageQty.GetValueOrDefault() == 0)
{
continue; ///O不能做被除数
}
planCartInfo.RequiredBoxQty = Convert.ToInt32(Math.Ceiling(planCartInfo.RequiredPartQty.GetValueOrDefault() / planCartInfo.PullPackageQty.GetValueOrDefault()));
///仓储衔接明细表
MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
///计划拉动单明细
PlanPullOrderDetailInfo planPullOrderDetailInfo = PlanPullOrderDetailBLL.CreatePlanPullOrderDetail(loginUser);
///MaintainInhouseLogisticStandardInfo -> PlanPullOrderDetailInfo
PlanPullOrderDetailBLL.GetPlanPullOrderDetail(maintainInhouseLogisticStandardInfo, ref planPullOrderDetailInfo);
///TwdPullOrderInfo -> TwdPullOrderDetailInfo
PlanPullOrderDetailBLL.GetPlanPullOrderDetailInfo(planPullOrderInfo, ref planPullOrderDetailInfo);
///RequiredPackageQty
planPullOrderDetailInfo.RequiredPackageQty = planCartInfo.RequiredBoxQty.GetValueOrDefault();
///RequiredPartQty
planPullOrderDetailInfo.RequiredPartQty = planCartInfo.RequiredPartQty.GetValueOrDefault();
///计划拉动明细sql
@string.AppendLine(PlanPullOrderDetailDAL.GetInsertSql(planPullOrderDetailInfo));
///PlanPullOrderDetailInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderDetail(planPullOrderDetailInfo, ref detailInfo);
///仓储明细集合Add
materialPulling.MaterialPullingOrderDetailInfos.Add(detailInfo);
}
///拉动单生成后需要调用拉动仓储衔接函数获取语句
@string.AppendLine(MaterialPullingCommonBLL.Handler(materialPulling, loginUser));
}
#endregion
#region 提前拉动 TWD
var twdCartGroups = cartGroups.Where(d => d.Key.PullMode == (int)PullModeConstants.Pcs || d.Key.PullMode == (int)PullModeConstants.Twd).ToList();
///TWD计数器
List <TwdCounterInfo> twdCounterInfos = new TwdCounterBLL().GetList("" +
"[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " +
"[PART_PULL_FID] in ('" + string.Join("','", maintainInhouseLogisticStandardInfos.Select(d => d.Fid).ToArray()) + "') and " +
"isnull([CURRENT_QTY],0) > 0", string.Empty);
///TWD零件类
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxBLL().GetList("" +
"[STATUS] =" + (int)BasicDataStatusConstants.Enable + " and " +
"[PART_BOX_CODE] in ('" + string.Join("','", twdCartGroups.Select(d => d.Key.PartBoxCode).ToArray()) + "')", string.Empty);
foreach (var twdCartGroup in twdCartGroups)
{
///从参数集合从提取对应的紧急拉动购物车集合
List <EmergencyPullingCartInfo> pullingCartInfos = emergencyPullingCartInfos.Where(d =>
d.PullMode == twdCartGroup.Key.PullMode && d.PartBoxCode == twdCartGroup.Key.PartBoxCode).ToList();
if (pullingCartInfos.Count == 0)
{
continue;
}
///TWD零件类
TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == twdCartGroup.Key.PartBoxCode);
if (twdPartBoxInfo == null)
{
continue;
}
///触发层级拉动的集合
List <EmergencyPullingCartInfo> pullingLevelCartInfos = pullingCartInfos.Where(d => d.TriggerPullFlag == true).ToList();
foreach (EmergencyPullingCartInfo pullingLevelCartInfo in pullingLevelCartInfos)
{
///需要根据其物料拉动信息外键获取对应的计数器数据（状态必须为已启用）
TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartPullFid == pullingLevelCartInfo.LogisticStandardFid.GetValueOrDefault() &&
d.Status == (int)BasicDataStatusConstants.Enable);
if (twdCounterInfo == null)
{
continue;
}
///在此之前需要根据物料图号、供应商、层级仓库、层级存储区在物料拉动信息中获取匹配的目标仓库、目标存储区数据
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d =>
d.PartNo == pullingLevelCartInfo.PartNo &&
d.SupplierNum == pullingLevelCartInfo.SupplierNum &&
d.WmNo == pullingLevelCartInfo.TriggerWmNo &&
d.ZoneNo == pullingLevelCartInfo.TriggerZoneNo);
///触发层级拉动
@string.AppendFormat(TwdCounterBLL.LevelPullCounter(maintainInhouseLogisticStandardInfo, pullingLevelCartInfo.RequiredPartQty.GetValueOrDefault(), loginUser, twdCounterInfo.Fid.GetValueOrDefault(), twdPartBoxInfo));
}
///提前拉动的集合
List <EmergencyPullingCartInfo> advancePullCartInfos = pullingCartInfos.Where(d =>
d.TriggerPullFlag == false && d.EmergencyPullMode == (int)EmergencyPullModeConstants.AdvancePull).ToList();
if (advancePullCartInfos.Count == 0)
{
continue;
}
///仓储衔接主表
MaterialPullingOrderInfo materialPulling = MaterialPullingCommonBLL.CreateMaterialPullingOrderInfo();
///TWD拉动单主表
TwdPullOrderInfo twdPullOrderInfo = TwdPullOrderBLL.CreateTwdPullOrderInfo(loginUser);
///TwdPartBoxInfo -> TwdPullOrderInfo
TwdPullOrderBLL.GetTwdPullOrderInfo(twdPartBoxInfo, ref twdPullOrderInfo);
///TWD拉动单主表sql
@string.AppendLine(TwdPullOrderDAL.GetInsertSql(twdPullOrderInfo));
///TwdPullOrderInfo-->MaterialPullingOrderInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderInfo(twdPullOrderInfo, ref materialPulling);
int rowNo = 0; ///行号
///逐条循环每个购物车
foreach (EmergencyPullingCartInfo advancePullCartInfo in advancePullCartInfos)
{
///需要根据其物料拉动信息外键获取对应的计数器数据（状态必须为已启用）
///TT_MPM_TWD_COUNTER TWD计数器
TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartPullFid == advancePullCartInfo.LogisticStandardFid.GetValueOrDefault() &&
d.Status == (int)BasicDataStatusConstants.Enable);
if (twdCounterInfo == null)
{
continue;
}
///物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == advancePullCartInfo.LogisticStandardFid);
if (maintainInhouseLogisticStandardInfo == null)
{
continue;
}
///物料包装数量在此需要根据单包装数量以及物料需求数量进行向上圆整计算
///REQUIRED_BOX_QTY:物料包装数量 PULL_PACKAGE_QTY:单包装数量 REQUIRED_PART_QTY:物料需求数量
if (advancePullCartInfo.PullPackageQty.GetValueOrDefault() == 0)
{
continue; ///O不能做被除数
}
advancePullCartInfo.RequiredBoxQty = Convert.ToInt32(Math.Ceiling(advancePullCartInfo.RequiredPartQty.GetValueOrDefault() / advancePullCartInfo.PullPackageQty.GetValueOrDefault()));
///若该系统配置标记为false时、计数器当前累计数量不允许小于购物车物料需求数量，
if (allowedFlag.ToLower() == "false" && twdCounterInfo.CurrentQty.GetValueOrDefault() < advancePullCartInfo.RequiredPartQty.GetValueOrDefault())
{
continue;
}
///仓储衔接明细表
MaterialPullingOrderDetailInfo detailInfo = MaterialPullingCommonBLL.CreateMaterialPullingOrderDetailInfo();
///TWD明细表
TwdPullOrderDetailInfo pullOrderDetailInfo = TwdPullOrderDetailBLL.CreateTwdPullOrderDetailInfo(loginUser);
///MaintainInhouseLogisticStandardInfo -> TwdPullOrderDetailInfo
TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(maintainInhouseLogisticStandardInfo, ref pullOrderDetailInfo);
///TwdPullOrderInfo -> TwdPullOrderDetailInfo
TwdPullOrderDetailBLL.GetTwdPullOrderDetailInfo(twdPullOrderInfo, ref pullOrderDetailInfo);
///ROW_NO,行号
pullOrderDetailInfo.RowNo = ++rowNo;
///REQUIRED_PACKAGE_QTY,需求包装数
pullOrderDetailInfo.RequiredPackageQty = advancePullCartInfo.RequiredBoxQty.GetValueOrDefault();
///REQUIRED_PART_QTY,需求物料数量
pullOrderDetailInfo.RequiredPartQty = advancePullCartInfo.RequiredPartQty.GetValueOrDefault();
///TWD明细表sql
@string.AppendLine(TwdPullOrderDetailDAL.GetInsertSql(pullOrderDetailInfo));
///TwdPullOrderDetailInfo -> MaterialPullingOrderDetailInfo
MaterialPullingCommonBLL.GetMaterialPullingOrderDetailInfo(pullOrderDetailInfo, ref detailInfo);
///仓储衔接集合Add
materialPulling.MaterialPullingOrderDetailInfos.Add(detailInfo);
///否则直接对计数器的当前累计数量按购物车物料需求数量进行扣减
@string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] " +
"set [CURRENT_QTY] = isnull([CURRENT_QTY],0) - " + advancePullCartInfo.RequiredPartQty.GetValueOrDefault() + "," +
"[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 - advancePullCartInfo.RequiredPartQty.GetValueOrDefault();
///SOURCE_DATA,目视来源数据
twdCounterLogInfo.SourceData = twdPullOrderInfo.OrderCode;
///SOURCE_DATA_FID,数据来源外键
twdCounterLogInfo.SourceDataFid = twdPullOrderInfo.Fid;
///SOURCE_DATA_TYPE,数据来源类型
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.AdvancePull;
///计数器日志sql
@string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
}
///拉动单生成后需要调用拉动仓储衔接函数获取语句
@string.AppendLine(MaterialPullingCommonBLL.Handler(materialPulling, loginUser));
}
#endregion
return(@string.ToString());
}
