/// <summary>
/// 验证有效物料仓库和物料拉动信息-进行删除
/// </summary>
/// <param name="id"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool LogicDeleteInfo(long id, string loginUser)
{
///已有有效物料仓储信息或物料拉动信息的物料数据不允许进行删除操作
int cnt = new MaintainInhouseLogisticStandardDAL().GetCounts("[PART_NO] in (select [PART_NO] from [LES].[TM_BAS_MAINTAIN_PARTS] with(nolock) where [ID] = " + id + " and [VALID_FLAG] = 1)");
if (cnt > 0)
{
throw new Exception("MC:0x00000727");///已有有效物料拉动信息,无法删除
}
cnt = new PartsStockDAL().GetCounts("[PART_NO] in (select [PART_NO] from [LES].[TM_BAS_MAINTAIN_PARTS] with(nolock) where [ID] = " + id + " and [VALID_FLAG] = 1)");
if (cnt > 0)
{
throw new Exception("MC:0x00000728");///已有有效物料拉动信息,无法删除
}
return(dal.LogicDelete(id, loginUser) > 0 ? true : false);
}
/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
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>
/// UpdateInfo
/// </summary>
/// <param name="fields"></param>
/// <param name="id"></param>
/// <returns></returns>
public bool UpdateInfo(string fields, long id)
{
string inboundPackageModel = CommonBLL.GetFieldValue(fields, "INBOUND_PACKAGE_MODEL");
string inboundPackage = CommonBLL.GetFieldValue(fields, "INBOUND_PACKAGE");
///入库包装数量⑧不能小于等于零，当入库包装型号⑦不为空时入库包装数量⑧为必填项
if (!string.IsNullOrEmpty(inboundPackageModel) && int.Parse(inboundPackage) <= 0)
{
throw new Exception("MC:0x00000089");///当入库包装型号不为空时入库包装数量必须大于零
}
if (CommonBLL.GetFieldValue(fields, "IS_OUTPUT") == "1")
{
if (CommonBLL.GetFieldValue(fields, "SYNCHRONOUS_WM_NO") == "" || CommonBLL.GetFieldValue(fields, "SYNCHRONOUS_ZONE_NO") == "")
{
throw new Exception("MC:0x00000439");///当同步出库标记为是时,同步出库仓库,存储区为必填项
}
}
string min = CommonBLL.GetFieldValue(fields, "MIN");
int intMin = 0;
int.TryParse(min, out intMin);
string safeStock = CommonBLL.GetFieldValue(fields, "SAFE_STOCK");
int intSafeStock = 0;
int.TryParse(safeStock, out intSafeStock);
///最小库存 小于等于 安全库存 小于 最大库存，最小库存必须大于等于零
if (intMin > intSafeStock)
{
throw new Exception("MC:0x00000090");///最小库存不能大于安全库存
}
string max = CommonBLL.GetFieldValue(fields, "MAX");
int intMax = 0;
int.TryParse(max, out intMax);
if (intSafeStock >= 0 && intMax > 0 && intSafeStock >= intMax)
{
throw new Exception("MC:0x00000091");///安全库存不能大于等于最大库存
}
string partNo = CommonBLL.GetFieldValue(fields, "PART_NO");
string supplierNum = CommonBLL.GetFieldValue(fields, "SUPPLIER_NUM");
string wmNo = CommonBLL.GetFieldValue(fields, "WM_NO");
string zoneNo = CommonBLL.GetFieldValue(fields, "ZONE_NO");
string dloc = CommonBLL.GetFieldValue(fields, "DLOC");
///相同物料号②、供应商代码①、仓库代码⑨、存储区代码⑩、库位代码⑪的数据不允许重复
int cnt = dal.GetCounts(" [ID] <> " + id + " and [PART_NO] = N'" + partNo + "' and [SUPPLIER_NUM] = N'" + supplierNum + "' and [WM_NO] = N'" + wmNo + "' and [ZONE_NO] = N'" + zoneNo + "'"
+ (string.IsNullOrEmpty(dloc) ? string.Empty : " and [DLOC] = N'" + dloc + "'"));
if (cnt > 0)
{
throw new Exception("MC:0x00000094");///相同物料号、供应商代码、仓库代码、存储区代码、库位代码的数据不允许重复
}
string sameZoneSameFinalZoneValidFlag = new ConfigDAL().GetValueByCode("SAME_ZONE_SAME_FINAL_ZONE_VALID_FLAG");
///TODO:若该标记为true时，需要校验该仓库存储区下所有物料的同步出库仓库、同步出库存储区必须相同，是否同步出库默认为false
if (Convert.ToBoolean(sameZoneSameFinalZoneValidFlag) == true && CommonBLL.GetFieldValue(fields, "IS_OUTPUT") == "1")
{
///判断当前 仓库、存储区、是否同步出库 是否在表中存在，如果没有进行修改，如果有进行判断
int count = new PartsStockDAL().GetCounts("[ID] <> " + id + " and [WM_NO] = N'" + CommonBLL.GetFieldValue(fields, "WM_NO") + "' and[ZONE_NO] = N'" + CommonBLL.GetFieldValue(fields, "ZONE_NO") + "' and [IS_OUTPUT] = N'" + CommonBLL.GetFieldValue(fields, "IS_OUTPUT") + "'");
if (count != 0)
{
count = new PartsStockDAL().GetCounts("[ID] <> " + id + " and [WM_NO] = N'" + CommonBLL.GetFieldValue(fields, "WM_NO") + "' and[ZONE_NO] = N'" + CommonBLL.GetFieldValue(fields, "ZONE_NO") + "' and [IS_OUTPUT] = N'" + CommonBLL.GetFieldValue(fields, "IS_OUTPUT") + "' and [SYNCHRONOUS_WM_NO] = N'" + CommonBLL.GetFieldValue(fields, "SYNCHRONOUS_WM_NO") + "' and [SYNCHRONOUS_ZONE_NO] = N'" + CommonBLL.GetFieldValue(fields, "SYNCHRONOUS_ZONE_NO") + "'");
if (count == 0)
{
throw new Exception("MC:0x00000438");///需要校验该仓库存储区下所有物料的同步出库仓库、同步出库存储区必须相同
}
}
}
return(dal.UpdateInfo(fields, id) > 0 ? true : false);
}
/// <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>
/// 发布
/// </summary>
/// <param name="aId"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool StartInfos(List <string> rowsKeyValues, string loginUser)
{
if (dal.GetList("[ID] IN (" + string.Join(",", rowsKeyValues) + ") and [STATUS] = " + (int)InventoryOrderStatusConstants.CREATED + "", string.Empty).Count == 0)
{
throw new Exception("MC:0x00000369");///状态为已创建的盘点单才允许进行发布
}
List <InventoryNoticeOrderInfo> info = dal.GetList("[ID] IN (" + string.Join(",", rowsKeyValues) + ")", string.Empty);
if (info.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
List <PartsStockInfo> partsStocks = new PartsStockDAL().GetList("[WM_NO] in ('" + string.Join("','", info.Select(d => d.WmNo).ToArray()) + "') and [ZONE_NO] in ('" + string.Join("','", info.Select(d => d.ZoneNo).ToArray()) + "') and [KEEPER] in ('" + string.Join("','", info.Select(d => d.Keeper).ToArray()) + "')", string.Empty);
if (partsStocks.Count == 0)
{
throw new Exception("MC:0x00000370");///没有相关的物料仓储信息
}
List <StocksInfo> stocks = new StocksDAL().GetList("[WM_NO] in ('" + string.Join("','", partsStocks.Select(d => d.WmNo).ToArray()) + "') and [ZONE_NO] in ('" + string.Join("','", partsStocks.Select(d => d.ZoneNo).ToArray()) + "') and [PACKAGE_MODEL] in ('" + string.Join("','", partsStocks.Select(d => d.InboundPackageModel).ToArray()) + "') and [PART_NO] in ('" + string.Join("','", partsStocks.Select(d => d.PartNo).ToArray()) + "') and [SUPPLIER_NUM] in ('" + string.Join("','", partsStocks.Select(d => d.SupplierNum).ToArray()) + "')", string.Empty);
string check = new ConfigBLL().GetValueByCode("NO_STOCK_MATERIAL_NOT_RELEASE_TO_INVENTORY_ORDER");
string stock = new ConfigBLL().GetValueByCode("MATERIALS_USED_IN_AVAILABLE_STOCK");
string sql = string.Empty;
foreach (var item in info)
{
List <PartsStockInfo> partsStockInfos = partsStocks.Where(d => d.Keeper == item.Keeper && d.WmNo == item.WmNo && d.ZoneNo == item.ZoneNo).ToList();
if (partsStockInfos.Count == 0)
{
throw new Exception("MC:0x00000370");///没有相关的物料仓储信息
}
foreach (var items in partsStockInfos)
{
List <StocksInfo> stocksInfos = stocks.Where(d => d.Keeper == items.Keeper && d.WmNo == items.WmNo && d.ZoneNo == item.ZoneNo && d.PartNo == items.PartNo).ToList();
string Package = items.Package == null ? "null" : items.Package.ToString();
if (check == "true")
{
int sum = 0;
if (stock == "true")
{
sum = (int)stocksInfos.Select(d => d.AvailbleStocks).Sum();
}
else
{
sum = (int)stocksInfos.Select(d => d.StocksNum).Sum();
}
if (sum > 0)
{
sql += "insert into [LES].[TT_WMM_INVENTORY_ORDER_PART] "
+ "([FID],[ORDER_CODE],[ORDER_FID],[WM_NO],[ZONE_NO],[DLOC],[PART_NO],[SUPPLIER_NUM],[PACKAGE_MODEL],[PACKAGE],[PART_CNAME],[PACKAGE_QTY],[REFERENCE_QTY],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])"
+ "values("
+ "newid(),"
+ "N'" + item.OrderCode + "',"
+ "N'" + item.Fid + "',"
+ "N'" + items.WmNo + "',"
+ "N'" + items.ZoneNo + "',"
+ "N'" + items.Dloc + "',"
+ "N'" + items.PartNo + "',"
+ "N'" + items.SupplierNum + "',"
+ "N'" + items.InboundPackageModel + "',"
+ Package + ","
+ "N'" + items.PartCname + "',"
+ "N'" + stocksInfos.Select(d => d.Stocks).Sum() + "',"
+ "N'" + sum + "',"
+ "1,"
+ "GETDATE(),"
+ "N'" + loginUser + "'"
+ ")\n";
}
}
else
{
int sum = 0;
if (stock == "true")
{
sum = (int)stocksInfos.Select(d => d.AvailbleStocks).Sum();
}
else
{
sum = (int)stocksInfos.Select(d => d.StocksNum).Sum();
}
sql += "insert into [LES].[TT_WMM_INVENTORY_ORDER_PART] "
+ "([FID],[ORDER_CODE],[ORDER_FID],[WM_NO],[ZONE_NO],[DLOC],[PART_NO],[SUPPLIER_NUM],[PACKAGE_MODEL],[PACKAGE],[PART_CNAME],[PACKAGE_QTY],[REFERENCE_QTY],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])"
+ "values("
+ "newid(),"
+ "N'" + item.OrderCode + "',"
+ "N'" + item.Fid + "',"
+ "N'" + items.WmNo + "',"
+ "N'" + items.ZoneNo + "',"
+ "N'" + items.Dloc + "',"
+ "N'" + items.PartNo + "',"
+ "N'" + items.SupplierNum + "',"
+ "N'" + items.PackageModel + "',"
+ Package + ","
+ "N'" + items.PartCname + "',"
+ "N'" + stocksInfos.Select(d => d.Stocks).Sum() + "',"
+ "N'" + sum + "',"
+ "1,"
+ "GETDATE(),"
+ "N'" + loginUser + "'"
+ ")\n";
}
}
}
using (TransactionScope trans = new TransactionScope())
{
if (string.IsNullOrEmpty(sql))
{
throw new Exception("MC:0x00000440");///没有相关物料明细
}
sql += "update [LES].[TT_WMM_INVENTORY_NOTICE_ORDER] set [STATUS] = " + (int)InventoryOrderStatusConstants.PUBLISHED + ",[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [VALID_FLAG] = 1 and [ID] IN (" + string.Join(",", rowsKeyValues) + ") ";
CommonDAL.ExecuteNonQueryBySql(sql);
trans.Complete();
}
return(true);
}
