using Microsoft.Extensions.Options;
using Performance.DtoModels;
using Performance.DtoModels.AppSettings;
using Performance.DtoModels.Response;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Google.Protobuf.WellKnownTypes;
using HandsonRowData = Performance.DtoModels.HandsonRowData;
using Performance.Services.ExtractExcelService;

namespace Performance.Services
{
    public class CollectService : IAutoInjection
    {
        private readonly PerforUserRepository userRepository;
        private readonly PerforPerallotRepository perforPerallotRepository;
        private readonly PerforPersheetRepository perforPersheetRepository;
        private readonly PerforImheaderRepository perforImheaderRepository;
        private readonly PerforImdataRepository perforImdataRepository;
        private readonly PerforImemployeeRepository perforImEmployeeRepository;
        private readonly PerforImemployeeclinicRepository perforImemployeeclinicRepository;
        private readonly PerforImemployeelogisticsRepository perforImemployeelogisticsRepository;
        private readonly PerforCollectdataRepository perforcollectdataRepository;
        private readonly PerforCollectpermissionRepository perforcollectpermissionRepository;
        private readonly PerforUserroleRepository userroleRepository;
        private readonly PerforImaccountbasicRepository perforImaccountbasicRepository;
        private readonly PerforPerdeptdicRepository perforPerdeptdicRepository;
        private readonly PerforExmoduleRepository exmoduleRepository;
        private readonly PerforExitemRepository exitemRepository;
        private readonly PerforExspecialRepository exspecialRepository;
        private readonly SheetSevice sheetSevice;
        private readonly Application options;

        public CollectService(
            PerforUserRepository userRepository,
            PerforPerallotRepository perforPerallotRepository,
            PerforPersheetRepository perforPersheetRepository,
            PerforImheaderRepository perforImheaderRepository,
            PerforImdataRepository perforImdataRepository,
            PerforImemployeeRepository perforImEmployeeRepository,
            PerforImemployeeclinicRepository perforImemployeeclinicRepository,
            PerforImemployeelogisticsRepository perforImemployeelogisticsRepository,
            PerforCollectdataRepository perforcollectdataRepository,
            PerforCollectpermissionRepository perforcollectpermissionRepository,
            PerforUserroleRepository userroleRepository,
            PerforImaccountbasicRepository perforImaccountbasicRepository,
            PerforPerdeptdicRepository perforPerdeptdicRepository,
            PerforExmoduleRepository exmoduleRepository,
            PerforExitemRepository exitemRepository,
            PerforExspecialRepository exspecialRepository,
            SheetSevice sheetSevice,
            IOptions<Application> options)
        {
            this.userRepository = userRepository;
            this.perforPerallotRepository = perforPerallotRepository;
            this.perforPersheetRepository = perforPersheetRepository;
            this.perforImheaderRepository = perforImheaderRepository;
            this.perforImdataRepository = perforImdataRepository;
            this.perforImEmployeeRepository = perforImEmployeeRepository;
            this.perforImemployeeclinicRepository = perforImemployeeclinicRepository;
            this.perforImemployeelogisticsRepository = perforImemployeelogisticsRepository;
            this.perforcollectdataRepository = perforcollectdataRepository;
            this.perforcollectpermissionRepository = perforcollectpermissionRepository;
            this.userroleRepository = userroleRepository;
            this.perforImaccountbasicRepository = perforImaccountbasicRepository;
            this.perforPerdeptdicRepository = perforPerdeptdicRepository;
            this.exmoduleRepository = exmoduleRepository;
            this.exitemRepository = exitemRepository;
            this.exspecialRepository = exspecialRepository;
            this.sheetSevice = sheetSevice;
            this.options = options.Value;
        }

        public string[] retain { get; } = new[] { "核算单元（医技组）", "核算单元（医生组）", "核算单元（护理组）", "科室名称", "核算单元",
                    "核算单元类型", "人员工号", "医生姓名", "姓名", "核算单元分类", "人员分类" };

