MES / haiNeng · Files

using Hh.Mes.Common;
using Hh.Mes.Common.config;
using Hh.Mes.Common.log;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.EnumEntitys;
using Hh.Mes.POJO.Response;
using Hh.Mes.POJO.WebEntity;
using Hh.Mes.POJO.WebEntity.api;
using Hh.Mes.POJO.WebEntity.bus;
using Hh.Mes.Service.Repository;
using Hh.Mes.Service.WebService.Wo;
using MySqlX.XDevAPI.Common;
using MySqlX.XDevAPI.Relational;
using NPOI.OpenXmlFormats.Dml.Diagram;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using static Hh.Mes.Common.Exel.NpoiExcelImportHelper;

namespace Hh.Mes.Service.WebService.Planned
{
    /// <summary>
    /// 定义导入方法加上特性
    /// dicDtSource 添加需要保存到数据源的数据源 
    /// 注意：只改对应业务的dt 数据【公共方法里面的读取的数据源不要修改、其他地方可能需要用到】
    /// </summary>
    public class ImportMethod : RepositorySqlSugar<base_material>
    {
        public string materialDtKey { get; set; }

        private base_process_route_head processHead { get; set; }
        private List<base_process_route_detail> processDetails { get; set; }

        public ImportMethod()
        {
        }

        #region 设备管理 【Action里面的 equipment 】 是数据库表名 ,方法名没有特殊要求 ，注意不要删除sysDicDtSource里面的key
        [Action("equipment")]
        public Response EquipmentMethod(Dictionary<string, SysDtAllCls> sysDicDtSource, dynamic user, DateTime nowTime)
        {
            #region init
            var response = new Response();
            //【修改项1】
            var excelFileNameKeys = ExcelName.equipment.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            if (response.Code != 200) {
                response.Message = $"【{TableName.excelFileNameEquipment}】" + response.Message;
                return response;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt = response.Result.data;

            DataTable titleVerifySourceDt = response.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName = TableName.tableNameEquipment;
            var eqSaveDt = GetTableStructure(tableName);
            var temp = new SysDtAllCls()
            {
                dtData = eqSaveDt
            };
            var dtSourceNameKey = tableName + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey, temp);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt = excelDtSourceDt.Distinct("Code");
            #endregion

            #region 赋值  理论上是通用的,修改excel文件名 【修改项4】
            var columns = tempExcelDtSourceDt.Columns;
            for (int i = 0; i < tempExcelDtSourceDt.Rows.Count; i++)
            {
                var dr = eqSaveDt.NewRow();
                for (int c = 0; c < columns.Count; c++)
                {
                    var colName = columns[c].ColumnName;
                    dynamic colNameVal = tempExcelDtSourceDt.Rows[i][colName].ToString();
                    response= ImportOhter.SetDataTableDataBefore(response, i, colName, colNameVal, titleVerifySourceDt, TableName.excelFileNameEquipment);
                    if (response.Code != 200) return response;

                    colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt.Columns[colName].DataType.Name);
                    dr[colName] = colNameVal;
                }

                dr["CreatedBy"] = user.Account;
                dr["Created"] =nowTime;
                eqSaveDt.Rows.Add(dr);
            }
            #endregion

            #region 逻辑判断


            #endregion

            return response;
        }
        #endregion

