using AutoMapper;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Logging;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Performance.DtoModels;
using Performance.DtoModels.Request;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Infrastructure.Models;
using Performance.Repository;
using Performance.Services.AllotCompute;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

namespace Performance.Services
{
    public class EmployeeService : IAutoInjection
    {
        private readonly IMapper _mapper;
        private PerforImemployeeRepository perforImemployeeRepository;
        private PerforPersheetRepository perforPersheetRepository;
        private PerforImdataRepository perforImdataRepository;
        private PerforPerallotRepository perforPerallotRepository;
        private PerforImemployeeclinicRepository perforImemployeeclinicRepository;
        private PerforUserhospitalRepository perforUserhospitalRepository;
        private PerforPerallotRepository perallotRepository;
        private PerforPerapramountRepository perapramountRepository;
        private PerforImemployeelogisticsRepository perforImemployeelogisticsRepository;
        private PerforUserroleRepository userroleRepository;
        private PerforPeremployeeRepository peremployeeRepository;
        private PerforUserRepository userRepository;
        private readonly PerforRoleRepository _roleRepository;
        private readonly PerforPerapramounthideRepository _hideRepository;
        private readonly PerforExresultgatherRepository exresultgatherRepository;
        private readonly PerforImheaderRepository imheaderRepository;
        private readonly PerforPerdeptdicRepository perdeptdicRepository;
        private readonly PerforExmoduleRepository exmoduleRepository;
        private readonly PerforExitemRepository exitemRepository;
        private readonly PerforExspecialRepository exspecialRepository;
        private readonly PerforExresultRepository exresultRepository;
        private ILogger<EmployeeService> logger;

        public EmployeeService(
            IMapper mapper,
            PerforImemployeeRepository perforImemployeeRepository,
            PerforPersheetRepository perforPersheetRepository,
            PerforImdataRepository perforImdataRepository,
            PerforPerallotRepository perforPerallotRepository,
            PerforImemployeeclinicRepository perforImemployeeclinicRepository,
            PerforUserhospitalRepository perforUserhospitalRepository,
            PerforPerallotRepository perallotRepository,
            PerforPerapramountRepository perapramountRepository,
            PerforImemployeelogisticsRepository perforImemployeelogisticsRepository,
            PerforUserroleRepository userroleRepository,
            PerforPeremployeeRepository peremployeeRepository,
            PerforUserRepository userRepository,
            PerforRoleRepository roleRepository,
            PerforPerapramounthideRepository hideRepository,
            PerforExresultgatherRepository exresultgatherRepository,
            PerforImheaderRepository imheaderRepository,
            PerforPerdeptdicRepository perdeptdicRepository,
            PerforExmoduleRepository exmoduleRepository,
            PerforExitemRepository exitemRepository,
            PerforExspecialRepository exspecialRepository,
            PerforExresultRepository exresultRepository,
            ILogger<EmployeeService> logger)
        {
            _mapper = mapper;
            this.perforImemployeeRepository = perforImemployeeRepository;
            this.perforPersheetRepository = perforPersheetRepository;
            this.perforImdataRepository = perforImdataRepository;
            this.perforPerallotRepository = perforPerallotRepository;
            this.perforImemployeeclinicRepository = perforImemployeeclinicRepository;
            this.perforUserhospitalRepository = perforUserhospitalRepository;
            this.perallotRepository = perallotRepository;
            this.perapramountRepository = perapramountRepository;
            this.perforImemployeelogisticsRepository = perforImemployeelogisticsRepository;
            this.userroleRepository = userroleRepository;
            this.peremployeeRepository = peremployeeRepository;
            this.userRepository = userRepository;
            _roleRepository = roleRepository;
            _hideRepository = hideRepository;
            this.exresultgatherRepository = exresultgatherRepository;
            this.imheaderRepository = imheaderRepository;
            this.perdeptdicRepository = perdeptdicRepository;
            this.exmoduleRepository = exmoduleRepository;
            this.exitemRepository = exitemRepository;
            this.exspecialRepository = exspecialRepository;
            this.exresultRepository = exresultRepository;
            this.logger = logger;
        }

        #region 行政人员

        /// <summary>
        ///获取人员信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee GetEmployee(EmployeeRequest request)
        {
            var employee = perforImemployeeRepository.GetEntity(t => t.ID == request.ID);
            if (employee == null)
                throw new PerformanceException("该人员不存在");
            return employee;
        }

        /// <summary>
        ///获取人员列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<im_employee> GetEmployeeList(int? allotId, int userId)
        {
            if (allotId == null || allotId == 0)
            {
                var userHospital = perforUserhospitalRepository.GetEntity(t => t.UserID == userId);
                if (userHospital == null)
                    throw new PerformanceException("用户未绑定医院！");
                var allotList = perallotRepository.GetEntities(t => t.HospitalId == userHospital.HospitalID
                && new List<int> { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed }.Contains(t.States));
                if (allotList != null && allotList.Any())
                {
                    allotId = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First().ID;
                }
            }

            var employee = perforImemployeeRepository.GetEntities(t => t.AllotID == allotId);
            return employee?.OrderBy(t => t.RowNumber).ToList();
        }

        /// <summary>
        /// 新增人员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee Insert(EmployeeRequest request)
        {
            var allot = perforPerallotRepository.GetEntity(t => t.ID == request.AllotID);
            if (allot == null)
                throw new PerformanceException("绩效方案不存在");

            var sheet = perforPersheetRepository.GetEntity(t => t.AllotID == request.AllotID && t.SheetType == (int)SheetType.Employee);
            if (sheet == null)
            {
                sheet = new per_sheet { AllotID = allot.ID, SheetName = "行政人员名单", SheetType = (int)SheetType.Employee, Source = 1 };
                perforPersheetRepository.Add(sheet);
            }

            var employee = _mapper.Map<im_employee>(request);
            employee.WorkTime = ConvertHelper.To<DateTime?>(request.WorkTime);
            employee.SheetID = sheet.ID;
            perforImemployeeRepository.Add(employee);
            return employee;
        }

        /// <summary>
        /// 修改人员信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee Update(EmployeeRequest request)
        {
            var employee = perforImemployeeRepository.GetEntity(t => t.ID == request.ID);
            if (employee == null)
                throw new PerformanceException("该人员不存在");
            employee.AccountingUnit = request.AccountingUnit;
            employee.DoctorName = request.DoctorName;

            employee.AccountType = request.AccountType;
            employee.Department = request.Department;
            employee.FitPeople = request.FitPeople;
            employee.JobTitle = request.JobTitle;
            employee.PostCoefficient = request.PostCoefficient;
            employee.WorkTime = ConvertHelper.To<DateTime?>(request.WorkTime);
            employee.ScoreAverageRate = request.ScoreAverageRate;
            employee.Attendance = request.Attendance;
            employee.PeopleNumber = request.PeopleNumber;
            employee.Workload = request.Workload;
            employee.OtherPerfor = request.OtherPerfor;
            employee.Punishment = request.Punishment;
            employee.Adjust = request.Adjust;
            employee.Grant = request.Grant;
            //修改人员信息
            perforImemployeeRepository.Update(employee);
            return employee;
        }

        /// <summary>
        /// 删除人员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool Delete(EmployeeRequest request)
        {
            var employee = perforImemployeeRepository.GetEntity(t => t.ID == request.ID);
            if (null == employee)
                throw new PerformanceException("该人员不存在");

            return perforImemployeeRepository.Remove(employee);
        }

        #endregion 行政人员

        #region 临床人员