        /// <summary>
        /// 查询采集内容
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public IEnumerable<CollectPermission> GetCollectContent(int hospitalId, int userId)
        {
            var states = new[] { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed, (int)AllotStates.GenerateAccomplish };
            var allotList = perforPerallotRepository.GetEntities(w => w.HospitalId == hospitalId && states.Contains(w.States))
                ?.OrderByDescending(w => w.Year).ThenByDescending(w => w.Month).ToList();
            if (allotList == null || !allotList.Any())
                return null;

            var allot = allotList.First();

            var types = new[] { (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload, (int)SheetType.SpecialUnit };

            var sheets = exmoduleRepository.GetEntities(w => w.HospitalId == hospitalId && types.Contains(w.SheetType ?? 0));
            var specials = exspecialRepository.GetEntities(w => w.HospitalId == hospitalId);
            if ((sheets == null || !sheets.Any()) && (specials == null || !specials.Any()))
                return null;

            var headers = exitemRepository.GetEntities(w => sheets.Select(m => m.Id).Contains(w.ModuleId ?? 0)) ?? new List<ex_item>();
            if ((headers == null || !headers.Any()) && (specials == null || !specials.Any()))
                return null;

            if (specials != null && specials.Any())
            {
                sheets.Add(new ex_module
                {
                    HospitalId = allot.HospitalId,
                    SheetType = (int)SheetType.SpecialUnit,
                    ModuleName = "4.2 特殊核算单元绩效测算表",
                    Id = 0,
                });

                headers.AddRange(specials.Select(t => new ex_item { ItemName = t.Target, ModuleId = 0 }));
            }

            var permissions = perforcollectpermissionRepository.GetEntities(w => w.HospitalId == hospitalId && w.UserId == userId);

            // 查询sheet和列头对应
            var sheetHeads = from sheet in sheets
                             join head in headers on sheet.Id equals head.ModuleId
                             select new { sheet.ModuleName, sheet.SheetType, HeadName = head.ItemName };

            List<CollectPermission> result;
            // 查询列头和权限对应
            if (permissions == null)
            {
                result = sheetHeads.Select(sh => new CollectPermission { HospitalId = hospitalId, SheetName = sh.ModuleName, SheetType = sh.SheetType.Value, HeadName = sh.HeadName })?.ToList();
            }
            else
            {
                result = (from sh in sheetHeads
                          join pmss in permissions on new { SheetName = sh.ModuleName, sh.HeadName } equals new { pmss.SheetName, pmss.HeadName }
                          into emppmss
                          from item in emppmss.DefaultIfEmpty()
                          select new CollectPermission
                          {
                              HospitalId = hospitalId,
                              SheetName = sh.ModuleName,
                              SheetType = sh.SheetType.Value,
                              HeadName = sh.HeadName,
                              PermissionId = item?.Id,
                              Readnoly = item?.Readnoly ?? 0,
                              AttachLast = item?.AttachLast ?? 0,
                              Visible = item?.Visible ?? 0
                          }).ToList();
            }
            if (result != null && result.Any())
            {
                result.ForEach(w =>
                {
                    w.BanChange = retain.Any(r => r == w.HeadName);
                    w.Readnoly = w.BanChange ? 1 : w.Readnoly;
                    w.AttachLast = w.BanChange ? 1 : w.AttachLast;
                    w.Visible = w.BanChange ? 1 : w.Visible;
                });
                return result;
            }
            return result;
        }

        /// <summary>
        /// 设置采集录入权限
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="collects"></param>
        public void SetPermission(int userid, IEnumerable<CollectPermission> collects)
        {
            var user = userRepository.GetEntity(w => w.ID == userid);
            if (user == null)
                throw new PerformanceException("参数无效");

            //// 删除
            //var data3 = collects.Where(w => w.PermissionId.HasValue && 0 == w.Visible && 0 == w.AttachLast && 0 == w.Readnoly);
            //if (data3 != null)
            //{
            //    var ids = data3.Select(t => t.PermissionId).ToList();
            //    var deletes = perforcollectpermissionRepository.GetEntities(w => ids.Contains(w.Id));
            //    if (deletes != null)
            //        perforcollectpermissionRepository.RemoveRange(deletes.ToArray());
            //}

            //// 修改
            //var data1 = collects.Where(w => w.PermissionId.HasValue && (w.Visible > 0 || w.AttachLast > 0 || w.Readnoly > 0));
            //if (data1 != null)
            //{
            //    var ids = data1.Select(t => t.PermissionId).ToList();
            //    var updates = perforcollectpermissionRepository.GetEntities(w => ids.Contains(w.Id));
            //    if (updates != null)
            //    {
            //        foreach (var item in updates)
            //        {
            //            item.AttachLast = data1.FirstOrDefault(w => w.PermissionId == item.Id).AttachLast;
            //            item.Readnoly = data1.FirstOrDefault(w => w.PermissionId == item.Id).Readnoly;
            //            item.Visible = data1.FirstOrDefault(w => w.PermissionId == item.Id).Visible;
            //        }
            //        perforcollectpermissionRepository.UpdateRange(updates.ToArray());
            //    }
            //}

            var hospitalid = collects?.FirstOrDefault(w => w.HospitalId > 0)?.HospitalId;
            if (hospitalid.HasValue)
            {
                var permissions = perforcollectpermissionRepository.GetEntities(t => t.HospitalId == hospitalid.Value && t.UserId == userid);
                if (permissions != null && permissions.Any())
                    perforcollectpermissionRepository.RemoveRange(permissions.ToArray());
            }

            // 新增
            var users = new List<sys_user> { user };
            var children = userRepository.GetEntities(w => w.ParentID == userid);
            if (children != null && children.Any()) users.AddRange(children);

            var data2 = collects.Where(w => w.Visible > 0);
            foreach (var item in users)
            {
                var inserts = data2?.Select(w => new collect_permission
                {
                    UserId = item.ID,
                    SheetName = w.SheetName,
                    SheetType = w.SheetType,
                    HospitalId = w.HospitalId,
                    HeadName = w.HeadName,
                    AttachLast = w.AttachLast,
                    Readnoly = w.Readnoly,
                    Visible = w.Visible,
                });
                if (data2 != null)
                {
                    perforcollectpermissionRepository.AddRange(inserts.ToArray());
                }
            }
        }

        public IEnumerable<collect_permission> GetCollectSheet(int hospitalId, int userId, string sheetName = "")
        {
            var userrole = userroleRepository.GetEntity(t => t.UserID == userId);
            if (userrole == null) return new List<collect_permission>();

            if (!options.CollectRoles.Contains(userrole.RoleID))
            {
                var types = new[] { (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload, (int)SheetType.SpecialUnit };
                var sheetNames = exmoduleRepository.GetEntities(w => w.HospitalId == hospitalId && types.Contains(w.SheetType ?? 0))?.Select(t => t.ModuleName).ToList() ?? new List<string>();
                sheetNames.Add("4.2 特殊核算单元绩效测算表");

                Expression<Func<collect_permission, bool>> exp = (w) => w.HospitalId == hospitalId && w.UserId == userId && sheetNames.Contains(w.SheetName);
                if (!string.IsNullOrEmpty(sheetName))
                    exp = exp.And(w => w.SheetName == sheetName);
                return perforcollectpermissionRepository.GetEntities(exp);
            }

            var result = GetCollectSheetByAllot(hospitalId, userId);
            return result;
        }

        private IEnumerable<collect_permission> GetCollectSheetByAllot(int hospitalId, int userId, string sheetName = "")
        {
            //var states = new[] { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed, (int)AllotStates.GenerateAccomplish };
            //var allotList = perforPerallotRepository.GetEntities(w => w.HospitalId == hospitalId && states.Contains(w.States))
            //    ?.OrderByDescending(w => w.Year).ThenByDescending(w => w.Month).ToList();
            //if (allotList == null || !allotList.Any())
            //    return null;

            //var allot = allotList.First();

            var types = new[] { (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload, (int)SheetType.SpecialUnit };

            var sheets = exmoduleRepository.GetEntities(w => w.HospitalId == hospitalId && types.Contains(w.SheetType ?? 0)) ?? new List<ex_module>();
            var headers = exitemRepository.GetEntities(w => sheets.Select(t => t.Id).Contains(w.ModuleId ?? 0)) ?? new List<ex_item>();
            var specials = exspecialRepository.GetEntities(t => t.HospitalId == hospitalId);

            Expression<Func<collect_permission, bool>> exp = (w) => w.HospitalId == hospitalId && w.UserId == userId;
            if (!string.IsNullOrEmpty(sheetName))
            {
                exp = exp.And(w => w.SheetName == sheetName);
                sheets = sheets.Where(w => w.ModuleName == sheetName)?.ToList();
            }
            var permissions = perforcollectpermissionRepository.GetEntities(exp);

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

            if ((headers == null || !headers.Any()) && (specials == null || !specials.Any()))
                return null;

            if (specials != null && specials.Any())
            {
                sheets.Add(new ex_module
                {
                    HospitalId = hospitalId,
                    SheetType = (int)SheetType.SpecialUnit,
                    ModuleName = "4.2 特殊核算单元绩效测算表",
                    Id = 0,
                });

                headers.AddRange(specials.Select(t => new ex_item { ItemName = t.Target, ModuleId = 0 }));
            }

            // 查询sheet和列头对应
            var sheetHeads = from sheet in sheets
                             join head in headers on sheet.Id equals head.ModuleId
                             select new { sheet.ModuleName, sheet.SheetType, HeadName = head.ItemName };

            var result = sheetHeads.Select(sh => new collect_permission { HospitalId = hospitalId, SheetName = sh.ModuleName, SheetType = sh.SheetType.Value, HeadName = sh.HeadName, AttachLast = 0, Visible = 1 })?.ToList();

            if (result != null && result.Any())
            {
                foreach (var item in result)
                {
                    var permission = permissions?.FirstOrDefault(t => t.HospitalId == item.HospitalId && t.SheetName == item.SheetName && t.SheetType == item.SheetType && t.HeadName == item.HeadName);
                    if (permission == null) continue;

                    item.Visible = permission.Visible;
                }
            }

            return result;
        }

        #region 加载采集内容

        /// <summary>
        /// 加载采集内容
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public HandsonTable GetCollectData(int userId, int allotId, string sheetName)
        {
            var user = userRepository.GetEntity(w => w.ID == userId);
            if (user == null)
                throw new PerformanceException("参数无效");

            var allot = perforPerallotRepository.GetEntity(w => w.ID == allotId);
            if (allot == null)
                throw new PerformanceException("参数无效");

            var states = new[] { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed, (int)AllotStates.GenerateAccomplish };
            // 如果录入绩效未生成 则获取最近新可用绩效记录
            if (!states.Contains(allot.States))
            {
                var allotList = perforPerallotRepository
                   .GetEntities(w => w.HospitalId == allot.HospitalId && states.Contains(w.States))
                   ?.OrderBy(w => w.Year).ThenByDescending(w => w.Month).ToList();
                allot = allotList?.First();
            }
            if (allot == null)
                throw new PerformanceException("当前医院没有录入项");

            var types = new[] { (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload, (int)SheetType.SpecialUnit };

            var sheet = new ex_module
            {
                HospitalId = allot.HospitalId,
                SheetType = (int)SheetType.SpecialUnit,
                ModuleName = "4.2 特殊核算单元绩效测算表",
            };
            var headers = new List<ex_item>();
            if (sheetName.NoBlank() == sheet.ModuleName.NoBlank())
            {
                var specials = exspecialRepository.GetEntities(t => t.HospitalId == allot.HospitalId);
                if (specials == null || !specials.Any())
                    throw new PerformanceException("SHEET页没有有效列头");

                headers = specials.Select(t => new ex_item { ItemName = t.Target }).ToList();
            }
            else
            {
                sheet = exmoduleRepository.GetEntity(w => w.HospitalId == allot.HospitalId && w.ModuleName.NoBlank() == sheetName.NoBlank() && types.Contains(w.SheetType ?? 0));
                if (sheet == null)
                    throw new PerformanceException("找不到当前SHEET页信息");

                headers = exitemRepository.GetEntities(w => w.ModuleId == sheet.Id);
                if (headers == null || !headers.Any())
                    throw new PerformanceException("SHEET页没有有效列头");
            }

            var cols = headers.Select(w => w.ItemName).ToArray();
            var permissions = GetCollectSheet(allot.HospitalId, userId, sheetName)?.ToList();
            if (permissions == null || !permissions.Any())
                throw new PerformanceException("用户没有权限编辑当前SHEET页");

            List<HandsonRowData> rowDatas = new List<HandsonRowData>();
            var collectdata = perforcollectdataRepository.GetEntities(w => w.SheetName == sheet.ModuleName && w.AllotID == allotId);
            if (collectdata != null)
            {
                Dictionary<string, string> config = CollectDataConfig.Default;

                foreach (var rownumber in collectdata.Select(w => w.RowNumber).Distinct().OrderBy(t => t))
                {
                    var first = collectdata.FirstOrDefault(w => w.RowNumber == rownumber);
                    var json = JsonHelper.Serialize(first);
                    var firstDic = JsonHelper.Deserialize<Dictionary<string, string>>(json);

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

                    foreach (var item in collectdata.Where(w => w.RowNumber == rownumber))
                    {
                        cells.Add(new HandsonCellData(item.TypeName, item.CellValue));
                    }
                    rowDatas.Add(new HandsonRowData(rownumber, cells));
                }
            }

            if (rowDatas == null || !rowDatas.Any())
                rowDatas = GetAccountExtra(allot, sheet.SheetType.Value, sheetName);

            rowDatas?.RemoveAll(t => !t.CellData.Any(w => !string.IsNullOrEmpty(w.Value?.ToString())));
            if (rowDatas != null && rowDatas.Any())
                rowDatas = rowDatas.OrderBy(t => t.Row).ToList();

            HandsonTable handson = new HandsonTable(sheet.SheetType.Value, cols, permissions);
            handson.SetRowData(rowDatas, (collectdata != null));
            if (collectdata != null && collectdata.Any(t => new int[] { 2, 3 }.Contains(t.Status)))
            {
                handson.Columns?.ForEach(t => t.ReadOnly = true);
                handson.ReadOnly = true;
            }
            return handson;
        }

        private List<HandsonRowData> GetCommonData(per_sheet sheet)
        {
            var rowdatas = new List<HandsonRowData>();
            var imdatas = perforImdataRepository.GetEntities(w => w.SheetID == sheet.ID);
            if (imdatas != null && imdatas.Any())
            {
                var rownums = imdatas.Select(w => new { w.RowNumber, w.Department }).Distinct()?.OrderBy(w => w.RowNumber);
                foreach (var rownum in rownums)
                {
                    var rowdata = imdatas.Where(w => w.RowNumber == rownum.RowNumber && w.Department == rownum.Department);
                    var setdata = new List<HandsonCellData>(); //rowdata.Select(w => new HandsonCellData(w.TypeName, w.CellValue))?.ToList();
                    if (setdata == null) continue;

                    setdata.Add(new HandsonCellData("科室名称", rownum.Department));
                    var units = rowdata.Where(w => w.UnitType.HasValue).Select(w => new { w.UnitType, w.AccountingUnit }).Distinct();
                    foreach (var item in units)
                    {
                        if ((new[] { (int)UnitType.医生组, (int)UnitType.其他医生组 }).Contains(item.UnitType.Value))
                        {
                            setdata.Add(new HandsonCellData("核算单元（医生组）", item.AccountingUnit));
                            GetUnitClassification(setdata, UnitType.医生组, sheet);
                        }
                        else if ((new[] { (int)UnitType.医技组, (int)UnitType.其他医技组 }).Contains(item.UnitType.Value))
                        {
                            setdata.Add(new HandsonCellData("核算单元（医技组）", item.AccountingUnit));
                            GetUnitClassification(setdata, UnitType.医技组, sheet);
                        }
                        else if ((new[] { (int)UnitType.护理组, (int)UnitType.其他护理组 }).Contains(item.UnitType.Value))
                        {
                            setdata.Add(new HandsonCellData("核算单元（护理组）", item.AccountingUnit));
                            GetUnitClassification(setdata, UnitType.护理组, sheet);
                        }
                        setdata.Add(new HandsonCellData("核算单元", item.AccountingUnit));
                    }
                    if (sheet.SheetType == (int)SheetType.PersonExtra)
                    {
                        var employee = rowdata.Select(w => new { w.EmployeeName, w.JobNumber }).Distinct().FirstOrDefault();

                        setdata.Add(new HandsonCellData("医生姓名", employee.EmployeeName));
                        setdata.Add(new HandsonCellData("人员工号", employee.JobNumber));
                    }
                    rowdatas.Add(new HandsonRowData(rownum.RowNumber.Value, setdata));
                }
            }
            return rowdatas;
        }

        private List<HandsonRowData> GetLogisticsEmployee(per_sheet sheet)
        {
            var employeeList = perforImemployeelogisticsRepository.GetEntities(t => t.SheetID == sheet.ID)?.OrderBy(w => w.RowNumber);
            if (employeeList != null && employeeList.Any())
            {
                var temps = employeeList.Select(item => new { row = item.RowNumber.Value, dic = JsonHelper.Deserialize(item) });
                return temps.Select(temp => CreateRowData(temp.row, ExcelReadConfig.LogisticsEmployee, temp.dic)).ToList();
            }
            return new List<HandsonRowData>();
        }

        private List<HandsonRowData> GetClinicEmployee(per_sheet sheet)
        {
            var employeeList = perforImemployeeclinicRepository.GetEntities(t => t.SheetID == sheet.ID)?.OrderBy(w => w.RowNumber);
            if (employeeList != null && employeeList.Any())
            {
                var temps = employeeList.Select(item => new { row = item.RowNumber.Value, dic = JsonHelper.Deserialize(item) });
                return temps.Select(temp => CreateRowData(temp.row, ExcelReadConfig.ClinicEmployee, temp.dic)).ToList();
            }
            return new List<HandsonRowData>();
        }

        private List<HandsonRowData> GerEmployee(per_sheet sheet)
        {
            var employeeList = perforImEmployeeRepository.GetEntities(t => t.SheetID == sheet.ID)?.OrderBy(w => w.RowNumber);
            if (employeeList != null && employeeList.Any())
            {
                var temps = employeeList.Select(item => new { row = item.RowNumber.Value, dic = JsonHelper.Deserialize(item) });
                return temps.Select(temp => CreateRowData(temp.row, ExcelReadConfig.Employee, temp.dic)).ToList();
            }
            return new List<HandsonRowData>();
        }

        private List<HandsonRowData> GetAccountExtra(per_allot allot, int sheetType)
        {
            List<HandsonRowData> handsonRows = new List<HandsonRowData>();

            if (!CollectDataConfig.AccountExrtaType.Contains(sheetType)) return handsonRows;

            List<im_data> basicData = new List<im_data>();

            var sheet = perforPersheetRepository.GetEntity(w => w.AllotID == allot.ID && w.SheetType == (int)SheetType.AccountBasic);
            if (sheet != null)
            {
                basicData = perforImdataRepository.GetEntities(t => t.SheetID == sheet.ID);
            }
            else
            {
                var allotStates = new int[] { (int)AllotStates.Archive, (int)AllotStates.GenerateSucceed, (int)AllotStates.GenerateAccomplish };
                var allots = perforPerallotRepository.GetEntities(t => t.HospitalId == allot.HospitalId && allotStates.Contains(t.States) && t.Year < allot.Year && t.Month < allot.Month);
                var prevAllot = allots.LastOrDefault();
                if (prevAllot == null) return handsonRows;

                sheet = perforPersheetRepository.GetEntity(w => w.AllotID == prevAllot.ID && w.SheetType == (int)SheetType.AccountBasic);
                basicData = perforImdataRepository.GetEntities(t => t.SheetID == sheet.ID);
            }

            if (basicData == null || !basicData.Any()) return handsonRows;

            var data = basicData.Select(t => new { t.RowNumber, UnitType = ((UnitType)t.UnitType).ToString(), t.AccountingUnit }).ToDistinct().OrderBy(t => t.RowNumber);

            foreach (var item in data)
            {
                List<HandsonCellData> handsonCells = new List<HandsonCellData>();

                handsonCells.Add(new HandsonCellData("核算单元类型", item.UnitType));
                handsonCells.Add(new HandsonCellData("核算单元", item.AccountingUnit));

                handsonRows.Add(new HandsonRowData(item.RowNumber.Value, handsonCells));
            }
            return handsonRows;
        }

        private List<HandsonRowData> GetAccountExtra(per_allot allot, int sheetType, string sheetName)
        {
            List<HandsonRowData> handsonRows = new List<HandsonRowData>();

            var unitTypes = new string[] { UnitType.医生组.ToString(), UnitType.医技组.ToString(), UnitType.护理组.ToString(), UnitType.特殊核算组.ToString() };

            var departments = perforPerdeptdicRepository.GetEntities(t => t.HospitalId == allot.HospitalId && unitTypes.Contains(t.UnitType) && !string.IsNullOrEmpty(t.Department) && t.Department.Replace("无", "").Replace("/", "").Trim() != "");
            if (departments == null || !departments.Any()) return handsonRows;

            var data = departments.GroupBy(t => t.Department).Select(t => new
            {
                Department = t.Key,
                AccountingUnitDoctor = GetAccountingUnit(t.FirstOrDefault(group => group.Source == "门诊" && group.UnitType == UnitType.医生组.ToString())?.AccountingUnit,
                                                      t.FirstOrDefault(group => group.Source == "住院" && group.UnitType == UnitType.医生组.ToString())?.AccountingUnit),
                AccountingUnitTechnician = GetAccountingUnit(t.FirstOrDefault(group => group.Source == "门诊" && group.UnitType == UnitType.医技组.ToString())?.AccountingUnit,
                                                       t.FirstOrDefault(group => group.Source == "住院" && group.UnitType == UnitType.医技组.ToString())?.AccountingUnit),
                AccountingUnitNurse = GetAccountingUnit(t.FirstOrDefault(group => group.Source == "门诊" && group.UnitType == UnitType.护理组.ToString())?.AccountingUnit,
                                                     t.FirstOrDefault(group => group.Source == "住院" && group.UnitType == UnitType.护理组.ToString())?.AccountingUnit),
                AccountingUnitSpecial = t.FirstOrDefault(group => group.UnitType == UnitType.特殊核算组.ToString())?.Department
            }).OrderBy(t => t.Department).ToList();

            int rownumber;
            switch (sheetType)
            {
                case (int)SheetType.OtherIncome:
                case (int)SheetType.Expend:
                    data = data.Where(t => string.IsNullOrEmpty(t.AccountingUnitSpecial))?.ToList();
                    if (data != null && data.Any())
                    {
                        rownumber = 0;
                        foreach (var item in data)
                        {
                            List<HandsonCellData> handsonCells = new List<HandsonCellData>();

                            handsonCells.Add(new HandsonCellData("核算单元（医技组）", item.AccountingUnitTechnician));
                            handsonCells.Add(new HandsonCellData("核算单元（医生组）", item.AccountingUnitDoctor));
                            handsonCells.Add(new HandsonCellData("核算单元（护理组）", item.AccountingUnitNurse));
                            handsonCells.Add(new HandsonCellData("科室名称", item.Department));

                            handsonRows.Add(new HandsonRowData(rownumber, handsonCells));
                            rownumber++;
                        }
                    }
                    break;

                case (int)SheetType.SpecialUnit:
                    var specials = data.Where(t => !string.IsNullOrEmpty(t.AccountingUnitSpecial)).Select(t => t.AccountingUnitSpecial);
                    if (specials != null && specials.Any())
                    {
                        rownumber = 0;
                        foreach (var item in specials)
                        {
                            List<HandsonCellData> handsonCells = new List<HandsonCellData>();

                            handsonCells.Add(new HandsonCellData("科室名称", item));

                            handsonRows.Add(new HandsonRowData(rownumber, handsonCells));
                            rownumber++;
                        }
                    }
                    break;

                case int type when type == (int)SheetType.Workload && sheetName.Contains("医生"):
                    data = data.Where(t => string.IsNullOrEmpty(t.AccountingUnitSpecial) && (!string.IsNullOrEmpty(t.AccountingUnitDoctor) || !string.IsNullOrEmpty(t.AccountingUnitTechnician)))?.ToList();
                    if (data != null && data.Any())
                    {
                        rownumber = 0;
                        foreach (var item in data)
                        {
                            List<HandsonCellData> handsonCells = new List<HandsonCellData>();

                            handsonCells.Add(new HandsonCellData("核算单元", GetAccountingUnit(item.AccountingUnitDoctor, item.AccountingUnitTechnician)));
                            handsonCells.Add(new HandsonCellData("科室名称", item.Department));

                            handsonRows.Add(new HandsonRowData(rownumber, handsonCells));
                            rownumber++;
                        }
                    }
                    break;

                case int type when type == (int)SheetType.Workload && sheetName.Contains("护理"):
                    data = data.Where(t => string.IsNullOrEmpty(t.AccountingUnitSpecial) && !string.IsNullOrEmpty(t.AccountingUnitNurse))?.ToList();
                    if (data != null && data.Any())
                    {
                        rownumber = 0;
                        foreach (var item in data)
                        {
                            List<HandsonCellData> handsonCells = new List<HandsonCellData>();

                            handsonCells.Add(new HandsonCellData("核算单元", item.AccountingUnitNurse));
                            handsonCells.Add(new HandsonCellData("科室名称", item.Department));

                            handsonRows.Add(new HandsonRowData(rownumber, handsonCells));
                            rownumber++;
                        }
                    }
                    break;
            }
            return handsonRows;
        }

        private string GetAccountingUnit(params string[] account)
        {
            if (account == null || !account.Any()) return "";

            return account.FirstOrDefault(t => !string.IsNullOrEmpty(t?.Trim()));
        }

        private HandsonRowData CreateRowData(int row, ColumnInfo[] columns, SortedDictionary<string, object> dic)
        {
            var setdata = from cfg in columns
                          join d in dic.Keys on cfg.Field.ToUpper() equals d.ToUpper()
                          select new HandsonCellData(cfg.Description, dic[d]);
            var xx = new HandsonRowData(row, setdata);
            return xx;
        }

        private List<HandsonRowData> GetAccountBasic(per_sheet sheet, IEnumerable<per_dept_dic> dept, List<HandsonCellData> cells)
        {
            //var accounts = sheetSevice.SheetExport(sheet.ID);
            var result = new List<HandsonRowData>();

            var groupAccount = perforImdataRepository.GetEntities(t => t.SheetID == sheet.ID);  //.OrderBy(c=>c.RowNumber).GroupBy(c => c.RowNumber);
            if (groupAccount != null && groupAccount.Any())
            {
                foreach (var rowNumber in groupAccount.Select(t => t.RowNumber).Distinct())
                {
                    var numberData = groupAccount.Where(t => t.RowNumber == rowNumber);
                    var accountUnit = new HandsonCellData("核算单元", numberData.First().AccountingUnit);

                    var data = numberData?.Select(t => new HandsonCellData(t.TypeName, t.CellValue)).ToList();
                    data.Add(accountUnit);
                    switch (numberData.First().UnitType)
                    {
                        case (int)UnitType.医技组:
                            var unitType = new HandsonCellData("核算单元类型", UnitType.医技组);
                            data.Add(unitType);
                            break;

                        case (int)UnitType.医生组:
                            unitType = new HandsonCellData("核算单元类型", UnitType.医生组);
                            data.Add(unitType);
                            break;

                        case (int)UnitType.护理组:
                            unitType = new HandsonCellData("核算单元类型", UnitType.护理组);
                            data.Add(unitType);
                            break;

                        case (int)UnitType.特殊核算组:
                            unitType = new HandsonCellData("核算单元类型", UnitType.特殊核算组);
                            data.Add(unitType);
                            break;
                    }
                    var rowData = new HandsonRowData(rowNumber.Value, data);
                    result.Add(rowData);

                    string unit = ""; string type = "";
                    foreach (var cell in data)
                    {
                        if (cell.Name == "核算单元")
                        {
                            unit = cell.Value.ToString();
                        }
                        else if (cell.Name == "核算单元类型")
                        {
                            type = cell.Value.ToString();
                        }
                    }
                    cells.Add(new HandsonCellData(unit, type));
                }

                //var temps = accounts.Row.Select(item => new { row = item.Rownumber, dic = JsonHelper.Deserialize(item) });

                //return new List<HandsonRowData>(temps.Select(temp => CreateRowData(temp.row, ExcelReadConfig.AccountBaisc, temp.dic)));
            }
            UnitFit(result, dept, cells);
            return result;
        }

        private void GetUnitClassification(List<HandsonCellData> setdata, UnitType unitType, per_sheet sheet)
        {
            if (sheet.SheetType == (int)SheetType.AccountExtra)
                setdata.Add(new HandsonCellData("核算组别", unitType));
            else if (sheet.SheetType == (int)SheetType.PersonExtra)
                setdata.Add(new HandsonCellData("核算单元分类", unitType));
            else if (sheet.SheetType == (int)SheetType.BudgetRatio)
                setdata.Add(new HandsonCellData("核算单元类型", unitType));
        }

        #endregion 加载采集内容

        public void SaveCollectData(int allotId, SaveCollectData request, int userId, int status = 1)
        {
            var types = new List<int> { (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload, (int)SheetType.SpecialUnit };

            if (!types.Contains(request.SheetType))
                throw new PerformanceException("当前SHEET页信息错误");

            if (request.ColHeaders == null || request.ColHeaders.Length == 0)
                throw new PerformanceException("当前SHEET页列头信息错误");

            if (request.Data == null || request.Data.Length == 0)
                throw new PerformanceException("当前SHEET页列头信息错误");

            Dictionary<string, string> config = CollectDataConfig.Default;

            DateTime date = DateTime.Now;
            var dicData = CreateDataRow(allotId, request, config);
            List<collect_data> datas = new List<collect_data>();
            foreach (var item in dicData)
            {
                var json = JsonHelper.Serialize(item);
                var data = JsonHelper.Deserialize<collect_data>(json);
                if (!string.IsNullOrEmpty(data.AccountingUnitTechnician)
                    || !string.IsNullOrEmpty(data.AccountingUnitNurse)
                    || !string.IsNullOrEmpty(data.AccountingUnitDoctor)
                    || !string.IsNullOrEmpty(data.Department))
                {
                    data.Status = status;
                    data.SavedUser = userId;
                    data.SavedTime = date;
                    if (status == 2)
                        data.SubmitUser = userId;
                    datas.Add(data);
                }
            }
            perforcollectdataRepository.Execute("delete from collect_data where allotid = @allotid and sheetname=@sheetname", new { allotId, request.SheetName });
            perforcollectdataRepository.AddRange(datas.ToArray());
        }

        private List<Dictionary<string, string>> CreateDataRow(int allotId, 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);
                baseData.Add(nameof(collect_data.RowNumber), r.ToString());
                baseData.Add(nameof(collect_data.AllotID), allotId.ToString());
                baseData.Add(nameof(collect_data.SheetName), request.SheetName);

                // 创建动态列数据
                for (int c = 0; c < request.ColHeaders.Length; c++)
                {
                    var header = request.ColHeaders[c];
                    var first = config.FirstOrDefault(w => w.Value == header);

                    Dictionary<string, string> compData = new Dictionary<string, string>(baseData);
                    if (default(KeyValuePair<string, string>).Equals(first)
                        && !compData.ContainsKey(header)
                        && request.Data[r].Length > c)
                    {
                        compData.Add(nameof(collect_data.TypeName), header);
                        compData.Add(nameof(collect_data.CellValue), request.Data[r][c]);
                        allData.Add(compData);
                    }
                }
            }
            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];
                if (request.SheetType == (int)SheetType.Workload && header.Contains("核算单元"))
                {
                    if (request.SheetName.Contains("医生"))
                        header = CollectDataConfig.Default[nameof(collect_data.AccountingUnitDoctor)];
                    else if (request.SheetName.Contains("护理"))
                        header = CollectDataConfig.Default[nameof(collect_data.AccountingUnitNurse)];
                }

                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;
        }

        private void UnitFit(List<HandsonRowData> rowDatas, IEnumerable<per_dept_dic> dept, List<HandsonCellData> cellDatas)
        {
            var rowCount = rowDatas.Count;
            List<HandsonRowData> suppRowDatas = new List<HandsonRowData>();
            foreach (var dic in dept)
            {
                bool exists = false;
                bool exists2 = false;
                foreach (var rowData in rowDatas)
                {
                    foreach (var cellData in rowData.CellData)
                    {
                        //var cell = rowData.CellData.Where(c => c.Value == dic.AccountingUnit).ToList();
                        if (cellData.Value?.ToString() == dic.AccountingUnit)
                        {
                            exists = true;
                        }

                        if (exists && cellData.Value?.ToString() == dic.UnitType)
                        {
                            exists2 = true;
                        }
                    }
                }

                if ((exists == false && exists2 == false) || exists2 == false)
                {
                    cellDatas.Add(new HandsonCellData(dic.AccountingUnit, dic.UnitType));
                    var cells = new List<HandsonCellData>();
                    cells.Add(new HandsonCellData("核算单元", dic.AccountingUnit));
                    cells.Add(new HandsonCellData("核算单元类型", dic.UnitType));
                    suppRowDatas.Add(new HandsonRowData(rowCount++, cells));
                }
            }
            cellDatas.Distinct().ToList();
            rowDatas.AddRange(suppRowDatas);
        }

        private void UnitFit(List<HandsonRowData> rowDatas, List<HandsonCellData> cellDatas, per_sheet sheet)
        {
            var rowCount = rowDatas.Count;
            List<HandsonRowData> suppRowDatas = new List<HandsonRowData>();
            foreach (var cell in cellDatas)
            {
                bool exists = false;
                bool exists2 = false;
                var cells = new List<HandsonCellData>();
                foreach (var rowData in rowDatas)
                {
                    foreach (var cellData in rowData.CellData)
                    {
                        //var cell = rowData.CellData.Where(c => c.Value == dic.AccountingUnit).ToList();

                        if (cellData.Value?.ToString() == cell.Name)
                        {
                            exists = true;

                        }
                        if (cellData.Name == "核算单元分类" && cellData.Value == cell.Value)
                        {
                            exists2 = true;
                        }
                        else if (cellData.Name == "核算组别" && cellData.Value == cell.Value)
                        {
                            exists2 = true;
                        }
                        else if (cellData.Name == "核算单元类型" && cellData.Value == cell.Value)
                        {
                            exists2 = true;
                        }
                        else
                        {
                            exists2 = true;
                        }
                    }
                }

                if (exists == false || (exists == false && exists2 == false) || exists2 == false)
                {

                    cells.Add(new HandsonCellData("核算单元", cell.Name));
                    if (sheet.SheetName == "5.2业务中层行政中高层医院奖罚")
                    {
                        cells.Add(new HandsonCellData("核算单元分类", cell.Value));
                    }
                    else if (sheet.SheetName == "5.1 科室绩效医院奖罚")
                    {
                        cells.Add(new HandsonCellData("核算组别", cell.Value));
                    }
                    else
                    {
                        cells.Add(new HandsonCellData("核算单元类型", cell.Value));
                    }

                    suppRowDatas.Add(new HandsonRowData(++rowCount, cells));
                }
            }
            rowDatas.AddRange(suppRowDatas);
        }

        public void SubmitCollectData(int allotId, string sheetName, int userId)
        {
            var data = perforcollectdataRepository.GetEntities(t => t.AllotID == allotId && t.SheetName == sheetName);
            if (data == null || !data.Any())
                return;

            data.ForEach(t =>
            {
                t.Status = 2;
                t.SubmitUser = userId;
            });
            perforcollectdataRepository.UpdateRange(data.ToArray());
        }

        /// <summary>
        /// 数据录入显示列表
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public object CollectDataList(int hospitalId)
        {
            var allots = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId);
            if (allots == null || !allots.Any())
                return new List<per_allot>();

            var data = perforcollectdataRepository.GetEntities(t => allots.Select(a => a.ID).Contains(t.AllotID));


            allots = allots.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).ToList();
            return allots.Select(t => new
            {
                t.ID,
                t.Year,
                t.Month,
                Status = GetStatus(data?.Where(s => s.AllotID == t.ID))
            });
        }