        #region 用户表 【Action里面的 sys_user 】 是数据库表名 ,方法名没有特殊要求 ，注意不要删除sysDicDtSource里面的key
        [Action("sys_user")]
        public Response SysUserMethod(Dictionary<string, SysDtAllCls> sysDicDtSource, dynamic user, DateTime nowTime)
        {
            #region init
            var response = new Response();
            //【修改项1】
            var excelFileNameKeys = ExcelName.sys_user.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            if (response.Code != 200)
            {
                response.Message = $"【{TableName.excelFileNameSysUser}】" + response.Message;
                return response;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt = response.Result.data;

            DataTable titleVerifySourceDt = response.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName = TableName.tableNameSysUser;
            var eqSaveDt = GetTableStructure(tableName);
            if (eqSaveDt.Columns.Contains("Id")) eqSaveDt.Columns.Remove("Id");
            var temp = new SysDtAllCls()
            {
                dtData = eqSaveDt
            };
            var dtSourceNameKey = tableName + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey, temp);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt = excelDtSourceDt.Distinct("account");
            #endregion

            #region 赋值  理论上是通用的,修改excel文件名 【修改项4】
            var columns = tempExcelDtSourceDt.Columns;
            for (int i = 0; i < tempExcelDtSourceDt.Rows.Count; i++)
            {
                var dr = eqSaveDt.NewRow();
                for (int c = 0; c < columns.Count; c++)
                {
                    var colName = columns[c].ColumnName;
                    dynamic colNameVal = tempExcelDtSourceDt.Rows[i][colName].ToString();
                    response = ImportOhter.SetDataTableDataBefore(response, i, colName, colNameVal, titleVerifySourceDt, TableName.excelFileNameSysUser);
                    if (response.Code != 200) return response;

                    colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt.Columns[colName].DataType.Name);
                    dr[colName] = colNameVal;
                }

                dr["password"] = Encryption.Encrypt(ConfigRead.GetInstance.GetAppsetConnection().ResetPwd);
                dr["status"] = 1;
                dr["createBy"] = user.Account;
                dr["createTime"] = nowTime;
                eqSaveDt.Rows.Add(dr);
            }
            #endregion

            #region 逻辑判断

            //response.ResponseError("");
            //return response;
            #endregion

