using AutoMapper;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Performance.DtoModels;
using Performance.DtoModels.AppSettings;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Infrastructure.Models;
using Performance.Repository;
using Performance.Services.ExtractExcelService;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;

namespace Performance.Services
{
    public class PersonService : IAutoInjection
    {
        private readonly ILogger<PersonService> logger;
        private readonly PerforPerdeptdicRepository perdeptdicRepository;
        private readonly PerforPeremployeeRepository peremployeeRepository;
        private readonly PerforPerallotRepository perallotRepository;
        private readonly PerforUserRepository perforUserRepository;
        private readonly PerforUserroleRepository perforUserroleRepository;
        private readonly PerforRoleRepository perforRoleRepository;
        private readonly PerforAgsecondallotRepository agsecondallotRepository;
        private readonly Application application;
        private readonly IHostingEnvironment evn;

        private readonly Dictionary<string, (string, string)> dict = new Dictionary<string, (string, string)>
        {
            { nameof(DeptdicResponse.OutDoctorAccounting), (UnitType.医生组.ToString(), "门诊") },
            { nameof(DeptdicResponse.OutNurseAccounting), (UnitType.护理组.ToString(), "门诊") },
            { nameof(DeptdicResponse.OutTechnicAccounting), (UnitType.医技组.ToString(), "门诊") },
            { nameof(DeptdicResponse.InpatDoctorAccounting), (UnitType.医生组.ToString(), "住院") },
            { nameof(DeptdicResponse.InpatNurseAccounting), (UnitType.护理组.ToString(), "住院") },
            { nameof(DeptdicResponse.InpatTechnicAccounting), (UnitType.医技组.ToString(), "住院") },
            { nameof(DeptdicResponse.LogisticsAccounting), (UnitType.行政后勤.ToString(), null) },
            { nameof(DeptdicResponse.SpecialAccounting), (UnitType.特殊核算组.ToString(), null) },
        };

        public PersonService(ILogger<PersonService> logger,
            PerforPerdeptdicRepository perdeptdicRepository,
            PerforPeremployeeRepository peremployeeRepository,
            PerforPerallotRepository perallotRepository,
            PerforUserRepository perforUserRepository,
            PerforUserroleRepository perforUserroleRepository,
            PerforRoleRepository perforRoleRepository,
            PerforAgsecondallotRepository agsecondallotRepository,
            IOptions<Application> application,
            IHostingEnvironment evn
            )
        {
            this.logger = logger;
            this.perdeptdicRepository = perdeptdicRepository;
            this.peremployeeRepository = peremployeeRepository;
            this.perallotRepository = perallotRepository;
            this.perforUserRepository = perforUserRepository;
            this.perforUserroleRepository = perforUserroleRepository;
            this.perforRoleRepository = perforRoleRepository;
            this.agsecondallotRepository = agsecondallotRepository;
            this.application = application.Value;
            this.evn = evn;
        }

        /// <summary>
        /// 创建绩效时，批量创建员工信息
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="allotId"></param>
        /// <param name="prevAllotId"></param>
        public void CreateAllotPersons(int hospitalId, int allotId, int prevAllotId = -1)
        {
            var allot = perallotRepository.GetEntity(t => t.ID == allotId);
            if (allot == null) throw new PerformanceException("绩效信息错误！");

            var isExist = (peremployeeRepository.GetEntities(t => t.HospitalId == hospitalId && t.AllotId == allotId)?.Count ?? 0) > 0;
            if (isExist) return;

            List<per_employee> persons = new List<per_employee>();
            if (!new int[] { -1, 0 }.Contains(prevAllotId))
            {
                persons = peremployeeRepository.GetEntities(t => t.HospitalId == hospitalId && t.AllotId == prevAllotId);
                if (persons == null || !persons.Any())
                    persons = peremployeeRepository.GetEntities(t => t.HospitalId == hospitalId && t.AllotId == -1);
            }
            else
                persons = peremployeeRepository.GetEntities(t => t.HospitalId == hospitalId && t.AllotId == -1);

            if (persons == null || !persons.Any()) return;

            int day = DateTime.DaysInMonth(allot.Year, allot.Month);

            var data = persons.Select(t =>
            {
                var entity = new per_employee
                {
                    HospitalId = t.HospitalId,
                    AllotId = allotId,
                    AccountingUnit = t.AccountingUnit,
                    Department = t.Department,
                    DoctorName = t.DoctorName,
                    JobCategory = t.JobCategory,
                    Duty = t.Duty,
                    JobTitle = t.JobTitle,
                    UnitType = t.UnitType,
                    AttendanceDay = day,
                    Attendance = 1,
                    PermanentStaff = t.PermanentStaff,
                    EfficiencyNumber = t.EfficiencyNumber,
                    WorkTime = t.WorkTime,
                    BirthDate = t.BirthDate,
                    Age = t.Age,
                    ReservedRatio = t.ReservedRatio,
                    BankCard = t.BankCard,
                    Remark = t.Remark,
                    CreateTime = DateTime.Now,
                };
                if (!string.IsNullOrEmpty(t.PersonnelNumber) && !string.IsNullOrEmpty(t.JobNumber))
                {
                    entity.PersonnelNumber = t.PersonnelNumber;
                    entity.JobNumber = t.JobNumber;
                }
                else
                {
                    string number = !string.IsNullOrEmpty(t.PersonnelNumber) ? t.PersonnelNumber : t.JobNumber;
                    entity.PersonnelNumber = number;
                    entity.JobNumber = number;
                }
                return entity;
            }).ToList();
            SaveAllotPersons(data);
        }