        private int GetStatus(IEnumerable<collect_data> data)
        {
            if (data == null || !data.Any())
                return 1;

            if (data.Count(t => t.Status == 3) == data.Count())
                return 3;

            else if (data.Any(t => t.Status == 4))
                return 4;

            else if (data.Any(t => t.Status == 2))
                return 2;

            return 1;
        }

        /// <summary>
        /// 核算办显示列表
        /// </summary>
        /// <returns></returns>
        public dynamic PerformanceforList(int allotId)
        {
            var data = perforcollectdataRepository.GetEntities(t => t.AllotID == allotId && t.SubmitUser.HasValue);
            if (data == null || !data.Any())
                return new int[] { };

            var users = userRepository.GetEntities();
            var list = data.GroupBy(t => new { t.SubmitUser }).Select(t => new
            {
                UserId = t.Key.SubmitUser,
                User = users.FirstOrDefault(u => u.ID == t.Key.SubmitUser)?.RealName ?? "",
                Total = t.Sum(s => ConvertHelper.To<decimal>(s.CellValue)),
                Status = (t.Where(g => g.Status == 3)?.Count() ?? 0) == t.Count() ? 3
                : (t.Where(g => g.Status == 4)?.Count() ?? 0) == t.Count() ? 4
                : 2
            });
            return list;
        }

