/// <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>
/// 执行导入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="dataTable"></param>
/// <param name="fieldNames"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <ReceiveDetailInfo> receiveDetailInfos = CommonDAL.DatatableConvertToList <ReceiveDetailInfo>(dataTable).ToList();
if (receiveDetailInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(receiveDetailInfos.Select(d => d.PartNo).ToList());
///SUPPLIER_NUM.60、BOOK_KEEPER、RECEIVE_TYPE.70、TRAN_TIME.80、ORGANIZATION_FID.90、WM_NO.30、COST_CENTER.100、CONTRACT_NO.110合并
var groupReceives = receiveDetailInfos
.GroupBy(b => new { b.SupplierNum, b.BookKeeper, b.ReceiveType, b.TranTime, b.OrganizationFid, b.WmNo, b.CostCenter, b.ContractNo })
.Select(p => new { p.Key }).ToList();
string sql = string.Empty;
foreach (var groupReceive in groupReceives)
{
string receiveNo = new SeqDefineDAL().GetCurrentCode("RECEIVE_NO");
Guid receiveFid = Guid.NewGuid();
List <ReceiveDetailInfo> receiveDetailList = receiveDetailInfos.Where(d =>
d.SupplierNum == groupReceive.Key.SupplierNum &&
d.BookKeeper == groupReceive.Key.BookKeeper &&
d.ReceiveType == groupReceive.Key.ReceiveType &&
d.TranTime == groupReceive.Key.TranTime &&
d.OrganizationFid == groupReceive.Key.OrganizationFid &&
d.WmNo == groupReceive.Key.WmNo &&
d.CostCenter == groupReceive.Key.CostCenter &&
d.ContractNo == groupReceive.Key.ContractNo).ToList();
int rowNo = 1;
foreach (var receiveDetail in receiveDetailList)
{
///PART_NO.10、PART_ENAME.60、ZONE_NO.40、DLOC.50、ACTUAL_QTY.20
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == receiveDetail.PartNo);
if (maintainPartsInfo == null)
{
if (string.IsNullOrEmpty(receiveDetail.PartNo))
{
throw new Exception("MC:0x00000227");///器材编号不能为空
}
sql += "insert into [LES].[TM_BAS_MAINTAIN_PARTS] "
+ "(FID, PART_NO, PART_CNAME, PART_ENAME, PART_UNITS, PART_CLS, PART_GROUP, PURCHASE_UNIT_PRICE, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
+ "(NEWID(), "
+ "N'" + receiveDetail.PartNo + "', "
+ "N'" + receiveDetail.PartCname + "', "
+ "N'" + receiveDetail.PartEname + "', "
+ "N'" + receiveDetail.PartUnits + "', "
+ "N'" + receiveDetail.PartCls + "', "
+ "N'" + receiveDetail.PartGroup + "', "
+ "" + receiveDetail.PurchaseUnitPrice.GetValueOrDefault() + ", "
+ "1, N'" + loginUser + "', GETDATE());";
}
else
{
///物料描述
receiveDetail.PartCname = maintainPartsInfo.PartCname;
///规格型号
receiveDetail.PartCls = maintainPartsInfo.PartCls;
///产地
// receiveDetail.OriginPlace = maintainPartsInfo.OriginPlace;
///计量单位
receiveDetail.MeasuringUnitNo = maintainPartsInfo.PartUnits;
///物料采购单价
//receiveDetail.PurchaseUnitPrice = maintainPartsInfo.PurchaseUnitPrice;
}
receiveDetail.PartPrice = receiveDetail.PurchaseUnitPrice.GetValueOrDefault() * receiveDetail.ActualQty.GetValueOrDefault();
sql += "insert into [LES].[TT_WMM_RECEIVE_DETAIL] "
+ "(FID, RECEIVE_FID, SUPPLIER_NUM, TARGET_WM, TARGET_ZONE, TARGET_DLOC, PART_NO, PART_CNAME, PART_ENAME, MEASURING_UNIT_NO, ACTUAL_QTY, TRAN_NO, RUNSHEET_NO, ROW_NO, ORIGIN_PLACE, PURCHASE_UNIT_PRICE, PART_PRICE, PART_CLS, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
+ "(NEWID(), "
+ "N'" + receiveFid + "', "
+ "N'" + groupReceive.Key.SupplierNum + "', "
+ "N'" + receiveDetail.WmNo + "', "
+ "N'" + receiveDetail.ZoneNo + "', "
+ "N'" + receiveDetail.Dloc + "', "
+ "N'" + receiveDetail.PartNo + "', "
+ "N'" + receiveDetail.PartCname + "', "
+ "N'" + receiveDetail.PartEname + "', "
+ "N'" + receiveDetail.PartUnits + "', "
+ "" + receiveDetail.ActualQty.GetValueOrDefault() + ", "
+ "N'" + receiveNo + "', "
+ "N'" + groupReceive.Key.ContractNo + "', "
+ "" + rowNo++ + ", "
+ "N'" + receiveDetail.OriginPlace + "', "
+ "" + receiveDetail.PurchaseUnitPrice.GetValueOrDefault() + ", "
+ "" + receiveDetail.PartPrice.GetValueOrDefault() + ", "
+ "N'" + receiveDetail.PartCls + "', "
+ "1, N'" + loginUser + "', GETDATE());";
}
///物料价格合计
decimal sumOfPrice = receiveDetailList.Sum(d => d.PartPrice.GetValueOrDefault());
///物料合计数量
decimal sumPartQty = receiveDetailList.Sum(d => d.ActualQty.GetValueOrDefault());
if (!DateTime.TryParse(groupReceive.Key.TranTime.ToString("yyyy-MM-dd HH:mm:ss"), out DateTime tranTime))
{
throw new Exception("MC:0x00000328");///入库时间不能为空
}
sql += "insert into [LES].[TT_WMM_RECEIVE] "
+ "(FID, RECEIVE_NO, SUPPLIER_NUM, WM_NO, RECEIVE_TYPE, TRAN_TIME, BOOK_KEEPER, STATUS, RUNSHEET_NO, ORGANIZATION_FID, COST_CENTER, SUM_PART_QTY, SUM_OF_PRICE, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
+ "(N'" + receiveFid + "', "
+ "N'" + receiveNo + "', "
+ "N'" + groupReceive.Key.SupplierNum + "', "
+ "N'" + groupReceive.Key.WmNo + "', "
+ "" + groupReceive.Key.ReceiveType + ", "
+ "N'" + tranTime.ToString("yyyy-MM-dd HH:mm:ss") + "', "
+ "N'" + groupReceive.Key.BookKeeper + "', "
+ "" + (int)WmmOrderStatusConstants.Created + ", "
+ "N'" + groupReceive.Key.ContractNo + "', "
+ "N'" + groupReceive.Key.OrganizationFid + "', "
+ "N'" + groupReceive.Key.CostCenter + "', "
+ "" + sumPartQty + ", "
+ "" + sumOfPrice + ", "
+ "1, N'" + loginUser + "', GETDATE());";
}
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
/// <summary>
/// 完成
/// </summary>
/// <param name="id"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool GenerationdifferenceInfos(List <string> rowsKeyValues, string loginUser)
{
List <InventoryNoticeOrderInfo> inventoryNoticeOrderInfos = dal.GetList("[ID] IN (" + string.Join(",", rowsKeyValues) + ") and [STATUS] = " + (int)InventoryOrderStatusConstants.CONFIRMED + "", string.Empty);
if (inventoryNoticeOrderInfos.Count == 0)
{
throw new Exception("MC:0x00000376");///已确认状态才能进行完成操作
}
StringBuilder stringBuilder = new StringBuilder();
string check = new ConfigBLL().GetValueByCode("ENABLE_SAP_INVENTORY_REPORT_FLAG");
if (check == "true")
{
List <InventoryNoticeOrderInfo> infos = new List <InventoryNoticeOrderInfo>();
List <InventoryOrderPartInfo> orderPartInfos = new InventoryOrderPartDAL().GetList("[ORDER_FID] IN ('" + string.Join("','", inventoryNoticeOrderInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty);
List <InventoryPartBarcodeInfo> inventoryPartBarcodes = new InventoryPartBarcodeDAL().GetList("[PART_FID] IN ('" + string.Join("','", orderPartInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty);
Dictionary <string, Guid> keyValues = new Dictionary <string, Guid>();
List <MaintainPartsInfo> maintainParts = new MaintainPartsDAL().GetList("[PART_NO] IN ('" + string.Join("','", orderPartInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
foreach (var item in inventoryNoticeOrderInfos)
{
///生成SAP盘点报告中间表数据
Guid guid = Guid.NewGuid();
if (keyValues.Keys.Contains <string>(item.SapIblnr) == false)
{
keyValues.Add(item.SapIblnr, guid);
}
List <InventoryOrderPartInfo> partInfos = orderPartInfos.Where(d => d.OrderFid == item.Fid).ToList();
foreach (var items in partInfos)
{
string PartName = maintainParts.FirstOrDefault(d => d.PartNo == items.PartNo).PartCname;
stringBuilder.Append("insert into [LES].[TI_IFM_SAP_INVENTORY_CHECK_REPORT] ([FID],[LOG_FID],[IBLNR],[LGORT],[REMARKS],[MATNR],[MENGE],[ZLDAT],[AQTY],[DQTY],[PROCESS_FLAG],[PROCESS_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE])values(");
string partQty = string.Empty;
if (items.PartQty == null)
{
partQty = "NULL";
}
else
{
partQty = ((int)items.PartQty).ToString();
}
string sapDqty = string.Empty;
if (items.SapDqty == null)
{
sapDqty = "NULL";
}
else
{
sapDqty = ((int)items.SapDqty).ToString();
}
stringBuilder.Append("newid(),N'" + keyValues[item.SapIblnr] + "',N'" + item.SapIblnr + "',N'" + item.SapLgort + "',N'" + item.Comments + "',N'" + items.PartNo + "'," + partQty + ",N'" + item.SapZldat + "',N'" + items.SapMenge + "'," + sapDqty + ",N'" + item.Status + "',N'" + item.ModifyDate + "',1,N'" + loginUser + "',GETDATE());\n\n");
InventoryPartBarcodeInfo barcodeInfo = inventoryPartBarcodes.Where(d => d.PartFid == items.Fid).FirstOrDefault();
if (barcodeInfo != null)
{
if (barcodeInfo.PartQty != items.DifferenceQty)
{
stringBuilder.Append("update [LES].[TT_WMM_INVENTORY_PART_BARCODE] set [PART_QTY] = N'" + items.DifferenceQty + "',[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] = " + barcodeInfo.Id + ";\n\n");
}
}
}
///新数据排除盘点计划号一致的
InventoryNoticeOrderInfo InventoryNoticeOrderInfos = infos.Where(d => d.SapIblnr == item.SapIblnr).FirstOrDefault();
if (InventoryNoticeOrderInfos == null)
{
infos.Add(item);
}
}
List <InventoryNoticeOrderInfo> inventoryNotices = dal.GetList(" [ID] not in (" + string.Join(",", rowsKeyValues) + ") and [STATUS] = " + (int)InventoryOrderStatusConstants.CONFIRMED + " and [SAP_IBLNR] in ('" + string.Join("','", inventoryNoticeOrderInfos.Select(d => d.SapIblnr).ToArray()) + "')", string.Empty);
foreach (var item in infos)
{
List <InventoryNoticeOrderInfo> inventoryNoticeOrders = inventoryNotices.Where(d => d.SapIblnr == item.SapIblnr).ToList();
List <InventoryNoticeOrderInfo> inventories = inventoryNotices.Where(d => d.SapIblnr == item.SapIblnr && d.Status == item.Status).ToList();
if (inventoryNoticeOrders.Count == inventories.Count)
{
//生成盘点报告发送任务
string targetSystem = "SAP";
string methodCode = "LES-WMM-071";
string keyValue = item.SapIblnr;
stringBuilder.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql(targetSystem, keyValues[item.SapIblnr], methodCode, keyValue, loginUser));
}
}
}
using (TransactionScope trans = new TransactionScope())
{
if (!string.IsNullOrEmpty(stringBuilder.ToString()))
{
stringBuilder.Append("\n\n update [LES].[TT_WMM_INVENTORY_NOTICE_ORDER] set [STATUS] = " + (int)InventoryOrderStatusConstants.COMPLETED + ",[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [VALID_FLAG] = 1 and [ID] IN (" + string.Join(",", rowsKeyValues) + ") ");
CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString());
}
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 <MaintainPartsInfo> maintainPartsExcelInfos = CommonDAL.DatatableConvertToList <MaintainPartsInfo>(dataTable).ToList();
if (maintainPartsExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(maintainPartsExcelInfos.Select(d => d.PartNo).ToList());
///执行的SQL语句
string sql = string.Empty;
///获取工厂信息
List <PlantInfo> plantInfos = new PlantDAL().GetListForInterfaceDataSync();
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var maintainPartsExcelInfo in maintainPartsExcelInfos)
{
///导入时需要填写LES的工厂编号
PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.Plant == maintainPartsExcelInfo.Plant);
if (plantInfo == null)
{
throw new Exception("MC:0x00000215");///工厂代码在系统中不存在
}
///当前业务数据表中此工厂的该物料信息时需要新增
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == maintainPartsExcelInfo.PartNo && d.Plant == maintainPartsExcelInfo.Plant);
if (maintainPartsInfo == null)
{
///物料号①、物料中文名称②为必填项
if (string.IsNullOrEmpty(maintainPartsExcelInfo.PartCname) || string.IsNullOrEmpty(maintainPartsExcelInfo.PartNo))
{
throw new Exception("MC:3x00000020");///物料号、物料中文名称为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <MaintainPartsInfo>(maintainPartsExcelInfo, 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_PARTS with(nolock) where [PART_NO] = N'" + maintainPartsExcelInfo.PartNo + "' and [PLANT] = N'" + maintainPartsExcelInfo.Plant + "' and [VALID_FLAG] = 1) "
+ "insert into [LES].[TM_BAS_MAINTAIN_PARTS] ("
+ "[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(maintainPartsExcelInfo.PartCname))
{
throw new Exception("MC:3x00000020");///物料号、物料中文名称为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <MaintainPartsInfo>(maintainPartsExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_MAINTAIN_PARTS] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + maintainPartsInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}