ImportMethod_WorkOrder.cs 17.6 KB
using Hh.Mes.Common;
using Hh.Mes.Common.config;
using Hh.Mes.Common.DataTableTo;
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 partial class ImportMethod 
    {
        #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("workOrderCode");
            #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("productHeaderCode");
            #endregion


            #region 逻辑判断
            for (int i = 0; i < eqSaveDt.Rows.Count; i++)
            {
                //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 j = 0; j < excelDtSourceDt2.Rows.Count; j++)
                {
                    var isExt = Context.Queryable<base_material>().Any(x => x.materialCode == excelDtSourceDt2.Rows[j]["materialCode"].ToString());
                    if (isExt) continue;
                    response.ResponseError($"没有查询到物料主数据【mater_code】【{excelDtSourceDt2.Rows[j]["materialCode"]}】对应的信息,请先发送物料主数据接口数据!");
                    return response;
                }


                #region 从工艺路线明细查工序任务明细
                processDetails = Context.Queryable<base_process_route_detail>().Where(x => x.bodyKeys == processHead.keys).ToList();//通过工艺路线编码查工序
                var columns2 = tempExcelDtSourceDt2.Columns;
                for (int j = 0; j < tempExcelDtSourceDt2.Rows.Count; j++)
                {
                    var dr = eqSaveDt2.NewRow();
                    for (int c = 0; c < columns2.Count; c++)
                    {
                        var colName = columns2[c].ColumnName;
                        dynamic colNameVal = tempExcelDtSourceDt2.Rows[j][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
            }
            #endregion

            #endregion

            response = SaveBusWorkOrderAfter(sysDicDtSource, sysWebUser);
            return response;
        }
        #endregion

        #region Save BusWorkOrder Success After
        /// <summary>
        /// 工序任务表保存之后
        /// </summary>
        /// <param name="sysDicDtSource"></param>
        /// <param name="sysWebUser"></param>
        /// <returns></returns>
        public Response SaveBusWorkOrderAfter(Dictionary<string, SysDtAllCls> sysDicDtSource, dynamic sysWebUser)
        {
            var response = new Response();
            var dt = sysDicDtSource["bus_workOrder_head_source"].dtData;
            var dt_detail = sysDicDtSource["bus_workOrder_detail_source"].dtData;

            #region bus_workOrder_detail_material 工序任务明细物料表赋值
            for (int i = 0; i < dt_detail.Rows.Count; i++)
            {
                var r = dt_detail.Rows[i];
                var processHead = Context.Queryable<bus_workOrder_head>().Where(x => x.workOrderCode == r["workOrderCode"].ToString()).First();//查询工序任务表信息
                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 = r["productHeaderCode"].ToString(),
                    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);
            }
            #endregion

            #region api_workOrder_headapi_workOrder_detail原始数据赋值
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                var dr = dt.Rows[j];
                var head = new api_workOrder_head
                {
                    keys = Guid.NewGuid(),
                    workOrderCode = dr["workOrderCode"].ToString(),
                    productCode = dr["productHeaderCode"].ToString(),
                    planCode = dr["planCode"].ToString(),
                    lineCode = dr["lineCode"].ToString(),
                    orderType = "",
                    playdate = Convert.ToDateTime(dr["planStartTime"]),
                    enddate = Convert.ToDateTime(dr["planEndTime"].ToString()),
                    createTime = DateTime.Now,
                    createBy = SystemVariable.DefaultCreated
                };
                List<api_workOrder_detail> details = new List<api_workOrder_detail>();
                var detailDt = new DataTable();/////
                for (int i = 0; i < detailDt.Rows.Count; i++)
                {
                    details.Add(new api_workOrder_detail
                    {
                        headKeys = head.keys,
                        planCode = dr["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

            return response;
            //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
    }
}