        /// <summary>
        /// 科室显示列表
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public SheetExportResponse DepartmentTable(int allotId, int userId)
        {
            SheetExportResponse sheet = new SheetExportResponse();

            var data = perforcollectdataRepository.GetEntities(t => t.AllotID == allotId && t.SubmitUser == userId);
            if (data == null || !data.Any())
                return sheet;

            data.ForEach(t =>
            {
                t.AccountingUnitDoctor = t.AccountingUnitDoctor ?? "";
                t.AccountingUnitNurse = t.AccountingUnitNurse ?? "";
                t.AccountingUnitTechnician = t.AccountingUnitTechnician ?? "";
            });

            #region header

            var mergeCells = new List<Cell>
            {
                new Cell{ CellType = "header", CellValue = "", PointCell = 0, MergeRow = 1, MergeCell = 1 },
                new Cell{ CellType = "header", CellValue = "", PointCell = 1, MergeRow = 1, MergeCell = 1 },
                new Cell{ CellType = "header", CellValue = "", PointCell = 2, MergeRow = 1, MergeCell = 1 },
                new Cell{ CellType = "header", CellValue = "", PointCell = 3, MergeRow = 1, MergeCell = 1 },
            };
            var cells = new List<Cell>
            {
                new Cell{ CellType = "header", CellValue = "核算单元（医技组）", PointCell = 0, MergeRow = 1, MergeCell = 1 },
                new Cell{ CellType = "header", CellValue = "核算单元（医生组）", PointCell = 1, MergeRow = 1, MergeCell = 1 },
                new Cell{ CellType = "header", CellValue = "核算单元（护理组）", PointCell = 2, MergeRow = 1, MergeCell = 1 },
                new Cell{ CellType = "header", CellValue = "科室名称", PointCell = 3, MergeRow = 1, MergeCell = 1 },
            };

            int index = 4;
            var sheetnames = data.Select(t => t.SheetName).Distinct().OrderBy(t => t);
            foreach (var sheetname in sheetnames)
            {
                var sheetColumns = data.Where(t => t.SheetName == sheetname).Select(t => t.TypeName).Distinct().OrderBy(t => t);
                mergeCells.Add(new Cell { CellType = "header", CellValue = sheetname, PointCell = index, MergeRow = 1, MergeCell = sheetColumns.Count() });

                int i = 0;
                cells.AddRange(sheetColumns.Select(t =>
                {
                    i++;
                    return new Cell
                    {
                        CellType = "header",
                        CellValue = t,
                        PointCell = index + i,
                        MergeRow = 1,
                        MergeCell = 1
                    };
                }));
                index += sheetColumns.Count();
            }

            sheet.Header = new List<Row>
            {
                new Row(0){ Data = mergeCells },
                new Row(1){ Data = cells }
            };

            #endregion

            #region data

            List<Row> rows = new List<Row>();
            int rownumber = 0;
            var groupData = data.GroupBy(t => new { t.AccountingUnitTechnician, t.AccountingUnitDoctor, t.AccountingUnitNurse, t.Department });
            foreach (var item in groupData.OrderBy(t => t.Key.Department))
            {
                var rowcells = new List<Cell>
                {
                    new Cell { CellType = "body", CellValue = item.Key.AccountingUnitTechnician, PointCell = 0, MergeRow = 1, MergeCell = 1 },
                    new Cell { CellType = "body", CellValue = item.Key.AccountingUnitDoctor, PointCell = 1, MergeRow = 1, MergeCell = 1 },
                    new Cell { CellType = "body", CellValue = item.Key.AccountingUnitNurse, PointCell = 2, MergeRow = 1, MergeCell = 1 },
                    new Cell { CellType = "body", CellValue = item.Key.Department, PointCell = 1, MergeRow = 3, MergeCell = 1 },
                };

                index = 4;
                foreach (var header in cells.Select(t => t.CellValue.ToString()))
                {
                    var value = item.FirstOrDefault(t => t.CellValue == header)?.CellValue;
                    rowcells.Add(new Cell
                    {
                        CellType = "body",
                        CellValue = value,
                        PointCell = index,
                        MergeRow = 1,
                        MergeCell = 1
                    });
                    index++;
                }
                rows.Add(new Row(rownumber) { Data = rowcells });
                rownumber++;
            }

            sheet.Row = rows;

            #endregion

            return sheet;
        }

