qlhb/Ropin.Inspection.Api/Controllers/Base/FileImportController.cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using Ropin.Core.Extensions.Redis;
using Ropin.Core.Extensions;
using Ropin.Inspection.Api.Common.Options;
using Ropin.Inspection.Api.Common.Token;
using Ropin.Inspection.Service.Interface;
using Ropin.Inspection.Service.SYS.Interface;
using Ropin.Inspection.Service;
using System.Net.Http;
using Ropin.Inspection.Common.Helper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Ropin.Inspection.Api.Common;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
using log4net;
using System.Collections.Generic;
using System.ComponentModel;
using System;
using Ropin.Inspection.Model.ViewModel.ISP;
using NPOI.Util;
using System.Reflection;
using Ropin.Inspection.Model.ViewModel;
using Ropin.Inspection.Model;
using Ropin.Inspection.Model.SearchModel;
using Ropin.Inspection.Model.Entities;
using Ropin.Inspection.Common.Accessor.Interface;
using System.Security.Claims;
using NPOI.OpenXmlFormats.Dml.Diagram;
using NPOI.SS.Formula.Functions;
using NPOI.SS.Util;
using Newtonsoft.Json;
using System.Xml.Linq;

namespace Ropin.Inspection.Api.Controllers.Base
{
    public class FileImportController : BaseController
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(FileImportController));
        private readonly IClaimsAccessor _claims;
        private readonly ITsysOrganizeService _tsysOrganizeService;
        private readonly ITpntStoreService _TpntStoreService;
        private readonly ITispContentService _tispContentService;
        private readonly ITispContentGroupService _tispContentGroupService;
        private readonly ITispContentGroupItemService _tispContentGroupItemService;
        private readonly ITdevDevStoreService _tdevDevStoreService;

        public FileImportController(IClaimsAccessor claims,ITsysOrganizeService tsysOrganizeService, ITpntStoreService TpntStoreService, ITispContentService tispContentService, ITispContentGroupService tispContentGroupService, ITispContentGroupItemService tispContentGroupItemService, ITdevDevStoreService tdevDevStoreService)
        {
            _claims = claims;
            _tsysOrganizeService = tsysOrganizeService;
            _TpntStoreService = TpntStoreService;
            _tispContentService = tispContentService;
            _tispContentGroupService = tispContentGroupService;
            _tispContentGroupItemService = tispContentGroupItemService;
            _tdevDevStoreService = tdevDevStoreService;
        }

        /// <summary>
        /// 导入巡检内容前的判断
        /// </summary>
        /// <param name="orgCode">组织ID</param>
        /// <param name="storeCode">业主ID</param>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost("JudgmentImportIspContent")]
        public async Task<ApiResult> JudgmentImportIspContent(string orgCode, string storeCode, [Required] IFormFile file)
        {
            if (string.IsNullOrEmpty(orgCode) || string.IsNullOrEmpty(storeCode))
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            if (file == null)
            {
                return new ApiResult(ReturnCode.ArgsError, "请上传导入文件");
            }
            try
            {
                string msg = "";
                Guid OrgId = Guid.Parse(orgCode);
                Guid StoreId = Guid.Parse(storeCode);
                TsysOrganizeViewModel orgEntity = await _tsysOrganizeService.GetByIdAsync(OrgId);
                TpntStoreViewModel storeEntity = await _TpntStoreService.GetByIdAsync(StoreId);
                if (orgEntity == null || storeEntity == null)
                {
                    msg = "业主和组织获取失败";
                    return new ApiResult(ReturnCode.GeneralError, msg);
                }
                List<ISPFIleImport> researchExcels = new List<ISPFIleImport>();
                IWorkbook wk = null;
                var exName = file.FileName.Split('.').Last().ToLower();
                if (exName == "xlsx")
                {
                    wk = new XSSFWorkbook(file.OpenReadStream());
                }
                else
                {
                    wk = new HSSFWorkbook(file.OpenReadStream());
                }
                //获取第一个sheet
                ISheet sheet = wk.GetSheetAt(0);
                //获取第一行-设备
                IRow headrow0 = sheet.GetRow(0);
                //获取第二行-业主
                IRow headrow2 = sheet.GetRow(1);
                //获取第三行-组织
                IRow headrow3 = sheet.GetRow(2);
                IRow headrow4= sheet.GetRow(3);
                IRow headrow5 = sheet.GetRow(4);
                if (headrow0 == null || headrow2 == null || headrow3 == null || headrow4 == null || headrow5 == null)
                {
                    return new ApiResult(ReturnCode.GeneralError, "请确认表格内是否有数据数据");
                }
                string devCell0 = headrow0.Cells[0].ToString();
                var nameCell2 = headrow2.Cells.Where(x => x.ToString() == "使用部门：").FirstOrDefault();
                var valCell2 = headrow2.Cells[2].ToString();
                var nameCell3 = headrow3.Cells.Where(x => x.ToString() == "服务公司：").FirstOrDefault();
                var valCell3 = headrow3.Cells[2].ToString();
                if (nameCell2 is null || nameCell3 is null)
                {
                    msg = "导入格式有误,请使用模板进行导入";
                    return new ApiResult(ReturnCode.ArgsError, msg);
                }
                if ((storeEntity.C_Name.Trim() != valCell2 && storeEntity.C_SName?.Trim() != valCell2) || (orgEntity.C_SName?.Trim() != valCell3 && orgEntity.C_Name.Trim() != valCell3))
                {
                    msg = "业主和组织验证失败";
                    return new ApiResult(ReturnCode.ArgsError, msg);
                }
                if (!string.IsNullOrEmpty(devCell0))
                {
                    var devArray = devCell0.Split("点检记录表").ToList();
                    try
                    {
                        var arr0 = devArray[0].ToString();
                        if (string.IsNullOrEmpty(arr0))
                        {
                            msg = "获取设备名称错误，格式：【XXX点检记录表(设备编号：XXX)】";
                            return new ApiResult(ReturnCode.ArgsError, msg);
                        }
                        TdevDevStoreSearchModel searchDev = new TdevDevStoreSearchModel();
                        searchDev.C_StoreCode = storeCode;
                        searchDev.C_Name = arr0;
                        var DevData = await _tdevDevStoreService.GetConditionAsync(searchDev);
                        if (DevData == null || DevData.Count() == 0 || (DevData.Count() == 1 && DevData.First() == null) || DevData.Count() > 1)
                        {
                            msg =  $"请确认设备【{arr0}】名称是否正确或者是否存在于当前业主【{valCell2}】下";
                            return new ApiResult(ReturnCode.ArgsError, msg);
                        }
                        var devModel = DevData.FirstOrDefault();
                        msg = $"请确认是否导入数据到 组织：{valCell3}，业主：{valCell2}，设备：{devModel.C_Name}【{devModel.C_NumberCode}】 下吗？";
                        var data = new
                        {
                            devCode = devModel.C_ID,
                            Message=msg,
                        };
                        return new ApiResult<object>(data, ReturnCode.Success);
                    }
                    catch (Exception)
                    {
                        msg = "获取首行设备错误";
                        return new ApiResult(ReturnCode.GeneralError, msg);
                    }
                }
                else
                {
                    msg = "请填写表格表头，格式：【XXX点检记录表（设备编号：XXX）】";
                    return new ApiResult(ReturnCode.ArgsError, msg);
                }
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, "导入模版格式不对！");
            }
        }
        /// <summary>
        /// 导入巡检内容
        /// </summary>
        /// <param name="orgCode"></param>
        /// <param name="storeCode"></param>
        /// <param name="devCode">设备ID</param>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost("ImportIspContent")]
        public async Task<ApiResult> ImportIspContent(string orgCode, string storeCode, string devCode, [Required] IFormFile file)
        {
            if (string.IsNullOrEmpty(orgCode) || string.IsNullOrEmpty(storeCode))
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            if (file == null)
            {
                return new ApiResult(ReturnCode.ArgsError,"请上传导入文件");
            }
            try
            {
                List<ISPFIleImport> researchExcels = new List<ISPFIleImport>();
                IWorkbook wk = null;
                var exName = file.FileName.Split('.').Last().ToLower();
                if (exName == "xlsx")
                {
                    wk = new XSSFWorkbook(file.OpenReadStream());
                }
                else
                {
                    wk = new HSSFWorkbook(file.OpenReadStream());
                }
                //获取第一个sheet
                ISheet sheet = wk.GetSheetAt(0);
                // 存储已处理的合并区域
                List<CellRangeAddress> processedMergedRegions = new List<CellRangeAddress>();
                //获取第四行-标题行
                IRow headrow4 = sheet.GetRow(3);
                var researchExcel = new ISPFIleImport();
                var units = new List<string>();
                //读取每行,从第五行起
                for (int r = 4; r <= sheet.LastRowNum; r++)
                {
                    var copyExcel = researchExcel.Copy();
                    //获取当前行
                    IRow row = sheet.GetRow(r);
                    // 判断当前行是否有内容
                    bool hasContent = row != null && row.Cells.Any(cell =>
                    {
                        if (cell == null) return false;
                        var cellValue = cell.ToString().Trim();
                        return !string.IsNullOrEmpty(cellValue);
                    });

                    if (!hasContent)
                    {
                        // 如果当前行没有内容，跳过当前循环
                        continue;
                    }
                    //读取每列
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        ICell cell = row.GetCell(j); //一个单元格
                        var cellValue = cell.ToString();
                        var properties = copyExcel.GetType().GetProperties().ToList();
                        // 检查单元格是否在合并区域内
                        bool isMerged = false; bool isNewMergedRegion = false;
                        CellRangeAddress currentMergedRegion = null;
                        for (int i = 0; i < sheet.NumMergedRegions; i++)
                        {
                            CellRangeAddress mergedRegion = sheet.GetMergedRegion(i);
                            if (mergedRegion.FirstRow <= r && mergedRegion.LastRow >= r && mergedRegion.FirstColumn <= j && mergedRegion.LastColumn >= j)
                            {
                                isMerged = true;
                                currentMergedRegion = mergedRegion;
                                break;
                            }
                        }
                        if (isMerged && r > 1)
                        {
                            // 判断是否为新的合并区域
                            isNewMergedRegion = !processedMergedRegions.Contains(currentMergedRegion);
                            if (isNewMergedRegion)
                            {
                                // 若为新的合并区域，添加到已处理列表
                                processedMergedRegions.Add(currentMergedRegion);
                            }
                        }
                        foreach (var x in properties)
                        {
                            if (x.IsDefined(typeof(DescriptionAttribute)))
                            {
                                //如果是合并行且不是第一列(第一列要拿数据的) cell.IsMergedCell
                                if (isMerged && r > 1)
                                {
                                    var value = x.GetValue(copyExcel)?.ToString();
                                    //新行不是空且与旧值不想等,则重新赋值.合并行后当前行数据是空.
                                    if ((!string.IsNullOrEmpty(cellValue) && cellValue != value) || isNewMergedRegion)
                                    {
                                        var attribute = x.GetCustomAttribute<DescriptionAttribute>();
                                        if (attribute.Description == headrow4.GetCell(j).ToString())
                                        {
                                            x.SetValue(copyExcel, cellValue);
                                            researchExcel = copyExcel;
                                            continue;
                                        }
                                    }
                                }
                                else
                                {
                                    var attribute = x.GetCustomAttribute<DescriptionAttribute>();
                                    if (attribute.Description == headrow4.GetCell(j).ToString())
                                    {
                                        x.SetValue(copyExcel, cellValue);
                                        continue;
                                    }
                                }
                            }
                        }
                    }
                    researchExcel = copyExcel;
                    if (string.IsNullOrEmpty(copyExcel.Column4))
                    {
                        throw new Exception("巡检内容不能为空");
                    }
                    var copyExcel0 = copyExcel.Copy();
                    // 检查 Column0 是否已存在
                    var IsExist = researchExcels.Where(excel => excel.Column0.StartsWith(copyExcel.Column0)).ToList();
                    if (IsExist != null && IsExist.Count > 0)
                    {
                        copyExcel0.Column0 += "-" + IsExist.Count;
                    }
                    researchExcels.Add(copyExcel0);
                }
                var content = researchExcels.Select(x => new { x.Column0, x.Column4, x.Column5 }).Distinct().ToList();
                var contentGroup = researchExcels.Select(x => new { x.Column1, x.Column2, x.Column3, x.Column6 }).Distinct().ToList();
                var contentList = await _tispContentService.GetAllAsync(storeCode);
                TispContentGroupsSearchModel searchModel = new TispContentGroupsSearchModel();
                searchModel.C_StoreCode = storeCode;
                var contentGroupList = await _tispContentGroupService.GetContentGroupsAsync(searchModel);
                List<TISP_Content> contentArray = new List<TISP_Content>();
                List<TISP_ContentGroup> contentGroupArray = new List<TISP_ContentGroup>();
                List<TISP_ContentGroupItem> groupItemArray = new List<TISP_ContentGroupItem>();
                foreach (var item in content)
                {
                    int sort = 0;
                    if (!string.IsNullOrEmpty(item.Column0))
                    {
                        sort = UtilConvert.GetIntByStr(item.Column0);
                    }
                    var contentModel = contentList.Where(c => c.C_Name == item.Column4).FirstOrDefault();
                    if (contentModel == null)
                    {
                        TISP_Content contentAdd = new TISP_Content
                        {
                            C_ID = Guid.NewGuid(),
                            C_StoreCode = storeCode,
                            C_Name = item.Column4,
                            C_AlarmLevel = "1",//1=轻;2 = 中;3 = 重
                            C_Number = item.Column0,
                            I_Sort = sort,
                            C_Remark = item.Column5,
                            C_CreateBy = _claims.ApiUserId,
                            D_CreateOn = DateTime.Now,
                            C_Status = "1",
                        };
                        contentArray.Add(contentAdd);
                    }
                }
                int sortNum = 0;
                foreach (var item in contentGroup)
                {
                    int type = 1;
                    //1=防火检查记录表（周）;2 = 隐患整改验收单;3 = 消防设施每周检查记录;4 = 防火巡查记录表
                    switch (item.Column1)
                    {
                        case "防火检查记录表（周）": type = 1; break;
                        case "隐患整改验收单": type = 2; break;
                        case "消防设施每周检查记录": type = 3; break;
                        case "防火巡查记录表": type = 4; break;
                        case "点检工单记录表": type = 5; break;
                    }
                    var GroupModel = contentGroupList.Where(g => g.C_Name == item.Column2 && g.I_Type == type && g.C_PatrolItem == item.Column3).FirstOrDefault();
                    TISP_ContentGroup contentGroupAdd = null;
                    if (GroupModel == null)
                    {
                        contentGroupAdd = new TISP_ContentGroup
                        {
                            G_ID = Guid.NewGuid(),
                            C_StoreCode = storeCode,
                            C_DevStoreCode= devCode,
                            C_Name = item.Column2,
                            I_Sort = sortNum,
                            C_Remark = item.Column6,
                            I_Type = type,
                            C_PatrolItem = item.Column3,
                            G_CreateBy = _claims.ApiUserId,
                            D_CreateOn = DateTime.Now,
                            C_Status = "1",
                        };
                        contentGroupArray.Add(contentGroupAdd);
                        sortNum++;
                    }
                }
                foreach (var item in researchExcels)
                {
                    int type = 1;
                    //1=防火检查记录表（周）;2 = 隐患整改验收单;3 = 消防设施每周检查记录;4 = 防火巡查记录表
                    switch (item.Column1)
                    {
                        case "防火检查记录表（周）": type = 1; break;
                        case "隐患整改验收单": type = 2; break;
                        case "消防设施每周检查记录": type = 3; break;
                        case "防火巡查记录表": type = 4; break;
                        case "点检工单记录表": type = 5; break;
                    }
                    Guid contentGroupId = new Guid(), contentId = new Guid();
                    if (contentArray.Count > 0)
                    {
                        var ccmodel = contentArray.Where(c => c.C_Name == item.Column4).FirstOrDefault();
                        if (ccmodel != null) { contentId = ccmodel.C_ID; }
                    }
                    if (contentGroupArray.Count > 0)
                    {
                        var cgModel = contentGroupArray.Where(g => g.C_Name == item.Column2 && g.I_Type == type && g.C_PatrolItem == item.Column3).FirstOrDefault();
                        if (cgModel!=null) { contentGroupId = cgModel.G_ID; }
                        
                    }
                    if (Guid.Empty == contentId && contentList.Count() > 0)
                    {
                        var ccModel = contentList.Where(c => c.C_Name == item.Column4).FirstOrDefault();
                        if (ccModel != null) { contentId = ccModel.C_ID; }
                    }
                    if (Guid.Empty == contentGroupId && contentGroupList.Count() > 0)
                    {
                         var cgModel= contentGroupList.Where(g => g.C_Name == item.Column2 && g.I_Type == type && g.C_PatrolItem == item.Column3).FirstOrDefault();
                        if (cgModel != null) { contentGroupId = cgModel.G_ID; }
                    }
                    if (Guid.Empty != contentId && Guid.Empty != contentGroupId)
                    {
                        var ItemList = await _tispContentGroupItemService.GetContentGroupItemByIdAsync(contentGroupId, contentId);
                        if (ItemList == null || ItemList.Count() == 0)
                        {
                            var groupItemAdd = new TISP_ContentGroupItem
                            {
                                G_ID = Guid.NewGuid(),
                                G_ContentGroupCode = contentGroupId,
                                G_ContentCode = contentId
                            };
                            groupItemArray.Add(groupItemAdd);
                        }
                    }
                }
                if (contentArray.Count == 0 && contentGroupArray.Count == 0 && groupItemArray.Count == 0)
                {
                    return new ApiResult(ReturnCode.GeneralError, "要导入的数据已经存在！");
                }
                else
                {
                    bool result = await _tispContentService.CreateIspContent(contentArray, contentGroupArray, groupItemArray);
                    if (result)
                    {
                        return new ApiResult(ReturnCode.Success);
                    }
                    else
                    {
                        return new ApiResult(ReturnCode.GeneralError);
                    }
                }
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError,ex.Message);
            }
        }
    }
}