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;

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 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,
            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.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.Employee, (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload,
                (int)SheetType.ClinicEmployee, (int)SheetType.AccountExtra, (int)SheetType.PersonExtra, (int)SheetType.LogisticsEmployee,
                (int)SheetType.AccountDrugAssess, (int)SheetType.AccountMaterialsAssess, (int)SheetType.AccountScoreAverage,
                (int)SheetType.BudgetRatio, (int)SheetType.AssessBeforeOtherFee };

            var sheets = perforPersheetRepository.GetEntities(w => w.AllotID == allot.ID && types.Contains((w.SheetType ?? 0)));
            var headers = perforImheaderRepository.GetEntities(w => w.AllotID == allot.ID);
            var permissions = perforcollectpermissionRepository.GetEntities(w => w.HospitalId == hospitalId && w.UserId == userId);

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

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

            // 查询sheet和列头对应
            var sheetHeads = from sheet in sheets
                             join head in headers on sheet.ID equals head.SheetID
                             select new { sheet.SheetName, sheet.SheetType, HeadName = head.CellValue };

            List<CollectPermission> result;
            // 查询列头和权限对应
            if (permissions == null)
            {
                result = sheetHeads.Select(sh => new CollectPermission { HospitalId = hospitalId, SheetName = sh.SheetName, SheetType = sh.SheetType.Value, HeadName = sh.HeadName })?.ToList();
            }
            else
            {
                result = (from sh in sheetHeads
                          join pmss in permissions on new { sh.SheetName, sh.HeadName } equals new { pmss.SheetName, pmss.HeadName }
                          into emppmss
                          from item in emppmss.DefaultIfEmpty()
                          select new CollectPermission
                          {
                              HospitalId = hospitalId,
                              SheetName = sh.SheetName,
                              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 data2 = collects.Where(w => !w.PermissionId.HasValue && (w.Visible > 0 || w.AttachLast > 0 || w.Readnoly > 0));
            var inserts = data2?.Select(w => new collect_permission
            {
                UserId = userid,
                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))
            {
                Expression<Func<collect_permission, bool>> exp = (w) => w.HospitalId == hospitalId && w.UserId == userId;
                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.Employee, (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload,
                (int)SheetType.ClinicEmployee, (int)SheetType.AccountExtra, (int)SheetType.PersonExtra, (int)SheetType.LogisticsEmployee,
                (int)SheetType.AccountDrugAssess, (int)SheetType.AccountMaterialsAssess, (int)SheetType.AccountScoreAverage,
                (int)SheetType.BudgetRatio, (int)SheetType.AssessBeforeOtherFee,(int)SheetType.AccountBasic };

            var sheets = perforPersheetRepository.GetEntities(w => w.AllotID == allot.ID && types.Contains((w.SheetType ?? 0)));
            var headers = perforImheaderRepository.GetEntities(w => w.AllotID == allot.ID);

            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.SheetName == sheetName)?.ToList();
            }
            var permissions = perforcollectpermissionRepository.GetEntities(exp);

            // 查询列头和权限对应
            if (permissions == null)
            {
                if (sheets == null || !sheets.Any())
                    return null;

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

                // 查询sheet和列头对应
                var sheetHeads = from sheet in sheets
                                 join head in headers on sheet.ID equals head.SheetID
                                 select new { sheet.SheetName, sheet.SheetType, HeadName = head.CellValue };

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

                //if (result != null && result.Any())
                //{
                //    result.ForEach(w =>
                //    {
                //        var criterion = retain.Any(r => r == w.HeadName);
                //        w.Readnoly = criterion ? 1 : w.Readnoly;
                //    });
                return result;
                //}
            }

            return permissions;
        }

        #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.Employee, (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload,
                (int)SheetType.ClinicEmployee, (int)SheetType.AccountExtra, (int)SheetType.PersonExtra, (int)SheetType.LogisticsEmployee,
                (int)SheetType.AccountDrugAssess, (int)SheetType.AccountMaterialsAssess, (int)SheetType.AccountScoreAverage,
                (int)SheetType.BudgetRatio, (int)SheetType.AssessBeforeOtherFee,(int)SheetType.AccountBasic};

            var sheet = perforPersheetRepository
                .GetEntity(w => w.AllotID == allot.ID && w.SheetName == sheetName && types.Contains((w.SheetType ?? 0)));

            if (sheet == null)
                throw new PerformanceException("找不到当前SHEET页信息");

            var headers = perforImheaderRepository.GetEntities(w => w.SheetID == sheet.ID)?.OrderBy(w => w.PointCell);
            if (headers == null || !headers.Any())
                throw new PerformanceException("SHEET页没有有效列头");
            var cols = headers.Select(w => w.CellValue).ToArray();
            var permissions = GetCollectSheet(allot.HospitalId, userId, sheetName)?.ToList();
            if (permissions == null || !permissions.Any())
                throw new PerformanceException("用户没有权限编辑当前SHEET页");

            List<HandsonRowData> rowDatas = null;
            var collectdata = perforcollectdataRepository.GetEntities(w => w.SheetName == sheet.SheetName && w.AllotID == allotId);
            if (collectdata != null)
            {
                Dictionary<string, string> config = null;
                if (sheet.SheetType == (int)SheetType.Employee)
                    config = CollectDataConfig.Employee;
                else if (sheet.SheetType == (int)SheetType.ClinicEmployee)
                    config = CollectDataConfig.ClinicEmployee;
                else if (sheet.SheetType == (int)SheetType.LogisticsEmployee)
                    config = CollectDataConfig.LogisticsEmployee;
                else if (sheet.SheetType == (int)SheetType.PersonExtra)
                    config = CollectDataConfig.PersonExtra;
                else if (sheet.SheetType == (int)SheetType.AccountBasic)
                    config = CollectDataConfig.AccountBasic;
                else if (CollectDataConfig.AccountExrtaType.Contains(sheet.SheetType.Value))
                    config = CollectDataConfig.AccountExtra;
                else
                    config = CollectDataConfig.Default;

                rowDatas = new List<HandsonRowData>();
                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));
                }
            }
            else
            {
                if (sheet.SheetType == (int)SheetType.Employee)
                    rowDatas = GerEmployee(sheet);
                else if (sheet.SheetType == (int)SheetType.ClinicEmployee)
                    rowDatas = GetClinicEmployee(sheet);
                else if (sheet.SheetType == (int)SheetType.LogisticsEmployee)
                    rowDatas = GetLogisticsEmployee(sheet);
                else if (sheet.SheetType == (int)SheetType.AccountBasic)
                {
                    List<HandsonCellData> cells = new List<HandsonCellData>();
                    var dept = perforPerdeptdicRepository.GetAccountBasicAccountingUnit(allot.HospitalId);
                    rowDatas = GetAccountBasic(sheet, dept, cells);
                }
                else
                    rowDatas = GetCommonData(sheet);
            }

            //补全核算单元和类型
            var sTypes = new[] { (int)SheetType.AccountExtra, (int)SheetType.PersonExtra, (int)SheetType.AccountDrugAssess, (int)SheetType.AccountMaterialsAssess };
            var sTypeName = new[] { "6.1目标考核", "6.6 科室预算比例" };
            if (sTypes.Contains((int)sheet.SheetType) || sTypeName.Contains(sheet.SheetName))
            {
                List<HandsonCellData> cells = new List<HandsonCellData>();
                var perSheet = perforPersheetRepository.GetEntity(t => t.AllotID == allotId && t.SheetName == "4.1 临床科室医护绩效测算表");
                var dept = perforPerdeptdicRepository.GetAccountBasicAccountingUnit(allot.HospitalId);
                GetAccountBasic(perSheet, dept, cells);
                UnitFit(rowDatas, cells, sheet);
            }

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

            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));
            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 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)
        {
            var types = new List<int> { (int)SheetType.Employee, (int)SheetType.OtherIncome, (int)SheetType.Expend, (int)SheetType.Workload,
                (int)SheetType.ClinicEmployee, (int)SheetType.AccountExtra, (int)SheetType.PersonExtra, (int)SheetType.LogisticsEmployee};

            types.AddRange(CollectDataConfig.AccountExrtaType);

            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 = null;
            if (request.SheetType == (int)SheetType.Employee)
                config = CollectDataConfig.Employee;
            else if (request.SheetType == (int)SheetType.ClinicEmployee)
                config = CollectDataConfig.ClinicEmployee;
            else if (request.SheetType == (int)SheetType.LogisticsEmployee)
                config = CollectDataConfig.LogisticsEmployee;
            else if (request.SheetType == (int)SheetType.PersonExtra)
                config = CollectDataConfig.PersonExtra;
            else if (CollectDataConfig.AccountExrtaType.Contains(request.SheetType))
                config = CollectDataConfig.AccountExtra;
            else
                config = CollectDataConfig.Default;

            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))
                    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];
                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 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,
        };
    }
}
