OA2023/OASystem/OASystem.Api/Controllers/MarketCustomerResourcesController.cs

using Aspose.Cells;
using EyeSoft.Extensions;
using OASystem.API.OAMethodLib;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Attributes;
using OASystem.Domain.Dtos.CRM;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.ViewModels.CRM;
using OASystem.Infrastructure.Repositories.CRM;
using OASystem.RedisRepository;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Net;
using UAParser;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 市场客户资料
    /// </summary>
    [Route("api/[controller]/[action]")]
    public class MarketCustomerResourcesController : ControllerBase
    {
        private readonly NewClientDataRepository _clientDataRepository;
        private readonly SqlSugarClient _sqlSugar;

        private readonly ILogger<MarketCustomerResourcesController> _logger;
        private string keyName = "newClient_SearchKey";
        /// <summary>
        /// 初始化
        /// </summary>
        public MarketCustomerResourcesController(NewClientDataRepository clientDataRepository, SqlSugarClient sqlSugar, ILogger<MarketCustomerResourcesController> logger)
        {
            this._clientDataRepository = clientDataRepository;
            _sqlSugar = sqlSugar;
            _logger = logger;
        }

        /// <summary>
        /// 客户资料数据
        /// 基础数据
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> MarketCustomerInit(MarketCustomerInitDto dto)
        {
            JsonView jw = new JsonView();
            try
            {
                Result resultData = await _clientDataRepository._Init(dto);
                if (resultData.Code == 0)
                {
                    jw = JsonView(true, "查询成功!", resultData.Data);
                }
                else
                {
                    jw = JsonView(false, resultData.Msg);
                }
            }
            catch (Exception)
            {
                jw = JsonView(false, "程序错误！");
            }

            return Ok(jw);
        }

        /// <summary>
        /// 查询客户资料数据
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        //[ApiLog("Crm_NewClientData", OperationEnum.List)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryNewClientData(NewClientDataQueryDto dto)
        {
            #region 参数验证
            if (dto.OperationUserId < 0)
                return Ok(JsonView(false, "请传入有效的OperationUserId参数！"));

            if (dto.PortType < 0)
                return Ok(JsonView(false, "请传入有效的PortType参数！"));
            #endregion

            JsonView jw = new JsonView();
            var startTime = DateTime.UtcNow;

            try
            {
                Result resultData = await _clientDataRepository.QueryNewClientData(dto);
                if (resultData.Code == 0)
                {
                    #region 客户资料表操作记录
                    //await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.NoOperation, dto.OperationUserId, 0, "");
                    #endregion

                    jw = JsonView(true, resultData.Msg, resultData.Data);
                }
                else
                {
                    jw = JsonView(false, resultData.Msg);
                }
            }
            catch (Exception)
            {
                jw = JsonView(false, "程序错误！");
            }
            finally
            {
                using var ssg = _sqlSugar.CopyNew();
                var status = HttpContext.Response.StatusCode.ToString();
                _ = Task.Run(async () =>
                {
                    try
                    {
                        string remoteIp = string.Empty,
                               location = string.Empty;

                        // 检查请求头中的X-Forwarded-For，以获取真实的客户端IP地址
                        if (HttpContext.Request.Headers.ContainsKey("X-Forwarded-For"))
                        {
                            remoteIp = HttpContext.Request.Headers["X-Forwarded-For"].ToString().Split(',', StringSplitOptions.RemoveEmptyEntries)[0];
                        }
                        else
                        {
                            remoteIp = HttpContext.Connection.RemoteIpAddress?.ToString();
                        }

                        var task = GetIpInfo(remoteIp);

                        string deviceType = string.Empty, browser = string.Empty, os = string.Empty;
                        var userAgent = HttpContext.Request.Headers["User-Agent"].FirstOrDefault();
                        if (!string.IsNullOrEmpty(userAgent))
                        {
                            // 解析User-Agent头
                            var parser = Parser.GetDefault();
                            var client = parser.Parse(userAgent);

                            // 提取浏览器信息
                            browser = client.UA.Family; // 浏览器名称
                            var browserVersion = client.UA.Major + "." + client.UA.Minor + "." + client.UA.Patch; // 浏览器版本
                            browser += $"({browserVersion})";

                            // 提取操作系统信息
                            os = client.OS.Family; // 操作系统名称

                            var osVersion = string.Empty; // 操作系统版本
                            if (!string.IsNullOrEmpty(client.OS.Major)) osVersion += client.OS.Major;
                            if (!string.IsNullOrEmpty(client.OS.Minor)) osVersion += "." + client.OS.Minor;
                            if (!string.IsNullOrEmpty(client.OS.Patch)) osVersion += "." + client.OS.Patch;

                            if (!string.IsNullOrEmpty(osVersion)) os += $"({osVersion})";

                            // 提取设备信息
                            deviceType = client.Device.Family; // 设备类型，如 'mobile', 'tablet', 'desktop' 等

                        }

                        // 记录请求结束时间
                        var endTime = DateTime.UtcNow;
                        // 计算耗时
                        var duration = (long)(endTime - startTime).TotalMilliseconds;

                        //等待任务进行
                        (remoteIp, location) = await task;

                        //单独记录操作
                        var logInfo = new Crm_TableOperationRecord()
                        {
                            TableName = "Crm_NewClientData",
                            PortType = dto.PortType,
                            OperationItem = OperationEnum.List,
                            DataId = 0,
                            RequestUrl = "/api/MarketCustomerResources/QueryNewClientData",
                            RemoteIp = remoteIp,
                            Location = location,
                            RequestParam = JsonConvert.SerializeObject(dto),
                            ReturnResult = JsonConvert.SerializeObject(jw),
                            Elapsed = duration,
                            Status = status,
                            CreateUserId = dto.OperationUserId,
                            UpdatePreData = "",
                            UpdateBefData = "",
                            Browser = browser,
                            Os = os,
                            DeviceType = deviceType,
                        };
                        // 存储到数据库
                        await ssg.Insertable(logInfo).ExecuteCommandAsync();
                    }
                    catch (Exception ex)
                    {
                    }
                });
            }

            return Ok(jw);
        }

        /// <summary>
        /// 客户资料数据
        /// Details
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.Details)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostNewClientDataDetails(NewClientDataDetailsDto dto)
        {
            #region 参数验证
            if (dto.Id < 0)
                return Ok(JsonView(false, "请传入有效的Id参数！"));

            if (dto.UserId < 0)
                return Ok(JsonView(false, "请传入有效的UserId参数！"));

            if (dto.PortType < 0)
                return Ok(JsonView(false, "请传入有效的PortType参数！"));
            #endregion

            JsonView jw = new JsonView();
            try
            {
                Result resultData = await _clientDataRepository._Details(dto.PortType, dto.Id);
                if (resultData.Code == 0)
                {
                    #region 客户资料表操作记录
                    //await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Details, dto.UserId, dto.Id, "");
                    #endregion
                    jw = JsonView(true, "查询成功!", resultData.Data);
                }
                else
                {
                    jw = JsonView(false, resultData.Msg);
                }
            }
            catch (Exception)
            {
                jw = JsonView(false, "程序错误！");
            }

            return Ok(jw);
        }

        /// <summary>
        /// 客户资料操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.NoOperation)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientOp(NewClientOpDto dto)
        {
            #region 参数验证

            if (dto.CreateUserId < 0)
            {
                return Ok(JsonView(false, "请传入有效的CreateUserId参数！"));
            }

            if (dto.PortType < 0)
            {
                return Ok(JsonView(false, "请传入有效的PortType参数！"));
            }
            #endregion

            try
            {
                Domain.Result result = await _clientDataRepository.NewClientOp(dto);
                if (result.Code != 0)
                {
                    return Ok(JsonView(false, result.Msg));
                }
                #region 客户资料操作记录
                //OperationEnum operationEnum = OperationEnum.NoOperation;
                //if (dto.Status == 1)
                //{
                //    operationEnum = OperationEnum.Add;
                //    dto.Id = Convert.ToInt32(result.Data);
                //}
                //else if (dto.Status == 2) operationEnum = OperationEnum.Edit;
                //await GeneralMethod.NewClientOperationRecord(dto.PortType, operationEnum, dto.CreateUserId, dto.Id, "");
                #endregion

                //this.keyName += "_" + dto.CreateUserId;
                //var exists = await RedisFactory.CreateRedisRepository().KeyExistsAsync(keyName);
                //if (exists) await RedisFactory.CreateRedisRepository().KeyDeleteAsync(keyName);

                return Ok(JsonView(true, result.Msg + "Id:" + dto.Id));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！Msg:" + ex.Message));
            }
        }

        /// <summary>
        /// 新客户资料操作（删除）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.Del)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientDel(DelBaseDto dto)
        {
            #region 参数验证
            if (dto.Id < 0)
            {
                return Ok(JsonView(false, "请传入有效的Id参数！"));
            }

            if (dto.DeleteUserId < 0)
            {
                return Ok(JsonView(false, "请传入有效的DeleteUserId参数！"));
            }

            if (dto.PortType < 0)
            {
                return Ok(JsonView(false, "请传入有效的PortType参数！"));
            }
            #endregion
            var res = await _clientDataRepository.DelNewClientData(dto);
            if (res.Code != 0)
            {
                return Ok(JsonView(false, "删除失败"));
            }

            #region 客户资料表操作记录
            //await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Del, dto.DeleteUserId, dto.Id, "");
            #endregion

            //this.keyName += "_" + dto.DeleteUserId;
            //var exists = await RedisFactory.CreateRedisRepository().KeyExistsAsync(keyName);
            //if (exists) await RedisFactory.CreateRedisRepository().KeyDeleteAsync(keyName);

            return Ok(JsonView(true, "删除成功！"));
        }

        /// <summary>
        /// 获取下拉列表数据和单条数据信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.Details)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QuerySelectAndSingleData(QuerySingleDto dto)
        {
            JsonView jw = new JsonView();
            var result = await _clientDataRepository.QuerySelectAndSingleData(dto);
            if (result.Code == 0)
            {
                #region 客户资料表操作记录
                //await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Details, dto.UserId, dto.Id, "");
                #endregion

                jw = JsonView(true, result.Msg, result.Data);
            }
            else
            {
                jw = JsonView(false, result.Msg);
            }
            return Ok(jw);
        }

        /// <summary>
        /// 获取现有负责人
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryUserSelect()
        {
            Result resTable = _clientDataRepository.QueryUserSelect();
            return Ok(JsonView(true, resTable.Msg, resTable.Data));
        }

        /// <summary>
        /// 获取出团数据
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryNumberGroups()
        {
            var result = await _clientDataRepository.QueryNumberGroups();
            if (result.Code != 0)
            {
                return Ok(JsonView(false, result.Msg));
            }
            return Ok(JsonView(true, result.Msg, result.Data));
        }

        /// <summary>
        /// 新客户资料操作
        /// 批量分配
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.BatchAssignment)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostBatchAssignment(BatchAssignmentDto dto)
        {
            #region 参数验证
            if (dto.UserId < 0)
            {
                return Ok(JsonView(false, "请传入有效的UserId参数！"));
            }

            if (dto.PortType < 0)
            {
                return Ok(JsonView(false, "请传入有效的PortType参数！"));
            }
            #endregion

            var res = await _clientDataRepository._BatchAssignment(dto);
            if (res.Code != 0)
            {
                return Ok(JsonView(false, res.Msg));
            }
            return Ok(JsonView(true, "操作成功！"));
        }

        private Dictionary<int, List<int>> MCRSetting()
        {
            Dictionary<int, List<int>> keyValuePairs = new Dictionary<int, List<int>>();
            keyValuePairs.Add(419, new List<int>() //四川
            {
                376,377,378,381,382,387,388,389,390,753,754,1296,1303
            });
            keyValuePairs.Add(420, new List<int>() //云南
            {
                407,408,409,410,449,451,452,453,567,754,1297,1304
            });
            keyValuePairs.Add(421, new List<int>() { 424, 425, 426, 427, 428, 429, 754, 1298, 1305, 1307, 1306 }); // 贵州
            keyValuePairs.Add(422, new List<int>() { 415, 416, 754, 1317, 1316, 1315, 455, 1321, 1320, 1319, 1318 }); // 西藏
            keyValuePairs.Add(423, new List<int>() { 417, 418, 454, 456, 754, 1313, 1314 }); // 重庆
            keyValuePairs.Add(578, new List<int>() { 581, 582, 583, 754, 1299, 1300 }); // 青海
            keyValuePairs.Add(605, new List<int>() { 588, 589, 590, 591, 592, 593, 754, 1301, 1310, 1309, 1308 }); // 陕西
            keyValuePairs.Add(606, new List<int>() { 597, 598, 599, 600, 601, 602, 603, 604, 754, 1324, 1323, 1322 }); // 宁夏
            keyValuePairs.Add(625, new List<int>() { 617, 618, 619, 620, 621, 622, 622, 623, 624, 754, 1302, 1312, 1311 }); // 甘肃
            keyValuePairs.Add(634, new List<int>() { 630, 631, 632, 633, 754 }); // 新疆

            return keyValuePairs;
        }

        private Dictionary<int, List<int>> MCRSecondSetting()
        {
            Dictionary<int, List<int>> keyValuePairs = new Dictionary<int, List<int>>();

            _sqlSugar.Queryable<Sys_SetData>()
                .Where(x => x.STid == 33 && x.IsDel == 0)
                .Select(x => new { x.Id, x.Name, x.Remark })
                .ToList()
                .ForEach(x =>
                {
                    try
                    {
                        var list = JsonConvert.DeserializeObject<List<int>>(x.Remark);
                        keyValuePairs.Add(x.Id, list);
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError($"MCRSecondSetting JsonConvert.DeserializeObject ERROR: {ex.Message}");
                        keyValuePairs.Add(x.Id, new List<int>());
                    }
                });

            return keyValuePairs;
        }

        private Dictionary<int, List<int>> MCRLvSetting()
        {
            var keyValuePairs = new Dictionary<int, List<int>>();

            _sqlSugar.Queryable<Sys_SetData>()
                .Where(x => x.STid == 42 && x.IsDel == 0)
                .Select(x => new { x.Id, x.Name, x.Remark })
                .ToList()
                .ForEach(x =>
                {
                    try
                    {
                        var list = JsonConvert.DeserializeObject<List<int>>(x.Remark);
                        keyValuePairs.Add(x.Id, list);
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError($"MCRLvSetting JsonConvert.DeserializeObject ERROR: {ex.Message}");
                        keyValuePairs.Add(x.Id, new List<int>());
                    }
                });

            return keyValuePairs;
        }


        [HttpPost]
        public IActionResult QueryClientType(QueryClientTypeDto Dto)
        {
            var jw = JsonView(true, "获取成功！");

            var dic_lv = MCRLvSetting();
            var dic_setting = MCRSecondSetting();
            ArrayList arr = new ArrayList();
            ArrayList lvArr = new ArrayList();
            var ids = new List<int>();
            var lvids = new List<int>();

            var items = _sqlSugar.Queryable<Sys_SetData>()
                .Where(x => x.STid == 42 && x.IsDel == 0)
                .Where(x => Dto.SetDataIdArr.Contains(x.Id))
                .Select(x => new { x.Id, x.Name, x.Remark })
                .ToList();


            foreach (var item in items)
            {
                if (dic_lv.Keys.Contains(item.Id))
                {
                    lvids.AddRange(dic_lv[item.Id]);
                    var remarkids = JsonConvert.DeserializeObject<List<int>>(item.Remark);
                    if (remarkids.Any())
                    {
                        foreach (var remarkid in remarkids)
                        {
                            if (dic_setting.Keys.Contains(remarkid))
                            {
                                ids.AddRange(dic_setting[remarkid]);
                            }
                        }
                    }
                }
            }

            arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
                       .Where(u => ids.Contains(u.Id) && u.IsDel == 0)
                       .Select(x => new { x.Id, x.Name, x.Remark })
                       .ToList());

            if (arr.Count == 0)
            {
                arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
                        .Where(u => u.STid == 37 && u.IsDel == 0)
                        .Select(x => new { x.Id, x.Name, x.Remark }).ToList());
            }

            // 创建比较器实例
            IComparer remakeComparer = new RemakeComparer();

            // 使用ArrayList.Sort方法和自定义比较器对arr进行排序
            arr.Sort(remakeComparer);

            var rangelv = _sqlSugar.Queryable<Sys_SetData>()
                       .Where(u => lvids.Contains(u.Id) && u.IsDel == 0)
                       .Select(x => new { x.Id, x.Name })
                       .ToList();

            lvArr.AddRange(rangelv);

            if (lvArr.Count == 0)
            {
                lvArr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
                        .Where(u => u.STid == 33 && u.IsDel == 0)
                        .Select(x => new { x.Id, x.Name }).ToList());
            }

            jw.Data = new
            {
                TypeArr = arr,
                LvArr = lvArr,
            };

            return Ok(jw);
        }

        [HttpPost]
        public IActionResult QueryClientTypeSecond(QueryClientTypeDto Dto)
        {
            var jw = JsonView(true, "获取成功！");

            var keyValuePairs = MCRSecondSetting();
            ArrayList arr = new ArrayList();
            var ids = new List<int>();
            foreach (var item in Dto.SetDataIdArr)
            {
                if (keyValuePairs.Keys.Contains(item))
                {
                    ids.AddRange(keyValuePairs[item]);
                }
            }

            arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
                       .Where(u => ids.Contains(u.Id) && u.IsDel == 0)
                       .Select(x => new { x.Id, x.Name, x.Remark })
                       .ToList());

            if (arr.Count == 0)
            {
                arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
                        .Where(u => u.STid == 37 && u.IsDel == 0)
                        .Select(x => new { x.Id, x.Name, x.Remark }).ToList());
            }

            // 创建比较器实例
            IComparer remakeComparer = new RemakeComparer();

            // 使用ArrayList.Sort方法和自定义比较器对arr进行排序
            arr.Sort(remakeComparer);

            jw.Data = arr;

            return Ok(jw);
        }

        /// <summary>
        /// 客户资料数据 全信息导出 excel 下载
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.Download)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientDataExcelDownload(NewClientDataExcelDownloadDto dto)
        {
            #region 参数验证
            if (dto.OperationUserId < 0) return Ok(JsonView(false, "请传入有效的OperationUserId参数！"));

            if (dto.PortType < 0) return Ok(JsonView(false, "请传入有效的PortType参数！"));
            if (dto.PageId < 0) return Ok(JsonView(false, "请传入有效的PageId参数！"));
            #endregion

            #region 页面操作权限验证
            var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.OperationUserId, dto.PageId);

            if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权!"));
            #endregion

            var dt = await _clientDataRepository.NewClientDataExcelDownload(dto);
            if (dt != null)
            {
                string tempPath = (AppSettingsHelper.Get("ExcelBasePath") + "Template/公司客户资料导出模板.xlsx");
                var designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(tempPath);
                designer.SetDataSource("NCDDT", dt);
                designer.Process();

                //文件名
                string fileName = $"公司客户资料(全信息导出){DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
                designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + "NewClientDataExcelDownload/" + fileName);
                string url = AppSettingsHelper.Get("ExcelBaseUrl") + "Office/Excel/NewClientDataExcelDownload/" + fileName;

                #region 客户资料表操作记录
                var respose = JsonView(true, "成功", url);
                //var paramData = new
                //{
                //    APIName = @"MarketCustomerResources/NewClientDataExcelDownload",
                //    RequestParam = dto,
                //    ResposeParam = respose
                //};
                //await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Download, dto.OperationUserId, 0, JsonConvert.SerializeObject(paramData));
                #endregion

                return Ok(respose);
            }

            return Ok(JsonView(false));

        }


        /// <summary>
        /// 客户资料数据 分权限导出 excel 下载
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ApiLog("Crm_NewClientData", OperationEnum.Download)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientDataAuthorityExcelDownload(NewClientDataExcelDownloadDto dto)
        {
            #region 参数验证
            if (dto.OperationUserId < 0) return Ok(JsonView(false, "请传入有效的OperationUserId参数！"));

            if (dto.PortType < 0) return Ok(JsonView(false, "请传入有效的PortType参数！"));
            if (dto.PageId < 0) return Ok(JsonView(false, "请传入有效的PageId参数！"));
            #endregion

            #region 页面操作权限验证
            var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.OperationUserId, dto.PageId);

            if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权!"));
            #endregion

            var dt = await _clientDataRepository.NewClientDataExcelDownload(dto);
            if (dt != null)
            {
                string tempPath = (AppSettingsHelper.Get("ExcelBasePath") + "Template/公司客户资料分权限导出模板.xlsx");
                var designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(tempPath);
                designer.SetDataSource("NCDDT", dt);
                designer.Process();

                //文件名
                string fileName = $"公司客户资料(分权限导出){DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
                designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + "NewClientDataExcelDownload/" + fileName);
                string url = AppSettingsHelper.Get("ExcelBaseUrl") + "Office/Excel/NewClientDataExcelDownload/" + fileName;

                var respose = JsonView(true, "成功", url);

                return Ok(respose);
            }


            return Ok(JsonView(false));

        }


        /// <summary>
        /// 客户资料数据 历史记录 Init
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientDataRecordInit(NewClientDataRecordInitDto dto)
        {

            #region 参数验证
            if (dto.UserId < 0) return Ok(JsonView(false, MsgTips.UserId));
            if (!SharingStaticData.PortTypes.Contains(dto.PortType)) return Ok(JsonView(false, MsgTips.Port));
            if (dto.PageId < 0) return Ok(JsonView(false, MsgTips.PageId));
            #endregion

            #region 页面操作权限验证
            var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
            #endregion
            var enumfiltrates = new string[] { "NoOperation", "Audit", "Login" };
            var operations = EnumHelper.GetEnumDescriptions(typeof(OperationEnum), enumfiltrates);

            var operation1 = new List<dynamic>();
            foreach (var item in operations)
            {
                operation1.Add(new { value = item.Key, text = item.Value });
            }

            object userDatas = Array.Empty<object>();
            if (dto.UserId == 21)
            {
                var userIds = await _sqlSugar.Queryable<Crm_ClientDataAndUser>().Where(x => x.IsDel == 0).Select(x => x.usersId).Distinct().ToListAsync();
                if (userIds.Any())
                {
                    userDatas = await _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && userIds.Contains(x.Id)).Select(x => new { value = x.Id, text = x.CnName }).ToListAsync();
                }
            }

            var data = new
            {
                operations = operation1,
                userDatas = userDatas
            };
            return Ok(JsonView(data));

        }

        /// <summary>
        /// 客户资料数据 历史记录
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientDataRecord(NewClientDataRecordDto dto)
        {
            #region 参数验证
            if (dto.UserId < 0) return Ok(JsonView(false, MsgTips.UserId));
            if (!SharingStaticData.PortTypes.Contains(dto.PortType)) return Ok(JsonView(false, MsgTips.Port));
            if (dto.PageId < 0) return Ok(JsonView(false, MsgTips.PageId));
            #endregion

            #region 页面操作权限验证
            var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
            #endregion

            return Ok(await _clientDataRepository.NewClientDataRecord(dto));

        }

        #region 修改数据
        //[HttpPost]
        //public IActionResult SynchronizationData()
        //{
        //    var keyValuePairs = new Dictionary<int, List<string>>
        //        {
        //            { 5, new List<string> { "省级部门", "省级单位" } },
        //            { 10, new List<string> { "市州" } },
        //            { 15, new List<string> { "省属国企" } },
        //            { 20, new List<string> { "省属学校", "省属高校" } },
        //            { 25, new List<string> { "省级行业协会", "省级社团", "省级联合会" , "省属行业协会", "民营企业", "地方国企" } },
        //            { 30, new List<string> { "市级部门", "市级单位" } },
        //            { 35, new List<string> { "区市县" } },
        //            { 40, new List<string> { "市级国企", "市属国企" } },
        //            { 45, new List<string> { "市属学校", "市属高校" } },
        //            { 50, new List<string> { "市级行业协会", "市级社团", "市级联合会" } }
        //        };

        //    var setting = MCRSetting();
        //    var arr = new List<List<Sys_SetData>>();

        //    foreach (var item in setting.Keys)
        //    {
        //        var setDataList_DB = _sqlSugar.Queryable<Sys_SetData>()
        //                                    .Where(x => x.IsDel == 0 && setting[item].Contains(x.Id))
        //                                    .ToList();
        //        foreach (var setData in setDataList_DB)
        //        {
        //            foreach (var lv in keyValuePairs.Keys)
        //            {
        //                var islv =  keyValuePairs[lv].Where(x => setData.Name.Contains(x)).Count() > 0;

        //                if (islv)
        //                {
        //                    setData.Remark = lv.ToString();
        //                }
        //            }
        //        }

        //        arr.Add(setDataList_DB);
        //    }

        //    _sqlSugar.Updateable(arr.SelectMany(x => x).ToList()).UpdateColumns(x => new
        //    {
        //        x.Remark
        //    }).ExecuteCommand();

        //    return Ok(arr);
        //}

        [HttpPost]
        public IActionResult QueryData()
        {

            var db = _sqlSugar.Queryable<Crm_NewClientData>()
                 .Where(x => x.IsDel == 0)
                 .Select(x => new Crm_NewClientData
                 {
                     Id = x.Id,
                     Client = x.Client,
                     Category = x.Category,
                     Location = x.Location,
                 }).ToList();

            var count = 0;
            var updateid = new List<int>();
            foreach (var item in db)
            {
                EncryptionProcessor.DecryptProperties(item);
                if (!string.IsNullOrWhiteSpace(item.Location) && item.Location.Contains("重庆") && item.Category == 0)
                {
                    count++;
                    //if (!string.IsNullOrWhiteSpace(item.Client) && (item.Client.Contains("大学") || item.Client.Contains("学院")))
                    {
                        updateid.Add(item.Id);
                    }
                }
            }

            var updateCount = _sqlSugar.Updateable<Crm_NewClientData>().Where(x => updateid.Contains(x.Id))
                .SetColumns(x => new Crm_NewClientData { Category = 456 }).ExecuteCommand();

            return Ok(new
            {
                notCount = count,
                updateCount = updateCount
            });
        }

        [HttpPost]
        public IActionResult InsertData()
        {

            string sql = $@"SELECT * FROM [dbo].[Crm_NewClientData] WHERE ID IN (
	                    --所有四川省域的
	                    select Id from [dbo].[Crm_NewClientData] where ISDEL = 0 AND Lvlid IN (
			                    select S.Id  from Sys_SetData S
			                    where STid = 33 and isdel = 0 and 
			                    (Name like '%四%川%' or Name like '%成%都%')
		                    )
	
                    ) AND IsDel = 0 
                    AND ID IN (
	                    SELECT NewClientDataId  FROM [dbo].[Crm_ClientDataAndUser] 
		                    WHERE NewClientDataId IN (
			                    --所有单独数据
			                    SELECT NewClientDataId  FROM [dbo].[Crm_ClientDataAndUser] 
			                    WHERE ISDEL = 0 
			                    GROUP BY NewClientDataId 
			                    HAVING COUNT(NewClientDataId) = 1  
		                    ) AND IsDel = 0 AND UsersId = 21
                    )";
            var clientList = _sqlSugar.SqlQueryable<Crm_NewClientData>(sql).Select(x => x.Id).ToList().Select(x => new Crm_ClientDataAndUser
            {
                NewClientDataId = x,
                usersId = 330,
                CreateTime = DateTime.Now,
                CreateUserId = 235,
                IsDel = 0,
            }).ToList();
            var insertCount = _sqlSugar.Insertable(clientList).ExecuteCommand();
            return Ok(insertCount);
        }

        [HttpPost]
        public IActionResult ExportData()
        {

            var soure = _sqlSugar.Queryable<Crm_NewClientData>()
                 .InnerJoin<Crm_ClientDataAndUser>((a, b) => b.NewClientDataId == a.Id && b.IsDel == 0)
                 .Where((a, b) => a.IsDel == 0 && b.usersId == 95)
                 .Select((a, b) => a)
                 .ToList();

            foreach (var item in soure)
            {
                EncryptionProcessor.DecryptProperties(item);
            }

            // 创建WorkbookDesigner对象
            WorkbookDesigner designer = new WorkbookDesigner();

            // 加载模板文件（包含智能标记）
            designer.Workbook = new Workbook("C:\\Users\\PC\\Desktop\\111.xlsx");

            // 设置数据源，这里假设你的List集合名为listData
            designer.SetDataSource("ListData", soure);

            // 处理智能标记以将数据填充到工作表中
            designer.Process();

            // 保存Excel文件
            designer.Workbook.Save("C:\\Users\\PC\\Desktop\\111000.xlsx");

            return Ok(1);
        }

        [HttpPost]
        public IActionResult DeleteData()
        {

            var soure = _sqlSugar.Queryable<Crm_NewClientData>()
                 .InnerJoin<Crm_ClientDataAndUser>((a, b) => b.NewClientDataId == a.Id && b.IsDel == 0)
                 .Where((a, b) => a.IsDel == 0 && b.usersId == 330)
                 .Select((a, b) => b.Id)
                 .ToList();

            var count = _sqlSugar.Updateable<Crm_ClientDataAndUser>()
                 .Where(u => soure.Contains(u.Id) && u.IsDel == 0)
                 .SetColumns(x => new Crm_ClientDataAndUser
                 {
                     DeleteTime = DateTime.Now.ToString(),
                     DeleteUserId = 235,
                     IsDel = 1
                 }).ExecuteCommand();

            return Ok(count);
        }

        [HttpPost]
        public IActionResult ReassignCustomers()
        {
            var user_Client = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
                      .Where(x => x.usersId == 302 && x.IsDel == 0)
                      .Select(x => new
                      {
                          x.Id,
                          x.NewClientDataId
                      })
                      .ToList();
            var ids = user_Client.Select(x => x.NewClientDataId).ToList();

            string setDataSql = $"select * from Sys_SetData where STid = 33 and isdel = 0 and (Name like '%重庆%')  ";

            var setDataids = _sqlSugar.SqlQueryable<Sys_SetData>(setDataSql).Select(x => x.Id).ToList();

            var clients = _sqlSugar.Queryable<Crm_NewClientData>().Where(x => ids.Contains(x.Id) && setDataids.Contains(x.Lvlid) && x.IsDel == 0).ToList();

            var clients_ids = clients.Select(x => x.Id).ToList();

            int updateCount = 0;

            _sqlSugar.BeginTran();

            if (clients.Count == 223)
            {

                updateCount = _sqlSugar.Updateable<Crm_ClientDataAndUser>()
                          .Where(x => x.usersId == 302 && clients_ids.Contains(x.NewClientDataId) && x.IsDel == 0)
                          .SetColumns(x => new Crm_ClientDataAndUser
                          {
                              usersId = 21
                          })
                          .ExecuteCommand();
            }

            if (updateCount == clients.Count)
            {
                _sqlSugar.CommitTran();
            }
            else
            {
                _sqlSugar.RollbackTran();
            }


            return Ok(new
            {
                count = clients.Count,
                updateCount,
            });
        }

        [HttpPost]
        public IActionResult InsertDataJiang()
        {
            var list_DB = _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0).ToList();
            foreach (var item in list_DB)
            {
                EncryptionProcessor.DecryptProperties(item);
            }

            list_DB = list_DB.Where(x => !string.IsNullOrWhiteSpace(x.Client) && (x.Client.Contains("学院") || x.Client.Contains("大学"))).ToList();

            var arr = list_DB.Select(x => x.Id).ToList().Select(x => new Crm_ClientDataAndUser
            {
                NewClientDataId = x,
                usersId = 327,
                CreateTime = DateTime.Now,
                CreateUserId = 235,
                IsDel = 0,
            }).ToList();
            var insertCount = _sqlSugar.Insertable(arr).ExecuteCommand();

            return Ok(insertCount);
        }

        [HttpPost]
        public IActionResult InsertDataGu()
        {
            string sql = @"SELECT  *  FROM  Crm_NewClientData cncd  WHERE  IsDel  = 0 
	                      AND  Lvlid in (
		                      select Id  from Sys_SetData where STid = 33 and isdel = 0 
		                      and (Name like '%云%南%' or Name like '%昆%明%')
	                      )";

            var list = _sqlSugar.SqlQueryable<Crm_NewClientData>(sql).Select(x => x.Id).ToList();

            var toUserList = new List<Crm_ClientDataAndUser>();

            foreach (var item in list)
            {
                toUserList.Add(new Crm_ClientDataAndUser
                {
                    CreateTime = DateTime.Now,
                    CreateUserId = 235,
                    NewClientDataId = item,
                    usersId = 364,
                    IsDel = 0,
                });
            }

            var updateCount = 0;
            if (toUserList.Count == 327)
            {
                updateCount = _sqlSugar.Insertable(toUserList).ExecuteCommand();
            }

            return Ok(new
            {
                updateCount
            });

        }

        [HttpPost]
        public IActionResult CloneUserClient(CloneUserClientDto dto)
        {
            var list = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
                                 .Where(x => x.IsDel == 0 && x.usersId == dto.UserId)
                                 .ToList();

            foreach (var item in list)
            {
                item.usersId = dto.ToUserId;
            }

            var count = _sqlSugar.Insertable<Crm_ClientDataAndUser>(list).ExecuteCommand();

            return Ok(new
            {
                count,
            });
        }

        #endregion

        /// <summary>
        /// 客户资料 操作记录记录查询
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        [HttpGet]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> NewClientDataOperatingRrecord(int userId)
        {

            var datas = await _sqlSugar.Queryable<Crm_TableOperationRecord, Crm_NewClientData, Sys_Users>((tor, ncd, u) =>
             new JoinQueryInfos(
                JoinType.Left, tor.DataId == ncd.Id,
                JoinType.Left, tor.CreateUserId == u.Id
                ))
                .Where((tor, ncd, u) => tor.TableName == "Crm_NewClientData" && tor.CreateUserId == userId && (int)tor.OperationItem > 1)
                .Select((tor, ncd, u) => new
                {
                    tor.PortType,
                    tor.OperationItem,
                    tor.DataId,
                    ncd.Client,
                    u.CnName,
                    tor.CreateTime
                }).ToListAsync();

            var view = new List<dynamic>();

            if (datas.Any())
            {
                datas = datas.OrderByDescending(x => x.CreateTime).ToList();
                foreach (var item in datas)
                {
                    var text = item.OperationItem.GetDescription();

                    view.Add(new
                    {
                        Client = AesEncryptionHelper.Decrypt(item.Client),
                        item.PortType,
                        item.OperationItem,
                        text = text,
                        item.DataId,
                        item.CnName,
                        item.CreateTime
                    });
                }
            }
            return Ok(JsonView(view));

        }

        [HttpPost]
        public async Task<IActionResult> SearchClientByKeyword(SearchClientByKeywordDto dto)
        {
            var jw = JsonView(true);
            var expression = Expressionable.Create<Crm_NewClientData>()
                                           .And(x => x.IsDel == 0)
                                           .AndIF(dto.Lvlid != 0, x => x.Lvlid == dto.Lvlid)
                                           .AndIF(dto.Category != 0, x => x.Category == dto.Category);

            if (dto.Range != 0)
            {
                var rangeSetDataList = await _clientDataRepository.GetCachedRangeSetDataAsync(dto.Range);
                expression = expression.And(x => rangeSetDataList.Contains(x.Lvlid));
            }

            this.keyName += "_" + dto.UserId;
            //获取个人的客户
            if (dto.UserId != 21)
            {
                var userList = _clientDataRepository.GetNewExistClient(dto.UserId).Select(x => x.Id);
                var newClientData = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
                         .Where(x => x.IsDel == 0 && userList.Contains(x.usersId))
                         .Select(x => x.NewClientDataId)
                         .Distinct()
                         .ToList();
                expression = expression.And(x => newClientData.Contains(x.Id));
            }

            var exists = false;
            //var exists = await RedisFactory.CreateRedisRepository().KeyExistsAsync(keyName);
            //if (exists) exists = await RedisFactory.CreateRedisRepository().HashFieldExistAsync(keyName, dto.Keyword);

            if (!exists)
            {
                string field = "Id,Client";
                var newClitnData = _sqlSugar.Queryable<Crm_NewClientData>()
                                            .Where(expression.ToExpression())
                                            .Select(field)
                                            .ToList();
                foreach (var item in newClitnData)
                {
                    EncryptionProcessor.DecryptProperties(item);
                }

                var keywordArray = dto.Keyword.ToCharArray();
                var matches = newClitnData
                        .Where(fullString => !string.IsNullOrWhiteSpace(fullString.Client) &&
                             keywordArray.All(keyword => fullString.Client.Contains(keyword)))
                        .Select(x => new
                        {
                            x.Client,
                            x.Id
                        })
                        .DistinctBy(x => x.Client)
                        .ToList();

                if (matches.Any())
                {
                    //await RedisFactory.CreateRedisRepository().HashSetAsync(keyName, dto.Keyword, matches);
                    //await RedisFactory.CreateRedisRepository().KeyExpireInAsync(keyName, new TimeSpan(0,1,0));
                    jw.Data = matches;
                }
                else
                {
                    jw.Code = 201;
                    jw.Msg = "empty list";
                    jw.Data = new List<string>(1);
                }

            }
            else
            {
                jw.Data = await RedisFactory.CreateRedisRepository().HashGetAsync<List<string>>(keyName, dto.Keyword);
            }

            return Ok(jw);
        }

        [HttpGet("{userId}")]
        public async Task<IActionResult> SearchClientByUserId(int userId)
        {
            var sw = Stopwatch.StartNew();
            var jw = JsonView(true);
            if (!await _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.Id == userId).AnyAsync())
            {
                jw.Code = StatusCodes.Status400BadRequest;
                jw.Msg = $"请传入有效的UserId";
                return Ok(jw);
            }

            var expression = Expressionable.Create<Crm_NewClientData>().And(x => x.IsDel == 0);

            //获取个人的客户
            if (userId != 21)
            {
                var userList = _clientDataRepository.GetNewExistClient(userId).Select(x => x.Id);
                var newClientData = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
                    .Where(x => x.IsDel == 0 && userList.Contains(x.usersId))
                    .Select(x => x.NewClientDataId)
                    .Distinct()
                    .ToList();
                expression = expression.And(x => newClientData.Contains(x.Id));
            }

            string field = "Id,Client";
            var newClitnData = _sqlSugar.Queryable<Crm_NewClientData>()
                .Where(expression.ToExpression())
                .Select(field)
                .ToList();
            foreach (var item in newClitnData)
            {
                EncryptionProcessor.DecryptProperties(item);
            }

            var matches = newClitnData
                    .Where(fullString => !string.IsNullOrWhiteSpace(fullString.Client))
                    .OrderByDescending(x => x.Id)
                    .Select(x => new { x.Id, x.Client })
                    .DistinctBy(x => x.Client)
                    .ToList();

            if (matches.Any())
            {
                jw.Data = matches;
                jw.Count = matches.Count();
            }
            else
            {
                jw.Code = 201;
                jw.Msg = "empty list";
                jw.Data = new List<string>(1);
            }

            sw.Stop();
            jw.Msg = $"操作成功!耗时：{sw.Elapsed.TotalMilliseconds}ms.";
            return Ok(jw);
        }

        [HttpPost]
        public async Task<IActionResult> QueryUnlockDropList()
        {
            var jw = JsonView(true);
            var userList = await _sqlSugar.Queryable<Sys_SetData>().FirstAsync
                (x => x.Id == 1434 && x.IsDel == 0);

            try
            {
                if (userList != null)
                {
                    var result = JsonConvert.DeserializeObject<List<int>>(userList.Remark);
                    jw.Data = result;
                }
            }
            catch (Exception)
            {
                jw.Code = 500;
                jw.Msg = "数据源错误!";
                jw.Data = new List<int>(1);
            }

            return Ok(jw);
        }

        [HttpPost]
        public IActionResult InsertDataExcel(InsertDataExcelDto dto)
        {
            //string client = dto.Client;

            int toUser = dto.ToUser;

            var clietnArr = dto.Client;

            var count = 0;

            //var notClentUser = new List<int>() { 21, 95  , 327 };
            var notClentUser = new List<int>() { 213, 327 };

            var insertList = new List<Crm_ClientDataAndUser>();

            if (clietnArr.Count > 0)
            {
                _sqlSugar.BeginTran();


                //检索相关客户数据
                var newClientArr = _sqlSugar.Queryable<Crm_NewClientData>()
                    .Where(x => x.IsDel == 0)
                    .Select(x => new Crm_NewClientData
                    {
                        Client = x.Client,
                        Id = x.Id
                    })
                    .ToList()
                    .Select(x => new
                    {
                        Client = AesEncryptionHelper.Decrypt(x.Client),
                        Id = x.Id
                    });

                foreach (var item in clietnArr)
                {
                    var searchClient = newClientArr.Where(x => x.Client == item).ToList();

                    foreach (var clientData in searchClient)
                    {
                        var clientDataAndUser = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
                                                .Where(x => x.NewClientDataId == clientData.Id && x.IsDel == 0)
                                                .ToList();

                        if (clientDataAndUser.Count > 0)
                        {
                            //清除关联表数据
                            _sqlSugar.Updateable<Crm_ClientDataAndUser>()
                                .Where(x => !notClentUser.Contains(x.usersId)
                                && x.NewClientDataId == clientData.Id && x.IsDel == 0)
                                .SetColumns(x => new Crm_ClientDataAndUser
                                {
                                    IsDel = 1,
                                    DeleteTime = DateTime.Now.ToString(),
                                    DeleteUserId = 235
                                })
                                .ExecuteCommand();
                        }

                        insertList.Add(new Crm_ClientDataAndUser
                        {
                            usersId = toUser,
                            NewClientDataId = clientData.Id,
                            CreateTime = DateTime.Now,
                            CreateUserId = 235,
                        });
                    }
                }

                count = _sqlSugar.Insertable(insertList).ExecuteCommand();

                _sqlSugar.CommitTran();
            }

            return Ok(new
            {
                count,
            });
        }

        /// <summary>
        /// 客户资料分配（按照数据ID） - UserId
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult InsertDataExcel1(IFormFile file)
        {

            // 检查文件是否为空
            if (file == null || file.Length == 0)
            {
                return BadRequest("No file uploaded.");
            }

            // 保存文件到服务器
            var uploadsFolder = Path.Combine(Directory.GetCurrentDirectory(), "File");
            if (!Directory.Exists(uploadsFolder))
            {
                Directory.CreateDirectory(uploadsFolder);
            }

            var filePath = Path.Combine(uploadsFolder, file.FileName);
            using (var stream = new FileStream(filePath, FileMode.Create))
            {
                file.CopyToAsync(stream);
            }

            Workbook workbook = new Workbook(filePath);

            // 获取第一个工作表
            Worksheet worksheet = workbook.Worksheets[0];

            // 获取表头（第一行作为列名）
            int headerRowIndex = 0; // 假设第一行是表头
            Aspose.Cells.Row headerRow = worksheet.Cells.Rows[headerRowIndex];
            int colCount = worksheet.Cells.MaxDataColumn + 1;

            // 动态存储列名
            var clients = new List<Crm_ClientDataAndUser>();

            // 遍历数据行（从第二行开始）
            int rowCount = worksheet.Cells.MaxDataRow + 1;
            for (int row = headerRowIndex + 1; row < rowCount; row++)
            {
                var cellVal1 = worksheet.Cells[row, 0].Value;  //序号
                var cellVal2 = worksheet.Cells[row, 1].Value;  //数据Id
                var cellVal3 = worksheet.Cells[row, 2].Value;  //userId

                int parentId = !string.IsNullOrEmpty(cellVal2?.ToString()) ? int.Parse(cellVal2?.ToString()) : 0;
                int userId = !string.IsNullOrEmpty(cellVal3?.ToString()) ? int.Parse(cellVal3?.ToString()) : 0;
                var rowData = new Crm_ClientDataAndUser()
                {
                    usersId = userId,
                    NewClientDataId = parentId
                };

                clients.Add(rowData); // 将当前行数据添加到集合中
            }

            var count = 0;

            //var notClentUser = new List<int>() { 21, 95  , 327 };
            var notClentUser = new List<int>() { 21, 95, 327, 213 };

            var insertList = new List<Crm_ClientDataAndUser>();

            if (clients.Count > 0)
            {
                _sqlSugar.BeginTran();


                //检索相关客户数据
                var newClientArr = _sqlSugar.Queryable<Crm_NewClientData>()
                    .Where(x => x.IsDel == 0)
                    .Select(x => new Crm_NewClientData
                    {
                        Client = x.Client,
                        Id = x.Id
                    })
                    .ToList();

                foreach (var item in clients)
                {
                    var searchClient = newClientArr.Where(x => x.Id == item.NewClientDataId).ToList();

                    foreach (var clientData in searchClient)
                    {
                        var clientDataAndUser = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
                                                .Where(x => x.NewClientDataId == clientData.Id && x.IsDel == 0)
                                                .ToList();

                        if (clientDataAndUser.Count > 0)
                        {
                            //清除关联表数据
                            _sqlSugar.Updateable<Crm_ClientDataAndUser>()
                                .Where(x => !notClentUser.Contains(x.usersId)
                                && x.NewClientDataId == clientData.Id && x.IsDel == 0)
                                .SetColumns(x => new Crm_ClientDataAndUser
                                {
                                    IsDel = 1,
                                    DeleteTime = DateTime.Now.ToString(),
                                    DeleteUserId = 235
                                })
                                .ExecuteCommand();
                        }

                        insertList.Add(new Crm_ClientDataAndUser
                        {
                            usersId = item.usersId,
                            NewClientDataId = clientData.Id,
                            CreateTime = DateTime.Now,
                            CreateUserId = 235,
                        });
                    }
                }

                count = _sqlSugar.Insertable(insertList).ExecuteCommand();

                _sqlSugar.CommitTran();
            }

            return Ok(new
            {
                count,
            });
        }


        /// <summary>
        /// 读取excel分配客户
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public IActionResult ReadExcelAllocation(IFormFile file, int userId)
        {
            if (file == null || file.Length == 0)
            {
                return BadRequest("No file uploaded.");
            }

            var uploadsFolder = Path.Combine(Directory.GetCurrentDirectory(), "File");
            if (!Directory.Exists(uploadsFolder))
            {
                Directory.CreateDirectory(uploadsFolder);
            }

            var filePath = Path.Combine(uploadsFolder, file.FileName);
            using (var stream = new FileStream(filePath, FileMode.Create))
            {
                file.CopyTo(stream);
            }

            Workbook workbook = new Workbook(filePath);
            Worksheet worksheet = workbook.Worksheets[0];

            int headerRowIndex = 0; // 第一行为表头
            int rowCount = worksheet.Cells.MaxDataRow + 1;

            var notids = 0;

            // 第1列（索引0）：NewClientDataId（客户数据ID）
            var clients = new List<Crm_ClientDataAndUser>();
            for (int row = headerRowIndex + 1; row < rowCount; row++)
            {
                var cellVal1 = worksheet.Cells[row, 0].Value;  // 数据id

                int newClientDataId = !string.IsNullOrEmpty(cellVal1?.ToString())
                    ? int.Parse(cellVal1.ToString())
                    : 0;

                if (newClientDataId <= 0)
                {
                    notids++;
                    // 跳过无效行
                    continue;
                }

                clients.Add(new Crm_ClientDataAndUser
                {
                    NewClientDataId = newClientDataId,
                    usersId = userId,
                    CreateTime = DateTime.Now,
                    CreateUserId = 235,
                    IsDel = 0,
                });
            }

            var count = 0;
            var updateCount = 0;
            var ids = clients.Select(x => x.NewClientDataId).ToList();

            if (clients.Count > 0)
            {
                _sqlSugar.BeginTran();


                //清除所有id旧数据
                updateCount = _sqlSugar.Updateable<Crm_ClientDataAndUser>()
                   .Where(x => x.IsDel == 0)
                   .Where(x => ids.Contains(x.NewClientDataId))
                   .SetColumns(x => new Crm_ClientDataAndUser
                   {
                       IsDel = 1,
                       DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                       DeleteUserId = 235
                   })
                   .ExecuteCommand();

                //分配新数据
                count = _sqlSugar.Insertable(clients).ExecuteCommand();

                _sqlSugar.CommitTran();
            }

            return Ok(new
            {
                insertCount = count,
                updateCount = updateCount,
                notids
            });
        }

        [HttpPost]
        public IActionResult SchoolAllocation()
        {
            var clients = _sqlSugar.Queryable<Crm_NewClientData>()
               .Where(x => x.IsDel == 0)
               .Select(x => new Crm_NewClientData { Id = x.Id, Client = x.Client })
               .ToList();

            var keys = new List<string>()
            {
                "大学", "学院", "中学", "教育", "学校", "一中", "二中", "七中", "十五中", "二十五中","高校"
            };

            var count = 0;
            var insertArr = new List<Crm_ClientDataAndBusiness>();

            // --368  教育

            foreach (var item in clients)
            {
                EncryptionProcessor.DecryptProperties(item);

                foreach (var key in keys)
                {
                    if (!string.IsNullOrWhiteSpace(item.Client) && item.Client.Contains(key))
                    {
                        insertArr.Add(new Crm_ClientDataAndBusiness
                        {
                            NewClientDataId = item.Id,
                            SetDataId = 368, // 教育
                            CreateTime = DateTime.Now,
                            CreateUserId = 235,
                            IsDel = 0
                        });
                    }
                }
            }

            count = _sqlSugar.Insertable(insertArr)
                 .ExecuteCommand();

            return count > 0 ? Ok(JsonView(true, "分配成功！", count)) : Ok(JsonView(false, "分配失败！"));
        }

        /// <summary>
        /// 根据提供的数据id导出对应的excel
        /// </summary>
        /// <param name="ids">数据id</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> ExportExcelByIds(List<int> ids)
        {
            try
            {
                if (ids == null || ids.Count == 0)
                {
                    return Ok(JsonView(false, "请提供要导出的客户数据ID"));
                }

                // 查询客户数据
                var clientDatas = await _sqlSugar.Queryable<Crm_NewClientData>()
                    .Where(x => ids.Contains(x.Id) && x.IsDel == 0)
                    .OrderByDescending(x => x.CreateTime)
                    .ToListAsync();

                if (clientDatas == null || clientDatas.Count == 0)
                {
                    return Ok(JsonView(false, "未找到要导出的客户数据"));
                }

                var newClientDataView = new List<NewClientDataExcelDownloadView>();
                foreach (var client in clientDatas)
                {
                    var view = new NewClientDataExcelDownloadView
                    {
                        Id = client.Id,
                        Number = client.Number,
                        Lvlid = client.Lvlid,
                        Client = client.Client,
                        Weight = client.Weight,
                        ClientShort = client.ClientShort,
                        Contact = client.Contact,
                        Gender = client.Gender,
                        Passport = client.Passport,
                        PassportDate = client.PassportDate,
                        Job = client.Job,
                        Telephone = client.Telephone,
                        Phone = client.Phone,
                        Email = client.Email,
                        Location = client.Location,
                        Address = client.Address,
                        Birthday = client.Birthday,
                        OtherInfo = client.OtherInfo,
                        Wechat = client.Wechat,
                        Category = client.Category,
                        PreDele = client.PreDele,
                        FinlishedDele = client.FinlishedDele,
                        LastUpdateUserId = client.LastUpdateUserId,
                        LastUpdateTime = client.LastUpdateTime,
                        CreateTime = client.CreateTime,
                        CreateUserId = client.CreateUserId,
                        IsDel = client.IsDel
                    };
                    newClientDataView.Add(view);
                }

                // 查询关联数据
                var userDatas = await _sqlSugar.Queryable<Sys_Users>().ToListAsync();
                var setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0).ToListAsync();
                var clientIds = clientDatas.Select(x => x.Id).ToList();

                List<AscribedUser> ascribedUser = new List<AscribedUser>();
                List<AscribedDepartment> ascribedDepartment = new List<AscribedDepartment>();

                if (clientIds.Count > 0)
                {
                    ascribedUser = await _sqlSugar.SqlQueryable<AscribedUser>(
                        $"select u1.UsersId as UserId ,u2.CnName,u1.NewClientDataId from Crm_ClientDataAndUser u1,Sys_Users u2 where u1.UsersId=u2.Id AND u1.ISDEL = 0 AND u1.NewClientDataId IN ({string.Join(",", clientIds)})")
                        .ToListAsync();

                    ascribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>(
                        $"select d2.Id,d2.Name,d1.NewClientDataId from Crm_ClientDataAndBusiness d1,Sys_SetData d2 where d1.SetDataId=d2.Id AND d1.ISDEL = 0 AND d1.NewClientDataId IN ({string.Join(",", clientIds)})")
                        .ToListAsync();
                }

                // 填充额外字段
                int index = 1;
                foreach (var item in newClientDataView)
                {
                    EncryptionProcessor.DecryptProperties(item); // 解密

                    item.RowNumber = index;
                    item.CreateUserName = userDatas.Find(x => x.Id == item.CreateUserId)?.CnName ?? "-";
                    item.CategoryStr = setDatas.Find(x => x.Id == item.Category)?.Name ?? "-";
                    item.LvlidStr = setDatas.Find(x => x.Id == item.Lvlid)?.Name ?? "-";

                    var currAscribedUser = ascribedUser.Where(x => x.NewClientDataId == item.Id).ToList();
                    if (currAscribedUser.Any())
                    {
                        item.AscribedUserLable = string.Join("、", currAscribedUser.Select(x => x.CnName).ToList());
                    }

                    var currAscribedDepartment = ascribedDepartment.Where(x => x.NewClientDataId == item.Id).ToList();
                    if (currAscribedDepartment.Any())
                    {
                        item.AscribedDepartmentLable = string.Join("、", currAscribedDepartment.Select(x => x.Name).ToList());
                    }

                    index++;
                }

                // 使用模板生成Excel
                string tempPath = AppSettingsHelper.Get("ExcelBasePath") + "Template/公司客户资料导出模板.xlsx";
                var designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(tempPath);
                designer.SetDataSource("NCDDT", newClientDataView);
                designer.Process();

                // 文件名
                string fileName = $"公司客户资料(按ID导出){DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";

                // 确保目录存在
                string savePath = AppSettingsHelper.Get("ExcelBasePath") + "NewClientDataExcelDownload/";
                if (!Directory.Exists(savePath))
                {
                    Directory.CreateDirectory(savePath);
                }

                designer.Workbook.Save(savePath + fileName);
                string url = AppSettingsHelper.Get("ExcelBaseUrl") + "Office/Excel/NewClientDataExcelDownload/" + fileName;

                return Ok(JsonView(true, "导出成功", url));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, $"导出失败：{ex.Message}"));
            }
        }

        /// <summary>
        /// 获取IP信息
        /// </summary>
        /// <param name="ip">ipv4 or ipv6</param>
        /// <returns></returns>
        private async Task<(string ip, string local)> GetIpInfo(string ip)
        {
            string local = string.Empty;

            if (IPAddress.TryParse(ip, out _))
            {
                using HttpClient _httpClient = new HttpClient();
                var response = await _httpClient.GetAsync($"https://api.vore.top/api/IPdata?ip={ip}");
                response.EnsureSuccessStatusCode();
                var json = await response.Content.ReadAsStringAsync();
                var ipInfo = Newtonsoft.Json.JsonConvert.DeserializeObject<dynamic>(json);
                if (ipInfo.code == 200)
                {
                    ip = ipInfo.ipinfo.text;
                    local = $"{ipInfo.adcode.o}";
                }
            }

            return (ip, local);
        }

        #region 回滚数据记录
        //[HttpPost]
        //public async Task<IActionResult> actionResult()
        //{
        //    var jw = JsonView(true);
        //    var sql = @" SELECT * FROM OA2023DB.dbo.Crm_TableOperationRecord 
        //                 WHERE TableName ='Crm_NewClientData' 
        //                 AND OperationItem IN(4) AND  CreateTime > '2025-02-03' AND  CreateTime < '2025-03-06' 
        //                 AND ReturnResult LIKE '%修改成功%'	";

        //    var list = _sqlSugar.SqlQueryable<Crm_TableOperationRecord>(sql).Select(x=>new Crm_TableOperationRecord
        //    {
        //        RequestParam = x.RequestParam
        //    }).ToList();

        //    foreach (var item in list)
        //    {
        //        var string1 = item.RequestParam.Trim('"').Replace("\\", "");
        //        //if (!string.IsNullOrEmpty(item.RequestParam))
        //        //{

        //        //    var req = item.RequestParam;

        //        //    if (CommonFun.IsValidJson(item.RequestParam))
        //        //    {

        //        //        var res1 = JToken.Parse(item.RequestParam);

        //        //    }


        //        //    NewClientOpDto dto = JsonConvert.DeserializeObject<NewClientOpDto>(JsonConvert.DeserializeObject<string>(item.RequestParam));
        //            NewClientOpDto dto1 = JsonConvert.DeserializeObject<NewClientOpDto>(string1);
        //            var result = await this.NewClientOp(dto1);
        //        //}
        //    }

        //    return Ok(jw);
        //}
        #endregion
    }
}