/// <summary>
/// PartsStockInfo -> TranDetailsInfo
/// </summary>
/// <param name="partsStockInfo"></param>
/// <param name="tranDetailsInfo"></param>
/// <returns></returns>
public static void GetTranDetailsInfo(PartsStockInfo partsStockInfo, ref TranDetailsInfo tranDetailsInfo)
{
if (partsStockInfo == null)
{
return;
}
///零件呢称
tranDetailsInfo.PartNickname = partsStockInfo.PartNickname;
///是否按批次
tranDetailsInfo.IsBatch = partsStockInfo.IsBatch;
///MAX
tranDetailsInfo.Max = partsStockInfo.Max;
///MIN
tranDetailsInfo.Min = partsStockInfo.Min;
///PACKAGE
tranDetailsInfo.Package = partsStockInfo.InboundPackage;
///PACKAGE_MODEL
tranDetailsInfo.PackageModel = partsStockInfo.InboundPackageModel;
///KEEPER
tranDetailsInfo.Keeper = partsStockInfo.Keeper;
///INHOUSE_PACKAGE_MODEL
tranDetailsInfo.InhousePackageModel = partsStockInfo.InhousePackageModel;
///INHOUSE_PACKAGE
tranDetailsInfo.InhousePackage = partsStockInfo.InhousePackage;
}
/// <summary>
/// PartsStockInfo -> OutputDetailInfo
/// </summary>
/// <param name="partsStockInfo"></param>
/// <param name="info"></param>
public static void GetOutputDetailInfo(PartsStockInfo partsStockInfo, ref OutputDetailInfo info)
{
if (partsStockInfo == null)
{
return;
}
///TARGET_DLOC,目的库位
info.TargetDloc = partsStockInfo.Dloc;
///PART_CNAME,车辆模型_零件中文名
info.PartCname = partsStockInfo.PartCname;
///PACKAGE,单包装数
info.Package = partsStockInfo.InboundPackage;
///PACKAGE_MODEL,包装型号
info.PackageModel = partsStockInfo.InboundPackageModel;
///MEASURING_UNIT_NO,单位
info.MeasuringUnitNo = partsStockInfo.PartUnits;
///PART_ENAME,车辆模型_零件德文名
info.PartEname = partsStockInfo.PartEname;
///INHOUSE_PACKAGE,上线包装数量
info.InhousePackage = partsStockInfo.InhousePackage;
///INHOUSE_PACKAGE_MODEL,上线包装型号
info.InhousePackageModel = partsStockInfo.InhousePackageModel;
///PART_CLS,零件类别
info.PartCls = partsStockInfo.PartCls;
}
/// <summary>
///
/// </summary>
/// <param name="partNos"></param>
/// <returns></returns>
public List <PartsStockInfo> GetListForInterfaceDataSync(List <string> partNos)
{
string sql = "select [ID],[PART_NO],[SUPPLIER_NUM],[WM_NO],[ZONE_NO],[DLOC],[INBOUND_PACKAGE_MODEL],[INBOUND_PACKAGE] "
+ "from [LES].[TM_BAS_PARTS_STOCK] with(nolock) "
+ "where [VALID_FLAG] = 1 and [PART_NO] in ('" + string.Join("','", partNos.ToArray()) + "');";
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
List <PartsStockInfo> list = new List <PartsStockInfo>();
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
PartsStockInfo info = new PartsStockInfo();
info.Id = DBConvert.GetInt64(dr, dr.GetOrdinal("ID"));
info.PartNo = DBConvert.GetString(dr, dr.GetOrdinal("PART_NO"));
info.SupplierNum = DBConvert.GetString(dr, dr.GetOrdinal("SUPPLIER_NUM"));
info.WmNo = DBConvert.GetString(dr, dr.GetOrdinal("WM_NO"));
info.ZoneNo = DBConvert.GetString(dr, dr.GetOrdinal("ZONE_NO"));
info.Dloc = DBConvert.GetString(dr, dr.GetOrdinal("DLOC"));
info.InboundPackage = DBConvert.GetInt32Nullable(dr, dr.GetOrdinal("INBOUND_PACKAGE"));
info.InboundPackageModel = DBConvert.GetString(dr, dr.GetOrdinal("INBOUND_PACKAGE_MODEL"));
list.Add(info);
}
}
return(list);
}
/// <summary>
/// PartsStockInfo -> WmsVmiInboundOrderDetailInfo
/// </summary>
/// <param name="partsStockInfo"></param>
/// <param name="wmsVmiInboundOrderDetailInfo"></param>
public static void GetWmsVmiInboundOrderDetailInfo(PartsStockInfo partsStockInfo, ref WmsVmiInboundOrderDetailInfo wmsVmiInboundOrderDetailInfo)
{
if (partsStockInfo == null)
{
return;
}
///SNP
wmsVmiInboundOrderDetailInfo.Snp = partsStockInfo.InboundPackage;
///PACKAGECODE
wmsVmiInboundOrderDetailInfo.Packagecode = partsStockInfo.InboundPackageModel;
}
/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(PartsStockInfo info)
{
///入库包装数量⑧不能小于等于零，当入库包装型号⑦不为空时入库包装数量⑧为必填项
if (!string.IsNullOrEmpty(info.InboundPackageModel) && info.InboundPackage.GetValueOrDefault() <= 0)
{
throw new Exception("MC:0x00000089");///当入库包装型号不为空时入库包装数量必须大于零
}
///最小库存 小于等于 安全库存 小于 最大库存，最小库存必须大于等于零
if (info.Min.GetValueOrDefault() > info.SafeStock.GetValueOrDefault())
{
throw new Exception("MC:0x00000090");///最小库存不能大于安全库存
}
//if (info.IsOutput == true)
//{
// if (info.SynchronousWmNo == "" || info.SynchronousZoneNo == "")
// throw new Exception("MC:0x00000439");///当同步出库标记为是时,同步出库仓库,存储区为必填项
//}
if (info.SafeStock.GetValueOrDefault() >= 0 &&
info.Max.GetValueOrDefault() > 0 &&
info.SafeStock.GetValueOrDefault() >= info.Max.GetValueOrDefault())
{
throw new Exception("MC:0x00000091");///安全库存不能大于等于最大库存
}
///相同物料号②、供应商代码①、仓库代码⑨、存储区代码⑩、库位代码⑪的数据不允许重复
int cnt = dal.GetCounts("[PART_NO] = N'" + info.PartNo + "' and [SUPPLIER_NUM] = N'" + info.SupplierNum + "' and [WM_NO] = N'" + info.WmNo + "' and [ZONE_NO] = N'" + info.ZoneNo + "'"
+ (string.IsNullOrEmpty(info.Dloc) ? string.Empty : " and [DLOC] = N'" + info.Dloc + "'"));
if (cnt > 0)
{
throw new Exception("MC:0x00000094");///相同物料号、供应商代码、仓库代码、存储区代码、库位代码的数据不允许重复
}
///若该标记为true时，需要校验该仓库存储区下所有物料的同步出库仓库、同步出库存储区必须相同，是否同步出库默认为false
string sameZoneSameFinalZoneValidFlag = new ConfigDAL().GetValueByCode("SAME_ZONE_SAME_FINAL_ZONE_VALID_FLAG");
//if (Convert.ToBoolean(sameZoneSameFinalZoneValidFlag) == true )
//{
// //判断数据库中是否存在 仓库、存储区、是否同步出库 如果没有跳过如果有进行判断是否同步出库仓库、同步出库存储区一致
// int count = new PartsStockDAL().GetCounts("[WM_NO] = N'" + info.WmNo + "' and[ZONE_NO] = N'" + info.ZoneNo );
// if (count != 0)
// {
// count = new PartsStockDAL().GetCounts("[WM_NO] = N'" + info.WmNo + "' and[ZONE_NO] = N'" + info.ZoneNo + "' and [IS_OUTPUT] = N'" + info.IsOutput + "' and [SYNCHRONOUS_WM_NO] = N'" + info.SynchronousWmNo + "' and [SYNCHRONOUS_ZONE_NO] = N'" + info.SynchronousZoneNo + "'");
// if (count == 0)
// throw new Exception("MC:0x00000438");///需要校验该仓库存储区下所有物料的同步出库仓库、同步出库存储区必须相同
// }
//}
return(dal.Add(info));
}
/// <summary>
/// PartsStockInfo -> BarcodeInfo
/// </summary>
/// <param name="partsStockInfo"></param>
/// <param name="barcodeInfo"></param>
public static void GetBarcodeInfo(PartsStockInfo partsStockInfo, ref BarcodeInfo barcodeInfo)
{
if (partsStockInfo == null)
{
return;
}
///PART_NICKNAME
barcodeInfo.PartNickname = partsStockInfo.PartNickname;
///MEASURING_UNIT_NO
barcodeInfo.MeasuringUnitNo = partsStockInfo.PartUnits;
///WM_NO
barcodeInfo.WmNo = partsStockInfo.WmNo;
///ZONE_NO
barcodeInfo.ZoneNo = partsStockInfo.ZoneNo;
///LINE_POSITION
barcodeInfo.LinePosition = partsStockInfo.LineSiteDloc;
///SUPERMARKET_ADDRESS
barcodeInfo.SupermarketAddress = partsStockInfo.SupperZoneDloc;
}
/// <summary>
/// PartsStockInfo -> VmiReceiveDetailInfo
/// </summary>
/// <param name="partsStockInfo"></param>
/// <param name="vmiReceiveDetailInfo"></param>
public static void GetVmiReceiveDetailInfo(PartsStockInfo partsStockInfo, ref VmiReceiveDetailInfo vmiReceiveDetailInfo)
{
if (partsStockInfo == null)
{
return;
}
///PACKAGE_MODEL
vmiReceiveDetailInfo.PackageModel = partsStockInfo.InboundPackageModel;
///PACKAGE
vmiReceiveDetailInfo.Package = partsStockInfo.InboundPackage;
///ASSEMBLY_LINE
vmiReceiveDetailInfo.AssemblyLine = partsStockInfo.AssemblyLine;
///INHOUSE_PACKAGE_MODEL
vmiReceiveDetailInfo.PackageModel = partsStockInfo.InhousePackageModel;
///INHOUSE_PACKAGE
vmiReceiveDetailInfo.Package = partsStockInfo.InhousePackage;
/////FINAL_WM
//vmiReceiveDetailInfo.FinalWm = partsStockInfo.SynchronousWmNo;
/////FINAL_ZONE
//vmiReceiveDetailInfo.FinalZone = partsStockInfo.SynchronousZoneNo;
///TARGET_DLOC
vmiReceiveDetailInfo.TargetDloc = partsStockInfo.Dloc;
}
/// <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>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardExcelInfos = CommonDAL.DatatableConvertToList <MaintainInhouseLogisticStandardInfo>(dataTable).ToList();
if (maintainInhouseLogisticStandardExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList());
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList());
List <PartsStockInfo> partsStockInfos = new PartsStockDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList());
List <PartsBoxInfo> partsBoxInfos = new PartsBoxDAL().GetList("", string.Empty);
///执行的SQL语句
string sql = string.Empty;
List <MaintainInhouseLogisticStandardInfo> standardInfos = new List <MaintainInhouseLogisticStandardInfo>();
fieldNames.Add("SWmNo", "S_WM_NO");
fieldNames.Add("SZoneNo", "S_ZONE_NO");
fieldNames.Add("TWmNo", "T_WM_NO");
fieldNames.Add("TZoneNo", "T_ZONE_NO");
fieldNames.Add("Plant", "PLANT");
fieldNames.Add("Workshop", "WORKSHOP");
fieldNames.Add("AssemblyLine", "ASSEMBLY_LINE");
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var maintainInhouseLogisticStandardExcelInfo in maintainInhouseLogisticStandardExcelInfos)
{
///
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo);
if (maintainPartsInfo == null)
{
throw new Exception("MC:0x00000224");///物料基础信息数据错误
}
maintainInhouseLogisticStandardExcelInfo.Status = (int)BasicDataStatusConstants.Created;
///物料简称、物料中文描述、物料英文描述由基础数据中同步
maintainInhouseLogisticStandardExcelInfo.PartCname = maintainPartsInfo.PartCname;
maintainInhouseLogisticStandardExcelInfo.PartEname = maintainPartsInfo.PartEname;
maintainInhouseLogisticStandardExcelInfo.PartNickname = maintainPartsInfo.PartNickname;
///
PartsBoxInfo partsBoxInfo = partsBoxInfos.FirstOrDefault(d => d.PullMode.ToString() == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
d.BoxParts == maintainInhouseLogisticStandardExcelInfo.InhousePartClass);
if (partsBoxInfo == null)
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
maintainInhouseLogisticStandardExcelInfo.SWmNo = partsBoxInfo.SWmNo;
maintainInhouseLogisticStandardExcelInfo.SZoneNo = partsBoxInfo.SZoneNo;
maintainInhouseLogisticStandardExcelInfo.TWmNo = partsBoxInfo.TWmNo;
maintainInhouseLogisticStandardExcelInfo.TZoneNo = partsBoxInfo.TZoneNo;
maintainInhouseLogisticStandardExcelInfo.Plant = partsBoxInfo.Plant;
maintainInhouseLogisticStandardExcelInfo.Workshop = partsBoxInfo.Workshop;
maintainInhouseLogisticStandardExcelInfo.AssemblyLine = partsBoxInfo.AssemblyLine;
///目标地点
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
d.WmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo &&
d.ZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo);
///无维护先后要求
if (partsStockInfo != null)
{
maintainInhouseLogisticStandardExcelInfo.InboundPackageModel = partsStockInfo.InboundPackageModel;
maintainInhouseLogisticStandardExcelInfo.InboundPackage = partsStockInfo.InboundPackage;
}
///当所选拉动零件类⑥的拉动方式⑤为10时间窗且其配置为库存当量拉动时，MIN⑯和MAX⑰允许维护大于零的数据，且MIN⑯小于MAX⑰
if (int.Parse(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) == (int)PullModeConstants.Twd)///TODO:缺少库存当量拉动的判断，等TWD表结构
{
if (maintainInhouseLogisticStandardExcelInfo.Min.GetValueOrDefault() > maintainInhouseLogisticStandardExcelInfo.Max.GetValueOrDefault())
{
throw new Exception("MC:0x00000404");///MIN值必须小于MAX
}
}
if (maintainInhouseLogisticStandardExcelInfo.IsTriggerPull.GetValueOrDefault() == true)
{
if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.WmNo))
{
throw new Exception("MC:0x00000405");///层级拉动仓库不允许为空
}
if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.ZoneNo))
{
throw new Exception("MC:0x00000406");///层级拉动存储区不允许为空
}
}
///
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d =>
d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass);
if (maintainInhouseLogisticStandardInfo == null)
{
if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.PartNo) ||
string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) ||
string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhousePartClass))
{
throw new Exception("MC:0x00000226");///物料号、拉动模式、零件类为必填项
}
///相同目标仓库存储区，同物料号同供应商，即使跨拉动方式也需要唯一
int cnt = maintainInhouseLogisticStandardInfos.Where(d =>
d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
d.TWmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo &&
d.TZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo &&
d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum).Count();
if (cnt > 0)
{
throw new Exception("MC:0x00000408");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
}
///物料号①、拉动方式⑤、拉动零件类⑥、供应商代码⑦组合唯一
cnt = maintainInhouseLogisticStandardInfos.Where(d =>
d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass &&
d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum).Count();
if (cnt > 0)
{
throw new Exception("MC:0x00000407");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <MaintainInhouseLogisticStandardInfo>(maintainInhouseLogisticStandardExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
sql += "if not exists (select * from LES.TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD with(nolock) "
+ "where [PART_NO] = N'" + maintainInhouseLogisticStandardExcelInfo.PartNo + "' and [INHOUSE_SYSTEM_MODE] = N'" + maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode + "' and [INHOUSE_PART_CLASS] = N'" + maintainInhouseLogisticStandardExcelInfo.InhousePartClass + "' and [VALID_FLAG] = 1) "
+ "insert into [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
maintainInhouseLogisticStandardInfos.Add(maintainInhouseLogisticStandardExcelInfo);
continue;
}
///
if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.PartNo) ||
string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) ||
string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhousePartClass))
{
throw new Exception("MC:0x00000226");///物料号、拉动模式、零件类为必填项
}
///相同目标仓库存储区，同物料号同供应商，即使跨拉动方式也需要唯一
int count = maintainInhouseLogisticStandardInfos.Where(d =>
d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
d.TWmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo &&
d.TZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo &&
d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum &&
d.Id != maintainInhouseLogisticStandardInfo.Id).Count();
if (count > 0)
{
throw new Exception("MC:0x00000408");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
}
///物料号①、拉动方式⑤、拉动零件类⑥、供应商代码⑦组合唯一
count = maintainInhouseLogisticStandardInfos.Where(d =>
d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass &&
d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum &&
d.Id != maintainInhouseLogisticStandardInfo.Id).Count();
if (count > 0)
{
throw new Exception("MC:0x00000407");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <MaintainInhouseLogisticStandardInfo>(maintainInhouseLogisticStandardExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + maintainInhouseLogisticStandardInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// 提交
/// </summary>
/// <param name="rowsKeyValues"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool SubmitInfos(List <string> rowsKeyValues, string loginUser)
{
///状态必须已创建
List <PackageInboundInfo> info = dal.GetList("[ID] IN (" + string.Join(",", rowsKeyValues) + ")", string.Empty);
if (info.Count == 0)
{
throw new Exception("MC:0x00000084");///数据有误
}
string sql = string.Empty;
List <PackageInboundDetailInfo> inboundDetailInfos = new PackageInboundDetailDAL().GetList(" [ORDER_FID] IN ('" + string.Join("','", info.Select(d => d.Fid).ToArray()) + "')", string.Empty);
List <PackageApplianceInfo> PackageAppliance = new PackageApplianceDAL().GetList("[PACKAGE_NO] IN ('" + string.Join("','", inboundDetailInfos.Select(d => d.PackageModel).ToArray()) + "')", string.Empty);
List <PartsStockInfo> partsStocks = new PartsStockDAL().GetList("[INBOUND_PACKAGE_MODEL] IN ('" + string.Join("','", inboundDetailInfos.Select(d => d.PackageModel).ToArray()) + "')", string.Empty);
foreach (var item in info)
{
if (item == null)
{
throw new Exception("MC:0x00000084");///数据有误
}
if (item.Status != (int)PackageInboundStatusConstants.Created)
{
throw new Exception("MC:0x00000128");///状态为10.已创建时可以进行提交，更新状态为20.已提交
}
List <PackageInboundDetailInfo> packageInboundDetails = inboundDetailInfos.Where(d => d.OrderFid == item.Fid).ToList();
sql += "update [LES].[TT_PCM_PACKAGE_INBOUND] set [STATUS] = " + (int)PackageInboundStatusConstants.Published + " where [ID] = " + item.Id + ";";
///根据器具入库单明细生成包装器具交易记录
foreach (var items in packageInboundDetails)
{
string packageCname = string.Empty;
PackageApplianceInfo infos = PackageAppliance.FirstOrDefault(d => d.PackageNo == items.PackageModel);
if (infos == null)
{
packageCname = "NULL";
}
else
{
packageCname = infos.PackageCname;
}
PartsStockInfo stockInfo = partsStocks.FirstOrDefault(d => d.InboundPackageModel == items.PackageModel);
string inboundPackage = string.Empty;
if (stockInfo == null)
{
inboundPackage = "NULL";
}
else
{
inboundPackage = stockInfo.InboundPackage.ToString();
}
sql += "insert into [LES].[TT_PCM_PACKAGE_TRAN_DETAIL] ([FID],[PLANT],[TRAN_NO],[TRAN_TYPE],[PACKAGE_CNAME],[PACKAGE],[SUPPLIER_NUM],[PACKAGE_NO],[WM_NO],[ZONE_NO],[DLOC],[TARGET_WM],[TARGET_ZONE],[TARGET_DLOC],[PACKAGE_QTY],[STATUS],[COMMENTS],[VALID_FLAG],[CREATE_USER],[CREATE_DATE])"
+ "VALUES(newid(),"
+ "N'" + item.Plant + "',"
+ "N'" + item.OrderNo + "',"
+ (int)PackageTranTypeConstants.EmptyInbound + ","
+ "N'" + packageCname + "',"
+ inboundPackage + ","
+ "N'" + items.SupplierNum + "',"
+ "N'" + items.PackageModel + "',"
+ "N'" + items.SWmNo + "',"
+ "N'" + items.SZoneNo + "',"
+ "N'" + items.SDloc + "',"
+ "N'" + items.TWmNo + "',"
+ "N'" + items.TZoneNo + "',"
+ "N'" + items.TDloc + "',"
+ "N'" + items.PackageQty + "',"
+ "N'" + items.PackageStatus + "',"
+ "N'" + items.Comments + "',"
+ "1,"
+ "N'" + loginUser + "',"
+ "GETDATE()"
+ ");";
}
}
using (TransactionScope trans = new TransactionScope())
{
CommonDAL.ExecuteNonQueryBySql(sql);
trans.Complete();
}
return(true);
}
/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <PartsStockInfo> partsStockExcelInfos = CommonDAL.DatatableConvertToList <PartsStockInfo>(dataTable).ToList();
if (partsStockExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <PartsStockInfo> partsStockInfos = new PartsStockDAL().GetListForInterfaceDataSync(partsStockExcelInfos.Select(d => d.PartNo).ToList());
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(partsStockExcelInfos.Select(d => d.PartNo).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var partsStockExcelInfo in partsStockExcelInfos)
{
///
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == partsStockExcelInfo.PartNo);
if (maintainPartsInfo == null)
{
throw new Exception("MC:0x00000224");///物料基础信息数据错误
}
///物料简称、物料中文描述、物料英文描述由基础数据中同步
partsStockExcelInfo.PartCname = maintainPartsInfo.PartCname;
partsStockExcelInfo.PartEname = maintainPartsInfo.PartEname;
partsStockExcelInfo.PartNickname = maintainPartsInfo.PartNickname;
///
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == partsStockExcelInfo.PartNo &&
d.SupplierNum == partsStockExcelInfo.SupplierNum &&
d.WmNo == partsStockExcelInfo.WmNo &&
d.ZoneNo == partsStockExcelInfo.ZoneNo);
if (partsStockInfo == null)
{
if (string.IsNullOrEmpty(partsStockExcelInfo.PartNo) ||
string.IsNullOrEmpty(partsStockExcelInfo.WmNo) ||
string.IsNullOrEmpty(partsStockExcelInfo.ZoneNo))
{
throw new Exception("MC:0x00000223");///物料号、仓库、存储区为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <PartsStockInfo>(partsStockExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
sql += "if not exists (select * from LES.TM_BAS_PARTS_STOCK with(nolock) "
+ "where [PART_NO] = N'" + partsStockExcelInfo.PartNo + "' "
+ "and [WM_NO] = N'" + partsStockExcelInfo.WmNo + "' "
+ "and [ZONE_NO] = N'" + partsStockExcelInfo.ZoneNo + "' "
+ "and [SUPPLIER_NUM] = N'" + partsStockExcelInfo.SupplierNum + "' and [VALID_FLAG] = 1) "
+ "insert into [LES].[TM_BAS_PARTS_STOCK] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
continue;
}
///
if (string.IsNullOrEmpty(partsStockExcelInfo.PartNo) ||
string.IsNullOrEmpty(partsStockExcelInfo.WmNo) ||
string.IsNullOrEmpty(partsStockExcelInfo.ZoneNo))
{
throw new Exception("MC:0x00000223");///物料号、仓库、存储区为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <PartsStockInfo>(partsStockExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_PARTS_STOCK] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + partsStockInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// 发货(提交）
/// </summary>
/// <param name="rowsKeyValues"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool ReleaseInfos(List <string> rowsKeyValues, string loginUser)
{
if (rowsKeyValues.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
List <VmiShippingPartInfo> vmiShippingPartInfos = new List <VmiShippingPartInfo>();
foreach (var rowsKeyValue in rowsKeyValues)
{
string[] keyValues = rowsKeyValue.Split(new char[] { '^' }, StringSplitOptions.RemoveEmptyEntries);
if (keyValues.Length == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
if (keyValues.Length == 1)
{
throw new Exception("MC:0x00000496");///预发货数量不能为空
}
VmiShippingPartInfo vmiShippingPartInfo = new VmiShippingPartInfo();
vmiShippingPartInfo.Id = Convert.ToInt64(keyValues[0]);
vmiShippingPartInfo.AsnConfirmQty = Convert.ToDecimal(keyValues[1]);
vmiShippingPartInfos.Add(vmiShippingPartInfo);
}
List <VmiShippingPartInfo> vmiShippingParts = dal.GetList("[ID] in (" + string.Join(",", vmiShippingPartInfos.Select(d => d.Id).ToArray()) + ")", string.Empty);
if (vmiShippingParts.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
List <VmiPullOrderInfo> vmiPullOrderInfos = new VmiPullOrderDAL().GetList("" +
"[FID] in ('" + string.Join("','", vmiShippingParts.Select(d => d.OrderFid.GetValueOrDefault().ToString()).ToArray()) + "') and " +
"[ORDER_STATUS] <> " + (int)PullOrderStatusConstants.Released + "", string.Empty);
if (vmiPullOrderInfos.Count > 0)
{
throw new Exception("MC:0x00000378");///状态为已发布时才能进行发货操作
}
///TODO:此时对应购物车中的相关数据是否需要清理
///预执行SQL脚本
StringBuilder @string = new StringBuilder();
///根据零件类、拉动模式等条件分组
var vmiShippingPartBoxs = vmiShippingParts.
GroupBy(d => new { d.PartBoxCode, d.PullMode, d.RouteCode, d.Plant, d.SWmNo, d.SZoneNo, d.TWmNo, d.TZoneNo, d.TDock }).
Select(d => new { d.Key }).ToList();
///获取零件仓储信息集合
List <PartsStockInfo> partsStockInfos = new PartsStockDAL().GetList("[PART_NO] in ('" + string.Join("','", vmiShippingParts.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
///发布VMI出库单时实发数量等于需求数量
string release_vmi_output_actual_qty_equals_required = new ConfigDAL().GetValueByCode("RELEASE_VMI_OUTPUT_ACTUAL_QTY_EQUALS_REQUIRED");
///遍历零件类
foreach (var vmiShippingPartBox in vmiShippingPartBoxs)
{
///
List <VmiShippingPartInfo> partInfos = vmiShippingParts.Where(d =>
d.PartBoxCode == vmiShippingPartBox.Key.PartBoxCode &&
d.PullMode == vmiShippingPartBox.Key.PullMode &&
d.RouteCode == vmiShippingPartBox.Key.RouteCode &&
d.Plant == vmiShippingPartBox.Key.Plant &&
d.SWmNo == vmiShippingPartBox.Key.SWmNo &&
d.SZoneNo == vmiShippingPartBox.Key.SZoneNo &&
d.TWmNo == vmiShippingPartBox.Key.TWmNo &&
d.TZoneNo == vmiShippingPartBox.Key.TZoneNo &&
d.TDock == vmiShippingPartBox.Key.TDock).ToList();
if (partInfos.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
///
VmiOutputInfo vmiOutputInfo = VmiOutputBLL.CreateVmiOutputInfo(loginUser);
///OUTPUT_NO,出库单号
vmiOutputInfo.OutputNo = new SeqDefineDAL().GetCurrentCode("VMI_OUTPUT_NO");
///ASN_NO,ASN编号,TODO:目前以是否写入了ASN_NO作为区别是否编辑ASN的分类，后续考虑增加字段ASN_FLAG？
vmiOutputInfo.AsnNo = vmiOutputInfo.OutputNo;
///WM_NO,仓库编码
vmiOutputInfo.WmNo = vmiShippingPartBox.Key.SWmNo;
///ZONE_NO,存贮区编码
vmiOutputInfo.ZoneNo = vmiShippingPartBox.Key.SZoneNo;
///T_WM_NO,目标仓库代码
vmiOutputInfo.TWmNo = vmiShippingPartBox.Key.TWmNo;
///T_ZONE_NO,目标存储区代码
vmiOutputInfo.TZoneNo = vmiShippingPartBox.Key.TZoneNo;
///T_DOCK,目标道口代码
vmiOutputInfo.TDock = vmiShippingPartBox.Key.TDock;
///PART_BOX_CODE,零件类代码
vmiOutputInfo.PartBoxCode = vmiShippingPartBox.Key.PartBoxCode;
///ROUTE,送货路径
vmiOutputInfo.Route = vmiShippingPartBox.Key.RouteCode;
///PULL_MODE,拉动方式
vmiOutputInfo.PullMode = vmiShippingPartBox.Key.PullMode;
///OUTPUT_TYPE,出库类型
vmiOutputInfo.OutputType = (int)VmiOutputTypeConstants.PullingOutbound;
///SEND_TIME,发送时间
vmiOutputInfo.SendTime = DateTime.Now;
///STATUS,出库单状态
vmiOutputInfo.Status = (int)WmmOrderStatusConstants.Published;
///
@string.AppendLine(VmiOutputDAL.GetInsertSql(vmiOutputInfo));
///行号
int rowNo = 0;
foreach (var partInfo in partInfos)
{
VmiShippingPartInfo shippingPartInfo = vmiShippingPartInfos.FirstOrDefault(d => d.Id == partInfo.Id);
if (shippingPartInfo == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
if (partInfo.AsnDraftQty.GetValueOrDefault() < shippingPartInfo.AsnConfirmQty.GetValueOrDefault())
{
throw new Exception("MC:0x00000501");///发货数量不能超过预发货数量
}
///获取对应零件仓储信息
PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d =>
d.WmNo == partInfo.TWmNo &&
d.ZoneNo == partInfo.TZoneNo &&
d.PartNo == partInfo.PartNo &&
d.SupplierNum == partInfo.SupplierNum);
if (partsStockInfo == null)
{
throw new Exception("MC:0x00000451");///物料仓储信息错误
}
///页面提交来的发货数量
partInfo.RequiredPartQty = shippingPartInfo.AsnConfirmQty;
///
VmiOutputDetailInfo vmiOutputDetailInfo = VmiOutputDetailBLL.CreateVmiOutputDetailInfo(loginUser);
///ROW_NO,行号
vmiOutputDetailInfo.RowNo = ++rowNo;
VmiOutputDetailBLL.GetVmiOutputDetailInfo(partInfo, ref vmiOutputDetailInfo);
VmiOutputDetailBLL.GetVmiOutputDetailInfo(vmiOutputInfo, ref vmiOutputDetailInfo);
VmiOutputDetailBLL.GetVmiOutputDetailInfo(partsStockInfo, ref vmiOutputDetailInfo);
VmiOutputDetailBLL.GetVmiOutputDetailInfo(ref vmiOutputDetailInfo);
if (!string.IsNullOrEmpty(release_vmi_output_actual_qty_equals_required) && release_vmi_output_actual_qty_equals_required.ToLower() == "true")
{
///ACTUAL_BOX_NUM,实际包装数
vmiOutputDetailInfo.ActualBoxNum = vmiOutputDetailInfo.RequiredBoxNum;
///ACTUAL_QTY,实际数量
vmiOutputDetailInfo.ActualQty = vmiOutputDetailInfo.RequiredQty;
}
@string.AppendLine(VmiOutputDetailDAL.GetInsertSql(vmiOutputDetailInfo));
///
string validFlagSql = string.Empty;
if (partInfo.AsnDraftQty.GetValueOrDefault() == shippingPartInfo.AsnConfirmQty.GetValueOrDefault())
{
validFlagSql = ",[VALID_FLAG] = 0";
}
///
@string.AppendLine("update [LES].[TE_MPM_VMI_SHIPPING_PART] " +
"set [ASN_DRAFT_QTY] = isnull([ASN_DRAFT_QTY],0) - " + shippingPartInfo.AsnConfirmQty.GetValueOrDefault() + "" + validFlagSql + ",[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' " +
"where [ID] = " + shippingPartInfo.Id + ";");
///
@string.AppendLine("update [LES].[TT_MPM_VMI_PULL_ORDER_DETAIL] " +
"set [ASN_DRAFT_QTY] = isnull([ASN_DRAFT_QTY],0) - " + shippingPartInfo.AsnConfirmQty.GetValueOrDefault() + "," +
"[ASN_CONFIRM_QTY] = isnull([ASN_CONFIRM_QTY],0) + " + shippingPartInfo.AsnConfirmQty.GetValueOrDefault() + "," +
"[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' " +
"where [FID] = N'" + partInfo.Fid.GetValueOrDefault() + "';");
}
}
///
using (var trans = new TransactionScope())
{
if (!CommonDAL.ExecuteNonQueryBySql(@string.ToString()))
{
throw new Exception("MC:0x00000173");///操作失败
}
trans.Complete();
}
return(true);
}
/// <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();
}
}