        /// <summary>
        /// 科室显示列表
        /// </summary>
        /// <returns></returns>
        public HandsonTableBase DepartmentList(int allotId, int userId)
        {
            HandsonTableBase table = new HandsonTableBase();
            var data = perforcollectdataRepository.GetEntities(t => t.AllotID == allotId && t.SubmitUser == userId);
            if (data == null || !data.Any())
                return table;

            data.ForEach(t =>
            {
                t.AccountingUnitDoctor = t.AccountingUnitDoctor ?? "";
                t.AccountingUnitNurse = t.AccountingUnitNurse ?? "";
                t.AccountingUnitTechnician = t.AccountingUnitTechnician ?? "";
            });

            #region headers columns

            var sheets = data.Select(t => t.SheetName).Distinct().OrderBy(t => t).ToList();
            var mergeCells = new List<NestedHeader>
            {
                new NestedHeader("", 1),
                new NestedHeader("", 1),
                new NestedHeader("", 1),
                new NestedHeader("", 1),
            };

            var cells = new List<string>
            {
                "核算单元（医技组）",
                "核算单元（医生组）",
                "核算单元（护理组）",
                "科室名称",
            };
            var headers = new List<(string, string)>();

            var columns = new List<HandsonColumn>
            {
                new HandsonColumn("核算单元（医技组）", true),
                new HandsonColumn("核算单元（医生组）", true),
                new HandsonColumn("核算单元（护理组）", true),
                new HandsonColumn("科室名称", true),
            };
            foreach (var sheet in sheets)
            {
                var sheetColumns = data.Where(t => t.SheetName == sheet).Select(t => t.TypeName).Distinct().OrderBy(t => t).ToList();
                if (new string[] { "3.1", "3.2" }.Any(t => sheet.StartsWith(t)) && sheetColumns.Contains("核算单元"))
                {
                    sheetColumns.Remove("核算单元");
                }
                cells.AddRange(sheetColumns);
                headers.AddRange(sheetColumns.Select(t => (sheet, t)));
                mergeCells.Add(new NestedHeader(sheet, sheetColumns.Count()));
                columns.AddRange(sheetColumns.Select(t => new HandsonColumn(sheet + "_" + t, true, DataFormat.小数)));
            }

            #endregion

            #region data

            List<Dictionary<string, object>> tableData = new List<Dictionary<string, object>>();

            var groupData = data.GroupBy(t => new { t.AccountingUnitTechnician, t.AccountingUnitDoctor, t.AccountingUnitNurse, t.Department });
            foreach (var item in groupData.OrderBy(t => t.Key.Department))
            {
                var dic = new Dictionary<string, object>
                {
                    { "核算单元（医技组）", item.Key.AccountingUnitTechnician },
                    { "核算单元（医生组）", item.Key.AccountingUnitDoctor },
                    { "核算单元（护理组）", item.Key.AccountingUnitNurse },
                    { "科室名称", item.Key.Department }
                };

                foreach (var header in headers)
                {
                    var key = header.Item1 + "_" + header.Item2;
                    if (dic.ContainsKey(key)) continue;

                    var value = item.FirstOrDefault(t => t.SheetName == header.Item1 && t.TypeName == header.Item2)?.CellValue;
                    dic.Add(key, value);
                }
                tableData.Add(dic);
            }

            #endregion

            table.Columns = columns;
            table.NestedHeadersArray = new object[] { mergeCells, cells };
            table.ColHeaders = cells;
            table.Data = tableData;

            return table;
        }

