using SqlSugar; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; using WMS.Util; using WMS.Info; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System.Drawing; using System.Text.RegularExpressions; namespace WMS.Core.ImportExecl { public class ImportDataOrExecl { /// /// 生产转发料单 /// /// /// /// /// public JsonExecl ImportProdOrder(DataTable execlDt, int busType, string loginName) { JsonExecl json = new JsonExecl(); json.Type = 0; string sql = ""; SqlSugarClient sugar = null; // 保存需要操作的表名 List LstTable = new List(); // 保存数据库表字段信息 List lstHeat = new List(); // 保存导出的数据 DataTable ImportDT = new DataTable(); try { sugar = SysDbCore.GetDbCtx(); sugar.BeginTran(); if (busType.ToString() != "1") throw new Exception("单据类型不正确"); if (execlDt == null) throw new Exception("数据源不能为null"); if (execlDt.Rows.Count <= 0) throw new Exception("数据源数据行数必须大于0"); #region 获取数据库参数配置 // 保存关键字段 string RELATIONSHIPCEL = string.Empty; DataTable dtTables = sugar.Ado.GetDataTable("SELECT F_NO,BUSINESSTYPE,TABLENAME,STRUCTURE,RELATIONSHIPCEL,BUSINSERTVAL FROM BASE_IMPORTRELATIONSHIP WHERE BUSINSERTVAL='" + busType + "'"); if (dtTables.Rows.Count <= 0) throw new Exception("未找到参数设置"); foreach (DataRow rows in dtTables.Rows) { LstTable.Add(rows["TABLENAME"].ToString() + '|' + rows["STRUCTURE"].ToString() + "|" + rows["BUSINSERTVAL"].ToString()); if (rows["RELATIONSHIPCEL"].ToString() != "" && rows["STRUCTURE"].ToString() == "表头") { RELATIONSHIPCEL = rows["RELATIONSHIPCEL"].ToString(); } } string[] StrRELATIONSHIPCEL = RELATIONSHIPCEL.Split(','); if (LstTable.Count < 1) throw new Exception("数据异常"); // 保存删除的脚本 List delectsql = new List(); // 保存数据库中没有的料号 List lstmatno = new List(); string BusType = string.Empty; // 业务类型 string TableType = string.Empty; // 表类型 string TablesName = string.Empty; // 表名 string CellsName = string.Empty; // 列名 string CellsMemo = string.Empty; // 备注 string CellsType = string.Empty; // 字段类型 string CellsNULLABLE = string.Empty; // 是否允许未空 #region 获取数据库表字段信息 // 多表导入 foreach (string tabName in LstTable) { string[] tabs = tabName.Split('|'); TablesName = tabs[0]; TableType = tabs[1]; BusType = tabs[2]; // 保存表的备注 sql = "select * from user_col_comments t5 where t5.TABLE_NAME=upper('" + TablesName + "')"; DataTable CellTitle = sugar.Ado.GetDataTable(sql); // 保存表的列名 sql = "select * from user_tab_columns t3 where t3.TABLE_NAME=upper('" + TablesName + "')"; DataTable dtcolums = sugar.Ado.GetDataTable(sql); foreach (DataRow rows in dtcolums.Rows) { CellsName = rows["COLUMN_NAME"].ToString(); CellsMemo = CellTitle.Select("COLUMN_NAME='" + CellsName + "'")[0]["COMMENTS"].ToString(); CellsType = rows["DATA_TYPE"].ToString(); CellsNULLABLE = rows["NULLABLE"].ToString(); DBTableCell dbt = new DBTableCell(); dbt.BusType = BusType; dbt.TablesName = TablesName; dbt.TableType = TableType; dbt.CellsName = CellsName; dbt.CellsMemo = CellsMemo; dbt.CellsType = CellsType; dbt.CellsNULLABLE = CellsNULLABLE; lstHeat.Add(dbt); } } #endregion #region EXECL 数据获取 // 保存 EXECL 文件中的非法列名 List execlCelName = new List(); foreach (DataColumn columns in execlDt.Columns) { var t = lstHeat.Where(v => v.CellsMemo == columns.ColumnName).FirstOrDefault(); if (t == null) execlCelName.Add(columns.ColumnName); } if (execlCelName.Count > 0) throw new Exception((string.Join(",", execlCelName)) + "列不是合法的列名"); #endregion #endregion if (LstTable.Count == 1) { #region 单表导入 string tablename = LstTable[0].Split('|')[0]; string inserT = string.Empty; string inserV = string.Empty; var t = lstHeat.Where(v => v.TableType == "表头" && v.BusType == busType.ToString() && v.TablesName == tablename).ToList(); // 单表导入 foreach (DataRow rows in execlDt.Rows) { inserT = string.Empty; inserV = string.Empty; foreach (var tlst in t) { if ((tablename == "BASE_MATITEM" && tlst.CellsName == "F_NO")) { DataTable dtmat = sugar.Ado.GetDataTable("SELECT * FROM BASE_MATITEM WHERE F_NO='" + rows[tlst.CellsMemo].ToString() + "'"); if (dtmat.Rows.Count > 0) { lstmatno.Add(rows[tlst.CellsMemo].ToString()); inserT = string.Empty; inserV = string.Empty; break; } } if (execlDt.Columns.Contains(tlst.CellsMemo)) { inserT += tlst.CellsName + ","; inserV += GetDbCCells(tlst, rows[tlst.CellsMemo].ToString()) + ","; } else { string v1 = string.Empty; string v2 = string.Empty; GetDbCCell(tlst, out v1, out v2); if (!string.IsNullOrEmpty(v2)) { inserT += v1 + ","; inserV += v2 + ","; } } } inserT = inserT.Trim(','); inserV = inserV.Trim(','); sql += "INSERT INTO " + tablename + "(" + inserT + ") VALUES(" + inserV + ");"; } #endregion } else { if (StrRELATIONSHIPCEL.Count() <= 0) throw new Exception("多表导入表头未设置关键字段"); // 保存关键列和值 Dictionary lstStr = new Dictionary(); #region 获取 EXECL 数据 #region 保存关键列和值 // 先将关键字段赋值为空 foreach (var str in StrRELATIONSHIPCEL) { lstStr.Add(str, ""); } foreach (DataRow rows in execlDt.Rows) { string key = string.Empty; string val = string.Empty; // 遍历关键列名 foreach (var str in StrRELATIONSHIPCEL) { key = str; val = rows[str].ToString(); if (lstStr[key] != val && lstStr[key] != "") throw new Exception(key + ":关键字段不允许有两条不同的值"); else lstStr[key] = val; } } #endregion // 将数据列按照表类型分组，分成表头和表体 var strs = lstHeat.GroupBy(v => new { v.TableType, v.TablesName }).Select(v => new { heat = v.Key.TableType, tableName = v.Key.TablesName }).ToArray(); sql = ""; string ot = "KLORDER"; string torder = DateTime.Now.ToString("yyMMddhhmmss"); string exorder = string.Empty; bool isCF = false; foreach (var heats in strs) { if (heats.heat == "表头") { #region 表头 string inserT = string.Empty; string inserV = string.Empty; { #region 生产发料单 var t = lstHeat.Where(v => v.TableType == heats.heat && v.BusType == busType.ToString() && v.TablesName == heats.tableName).ToList(); foreach (var tlst in t) { if (tlst.CellsName == "F_TYPENUM") { inserT += tlst.CellsName + ","; inserV += "'3',"; continue; } if (tlst.CellsName == "F_NO") { exorder = GetDbCCells(tlst, lstStr[tlst.CellsMemo]); DataTable dt = sugar.Ado.GetDataTable("select * from BILL_ORDERGRP where " + tlst.CellsName + "='" + lstStr[tlst.CellsMemo] + "'"); if (dt.Rows.Count > 0) { if (dt.Rows[0]["F_STATENUM"].ToString() != "0") { throw new Exception("单据已存在，并且已经开始作业，无法重复导入"); } else { delectsql.Add("DELETE BILL_ORDERGRP WHERE " + tlst.CellsName + "='" + lstStr[tlst.CellsMemo] + "';"); delectsql.Add("DELETE BILL_ORDERITEM WHERE F_ORDERNO='" + lstStr[tlst.CellsMemo] + "';"); } } } if (lstStr.ContainsKey(tlst.CellsMemo)) { inserT += tlst.CellsName + ","; inserV += GetDbCCells(tlst, lstStr[tlst.CellsMemo]) + ","; } else { string v1 = string.Empty; string v2 = string.Empty; GetDbCCell(tlst, out v1, out v2); if (!string.IsNullOrEmpty(v2)) { inserT += v1 + ","; inserV += v2 + ","; } } } #endregion } inserT = inserT.Trim(','); inserV = inserV.Trim(','); sql += "INSERT INTO " + heats.tableName + "(" + inserT + ") VALUES(" + inserV + ");"; inserT = string.Empty; inserV = string.Empty; if (busType.ToString() == "1") { { #region 生产转收货单 var t = lstHeat.Where(v => v.TableType == heats.heat && v.BusType == busType.ToString() && v.TablesName == heats.tableName).ToList(); foreach (var tlst in t) { if (tlst.CellsName == "F_TYPENUM") { inserT += tlst.CellsName + ","; inserV += "'1',"; continue; } if (tlst.CellsName == "F_NO") { inserT += tlst.CellsName + ","; inserV += "'" + ot + lstStr[tlst.CellsMemo] + "',"; DataTable dt = sugar.Ado.GetDataTable("select * from BILL_ORDERGRP where " + tlst.CellsName + "='" + ot + lstStr[tlst.CellsMemo] + "'"); if (dt.Rows.Count > 0) { if (dt.Rows[0]["F_STATENUM"].ToString() != "0") { throw new Exception("单据已存在，并且已经开始作业，无法重复导入"); } else { delectsql.Add("DELETE BILL_ORDERGRP WHERE " + tlst.CellsName + "='" + ot + lstStr[tlst.CellsMemo] + "';"); delectsql.Add("DELETE BILL_ORDERITEM WHERE F_ORDERNO='" + ot + lstStr[tlst.CellsMemo] + "';"); } } continue; } if (lstStr.ContainsKey(tlst.CellsMemo)) { inserT += tlst.CellsName + ","; inserV += GetDbCCells(tlst, lstStr[tlst.CellsMemo]) + ","; } else { string v1 = string.Empty; string v2 = string.Empty; GetDbCCell(tlst, out v1, out v2); if (!string.IsNullOrEmpty(v2)) { inserT += v1 + ","; inserV += v2 + ","; } } } #endregion } inserT = inserT.Trim(','); inserV = inserV.Trim(','); sql += "INSERT INTO " + heats.tableName + "(" + inserT + ") VALUES(" + inserV + ");"; } #endregion } else { #region 表体 foreach (DataRow rows in execlDt.Rows) { var t = lstHeat.Where(v => v.TableType == heats.heat && v.BusType == busType.ToString() && v.TablesName == heats.tableName).ToList(); string rowno = Guid.NewGuid().ToString(); string inserT = string.Empty; string inserV = string.Empty; #region 生产发料单 foreach (var tlst in t) { if (tlst.CellsName == "F_ROWNO") { inserT += tlst.CellsName + ","; inserV += "'" + rowno + "',"; continue; } if (tlst.CellsName == "F_MATNO" && tlst.CellsMemo == "物料编号") { DataTable dtmat = sugar.Ado.GetDataTable("SELECT * FROM BASE_MATITEM WHERE F_NO='" + rows[tlst.CellsMemo].ToString() + "'"); if (dtmat.Rows.Count <= 0) { lstmatno.Add(rows[tlst.CellsMemo].ToString()); } } if (execlDt.Columns.Contains(tlst.CellsMemo)) { inserT += tlst.CellsName + ","; if (lstStr.ContainsKey(tlst.CellsMemo)) { inserV += GetDbCCells(tlst, lstStr[tlst.CellsMemo]) + ","; ; } else { inserV += GetDbCCells(tlst, rows[tlst.CellsMemo].ToString()) + ","; ; } } else { string v1 = string.Empty; string v2 = string.Empty; GetDbCCell(tlst, out v1, out v2); if (!string.IsNullOrEmpty(v2)) { inserT += v1 + ","; inserV += v2 + ","; } } } #endregion inserT = inserT.Trim(','); inserV = inserV.Trim(','); sql += "INSERT INTO " + heats.tableName + "(" + inserT + ") VALUES(" + inserV + ");"; inserT = string.Empty; inserV = string.Empty; if (busType.ToString() == "1") { #region 生产转收货单 foreach (var tlst in t) { if (tlst.CellsName == "F_ORDERNO") { inserT += tlst.CellsName + ","; if (isCF) { inserV += "'" + ot + torder + "',"; } else { inserV += "'" + ot + lstStr[tlst.CellsMemo] + "',"; } continue; } if (tlst.CellsName == "F_FROMORDERNO") { inserT += tlst.CellsName + ","; inserV += exorder + ","; continue; } if (tlst.CellsName == "F_FROMORDERITEMNO") { inserT += tlst.CellsName + ","; inserV += "'" + rowno + "',"; continue; } if (execlDt.Columns.Contains(tlst.CellsMemo)) { inserT += tlst.CellsName + ","; if (lstStr.ContainsKey(tlst.CellsMemo)) { inserV += GetDbCCells(tlst, lstStr[tlst.CellsMemo]) + ","; ; } else { inserV += GetDbCCells(tlst, rows[tlst.CellsMemo].ToString()) + ","; } } else { string v1 = string.Empty; string v2 = string.Empty; GetDbCCell(tlst, out v1, out v2); if (!string.IsNullOrEmpty(v2)) { inserT += v1 + ","; inserV += v2 + ","; } } } inserT = inserT.Trim(','); inserV = inserV.Trim(','); sql += "INSERT INTO " + heats.tableName + "(" + inserT + ") VALUES(" + inserV + ");"; inserT = string.Empty; inserV = string.Empty; #endregion } } #endregion } } #endregion } if (lstmatno.Count > 0 && LstTable.Count > 1) { string mats = string.Join(",", lstmatno); throw new Exception(mats + "：未找到数据"); } else if (lstmatno.Count > 0 && LstTable.Count == 1) { string mats = string.Join(",", lstmatno); } if (!string.IsNullOrEmpty(sql)) { foreach (string str in delectsql) { sql = str + sql; } string[] strs = sql.Split(';'); int res = 0; foreach (string str in strs) { if (!string.IsNullOrEmpty(str)) { res = sugar.Ado.ExecuteCommand(str); if (res <= 0) throw new Exception("添加失败:" + str); } } sugar.CommitTran(); json.Type = 2; json.Message = "操作成功"; } } catch (Exception ex) { sugar.RollbackTran(); json.Type = 0; json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } public string RegexNot(string restring) { return restring.Trim().Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", ""); //替换所有不可见字符为空格 } /// /// 基础资料导入方法 /// /// 文件路径/param> /// 业务类型 /// 登陆账号 public JsonExecl ImportData(DataTable dtExeclSouse, int busType, string loginName) { JsonExecl json = new JsonExecl(); json.Type = 0; int OKCount = 0; string sql = ""; SqlSugarClient sugar = SysDbCore.GetDbCtx(); try { if (dtExeclSouse == null) throw new Exception("数据源不能为null"); if (dtExeclSouse.Rows.Count <= 0) throw new Exception("数据源数据行数必须大于0"); //int heatIndex = 0; //if (string.IsNullOrEmpty(filePath)) // throw new Exception("文件路径不能为空"); //if (!File.Exists(filePath)) // throw new Exception("文件不存在"); //if (string.IsNullOrEmpty(busType)) // throw new Exception("类型不能为空"); #region 获取数据库参数配置 // 获取参数配置 var importLst = sugar.Queryable().Where(v => v.BUSINSERTVAL == busType).First(); if (importLst == null) throw new Exception("未找到主表参数"); var imBaseDtLst = sugar.Queryable().Where(v => v.F_NO == importLst.F_NO).ToList(); if (imBaseDtLst.Count <= 0) throw new Exception("未找到字段参数的配置"); var GetF_NO = imBaseDtLst.Where(v => v.DBCELLNAME == "F_NO").FirstOrDefault(); if (GetF_NO == null && busType >= 1000 && busType < 2000) throw new Exception("参数配置缺少主键 [ F_NO || F_ROWNO ] 参数"); #endregion // 获取数据源 DataTable execlDt = dtExeclSouse;// ExcelHelper.ExcelImport(filePath, importLst.SHEETNAME, heatIndex); List lstCells = new List(); #region 获取主数据的参数属性 sql = string.Format(@"select mome.TABLE_NAME as TableName,mome.COLUMN_NAME as CelName,mome.COMMENTS as CelMemo,cel.DATA_TYPE as CelType,CEL.NULLABLE as CelIsNull,cel.DATA_DEFAULT as CelDefualVal from user_col_comments mome left join user_tab_columns cel on mome.TABLE_NAME = cel.TABLE_NAME and mome.COLUMN_NAME = cel.COLUMN_NAME where mome.TABLE_NAME = upper('{0}') and cel.TABLE_NAME = upper('{0}')", importLst.TABLENAME); DataTable CellTitle = sugar.Ado.GetDataTable(sql); foreach (DataRow row in CellTitle.Rows) { var celName = row["CelName"].ToString(); DBCells cell = new DBCells(); cell.TableName = row["TableName"].ToString(); cell.CelName = row["CelName"].ToString(); cell.CelMemo = row["CelMemo"].ToString(); cell.CelType = row["CelType"].ToString(); cell.CelIsNull = row["CelIsNull"].ToString(); cell.CelDefualVal = row["CelDefualVal"].ToString(); var tdb = imBaseDtLst.Where(v => v.DBCELLNAME == celName).FirstOrDefault(); cell.IsConfig = tdb == null ? false : true; cell.ExeclName = tdb == null ? "" : tdb.EXECLCELLNAME; lstCells.Add(cell); } #endregion #region 验证文件的合法性 List lstErrCel = new List(); foreach (var dbHt in imBaseDtLst) { if (!execlDt.Columns.Contains(dbHt.EXECLCELLNAME)) { lstErrCel.Add(dbHt.EXECLCELLNAME); } } if (lstErrCel.Count > 0) throw new Exception(string.Format("EXECL 缺少配置项：[{0}]", string.Join(",", lstErrCel))); #endregion int celCode = execlDt.Columns.Count; int i = 0; string ExSql = string.Empty; Type t = execlDt.Columns[0].DataType; string setCelVal = string.Empty; DataTable dtSouse = new DataTable(); dtSouse.Columns.Add("数据类型"); dtSouse.Columns.Add("数据索引"); dtSouse.Columns.Add("数据主键"); dtSouse.Columns.Add("原因"); Dictionary dicRes = new Dictionary(); if (importLst.RELATIONSHIPCEL != "UPDATE") { #region 主数据，不存在增加，存在修改 foreach (DataRow exRow in execlDt.Rows) { ExSql = ""; if (i == 4196 || i == 5604 || i == 7743) { } if (busType >= 1000 && busType < 2000) { #region 基础资料 var F_NO = exRow[GetF_NO.EXECLCELLNAME].ToString(); if (!string.IsNullOrEmpty(F_NO)) { sql = string.Format("SELECT * FROM {0} WHERE F_NO='{1}'", importLst.TABLENAME, F_NO.TrimStart('0')); DataTable tdt = sugar.Ado.GetDataTable(sql); if (tdt != null) { if (tdt.Rows.Count > 0) { // 修改 ExSql = GetUpdateSql(exRow, lstCells, F_NO.TrimStart('0'), busType); if (!ExSql.Contains("UPDATE")) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), ExSql }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), ExSql); ExSql = ""; } setCelVal = "修改"; } else { // 增加 ExSql = GetInsertSql(exRow, lstCells, F_NO.TrimStart('0'), busType); setCelVal = "增加"; } } else { // 增加 ExSql = GetInsertSql(exRow, lstCells, F_NO.TrimStart('0'), busType); setCelVal = "增加"; } if (!string.IsNullOrEmpty(ExSql)) { try { int res = sugar.Ado.ExecuteCommand(ExSql); if (res <= 0) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + "失败" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + "失败"); } //WriteExecl(filePath, celCode, i, t, setCelVal + "失败"); else { OKCount++; dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + "成功" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + "成功"); } //WriteExecl(filePath, celCode, i, t, setCelVal + "成功"); } catch (Exception ex) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + ":" + ex.Message }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + ":" + ex.Message); //WriteExecl(filePath, celCode, i, t, setCelVal + ":" + ex.Message); } } } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "主键为空" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "主键为空"); //WriteExecl(filePath, celCode, i, t, "主键为空"); } #endregion } else if (busType >= 2000) { string keyss = string.Empty; // 初始化库存只增加 if (busType == 2000) { string F_ROWNO = Guid.NewGuid().ToString(); string Exmatmo = keyss = exRow["物料编号"].ToString(); string ExState = exRow["物料状态"].ToString(); string Exposition = exRow["存储位置"].ToString(); var ises = execlDt.Select("物料编号='" + Exmatmo + "' and 物料状态='" + ExState + "' and 存储位置='" + Exposition + "'"); if (ises.Count() > 1) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料，物料状态，存储位置。行数重复" }); dtSouse.Rows.Add("表体", i.ToString(), Exmatmo, "失败：物料不存在"); } else ExSql = GetInsertINVCONTR(exRow, lstCells, F_ROWNO, busType, i, sugar, ref dtSouse, ref dicRes); } else if (busType == 2001) { string Exmatmo = keyss = exRow["物料编号"].ToString(); string Exsupno = exRow["供应商编号"].ToString(); string Exqty = exRow["数量"].ToString(); if (string.IsNullOrEmpty(Exmatmo) || string.IsNullOrEmpty(Exsupno) || string.IsNullOrEmpty(Exqty)) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料编号||供应商编号||数量 不允许未空" }); dtSouse.Rows.Add("表体", i.ToString(), Exmatmo, "失败：物料编号||供应商编号||数量 不允许未空"); } else { var sup = sql = string.Format("SELECT * FROM BASE_SUPPLIER WHERE F_NO='{0}'", Exsupno); DataTable dtSup = sugar.Ado.GetDataTable(sql); if (dtSup.Rows.Count <= 0) { dicRes.Add(i, new string[] { i.ToString(), "失败：供应商不存在" }); dtSouse.Rows.Add("表体", i.ToString(), Exmatmo, "失败：供应商不存在"); } else { sql = string.Format("SELECT * FROM BILL_INVCONTR WHERE F_MATNO='{0}' AND F_SUPPLIERNO='{1}'", Exmatmo, Exsupno); DataTable dtStock = sugar.Ado.GetDataTable(sql); if (dtStock.Rows.Count > 0) { if (dtStock.Rows[0]["F_ROWNO"].ToString() == "240c531a-59d6-4905-8500-4478e3c09f05") { } ExSql = "UPDATE BILL_INVCONTR SET F_QTY=F_QTY+" + Exqty + " WHERE F_ROWNO='" + dtStock.Rows[0]["F_ROWNO"].ToString() + "'"; } else { string F_ROWNO = Guid.NewGuid().ToString(); ExSql = GetInsertINVCONTR(exRow, lstCells, F_ROWNO, busType, i, sugar, ref dtSouse, ref dicRes); } } } } try { if (!string.IsNullOrEmpty(ExSql)) { int res = sugar.Ado.ExecuteCommand(ExSql); if (res <= 0) { dicRes.Add(i, new string[] { i.ToString(), setCelVal + "失败" }); dtSouse.Rows.Add("表体", i.ToString(), keyss, setCelVal + "失败"); } //WriteExecl(filePath, celCode, i, t, setCelVal + "失败"); else { OKCount++; dicRes.Add(i, new string[] { i.ToString(), setCelVal + "成功" }); dtSouse.Rows.Add("表体", i.ToString(), keyss, setCelVal + "成功"); } } //WriteExecl(filePath, celCode, i, t, setCelVal + "成功"); } catch (Exception ex) { dicRes.Add(i, new string[] { i.ToString(), setCelVal + ":" + ex.Message }); dtSouse.Rows.Add("表体", i.ToString(), keyss, setCelVal + ":" + ex.Message); //WriteExecl(filePath, celCode, i, t, setCelVal + ":" + ex.Message); } } i++; } #endregion } else { #region 只有修改，找不到就报错 bool isDb = false; foreach (DataRow exRow in execlDt.Rows) { // 质检规则 if (busType == 1001) { #region 质检规则 isDb = false; var F_NO = RegexNot(exRow[GetF_NO.EXECLCELLNAME].ToString());//替换所有不可见字符为空格 if (!string.IsNullOrEmpty(F_NO)) { sql = string.Format("SELECT * FROM {0} WHERE F_NO='{1}'", importLst.TABLENAME, F_NO.TrimStart('0')); DataTable tdt = sugar.Ado.GetDataTable(sql); if (tdt != null) { if (tdt.Rows.Count > 0) { if (busType == 1001) { #region 导入质检规则 // 判断质检规则是否有配置指定参数值 var f1 = lstCells.Where(v => v.CelName == "F_QCTYPECODE").FirstOrDefault(); var f2 = lstCells.Where(v => v.CelName == "F_QCQTY").FirstOrDefault(); var f3 = lstCells.Where(v => v.CelName == "F_NO").FirstOrDefault(); if (f1 == null || f2 == null || f3 == null) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "抽检方案或者数值未配置" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "抽检方案或者数值未配置"); } else { // 获取 EXECL 值 var F_QCTYPECODE = exRow[f1.ExeclName].ToString(); var F_QCQTY = exRow[f2.ExeclName].ToString(); var GF_NO = exRow[f3.ExeclName].ToString(); if (GF_NO == "1181802") { } if (F_QCTYPECODE == "固定") { // 判断物料的包装单位和基本但是是否为空 if (string.IsNullOrEmpty(tdt.Rows[0]["F_DEFINE04"].ToString()) || string.IsNullOrEmpty(tdt.Rows[0]["F_BASEUNITNO"].ToString())) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "该物料未维护最小包装单位或者基本单位" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "该物料未维护最小包装单位或者基本单位"); } else { // 查询转换率 sql = string.Format("SELECT * FROM BASE_MATCONVUNIT WHERE F_MATNO='{0}' and F_CONVUNIT='{1}'", GF_NO, tdt.Rows[0]["F_DEFINE04"].ToString()); DataTable GetUnit = sugar.Ado.GetDataTable(sql); if (GetUnit.Rows.Count == 1) { var convval = Convert.ToDecimal(GetUnit.Rows[0]["F_CONVERSION"].ToString()); JsonConvUnitEntity cu = UnitByConvUnit.GetConvUnitByBase(busType, GF_NO, tdt.Rows[0]["F_DEFINE04"].ToString(), convval); exRow[f2.ExeclName] = cu.Entity.BaseValue.ToString(); isDb = true; // 修改 ExSql = GetUpdateSql(exRow, lstCells, F_NO.TrimStart('0'), busType); if (!ExSql.Contains("UPDATE")) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), ExSql }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), ExSql); ExSql = ""; } setCelVal = "修改"; } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "该物料未配置基本单位与包装单位的转换" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "该物料未配置基本单位与包装单位的转换"); } } } else { isDb = true; // 修改 ExSql = GetUpdateSql(exRow, lstCells, F_NO.TrimStart('0'), busType); if (!ExSql.Contains("UPDATE")) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), ExSql }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), ExSql); ExSql = ""; } setCelVal = "修改"; } } #endregion } else { isDb = true; // 修改 ExSql = GetUpdateSql(exRow, lstCells, F_NO.TrimStart('0'), busType); if (!ExSql.Contains("UPDATE")) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), ExSql }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), ExSql); ExSql = ""; } setCelVal = "修改"; } } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), F_NO + ":未找到主数据" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "未找到主数据"); } } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), F_NO + ":未找到主数据" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "未找到主数据"); } if (isDb) { if (!string.IsNullOrEmpty(ExSql)) { try { int res = sugar.Ado.ExecuteCommand(ExSql); if (res <= 0) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + "失败" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + "失败"); } //WriteExecl(filePath, celCode, i, t, setCelVal + "失败"); else { OKCount++; dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + "成功" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + "成功"); } //WriteExecl(filePath, celCode, i, t, setCelVal + "成功"); } catch (Exception ex) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + ":" + ex.Message }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + ":" + ex.Message); //WriteExecl(filePath, celCode, i, t, setCelVal + ":" + ex.Message); } } } } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "主键为空" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "主键为空"); } #endregion } else if (busType == 1002) { var F_NO = exRow[GetF_NO.EXECLCELLNAME].ToString(); try { #region SAP货位 sql = string.Format("SELECT * FROM {0} WHERE F_NO='{1}'", importLst.TABLENAME, F_NO.TrimStart('0')); DataTable tdt = sugar.Ado.GetDataTable(sql); if (tdt != null) { if (tdt.Rows.Count > 0) { // 修改 ExSql = GetUpdateSql(exRow, lstCells, F_NO.TrimStart('0'), busType); if (!ExSql.Contains("UPDATE")) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), ExSql }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), ExSql); ExSql = ""; } setCelVal = "修改"; if (!string.IsNullOrEmpty(ExSql)) { int res = sugar.Ado.ExecuteCommand(ExSql); if (res <= 0) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + "失败" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + "失败"); } //WriteExecl(filePath, celCode, i, t, setCelVal + "失败"); else { OKCount++; dicRes.Add(i, new string[] { F_NO.TrimStart('0'), setCelVal + "成功" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), setCelVal + "成功"); } } } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "未找到物料" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "未找到物料 - 0"); } } else { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), "未找到物料" }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), "未找到物料 - null"); } } catch (Exception ex) { dicRes.Add(i, new string[] { F_NO.TrimStart('0'), ex.Message }); dtSouse.Rows.Add("表体", i.ToString(), F_NO.TrimStart('0'), ex.Message); } #endregion } i++; } #endregion } ExcelConfig cof = new ExcelConfig(); cof.Title = "导入时异常信息"; // 文件名 cof.TitlePoint = 18; // 标题字号 cof.HeadPoint = 14;// 列头字号 cof.IsAllSizeColumn = true; // 列宽自适应 cof.ColumnEntity = new List() { new ColumnModel(){Column = "数据类型",ExcelColumn = "数据类型"}, new ColumnModel(){Column = "数据索引",ExcelColumn = "数据索引"}, new ColumnModel(){Column = "数据主键",ExcelColumn = "数据主键"}, new ColumnModel(){Column = "原因",ExcelColumn = "原因",Background=Color.Red}, }; json.Message = "成功：" + OKCount + " 失败：" + (dicRes.Count() - OKCount); json.Type = ((dicRes.Count() - OKCount)) <= 0 ? 2 : 1; json.dtSou = dtSouse; json.ExeclCfg = cof; } catch (Exception ex) { json.Type = 0; if (!string.IsNullOrEmpty(json.Message)) json.Message = "数据库录入成功：" + json.Message + " \n 操作回写EXECL文件时异常！" + ex.Message; else json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } /// /// /// /// 行数据 /// 数据库列信息 /// 库存主键 /// 业务类型：固定>2000 /// 循环的索引（为了将异常数据写入EXECL反馈） /// 数据库连接对象 /// 返回的异常信息 /// public string GetInsertINVCONTR(DataRow row, List lstCels, string F_ROWNO, int busType, int i, SqlSugarClient sugar, ref DataTable dtSouse, ref Dictionary dicRes) { // 保存SQL列 string resT = string.Empty; // 保存SQL值 string valT = string.Empty; string val = string.Empty; // 料号 string matNoEX = RegexNot(row["物料编号"].ToString().Trim()); #region 物料 if (string.IsNullOrEmpty(matNoEX)) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]不能为空" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "物料号不能为空"); return ""; } string sql = string.Format("SELECT * FROM BASE_MATITEM WHERE F_NO='{0}'", matNoEX); DataTable dtMat = sugar.Ado.GetDataTable(sql); if (dtMat.Rows.Count <= 0) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]未找到物料基础信息" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "未找到物料基础信息"); return ""; } #endregion // 物料状态 int matSatet = 0; string strWarehouse = string.Empty; string strWareAear = string.Empty; if (busType == 2000) { strWarehouse = "XN"; strWareAear = "INIT_XN"; #region 获取状态 string matSatetEX = RegexNot(row["物料状态"].ToString().Trim()); switch (matSatetEX) { case "UU-合格": matSatet = 1; break; case "BLOCK-不合格": matSatet = 3; break; case "QI-冻结": matSatet = 4; break; default: dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]状态不是可识别的状态" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "状态不是可识别的状态"); return ""; } #endregion } else if (busType == 2001) { strWarehouse = "WW"; strWareAear = "WW_Warea"; matSatet = 1; } // 保存数量 decimal qty = 0; #region 数量 string StrqtyEX = RegexNot(row["数量"].ToString().Trim()); try { decimal qtyEX = decimal.Parse(StrqtyEX); if (qtyEX <= 0) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]数量值不能小于等于0" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "数量值不能小于等于0："); return ""; } else { if (matSatet == 1 && busType == 2000) { // 获取库存数量 sql = string.Format("SELECT F_QTY,F_MATNO FROM BILL_INVCONTR WHERE F_WAREHOUSENO IN('LS','2FCJ','2F','ZC','WY','JZ','WZZ','GY','PT','WTP','WH','1FDT','TB') and F_MATNO='{0}' and F_MATSTATENUM=1", matNoEX); DataTable dtStock = sugar.Ado.GetDataTable(sql); decimal dbqty = 0; if (dtStock.Rows.Count > 0) { //var sumQty = from p in dtSouse.AsEnumerable() // group p by new { DBmatno = p.Field("F_MATNO") } into m // select new // { // DBmatno = m.Key.DBmatno, // stockQty = m.Sum(v => v.Field("F_QTY")) // }; foreach (DataRow item in dtStock.Rows) { dbqty += Convert.ToDecimal(item["F_QTY"].ToString()); } // EXECL数量减去WMS数量等于本次要操作的数量 qty = qtyEX - dbqty; if (qty == 0) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]EXECL数量与WMS相同，不予以导入" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "EXECL数量与WMS相同，不予以导入"); return ""; } } else qty = qtyEX; } else // 未找到库存则已EXECL数量为准 qty = qtyEX; } } catch (Exception ex) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]数量值异常" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "数量值异常：" + ex.Message); return ""; } #endregion string F_CONTRGRPNO = string.Empty; if (busType == 2000) { #region 容器组ID string POSITION = RegexNot(row["存储位置"].ToString().Trim()); if (!string.IsNullOrEmpty(POSITION)) { string[] strPosi = new string[] { "3000", "3001", "3010", "3030", "3040", "3041", "3045", "3046", "3050", "3060", "3090", "3095", "3099" }; if (!strPosi.Contains(POSITION)) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]不是可识别的存储位置" }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "不是可识别的存储位置：" + POSITION); return ""; } F_CONTRGRPNO = "d1f0db88-75df-483c-b7d2-XN-INIT-" + POSITION; sql = string.Format("SELECT * FROM BASE_WARECELL WHERE F_NO ='{0}' AND F_CNTRGRPNO='{1}'", "XN-INIT-" + POSITION, F_CONTRGRPNO); DataTable dtCell = sugar.Ado.GetDataTable(sql); if (dtCell.Rows.Count <= 0) { dicRes.Add(i, new string[] { i.ToString(), "失败：物料号[" + matNoEX + "]未找到容器ID，请检查存储位置:" + POSITION }); dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "未找到容器ID，请检查存储位置:" + POSITION); return ""; } } else { dtSouse.Rows.Add("表体", i.ToString(), matNoEX, "存储位置不能为空："); return ""; } #endregion } else if (busType == 2001) { F_CONTRGRPNO = "00000000000000000000000000000000"; } string TempF_BATCHNO = "CS" + System.DateTime.Now.ToString("yyyyMMdd"); string TempF_MATVERSION = "-"; string TempF_MATGEAR = "-"; resT = "F_ROWNO,F_MATSTATENUM,F_QTY,F_CONTRGRPNO,F_MATNO,F_MATNAME,F_WAREHOUSENO,F_WAREANO,F_BATCHNO,F_MATVERSION,F_MATGEAR,"; valT = string.Format("'{0}',{1},{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',", F_ROWNO, matSatet, qty, F_CONTRGRPNO, matNoEX, dtMat.Rows[0]["F_NAME"].ToString(), strWarehouse, strWareAear, TempF_BATCHNO, TempF_MATVERSION, TempF_MATGEAR); // 保存不需要循环获取的字段 foreach (var cel in lstCels) { val = string.Empty; if (resT.Contains(cel.CelName)) continue; if (cel.IsConfig) { val = RegexNot(row[cel.ExeclName].ToString().Trim()); } if (cel.CelIsNull == "N") { resT += cel.CelName + ","; #region 不允许为空 switch (cel.CelType) { case "DATE": if (string.IsNullOrEmpty(val)) valT += "to_date('" + DateTime.Now.ToString("yyyy-MM-dd") + "','YYYY-MM-DD'),"; else valT += "to_date('" + val + "','YYYY-MM-DD'),"; break; case "NUMBER": if (string.IsNullOrEmpty(val)) valT += "'0',"; else valT += "'" + val + "',"; break; default: if (string.IsNullOrEmpty(val)) valT += "'WMS',"; else valT += "'" + val + "',"; break; } #endregion } else { #region 允许为空 if (!string.IsNullOrEmpty(val)) { resT += cel.CelName + ","; switch (cel.CelType) { case "DATE": valT += "to_date('" + val + "','YYYY-MM-DD'),"; break; case "NUMBER": valT += "'" + val + "',"; break; default: valT += "'" + val + "',"; break; } } #endregion } } resT = resT.Trim(','); valT = valT.Trim(','); return string.Format(@"INSERT INTO {0}({1}) VALUES({2})", lstCels[0].TableName, resT, valT); } /// /// 通用单据导入方法（简单校验） /// /// /// /// /// public JsonExecl ImportOrderD(DataTable dtExeclSouse, int busTypeVal, string loginName) { JsonExecl json = new JsonExecl(); json.Type = 0; // busType SqlSugarClient sugar = SysDbCore.GetDbCtx(); try { if (dtExeclSouse == null) throw SysExCore.ThrowFailException("数据源不能为null"); if (dtExeclSouse.Rows.Count <= 0) throw SysExCore.ThrowFailException("数据源数据行数必须大于0"); // 获取源数据 DataTable execlDt = dtExeclSouse;//ExcelHelper.ExcelImport(filePath, 0, 0); EOrderType otype = (EOrderType)busTypeVal; #region 获取数据库参数配置 // 获取参数配置 var importLst = sugar.Queryable().Where(v => v.BUSINSERTVAL == busTypeVal).ToList(); var imptHt = importLst.Where(v => v.STRUCTURE == "表头").FirstOrDefault(); if (imptHt == null) throw SysExCore.ThrowFailException("单据导入必须有表头参数定义"); // 获取表体参数 var imptDt = importLst.Where(v => v.STRUCTURE == "表体").FirstOrDefault(); if (imptDt == null) throw SysExCore.ThrowFailException("单据导入必须有表体参数定义"); var imBaseHtLst = sugar.Queryable().Where(v => v.F_NO == imptHt.F_NO).ToList(); var imBaseDtLst = sugar.Queryable().Where(v => v.F_NO == imptDt.F_NO).ToList(); if (imBaseHtLst.Count <= 0 || imBaseDtLst.Count <= 0) throw SysExCore.ThrowFailException("未找到字段参数的配置"); #endregion // string busType = busTypeVal;//imptHt.BUSINESSTYPE; // 表体区开始索引 int staIndex = 0; // 表体区结束索引 int endIndex = 0; int heatStaIndex = 0; Dictionary dicKeys = new Dictionary(); List lstOrder = new List(); List lstItem = new List(); // 判断表头数据是否在 EXECL 的表头数据中 if (imBaseHtLst.Where(v => v.F_DATATYPE == "表头").FirstOrDefault() != null) { #region EXECL 表头表体数据混合模式 DataTable dtHead = new DataTable(); DataTable dtData = new DataTable(); List lstTitel = new List(); // 如果表头关键词为空，则从 0 索引开始查询 if (string.IsNullOrEmpty(imptDt.F_STAKEY)) { if (string.IsNullOrEmpty(imptDt.F_ENDKEY)) throw SysExCore.ThrowFailException("EXECL 表体区，表头区混合模式下必须是 { 表体开始关键词为空，表体结束关键词不能为空 } "); #region 初始化表头数据 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_STATENUM = 0; order.F_TYPENUM = busTypeVal; foreach (DataColumn cel in execlDt.Columns) { dtHead.Columns.Add(cel.ColumnName); } bool IsEmtry = false; var tlstHt = imBaseHtLst.Where(v => v.F_DATATYPE == "表体").ToList(); if (tlstHt.Count > 0) { #region 初始化表头对象，先从表体区找到表头的值 foreach (var ht in tlstHt) { var t = execlDt.Rows[0][ht.EXECLCELLNAME].ToString().Trim(); var val = RegexNot(t); #region 表头单号 if (ht.DBCELLNAME == "F_NO") { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else { SetProperties(order, ht.DBCELLNAME, busTypeVal + "_" + val); } } #endregion #region 表头供应商 else if (ht.DBCELLNAME == "F_SUPPLIERNO") { if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "供应商不存在"); } } } } #endregion #region 表头客户 else if (ht.DBCELLNAME == "F_CUSTOMERNO") { if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "客户不存在"); } } } } #endregion #region 设置字段 else { if (ht.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, ht.DBCELLNAME, val); } else SetProperties(order, ht.DBCELLNAME, val); } #endregion //if (!string.IsNullOrEmpty(val)) //{ // SetProperties(order, ht.DBCELLNAME, val); //} } #endregion } #region 初始化表头对象，在找到表头区的值，目标是为了生成单号给表体。 for (int i = 0; i < execlDt.Rows.Count; i++) { IsEmtry = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { IsEmtry = true; break; } } // 如果有有一列不为空，则正常走逻辑 if (IsEmtry) { if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (endIndex > 0) { heatStaIndex = i; for (int l = 0; l < execlDt.Columns.Count; l++) { var ht = imBaseHtLst.Where(v => v.F_DATATYPE == "表头" && v.EXECLCELLNAME == execlDt.Rows[i][l].ToString()).FirstOrDefault(); if (ht != null) { var t = execlDt.Rows[i][l + 1].ToString(); var val = RegexNot(t); #region 表头单号 if (ht.DBCELLNAME == "F_NO") { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else { SetProperties(order, ht.DBCELLNAME, busTypeVal + "_" + val); } } #endregion #region 表头供应商 else if (ht.DBCELLNAME == "F_SUPPLIERNO") { if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "供应商不存在"); } } } } #endregion #region 表头客户 else if (ht.DBCELLNAME == "F_CUSTOMERNO") { if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "客户不存在"); } } } } #endregion #region 设置字段 else { if (ht.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); else SetProperties(order, ht.DBCELLNAME, val); } else SetProperties(order, ht.DBCELLNAME, val); //if (!string.IsNullOrEmpty(val)) // SetProperties(order, ht.DBCELLNAME, val); } #endregion } } } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("EXECL 文件中未找到单号"); if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); // 如果F_DEFINE15等于空,表示EXECL文件中的数据是完整的。就开始验证数据库中的验证 if (string.IsNullOrEmpty(order.F_DEFINE15)) { var torder = sugar.Queryable().Where(v => v.F_NO == order.F_NO).First(); if (torder != null) { if (torder.F_STATENUM > 0) order.F_DEFINE15 = order.F_NO + ":单据已开始，无法重新覆盖"; } } lstOrder.Add(order); #endregion #region 初始化表体数据 endIndex = 0; for (int i = 0; i < execlDt.Rows.Count; i++) { IsEmtry = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { IsEmtry = true; break; } } // 如果有有一列不为空，则正常走逻辑 if (IsEmtry) { #region 非空的数据添加到临时 DataTable if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (endIndex <= 0) { #region 表体对象初始化 BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; for (int l = 0; l < execlDt.Columns.Count; l++) { var dt = imBaseDtLst.Where(v => v.EXECLCELLNAME == execlDt.Columns[l].ToString()).FirstOrDefault(); if (dt != null) { if (dt.DBCELLNAME.Equals("F_ORDERNO")) continue; var t = execlDt.Rows[i][l].ToString(); var val = RegexNot(t); #region 物料 if (dt.DBCELLNAME == "F_MATNO") { item.F_MATNO = val; if (string.IsNullOrEmpty(val)) { SetProperties(item, "F_DEFINE10", dt.EXECLCELLNAME + ":不能为空"); } else { var mat = sugar.Queryable().Where(v => v.F_NO == val).First(); if (mat == null) item.F_DEFINE10 = val + ":物料不存在"; else { if (mat.F_QCTYPECODE == null) { item.F_DEFINE10 = val + ":质检标示不完全"; } else if (int.Parse(mat.F_QCTYPECODE) <= 0 || int.Parse(mat.F_QCTYPECODE) >= 4) { item.F_DEFINE10 = val + ":质检标示不是可识别的标识"; } else { item.F_MATNAME = mat.F_NAME; item.F_UNITNO = mat.F_BASEUNITNO; } } } } #endregion else { if (dt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", dt.EXECLCELLNAME + ":不能为空"); else SetProperties(item, dt.DBCELLNAME, val); } else SetProperties(item, dt.DBCELLNAME, val); } } } lstItem.Add(item); #endregion } #endregion } } #endregion } else { // 表体开始不能为空，表体结束不能为空 if (string.IsNullOrEmpty(imptDt.F_ENDKEY) || string.IsNullOrEmpty(imptDt.F_STAKEY)) throw SysExCore.ThrowFailException("EXECL表头区，表体区，表尾区混合模式下。{ 表体的开始关键词和表体的结束关键词不能为空 }"); #region 初始化表头 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_STATENUM = 0; order.F_TYPENUM = busTypeVal; var celName = string.Empty; var celVal = string.Empty; // 保存源列名和表体列名的关系 // 0：源列名 // 1：表体列名 List lstKeys = new List(); bool isNull = false; for (int i = 0; i < execlDt.Rows.Count; i++) { isNull = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { isNull = true; break; } } if (isNull) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; foreach (DataColumn cel in execlDt.Columns) { lstKeys.Add(new string[] { cel.ColumnName, execlDt.Rows[i][cel].ToString() }); } heatStaIndex = i; continue; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex > 0 && endIndex <= 0 && lstKeys.Count > 0) { // 如果表头中的数据存在表体，则初始化对象 #region 初始化表头数据 // 查询表头的数据中是否在表体 var tlstHt = imBaseHtLst.Where(v => v.F_DATATYPE == "表体"); if (tlstHt != null) { if (tlstHt.Count() > 0) { foreach (var dt in tlstHt) { var cel = lstKeys.Where(v => v[1] == dt.EXECLCELLNAME).FirstOrDefault(); if (cel != null) { var t = execlDt.Rows[i][cel[0]].ToString(); var val = RegexNot(t); #region 表头单号 if (dt.DBCELLNAME == "F_NO") { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空"); } else { SetProperties(order, dt.DBCELLNAME, busTypeVal + "_" + val); } } #endregion #region 表头供应商 else if (dt.DBCELLNAME == "F_SUPPLIERNO") { if (dt.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "供应商不存在"); } } } } #endregion #region 表头客户 else if (dt.DBCELLNAME == "F_CUSTOMERNO") { if (dt.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "客户不存在"); } } } } #endregion #region 设置字段 else { if (dt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, dt.DBCELLNAME, val); } else SetProperties(order, dt.DBCELLNAME, val); } #endregion } } } } #endregion } if (staIndex <= 0 || endIndex > 0) { for (int l = 0; l < execlDt.Columns.Count; l++) { celName = execlDt.Rows[i][l].ToString(); if (!string.IsNullOrEmpty(celName)) { var imp = imBaseHtLst.Where(v => celName.Contains(v.EXECLCELLNAME) && v.F_DATATYPE == "表头").FirstOrDefault(); if (imp != null) { var t = execlDt.Rows[i][l + 1].ToString(); celVal = RegexNot(t); #region 表头单号 if (imp.DBCELLNAME == "F_NO") { if (string.IsNullOrEmpty(celVal)) { SetProperties(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空"); } else { SetProperties(order, imp.DBCELLNAME, busTypeVal + "_" + celVal); } } #endregion #region 表头供应商 else if (imp.DBCELLNAME == "F_SUPPLIERNO") { if (imp.F_ISNULL == 1 && string.IsNullOrEmpty(celVal)) { SetProperties(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(celVal)) { var sup = sugar.Queryable().Where(v => v.F_NO == celVal || v.F_NAME == celVal).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "供应商不存在"); } } } } #endregion #region 表头客户 else if (imp.DBCELLNAME == "F_CUSTOMERNO") { if (imp.F_ISNULL == 1 && string.IsNullOrEmpty(celVal)) { SetProperties(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(celVal)) { var sup = sugar.Queryable().Where(v => v.F_NO == celVal || v.F_NAME == celVal).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "客户不存在"); } } } } #endregion #region 设置字段 else { if (imp.F_ISNULL == 1) { if (string.IsNullOrEmpty(celVal)) { SetProperties(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, imp.DBCELLNAME, celVal); } else SetProperties(order, imp.DBCELLNAME, celVal); } #endregion } } } } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); // 如果F_DEFINE15等于空,表示EXECL文件中的数据是完整的。就开始验证数据库中的验证 if (string.IsNullOrEmpty(order.F_DEFINE15)) { var torder = sugar.Queryable().Where(v => v.F_NO == order.F_NO).First(); if (torder != null) { if (torder.F_STATENUM > 0) order.F_DEFINE15 = order.F_NO + ":单据已开始，无法重新覆盖"; } } lstOrder.Add(order); staIndex = 0; endIndex = 0; #region 初始化表体数据 for (int i = 0; i < execlDt.Rows.Count; i++) { isNull = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { isNull = true; break; } } if (isNull) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; continue; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex > 0 && endIndex <= 0 && lstKeys.Count > 0) { if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单据编号值不能为空"); BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; foreach (var cel in lstKeys) { if (cel[1] == "PO单号") { } //if (!string.IsNullOrEmpty(execlDt.Rows[i][cel[0]].ToString())) //{ var impt = imBaseDtLst.Where(v => v.EXECLCELLNAME == cel[1]).FirstOrDefault(); if (impt != null) { var t = execlDt.Rows[i][cel[0]].ToString(); var val = RegexNot(t); if (impt.DBCELLNAME.Equals("F_ORDERNO")) continue; #region 物料 if (impt.DBCELLNAME == "F_MATNO") { item.F_MATNO = val; if (string.IsNullOrEmpty(val)) { SetProperties(item, "F_DEFINE10", impt.EXECLCELLNAME + ":不能为空"); } else { var mat = sugar.Queryable().Where(v => v.F_NO == val).First(); if (mat == null) item.F_DEFINE10 = val + ":物料不存在"; else { if (mat.F_QCTYPECODE == null) { item.F_DEFINE10 = val + ":质检标示不完全"; } else if (int.Parse(mat.F_QCTYPECODE) <= 0 || int.Parse(mat.F_QCTYPECODE) >= 4) { item.F_DEFINE10 = val + ":质检标示不是可识别的标识"; } else { item.F_MATNAME = mat.F_NAME; item.F_UNITNO = mat.F_BASEUNITNO; } } } } #endregion else { if (impt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", impt.EXECLCELLNAME + ":不能为空"); else SetProperties(item, impt.DBCELLNAME, val); } else SetProperties(item, impt.DBCELLNAME, val); } } } lstItem.Add(item); } } } #endregion } #endregion } else { // 全表体混合模式，关键字段 [单号] ,在 EXECL 中必须用 [单号字样表示] if (!execlDt.Columns.Contains("单号")) throw SysExCore.ThrowFailException("EXECL表体区混合模式下， EXECL 文件中必须包含 [ 单号 ] 关键字"); if (imBaseHtLst.Where(v => v.EXECLCELLNAME == "单号").FirstOrDefault() == null) throw SysExCore.ThrowFailException("表头字段配置参数列表中必须包含 [ 单号 ] 关系列"); if (imBaseDtLst.Where(v => v.EXECLCELLNAME == "单号").FirstOrDefault() == null) throw SysExCore.ThrowFailException("表体字段配置参数列表中必须包含 [ 单号 ] 关系列"); #region EXECL 数据库表头 主、子 表数据，混合在 EXECL 表体区 // 临时表体源 DataTable temDt = new DataTable(); // 判断是否指定了开始行的关键词 if (!string.IsNullOrEmpty(imptDt.F_STAKEY)) { // 如果指定了关键词，则将EXECL中的表体区数据抠出。 #region 将EXECL表体区的数据抠出来放在 temDt // 保存源列名和现列名的关系 List lstTitel = new List(); for (int i = 0; i < execlDt.Rows.Count; i++) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex == i) { foreach (DataColumn cel in execlDt.Columns) { if (!execlDt.Rows[staIndex][cel].ToString().Contains("提交结果：")) { if (string.IsNullOrEmpty(execlDt.Rows[staIndex][cel].ToString().Trim())) throw SysExCore.ThrowFailException("表体区的表头不能为空"); string[] s = new string[] { cel.ColumnName, execlDt.Rows[staIndex][cel].ToString().Trim() }; lstTitel.Add(s); temDt.Columns.Add(execlDt.Rows[staIndex][cel].ToString().Trim()); } } } if (i > staIndex && lstTitel.Count > 0) { if (endIndex <= 0) { var di = temDt.Rows.Count; temDt.Rows.Add(1); foreach (var ls in lstTitel) { temDt.Rows[di][ls[1]] = execlDt.Rows[i][ls[0]]; } } } } #endregion } else { // 如果未制定关键词，则按照源 temDt = execlDt; } #region 转换对象 var F_NOLst = from t in temDt.AsEnumerable() group t by new { F_NO = t.Field("单号"), } into m select new { F_NO = m.Key.F_NO }; foreach (var F_NO in F_NOLst) { if (string.IsNullOrEmpty(F_NO.F_NO)) continue; var F_NORow = temDt.Select("单号='" + F_NO.F_NO + "'"); if (F_NORow.Length <= 0) throw SysExCore.ThrowFailException("未找到数据信息"); #region 初始化表头 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_TYPENUM = busTypeVal; order.F_STATENUM = 0; foreach (var imbase in imBaseHtLst) { if (temDt.Columns.Contains(imbase.EXECLCELLNAME)) { var t = F_NORow[0][imbase.EXECLCELLNAME].ToString().Trim(); var val = RegexNot(t); #region 表头单号 if (imbase.DBCELLNAME == "F_NO") { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空"); } else { SetProperties(order, imbase.DBCELLNAME, busTypeVal + "_" + val); } } #endregion #region 表头供应商 else if (imbase.DBCELLNAME == "F_SUPPLIERNO") { if (imbase.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "供应商不存在"); } } } } #endregion #region 表头客户 else if (imbase.DBCELLNAME == "F_CUSTOMERNO") { if (imbase.F_ISNULL == 1 && string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空"); } else { if (!string.IsNullOrEmpty(val)) { var sup = sugar.Queryable().Where(v => v.F_NO == val || v.F_NAME == val).First(); if (sup == null) { SetProperties(order, "F_DEFINE15", "客户不存在"); } } } } #endregion #region 设置字段 else { if (imbase.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, imbase.DBCELLNAME, val); } else SetProperties(order, imbase.DBCELLNAME, val); } #endregion } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); // 如果F_DEFINE15等于空,表示EXECL文件中的数据是完整的。就开始验证数据库中的验证 if (string.IsNullOrEmpty(order.F_DEFINE15)) { var torder = sugar.Queryable().Where(v => v.F_NO == order.F_NO).First(); if (torder != null) { if (torder.F_STATENUM > 0) order.F_DEFINE15 = order.F_NO + ":单据已开始，无法重新覆盖"; } } lstOrder.Add(order); #region 初始化表体 foreach (DataRow row in F_NORow) { BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; if (busTypeVal == 17) { item.F_MATSTATENUM = 1; } foreach (var imbase in imBaseDtLst) { var t = row[imbase.EXECLCELLNAME].ToString(); var val = RegexNot(t); #region 物料状态 if (imbase.DBCELLNAME.Equals("F_ORDERNO")) continue; // 判断物料状态 if (imbase.DBCELLNAME.Equals("F_MATSTATENUM")) { if (!string.IsNullOrEmpty(val)) { EMatState ot = (EMatState)Enum.Parse(typeof(EMatState), val); SetProperties(item, imbase.DBCELLNAME, ((int)ot).ToString()); } else { SetProperties(item, imbase.DBCELLNAME, "0"); } } #endregion #region 物料 else if (imbase.DBCELLNAME == "F_MATNO") { item.F_MATNO = val; if (string.IsNullOrEmpty(val)) { SetProperties(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":不能为空"); } else { var mat = sugar.Queryable().Where(v => v.F_NO == val).First(); if (mat == null) item.F_DEFINE10 = val + ":物料不存在"; else { if (mat.F_QCTYPECODE == null) { item.F_DEFINE10 = val + ":质检标示不完全"; } else if (int.Parse(mat.F_QCTYPECODE) <= 0 || int.Parse(mat.F_QCTYPECODE) >= 4) { item.F_DEFINE10 = val + ":质检标示不是可识别的标识"; } else { item.F_MATNAME = mat.F_NAME; item.F_UNITNO = mat.F_BASEUNITNO; } } } } #endregion #region 设置字段 else { if (imbase.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":不能为空"); else SetProperties(item, imbase.DBCELLNAME, val); } else { SetProperties(item, imbase.DBCELLNAME, val); } } #endregion } lstItem.Add(item); } #endregion } #endregion #endregion } var vvv = lstOrder; var bbb = lstItem; #region MyRegion #endregion #region 数据库操作 if (lstItem.Count <= 0) throw SysExCore.ThrowFailException("表体数据未空，无法导入"); if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() == null && lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() == null) { try { int f = 0; sugar.BeginTran(); foreach (var order in lstOrder) { f = 0; var dbOrder = sugar.Queryable().Where(v => v.F_NO == order.F_NO).First(); if (dbOrder == null) { f = sugar.Insertable(order).ExecuteCommand(); if (f > 0) { f = sugar.Insertable(lstItem.Where(v => v.F_ORDERNO == order.F_NO).ToArray()).ExecuteCommand(); } } else { if (dbOrder.F_ISDELETE == 1 || dbOrder.F_ISSTOP == 1) throw new Exception("此单据已经停用或者已经被删除，无法导入"); if (order.F_STATENUM < 3) { foreach (var item in lstItem) { //EOrderType _EOrderType = EOrderType.Check_In_FG; if (order.F_TYPENUM == 7) { var SSS = item.F_FROMORDERNO; if (item.F_FROMORDERNO.Split('_').Count() <= 1) { SSS = "SAP05" + "_" + item.F_FROMORDERNO; } var or5 = sugar.Queryable().Where(v => v.F_NO == SSS).First(); if (or5 == null) throw new Exception("未找到PO指令单号"); } else if (order.F_TYPENUM == 6) { var or5 = sugar.Queryable().Where(v => v.F_NO == order.F_NO).First(); if (or5 != null) throw new Exception("INB单不允许重复导入"); } else if (order.F_TYPENUM == 37) { var SSS = item.F_FROMORDERNO; if (item.F_FROMORDERNO.Split('_').Count() <= 1) { SSS = "SAP06" + "_" + item.F_FROMORDERNO; } var or5 = sugar.Queryable().Where(v => v.F_NO == SSS).First(); if (or5 == null) throw new Exception("未找到INB指令单号"); } var dbItem = sugar.Queryable().Where(v => v.F_ORDERNO == order.F_NO && v.F_MATNO == item.F_MATNO).First(); if (dbItem == null) { f = sugar.Insertable(item).ExecuteCommand(); } else { // 如果WMS的操作数量大于本次修改的计划量，将不允许修改 if (dbItem.F_AQTY > item.F_FQTY) { item.F_DEFINE10 = "计划量不能小于实际量"; } else { dbItem.F_AQTY = item.F_AQTY; f = sugar.Updateable(dbItem).ExecuteCommand(); } } } } else { order.F_DEFINE15 = string.Format("单据状态：<{0}>操作失败", order.F_NO); } } } sugar.CommitTran(); } catch (Exception ex) { sugar.RollbackTran(); lstOrder.ForEach(w => w.F_DEFINE15 = ex.Message); lstItem.ForEach(w => w.F_DEFINE10 = ex.Message); } } #endregion if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() != null || lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() != null) { DataTable dtSouse = new DataTable(); dtSouse.Columns.Add("数据类型"); dtSouse.Columns.Add("数据值"); dtSouse.Columns.Add("原因"); var heat = lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).ToArray(); if (heat.Count() > 0) { foreach (var item in heat) { dtSouse.Rows.Add("表头", item.F_NO, item.F_DEFINE15); } } var data = lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).ToArray(); if (data.Count() > 0) { foreach (var item in data) { dtSouse.Rows.Add("表体", item.F_MATNO, item.F_DEFINE10); } } if (dtSouse.Rows.Count > 0) { ExcelConfig cof = new ExcelConfig(); //cof.FileName = @"D:\Project\科勒\EXECL\ERR222.xls"; // 文件名 只能是 xls格式 cof.Title = "导入时异常信息"; // 文件名 cof.TitlePoint = 18; // 标题字号 cof.HeadPoint = 14;// 列头字号 cof.IsAllSizeColumn = true; // 列宽自适应 cof.ColumnEntity = new List() { new ColumnModel(){Column = "数据类型",ExcelColumn = "数据类型"}, new ColumnModel(){Column = "数据值",ExcelColumn = "数据值"}, new ColumnModel(){Column = "原因",ExcelColumn = "原因",Background=Color.Red}, }; json.Type = 1; json.Message = "导入失败"; json.dtSou = dtSouse; json.ExeclCfg = cof; //ExcelHelper.ExcelExport(dtSouse, cof); } else { json.Type = 2; json.Message = "导入成功"; } } else { json.Type = 2; json.Message = "导入成功"; } } catch (Exception ex) { json.Type = 0; json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } /// /// 通用单据导入方法（没有任何验证） /// /// /// /// /// public JsonExecl ImportOrderData(DataTable dtExeclSouse, int busTypeVal, string loginName) { JsonExecl json = new JsonExecl(); json.Type = 0; // busType SqlSugarClient sugar = SysDbCore.GetDbCtx(); try { if (dtExeclSouse == null) throw SysExCore.ThrowFailException("数据源不能为null"); if (dtExeclSouse.Rows.Count <= 0) throw SysExCore.ThrowFailException("数据源数据行数必须大于0"); // 获取源数据 DataTable execlDt = dtExeclSouse;//ExcelHelper.ExcelImport(filePath, 0, 0); EOrderType otype = (EOrderType)busTypeVal; List importLst = null; #region 获取数据库参数配置 if (busTypeVal == 0) throw SysExCore.ThrowFailException("单据类型异常"); else if (busTypeVal > 0) // 获取参数配置 importLst = sugar.Queryable().Where(v => v.BUSINSERTVAL == busTypeVal).ToList(); else if (busTypeVal == -1 || busTypeVal == -2) { busTypeVal = 7; // PO收货单(中) if (busTypeVal == -1) importLst = sugar.Queryable().Where(v => new string[] { "9", "10" }.Contains(v.F_NO)).ToList(); else // PO收货单(英) importLst = sugar.Queryable().Where(v => new string[] { "50", "51" }.Contains(v.F_NO)).ToList(); } else { throw SysExCore.ThrowFailException("未识别的单据类型"); } if (importLst.Count <= 0) throw SysExCore.ThrowFailException("未找到配置参数"); var imptHt = importLst.Where(v => v.STRUCTURE == "表头").FirstOrDefault(); if (imptHt == null) throw SysExCore.ThrowFailException("单据导入必须有表头参数定义"); // 获取表体参数 var imptDt = importLst.Where(v => v.STRUCTURE == "表体").FirstOrDefault(); if (imptDt == null) throw SysExCore.ThrowFailException("单据导入必须有表体参数定义"); var imBaseHtLst = sugar.Queryable().Where(v => v.F_NO == imptHt.F_NO).ToList(); var imBaseDtLst = sugar.Queryable().Where(v => v.F_NO == imptDt.F_NO).ToList(); if (imBaseHtLst.Count <= 0 || imBaseDtLst.Count <= 0) throw SysExCore.ThrowFailException("未找到字段参数的配置"); #endregion // string busType = busTypeVal;//imptHt.BUSINESSTYPE; // 表体区开始索引 int staIndex = 0; // 表体区结束索引 int endIndex = 0; int heatStaIndex = 0; Dictionary dicKeys = new Dictionary(); List lstOrder = new List(); List lstItem = new List(); // 判断表头数据是否在 EXECL 的表头数据中 if (imBaseHtLst.Where(v => v.F_DATATYPE == "表头").FirstOrDefault() != null) { #region EXECL 表头表体数据混合模式 DataTable dtHead = new DataTable(); DataTable dtData = new DataTable(); List lstTitel = new List(); // 如果表头关键词为空，则从 0 索引开始查询 if (string.IsNullOrEmpty(imptDt.F_STAKEY)) { if (string.IsNullOrEmpty(imptDt.F_ENDKEY)) throw SysExCore.ThrowFailException("EXECL 表体区，表头区混合模式下必须是 { 表体开始关键词为空，表体结束关键词不能为空 } "); #region 初始化表头数据 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_STATENUM = 0; order.F_TYPENUM = busTypeVal; foreach (DataColumn cel in execlDt.Columns) { dtHead.Columns.Add(cel.ColumnName); } bool IsEmtry = false; var tlstHt = imBaseHtLst.Where(v => v.F_DATATYPE == "表体").ToList(); if (tlstHt.Count > 0) { #region 初始化表头对象，先从表体区找到表头的值 foreach (var ht in tlstHt) { var t = execlDt.Rows[0][ht.EXECLCELLNAME].ToString().Trim(); var val = RegexNot(t); if (ht.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, ht.DBCELLNAME, val); } else SetProperties(order, ht.DBCELLNAME, val); } #endregion } #region 初始化表头对象，在找到表头区的值，目标是为了生成单号给表体。 for (int i = 0; i < execlDt.Rows.Count; i++) { IsEmtry = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { IsEmtry = true; break; } } // 如果有有一列不为空，则正常走逻辑 if (IsEmtry) { if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (endIndex > 0) { heatStaIndex = i; for (int l = 0; l < execlDt.Columns.Count; l++) { var ht = imBaseHtLst.Where(v => v.F_DATATYPE == "表头" && v.EXECLCELLNAME == execlDt.Rows[i][l].ToString()).FirstOrDefault(); if (ht != null) { var t = execlDt.Rows[i][l + 1].ToString(); var val = RegexNot(t); if (ht.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空"); else SetProperties(order, ht.DBCELLNAME, val); } else SetProperties(order, ht.DBCELLNAME, val); } } } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("EXECL 文件中未找到单号"); if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); lstOrder.Add(order); #endregion #region 初始化表体数据 endIndex = 0; for (int i = 0; i < execlDt.Rows.Count; i++) { IsEmtry = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { IsEmtry = true; break; } } // 如果有有一列不为空，则正常走逻辑 if (IsEmtry) { #region 非空的数据添加到临时 DataTable if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (endIndex <= 0) { #region 表体对象初始化 BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; item.F_LOCKSTATUS = (int)ELOCKSTATUS.NoLockStatus; for (int l = 0; l < execlDt.Columns.Count; l++) { var dt = imBaseDtLst.Where(v => v.EXECLCELLNAME == execlDt.Columns[l].ToString()).FirstOrDefault(); if (dt != null) { if (dt.DBCELLNAME.Equals("F_ORDERNO")) continue; var t = execlDt.Rows[i][l].ToString(); var val = RegexNot(t); // 物料状态 if (dt.DBCELLNAME.Equals("F_MATSTATENUM")) { var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First(); if (matstate == null) SetProperties(item, "F_DEFINE10", dt.EXECLCELLNAME + ":选中的值《" + val + "》不存在"); else SetProperties(item, dt.DBCELLNAME, matstate.F_NUM.ToString()); continue; } if (dt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", dt.EXECLCELLNAME + ":不能为空"); else SetProperties(item, dt.DBCELLNAME, val); } else SetProperties(item, dt.DBCELLNAME, val); } } lstItem.Add(item); #endregion } #endregion } } #endregion } else { // 表体开始不能为空，表体结束不能为空 if (string.IsNullOrEmpty(imptDt.F_ENDKEY) || string.IsNullOrEmpty(imptDt.F_STAKEY)) throw SysExCore.ThrowFailException("EXECL表头区，表体区，表尾区混合模式下。{ 表体的开始关键词和表体的结束关键词不能为空 }"); #region 初始化表头 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_STATENUM = 0; order.F_TYPENUM = busTypeVal; var celName = string.Empty; var celVal = string.Empty; // 保存源列名和表体列名的关系 // 0：源列名 // 1：表体列名 List lstKeys = new List(); bool isNull = false; for (int i = 0; i < execlDt.Rows.Count; i++) { isNull = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { isNull = true; break; } } if (isNull) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; foreach (DataColumn cel in execlDt.Columns) { lstKeys.Add(new string[] { cel.ColumnName, execlDt.Rows[i][cel].ToString() }); } heatStaIndex = i; continue; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex > 0 && endIndex <= 0 && lstKeys.Count > 0) { // 如果表头中的数据存在表体，则初始化对象 #region 初始化表头数据 // 查询表头的数据中是否在表体 var tlstHt = imBaseHtLst.Where(v => v.F_DATATYPE == "表体"); if (tlstHt != null) { if (tlstHt.Count() > 0) { foreach (var dt in tlstHt) { var cel = lstKeys.Where(v => v[1] == dt.EXECLCELLNAME).FirstOrDefault(); if (cel != null) { var t = execlDt.Rows[i][cel[0]].ToString(); var val = RegexNot(t); if (dt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, dt.DBCELLNAME, val); } else SetProperties(order, dt.DBCELLNAME, val); } } } } #endregion } if (staIndex <= 0 || endIndex > 0) { for (int l = 0; l < execlDt.Columns.Count; l++) { celName = execlDt.Rows[i][l].ToString(); if (!string.IsNullOrEmpty(celName)) { var imp = imBaseHtLst.Where(v => celName.Contains(v.EXECLCELLNAME) && v.F_DATATYPE == "表头").FirstOrDefault(); if (imp != null) { var t = execlDt.Rows[i][l + 1].ToString(); celVal = RegexNot(t); if (imp.F_ISNULL == 1) { if (string.IsNullOrEmpty(celVal)) { SetProperties(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, imp.DBCELLNAME, celVal); } else SetProperties(order, imp.DBCELLNAME, celVal); } } } } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); lstOrder.Add(order); staIndex = 0; endIndex = 0; #region 初始化表体数据 for (int i = 0; i < execlDt.Rows.Count; i++) { isNull = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { isNull = true; break; } } if (isNull) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; continue; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex > 0 && endIndex <= 0 && lstKeys.Count > 0) { if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单据编号值不能为空"); BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; item.F_LOCKSTATUS = (int)ELOCKSTATUS.NoLockStatus; foreach (var cel in lstKeys) { var impt = imBaseDtLst.Where(v => v.EXECLCELLNAME == cel[1]).FirstOrDefault(); if (impt != null) { if (impt.DBCELLNAME.Equals("F_ORDERNO")) continue; var t = execlDt.Rows[i][cel[0]].ToString(); var val = RegexNot(t); // 物料状态 if (impt.DBCELLNAME.Equals("F_MATSTATENUM")) { var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First(); if (matstate == null) SetProperties(item, "F_DEFINE10", impt.EXECLCELLNAME + ":选中的值《" + val + "》不存在"); else SetProperties(item, impt.DBCELLNAME, matstate.F_NUM.ToString()); continue; } if (impt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", impt.EXECLCELLNAME + ":不能为空"); else SetProperties(item, impt.DBCELLNAME, val); } else SetProperties(item, impt.DBCELLNAME, val); } } lstItem.Add(item); } } } #endregion } #endregion } else { // 全表体混合模式，关键字段 [单号] ,在 EXECL 中必须用 [单号字样表示] if (!execlDt.Columns.Contains("单号")) throw SysExCore.ThrowFailException("EXECL表体区混合模式下， EXECL 文件中必须包含 [ 单号 ] 关键字"); if (imBaseHtLst.Where(v => v.EXECLCELLNAME == "单号").FirstOrDefault() == null) throw SysExCore.ThrowFailException("表头字段配置参数列表中必须包含 [ 单号 ] 关系列"); if (imBaseDtLst.Where(v => v.EXECLCELLNAME == "单号").FirstOrDefault() == null) throw SysExCore.ThrowFailException("表体字段配置参数列表中必须包含 [ 单号 ] 关系列"); #region EXECL 数据库表头 主、子 表数据，混合在 EXECL 表体区 // 临时表体源 DataTable temDt = new DataTable(); // 判断是否指定了开始行的关键词 if (!string.IsNullOrEmpty(imptDt.F_STAKEY)) { // 如果指定了关键词，则将EXECL中的表体区数据抠出。 #region 将EXECL表体区的数据抠出来放在 temDt // 保存源列名和现列名的关系 List lstTitel = new List(); for (int i = 0; i < execlDt.Rows.Count; i++) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex == i) { foreach (DataColumn cel in execlDt.Columns) { if (!execlDt.Rows[staIndex][cel].ToString().Contains("提交结果：")) { if (string.IsNullOrEmpty(execlDt.Rows[staIndex][cel].ToString().Trim())) throw SysExCore.ThrowFailException("表体区的表头不能为空"); string[] s = new string[] { cel.ColumnName, execlDt.Rows[staIndex][cel].ToString().Trim() }; lstTitel.Add(s); temDt.Columns.Add(execlDt.Rows[staIndex][cel].ToString().Trim()); } } } if (i > staIndex && lstTitel.Count > 0) { if (endIndex <= 0) { var di = temDt.Rows.Count; temDt.Rows.Add(1); foreach (var ls in lstTitel) { temDt.Rows[di][ls[1]] = execlDt.Rows[i][ls[0]]; } } } } #endregion } else { // 如果未制定关键词，则按照源 temDt = execlDt; } #region 转换对象 var F_NOLst = from t in temDt.AsEnumerable() group t by new { F_NO = t.Field("单号"), } into m select new { F_NO = m.Key.F_NO }; foreach (var F_NO in F_NOLst) { if (string.IsNullOrEmpty(F_NO.F_NO)) continue; var F_NORow = temDt.Select("单号='" + F_NO.F_NO + "'"); if (F_NORow.Length <= 0) throw SysExCore.ThrowFailException("未找到数据信息"); #region 初始化表头 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_TYPENUM = busTypeVal; order.F_STATENUM = 0; foreach (var imbase in imBaseHtLst) { if (temDt.Columns.Contains(imbase.EXECLCELLNAME)) { var t = F_NORow[0][imbase.EXECLCELLNAME].ToString().Trim(); var val = RegexNot(t); if (imbase.DBCELLNAME == "F_DEFINE02") { } if (imbase.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, imbase.DBCELLNAME, val); } else SetProperties(order, imbase.DBCELLNAME, val); } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); //if (order.F_TYPENUM == 5 || order.F_TYPENUM == 6) //{ // order.F_NO = order.F_TYPENUM.ToString().PadLeft(2, '0') + "_" + order.F_NO; //} lstOrder.Add(order); #region 初始化表体 foreach (DataRow row in F_NORow) { BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_OUTLINETYPE = "0"; item.F_LOCKSTATUS = 0; if (busTypeVal == 17) { item.F_MATSTATENUM = 1; } foreach (var imbase in imBaseDtLst) { var t = row[imbase.EXECLCELLNAME].ToString(); var val = RegexNot(t); // 物料状态 if (imbase.DBCELLNAME.Equals("F_MATSTATENUM")) { var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First(); if (matstate == null) SetProperties(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":选中的值《" + val + "》不存在"); else SetProperties(item, imbase.DBCELLNAME, matstate.F_NUM.ToString()); continue; } if (imbase.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":不能为空"); else SetProperties(item, imbase.DBCELLNAME, val); } else { SetProperties(item, imbase.DBCELLNAME, val); } } item.F_ORDERNO = order.F_NO; lstItem.Add(item); } #endregion } #endregion #endregion } var vvv = lstOrder; var bbb = lstItem; #region 数据库操作 if (lstItem.Count <= 0) throw SysExCore.ThrowFailException("表体数据未空，无法导入"); if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() == null && lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() == null) { try { //OrderCore core = new OrderCore(); //LoginUserInfo LoginUser = LoginBLLCore.GetLoginUser(); //var jsonAdd = core.SaveOrder(LoginUser, lstOrder, lstItem); //if (jsonAdd.type == 0) // throw SysExCore.ThrowFailException(jsonAdd.message); //else if (jsonAdd.type == 1) //{ // lstOrder = jsonAdd.lstOrder; // lstItem = jsonAdd.lstItem; //} } catch (Exception ex) { throw SysExCore.ThrowFailException(ex.Message); //lstOrder.ForEach(w => w.F_DEFINE15 = ex.Message); //lstItem.ForEach(w => w.F_DEFINE10 = ex.Message); } } #endregion if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() != null || lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() != null) { #region 写入异常结果 DataTable dtSouse = new DataTable(); dtSouse.Columns.Add("数据类型"); dtSouse.Columns.Add("数据值"); dtSouse.Columns.Add("原因"); var heat = lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).ToArray(); if (heat.Count() > 0) { foreach (var item in heat) { dtSouse.Rows.Add("表头", item.F_NO, item.F_DEFINE15); } } var data = lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).ToArray(); if (data.Count() > 0) { foreach (var item in data) { dtSouse.Rows.Add("表体", item.F_MATNO, item.F_DEFINE10); } } if (dtSouse.Rows.Count > 0) { ExcelConfig cof = new ExcelConfig(); //cof.FileName = @"D:\Project\科勒\EXECL\ERR222.xls"; // 文件名 只能是 xls格式 cof.Title = "导入时异常信息"; // 文件名 cof.TitlePoint = 18; // 标题字号 cof.HeadPoint = 14;// 列头字号 cof.IsAllSizeColumn = true; // 列宽自适应 cof.ColumnEntity = new List() { new ColumnModel(){Column = "数据类型",ExcelColumn = "数据类型"}, new ColumnModel(){Column = "数据值",ExcelColumn = "数据值"}, new ColumnModel(){Column = "原因",ExcelColumn = "原因",Background=Color.Red}, }; json.Type = 1; json.Message = "导入失败"; json.dtSou = dtSouse; json.ExeclCfg = cof; //ExcelHelper.ExcelExport(dtSouse, cof); } else { json.Type = 2; json.Message = "导入成功"; } #endregion } else { json.Type = 2; json.Message = "导入成功"; } } catch (Exception ex) { json.Type = 0; json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } /// /// 收货单格式验证 /// /// /// /// /// public JsonExecl ImportOrderDataCheck(DataTable dtExeclSouse, int busTypeVal, string loginName) { if (busTypeVal != -5) throw SysExCore.ThrowFailException("只能验证收货单"); busTypeVal = 7; JsonExecl json = new JsonExecl(); json.Type = 0; // busType SqlSugarClient sugar = SysDbCore.GetDbCtx(); try { if (dtExeclSouse == null) throw SysExCore.ThrowFailException("数据源不能为null"); if (dtExeclSouse.Rows.Count <= 0) throw SysExCore.ThrowFailException("数据源数据行数必须大于0"); // 获取源数据 DataTable execlDt = dtExeclSouse;//ExcelHelper.ExcelImport(filePath, 0, 0); EOrderType otype = (EOrderType)busTypeVal; List importLst = null; #region 获取数据库参数配置 importLst = sugar.Queryable().Where(v => new string[] { "9", "10" }.Contains(v.F_NO)).ToList(); // 获取参数配置 if (importLst.Count <= 0) throw SysExCore.ThrowFailException("未找到配置参数"); var imptHt = importLst.Where(v => v.STRUCTURE == "表头").FirstOrDefault(); if (imptHt == null) throw SysExCore.ThrowFailException("单据导入必须有表头参数定义"); // 获取表体参数 var imptDt = importLst.Where(v => v.STRUCTURE == "表体").FirstOrDefault(); if (imptDt == null) throw SysExCore.ThrowFailException("单据导入必须有表体参数定义"); var imBaseHtLst = sugar.Queryable().Where(v => v.F_NO == imptHt.F_NO).ToList(); var imBaseDtLst = sugar.Queryable().Where(v => v.F_NO == imptDt.F_NO).ToList(); if (imBaseHtLst.Count <= 0 || imBaseDtLst.Count <= 0) throw SysExCore.ThrowFailException("未找到字段参数的配置"); #endregion // string busType = busTypeVal;//imptHt.BUSINESSTYPE; // 表体区开始索引 int staIndex = 0; // 表体区结束索引 int endIndex = 0; int heatStaIndex = 0; Dictionary dicKeys = new Dictionary(); List lstOrder = new List(); List lstItem = new List(); // 判断表头数据是否在 EXECL 的表头数据中 if (imBaseHtLst.Where(v => v.F_DATATYPE == "表头").FirstOrDefault() != null) { #region EXECL 表头表体数据混合模式 DataTable dtHead = new DataTable(); DataTable dtData = new DataTable(); List lstTitel = new List(); // 如果表头关键词为空，则从 0 索引开始查询 if (string.IsNullOrEmpty(imptDt.F_STAKEY)) { if (string.IsNullOrEmpty(imptDt.F_ENDKEY)) throw SysExCore.ThrowFailException("EXECL 表体区，表头区混合模式下必须是 { 表体开始关键词为空，表体结束关键词不能为空 } "); #region 初始化表头数据 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_STATENUM = 0; order.F_TYPENUM = busTypeVal; foreach (DataColumn cel in execlDt.Columns) { dtHead.Columns.Add(cel.ColumnName); } bool IsEmtry = false; var tlstHt = imBaseHtLst.Where(v => v.F_DATATYPE == "表体").ToList(); if (tlstHt.Count > 0) { #region 初始化表头对象，先从表体区找到表头的值 foreach (var ht in tlstHt) { var t = execlDt.Rows[0][ht.EXECLCELLNAME].ToString().Trim(); var val = RegexNot(t); if (ht.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", order.F_DEFINE15 + "|" + ht.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, ht.DBCELLNAME, val); } else SetProperties(order, ht.DBCELLNAME, val); } #endregion } #region 初始化表头对象，在找到表头区的值，目标是为了生成单号给表体。 for (int i = 0; i < execlDt.Rows.Count; i++) { IsEmtry = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { IsEmtry = true; break; } } // 如果有有一列不为空，则正常走逻辑 if (IsEmtry) { if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (endIndex > 0) { heatStaIndex = i; for (int l = 0; l < execlDt.Columns.Count; l++) { var ht = imBaseHtLst.Where(v => v.F_DATATYPE == "表头" && v.EXECLCELLNAME == execlDt.Rows[i][l].ToString()).FirstOrDefault(); if (ht != null) { var t = execlDt.Rows[i][l + 1].ToString(); var val = RegexNot(t); if (ht.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(order, "F_DEFINE15", order.F_DEFINE15 + "|" + ht.EXECLCELLNAME + ":不能为空"); else SetProperties(order, ht.DBCELLNAME, val); } else SetProperties(order, ht.DBCELLNAME, val); } } } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("EXECL 文件中未找到单号"); if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); lstOrder.Add(order); #endregion #region 初始化表体数据 endIndex = 0; for (int i = 0; i < execlDt.Rows.Count; i++) { IsEmtry = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { IsEmtry = true; break; } } // 如果有有一列不为空，则正常走逻辑 if (IsEmtry) { #region 非空的数据添加到临时 DataTable if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (endIndex <= 0) { #region 表体对象初始化 BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; item.F_LOCKSTATUS = (int)ELOCKSTATUS.NoLockStatus; for (int l = 0; l < execlDt.Columns.Count; l++) { var dt = imBaseDtLst.Where(v => v.EXECLCELLNAME == execlDt.Columns[l].ToString()).FirstOrDefault(); if (dt != null) { if (dt.DBCELLNAME.Equals("F_ORDERNO")) continue; var t = execlDt.Rows[i][l].ToString(); var val = RegexNot(t); // 物料状态 if (dt.DBCELLNAME.Equals("F_MATSTATENUM")) { var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First(); if (matstate == null) SetProperties(item, "F_DEFINE10", item.F_DEFINE10 + "|" + dt.EXECLCELLNAME + ":选中的值《" + val + "》不存在"); else SetProperties(item, dt.DBCELLNAME, matstate.F_NUM.ToString()); continue; } if (dt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", item.F_DEFINE10 + "|" + dt.EXECLCELLNAME + ":不能为空"); else SetProperties(item, dt.DBCELLNAME, val); } else SetProperties(item, dt.DBCELLNAME, val); } } lstItem.Add(item); #endregion } #endregion } } #endregion } else { // 表体开始不能为空，表体结束不能为空 if (string.IsNullOrEmpty(imptDt.F_ENDKEY) || string.IsNullOrEmpty(imptDt.F_STAKEY)) throw SysExCore.ThrowFailException("EXECL表头区，表体区，表尾区混合模式下。{ 表体的开始关键词和表体的结束关键词不能为空 }"); #region 初始化表头 BILL_ORDERGRP order = new BILL_ORDERGRP(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_STATENUM = 0; order.F_TYPENUM = busTypeVal; order.F_DEFINE15 = ""; var celName = string.Empty; var celVal = string.Empty; // 保存源列名和表体列名的关系 // 0：源列名 // 1：表体列名 List lstKeys = new List(); bool isNull = false; for (int i = 0; i < execlDt.Rows.Count; i++) { isNull = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { isNull = true; break; } } if (isNull) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; foreach (DataColumn cel in execlDt.Columns) { lstKeys.Add(new string[] { cel.ColumnName, execlDt.Rows[i][cel].ToString() }); } heatStaIndex = i; continue; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex > 0 && endIndex <= 0 && lstKeys.Count > 0) { // 如果表头中的数据存在表体，则初始化对象 #region 初始化表头数据 // 查询表头的数据中是否在表体 var tlstHt = imBaseHtLst.Where(v => v.F_DATATYPE == "表体"); if (tlstHt != null) { if (tlstHt.Count() > 0) { foreach (var dt in tlstHt) { var cel = lstKeys.Where(v => v[1] == dt.EXECLCELLNAME).FirstOrDefault(); if (cel != null) { var t = execlDt.Rows[i][cel[0]].ToString(); var val = RegexNot(t); if (dt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE15", order.F_DEFINE15 + "|" + dt.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, dt.DBCELLNAME, val); } else SetProperties(order, dt.DBCELLNAME, val); } } } } #endregion } if (staIndex <= 0 || endIndex > 0) { for (int l = 0; l < execlDt.Columns.Count; l++) { celName = execlDt.Rows[i][l].ToString(); if (!string.IsNullOrEmpty(celName)) { var imp = imBaseHtLst.Where(v => celName.Contains(v.EXECLCELLNAME) && v.F_DATATYPE == "表头").FirstOrDefault(); if (imp != null) { var t = execlDt.Rows[i][l + 1].ToString(); celVal = RegexNot(t); if (imp.F_ISNULL == 1) { if (string.IsNullOrEmpty(celVal)) { SetProperties(order, "F_DEFINE15", order.F_DEFINE15 + "|" + imp.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, imp.DBCELLNAME, celVal); } else SetProperties(order, imp.DBCELLNAME, celVal); } } } } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单号不能为空"); lstOrder.Add(order); staIndex = 0; endIndex = 0; #region 初始化表体数据 for (int i = 0; i < execlDt.Rows.Count; i++) { isNull = false; foreach (DataColumn cel in execlDt.Columns) { if (!string.IsNullOrEmpty(execlDt.Rows[i][cel].ToString())) { isNull = true; break; } } if (isNull) { if (execlDt.Rows[i][0].ToString() == imptDt.F_STAKEY) { staIndex = i; continue; } if (execlDt.Rows[i][0].ToString() == imptDt.F_ENDKEY) { endIndex = i; } if (staIndex > 0 && endIndex <= 0 && lstKeys.Count > 0) { if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("单据编号值不能为空"); BILL_ORDERITEM item = new BILL_ORDERITEM(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; item.F_ORDERNO = order.F_NO; item.F_DEFINE10 = ""; item.F_LOCKSTATUS = (int)ELOCKSTATUS.NoLockStatus; foreach (var cel in lstKeys) { var impt = imBaseDtLst.Where(v => v.EXECLCELLNAME == cel[1]).FirstOrDefault(); if (impt != null) { if (impt.DBCELLNAME.Equals("F_ORDERNO")) continue; var t = execlDt.Rows[i][cel[0]].ToString(); var val = RegexNot(t); // 物料状态 if (impt.DBCELLNAME.Equals("F_MATSTATENUM")) { var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First(); if (matstate == null) SetProperties(item, "F_DEFINE10", item.F_DEFINE10 + "|" + impt.EXECLCELLNAME + ":选中的值《" + val + "》不存在"); else SetProperties(item, impt.DBCELLNAME, matstate.F_NUM.ToString()); continue; } if (impt.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) SetProperties(item, "F_DEFINE10", item.F_DEFINE10 + "|" + impt.EXECLCELLNAME + ":不能为空"); else SetProperties(item, impt.DBCELLNAME, val); } else SetProperties(item, impt.DBCELLNAME, val); } } //if (order.F_TYPENUM == 7) //{ // if (item.F_FROMORDERNO.Split('_').Count() <= 1) // { // item.F_FROMORDERNO = "SAP05" + "_" + item.F_FROMORDERNO; // } //} //if (order.F_TYPENUM == 37) //{ // if (item.F_FROMORDERNO.Split('_').Count() <= 1) // { // item.F_FROMORDERNO = "SAP06" + "_" + item.F_FROMORDERNO; // } //} lstItem.Add(item); } } } #endregion } #endregion } else { throw SysExCore.ThrowFailException("数据库配置异常"); } var vvv = lstOrder; var bbb = lstItem; #region 数据库操作 if (lstItem.Count <= 0) throw SysExCore.ThrowFailException("表体数据未空，无法导入"); foreach (BILL_ORDERGRP order in lstOrder) { #region 单号 if (string.IsNullOrEmpty(order.F_NO)) { order.F_DEFINE15 += "|单号不能为空"; } #endregion #region 供应商 if (!string.IsNullOrEmpty(order.F_SUPPLIERNO)) { var sup = sugar.Queryable().Where(v => v.F_NO == order.F_SUPPLIERNO).First(); if (sup == null) { order.F_DEFINE15 += "|供应商不存在"; } } #endregion #region 客户 if (!string.IsNullOrEmpty(order.F_CUSTOMERNO)) { var cus = sugar.Queryable().Where(v => v.F_NO == order.F_CUSTOMERNO).First(); if (cus == null) { order.F_DEFINE15 += "|客户不存在"; } } #endregion #region 单据类型 if (order.F_TYPENUM <= 0 || order.F_TYPENUM >= 39) { order.F_DEFINE15 += "|不是可识别的单据类型"; } #endregion #region 是否有表体数据 var items = lstItem.Where(v => v.F_ORDERNO == order.F_NO).ToArray(); if (items.Count() <= 0) { order.F_DEFINE15 += string.Format("|{0}表体数据不能为空", order.F_NO); } #endregion #region 表体数据验证 foreach (var item in items) { if (item.F_PROSTARTIME == null) item.F_PROSTARTIME = DateTime.Now; if (item.F_PROENDTIME == null) item.F_PROENDTIME = DateTime.Now; #region 物料状态验证 // PO单，PO收货单，INB单，INB收货单，负入库，委外退料：质检中 if (new int[] { 5, 6, 7, 37, 18, 24 }.Contains(order.F_TYPENUM)) { item.F_MATSTATENUM = 6; if (order.F_TYPENUM == 18) { #region 负入库 if (string.IsNullOrEmpty(item.F_DEFINE16)) { item.F_DEFINE10 += string.Format("|单据[{0}],是否质检不能为空", item.F_ORDERNO); } if (item.F_DEFINE16 == "免检-1") item.F_MATSTATENUM = 1; #endregion } if (new int[] { 7, 37 }.Contains(order.F_TYPENUM)) { item.F_SUPPLIERNO = order.F_SUPPLIERNO; } } // 生产退料单,非生产性领料，委外发料,原材料报检单，质检结果单（判断目标状态）,自选状态 else if (new int[] { 14, 17, 23, 9, 12 }.Contains(order.F_TYPENUM)) { if (item.F_MATSTATENUM <= 0) { item.F_DEFINE10 += string.Format("|单号[{0}],物料[{1}]状态不能为0", item.F_ORDERNO, item.F_MATNO); } var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && v.F_NUM == item.F_MATSTATENUM).First(); if (matstate == null) { item.F_DEFINE10 += string.Format("|冻结指令单[{0}]，[{1}]不是可识别的物料状态", item.F_ORDERNO, item.F_MATNO); } if (order.F_TYPENUM == 17) { if (matstate.F_NUM != 1) { item.F_DEFINE10 += string.Format("|非生产性领料，物料[{0}]状态只能为合格状态.[{1}]", item.F_ORDERNO, matstate.F_NAME); } } } // 生产计划，生产领料，可供容器退料：合格 else if (new int[] { 4, 13, 15 }.Contains(order.F_TYPENUM)) { item.F_MATSTATENUM = 1; if (order.F_TYPENUM == 4) { if (string.IsNullOrEmpty(item.F_DEFINE15)) { item.F_DEFINE10 += string.Format("|单据[{0}],优先级不能为空", item.F_ORDERNO); } try { int.Parse(item.F_DEFINE15); } catch (Exception) { item.F_DEFINE10 += string.Format("|单据[{0}],优先级必须为数字", item.F_ORDERNO); } } } // RO退货单,PO退货单：不合格 else if (new int[] { 16, 38 }.Contains(order.F_TYPENUM)) { if (item.F_MATSTATENUM <= 0) { item.F_DEFINE10 += string.Format("|物料状态不能为空", item.F_ORDERNO, item.F_MATNO); } // item.F_MATSTATENUM = 3; } // ，冻结指令单,目标状态为：冻结 else if (order.F_TYPENUM == 8) { item.F_MATSTATENUM = 4; if (string.IsNullOrEmpty(item.F_DEFINE19)) { item.F_DEFINE10 += string.Format("|单据[{0}],目标货位不能为空", item.F_ORDERNO); } } else { } #endregion #region 冻结指令单,质检结果单原原状态判断 // 判断，冻结指令单，质检结果单 的原状态是否合法 if (new int[] { 8, 12 }.Contains(order.F_TYPENUM)) { if (!string.IsNullOrEmpty(item.F_DEFINE09)) { //int t = int.Parse(item.F_DEFINE09.Substring(0, 1)); var matstate = sugar.Queryable().Where(v => v.F_PNO == "EMatState" && (v.F_NUM.ToString() == item.F_DEFINE09.ToString() || v.F_NAME == item.F_DEFINE09.ToString())).First(); if (matstate == null) { item.F_DEFINE10 += string.Format("|单据[{0}]，[{1}]不是可识别的原状态", item.F_ORDERNO, item.F_MATNO); } item.F_DEFINE09 = matstate.F_NUM.ToString(); } else { item.F_DEFINE10 += string.Format("|质检结果单[{0}]，[{1}]原状态不允许为空", item.F_ORDERNO, item.F_MATNO); } } #endregion if (!new int[] { 5, 6 }.Contains(order.F_TYPENUM)) { var exists = items.Where(v => v.F_LGORT == item.F_LGORT && v.F_FROMORDERNO == item.F_FROMORDERNO && v.F_MATNO == item.F_MATNO && v.F_BATCHNO == item.F_BATCHNO && v.F_GEAR == item.F_GEAR && v.F_VERSION == item.F_VERSION).ToArray(); if (exists.Count() > 1) { item.F_DEFINE10 += string.Format("|同一个单号[{0}]中不允许有重复物料[{1}]信息", item.F_ORDERNO, item.F_MATNO); } } BILL_ORDERITEM oldItem = null; if (item.F_ORDERNO.Split('_').Count() <= 1) { //if (new int[] { 5, 6 }.Contains(order.F_TYPENUM)) item.F_ORDERNO = order.F_TYPENUM.ToString().PadLeft(2, '0') + "_" + item.F_ORDERNO; } if (!string.IsNullOrEmpty(item.F_FROMORDERNO)) { if (new int[] { 7, 37 }.Contains(order.F_TYPENUM)) { if (item.F_FROMORDERNO.Split('_').Count() <= 1) item.F_FROMORDERNO = (order.F_TYPENUM == 7 ? "05" : "06") + "_" + item.F_FROMORDERNO; } } // 获取库存中是否存在 oldItem = sugar.Queryable().Where(v => v.F_LGORT == item.F_LGORT && v.F_FROMORDERNO == item.F_FROMORDERNO && v.F_ORDERNO == item.F_ORDERNO && v.F_MATNO == item.F_MATNO && v.F_BATCHNO == item.F_BATCHNO && v.F_GEAR == item.F_GEAR && v.F_VERSION == item.F_VERSION).First();// #region 物料信息 if (string.IsNullOrEmpty(item.F_MATNO)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":料号不能为空"; } if (string.IsNullOrEmpty(item.F_BATCHNO)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":批次不能为空"; } if (string.IsNullOrEmpty(item.F_VERSION)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":版本号不能为空"; } var mat = sugar.Queryable().Where(v => v.F_NO == item.F_MATNO).First(); if (mat == null) { item.F_DEFINE10 += "|" + item.F_MATNO + ":未找到基础资料"; } else { if (string.IsNullOrEmpty(mat.F_WAREHOUSENO)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":基础信息默认仓库为空"; } if (string.IsNullOrEmpty(mat.F_OFFWAREA)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":基础信息默认区域为空"; } if (string.IsNullOrEmpty(mat.F_QCTYPECODE)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":质检标示不完全"; } else if (int.Parse(mat.F_QCTYPECODE) <= 0 || int.Parse(mat.F_QCTYPECODE) >= 4) { item.F_DEFINE10 += "|" + item.F_MATNO + ":质检标示不是可识别的标识"; } item.F_MATNAME = mat.F_NAME; } BILL_ORDERGRP PO_Order = null; #endregion #region 非生性领料 负入库成本中心判断 if (order.F_TYPENUM == (int)EOrderType.NoPro_Back_Mat || order.F_TYPENUM == (int)EOrderType.NoPro_Res_Mat) { if (string.IsNullOrEmpty(item.F_COST)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":成本中心不能为空"; } } #endregion #region 产线信息 if (!string.IsNullOrEmpty(item.F_PROLINENO)) { var t = item.F_PROLINENO.Trim(); if (!string.IsNullOrEmpty(t)) { BASE_DATA proline = null; var prolineNo = item.F_PROLINENO.Substring(0, 1); proline = sugar.Queryable().Where(v => v.F_PNO == "PROLINENO" && (v.F_VALUE == prolineNo || v.F_NO == item.F_PROLINENO || v.F_NAME == prolineNo)).First(); if (proline == null) { item.F_DEFINE10 += "|" + item.F_MATNO + ":产线编码不存在"; } item.F_PROLINENO = proline.F_NO; item.F_PROLINENAME = proline.F_NAME; } } #endregion #region 包装方式 if (!string.IsNullOrEmpty(item.F_PACKMETHOD)) { var t = item.F_PACKMETHOD.Trim(); if (!string.IsNullOrEmpty(t)) { SYS_DATA proline = null; int va = 0; try { var prolineNo = item.F_PACKMETHOD.Substring(0, 2); va = int.Parse(prolineNo); } catch (Exception ex) { item.F_DEFINE10 += "|" + item.F_MATNO + ":包装方式格式不正确"; } proline = sugar.Queryable().Where(v => v.F_PNO == "EPackType" && (v.F_NAME == t || v.F_NUM == va)).First(); if (proline == null) { item.F_DEFINE10 += "|" + item.F_MATNO + ":包装方式不存在"; } item.F_PACKMETHOD = proline.F_NAME; } } #endregion #region 生产计划单特殊判断 else if (order.F_TYPENUM == (int)EOrderType.Pro_Plan) { if (string.IsNullOrEmpty(item.F_PROLINENO)) { item.F_DEFINE10 += "|" + item.F_MATNO + ":产线编号不能为空"; } var plan = sugar.Queryable().Where(v => v.F_PNO == "PROLINENO" && (v.F_NO == item.F_PROLINENO || v.F_NAME == item.F_PROLINENO || v.F_VALUE == item.F_PROLINENO)).First(); if (plan == null) { item.F_DEFINE10 += "|" + item.F_MATNO + ":产线编号不存在"; } // 将产线信息重新写入 item.F_PROLINENO = plan.F_NO; item.F_PROLINENAME = plan.F_NAME; } #endregion #region 其它单据 else { if (oldItem != null) { if (item.F_FQTY <= oldItem.F_AQTY) { item.F_DEFINE10 += "|" + item.F_MATNO + ":修改数量不能小于已经操作的数量"; } } } #endregion #region 单位转换 { ConvUnitEntity entity = null; if (string.IsNullOrEmpty(item.F_MATNO) || string.IsNullOrEmpty(item.F_UNITNO)) item.F_DEFINE10 += "|料号或者转换单位不能为空"; if (item.F_FQTY <= 0) item.F_DEFINE10 += "|参数值不能小于0"; if (mat == null) continue; if (string.IsNullOrEmpty(mat.F_BASEUNITNO)) item.F_DEFINE10 += "|物料基本单位未维护"; if (string.IsNullOrEmpty(mat.F_DEFINE04)) item.F_DEFINE10 += "|最小包装单位未维护"; var convUnit = sugar.Queryable().Where(v => v.F_MATNO == item.F_MATNO && v.F_CONVUNIT == item.F_UNITNO).First(); if (convUnit == null) item.F_DEFINE10 += "|" + item.F_MATNO + "：未找到基本转换关系[" + item.F_UNITNO + "-->" + mat.F_BASEUNITNO + "]"; else { if (convUnit.F_CONVERSION <= 0) item.F_DEFINE10 += "|基本转换率不能小于等于0"; } if (mat.F_BASEUNITNO != mat.F_DEFINE04) { var minUnit = sugar.Queryable().Where(v => v.F_MATNO == item.F_MATNO && v.F_CONVUNIT == mat.F_DEFINE04).First(); if (minUnit == null) item.F_DEFINE10 += "|" + item.F_MATNO + "：未找到包装转换关系[" + mat.F_BASEUNITNO + "-->" + mat.F_DEFINE04 + "]"; if (minUnit.F_CONVERSION <= 0) item.F_DEFINE10 += "|包装转换率不能小于等于0"; } } #endregion } #endregion } #endregion if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() != null || lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() != null) { #region 写入异常结果 DataTable dtSouse = new DataTable(); dtSouse.Columns.Add("数据类型"); dtSouse.Columns.Add("数据值"); dtSouse.Columns.Add("原因"); var heat = lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).ToArray(); if (heat.Count() > 0) { foreach (var item in heat) { dtSouse.Rows.Add("表头", item.F_NO, item.F_DEFINE15); } } var data = lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).ToArray(); if (data.Count() > 0) { foreach (var item in data) { dtSouse.Rows.Add("表体", item.F_MATNO, item.F_DEFINE10); } } if (dtSouse.Rows.Count > 0) { ExcelConfig cof = new ExcelConfig(); //cof.FileName = @"D:\Project\科勒\EXECL\ERR222.xls"; // 文件名 只能是 xls格式 cof.Title = "导入时异常信息"; // 文件名 cof.TitlePoint = 18; // 标题字号 cof.HeadPoint = 14;// 列头字号 cof.IsAllSizeColumn = true; // 列宽自适应 cof.ColumnEntity = new List() { new ColumnModel(){Column = "数据类型",ExcelColumn = "数据类型"}, new ColumnModel(){Column = "数据值",ExcelColumn = "数据值"}, new ColumnModel(){Column = "原因",ExcelColumn = "原因",Background=Color.Red}, }; json.Type = 1; json.Message = "导入失败"; json.dtSou = dtSouse; json.ExeclCfg = cof; //ExcelHelper.ExcelExport(dtSouse, cof); } else { json.Type = 2; json.Message = "导入成功"; } #endregion } else { json.Type = 2; json.Message = "导入成功"; } } catch (Exception ex) { json.Type = 0; json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } /// /// 盘点导入 /// /// /// /// public JsonExecl ImportCheckOrder(DataTable dtExeclSouse, int busTypeVal, string loginName) { JsonExecl json = new JsonExecl(); json.Type = 0; // busType SqlSugarClient sugar = SysDbCore.GetDbCtx(); try { if (dtExeclSouse == null) throw SysExCore.ThrowFailException("数据源不能为null"); if (dtExeclSouse.Rows.Count <= 0) throw SysExCore.ThrowFailException("数据源数据行数必须大于0"); // 获取源数据 DataTable execlDt = dtExeclSouse;//ExcelHelper.ExcelImport(filePath, 0, 0); EOrderType otype = (EOrderType)busTypeVal; if (otype != EOrderType.Inventory) throw new Exception("类型不是盘点类型"); #region 获取数据库参数配置 // 获取参数配置 var importLst = sugar.Queryable().Where(v => v.BUSINSERTVAL == busTypeVal).ToList(); var imptHt = importLst.Where(v => v.STRUCTURE == "表头").FirstOrDefault(); if (imptHt == null) throw SysExCore.ThrowFailException("单据导入必须有表头参数定义"); // 获取表体参数 var imptDt = importLst.Where(v => v.STRUCTURE == "表体").FirstOrDefault(); if (imptDt == null) throw SysExCore.ThrowFailException("单据导入必须有表体参数定义"); var imBaseHtLst = sugar.Queryable().Where(v => v.F_NO == imptHt.F_NO).ToList(); var imBaseDtLst = sugar.Queryable().Where(v => v.F_NO == imptDt.F_NO).ToList(); if (imBaseHtLst.Count <= 0 || imBaseDtLst.Count <= 0) throw SysExCore.ThrowFailException("未找到字段参数的配置"); #endregion List lstOrder = new List(); List lstItem = new List(); // 全表体混合模式，关键字段 [单号] ,在 EXECL 中必须用 [单号字样表示] if (!execlDt.Columns.Contains("盘点单号")) throw SysExCore.ThrowFailException("EXECL表体区混合模式下， EXECL 文件中必须包含 [ 盘点单号 ] 关键字"); if (imBaseHtLst.Where(v => v.EXECLCELLNAME == "盘点单号").FirstOrDefault() == null) throw SysExCore.ThrowFailException("表头字段配置参数列表中必须包含 [ 盘点单号 ] 关系列"); if (imBaseDtLst.Where(v => v.EXECLCELLNAME == "盘点单号").FirstOrDefault() == null) throw SysExCore.ThrowFailException("表体字段配置参数列表中必须包含 [ 盘点单号 ] 关系列"); // 数据源 DataTable temDt = execlDt; #region 转换对象 var F_NOLst = from t in temDt.AsEnumerable() group t by new { F_NO = t.Field("盘点单号") } into m select new { F_NO = m.Key.F_NO }; foreach (var F_NO in F_NOLst) { if (string.IsNullOrEmpty(F_NO.F_NO)) continue; var F_NORow = temDt.Select("盘点单号='" + F_NO.F_NO + "'"); if (F_NORow.Length <= 0) throw SysExCore.ThrowFailException("未找到数据信息"); #region 初始化表头 BILL_ORDERGRPCHECK order = new BILL_ORDERGRPCHECK(); order.F_ISDELETE = 0; order.F_ISSTOP = 0; order.F_ADDUSERNO = loginName; order.F_EDITUSERNO = loginName; order.F_EDITTIME = DateTime.Now; order.F_ADDTIME = DateTime.Now; order.F_TYPENUM = busTypeVal; order.F_STATENUM = 0; order.F_CHECKTYPENUM = 2; // 盘点方案 -> 抽盘 foreach (var imbase in imBaseHtLst) { if (temDt.Columns.Contains(imbase.EXECLCELLNAME)) { var t = F_NORow[0][imbase.EXECLCELLNAME].ToString().Trim(); var val = RegexNot(t); if (imbase.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(order, "F_DEFINE14", val); SetProperties(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空"); } else SetProperties(order, imbase.DBCELLNAME, val); } else { SetProperties(order, imbase.DBCELLNAME, val); } } } #endregion if (string.IsNullOrEmpty(order.F_NO)) throw SysExCore.ThrowFailException("盘点单号不能为空"); //order.F_NO = order.F_TYPENUM.ToString().PadLeft(2, '0') + "_" + order.F_NO; lstOrder.Add(order); #region 初始化表体 foreach (DataRow row in F_NORow) { BILL_ORDERGRPCHECKWHERE item = new BILL_ORDERGRPCHECKWHERE(); item.F_ROWNO = Guid.NewGuid().ToString(); item.F_ISSTOP = 0; item.F_ISDELETE = 0; item.F_ADDTIME = DateTime.Now; item.F_EDITTIME = DateTime.Now; item.F_ADDUSERNO = loginName; item.F_EDITUSERNO = loginName; if (busTypeVal == 17) { item.F_MATSTATENUM = 1; } var wacode = string.Empty; foreach (var imbase in imBaseDtLst) { var t = row[imbase.EXECLCELLNAME].ToString(); var val = RegexNot(t); if (imbase.DBCELLNAME == "F_WAREHOUENO") { #region 仓库号 if (string.IsNullOrEmpty(val)) { SetProperties(item, "F_DEFINE09", val); SetProperties(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":不能为空"); continue; } var wahouse = sugar.Queryable().Where(v => v.F_NO == val).First(); if (wahouse == null) { SetProperties(item, "F_DEFINE09", val); SetProperties(item, "F_DEFINE10", "仓库编号不存在"); continue; } wacode = val; #endregion } if (imbase.DBCELLNAME == "F_WARENO") { #region 区域 if (!string.IsNullOrEmpty(val)) { var wahouse = sugar.Queryable().Where(v => v.F_NO == val && v.F_WAREHOUSENO == wacode).First(); if (wahouse == null) { SetProperties(item, "F_DEFINE09", val); SetProperties(item, "F_DEFINE10", "区域编号不存在"); continue; } } #endregion } if (imbase.DBCELLNAME == "F_WARECELLNO") { #region 货位 if (!string.IsNullOrEmpty(val)) { var wahouse = sugar.Queryable().Where(v => v.F_NO == val).First(); if (wahouse == null) { SetProperties(item, "F_DEFINE09", val); SetProperties(item, "F_DEFINE10", "货位编号不存在"); continue; } } #endregion } if (imbase.DBCELLNAME == "F_MATNO") { #region 物料编码 if (!string.IsNullOrEmpty(val)) { var wahouse = sugar.Queryable().Where(v => v.F_NO == val).First(); if (wahouse == null) { SetProperties(item, "F_DEFINE09", val); SetProperties(item, "F_DEFINE10", "物料编码不存在"); continue; } } #endregion } if (imbase.F_ISNULL == 1) { if (string.IsNullOrEmpty(val)) { SetProperties(item, "F_DEFINE09", val); SetProperties(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":不能为空"); } else SetProperties(item, imbase.DBCELLNAME, val); } else { SetProperties(item, imbase.DBCELLNAME, val); } } //item.F_FROMORDERNO = order.F_TYPENUM.ToString().PadLeft(2, '0') + "_" + item.F_FROMORDERNO; item.F_ORDERNO = order.F_NO; lstItem.Add(item); } #endregion } #endregion #region 数据库操作 if (lstItem.Count <= 0) throw SysExCore.ThrowFailException("表体数据未空，无法导入"); if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() == null && lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() == null) { try { int f = 0; sugar.BeginTran(); foreach (var order in lstOrder) { f = 0; var dbOrder = sugar.Queryable().Where(v => v.F_NO == order.F_NO).First(); if (dbOrder == null) { f = sugar.Insertable(order).ExecuteCommand(); if (f > 0) { f = sugar.Insertable(lstItem.Where(v => v.F_ORDERNO == order.F_NO).ToArray()).ExecuteCommand(); } } else { if (dbOrder.F_ISDELETE == 1 || dbOrder.F_ISSTOP == 1) throw new Exception("此单据已经停用或者已经被删除，无法导入"); if (order.F_STATENUM < 3) { foreach (var item in lstItem) { var dbItem = sugar.Queryable().Where(v => v.F_ORDERNO == order.F_NO && v.F_MATNO == item.F_MATNO).First(); if (dbItem == null) { f = sugar.Insertable(item).ExecuteCommand(); } else { // 如果WMS的操作数量大于本次修改的计划量，将不允许修改 if (dbItem.F_AQTY > item.F_FQTY) { item.F_DEFINE10 = "计划量不能小于实际量"; } else { dbItem.F_FQTY = item.F_FQTY; f = sugar.Updateable(dbItem).ExecuteCommand(); } } } } else { order.F_DEFINE15 = string.Format("单据状态：<{0}>操作失败", order.F_STATENUM); } } } sugar.CommitTran(); } catch (Exception ex) { sugar.RollbackTran(); lstOrder.ForEach(w => w.F_DEFINE15 = ex.Message); lstItem.ForEach(w => w.F_DEFINE10 = ex.Message); } } #endregion #region 返回结果 if (lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).FirstOrDefault() != null || lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).FirstOrDefault() != null) { DataTable dtSouse = new DataTable(); dtSouse.Columns.Add("数据类型"); dtSouse.Columns.Add("数据值"); dtSouse.Columns.Add("原因"); var heat = lstOrder.Where(v => !string.IsNullOrEmpty(v.F_DEFINE15)).ToArray(); if (heat.Count() > 0) { foreach (var item in heat) { dtSouse.Rows.Add("表头", item.F_DEFINE14, item.F_DEFINE15); } } var data = lstItem.Where(v => !string.IsNullOrEmpty(v.F_DEFINE10)).ToArray(); if (data.Count() > 0) { foreach (var item in data) { dtSouse.Rows.Add("表体", item.F_DEFINE09, item.F_DEFINE10); } } if (dtSouse.Rows.Count > 0) { ExcelConfig cof = new ExcelConfig(); //cof.FileName = @"D:\Project\科勒\EXECL\ERR222.xls"; // 文件名 只能是 xls格式 cof.Title = "导入时盘点异常信息"; // 文件名 cof.TitlePoint = 18; // 标题字号 cof.HeadPoint = 14;// 列头字号 cof.IsAllSizeColumn = true; // 列宽自适应 cof.ColumnEntity = new List() { new ColumnModel(){Column = "数据类型",ExcelColumn = "数据类型"}, new ColumnModel(){Column = "数据值",ExcelColumn = "数据值"}, new ColumnModel(){Column = "原因",ExcelColumn = "原因",Background=Color.Red}, }; json.Type = 1; json.Message = "导入失败"; json.dtSou = dtSouse; json.ExeclCfg = cof; //ExcelHelper.ExcelExport(dtSouse, cof); } else { json.Type = 2; json.Message = "导入成功"; } } else { json.Type = 2; json.Message = "导入成功"; } #endregion } catch (Exception ex) { json.Type = 0; json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } /// /// 转仓导入 /// /// /// /// public JsonExecl ImportTransferOrder(DataTable dtExeclSouse, int busTypeVal, string loginName) { JsonExecl json = new JsonExecl(); json.Type = 0; // busType SqlSugarClient sugar = SysDbCore.GetDbCtx(); try { if (dtExeclSouse == null) throw SysExCore.ThrowFailException("数据源不能为null"); if (dtExeclSouse.Rows.Count <= 0) throw SysExCore.ThrowFailException("数据源数据行数必须大于0"); // 获取源数据 DataTable execlDt = dtExeclSouse;//ExcelHelper.ExcelImport(filePath, 0, 0); #region 获取数据库参数配置 // 获取参数配置 var importLst = sugar.Queryable().Where(v => v.BUSINSERTVAL == busTypeVal).ToList(); var imptHt = importLst.Where(v => v.STRUCTURE == "表头").FirstOrDefault(); if (imptHt == null) throw SysExCore.ThrowFailException("单据导入必须有表头参数定义"); // 获取表体参数 var imptDt = importLst.Where(v => v.STRUCTURE == "表体").FirstOrDefault(); if (imptDt == null) throw SysExCore.ThrowFailException("单据导入必须有表体参数定义"); #endregion // 全表体混合模式，关键字段 [单号] ,在 EXECL 中必须用 [单号字样表示] if (!execlDt.Columns.Contains("FromLoc")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ FromLoc ] 列"); if (!execlDt.Columns.Contains("FromPart")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ FromPart ] 列"); if (!execlDt.Columns.Contains("FromLot")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ FromLot ] 列"); if (!execlDt.Columns.Contains("FromWarehouse")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ FromWarehouse ] 列"); if (!execlDt.Columns.Contains("ToLoc")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ ToLoc ] 列"); if (!execlDt.Columns.Contains("ToPart")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ ToPart ] 列"); if (!execlDt.Columns.Contains("ToLot")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ ToLot ] 列"); if (!execlDt.Columns.Contains("ToWarehouse")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ ToWarehouse ] 列"); if (!execlDt.Columns.Contains("IssNbr")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ IssNbr ] 列"); if (!execlDt.Columns.Contains("QtyExp")) throw SysExCore.ThrowFailException(" EXECL 文件中必须包含 [ QtyExp ] 列"); // 数据源 foreach (DataColumn item in execlDt.Columns) { item.ColumnName = $"F_{item.ColumnName}"; } var orderLst = FuncTable2Entity.DataTableToList(execlDt); orderLst = orderLst.FindAll(f => !string.IsNullOrWhiteSpace(f.F_fromLoc) && !string.IsNullOrWhiteSpace(f.F_fromPart) && !string.IsNullOrWhiteSpace(f.F_issNbr) && !string.IsNullOrWhiteSpace(f.F_toLoc) && !string.IsNullOrWhiteSpace(f.F_toPart)); sugar.Insertable(orderLst).ExecuteCommand(); json.Type = 2; json.Message = "导入成功"; } catch (Exception ex) { json.Type = 0; json.Message = ex.Message; } finally { sugar.Dispose(); } return json; } #region 通用单据方法 /// /// 初始化对象 /// /// /// /// /// public void SetProperties(T t, string CelName, string CelVal) { PropertyInfo properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public).Where(v => v.Name == CelName).FirstOrDefault(); if (properties == null) { throw SysExCore.ThrowFailException(t.GetType().Name + ">" + CelName + ">未找到指定属性"); } var ts = properties.PropertyType; string TVal = CelVal.ToUpper().Trim(); if (ts.FullName.Contains("Decimal")) { properties.SetValue(t, Convert.ToDecimal(TVal)); } else if (ts.FullName.Contains("DateTime")) { properties.SetValue(t, Convert.ToDateTime(TVal)); } else if (ts.FullName.Contains("Int32")) { properties.SetValue(t, Convert.ToInt32(TVal)); } else { properties.SetValue(t, TVal); } } public void SetProperties(T t) { PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (var item in properties) { var ts = item.PropertyType; if (ts.FullName.Contains("Decimal")) { item.SetValue(t, 0); } else if (ts.FullName.Contains("DateTime")) { item.SetValue(t, DateTime.Now); } else if (ts.FullName.Contains("Int32")) { item.SetValue(t, 0); } else { item.SetValue(t, ""); } } } #endregion #region 基础资料方法 /// /// 获取增加的SQL语句 /// /// 行值 /// 数据库列属性 /// public string GetUpdateSql(DataRow row, List lstCels, string F_NO, int busType) { string upT = string.Empty; foreach (var cel in lstCels) { if (cel.IsConfig) { string tval = RegexNot(row[cel.ExeclName].ToString()); string val = tval.ToUpper().Trim(); if (cel.CelName == "F_NO") val = val.TrimStart('0'); if (cel.CelName == "F_BASELOCATION") { if (string.IsNullOrEmpty(val)) return cel.ExeclName + ":不能为空"; } if (cel.CelIsNull == "N") { switch (cel.CelType) { case "DATE": if (!string.IsNullOrEmpty(val)) upT += cel.CelName + "=to_date('" + val + "','YYYY-MM-DD'),"; break; case "NUMBER": if (!string.IsNullOrEmpty(val)) upT += cel.CelName + "='" + val + "',"; break; default: if (!string.IsNullOrEmpty(val)) upT += cel.CelName + "='" + val + "',"; else upT += cel.CelName + "=null,"; break; } } else { #region 允许为空 if (!string.IsNullOrEmpty(val)) { upT += cel.CelName + "="; switch (cel.CelType) { case "DATE": upT += "to_date('" + val + "','YYYY-MM-DD'),"; break; case "NUMBER": upT += "'" + val + "',"; break; default: upT += "'" + val + "',"; break; } } #endregion } } } upT = upT.Trim(','); return string.Format(@"UPDATE {0} SET {1} WHERE F_NO='{2}'", lstCels[0].TableName, upT, F_NO); } /// /// 获取增加的SQL语句 /// /// 行值 /// 数据库列属性 /// public string GetInsertSql(DataRow row, List lstCels, string F_NO, int busType) { string resT = "F_NO,"; string valT = "'" + F_NO + "',"; string val = string.Empty; string tno = string.Empty; if (busType >= 2000) tno = Guid.NewGuid().ToString(); foreach (var cel in lstCels) { val = string.Empty; if (cel.CelName == "F_NO") { if (busType < 2000) continue; } if (cel.IsConfig) { if (string.IsNullOrEmpty(tno)) val = RegexNot(row[cel.ExeclName].ToString().Trim()); else val = tno; } val = val.ToUpper().Trim(); if (cel.CelIsNull == "N") { resT += cel.CelName + ","; #region 不允许为空 switch (cel.CelType) { case "DATE": if (string.IsNullOrEmpty(val)) valT += "to_date('" + DateTime.Now.ToString("yyyy-MM-dd") + "','YYYY-MM-DD'),"; else valT += "to_date('" + val + "','YYYY-MM-DD'),"; break; case "NUMBER": if (string.IsNullOrEmpty(val)) valT += "'0',"; else valT += "'" + val + "',"; break; default: if (string.IsNullOrEmpty(val)) valT += "'WMS',"; else valT += "'" + val + "',"; break; } #endregion } else { #region 允许为空 if (!string.IsNullOrEmpty(val)) { resT += cel.CelName + ","; switch (cel.CelType) { case "DATE": valT += "to_date('" + val + "','YYYY-MM-DD'),"; break; case "NUMBER": valT += "'" + val + "',"; break; default: valT += "'" + val + "',"; break; } } #endregion } } resT = resT.Trim(','); valT = valT.Trim(','); return string.Format(@"INSERT INTO {0}({1}) VALUES({2})", lstCels[0].TableName, resT, valT); } #endregion #region 生产转收货方法 public string GetDbCCells(DBTableCell cell, string t1) { string res = ""; switch (cell.CellsType) { case "DATE": res = "to_date('" + t1 + "','yyyy-mm-dd')"; break; case "VARCHAR2": case "NVARCHAR2": res = "'" + t1 + "'"; break; case "NUMBER": res = "'" + t1 + "'"; break; default: break; } return res; } public void GetDbCCell(DBTableCell cell, out string t1, out string v1) { t1 = cell.CellsName; v1 = string.Empty; string[] conkey = new string[] { "F_NO", "F_ROWNO" }; if (conkey.Contains(cell.CellsName)) { v1 = "'" + Guid.NewGuid().ToString() + "'"; } else if (cell.CellsNULLABLE == "N") { switch (cell.CellsType) { case "DATE": v1 = "to_date('" + DateTime.Now.ToString("yyyy-MM-dd") + "','yyyy-mm-dd')"; break; case "VARCHAR2": case "NVARCHAR2": v1 = "'System'"; break; case "NUMBER": v1 = "'0'"; break; default: break; } } } #endregion #region 写入EXECL /// /// 写入EXECL /// /// 文件名 /// 写入的列索引 /// 写入的行索引 /// 原列类型 /// 写入的值 public void WriteExecl(string filePath, int celCount, Type celType, Dictionary dicVal, string Mess) { #region 将结果写入EXECL ISheet sheet = null; ICellStyle dateStyle = null; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { if (filePath.IndexOf(".xlsx") == -1)//2003 { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); sheet = hssfworkbook.GetSheetAt(0); // 定义列样式 dateStyle = hssfworkbook.CreateCellStyle(); dateStyle.FillForegroundColor = 3; dateStyle.VerticalAlignment = VerticalAlignment.Center; dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; dateStyle.FillPattern = FillPattern.SolidForeground; // 第一行最后一列添加操作结果 { IRow dataRow = sheet.GetRow(0); // 获取表头行 ICell newCell = dataRow.CreateCell(celCount); newCell.CellStyle = dateStyle; Type dataType = celType; string val = Mess; ExcelHelper.SetCell(newCell, dateStyle, dataType, val); } foreach (var item in dicVal) { IRow dataRow = sheet.GetRow(item.Key + 1); // 获取表头行 ICell newCell = dataRow.CreateCell(celCount); newCell.CellStyle = dateStyle; Type dataType = celType; string val = item.Value; ExcelHelper.SetCell(newCell, dateStyle, dataType, val); } using (MemoryStream ms = new MemoryStream()) { hssfworkbook.Write(ms); ms.Flush(); ms.Position = 0; using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } else//2007 { XSSFWorkbook xssfworkbook = new XSSFWorkbook(file); sheet = xssfworkbook.GetSheetAt(0); // 定义列样式 dateStyle = xssfworkbook.CreateCellStyle(); dateStyle = xssfworkbook.CreateCellStyle(); dateStyle.FillForegroundColor = 5; dateStyle.VerticalAlignment = VerticalAlignment.Center; dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; dateStyle.FillPattern = FillPattern.SolidForeground; foreach (var item in dicVal) { IRow dataRow = sheet.GetRow(item.Key + 1); // 获取表头行 ICell newCell = dataRow.CreateCell(celCount); newCell.CellStyle = dateStyle; Type dataType = celType; string val = item.Value; ExcelHelper.SetCell(newCell, dateStyle, dataType, val); } using (MemoryStream ms = new MemoryStream()) { xssfworkbook.Write(ms); ms.Flush(); ms.Position = 0; using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } } #endregion } #endregion } public class DBCells { /// /// 表名 /// public string TableName { get; set; } /// /// 字段名 /// public string CelName { get; set; } /// /// 字段说明 /// public string CelMemo { get; set; } /// /// 字段类型 /// public string CelType { get; set; } /// /// 字段是否允许未空 /// public string CelIsNull { get; set; } /// /// 字段默认值 /// public string CelDefualVal { get; set; } /// /// 是否为配置项 /// public bool IsConfig { get; set; } /// /// Execl名称 /// public string ExeclName { get; set; } } public class DBTableCell { public string BusType { get; set; } // 业务类型 public string TableType { get; set; } // 表类型 public string TablesName { get; set; } // 表名 public string CellsName { get; set; } // 列名 public string CellsMemo { get; set; } // 备注 public string CellsType { get; set; } // 字段类型 public string CellsNULLABLE { get; set; } // 是否允许未空 } }