/// <summary>
/// 同步供应商基础数据
/// </summary>
/// <param name="sapSupplierQuotaInfos"></param>
/// <param name="loginUser"></param>
private static string GetSyncSupplierSql(List <SapSupplierQuotaInfo> sapSupplierQuotaInfos, string loginUser)
{
StringBuilder @string = new StringBuilder();
///获取业务表中要变更的数据集合,准备对比
List <SupplierInfo> supplierInfos = new SupplierBLL().GetListForInterfaceDataSync(sapSupplierQuotaInfos.Select(d => d.Lifnr).ToList());
///
foreach (var sapSupplierQuotaInfo in sapSupplierQuotaInfos)
{
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == sapSupplierQuotaInfo.Lifnr);
if (supplierInfo == null)
{
supplierInfo = new SupplierInfo();
supplierInfo.Fid = Guid.NewGuid();
supplierInfo.SupplierNum = sapSupplierQuotaInfo.Lifnr;
supplierInfo.SupplierName = sapSupplierQuotaInfo.SupplierName;
supplierInfo.SupplierType = (int)SupplierTypeConstants.MaterialSupplier;
supplierInfo.CreateUser = loginUser;
supplierInfo.CreateDate = DateTime.Now;
@string.AppendLine(SupplierDAL.GetInsertSql(supplierInfo));
supplierInfos.Add(supplierInfo);
continue;
}
///更新
@string.AppendLine("update [LES].[TM_BAS_SUPPLIER] " +
"set [SUPPLIER_NAME] = N'" + sapSupplierQuotaInfo.SupplierName + "'," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [FID] = N'" + supplierInfo.Fid.GetValueOrDefault() + "';");
}
return(@string.ToString());
}
/// <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 <WmsVmiTranDetailInfo> wmsVmiTranDetailInfos = new WmsVmiTranDetailBLL().GetList("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", string.Empty);
if (wmsVmiTranDetailInfos.Count == 0)
{
return;
}
StringBuilder @string = new StringBuilder();
///获取仓库信息
List <WarehouseInfo> warehouseInfos = new WarehouseBLL().GetList("" +
"[WAREHOUSE] in ('" + string.Join("','", wmsVmiTranDetailInfos.Select(d => d.VmiWarehouseCode).ToArray()) + "') "
// + " and [WAREHOUSE_TYPE] = " + (int)WarehouseTypeConstants.VMI + ""
, string.Empty);
if (warehouseInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +
"[ID] in (" + string.Join(",", wmsVmiTranDetailInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取供应商信息
List <SupplierInfo> supplierInfos = new SupplierBLL().GetList("" +
"[SUPPLIER_NUM] in ('" + string.Join("','", wmsVmiTranDetailInfos.Select(d => d.SupplierCode).ToArray()) + "') and " +
"[SUPPLIER_TYPE] = " + (int)SupplierTypeConstants.MaterialSupplier + "", string.Empty);
if (supplierInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000229' where " +
"[ID] in (" + string.Join(",", wmsVmiTranDetailInfos.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_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000429' where " +
"[ID] in (" + string.Join(",", wmsVmiTranDetailInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取相关物料基础信息
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetList("[PART_NO] in ('" + string.Join("','", wmsVmiTranDetailInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
///获取相关物料仓储信息
List <PartsStockInfo> partsStockInfos = new PartsStockBLL().GetList("" +
"[PART_NO] in ('" + string.Join("','", wmsVmiTranDetailInfos.Select(d => d.PartNo).ToArray()) + "') and " +
"[WM_NO] in ('" + string.Join("','", warehouseInfos.Select(d => d.Warehouse).ToArray()) + "')", string.Empty);
///
List <long> dealedIds = new List <long>();
foreach (WmsVmiTranDetailInfo wmsVmiTranDetailInfo in wmsVmiTranDetailInfos)
{
var vmiWareHouseInfo = warehouseInfos.FirstOrDefault(fod => fod.Warehouse == wmsVmiTranDetailInfo.VmiWarehouseCode);
if (vmiWareHouseInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +///VMI供应商信息未维护
"[ID] = " + wmsVmiTranDetailInfo.Id + ";");
continue;
}
if (vmiWareHouseInfo.WarehouseType != (int)WarehouseTypeConstants.VMI)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000518' where " +///VMI供应商信息未维护
"[ID] = " + wmsVmiTranDetailInfo.Id + ";");
continue;
}
///VMI供应商关系
VmiSupplierInfo vmiSupplierInfo = vmiSupplierInfos.FirstOrDefault(d => d.SupplierNum == wmsVmiTranDetailInfo.SupplierCode && d.WmNo == wmsVmiTranDetailInfo.VmiWarehouseCode);
if (vmiSupplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000429' where " +///VMI供应商信息未维护
"[ID] = " + wmsVmiTranDetailInfo.Id + ";");
continue;
}
if (!vmiSupplierInfo.VmiFlag.GetValueOrDefault())
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000466' where " +///该供应商未启用WMS功能
"[ID] = " + wmsVmiTranDetailInfo.Id + ";");
continue;
}
if (string.IsNullOrEmpty(vmiSupplierInfo.ZoneNo))
{
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000481' where " +///VMI存储区未配置
"[ID] = " + wmsVmiTranDetailInfo.Id + ";");
continue;
}
///指定存储区
wmsVmiTranDetailInfo.ZoneNo = vmiSupplierInfo.ZoneNo;
///创建交易
TranDetailsInfo tranDetailsInfo = TranDetailsBLL.CreateTranDetailsInfo(loginUser);
///PartsStockInfo -> TranDetailsInfo
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d =>
d.PartNo == wmsVmiTranDetailInfo.PartNo && d.WmNo == vmiSupplierInfo.WmNo && d.ZoneNo == vmiSupplierInfo.ZoneNo);
TranDetailsBLL.GetTranDetailsInfo(partsStockInfo, ref tranDetailsInfo);
///WmsVmiTranDetailInfo -> TranDetailsInfo
wmsVmiTranDetailInfo.Dloc = partsStockInfo.Dloc;
TranDetailsBLL.GetTranDetailsInfo(wmsVmiTranDetailInfo, ref tranDetailsInfo);
///MaintainPartsInfo -> TranDetailsInfo
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == tranDetailsInfo.PartNo);
TranDetailsBLL.GetTranDetailsInfo(maintainPartsInfo, ref tranDetailsInfo);
///SupplierInfo -> TranDetailsInfo
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == tranDetailsInfo.SupplierNum);
TranDetailsBLL.GetTranDetailsInfo(supplierInfo, ref tranDetailsInfo);
///包装数量计算
TranDetailsBLL.CalculateTranDetailsInfo(ref tranDetailsInfo);
///获取库存交易记录的生成语句
@string.AppendLine(TranDetailsDAL.GetInsertSql(tranDetailsInfo));
dealedIds.Add(wmsVmiTranDetailInfo.Id);
}
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
@string.AppendLine("update [LES].[TI_IFM_WMS_VMI_TRAN_DETAIL] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," +
"[PROCESS_TIME] = GETDATE() where " +
"[ID] in (" + string.Join(",", dealedIds.ToArray()) + ");");
}
///执行
using (var 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>
/// 检验模式同步
/// </summary>
/// <returns></returns>
public static void SyncCheckMode(string loginUser)
{
///sql 添加语句
StringBuilder sql = new StringBuilder();
///获取未处理的检验模式中间表数据
List <QmisCheckModeInfo> qmisCheckModeInfos = new QmisCheckModeBLL().GetListByPage("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", "[ID]", 1, 1000, out int dataCnt);
if (dataCnt == 0)
{
return;
}
List <long> dealedIds = new List <long>();
///获取所有未处理状态的物料相关信息
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetList(" [PART_NO] IN ('" + string.Join("','", qmisCheckModeInfos.Select(d => d.PartNo).ToArray()) + "') ", "ID");
///获取所有未处理状态的供应商信息
List <SupplierInfo> supplierInfos = new SupplierBLL().GetList(" [SUPPLIER_NUM] IN ('" + string.Join("','", qmisCheckModeInfos.Select(d => d.SupplierNo).ToArray()) + "')", "ID");
///循环未处理状态的中间表信息
foreach (var qmisCheckModeInfo in qmisCheckModeInfos)
{
///判断是否存在该物料如果不存在修改为挂起状态, 且Common=7x00000017 (物料号不存在)
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == qmisCheckModeInfo.PartNo);
if (maintainPartsInfo == null)
{
sql.Append("UPDATE [LES].[TI_IFM_QMIS_CHECK_MODE] SET [PROCESS_FLAG]=" + (int)ProcessFlagConstants.Suspend + " , [COMMENTS]='7x00000017' WHERE [ID]='" + qmisCheckModeInfo.Id + "';");
continue;
}
///判断是否存在该供应商, 如果不存在修改为挂起状态, 且Common=7x00000018 (供应商不存在)
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == qmisCheckModeInfo.SupplierNo);
if (supplierInfo == null)
{
sql.Append("UPDATE [LES].[TI_IFM_QMIS_CHECK_MODE] SET [PROCESS_FLAG]=" + (int)ProcessFlagConstants.Suspend + " , [COMMENTS]='7x00000018' WHERE [ID]='" + qmisCheckModeInfo.Id + "';");
continue;
}
///检查检验模式, 如果检验模式不存在0,1,2 之间., 修改为挂起状态. 且Common=7x00000019(检验模式不存在) 0:免检；1：抽检；2：批检
if (qmisCheckModeInfo.CheckMode.Trim() != "0" && qmisCheckModeInfo.CheckMode.Trim() != "1" && qmisCheckModeInfo.CheckMode.Trim() != "2")
{
sql.Append("UPDATE [LES].[TI_IFM_QMIS_CHECK_MODE] SET [PROCESS_FLAG]=" + (int)ProcessFlagConstants.Suspend + " , [COMMENTS]='7x00000019' WHERE [ID]='" + qmisCheckModeInfo.Id + "';");
continue;
}
///添加执行语句, 如果存在,就修改检验模式, 如果不存在进行新增
sql.Append("if exists(select * from LES.TM_BAS_PART_INSPECTION_MODE with(nolock) where "
+ "[PART_NO] = N'" + qmisCheckModeInfo.PartNo + "' and "
+ "[SUPPLIER_NUM] = N'" + qmisCheckModeInfo.SupplierNo + "' and "
+ "[VALID_FLAG] = 1) "
+ "UPDATE [LES].[TM_BAS_PART_INSPECTION_MODE] SET "
+ "[INSPECTION_MODE]='" + qmisCheckModeInfo.CheckMode + "'"
+ " WHERE [VALID_FLAG]=1 AND [PART_NO]='" + qmisCheckModeInfo.PartNo + "' AND [SUPPLIER_NUM]='" + qmisCheckModeInfo.SupplierNo + "'; ");
sql.AppendLine();
///添加执行语句, 如果不存在就添加.
sql.AppendFormat("if not exists (select * from LES.TM_BAS_PART_INSPECTION_MODE with(nolock) where "
+ "[PART_NO] = N'{0}' and "
+ "[SUPPLIER_NUM] = N'{1}' and "
+ "[VALID_FLAG] = 1) "
+ "INSERT INTO [LES].[TM_BAS_PART_INSPECTION_MODE] ("
+ "[FID] ,"
+ "[PART_NO] ,"
+ "[SUPPLIER_NUM] ,"
+ "[INSPECTION_MODE] ,"
+ "[VALID_FLAG] ,"
+ "[CREATE_DATE] ,"
+ "[CREATE_USER] ) VALUES ( "
+ "NEWID() ," /// FID - uniqueidentifier
+ "N'{2}' ," /// PART_NO - nvarchar(32)
+ "N'{3}' ," /// SUPPLIER_NUM - nvarchar(32)
+ "{4} ," /// INSPECTION_MODE - int
+ "1 ," /// VALID_FLAG - bit
+ "GETDATE() ," /// CREATE_DATE - datetime
+ "N'{5}' ) ; ", /// CREATE_USER - nvarchar(64)
qmisCheckModeInfo.PartNo,
qmisCheckModeInfo.SupplierNo,
qmisCheckModeInfo.PartNo,
qmisCheckModeInfo.SupplierName,
Convert.ToInt32(qmisCheckModeInfo.CheckMode),
loginUser);
sql.AppendLine();
dealedIds.Add(qmisCheckModeInfo.Id);
}
if (dealedIds.Count > 0)
{
///中间表数据更新为已处理状态, 修改时间,修改人
sql.Append("update [LES].[TI_IFM_QMIS_CHECK_MODE] "
+ "set [PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + ",[PROCESS_TIME] = GETDATE() , [MODIFY_DATE]=GETDATE(),[MODIFY_USER]='" + loginUser
+ "' where [ID] in (" + string.Join(",", dealedIds.ToArray()) + ");");
}
sql.AppendLine();
if (sql.ToString().Length > 0)
{
Log.WriteLogToFile(sql.ToString(), AppDomain.CurrentDomain.BaseDirectory + @"\SQL-Log\", DateTime.Now.ToString("yyyyMMddHHmm"));
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(sql.ToString());
}
}
public void Handler()
{
#region 基础变量
///最近一条的中间表数据状态
int processFlag = 0;
///本次线程已处理的中间表主键
List <long> dealedIds = new List <long>();
//数据库执行语句
StringBuilder @string = new StringBuilder();
///物料需求提前天数
int.TryParse(materialRequireAdvanceDays, out int intMaterialRequrieAdvanceDays);
intMaterialRequrieAdvanceDays = 0 - intMaterialRequrieAdvanceDays;
#endregion
while (processFlag != 10)
{
///开始处理时间
DateTime startExecuteTime = DateTime.Now;
///获取状态⑮为10.未处理的SAP生产订单数据
///因为后续处理过程较为复杂，所以一次获取一条ID主键最靠前的10.未处理或30.挂起或40.逆处理状态数据
///当上一条处理数据为30.挂起状态时需要继续处理下一条，否则执行结束
///也就意味着30.挂起的数据将优先处理且为了保障挂起数据不影响正常未处理数据而设定的逻辑
#region 获取待处理的数据
string textWhere = "[PROCESS_FLAG] in (" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Suspend + "," + (int)ProcessFlagConstants.ConverseProgress + ")";
///集合大于0，排除
if (dealedIds.Count > 0)
{
textWhere += "and [ID] not in (" + string.Join(",", dealedIds.ToArray()) + ")";
}
///
SapProductOrderInfo sapProductOrderInfo = new SapProductOrderBLL().GetTopOneInfo(textWhere, "[ID] asc");
if (sapProductOrderInfo == null)
{
throw new Exception("MC:3x00000015");///没有已启用的SAP生产订单信息
}
if (sapProductOrderInfo.OnlineDate == null)
{
throw new Exception("MC:3x00000033");///SAP生产订单上线日期信息错误
}
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":Start " + sapProductOrderInfo.Aufnr);
///SAP生产订单上线日期
string sapProductOrderDate = sapProductOrderInfo.OnlineDate.GetValueOrDefault().ToString("yyyyMMdd");
///处理状态
processFlag = sapProductOrderInfo.ProcessFlag.GetValueOrDefault();
///
dealedIds.Add(sapProductOrderInfo.Id);
#endregion
#region SAP生产订单物料清单
///获取SAP生产订单中订单号⑤=③对应的SAP生产订单物料清单
SapProductOrderBomInfo sapProductOrderBomInfo = new SapProductOrderBomBLL().GetInfoByAufnr(sapProductOrderInfo.Aufnr);
///若此时未能获取到数据则表示该SAP生产订单的物料清单还未能从SAP成功接收到
if (sapProductOrderBomInfo == null)
{
///需要终止该条生产订单的处理，并且标记为挂起状态⑮
new SapProductOrderBLL().UpdateInfo("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + ",[MODIFY_USER] = N'" + loginUser + "',[MODIFY_DATE] = GETDATE()", sapProductOrderInfo.Id);
continue;
}
#endregion
#region 订单物料清单XML解析
///SAP生产订单物料清单中的物料信息⑦需要XML解析后并逐条以工厂③=②、物料号①=⑦.MATNR、供应商②=⑦.LIFNR
XmlWrapper xmlWrapper = new XmlWrapper(sapProductOrderBomInfo.Matnrs, LoadType.FromString);
List <object> objMatnrs = xmlWrapper.XmlToList("/MatnrsAll/Matnrs", typeof(Matnrs));
if (objMatnrs.Count == 0)
{
throw new Exception("0x00000182");///无订单物料清单
}
List <string> partNos = new List <string>();
List <string> supplierNums = new List <string>();
foreach (Matnrs matnr in objMatnrs)
{
partNos.Add(matnr.Matnr);
supplierNums.Add(matnr.Lifnr);
}
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(partNos);
if (maintainPartsInfos.Count == 0)
{
throw new Exception("0x00000182");///无订单物料清单
}
List <SupplierInfo> supplierInfos = new SupplierBLL().GetListForInterfaceDataSync(supplierNums);
#endregion
///需根据SAP生产订单中的上线日期⑩判定供货计划TT_ATP_SUPPLY_PLAN中的对应日期列是否存在
///日期列的列名规则为yyyyMMdd数据类型为decimal(18,4)，不存在则需要新建列
ProcessSupplyPlanDate(sapProductOrderInfo.OnlineDate.GetValueOrDefault().AddDays(intMaterialRequrieAdvanceDays));
///
string plant = new PlantBLL().GetPlantBySapPlantCode(sapProductOrderInfo.Dwerk);
if (string.IsNullOrEmpty(plant))
{
throw new Exception("3x00000016");///工厂不存在
}
string assemblyLine = new AssemblyLineBLL().GetAssemblyLineBySapAssemblyLine(sapProductOrderInfo.Verid);
if (string.IsNullOrEmpty(assemblyLine))
{
throw new Exception("3x00000017");///产线不存在
}
///根据生产订单号⑤=①获取生产订单TT_BAS_PULL_ORDERS数据
PullOrdersInfo pullOrdersInfo = new PullOrdersBLL().GetInfoByOrderNo(sapProductOrderInfo.Aufnr);
///生产订单上线日期
string productOrderDate = sapProductOrderDate;
///供货日期
string materialRequireDate = sapProductOrderInfo.OnlineDate.GetValueOrDefault().AddDays(intMaterialRequrieAdvanceDays).ToString("yyyyMMdd");
#region 首次下发
///若此时未能成功获取数据则表示该生产订单为首次下发,且不是删除的生产订单
if (pullOrdersInfo == null && string.IsNullOrEmpty(sapProductOrderInfo.Zsc))
{
if (calculateSupplyPlanFlag.ToLower() == "true")
{
#region 供货计划
foreach (Matnrs matnr in objMatnrs)
{
decimal partQty = 0;
decimal.TryParse(matnr.Bdmng, out partQty);
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == matnr.Matnr);
string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname.Replace("'", "''");
string partPurchaser = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartPurchaser;
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == matnr.Lifnr);
string supplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName;
///若不存在则insert，再以工厂③、物料号①、供应商②更新其对应日期的物料数量
@string.AppendFormat(@"
if not exists (select 1 from [LES].[TT_ATP_SUPPLY_PLAN] with(nolock) where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}' and [VALID_FLAG] = 1)
begin
insert into [LES].[TT_ATP_SUPPLY_PLAN] ([FID],[PART_NO],[PART_CNAME],[PART_PURCHASER],[SUPPLIER_NUM],[SUPPLIER_NAME],[PLANT],[VALID_FLAG],[CREATE_USER],[CREATE_DATE]) values (NEWID(),N'{0}',N'{6}',N'{7}',N'{1}',N'{8}',N'{2}',1,N'{5}',GETDATE());
end
update [LES].[TT_ATP_SUPPLY_PLAN] set [PART_CNAME] = N'{6}',[PART_PURCHASER] = N'{7}',[SUPPLIER_NAME] = N'{8}',[{3}] = ISNULL([{3}] , 0) + {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE() where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';"
, matnr.Matnr, matnr.Lifnr, plant, materialRequireDate, partQty, loginUser, partCname, partPurchaser, supplierName);
}
#endregion
}
#region 生产订单
///
pullOrdersInfo = PullOrdersBLL.CreatePullOrdersInfo(loginUser);
///
PullOrdersBLL.GetPullOrdersInfo(sapProductOrderInfo, ref pullOrdersInfo);
///WERK,接口_工厂
pullOrdersInfo.Werk = plant;
///ORDER_DATE,订单日期
pullOrdersInfo.OrderDate = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate);
///ASSEMBLY_LINE,工厂模型_流水线
pullOrdersInfo.AssemblyLine = assemblyLine;
///PLAN_EXECUTE_TIME,计划执行时间
pullOrdersInfo.PlanExecuteTime = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate);
///
@string.AppendLine(PullOrdersDAL.GetInsertSql(pullOrdersInfo));
///并批量插入生产订单物料清单（参见TT_BAS_PULL_ORDER_BOM备注中的对应关系）
WmsVmiProductOrderInfo wmsVmiProductOrderInfo = WmsVmiProductOrderBLL.CreateWmsVmiProductOrderInfo(loginUser);
///
WmsVmiProductOrderBLL.GetWmsVmiProductOrderInfo(pullOrdersInfo, ref wmsVmiProductOrderInfo);
///
wmsVmiProductOrderInfo.DownLineTime = sapProductOrderInfo.OfflineDate;
wmsVmiProductOrderInfo.OnlineTime = sapProductOrderInfo.OnlineDate;
wmsVmiProductOrderInfo.LockFlag = sapProductOrderInfo.LockFlag;
@string.AppendLine(WmsVmiProductOrderDAL.GetInsertSql(wmsVmiProductOrderInfo));
@string.AppendLine(BLL.LES.CommonBLL.GetCreateOutboundLogSql("VMI", wmsVmiProductOrderInfo.LogFid.GetValueOrDefault(), "LES-WMS-012", wmsVmiProductOrderInfo.OrderNo, loginUser));
foreach (Matnrs matnr in objMatnrs)
{
decimal partQty = 0;
decimal.TryParse(matnr.Bdmng, out partQty);
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == matnr.Matnr);
string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname;
@string.AppendFormat(@"insert into [LES].[TT_BAS_PULL_ORDER_BOM]
([FID],[ORDERFID],[ZORDNO],[ZKWERK],[ZBOMID],[ZCOMNO],[ZQTY],[ZLOC],[SUPPLIER_NUM],[PLATFORM],[CREATE_USER],[CREATE_DATE],[VALID_FLAG],[ZCOMDS],[ZDATE])
values (NEWID(),N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',{5},N'{6}',N'{7}',N'{8}',N'{9}',GETDATE(),1,N'{10}',N'{11}');"
, pullOrdersInfo.Fid.GetValueOrDefault(), sapProductOrderInfo.Aufnr, plant, matnr.Aennr, matnr.Matnr, partQty, matnr.Ebort, matnr.Lifnr, matnr.Platform, loginUser, partCname, sapProductOrderDate);
}
#endregion
#region 计划拉动状态
///获取已启用的计划零件类的零件类外键①
///并将TT_BAS_PULL_ORDERS的订单外键②写入TT_MPM_PLAN_PULL_CREATE_STATUS
///其中的状态③为10.未生成（20.已生成，在系统代码中创建CREATE_STATUS，还需修改计划拉动单生成逻辑中的对应枚举项）
List <PlanPartBoxInfo> planPartBoxInfos = new PlanPartBoxBLL().GetList("[STATUS] = " + (int)BasicDataStatusConstants.Enable + "", string.Empty);
///
foreach (PlanPartBoxInfo planPartBoxInfo in planPartBoxInfos)
{
@string.AppendFormat(@"insert into [LES].[TT_MPM_PLAN_PULL_CREATE_STATUS]
(FID,PART_BOX_FID,ORDER_FID,STATUS,CREATE_USER,CREATE_DATE,VALID_FLAG)
values (NEWID(),N'{0}',N'{1}',{2},'{3}',GETDATE(),1);"
, planPartBoxInfo.Fid.GetValueOrDefault(), pullOrdersInfo.Fid.GetValueOrDefault(), (int)CreateStatusConstants.NotGenerated, loginUser);
}
#endregion
///同时更新SAP生产订单物料清单处理状态⑮为20.已处理
@string.AppendFormat(@"update [LES].[TI_IFM_SAP_PRODUCT_ORDER_BOM]
set PROCESS_FLAG = {0},PROCESS_TIME = GETDATE(),[MODIFY_USER] = N'{1}',[MODIFY_DATE] = GETDATE() where [ID] = {2};"
, (int)ProcessFlagConstants.Processed, loginUser, sapProductOrderBomInfo.Id);
}
#endregion
#region 是首次下发
else
{
if (calculateSupplyPlanFlag.ToLower() == "true")
{
///若之前成功获取了生产订单则比对上线日期⑩⑤是否较SAP生产订单有变化
#region 供货计划
///获取生产订单物料清单
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomBLL().GetList("[ZORDNO] = N'" + pullOrdersInfo.OrderNo + "'", string.Empty);
///SAP订单删除，需要重新计算供货计划
if (sapProductOrderInfo.Zsc.ToUpper() == "X")
{
///需要根据生产订单物料清单TT_BAS_PULL_ORDER_BOM和生产订单的订单日期⑤扣减供货计划
foreach (PullOrderBomInfo pullOrderBomInfo in pullOrderBomInfos)
{
@string.AppendLine("update [LES].[TT_ATP_SUPPLY_PLAN] " +
"set [" + materialRequireDate + "] = ISNULL([" + materialRequireDate + "] , 0) - " + pullOrderBomInfo.Zqty.GetValueOrDefault() + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [PART_NO] = N'" + pullOrderBomInfo.Zcomno + "' and " +
"[SUPPLIER_NUM] = N'" + pullOrderBomInfo.SupplierNum + "' and " +
"[PLANT] = N'" + plant + "';");
}
}
else
{
///若日期无变化⑩=⑤处理状态⑮为10.未处理或30.挂起时不需要更新供货计划
if (sapProductOrderInfo.OnlineDate.GetValueOrDefault() == pullOrdersInfo.OrderDate.GetValueOrDefault())
{
///若处理状态⑮为40.逆处理时
if (processFlag == (int)ProcessFlagConstants.ConverseProgress)
{
///根据SAP生产订单上线日期⑩及物料清单扣减供货计划
foreach (Matnrs matnr in objMatnrs)
{
decimal partQty = 0;
decimal.TryParse(matnr.Bdmng, out partQty);
@string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN]
set [{3}] = ISNULL([{3}] , 0) - {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE()
where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';"
, matnr.Matnr, matnr.Lifnr, plant, materialRequireDate, partQty, loginUser);
}
///再以生产订单⑤日期及物料清单累加供货计划
foreach (PullOrderBomInfo pullOrderBomInfo in pullOrderBomInfos)
{
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == pullOrderBomInfo.Zcomno);
string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname;
string partPurchaser = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartPurchaser;
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == pullOrderBomInfo.SupplierNum);
string supplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName;
@string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN]
set [PART_CNAME] = N'{6}',[PART_PURCHASER] = N'{7}',[SUPPLIER_NAME] = N'{8}',[{3}] = ISNULL([{3}] , 0) + {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE()
where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';"
, pullOrderBomInfo.Zcomno, pullOrderBomInfo.SupplierNum, plant, materialRequireDate, pullOrderBomInfo.Zqty.GetValueOrDefault(), loginUser, partCname, partPurchaser, supplierName);
}
}
}
///日期有变化⑩<>⑤
else
{
///SAP生产订单处理状态⑮为10.未处理或30.挂起时
if (processFlag == (int)ProcessFlagConstants.Untreated || processFlag == (int)ProcessFlagConstants.Suspend)
{
///需要根据生产订单物料清单TT_BAS_PULL_ORDER_BOM和生产订单的订单日期⑤扣减供货计划
foreach (PullOrderBomInfo pullOrderBomInfo in pullOrderBomInfos)
{
@string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN]
set [{3}] = ISNULL([{3}] , 0) - {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE()
where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';"
, pullOrderBomInfo.Zcomno, pullOrderBomInfo.SupplierNum, plant, materialRequireDate, pullOrderBomInfo.Zqty.GetValueOrDefault(), loginUser);
}
///同时根据SAP生产订单上线日期⑩及物料清单累加供货计划
foreach (Matnrs matnr in objMatnrs)
{
decimal partQty = 0;
decimal.TryParse(matnr.Bdmng, out partQty);
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == matnr.Matnr);
string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname;
string partPurchaser = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartPurchaser;
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == matnr.Lifnr);
string supplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName;
@string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN]
set [PART_CNAME] = N'{6}',[PART_PURCHASER] = N'{7}',[SUPPLIER_NAME] = N'{8}',[{3}] = ISNULL([{3}] , 0) + {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE()
where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';"
, matnr.Matnr, matnr.Lifnr, plant, materialRequireDate, partQty, loginUser, partCname, partPurchaser, supplierName);
}
}
}
}
#endregion
}
#region 更新生产订单
///生产订单删除
if (sapProductOrderInfo.Zsc.ToUpper() == "X")
{
@string.AppendLine("update [LES].[TT_BAS_PULL_ORDERS] " +
"set [VALID_FLAG] = 0," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + sapProductOrderInfo.Id + " and " +
"[VALID_FLAG] = 1;");
@string.AppendLine("update [LES].[TT_BAS_PULL_ORDER_BOM] " +
"set [VALID_FLAG] = 0," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ZORDNO] = N'" + pullOrdersInfo.OrderNo + "' and " +
"[VALID_FLAG] = 1;");
}
else
{
///更新生产订单时版本号⑨累加，同时更新生产订单内容、以及SAP生产订单处理状态⑮为20.已处理
@string.AppendFormat("update [LES].[TT_BAS_PULL_ORDERS] set " +
"[WERK] = N'{1}'," +
"[MODEL_YEAR] = N'{2}'," +
"[VEHICLE_ORDER] = N'{3}'," +
"[ORDER_DATE] = N'{4}'," +
"[ASSEMBLY_LINE] = N'{5}'," +
"[PART_NO] = N'{6}'," +
"[VERSION] = ISNULL([VERSION],0) + 1," +
"[MODIFY_USER] = N'{7}'," +
"[MODIFY_DATE] = GETDATE()," +
"[PLAN_EXECUTE_TIME] = N'{8}' " +
"where [ID] = {0};",
pullOrdersInfo.Id,
plant,
sapProductOrderInfo.CarColor,
sapProductOrderInfo.OnlineSeq,
sapProductOrderDate,
assemblyLine,
sapProductOrderInfo.Matnr,
loginUser,
sapProductOrderDate);
}
///TODO:在下发给WMS时也需要提供生产订单删除的逻辑标识
WmsVmiProductOrderInfo wmsVmiProductOrderInfo = WmsVmiProductOrderBLL.CreateWmsVmiProductOrderInfo(loginUser);
///
PullOrdersBLL.GetPullOrdersInfo(sapProductOrderInfo, ref pullOrdersInfo);
///WERK,接口_工厂
pullOrdersInfo.Werk = plant;
///ORDER_DATE,订单日期
pullOrdersInfo.OrderDate = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate);
///ASSEMBLY_LINE,工厂模型_流水线
pullOrdersInfo.AssemblyLine = assemblyLine;
///PLAN_EXECUTE_TIME,计划执行时间
pullOrdersInfo.PlanExecuteTime = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate);
///VERSION,版本号
pullOrdersInfo.Version = pullOrdersInfo.Version.GetValueOrDefault() + 1;
///
WmsVmiProductOrderBLL.GetWmsVmiProductOrderInfo(pullOrdersInfo, ref wmsVmiProductOrderInfo);
///
wmsVmiProductOrderInfo.DownLineTime = sapProductOrderInfo.OfflineDate;
wmsVmiProductOrderInfo.OnlineTime = sapProductOrderInfo.OnlineDate;
wmsVmiProductOrderInfo.LockFlag = sapProductOrderInfo.LockFlag;
@string.AppendLine(WmsVmiProductOrderDAL.GetInsertSql(wmsVmiProductOrderInfo));
@string.AppendLine(BLL.LES.CommonBLL.GetCreateOutboundLogSql("VMI", wmsVmiProductOrderInfo.LogFid.GetValueOrDefault(), "LES-WMS-012", wmsVmiProductOrderInfo.OrderNo, loginUser));
#endregion
}
#endregion
#region 更新SAP生产订单
@string.AppendFormat(@"update [LES].[TI_IFM_SAP_PRODUCT_ORDER]
set PROCESS_FLAG = {0},PROCESS_TIME = GETDATE(),[MODIFY_USER] = N'{1}',[MODIFY_DATE] = GETDATE()
where [ID] = {2};"
, (int)ProcessFlagConstants.Processed, loginUser, sapProductOrderInfo.Id);
#endregion
#region 数据库语句执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
BLL.LES.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
///这个很重要
@string.Clear();
#endregion
///订单计算用时
TimeSpan ts = new TimeSpan();
ts = DateTime.Now - startExecuteTime;
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":End " + sapProductOrderInfo.Aufnr + "," + ts.TotalSeconds + "s");
}
}
/// <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="loginUser"></param>
/// <returns></returns>
public static void Sync(string loginUser)
{
///获取没有处理的物料发货单表
List <SrmVmiShippingNoteInfo> srmVmiShippingNoteInfos = new SrmVmiShippingNoteBLL().GetList("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", "[ID]");
if (srmVmiShippingNoteInfos.Count == 0)
{
return;
}
///获取没有处理的物料发货单详情表
List <SrmVmiShippingNoteDetailInfo> srmVmiShippingNoteDetailInfos = new SrmVmiShippingNoteDetailBLL().GetList("[NOTE_FID] in ('" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.LogFid).ToArray()) + "')", "[ID]");
if (srmVmiShippingNoteDetailInfos.Count == 0)
{
return;
}
StringBuilder @string = new StringBuilder();
///获取相关仓库信息
List <WarehouseInfo> warehouseInfos = new WarehouseBLL().GetList("[WAREHOUSE] in ('" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.VmiWmNo).ToArray()) + "')", string.Empty);
if (warehouseInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +///仓库信息不存在
"[ID] in (" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取相关供应商基础信息
List <SupplierInfo> supplierInfos = new SupplierBLL().GetList("[SUPPLIER_NUM] in ('" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.SupplierCode).ToArray()) + "')", string.Empty);
if (supplierInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000229' where " +///供应商信息不存在
"[ID] in (" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取VMI供应商关系
List <VmiSupplierInfo> vmiSupplierInfos = new VmiSupplierBLL().GetList("" +
"[SUPPLIER_NUM] in ('" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.SupplierCode).ToArray()) + "') and " +
"[WM_NO] in ('" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.VmiWmNo).ToArray()) + "')", string.Empty);
if (vmiSupplierInfos.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000429' where " +///VMI供应商信息未维护
"[ID] in (" + string.Join("','", srmVmiShippingNoteInfos.Select(d => d.Id).ToArray()) + ");");
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
return;
}
///获取相关物料基础信息
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetList("" +
"[PART_NO] in ('" + string.Join("','", srmVmiShippingNoteDetailInfos.Select(d => d.Partno).ToArray()) + "')", string.Empty);
///获取相关物料仓储信息
List <PartsStockInfo> partsStockInfos = new PartsStockBLL().GetList("" +
"[PART_NO] in ('" + string.Join("','", srmVmiShippingNoteDetailInfos.Select(d => d.Partno).ToArray()) + "') and " +
"[WM_NO] in ('" + string.Join("','", warehouseInfos.Select(d => d.Warehouse).ToArray()) + "')", string.Empty);
///获取相关包装器具基础信息
List <PackageApplianceInfo> packageApplianceInfos = new List <PackageApplianceInfo>();
if (partsStockInfos.Count > 0)
{
///标准包装
List <string> packageModels = partsStockInfos.
Where(d => !string.IsNullOrEmpty(d.PackageModel)).
Select(d => d.PackageModel).ToList();
///入库包装
packageModels.AddRange(partsStockInfos.
Where(d => !string.IsNullOrEmpty(d.InboundPackageModel) && !packageModels.Contains(d.InboundPackageModel)).
Select(d => d.InboundPackageModel).ToList());
///上线包装
packageModels.AddRange(partsStockInfos.
Where(d => !string.IsNullOrEmpty(d.InhousePackageModel) && !packageModels.Contains(d.InhousePackageModel)).
Select(d => d.InhousePackageModel).ToList());
///
packageApplianceInfos = new PackageApplianceBLL().GetList("[PAKCAGE_NO] in ('" + string.Join("','", packageModels.ToArray()) + "')", string.Empty);
}
///获取系统配置
Dictionary <string, string> configs = new ConfigBLL().GetValuesByCodes(new string[] {
"RELEASE_VMI_RECEIVE_ACTUAL_QTY_EQUALS_REQUIRED",
"ENABLE_VMI_FLAG"
});
///
List <long> dealedIds = new List <long>();
///如果数据不为空,按照规则分发
foreach (SrmVmiShippingNoteInfo srmVmiShippingNoteInfo in srmVmiShippingNoteInfos)
{
///本单据的对应仓库
WarehouseInfo warehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == srmVmiShippingNoteInfo.VmiWmNo);
///如果未处理的物料发货单中仓库码不存在, 修改中间表数据为挂起状态
if (warehouseInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000230' where " +///仓库信息不存在
"[ID] = " + srmVmiShippingNoteInfo.Id + ";");
continue;
}
///仓库类型不是VMI
if (warehouseInfo.WarehouseType.GetValueOrDefault() != (int)WarehouseTypeConstants.VMI)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000219' where " +///仓库类型错误
"[ID] = " + srmVmiShippingNoteInfo.Id + ";");
continue;
}
///本发货单的物料明细
List <SrmVmiShippingNoteDetailInfo> srmVmiShippingNoteDetails = srmVmiShippingNoteDetailInfos.Where(d =>
d.NoteFid.GetValueOrDefault() == srmVmiShippingNoteInfo.Fid).ToList();
if (srmVmiShippingNoteDetails.Count == 0)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000465' where " +///发货单物料数据错误
"[ID] = " + srmVmiShippingNoteInfo.Id + ";");
continue;
}
///供应商
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == srmVmiShippingNoteInfo.SupplierCode);
if (supplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000229' where " +///供应商信息不存在
"[ID] = " + srmVmiShippingNoteInfo.Id + ";");
continue;
}
///VMI供应商关系
VmiSupplierInfo vmiSupplierInfo = vmiSupplierInfos.FirstOrDefault(d => d.SupplierNum == supplierInfo.SupplierNum && d.WmNo == warehouseInfo.Warehouse);
if (vmiSupplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000429' where " +///VMI供应商信息未维护
"[ID] = " + srmVmiShippingNoteInfo.Id + ";");
continue;
}
///如果该仓库启用的是VMI模块则需要将单据写入VMI入库单
if (warehouseInfo.VmiEnable.GetValueOrDefault())
{
///发布VMI入库单时实收数量默认等于需求数量
configs.TryGetValue("RELEASE_VMI_RECEIVE_ACTUAL_QTY_EQUALS_REQUIRED", out string release_vmi_receive_actual_qty_equals_required);
///创建VMI入库单,TODO：默认类型与状态
VmiReceiveInfo vmiReceiveInfo = VmiReceiveBLL.CreateVmiReceiveInfo(
loginUser,
(int)VmiReceiveTypeConstants.ProductionWarehousing,
(int)WmmOrderStatusConstants.Published);
///SrmVmiShippingNoteInfo -> VmiReceiveInfo
VmiReceiveBLL.GetVmiReceiveInfo(srmVmiShippingNoteInfo, ref vmiReceiveInfo);
///SupplierInfo -> VmiReceiveInfo
VmiReceiveBLL.GetVmiReceiveInfo(supplierInfo, ref vmiReceiveInfo);
///VmiSupplierInfo -> VmiReceiveInfo
VmiReceiveBLL.GetVmiReceiveInfo(vmiSupplierInfo, ref vmiReceiveInfo);
///生成入库单语句
@string.AppendLine(VmiReceiveDAL.GetInsertSql(vmiReceiveInfo));
///
foreach (SrmVmiShippingNoteDetailInfo srmVmiShippingNoteDetail in srmVmiShippingNoteDetails)
{
///创建VMI入库单明细
VmiReceiveDetailInfo vmiReceiveDetailInfo = VmiReceiveDetailBLL.CreateVmiReceiveDetailInfo(loginUser);
///VmiReceiveInfo -> VmiReceiveDetailInfo
VmiReceiveDetailBLL.GetVmiReceiveDetailInfo(vmiReceiveInfo, ref vmiReceiveDetailInfo);
///MaintainPartsInfo -> VmiReceiveDetailInfo
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == srmVmiShippingNoteDetail.Partno);
VmiReceiveDetailBLL.GetVmiReceiveDetailInfo(maintainPartsInfo, ref vmiReceiveDetailInfo);
///PartsStockInfo -> VmiReceiveDetailInfo
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == srmVmiShippingNoteDetail.Partno && d.WmNo == warehouseInfo.Warehouse);
VmiReceiveDetailBLL.GetVmiReceiveDetailInfo(partsStockInfo, ref vmiReceiveDetailInfo);
///PackageApplianceInfo -> VmiReceiveDetailInfo
PackageApplianceInfo packageApplianceInfo = partsStockInfo == null ? null : packageApplianceInfos.FirstOrDefault(d => d.PackageNo == partsStockInfo.InboundPackageModel);
VmiReceiveDetailBLL.GetVmiReceiveDetailInfo(packageApplianceInfo, ref vmiReceiveDetailInfo);
///SrmVmiShippingNoteDetailInfo -> VmiReceiveDetailInfo
VmiReceiveDetailBLL.GetVmiReceiveDetailInfo(srmVmiShippingNoteDetail, ref vmiReceiveDetailInfo);
///发布VMI入库单时实收数量默认等于需求数量
if (!string.IsNullOrEmpty(release_vmi_receive_actual_qty_equals_required) && release_vmi_receive_actual_qty_equals_required.ToLower() == "true")
{
///ACTUAL_BOX_NUM
vmiReceiveDetailInfo.ActualBoxNum = vmiReceiveDetailInfo.RequiredBoxNum;
///ACTUAL_QTY
vmiReceiveDetailInfo.ActualQty = vmiReceiveDetailInfo.RequiredQty;
}
///生成入库单明细语句
@string.AppendLine(VmiReceiveDetailDAL.GetInsertSql(vmiReceiveDetailInfo));
}
dealedIds.Add(srmVmiShippingNoteInfo.Id);
}
///如果未启用VMI模块，则根据WMS系统开关决定是否写入中间表
else
{
///是否启用WMS系统标记
configs.TryGetValue("ENABLE_VMI_FLAG", out string enable_vmi_flag);
if (!string.IsNullOrEmpty(enable_vmi_flag) && enable_vmi_flag.ToLower() == "true" && vmiSupplierInfo.VmiFlag.GetValueOrDefault())
{
///创建WMS入库单
WmsVmiInboundOrderInfo wmsVmiInboundOrderInfo = WmsVmiInboundOrderBLL.CreateWmsVmiInboundOrderInfo((int)ProcessFlagConstants.Untreated, loginUser);
///SrmVmiShippingNoteInfo -> WmsVmiInboundOrderInfo
WmsVmiInboundOrderBLL.GetWmsVmiInboundOrderInfo(srmVmiShippingNoteInfo, ref wmsVmiInboundOrderInfo);
///生成入库单语句
@string.AppendLine(WmsVmiInboundOrderDAL.GetInsertSql(wmsVmiInboundOrderInfo));
///
foreach (SrmVmiShippingNoteDetailInfo srmVmiShippingNoteDetail in srmVmiShippingNoteDetails)
{
///创建WMS入库单明细
WmsVmiInboundOrderDetailInfo wmsVmiInboundOrderDetailInfo = WmsVmiInboundOrderDetailBLL.CreateWmsVmiInboundOrderDetailInfo(loginUser);
///WmsVmiInboundOrderInfo -> WmsVmiInboundOrderDetailInfo
WmsVmiInboundOrderDetailBLL.GetWmsVmiInboundOrderDetailInfo(wmsVmiInboundOrderInfo, ref wmsVmiInboundOrderDetailInfo);
///
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == srmVmiShippingNoteDetail.Partno && d.WmNo == warehouseInfo.Warehouse);
WmsVmiInboundOrderDetailBLL.GetWmsVmiInboundOrderDetailInfo(partsStockInfo, ref wmsVmiInboundOrderDetailInfo);
///SrmVmiShippingNoteDetailInfo -> WmsVmiInboundOrderDetailInfo
WmsVmiInboundOrderDetailBLL.GetWmsVmiInboundOrderDetailInfo(srmVmiShippingNoteDetail, ref wmsVmiInboundOrderDetailInfo);
///生成WMS入库单明细语句
@string.AppendLine(WmsVmiInboundOrderDetailDAL.GetInsertSql(wmsVmiInboundOrderDetailInfo));
}
///
string targetSystem = "VMI";
string methodCode = "LES-WMS-001";
@string.AppendLine(BLL.LES.CommonBLL.GetCreateOutboundLogSql(
targetSystem,
wmsVmiInboundOrderInfo.LogFid.GetValueOrDefault(),
methodCode,
srmVmiShippingNoteInfo.ShippingCode,
loginUser));
dealedIds.Add(srmVmiShippingNoteInfo.Id);
continue;
}
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000466' where " +///该供应商未启用WMS功能
"[ID] = " + srmVmiShippingNoteInfo.Id + ";");
continue;
}
}
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
@string.AppendLine("update [LES].[TI_IFM_SRM_VMI_SHIPPING_NOTE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Processed + "," +
"[PROCESS_TIME] = GETDATE() where " +
"[ID] in (" + string.Join(",", dealedIds.ToArray()) + ");");
}
///执行
using (var trans = new TransactionScope())
{
if (@string.Length > 0)
{
BLL.SYS.CommonBLL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
}
/// <summary>
/// 同步
/// </summary>
/// <param name="loginUser"></param>
public static void Sync(string loginUser)
{
///获取没有处理的送货单数据
List <SrmBarcodeInfo> srmBarcodeInfos = new SrmBarcodeBLL().GetList("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Untreated + "", string.Empty);
if (srmBarcodeInfos.Count == 0)
{
return;
}
///获取对应的ASN单据，TODO:单据类型过滤？原始单据号是指？
List <ReceiveInfo> receiveInfos = new ReceiveBLL().GetList("[ASN_NO] in ('" + string.Join("','", srmBarcodeInfos.Select(d => d.SourceOrderCode).ToArray()) + "')", string.Empty);
if (receiveInfos.Count == 0)
{
return;
}
List <ReceiveDetailInfo> receiveDetailInfos = new ReceiveDetailBLL().GetList("[RECEIVE_FID] in ('" + string.Join("','", receiveInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "')", string.Empty);
if (receiveDetailInfos.Count == 0)
{
return;
}
///供应商
List <SupplierInfo> supplierInfos = new SupplierBLL().GetList("[SUPPLIER_NUM] in ('" + string.Join("','", receiveInfos.Select(d => d.SupplierNum).ToArray()) + "')", string.Empty);
if (supplierInfos.Count == 0)
{
return;
}
///获取相关物料仓储信息
List <PartsStockInfo> partsStockInfos = new PartsStockBLL().GetList("" +
"[PART_NO] in ('" + string.Join("','", srmBarcodeInfos.Select(d => d.PartNo).ToArray()) + "') and " +
"[WM_NO] in ('" + string.Join("','", receiveInfos.Select(d => d.WmNo).ToArray()) + "')", string.Empty);
///获取相关包装器具基础信息
List <PackageApplianceInfo> packageApplianceInfos = new List <PackageApplianceInfo>();
if (partsStockInfos.Count > 0)
{
///标准包装
List <string> packageModels = partsStockInfos.
Where(d => !string.IsNullOrEmpty(d.PackageModel)).
Select(d => d.PackageModel).ToList();
///入库包装
packageModels.AddRange(partsStockInfos.
Where(d => !string.IsNullOrEmpty(d.InboundPackageModel) && !packageModels.Contains(d.InboundPackageModel)).
Select(d => d.InboundPackageModel).ToList());
///上线包装
packageModels.AddRange(partsStockInfos.
Where(d => !string.IsNullOrEmpty(d.InhousePackageModel) && !packageModels.Contains(d.InhousePackageModel)).
Select(d => d.InhousePackageModel).ToList());
///
packageApplianceInfos = new PackageApplianceBLL().GetList("[PAKCAGE_NO] in ('" + string.Join("','", packageModels.ToArray()) + "')", string.Empty);
}
///
StringBuilder @string = new StringBuilder();
///
List <long> dealedIds = new List <long>();
///
foreach (SrmBarcodeInfo srmBarcodeInfo in srmBarcodeInfos)
{
///获取入库单。TODO:需要两个编号才能定位到唯一明细数据
ReceiveInfo receiveInfo = receiveInfos.FirstOrDefault(d => d.AsnNo == srmBarcodeInfo.SourceOrderCode);
///标签数据与ASN单据数据到达LES可能存在先后顺序
if (receiveInfo == null)
{
continue;
}
ReceiveDetailInfo receiveDetailInfo = receiveDetailInfos.FirstOrDefault(d =>
d.PartNo == srmBarcodeInfo.PartNo &&
d.ReceiveFid.GetValueOrDefault() == receiveInfo.Fid.GetValueOrDefault() &&
d.RunsheetNo == srmBarcodeInfo.SourceOrderCode);
if (receiveDetailInfo == null)
{
continue;
}
///供应商
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == receiveInfo.SupplierNum);
if (supplierInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_BARCODE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000229' where " +///供应商信息不存在
"[ID] = " + srmBarcodeInfo.Id + ";");
continue;
}
///物料仓储信息
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d =>
d.PartNo == srmBarcodeInfo.PartNo &&
d.SupplierNum == supplierInfo.SupplierNum &&
d.WmNo == receiveInfo.WmNo &&
d.ZoneNo == receiveInfo.ZoneNo);
if (partsStockInfo == null)
{
partsStockInfo = partsStockInfos.FirstOrDefault(d =>
d.PartNo == srmBarcodeInfo.PartNo &&
d.WmNo == receiveInfo.WmNo &&
d.ZoneNo == receiveInfo.ZoneNo);
}
if (partsStockInfo == null)
{
@string.AppendLine("update [LES].[TI_IFM_SRM_BARCODE] set " +
"[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + "," +
"[PROCESS_TIME] = GETDATE()," +
"[COMMENTS] = N'0x00000370' where " +///没有相关的物料仓储信息
"[ID] = " + srmBarcodeInfo.Id + ";");
continue;
}
///创建标签对象
BarcodeInfo barcodeInfo = BarcodeBLL.CreateBarcodeInfo(loginUser);
///SrmBarcodeInfo -> BarcodeInfo
BarcodeBLL.GetBarcodeInfo(srmBarcodeInfo, ref barcodeInfo);
///SupplierInfo -> BarcodeInfo
BarcodeBLL.GetBarcodeInfo(supplierInfo, ref barcodeInfo);
///PartsStockInfo -> BarcodeInfo
BarcodeBLL.GetBarcodeInfo(partsStockInfo, ref barcodeInfo);
///PackageApplianceInfo -> BarcodeInfo
PackageApplianceInfo packageApplianceInfo = packageApplianceInfos.FirstOrDefault(d => d.PackageNo == partsStockInfo.InboundPackageModel);
BarcodeBLL.GetBarcodeInfo(packageApplianceInfo, ref barcodeInfo);
///
barcodeInfo.CreateSourceFid = receiveDetailInfo.Fid;
///
@string.AppendLine(BarcodeDAL.GetInsertSql(barcodeInfo));
dealedIds.Add(srmBarcodeInfo.Id);
}
if (dealedIds.Count > 0)
{
///已处理的中间表数据更新为已处理状态
@string.AppendLine("update [LES].[TI_IFM_SRM_BARCODE] 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()
{
InitConfigFlag();
///获取未同步的交易记录
List <TranDetailsInfo> tranDetailsInfos = new TranDetailsBLL().GetList("[TRAN_STATE] = " + (int)WmmTranStateConstants.Created + "", "[ID]");
if (tranDetailsInfos.Count == 0)
{
return;
}
///仓库
List <string> wmNos = tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.WmNo)).Select(d => d.WmNo).ToList();
wmNos.AddRange(tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.TargetWm)).Select(d => d.TargetWm).ToList());
List <WarehouseInfo> warehouseInfos = new WarehouseBLL().GetList("[WAREHOUSE] in ('" + string.Join("','", wmNos.ToArray()) + "')", string.Empty);
///存储区
List <string> zoneNos = tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.ZoneNo)).Select(d => d.ZoneNo).ToList();
zoneNos.AddRange(tranDetailsInfos.Where(d => !string.IsNullOrEmpty(d.TargetZone)).Select(d => d.TargetZone).ToList());
List <ZonesInfo> zonesInfos = new ZonesBLL().GetList("[ZONE_NO] in ('" + string.Join("','", zoneNos.ToArray()) + "')", string.Empty);
///工厂
List <PlantInfo> plantInfos = new PlantBLL().GetListForInterfaceDataSync();
///供应商
List <SupplierInfo> supplierInfos = new SupplierBLL().GetListForInterfaceDataSync(tranDetailsInfos.Select(d => d.SupplierNum).ToList());
///物料
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(tranDetailsInfos.Select(d => d.PartNo).ToList());
///逐条处理
foreach (var tranDetailsInfo in tranDetailsInfos)
{
StringBuilder stringBuilder = new StringBuilder();
///工厂
PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.Plant == tranDetailsInfo.Plant);
///供应商
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == tranDetailsInfo.SupplierNum);
///目标仓库
WarehouseInfo targetWarehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == tranDetailsInfo.TargetWm);
///目标存储区
ZonesInfo targetZonesInfo = zonesInfos.FirstOrDefault(d => d.ZoneNo == tranDetailsInfo.TargetZone);
///目标工厂
PlantInfo targetPlantInfo = targetZonesInfo == null ? null : plantInfos.FirstOrDefault(d => d.Plant == targetZonesInfo.Plant);
///来源
ZonesInfo sourceZonesInfo = zonesInfos.FirstOrDefault(d => d.ZoneNo == tranDetailsInfo.ZoneNo);
///来源工厂
PlantInfo sourcePlantInfo = sourceZonesInfo == null ? null : plantInfos.FirstOrDefault(d => d.Plant == sourceZonesInfo.Plant);
///物料信息
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == tranDetailsInfo.PartNo && d.Plant == tranDetailsInfo.Plant);
///创建库存对象
StocksInfo stocksInfo = null;
///
switch (tranDetailsInfo.TranType.GetValueOrDefault())
{
///物料入库
case (int)WmmTranTypeConstants.Inbound:
///目标可用库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///撤销入库
case (int)WmmTranTypeConstants.UndoInbound:
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
UndoStock(ref stocksInfo);
stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///物料出库
case (int)WmmTranTypeConstants.Outbound:
///来源可用库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///撤销出库
case (int)WmmTranTypeConstants.UndoOutbound:
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
UndoStock(ref stocksInfo);
stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///冻结入库
case (int)WmmTranTypeConstants.FrozenInbound:
///目标冻结库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///冻结出库
case (int)WmmTranTypeConstants.FrozenOutbound:
///来源冻结库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///物料冻结
case (int)WmmTranTypeConstants.MaterialFreezing:
///来源可用库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
///目标冻结库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///状态冻结
case (int)WmmTranTypeConstants.StateFreezing:
///来源可用库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
///来源冻结库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///物料解冻
case (int)WmmTranTypeConstants.MaterialThawing:
///来源冻结库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
///目标可用库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///状态解冻
case (int)WmmTranTypeConstants.StateThawing:
///来源可用库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
///来源冻结库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///冻结移动
case (int)WmmTranTypeConstants.FrozenMovement:
///来源冻结库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.FrozenReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
///目标冻结库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
stringBuilder.AppendFormat(stocksBLL.FrozenRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
///物料移动
case (int)WmmTranTypeConstants.Movement:
///来源可用库存减少
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, sourceZonesInfo, true);
stringBuilder.AppendFormat(stocksBLL.StocksReduceSql(stocksInfo, tranDetailsInfo.Id, loginUser));
///目标可用库存增加
stocksInfo = HandlingStocksData(tranDetailsInfo, maintainPartsInfo, targetZonesInfo, false);
stringBuilder.AppendFormat(stocksBLL.StocksRaiseSql(stocksInfo, tranDetailsInfo.Id, loginUser));
break;
default: continue;
}
///SAP移动数据
stringBuilder.AppendFormat(CreateSapTranData(tranDetailsInfo, sourceZonesInfo, targetZonesInfo, sourcePlantInfo, targetPlantInfo));
///SRM入库数据
stringBuilder.AppendFormat(CreateSrmTranData(tranDetailsInfo, supplierInfo, targetWarehouseInfo));
///WMS入库数据
stringBuilder.AppendFormat(CreateVmiTranData(tranDetailsInfo, supplierInfo, targetWarehouseInfo));
#region 执行
using (TransactionScope trans = new TransactionScope())
{
if (stringBuilder.Length > 0)
{
BLL.LES.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString());
}
trans.Complete();
}
#endregion
Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":" + stocksInfo.PartNo + "|" + tranDetailsInfo.TranType.GetValueOrDefault() + "|" + stocksInfo.WmNo + "|" + stocksInfo.ZoneNo + "|" + stocksInfo.Dloc + "|");
}
}