using NCC.Common;
using NCC.Common.Configuration;
using NCC.Common.Core.Manager;
using NCC.Common.Enum;
using NCC.Common.Extension;
using NCC.Common.Filter;
using NCC.Common.Helper;
using NCC.Common.Model.NPOI;
using NCC.Dependency;
using NCC.DynamicApiController;
using NCC.FriendlyException;
using NCC.Extend.Interfaces.Zlgl;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using NCC.Extend.Entitys;
using NCC.Extend.Entitys.Dto.Zlgl;
using NCC.System.Entitys.System;
using Yitter.IdGenerator;
using NCC.DataEncryption;
using NCC.JsonSerialization;
using NCC.System.Entitys.Model.Permission.User;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace NCC.Extend.Zlgl
{
    /// <summary>
    /// 资料管理服务
    /// </summary>
    [ApiDescriptionSettings(Tag = "Extend",Name = "Zlgl", Order = 200)]
    [Route("api/Extend/[controller]")]
    public class ZlglService : IZlglService, IDynamicApiController, ITransient
    {
        private readonly ISqlSugarRepository<ZlglEntity> _zlglRepository;
        private readonly SqlSugarScope _db;
        private readonly IUserManager _userManager;

        /// <summary>
        /// 初始化一个<see cref="ZlglService"/>类型的新实例
        /// </summary>
        public ZlglService(
            ISqlSugarRepository<ZlglEntity> zlglRepository,
            IUserManager userManager)
        {
            _zlglRepository = zlglRepository;            
            _db = _zlglRepository.Context;
            _userManager = userManager;
        }

        /// <summary>
        /// 获取资料管理
        /// </summary>
        /// <param name="id">参数</param>
        /// <returns></returns>
        [HttpGet("{id}")]
        public async Task<dynamic> GetInfo(string id)
        {
            var entity = await _db.Queryable<ZlglEntity>().FirstAsync(p => p.Id == id);
            var output = entity.Adapt<ZlglInfoOutput>();
            return output;
        }

        /// <summary>
		/// 获取资料管理列表
		/// </summary>
		/// <param name="input">请求参数</param>
		/// <returns></returns>
        [HttpGet("")]
        public async Task<dynamic> GetList([FromQuery] ZlglListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryFbsj = input.fbsj != null ? input.fbsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startFbsj = queryFbsj != null ? Ext.GetDateTime(queryFbsj.First()) : null;
            DateTime? endFbsj = queryFbsj != null ? Ext.GetDateTime(queryFbsj.Last()) : null;
            List<string> queryCcrq = input.ccrq != null ? input.ccrq.Split(',').ToObeject<List<string>>() : null;
            DateTime? startCcrq = queryCcrq != null ? Ext.GetDateTime(queryCcrq.First()) : null;
            DateTime? endCcrq = queryCcrq != null ? Ext.GetDateTime(queryCcrq.Last()) : null;
            var data = await _db.Queryable<ZlglEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.zlm), p => p.Zlm.Contains(input.zlm))
                .WhereIF(!string.IsNullOrEmpty(input.sbbh), p => p.Sbbh.Contains(input.sbbh))
                .WhereIF(!string.IsNullOrEmpty(input.gg), p => p.Gg.Contains(input.gg))
                .WhereIF(!string.IsNullOrEmpty(input.fl), p => p.Fl.Equals(input.fl))
                .WhereIF(queryFbsj != null, p => p.Fbsj >= new DateTime(startFbsj.ToDate().Year, startFbsj.ToDate().Month, startFbsj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryFbsj != null, p => p.Fbsj <= new DateTime(endFbsj.ToDate().Year, endFbsj.ToDate().Month, endFbsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.bh), p => p.Bh.Contains(input.bh))
                .WhereIF(!string.IsNullOrEmpty(input.ccbh), p => p.Ccbh.Contains(input.ccbh))
                .WhereIF(queryCcrq != null, p => p.Ccrq >= new DateTime(startCcrq.ToDate().Year, startCcrq.ToDate().Month, startCcrq.ToDate().Day, 0, 0, 0))
                .WhereIF(queryCcrq != null, p => p.Ccrq <= new DateTime(endCcrq.ToDate().Year, endCcrq.ToDate().Month, endCcrq.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.th), p => p.Th.Contains(input.th))
                .Select(it=> new ZlglListOutput
                {
                    id = it.Id,
                    zlm=it.Zlm,
                    sbbh=it.Sbbh,
                    gg=it.Gg,
                    fl=it.Fl,
                    fl1 = SqlFunc.Subqueryable<DictionaryDataEntity>().Where(x => x.Id == it.Fl).Select(x => x.FullName),
                    fbsj=it.Fbsj,
                    bh=it.Bh,
                    ccbh=it.Ccbh,
                    ccrq=it.Ccrq,
                    th=it.Th,
                    fj1 = it.Fj1,
                    fj2 = it.Fj2,
                    jszl = it.Jszl,
                    zlzl = it.Zlzl,
                    qtzl = it.Qtzl,
                    xctp = it.Xctp,
                    sbwhry = it.Sbwhry
                }).MergeTable().OrderBy(sidx+" "+input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
                return PageResult<ZlglListOutput>.SqlSugarPageResult(data);
        }

        /// <summary>
        /// 新建资料管理
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPost("")]
        public async Task Create([FromBody] ZlglCrInput input)
        {
            var userInfo = await _userManager.GetUserInfo();
            var entity = input.Adapt<ZlglEntity>();
            entity.Id = YitIdHelper.NextId().ToString();
            var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1000);
        }

        /// <summary>
        /// 更新资料管理
        /// </summary>
        /// <param name="id">主键</param>
        /// <param name="input">参数</param>
        /// <returns></returns>
        [HttpPut("{id}")]
        public async Task Update(string id, [FromBody] ZlglUpInput input)
        {
            var entity = input.Adapt<ZlglEntity>();
            entity.Id = id; // 确保设置主键
            // 显式指定要更新的列，确保 sbwhry 字段被更新（包括 null 值也要更新）
            var isOk = await _db.Updateable(entity)
                .Where(it => it.Id == id)
                .UpdateColumns(it => new { 
                    it.Zlm, it.Sbbh, it.Gg, it.Fl, it.Fbsj, it.Bh, it.Ccbh, it.Ccrq, 
                    it.Sbjs, it.Fj1, it.Fj2, it.Jszl, it.Zlzl, it.Qtzl, it.Xctp, it.Th, it.Sbwhry 
                })
                .ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1001);
        }

        /// <summary>
        /// 删除资料管理
        /// </summary>
        /// <returns></returns>
        [HttpDelete("{id}")]
        public async Task Delete(string id)
        {
            var entity = await _db.Queryable<ZlglEntity>().FirstAsync(p => p.Id == id);
            _ = entity ?? throw NCCException.Oh(ErrorCode.COM1005);
            var isOk = await _db.Deleteable<ZlglEntity>().Where(d => d.Id == id).ExecuteCommandAsync();
            if (!(isOk > 0)) throw NCCException.Oh(ErrorCode.COM1002);
        }

        /// <summary>
        /// 根据关键词模糊搜索资料管理（分页）
        /// </summary>
        /// <remarks>
        /// 根据关键词在所有字段中进行模糊匹配搜索，支持搜索的字段包括：编号、资料名、设备编号、规格、编号、出厂编号、图号、设备维护人员、分类
        /// </remarks>
        /// <param name="keyword">搜索关键词，将在所有字符串字段中进行模糊匹配</param>
        /// <param name="currentPage">当前页码，默认值为1</param>
        /// <param name="pageSize">每页数量，默认值为20</param>
        /// <returns>返回分页的资料管理列表</returns>
        [HttpGet("Actions/SearchByKeyword")]
        public async Task<dynamic> SearchByKeyword([FromQuery] string keyword, [FromQuery] int currentPage = 1, [FromQuery] int pageSize = 20)
        {
            if (string.IsNullOrEmpty(keyword))
            {
                return PageResult<ZlglListOutput>.SqlSugarPageResult(new SqlSugarPagedList<ZlglListOutput>
                {
                    list = new List<ZlglListOutput>(),
                    pagination = new PagedModel
                    {
                        PageIndex = currentPage,
                        PageSize = pageSize,
                        Total = 0
                    }
                });
            }
            var data = await _db.Queryable<ZlglEntity>()
                .Where(p => p.Id.Contains(keyword) 
                    || (p.Zlm != null && p.Zlm.Contains(keyword))
                    || (p.Sbbh != null && p.Sbbh.Contains(keyword))
                    || (p.Gg != null && p.Gg.Contains(keyword))
                    || (p.Bh != null && p.Bh.Contains(keyword))
                    || (p.Ccbh != null && p.Ccbh.Contains(keyword))
                    || (p.Th != null && p.Th.Contains(keyword))
                    || (p.Sbwhry != null && p.Sbwhry.Contains(keyword))
                    || (p.Fl != null && p.Fl.Contains(keyword)))
                .Select(it => new ZlglListOutput
                {
                    id = it.Id,
                    zlm = it.Zlm,
                    sbbh = it.Sbbh,
                    gg = it.Gg,
                    fl = it.Fl,
                    fl1 = SqlFunc.Subqueryable<DictionaryDataEntity>().Where(x => x.Id == it.Fl).Select(x => x.FullName),
                    fbsj = it.Fbsj,
                    bh = it.Bh,
                    ccbh = it.Ccbh,
                    ccrq = it.Ccrq,
                    th = it.Th,
                    jszl = it.Jszl,
                    zlzl = it.Zlzl,
                    qtzl = it.Qtzl,
                    xctp = it.Xctp,
                    sbwhry = it.Sbwhry
                }).MergeTable().OrderBy("id desc").ToPagedListAsync(currentPage, pageSize);
            return PageResult<ZlglListOutput>.SqlSugarPageResult(data);
        }

        /// <summary>
        /// 根据关键词模糊搜索资料管理（不分页）
        /// </summary>
        /// <remarks>
        /// 根据关键词在所有字段中进行模糊匹配搜索，支持搜索的字段包括：编号、资料名、设备编号、规格、编号、出厂编号、图号、设备维护人员、分类
        /// </remarks>
        /// <param name="keyword">搜索关键词，将在所有字符串字段中进行模糊匹配</param>
        /// <returns>返回所有匹配的资料管理列表（不分页）</returns>
        [HttpGet("Actions/SearchByKeywordNoPaging")]
        public async Task<dynamic> SearchByKeywordNoPaging([FromQuery] string keyword)
        {
            if (string.IsNullOrEmpty(keyword))
            {
                return new List<ZlglListOutput>();
            }
            var data = await _db.Queryable<ZlglEntity>()
                .Where(p => p.Id.Contains(keyword) 
                    || (p.Zlm != null && p.Zlm.Contains(keyword))
                    || (p.Sbbh != null && p.Sbbh.Contains(keyword))
                    || (p.Gg != null && p.Gg.Contains(keyword))
                    || (p.Bh != null && p.Bh.Contains(keyword))
                    || (p.Ccbh != null && p.Ccbh.Contains(keyword))
                    || (p.Th != null && p.Th.Contains(keyword))
                    || (p.Sbwhry != null && p.Sbwhry.Contains(keyword))
                    || (p.Fl != null && p.Fl.Contains(keyword)))
                .Select(it => new ZlglListOutput
                {
                    id = it.Id,
                    zlm = it.Zlm,
                    sbbh = it.Sbbh,
                    gg = it.Gg,
                    fl = it.Fl,
                    fl1 = SqlFunc.Subqueryable<DictionaryDataEntity>().Where(x => x.Id == it.Fl).Select(x => x.FullName),
                    fbsj = it.Fbsj,
                    bh = it.Bh,
                    ccbh = it.Ccbh,
                    ccrq = it.Ccrq,
                    th = it.Th,
                    jszl = it.Jszl,
                    zlzl = it.Zlzl,
                    qtzl = it.Qtzl,
                    xctp = it.Xctp,
                    sbwhry = it.Sbwhry
                }).MergeTable().OrderBy("id desc").ToListAsync();
            return data;
        }

        /// <summary>
        /// 资料管理简单搜索（仅匹配资料名称、资产编码、所属设备、关联产品）
        /// </summary>
        /// <param name="input">请求参数</param>
        /// <returns></returns>
        [HttpGet("SimpleSearch")]
        public async Task<dynamic> GetSimpleSearchList([FromQuery] ZlglSimpleSearchInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            var query = _db.Queryable<ZlglEntity>();
            
            // 只有当searchKey不为空时才应用搜索条件
            if (!string.IsNullOrEmpty(input.searchKey))
            {
                var searchKey = input.searchKey.Trim();
                // 使用 EXISTS 子查询来匹配关联产品名称，避免先查询所有产品ID
                // 注意：参数名必须与 Select 中的参数名一致，都使用 it
                query = query.Where(it => 
                    (it.Zlm != null && it.Zlm.Contains(searchKey)) ||
                    (it.Sbbh != null && it.Sbbh.Contains(searchKey)) || // 资产编码
                    (it.Sbjs != null && it.Sbjs.Contains(searchKey)) ||
                    SqlFunc.Subqueryable<CpglEntity>().Where(x => x.Id == it.Fl && x.Cpmc != null && x.Cpmc.Contains(searchKey)).Any());
            }
            
            // 关联产品筛选
            if (!string.IsNullOrEmpty(input.fl))
            {
                query = query.Where(it => it.Fl.Equals(input.fl));
            }
            
            // 客户权限：仅显示该客户在“我的设备”中已有设备关联产品的资料（传入 sskhbh 时生效；管理员不传则看全部）
            // 使用与 KhsbPermissionHelper 一致的 sskhbh 扩展逻辑：当前用户可看 [userId, 所属组织, 上级组织] 下的设备
            if (!string.IsNullOrEmpty(input.sskhbh))
            {
                var allowedSskhbhIds = await KhsbPermissionHelper.GetUserAllowedSskhbhIdsAsync(_userManager, _db);
                if (allowedSskhbhIds == null)
                {
                    // 管理员：不限制客户
                }
                else if (allowedSskhbhIds.Count == 0)
                {
                    query = query.Where(it => false);
                }
                else
                {
                    // 获取该用户可查看的设备所关联的产品ID：优先 Khsb.Fl，其次通过 Khsb.Dysbbh=cpgl.Cpid 关联
                    var productIdsFromFl = await _db.Queryable<KhsbEntity>()
                        .Where(k => k.Sskhbh != null && allowedSskhbhIds.Contains(k.Sskhbh))
                        .Where(k => k.Fl != null && k.Fl != "")
                        .Select(k => k.Fl)
                        .Distinct()
                        .ToListAsync();
                    var productIdsFromDysbbh = await _db.Queryable<KhsbEntity>()
                        .InnerJoin<CpglEntity>((k, c) => k.Dysbbh != null && k.Dysbbh != "" && k.Dysbbh == c.Cpid)
                        .Where((k, c) => k.Sskhbh != null && allowedSskhbhIds.Contains(k.Sskhbh))
                        .Select((k, c) => c.Id)
                        .Distinct()
                        .ToListAsync();
                    var allowedProductIds = (productIdsFromFl ?? new List<string>())
                        .Union(productIdsFromDysbbh ?? new List<string>())
                        .Distinct()
                        .ToList();
                    if (allowedProductIds == null || allowedProductIds.Count == 0)
                        query = query.Where(it => false);
                    else
                        query = query.Where(it => it.Fl != null && allowedProductIds.Contains(it.Fl));
                }
            }
            
            var data = await query
                .Select(it => new ZlglListOutput
                {
                    id = it.Id,
                    zlm = it.Zlm,
                    sbbh = it.Sbbh,
                    gg = it.Gg,
                    fl = it.Fl,
                    fl1 = SqlFunc.Subqueryable<CpglEntity>().Where(x => x.Id == it.Fl).Select(x => x.Cpmc), // 关联产品名称
                    fbsj = it.Fbsj,
                    bh = it.Bh,
                    ccbh = it.Ccbh,
                    ccrq = it.Ccrq,
                    th = it.Th,
                    jszl = it.Jszl,
                    zlzl = it.Zlzl,
                    qtzl = it.Qtzl,
                    xctp = it.Xctp,
                    sbwhry = it.Sbwhry
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToPagedListAsync(input.currentPage, input.pageSize);
            return PageResult<ZlglListOutput>.SqlSugarPageResult(data);
        }

        #region 导入导出与模板

        /// <summary>
        /// 获取资料管理列表（不分页，用于导出）
        /// </summary>
        [HttpGet("GetNoPagingList")]
        public async Task<List<ZlglListOutput>> GetNoPagingList([FromQuery] ZlglListQueryInput input)
        {
            var sidx = input.sidx == null ? "id" : input.sidx;
            List<string> queryFbsj = input.fbsj != null ? input.fbsj.Split(',').ToObeject<List<string>>() : null;
            DateTime? startFbsj = queryFbsj != null ? Ext.GetDateTime(queryFbsj.First()) : null;
            DateTime? endFbsj = queryFbsj != null ? Ext.GetDateTime(queryFbsj.Last()) : null;
            List<string> queryCcrq = input.ccrq != null ? input.ccrq.Split(',').ToObeject<List<string>>() : null;
            DateTime? startCcrq = queryCcrq != null ? Ext.GetDateTime(queryCcrq.First()) : null;
            DateTime? endCcrq = queryCcrq != null ? Ext.GetDateTime(queryCcrq.Last()) : null;
            var data = await _db.Queryable<ZlglEntity>()
                .WhereIF(!string.IsNullOrEmpty(input.zlm), p => p.Zlm.Contains(input.zlm))
                .WhereIF(!string.IsNullOrEmpty(input.sbbh), p => p.Sbbh.Contains(input.sbbh))
                .WhereIF(!string.IsNullOrEmpty(input.gg), p => p.Gg.Contains(input.gg))
                .WhereIF(!string.IsNullOrEmpty(input.fl), p => p.Fl.Equals(input.fl))
                .WhereIF(queryFbsj != null, p => p.Fbsj >= new DateTime(startFbsj.ToDate().Year, startFbsj.ToDate().Month, startFbsj.ToDate().Day, 0, 0, 0))
                .WhereIF(queryFbsj != null, p => p.Fbsj <= new DateTime(endFbsj.ToDate().Year, endFbsj.ToDate().Month, endFbsj.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.bh), p => p.Bh.Contains(input.bh))
                .WhereIF(!string.IsNullOrEmpty(input.ccbh), p => p.Ccbh.Contains(input.ccbh))
                .WhereIF(queryCcrq != null, p => p.Ccrq >= new DateTime(startCcrq.ToDate().Year, startCcrq.ToDate().Month, startCcrq.ToDate().Day, 0, 0, 0))
                .WhereIF(queryCcrq != null, p => p.Ccrq <= new DateTime(endCcrq.ToDate().Year, endCcrq.ToDate().Month, endCcrq.ToDate().Day, 23, 59, 59))
                .WhereIF(!string.IsNullOrEmpty(input.th), p => p.Th.Contains(input.th))
                .Select(it => new ZlglListOutput
                {
                    id = it.Id,
                    zlm = it.Zlm,
                    sbbh = it.Sbbh,
                    gg = it.Gg,
                    fl = it.Fl,
                    fbsj = it.Fbsj,
                    bh = it.Bh,
                    ccbh = it.Ccbh,
                    ccrq = it.Ccrq,
                    th = it.Th,
                    fj1 = it.Fj1,
                    fj2 = it.Fj2,
                    jszl = it.Jszl,
                    zlzl = it.Zlzl,
                    qtzl = it.Qtzl,
                    xctp = it.Xctp,
                    sbwhry = it.Sbwhry
                }).MergeTable().OrderBy(sidx + " " + input.sort).ToListAsync();
            return data;
        }

        /// <summary>
        /// 导出资料管理（传 ids 按 id 导出，否则按当前查询条件导出；关联产品解析为产品名称）
        /// </summary>
        [HttpGet("Actions/Export")]
        public async Task<dynamic> Export([FromQuery] ZlglListQueryInput input)
        {
            var exportData = new List<ZlglListOutput>();
            if (!string.IsNullOrEmpty(input.ids))
            {
                var idList = input.ids.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(s => s.Trim()).Where(s => !string.IsNullOrEmpty(s)).ToList();
                if (idList.Count > 0)
                {
                    var list = await _db.Queryable<ZlglEntity>()
                        .Where(p => idList.Contains(p.Id))
                        .Select(it => new ZlglListOutput
                        {
                            id = it.Id,
                            zlm = it.Zlm,
                            sbbh = it.Sbbh,
                            gg = it.Gg,
                            fl = it.Fl,
                            fbsj = it.Fbsj,
                            bh = it.Bh,
                            ccbh = it.Ccbh,
                            ccrq = it.Ccrq,
                            th = it.Th,
                            fj1 = it.Fj1,
                            fj2 = it.Fj2,
                            jszl = it.Jszl,
                            zlzl = it.Zlzl,
                            qtzl = it.Qtzl,
                            xctp = it.Xctp,
                            sbwhry = it.Sbwhry
                        }).ToListAsync();
                    exportData = list;
                }
            }
            else
            {
                exportData = await GetNoPagingList(input);
            }

            // 关联产品 fl 解析为产品名称（cpgl.Cpmc）
            var flIds = exportData.Where(x => !string.IsNullOrEmpty(x.fl)).Select(x => x.fl).Distinct().ToList();
            var cpglDict = flIds.Count > 0
                ? (await _db.Queryable<CpglEntity>().Where(c => flIds.Contains(c.Id)).Select(c => new { c.Id, c.Cpmc }).ToListAsync()).ToDictionary(c => c.Id, c => c.Cpmc ?? "")
                : new Dictionary<string, string>();
            // 设备维护人员 sbwhry 为逗号分隔的 ID，导出时解析为姓名，多人用分号隔开
            var sbwhryIds = new List<string>();
            foreach (var row in exportData)
            {
                if (!string.IsNullOrEmpty(row.sbwhry))
                    sbwhryIds.AddRange(row.sbwhry.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(s => s.Trim()).Where(s => !string.IsNullOrEmpty(s)));
            }
            sbwhryIds = sbwhryIds.Distinct().ToList();
            var sbwhryDict = sbwhryIds.Count > 0
                ? (await _db.Queryable<SbwhryEntity>().Where(s => sbwhryIds.Contains(s.Id)).Select(s => new { s.Id, s.Xm }).ToListAsync()).ToDictionary(s => s.Id, s => s.Xm ?? "")
                : new Dictionary<string, string>();
            foreach (var row in exportData)
            {
                if (!string.IsNullOrEmpty(row.fl) && cpglDict.TryGetValue(row.fl, out var cpmc))
                    row.fl1 = cpmc;
                if (!string.IsNullOrEmpty(row.sbwhry))
                {
                    var names = row.sbwhry.Split(',', StringSplitOptions.RemoveEmptyEntries)
                        .Select(id => id.Trim())
                        .Where(id => !string.IsNullOrEmpty(id))
                        .Select(id => sbwhryDict.TryGetValue(id, out var xm) ? xm : id)
                        .Where(n => !string.IsNullOrEmpty(n));
                    row.sbwhry = string.Join(";", names);
                }
            }

            var excelconfig = new ExcelConfig
            {
                FileName = "资料管理.xls",
                HeadFont = "微软雅黑",
                HeadPoint = 10,
                IsAllSizeColumn = true,
                ColumnModel = new List<ExcelColumnModel>
                {
                    new ExcelColumnModel { Column = "id", ExcelColumn = "id" },
                    new ExcelColumnModel { Column = "zlm", ExcelColumn = "资料名称" },
                    new ExcelColumnModel { Column = "sbbh", ExcelColumn = "设备编号" },
                    new ExcelColumnModel { Column = "gg", ExcelColumn = "规格" },
                    new ExcelColumnModel { Column = "fl1", ExcelColumn = "关联产品" },
                    new ExcelColumnModel { Column = "fbsj", ExcelColumn = "发布时间" },
                    new ExcelColumnModel { Column = "bh", ExcelColumn = "编号" },
                    new ExcelColumnModel { Column = "ccbh", ExcelColumn = "出厂编号" },
                    new ExcelColumnModel { Column = "ccrq", ExcelColumn = "出厂日期" },
                    new ExcelColumnModel { Column = "th", ExcelColumn = "设备图号" },
                    new ExcelColumnModel { Column = "sbwhry", ExcelColumn = "设备维护人员" }
                }
            };
            var addPath = FileVariable.TemporaryFilePath + excelconfig.FileName;
            ExcelExportHelper<ZlglListOutput>.Export(exportData, excelconfig, addPath);
            var fileName = _userManager.UserId + "|" + addPath + "|xls";
            return new { name = excelconfig.FileName, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(fileName, "NCC") };
        }

        /// <summary>
        /// 模板下载（关联产品来自 cpgl：选产品编号后关联产品名称列公式带出）
        /// </summary>
        [HttpGet("TemplateDownload")]
        public async Task<dynamic> TemplateDownload()
        {
            var user = await _userManager.GetUserInfo();
            string userId = user.userId;
            string name = "资料管理导入模板.xlsx";
            string addFilePath = FileVariable.TemporaryFilePath + name;

            var fileds = GetFiledMaps();
            var headersToWrite = new List<string>();
            int 关联产品ColIndex = -1, 关联产品名称ColIndex = -1;
            int colIndex = 0;
            foreach (var kv in fileds)
            {
                headersToWrite.Add(kv.Key);
                if (kv.Value == "fl") 关联产品ColIndex = colIndex;
                else if (kv.Value == "fl1") 关联产品名称ColIndex = colIndex;
                colIndex++;
            }

            var cpglList = await _db.Queryable<CpglEntity>()
                .Where(c => c.Cpid != null && c.Cpid != "")
                .Select(c => new { c.Cpid, c.Cpmc })
                .ToListAsync();
            var cpidList = cpglList.Select(c => (c.Cpid ?? "").Trim()).Where(s => !string.IsNullOrEmpty(s)).ToList();
            const int maxListLen = 250;
            var cpidDropdown = new List<string>();
            var len = 0;
            foreach (var s in cpidList)
            {
                if (len + (len > 0 ? 1 : 0) + s.Length > maxListLen) break;
                len += (len > 0 ? 1 : 0) + s.Length;
                cpidDropdown.Add(s);
            }

            var workbook = new XSSFWorkbook();
            try
            {
                const string cpglSheetName = "ZlglCpgl";
                var sheet = workbook.CreateSheet("sheet1");
                var headerRow = sheet.CreateRow(0);
                for (int i = 0; i < headersToWrite.Count; i++)
                    headerRow.CreateCell(i).SetCellValue(headersToWrite[i]);

                if (关联产品ColIndex >= 0 && cpidDropdown.Count > 0)
                {
                    var helper = sheet.GetDataValidationHelper();
                    var constraint = helper.CreateExplicitListConstraint(cpidDropdown.ToArray());
                    var range = new CellRangeAddressList(1, 9999, 关联产品ColIndex, 关联产品ColIndex);
                    var validation = helper.CreateValidation(constraint, range);
                    validation.SuppressDropDownArrow = false;
                    validation.ShowPromptBox = true;
                    validation.EmptyCellAllowed = true;
                    sheet.AddValidationData(validation);
                }

                if (关联产品名称ColIndex >= 0 && 关联产品ColIndex >= 0)
                {
                    string idColLetter = ColIndexToLetter(关联产品ColIndex);
                    for (int r = 1; r <= 500; r++)
                    {
                        int excelRow = r + 1;
                        var row = sheet.CreateRow(r);
                        for (int c = 0; c < headersToWrite.Count; c++)
                            row.CreateCell(c);
                        var nameCell = row.GetCell(关联产品名称ColIndex);
                        nameCell.SetCellFormula($"IF({idColLetter}{excelRow}=\"\",\"\",VLOOKUP({idColLetter}{excelRow},{cpglSheetName}!A:B,2,0))");
                    }
                }

                var cpglSheet = workbook.CreateSheet(cpglSheetName);
                cpglSheet.CreateRow(0).CreateCell(0).SetCellValue("关联产品");
                cpglSheet.GetRow(0).CreateCell(1).SetCellValue("关联产品名称");
                for (int i = 0; i < cpglList.Count; i++)
                {
                    var prow = cpglSheet.CreateRow(i + 1);
                    prow.CreateCell(0).SetCellValue(cpglList[i].Cpid ?? "");
                    prow.CreateCell(1).SetCellValue(cpglList[i].Cpmc ?? "");
                }

                using (var fs = new FileStream(addFilePath, FileMode.Create, FileAccess.Write))
                    workbook.Write(fs);
            }
            finally
            {
                workbook?.Close();
            }

            return new { name = name, url = "/api/File/Download?encryption=" + DESCEncryption.Encrypt(userId + "|" + name, "NCC") };
        }

        /// <summary>
        /// 导入预览
        /// </summary>
        [HttpGet("ImportPreview")]
        public dynamic ImportPreview(string fileName, string orgId = "")
        {
            return new
            {
                dataRow = ExcelImportHelper.ImportPreview(fileName, GetFiledMaps(orgId)),
                fileds = GetFiledMaps(orgId)
            };
        }

        /// <summary>
        /// 模板/导入字段映射（模板不含 id，导出列单独配置且含 id）
        /// </summary>
        public Dictionary<string, string> GetFiledMaps(string orgId = "")
        {
            var fileds = new Dictionary<string, string>
            {
                { "资料名称", "zlm" },
                { "设备编号", "sbbh" },
                { "规格", "gg" },
                { "关联产品", "fl" },
                { "关联产品名称", "fl1" },
                { "发布时间", "fbsj" },
                { "编号", "bh" },
                { "出厂编号", "ccbh" },
                { "出厂日期", "ccrq" },
                { "设备图号", "th" },
                { "设备维护人员", "sbwhry" }
            };
            return fileds;
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        [HttpPost("ImportData")]
        public async Task<dynamic> ImportData([FromBody] ImportDataDto input)
        {
            var user = new UserInfo();
            await Scoped.Create(async (_, scope) =>
            {
                var services = scope.ServiceProvider;
                var _userManager = App.GetService<IUserManager>(services);
                user = await _userManager.GetUserInfo();
            });

            var output = new ImportDataOutput();
            foreach (var item in input.list)
            {
                try
                {
                    var obj = item.ToJson().ToJToken();
                    var settings = new JsonSerializerSettings { DateFormatString = "yyyy-MM-dd HH:mm:ss" };
                    ZlglEntity entity;
                    try
                    {
                        entity = obj.ToObject<ZlglEntity>();
                    }
                    catch
                    {
                        entity = JsonConvert.DeserializeObject<ZlglEntity>(obj.ToJson().ReplaceRegex("日", "日 ").ReplaceRegex("点", ":").ReplaceRegex("分", ":00"), settings);
                    }

                    // 关联产品：Excel 填产品编号 cpid，解析为 cpgl.Id 存 fl
                    var 关联产品Str = (obj["关联产品"] ?? obj["fl"])?.ToString()?.Trim();
                    if (关联产品Str.IsNotEmptyOrNull())
                    {
                        var cp = (await _db.Queryable<CpglEntity>().Where(c => c.Cpid == 关联产品Str).ToListAsync()).FirstOrDefault();
                        if (cp == null)
                        {
                            AddFailWithError(output, item, "关联产品", "未找到该产品编号，请从下拉选择");
                            output.fnum++;
                            continue;
                        }
                        entity.Fl = cp.Id;
                    }

                    // 设备维护人员：Excel 填 张三 或 张三;李四，按姓名查 sbwhry 表得到 Id，逗号分隔保存
                    var 人员Str = (obj["设备维护人员"] ?? obj["sbwhry"])?.ToString()?.Trim();
                    if (人员Str.IsNotEmptyOrNull())
                    {
                        var names = 人员Str.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(n => n.Trim()).Where(n => !string.IsNullOrEmpty(n)).ToList();
                        var ids = new List<string>();
                        bool 人员解析失败 = false;
                        foreach (var name in names)
                        {
                            var sb = (await _db.Queryable<SbwhryEntity>().Where(s => s.Xm == name).ToListAsync()).FirstOrDefault();
                            if (sb == null)
                            {
                                AddFailWithError(output, item, "设备维护人员", "未找到人员：" + name);
                                output.fnum++;
                                人员解析失败 = true;
                                break;
                            }
                            ids.Add(sb.Id);
                        }
                        if (人员解析失败) continue;
                        entity.Sbwhry = string.Join(",", ids);
                    }

                    entity.Id = YitIdHelper.NextId().ToString();
                    if (input.itemStr.IsNotEmptyOrNull() && entity.Fl.IsNullOrEmpty())
                        entity.Fl = input.itemStr;

                    var isOk = await _db.Insertable(entity).IgnoreColumns(ignoreNullColumn: true).ExecuteCommandAsync();
                    if (isOk < 1)
                    {
                        AddFailWithError(output, item, "数据", "新增失败");
                        output.fnum++;
                    }
                    else
                        output.snum++;
                }
                catch (Exception ex)
                {
                    AddFailWithError(output, item, "数据", ex.Message);
                    output.fnum++;
                }
            }
            // 与故障查询一致：resultType 0 表示全部成功，1 表示有失败（前端据此展示成功/失败块及失败列表）
            if (output.snum == input.list.Count)
                output.resultType = 0;
            else
                output.resultType = 1;
            return output;
        }

        private static void AddFailWithError(ImportDataOutput output, object item, string errorField, string errorMessage)
        {
            try
            {
                var jobj = (item is JObject jo) ? jo : JObject.Parse(item.ToJson());
                jobj["_errorField"] = errorField ?? "";
                jobj["_errorMessage"] = errorMessage ?? "";
                output.failResult.Add(jobj);
            }
            catch
            {
                output.failResult.Add(item);
            }
        }

        private static string ColIndexToLetter(int colIndex)
        {
            int n = colIndex;
            string result = "";
            while (n >= 0)
            {
                result = (char)('A' + (n % 26)) + result;
                n = n / 26 - 1;
            }
            return result;
        }

        #endregion
    }
}