设备清单导入接口开发，安灯响应接口修改

yl

2022-11-01 f0c3d69b6d97faef544eafc841ecb6d995ca7be6

设备清单导入接口开发，安灯响应接口修改

 .vs/VueWebApi/v16/.suo

Binary files differ

 VueWebApi/Controllers/AppDeviceManageController.cs

@@ -14,7 +14,7 @@
{
    [RoutePrefix(prefix: "api/AppDeviceManage")]
    [ControllerGroup("App设备管理", "在线接口")]
    [ChannelActionFilterAttributeApp]
    //[ChannelActionFilterAttributeApp]
    public class AppDeviceManageController : ApiController
    {
        //定义全局信息返回变量

 VueWebApi/Controllers/LoginController.cs

@@ -138,8 +138,12 @@
                    sql = @"update [dbo].[TUser] set rid=@rid where usercode=@usercode";
                    list.Add(new { str = sql, parm = new { rid = rid, usercode = username } });
                    bool aa = DapperHelper.DoTransaction(list);
                    redis.Remove("LoginUserID" + usertype + userid, 1); //删除redis
                }
                else 
                {
                redis.Remove("LoginUserID" + usertype + userid, 0); //删除redis
                }
                mes.code = "200";
                mes.count = 0;
                mes.Message = "强制下线成功!";
@@ -254,10 +258,10 @@
                    HttpContext.Current.Session.Clear(); //从会话状态集合中删除所有的键值
                    User loginUser = JsonConvert.DeserializeObject<User>(JsonConvert.SerializeObject(ms));
                    //如果redis登录用户中不存在当前用户
                    if (redis.Get<User>("LoginUserID" + ms.usertype + ms.userid, 0) == null)
                    if (redis.Get<User>("LoginUserID" + ms.usertype + ms.userid, 1) == null)
                    {
                        //写入redis(注意：redis类里面的失效时间必须在key创建完成之后才生效)
                        redis.Set<User>("LoginUserID" + ms.usertype + ms.userid, ms, redis.secondsTimeOut, 0);
                        redis.Set<User>("LoginUserID" + ms.usertype + ms.userid, ms, redis.secondsTimeOut, 1);
                        //登录信息写入session
                        HttpContext.Current.Session["User"] = JsonConvert.SerializeObject(ms);
                        //更新rid
@@ -273,7 +277,7 @@
                    else //如果存在
                    {
                        //获取redis中当前用户信息
                        User r_loginUser = redis.Get<User>("LoginUserID" + ms.usertype + ms.userid, 0);
                        User r_loginUser = redis.Get<User>("LoginUserID" + ms.usertype + ms.userid, 1);
                        //如果session中的用户usercode和redis中的用户usercode匹配
                        if ((r_loginUser.usercode + r_loginUser.usertype).Equals(loginUser.usercode + loginUser.usertype))
                        {
@@ -284,7 +288,7 @@
                        else
                        {
                            //重置过期时间(注意：redis类里面的失效时间必须在key创建完成之后才生效)
                            redis.Set<User>("LoginUserID" + ms.usertype + loginUser.userid, loginUser, redis.secondsTimeOut, 0);
                            redis.Set<User>("LoginUserID" + ms.usertype + loginUser.userid, loginUser, redis.secondsTimeOut, 1);
                            //登录信息写入session
                            HttpContext.Current.Session["User"] = JsonConvert.SerializeObject(ms);
                            //更新rid

 VueWebApi/DLL/DAL/AppDeviceManageDAL.cs

@@ -607,7 +607,7 @@
                        string imgPath1 = "", imgPath2 = "", AbsolutePath = "";
                        HttpPostedFile file = files[i];
                        string imgName = DateTime.Now.ToString("yyyyMMddhhmmss") + i.ToString();
                        string keyname = files.AllKeys[i].ToString();
                        string keyname = files.AllKeys[i].ToString().Substring(0, files.AllKeys[i].ToString().IndexOf(";"));
                        if (keyname == "files1")
                        {
                            imgPath1 = "/DeviceImage/image1/" + imgName + file.FileName;     //通过此对象获取文件名
@@ -618,7 +618,6 @@
                            imgPath2 = "/DeviceImage/image2/" + imgName + file.FileName;     //通过此对象获取文件名
                            AbsolutePath = System.Web.HttpContext.Current.Server.MapPath(imgPath2);
                        }

                        file.SaveAs(AbsolutePath); //将上传的东西保存
                                                   //循环写维修记录对应图片表
                        sql = @"insert into  TEqp_RepairImage(source_wo,wo_type,img1url,img2url) 
@@ -1128,7 +1127,7 @@
            try
            {
                list.Clear();
                for (int i = 1; i <= json.Count; i++)
                for (int i = 0; i <json.Count; i++)
                {
                    string code = json[i].code.ToString();
                    string name = json[i].name.ToString();

 VueWebApi/DLL/DAL/LoginDAL.cs

@@ -280,7 +280,7 @@
                cont = DapperHelper.SQL(sql, dynamicParams);
                if (cont > 0)
                {
                    redis.Remove("LoginUserID" + usertype + userid, 0); //删除redis
                    redis.Remove("LoginUserID" + usertype + userid, 1); //删除redis
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "登出成功!";

 VueWebApi/Excel/É豸Çåµ¥.xls

Binary files differ

 VueWebApi/Logs/2022-10-30.TXT

 VueWebApi/Logs/2022-11-01.TXT

 VueWebApi/Properties/PublishProfiles/FolderProfile.pubxml.user

@@ -729,16 +729,16 @@
      <publishTime>12/24/2021 15:38:29</publishTime>
    </File>
    <File Include="bin/VueWebApi.dll">
      <publishTime>10/29/2022 12:58:45</publishTime>
      <publishTime>11/01/2022 18:10:08</publishTime>
    </File>
    <File Include="bin/VueWebApi.dll.config">
      <publishTime>06/15/2022 15:31:43</publishTime>
    </File>
    <File Include="bin/VueWebApi.pdb">
      <publishTime>10/29/2022 12:58:45</publishTime>
      <publishTime>11/01/2022 18:10:08</publishTime>
    </File>
    <File Include="bin/VueWebApi.xml">
      <publishTime>10/29/2022 12:58:45</publishTime>
      <publishTime>11/01/2022 18:10:08</publishTime>
    </File>
    <File Include="bin/WebActivatorEx.dll">
      <publishTime>02/08/2013 16:42:28</publishTime>
@@ -844,6 +844,9 @@
    </File>
    <File Include="Excel/设备保养项目.xls">
      <publishTime>08/29/2022 12:08:02</publishTime>
    </File>
    <File Include="Excel/设备清单.xls">
      <publishTime>11/01/2022 08:08:32</publishTime>
    </File>
    <File Include="Excel/设备点检项目.xls">
      <publishTime>08/17/2022 09:56:57</publishTime>
@@ -1542,7 +1545,7 @@
      <publishTime>06/10/2022 08:20:24</publishTime>
    </File>
    <File Include="Web.config">
      <publishTime>10/29/2022 12:58:51</publishTime>
      <publishTime>11/01/2022 18:10:09</publishTime>
    </File>
  </ItemGroup>
</Project>

 VueWebApi/Tools/ChannelActionFilterAttributeApp.cs

@@ -33,10 +33,10 @@
                var guid = HttpContext.Current.Request.Headers["guid"].ToString();
                base.OnActionExecuting(actionContext);
                //如果存在
                if (redis.Get<User>(rediskey, 0).userid.ToString() != "")
                if (redis.Get<User>(rediskey, 1).userid.ToString() != "")
                {
                    //获取redis中当前用户信息
                    User r_loginUser = redis.Get<User>(rediskey, 0);
                    User r_loginUser = redis.Get<User>(rediskey, 1);
                    //如果session中的用户guid和redis中的用户guid匹配
                    if (r_loginUser.guid.Equals(guid))
                    {

 VueWebApi/Tools/ImportExcel.cs

@@ -272,6 +272,171 @@
        }

        /// <summary>
        /// Excel 3个Sheet导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<DataTable> ExcelToThreeTableList(string file)
        {
            List<DataTable> list = new List<DataTable>();
            IWorkbook Workbook;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook é€‚用XLSX格式，HSSFWorkbook é€‚用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            int count = Workbook.NumberOfSheets; //获取所有SheetName
            if (count < 3)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(0);
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                if (headerRow == null)
                {
                    return list;
                }
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;

                //循环添加标题列
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //数据
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    if (row != null && row.Cells.Count > 0)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                //清除最后的空行
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    bool isnull = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                        {
                            if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                            {
                                isnull = false;
                                break;
                            }
                        }
                    }
                    if (isnull)
                    {
                        table.Rows[i].Delete();
                        i = 0;
                    }
                }
                list.Add(table);
            }
            else
            {
                for (int m = 0; m < count; m++)
                {
                    DataTable table = new DataTable();
                    //定位在第一个sheet
                    ISheet sheet = Workbook.GetSheetAt(m);
                    //第一行为标题行
                    IRow headerRow = sheet.GetRow(0);
                    if (headerRow == null)
                    {
                        return list;
                    }
                    int cellCount = headerRow.LastCellNum;
                    int rowCount = sheet.LastRowNum;

                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null && row.Cells.Count > 0)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    list.Add(table);
                }
            }
            return list;
        }




        /// <summary>
        /// èŽ·å–单元格类型
        /// </summary>
        /// <param name="cell"></param>
@@ -700,6 +865,196 @@
            return list;
        }

        /// <summary>
        /// Excel 3个Sheet æ•°æ®éªŒè¯
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<ExcelErro> ExcelToThreeTableListErro(string file)
        {
            List<ExcelErro> list = new List<ExcelErro>();
            IWorkbook Workbook;
            bool iscell = false;
            int iscellCount = 0;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook é€‚用XLSX格式，HSSFWorkbook é€‚用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            for (int mm = 0; mm < 3; mm++)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(mm);
                //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);  
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                int rowCount0 = sheet.FirstRowNum;
                //int rowCount = sheet.LastRowNum;
                int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //获取删除空行之后的数据行 (只针对于最后数据项有空行的)
                if (rowCount - rowCount0 >= 1)
                {
                    //-------------判断必填项是否为空----------------//
                    //循环数据行
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        iscell = false;
                        iscellCount = 0;
                        IRow row = sheet.GetRow(i);
                        //判断是否有空行
                        if (row == null)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = (i + 1).ToString();
                            erro.ErrorField = "空行";
                            erro.ErrorCont = "空行";
                            list.Add(erro);
                            continue;
                        }
                        //判断是否存在空数据行
                        for (int c = 0; c < cellCount; c++)
                        {
                            if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
                            {
                                iscell = false;
                            }
                            else
                            {
                                iscell = true;
                                iscellCount++;
                            }
                        }
                        if (iscell == true && iscellCount == cellCount)  //空行
                        {
                            continue;
                        }
                        else
                        {
                            //循环标题列
                            for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                            {
                                //获取单元格的填充色
                                string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
                                if (rgb == "51")  //必填项字段列头填充色
                                {
                                    if (sheet.GetRow(i) == null || sheet.GetRow(i).ToString() == "")  //存在空行
                                    {
                                        ExcelErro erro = new ExcelErro();
                                        erro.RoeNumber = i.ToString();
                                        erro.ErrorField = "{第" + i.ToString() + "行}";
                                        erro.ErrorCont = "{第" + i.ToString() + "}行为空";
                                        list.Add(erro);
                                        break;
                                    }
                                    else
                                    {
                                        string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim();   //GetCell:返回包括空列值  Cells:只返回有值的列
                                        if (cellValue == "" || cellValue == null)
                                        {
                                            ExcelErro erro = new ExcelErro();
                                            erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
                                            erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                            erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空";
                                            list.Add(erro);
                                        }
                                    }
                                }

                            }
                        }
                    }
                    //-------------判断唯一项是否重复----------------//
                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    DataView view = new DataView(table);
                    //获取所有唯一列
                    List<string> list0 = new List<string>();
                    //循环标题列
                    for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                    {
                        bool isContain = headerRow.Cells[j].StringCellValue.Contains("唯一");  //标题列中的必填列
                        if (isContain == true)
                        {
                            //
                            if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue + "" }).Rows.Count < table.Rows.Count)
                            {
                                ExcelErro erro = new ExcelErro();
                                erro.RoeNumber = "/";
                                erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
                                list.Add(erro);
                            }
                        }
                    }
                }
            }
            List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
            return list;
        }





 VueWebApi/Tools/ImportExcelData.cs

