WEBUI/WMS.BZCore/ImportExecl/ImportDataOrExecl.cs

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
    {




        /// <summary>
        /// 生产转发料单
        /// </summary>
        /// <param name="execlDt"></param>
        /// <param name="busType"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public JsonExecl ImportProdOrder(DataTable execlDt, int busType, string loginName)
        {
            JsonExecl json = new JsonExecl();
            json.Type = 0;
            string sql = "";
            SqlSugarClient sugar = null;
            // 保存需要操作的表名
            List<string> LstTable = new List<string>();
            // 保存数据库表字段信息
            List<DBTableCell> lstHeat = new List<DBTableCell>();
            // 保存导出的数据
            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<string> delectsql = new List<string>();
                // 保存数据库中没有的料号
                List<string> lstmatno = new List<string>();

                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<string> execlCelName = new List<string>();
                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<string, string> lstStr = new Dictionary<string, string>();

                    #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", ""); //替换所有不可见字符为空格
        }

        /// <summary>
        /// 基础资料导入方法
        /// </summary>
        /// <param name="filePath">文件路径/param>
        /// <param name="busType">业务类型</param>
        /// <param name="loginName">登陆账号</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<BASE_IMPORTRELATIONSHIP>().Where(v => v.BUSINSERTVAL == busType).First();
                if (importLst == null)
                    throw new Exception("未找到主表参数");

                var imBaseDtLst = sugar.Queryable<BASE_IMPORTBASE>().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<DBCells> lstCells = new List<DBCells>();

                #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<string> lstErrCel = new List<string>();
                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<int, string[]> dicRes = new Dictionary<int, string[]>();


                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<ColumnModel>() {
                             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;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="row">行数据</param>
        /// <param name="lstCels">数据库列信息</param>
        /// <param name="F_ROWNO">库存主键</param>
        /// <param name="busType">业务类型：固定>2000</param>
        /// <param name="i">循环的索引（为了将异常数据写入EXECL反馈）</param>
        /// <param name="sugar">数据库连接对象</param>
        /// <param name="dtSouse">返回的异常信息</param>
        /// <returns></returns>
        public string GetInsertINVCONTR(DataRow row, List<DBCells> lstCels, string F_ROWNO, int busType, int i, SqlSugarClient sugar, ref DataTable dtSouse, ref Dictionary<int, string[]> 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<string>("F_MATNO") } into m
                            //             select new
                            //             {
                            //                 DBmatno = m.Key.DBmatno,
                            //                 stockQty = m.Sum(v => v.Field<decimal>("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);
        }

        /// <summary>
        /// 通用单据导入方法（简单校验）
        /// </summary>
        /// <param name="dtExeclSouse"></param>
        /// <param name="busTypeVal"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        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<BASE_IMPORTRELATIONSHIP>().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<BASE_IMPORTBASE>().Where(v => v.F_NO == imptHt.F_NO).ToList();
                var imBaseDtLst = sugar.Queryable<BASE_IMPORTBASE>().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<int, string> dicKeys = new Dictionary<int, string>();

                List<BILL_ORDERGRP> lstOrder = new List<BILL_ORDERGRP>();
                List<BILL_ORDERITEM> lstItem = new List<BILL_ORDERITEM>();

                // 判断表头数据是否在 EXECL 的表头数据中
                if (imBaseHtLst.Where(v => v.F_DATATYPE == "表头").FirstOrDefault() != null)
                {
                    #region EXECL 表头表体数据混合模式
                    DataTable dtHead = new DataTable();
                    DataTable dtData = new DataTable();
                    List<string[]> lstTitel = new List<string[]>();
                    // 如果表头关键词为空，则从 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<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, busTypeVal + "_" + val);
                                    }
                                }
                                #endregion

                                #region 表头供应商
                                else if (ht.DBCELLNAME == "F_SUPPLIERNO")
                                {
                                    if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                    {
                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            var sup = sugar.Queryable<BASE_SUPPLIER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                            if (sup == null)
                                            {
                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "供应商不存在");
                                            }
                                        }
                                    }
                                }
                                #endregion

                                #region 表头客户
                                else if (ht.DBCELLNAME == "F_CUSTOMERNO")
                                {
                                    if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                    {
                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            var sup = sugar.Queryable<BASE_CUSTOMER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                            if (sup == null)
                                            {
                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "客户不存在");
                                            }
                                        }
                                    }
                                }
                                #endregion

                                #region 设置字段
                                else
                                {

                                    if (ht.F_ISNULL == 1)
                                    {
                                        if (string.IsNullOrEmpty(val))
                                        {
                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                        }
                                        else
                                            SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                    }
                                    else
                                        SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                }
                                #endregion

                                //if (!string.IsNullOrEmpty(val))
                                //{
                                //    SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                                }
                                                else
                                                {
                                                    SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, busTypeVal + "_" + val);
                                                }
                                            }
                                            #endregion

                                            #region 表头供应商
                                            else if (ht.DBCELLNAME == "F_SUPPLIERNO")
                                            {
                                                if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                                {
                                                    SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                                }
                                                else
                                                {
                                                    if (!string.IsNullOrEmpty(val))
                                                    {
                                                        var sup = sugar.Queryable<BASE_SUPPLIER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                                        if (sup == null)
                                                        {
                                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "供应商不存在");
                                                        }
                                                    }
                                                }
                                            }
                                            #endregion

                                            #region 表头客户
                                            else if (ht.DBCELLNAME == "F_CUSTOMERNO")
                                            {
                                                if (ht.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                                {
                                                    SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                                }
                                                else
                                                {
                                                    if (!string.IsNullOrEmpty(val))
                                                    {
                                                        var sup = sugar.Queryable<BASE_CUSTOMER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                                        if (sup == null)
                                                        {
                                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "客户不存在");
                                                        }
                                                    }
                                                }
                                            }
                                            #endregion

                                            #region 设置字段
                                            else
                                            {
                                                if (ht.F_ISNULL == 1)
                                                {
                                                    if (string.IsNullOrEmpty(val))
                                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                                    else
                                                        SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                                }
                                                else
                                                    SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                                //if (!string.IsNullOrEmpty(val))
                                                //    SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>().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<BILL_ORDERITEM>(item, "F_DEFINE10", dt.EXECLCELLNAME + ":不能为空");
                                                }
                                                else
                                                {
                                                    var mat = sugar.Queryable<BASE_MATITEM>().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<BILL_ORDERITEM>(item, "F_DEFINE10", dt.EXECLCELLNAME + ":不能为空");
                                                    else
                                                        SetProperties<BILL_ORDERITEM>(item, dt.DBCELLNAME, val);
                                                }

                                                else
                                                    SetProperties<BILL_ORDERITEM>(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<string[]> lstKeys = new List<string[]>();
                        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<BILL_ORDERGRP>(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空");
                                                        }
                                                        else
                                                        {
                                                            SetProperties<BILL_ORDERGRP>(order, dt.DBCELLNAME, busTypeVal + "_" + val);
                                                        }
                                                    }
                                                    #endregion

                                                    #region 表头供应商
                                                    else if (dt.DBCELLNAME == "F_SUPPLIERNO")
                                                    {
                                                        if (dt.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                                        {
                                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空");
                                                        }
                                                        else
                                                        {
                                                            if (!string.IsNullOrEmpty(val))
                                                            {
                                                                var sup = sugar.Queryable<BASE_SUPPLIER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                                                if (sup == null)
                                                                {
                                                                    SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "供应商不存在");
                                                                }
                                                            }
                                                        }
                                                    }
                                                    #endregion

                                                    #region 表头客户
                                                    else if (dt.DBCELLNAME == "F_CUSTOMERNO")
                                                    {
                                                        if (dt.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                                        {
                                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空");
                                                        }
                                                        else
                                                        {
                                                            if (!string.IsNullOrEmpty(val))
                                                            {
                                                                var sup = sugar.Queryable<BASE_CUSTOMER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                                                if (sup == null)
                                                                {
                                                                    SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "客户不存在");
                                                                }
                                                            }
                                                        }
                                                    }
                                                    #endregion

                                                    #region 设置字段
                                                    else
                                                    {

                                                        if (dt.F_ISNULL == 1)
                                                        {
                                                            if (string.IsNullOrEmpty(val))
                                                            {
                                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空");
                                                            }
                                                            else
                                                                SetProperties<BILL_ORDERGRP>(order, dt.DBCELLNAME, val);
                                                        }
                                                        else
                                                            SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空");
                                                    }
                                                    else
                                                    {
                                                        SetProperties<BILL_ORDERGRP>(order, imp.DBCELLNAME, busTypeVal + "_" + celVal);
                                                    }
                                                }
                                                #endregion

                                                #region 表头供应商
                                                else if (imp.DBCELLNAME == "F_SUPPLIERNO")
                                                {
                                                    if (imp.F_ISNULL == 1 && string.IsNullOrEmpty(celVal))
                                                    {
                                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空");
                                                    }
                                                    else
                                                    {
                                                        if (!string.IsNullOrEmpty(celVal))
                                                        {
                                                            var sup = sugar.Queryable<BASE_SUPPLIER>().Where(v => v.F_NO == celVal || v.F_NAME == celVal).First();
                                                            if (sup == null)
                                                            {
                                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "供应商不存在");
                                                            }
                                                        }
                                                    }
                                                }
                                                #endregion

                                                #region 表头客户
                                                else if (imp.DBCELLNAME == "F_CUSTOMERNO")
                                                {
                                                    if (imp.F_ISNULL == 1 && string.IsNullOrEmpty(celVal))
                                                    {
                                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空");
                                                    }
                                                    else
                                                    {
                                                        if (!string.IsNullOrEmpty(celVal))
                                                        {
                                                            var sup = sugar.Queryable<BASE_CUSTOMER>().Where(v => v.F_NO == celVal || v.F_NAME == celVal).First();
                                                            if (sup == null)
                                                            {
                                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "客户不存在");
                                                            }
                                                        }
                                                    }
                                                }
                                                #endregion

                                                #region 设置字段
                                                else
                                                {
                                                    if (imp.F_ISNULL == 1)
                                                    {
                                                        if (string.IsNullOrEmpty(celVal))
                                                        {
                                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空");
                                                        }
                                                        else
                                                            SetProperties<BILL_ORDERGRP>(order, imp.DBCELLNAME, celVal);
                                                    }
                                                    else
                                                        SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>().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<BILL_ORDERITEM>(item, "F_DEFINE10", impt.EXECLCELLNAME + ":不能为空");
                                                }
                                                else
                                                {
                                                    var mat = sugar.Queryable<BASE_MATITEM>().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<string[]> lstTitel = new List<string[]>();
                        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<string>("单号"), } 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<BILL_ORDERGRP>(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        SetProperties<BILL_ORDERGRP>(order, imbase.DBCELLNAME, busTypeVal + "_" + val);
                                    }
                                }
                                #endregion

                                #region 表头供应商
                                else if (imbase.DBCELLNAME == "F_SUPPLIERNO")
                                {
                                    if (imbase.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                    {
                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            var sup = sugar.Queryable<BASE_SUPPLIER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                            if (sup == null)
                                            {
                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "供应商不存在");
                                            }
                                        }
                                    }
                                }
                                #endregion

                                #region 表头客户
                                else if (imbase.DBCELLNAME == "F_CUSTOMERNO")
                                {
                                    if (imbase.F_ISNULL == 1 && string.IsNullOrEmpty(val))
                                    {
                                        SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            var sup = sugar.Queryable<BASE_CUSTOMER>().Where(v => v.F_NO == val || v.F_NAME == val).First();
                                            if (sup == null)
                                            {
                                                SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", "客户不存在");
                                            }
                                        }
                                    }
                                }
                                #endregion

                                #region 设置字段
                                else
                                {
                                    if (imbase.F_ISNULL == 1)
                                    {
                                        if (string.IsNullOrEmpty(val))
                                        {
                                            SetProperties<BILL_ORDERGRP>(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空");
                                        }
                                        else
                                            SetProperties<BILL_ORDERGRP>(order, imbase.DBCELLNAME, val);
                                    }
                                    else
                                        SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>().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<BILL_ORDERITEM>(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                    {
                                        var mat = sugar.Queryable<BASE_MATITEM>().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<BILL_ORDERGRP>().Where(v => v.F_NO == order.F_NO).First();
                            if (dbOrder == null)
                            {
                                f = sugar.Insertable<BILL_ORDERGRP>(order).ExecuteCommand();
                                if (f > 0)
                                {
                                    f = sugar.Insertable<BILL_ORDERITEM>(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<BILL_ORDERGRP>().Where(v => v.F_NO == SSS).First();
                                            if (or5 == null)
                                                throw new Exception("未找到PO指令单号");
                                        }
                                        else if (order.F_TYPENUM == 6)
                                        {
                                            var or5 = sugar.Queryable<BILL_ORDERGRP>().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<BILL_ORDERGRP>().Where(v => v.F_NO == SSS).First();
                                            if (or5 == null)
                                                throw new Exception("未找到INB指令单号");
                                        }

                                        var dbItem = sugar.Queryable<BILL_ORDERITEM>().Where(v => v.F_ORDERNO == order.F_NO && v.F_MATNO == item.F_MATNO).First();
                                        if (dbItem == null)
                                        {
                                            f = sugar.Insertable<BILL_ORDERITEM>(item).ExecuteCommand();
                                        }
                                        else
                                        {
                                            // 如果WMS的操作数量大于本次修改的计划量，将不允许修改
                                            if (dbItem.F_AQTY > item.F_FQTY)
                                            {
                                                item.F_DEFINE10 = "计划量不能小于实际量";
                                            }
                                            else
                                            {
                                                dbItem.F_AQTY = item.F_AQTY;
                                                f = sugar.Updateable<BILL_ORDERITEM>(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<ColumnModel>() {
                             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;
        }


        /// <summary>
        /// 通用单据导入方法（没有任何验证）
        /// </summary>
        /// <param name="dtExeclSouse"></param>
        /// <param name="busTypeVal"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        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<BASE_IMPORTRELATIONSHIP> importLst = null;
                #region 获取数据库参数配置
                if (busTypeVal == 0)
                    throw SysExCore.ThrowFailException("单据类型异常");
                else if (busTypeVal > 0)
                    // 获取参数配置
                    importLst = sugar.Queryable<BASE_IMPORTRELATIONSHIP>().Where(v => v.BUSINSERTVAL == busTypeVal).ToList();
                else if (busTypeVal == -1 || busTypeVal == -2)
                {

                    busTypeVal = 7; // PO收货单(中)
                    if (busTypeVal == -1)
                        importLst = sugar.Queryable<BASE_IMPORTRELATIONSHIP>().Where(v => new string[] { "9", "10" }.Contains(v.F_NO)).ToList();
                    else  //  PO收货单(英)
                        importLst = sugar.Queryable<BASE_IMPORTRELATIONSHIP>().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<BASE_IMPORTBASE>().Where(v => v.F_NO == imptHt.F_NO).ToList();
                var imBaseDtLst = sugar.Queryable<BASE_IMPORTBASE>().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<int, string> dicKeys = new Dictionary<int, string>();

                List<BILL_ORDERGRP> lstOrder = new List<BILL_ORDERGRP>();
                List<BILL_ORDERITEM> lstItem = new List<BILL_ORDERITEM>();

                // 判断表头数据是否在 EXECL 的表头数据中
                if (imBaseHtLst.Where(v => v.F_DATATYPE == "表头").FirstOrDefault() != null)
                {
                    #region EXECL 表头表体数据混合模式
                    DataTable dtHead = new DataTable();
                    DataTable dtData = new DataTable();
                    List<string[]> lstTitel = new List<string[]>();
                    // 如果表头关键词为空，则从 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<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                        SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                }
                                else
                                    SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>(order, "F_DEFINE15", ht.EXECLCELLNAME + ":不能为空");
                                                else
                                                    SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                            }
                                            else
                                                SetProperties<BILL_ORDERGRP>(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<SYS_DATA>().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First();
                                                if (matstate == null)
                                                    SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", dt.EXECLCELLNAME + ":选中的值《" + val + "》不存在");
                                                else
                                                    SetProperties<BILL_ORDERITEM>(item, dt.DBCELLNAME, matstate.F_NUM.ToString());
                                                continue;
                                            }

                                            if (dt.F_ISNULL == 1)
                                            {
                                                if (string.IsNullOrEmpty(val))
                                                    SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", dt.EXECLCELLNAME + ":不能为空");
                                                else
                                                    SetProperties<BILL_ORDERITEM>(item, dt.DBCELLNAME, val);
                                            }

                                            else
                                                SetProperties<BILL_ORDERITEM>(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<string[]> lstKeys = new List<string[]>();
                        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<BILL_ORDERGRP>(order, "F_DEFINE15", dt.EXECLCELLNAME + ":不能为空");
                                                        }
                                                        else
                                                            SetProperties<BILL_ORDERGRP>(order, dt.DBCELLNAME, val);
                                                    }
                                                    else
                                                        SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>(order, "F_DEFINE15", imp.EXECLCELLNAME + ":不能为空");
                                                    }
                                                    else
                                                        SetProperties<BILL_ORDERGRP>(order, imp.DBCELLNAME, celVal);
                                                }
                                                else
                                                    SetProperties<BILL_ORDERGRP>(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<SYS_DATA>().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First();
                                                if (matstate == null)
                                                    SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", impt.EXECLCELLNAME + ":选中的值《" + val + "》不存在");
                                                else
                                                    SetProperties<BILL_ORDERITEM>(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<string[]> lstTitel = new List<string[]>();
                        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<string>("单号"), } 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<BILL_ORDERGRP>(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                        SetProperties<BILL_ORDERGRP>(order, imbase.DBCELLNAME, val);
                                }
                                else
                                    SetProperties<BILL_ORDERGRP>(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<SYS_DATA>().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First();
                                    if (matstate == null)
                                        SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", imbase.EXECLCELLNAME + ":选中的值《" + val + "》不存在");
                                    else
                                        SetProperties<BILL_ORDERITEM>(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<ColumnModel>() {
                             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;
        }


        /// <summary>
        /// 收货单格式验证
        /// </summary>
        /// <param name="dtExeclSouse"></param>
        /// <param name="busTypeVal"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        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<BASE_IMPORTRELATIONSHIP> importLst = null;
                #region 获取数据库参数配置

                importLst = sugar.Queryable<BASE_IMPORTRELATIONSHIP>().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<BASE_IMPORTBASE>().Where(v => v.F_NO == imptHt.F_NO).ToList();
                var imBaseDtLst = sugar.Queryable<BASE_IMPORTBASE>().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<int, string> dicKeys = new Dictionary<int, string>();

                List<BILL_ORDERGRP> lstOrder = new List<BILL_ORDERGRP>();
                List<BILL_ORDERITEM> lstItem = new List<BILL_ORDERITEM>();

                // 判断表头数据是否在 EXECL 的表头数据中
                if (imBaseHtLst.Where(v => v.F_DATATYPE == "表头").FirstOrDefault() != null)
                {
                    #region EXECL 表头表体数据混合模式
                    DataTable dtHead = new DataTable();
                    DataTable dtData = new DataTable();
                    List<string[]> lstTitel = new List<string[]>();
                    // 如果表头关键词为空，则从 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<BILL_ORDERGRP>(order, "F_DEFINE15", order.F_DEFINE15 + "|" + ht.EXECLCELLNAME + ":不能为空");
                                    }
                                    else
                                        SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                }
                                else
                                    SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>(order, "F_DEFINE15", order.F_DEFINE15 + "|" + ht.EXECLCELLNAME + ":不能为空");
                                                else
                                                    SetProperties<BILL_ORDERGRP>(order, ht.DBCELLNAME, val);
                                            }
                                            else
                                                SetProperties<BILL_ORDERGRP>(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<SYS_DATA>().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First();
                                                if (matstate == null)
                                                    SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", item.F_DEFINE10 + "|" + dt.EXECLCELLNAME + ":选中的值《" + val + "》不存在");
                                                else
                                                    SetProperties<BILL_ORDERITEM>(item, dt.DBCELLNAME, matstate.F_NUM.ToString());
                                                continue;
                                            }

                                            if (dt.F_ISNULL == 1)
                                            {
                                                if (string.IsNullOrEmpty(val))
                                                    SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", item.F_DEFINE10 + "|" + dt.EXECLCELLNAME + ":不能为空");
                                                else
                                                    SetProperties<BILL_ORDERITEM>(item, dt.DBCELLNAME, val);
                                            }

                                            else
                                                SetProperties<BILL_ORDERITEM>(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<string[]> lstKeys = new List<string[]>();
                        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<BILL_ORDERGRP>(order, "F_DEFINE15", order.F_DEFINE15 + "|" + dt.EXECLCELLNAME + ":不能为空");
                                                        }
                                                        else
                                                            SetProperties<BILL_ORDERGRP>(order, dt.DBCELLNAME, val);
                                                    }
                                                    else
                                                        SetProperties<BILL_ORDERGRP>(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<BILL_ORDERGRP>(order, "F_DEFINE15", order.F_DEFINE15 + "|" + imp.EXECLCELLNAME + ":不能为空");
                                                    }
                                                    else
                                                        SetProperties<BILL_ORDERGRP>(order, imp.DBCELLNAME, celVal);
                                                }
                                                else
                                                    SetProperties<BILL_ORDERGRP>(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<SYS_DATA>().Where(v => v.F_PNO == "EMatState" && (v.F_NAME == val)).First();
                                                if (matstate == null)
                                                    SetProperties<BILL_ORDERITEM>(item, "F_DEFINE10", item.F_DEFINE10 + "|" + impt.EXECLCELLNAME + ":选中的值《" + val + "》不存在");
                                                else
                                                    SetProperties<BILL_ORDERITEM>(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<BASE_SUPPLIER>().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<BASE_CUSTOMER>().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<SYS_DATA>().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<SYS_DATA>().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<BILL_ORDERITEM>().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<BASE_MATITEM>().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<BASE_DATA>().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<SYS_DATA>().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<BASE_DATA>().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<BASE_MATCONVUNIT>().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<BASE_MATCONVUNIT>().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<ColumnModel>() {
                             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;

        }

        /// <summary>
        /// 盘点导入
        /// </summary>
        /// <param name="dtExeclSouse"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        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<BASE_IMPORTRELATIONSHIP>().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<BASE_IMPORTBASE>().Where(v => v.F_NO == imptHt.F_NO).ToList();
                var imBaseDtLst = sugar.Queryable<BASE_IMPORTBASE>().Where(v => v.F_NO == imptDt.F_NO).ToList();
                if (imBaseHtLst.Count <= 0 || imBaseDtLst.Count <= 0)
                    throw SysExCore.ThrowFailException("未找到字段参数的配置");
                #endregion

                List<BILL_ORDERGRPCHECK> lstOrder = new List<BILL_ORDERGRPCHECK>();
                List<BILL_ORDERGRPCHECKWHERE> lstItem = new List<BILL_ORDERGRPCHECKWHERE>();

                // 全表体混合模式，关键字段 [单号] ,在 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<string>("盘点单号") } 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<BILL_ORDERGRPCHECK>(order, "F_DEFINE14", val);
                                    SetProperties<BILL_ORDERGRPCHECK>(order, "F_DEFINE15", imbase.EXECLCELLNAME + ":不能为空");
                                }
                                else
                                    SetProperties<BILL_ORDERGRPCHECK>(order, imbase.DBCELLNAME, val);
                            }
                            else
                            {
                                SetProperties<BILL_ORDERGRPCHECK>(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<BASE_WAREHOUSE>().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<BASE_WAREA>().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<BASE_WARECELL>().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<BASE_MATITEM>().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<BILL_ORDERGRPCHECK>().Where(v => v.F_NO == order.F_NO).First();
                            if (dbOrder == null)
                            {
                                f = sugar.Insertable<BILL_ORDERGRPCHECK>(order).ExecuteCommand();
                                if (f > 0)
                                {
                                    f = sugar.Insertable<BILL_ORDERGRPCHECKWHERE>(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<BILL_ORDERITEM>().Where(v => v.F_ORDERNO == order.F_NO && v.F_MATNO == item.F_MATNO).First();
                                        if (dbItem == null)
                                        {
                                            f = sugar.Insertable<BILL_ORDERITEM>(item).ExecuteCommand();
                                        }
                                        else
                                        {
                                            // 如果WMS的操作数量大于本次修改的计划量，将不允许修改
                                            if (dbItem.F_AQTY > item.F_FQTY)
                                            {
                                                item.F_DEFINE10 = "计划量不能小于实际量";
                                            }
                                            else
                                            {
                                                dbItem.F_FQTY = item.F_FQTY;
                                                f = sugar.Updateable<BILL_ORDERITEM>(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<ColumnModel>() {
                             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;
        }


        /// <summary>
        /// 转仓导入
        /// </summary>
        /// <param name="dtExeclSouse"></param>
        /// <param name="loginName"></param>
        /// <returns></returns>
        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<BASE_IMPORTRELATIONSHIP>().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<BILL_TRANSFERORDERLINE>(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 通用单据方法
        /// <summary>
        /// 初始化对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="CelName"></param>
        /// <param name="CelVal"></param>
        public void SetProperties<T>(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 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 基础资料方法
        /// <summary>
        /// 获取增加的SQL语句
        /// </summary>
        /// <param name="row">行值</param>
        /// <param name="lstCels">数据库列属性</param>
        /// <returns></returns>
        public string GetUpdateSql(DataRow row, List<DBCells> 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);
        }

        /// <summary>
        /// 获取增加的SQL语句
        /// </summary>
        /// <param name="row">行值</param>
        /// <param name="lstCels">数据库列属性</param>
        /// <returns></returns>
        public string GetInsertSql(DataRow row, List<DBCells> 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
        /// <summary>
        /// 写入EXECL
        /// </summary>
        /// <param name="filePath">文件名</param>
        /// <param name="celCount">写入的列索引</param>
        /// <param name="rowIndex">写入的行索引</param>
        /// <param name="celType">原列类型</param>
        /// <param name="celVal">写入的值</param>
        public void WriteExecl(string filePath, int celCount, Type celType, Dictionary<int, string> 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
    {
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName { get; set; }
        /// <summary>
        /// 字段名
        /// </summary>
        public string CelName { get; set; }
        /// <summary>
        /// 字段说明
        /// </summary>
        public string CelMemo { get; set; }
        /// <summary>
        /// 字段类型
        /// </summary>
        public string CelType { get; set; }
        /// <summary>
        /// 字段是否允许未空
        /// </summary>
        public string CelIsNull { get; set; }
        /// <summary>
        /// 字段默认值
        /// </summary>
        public string CelDefualVal { get; set; }
        /// <summary>
        /// 是否为配置项
        /// </summary>
        public bool IsConfig { get; set; }
        /// <summary>
        /// Execl名称
        /// </summary>
        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; }     // 是否允许未空
    }
}