OA2023/OASystem/OASystem.Api/Controllers/SystemController.cs

using Aspose.Cells;
using NPOI.HPSF;
using NPOI.POIFS.Crypt;
using OASystem.API.OAMethodLib;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Attributes;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using System.Collections;
using System.Data;
using System.Dynamic;
using System.Linq;
using static OASystem.API.OAMethodLib.GeneralMethod;
using static OASystem.API.OAMethodLib.JWTHelper;
using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 系统设置
    /// </summary>
    //[Authorize]
    [Route("api/[controller]/[action]")]
    public class SystemController : ControllerBase
    {
        private readonly CompanyRepository _syscomRep;
        private readonly DepartmentRepository _sysDepRep;
        private readonly UsersRepository _userRep;
        private readonly IMapper _mapper;
        private readonly IConfiguration _config;
        private readonly SqlSugarClient _sqlSugar;
        private readonly MessageRepository _messageRep;
        private readonly SetDataRepository _setDataRepository;
        private readonly SystemMenuPermissionRepository _SystemMenuPermissionRepository;
        private readonly CompanyRepository _CompanyRepository;
        private readonly PageFunctionPermissionRepository _PageFunctionPermissionRepository;
        private readonly SystemMenuAndFunctionRepository _SystemMenuAndFunctionRepository;
        private readonly JobPostAuthorityRepository _JobPostAuthorityRepository;
        private readonly JobPostRepository _jobRep;
        private readonly SetDataTypeRepository _setDataTypeRep;
        private readonly UserAuthorityRepository _UserAuthorityRepository;
        private readonly List<int> _operationTypeList = new() { 1, 2, 3, 4, 5 }; //操作通知所属类型
        private readonly List<int> _taskTypeList = new() { 6 };//任务通知 TaskNotification
        private readonly ApprovalProcessRepository _approvalProcessRep;

        public SystemController(
            CompanyRepository syscom,
            DepartmentRepository sysDepRep,
            UsersRepository userRep,
            IMapper mapper, IConfiguration config,
            SqlSugarClient sqlSugar,
            SetDataRepository setDataRepository,
            CompanyRepository companyRepository,
            SystemMenuPermissionRepository systemMenuPermissionRepository,
            PageFunctionPermissionRepository pageFunctionPermissionRepository,
            SystemMenuAndFunctionRepository systemMenuAndFunctionRepository,
            JobPostAuthorityRepository jobPostAuthorityRepository,
            JobPostRepository jobRep,
            UserAuthorityRepository userAuthorityRepository,
            MessageRepository messageRep,
            SetDataTypeRepository setDataTypeRep,
            ApprovalProcessRepository approvalProcessRep
            )
        {
            _syscomRep = syscom;
            _sysDepRep = sysDepRep;
            _messageRep = messageRep;
            _userRep = userRep;
            _mapper = mapper;
            _sqlSugar = sqlSugar;
            _setDataRepository = setDataRepository;
            _CompanyRepository = companyRepository;
            _SystemMenuPermissionRepository = systemMenuPermissionRepository;
            _PageFunctionPermissionRepository = pageFunctionPermissionRepository;
            _SystemMenuAndFunctionRepository = systemMenuAndFunctionRepository;
            _JobPostAuthorityRepository = jobPostAuthorityRepository;
            _UserAuthorityRepository = userAuthorityRepository;
            _jobRep = jobRep;
            _setDataTypeRep = setDataTypeRep;
            _config = config;
            _approvalProcessRep = approvalProcessRep;
        }
        #region 消息

        /// <summary>
        /// 获取消息列表-整合版
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PsotMsgPageList(MsgDto dto)
        {
            var msgData = await _messageRep.GetMsgList(dto);
            return msgData.Code != 0 ? Ok(JsonView(false, msgData.Msg)) : Ok(JsonView(msgData.Data));
        }

        #region 消息列表 - 分开

        /// <summary>
        /// 系统消息
        /// 消息类型  2024-03-06 14：37
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PotsMsgTypeData(MsgTypeDto dto)
        {
            if (dto.PortType < 1 || dto.PortType > 3) 
                return Ok(JsonView(false, MsgTips.Port));

            if (dto.UserId < 1) 
                return Ok(JsonView(false, "请输入有效的UserId参数。"));

            var msgData = await _messageRep.PotsMsgTypeData(dto);

            return msgData.Code != 0 ? Ok(JsonView(400, msgData.Msg, new string[] { })) : Ok(JsonView(true, msgData.Msg, msgData.Data));
        }

        /// <summary>
        /// 系统消息
        /// 消息List  2024-03-06 14：54
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PotsMessagePageList(PotsMessagePageListDto dto)
        {
            #region 参数验证

            if (dto.PortType < 1 || dto.PortType > 3)
                return Ok(JsonView(false, MsgTips.Port));

            var typeData = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.STid == 77).Select(it => it.Id).ToListAsync();
            if (typeData.Count < 0) 
                return Ok(JsonView(false, "消息类型不存在"));

            if (!typeData.Contains(dto.Type)) 
                return Ok(JsonView(false, "请输入有效的Type参数。1021 团组操作通知 1020 任务操作通知 "));
            if (dto.UserId < 1) 
                return Ok(JsonView(false, "请输入有效的UserId参数。"));

            if (dto.ReadStatus < 1 || dto.ReadStatus > 3) 
                return Ok(JsonView(false, "请输入有效的ReadStatus参数。1 全部(包含已读/未读) 2 未读 3 已读"));
            #endregion

            //userId
            string msgSqlWhere = $" And smra.ReadableUId = {dto.UserId}";

            //消息类型
            string typeStr = "";
            List<NotificationTypeView> messageTypeViews = AppSettingsHelper.Get<NotificationTypeView>("MessageNotificationType");
            if (dto.Type == 1020) //任务操作通知 
            {
                typeStr = String.Join(",", messageTypeViews.Where(it => it.TypeId == 1020).FirstOrDefault().MsgTypeIds.ToList());
            }
            else if (dto.Type == 1021)//团组操作通知
            {
                typeStr = String.Join(",", messageTypeViews.Where(it => it.TypeId == 1021).FirstOrDefault().MsgTypeIds.ToList());
            }
            else if (dto.Type == 1022)//公告通知
            {
                typeStr = String.Join(",", messageTypeViews.Where(it => it.TypeId == 1022).FirstOrDefault().MsgTypeIds.ToList());
            }

            if (!string.IsNullOrEmpty(typeStr))
            {
                msgSqlWhere += $" And sm.Type In ({typeStr})";
            }

            //是否已读处理 1 全部(包含已读/未读) 2 未读 3 已读

            msgSqlWhere += dto.ReadStatus switch
            {
                2 => " And smra.IsRead = 0",
                3 => " And smra.IsRead = 1",
                _ => ""
            };

            string msgSql = string.Format(@"Select * From(
                                                Select row_number() over(order by sm.ReleaseTime Desc) as RowNumber,
                                                    sm.Id,sm.Type,sm.Title,sm.Content,sd.DepName issuerDep,su.CnName issuerUser, 
                                                    sm.ReleaseTime,smra.ReadableUId,smra.IsRead,sm.DiId,sm.Param
                                                From Sys_Message sm 
                                                Inner Join Sys_MessageReadAuth smra On sm.Id = smra.MsgId
                                                Inner Join Sys_Users su On sm.IssuerId = su.Id
                                                Inner Join Sys_Department sd On su.DepId = sd.Id
                                                Inner Join Sys_Users suAuth On smra.ReadableUId = suAuth.Id
                                                Where sm.IsDel = 0
                                                And smra.IsDel = 0 {0}
                                            ) Temp", msgSqlWhere);


            try
            {
                RefAsync<int> totalCount = 0;
                var data = await _sqlSugar.SqlQueryable<MessageListView>(msgSql).ToPageListAsync(dto.PageIndex, dto.PageSize, totalCount);

                return Ok(JsonView(true, "操作成功!", data, totalCount));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }

        }

        /// <summary>
        /// 系统消息
        /// 获取消息未读条数
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PotsMessageUnreadTotalCount(PotsMessageUnreadTotalCountDto dto)
        {
            #region 参数验证

            if (dto.UserId < 1)
                return Ok(JsonView(false, "请输入有效的UserId参数。"));

            #endregion

            try
            {

                #region 限制人员登录
                var userInfo = await _sqlSugar
                    .Queryable<Sys_Users>()
                    .Where((u) => u.Id == dto.UserId)
                    .Select((u) => new
                    {
                        CnName = u.CnName
                    })
                    .FirstAsync();

                var noLoginAuth = _config.GetSection("NoLoginAuth").Get<List<string>>();
                if (noLoginAuth.Any())
                {
                    if (noLoginAuth.Contains(userInfo.CnName)) return Ok(JsonView(204, "NO ACCESS!!", ""));
                }

                //其他市场部人员 限制登录时间段
                //var currentDateTime = DateTime.Now;
                //var startTime = DateTime.Parse(_config["ApiAccessTime:StartTime"]);
                //var endTime = DateTime.Parse(_config["ApiAccessTime:EndTime"]);
                //if (currentDateTime < startTime && currentDateTime > endTime) return Ok(JsonView(204, "NO ACCESS!!", ""));
                #endregion

                int messageUnReadCount = await _messageRep.GetUnReadCount(dto.UserId);
                int announcementUnReadCount = await _messageRep.GetAnnouncementUnReadCount(dto.UserId);
                
                return Ok(JsonView(true, "操作成功!", new { messageUnReadCount = messageUnReadCount, announcementUnReadCount = announcementUnReadCount }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }

        }

        #endregion

        /// <summary>
        /// 获取消息详细信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMsgInfo(MsgInfoDto dto)
        {
            if (dto.PortType < 1 || dto.PortType > 3)
                return Ok(JsonView(false, MsgTips.Port));

            var msgData = await _messageRep.GetMsgInfo(dto);

            return msgData.Code != 0 ? Ok(JsonView(false, msgData.Msg)) : Ok(JsonView(true, "操作成功!", msgData.Data));
        }

        /// <summary>
        /// 消息设置已读
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SetMessageRead(MsgSetReadDto dto)
        {
            var msgData = await _messageRep.SetMsgRead(dto);
            return msgData.Code != 0 ? Ok(JsonView(false, msgData.Msg)) : Ok(JsonView(true));
        }

        /// <summary>
        /// 消息 删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DeleMsg(MsgDeleteDto dto)
        {
            var msgData = await _messageRep.DelMsg(dto);
            return msgData.Code != 0 ? Ok(JsonView(false, msgData.Msg)) : Ok(JsonView(true));
        }
        #endregion

        #region 数据类型资料
        /// <summary>
        /// 根据类型查询数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QuerySetData(SetDataDto dto)
        {
            try
            {
                if (dto.DataType == 0)
                {
                    return Ok(JsonView(false, "请传类型Id！"));
                }
                var setData = _setDataRepository.QueryDto<Sys_SetData, SetDataView>(s => s.STid == dto.DataType && s.IsDel == 0).ToList();
                if (setData.Count == 0)
                {
                    return Ok(JsonView(false, "暂无数据！"));
                }
                return Ok(JsonView(true, "查询成功！", setData));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
            }
        }


        /// <summary>
        /// 根据类型查询数据(Array)
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public IActionResult QuerySetDataInitByArr(QuerySetDataInitByArr Dto)
        {
            if (Dto.DataTypeArr == null || !Dto.DataTypeArr.Any())
            {
                return Ok(JsonView(false, "请传入有效的类型数组！"));
            }

            var DbQuery = _setDataRepository.QueryDto<Sys_SetData, SetDataView>(s => Dto.DataTypeArr.Contains(s.STid)).ToList();
            var GroupResult = DbQuery.GroupBy(x => x.STid).Select(x => new
            {
                key = x.Key,
                Arr = x.ToList()
            });
            return Ok(JsonView(true, "查询成功！", GroupResult));
        }

        /// <summary>
        /// 数据类型表查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QuerySetDataType(SetDataTypeDto dto)
        {
            try
            {
                Result setDataType = await _setDataTypeRep.QuerySetDataType(dto);
                if (setDataType.Code == 0)
                {
                    return Ok(JsonView(true, "查询成功", setDataType.Data));
                }
                else
                {
                    return Ok(JsonView(false, setDataType.Msg));
                }
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "程序错误！"));
            }
        }

        /// <summary>
        /// 数据类型表操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OperationSetDataType(OperationSetDataTypeDto dto)
        {
            if (dto == null || string.IsNullOrEmpty(dto.Name))
            {
                return Ok(JsonView(false, "请检查类型名称是否填写！"));
            }

            try
            {
                Result result = await _setDataTypeRep.OperationSetDataType(dto);
                if (result.Code != 0)
                {
                    return Ok(JsonView(false, result.Msg));
                }
                return Ok(JsonView(true, result.Msg));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
            }
        }

        /// <summary>
        /// 数据类型表操作删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelSetDataType(DelSetDataTypeDto dto)
        {
            if (dto == null || dto.Id <= 0)
                return Ok(JsonView(false, "请传入有效的Id参数！"));

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

            try
            {
                var res = await _setDataTypeRep.SoftDeleteByIdAsync<Sys_SetDataType>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
            }
        }
        #endregion

        #region 数据类型板块
        /// <summary>
        /// 数据类型板块表查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QuerySetDataInfo(SetDataIDto dto)
        {
            try
            {
                Result setData = await _setDataRepository.QuerySetData(dto);
                if (setData.Code == 0)
                {
                    return Ok(JsonView(true, "查询成功", setData.Data));
                }
                else
                {
                    return Ok(JsonView(false, setData.Msg));
                }
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }

        /// <summary>
        /// 数据类型板块表操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OperationSetData(OperationSetDataDto dto)
        {
            if (string.IsNullOrEmpty(dto.Name)) return Ok(JsonView(false, "请检查板块名称是否填写！"));

            Result result = await _setDataRepository.OperationSetData(dto);

            if (result.Code != 0)
            {
                return Ok(JsonView(false, result.Msg));
            }
            return Ok(JsonView(true, result.Msg));
        }

        /// <summary>
        /// 数据类型表操作删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelSetData(DelSetDataDto dto)
        {
            try
            {
                var res = await _setDataRepository.SoftDeleteByIdAsync<Sys_SetData>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion

        #region 企业操作
        /// <summary>
        /// 查询企业数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> getCompanyList(DtoBase dto)
        {
            if (dto.PortType == 1)
            {
                var CompanyDataResult = _CompanyRepository.GetCompanyData();
                if (CompanyDataResult.Code != 0)
                {
                    return Ok(JsonView(CompanyDataResult.Msg));
                }
                List<CompanyView> companyListView = _mapper.Map<List<CompanyView>>(CompanyDataResult.Data);
                for (int i = 0; i < companyListView.Count; i++)
                {
                    if (companyListView[i].ParentCompanyId != 0)
                    {
                        companyListView[i].ParentCompanyName = companyListView.Find(x => x.Id == companyListView[i].ParentCompanyId).CompanyName;
                    }
                    if (companyListView[i].ContactUserId != 0)
                    {
                        var user = _userRep.QueryDto<Sys_Users, UserInfoWebView>(x => x.Id == companyListView[i].ContactUserId).ToList();
                        if (user.Count != 0)
                        {
                            companyListView[i].ContactUserName = user[0].CnName;
                        }

                    }

                }
                return Ok(JsonView(true, "查询成功！", companyListView));
            }
            else if (dto.PortType == 2)
            {
                var CompanyDataResult = _CompanyRepository.GetCompanyData();
                if (CompanyDataResult.Code != 0)
                {
                    return Ok(JsonView(CompanyDataResult.Msg));
                }

                return Ok(JsonView(true, "查询成功！", CompanyDataResult.Data));
            }
            else if (dto.PortType == 3)
            {
                return Ok(JsonView(false, "暂无数据！"));
            }
            else
            {
                return Ok(JsonView(false, "暂无数据！"));
            }
        }

        /// <summary>
        /// 添加企业数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AddCompany(AddCompanyDto dto)
        {
            if (string.IsNullOrWhiteSpace(dto.CompanyName) || dto.CreateUserId == 0 || string.IsNullOrWhiteSpace(dto.CompanyCode))
            {
                return Ok(JsonView(false, "请检查信息是否输入完整！"));
            }
            else if (string.IsNullOrWhiteSpace(dto.Tel))
            {
                return Ok(JsonView(false, "请检查联系方式是否输入正确！"));
            }
            else
            {
                Sys_Company _Company = _mapper.Map<Sys_Company>(dto);
                Result data = await _syscomRep.AddCompany(_Company);
                if (data.Code != 0)
                {
                    return Ok(JsonView(false, "添加失败！"));

                }
                return Ok(JsonView(true, "添加成功"));
            }


        }

        /// <summary>
        /// 企业修改
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditCompany(EditCompanyDto dto)
        {
            if (string.IsNullOrWhiteSpace(dto.CompanyName) || string.IsNullOrWhiteSpace(dto.CompanyCode) || string.IsNullOrWhiteSpace(dto.Address) || dto.ContactUserId == 0)
            {
                return Ok(JsonView(false, "请检查信息是否输入完整！"));
            }
            else if (string.IsNullOrWhiteSpace(dto.Tel))
            {
                return Ok(JsonView(false, "请检查联系方式是否输入正确！"));
            }
            else
            {
                bool res = await _syscomRep.UpdateAsync(a => a.Id == dto.Id, a => new Sys_Company
                {
                    CompanyName = dto.CompanyName,
                    CompanyCode = dto.CompanyCode,
                    Address = dto.Address,
                    ParentCompanyId = dto.ParentCompanyId,
                    Tel = dto.Tel,
                    ContactUserId = dto.ContactUserId,
                    Remark = dto.Remark,
                });
                if (!res) { return Ok(JsonView(false, "修改失败")); }
                return Ok(JsonView(true, "修改成功！"));
            }


        }

        /// <summary>
        /// 企业删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelCompany(DelCompanyDto dto)
        {
            bool res = await _syscomRep.SoftDeleteAsync<Sys_Company>(dto.Id.ToString());
            if (!res) { return Ok(JsonView(false, "删除失败")); }
            return Ok(JsonView(true, "删除成功"));
        }
        #endregion

        #region 部门操作

        /// <summary>
        /// 查询部门数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryDepartmentList(DepartmentDto dto)
        {

            if (dto.PortType == 1)
            {
                if (dto.CompanyId != 0)
                {
                    var result = _sysDepRep.QueryDto<Sys_Department, DepartmentIView>(s => s.CompanyId == dto.CompanyId).ToList();
                    if (result.Count == 0)
                    {
                        return Ok(JsonView(false, "暂无数据！"));
                    }
                    int resultCount = result.Count;
                    for (int i = 0; i < resultCount; i++)
                    {
                        if (result[i].ParentDepId != 0)
                        {
                            result[i].ParentDepName = result.Find(x => x.Id == result[i].ParentDepId).ParentDepName;
                        }
                        var company = _sysDepRep.QueryDto<Sys_Company, CompanyView>(s => s.Id == result[i].CompanyId).ToList();
                        if (company.Count != 0)
                        {
                            result[i].CompanyName = company[0].CompanyName;
                        }
                    }
                    return Ok(JsonView(true, "查询成功！", result));
                }
                else
                {
                    var result = _sysDepRep.QueryDto<Sys_Department, DepartmentIView>(s => s.IsDel <= 1).ToList();
                    if (result.Count == 0)
                    {
                        return Ok(JsonView(false, "暂无数据！"));
                    }
                    for (int i = 0; i < result.Count; i++)
                    {
                        if (result[i].ParentDepId != 0)
                        {
                            result[i].ParentDepName = result.Find(x => x.Id == result[i].ParentDepId).ParentDepName;
                        }
                        var company = _sysDepRep.QueryDto<Sys_Company, CompanyView>(s => s.Id == result[i].CompanyId).ToList();
                        if (company.Count != 0)
                        {
                            result[i].CompanyName = company[0].CompanyName;
                        }
                    }
                    return Ok(JsonView(true, "查询成功！", result));
                }
            }
            else if (dto.PortType == 2)
            {
                var result = _sysDepRep.QueryDto<Sys_Department, DepartmentIView>(s => s.CompanyId == dto.CompanyId).ToList();
                if (result.Count == 0)
                {
                    return Ok(JsonView(400, "暂无数据！", new List<string>()));
                }
                return Ok(JsonView(true, "查询成功！", result));
            }
            else if (dto.PortType == 3)
            {

            }
            else
            {

            }

            return Ok(JsonView(false, "暂无数据！"));
        }
        /// <summary>
        /// 部门添加
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AddDepartment(AddDepartmentDto dto)
        {
            try
            {
                if (dto.CreateUserId == 0 || string.IsNullOrWhiteSpace(dto.DepName) || dto.CompanyId == 0 || string.IsNullOrWhiteSpace(dto.DepCode))
                {
                    return Ok(JsonView(false, "请检查信息是否输入完整！"));
                }
                else
                {
                    Sys_Department _Department = _mapper.Map<Sys_Department>(dto);
                    int id = await _sysDepRep.AddAsyncReturnId(_Department);
                    if (id == 0)
                    {
                        return Ok(JsonView(false, "添加失败！"));
                    }
                    return Ok(JsonView(true, "添加成功！", new { Id = id }));
                }
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }


        }

        /// <summary>
        /// 部门修改
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditDepartment(EditDepartmentDto dto)
        {
            try
            {
                if (dto.Id == 0 || string.IsNullOrWhiteSpace(dto.DepName) || dto.CompanyId == 0 || string.IsNullOrWhiteSpace(dto.DepCode))
                {
                    return Ok(JsonView(false, "请检查信息是否输入完整！"));
                }
                else
                {
                    bool res = await _sysDepRep.UpdateAsync<Sys_Department>(a => a.Id == dto.Id, a => new Sys_Department
                    {
                        CompanyId = dto.CompanyId,
                        DepCode = dto.DepCode,
                        DepName = dto.DepName,
                        ParentDepId = dto.ParentDepId,
                        Remark = dto.Remark,
                    });
                    if (!res)
                    {
                        return Ok(JsonView(false, "修改失败！"));
                    }
                    return Ok(JsonView(true, "修改成功！"));
                }
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }


        }

        /// <summary>
        /// 部门删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelDepartment(DelDepartmentDto dto)
        {
            try
            {
                if (dto.Id == 0)
                {
                    return Ok(JsonView(-1, "请检查信息是否输入完整！", string.Empty));
                }
                else
                {
                    bool res = await _sysDepRep.SoftDeleteAsync<Sys_Department>(dto.Id.ToString());
                    if (!res)
                    {
                        return Ok(JsonView(false, "删除失败！"));
                    }
                    return Ok(JsonView(true, "删除成功！"));
                }
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "程序错误！"));
            }
        }
        #endregion

        #region 岗位板块
        /// <summary>
        /// 岗位查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryJobPost(QueryJobPostDto dto)
        {
            try
            {
                if (dto.PortType == 1)
                {
                    string sqlWhere = string.Empty;
                    if (dto.CompanyId != 0)
                    {
                        sqlWhere += string.Format(@" And jp.CompanyId={0}", dto.CompanyId);
                    }
                    if (dto.DepId != 0)
                    {
                        sqlWhere += string.Format(@" And jp.DepId={0}", dto.DepId);
                    }
                    sqlWhere += string.Format(@" And jp.IsDel={0}", 0);
                    if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                    {
                        Regex r = new Regex("And");
                        sqlWhere = r.Replace(sqlWhere, "Where", 1);
                    }
                    List<Sys_JobPostI> jobList = await _jobRep.QueryJobPost(sqlWhere);

                    List<JobPostView> List = _mapper.Map<List<JobPostView>>(jobList);
                    if (jobList.Count == 0)
                    {
                        return Ok(JsonView(false, "暂无数据！"));
                    }
                    return Ok(JsonView(true, "查询成功！", jobList));
                }
                else if (dto.PortType == 2)
                {
                    var result = _jobRep.QueryDto<Sys_JobPost, JobPostView>(s => s.CompanyId == dto.CompanyId && s.DepId == dto.DepId).ToList();
                    if (result.Count == 0)
                    {
                        return Ok(JsonView(false, "暂无数据！"));
                    }
                    return Ok(JsonView(true, "查询成功！", result));
                }
                else if (dto.PortType == 3)
                {
                    return Ok(JsonView(false, "暂无数据！"));
                }
                else
                {
                    return Ok(JsonView(false, "暂无数据！"));
                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 添加岗位
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AddJobPost(AddJobPostDto dto)
        {
            try
            {
                Sys_JobPost sys_Job = _mapper.Map<Sys_JobPost>(dto);
                int id = await _jobRep.AddAsyncReturnId(sys_Job);
                if (id == 0)
                {
                    return Ok(JsonView(false, "添加失败"));
                }
                return Ok(JsonView(true, "添加成功", new { Id = id }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 修改岗位
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditJobPost(EditJobPostDto dto)
        {
            try
            {
                bool res = await _jobRep.UpdateAsync<Sys_JobPost>(a => a.Id == dto.Id, a => new Sys_JobPost
                {
                    CompanyId = dto.CompanyId,
                    DepId = dto.DepId,
                    JobName = dto.JobName,
                    Remark = dto.Remark,
                });
                if (!res)
                {
                    return Ok(JsonView(false, "修改失败"));
                }
                return Ok(JsonView(true, "修改成功"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 删除岗位
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelJobPost(DelJobPostDto dto)
        {
            try
            {
                bool res = await _jobRep.SoftDeleteAsync<Sys_JobPost>(dto.Id.ToString());
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败！"));
                }
                return Ok(JsonView(true, "删除成功"));
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion

        #region 用户操作

        ///// <summary>
        ///// 用户表指定字段加密
        ///// </summary>
        ///// <param name="dto"></param>
        ///// <returns></returns>
        //[HttpPost]
        //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        //public async Task<IActionResult> UserBatchEncryption()
        //{
        //    var items = await _sqlSugar.Queryable<Sys_Users>().Select(x => new Sys_Users() { Id = x.Id, Phone = x.Phone, UrgentPhone = x.UrgentPhone, IDCard = x.IDCard }).ToListAsync();

        //    foreach (var item in items) EncryptionProcessor.EncryptProperties(item);

        //    var updItems = await _sqlSugar.Updateable(items).UpdateColumns(x => new { x.Phone, x.UrgentPhone, x.IDCard }).ExecuteCommandAsync();

        //    return Ok(JsonView(updItems));
        //}


        /// <summary>
        /// 查询所有员工名称
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetUserNameList(DtoBase dto)
        {
            var result = _userRep.GetUserNameList(dto.PortType);
            if (result.Result.Code != 0)
            {
                return Ok(JsonView(false, "暂无数据！"));

            }
            return Ok(JsonView(true, "查询成功！", result.Result.Data));
        }

        /// <summary>
        /// 查询所有员工（web）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetUserList(DtoBase dto)
        {
            var result = _userRep.GetUserList(dto.PortType, string.Empty);
            if (result.Result.Code != 0)
            {
                return Ok(JsonView(false, "暂无数据！"));

            }
            return Ok(JsonView(true, "查询成功！", result.Result.Data));
        }

        /// <summary>
        /// 查询用户数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryUserList(UserDto dto)
        {
            string sqlWhere = string.Format(" Where su.IsDel = 0 ");
            if (dto.CompanyId != 0)
            {
                sqlWhere += string.Format(@" And su.CompanyId={0}", dto.CompanyId);
            }
            if (dto.DepId != 0)
            {
                sqlWhere += string.Format(@" And su.DepId={0}", dto.DepId);
            }
            if (dto.JobPostId != 0)
            {
                sqlWhere += string.Format(@" And su.JobPostId={0}", dto.JobPostId);
            }

            List<UserInfo> _userList = await _userRep.QueryUser(sqlWhere);
            if (_userList.Count == 0)
            {
                return Ok(JsonView(400, "暂无数据！", new List<String>()));
            }

            foreach (var item in _userList) EncryptionProcessor.DecryptProperties(item);

            List<UserInfoWebView> userList = _mapper.Map<List<UserInfoWebView>>(_userList);
            return Ok(JsonView(true, "查询成功！", userList));
        }

        /// <summary>
        /// 员工信息 个人详细信息
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> UserInfo(UserInfoDto dto)
        {
            if (dto.Id < 1) return Ok(JsonView(false, "请选择有效的userId!"));
            if (dto.PortType < 1 || dto.PortType > 3) return Ok(JsonView(false, MsgTips.Port));

            var _view = await _sqlSugar.Queryable<Sys_Users>()
                                       .Where(x => x.IsDel == 0 && x.Id == dto.Id)
                                       .FirstAsync();

            //解密
            EncryptionProcessor.DecryptProperties(_view);

            var data = new
            {
                _view.Id,
                _view.CnName,
                _view.EnName,
                _view.Number,
                _view.CompanyId,
                _view.DepId,
                _view.JobPostId,
                _view.Password,
                _view.Sex,
                _view.Ext,
                _view.Phone,
                _view.UrgentPhone,
                _view.Email,
                _view.Address,
                _view.Edate,
                //_view.Seniority,
                _view.Birthday,
                _view.IDCard,
                _view.StartWorkDate,
                _view.GraduateInstitutions,
                _view.Professional,
                _view.Education,
                _view.TheOrAdultEducation,
                _view.MaritalStatus,
                _view.HomeAddress,
                _view.UsePeriod,
                _view.WorkExperience,
                _view.Certificate,
                //_view.QiyeChatUserId,
                _view.Remark
            };

            return Ok(JsonView(true, "操作成功!", data));
        }

        /// <summary>
        /// 员工信息 All信息修改
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditUserInfo(EditUserInfoDto _dto)
        {
            EditUserInfoDtoValidator validator = new EditUserInfoDtoValidator();
            var validatorRes = await validator.ValidateAsync(_dto);
            if (!validatorRes.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validatorRes.Errors) errors.AppendLine(error.ErrorMessage);

                return Ok(JsonView(false, errors.ToString()));
            }

            var userInfo = _mapper.Map<Sys_Users>(_dto);

            if (_dto.CurrUserId < 1) return Ok(JsonView(false, "暂无修改权限！"));

            //修改权限验证 指定人员 信息部门(4)和人事部刘一茹( 230)、赖红燕(309)
            List<int> userIds = new List<int>() {
                        4   ,//管理员
                        5   ,//杨俊霄
                        117 ,//人事审核号
                        208 ,//雷怡
                        230 ,//刘一茹
                        233 ,//刘华举
                        234 ,//蒋金辰
                        235 ,//袁榕烽
                        309 ,//赖红燕
                     };

            if (!userIds.Contains(_dto.CurrUserId)) return Ok(JsonView(false, "暂无修改权限！"));

            //加密
            EncryptionProcessor.EncryptProperties(userInfo);

            var res = await _sqlSugar.Updateable(userInfo)
                                     .IgnoreColumns(x => new
                                     {
                                         x.QiyeChatUserId,
                                         x.Rdate,
                                         x.Seniority,
                                         x.HrAudit,
                                         x.CreateUserId,
                                         x.CreateTime,
                                         x.DeleteUserId,
                                         x.DeleteTime,
                                         x.IsDel
                                     })
                                     .Where(x => x.Id == _dto.Id)
                                     .ExecuteCommandAsync();

            if (res > 0) return Ok(JsonView(true, "操作成功!"));

            return Ok(JsonView(false, "操作失败!"));
        }

        /// <summary>
        /// 修改用户信息（上级修改/分配 公司、部门、岗位、工号等信息）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditUser(EditUserDto dto)
        {
            if (dto.CurrUserId < 1) return Ok(JsonView(false, "暂无修改权限！"));

            //修改权限验证 指定人员 信息部门(4)和人事部刘一茹( 230)、赖红燕(309)
            var userIds = new List<int>() {
                        4   ,//管理员
                        5   ,//杨俊霄
                        117 ,//人事审核号
                        208 ,//雷怡
                        230 ,//刘一茹
                        233 ,//刘华举
                        234 ,//蒋金辰
                        235 ,//袁榕烽
                        309 ,//赖红燕
                     };

            if (!userIds.Contains(dto.CurrUserId)) return Ok(JsonView(false, "暂无修改权限！"));

            bool res = await _userRep.UpdateAsync<Sys_Users>(a => a.Id == dto.Id, a => new Sys_Users
            {
                Number = dto.Number,
                CompanyId = dto.CompanyId,
                DepId = dto.DepId,
                JobPostId = dto.JobPostId,
                Ext = dto.Ext,
                UsePeriod = dto.UsePeriod,
                //HrAudit = dto.HrAudit
            });
            if (!res)
            {
                return Ok(JsonView(false, "修改失败！"));
            }

            return Ok(JsonView(true, "修改成功！"));
        }

        /// <summary>
        /// 修改用户信息（登录用户修改个人信息）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditMyUser(EditMyUserDto dto)
        {
            if (string.IsNullOrWhiteSpace(dto.CnName) ||
                string.IsNullOrWhiteSpace(dto.Address) ||
                string.IsNullOrWhiteSpace(dto.IDCard) ||
                dto.Sex != 0 && dto.Sex != 1 ||
                string.IsNullOrWhiteSpace(dto.MaritalStatus) ||
                string.IsNullOrWhiteSpace(dto.HomeAddress) ||
                dto.Birthday >= DateTime.Now.AddYears(-1))
            {
                return Ok(JsonView(false, "请完善你的个人信息！"));
            }
            else if (string.IsNullOrWhiteSpace(dto.GraduateInstitutions) ||
                string.IsNullOrWhiteSpace(dto.Professional) ||
                dto.Education == 0 ||
                string.IsNullOrWhiteSpace(dto.GraduateInstitutions))
            {
                return Ok(JsonView(false, "请完善你的学历信息！"));
            }
            else if (string.IsNullOrWhiteSpace(dto.Phone) ||
                string.IsNullOrWhiteSpace(dto.UrgentPhone) ||
                string.IsNullOrWhiteSpace(dto.Email))
            {
                return Ok(JsonView(false, "请检查联系方式、紧急联系人及邮箱输写是否正确!"));
            }
            else
            {
                //指定字段加密
                var phone = AesEncryptionHelper.Encrypt(dto.Phone);
                var urgentPhone = AesEncryptionHelper.Encrypt(dto.UrgentPhone);
                var IDCard = AesEncryptionHelper.Encrypt(dto.IDCard);

                bool res = await _userRep.UpdateAsync<Sys_Users>(a => a.Id == dto.Id, a => new Sys_Users
                {
                    CnName = dto.CnName,
                    EnName = dto.EnName,
                    Sex = dto.Sex,
                    Phone = phone,
                    UrgentPhone = urgentPhone,
                    Email = dto.Email,
                    Address = dto.Address,
                    Edate = dto.Edate,
                    Birthday = dto.Birthday,
                    IDCard = IDCard,
                    GraduateInstitutions = dto.GraduateInstitutions,
                    Professional = dto.Professional,
                    Education = dto.Education,
                    TheOrAdultEducation = dto.TheOrAdultEducation,
                    MaritalStatus = dto.MaritalStatus,
                    HomeAddress = dto.HomeAddress,
                    WorkExperience = dto.WorkExperience,
                    Certificate = dto.Certificate
                });
                if (!res)
                {
                    return Ok(JsonView(false, "修改失败！"));
                }
                return Ok(JsonView(true, "修改成功！"));
            }
        }

        /// <summary>
        /// 删除用户信息
        /// 即为离职
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostUserDelById(UserDelDto dto)
        {
            if (dto == null)
            {
                return Ok(JsonView(false, "参数不能为空！"));
            }
            var _Users = _sqlSugar.Queryable<Sys_Users>().First(a => a.IsDel == 0 && a.Id == dto.Id);
            if (_Users != null)
            {
                var sys_UsersList = _sqlSugar.Queryable<Sys_Users>().Where(a => a.IsDel == 0 && a.CompanyId == _Users.CompanyId).ToList();
                if (sys_UsersList.Count == 1)
                {
                    return Ok(JsonView(false, "该人员为公司最后一位，不可删除！"));
                }
            }

            bool res = await _userRep.UpdateAsync<Sys_Users>(a => a.Id == dto.Id, a => new Sys_Users
            {
                IsDel = 1,
                DeleteUserId = dto.OperateUserId,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                Rdate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
            });
            if (!res)
            {
                return Ok(JsonView(false, "操作失败！"));
            }
            return Ok(JsonView(true, "操作成功！"));
        }

        /// <summary>
        /// 员工信息 
        /// 人事审核
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PersonnelAudit(PersonnelAuditDto dto)
        {
            if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数！"));
            if (dto.Id < 1) return Ok(JsonView(false, "请传入有效的Id参数！"));
            if (dto.IsAudit < 1 || dto.IsAudit > 2) return Ok(JsonView(false, "请传入有效的IsAudit参数！1：通过 2拒绝"));

            bool res = await _userRep.UpdateAsync<Sys_Users>(a => a.Id == dto.Id, a => new Sys_Users { HrAudit = dto.IsAudit });
            if (res)
            {
                //审核成功添加员工基础页面权限
                var userData = _sqlSugar.Queryable<Sys_Users>().Where(it => it.Id == dto.Id).First();
                int depId = 0, postId = 0;
                if (userData != null) { depId = userData.DepId; postId = userData.JobPostId; }

                bool s = DefaultPostAuth(depId, postId, dto.Id, dto.UserId);
                string str = $"基础页面权限添加失败!";
                if (s) str = $"基础页面权限添加成功!";

                return Ok(JsonView(true, $"操作成功！{str}"));
            }
            return Ok(JsonView(false, "操作失败！"));

        }

        /// <summary>
        /// 部门查询员工
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult QueryUserByDepart(QueryUserByDepartDto dto)
        {
            var jw = JsonView(false);
            if (dto.DepartId < 1)
            {
                jw.Msg = "请传入正确的部门id";
                return Ok(jw);
            }

            string sql = $@"SELECT  * FROM  Sys_Users su WHERE su.JobPostId  in (SELECT  id FROM  Sys_JobPost sj  WHERE  sj.IsDel  = 0 AND  sj.DepId  = {dto.DepartId} )
                            AND  su.IsDel  = 0 ";

            var result = _sqlSugar.SqlQueryable<Sys_Users>(sql).Select(x => new
            {
                x.Id,
                x.CnName,
                x.EnName
            }).ToList();

            jw.Data = result;
            jw.Code = 200;
            jw.Msg = "获取成功！";

            return Ok(jw);
        }

        #endregion

        #region 权限模块
        /// <summary>
        /// 权限数据页面初始化
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        //[Authorize]
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetAuth(AuthDto dto)
        {
            Result result = new Result();
            //模块数据
            var setDataResult = await _setDataRepository.GetSySDefultModule();
            if (setDataResult.Code != 0)
            {
                return Ok(JsonView(setDataResult.Msg));
            }

            //操作方式
            var PageOperation = _PageFunctionPermissionRepository.QueryDto<Sys_PageFunctionPermission, Sys_PageFunctionPermission>(x => x.IsEnable == 1).ToList();

            //获取所有关联页面
            var Sys_SystemMenuAndFunction = _SystemMenuAndFunctionRepository.QueryDto<Sys_SystemMenuAndFunction, SystemMenuAndFunctionView>().ToList();


            //页面数据
            var SystemMenuPermissionData = _SystemMenuPermissionRepository.QueryDto<Sys_SystemMenuPermission, SystemMenuPermissionView>(x => x.Mid == dto.moduleId && x.IsEnable == 1).ToList();
            if (SystemMenuPermissionData == null || SystemMenuPermissionData.Count() == 0)
            {
                return Ok(JsonView("暂无数据"));
            }

            ArrayList viewData = new ArrayList();
            //组合页面数据
            foreach (var item in SystemMenuPermissionData)
            {
                ArrayList ids = new ArrayList();
                foreach (var viewop in PageOperation)
                {
                    var op = Sys_SystemMenuAndFunction.FirstOrDefault(x => x.SmId == item.Id && x.FId == viewop.Id);
                    if (op != null)
                    {
                        ids.Add(viewop.Id);
                    }
                }
                viewData.Add(new
                {
                    Id = item.Id,
                    Mid = item.Mid,
                    Name = item.Name,
                    SystemMenuCode = item.SystemMenuCode,
                    opList = ids,
                    selList = new string[0]
                });
            }

            //公司数据
            var CompanyDataResult = _CompanyRepository.GetCompanyData();
            if (CompanyDataResult.Code != 0)
            {
                return Ok(JsonView(CompanyDataResult.Msg));
            }

            result.Code = 0;
            result.Msg = "成功！";
            var Dyresult = new
            {
                setDataResult = setDataResult.Data,
                CompanyDataResult = CompanyDataResult.Data,
                SystemMenuPermissionData = viewData,
                PageOperation = PageOperation,
            };

            return Ok(JsonView(200, "成功！", Dyresult));
        }
        /// <summary>
        /// 获取职务权限
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public IActionResult QueryJobAuth(QueryJobAuthDto dto)
        {
            //选中的操作权限
            var DBdata = _JobPostAuthorityRepository.QueryDto<Sys_JobPostAuthority, JobPostAuthorityView>(x => x.JpId == dto.jobid).ToList();
            var SystemMenuPermissionData = _SystemMenuPermissionRepository.QueryDto<Sys_SystemMenuPermission, SystemMenuPermissionView>(x => x.Mid == dto.moduleId).ToList();
            if (SystemMenuPermissionData == null || SystemMenuPermissionData.Count() == 0)
            {
                return Ok(JsonView("暂无数据"));
            }

            //所有操作
            var PageOperation = _PageFunctionPermissionRepository.QueryDto<Sys_PageFunctionPermission, Sys_PageFunctionPermission>().ToList();

            //获取所有关联页面
            var Sys_SystemMenuAndFunction = _SystemMenuAndFunctionRepository.QueryDto<Sys_SystemMenuAndFunction, SystemMenuAndFunctionView>().ToList();

            ArrayList viewData = new ArrayList();
            //组合页面数据
            foreach (var item in SystemMenuPermissionData)
            {
                ArrayList ids = new ArrayList();
                foreach (var viewop in PageOperation)
                {
                    var op = Sys_SystemMenuAndFunction.FirstOrDefault(x => x.SmId == item.Id && x.FId == viewop.Id);
                    if (op != null)
                    {
                        ids.Add(viewop.Id);
                    }
                }

                //获取本职务的页面拥有的权限
                var DBwhere = DBdata.Where(x => x.SmId == item.Id && x.JpId == dto.jobid).ToList();

                viewData.Add(new
                {
                    Id = item.Id,
                    Mid = item.Mid,
                    Name = item.Name,
                    SystemMenuCode = item.SystemMenuCode,
                    opList = ids,
                    selList = DBwhere.Select(x => x.FId)
                });
            }

            return Ok(JsonView(200, "成功!", viewData));
        }
        /// <summary>
        /// 保存岗位权限
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SaveJobAuth(SaveJobDto dto)
        {
            //获取所有关联页面
            var Sys_SystemMenuAndFunction = _SystemMenuAndFunctionRepository.QueryDto<Sys_SystemMenuAndFunction, SystemMenuAndFunctionView>().ToList();

            var RemoveJobPostAuthList = _SystemMenuAndFunctionRepository._sqlSugar.SqlQueryable<Sys_JobPostAuthority>($@"
                  select a.* from Sys_JobPostAuthority a, Sys_SetData b ,Sys_SystemMenuPermission c
                  where a.SmId = c.Id and c.Mid = b.Id and JpId = {dto.Jpid} and  c.Mid ={dto.modulId} 
            ").ToList();

            List<Sys_JobPostAuthority> adds = new List<Sys_JobPostAuthority>();
            foreach (var item in dto.Savejobs)
            {
                foreach (var fid in item.FIds)
                {
                    var whereobj = Sys_SystemMenuAndFunction.FirstOrDefault(x => x.FId == fid && x.SmId == item.SmId);
                    if (whereobj != null)
                    {
                        adds.Add(new Sys_JobPostAuthority
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = dto.UserId,
                            FId = fid,
                            JpId = dto.Jpid,
                            SmId = item.SmId
                        });
                    }
                }
            }

            _JobPostAuthorityRepository.BeginTran();
            try
            {   //删除岗位
                bool isdel = await _JobPostAuthorityRepository.DeletesAsync<Sys_JobPostAuthority>(RemoveJobPostAuthList);
                int UpRows = _JobPostAuthorityRepository.Adds<Sys_JobPostAuthority>(adds);

                //获取所有职位员工
                var jobUserAll = await QueryUserList(new UserDto { PortType = 2, JobPostId = dto.Jpid });
                List<UserInfoWebView> users = null;
                var QueryUserListApiResult = (((jobUserAll as OkObjectResult).Value) as OASystem.Domain.ViewModels.JsonView);
                if (QueryUserListApiResult != null)
                {
                    if (QueryUserListApiResult.Code == 200)
                    {
                        users = QueryUserListApiResult.Data as List<UserInfoWebView>;
                    }
                }

                if (users != null && users.Count > 0)
                {
                    List<Sys_UserAuthority> userAuth = null;
                    var uids = string.Join(',', users.Select(x => x.Id)).TrimEnd(',');
                    var RemoveUserAuthorityListAndTemp = _UserAuthorityRepository._sqlSugar.SqlQueryable<Sys_UserAuthority>($@"
                           select a.* from Sys_UserAuthority a, Sys_SetData b ,Sys_SystemMenuPermission c
                           where a.SmId = c.Id and c.Mid = b.Id and uid in ({uids}) and  c.Mid = {dto.modulId} and IsTemp = 1                       
                    ").ToList();

                    foreach (var user in users)
                    {
                        //删除个人级岗位权限
                        isdel = await _UserAuthorityRepository.DeletesAsync<Sys_UserAuthority>
                            (RemoveUserAuthorityListAndTemp.FindAll(x => x.UId == user.Id));

                        userAuth = adds.Select(x => new Sys_UserAuthority
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = dto.UserId,
                            FId = x.FId,
                            SmId = x.SmId,
                            UId = user.Id,
                            IsTemp = 1,
                        }).ToList();

                        //添加个人级别岗位
                        int AddRows = _UserAuthorityRepository.Adds<Sys_UserAuthority>(userAuth);
                    }
                }
            }
            catch (Exception ex)
            {
                _JobPostAuthorityRepository.RollbackTran();
                return Ok(JsonView("系统错误！"));
            }

            _JobPostAuthorityRepository.CommitTran();
            return Ok(JsonView(200, "成功", new { }));
        }


        /// <summary>
        /// 获取员工权限
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public IActionResult QueryUserAuth(QueryUserAuthDto dto)
        {
            //选中的员工操作权限
            var DBdata = _UserAuthorityRepository.QueryDto<Sys_UserAuthority, UserAuthorityView>(x => x.UId == dto.Userid).ToList();
            var SystemMenuPermissionData = _SystemMenuPermissionRepository.QueryDto<Sys_SystemMenuPermission, SystemMenuPermissionView>(x => x.Mid == dto.moduleId).ToList();
            if (SystemMenuPermissionData == null || SystemMenuPermissionData.Count() == 0)
            {
                return Ok(JsonView("暂无数据"));
            }

            //所有操作
            var PageOperation = _PageFunctionPermissionRepository.QueryDto<Sys_PageFunctionPermission, Sys_PageFunctionPermission>(x => x.IsEnable == 1).ToList();

            //获取所有关联页面
            var Sys_SystemMenuAndFunction = _SystemMenuAndFunctionRepository.QueryDto<Sys_SystemMenuAndFunction, SystemMenuAndFunctionView>().ToList();

            ArrayList viewData = new ArrayList();
            //组合页面数据
            foreach (var item in SystemMenuPermissionData)
            {
                ArrayList ids = new ArrayList();
                foreach (var viewop in PageOperation)
                {
                    var op = Sys_SystemMenuAndFunction.FirstOrDefault(x => x.SmId == item.Id && x.FId == viewop.Id);
                    if (op != null)
                    {
                        ids.Add(viewop.Id);
                    }
                }

                //获取本员工拥有的权限
                var DBwhere = DBdata.Where(x => x.SmId == item.Id && x.UId == dto.Userid).ToList();

                viewData.Add(new
                {
                    Id = item.Id,
                    Mid = item.Mid,
                    Name = item.Name,
                    SystemMenuCode = item.SystemMenuCode,
                    opList = ids,
                    selList = DBwhere.Select(x => x.FId)
                });
            }

            return Ok(JsonView(200, "成功!", viewData));
        }


        /// <summary>
        /// 根据Id获取员工所有移动端查看权限
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public IActionResult QueryUserAuthByUserId(QueryUserAuthByUserIdDto dto)
        {
            string sql = string.Format(@" Select sd1.Id as ModuleId,sd1.Name as ModuleName,sm.Id as MenuId,sm.Name as MenuName From Sys_UserAuthority as u With(Nolock) 
Inner Join Sys_SystemMenuPermission as sm With(Nolock) On u.SmId = sm.Id
Inner Join Sys_SetData as sd1 With(Nolock) On sm.Mid = sd1.Id
Where u.IsDel = 0 And sm.IsDel = 0
And u.UId = {0} And u.FId = 1 ", dto.UserId);

            List<SystemModule_UserAuthSqlView> _dataSource = _sqlSugar.SqlQueryable<SystemModule_UserAuthSqlView>(sql).ToList();
            List<SystemModule_UserAuthView> result = new List<SystemModule_UserAuthView>();

            foreach (SystemModule_UserAuthSqlView item in _dataSource)
            {
                if (result.FirstOrDefault(s => s.ModuleId == item.ModuleId) == null)
                {
                    List<SystemModule_UserAuthSqlView> tempList = _dataSource.Where(s => s.ModuleId == item.ModuleId).ToList();
                    List<SystemMenu_UserAuthView> menuList = new List<SystemMenu_UserAuthView>();

                    foreach (SystemModule_UserAuthSqlView item2 in tempList)
                    {
                        SystemMenu_UserAuthView menu = new SystemMenu_UserAuthView() { MenuId = item2.MenuId, MenuName = item2.MenuName };
                        menuList.Add(menu);
                    }

                    SystemModule_UserAuthView module = new SystemModule_UserAuthView() { MenuList = menuList, ModuleId = item.ModuleId, ModuleName = item.ModuleName };

                    result.Add(module);
                }
            }

            return Ok(JsonView(200, "成功!", result));
        }

        /// <summary>
        /// 保存员工权限
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SaveUserAuth(SaveUserDto dto)
        {
            //获取所有关联页面
            var Sys_SystemMenuAndFunction = _SystemMenuAndFunctionRepository.QueryDto<Sys_SystemMenuAndFunction, SystemMenuAndFunctionView>().ToList();
            //获取用户当前模块所有启用页面
            var userpageList = _SystemMenuPermissionRepository._sqlSugar.SqlQueryable<Sys_UserAuthority>($@"
                  select a.* from Sys_UserAuthority a, Sys_SetData b ,Sys_SystemMenuPermission c
                    where a.SmId = c.Id and c.Mid = b.Id and uid = {dto.uid} and  c.Mid ={dto.Modulid} 
            ").ToList();

            List<Sys_UserAuthority> adds = new List<Sys_UserAuthority>();
            foreach (var item in dto.Savejobs)
            {
                foreach (var fid in item.FIds)
                {
                    var whereobj = Sys_SystemMenuAndFunction.FirstOrDefault(x => x.FId == fid && x.SmId == item.SmId);
                    if (whereobj != null)
                    {
                        adds.Add(new Sys_UserAuthority
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = dto.UserId,
                            FId = fid,
                            UId = dto.uid,
                            SmId = item.SmId,
                            IsTemp = 0
                        });
                    }
                }
            }

            _JobPostAuthorityRepository.BeginTran();
            try
            {
                List<Sys_UserAuthority> userAuth = null;
                //删除个人级岗位权限
                bool isdel = await _UserAuthorityRepository.DeletesAsync<Sys_UserAuthority>(userpageList);

                userAuth = adds.Select(x => new Sys_UserAuthority
                {
                    CreateTime = DateTime.Now,
                    CreateUserId = dto.UserId,
                    FId = x.FId,
                    SmId = x.SmId,
                    UId = dto.uid,
                    IsTemp = 0,
                }).ToList();

                //添加个人级别岗位
                int AddRows = _UserAuthorityRepository.Adds<Sys_UserAuthority>(userAuth);
            }
            catch (Exception ex)
            {
                _JobPostAuthorityRepository.RollbackTran();
                return Ok(JsonView("系统错误！"));
            }

            _JobPostAuthorityRepository.CommitTran();
            return Ok(JsonView(200, "成功", new { }));
        }

        #endregion

        #region 页面配置

        /// <summary>
        /// 页面配置界面数据初始化
        /// </summary>
        /// <returns></returns>
        //[Authorize]
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PageConfigInit()
        {
            ArrayList arr = new ArrayList();
            var viewList = await _setDataRepository.GetSetDataAndPageInfoBySTId();
            if (viewList.Code != 0)
            {
                return Ok(JsonView(viewList.Msg));
            }
            var ModList = await _setDataRepository.GetSySDefultModule();

            return Ok(JsonView(new
            {
                viewList,
                ModList,
            }));
        }


        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> EditPageInfo(SetDataAndPageInfoDto dto)
        {
            JsonView view = null;
            _SystemMenuPermissionRepository.BeginTran();

            var istrue = await _SystemMenuPermissionRepository.UpdateAsync(x => x.Id == dto.Pageid, x => new Sys_SystemMenuPermission
            {
                AndroidUrl = dto.AndroidUrl,
                CreateTime = DateTime.Now,
                Icon = dto.Icon,
                IosUrl = dto.IosUrl,
                Name = dto.PageName,
                PhoneIsEnable = dto.PagePhoneIsEnable,
                CreateUserId = dto.UserId,
                IsDel = 0,
                IsEnable = dto.PageIsEnable,
                Mid = dto.Modulid,
                Remark = dto.PageRemark,
                SystemMenuCode = dto.SystemMenuCode,
                WebUrl = dto.WebUrl,
            });

            if (istrue)
            {

                //删除页面绑定的操作后重新绑定
                await _SystemMenuAndFunctionRepository.DeleteAsync(x => x.SmId == dto.Pageid);

                List<Sys_SystemMenuAndFunction> binFun = new List<Sys_SystemMenuAndFunction>();

                foreach (var item in dto.FunArr)
                {
                    binFun.Add(new Sys_SystemMenuAndFunction
                    {
                        CreateTime = DateTime.Now,
                        CreateUserId = dto.UserId,
                        FId = item,
                        SmId = dto.Pageid,
                        IsDel = 0,
                    });
                }
                int number = _SystemMenuAndFunctionRepository.Adds<Sys_SystemMenuAndFunction>(binFun);

                view = JsonView(istrue);
                _SystemMenuPermissionRepository.CommitTran();
            }
            else
            {
                _SystemMenuPermissionRepository.RollbackTran();
                view = JsonView("添加失败");
            }
            return Ok(view);
        }


        /// <summary>
        /// 添加一个页面
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AddPageInfo(SetDataAndPageInfoDto dto)
        {
            JsonView view = null;

            _SystemMenuPermissionRepository.BeginTran();

            int number = await _SystemMenuPermissionRepository.AddAsyncReturnId(new Sys_SystemMenuPermission
            {
                AndroidUrl = dto.AndroidUrl,
                CreateTime = DateTime.Now,
                Icon = dto.Icon,
                IosUrl = dto.IosUrl,
                Name = dto.PageName,
                PhoneIsEnable = dto.PagePhoneIsEnable,
                CreateUserId = dto.UserId,
                IsDel = 0,
                IsEnable = dto.PageIsEnable,
                Mid = dto.Modulid,
                Remark = dto.PageRemark,
                SystemMenuCode = dto.SystemMenuCode,
                WebUrl = dto.WebUrl,
            });

            List<Sys_SystemMenuAndFunction> binFun = new List<Sys_SystemMenuAndFunction>();

            foreach (var item in dto.FunArr)
            {
                binFun.Add(new Sys_SystemMenuAndFunction
                {
                    CreateTime = DateTime.Now,
                    CreateUserId = dto.UserId,
                    FId = item,
                    SmId = number,
                    IsDel = 0,
                });
            }

            number = _SystemMenuAndFunctionRepository.Adds<Sys_SystemMenuAndFunction>(binFun);

            if (number > 0)
            {
                view = JsonView(number);
                _SystemMenuPermissionRepository.CommitTran();
            }
            else
            {
                _SystemMenuPermissionRepository.RollbackTran();
                view = JsonView("添加失败");
            }

            return Ok(view);
        }

        /// <summary>
        /// 删除页面
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelPageInfo(List<SystemMenuDelDto> Dto)
        {
            JsonView view = new JsonView();
            if (Dto.Count > 0)
            {
                try
                {
                    _SystemMenuPermissionRepository.BeginTran();
                    bool istrue = false;
                    foreach (var item in Dto)
                    {
                        istrue = await _SystemMenuPermissionRepository.SoftDeleteAsync(item.Pageid.ToString());
                        if (!istrue)
                        {
                            throw new Exception("修改失败");
                        }
                    }

                    view.Code = 200;
                    view.Msg = "删除成功！";
                    view.Data = istrue;
                    _SystemMenuPermissionRepository.CommitTran();
                }
                catch (Exception)
                {
                    _SystemMenuPermissionRepository.RollbackTran();
                }
            }
            return Ok(JsonView(view));
        }

        /// <summary>
        /// 获取页面绑定的操作
        /// </summary>
        /// <param name="Dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryPageFunById(PageFunDto Dto)
        {
            //页面与操作关联表
            var pageAndFunList = _SystemMenuAndFunctionRepository.QueryDto<Sys_SystemMenuAndFunction, SystemMenuAndFunctionView>().ToList();
            //页面功能表
            var pageFunList = _PageFunctionPermissionRepository.QueryDto<Sys_PageFunctionPermission, Sys_PageFunctionPermission>(x => x.IsEnable == 1).ToList();

            ArrayList arr = new ArrayList();
            foreach (var item in pageFunList)
            {
                var FindVal = pageAndFunList.Find(x => x.SmId == Dto.Pageid && x.FId == item.Id);
                if (FindVal == null)
                {
                    arr.Add(new
                    {
                        id = item.Id,
                        name = item.FunctionName,
                        value = false
                    });
                }
                else
                {
                    arr.Add(new
                    {
                        id = item.Id,
                        name = item.FunctionName,
                        value = true
                    });
                }
            }
            return Ok(JsonView(arr));
        }
        #endregion

        #region 页面操作
        /// <summary>
        /// 操作权限功能表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PageFunInit()
        {
            var PageFunInit = _PageFunctionPermissionRepository.QueryDto<Sys_PageFunctionPermission, PageFunctionPermissionView>().ToList();
            if (PageFunInit == null)
            {
                return Ok(JsonView(false, "暂无数据！"));
            }
            return Ok(JsonView(true, "查询成功！", PageFunInit));
        }

        /// <summary>
        /// 操作权限功能表操作（Status 1：添加，2：编辑）
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OperationFun(OperationFunInitDta dto)
        {
            Result result = await _PageFunctionPermissionRepository.OperationFunInit(dto);
            if (result.Code != 0)
            {
                return Ok(JsonView(false, result.Msg));
            }
            return Ok(JsonView(true, result.Msg));
        }

        /// <summary>
        /// 删除功能
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelFun(DelFunInitDta dto)
        {
            var res = await _PageFunctionPermissionRepository.SoftDeleteByIdAsync<Sys_PageFunctionPermission>(dto.Id.ToString(), dto.DeleteUserId);
            if (!res)
            {
                return Ok(JsonView(false, "删除失败"));
            }
            return Ok(JsonView(true, "删除成功！"));
        }

        #endregion

        #region 各部门首页消息提示
        /// <summary>
        /// 部门首页消息提示
        /// </summary>
        /// <returns></returns>
        [HttpGet("{portType}")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DepartmentHomePageMessagePrompts(int portType)
        {
            var currUserInfo = JwtHelper.SerializeJwt(HttpContext.Request.Headers.Authorization);
            if (currUserInfo == null) return Ok(JsonView(false, "请传入token!"));
            var department = currUserInfo.Department;
            if (portType < 1 || portType > 3) return Ok(JsonView(false, MsgTips.Port));

            if (portType == 1 || portType == 2 || portType == 3) // web
            {
                //固定查询时间(当前月的上月)
                var lastMoth = DateTime.Now.AddMonths(-1);
                var (startDate, endDate) = CommonFun.GetMonthStartAndEndDates(lastMoth.Year, lastMoth.Month);
                var startDateTime = Convert.ToDateTime(startDate.ToString("yyyy-MM-dd 00:00:00"));
                var endDateTime = Convert.ToDateTime(endDate.ToString("yyyy-MM-dd 23:59:59"));
                //固定查询时间(本年)
                var currStartDate = Convert.ToDateTime($"{DateTime.Now.Year}-01-01 00:00:00");
                var currEntDate = Convert.ToDateTime($"{DateTime.Now.Year}-12-31 23:59:59");
                if (department.Equals("总经办")) //总经办
                {
                    //1、资料数据(市场部客户资源、op地接导游、op地接车数据、商邀数据、团组收款数据)数据提示Range(固定上个月) -- Add

                    #region 市场部客户资源
                    //1.1 市场部客户资源
                    var users = await _sqlSugar.Queryable<Sys_Users>()
                        .LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                        .Where((u, d) => u.IsDel == 0 && (d.DepName.Contains("市场部") || u.Id == 21))
                        .Select((u, d) => new { u.Id, u.CnName })
                        .ToListAsync();

                    var marketData = await _sqlSugar.Queryable<Crm_NewClientData>()
                        .LeftJoin<Sys_Users>((ncd, u) => ncd.CreateUserId == u.Id)
                        .Where((ncd, u) => ncd.IsDel == 0 && ncd.CreateTime >= startDateTime && ncd.CreateTime <= endDateTime)
                        .Select((ncd, u) => new
                        {
                            Area = ncd.Location,
                            ncd.Client,
                            ncd.Contact,
                            ncd.Job,
                            Tel = ncd.Telephone,
                            ncd.CreateUserId,
                            CreatleUserName = u.CnName,
                            ncd.CreateTime,
                        })
                        .ToListAsync();
                    foreach (var item in marketData) EncryptionProcessor.DecryptProperties(item);
                    var marketDataGroup = marketData.GroupBy(x => x.CreateUserId).Select(g => new { CreateUserId = g.Key, Items = g.ToList(), Count = g.Count() });

                    var marketData2 = new List<dynamic>();
                    foreach (var user in users)
                    {
                        var userMarketData = marketDataGroup.FirstOrDefault(x => user.Id == x.CreateUserId);
                        if (userMarketData != null)
                        {
                            marketData2.Add(new
                            {
                                uId = user.Id,
                                name = user.CnName,
                                count = userMarketData.Count,
                                msgTips = $"上月新增市场客户资源共{userMarketData.Count}条",
                                userMarketData = userMarketData.Items
                            });
                        }
                        else
                        {
                            marketData2.Add(new
                            {
                                uId = user.Id,
                                name = user.CnName,
                                count = 0,
                                msgTips = $"上月新增市场客户资源共{0}条",
                                userMarketData = new List<dynamic>() { },
                            });
                        }
                    }

                    var marketData1 = new
                    {
                        msgTips = $"上月新增市场客户资源共{marketData.Count}条",
                        Data = marketData2,
                    };
                    #endregion

                    #region op地接导游
                    //1.2 op地接导游 
                    var opTourGuideData = await _sqlSugar.Queryable<Res_LocalGuideData>()
                        .LeftJoin<Sys_Users>((lgd, u) => lgd.CreateUserId == u.Id)
                        .Where((lgd, u) => lgd.IsDel == 0 && lgd.CreateTime >= startDateTime && lgd.CreateTime <= endDateTime)
                        .Select((lgd, u) => new
                        {
                            Area = lgd.UnitArea,
                            Client = lgd.UnitName,
                            lgd.Contact,
                            Job = "",
                            Tel = lgd.ContactTel,
                            lgd.CreateUserId,
                            CreatleUserName = u.CnName,
                            lgd.CreateTime,
                        })
                        .ToListAsync();

                    foreach (var item in opTourGuideData) EncryptionProcessor.DecryptProperties(item);
                    var opTourGuideDataGroups = opTourGuideData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreatleUserName ?? "-",
                            msgTips = $"上月新增OP地接导游资源共{g.Count()}条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });

                    var opTourGuideData1 = new
                    {
                        msgTips = $"上月新增OP地接导游资源共{opTourGuideData.Count}条",
                        Data = opTourGuideDataGroups,
                    };
                    #endregion

                    #region op车数据
                    //1.3 op车数据 
                    var opCarData = await _sqlSugar.Queryable<Res_CarData>()
                        .LeftJoin<Sys_Users>((cd, u) => cd.CreateUserId == u.Id)
                        .Where((cd, u) => cd.IsDel == 0 && cd.CreateTime >= startDateTime && cd.CreateTime <= endDateTime)
                        .Select((cd, u) => new CarDataMsgTipsView()
                        {
                            Area = cd.UnitArea,
                            Client = cd.UnitName,
                            Contact = cd.Contact,
                            Job = "",
                            Tel = cd.ContactTel,
                            CreateUserId = cd.CreateUserId,
                            CreatleUserName = u.CnName,
                            CreateTime = cd.CreateTime,
                        })
                        .ToListAsync();
                    foreach (var item in opCarData) EncryptionProcessor.DecryptProperties(item);
                    var opCarDataGroups = opCarData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreatleUserName ?? "-",
                            msgTips = $"上月新增OP地接车资源共{g.Count()}条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });
                    var opCarData1 = new
                    {
                        msgTips = $"上月新增OP地接车资源共{opCarData.Count}条",
                        Data = opCarDataGroups,
                    };
                    #endregion

                    #region 商邀数据
                    //1.4 商邀数据 
                    var invitationData = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>()
                        .LeftJoin<Sys_Users>((ioa, u) => ioa.CreateUserId == u.Id)
                        .Where((ioa, u) => ioa.IsDel == 0 && ioa.CreateTime >= startDateTime && ioa.CreateTime <= endDateTime)
                        .Select((ioa, u) => new
                        {
                            //Area = string.Format("{0}{1}", ioa.Country, !string.IsNullOrEmpty(ioa.City) ? "" : "-" + ioa.City),
                            Area = string.Format("{0}{1}", ioa.Country, ioa.City),
                            Client = ioa.UnitName,
                            ioa.Contact,
                            ioa.Job,
                            ioa.Tel,
                            ioa.CreateUserId,
                            CreatleUserName = u.CnName,
                            ioa.CreateTime,
                        })
                        .ToListAsync();
                    var invitationGroups = invitationData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreatleUserName ?? "-",
                            msgTips = $"上月新增商邀资源共{g.Count()}条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });
                    var invitationData1 = new
                    {
                        msgTips = $"上月新增商邀资源共{invitationData.Count}条",
                        Data = invitationGroups,
                    };
                    #endregion

                    #region 团组收款数据
                    //1.5 团组收款数据  
                    var groupCollectionData = await _sqlSugar.Queryable<Fin_ForeignReceivables>()
                        .LeftJoin<Sys_Users>((fr, u) => fr.CreateUserId == u.Id)
                        .LeftJoin<Grp_DelegationInfo>((fr, u, di) => fr.Diid == di.Id)
                        .LeftJoin<Sys_SetData>((fr, u, di, sd) => fr.Currency == sd.Id)
                        .Where((fr, u, di, sd) => fr.IsDel == 0 && fr.CreateTime >= startDateTime && fr.CreateTime <= endDateTime)
                        .Select((fr, u, di, sd) => new
                        {
                            fr.PriceName,
                            fr.Price,
                            fr.Count,
                            fr.Unit,
                            fr.ItemSumPrice,
                            Currency = sd.Name,
                            GroupName = di.TeamName,
                            fr.CreateUserId,
                            CreateUserName = u.CnName,
                            fr.CreateTime,
                        })
                        .ToListAsync();
                    var groupCollectionGroups = groupCollectionData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreateUserName ?? "-",
                            msgTips = $"上月累计团组收款共{g.Count()}条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });

                    var groupCollectionData2 = new
                    {
                        msgTips = $"上月累计团组收款共{groupCollectionData.Count}条",
                        Data = groupCollectionGroups,
                    };
                    #endregion

                    var materialData = new
                    {
                        marketData = marketData1,
                        opTourGuideData = opTourGuideData1,
                        opCarData = opCarData1,
                        invitationData = invitationData1,
                        groupCollectionData = groupCollectionData2
                    };
                    //2、费用未审核(日付申请未审核数据、团组费用未审核数据)费用提示Range(固定本年)

                    #region 日付申请未审核数据
                    var dailyPaymentData = await _sqlSugar.Queryable<Fin_DailyFeePayment>()
                                                 .LeftJoin<Sys_Users>((dfp, u) => dfp.CreateUserId == u.Id)
                                                 .Where(dfp => dfp.IsDel == 0 &&
                                                               dfp.MAudit == 0 &&
                                                               dfp.CreateTime >= currStartDate &&
                                                               dfp.CreateTime <= currEntDate
                                                       )
                                                 .OrderBy(dfp => dfp.CreateTime, OrderByType.Desc)
                                                 //.OrderBy(dfp => dfp.FAudit, OrderByType.Desc)
                                                 .Select((dfp, u) => new
                                                 {
                                                     id = dfp.Id,
                                                     amountName = dfp.Instructions,
                                                     amount = dfp.SumPrice,
                                                     fAuditStatus = dfp.FAudit == 1 ? "审核通过" :
                                                                    dfp.FAudit == 2 ? "审核未通过" : "未审核",
                                                     fAuditDate = dfp.FAuditDate,
                                                     dfp.CreateUserId,
                                                     CreateUserName = u.CnName,
                                                     dfp.CreateTime
                                                 })
                                                 .ToListAsync();

                    var dailyPaymentGroups = dailyPaymentData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreateUserName ?? "-",
                            msgTips = $"本年有{g.Count()}条未审核日常付款申请条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });

                    var dailyPaymentData1 = new
                    {
                        msgTips = $"本年有{dailyPaymentData.Count()}条未审核日常付款申请条",
                        Data = dailyPaymentGroups,
                    };
                    #endregion

                    #region 团组费用未审核数据

                    var groupPaymentData = await _sqlSugar.Queryable<Grp_CreditCardPayment>()
                        .LeftJoin<Grp_DelegationInfo>((ccp, di) => ccp.DIId == di.Id)
                        .Where((ccp, di) => ccp.IsDel == 0 &&
                                            ccp.IsAuditGM == 0 &&
                                            ccp.CreateTime >= currStartDate &&
                                            ccp.CreateTime <= currEntDate
                              )
                        .Select((ccp, di) => new
                        {
                            ccp.Id,
                            ccp.DIId,
                            GroupName = di.TeamName,
                            ccp.CreateTime,
                        })
                        .ToListAsync();

                    var groupPaymentGroups = groupPaymentData.GroupBy(x => x.DIId)
                       .Select(g => new
                       {
                           uId = g.Key,
                           name = g.ToList().FirstOrDefault()?.GroupName ?? "-",
                           msgTips = $"本年有{g.Count()}条未审核团组费用申请条",
                           userTourGuideData = g.ToList(),
                           Count = g.Count()
                       });

                    var groupPaymentData1 = new
                    {
                        msgTips = $"本年有{groupPaymentData.Count()}条未审核团组费用申请条",
                        Data = groupPaymentGroups,
                    };
                    #endregion

                    var feeUnAuditData = new
                    {
                        dailyPaymentData = dailyPaymentData1,
                        groupPaymentData = groupPaymentData1
                    };

                    return Ok(JsonView(new { materialData = materialData, feeUnAuditData = feeUnAuditData }));
                }
                else if (department.Equals("国交部"))//国交部
                {
                    #region 团组费用录入提示
                    /*
                     * 名称：团组费用录入提示
                     * 描述：团组费用录入提醒(根据团组结束时间 - 3天 如果对应的数据表里没有这个团组的费用信息)
                     * 条件：时间范围不限制、 根据团组结束时间 - 3天 == 当前天数
                     */
                    var groupModlue = new List<int>() {
                        76,  //酒店预订
                        79,  //车/导游地接
                        80,  //签证
                        81,  //邀请/公务活动
                        //82,  //团组客户保险
                        85,  //机票预订
                        98,  //其他款项
                        //285 ,//收款退还
                        //1015,//超支费用
                    };
                    var crrDate = DateTime.Now.ToString("yyyy-MM-dd");
                    var groupModlueData = await _sqlSugar.Queryable<Sys_SetData>().Where(x => groupModlue.Contains(x.Id)).ToListAsync();

                    var groupFeeData = await _sqlSugar.Queryable<Sys_SetData>()
                        .LeftJoin<Grp_CreditCardPayment>((sd, ccp) => sd.Id == ccp.CTable)
                        .LeftJoin<Grp_DelegationInfo>((sd, ccp, di) => ccp.DIId == di.Id)
                        .Where((sd, ccp, di) => ccp.IsDel == 0 &&
                                                di.IsDel == 0 &&
                                                groupModlue.Contains(ccp.CTable) &&
                                                di.VisitEndDate.AddDays(-3).ToString("yyyy-MM-dd").Equals(crrDate)
                         )
                        .GroupBy((sd, ccp, di) => new { di.Id, di.TeamName, ccp.CTable, sd.Name }) //可以多字段
                        .Select((sd, ccp, di) => new
                        {
                            diId = di.Id,
                            groupName = di.TeamName,
                            groupType = ccp.CTable,
                            groupTypeName = sd.Name,
                            //userId = ccp.CreateUserId,
                            count = SqlFunc.AggregateCount(ccp.CTable),
                        })
                        .ToListAsync();
                    var groupFeeData1 = groupFeeData.GroupBy(x => x.diId);
                    var groupNotFilledFeeData = new List<dynamic>();
                    foreach (var groupFee in groupFeeData1)
                    {
                        var diId = groupFee.Key;
                        var addData = groupFeeData.Where(x => x.diId == diId).Select(x => x.groupType).ToList();
                        if (addData.Count < 1) continue;
                        var unAddData = groupModlue.Except(addData).ToList();
                        if (unAddData.Count < 1) continue;

                        foreach (var typeId in unAddData)
                        {
                            groupNotFilledFeeData.Add(new
                            {
                                diId = diId,
                                groupName = groupFee.FirstOrDefault()?.groupName ?? "-",
                                groupType = typeId,
                                groupTypeName = groupModlueData.Find(x => x.Id == typeId)?.Name ?? "-",
                                count = 0
                            });
                        }
                    }
                    #endregion

                    #region 日付申请 -- 未审核数据

                    var depUserData = await _sqlSugar.Queryable<Sys_Users>()
                        .LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                        .LeftJoin<Sys_JobPost>((u, d, jp) => u.JobPostId == jp.Id)
                        .Where((u, d, jp) => u.IsDel == 0 && d.DepName.Contains("国交部"))
                        .Select((u, d, jp) => new
                        {
                            u.Id,
                            u.CnName,
                            u.DepId,
                            d.DepName,
                            u.JobPostId,
                            jp.JobName
                        })
                        .ToListAsync();
                    var depUserData1 = depUserData.Select(x => x.Id).ToList();

                    var unAuditDailyFeeData = await _sqlSugar.Queryable<Fin_DailyFeePayment>()
                        .LeftJoin<Sys_Users>((dfp, u) => dfp.CreateUserId == u.Id)
                        .Where((dfp, u) => dfp.IsDel == 0 &&
                                           depUserData1.Contains(dfp.CreateUserId) &&
                                           dfp.MAudit == 0 &&
                                           dfp.CreateTime >= currStartDate &&
                                           dfp.CreateTime <= currEntDate
                         )
                        .Select((dfp, u) => new
                        {
                            id = dfp.Id,
                            amountName = dfp.Instructions,
                            amount = dfp.SumPrice,
                            fAuditStatus = dfp.FAudit == 1 ? "审核通过" :
                                           dfp.FAudit == 2 ? "审核未通过" : "未审核",
                            fAuditDate = dfp.FAuditDate,
                            dfp.CreateUserId,
                            CreateUserName = u.CnName,
                            dfp.CreateTime
                        })
                        .ToArrayAsync();

                    #endregion

                    var jobs = new List<string>() { "经理", "主管" };
                    if (jobs.Contains(currUserInfo.Role)) //经理、主管
                    {
                        #region 经理、主管
                        //经理、主管(部门下所有人员) --> 日付(-:未审核提示)、团组费用录入提醒(根据团组结束时间-3天 如果对应的数据表里没有这个团组的费用信息)

                        var dailyPaymentData = new
                        {
                            msgTips = $"本年有{unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                            Data = new
                            {
                                uId = currUserInfo.UserId,
                                name = currUserInfo.UserName,
                                msgTips = $"本年有{unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                                userTourGuideData = unAuditDailyFeeData,
                                Count = unAuditDailyFeeData.Count()
                            }
                        };

                        var groupNotFilledFeeDataAll = groupNotFilledFeeData
                            .GroupBy(x => x.diId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.groupName ?? "-",
                                msgTips = $"-",
                                hotelFeeData = g.Select(x1 => new
                                {
                                    x1.groupType,
                                    x1.groupTypeName
                                }).ToList(),
                                Count = g.Count()
                            })
                            .ToList();

                        var groupNotFillEdFeeData1 = new
                        {
                            msgTips = $"今天有{groupNotFilledFeeDataAll.Count()}个团组费用未填写",
                            Data = groupNotFilledFeeDataAll,
                        };

                        #endregion

                        return Ok(JsonView(new { groupNotFillEdFeeData = groupNotFillEdFeeData1, dailyPaymentData = dailyPaymentData }));
                    }
                    else if (currUserInfo.Role.Equals("计调"))//计调
                    {
                        #region 计调
                        //计调(myself) --> 暂定
                        return Ok(JsonView(false, "国交部-->计调岗位消息提示正在开发中......"));
                        #endregion
                    }
                    else if (currUserInfo.Role.Equals("机票"))//机票
                    {
                        #region 机票
                        //机票(myself) --> 日付(-:未审核提示)、机票费用(-:未录入提示)
                        var airTicket_unAuditDailyFeeData = unAuditDailyFeeData.Where(x => x.CreateUserId == currUserInfo.UserId).ToList();

                        var dailyPaymentData = new
                        {
                            msgTips = $"本年有{airTicket_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                            Data = new
                            {
                                uId = currUserInfo.UserId,
                                name = currUserInfo.UserName,
                                msgTips = $"本年有{airTicket_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                                userTourGuideData = airTicket_unAuditDailyFeeData,
                                Count = airTicket_unAuditDailyFeeData.Count()
                            }
                        };

                        var airTicket_groupNotFilledData = groupNotFilledFeeData
                            .Where(x => x.groupType == 85)
                            .GroupBy(x => x.diId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.groupName ?? "-",
                                msgTips = $"-",
                                airTicketFeeData = g.Select(x1 => new
                                {
                                    x1.groupType,
                                    x1.groupTypeName
                                }).ToList(),
                                Count = g.Count()
                            })
                            .ToList();

                        var groupNotFillEdFeeData1 = new
                        {
                            msgTips = $"今天有{airTicket_groupNotFilledData.Count()}个团组费用未填写",
                            Data = airTicket_groupNotFilledData,
                        };

                        #endregion

                        return Ok(JsonView(new { groupNotFillEdFeeData = groupNotFillEdFeeData1, dailyPaymentData = dailyPaymentData }));
                    }
                    else if (currUserInfo.Role.Equals("酒店"))//酒店
                    {
                        #region 酒店
                        //酒店(myself) --> 日付(-:未审核提示)、酒店费用(-:未录入提示)
                        var hotle_unAuditDailyFeeData = unAuditDailyFeeData.Where(x => x.CreateUserId == currUserInfo.UserId).ToList();

                        var dailyPaymentData = new
                        {
                            msgTips = $"本年有{hotle_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                            Data = new
                            {
                                uId = currUserInfo.UserId,
                                name = currUserInfo.UserName,
                                msgTips = $"本年有{hotle_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                                hotelFeeData = hotle_unAuditDailyFeeData,
                                Count = hotle_unAuditDailyFeeData.Count()
                            }
                        };

                        var hotel_groupNotFilledData = groupNotFilledFeeData
                            .Where(x => x.groupType == 76)
                            .GroupBy(x => x.diId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.groupName ?? "-",
                                msgTips = $"-",
                                hotelFeeData = g.Select(x1 => new
                                {
                                    x1.groupType,
                                    x1.groupTypeName
                                }).ToList(),
                                Count = g.Count()
                            })
                            .ToList();

                        var groupNotFillEdFeeData1 = new
                        {
                            msgTips = $"今天有{hotel_groupNotFilledData.Count()}个团组费用未填写",
                            Data = hotel_groupNotFilledData,
                        };

                        #endregion

                        return Ok(JsonView(new { groupNotFillEdFeeData = groupNotFillEdFeeData1, dailyPaymentData = dailyPaymentData }));
                    }
                    else if (currUserInfo.Role.Equals("签证"))//签证
                    {
                        #region 签证
                        //签证(myself) --> 日付(-:未审核提示)、签证费用(-:未录入提示)
                        var visa_unAuditDailyFeeData = unAuditDailyFeeData.Where(x => x.CreateUserId == currUserInfo.UserId).ToList();

                        var dailyPaymentData = new
                        {
                            msgTips = $"本年有{visa_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                            Data = new
                            {
                                uId = currUserInfo.UserId,
                                name = currUserInfo.UserName,
                                msgTips = $"本年有{visa_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                                visaFeeData = visa_unAuditDailyFeeData,
                                Count = visa_unAuditDailyFeeData.Count()
                            }
                        };

                        var visa_groupNotFilledData = groupNotFilledFeeData
                            .Where(x => x.groupType == 80)
                            .GroupBy(x => x.diId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.groupName ?? "-",
                                msgTips = $"-",
                                airTicketFeeData = g.Select(x1 => new
                                {
                                    x1.groupType,
                                    x1.groupTypeName
                                }).ToList(),
                                Count = g.Count()
                            })
                            .ToList();

                        var groupNotFillEdFeeData1 = new
                        {
                            msgTips = $"今天有{visa_groupNotFilledData.Count()}个团组费用未填写",
                            Data = visa_groupNotFilledData,
                        };
                        #endregion

                        return Ok(JsonView(new { groupNotFillEdFeeData = groupNotFillEdFeeData1, dailyPaymentData = dailyPaymentData }));
                    }
                    else if (currUserInfo.Role.Equals("商邀"))//商邀
                    {
                        #region 商邀
                        //商邀(myself) --> 日付(-:未审核提示)、商邀费用(-:未录入提示)、(公务、翻译人)(-:新增提示)
                        //日付
                        var in_unAuditDailyFeeData = unAuditDailyFeeData.Where(x => x.CreateUserId == currUserInfo.UserId).ToList();

                        var dailyPaymentData = new
                        {
                            msgTips = $"本年有{in_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                            Data = new
                            {
                                uId = currUserInfo.UserId,
                                name = currUserInfo.UserName,
                                msgTips = $"本年有{in_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                                visaFeeData = in_unAuditDailyFeeData,
                                Count = in_unAuditDailyFeeData.Count()
                            }
                        };

                        //团组未录入费用
                        var in_groupNotFilledData = groupNotFilledFeeData
                            .Where(x => x.groupType == 81)
                            .GroupBy(x => x.diId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.groupName ?? "-",
                                msgTips = $"-",
                                airTicketFeeData = g.Select(x1 => new
                                {
                                    x1.groupType,
                                    x1.groupTypeName
                                }).ToList(),
                                Count = g.Count()
                            })
                            .ToList();

                        var groupNotFillEdFeeData1 = new
                        {
                            msgTips = $"今天有{in_groupNotFilledData.Count()}个团组费用未填写",
                            Data = in_groupNotFilledData,
                        };

                        //商邀 - 基础数据
                        var invitationData = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>()
                        .LeftJoin<Sys_Users>((ioa, u) => ioa.CreateUserId == u.Id)
                        .Where((ioa, u) => ioa.IsDel == 0 && ioa.CreateTime >= startDateTime && ioa.CreateTime <= endDateTime)
                        .Select((ioa, u) => new
                        {
                            //Area = string.Format("{0}{1}", ioa.Country, !string.IsNullOrEmpty(ioa.City) ? "" : "-" + ioa.City),
                            Area = string.Format("{0}{1}", ioa.Country, ioa.City),
                            Client = ioa.UnitName,
                            ioa.Contact,
                            ioa.Job,
                            ioa.Tel,
                            ioa.CreateUserId,
                            CreatleUserName = u.CnName,
                            ioa.CreateTime,
                        })
                        .ToListAsync();
                        var invitationGroups = invitationData.GroupBy(x => x.CreateUserId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.CreatleUserName ?? "-",
                                msgTips = $"上月新增商邀资源共{g.Count()}条",
                                userTourGuideData = g.ToList(),
                                Count = g.Count()
                            });
                        var invitationData1 = new
                        {
                            msgTips = $"上月新增商邀资源共{invitationData.Count}条",
                            Data = invitationGroups,
                        };

                        #endregion

                        return Ok(JsonView(new { groupNotFillEdFeeData = groupNotFillEdFeeData1, dailyPaymentData = dailyPaymentData, invitationData = invitationData }));
                    }
                    else if (currUserInfo.Role.Equals("OP"))//OP
                    {
                        #region OP
                        //OP(myself) --> 日付(-:未审核提示)、OP费用(-:未录入提示)、(导游、车)资源信息(-:新增提示)
                        //日付
                        var in_unAuditDailyFeeData = unAuditDailyFeeData.Where(x => x.CreateUserId == currUserInfo.UserId).ToList();

                        var dailyPaymentData = new
                        {
                            msgTips = $"本年有{in_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                            Data = new
                            {
                                uId = currUserInfo.UserId,
                                name = currUserInfo.UserName,
                                msgTips = $"本年有{in_unAuditDailyFeeData.Count()}条未审核日常付款申请条",
                                visaFeeData = in_unAuditDailyFeeData,
                                Count = in_unAuditDailyFeeData.Count()
                            }
                        };

                        //OP - 团组未录入费用
                        var in_groupNotFilledData = groupNotFilledFeeData
                            .Where(x => x.groupType == 79)
                            .GroupBy(x => x.diId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.groupName ?? "-",
                                msgTips = $"-",
                                airTicketFeeData = g.Select(x1 => new
                                {
                                    x1.groupType,
                                    x1.groupTypeName
                                }).ToList(),
                                Count = g.Count()
                            })
                            .ToList();

                        var groupNotFillEdFeeData1 = new
                        {
                            msgTips = $"今天有{in_groupNotFilledData.Count()}个团组费用未填写",
                            Data = in_groupNotFilledData,
                        };

                        #region op地接导游
                        //1.2 op地接导游 
                        var opTourGuideData = await _sqlSugar.Queryable<Res_LocalGuideData>()
                            .LeftJoin<Sys_Users>((lgd, u) => lgd.CreateUserId == u.Id)
                            .Where((lgd, u) => lgd.IsDel == 0 && lgd.CreateTime >= startDateTime && lgd.CreateTime <= endDateTime)
                            .Select((lgd, u) => new
                            {
                                Area = lgd.UnitArea,
                                Client = lgd.UnitName,
                                lgd.Contact,
                                Job = "",
                                Tel = lgd.ContactTel,
                                lgd.CreateUserId,
                                CreatleUserName = u.CnName,
                                lgd.CreateTime,
                            })
                            .ToListAsync();
                        foreach (var item in opTourGuideData) EncryptionProcessor.DecryptProperties(item);

                        var opTourGuideDataGroups = opTourGuideData.GroupBy(x => x.CreateUserId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.CreatleUserName ?? "-",
                                msgTips = $"上月新增OP地接导游资源共{g.Count()}条",
                                userTourGuideData = g.ToList(),
                                Count = g.Count()
                            });

                        var opTourGuideData1 = new
                        {
                            msgTips = $"上月新增OP地接导游资源共{opTourGuideData.Count}条",
                            Data = opTourGuideDataGroups,
                        };
                        #endregion

                        #region op车数据
                        //1.3 op车数据 
                        var opCarData = await _sqlSugar.Queryable<Res_CarData>()
                            .LeftJoin<Sys_Users>((cd, u) => cd.CreateUserId == u.Id)
                            .Where((cd, u) => cd.IsDel == 0 && cd.CreateTime >= startDateTime && cd.CreateTime <= endDateTime)
                            .Select((cd, u) => new
                            {
                                Area = cd.UnitArea,
                                Client = cd.UnitName,
                                cd.Contact,
                                Job = "",
                                Tel = cd.ContactTel,
                                cd.CreateUserId,
                                CreatleUserName = u.CnName,
                                cd.CreateTime,
                            })
                            .ToListAsync();
                        var opCarDataGroups = opCarData.GroupBy(x => x.CreateUserId)
                            .Select(g => new
                            {
                                uId = g.Key,
                                name = g.ToList().FirstOrDefault()?.CreatleUserName ?? "-",
                                msgTips = $"上月新增OP地接车资源共{g.Count()}条",
                                userTourGuideData = g.ToList(),
                                Count = g.Count()
                            });
                        var opCarData1 = new
                        {
                            msgTips = $"上月新增OP地接车资源共{opCarData.Count}条",
                            Data = opCarDataGroups,
                        };
                        #endregion

                        #endregion

                        return Ok(JsonView(new
                        {
                            groupNotFillEdFeeData = groupNotFillEdFeeData1,
                            dailyPaymentData = dailyPaymentData,
                            CarData = opCarData1,
                            TourGuideData = opTourGuideData
                        }));
                    }

                    return Ok(JsonView(false, "国交部消息提示正在开发中......"));
                }
                else if (department.Equals("财务部"))//总经办
                {
                    #region 团组收款数据
                    var groupCollectionData = await _sqlSugar.Queryable<Fin_ForeignReceivables>()
                        .LeftJoin<Sys_Users>((fr, u) => fr.CreateUserId == u.Id)
                        .LeftJoin<Grp_DelegationInfo>((fr, u, di) => fr.Diid == di.Id)
                        .LeftJoin<Sys_SetData>((fr, u, di, sd) => fr.Currency == sd.Id)
                        .Where((fr, u, di, sd) => fr.IsDel == 0 && fr.CreateTime >= startDateTime && fr.CreateTime <= endDateTime)
                        .Select((fr, u, di, sd) => new
                        {
                            fr.PriceName,
                            fr.Price,
                            fr.Count,
                            fr.Unit,
                            fr.ItemSumPrice,
                            Currency = sd.Name,
                            GroupName = di.TeamName,
                            fr.CreateUserId,
                            CreateUserName = u.CnName,
                            fr.CreateTime,
                        })
                        .ToListAsync();

                    var groupCollectionGroups = groupCollectionData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreateUserName ?? "-",
                            msgTips = $"上月累计团组收款共{g.Count()}条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });

                    var groupCollectionData1 = new
                    {
                        msgTips = $"上月累计团组收款共{groupCollectionData.Count}条",
                        Data = groupCollectionGroups,
                    };

                    #endregion

                    #region 日付申请未审核数据
                    var dailyPaymentData = await _sqlSugar.Queryable<Fin_DailyFeePayment>()
                                                 .LeftJoin<Sys_Users>((dfp, u) => dfp.CreateUserId == u.Id)
                                                 .Where(dfp => dfp.IsDel == 0 &&
                                                               dfp.MAudit == 0 &&
                                                               dfp.CreateTime >= currStartDate &&
                                                               dfp.CreateTime <= currEntDate
                                                       )
                                                 .OrderBy(dfp => dfp.CreateTime, OrderByType.Desc)
                                                 //.OrderBy(dfp => dfp.FAudit, OrderByType.Desc)
                                                 .Select((dfp, u) => new
                                                 {
                                                     id = dfp.Id,
                                                     amountName = dfp.Instructions,
                                                     amount = dfp.SumPrice,
                                                     fAuditStatus = dfp.FAudit == 1 ? "审核通过" :
                                                                    dfp.FAudit == 2 ? "审核未通过" : "未审核",
                                                     fAuditDate = dfp.FAuditDate,
                                                     dfp.CreateUserId,
                                                     CreateUserName = u.CnName,
                                                     dfp.CreateTime
                                                 })
                                                 .ToListAsync();

                    var dailyPaymentGroups = dailyPaymentData.GroupBy(x => x.CreateUserId)
                        .Select(g => new
                        {
                            uId = g.Key,
                            name = g.ToList().FirstOrDefault()?.CreateUserName ?? "-",
                            msgTips = $"本年有{g.Count()}条未审核日常付款申请条",
                            userTourGuideData = g.ToList(),
                            Count = g.Count()
                        });

                    var dailyPaymentData1 = new
                    {
                        msgTips = $"本年有{dailyPaymentData.Count()}条未审核日常付款申请条",
                        Data = dailyPaymentGroups,
                    };
                    #endregion

                    return Ok(JsonView(new { groupCollectionData = groupCollectionData1, dailyPaymentData = dailyPaymentData1 }));
                }
                return Ok(JsonView(false, "其余部门消息提示正在开发者中......"));
            }
            return Ok(JsonView(false));
        }
        #endregion

        #region backups
        /// <summary>
        /// 数据库备份
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SQLServerBackups()
        {
            var now = DateTime.Now;

            var folderPath = $"D:\\数据备份\\{now.ToString("yyyyMMdd")}\\SQLServerBackups";
            if (!Directory.Exists(folderPath)) Directory.CreateDirectory(folderPath);

            //string backupFilePath = $"{folderPath}\\database_backup_OA2014DB_{now.ToString("yyyyMMddHHmmss")}.bat";
            //var backupSql = $"BACKUP DATABASE [OA2014] TO DISK='{backupFilePath}'";
            //Console.WriteLine($"原生备份开始执行");
            //_sqlSugar.ChangeDatabase(DBEnum.OA2014DB);
            //await _sqlSugar.Ado.ExecuteCommandAsync(backupSql);
            //Console.WriteLine($"原生备份成功，文件已生成到：{backupFilePath}");

            //string backupFilePath = $"{folderPath}\\database_backup_OA2014DB_{now.ToString("yyyyMMddHHmmss")}.sql";
            //var backupSql = $"BACKUP DATABASE [OA2014] TO DISK='{backupFilePath}'";

            //Console.WriteLine($"原生备份开始执行");
            //_sqlSugar.ChangeDatabase(DBEnum.OA2014DB);
            //using (StreamWriter writer = new StreamWriter(backupFilePath))
            //{
            //    // 获取所有表名
            //    var tables = _sqlSugar.Ado.GetDataTable("SELECT name FROM sys.tables").AsEnumerable();

            //    foreach (var tableRow in tables)
            //    {
            //        var tableName = tableRow.Field<string>("name");

            //        // 获取表结构的 SQL
            //        var createTableSql = _sqlSugar.Ado.GetDataTable($@"
            //            SELECT OBJECT_DEFINITION(OBJECT_ID('{tableName}')) AS CreateTableSql").Rows[0][0];
            //        writer.WriteLine(createTableSql);
            //        writer.WriteLine();

            //        // 获取表数据并生成 INSERT 脚本
            //        var data = _sqlSugar.Queryable<dynamic>().AS(tableName) .ToList();
            //        foreach (var item in data)
            //        {
            //            var properties = item.GetType().GetProperties();
            //            var values = new List<string>();
            //            foreach (var prop in properties)
            //            {
            //                var value = prop.GetValue(item);
            //                values.Add(value == null ? "NULL" : $"'{value}'");
            //            }
            //            var insertSql = $"INSERT INTO {tableName} VALUES ({string.Join(", ", values)});";
            //            writer.WriteLine(insertSql);
            //        }
            //    }
            //}

            //Console.WriteLine($"数据库备份成功，SQL 脚本已生成到：{backupFilePath}");

            return Ok(JsonView(false));
        }

        #endregion

        #region 动态读取excel并去重

        /// <summary>
        /// 客户名单excel导入
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DynamicReadExcel(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))
            {
                await file.CopyToAsync(stream);
            }

            Workbook workbook = new Workbook(filePath);

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

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

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

            var lvData = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 33).ToList(); 

            // 遍历数据行（从第二行开始）
            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;  //单位名称
                var cellVal3 = worksheet.Cells[row, 2].Value;  //出访次数

                int count = !string.IsNullOrEmpty(cellVal3?.ToString()) ? int.Parse(cellVal3?.ToString()) : 0;
                var rowData = new Crm_NewClientData() {
                    Number = count,
                    Client = cellVal2?.ToString().Trim(),//单位名称
                };

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


            if (clients.Any())
            {
                var clientArr = clients.Select(x => x.Client).ToList();
                var clientDatas = _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0)
                    .ToList()
                    .Select(x => new { id = x.Id, client = AesEncryptionHelper.Decrypt(x.Client), remark = AesEncryptionHelper.Decrypt(x.Remark) });

                var clientDatas1 = clientDatas.Where(x => clientArr.Contains(x.client)).ToList();

                var updates = new List<Crm_NewClientData>();

                foreach (var item in clientDatas1)
                {

                    string newRemark = item.remark;
                    var str = clients.FirstOrDefault(x => x.Client.Equals(item.client))?.Number ?? 0;
                    if (str > 0)
                    {
                        newRemark += $"\r\n2025年拟出访次数{str}次";
                    }

                    updates.Add(new Crm_NewClientData() { 
                        Id = item.id,

                        Remark = AesEncryptionHelper.Encrypt(newRemark)
                    });

                }

                if (updates.Any())
                {
                    //加密后更改
                    var update = _sqlSugar.Updateable(updates).UpdateColumns(it => new {
                        //it.Client, 
                        it.Remark,
                        //it.Phone,
                        //it.Address,
                        // it.Location,
                    }).ExecuteCommand();


                    if (update > 0)
                    {
                        return Ok(JsonView(true, $"数据加密成功!加密条数：{update}"));
                    }
                }


            }
            return Ok(JsonView(false));
        }



        /// <summary>
        /// 客户数据加密
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DynamicReadExcel1()
        {

            // 动态存储列名
            var clients = await _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0 && x.Id > 6789).ToListAsync();

            if (clients.Any())
            {
                foreach (var item in clients)
                {
                    item.Weight = AesEncryptionHelper.Encrypt("391");
                    //Encrypted
                    //EncryptionProcessor.EncryptProperties(item);
                }

                //加密后更改
                var update = _sqlSugar.Updateable(clients).UpdateColumns(it => new { 
                    //it.Client, 
                    it.Weight,
                    //it.Phone,
                    //it.Address,
                    // it.Location,
                }).ExecuteCommand();


                if (update > 0)
                {
                    return Ok(JsonView(true,$"数据加密成功!加密条数：{update}"));
                }

            }

            return Ok(JsonView(false));
        }


        /// <summary>
        /// 数库事务异常测试
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> TransactionExTest()
        {
            _sqlSugar.BeginTran();


            var a1 = new List<dynamic>();

            var a2 = a1[0].ToString();


            _sqlSugar.RollbackTran();

            return Ok(JsonView(false));
        }


        /// <summary>
        /// 数据测试
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> TransactionExTest1()
        {
            
            var groupIds = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                .Where(x => x.IsDel == 0)
                .Where(x => x.VisitDate >= Convert.ToDateTime("2025-01-01 00:00:00") && x.VisitDate <= Convert.ToDateTime("2025-12-31 23:59:59"))
                .Select(x => x.Id)
                .ToListAsync();

            var datas = new List<View1>();
            foreach (var id in groupIds)
            {
                var sql = @"
SELECT 
    dac.DiId,
    CASE ntf.City
        WHEN '全部城市' THEN ntf.Country + ' -> 全部城市'
        WHEN '其他城市' THEN ntf.Country + ' -> 其他城市'
        ELSE ntf.Country + ' -> ' + ntf.City
    END as City
FROM Grp_DayAndCost dac
LEFT JOIN grp_NationalTravelFee ntf ON dac.NationalTravelFeeId = ntf.Id
WHERE dac.IsDel = 0 
    AND dac.DiId = @DiId
GROUP BY 
    dac.DiId,
    CASE ntf.City
        WHEN '全部城市' THEN ntf.Country + ' -> 全部城市'
        WHEN '其他城市' THEN ntf.Country + ' -> 其他城市'
        ELSE ntf.Country + ' -> ' + ntf.City
    END";

                var data = await _sqlSugar.Ado.SqlQueryAsync<View1>(sql, new { DiId = id });

                datas.AddRange(data);
            }

            var view = datas.GroupBy(x => x.City)
                .Select(x => new
                {
                    x.Key,
                    Count = x.Count()
                })
                .OrderByDescending(x => x.Count)
                .Skip(1)
                .Take(20)
                .ToList();
            return Ok(JsonView(view));
        }

        private class View1 {

            public int DiId { get; set; }

            public string City { get; set; }
        }



        #endregion

        #region 审批模板


        /// <summary>
        /// 审批模板 - 创建模板
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AuditTempCreate()
        {
            //领用-其他物品
            //var result = await _approvalProcessRep.CreateAuditTemplate(ApprovalProcessDto.AuditTempOtherGoods());
            //领用-贵重物品
            var result = await _approvalProcessRep.CreateAuditTemplateAsync(ApprovalProcessDto.AuditTempValuableGoods());
            return Ok(JsonView(result));
        }

        #endregion

        #region 资料相关Excel 导入
        /// <summary>
        /// excel导入 策划部供应商资料
        /// </summary>
        /// <param name="file"></param>
        /// <param name="currUserId">录入人userId</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelImportMediaSupplier(IFormFile file, [FromQuery] int currUserId = 258)
        {
            // 检查文件是否为空
            if (file == null || file.Length == 0)
            {
                return Ok(JsonView(false, "请选择文件!"));
            }

            // 文件签名验证
            var perExtensions = new[] { ".xls", ".xlsx" };
            Dictionary<string, List<byte[]>> fileSignature = CommonFun.FileSignature;
            fileSignature = fileSignature.Where(pair => perExtensions.Contains(pair.Key)).ToDictionary(pair => pair.Key, pair => pair.Value);

            // 文件扩展名验证
            var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
            if (string.IsNullOrEmpty(ext) || !fileSignature.ContainsKey(ext))
            {
                return Ok(JsonView(false, "不支持的文件类型!"));
            }

            using (var memoryStream = new MemoryStream())
            {
                await file.CopyToAsync(memoryStream);

                // 检查文件签名
                var fileData = memoryStream.ToArray();
                var signatures = fileSignature[ext];
                bool signatureValid = signatures.Any(signature =>
                    fileData.Take(signature.Length).SequenceEqual(signature));

                if (!signatureValid)
                {
                    return Ok(JsonView(false, "文件内容与类型不匹配!"));
                }
            }

            // 保存文件到服务器
            var uploadsFolder = @$"D:\FTP\File\OA2023\Office\Excel\MediaSupplierExcelImportFile";
            if (!Directory.Exists(uploadsFolder))
            {
                Directory.CreateDirectory(uploadsFolder);
            }

            var fileName = CommonFun.ValidFileName(file.Name);

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

            Workbook workbook = new Workbook(filePath);

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

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

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

            var typeDatas = await _sqlSugar.Queryable<Sys_SetData>()
               .Where(x => x.IsDel == 0 && x.STid == 21)
               .Select(x => new { x.Id, Text = x.Name })
               .ToListAsync();

            // 遍历数据行（从第二行开始）
            int rowCount = worksheet.Cells.MaxDataRow + 1;
            var msgs = new StringBuilder();
            for (int row = headerRowIndex + 2; row < rowCount; row++)
            {
                var cellVal1 = worksheet.Cells[row, 0].Value;    //序号
                var cellVal2 = worksheet.Cells[row, 1].Value;    //供应商类型
                var cellVal3 = worksheet.Cells[row, 2].Value;    //地区
                var cellVal4 = worksheet.Cells[row, 3].Value;    //城市
                var cellVal5 = worksheet.Cells[row, 4].Value;    //单位名称
                var cellVal6 = worksheet.Cells[row, 5].Value;    //单位联系人
                var cellVal7 = worksheet.Cells[row, 6].Value;    //性别
                var cellVal8 = worksheet.Cells[row, 7].Value;    //联系电话
                var cellVal9 = worksheet.Cells[row, 8].Value;    //电子邮件
                var cellVal10 = worksheet.Cells[row, 9].Value;   //单位缩写
                var cellVal11 = worksheet.Cells[row, 10].Value;  //职位
                var cellVal12 = worksheet.Cells[row, 11].Value;  //传真号码
                var cellVal13 = worksheet.Cells[row, 12].Value;  //微信
                var cellVal14 = worksheet.Cells[row, 13].Value;  //单位地址
                var cellVal15 = worksheet.Cells[row, 14].Value;  //备注

                int typeId = typeDatas.FirstOrDefault(x => x.Text == cellVal2?.ToString())?.Id ?? 0;
                if (typeId < 1)
                {
                    var name = cellVal5?.ToString() ?? $"序号：{cellVal1?.ToString() ?? "-"}";
                    msgs.AppendLine($"[{name}]未设置供应商类型！请手动录入！！！");
                    continue;
                }

                var rowData = new Res_MediaSuppliers()
                {
                    TypeId = typeId,
                    Privince = cellVal3?.ToString() ?? "",
                    City = cellVal4?.ToString() ?? "",
                    UnitName = cellVal5?.ToString() ?? "",
                    Contact = cellVal6?.ToString() ?? "",
                    Sex = string.IsNullOrEmpty(cellVal7?.ToString() ?? "") ? -1 : cellVal7.ToString().Equals("男") ? 0 : 1,
                    Tel = cellVal8?.ToString() ?? "",
                    Email = cellVal9?.ToString() ?? "",
                    UnitAbbreviation = cellVal10?.ToString() ?? "",
                    Post = cellVal11?.ToString() ?? "",
                    Fax = cellVal12?.ToString() ?? "",
                    WeChat = cellVal13?.ToString() ?? "",
                    UnitAddress = cellVal14?.ToString() ?? "",
                    Remark = cellVal15?.ToString() ?? "",
                    CreateUserId = currUserId

                };

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

            if (datas.Any())
            {
                var insert = await _sqlSugar.Insertable(datas).ExecuteCommandAsync();
                if (insert > 0)
                {
                    return Ok(JsonView(true, $"导入成功！count:{insert} warningMsg:{msgs.ToString()}"));
                }
            }
            return Ok(JsonView(false, "excel导入失败！"));
        }


        /// <summary>
        /// excel导入 op地接供应商资料收集模板
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> CarDataSouceExcelTemp()
        {
            var url = $"http://132.232.92.186:24/Office/Excel/CarDataSouceImportFile/op地接供应商资料收集模板.xlsx";
            return Ok(JsonView(true, url));
        }

        /// <summary>
        /// excel导入 车公司资料
        /// </summary>
        /// <param name="file"></param>
        /// <param name="currUserId">录入人userId</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelImportCarDataSouce(IFormFile file, [FromQuery] int currUserId = 208)
        {
            // 检查文件是否为空
            if (file == null || file.Length == 0)
            {
                return Ok(JsonView(false, "请选择文件!"));
            }

            // 文件签名验证
            var perExtensions = new[] { ".xls", ".xlsx" };
            Dictionary<string, List<byte[]>> fileSignature = CommonFun.FileSignature;
            fileSignature = fileSignature.Where(pair => perExtensions.Contains(pair.Key)).ToDictionary(pair => pair.Key, pair => pair.Value);

            // 文件扩展名验证
            var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
            if (string.IsNullOrEmpty(ext) || !fileSignature.ContainsKey(ext))
            {
                return Ok(JsonView(false, "不支持的文件类型!"));
            }

            using (var memoryStream = new MemoryStream())
            {
                await file.CopyToAsync(memoryStream);

                // 检查文件签名
                var fileData = memoryStream.ToArray();
                var signatures = fileSignature[ext];
                bool signatureValid = signatures.Any(signature =>
                    fileData.Take(signature.Length).SequenceEqual(signature));

                if (!signatureValid)
                {
                    return Ok(JsonView(false, "文件内容与类型不匹配!"));
                }
            }

            // 保存文件到服务器
            var uploadsFolder = @$"D:\FTP\File\OA2023\Office\Excel\CarDataSouceImportFile";
            if (!Directory.Exists(uploadsFolder))
            {
                Directory.CreateDirectory(uploadsFolder);
            }

            var fileName = CommonFun.ValidFileName(file.Name);

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

            Workbook workbook = new Workbook(filePath);

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

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

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

            // 遍历数据行（从第三行开始）
            int rowCount = worksheet.Cells.MaxDataRow + 1;
            var msgs = new StringBuilder();
            for (int row = headerRowIndex + 2; row < rowCount; row++)
            {
                var cellVal1 = worksheet.Cells[row, 0].Value;    //地区
                var cellVal2 = worksheet.Cells[row, 1].Value;    //公司名称
                var cellVal3 = worksheet.Cells[row, 2].Value;    //公司地址
                var cellVal4 = worksheet.Cells[row, 3].Value;    //联系人
                var cellVal5 = worksheet.Cells[row, 4].Value;    //联系电话
                var cellVal6 = worksheet.Cells[row, 5].Value;    //电子邮箱
                var cellVal7 = worksheet.Cells[row, 6].Value;    //其他信息

                var dtTimeNoew = DateTime.Now;
                var rowData = new Res_CarData()
                {
                    UnitArea = cellVal1?.ToString() ?? "",
                    UnitName = cellVal2?.ToString() ?? "",
                    Address = cellVal3?.ToString() ?? "",
                    Contact = cellVal4?.ToString() ?? "",
                    ContactTel = cellVal5?.ToString() ?? "",
                    ContactEmail = cellVal6?.ToString() ?? "",
                    OtherInfo = cellVal7?.ToString() ?? "",
                    LastUpdateTime = dtTimeNoew,
                    LastUpdateUserId = currUserId,
                    CreateTime = dtTimeNoew,
                    CreateUserId = currUserId,
                    Remark = $"excel导入添加！"
                };
                EncryptionProcessor.EncryptProperties(rowData);
                datas.Add(rowData); 
            }

            if (datas.Any())
            {
                var insert = await _sqlSugar.Insertable(datas).ExecuteCommandAsync();
                if (insert > 0)
                {
                    return Ok(JsonView(true, $"导入成功！count:{insert} warningMsg:{msgs.ToString()}"));
                }
            }
            return Ok(JsonView(false, "excel导入失败！"));
        }

        /// <summary>
        /// excel导入 世运会成本预算明细Excel
        /// </summary>
        /// <param name="file"></param>
        /// <param name="currUserId">录入人userId</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelImportGamesBudgetDataSouce(IFormFile file, [FromQuery] int currUserId = 208)
        {
            // 检查文件是否为空
            if (file == null || file.Length == 0)
            {
                return Ok(JsonView(false, "请选择文件!"));
            }

            // 文件签名验证
            var perExtensions = new[] { ".xls", ".xlsx" };
            Dictionary<string, List<byte[]>> fileSignature = CommonFun.FileSignature;
            fileSignature = fileSignature.Where(pair => perExtensions.Contains(pair.Key)).ToDictionary(pair => pair.Key, pair => pair.Value);

            // 文件扩展名验证
            var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
            if (string.IsNullOrEmpty(ext) || !fileSignature.ContainsKey(ext))
            {
                return Ok(JsonView(false, "不支持的文件类型!"));
            }

            using (var memoryStream = new MemoryStream())
            {
                await file.CopyToAsync(memoryStream);

                // 检查文件签名
                var fileData = memoryStream.ToArray();
                var signatures = fileSignature[ext];
                bool signatureValid = signatures.Any(signature =>
                    fileData.Take(signature.Length).SequenceEqual(signature));

                if (!signatureValid)
                {
                    return Ok(JsonView(false, "文件内容与类型不匹配!"));
                }
            }

            // 保存文件到服务器
            var uploadsFolder = @$"D:\FTP\File\OA2023\Office\Excel\CarDataSouceImportFile";
            if (!Directory.Exists(uploadsFolder))
            {
                Directory.CreateDirectory(uploadsFolder);
            }

            var fileName = CommonFun.ValidFileName(file.Name);

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

            Workbook workbook = new Workbook(filePath);

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

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

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

            // 遍历数据行（从第3行开始）
            int rowCount = worksheet.Cells.MaxDataRow + 1;
            var msgs = new StringBuilder();
            for (int row = headerRowIndex + 2; row < rowCount; row++)
            {
                var cellVal0 = worksheet.Cells[row, 0].Value;    //直属父级
                var cellVal1 = worksheet.Cells[row, 1].Value;    //工作项目
                var cellVal2 = worksheet.Cells[row, 2].Value;    //测算内容
                var cellVal3 = worksheet.Cells[row, 3].Value;    //数量
                var cellVal4 = worksheet.Cells[row, 4].Value;    //单位
                var cellVal5 = worksheet.Cells[row, 5].Value;    //单价（元）
                var cellVal6 = worksheet.Cells[row, 6].Value;    //周期-时间
                var cellVal7 = worksheet.Cells[row, 7].Value;    //周期-单位
                var cellVal8 = worksheet.Cells[row, 8].Value;    //项费用合计
                var cellVal9 = worksheet.Cells[row, 9].Value;    //说明
                var cellVal10 = worksheet.Cells[row, 10].Value;    //备注

                var dtTimeNoew = DateTime.Now;

                decimal.TryParse(cellVal3?.ToString(), out decimal quantity);
                decimal.TryParse(cellVal5?.ToString(), out decimal unitPrice);
                decimal.TryParse(cellVal8?.ToString(), out decimal itemTotal);
                int.TryParse(cellVal6?.ToString(), out int cycleTime);

                var rowData = new Grp_GamesBudgetMaster()
                {
                    T0 = cellVal0?.ToString() ?? "",
                    ProjectWork = cellVal1?.ToString() ?? "",
                    CalculationContent = cellVal2?.ToString() ?? "",
                    Quantity = quantity,
                    Unit = cellVal4?.ToString() ?? "",
                    UnitPrice = unitPrice,
                    CycleTime = cycleTime,
                    CycleUnit = cellVal7?.ToString() ?? "",
                    ItemTotal = itemTotal,
                    Specification = cellVal9?.ToString() ?? "",
                    LastUpdateTime = dtTimeNoew.ToString("yyyy-MM-dd HH:mm:ss"),
                    LastUpdateUserId = currUserId,
                    CreateTime = dtTimeNoew,
                    CreateUserId = currUserId,
                    Remark = cellVal10?.ToString() ?? ""
                };

                datas.Add(rowData);
            }

            if (datas.Any())
            {
                var insert = await _sqlSugar.Insertable(datas).ExecuteCommandAsync();
                if (insert > 0)
                {
                    return Ok(JsonView(true, $"导入成功！count:{insert} warningMsg:{msgs.ToString()}"));
                }
            }
            return Ok(JsonView(false, "excel导入失败！"));
        }

        /// <summary>
        /// excel导入 签证费用标准
        /// </summary>
        /// <param name="file"></param>
        /// <param name="currUserId">录入人userId</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelImportVisaFeeStandard(IFormFile file, [FromQuery] int currUserId = 208)
        {
            // 检查文件是否为空
            if (file == null || file.Length == 0)
            {
                return Ok(JsonView(false, "请选择文件!"));
            }

            // 文件签名验证
            var perExtensions = new[] { ".xls", ".xlsx" };
            Dictionary<string, List<byte[]>> fileSignature = CommonFun.FileSignature;
            fileSignature = fileSignature.Where(pair => perExtensions.Contains(pair.Key)).ToDictionary(pair => pair.Key, pair => pair.Value);

            // 文件扩展名验证
            var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
            if (string.IsNullOrEmpty(ext) || !fileSignature.ContainsKey(ext))
            {
                return Ok(JsonView(false, "不支持的文件类型!"));
            }

            using (var memoryStream = new MemoryStream())
            {
                await file.CopyToAsync(memoryStream);

                // 检查文件签名
                var fileData = memoryStream.ToArray();
                var signatures = fileSignature[ext];
                bool signatureValid = signatures.Any(signature =>
                    fileData.Take(signature.Length).SequenceEqual(signature));

                if (!signatureValid)
                {
                    return Ok(JsonView(false, "文件内容与类型不匹配!"));
                }
            }

            // 保存文件到服务器
            var uploadsFolder = @$"D:\FTP\File\OA2023\Office\Excel\CarDataSouceImportFile";
            if (!Directory.Exists(uploadsFolder))
            {
                Directory.CreateDirectory(uploadsFolder);
            }

            var fileName = CommonFun.ValidFileName(file.Name);

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

            var workbook = new Workbook(filePath);


            // 获取第一个工作表
            Worksheet worksheet = workbook.Worksheets[0]; // 因公护照签证费用标准
            Worksheet worksheet1 = workbook.Worksheets[1]; // 因私护照签证费用标准

            var oldVisaFeeStandards = await _sqlSugar.Queryable<Res_CountryFeeCost>()
                .Where(x => x.IsDel == 0)
                .ToListAsync();

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

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

            // 遍历数据行（从第3行开始）
            int rowCount = worksheet.Cells.MaxDataRow + 1;
            var msgs = new StringBuilder();
            for (int row = headerRowIndex + 1; row < rowCount; row++)
            {
                var cellVal0 = worksheet.Cells[row, 0].Value;      //国家
                var cellVal1 = worksheet.Cells[row, 1].Value;      //公务护照免签
                var cellVal2 = worksheet.Cells[row, 2].Value;      //公务护照签证费
                var cellVal3 = worksheet.Cells[row, 3].Value;      //公务护照签证代办费
                var cellVal4 = worksheet.Cells[row, 4].Value;      //公务普通护照免签
                var cellVal5 = worksheet.Cells[row, 5].Value;      //公务普通护照签证费
                var cellVal6 = worksheet.Cells[row, 6].Value;      //公务普通护照代办费
                var cellVal7 = worksheet.Cells[row, 7].Value;      //云南代办费
                var cellVal8 = worksheet.Cells[row, 8].Value;      //贵州代办费
                var cellVal9 = worksheet.Cells[row, 9].Value;      //重庆代办费
                var cellVal10 = worksheet.Cells[row, 10].Value;    //重庆外办出入境证明费用
                var cellVal11 = worksheet.Cells[row, 11].Value;    //送签地点
                var cellVal12 = worksheet.Cells[row, 12].Value;    //办理时间（工作日）

                var dtTimeNoew = DateTime.Now;

                var isVisaExemptionLargeVal = cellVal1?.ToString() == "是"; //公务护照免签
                _ = decimal.TryParse(cellVal2?.ToString(), out decimal largeVisaPrice);    //公务护照签证费
                _ = decimal.TryParse(cellVal3?.ToString(), out decimal largeAgencyFee);    //公务护照签证代办费
                var isVisaExemptionSmallVal = cellVal4?.ToString() == "是" ? true : false; //公务普通护照免签
                _ = decimal.TryParse(cellVal5?.ToString(), out decimal smallVisaPrice);    //公务普通护照签证费
                _ = decimal.TryParse(cellVal6?.ToString(), out decimal smallAgencyFee);    //公务普通护照签证代办费

                _ = decimal.TryParse(cellVal7?.ToString(), out decimal yunNanAgencyFee);    //云南代办费
                _ = decimal.TryParse(cellVal8?.ToString(), out decimal guiZhouAgencyFee);    //贵州代办费
                _ = decimal.TryParse(cellVal9?.ToString(), out decimal chongQingAgencyFee);    //重庆代办费

                string countryName = cellVal0?.ToString() ?? "";
                string continent=string.Empty,
                       remark =string.Empty;
                var oldDataInfo = oldVisaFeeStandards.FirstOrDefault(x => x.VisaFeeType == 0 && x.VisaCountry == countryName);
                if (oldDataInfo != null)
                {
                    continent = oldDataInfo.VisaContinent;
                    remark = oldDataInfo.VisaPriceDesc;
                }

                var detailsList = new List<VisaFeeStandardDetails>() {
                    new(){
                        ProvinceId = 122,
                        ProvinceName = "四川",
                        VisaAddress = cellVal11?.ToString() ?? "",
                        VisaTime = cellVal12?.ToString() ?? "0",
                        IsVisaExemptionLarge = isVisaExemptionLargeVal,
                        LargeVisaPrice = largeVisaPrice,
                        LargeAgencyFee = largeAgencyFee,
                        IsVisaExemptionSmall = isVisaExemptionSmallVal,
                        SmallVisaPrice = smallVisaPrice,
                        SmallAgencyFee = smallAgencyFee,
                        Remark = remark,
                    },
                    new(){
                        ProvinceId = 103,
                        ProvinceName = "重庆",
                        SmallAgencyFee = chongQingAgencyFee,
                        NormExtFee = 40.00M,
                        UrgExtFee = 140.00M,
                    },
                    new(){
                        ProvinceId = 108,
                        ProvinceName = "贵州",
                        SmallAgencyFee = guiZhouAgencyFee,
                    },
                    new(){
                        ProvinceId = 132,
                        ProvinceName = "云南",
                        SmallAgencyFee = yunNanAgencyFee,
                    },
                };

                var rowData = new VisaFeeStandardInfoView()
                {
                   Continent = continent,
                   Country = countryName,
                   FeeType = 0,
                   VisaFees = detailsList
                };

                datas.Add(rowData);
            }

            // 获取表头（第一行作为列名）
            int headerRowIndex1 = 0;
            Row headerRow1 = worksheet1.Cells.Rows[headerRowIndex1];
            int colCount1 = worksheet1.Cells.MaxDataColumn + 1;

            // 遍历数据行（从第3行开始）
            int rowCount1 = worksheet1.Cells.MaxDataRow + 1;
            for (int row = headerRowIndex1 + 1; row < rowCount1; row++)
            {
                var cellVal0 = worksheet1.Cells[row, 0].Value;      //国家
                var cellVal1 = worksheet1.Cells[row, 1].Value;      //普通护照免签
                var cellVal2 = worksheet1.Cells[row, 2].Value;      //签证费
                var cellVal3 = worksheet1.Cells[row, 3].Value;      //送签地点
                var cellVal4 = worksheet1.Cells[row, 4].Value;      //办理时间（工作日）

                var dtTimeNoew = DateTime.Now;

                var isVisaExemptionSmallVal = cellVal1?.ToString() == "是"; //普通护照免签
                _ = decimal.TryParse(cellVal2?.ToString(), out decimal smallVisaPrice);    //普通护照签证费

                string countryName = cellVal0?.ToString() ?? "";
                string continent = string.Empty;
                var oldDataInfo = oldVisaFeeStandards.FirstOrDefault(x => x.VisaFeeType == 1 && x.VisaCountry == countryName);
                if (oldDataInfo != null)
                {
                    continent = oldDataInfo.VisaContinent;
                }

                var detailsList = new List<VisaFeeStandardDetails>() {
                    new(){
                        ProvinceId = 122,
                        ProvinceName = "四川",
                        VisaAddress = cellVal3?.ToString() ?? "",
                        VisaTime = cellVal4?.ToString() ?? "0",
                        IsVisaExemptionSmall = isVisaExemptionSmallVal,
                        SmallVisaPrice = smallVisaPrice,
                    }
                };

                var rowData = new VisaFeeStandardInfoView()
                {
                    Continent = continent,
                    Country = countryName,
                    FeeType = 1,
                    VisaFees = detailsList
                };

                datas.Add(rowData);
            }


            if (datas.Any())
            {
                _sqlSugar.BeginTran();
                try
                {
                    foreach (var item in datas)
                    {

                        var now = DateTime.Now;
                        var visaFeeStandard = _mapper.Map<Res_VisaFeeStandard>(item);
                        visaFeeStandard.LastUpdateTime = now;
                        visaFeeStandard.LastUpdateUserId = currUserId;
                        visaFeeStandard.CreateTime = now;
                        visaFeeStandard.CreateUserId = currUserId;

                        var insertId = await _sqlSugar.Insertable(visaFeeStandard).ExecuteReturnIdentityAsync();

                        if (insertId < 1)
                        {
                            _sqlSugar.RollbackTran();
                            return Ok(JsonView(false, "excel导入失败！"));
                        }

                        var visaFeeStandardDetails = _mapper.Map<List<Res_VisaFeeStandardDetails>>(item.VisaFees);
                        visaFeeStandardDetails.ForEach(x => {
                            x.ParentId = insertId;
                            x.CreateUserId = currUserId;
                            x.CreateTime = now;
                        });

                        var insertSubId = await _sqlSugar.Insertable(visaFeeStandardDetails).ExecuteCommandAsync();
                        if (insertSubId < 1)
                        {
                            _sqlSugar.RollbackTran();
                            return Ok(JsonView(false, "excel导入失败！"));
                        }

                    }
                    _sqlSugar.CommitTran();

                    return Ok(JsonView(true, $"excel导入成功！Total：{datas.Count}"));
                }
                catch (Exception ex)
                {
                    _sqlSugar.RollbackTran();
                    return Ok(JsonView(false, ex.Message));
                }

            }
            return Ok(JsonView(false, "excel导入失败！"));
        }

        #endregion

        #region 资料导出Excel
        /// <summary>
        /// excel导出 策划部供应商资料
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelDownloadMediaSupplier()
        {
            var datas = await _sqlSugar.Queryable<Res_MediaSuppliers>().Where(x => x.IsDel == 0).ToListAsync();

            //解密
            var dataView = new List<MediaSupplierExcel>();
            foreach (var item in datas)
            {
                EncryptionProcessor.DecryptProperties(item);

                var typeName = await _sqlSugar.Queryable<Sys_SetData>()
                    .Where(x => x.IsDel == 0 && x.Id == item.TypeId)
                    .Select(x => x.Name)
                    .FirstAsync();

                var createUserName = await _sqlSugar.Queryable<Sys_Users>()
                    .Where(x => x.Id == item.CreateUserId)
                    .Select(x => x.CnName)
                    .FirstAsync();

                dataView.Add(new MediaSupplierExcel
                {
                    Id = item.Id,
                    TypeName = typeName,
                    Privince = item.Privince,
                    City = item.City,
                    UnitName = item.UnitName,
                    UnitAbbreviation = item.UnitAbbreviation,
                    UnitAddress = item.UnitAddress,
                    Contact = item.Contact,
                    Sex = item.Sex,
                    Post = item.Post,
                    Tel = item.Tel,
                    Email = item.Email,
                    Fax = item.Fax,
                    CreateTime = item.CreateTime,
                    CrateUserName = createUserName
                });
            }

            var exportResult = await GeneralMethod.ExportToExcel<MediaSupplierExcel>(dataView, "策划部供应商资料");

            return Ok(JsonView(exportResult));
        }

        private class MediaSupplierExcel
        {
            public int Id { get; set; }

            /// <summary>
            /// 类型
            /// </summary>
            public string TypeName { get; set; }


            /// <summary>
            /// 供应商Type（设置数据外键编号）
            /// </summary>
            public int TypeId { get; set; }

            /// <summary>
            /// 省份
            /// </summary>
            public string Privince { get; set; }

            /// <summary>
            /// 城市
            /// </summary>
            public string City { get; set; }

            /// <summary>
            /// 单位名称
            /// </summary>
            public string UnitName { get; set; }

            /// <summary>
            /// 单位缩写
            /// </summary>
            public string UnitAbbreviation { get; set; }

            /// <summary>
            /// 单位地址
            /// </summary>
            public string UnitAddress { get; set; }

            /// <summary>
            /// 单位联系人
            /// </summary>
            public string Contact { get; set; }

            /// <summary>
            /// 联系人性别
            /// </summary>
            public int Sex { get; set; }

            /// <summary>
            /// 性别
            /// </summary>
            public string SexRole
            {
                get
                {
                    if (Sex == 0) return "男";
                    else if (Sex == 1) return "女";
                    return "";
                }
            }

            /// <summary>
            /// 联系人职位
            /// </summary>
            public string Post { get; set; }

            /// <summary>
            /// 联系人电话
            /// </summary>
            public string Tel { get; set; }

            /// <summary>
            /// 联系人邮箱
            /// </summary>
            public string Email { get; set; }

            /// <summary>
            /// 联系人微信
            /// </summary>
            public string WeChat { get; set; }
            /// <summary>
            /// 传真
            /// </summary>
            public string Fax { get; set; }

            /// <summary>
            /// 创建人
            /// </summary>
            public string CrateUserName { get; set; }

            /// <summary>
            /// 创建时间
            /// </summary>
            public DateTime CreateTime { get; set; } = DateTime.Now;

            /// <summary>
            /// 备注
            /// </summary>
            public string Remark { get; set; }

        }

        /// <summary>
        /// excel导出 车供应商资料
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelDownloadCar()
        {
            var datas = await _sqlSugar.Queryable<Res_CarData>().Where(x => x.IsDel == 0).ToListAsync();

            var dataView = new List<CarDataExcel>();

            var userInfos = await _sqlSugar.Queryable<Sys_Users>()
                .Select(x => new { x.Id, x.CnName })
                .ToListAsync();
            foreach (var item in datas)
            {
                //解密
                EncryptionProcessor.DecryptProperties(item);

                var createUserName = userInfos.Find(x => x.Id == item.CreateUserId)?.CnName ?? "-";
                var lastUpdateUserName = userInfos.Find(x => x.Id == item.LastUpdateUserId)?.CnName ?? "-";

                dataView.Add(new CarDataExcel
                {
                    Id = item.Id,
                    UnitArea = item.UnitArea,
                    UnitName = item.UnitName,
                    Address = item.Address,
                    Contact = item.Contact,
                    ContactTel = item.ContactTel,
                    ContactEmail = item.ContactEmail,
                    ContactFax = item.ContactFax,
                    CarDes = item.CarDes,
                    OtherInfo = item.OtherInfo,
                    Score = item.Score,
                    QualificationScore = item.QualificationScore,
                    CarAgeScore = item.CarAgeScore,
                    CleanImgScore = item.CleanImgScore,
                    SmellScore = item.SmellScore,
                    WaterPaperScore = item.WaterPaperScore,
                    HardwareScore = item.HardwareScore,
                    SafetyScore = item.SafetyScore,
                    DrivingAgeScore = item.DrivingAgeScore,
                    Remark = item.Remark,
                    LastUpdateTime = item.LastUpdateTime,
                    LastUpdateUserName = lastUpdateUserName,
                    CreateTime = item.CreateTime,
                    CrateUserName = createUserName
                });
            }

            var exportResult = await GeneralMethod.ExportToExcel<CarDataExcel>(dataView, "车公司资料");

            return Ok(JsonView(exportResult));
        }

        private class CarDataExcel
        {
            public int Id { get; set; }

            /// <summary>
            /// 区域
            /// </summary>
            public string UnitArea { get; set; }

            /// <summary>
            /// 公司名称
            /// </summary>
            public string UnitName { get; set; }

            /// <summary>
            /// 地址
            /// </summary>
            public string Address { get; set; }

            /// <summary>
            /// 联系人
            /// </summary>
            public string Contact { get; set; }

            /// <summary>
            /// 联系人手机号
            /// </summary>
            public string ContactTel { get; set; }

            /// <summary>
            /// 联系人邮箱
            /// </summary>
            public string ContactEmail { get; set; }

            /// <summary>
            /// 联系人传真
            /// </summary>
            public string ContactFax { get; set; }

            /// <summary>
            /// 车描述
            /// </summary>
            public string CarDes { get; set; }

            ///// <summary>
            ///// 车图片路径
            ///// 存储多个 使用/r/n
            ///// </summary>
            //public string CarPicPaths { get; set; }

            /// <summary>
            /// 其他信息
            /// </summary>
            public string OtherInfo { get; set; }

            /// <summary>
            /// 服务评分
            /// </summary>
            public int Score { get; set; }

            /// <summary>
            /// 相关的车辆资质
            /// A B C  选择
            /// </summary>
            public string QualificationScore { get; set; }

            /// <summary>
            /// 车辆2-4年新，VIP及以上需要2年新
            /// A B C  选择
            /// </summary>
            public string CarAgeScore { get; set; }

            /// <summary>
            /// 车身干净，无文字图片等
            /// A B C  选择
            /// </summary>
            public string CleanImgScore { get; set; }

            /// <summary>
            /// 车内整洁、无异味
            /// A B C  选择
            /// </summary>
            public string SmellScore { get; set; }

            /// <summary>
            /// 提前备水，纸巾等
            /// A B C  选择
            /// </summary>
            public string WaterPaperScore { get; set; }

            /// <summary>
            /// 车辆配置高（皮座椅等）
            /// A B C  选择
            /// </summary>
            public string HardwareScore { get; set; }

            /// <summary>
            /// 时间概念强
            /// A B C  选择
            /// </summary>
            public string TimeScore { get; set; }

            /// <summary>
            /// 安全意识高
            /// A B C  选择
            /// </summary>
            public string SafetyScore { get; set; }

            /// <summary>
            /// 司机驾龄时间长，提前熟悉路线
            /// A B C  选择
            /// </summary>
            public string DrivingAgeScore { get; set; }

            /// <summary>
            /// 最后更新者Id
            /// </summary>
            public int LastUpdateUserId { get; set; }

            /// <summary>
            /// 最后更新者
            /// </summary>
            public string? LastUpdateUserName { get; set; }

            /// <summary>
            ///  最后更新时间
            /// </summary>
            public DateTime LastUpdateTime { get; set; }

            /// <summary>
            /// 创建人
            /// </summary>
            public string CrateUserName { get; set; }

            /// <summary>
            /// 创建时间
            /// </summary>
            public DateTime CreateTime { get; set; } = DateTime.Now;

            /// <summary>
            /// 备注
            /// </summary>
            public string Remark { get; set; }

        }
        
        /// <summary>
        /// excel导出 导游地接
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ExcelDownloadLocalGuide()
        {
            var datas = await _sqlSugar.Queryable<Res_LocalGuideData>().Where(x => x.IsDel == 0).ToListAsync();

            var dataView = new List<LocalGuideDataExcel>();

            var userInfos = await _sqlSugar.Queryable<Sys_Users>()
                .Select(x => new { x.Id, x.CnName })
                .ToListAsync();
            foreach (var item in datas)
            {
                //解密
                EncryptionProcessor.DecryptProperties(item);

                var createUserName = userInfos.Find(x => x.Id == item.CreateUserId)?.CnName ?? "-";
                var lastUpdateUserName = userInfos.Find(x => x.Id == item.LastUpdateUserId)?.CnName ?? "-";

                dataView.Add(new LocalGuideDataExcel
                {
                    Id = item.Id,
                    UnitArea = item.UnitArea,
                    UnitName = item.UnitName,
                    Address = item.Address,
                    Contact = item.Contact,
                    ContactTel = item.ContactTel,
                    ContactEmail = item.ContactEmail,
                    ContactFax = item.ContactFax,
                    OtherInfo = item.OtherInfo,
                    Score = item.Score,
                    SuitScore = item.SuitScore,
                    ServeScore = item.ServeScore,
                    TalkProScore = item.TalkProScore,
                    TimeScore = item.TimeScore,
                    FitScore = item.FitScore,
                    StrainScore = item.StrainScore,
                    LocalAndChineseScore = item.LocalAndChineseScore,
                    StaffType = item.StaffType,
                    Remark = item.Remark,
                    LastUpdateTime = item.LastUpdateTime,
                    LastUpdateUserName = lastUpdateUserName,
                    CreateTime = item.CreateTime,
                    CrateUserName = createUserName
                });
            }

            var exportResult = await GeneralMethod.ExportToExcel<LocalGuideDataExcel>(dataView, "导游地接资料");

            return Ok(JsonView(exportResult));
        }
        private class LocalGuideDataExcel
        {
            public int Id { get; set; }

            /// <summary>
            /// 区域
            /// </summary>
            public string UnitArea { get; set; }

            /// <summary>
            /// 公司名称
            /// </summary>
            public string UnitName { get; set; }

            /// <summary>
            /// 地址
            /// </summary>
            public string Address { get; set; }

            /// <summary>
            /// 联系人
            /// </summary>
            public string Contact { get; set; }

            /// <summary>
            /// 联系人手机号
            /// </summary>
            public string ContactTel { get; set; }

            /// <summary>
            /// 联系人邮箱
            /// </summary>
            public string ContactEmail { get; set; }

            /// <summary>
            /// 联系人传真
            /// </summary>
            public string ContactFax { get; set; }

            /// <summary>
            /// 其他信息
            /// </summary>
            public string OtherInfo { get; set; }

            /// <summary>
            /// 服务评分
            /// </summary>
            public int Score { get; set; }

            /// <summary>
            /// 着装得体
            /// A B C  选择
            /// </summary>
            public string SuitScore { get; set; }

            /// <summary>
            /// 服务意识强度
            /// A B C  选择
            /// </summary>
            public string ServeScore { get; set; }

            /// <summary>
            /// 讲解水平专业
            /// A B C  选择
            /// </summary>
            public string TalkProScore { get; set; }

            /// <summary>
            /// 时间概念强度
            /// A B C  选择
            /// </summary>
            public string TimeScore { get; set; }

            /// <summary>
            /// 配合能力强，服从安排
            /// A B C  选择
            /// </summary>
            public string FitScore { get; set; }

            /// <summary>
            /// 应变能力强
            /// A B C  选择
            /// </summary>
            public string StrainScore { get; set; }

            /// <summary>
            /// 当地语言和中文表达流畅
            /// A B C  选择
            /// </summary>
            public string LocalAndChineseScore { get; set; }

            /// <summary>
            /// 导游地接的类型：0公司1私人
            /// </summary>
            [SugarColumn(IsNullable = true, ColumnDataType = "int")]
            public int StaffType { get; set; }

            public string StaffTypeLabel { get{

                    if (StaffType == 1) return "私人";
                    else if (StaffType == 0) return "公司";
                    return "-";
                } }

            /// 最后更新者Id
            /// </summary>
            public int LastUpdateUserId { get; set; }

            /// <summary>
            /// 最后更新者
            /// </summary>
            public string? LastUpdateUserName { get; set; }

            /// <summary>
            ///  最后更新时间
            /// </summary>
            public DateTime LastUpdateTime { get; set; }

            /// <summary>
            /// 创建人
            /// </summary>
            public string CrateUserName { get; set; }

            /// <summary>
            /// 创建时间
            /// </summary>
            public DateTime CreateTime { get; set; } = DateTime.Now;

            /// <summary>
            /// 备注
            /// </summary>
            public string Remark { get; set; }

        }
        #endregion
    }
}