        /// <summary>
        ///获取临床人员列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<im_employee_clinic> GetEmployeeClinicList(int? allotId, int userId)
        {
            if (allotId == null || allotId == 0)
            {
                var userHospital = perforUserhospitalRepository.GetEntity(t => t.UserID == userId);
                if (userHospital == null)
                    throw new PerformanceException("用户未绑定医院！");
                var allotList = perallotRepository.GetEntities(t => t.HospitalId == userHospital.HospitalID
                && new List<int> { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed }.Contains(t.States));
                if (allotList != null && allotList.Any())
                {
                    allotId = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First().ID;
                }
            }

            var employee = perforImemployeeclinicRepository.GetEntities(t => t.AllotID == allotId);
            return employee?.OrderBy(t => t.RowNumber).ToList();
        }

        /// <summary>
        /// 新增临床人员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee_clinic InsertClinic(im_employee_clinic request)
        {
            var allot = perforPerallotRepository.GetEntity(t => t.ID == request.AllotID);
            if (allot == null)
                throw new PerformanceException("绩效方案不存在");

            var sheet = perforPersheetRepository.GetEntity(t => t.AllotID == request.AllotID && t.SheetType == (int)SheetType.ClinicEmployee);
            if (sheet == null)
            {
                sheet = new per_sheet { AllotID = allot.ID, SheetName = "临床人员名单", SheetType = (int)SheetType.Employee, Source = 1 };
                perforPersheetRepository.Add(sheet);
            }

            request.SheetID = sheet.ID;
            perforImemployeeclinicRepository.Add(request);
            return request;
        }

        /// <summary>
        /// 修改临床人员信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee_clinic UpdateClinic(im_employee_clinic request)
        {
            var employee = perforImemployeeclinicRepository.GetEntity(t => t.ID == request.ID);
            if (employee == null)
                throw new PerformanceException("该人员不存在");
            employee.AccountingUnit = request.AccountingUnit;
            employee.DoctorName = request.DoctorName;

            employee.UnitType = request.UnitType;
            employee.Department = request.Department;
            employee.JobTitle = request.JobTitle;
            employee.PostCoefficient = request.PostCoefficient;
            employee.ScoreAverageRate = request.ScoreAverageRate;
            employee.Efficiency = request.Efficiency;
            employee.Scale = request.Scale;
            employee.Management = request.Management;
            employee.Attendance = request.Attendance;
            employee.PeopleNumber = request.PeopleNumber;
            employee.Workload = request.Workload;
            employee.OtherPerfor = request.OtherPerfor;
            employee.Punishment = request.Punishment;
            employee.Adjust = request.Adjust;
            employee.Grant = request.Grant;
            //修改人员信息
            perforImemployeeclinicRepository.Update(employee);
            return employee;
        }

        /// <summary>
        /// 删除临床人员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool DeleteClinic(im_employee_clinic request)
        {
            var employee = perforImemployeeclinicRepository.GetEntity(t => t.ID == request.ID);
            if (null == employee)
                throw new PerformanceException("该人员不存在");

            return perforImemployeeclinicRepository.Remove(employee);
        }

        #endregion 临床人员

        #region 行政后勤

        /// <summary>
        ///获取临床人员列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<im_employee_logistics> GetEmployeeLogisticsList(int? allotId, int userId)
        {
            if (allotId == null || allotId == 0)
            {
                var userHospital = perforUserhospitalRepository.GetEntity(t => t.UserID == userId);
                if (userHospital == null)
                    throw new PerformanceException("用户未绑定医院！");
                var allotList = perallotRepository.GetEntities(t => t.HospitalId == userHospital.HospitalID
                && new List<int> { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed }.Contains(t.States));
                if (allotList != null && allotList.Any())
                {
                    allotId = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).First().ID;
                }
            }

            var employee = perforImemployeelogisticsRepository.GetEntities(t => t.AllotID == allotId);
            return employee?.OrderBy(t => t.RowNumber).ToList();
        }

        /// <summary>
        /// 新增临床人员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee_logistics InsertLogistics(im_employee_logistics request)
        {
            var allot = perforPerallotRepository.GetEntity(t => t.ID == request.AllotID);
            if (allot == null)
                throw new PerformanceException("绩效方案不存在");

            var sheet = perforPersheetRepository.GetEntity(t => t.AllotID == request.AllotID && t.SheetType == (int)SheetType.LogisticsEmployee);
            if (sheet == null)
            {
                sheet = new per_sheet { AllotID = allot.ID, SheetName = "临床人员名单", SheetType = (int)SheetType.Employee, Source = 1 };
                perforPersheetRepository.Add(sheet);
            }

            request.SheetID = sheet.ID;
            perforImemployeelogisticsRepository.Add(request);
            return request;
        }

        /// <summary>
        /// 修改临床人员信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public im_employee_logistics UpdateLogistics(im_employee_logistics request)
        {
            var employee = perforImemployeelogisticsRepository.GetEntity(t => t.ID == request.ID);
            if (employee == null)
                throw new PerformanceException("该人员不存在");
            employee.AccountingUnit = request.AccountingUnit;
            employee.DoctorName = request.DoctorName;

            employee.AccountType = request.AccountType;
            employee.Department = request.Department;
            employee.FitPeople = request.FitPeople;
            employee.JobTitle = request.JobTitle;
            employee.PostCoefficient = request.PostCoefficient;
            //employee.WorkTime = ConvertHelper.To<DateTime?>(request.WorkTime);
            employee.ScoreAverageRate = request.ScoreAverageRate;
            employee.Attendance = request.Attendance;
            //employee.PeopleNumber = request.PeopleNumber;
            //employee.Workload = request.Workload;
            employee.OtherPerfor = request.OtherPerfor;
            //employee.Punishment = request.Punishment;
            //employee.Adjust = request.Adjust;
            //employee.Grant = request.Grant;
            //修改人员信息
            perforImemployeelogisticsRepository.Update(employee);
            return employee;
        }

        /// <summary>
        /// 删除临床人员
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool DeleteLogistics(im_employee_clinic request)
        {
            var employee = perforImemployeelogisticsRepository.GetEntity(t => t.ID == request.ID);
            if (null == employee)
                throw new PerformanceException("该人员不存在");

            return perforImemployeelogisticsRepository.Remove(employee);
        }

        #endregion 行政后勤

        /// <summary>
        /// 人事科修改后提交状态
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public bool Audit(int allotId)
        {
            var allot = perforPerallotRepository.GetEntity(t => t.ID == allotId);
            if (allot == null || !new List<int> { (int)AllotStates.GenerateSucceed, (int)AllotStates.GenerateAccomplish }.Contains(allot.States))
                throw new PerformanceException("绩效信息错误");
            if ((int)AllotStates.Archive == allot.States)
                throw new PerformanceException("绩效已归档，暂不支持修改");

            allot.Generate = (int)AllotGenerate.PersonnelOffice;
            return perforPerallotRepository.Update(allot);
        }