@@ -568,62 +568,117 @@
        {
            string Message = "";
            code = "";
            DataTable excelTable = new DataTable();
            excelTable = ImportExcel.ExcelToTable(FileCode);
            if (excelTable.Columns.Count != 10)
            List<DataTable> excelTable = new List<DataTable>();
            excelTable = ImportExcel.ExcelToThreeTableList(FileCode);
            if (excelTable.Count != 6)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "导入模板不符合规范,请检查Sheet数";
            }
            else if (excelTable.Columns[0].ColumnName != "序号")
            else if (excelTable[0].Columns.Count != 4)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)不符合规范,请检查列名字段数";
            }
            else if (excelTable.Columns[1].ColumnName != "设备编号(唯一)")
            else if (excelTable[1].Columns.Count != 5)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)不符合规范,请检查列名字段数";
            }
            else if (excelTable.Columns[2].ColumnName != "设备名称")
            else if (excelTable[2].Columns.Count != 9)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备清单)不符合规范,请检查列名字段数";
            }
            else if (excelTable.Columns[3].ColumnName != "设备类型")
            else if (excelTable[0].Columns[0].ColumnName != "序号")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第1列应为{序号}";
            }
            else if (excelTable.Columns[4].ColumnName != "生产车间")
            else if (excelTable[0].Columns[1].ColumnName != "设备类型编号(唯一)")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第2列应为{设备类型编号(唯一)}";
            }
            else if (excelTable.Columns[5].ColumnName != "所属产线")
            else if (excelTable[0].Columns[2].ColumnName != "设备类型名称")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第3列应为{设备类型名称}";
            }
            else if (excelTable.Columns[6].ColumnName != "投入日期")
            else if (excelTable[0].Columns[3].ColumnName != "设备类型描述")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第4列应为{设备类型描述}";
            }
            else if (excelTable.Columns[7].ColumnName != "稼动率（%）")
            else if (excelTable[1].Columns[0].ColumnName != "序号")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第1列应为{序号}";
            }
            else if (excelTable.Columns[8].ColumnName != "使用状态")
            else if (excelTable[1].Columns[1].ColumnName != "设备组编号(唯一)")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第2列应为{设备组编号(唯一)}";
            }
            else if (excelTable.Columns[9].ColumnName != "数采标识1")
            else if (excelTable[1].Columns[2].ColumnName != "设备组名称")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第3列应为{设备组名称}";
            }
            else if (excelTable[1].Columns[3].ColumnName != "设备类型编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第4列应为{设备类型编号(唯一)}";
            }
            else if (excelTable[1].Columns[4].ColumnName != "设备组描述")
            {
                code = "300";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第5列应为{设备组描述}";
            }
            else if (excelTable[2].Columns[0].ColumnName != "序号")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{序号}";
            }
            else if (excelTable[2].Columns[1].ColumnName != "设备编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备编号(唯一)}";
            }
            else if (excelTable[2].Columns[2].ColumnName != "设备名称")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备名称}";
            }
            else if (excelTable[2].Columns[3].ColumnName != "设备类型编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备类型编号(唯一)}";
            }
            else if (excelTable[2].Columns[4].ColumnName != "设备组编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备组编号(唯一)}";
            }
            else if (excelTable[2].Columns[5].ColumnName != "投入日期")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{投入日期}";
            }
            else if (excelTable[2].Columns[6].ColumnName != "生产车间")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{生产车间}";
            }
            else if (excelTable[2].Columns[7].ColumnName != "使用状态")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{使用状态}";
            }
            else if (excelTable[2].Columns[8].ColumnName != "稼动率（%）")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{稼动率（%）}";
            }
            else
            {
@@ -1643,7 +1698,7 @@
            code = "";
            List<DataTable> excelTable = new List<DataTable>();
            excelTable = ImportExcel.ExcelToTableList(FileCode);
            if (excelTable.Count != 2)
            if (excelTable.Count != 4)
            {
                code = "300";
                Message = "导入模板不符合规范,请检查Sheet数";
@@ -2123,9 +2178,127 @@
            StuCode = "";
            count = 0;
            string sql = "";
            var dynamicParams = new DynamicParameters();
            DataTable dt;
            List<ExcelErro> list = new List<ExcelErro>();

            List<DataTable> excelTable = new List<DataTable>();
            list = ImportExcel.ExcelToThreeTableListErro(FileCode);    //验证Excel数据必填字段是否为空、唯一字段是否重复
            excelTable = ImportExcel.ExcelToThreeTableList(FileCode);  //获取Excel数据
            //设备类型表,判断设备类型是否存在
            for (int i = 0; i < excelTable[0].Rows.Count; i++)
            {
                if (excelTable[0].Rows[i][1].ToString().Trim() != null && excelTable[0].Rows[i][1].ToString().Trim() != "")
                {
                    sql = @"select *  from TEqpType where code=@code";
                    dynamicParams.Add("@code", excelTable[0].Rows[i][1].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count > 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备类型编号(唯一)}";
                        erro.ErrorCont = "设备类型表:{设备类型编号(唯一)}字段" + excelTable[0].Rows[i][1].ToString().Trim() + "已存在";
                        list.Add(erro);
                    }
                }
            }
            //设备组表,判断设备组是否存在
            for (int j = 0; j < excelTable[1].Rows.Count; j++)
            {
                if (excelTable[1].Rows[j][1].ToString().Trim() != null && excelTable[1].Rows[j][1].ToString().Trim() != "")
                {
                    sql = @"select *  from TEqpGroup where code=@code";
                    dynamicParams.Add("@code", excelTable[1].Rows[j][1].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count > 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备组编号(唯一)}";
                        erro.ErrorCont = "设备组表:{设备组编号(唯一)}字段" + excelTable[1].Rows[j][1].ToString().Trim() + "已存在";
                        list.Add(erro);
                    }
                }
            }
            //判断子表外键不存在于主表主键中的数据
            var dt3 = from r in excelTable[1].AsEnumerable()
                      where !(
                          from rr in excelTable[0].AsEnumerable()
                          select rr.Field<string>("设备类型编号(唯一)")
                      ).Contains(r.Field<string>("设备类型编号(唯一)"))
                      select r;
            List<DataRow> listRow = dt3.ToList();
            if (listRow.Count > 0)
            {
                ExcelErro erro = new ExcelErro();
                erro.RoeNumber = "/";
                erro.ErrorField = "{设备类型编号(唯一)}";
                erro.ErrorCont = "设备组信息:{设备类型编号(唯一)}字段中有值在设备类型信息:{设备类型编号(唯一)}中不存在";
                list.Add(erro);
            }
            //设备清单表
            for (int k = 0; k < excelTable[2].Rows.Count; k++)
            {
                if (excelTable[2].Rows[k][1].ToString().Trim() != null && excelTable[2].Rows[k][1].ToString().Trim() != "")
                {
                    sql = @"select *  from TEqpInfo where code=@code";
                    dynamicParams.Add("@code", excelTable[2].Rows[k][1].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count > 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备编号(唯一)}";
                        erro.ErrorCont = "设备表:{设备编号(唯一)}字段" + excelTable[2].Rows[k][1].ToString().Trim() + "已存在";
                        list.Add(erro);
                    }
                    //判断设备组是否对应正确的设备类型
                    var yourGetData = excelTable[1].AsEnumerable().Where(x => (x.Field<string>("设备类型编号(唯一)") == excelTable[2].Rows[k][3].ToString().Trim()) && x.Field<string>("设备组编号(唯一)") == excelTable[2].Rows[k][4].ToString().Trim()).CopyToDataTable();
                    if (yourGetData.Rows.Count <= 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备类型编号(唯一)}/{设备组编号(唯一)}";
                        erro.ErrorCont = "设备表:{设备编号(唯一)}字段" + excelTable[2].Rows[k][1].ToString().Trim() + "对应{设备类型编号(唯一)}:"+ excelTable[2].Rows[k][3].ToString().Trim() + "/{设备组编号(唯一)}:"+ excelTable[2].Rows[k][4].ToString().Trim() + "与设备组清单中设置对不上";
                        list.Add(erro);
                    }
                }
            }
            //车间判断
            for (int m = 0; m < excelTable[1].Rows.Count; m++)
            {
                if (excelTable[1].Rows[m][6].ToString().Trim() != null && excelTable[1].Rows[m][6].ToString().Trim() != "")
                {
                    sql = @"select *  from TOrganization where org_name=@code and description='W'";
                    dynamicParams.Add("@code", excelTable[1].Rows[m][6].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count<=0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{生产车间}";
                        erro.ErrorCont = "设备表:{生产车间}字段" + excelTable[1].Rows[m][6].ToString().Trim() + "不存在";
                        list.Add(erro);
                    }
                }
            }
            if (list.Count > 0)
            {
                int index = 0;
                foreach (ExcelErro item in list)
                {
                    index++;
                    item.Seq = index.ToString();
                }
                StuCode = "301";
                message = "数据验证失败";
            }
            else
            {
                StuCode = "200";
                message = "数据验证成功";
                count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
            }
            return list;
        }
        #endregion