        /// <summary>
        /// 提交数据审核
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="userId"></param>
        /// <param name="status"></param>
        public bool CollectDataAudit(int allotId, int userId, int status)
        {
            var data = perforcollectdataRepository.GetEntities(t => t.AllotID == allotId && t.SubmitUser == userId);
            if (data == null || !data.Any())
                return true;

            data.ForEach(t =>
            {
                t.Status = status;
            });
            return perforcollectdataRepository.UpdateRange(data.ToArray());
        }
    }

    public class CollectDataConfig
    {
        public static Dictionary<string, string> Employee { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "人员分类" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元" },
            { nameof(collect_data.Department), "核算单元" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "医生姓名" },
        };

        public static Dictionary<string, string> ClinicEmployee { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "核算单元分类" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元" },
            { nameof(collect_data.Department), "核算单元" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "医生姓名" },
        };

        public static Dictionary<string, string> LogisticsEmployee { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "人员分类" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元" },
            { nameof(collect_data.Department), "核算单元" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "人员姓名" },
        };

        public static Dictionary<string, string> PersonExtra { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "核算单元分类" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元" },
            { nameof(collect_data.Department), "核算单元" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "医生姓名" },
        };

        public static Dictionary<string, string> AccountExtra { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "核算单元类型" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元" },
            { nameof(collect_data.Department), "核算单元" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "医生姓名" },
        };

