/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser, string where)
{
List <InventoryOrderPartInfo> inventoryOrderPartInfos = CommonDAL.DatatableConvertToList <InventoryOrderPartInfo>(dataTable).ToList();
if (inventoryOrderPartInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
StringBuilder stringBuilder = new StringBuilder();
List <InventoryOrderPartInfo> orderPartInfos = dal.GetList(where, string.Empty);
foreach (var item in inventoryOrderPartInfos)
{
InventoryOrderPartInfo inventory = orderPartInfos.Where(d => d.Dloc == item.Dloc && d.PartNo == item.PartNo && d.SupplierNum == item.SupplierNum && d.PackageModel == item.PackageModel && d.PackageQty == item.PackageQty).FirstOrDefault();
if (inventory == null)
{
throw new Exception("MC:0x00000255");///数据格式不符合导入规范
}
if (item.PartQty == null)
{
throw new Exception("MC:0x00000380");///物料盘点数量为必填项
}
bool regular = Regex.IsMatch(item.PartQty.ToString(), @"^(^-?|^\+?|\d)\d+$");
if (regular == false)
{
regular = Regex.IsMatch(item.PartQty.ToString(), @"^(^-?|^\+?|^\d?)\d*\.\d+$");
if (regular == false)
{
throw new Exception("MC:0x00000381");///物料盘点数量的数据格式不正确，请输入数字格式
}
}
inventory.DifferenceQty = item.PartQty - (inventory.ReferenceQty == null ? 0 : inventory.ReferenceQty);
inventory.SapDqty = item.PartQty - (inventory.SapMenge == null ? 0 : inventory.SapMenge);
stringBuilder.Append("update [LES].[TT_WMM_INVENTORY_ORDER_PART] set [DIFFERENCE_QTY] = N'" + inventory.DifferenceQty + "',[SAP_DQTY] = N'" + inventory.SapDqty + "',[PART_QTY] = N'" + item.PartQty + "',[COMMENTS] = N'" + item.Comments + "',[MODIFY_USER] = N'" + loginUser + "',[MODIFY_DATE] = GETDATE() where [FID] = N'" + inventory.Fid + "';\n\n");
}
if (string.IsNullOrEmpty(stringBuilder.ToString()))
{
throw new Exception("MC:0x00000283");///:没有可导入更新的数据
}
return(CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString()));
}
/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
DateTime dtDate;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (!DateTime.TryParse(dataTable.Rows[i]["WorkDay"].ToString(), out dtDate))
{
throw new Exception("MC:0x00000393");///日期格式不正确
}
if (!DateTime.TryParse(dataTable.Rows[i]["WindowTime"].ToString(), out dtDate))
{
throw new Exception("MC:0x00000393");///日期格式不正确
}
}
///导入数据
List <TwdWindowTimeInfo> twdWindows = CommonDAL.DatatableConvertToList <TwdWindowTimeInfo>(dataTable).ToList();
if (twdWindows.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///比对数据
List <TwdWindowTimeInfo> timeInfos = dal.GetList("[PART_BOX_CODE] in ('" + string.Join("','", twdWindows.Select(d => d.PartBoxCode).ToArray()) + "') and [WORK_DAY] in ('" + string.Join("','", twdWindows.Select(d => d.WorkDay).ToArray()) + "') and [WINDOW_TIME] in ('" + string.Join("','", twdWindows.Select(d => d.WindowTime).ToArray()) + "')", string.Empty).ToList();
///零件类数据
List <TwdPartBoxInfo> twdParts = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("','", twdWindows.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty).ToList();
if (twdParts.Count() != twdWindows.Count())
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
List <string> fields = new List <string>(fieldNames.Keys);
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in twdWindows)
{
TwdWindowTimeInfo timeInfo = timeInfos.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode && d.WorkDay == item.WorkDay && d.WindowTime == item.WindowTime);
TwdPartBoxInfo boxInfo = twdParts.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode);
///发单时间 = 工作日年月日 + 窗口时间时分秒 - 提前时间
int advanceTime = boxInfo.RequirementAccumulateTime.GetValueOrDefault() + ///需求累积时间
boxInfo.LoadTime.GetValueOrDefault() + ///装货时间
boxInfo.TransportTime.GetValueOrDefault() + ///运输时间
boxInfo.UnloadTime.GetValueOrDefault(); ///卸货时间
item.SendTime = item.WindowTime.GetValueOrDefault().AddMinutes(-advanceTime);
if (timeInfo == null)
{
if (item.WindowTime == null || item.WorkDay == null || item.PartBoxCode == null)
{
throw new Exception("MC:0x00000510");///零件类工作日窗口时间不可为空
}
stringBuilder.Append("insert into [LES].[TT_MPM_TWD_WINDOW_TIME]([FID],[PART_BOX_FID],[PART_BOX_CODE],[PART_BOX_NAME],[PLANT],[WORKSHOP],[ASSEMBLY_LINE],[SUPPLIER_NUM],[WORK_DAY],[SEND_TIME],[WINDOW_TIME],[SEND_TIME_STATUS],[TIME_ZONE],[COMMENTS],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])values(");
stringBuilder.Append("newid(),N'" + boxInfo.Fid + "',N'" + item.PartBoxCode + "',N'" + boxInfo.PartBoxName + "',N'" + boxInfo.Plant + "',N'" + boxInfo.Workshop + "',N'" + boxInfo.AssemblyLine + "',N'" + boxInfo.SupplierNum + "',N'" + item.WorkDay + "',N'" + item.SendTime + "',N'" + item.WindowTime + "'," + (int)SendTimeStatusConstants.NoSend + ",N'" + item.TimeZone + "',N'" + item.Comments + "',1,GETDATE(),N'" + loginUser + "'");
stringBuilder.Append(");");
timeInfos.Add(item);
continue;
}
if (timeInfo.SendTimeStatus != (int)SendTimeStatusConstants.NoSend)
{
throw new Exception("MC:0x00000311");///发单状态⑪为10未发单才允许被修改
}
if (item.WindowTime == null || item.WorkDay == null || item.PartBoxCode == null)
{
throw new Exception("MC:0x00000510");///零件类工作日窗口时间不可为空
}
stringBuilder.Append("update [LES].[TT_MPM_TWD_WINDOW_TIME] set [PLANT] = N'" + boxInfo.Plant + "',[WORKSHOP] = N'" + boxInfo.Workshop + "',[ASSEMBLY_LINE] = N'" + boxInfo.AssemblyLine + "',[SUPPLIER_NUM] = N'" + boxInfo.SupplierNum + "',[SEND_TIME] = N'" + item.SendTime + "',[COMMENTS] = N'" + item.Comments + "',[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] = " + timeInfo.Id + ";");
}
if (string.IsNullOrEmpty(stringBuilder.ToString()))
{
throw new Exception("MC:0x00000283");///没有可导入更新的数据
}
return(CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString()));
}
/// <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 <ZonesInfo> zonesExcelInfos = CommonDAL.DatatableConvertToList <ZonesInfo>(dataTable).ToList();
if (zonesExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <ZonesInfo> zonesInfos = new ZonesDAL().GetList("" +
"[ZONE_NO] in ('" + string.Join("','", zonesExcelInfos.Select(d => d.ZoneNo).ToArray()) + "')", string.Empty);
///仓库
List <WarehouseInfo> warehouseInfos = new WarehouseDAL().GetList("" +
"[WAREHOUSE] in ('" + string.Join("','", zonesExcelInfos.Select(d => d.WmNo).ToArray()) + "')", string.Empty);
///允许存储区代码在不同的仓库中重复使用
string allow_zoneno_repeat_at_different_warehouse = new ConfigDAL().GetValueByCode("ALLOW_ZONENO_REPEAT_AT_DIFFERENT_WAREHOUSE");
///执行的SQL语句
StringBuilder @string = new StringBuilder();
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var zonesExcelInfo in zonesExcelInfos)
{
WarehouseInfo warehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == zonesExcelInfo.WmNo);
if (warehouseInfo == null)
{
throw new Exception("MC:0x00000230");///仓库信息不存在
}
///数据校验
ValidInfo(zonesExcelInfo, allow_zoneno_repeat_at_different_warehouse);
///当前业务数据表中此工厂的该物流路线时需要新增
ZonesInfo zonesInfo = zonesInfos.FirstOrDefault(d => d.ZoneNo == zonesExcelInfo.ZoneNo && d.WmNo == zonesExcelInfo.WmNo);
if (zonesInfo == null)
{
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <ZonesInfo>(zonesExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///
zonesExcelInfo.Fid = Guid.NewGuid();
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
@string.AppendLine("if not exists (select * from [LES].[TM_WMM_ZONES] with(nolock) where [ZONE_NO] = N'" + zonesExcelInfo.ZoneNo + "' and [VALID_FLAG] = 1) " +
"insert into [LES].[TM_WMM_ZONES] " +
"([FID]," + insertFieldString + "[CREATE_USER]," + "[CREATE_DATE]," + "[VALID_FLAG]) values " +
"(N'" + zonesExcelInfo.Fid.GetValueOrDefault() + "'," + insertValueString + "N'" + loginUser + "'," + "GETDATE()," + "1);");
zonesInfos.Add(zonesExcelInfo);
continue;
}
if (string.IsNullOrEmpty(zonesExcelInfo.ZoneName))
{
throw new Exception("MC:0x00000739");///存储区名称不能为空
}
if (string.IsNullOrEmpty(zonesExcelInfo.WmNo))
{
throw new Exception("MC:0x00000528");///仓库代码不能为空
}
if (zonesExcelInfo.Settlementflag == null)
{
throw new Exception("MC:0x00000529");///结算标记不能为空
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <ZonesInfo>(zonesExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
@string.AppendLine("update [LES].[TM_WMM_ZONES] " +
"set " + valueString + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " +
"where [FID] = N'" + zonesInfo.Fid.GetValueOrDefault() + "';");
}
///执行
using (var trans = new TransactionScope())
{
if (@string.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(@string.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 <UserInfo> userExcelInfos = CommonDAL.DatatableConvertToList <UserInfo>(dataTable).ToList();
if (userExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <UserInfo> userInfos = new UserDAL().GetList("[LOGIN_NAME] in ('" + string.Join("','", userExcelInfos.Select(d => d.LoginName).ToArray()) + "')", string.Empty);
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var userExcelInfo in userExcelInfos)
{
if (userExcelInfo.LoginName.ToLower() == "admin")
{
continue;
}
///
UserInfo userInfo = userInfos.FirstOrDefault(d => d.LoginName == userExcelInfo.LoginName);
if (userInfo == null)
{
///物料号①、物料中文名称②为必填项
if (string.IsNullOrEmpty(userExcelInfo.LoginName) || string.IsNullOrEmpty(userExcelInfo.EmployeeName))
{
throw new Exception("MC:0x00000238");///用户名与用户姓名是必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql(userExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///默认密码
string defaultPassword = new ConfigBLL().GetValueByCode("USER_DEFAULT_PASSWORD");
if (string.IsNullOrEmpty(defaultPassword))
{
defaultPassword = emptyDefaultPassword;
}
sql += "if not exists (select * from dbo.TS_SYS_USER with(nolock) where [LOGIN_NAME] = N'" + userExcelInfo.LoginName + "' and [VALID_FLAG] = 1) "
+ "insert into dbo.[TS_SYS_USER] ("
+ "[FID],"
+ insertFieldString
+ "[PASSWORD],"
+ "[USER_STATUS],"
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + SignatureHelper.GetSignature(defaultPassword) + "'," ///PASSWORD
+ "" + (int)UserStatusConstants.Disable + "," ///USER_STATUS
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
continue;
}
///物料中文名称②为必填项
if (string.IsNullOrEmpty(userExcelInfo.EmployeeName))
{
throw new Exception("MC:0x00000238");///用户名与用户姓名是必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql(userExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update dbo.[TS_SYS_USER] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + userInfo.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 <SupplierInfo> supplierExcelInfos = CommonDAL.DatatableConvertToList <SupplierInfo>(dataTable).ToList();
if (supplierExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <SupplierInfo> supplierInfos = new SupplierDAL().GetListForInterfaceDataSync(supplierExcelInfos.Select(d => d.SupplierNum).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var supplierExcelInfo in supplierExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == supplierExcelInfo.SupplierNum);
if (supplierInfo == null)
{
if (string.IsNullOrEmpty(supplierExcelInfo.SupplierNum) ||
string.IsNullOrEmpty(supplierExcelInfo.SupplierName) ||
supplierExcelInfo.SupplierType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000221");///供应商代码、名称、类型为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <SupplierInfo>(supplierExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_BAS_SUPPLIER with(nolock) where [SUPPLIER_NUM] = N'" + supplierExcelInfo.SupplierNum + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_BAS_SUPPLIER] ("
+ "[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(supplierExcelInfo.SupplierName) ||
supplierExcelInfo.SupplierType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000221");///供应商代码、名称、类型为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <SupplierInfo>(supplierExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_SUPPLIER] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + supplierInfo.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 <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 <PackageApplianceInfo> packageApplianceExcelInfos = CommonDAL.DatatableConvertToList <PackageApplianceInfo>(dataTable).ToList();
if (packageApplianceExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <PackageApplianceInfo> packageApplianceInfos = new PackageApplianceDAL().GetListForInterfaceDataSync(packageApplianceExcelInfos.Select(d => d.PackageNo).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var packageApplianceExcelInfo in packageApplianceExcelInfos)
{
PackageApplianceInfo packageApplianceInfo = packageApplianceInfos.FirstOrDefault(d => d.PackageNo == packageApplianceExcelInfo.PackageNo);
if (packageApplianceInfo == null)
{
if (string.IsNullOrEmpty(packageApplianceExcelInfo.PackageNo) ||
string.IsNullOrEmpty(packageApplianceExcelInfo.PackageCname))
{
throw new Exception("MC:0x00000237");///包装器具代码，名称为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <PackageApplianceInfo>(packageApplianceExcelInfo, 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_PACKAGE_APPLIANCE] with(nolock) where [LOCATION] = N'" + packageApplianceExcelInfo.PackageNo + "' and [VALID_FLAG] = 1) "
+ "insert into [LES].[TM_BAS_PACKAGE_APPLIANCE] ("
+ "[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(packageApplianceExcelInfo.PackageNo) ||
string.IsNullOrEmpty(packageApplianceExcelInfo.PackageCname))
{
throw new Exception("MC:0x00000237");///包装器具代码，名称为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <PackageApplianceInfo>(packageApplianceExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_PACKAGE_APPLIANCE] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + packageApplianceInfo.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 <RouteInfo> routeExcelInfos = CommonDAL.DatatableConvertToList <RouteInfo>(dataTable).ToList();
if (routeExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <RouteInfo> routeInfos = new RouteDAL().GetListForInterfaceDataSync(routeExcelInfos.Select(d => d.Route).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var routeExcelInfo in routeExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
RouteInfo routeInfo = routeInfos.FirstOrDefault(d => d.Route == routeExcelInfo.Route);
if (routeInfo == null)
{
///代码、名称、类型、工厂为必填项
if (string.IsNullOrEmpty(routeExcelInfo.Route) || string.IsNullOrEmpty(routeExcelInfo.RouteName) || routeExcelInfo.RouteType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000216");///路径代码、名称、类型、对应工厂为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <RouteInfo>(routeExcelInfo, 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_ROUTE with(nolock) where [ROUTE] = N'" + routeExcelInfo.Route + "' and [VALID_FLAG] = 1) "
+ "insert into [LES].[TM_BAS_ROUTE] ("
+ "[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(routeExcelInfo.RouteName) || routeExcelInfo.RouteType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000216");///路径代码、名称、类型、对应工厂为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <RouteInfo>(routeExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_ROUTE] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + routeInfo.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 <JisPartBoxInfo> jisPartBoxes = CommonDAL.DatatableConvertToList <JisPartBoxInfo>(dataTable).ToList();
if (jisPartBoxes.Count == 0 || jisPartBoxes == null)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <JisPartBoxInfo> jisParts = new JisPartBoxDAL().GetList(" [PART_BOX_CODE] in ('" + string.Join("', '", jisPartBoxes.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", "");
List <JisPartBoxInfo> boxInfos = new JisPartBoxDAL().GetList(" [PART_BOX_NAME] in ('" + string.Join("', '", jisPartBoxes.Select(d => d.PartBoxName).ToList().ToArray()) + "')", "");
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var item in jisPartBoxes)
{
JisPartBoxInfo info = jisParts.FirstOrDefault(d => d.PartBoxCode == item.PartBoxCode);
if (info == null)
{
if (string.IsNullOrEmpty(item.PartBoxCode) ||
string.IsNullOrEmpty(item.PartBoxName) ||
string.IsNullOrEmpty(item.Plant) ||
string.IsNullOrEmpty(item.Workshop) ||
string.IsNullOrEmpty(item.AssemblyLine) ||
string.IsNullOrEmpty(item.StatusPointCode) ||
string.IsNullOrEmpty(item.TWmNo) ||
string.IsNullOrEmpty(item.TZoneNo))
{
throw new Exception("MC:0x00000452");///零件类代码、零件类名称、工厂、车间、产线、状态点、目标仓库存储区为必填项
}
JisPartBoxInfo jisPartBoxInfo = boxInfos.FirstOrDefault(d => d.PartBoxName == item.PartBoxName);
if (jisPartBoxInfo != null)
{
throw new Exception("MC:0x00000303");///零件类名称重复
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <JisPartBoxInfo>(item, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_MPM_TWD_PART_BOX with(nolock) where [PART_BOX_CODE] = N'" + item.PartBoxCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_MPM_JIS_PART_BOX] ("
+ "[FID],"
+ insertFieldString
+ "[STATUS],"
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID(),"///FID
+ insertValueString
+ (int)BasicDataStatusConstants.Created + ","
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
jisParts.Add(item);
continue;
}
if (info.Status == (int)BasicDataStatusConstants.Created)
{
if (string.IsNullOrEmpty(item.PartBoxName) ||
string.IsNullOrEmpty(item.Plant) ||
string.IsNullOrEmpty(item.Workshop) ||
string.IsNullOrEmpty(item.AssemblyLine) ||
string.IsNullOrEmpty(item.StatusPointCode) ||
string.IsNullOrEmpty(item.TWmNo) ||
string.IsNullOrEmpty(item.TZoneNo))
{
throw new Exception("MC:0x00000452");///零件类代码、零件类名称、工厂、车间、产线、状态点、目标仓库存储区为必填项
}
JisPartBoxInfo jisPartBoxInfo = boxInfos.FirstOrDefault(d => d.PartBoxName == item.PartBoxName && d.Id != info.Id);
if (jisPartBoxInfo != null)
{
throw new Exception("MC:0x00000303");///零件类名称重复
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <JisPartBoxInfo>(item, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_MPM_JIS_PART_BOX] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + info.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>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <ScanPointInfo> scanPointExcelInfos = CommonDAL.DatatableConvertToList <ScanPointInfo>(dataTable).ToList();
if (scanPointExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <ScanPointInfo> scanPointInfos = dal.GetList("[SCAN_POINT_CODE] in ('" + string.Join("','", scanPointExcelInfos.Select(d => d.ScanPointCode).ToArray()) + "') ", string.Empty);
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var scanPointExcelInfo in scanPointExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
ScanPointInfo scanPointInfo = scanPointInfos.FirstOrDefault(d => d.ScanPointCode == scanPointExcelInfo.ScanPointCode);
if (scanPointInfo == null)
{
if (string.IsNullOrEmpty(scanPointExcelInfo.ScanPointCode) ||
string.IsNullOrEmpty(scanPointExcelInfo.ScanPointName) ||
string.IsNullOrEmpty(scanPointExcelInfo.Plant) ||
string.IsNullOrEmpty(scanPointExcelInfo.Workshop) ||
string.IsNullOrEmpty(scanPointExcelInfo.AssemblyLine))
{
throw new Exception("MC:0x00000282");///扫描点代码、扫描点名称、工厂、车间、生产线不能为空
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <ScanPointInfo>(scanPointExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_BAS_SCAN_POINT with(nolock) where [SCAN_POINT_CODE] = N'" + scanPointExcelInfo.ScanPointCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_BAS_SCAN_POINT] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID()," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
///为防止EXCEL中数据有重复
scanPointInfo = new ScanPointInfo();
scanPointInfo.ScanPointCode = scanPointExcelInfo.ScanPointCode;
scanPointInfos.Add(scanPointInfo);
///
continue;
}
if (string.IsNullOrEmpty(scanPointExcelInfo.ScanPointCode) ||
string.IsNullOrEmpty(scanPointExcelInfo.ScanPointName) ||
string.IsNullOrEmpty(scanPointExcelInfo.Plant) ||
string.IsNullOrEmpty(scanPointExcelInfo.Workshop) ||
string.IsNullOrEmpty(scanPointExcelInfo.AssemblyLine))
{
throw new Exception("MC:0x00000282");///扫描点代码、扫描点名称、工厂、车间、生产线不能为空
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <ScanPointInfo>(scanPointExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_SCAN_POINT] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + scanPointInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
throw new Exception("MC:0x00000283");///:没有可导入更新的数据
}
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>
/// 执行导入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));
}
/// <summary>
/// 执行导入EXCEL数据
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser, string whereText)
{
List <LackOfMaterialDetailInfo> lackOfMaterialDetailExcelInfos = CommonDAL.DatatableConvertToList <LackOfMaterialDetailInfo>(dataTable).ToList();
if (lackOfMaterialDetailExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <LackOfMaterialDetailInfo> lackOfMaterialDetailInfos = dal.GetList(whereText, string.Empty);
if (lackOfMaterialDetailInfos.Count == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
List <LackOfMaterialInfo> lackOfMaterialInfos = new LackOfMaterialDAL().GetList("" +
"[FID] in ('" + string.Join("','", lackOfMaterialDetailInfos.Select(d => d.LackOrderFid.GetValueOrDefault()).ToArray()) + "') and " +
"[STATUS] = " + (int)LackOfMaterialStatusConstants.Completed + "", string.Empty);
if (lackOfMaterialInfos.Count == 0)
{
throw new Exception("MC:0x00000084");///TODO:缺件表计算完成时才能进行反馈
}
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var lackOfMaterialDetailExcelInfo in lackOfMaterialDetailExcelInfos)
{
///当前业务数据表中此工厂的该物流路线时需要新增
LackOfMaterialDetailInfo LackOfMaterialDetailInfo = lackOfMaterialDetailInfos.FirstOrDefault(d =>
d.Plant == lackOfMaterialDetailExcelInfo.Plant &&
d.PartPurchaser == lackOfMaterialDetailExcelInfo.PartPurchaser &&
d.SupplierNum == lackOfMaterialDetailExcelInfo.SupplierNum &&
d.PartNo == lackOfMaterialDetailExcelInfo.PartNo);
if (LackOfMaterialDetailInfo == null)
{
throw new Exception("MC:0x00000255");///请勿新增数据
}
///未填写数据的不标记已反馈，反馈数相同时不再标记已反馈
if (lackOfMaterialDetailExcelInfo.FeedbackLackQty.GetValueOrDefault() == 0)
{
continue;
}
if (lackOfMaterialDetailExcelInfo.FeedbackLackQty.GetValueOrDefault() == LackOfMaterialDetailInfo.FeedbackLackQty.GetValueOrDefault())
{
continue;
}
if (string.IsNullOrEmpty(lackOfMaterialDetailExcelInfo.PartNo) ||
string.IsNullOrEmpty(lackOfMaterialDetailExcelInfo.SupplierNum) ||
string.IsNullOrEmpty(lackOfMaterialDetailExcelInfo.Plant))
{
throw new Exception("MC:0x00000254");///供应商代码、工厂、物料号为必填项
}
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <LackOfMaterialDetailInfo>(lackOfMaterialDetailExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TT_ATP_LACK_OF_MATERIAL_DETAIL] set "
+ valueString + "" +
"[FEEDBACK_FLAG] = 1," +
"[FEEDBACK_TIME] = GETDATE()," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = " + LackOfMaterialDetailInfo.Id + ";";
}
///
if (string.IsNullOrEmpty(sql))
{
throw new Exception("MC:0x00000084");///TODO:没有可导入更新的数据
}
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 <TwdCounterInfo> twdCounterExcelInfos = CommonDAL.DatatableConvertToList <TwdCounterInfo>(dataTable).ToList();
if (twdCounterExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
List <TwdCounterInfo> twdCounterInfos = dal.GetList("" +
"[PART_BOX_CODE] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " +
"[PART_NO] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartNo).ToArray()) + "') and " +
"[PACKAGE_MODEL] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PackageModel).ToArray()) + "')", string.Empty);
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetList("" +
"[INHOUSE_SYSTEM_MODE] = N'" + (int)PullModeConstants.Twd + "' and " +
"[INHOUSE_PART_CLASS] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "') and " +
"[PART_NO] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
///获取零件类信息
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("" +
"[PART_BOX_CODE] in ('" + string.Join("','", twdCounterExcelInfos.Select(d => d.PartBoxCode).ToArray()) + "')", string.Empty);
StringBuilder @string = new StringBuilder();
foreach (TwdCounterInfo twdCounterExcelInfo in twdCounterExcelInfos)
{
/// 零件类代码②、工厂③车间⑤生产线⑥工段⑬工位⑭、物料号⑩、物料版本⑫、包装容器⑰为联合主键，差异数量DiffQty作为累加当前计数⑮的依据，备注直接更新
TwdCounterInfo twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine &&
d.WorkshopSection == twdCounterExcelInfo.WorkshopSection &&
d.Location == twdCounterExcelInfo.Location &&
d.PartVersion == twdCounterExcelInfo.PartVersion);
#region 逐步减低维度处理
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine &&
d.WorkshopSection == twdCounterExcelInfo.WorkshopSection &&
d.Location == twdCounterExcelInfo.Location);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine &&
d.WorkshopSection == twdCounterExcelInfo.WorkshopSection);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop &&
d.AssemblyLine == twdCounterExcelInfo.AssemblyLine);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant &&
d.Workshop == twdCounterExcelInfo.Workshop);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel &&
d.Plant == twdCounterExcelInfo.Plant);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo &&
d.PackageModel == twdCounterExcelInfo.PackageModel);
}
if (twdCounterInfo == null)
{
twdCounterInfo = twdCounterInfos.FirstOrDefault(d =>
d.PartBoxCode == twdCounterExcelInfo.PartBoxCode &&
d.PartNo == twdCounterExcelInfo.PartNo);
}
if (twdCounterInfo == null)
{
throw new Exception("MC:0x00000255");///数据格式不符合导入规范
}
#endregion
if (twdCounterInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled)
{
throw new Exception("MC:0x00000455");///计数器已作废不能修改数量
}
@string.AppendLine("update [LES].[TT_MPM_TWD_COUNTER] set " +
"[CURRENT_QTY] = isnull([CURRENT_QTY],0) + " + twdCounterExcelInfo.DiffQty + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = " + twdCounterInfo.Id + ";");
///根据计数器的物料拉动信息外键获取物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d => d.Fid == twdCounterInfo.PartPullFid.GetValueOrDefault());
if (maintainInhouseLogisticStandardInfo == null)
{
throw new Exception("MC:0x00000213");///物料拉动信息数据错误
}
if (maintainInhouseLogisticStandardInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
throw new Exception("MC:0x00000233");///没有已启用的物料拉动信息
}
///获取零件类信息
TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == maintainInhouseLogisticStandardInfo.InhousePartClass);
///未能成功获取零件类信息
if (twdPartBoxInfo == null)
{
throw new Exception("MC:0x00000225");///拉动零件类数据错误
}
///零件类未启用
if (twdPartBoxInfo.Status.GetValueOrDefault() != (int)BasicDataStatusConstants.Enable)
{
throw new Exception("MC:0x00000456");///零件类未启用
}
///创建计数器日志
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以物料拉动信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandardInfo, ref twdCounterLogInfo);
///以零件类信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo);
///PART_QTY
twdCounterLogInfo.PartQty = twdCounterExcelInfo.DiffQty;
///SOURCE_DATA_FID
twdCounterLogInfo.SourceDataFid = twdCounterInfo.Fid;
///SOURCE_DATA_TYPE
twdCounterLogInfo.SourceDataType = (int)TwdCounterSourceDataTypeConstants.Manual;
///SOURCE_DATA
twdCounterLogInfo.SourceData = twdCounterInfo.PartBoxCode;
///Comments
twdCounterLogInfo.Comments = twdCounterExcelInfo.Comments;
///
@string.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
///触发层级拉动
@string.AppendLine(LevelPullRequirementCounter(
maintainInhouseLogisticStandardInfo,
twdCounterExcelInfo.DiffQty,
loginUser,
twdCounterInfo.Fid.GetValueOrDefault(),
twdCounterInfo.PartBoxCode));
}
///执行
using (var trans = new TransactionScope())
{
if (@string.Length == 0)
{
throw new Exception("MC:0x00000283");///没有可导入更新的数据
}
CommonDAL.ExecuteNonQueryBySql(@string.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 <WarehouseInfo> warehouseExcelInfos = CommonDAL.DatatableConvertToList <WarehouseInfo>(dataTable).ToList();
if (warehouseExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <WarehouseInfo> warehouseInfos = new WarehouseDAL().GetListForInterfaceDataSync(warehouseExcelInfos.Select(d => d.Warehouse).ToList());
///执行的SQL语句
string sql = string.Empty;
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var warehouseExcelInfo in warehouseExcelInfos)
{
WarehouseInfo warehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == warehouseExcelInfo.Warehouse);
if (warehouseInfo == null)
{
if (string.IsNullOrEmpty(warehouseExcelInfo.Warehouse) ||
string.IsNullOrEmpty(warehouseExcelInfo.WarehouseName) ||
warehouseExcelInfo.WarehouseType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000231");///仓库代码、名称、类型为必填项
}
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <WarehouseInfo>(warehouseExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///
warehouseExcelInfo.Fid = Guid.NewGuid();
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
sql += "if not exists (select * from LES.TM_BAS_WAREHOUSE with(nolock) "
+ "where [WAREHOUSE] = N'" + warehouseExcelInfo.Warehouse + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_BAS_WAREHOUSE] ("
+ "[FID],"
+ insertFieldString
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "N'" + warehouseExcelInfo.Fid.GetValueOrDefault() + "'," ///FID
+ insertValueString
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");";
warehouseInfos.Add(warehouseExcelInfo);
continue;
}
if (string.IsNullOrEmpty(warehouseExcelInfo.Warehouse) ||
string.IsNullOrEmpty(warehouseExcelInfo.WarehouseName) ||
warehouseExcelInfo.WarehouseType.GetValueOrDefault() == 0)
{
throw new Exception("MC:0x00000231");///仓库代码、名称、类型为必填项
}
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <WarehouseInfo>(warehouseExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
sql += "update [LES].[TM_BAS_WAREHOUSE] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [FID] = N'" + warehouseExcelInfo.Fid.GetValueOrDefault() + "';";
}
///
if (string.IsNullOrEmpty(sql))
{
return(false);
}
using (var trans = new TransactionScope())
{
foreach (var warehouseExcelInfo in warehouseExcelInfos)
{
warehouseExcelInfo.CreateDate = DateTime.Now;
new SupplierBLL().SyncSupplierByWarehouse(warehouseExcelInfo, loginUser);
}
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 <TwdPartBoxInfo> twdPartBoxExcelInfos = CommonDAL.DatatableConvertToList <TwdPartBoxInfo>(dataTable).ToList();
if (twdPartBoxExcelInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
///获取业务表中要变更的数据集合,准备对比
List <TwdPartBoxInfo> twdPartBoxInfos = new TwdPartBoxDAL().GetList("[PART_BOX_CODE] in ('" + string.Join("', '", twdPartBoxExcelInfos.Select(d => d.PartBoxCode).ToList().ToArray()) + "')", "");
///执行的SQL语句
StringBuilder @string = new StringBuilder();
List <string> fields = new List <string>(fieldNames.Keys);
///逐条处理中间表数据
foreach (var twdPartBoxExcelInfo in twdPartBoxExcelInfos)
{
TwdPartBoxInfo twdPartBoxInfo = twdPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == twdPartBoxExcelInfo.PartBoxCode);
///需要新增
if (twdPartBoxInfo == null)
{
///校验
ValidTwdPartBoxInfo(twdPartBoxExcelInfo);
///字段
string insertFieldString = string.Empty;
///值
string insertValueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
insertFieldString += "[" + fieldNames[fields[i]] + "],";
insertValueString += valueStr + ",";
}
///判断业务主键是否重复，以防止EXCEL中有重复数据，适用于基础数据导入
@string.AppendLine("if not exists (select * from LES.TM_MPM_TWD_PART_BOX with(nolock) " +
"where [PART_BOX_CODE] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and [VALID_FLAG] = 1)"
+ " insert into [LES].[TM_MPM_TWD_PART_BOX] ("
+ "[FID],"
+ insertFieldString
+ "[STATUS],"
+ "[CREATE_USER],"
+ "[CREATE_DATE],"
+ "[VALID_FLAG]"
+ ") values ("
+ "NEWID(),"///FID
+ insertValueString
+ (int)BasicDataStatusConstants.Created + ","
+ "N'" + loginUser + "'," ///CREATE_USER
+ "GETDATE()," ///CREATE_DATE
+ "1" ///VALID_FLAG
+ ");");
///防止EXCEL中有重复项
twdPartBoxInfos.Add(twdPartBoxExcelInfo);
continue;
}
///
if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Disabled)
{
continue;
}
///
twdPartBoxExcelInfo.Id = twdPartBoxInfo.Id;
///校验
ValidTwdPartBoxInfo(twdPartBoxExcelInfo);
///是否更新了拉动模式,需要同步更新物料拉动信息中的拉动模式
if (twdPartBoxInfo.TwdPullMode.GetValueOrDefault() != twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault())
{
@string.AppendLine("update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] " +
"set [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxExcelInfo.TwdPullMode.GetValueOrDefault() + "'," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [INHOUSE_SYSTEM_MODE] = N'" + twdPartBoxInfo.TwdPullMode.GetValueOrDefault() + "' and " +
"[INHOUSE_PART_CLASS] = N'" + twdPartBoxExcelInfo.PartBoxCode + "' and " +
"[VALID_FLAG] = 1;");
}
///已创建状态的数据可以全信息更新
if (twdPartBoxInfo.Status.GetValueOrDefault() == (int)BasicDataStatusConstants.Created)
{
///值
string valueString = string.Empty;
for (int i = 0; i < fields.Count; i++)
{
string valueStr = CommonDAL.GetFieldValueForSql <TwdPartBoxInfo>(twdPartBoxExcelInfo, fields[i]);
if (string.IsNullOrEmpty(valueStr))
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
}
@string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] set "
+ valueString
+ "[MODIFY_USER] = N'" + loginUser + "',"
+ "[MODIFY_DATE] = GETDATE() "
+ "where [ID] = " + twdPartBoxInfo.Id + ";");
continue;
}
///
@string.AppendLine("update [LES].[TM_MPM_TWD_PART_BOX] " +
"set [PART_BOX_NAME] = N'" + twdPartBoxExcelInfo.PartBoxName + "'," +
"[REQUIREMENT_ACCUMULATE_TIME] = " + (twdPartBoxExcelInfo.RequirementAccumulateTime == null ? "NULL" : "" + twdPartBoxExcelInfo.RequirementAccumulateTime.GetValueOrDefault() + "") + "," +
"[LOAD_TIME] = " + (twdPartBoxExcelInfo.LoadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.LoadTime.GetValueOrDefault() + "") + "," +
"[TRANSPORT_TIME] = " + (twdPartBoxExcelInfo.TransportTime == null ? "NULL" : "" + twdPartBoxExcelInfo.TransportTime.GetValueOrDefault() + "") + "," +
"[UNLOAD_TIME] = " + (twdPartBoxExcelInfo.UnloadTime == null ? "NULL" : "" + twdPartBoxExcelInfo.UnloadTime.GetValueOrDefault() + "") + "," +
"[DELAY_TIME] = " + (twdPartBoxExcelInfo.DelayTime == null ? "NULL" : "" + twdPartBoxExcelInfo.DelayTime.GetValueOrDefault() + "") + "," +
"[ONLINE_TIME] = " + (twdPartBoxExcelInfo.OnlineTime == null ? "NULL" : "" + twdPartBoxExcelInfo.OnlineTime.GetValueOrDefault() + "") + "," +
"[REQUIREMENT_ACCUMULATE_MODE] = " + twdPartBoxExcelInfo.RequirementAccumulateMode.GetValueOrDefault() + "," +
"[STATUS_POINT_CODE] = N'" + twdPartBoxExcelInfo.StatusPointCode + "'," +
"[ROUNDNESS_MODE] = " + twdPartBoxExcelInfo.RoundnessMode.GetValueOrDefault() + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] = " + twdPartBoxInfo.Id + ";");
}
///执行
using (TransactionScope trans = new TransactionScope())
{
if (@string.Length > 0)
{
CommonDAL.ExecuteNonQueryBySql(@string.ToString());
}
trans.Complete();
}
return(true);
}