        private void SaveAllotPersons(List<per_employee> employees)
        {
            try
            {
                if (employees.Count > 1000)
                {
                    int rows = 500;
                    for (int i = 0; i < Math.Ceiling((double)employees.Count / rows); i++)
                    {
                        peremployeeRepository.AddRange(employees.Skip(rows * i).Take(rows).ToArray());
                    }
                }
                else
                    peremployeeRepository.AddRange(employees.ToArray());
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
            }
        }

        /// <summary>
        /// 获取所有员工记录
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public List<per_employee> GetPerEmployee(int allotId)
        {
            return peremployeeRepository.GetEntities(t => t.AllotId == allotId)?.OrderBy(t => t.Id).ToList();
        }

        /// <summary>
        /// 获取所有员工记录
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public List<per_employee> GetPersons(int allotId, int userId)
        {
            var (dept, unittype) = GetDeptByUser(userId);
            Expression<Func<per_employee, bool>> exp = t => t.AllotId == allotId;
            if (!string.IsNullOrEmpty(dept) && unittype.Any())
            {
                exp = exp.And(t => t.AccountingUnit == dept && unittype.Contains(t.UnitType));
            }

            var list = peremployeeRepository.GetEntities(exp);
            if (list != null && list.Any())
                return list.OrderBy(w => w.IsVerify).ThenBy(t => t.Id).ToList();

            return new List<per_employee>();
        }

        /// <summary>
        /// 获取所有员工记录分页
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public PageList<per_employee> GetPersons(int allotId, int userId, PersonParamsRequest request)
        {
            var (dept, unittype) = GetDeptByUser(userId);
            Expression<Func<per_employee, bool>> exp = t => t.AllotId == allotId;
            if (!string.IsNullOrEmpty(dept) && unittype.Any())
            {
                exp = exp.And(t => t.AccountingUnit == dept && unittype.Contains(t.UnitType));
            }

            if (request != null && !string.IsNullOrEmpty(request.SearchQuery))
            {
                exp = exp.And(t => true && (t.AccountingUnit.Contains(request.SearchQuery) || t.DoctorName.Contains(request.SearchQuery) || t.Department.Contains(request.SearchQuery)));
            }

            var result = new List<per_employee>();

            return peremployeeRepository.GetEntitiesForPaging(request.PageNumber, request.PageSize, exp);
        }

        /// <summary>
        /// 新增员工信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public per_employee CreatePerson(PerEmployeeResponse request)
        {
            var employee = peremployeeRepository.GetEntity(t => t.AllotId == request.AllotId && t.PersonnelNumber == request.PersonnelNumber
            && t.DoctorName == request.DoctorName && t.Department == request.Department);
            if (employee != null)
                throw new PerformanceException($"员工工号为{request.PersonnelNumber}的数据已存在！");

            var unittype = EnumHelper.GetItems<UnitType>().Select(t => t.Description).ToList();
            unittype.AddRange(EnumHelper.GetItems<AccountUnitType>().Select(t => t.Description));

            if (!unittype.Contains(request.UnitType))
                throw new PerformanceException($"人员类别不符合规范！");

            var entity = Mapper.Map<per_employee>(request);
            var allot = perallotRepository.GetEntity(t => t.ID == request.AllotId);
            int day = DateTime.DaysInMonth(allot.Year, allot.Month);
            entity.Attendance = request.AttendanceDay / day;
            entity.CreateTime = DateTime.Now;
            entity.IsVerify = 0;

            //CheckAccountingDept(request.HospitalId.Value, request.AccountingUnit, request.Department);
            peremployeeRepository.Add(entity);
            return entity;
        }