@@ -3063,12 +3236,96 @@
        #region【Excel数据上传,设备清单】
        public static string NineSubmit(string FileCode, string User, out string StuCode)
        {
            string message = "";
            StuCode = "";
            string message = ""; StuCode = "";
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                List<DataTable> excelTable = new List<DataTable>();
                excelTable = ImportExcel.ExcelToThreeTableList(FileCode);
                //写入设备类型
                for (int i = 0; i < excelTable[0].Rows.Count; i++)
                {
                    sql = @"insert into TEqpType(code,name,lm_user,lm_date,remark) 
                            values(@code,@name,@lm_user,@lm_date,@remark)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = excelTable[0].Rows[i][1].ToString().Trim(),
                            name = excelTable[0].Rows[i][2].ToString().Trim(),
                            lm_user = User,
                            lm_date = DateTime.Now.ToString(),
                            remark= excelTable[0].Rows[i][3].ToString().Trim()

            }
                    });
                }
                //写入设备组
                for (int j = 0; j < excelTable[1].Rows.Count; j++)
                {
                    sql = @"insert into TEqpGroup(code,name,lm_user,lm_date,remark,eqptype_code) 
                            values(@code,@name,@lm_user,@lm_date,@remark,@eqptype_code)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = excelTable[1].Rows[j][1].ToString().Trim(),
                            name = excelTable[1].Rows[j][2].ToString().Trim(),
                            lm_user = User,
                            lm_date = DateTime.Now.ToString(),
                            remark = excelTable[1].Rows[j][4].ToString().Trim(),
                            eqptype_code= excelTable[1].Rows[j][3].ToString().Trim()

                        }
                    });
                }
                //导入设备清单
                for (int k = 0; k < excelTable[2].Rows.Count; k++)
                {
                    //获取车间编码
                    string sql0 = @"select code,name from TOrganization where org_name=@wkshp and description='W' and is_delete<>'1'";
                    dynamicParams.Add("@wkshp", excelTable[2].Rows[k][6].ToString());
                    var data = DapperHelper.selectdata(sql0, dynamicParams);

                    sql = @"insert into TEqpInfo(code,name,eqptype_code,eqpgroup_code,wksp_code,input_date,operation_ration,enable,opc_uom,lm_user,lm_date) 
                            values(@devicecode,@devicename,@devicetype,@devicegroup,@workshop,@importdate,@ratio,@status,@uom,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            devicecode = excelTable[2].Rows[k][1].ToString().Trim(),
                            devicename = excelTable[0].Rows[k][2].ToString().Trim(),
                            devicetype = excelTable[0].Rows[k][3].ToString().Trim(),
                            devicegroup = excelTable[0].Rows[k][4].ToString().Trim(),
                            workshop = excelTable[0].Rows[k][6].ToString().Trim(),
                            importdate = excelTable[0].Rows[k][5].ToString().Trim(),
                            ratio = excelTable[0].Rows[k][8].ToString().Trim(),
                            status = excelTable[0].Rows[k][7].ToString().Trim(),
                            uom = "台",
                            lm_user = User,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                }

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    StuCode = "200";
                    message = "导入成功！";
                }
                else
                {
                    StuCode = "300";
                    message = "导入失败！";
                }
            }
            catch (Exception e)
            {
                StuCode = "300";

 VueWebApi/Tools/RedisHelper.cs

@@ -42,9 +42,9 @@
                PooledRedisClientManager prc = new PooledRedisClientManager(readWriteHosts, readOnlyHosts,
                new RedisClientManagerConfig
                {
                    MaxWritePoolSize = readWriteHosts.Length * 5,
                    MaxReadPoolSize = readOnlyHosts.Length * 5,
                    AutoStart = true,
                    MaxWritePoolSize = readWriteHosts.Length * 5,  //最大写链接数
                    MaxReadPoolSize = readOnlyHosts.Length * 5,    //最大读链接数
                    AutoStart = true,                              //自动重启
                }, i, 200, 10);// { RedisClientFactory = (IRedisClientFactory)RedisCacheClientFactory.Instance.CreateRedisClient("127.0.0.1", 6379) }; 
                pools.Add(i, prc);
            }

 VueWebApi/VueWebApi.csproj

