/// <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 <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 <VmiSupplierInfo> vmiSupplierExcelInfos = CommonDAL.DatatableConvertToList <VmiSupplierInfo>(dataTable).ToList(); if (vmiSupplierExcelInfos.Count == 0) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } ///获取业务表中要变更的数据集合,准备对比 List <VmiSupplierInfo> vmiSupplierInfos = new VmiSupplierDAL().GetListForInterfaceDataSync(vmiSupplierExcelInfos.Select(d => d.SupplierNum).ToList()); List <SupplierInfo> supplierInfos = new SupplierDAL().GetListForInterfaceDataSync(vmiSupplierExcelInfos.Select(d => d.SupplierNum).ToList()); List <WarehouseInfo> warehouseInfos = new WarehouseDAL().GetListForInterfaceDataSync(vmiSupplierExcelInfos.Select(d => d.WmNo).ToList()); ///执行的SQL语句 string sql = string.Empty; List <string> fields = new List <string>(fieldNames.Keys); ///逐条处理中间表数据 foreach (var vmiSupplierExcelInfo in vmiSupplierExcelInfos) { ///当前业务数据表中此工厂的该物流路线时需要新增 VmiSupplierInfo vmiSupplierInfo = vmiSupplierInfos.FirstOrDefault(d => d.SupplierNum == vmiSupplierExcelInfo.SupplierNum && d.WmNo == vmiSupplierExcelInfo.WmNo); SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == vmiSupplierExcelInfo.SupplierNum); if (supplierInfo == null) { throw new Exception("MC:0x00000229");///供应商信息不存在 } WarehouseInfo warehouseInfo = warehouseInfos.FirstOrDefault(d => d.Warehouse == vmiSupplierExcelInfo.WmNo); if (warehouseInfo == null) { throw new Exception("MC:0x00000230");///仓库信息不存在 } vmiSupplierExcelInfo.SupplierName = supplierInfo.SupplierName; if (vmiSupplierInfo == null) { if (string.IsNullOrEmpty(vmiSupplierExcelInfo.SupplierNum) || string.IsNullOrEmpty(vmiSupplierExcelInfo.WmNo)) { throw new Exception("MC:0x00000228");///供应商与仓库为必填项 } ///字段 string insertFieldString = string.Empty; ///值 string insertValueString = string.Empty; for (int i = 0; i < fields.Count; i++) { string valueStr = CommonDAL.GetFieldValueForSql <VmiSupplierInfo>(vmiSupplierExcelInfo, 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_VMI_SUPPLIER with(nolock) " + "where [SUPPLIER_NUM] = N'" + vmiSupplierExcelInfo.SupplierNum + "' and [WM_NO] = N'" + vmiSupplierExcelInfo.WmNo + "' and [VALID_FLAG] = 1)" + " insert into [LES].[TM_BAS_VMI_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(vmiSupplierExcelInfo.SupplierNum) || string.IsNullOrEmpty(vmiSupplierExcelInfo.WmNo)) { throw new Exception("MC:0x00000228");///供应商与仓库为必填项 } ///值 string valueString = string.Empty; for (int i = 0; i < fields.Count; i++) { string valueStr = CommonDAL.GetFieldValueForSql <VmiSupplierInfo>(vmiSupplierExcelInfo, fields[i]); if (string.IsNullOrEmpty(valueStr)) { throw new Exception("MC:1x00000043");///数据格式不符合导入规范 } valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ","; } sql += "update [LES].[TM_BAS_VMI_SUPPLIER] set " + valueString + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + vmiSupplierInfo.Id + ";"; } /// if (string.IsNullOrEmpty(sql)) { return(false); } return(CommonDAL.ExecuteNonQueryBySql(sql)); }