        public static Dictionary<string, string> AccountBasic { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "核算单元分类" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元" },
            { nameof(collect_data.Department), "核算单元" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "医生姓名" },
        };

        public static Dictionary<string, string> Default { get; } = new Dictionary<string, string>
        {
            { nameof(collect_data.UnitType), "核算单元分类" },
            { nameof(collect_data.AccountingUnitTechnician), "核算单元（医技组）" },
            { nameof(collect_data.AccountingUnitDoctor), "核算单元（医生组）" },
            { nameof(collect_data.AccountingUnitNurse), "核算单元（护理组）" },
            { nameof(collect_data.Department), "科室名称" },
            { nameof(collect_data.JobNumber), "人员工号" },
            { nameof(collect_data.EmployeeName), "医生姓名" },
        };

        public static List<int> AccountExrtaType = new List<int>
        {
                (int)SheetType.AccountExtra,
                (int)SheetType.AccountDrugAssess,
                (int)SheetType.AccountMaterialsAssess,
                (int)SheetType.AccountScoreAverage,
                (int)SheetType.BudgetRatio,
                (int)SheetType.AssessBeforeOtherFee,
                (int)SheetType.AccountAdjustLaterOtherFee,
                (int)SheetType.WorkloadMedicineProp,
                (int)SheetType.WorkloadCMI,
                (int)SheetType.WorkloadIncline,
        };
    }
}