@@ -540,6 +540,7 @@
    <Content Include="Excel\设备保养项目.xls" />
    <Content Include="Excel\节拍工价.xls" />
    <Content Include="Excel\物料清单.xls" />
    <Content Include="Excel\设备清单.xls" />
    <None Include="Scripts\jquery-3.4.1.intellisense.js" />
    <Content Include="Scripts\jquery-3.4.1.js" />
    <Content Include="Scripts\jquery-3.4.1.min.js" />

 VueWebApi/bin/VueWebApi.pdb

Binary files differ

 VueWebApi/bin/VueWebApi.xml

@@ -3357,6 +3357,13 @@
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.ImportExcel.ExcelToThreeTableList(System.String)">
            <summary>
            Excel 3个Sheet导入成Datable
            </summary>
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.ImportExcel.GetCellValue(NPOI.SS.UserModel.ICell)">
            <summary>
            èŽ·å–单元格类型
@@ -3378,6 +3385,13 @@
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.ImportExcel.ExcelToThreeTableListErro(System.String)">
            <summary>
            Excel 3个Sheet æ•°æ®éªŒè¯
            </summary>
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.JPushManage.ExecutePushByUserId(System.String,System.String,System.String,System.Collections.Generic.List{VueWebApi.Models.AnDnDate})">
            <summary>
            æŽ¨é€åˆ°ä¸ªäºº,每次推送最多1000人    

 VueWebApi/obj/Release/DesignTimeResolveAssemblyReferencesInput.cache