        /// <summary>
        /// 修改员工信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool UpdatePerson(PerEmployeeResponse request)
        {
            var employee = peremployeeRepository.GetEntity(t => t.AllotId == request.AllotId && t.PersonnelNumber == request.PersonnelNumber
            && t.DoctorName == request.DoctorName && t.Department == request.Department);
            if (employee != null && employee.Id != request.Id)
                throw new PerformanceException($"员工工号为{request.PersonnelNumber}的数据已存在！");

            var unittype = EnumHelper.GetItems<UnitType>().Select(t => t.Description).ToList();
            unittype.AddRange(EnumHelper.GetItems<AccountUnitType>().Select(t => t.Description));

            if (!unittype.Contains(request.UnitType))
                throw new PerformanceException($"人员类别不符合规范！");

            if (employee == null)
                employee = peremployeeRepository.GetEntity(t => t.Id == request.Id) ?? throw new PerformanceException("人员信息无效！");

            //Mapper.Map(request, employee, typeof(per_employee), typeof(per_employee));

            employee.AccountingUnit = request.AccountingUnit;
            employee.Department = request.Department;
            employee.DoctorName = request.DoctorName;
            employee.PersonnelNumber = request.PersonnelNumber;
            //employee.JobNumber = request.JobNumber; // his
            employee.JobCategory = request.JobCategory;
            employee.Duty = request.Duty;
            employee.JobTitle = request.JobTitle;
            employee.UnitType = request.UnitType;
            employee.WorkTime = request.WorkTime == null ? null : ConvertHelper.To<DateTime?>(request.WorkTime);
            employee.BirthDate = request.BirthDate == null ? null : ConvertHelper.To<DateTime?>(request.BirthDate);
            employee.Age = request.Age;
            employee.BankCard = request.BankCard;
            employee.Remark = request.Remark;
            employee.EfficiencyNumber = request.EfficiencyNumber;
            employee.PermanentStaff = request.PermanentStaff;
            employee.AttendanceDay = request.AttendanceDay;
            employee.ReservedRatio = request.ReservedRatio;

            // 出勤率
            var allot = perallotRepository.GetEntity(t => t.ID == request.AllotId);
            var day = (decimal)DateTime.DaysInMonth(allot.Year, allot.Month);
            employee.Attendance = Math.Round((request.AttendanceDay ?? 0) / day, 4);

            //CheckAccountingDept(request.HospitalId.Value, request.AccountingUnit, request.Department);
            return peremployeeRepository.Update(employee);
        }

        /// <summary>
        /// 删除员工
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public bool DeletePerson(int employeeId)
        {
            var employee = peremployeeRepository.GetEntity(t => t.Id == employeeId);
            if (employee == null)
                throw new PerformanceException($"员工信息错误！");

            return peremployeeRepository.Remove(employee);
        }

        /// <summary>
        /// 检查核算单元是否已存在
        /// </summary>
        /// <param name="hosapitalId">医院Id</param>
        /// <param name="accountingUnit">核算单元</param>
        /// <param name="department">科室</param>
        private void CheckAccountingDept(int hosapitalId, string accountingUnit, string department)
        {
            var accountDept = perdeptdicRepository.GetEntity(t => t.HospitalId == hosapitalId && t.AccountingUnit == accountingUnit && t.Department == department);
            if (accountDept == null)
            {
                var entity = new per_dept_dic
                {
                    AccountingUnit = accountingUnit,
                    Department = department,
                    HospitalId = hosapitalId,
                    Source = "人事科增加人员记录时补充"
                };
                perdeptdicRepository.Add(entity);
            }
        }