        #region 医院其他绩效
        /// <summary>
        /// 获取所有医院其他绩效
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<per_apr_amount> GetAprList(int allotId, int userId)
        {
            var userrole = userroleRepository.GetEntity(t => t.UserID == userId);
            if (userrole == null) throw new PerformanceException("用户未绑定角色");

            var list = new List<per_apr_amount>();
            if (new int[] { 1, 2, 5, 6 }.Contains(userrole.RoleID))   //  绩效管理员、医院管理员、绩效核算办、院领导查看所有科室的数据
                list = perapramountRepository.GetEntities(t => t.AllotId == allotId && (t.Amount ?? 0) != 0);
            else
                list = perapramountRepository.GetEntities(t => t.AllotId == allotId && (t.Amount ?? 0) != 0 && t.CreateUser == userId);
            if (list != null && list.Any())
                list = list.OrderBy(w => w.IsVerify).ThenBy(t => t.DoctorName).ToList();

            return list;
        }
        /// <summary>
        /// 医院其他绩效审核详情
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="department"></param>
        /// <returns></returns>
        public List<view_per_apr_amount> GetAprList(int allotId, string department, int? status = null)
        {
            Expression<Func<per_apr_amount, bool>> predicate = w => w.AllotId == allotId && w.Amount.HasValue && w.Amount != 0;
            if (!string.IsNullOrEmpty(department))
                predicate = predicate.And(w => !string.IsNullOrEmpty(w.TypeInDepartment) && w.TypeInDepartment == department);

            if (status.HasValue)
                predicate = predicate.And(w => w.Status == status);

            var list = perapramountRepository.GetFullAmount(predicate);
            if (list != null && list.Any())
                list = list.OrderBy(t => t.DoctorName).ToList();

            return list;
        }
        /// <summary>
        /// 新增医院其他绩效
        /// </summary>
        /// <param name="request"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public bool InsertApr(per_apr_amount request, int userId)
        {
            if (request == null)
                return false;

            if (string.IsNullOrEmpty(request.PersonnelNumber))
                throw new PerformanceException("文件中存在“工号”为空的数据");
            if (string.IsNullOrEmpty(request.PerforType) && request.Amount != 0)
                throw new PerformanceException("文件中存在“绩效类型”为空的数据");

            var employee = peremployeeRepository.GetEntity(t => t.AllotId == request.AllotId && t.PersonnelNumber == request.PersonnelNumber);
            if (employee == null)
                throw new PerformanceException("工号在字典中不存在");

            request.TypeInDepartment = GetTypeInDepartment(userId);
            request.Status = 2;
            request.CreateDate = DateTime.Now;
            request.CreateUser = userId;
            return perapramountRepository.Add(request);
        }
        /// <summary>
        /// 修改医院其他绩效
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool UpdateApr(per_apr_amount request)
        {
            if (request == null)
                return false;


            if (string.IsNullOrEmpty(request.PersonnelNumber))
                throw new PerformanceException("文件中存在“工号”为空的数据");
            if (string.IsNullOrEmpty(request.PerforType) && request.Amount != 0)
                throw new PerformanceException("文件中存在“绩效类型”为空的数据");

            var data = perapramountRepository.GetEntity(t => t.Id == request.Id);
            if (data == null)
                throw new PerformanceException("修改数据无效");

            data.Status = 2;
            data.PersonnelNumber = request.PersonnelNumber;
            data.DoctorName = request.DoctorName;
            data.PerforType = request.PerforType;
            //data.AccountingUnit = request.AccountingUnit;
            data.Amount = request.Amount;

            return perapramountRepository.Update(data);
        }
        /// <summary>
        /// 删除医院其他绩效
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteApr(int id)
        {
            var data = perapramountRepository.GetEntity(t => t.Id == id);
            if (data != null)
                return perapramountRepository.Remove(data);

            return true;
        }

        /// <summary>
        /// 审核医院其他绩效
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool ConfirmAudit(int userid, AprAmountAuditRequest request)
        {
            if (request.IsPass == 1)
            {
                if (request?.Members == null || !request.Members.Any())
                    throw new PerformanceException("审核信息无效，请确认");

                foreach (var item in request.Members)
                {
                    if (string.IsNullOrEmpty(item.DoctorName) && string.IsNullOrEmpty(item.PersonnelNumber))
                        throw new PerformanceException("审核信息无效，请确认");
                }
            }

            var allApramounts = perapramountRepository.GetEntities(t => t.AllotId == request.AllotId);
            foreach (var member in request.Members)
            {
                var apramounts = allApramounts?.Where(t => (t.PersonnelNumber ?? "") == member.PersonnelNumber);
                if (request.IsPass == 1 && (apramounts == null || !apramounts.Any()))
                    throw new PerformanceException($"工号为“{member.PersonnelNumber}”的审核信息无效，请确认");

                foreach (var item in apramounts)
                {
                    item.Status = (request.IsPass == 1) ? 3 : 4;
                    item.AuditUser = userid;
                    item.AuditTime = DateTime.Now;
                    perapramountRepository.UpdateRange(apramounts.ToArray());
                }
            }
            return true;
        }
        /// <summary>
        /// 上传导入医院其他绩效
        /// </summary>
        /// <param name="allotid"></param>
        /// <param name="path"></param>
        /// <param name="userid"></param>
        public string ImpoerAprEmployees(int allotid, string path, int userid)
        {
            var userrole = userroleRepository.GetEntity(t => t.UserID == userid);
            if (userrole == null) throw new PerformanceException("用户未绑定角色");

            var data = new List<per_apr_amount>();
            if (new int[] { 1, 2, 5 }.Contains(userrole.RoleID))   //  绩效管理员、医院管理员、绩效核算办查看所有科室的数据
                data = perapramountRepository.GetEntities(t => t.AllotId == allotid && (t.Amount ?? 0) != 0);
            else
                data = perapramountRepository.GetEntities(t => t.AllotId == allotid && (t.Amount ?? 0) != 0 && t.CreateUser == userid);

            if (data != null && data.Any())
                perapramountRepository.RemoveRange(data.ToArray());

            try
            {
                IWorkbook workbook = null;

                var version = FileHelper.GetExtension(path) == ".xlsx" ? ExcelVersion.xlsx : ExcelVersion.xls;
                using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate))
                {
                    workbook = (version == ExcelVersion.xlsx)
                        ? (IWorkbook)(new XSSFWorkbook(fs))
                        : (IWorkbook)(new HSSFWorkbook(fs));
                }
                if (workbook == null) return "";
                var sheet = workbook.GetSheetAt(0);

                var firstRow = sheet.GetRow(0);
                Dictionary<int, string> excelheader = new Dictionary<int, string>();
                for (int cellindex = 0; cellindex < firstRow.LastCellNum + 1; cellindex++)
                {
                    var value = firstRow.GetCell(cellindex).GetValue();
                    if (!string.IsNullOrEmpty(value) && !excelheader.ContainsKey(cellindex))
                        excelheader.Add(cellindex, value);
                }

                if (excelheader == null || excelheader.Count == 0)
                    throw new PerformanceException("上传excel内容错误");

                Dictionary<string, int> dict = new Dictionary<string, int>
                {
                    { "人员工号", -1 }, { "姓名", -1 }, { "绩效类型", -1 }, { "金额", -1 }, { "备注", -1 },
                };

                foreach (var key in dict.Keys.ToList())
                {
                    if (excelheader.Any(w => w.Value == key))
                        dict[key] = excelheader.First(w => w.Value == key).Key;
                }

                var entities = new List<per_apr_amount>();
                var createtime = DateTime.Now;
                var typeIn = GetTypeInDepartment(userid);
                var employees = peremployeeRepository.GetEntities(t => t.AllotId == allotid);

                for (int rowindex = 1; rowindex < sheet.LastRowNum + 1; rowindex++)
                {
                    var row = sheet.GetRow(rowindex);
                    if (row == null) continue;

                    var entity = new per_apr_amount
                    {
                        Status = 2,
                        PersonnelNumber = dict["人员工号"] < 0 ? "" : row.GetCell(dict["人员工号"]).GetValue(),
                        DoctorName = dict["姓名"] < 0 ? "" : row.GetCell(dict["姓名"]).GetValue(),
                        PerforType = dict["绩效类型"] < 0 ? "" : row.GetCell(dict["绩效类型"]).GetValue(),
                        Amount = dict["金额"] < 0 ? 0 : ConvertHelper.To<decimal>(row.GetCell(dict["金额"]).GetValue(), 0),
                        Remark = dict["备注"] < 0 ? "" : row.GetCell(dict["备注"]).GetValue(),
                        TypeInDepartment = typeIn,
                        AllotId = allotid,
                        CreateDate = createtime,
                        CreateUser = userid,
                    };
                    entities.Add(entity);
                }