Binary files differ

 VueWebApi/obj/Release/Package/PackageTmp/Excel/ÎïÁÏÇåµ¥.xls

Binary files differ

 VueWebApi/obj/Release/Package/PackageTmp/Excel/É豸Çåµ¥.xls

Binary files differ

 VueWebApi/obj/Release/Package/PackageTmp/bin/VueWebApi.pdb

Binary files differ

 VueWebApi/obj/Release/Package/PackageTmp/bin/VueWebApi.xml

@@ -3357,6 +3357,13 @@
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.ImportExcel.ExcelToThreeTableList(System.String)">
            <summary>
            Excel 3个Sheet导入成Datable
            </summary>
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.ImportExcel.GetCellValue(NPOI.SS.UserModel.ICell)">
            <summary>
            èŽ·å–单元格类型
@@ -3378,6 +3385,13 @@
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.ImportExcel.ExcelToThreeTableListErro(System.String)">
            <summary>
            Excel 3个Sheet æ•°æ®éªŒè¯
            </summary>
            <param name="file">导入路径(包含文件名与扩展名)</param>
            <returns></returns>
        </member>
        <member name="M:VueWebApi.Tools.JPushManage.ExecutePushByUserId(System.String,System.String,System.String,System.Collections.Generic.List{VueWebApi.Models.AnDnDate})">
            <summary>
            æŽ¨é€åˆ°ä¸ªäºº,每次推送最多1000人    

 VueWebApi/obj/Release/VueWebApi.csproj.AssemblyReference.cache

Binary files differ

 VueWebApi/obj/Release/VueWebApi.csproj.FileListAbsolute.txt

@@ -568,4 +568,3 @@
D:\新凯迪MES\MES产品\VueWebApi\VueWebApi\obj\Release\VueWebApi.csproj.CopyComplete
D:\新凯迪MES\MES产品\VueWebApi\VueWebApi\obj\Release\VueWebApi.dll
D:\新凯迪MES\MES产品\VueWebApi\VueWebApi\obj\Release\VueWebApi.pdb
D:\新凯迪MES\MES产品\VueWebApi\VueWebApi\obj\Release\VueWebApi.csproj.AssemblyReference.cache

 VueWebApi/obj/Release/VueWebApi.pdb

Binary files differ