        /// <summary>
        /// 获取给医院所有的
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public IEnumerable<DeptdicResponse> GetDepartments(int hospitalId)
        {
            var depts = perdeptdicRepository.GetEntities(t => t.HospitalId == hospitalId);
            if (depts == null || !depts.Any()) return null;

            var result = depts.GroupBy(t => new { t.HISDeptName, t.Department }).Select(t => new DeptdicResponse
            {
                HospitalId = hospitalId,
                HISDeptName = t.Key.HISDeptName,
                Department = t.Key.Department,
                OutDoctorAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.医生组.ToString() && group.Source == "门诊")),
                OutNurseAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.护理组.ToString() && group.Source == "门诊")),
                OutTechnicAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.医技组.ToString() && group.Source == "门诊")),
                InpatDoctorAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.医生组.ToString() && group.Source == "住院")),
                InpatNurseAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.护理组.ToString() && group.Source == "住院")),
                InpatTechnicAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.医技组.ToString() && group.Source == "住院")),
                LogisticsAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.行政后勤.ToString())),
                SpecialAccounting = GetDeptdic(t.FirstOrDefault(group => group.Department == t.Key.Department && group.HISDeptName == t.Key.HISDeptName && group.UnitType == UnitType.特殊核算组.ToString())),
                CreateTime = t.Max(group => group.CreateTime),
                IsVerify = t.Min(w => w.IsVerify) ?? 1,
            });

            return result.OrderBy(w => w.IsVerify).ThenByDescending(t => t.CreateTime).ThenBy(t => t.Department);
        }

        private (string dept, string[] unittype) GetDeptByUser(int userId)
        {
            var user = perforUserRepository.GetEntity(t => t.ID == userId && t.IsDelete == 1);
            if (user == null) throw new PerformanceException("用户信息错误");

            var userrole = perforUserroleRepository.GetEntity(t => t.UserID == user.ID);
            var role = perforRoleRepository.GetEntity(t => t.ID == userrole.RoleID);

            if (role == null) return ("", new string[] { });

            Dictionary<int, string[]> dict = new Dictionary<int, string[]>
            {
                {  application.DirectorRole, new string[]{ UnitType.医生组.ToString(), UnitType.其他医生组.ToString(), UnitType.医技组.ToString(), UnitType.其他医技组.ToString() } },
                {  application.NurseRole, new string[]{ UnitType.护理组.ToString() } },
                {  application.SpecialRole, new string[]{ UnitType.特殊核算组.ToString() } },
                {  application.OfficeRole, new string[]{ UnitType.行政后勤.ToString(), "行政工勤" } },
            };

            if (dict.Keys.Contains(role.Type.Value))
            {
                return (user.Department, dict[role.Type.Value]);
            }

            return ("", new string[] { });
        }

        private Deptdic GetDeptdic(per_dept_dic dic)
        {
            if (dic == null) return new Deptdic() { IsVerify = 1 };

            return new Deptdic
            {
                Id = dic.Id,
                AccountingUnit = dic.AccountingUnit,
                IsVerify = dic.IsVerify ?? 1,
                VerifyMessage = dic.VerifyMessage,
            };
        }

        /// <summary>
        /// 创建科室核算信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public per_dept_dic CreateDeptDic(per_dept_dic request)
        {
            var deptdic = perdeptdicRepository.GetEntity(t => t.HospitalId == request.HospitalId && t.AccountingUnit == request.AccountingUnit
            && t.Department == request.Department && t.HISDeptName == request.HISDeptName && t.Source == request.Source && t.UnitType == request.UnitType);

            if (deptdic != null)
                throw new PerformanceException($"{request.Department}数据重复！");

            request.CreateTime = DateTime.Now;
            perdeptdicRepository.Add(request);

            return request;
        }

        /// <summary>
        /// 更新科室核算信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool UpdateDeptDic(per_dept_dic request)
        {
            var deptdic = perdeptdicRepository.GetEntity(t => t.HospitalId == request.HospitalId && t.AccountingUnit == request.AccountingUnit
            && t.Department == request.Department && t.Source == request.Source && t.UnitType == request.UnitType);

            if (deptdic != null && deptdic.Id != request.Id)
                throw new PerformanceException($"{request.Department}数据重复！");

            Mapper.Map(request, deptdic, typeof(per_dept_dic), typeof(per_dept_dic));
            return perdeptdicRepository.Add(deptdic);
        }

        /// <summary>
        /// 更新科室核算信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool UpdateDeptDic(DeptdicResponse request)
        {
            try
            {
                foreach (var item in dict)
                {
                    var property = typeof(DeptdicResponse).GetProperty(item.Key);
                    if (property == null) continue;

                    var deptdic = property.GetValue(request);
                    if (deptdic is Deptdic)
                    {
                        var data = (Deptdic)deptdic;
                        if (string.IsNullOrEmpty(data.AccountingUnit) && data.Id == 0)
                            continue;

                        if (data.Id > 0)
                        {
                            var entity = perdeptdicRepository.GetEntity(t => t.Id == data.Id);
                            if (entity == null) continue;
                            entity.HISDeptName = request.HISDeptName;
                            entity.Department = request.Department;
                            entity.AccountingUnit = data.AccountingUnit;
                            perdeptdicRepository.Update(entity);
                        }
                        else
                        {
                            var entity = new per_dept_dic
                            {
                                HospitalId = request.HospitalId,
                                HISDeptName = request.HISDeptName,
                                Department = request.Department,
                                AccountingUnit = data.AccountingUnit,
                                UnitType = item.Value.Item1,
                                Source = item.Value.Item2,
                                CreateTime = DateTime.Now,
                            };
                            perdeptdicRepository.Add(entity);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
                return false;
            }

            return true;
        }

        /// <summary>
        /// 删除科室核算信息
        /// </summary>
        /// <param name="deptdicId"></param>
        /// <returns></returns>
        public bool DeleteDeptDic(DeptdicResponse request)
        {
            if (request == null)
                throw new PerformanceException("科室记录不存在!");

            var deptdics = perdeptdicRepository.GetEntities(t => t.HISDeptName == request.HISDeptName && t.Department == request.Department && t.HospitalId == request.HospitalId);
            if (deptdics == null || !deptdics.Any())
                throw new PerformanceException("科室记录不存在!");

            return perdeptdicRepository.RemoveRange(deptdics.ToArray());
        }

        /// <summary>
        /// 系统/标准科室字典
        /// </summary>
        /// <param name="hospitalId">医院Id</param>
        /// <param name="type">1系统科室 2标准科室 3核算单元</param>
        /// <returns></returns>
        public List<TitleValue> DeptDics(int hospitalId, int type)
        {
            var deptdics = perdeptdicRepository.GetEntities(t => t.HospitalId == hospitalId);
            if (deptdics == null || !deptdics.Any()) return new List<TitleValue>();

            var result = new List<string>();
            if (type == 1)
            {
                result = deptdics.Select(t => t.HISDeptName).Distinct().ToList();
            }
            else if (type == 2)
            {
                result = deptdics.Select(t => t.Department).Distinct().ToList();
            }
            else if (type == 3)
            {
                result = deptdics.Where(t => !new string[] { UnitType.行政后勤.ToString(), UnitType.特殊核算组.ToString() }.Contains(t.UnitType)).Select(t => t.AccountingUnit).Distinct().ToList();
            }
            else if (type == 4)
            {
                result = deptdics.Where(t => t.UnitType == UnitType.行政后勤.ToString()).Select(t => t.AccountingUnit).Distinct().ToList();
            }
            else if (type == 5)
            {
                result = deptdics.Where(t => t.UnitType == UnitType.特殊核算组.ToString()).Select(t => t.AccountingUnit).Distinct().ToList();
            }
            return result.Select(t => new TitleValue { Title = t, Value = t }).ToList();
        }

        /// <summary>
        /// 科室工作量数据详情
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public object DeptWorkloadDetail(WorkDetailRequest request, int userId)
        {
            string[] unitTypes = GetUnitType(userId);
            if (unitTypes == null || !unitTypes.Any()) return new string[] { };

            var allot = perallotRepository.GetEntity(w => w.ID == request.AllotId);
            if (allot == null)
                return null;

            var data = perallotRepository.QueryWorkloadData(request.AllotId, request.AccountingUnit, unitTypes, allot.HospitalId);
            if (data != null && data.Any())
            {
                return data.GroupBy(t => new { t.Department, t.DoctorName, t.PersonnelNumber, t.Category })
                    .Select(t => new DeptIncomeResponse
                    {
                        Department = t.Key.Department,
                        DoctorName = t.Key.DoctorName,
                        PersonnelNumber = t.Key.PersonnelNumber,
                        Category = t.Key.Category,
                        Fee = t.Sum(group => group.Fee ?? 0)
                    }).OrderBy(t => t.PersonnelNumber).ThenBy(t => t.Category);
            }

            return new string[] { };
        }

        /// <summary>
        /// 科室工作量数据详情
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public object DeptIncomeDetail(WorkDetailRequest request, int userId)
        {
            string[] unitTypes = GetUnitType(userId);
            if (unitTypes == null || !unitTypes.Any()) return new string[] { };

            var allot = perallotRepository.GetEntity(w => w.ID == request.AllotId);
            if (allot == null)
                return null;
            var sources = new[] { "门诊", "住院" };
            if (!sources.Contains(request.Source))
                throw new PerformanceException($"数据来源错误，只支持：{string.Join(";", sources)}");

            var data = perallotRepository.QueryIncomeData(request.AllotId, request.Source, request.AccountingUnit, unitTypes, allot.HospitalId);
            if (data != null && data.Any())
            {
                return data.GroupBy(t => new { t.Department, t.DoctorName, t.PersonnelNumber, t.Category })
                    .Select(t => new DeptIncomeResponse
                    {
                        Department = t.Key.Department,
                        DoctorName = t.Key.DoctorName,
                        PersonnelNumber = t.Key.PersonnelNumber,
                        Category = t.Key.Category,
                        Fee = t.Sum(group => group.Fee ?? 0)
                    }).OrderBy(t => t.PersonnelNumber).ThenBy(t => t.Category);
            }

            return new string[] { };
        }

        private string[] GetUnitType(int userId)
        {
            Dictionary<int, string[]> dict = new Dictionary<int, string[]>
            {
                {  application.DirectorRole, new string []{ UnitType.医生组.ToString(), UnitType.其他医生组.ToString(), UnitType.其他医技组.ToString(), UnitType.医技组.ToString() } },
                {  application.NurseRole, new string []{ UnitType.护理组.ToString(), UnitType.其他护理组.ToString() } },
                {  application.SpecialRole, new string []{ UnitType.特殊核算组.ToString() } },
                {  application.OfficeRole, new string []{ UnitType.行政后勤.ToString() } },
            };

            var user = perforUserRepository.GetEntity(t => t.ID == userId);
            if (user == null)
                throw new NotImplementedException("人员ID无效");
            var userrole = perforUserroleRepository.GetEntity(t => t.UserID == userId);
            var role = perforRoleRepository.GetEntity(t => t.ID == userrole.RoleID);

            if (!role.Type.HasValue || !dict.ContainsKey(role.Type.Value))
                return new string[] { };

            return dict[role.Type.Value];
        }

        public HandsonTable GetBatchPersonStructrue(int hospitalId)
        {
            var result = new HandsonTable((int)SheetType.Unidentifiable, Person.Select(t => t.Item2).ToArray(), Person.Select(t => new collect_permission
            {
                HeadName = t.Item2,
                Visible = 1
            }).ToList());

            var deptdics = perdeptdicRepository.GetEntities(t => t.HospitalId == hospitalId);
            var ss = deptdics?.Where(t => !new string[] { UnitType.专家组.ToString() }.Contains(t.UnitType));
            if (result.Columns != null && result.Columns.Any())
            {
                foreach (var column in result.Columns)
                {

                    if (column.Data == "人员类别")
                    {
                        column.Type = "autocomplete";
                        column.Source = EnumHelper.GetItems<UnitType>().Select(w => w.Description.Replace("行政后勤", "行政工勤")).ToArray();
                        column.Strict = true;
                    }
                    else if (new[] { "出勤天数", "预留比例" }.Contains(column.Data))
                    {
                        column.Type = "numeric";
                        column.NumericFormat = new NumericFormat { Pattern = "0,00" };
                    }
                }
            }
            return result;
        }

        public HandsonTable GetDepartmentHands(int hospitalId)
        {
            HandsonTable handson = new HandsonTable((int)SheetType.Unidentifiable, DeptDic.Select(c => c.Value).ToArray(), DeptDic.Select(t => new collect_permission
            {
                HeadName = t.Value,
                Visible = 1,
                Readnoly = 0
            }).ToList());
            #region 科室下拉
            //if (handson.Columns != null && handson.Columns.Any())
            //{
            //    foreach (var column in handson.Columns)
            //    {
            //        if (column.Data == "标准科室")
            //        {
            //            column.Type = "autocomplete";
            //            column.Source = DeptDics(hospitalId, 2).Select(c => c.Value).ToArray();
            //            column.Strict = true;
            //        }
            //        else if (column.Data == "系统科室")
            //        {
            //            column.Type = "autocomplete";
            //            column.Source = DeptDics(hospitalId, 1).Select(c => c.Value).ToArray();
            //            column.Strict = true;
            //        }
            //        else if (new[] { "住院·核算单元医生组", "住院·核算单元护理组", "住院·核算单元医技组", "门诊·核算单元医生组", "门诊·核算单元护理组", "门诊·核算单元医技组" }.Contains(column.Data))
            //        {
            //            column.Type = "autocomplete";
            //            column.Source = DeptDics(hospitalId, 3).Select(c => c.Value).ToArray();
            //            column.Strict = true;
            //        }
            //        else if (column.Data == "行政后勤")
            //        {
            //            column.Type = "autocomplete";
            //            column.Source = DeptDics(hospitalId, 4).Select(c => c.Value).ToArray();
            //            column.Strict = true;
            //        }
            //        else if (column.Data == "特殊核算组")
            //        {
            //            column.Type = "autocomplete";
            //            column.Source = DeptDics(hospitalId, 5).Select(c => c.Value).ToArray();
            //            column.Strict = true;
            //        }
            //    }
            //}
            #endregion
            return handson;
        }

        public bool BathSavePerson(int AllotId, int HospitalId, SaveCollectData request)
        {
            var dict = new Dictionary<string, string>();
            Person.ForEach(t => dict.Add(t.Item1, t.Item2));
            var dicData = CreateDataRow(request, dict);
            List<per_employee> employees = new List<per_employee>();
            var persons = peremployeeRepository.GetEntities(t => t.HospitalId == HospitalId && t.AllotId == AllotId);
            var delPersonsNum =new List<string>();
            foreach (var item in dicData)
            {
                if (!string.IsNullOrEmpty(item["AttendanceDay"]) && !string.IsNullOrEmpty(item["ReservedRatio"]))
                {
                    decimal attendanceDay = ConvertHelper.To<decimal>(item["AttendanceDay"]), reservedRatio = ConvertHelper.To<decimal>(item["ReservedRatio"]);
                    if ((!Regex.IsMatch(item["AttendanceDay"], "^(?:[0-2]*[0-9]?|30|31)$")) || (!Regex.IsMatch(item["ReservedRatio"], @"^(0.\d{1,2}|0|1)?$")))
                        return false;
                }

                var json = JsonHelper.Serialize(item);
                var data = JsonHelper.Deserialize<per_employee>(json);
                if (persons != null)
                    if (persons.Any(t => t.PersonnelNumber?.Trim() == data.PersonnelNumber?.Trim())) delPersonsNum.Add(data.PersonnelNumber);

                var any = employees.Any(w => w.Department?.Trim() == data.Department?.Trim() && w.DoctorName?.Trim() == data.DoctorName?.Trim());

                if (!string.IsNullOrEmpty(data.Department?.Trim()) && !string.IsNullOrEmpty(data.AccountingUnit?.Trim()) && !string.IsNullOrEmpty(data.DoctorName?.Trim()) && !any)
                {
                    data.HospitalId = HospitalId;
                    data.AllotId = AllotId;
                    data.DoctorName = data.DoctorName?.Trim();
                    data.PersonnelNumber = data.PersonnelNumber?.Trim();
                    data.CreateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd hh:mm:dd"));
                    data.IsVerify = 0;
                    employees.Add(data);
                }
            }
            var delPerson = persons.Where(t => delPersonsNum.Contains(t.PersonnelNumber)).ToArray();
            peremployeeRepository.RemoveRange(delPerson);
            if (employees.Any())
                peremployeeRepository.AddRange(employees.ToArray());

            return true;
        }

        public void SaveDeptDicHands(int HospitalId, SaveCollectData request)
        {
            var dicData = CreateDataRow(request, DeptDic);
            var deptDic = perdeptdicRepository.GetEntities(t => t.HospitalId == HospitalId);
            var depts =deptDic?.Select(w => new { w.Department, w.HISDeptName }).Distinct();
            List<per_dept_dic> deptDics = new List<per_dept_dic>();
            var delDepartment = new List<string>();
            foreach (var dic in dicData)
            {
                var json = JsonHelper.Serialize(dic);
                var data = JsonHelper.Deserialize<DeptdicHands>(json);

                if (depts != null)
                    if (depts.Any(t => t.HISDeptName?.Trim() == data.HISDeptName?.Trim())) delDepartment.Add(data.HISDeptName);
                var any = deptDics.Any(w => w.HISDeptName?.Trim() == data.HISDeptName?.Trim());

                if (!string.IsNullOrEmpty(data.HISDeptName?.Trim()) && !any)
                {
                    DeptDicList(HospitalId, deptDics, data);
                }

            }
            var delDept = deptDic.Where(t => delDepartment.Contains(t.HISDeptName)).ToArray();
            perdeptdicRepository.RemoveRange(delDept);
            if (deptDics.Any())
                perdeptdicRepository.AddRange(deptDics.ToArray());
        }

        private void DeptDicList(int HospitalId, List<per_dept_dic> deptDics, DeptdicHands data)
        {
            var nowTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
            #region 住院

            if (!string.IsNullOrEmpty(data.InpatDoctorAccounting) || !string.IsNullOrEmpty(data.InpatNurseAccounting) || !string.IsNullOrEmpty(data.InpatTechnicAccounting))
            {

                if (!string.IsNullOrEmpty(data.InpatDoctorAccounting))
                {
                    per_dept_dic deptDic = new per_dept_dic() { Source = "住院", HospitalId = HospitalId, Department = data.Department?.Trim(), HISDeptName = data.HISDeptName?.Trim(), CreateTime = nowTime };
                    deptDic.AccountingUnit = data.InpatDoctorAccounting?.Trim();
                    deptDic.UnitType = "医生组";
                    deptDics.Add(deptDic);
                }
                if (!string.IsNullOrEmpty(data.InpatNurseAccounting))
                {
                    per_dept_dic deptDic = new per_dept_dic() { Source = "住院", HospitalId = HospitalId, Department = data.Department?.Trim(), HISDeptName = data.HISDeptName?.Trim(), CreateTime = nowTime };
                    deptDic.AccountingUnit = data.InpatNurseAccounting?.Trim();
                    deptDic.UnitType = "护理组";
                    deptDics.Add(deptDic);
                }
                if (!string.IsNullOrEmpty(data.InpatTechnicAccounting))
                {
                    per_dept_dic deptDic = new per_dept_dic() { Source = "住院", HospitalId = HospitalId, Department = data.Department?.Trim(), HISDeptName = data.HISDeptName?.Trim(), CreateTime = nowTime };
                    deptDic.AccountingUnit = data.InpatTechnicAccounting?.Trim();
                    deptDic.UnitType = "医技组";
                    deptDics.Add(deptDic);
                }
            }

            #endregion

            #region 门诊

            if (!string.IsNullOrEmpty(data.OutDoctorAccounting) || !string.IsNullOrEmpty(data.OutNurseAccounting) || !string.IsNullOrEmpty(data.OutTechnicAccounting))
            {

                if (!string.IsNullOrEmpty(data.OutDoctorAccounting))
                {
                    per_dept_dic deptDic = new per_dept_dic() { Source = "门诊", HospitalId = HospitalId, Department = data.Department?.Trim(), HISDeptName = data.HISDeptName?.Trim(), CreateTime = nowTime };
                    deptDic.AccountingUnit = data.OutDoctorAccounting?.Trim();
                    deptDic.UnitType = "医生组";
                    deptDics.Add(deptDic);
                }
                if (!string.IsNullOrEmpty(data.OutNurseAccounting))
                {
                    per_dept_dic deptDic = new per_dept_dic() { Source = "门诊", HospitalId = HospitalId, Department = data.Department?.Trim(), HISDeptName = data.HISDeptName?.Trim(), CreateTime = nowTime };
                    deptDic.AccountingUnit = data.OutNurseAccounting?.Trim();
                    deptDic.UnitType = "护理组";
                    deptDics.Add(deptDic);
                }
                if (!string.IsNullOrEmpty(data.OutTechnicAccounting))
                {
                    per_dept_dic deptDic = new per_dept_dic() { Source = "门诊", HospitalId = HospitalId, Department = data.Department?.Trim(), HISDeptName = data.HISDeptName?.Trim(), CreateTime = nowTime };
                    deptDic.AccountingUnit = data.OutTechnicAccounting?.Trim();
                    deptDic.UnitType = "医技组";
                    deptDics.Add(deptDic);
                }
            }

            #endregion


            if (!string.IsNullOrEmpty(data.LogisticsAccounting))
            {
                per_dept_dic deptDic = new per_dept_dic()
                {
                    HospitalId = HospitalId,
                    Department = data.Department?.Trim(),
                    HISDeptName = data.HISDeptName?.Trim(),
                    CreateTime = nowTime,
                    AccountingUnit = data.LogisticsAccounting?.Trim(),
                    UnitType = "行政后勤"
                };
                deptDics.Add(deptDic);
            }

            if (!string.IsNullOrEmpty(data.SpecialAccounting))
            {
                per_dept_dic deptDic = new per_dept_dic()
                {
                    HospitalId = HospitalId,
                    Department = data.Department?.Trim(),
                    HISDeptName = data.HISDeptName?.Trim(),
                    CreateTime = nowTime,
                    AccountingUnit = data.LogisticsAccounting?.Trim(),
                    UnitType = "特殊核算组"
                };
                deptDics.Add(deptDic);
            }
        }

        private List<Dictionary<string, string>> CreateDataRow(SaveCollectData request, Dictionary<string, string> config)
        {
            List<Dictionary<string, string>> allData = new List<Dictionary<string, string>>();

            for (int r = 0; r < request.Data.Length; r++)
            {
                // 创建固定数据列
                Dictionary<string, string> baseData = CreateBaseData(request, config, r);
                allData.Add(baseData);

            }
            return allData;
        }

        private Dictionary<string, string> CreateBaseData(SaveCollectData request, Dictionary<string, string> config, int rownumber)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            for (int c = 0; c < request.ColHeaders.Length; c++)
            {
                var header = request.ColHeaders[c];
                var first = config.FirstOrDefault(w => w.Value == header);
                if (!default(KeyValuePair<string, string>).Equals(first)
                    && !result.ContainsKey(header)
                    && request.Data[rownumber].Length > c)
                {
                    result.Add(first.Key, request.Data[rownumber][c]);
                }
            }

            return result;
        }

        public static List<(string, string, Func<per_employee, object>)> Person { get; } = new List<(string, string, Func<per_employee, object>)>
        {
            (nameof(per_employee.AccountingUnit), "核算单元", t => t.AccountingUnit),
            (nameof(per_employee.Department), "科室名称", t => t.Department),
            (nameof(per_employee.DoctorName), "姓名" ,t => t.DoctorName),
            (nameof(per_employee.PersonnelNumber), "员工工号", t => t.PersonnelNumber),
            (nameof(per_employee.JobCategory), "正式/临聘", t => t.JobCategory),
            (nameof(per_employee.Duty), "职务", t => t.Duty),
            (nameof(per_employee.JobTitle), "职称", t => t.JobTitle),
            (nameof(per_employee.UnitType), "人员类别", t => t.UnitType),
            (nameof(per_employee.AttendanceDay), "出勤天数", t => t.AttendanceDay),
            (nameof(per_employee.ReservedRatio), "预留比例", t => t.ReservedRatio),
            (nameof(per_employee.BankCard), "银行卡号", t => t.BankCard),
            (nameof(per_employee.Remark), "备注", t => t.Remark),
        };

        private static Dictionary<string, string> DeptDic { get; } = new Dictionary<string, string>
        {
            { nameof(DeptdicResponse.Department), "标准科室" },
            { nameof(DeptdicResponse.HISDeptName), "系统科室" },
            { nameof(DeptdicResponse.OutDoctorAccounting), "门诊·核算单元医生组" },
            { nameof(DeptdicResponse.OutNurseAccounting), "门诊·核算单元护理组" },
            { nameof(DeptdicResponse.OutTechnicAccounting), "门诊·核算单元医技组" },
            { nameof(DeptdicResponse.InpatDoctorAccounting), "住院·核算单元医生组" },
            { nameof(DeptdicResponse.InpatNurseAccounting), "住院·核算单元护理组" },
            { nameof(DeptdicResponse.InpatTechnicAccounting), "住院·核算单元医技组" },
            { nameof(DeptdicResponse.LogisticsAccounting), "行政工勤" },
            { nameof(DeptdicResponse.SpecialAccounting), "特殊核算组" }
        };

        public string GetPersonDictFile(int allotId, int userId)
        {
            var allot = perallotRepository.GetEntity(t => t.ID == allotId);
            if (allot == null)
                throw new PerformanceException("绩效记录不存在");

            var data = GetPersons(allotId, userId) ?? new List<per_employee>();

            var dpath = Path.Combine(evn.ContentRootPath, "Files", "Dictionary", $"{allot.HospitalId}");
            FileHelper.CreateDirectory(dpath);

            string filename = $"{allot.Year}{allot.Month.ToString().PadLeft(2, '0')}人员字典-{DateTime.Now.ToString("yyyyMMddhhmmss")}.xlsx";
            string filepath = Path.Combine(dpath, filename);
            FileStream stream = new FileStream(filepath, FileMode.Create);
            try
            {
                XSSFWorkbook workbook = new XSSFWorkbook();
                ExcelStyle excelStyle = new ExcelStyle(workbook);
                var style = excelStyle.SetBgkColorAndFormat(excelStyle.GetCellStyle());

                ISheet sheet = workbook.CreateSheet("人员字典");

                var header = sheet.CreateRow(0);
                int cellIndex = 0;
                foreach (var column in Person.Select(t => t.Item2))
                {
                    var cell = header.CreateCell(cellIndex);
                    cell.SetCellValue(column);
                    cell.CellStyle = style;
                    cellIndex++;
                }

                int startIndex = 1;
                foreach (var item in data)
                {
                    var row = sheet.CreateRow(startIndex);
                    cellIndex = 0;
                    foreach (var field in Person.Select(t => t.Item3))
                    {
                        var cell = row.CreateCell(cellIndex);
                        cell.SetCellOValue(field?.Invoke(item));
                        cell.CellStyle = style;
                        cellIndex++;
                    }
                    startIndex++;
                }

                workbook.Write(stream);
            }
            catch (Exception ex)
            {
                Console.WriteLine("写入异常" + ex);
            }
            finally
            {
                stream.Close();
            }
            return filepath;
        }
    }
}