                //var numbers = entities.Select(t => t.PersonnelNumber).Except(employees.Select(w => w.PersonnelNumber));
                //if (numbers?.Count() > 0 && numbers?.Count() <= 5)
                //    return $@"以下工号在字典中不存在:{JsonHelper.Serialize(numbers.ToArray())}";
                //else if (numbers?.Count() > 5)
                //    return $@"以下工号在字典中不存在:{JsonHelper.Serialize(numbers.Take(5)).Replace("]", ",...]")}";


                // 补充核算单元
                if (entities.Any())
                {
                    if (entities.Any(w => string.IsNullOrEmpty(w.PersonnelNumber) && w.Amount != 0))
                        throw new PerformanceException("文件中存在“工号”为空的数据");
                    if (entities.Any(w => string.IsNullOrEmpty(w.PerforType) && w.Amount != 0))
                        throw new PerformanceException("文件中存在“绩效类型”为空的数据");
                    //if (entities.Any(w => string.IsNullOrEmpty(w.TypeInDepartment) && w.Amount != 0))
                    //    throw new PerformanceException("文件中存在“录入科室”为空的数据");

                    //var employees = peremployeeRepository.GetEntities(w => w.AllotId == allotid);
                    //foreach (var item in entities.Where(w => !string.IsNullOrEmpty(w.PersonnelNumber)))
                    //{
                    //    item.AccountingUnit = employees?.FirstOrDefault(w => w.PersonnelNumber == item.PersonnelNumber)?.AccountingUnit ?? "";
                    //}
                    perapramountRepository.AddRange(entities.ToArray());
                }
                return "";
            }
            catch (PerformanceException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
                return "上传失败";
            }
        }

        /// <summary>
        /// 获取绩效类型字典
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public List<TitleValue> GetPerforTypeDict(int allotId)
        {
            var defaultTypes = new List<string> { "基础绩效", "管理绩效" };

            var aprAmountList = perapramountRepository.GetEntities(w => w.AllotId == allotId);
            if (aprAmountList != null && aprAmountList.Any(w => !defaultTypes.Contains(w.PerforType)))
            {
                var savedTypes = aprAmountList.Where(w => !defaultTypes.Contains(w.PerforType)).Select(t => t.PerforType).Distinct().OrderBy(t => t).ToList();
                defaultTypes.AddRange(savedTypes);
            }
            return defaultTypes.Select(t => new TitleValue
            {
                Title = t,
                Value = t
            }).ToList();
        }

        /// <summary>
        /// 医院其他绩效统计
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public List<Dictionary<string, string>> GetOtherPerStats(int allotId, string department = null)
        {
            var others = new List<Dictionary<string, string>>();
            var aprAmountList = perapramountRepository.GetFullAmount(w => w.AllotId == allotId && w.Status == 3);
            if (department != null)
                aprAmountList = aprAmountList.Where(t => t.AccountingUnit == department).ToList();
            var perForType = aprAmountList.Select(t => t.PerforType).Distinct();

            foreach (var num in aprAmountList.Select(t => t.PersonnelNumber).Distinct())
            {
                var dicData = new Dictionary<string, string>();
                var amount = aprAmountList.FirstOrDefault(t => t.PersonnelNumber == num);
                if (amount == null) continue;

                dicData.Add("核算单元", amount?.AccountingUnit ?? "");
                dicData.Add("工号", amount?.PersonnelNumber ?? "");
                dicData.Add("人员姓名", amount?.DoctorName ?? "");

                foreach (var type in perForType)
                {
                    var emp = aprAmountList.Where(t => t.PerforType == type && t.PersonnelNumber == num);
                    dicData.Add(type, Math.Round(emp?.Sum(c => c.Amount) ?? 0, 2).ToString());
                }

                var sum = aprAmountList.Where(c => c.PersonnelNumber == num)?.Sum(t => t.Amount);
                dicData.Add("合计", Math.Round(sum ?? 0, 2).ToString());
                others.Add(dicData);
            }

            return others;
        }
        #endregion

        /// <summary>
        /// 根据人员工号获取人员信息
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="jobNumber"></param>
        /// <returns></returns>
        public view_per_apr_amount GetEmployeeMessage(int allotId, string personnelNumber, int userId)
        {
            if (string.IsNullOrEmpty(personnelNumber)) return new view_per_apr_amount();

            var user = userRepository.GetEntity(w => w.ID == userId && w.IsDelete == 1);
            if (user == null) throw new PerformanceException("操作用户不存在或用户信息错误!");

            var employee = peremployeeRepository.GetEntity(w => w.AllotId == allotId && w.PersonnelNumber != null && w.PersonnelNumber.Trim() == personnelNumber.Trim());
            if (employee == null) return new view_per_apr_amount();

            return new view_per_apr_amount
            {
                AllotId = allotId,
                PersonnelNumber = employee.PersonnelNumber,
                DoctorName = employee.DoctorName,
                TypeInDepartment = user.Department,
                AccountingUnit = employee.AccountingUnit
            };
        }

        /// <summary>
        /// 更加用户ID获取录入科室
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public string GetTypeInDepartment(int userId)
        {
            var user = userRepository.GetEntity(t => t.ID == userId);
            return user?.Login ?? "";
        }

        #region 科室考核

        public List<Dictionary<string, string>> GetDeptAssessment(int allotId)
        {
            var sheet = perforPersheetRepository.GetEntity(t => t.AllotID == allotId && t.SheetName == "5.4 科室材料考核2");
            if (sheet == null)
                return new List<Dictionary<string, string>>();

            var imData = perforImdataRepository.GetEntities(t => t.AllotID == allotId && t.SheetID == sheet.ID);
            var headers = imData.OrderByDescending(c => c.IsTotal).Select(t => t.TypeName).Distinct();
            var dataNum = imData.Select(t => t.RowNumber)?.OrderBy(c => c.Value).Distinct().ToList();
            if (!dataNum.Any())
                return new List<Dictionary<string, string>>();

            var rowData = new List<Dictionary<string, string>>();
            foreach (var num in dataNum)
            {
                var dicData = new Dictionary<string, string>();
                var fisHeader = false;
                foreach (var header in headers)
                {
                    var headData = imData.Find(t => t.RowNumber == num && t.TypeName == header)?.CellValue;
                    dicData.Add(header, Math.Round((decimal)headData * 100) + "%");
                    if (!fisHeader)
                    {
                        var data = imData?.First(t => t.RowNumber == num);
                        switch (data.UnitType)
                        {
                            case (int)UnitType.医技组:
                                dicData.Add("核算单元类型", "医技组");
                                break;
                            case (int)UnitType.医生组:
                                dicData.Add("核算单元类型", "医生组");
                                break;
                            case (int)UnitType.护理组:
                                dicData.Add("核算单元类型", "护理组");
                                break;
                        }
                        dicData.Add("核算单元", data.AccountingUnit);
                        fisHeader = true;
                    }
                }
                rowData.Add(dicData);
            }

            return rowData;
        }

        #endregion

        #region 不公示其他绩效
        /// <summary>
        /// 获取所有医院其他绩效
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<per_apr_amount_hide> GetAprHideList(int allotId, int userId)
        {
            var userrole = userroleRepository.GetEntity(t => t.UserID == userId);
            if (userrole == null) throw new PerformanceException("用户未绑定角色");

            var list = new List<per_apr_amount_hide>();
            if (new int[] { 1, 2, 5, 6 }.Contains(userrole.RoleID))   //  绩效管理员、医院管理员、绩效核算办、院领导查看所有科室的数据
                list = _hideRepository.GetEntities(t => t.AllotId == allotId && (t.Amount ?? 0) != 0);
            else
                list = _hideRepository.GetEntities(t => t.AllotId == allotId && (t.Amount ?? 0) != 0 && t.CreateUser == userId);
            if (list != null && list.Any())
                list = list.OrderBy(w => w.IsVerify).ThenBy(t => t.DoctorName).ToList();

            return list;
        }
        /// <summary>
        /// 医院其他绩效审核详情
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="department"></param>
        /// <returns></returns>
        public List<view_per_apr_amount> GetAprHideList(int allotId, string department, int? status = null)
        {
            Expression<Func<per_apr_amount_hide, bool>> predicate = w => w.AllotId == allotId && w.Amount.HasValue && w.Amount != 0;
            if (!string.IsNullOrEmpty(department))
                predicate = predicate.And(w => !string.IsNullOrEmpty(w.TypeInDepartment) && w.TypeInDepartment == department);

            if (status.HasValue)
                predicate = predicate.And(w => w.Status == status);

            var list = _hideRepository.GetFullAmount(predicate);
            if (list != null && list.Any())
                list = list.OrderBy(t => t.DoctorName).ToList();

            return list;
        }
        /// <summary>
        /// 新增医院其他绩效
        /// </summary>
        /// <param name="request"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public bool InsertAprHide(per_apr_amount_hide request, int userId)
        {
            if (request == null)
                return false;

            if (string.IsNullOrEmpty(request.PersonnelNumber))
                throw new PerformanceException("文件中存在“工号”为空的数据");
            if (string.IsNullOrEmpty(request.PerforType) && request.Amount != 0)
                throw new PerformanceException("文件中存在“绩效类型”为空的数据");

            request.TypeInDepartment = GetTypeInDepartment(userId);
            request.Status = 2;
            request.CreateDate = DateTime.Now;
            request.CreateUser = userId;
            return _hideRepository.Add(request);
        }
        /// <summary>
        /// 修改医院其他绩效
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool UpdateAprHide(per_apr_amount_hide request)
        {
            if (request == null)
                return false;


            if (string.IsNullOrEmpty(request.PersonnelNumber))
                throw new PerformanceException("文件中存在“工号”为空的数据");
            if (string.IsNullOrEmpty(request.PerforType) && request.Amount != 0)
                throw new PerformanceException("文件中存在“绩效类型”为空的数据");

            var data = _hideRepository.GetEntity(t => t.Id == request.Id);
            if (data == null)
                throw new PerformanceException("修改数据无效");

            data.Status = 2;
            data.PersonnelNumber = request.PersonnelNumber;
            data.DoctorName = request.DoctorName;
            data.PerforType = request.PerforType;
            //data.AccountingUnit = request.AccountingUnit;
            data.Amount = request.Amount;

            return _hideRepository.Update(data);
        }
        /// <summary>
        /// 删除医院其他绩效
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteAprHide(int id)
        {
            var data = _hideRepository.GetEntity(t => t.Id == id);
            if (data != null)
                return _hideRepository.Remove(data);

            return true;
        }

        /// <summary>
        /// 审核医院其他绩效
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="request"></param>
        /// <returns></returns>
        public bool ConfirmAuditHide(int userid, AprAmountAuditRequest request)
        {
            if (request?.Members == null || !request.Members.Any())
                throw new PerformanceException("审核信息无效，请确认");

            foreach (var item in request.Members)
            {
                if (string.IsNullOrEmpty(item.DoctorName) && string.IsNullOrEmpty(item.PersonnelNumber))
                    throw new PerformanceException("审核信息无效，请确认");
            }

            var allApramounts = _hideRepository.GetEntities(t => t.AllotId == request.AllotId);
            foreach (var member in request.Members)
            {
                var apramounts = allApramounts?.Where(t => (t.PersonnelNumber ?? "") == member.PersonnelNumber);
                if (apramounts == null || !apramounts.Any())
                    throw new PerformanceException($"工号为“{member.PersonnelNumber}”的审核信息无效，请确认");

                foreach (var item in apramounts)
                {
                    item.Status = (request.IsPass == 1) ? 3 : 4;
                    item.AuditUser = userid;
                    item.AuditTime = DateTime.Now;
                    _hideRepository.UpdateRange(apramounts.ToArray());
                }
            }
            return true;
        }

        /// <summary>
        /// 上传导入医院其他绩效
        /// </summary>
        /// <param name="allotid"></param>
        /// <param name="path"></param>
        /// <param name="userid"></param>
        public void ImpoerAprHideEmployees(int allotid, string path, int userid)
        {
            var userrole = userroleRepository.GetEntity(t => t.UserID == userid);
            if (userrole == null) throw new PerformanceException("用户未绑定角色");

            var data = new List<per_apr_amount_hide>();
            if (new int[] { 1, 2, 5 }.Contains(userrole.RoleID))   //  绩效管理员、医院管理员、绩效核算办查看所有科室的数据
                data = _hideRepository.GetEntities(t => t.AllotId == allotid && (t.Amount ?? 0) != 0);
            else
                data = _hideRepository.GetEntities(t => t.AllotId == allotid && (t.Amount ?? 0) != 0 && t.CreateUser == userid);

            if (data != null && data.Any())
                _hideRepository.RemoveRange(data.ToArray());

            try
            {
                IWorkbook workbook = null;

                var version = FileHelper.GetExtension(path) == ".xlsx" ? ExcelVersion.xlsx : ExcelVersion.xls;
                using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate))
                {
                    workbook = (version == ExcelVersion.xlsx)
                        ? (IWorkbook)(new XSSFWorkbook(fs))
                        : (IWorkbook)(new HSSFWorkbook(fs));
                }
                if (workbook == null) return;
                var sheet = workbook.GetSheetAt(0);

                var firstRow = sheet.GetRow(0);
                Dictionary<int, string> excelheader = new Dictionary<int, string>();
                for (int cellindex = 0; cellindex < firstRow.LastCellNum + 1; cellindex++)
                {
                    var value = firstRow.GetCell(cellindex).GetValue();
                    if (!string.IsNullOrEmpty(value) && !excelheader.ContainsKey(cellindex))
                        excelheader.Add(cellindex, value);
                }

                if (excelheader == null || excelheader.Count == 0)
                    throw new PerformanceException("上传excel内容错误");

                Dictionary<string, int> dict = new Dictionary<string, int>
                {
                    { "人员工号", -1 }, { "姓名", -1 }, { "绩效类型", -1 }, { "金额", -1 }, { "备注", -1 },
                };

                foreach (var key in dict.Keys.ToList())
                {
                    if (excelheader.Any(w => w.Value == key))
                        dict[key] = excelheader.First(w => w.Value == key).Key;
                }

                var entities = new List<per_apr_amount_hide>();
                var createtime = DateTime.Now;
                var typeIn = GetTypeInDepartment(userid);
                for (int rowindex = 1; rowindex < sheet.LastRowNum + 1; rowindex++)
                {
                    var row = sheet.GetRow(rowindex);
                    if (row == null) continue;

                    var entity = new per_apr_amount_hide
                    {
                        Status = 2,
                        PersonnelNumber = dict["人员工号"] < 0 ? "" : row.GetCell(dict["人员工号"]).GetValue(),
                        DoctorName = dict["姓名"] < 0 ? "" : row.GetCell(dict["姓名"]).GetValue(),
                        PerforType = dict["绩效类型"] < 0 ? "" : row.GetCell(dict["绩效类型"]).GetValue(),
                        Amount = dict["金额"] < 0 ? 0 : ConvertHelper.To<decimal>(row.GetCell(dict["金额"]).GetValue(), 0),
                        Remark = dict["备注"] < 0 ? "" : row.GetCell(dict["备注"]).GetValue(),
                        TypeInDepartment = typeIn,
                        AllotId = allotid,
                        CreateDate = createtime,
                        CreateUser = userid,
                    };
                    entities.Add(entity);
                }

                // 补充核算单元
                if (entities.Any())
                {
                    if (entities.Any(w => string.IsNullOrEmpty(w.PersonnelNumber) && w.Amount != 0))
                        throw new PerformanceException("文件中存在“工号”为空的数据");
                    if (entities.Any(w => string.IsNullOrEmpty(w.PerforType) && w.Amount != 0))
                        throw new PerformanceException("文件中存在“绩效类型”为空的数据");
                    _hideRepository.AddRange(entities.ToArray());
                }
            }
            catch (PerformanceException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
            }
        }

        /// <summary>
        /// 获取绩效类型字典
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public List<TitleValue> GetPerforTypeDictHide(int allotId)
        {
            var defaultTypes = new List<string> { "基础绩效", "管理绩效" };

            var aprAmountList = _hideRepository.GetEntities(w => w.AllotId == allotId);
            if (aprAmountList != null && aprAmountList.Any(w => !defaultTypes.Contains(w.PerforType)))
            {
                var savedTypes = aprAmountList.Where(w => !defaultTypes.Contains(w.PerforType)).Select(t => t.PerforType).Distinct().OrderBy(t => t).ToList();
                defaultTypes.AddRange(savedTypes);
            }
            return defaultTypes.Select(t => new TitleValue
            {
                Title = t,
                Value = t
            }).ToList();
        }

        /// <summary>
        /// 医院其他绩效统计
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public List<Dictionary<string, string>> GetOtherPerStatsHide(int allotId)
        {
            var others = new List<Dictionary<string, string>>();
            var aprAmountList = _hideRepository.GetFullAmount(w => w.AllotId == allotId && w.Status == 3);
            var perForType = aprAmountList.Select(t => t.PerforType).Distinct();

            foreach (var num in aprAmountList.Select(t => t.PersonnelNumber).Distinct())
            {
                var dicData = new Dictionary<string, string>();
                var amount = aprAmountList.FirstOrDefault(t => t.PersonnelNumber == num);
                if (amount == null) continue;

                dicData.Add("核算单元", amount?.AccountingUnit ?? "");
                dicData.Add("工号", amount?.PersonnelNumber ?? "");
                dicData.Add("人员姓名", amount?.DoctorName ?? "");

                foreach (var type in perForType)
                {
                    var emp = aprAmountList.Where(t => t.PerforType == type && t.PersonnelNumber == num);
                    dicData.Add(type, Math.Round(emp?.Sum(c => c.Amount) ?? 0, 2).ToString());
                }

                var sum = aprAmountList.Where(c => c.PersonnelNumber == num)?.Sum(t => t.Amount);
                dicData.Add("合计", Math.Round(sum ?? 0, 2).ToString());
                others.Add(dicData);
            }

            return others;
        }
        #endregion

        public ComparisonResponse<DtoModels.Comparison<view_check_emp>> GetComparison(ComparisonPagingRequest request)
        {
            var result = new ComparisonResponse<DtoModels.Comparison<view_check_emp>>();

            if (request.ViewName == "view_check_dept")
            {
                result.Heads = ComparisonConfig.DeptHeads;
                result.Datas = peremployeeRepository.CheckAccountingUnitRealGiveFeeDiff(request);
            }
            else if (request.ViewName == "view_check_emp")
            {
                result.Heads = ComparisonConfig.EmpHeads;
                result.Datas = peremployeeRepository.CheckEmployeeRealGiveFeeDiff(request);
            }
            else
            {
                result.Datas = new DtoModels.Comparison<view_check_emp> { Datas = new List<view_check_emp>(), TotalCount = 0 };
            }
            //result.Datas = peremployeeRepository.GetComparison(request);
            return result;
        }

        public ComparisonResponse<DtoModels.Comparison<DeptComparisonTotal>> GetDeptComparisonTotal(int AllotId)
        {
            var result = new ComparisonResponse<DtoModels.Comparison<DeptComparisonTotal>>();
            result.Heads = ComparisonConfig.DeptTotalHeads;
            result.Datas = peremployeeRepository.CheckView_check_deptUnitRealGiveFeeDiffTotal(AllotId);

            return result;
        }

        #region 手工数据录入

        public List<GatherDropResponse> GetGatherDrop(int allotId)
        {
            var perSheets = perforPersheetRepository.GetEntities(t => t.AllotID == allotId && new[] { 3, 4, 7 }.Contains(t.SheetType.Value));
            if (perSheets == null || !perSheets.Any())
            {
                var allot = perallotRepository.GetEntity(t => t.ID == allotId);

                var list = perallotRepository.GetEntities(t => t.HospitalId == allot.HospitalId);
                if (list == null || !list.Any(t => t.ID == allot.ID)) return new List<GatherDropResponse>();

                list = list.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).ToList();

                var index = list.IndexOf(list.First(t => t.ID == allot.ID));

                // 先取上一个月的绩效Id，若没有取最后一个月的绩效Id，若都不存在则获取allotId为-1的数据
                allotId = index + 1 < list.Count ? list[index + 1].ID : list.First().ID;
                if (allotId == allot.ID) return new List<GatherDropResponse>();
            }



            //var sheets = perSheets.Select(t => new GatherDropResponse() { Label = t.SheetName, Value = t.SheetName });

            var imHeaders = imheaderRepository.GetEntities(t => t.AllotID == allotId);

            var exresultgather = exresultgatherRepository.GetEntities(t => t.AllotId == allotId);


            foreach (var item in exresultgather.Select(t => new { t.Category, t.Source }).Distinct())
            {
                int id = perSheets.Where(t => t.SheetName.Contains(item.Source)).Select(t => new { t.ID }).ToList()[0].ID;
                imHeaders.Add(
                    new im_header
                    {
                        SheetID = id,
                        CellValue = item.Category
                    }
                );
            }

            var result = new List<GatherDropResponse>();
            var cellValue = new[] { "核算单元（医技组）", "核算单元（医生组）", "核算单元（护理组）", "科室名称" };
            foreach (var sheet in perSheets)
            {
                var drop = new GatherDropResponse();
                var header = imHeaders.Where(t => t.SheetID == sheet.ID && !cellValue.Contains(t.CellValue)).Select(t => t.CellValue).Distinct();
                drop.Label = Regex.Replace(sheet.SheetName.Replace(" ", "").Replace(".", ""), "[0-9]", "")/*sheet.SheetName.Split(' ')[1]*/;
                drop.Value = Regex.Replace(sheet.SheetName.Replace(" ", "").Replace(".", ""), "[0-9]", "")/*sheet.SheetName.Split(' ')[1]*/;
                drop.Children = header.Select(t => new GatherDropResponse() { Label = t, Value = t }).ToList();
                result.Add(drop);
            }

            return result;

        }

        public HandsonTable GetGatherHands(int AllotId, GatherRequest request)
        {
            var result = new HandsonTable((int)SheetType.Unidentifiable, Gather.Select(t => t.Value).ToArray(), Gather.Select(t => new collect_permission
            {
                HeadName = t.Value,
                Visible = 1
            }).ToList());


            if (result.Columns != null && result.Columns.Any())
            {
                foreach (var column in result.Columns)
                {
                    if (column.Data == "数值")
                    {
                        column.Type = "numeric";
                        column.NumericFormat = new NumericFormat { Pattern = "0,00.00" };
                    }
                    else
                        column.Type = "text";
                }
            }
            List<ex_result_gather> data = new List<ex_result_gather>();
            if (!string.IsNullOrEmpty(request.Source) && !string.IsNullOrEmpty(request.Category))
            {
                data = exresultgatherRepository.GetEntities(t => t.AllotId == AllotId && t.Source.Contains(request.Source) && t.Category.Contains(request.Category));
            }

            if (data == null)
                return result;

            List<HandsonRowData> rowDatas = new List<HandsonRowData>();
            int i = 1;
            foreach (var item in data)
            {
                var json = JsonHelper.Serialize(item);
                var firstDic = JsonHelper.Deserialize<Dictionary<string, string>>(json);

                var cells = (from conf in Gather join fst in firstDic on conf.Key.ToUpper() equals fst.Key.ToUpper() select new HandsonCellData(conf.Value, fst.Value)).ToList();

                rowDatas.Add(new HandsonRowData(i, cells));
                i++;
            }
            result.SetRowData(rowDatas, rowDatas != null);
            return result;
        }

        public void SaveGatherHands(int allotId, SaveGatherData request)
        {
            var dicData = CreateDataRow(0, allotId, request, Gather);

            List<ex_result_gather> depts = new List<ex_result_gather>();
            DateTime timeNow = DateTime.Now;
            foreach (var item in dicData)
            {
                var json = JsonHelper.Serialize(item);
                var data = JsonHelper.Deserialize<ex_result_gather>(json);
                if (!string.IsNullOrEmpty(data.Department) && !string.IsNullOrEmpty(data.Fee.ToString()))
                {
                    data.Source = request.Source;
                    data.Category = request.Category;
                    data.AllotId = allotId;
                    data.CreateTime = timeNow;
                    depts.Add(data);
                }
            }

            exresultgatherRepository.Execute($@"delete from ex_result_gather where allotid = @allotid and source like '%{request.Source}%' and category = '{request.Category}' ", new { allotId });
            exresultgatherRepository.AddRange(depts.ToArray());
        }

        public GatherInfo GetGather(int allotId, string department, string source, PersonParamsRequest request)
        {
            Expression<Func<ex_result_gather, bool>> exp = t => t.AllotId == allotId && t.Department.Contains(department) && t.Source.Contains(source);

            if (request != null && !string.IsNullOrEmpty(request.SearchQuery))
                exp = exp.And(t => t.AllotId == allotId && t.Department.Contains(department) && t.Source.Contains(source) && t.DoctorName.Contains(request.SearchQuery) || t.PersonnelNumber.Contains(request.SearchQuery));

            var datas = exresultgatherRepository.GetEntities(exp);

            var result = datas.GroupBy(a => new { a.Source, a.Department, a.DoctorName, a.PersonnelNumber }).Select(t => new
            {
                Source = t.Key.Source,
                Department = t.Key.Department,
                DoctorName = t.Key.DoctorName,
                PersonnelNumber = t.Key.PersonnelNumber,
                Detail = t.GroupBy(group => group.Category).Select(s => new TitleValue<decimal>
                {
                    Title = string.IsNullOrEmpty(s.Key) ? "未知" : s.Key,
                    Value = s.Sum(sum => sum.Fee)
                })
            });

            List<GatherInfoRequest> gatherInfoRequests = new List<GatherInfoRequest>();
            foreach (var item in result.ToList())
            {
                GatherInfoRequest gatherInfoRequest = new GatherInfoRequest()
                {
                    Source = Regex.Replace(item.Source.Replace(" ", "").Replace(".", ""), "[0-9]", ""),
                    Department = item.Department,
                    DoctorName = item.DoctorName,
                    PersonnelNumber = item.PersonnelNumber,
                    Detail = new List<GatherInfoFee>()
                };
                foreach (var item2 in item.Detail)
                {
                    GatherInfoFee gatherInfoFee = new GatherInfoFee()
                    {
                        Category = item2.Title,
                        Fee = item2.Value
                    };
                    gatherInfoRequest.Detail.Add(gatherInfoFee);
                }
                gatherInfoRequests.Add(gatherInfoRequest);
            }

            var head = ColumnHeadsConfig.GatherHeads;
            head.ForEach(t =>
            {
                t.Name = t.Name.ToLower();
            });

            GatherInfo gatherInfo = new GatherInfo()
            {
                Heads = head,
                Datas = gatherInfoRequests.Skip((request.PageNumber - 1) * request.PageSize).Take(request.PageSize).ToList(),
                CurrentPage = request.PageNumber,
                TotalCount = gatherInfoRequests.Count(),
                PageSize = request.PageSize,
                TotalPages = (int)Math.Ceiling((double)gatherInfoRequests.Count() / request.PageSize)
            };
            return gatherInfo;
        }

        public GatherResponse GetGatherTotal(int allotId, PersonParamsRequest request)
        {
            List<GatherTotalRequest> gatherTotalRequests = new List<GatherTotalRequest>();

            Expression<Func<ex_result_gather, bool>> exp = t => t.AllotId == allotId;

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

            var datas = exresultgatherRepository.GetEntities(exp);
            if (datas != null && datas.Any())
            {
                var result = datas.GroupBy(a => new { a.AllotId, a.Department, a.Source }).Select(t => new
                {
                    ID = t.Key.AllotId,
                    Department = t.Key.Department,
                    Source = t.Key.Source,
                    Fee = t.Sum(a => a.Fee)
                });

                foreach (var item in result.ToList())
                {
                    GatherTotalRequest gatherTotalRequest = new GatherTotalRequest()
                    {
                        ID = item.ID,
                        Department = item.Department,
                        Source = Regex.Replace(item.Source.Replace(" ", "").Replace(".", ""), "[0-9]", "")/*item.Source.Split(' ')[1]*/,
                        Fee = item.Fee
                    };
                    gatherTotalRequests.Add(gatherTotalRequest);
                }
            }

            var head = ColumnHeadsConfig.GatherTotal;
            head.ForEach(t =>
            {
                t.Name = t.Name.ToLower();
            });
            GatherResponse gatherResponse = new GatherResponse()
            {
                Heads = head,
                Datas = gatherTotalRequests.Skip((request.PageNumber - 1) * request.PageSize).Take(request.PageSize).ToList(),
                CurrentPage = request.PageNumber,
                TotalCount = gatherTotalRequests.Count(),
                PageSize = request.PageSize,
                TotalPages = (int)Math.Ceiling((double)gatherTotalRequests.Count() / request.PageSize)
            };
            return gatherResponse;

        }

        public static Dictionary<string, string> Gather { get; } = new Dictionary<string, string>
        {
            {nameof(ex_result_gather.Department), "科室"},
            {nameof(ex_result_gather.DoctorName), "医生姓名"},
            {nameof(ex_result_gather.PersonnelNumber), "人员工号"},
            //{nameof(ex_result_gather.Category), "费用类型"},
            {nameof(ex_result_gather.Fee), "数值"},
            //{nameof(ex_result_gather.Source), "来源"}
        };

        private List<Dictionary<string, string>> CreateDataRow(int hospitalId, int allotId, SaveGatherData 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);
                baseData.Add(nameof(cof_hrp_department.AllotId), hospitalId.ToString());
                allData.Add(baseData);

            }
            return allData;
        }

        private Dictionary<string, string> CreateBaseData(SaveGatherData 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;
        }



        #endregion

        #region 录入校验

        public void CheckGatherData(int allotId, SaveGatherData saveGather)
        {
            var allot = perallotRepository.GetEntity(w => w.ID == allotId);
            if (allot == null) throw new PerformanceException("绩效记录不存在");

            var data = saveGather.Data;

            var departments = perdeptdicRepository.GetEntities(w => w.HospitalId == allot.HospitalId);
            if (departments == null || !departments.Any()) throw new PerformanceException("未配置科室字典");

            var notExistsDeptData = data.Where(w => !departments.Select(t => t.Department).Contains(w[0]));
            if (notExistsDeptData != null && notExistsDeptData.Any())
                throw new PerformanceException($"科室字典中不存在科室[{string.Join(",", notExistsDeptData.Select(t => t[0]).Distinct())}]");


            var employees = peremployeeRepository.GetEntities(w => w.AllotId == allotId);
            if (employees == null || !employees.Any()) throw new PerformanceException("未配置人员字典");

            var notExistNameData = data.Where(w => !employees.Select(t => t.DoctorName).Contains(w[1]) && !string.IsNullOrEmpty(w[1]));
            if (notExistNameData != null && notExistNameData.Any())
                throw new PerformanceException($"人员字典中不存在医生姓名[{string.Join(",", notExistNameData.Select(t => t[1]).Distinct())}]");

            var notExistNumberData = data.Where(w => !employees.Select(t => t.PersonnelNumber).Contains(w[2]) && !string.IsNullOrEmpty(w[2]));
            if (notExistNumberData != null && notExistNumberData.Any())
                throw new PerformanceException($"人员字典中不存在工号[{string.Join(",", notExistNumberData.Select(t => t[2]).Distinct())}]");

            var sheets = perforPersheetRepository.GetEntities(w => w.AllotID == allotId);
            if (sheets != null && sheets.Any())
            {
                if (!sheets.Select(t => t.SheetName).Any(t => t.Contains(saveGather.Source)))
                    throw new PerformanceException($"来源错误[{saveGather.Source}]");
            }
        }

        private static void SetDataStatesAndRemark(IEnumerable<ex_result_gather> data, int states, string remark, Func<ex_result_gather, string> func = null)
        {
            if (new int[] { 1, 2 }.Contains(states)) return;

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

            foreach (var item in data)
            {
                if (func != null)
                    remark = string.Format(remark, func.Invoke(item));

                //item.States = states;
                item.Remark = (!string.IsNullOrEmpty(item.Remark) && item.Remark.Length > 0) ? item.Remark + ", " + remark : remark;
            }
        }

        public void AddCategoryToConfig(int allotId)
        {
            var allot = perallotRepository.GetEntity(w => w.ID == allotId);

            var data = exresultgatherRepository.GetEntities(w => w.AllotId == allotId);
            if (data == null || !data.Any()) return;

            var modules = exmoduleRepository.GetEntities(w => w.HospitalId == allot.HospitalId && w.SheetType != (int)SheetType.Income);
            if (modules == null || !modules.Any()) return;

            var items = exitemRepository.GetEntities(w => modules.Select(t => t.Id).Distinct().Contains(w.ModuleId ?? 0));
            if (items == null || !items.Any()) return;

            try
            {
                var insertList = new List<ex_item>();
                foreach (var module in modules)
                {
                    var categories = data.Where(t => module.ModuleName.Contains(t.Source))?.Select(t => t.Category).Distinct();
                    if (categories == null || !categories.Any()) continue;

                    var moduleItems = items.Where(w => w.ModuleId == module.Id)?.Select(t => t.ItemName).Distinct();
                    if (moduleItems != null && moduleItems.Any())
                        categories = categories.Except(moduleItems);

                    if (categories == null || !categories.Any()) continue;

                    insertList.AddRange(categories.Select(t => new ex_item
                    {
                        ModuleId = module.Id,
                        ItemName = t,
                        ReadOnly = 0
                    }));
                }

                if (insertList != null && insertList.Any())
                {
                    exitemRepository.AddRange(insertList.ToArray());
                }

                var speacialCategories = data.Where(t => t.Source.Contains("特殊核算单元"))?.Select(t => new { t.Category, t.Department }).Distinct();
                if (speacialCategories == null || !speacialCategories.Any()) return;

                var specials = exspecialRepository.GetEntities(w => w.HospitalId == allot.HospitalId);
                if (specials == null || !specials.Any()) return;

                var list = (specials == null || !specials.Any())
                    ? speacialCategories
                    : speacialCategories.Where(w => !specials.Select(t => t.Target + t.Department).Contains(w.Category + w.Department));

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

                exspecialRepository.AddRange(list.Select(t => new ex_special
                {
                    HospitalId = allot.HospitalId,
                    Department = t.Department,
                    Target = t.Category
                }).ToArray());
            }
            catch (Exception ex)
            {
                logger.LogError(ex.Message);
            }
        }

        public void SyncDataToResult(int allotId)
        {
            var sheets = perforPersheetRepository.GetEntities(t => t.AllotID == allotId && new[] { 3, 4, 7 }.Contains(t.SheetType.Value));
            if (sheets == null || !sheets.Any()) return;

            var data = exresultgatherRepository.GetEntities(w => w.AllotId == allotId);
            if (data == null || !data.Any()) return;

            var syncData = _mapper.Map<List<ex_result>>(data);

            syncData.ForEach(x =>
            {
                x.Id = 0;
                x.Source = sheets.FirstOrDefault(t => t.SheetName.Contains(x.Source))?.SheetName ?? x.Source;
            });
            exresultRepository.AddRange(syncData.ToArray());
        }

        #endregion
    }

    public class ComparisonConfig
    {
        public static List<Heads> DeptHeads { get; } = new List<Heads>
        {
            new Heads{Column="核算单元组别",Name=nameof(view_check_dept.UnitType)},
            new Heads{Column="核算单元",Name=nameof(view_check_dept.AccountingUnit)},
            new Heads{Column="测算表实发",Name=nameof(view_check_dept.RealGiveFeeExecl)},
            new Heads{Column="软件实发",Name=nameof(view_check_dept.RealGiveFeeCompute)},
            new Heads{Column="差额",Name=nameof(view_check_dept.Diff)},
        };

        public static List<Heads> EmpHeads { get; } = new List<Heads>
        {
            new Heads{Column="核算单元组别",Name=nameof(view_check_emp.UnitType)},
            new Heads{Column="核算单元",Name=nameof(view_check_emp.AccountingUnit)},
            new Heads{Column="人员工号",Name=nameof(view_check_emp.JobNumber)},
            new Heads{Column="姓名",Name=nameof(view_check_emp.EmployeeName)},
            new Heads{Column="测算表实发",Name=nameof(view_check_emp.RealGiveFeeExecl)},
            new Heads{Column="软件实发",Name=nameof(view_check_emp.RealGiveFeeCompute)},
            new Heads{Column="差额",Name=nameof(view_check_emp.Diff)},
        };

        public static List<Heads> DeptTotalHeads { get; } = new List<Heads>
        {
            new Heads{Column="分类",Name=nameof(view_check_dept.UnitType)},
            new Heads{Column="实发不一致",Name="Count"},
            new Heads{Column="总额",Name="SumFee"},
        };
    }
}