            return response;
        }
        #endregion

        #region 物料表 【Action里面的 base_material 】 是数据库表名 ,方法名没有特殊要求 ，注意不要删除sysDicDtSource里面的key
        [Action("base_material")]
        public Response BaseMaterialMethod(Dictionary<string, SysDtAllCls> sysDicDtSource, dynamic user, DateTime nowTime)
        {
            #region init
            var response = new Response();
            //【修改项1】
            var excelFileNameKeys = ExcelName.base_material.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            if (response.Code != 200)
            {
                response.Message = $"【{TableName.excelFileNameBaseMaterial}】" + response.Message;
                return response;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt = response.Result.data;

            DataTable titleVerifySourceDt = response.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName = TableName.tableNameBaseMaterial;
            var eqSaveDt = GetTableStructure(tableName);
            if (eqSaveDt.Columns.Contains("Id")) eqSaveDt.Columns.Remove("Id");
            var temp = new SysDtAllCls()
            {
                dtData = eqSaveDt
            };
            var dtSourceNameKey = tableName + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey, temp);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt = excelDtSourceDt.Distinct("materialCode");
            #endregion

            #region 赋值  理论上是通用的,修改excel文件名 【修改项4】
            var columns = tempExcelDtSourceDt.Columns;
            for (int i = 0; i < tempExcelDtSourceDt.Rows.Count; i++)
            {
                var dr = eqSaveDt.NewRow();
                for (int c = 0; c < columns.Count; c++)
                {
                    var colName = columns[c].ColumnName;
                    dynamic colNameVal = tempExcelDtSourceDt.Rows[i][colName].ToString();
                    response = ImportOhter.SetDataTableDataBefore(response, i, colName, colNameVal, titleVerifySourceDt, TableName.excelFileNameBaseMaterial);
                    if (response.Code != 200) return response;

                    colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt.Columns[colName].DataType.Name);
                    dr[colName] = colNameVal;
                }
                dr["keys"]=Guid.NewGuid().ToString();
                dr["createBy"] = user.Account;
                dr["createTime"] = nowTime;
                eqSaveDt.Rows.Add(dr);
            }
            #endregion

            #region 逻辑判断

            //response.ResponseError("");
            //return response;
            #endregion

            return response;
        }
        #endregion

        #region 工艺路线表 【Action里面的 base_process_route_head 】 是数据库表名 ,方法名没有特殊要求 ，注意不要删除sysDicDtSource里面的key
        [Action("base_process_route_head")]
        public Response BaseProcessRouteHeadMethod(Dictionary<string, SysDtAllCls> sysDicDtSource, dynamic user, DateTime nowTime)
        {
            #region init
            var response = new Response();
            //【修改项1】
            var excelFileNameKeys = ExcelName.base_process_route_head.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            if (response.Code != 200)
            {
                response.Message = $"【{TableName.excelFileNameBaseProcessRouteHead}】" + response.Message;
                return response;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt = response.Result.data;

            DataTable titleVerifySourceDt = response.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName = TableName.tableNameBaseProcessRouteHead;
            var eqSaveDt = GetTableStructure(tableName);
            if (eqSaveDt.Columns.Contains("Id")) eqSaveDt.Columns.Remove("Id");
            var temp = new SysDtAllCls()
            {
                dtData = eqSaveDt
            };
            var dtSourceNameKey = tableName + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey, temp);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt = excelDtSourceDt.Distinct("processCode");
            #endregion
            ///////////////////////////////////////////////////////////////////
            #region 工艺路线明细
            #region init
            var response2 = new Response();
            //【修改项1】
            var excelFileNameKeys2 = ExcelName.base_process_route_detail.ToString();
            response2 = ImportOhter.ImportMethodBefore(sysDicDtSource, response2, excelFileNameKeys2);
            if (response2.Code != 200)
            {
                response2.Message = $"【{TableName.excelFileNameBaseProcessRouteDetail}】" + response2.Message;
                return response2;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt2 = response2.Result.data;

            DataTable titleVerifySourceDt2 = response2.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName2 = TableName.tableNameBaseProcessRouteDetail;
            var eqSaveDt2 = GetTableStructure(tableName2);
            if (eqSaveDt2.Columns.Contains("Id")) eqSaveDt2.Columns.Remove("Id");
            var temp2 = new SysDtAllCls()
            {
                dtData = eqSaveDt2
            };
            var dtSourceNameKey2 = tableName2 + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey2, temp2);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt_detail = excelDtSourceDt2.Distinct("oprSequenceCode");
            #endregion

            #endregion

            #region 赋值  理论上是通用的,修改excel文件名 【修改项4】
            var columns = tempExcelDtSourceDt.Columns;
            var detail_columns=tempExcelDtSourceDt_detail.Columns;
            for (int i = 0; i < tempExcelDtSourceDt.Rows.Count; i++)
            {
                var dr = eqSaveDt.NewRow();
                for (int c = 0; c < columns.Count; c++)
                {
                    var colName = columns[c].ColumnName;
                    dynamic colNameVal = tempExcelDtSourceDt.Rows[i][colName].ToString();
                    response = ImportOhter.SetDataTableDataBefore(response, i, colName, colNameVal, titleVerifySourceDt, TableName.excelFileNameBaseProcessRouteHead);
                    if (response.Code != 200) return response;

                    colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt.Columns[colName].DataType.Name);
                    dr[colName] = colNameVal;
                }
                dr["keys"] = Guid.NewGuid().ToString();
                dr["createBy"] = user.Account;
                dr["createTime"] = nowTime;
                dr["productCode"] = "";
                dr["productHeaderCode"] = "";
                eqSaveDt.Rows.Add(dr);

                //明细表赋值
                for (int j = 0; j < tempExcelDtSourceDt_detail.Rows.Count; j++)
                {
                    var dr2 = eqSaveDt2.NewRow();
                    for (int c = 0; c < detail_columns.Count; c++)
                    {
                        var colName = detail_columns[c].ColumnName;
                        dynamic colNameVal = tempExcelDtSourceDt_detail.Rows[j][colName].ToString();
                        response2 = ImportOhter.SetDataTableDataBefore(response2, j, colName, colNameVal, titleVerifySourceDt2, TableName.excelFileNameBaseProcessRouteDetail);
                        if (response2.Code != 200) return response2;

                        colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt2.Columns[colName].DataType.Name);
                        dr2[colName] = colNameVal;
                    }
                    dr2["headkeys"] = dr["keys"];
                    dr2["bodyKeys"] = Guid.NewGuid().ToString();
                    dr2["createBy"] = user.Account;
                    dr2["createTime"] = nowTime;
                    eqSaveDt2.Rows.Add(dr2);
                }
            }
            #endregion

            #region 逻辑判断
            #endregion

            return response;
        }

        #endregion

        #region 工序任务表 【Action里面的 bus_workOrder_head 】 是数据库表名 ,方法名没有特殊要求 ，注意不要删除sysDicDtSource里面的key
        [Action("bus_workOrder_head")]
        public Response BusWorkOrderHeadMethod(Dictionary<string, SysDtAllCls> sysDicDtSource, dynamic user, DateTime nowTime)
        {
            #region bus_workOrder_head
            #region init
            var response = new Response();
            //【修改项1】
            var excelFileNameKeys = ExcelName.bus_workOrder_head.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            if (response.Code != 200)
            {
                response.Message = $"【{TableName.excelFileNameBusWorkOrderHead}】" + response.Message;
                return response;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt = response.Result.data;

            DataTable titleVerifySourceDt = response.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName = TableName.tableNameBusWorkOrderHead;
            var eqSaveDt = GetTableStructure(tableName);
            if (eqSaveDt.Columns.Contains("Id")) eqSaveDt.Columns.Remove("Id");
            var temp = new SysDtAllCls()
            {
                dtData = eqSaveDt
            };
            var dtSourceNameKey = tableName + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey, temp);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt = excelDtSourceDt.Distinct("productHeaderCode");
            #endregion

            #region 赋值  理论上是通用的,修改excel文件名 【修改项4】
            var columns = tempExcelDtSourceDt.Columns;
            for (int i = 0; i < tempExcelDtSourceDt.Rows.Count; i++)
            {
                var dr = eqSaveDt.NewRow();
                for (int c = 0; c < columns.Count; c++)
                {
                    var colName = columns[c].ColumnName;
                    dynamic colNameVal = tempExcelDtSourceDt.Rows[i][colName].ToString();
                    response = ImportOhter.SetDataTableDataBefore(response, i, colName, colNameVal, titleVerifySourceDt, TableName.excelFileNameBusWorkOrderHead);
                    if (response.Code != 200) return response;

                    colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt.Columns[colName].DataType.Name);
                    dr[colName] = colNameVal;
                }

                dr["keys"]=Guid.NewGuid();
                dr["createBy"] = user.Account;
                dr["createTime"] = nowTime;
                dr["otherOrderCode"] = tempExcelDtSourceDt.Rows[i]["planCode"];
                eqSaveDt.Rows.Add(dr);
            }
            #endregion
            #endregion

            //////////////////////////////////------------------------////////////////////////////////////////
            
            #region bus_workOrder_detail 获取Excel表值
            #region init
            var response2 = new Response();
            //【修改项1】
            var excelFileNameKeys2 = ExcelName.bus_workOrder_detail.ToString();
            response2 = ImportOhter.ImportMethodBefore(sysDicDtSource, response2, excelFileNameKeys2);
            if (response2.Code != 200)
            {
                response2.Message = $"【{TableName.excelFileNameBusWorkOrderDetail}】" + response2.Message;
                return response2;
            }
            //公共方法数据源，需要判断的数据源单独建一个
            DataTable excelDtSourceDt2 = response2.Result.data;

            DataTable titleVerifySourceDt2 = response2.Result.title;
            #endregion

            #region 创建dt、添加保存到数据库的dicDtSource 【修改项2】
            var tableName2 = TableName.tableNameBusWorkOrderDetail;
            var eqSaveDt2 = GetTableStructure(tableName2);
            if (eqSaveDt2.Columns.Contains("Id")) eqSaveDt2.Columns.Remove("Id");
            var temp2 = new SysDtAllCls()
            {
                dtData = eqSaveDt2
            };
            var dtSourceNameKey2 = tableName2 + TableName.tableSuffix;
            sysDicDtSource.Add(dtSourceNameKey2, temp2);
            #endregion

            #region 去重 【修改项3 根据实际业务】
            var tempExcelDtSourceDt2 = excelDtSourceDt2.Distinct("materialCode");
            #endregion

            #endregion

            #region 逻辑判断
            //0、一次只允许导入一条生产订单
            if (eqSaveDt.Rows.Count > 1)
            {
                response.ResponseError($"一次只能导入一条工序任务，请重新编辑Excel！");
                return response;
            }
            //1、生产订单的产品号要先存在工艺路线的产品表中
            var row = eqSaveDt.Rows[0];//取工序任务导入信息行
            var product = Context.Queryable<base_product_header>().First(x => x.productCode == row["productHeaderCode"].ToString());
            if (product == null) 
            {
                response.ResponseError($"工艺路线产品表【产品编码productCode】[{row["productHeaderCode"]}]不存在，请填写正确的工艺线路基础数据！");
                return response;
            }
            row["processHeadKeys"] = product.processHeadKeys;
            //2、判断导入的线体是否存在线体基础表信息
            var line = Context.Queryable<base_line>().First(x => x.otherCode == row["lineCode"].ToString());
            if (line == null || string.IsNullOrEmpty(line.lineCode))
            {
                response.ResponseError($"没有查询到线体【line_code】和华恒线体一致的数据，请通知管理员在【工厂模型->线体设置】维护外部编码!");
                return response;
            }
            row["lineCode"] = line.lineCode;//线体由外部线体编码换成内部lineCode
            //3、生产订单查询工艺路线基础信息
            processHead = Context.Queryable<base_process_route_head>().First(x => x.productCode == row["productHeaderCode"].ToString() && x.lineCode == row["lineCode"].ToString());
            if (processHead == null || string.IsNullOrEmpty(processHead.lineCode))
            {
                response.ResponseError($"没有查询到工艺路线【lineCode】【{row["lineCode"]}】【productHeaderCode】【{row["productHeaderCode"]}】对应的工艺路线，请先导入工艺路线接口数据!");
                return response;
            }
            //4、生产订单数据
            for(int i=0;i< excelDtSourceDt2.Rows.Count;i++)
            {
                var isExt = Context.Queryable<base_material>().Any(x => x.materialCode == excelDtSourceDt2.Rows[i]["materialCode"].ToString());
                if (isExt) continue;
                response.ResponseError($"没有查询到物料主数据【mater_code】【{excelDtSourceDt2.Rows[i]["materialCode"]}】对应的信息，请先发送物料主数据接口数据!");
                return response;
            }
            #endregion

            #region 从工艺路线明细查工序任务明细
            processDetails = Context.Queryable<base_process_route_detail>().Where(x=>x.bodyKeys== processHead.keys).ToList();//通过工艺路线编码查工序
            var columns2 = tempExcelDtSourceDt2.Columns;
            for (int i = 0; i < tempExcelDtSourceDt2.Rows.Count; i++)
            {
                var dr = eqSaveDt2.NewRow();
                for (int c = 0; c < columns2.Count; c++)
                {
                    var colName = columns2[c].ColumnName;
                    dynamic colNameVal = tempExcelDtSourceDt2.Rows[i][colName].ToString();
                    response2 = ImportOhter.SetDataTableDataBefore(response2, i, colName, colNameVal, titleVerifySourceDt2, TableName.excelFileNameBusWorkOrderDetail);
                    if (response2.Code != 200) return response;

                    colNameVal = ImportOhter.SetDataTableData(colNameVal, eqSaveDt2.Columns[colName].DataType.Name);
                    dr[colName] = colNameVal;
                }

                dr["bodyKeys"] = Guid.NewGuid();
                dr["headKeys"] =row["keys"];
                dr["createBy"] = user.Account;
                dr["createTime"] = nowTime;
                dr["lineCode"] = row["lineCode"];
                foreach(var r in processDetails)
                {
                    dr["oprSequenceCode"] = r.oprSequenceCode;//工序
                    dr["oprSequenceName"] = r.oprSequenceName;
                }
                dr["productHeaderCode"] = row["productHeaderCode"];
                eqSaveDt2.Rows.Add(dr);
            }
           #endregion

           return response;
        }
        #endregion

        #region Save BusWorkOrder Success After
        public void SaveSuccessAfterBusWorkOrder(Dictionary<string,SysDtAllCls> sysDicDtSource,dynamic sysWebUser)
        {
            var response = new Response();
            var excelFileNameKeys = ExcelName.bus_workOrder_head.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            DataTable dt = response.Result.data;

            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, ExcelName.bus_workOrder_detail.ToString());
            DataTable dt_detail = response.Result.data;

            var processHead = Context.Queryable<bus_workOrder_head>().Where(x => x.workOrderCode == dt.Rows[0]["workOrderCode"].ToString()).First();//查询工序任务表信息

            AddBusWorkOrderDetailMaterial(dt_detail,processHead);
            AddApiWorkOrder(dt,dt_detail);
        }

        /// <summary>
        /// bus_workOrder_detail_material 工序任务明细物料表赋值
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dt_detail"></param>
        private void AddBusWorkOrderDetailMaterial(DataTable dt_detail,bus_workOrder_head processHead)
        {
            for(int i = 0; i < dt_detail.Rows.Count; i++)
            {
                var r = dt_detail.Rows[i];
                var busWorkOrderDetailMaterial = new bus_workOrder_detail_material
                {
                    headKey = processHead.keys,
                    bodyKey = Context.Queryable<bus_workOrder_detail>().Where(x=>x.headKeys==processHead.keys && x.workCenterCode == r["stationCode"].ToString())?.First()?.bodyKeys,
                    productCode = processHead.productHeaderCode,
                    materialCode = r["materialCode"].ToString(),
                    materialName = r["materialName"].ToString(),
                    num = Convert.ToDecimal(r["qty"]),
                    wh_code = r["whCode"].ToString(),
                    lineCode = processHead.lineCode,
                    workCenterCode = r["stationCode"].ToString(),
                    createBy = SystemVariable.DefaultCreated,
                    createTime = DateTime.Now
                };
                Context.Insertable(busWorkOrderDetailMaterial);
            }
            
        }

        /// <summary>
        /// api_workOrder_head、api_workOrder_detail原始数据赋值
        /// </summary>
        /// <param name="dt"></param>
        private void AddApiWorkOrder(DataTable dt,DataTable detailDt)
        {
            var head = new api_workOrder_head
            {
                keys = Guid.NewGuid(),
                workOrderCode = dt.Rows[0]["workOrderCode"].ToString(),
                productCode = dt.Rows[0]["productHeaderCode"].ToString(),
                planCode = dt.Rows[0]["planCode"].ToString(),
                lineCode = dt.Rows[0]["lineCode"].ToString(),
                orderType = "",
                playdate=Convert.ToDateTime(dt.Rows[0]["planStartTime"]),
                enddate=Convert.ToDateTime(dt.Rows[0]["planEndTime"].ToString()),
                createTime = DateTime.Now,
                createBy = SystemVariable.DefaultCreated
            };
            List<api_workOrder_detail> details = new List<api_workOrder_detail>();
            for (int i=0;i<detailDt.Rows.Count;i++)
            {
                details.Add(new api_workOrder_detail
                {
                    headKeys = head.keys,
                    planCode = dt.Rows[0]["planCode"].ToString(),
                    stationCode = detailDt.Rows[i]["stationCode"].ToString(),
                    materialCode = detailDt.Rows[i]["materialCode"].ToString(),
                    materialName = detailDt.Rows[i]["materialName"].ToString(),
                    qty =Convert.ToDecimal(detailDt.Rows[i]["qty"].ToString()),
                    whCode = detailDt.Rows[i]["whCode"].ToString(),
                    procedCode = detailDt.Rows[i]["procedCode"].ToString(),
                    createTime = DateTime.Now,
                    createBy = SystemVariable.DefaultCreated
                });
            }
            Context.Insertable(head).AddQueue();
            Context.Insertable(details).AddQueue();
        }
        #endregion

        #region Save BaseProcessRoute Success After
        /// <summary>
        /// 工艺路线表保存之后
        /// </summary>
        /// <param name="baseProcessRoute"></param>
        public void SaveSuccessAfterBaseProcessRoute(Dictionary<string, SysDtAllCls> sysDicDtSource,dynamic sysWebUser)
        {

            var response = new Response();
            var excelFileNameKeys = ExcelName.base_process_route_head.ToString();
            response = ImportOhter.ImportMethodBefore(sysDicDtSource, response, excelFileNameKeys);
            DataTable dt = response.Result.data;

            response=ImportOhter.ImportMethodBefore(sysDicDtSource,response,ExcelName.base_process_route_detail.ToString());
            DataTable dt_detail = response.Result.data; 

            AddBaseProcessProductRel(dt, sysWebUser?.Account);
            AddBaseWorkCenter(dt_detail, sysWebUser?.Account);
        }

        /// <summary>
        /// productCode 还是需要这个字段逗号隔开，用于生成 base_process_product_rel表;base_product_header不存在新增，存在跳过；
        /// </summary>
        /// <param name="product"></param>
        /// <param name="dr"></param>
        /// <param name="user"></param>
        private void AddBaseProcessProductRel(DataTable dt,string user)
        {
            //产品保存到base_process_product_rel表
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string[] products = dt.Rows[i]["productCode"]?.ToString().Split(',');
                var dr = dt.Rows[i];
                if (products != null)
                {
                    foreach (var p in products)
                    {
                        var sql = $@"insert into base_process_product_rel (keys,processCode,processName,productCode,createBy,createTime)
                              values( '{Guid.NewGuid()}','{dr["processCode"]}','{dr["processName"]}','{p}','{user}','{DateTime.Now}')";
                        var isHave = Context.Queryable<base_process_product_rel>().Where(x => x.processCode == dr["processCode"].ToString() && x.productCode == p).ToList();
                        var result = 0;
                        if (isHave.Count <= 0)//同时满足(相同工艺相同产品)条件；不存在新增，存在跳过
                        {
                            result = base.Context.Ado.ExecuteCommand(sql);
                        }
                        //保存 base_product_header 不存在新增、存在跳过
                        var ProcessHeadKeys = Context.Queryable<base_process_route_head>().Where(x => x.processCode == dr["processCode"].ToString())?.First();
                        sql = $@"INSERT INTO base_product_header (keys, productCode, productName, processCode, createTime, createBy,processHeadKeys)
                         VALUES('{Guid.NewGuid()}', '{p}', '', '{dr["processCode"]}', '{DateTime.Now}', '{user}', '{ProcessHeadKeys?.keys}');";
                        var isSave = Context.Queryable<base_product_header>().Where(x => x.processCode == dr["processCode"].ToString() && x.productCode == p).ToList();
                        if (isSave.Count <= 0)
                        {
                            result = base.Context.Ado.ExecuteCommand(sql);
                        }

                        if (result <= 0 && isHave.Count <= 0 )
                        {
                            Log4NetHelper.Instance.Error($"【ImportMethod-AddBaseProcessProductRel 执行新增工艺产品列表信息失败 sql】" + sql);
                        }

                        if (result <= 0 && isSave.Count <= 0)
                        {
                            Log4NetHelper.Instance.Error($"【ImportMethod-AddBaseProductHeader 执行新增产品设置信息失败 sql】" + sql);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// workCenterCode、workCenterName：保存到 base_work_center 表，不存在新增、存在跳过
        /// </summary>
        private void AddBaseWorkCenter(DataTable dt, string user)
        {
            for(int i=0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                var sql = $@"insert into base_work_center ([key],workCenterCode,workCenterName,createBy,createTime)
                              values( '{Guid.NewGuid()}','{dr["workCenterCode"]}','{dr["workCenterName"]}','{user}','{DateTime.Now}')";
                var isHave = Context.Queryable<base_work_center>().Where(x => x.workCenterCode == dr["workCenterCode"].ToString()).ToList();
                var result = 0;
                if (isHave.Count <= 0)//不存在新增，存在跳过
                {
                    result = base.Context.Ado.ExecuteCommand(sql);
                }

                if (result <= 0 && isHave.Count<=0)
                {
                    Log4NetHelper.Instance.Error($"【ImportMethod-Addbase_work_center 执行新增工作重心失败 sql】" + sql);
                }
            }
        }
        #endregion


        #region Save SysUser Success After 
        /// <summary>
        /// SysUser表保存成功之后
        /// </summary>
        /// <param name="dateTime"></param>
        public void SaveSuccessAfterSysUesr(DateTime dateTime,dynamic sysWebUser)
        {
            UpdateSysRelevance(sysWebUser.Account);
        }
        /// <summary>
        /// 更新部门关联的用户
        /// </summary>
        private void UpdateSysRelevance(string user)
        {
            // UserOrg：用户机构关联KEY 
            var relKey = "UserOrg";
            // 26：是“工艺管线预制车间”==>“巨驰”的部门ID
            var deptId = 26;
            var sql = $@"insert into sys_relevance (relKey,firstId,secondId,createBy)
                         select  UserOrg='{relKey}' , 
		                         t1.id ,
		                         secondId={deptId},
		                         createBy='{user}' 
                        from  sys_user t1 with (nolock)
                        left join  (select distinct firstId from sys_relevance) t2 on t1.id=t2.firstId
                        where   t2.firstId  is null";
            var result = base.Context.Ado.ExecuteCommand(sql);

            if (result <= 0)
            {
                Log4NetHelper.Instance.Error($"【ImportMethod-UpdateSysRelevance 执行更新部门关联的用户失败 sql】" + sql);
            }
        }
        #endregion

        #region 公共方法

        /// <summary>
        /// 获取表结构 没有数据源,并设置dt的表名 ,去掉id列
        /// </summary>
        public DataTable GetTableStructure(string tableName)
        {
            var sql = $"select * from {tableName} where 1!=1";
            var dt = base.Context.Ado.GetDataTable(sql);
            dt.TableName = tableName;
            //var columnsId = "id";
            // if (dt.Columns.Contains(columnsId)) dt.Columns.Remove(columnsId);
            return dt;
        }


        #endregion
    }
}