using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Linq;
using Performance.DtoModels;
using Performance.DtoModels.Request;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using Performance.Services.ExtractExcelService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace Performance.Services
{
    public class ReportService : IAutoInjection
    {
        private readonly ILogger<ReportService> logger;
        private readonly PerforReportRepository perforReportRepository;
        private readonly PerforPerallotRepository perforPerallotRepository;
        private readonly PerforResbaiscnormRepository perforResbaiscnormRepository;
        private readonly PerforHospersonfeeRepository perforHospersonfeeRepository;
        private readonly PerforResaccountRepository perforResaccountRepository;
        private readonly PerforRepimportconfigRepository repimportconfigRepository;
        private readonly PerforRepreportRepository repreportRepository;

        public ReportService(
            ILogger<ReportService> logger,
            PerforReportRepository perforReportRepository,
            PerforPerallotRepository perforPerallotRepository,
            PerforResbaiscnormRepository perforResbaiscnormRepository,
            PerforHospersonfeeRepository perforHospersonfeeRepository,
            PerforRepimportconfigRepository repimportconfigRepository,
            PerforResaccountRepository perforResaccountRepository,
            PerforRepreportRepository repreportRepository)
        {
            this.logger = logger;
            this.perforReportRepository = perforReportRepository;
            this.perforPerallotRepository = perforPerallotRepository;
            this.perforResbaiscnormRepository = perforResbaiscnormRepository;
            this.perforHospersonfeeRepository = perforHospersonfeeRepository;
            this.perforResaccountRepository = perforResaccountRepository;
            this.repimportconfigRepository = repimportconfigRepository;
            this.repreportRepository = repreportRepository;
        }

        /// <summary>
        /// 月群体人均绩效
        /// </summary>
        /// <returns></returns>
        public List<PerReport> GetAvgPerfor(int hospitalid)
        {
            return perforReportRepository.GetAvgPerfor(hospitalid);
        }

        /// <summary>
        /// 人群绩效比
        /// </summary>
        /// <returns></returns>
        public List<PerReport> AvgRatio(int hospitalid)
        {
            return perforReportRepository.AvgRatio(hospitalid);
        }

        /// <summary>
        /// 首页数据概况
        /// </summary>
        /// <param name="request"></param>
        public dynamic Survey(int hospitalId)
        {
            var states = new List<int>() { 6, 8 };
            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
            if (allotList == null || !allotList.Any())
                throw new PerformanceException("用户未创建绩效！");

            var allot = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).FirstOrDefault();
            var keyvalue = new Dictionary<string, decimal>();
            var basicList = perforResbaiscnormRepository.GetEntities(t => t.AllotID == allot.ID && t.PositionName.IndexOf("保底") < 0);
            if (basicList != null)
            {
                foreach (var basic in basicList)
                {
                    keyvalue.Add(basic.PositionName, Math.Round(basic.AvgValue.Value, 2));
                }
            }
            return new
            {
                year = allot.Year,
                month = allot.Month,
                avgperfor = keyvalue
            };
        }

        /// <summary>
        /// 科室医生人均绩效（含科主任）
        /// </summary>
        /// <returns></returns>
        public List<PerReport> DoctorAvg(int hospitalId, int isIndex)
        {
            var states = new List<int>() { 6, 8 };
            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
            if (allotList == null || !allotList.Any())
                throw new PerformanceException("用户未创建绩效！");

            var result = new List<PerReport>();
            var doctorList = perforResaccountRepository.GetEntities(t => t.UnitType != (int)UnitType.护理组 && allotList.Select(a => a.ID).Contains(t.AllotID.Value));
            if (isIndex == 1)
            {
                var allot = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).FirstOrDefault();
                doctorList = perforResaccountRepository.GetEntities(t => t.UnitType != (int)UnitType.护理组 && t.AllotID == allot.ID);
            }
            if (doctorList == null)
                return result;
            var s = from a in allotList
                    join d in doctorList on a.ID equals d.AllotID into data
                    from t in data
                    select new PerReport
                    {
                        Y = a.Year + "-" + a.Month.ToString().PadLeft(2, '0'),
                        X = t.AccountingUnit,
                        Value = Math.Round(t.Avg.Value, 2)
                    };
            return s.OrderBy(t => t.Y).ThenByDescending(t => t.Value).ToList();
        }

        /// <summary>
        /// 科室护理人均绩效（含护士长）
        /// </summary>
        /// <returns></returns>
        public List<PerReport> NurseAvg(int hospitalId, int isIndex)
        {
            var states = new List<int>() { 6, 8 };
            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
            if (allotList == null || !allotList.Any())
                throw new PerformanceException("用户未创建绩效！");

            var result = new List<PerReport>();
            var doctorList = perforResaccountRepository.GetEntities(t => t.UnitType == (int)UnitType.护理组 && allotList.Select(a => a.ID).Contains(t.AllotID.Value));
            if (isIndex == 1)
            {
                var allot = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).FirstOrDefault();
                doctorList = perforResaccountRepository.GetEntities(t => t.UnitType == (int)UnitType.护理组 && t.AllotID == allot.ID);
            }
            if (doctorList == null)
                return result;
            var s = from a in allotList
                    join d in doctorList on a.ID equals d.AllotID into data
                    from t in data
                    select new PerReport
                    {
                        Y = a.Year + "-" + a.Month.ToString().PadLeft(2, '0'),
                        X = t.AccountingUnit,
                        Value = Math.Round(t.Avg.Value, 2)
                    };
            return s.OrderBy(t => t.Y).ThenByDescending(t => t.Value).ToList();
        }

        /// <summary>
        /// 门诊患者均次费用
        /// </summary>
        /// <returns></returns>
        public List<PerReport> OutFeeAvg(int hospitalId)
        {
            var states = new List<int>() { 6, 8 };
            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
            if (allotList == null || !allotList.Any())
                throw new PerformanceException("用户未创建绩效！");

            var date = new List<string>();
            date = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).Take(6).Select(t => t.Year + "-" + t.Month.ToString().PadLeft(2, '0')).ToList();
            return perforReportRepository.OutFeeAvg(hospitalId, date);
        }

        /// <summary>
        /// 住院患者均次费用
        /// </summary>
        /// <returns></returns>
        public List<PerReport> InpatFeeAvg(int hospitalId)
        {
            var states = new List<int>() { 6, 8 };
            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
            if (allotList == null || !allotList.Any())
                throw new PerformanceException("用户未创建绩效！");

            var date = new List<string>();
            date = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).Take(6).Select(t => t.Year + "-" + t.Month.ToString().PadLeft(2, '0')).ToList();
            return perforReportRepository.InpatFeeAvg(hospitalId, date);
        }

        ///// <summary>
        ///// 科室药占比
        ///// </summary>
        ///// <returns></returns>
        //public List<PerReport> Medicine(int hospitalId, int isIndex)
        //{
        //    var states = new List<int>() { 6, 8 };
        //    var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
        //    if (allotList == null || !allotList.Any())
        //        throw new PerformanceException("用户未创建绩效！");

        //    var date = new List<string>();
        //    if (isIndex == 1)
        //    {
        //        var allot = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).FirstOrDefault();
        //        date.Add(allot.Year + "-" + allot.Month.ToString().PadLeft(2, '0'));
        //    }
        //    else
        //    {
        //        date = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).Take(6).Select(t => t.Year + "-" + t.Month.ToString().PadLeft(2, '0')).ToList();
        //    }
        //    return perforReportRepository.Medicine(hospitalId, date);
        //}

        ///// <summary>
        ///// 科室有效收入占比
        ///// </summary>
        ///// <returns></returns>
        //public List<PerReport> Income(int hospitalId, int isIndex)
        //{
        //    var states = new List<int>() { 6, 8 };
        //    var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
        //    if (allotList == null || !allotList.Any())
        //        throw new PerformanceException("用户未创建绩效！");

        //    var date = new List<string>();
        //    if (isIndex == 1)
        //    {
        //        var allot = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).FirstOrDefault();
        //        date.Add(allot.Year + "-" + allot.Month.ToString().PadLeft(2, '0'));
        //    }
        //    else
        //    {
        //        date = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).Take(6).Select(t => t.Year + "-" + t.Month.ToString().PadLeft(2, '0')).ToList();
        //    }
        //    return perforReportRepository.Income(hospitalId, date);
        //}

        /// <summary>
        /// 只支持EXCEL抽取报表数据
        /// </summary>
        /// <param name="allot"></param>
        /// <returns></returns>
        internal int ImportData(per_allot allot)
        {
            Dictionary<string, object> pairs = new Dictionary<string, object>
            {
                { "@allotid", allot.ID },
                { "@hospitalid", allot.HospitalId },
                { "@year", allot.Year },
                { "@month", allot.Month },
            };
            var imports = repimportconfigRepository.GetEntities(w => w.ScriptType == 2);
            foreach (var item in imports)
            {
                try
                {
                    var flag = perforPerallotRepository.ImportData(item, pairs);
                }
                catch (Exception ex)
                {
                    logger.LogError(ex.ToString());
                }
            }
            return 0;
        }

        /// <summary>
        /// 补充报表数据
        /// </summary>
        /// <param name="allot"></param>
        /// <returns></returns>
        internal int UpdateData(per_allot allot)
        {
            Dictionary<string, object> pairs = new Dictionary<string, object>
            {
                { "@allotid", allot.ID },
                { "@hospitalid", allot.HospitalId },
                { "@year", allot.Year },
                { "@month", allot.Month },
            };
            var imports = repimportconfigRepository.GetEntities(w => w.ScriptType == 3);
            foreach (var item in imports)
            {
                try
                {
                    var flag = perforPerallotRepository.UpdateData(item, pairs);
                }
                catch (Exception ex)
                {
                    logger.LogError(ex.ToString());
                }
            }
            return 0;
        }

        /// <summary>
        /// 首页
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public List<PerReport> IndexReport(int hospitalId, string source)
        {
            var states = new List<int>() { 6, 8 };
            var allotList = perforPerallotRepository.GetEntities(t => t.HospitalId == hospitalId && states.Contains(t.States));
            if (allotList == null || !allotList.Any())
                throw new PerformanceException("用户未创建绩效！");

            var allot = allotList.OrderByDescending(t => t.Year).ThenByDescending(t => t.Month).FirstOrDefault();
            var currentDate = allot.Year + "-" + allot.Month.ToString().PadLeft(2, '0');    //本月
            var yoyDate = (allot.Year - 1) + "-" + allot.Month.ToString().PadLeft(2, '0');  //同比
            var chainDate = allot.Year + "-" + (allot.Month - 1).ToString().PadLeft(2, '0');    //环比

            var report = new List<PerReport>();
            switch (source)
            {
                case "医院收入结构占比":
                    report = perforReportRepository.InHosIncome(hospitalId, currentDate);
                    break;

                case "绩效发放金额":
                    report = perforReportRepository.PerforPayment(hospitalId, currentDate, yoyDate, chainDate);
                    break;

                case "绩效发放金额占全院收入占比":
                    report = perforReportRepository.IndexPerforRatio(hospitalId, currentDate, yoyDate, chainDate);
                    break;

                case "药占比":
                    report = perforReportRepository.IndexDrugRatio(hospitalId, currentDate, yoyDate, chainDate);
                    break;

                case "材料占比":
                    report = perforReportRepository.IndexMaterialRatio(hospitalId, currentDate, yoyDate, chainDate);
                    break;

                case "结构占比":
                    report = perforReportRepository.IndexStructRatio(hospitalId, currentDate);
                    break;
            }

            return report;
        }

        /// <summary>
        /// 菜单
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public List<PerReport> MenuReport(ReportRequest request)
        {
            var report = new List<PerReport>();
            switch (request.Source)
            {
                case "业务总收入":
                    report = perforReportRepository.GeneralIncome(request);
                    break;

                case "门诊住院业务收入占比":
                    report = perforReportRepository.InHosIncome(request);
                    break;

                case "业务收入结构占比":
                    report = perforReportRepository.StructRatio(request);
                    break;

                case "药占比":
                    report = perforReportRepository.DrugRatio(request);
                    break;

                case "材料占比":
                    report = perforReportRepository.MaterialRatio(request);
                    break;

                case "绩效发放金额占全院收入占比":
                    report = perforReportRepository.PerforRatio(request);
                    break;

                case "绩效群体收入":
                    report = perforReportRepository.PerforGroup(request);
                    break;

                case "医生核算单元人均绩效":
                    report = perforReportRepository.DoctorAvg(request);
                    break;

                case "护理核算单元人均绩效":
                    report = perforReportRepository.NurseAvg(request);
                    break;
            }

            return report;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="allot"></param>
        /// <returns></returns>
        public void ExecProc(string execsql, object param)
        {
            try
            {
                perforPerallotRepository.Execute(execsql, param, 60 * 60);
            }
            catch (Exception ex)
            {
                logger.LogError($"执行存储过程时发生异常，sql：{execsql}；参数：{JsonHelper.Serialize(param)}；异常：{ex.Message}；");
            }
        }

        public SheetExportResponse Operation(ReportRequest request)
        {
            SheetExportResponse sheet = new SheetExportResponse();

            #region data

            IEnumerable<int> years = new int[] { };
            if (string.IsNullOrEmpty(request.Year))
            {
                string getYearsSql = "select max(`year`) `year` from view_operation_report_result where hospitalid = @hospitalid;";
                years = perforReportRepository.DapperQuery<int?>(getYearsSql, new { hospitalid = request.HospitalId })?.Select(t => t ?? 0);
            }
            else
            {
                years = Array.ConvertAll(request.Year.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries), t => ConvertHelper.To<int>(t));
            }

            if (years == null || !years.Any()) return sheet;

            string sql = $"select * from view_operation_report_result where hospitalid = @hospitalid and year in @year";
            if (request.AccountingUnit != null && request.AccountingUnit.Any())
            {
                sql += " and accountingunit in @accountingunit";
            }
            var data = perforReportRepository.DapperQuery<view_operation_report_result>(sql, new { hospitalId = request.HospitalId, year = years, accountingunit = request.AccountingUnit })?.ToList();

            #endregion

            years = data.Select(t => t.Year).Distinct().OrderByDescending(t => t);
            int index = 0;

            #region header

            var cells = new List<Cell>
            {
                new Cell{ CellType = "header", CellValue = "经济指标", PointCell = 0, MergeRow = 1, MergeCell = 3 },
            };

            for (int i = 1; i < 13; i++)
            {
                cells.Add(new Cell { CellType = "header", CellValue = $"{i}月", PointCell = i + 2, MergeRow = 1, MergeCell = 1 });
            }

            //foreach (var year in years)
            //{
            //    for (int i = 1; i < 13; i++)
            //    {
            //        cells.Add(new Cell { CellType = "header", CellValue = $"{i}月", PointCell = i + 2, MergeRow = 1, MergeCell = 1 });
            //    }
            //}

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

            #endregion

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

            var group = data.GroupBy(t => new { t.SourceType, t.Category, t.ItemName });
            data = data.GroupBy(t => new { t.Year, t.SourceType, t.Category, t.ItemName }).Select(t => new view_operation_report_result
            {
                Year = t.Key.Year,
                SourceType = t.Key.SourceType,
                Category = t.Key.Category,
                ItemName = t.Key.ItemName,
                January = t.Sum(g => g.January ?? 0),
                February = t.Sum(g => g.February ?? 0),
                March = t.Sum(g => g.March ?? 0),
                April = t.Sum(g => g.April ?? 0),
                May = t.Sum(g => g.May ?? 0),
                June = t.Sum(g => g.June ?? 0),
                July = t.Sum(g => g.July ?? 0),
                August = t.Sum(g => g.August ?? 0),
                September = t.Sum(g => g.September ?? 0),
                October = t.Sum(g => g.October ?? 0),
                November = t.Sum(g => g.November ?? 0),
                December = t.Sum(g => g.December ?? 0)
            }).ToList();

            var sourcetypes = data.Select(t => t.SourceType).Distinct();

            #region body

            Dictionary<int, Func<view_operation_report_result, object>> dict = new Dictionary<int, Func<view_operation_report_result, object>>
            {
                { 1, t => t.January },
                { 2, t => t.February },
                { 3, t => t.March },
                { 4, t => t.April },
                { 5, t => t.May },
                { 6, t => t.June },
                { 7, t => t.July },
                { 8, t => t.August },
                { 9, t => t.September },
                { 10, t => t.October },
                { 11, t => t.November },
                { 12, t => t.December },
            };

            int rownumber = 0;
            int maxPointIndex = 0;
            List<Row> rows = new List<Row>();
            foreach (var sourcetype in sourcetypes.OrderBy(t => t))
            {
                int inittype = 0;   // 0加载sourcetype、category; 1加载category; 2不加载sourcetype、category

                var sourcetypeData = data.Where(t => t.SourceType == sourcetype);
                if (sourcetypeData == null || !sourcetypeData.Any()) continue;

                foreach (var category in sourcetypeData.Select(t => t.Category).Distinct().OrderBy(t => t))
                {
                    var categoryData = sourcetypeData.Where(t => t.Category == category);
                    if (categoryData == null || !categoryData.Any()) continue;

                    foreach (var itemname in categoryData.Select(t => t.ItemName).Distinct().OrderBy(t => t))
                    {
                        var itemnameData = categoryData.Where(t => t.ItemName == itemname);
                        if (itemnameData == null || !itemnameData.Any()) continue;

                        var rowcells = new List<Cell>();
                        if (inittype == 0)
                        {
                            rowcells = new List<Cell>
                            {
                                new Cell { CellType = "body", CellValue = sourcetype, PointCell = 0, MergeRow = sourcetypeData.Count(), MergeCell = 1 },
                                new Cell { CellType = "body", CellValue = category, PointCell = 1, MergeRow = categoryData.Count(), MergeCell = 1 },
                            };
                        }
                        else if (inittype == 1)
                        {
                            rowcells.Add(new Cell { CellType = "body", CellValue = category, PointCell = 0, MergeRow = categoryData.Count(), MergeCell = 1 });
                        }

                        int itemnameIndex = inittype == 0 ? 2 : inittype == 1 ? 1 : 0;
                        rowcells.Add(new Cell { CellType = "body", CellValue = itemname, PointCell = itemnameIndex, MergeRow = itemnameData.Count(), MergeCell = 1 });

                        index = 0;
                        foreach (var year in years)
                        {
                            var thisdata = itemnameData.FirstOrDefault(t => t.Year == year);

                            int point = itemnameIndex + 1 + index * 12;
                            for (int i = 1; i < 13; i++)
                            {
                                rowcells.Add(new Cell
                                {
                                    CellType = "body",
                                    CellValue = thisdata != null ? dict[i].Invoke(thisdata) : null,
                                    PointCell = point,
                                    MergeRow = 1,
                                    MergeCell = 1
                                });
                                point++;
                            }

                            index++;
                        }

                        if (inittype == 0) maxPointIndex = rowcells.Max(t => t.PointCell);

                        inittype = 2;

                        rows.Add(new Row(rownumber) { Data = rowcells });
                        rownumber++;
                    }

                    inittype = 1;
                }
            }

            sheet.Row = rows;

            #endregion

            return sheet;
        }

        #region normal

        public TableData TableNormal(ConditionRequest request)
        {
            TableData tableData = new TableData();

            try
            {
                var report = repreportRepository.GetEntity(t => t.ID == request.ReportId);
                if (report == null) return tableData;

                List<QueryResult> results = perforReportRepository.DapperQuery<QueryResult>(report.Content, new
                {
                    request.HospitalId,
                    Year = request.Year?.FirstOrDefault() ?? DateTime.Now.Year,
                    Month = request.Month?.FirstOrDefault() ?? DateTime.Now.Month
                })?.ToList();
                if (results == null || !results.Any()) return tableData;

                (int rowNum, int columnNum) = GetRowAndColumnLevel(results);

                List<Column> columns = new List<Column>();
                Dictionary<string, Expression<Func<QueryResult, bool>>> colConditions = new Dictionary<string, Expression<Func<QueryResult, bool>>>();
                var fixedDict = JsonHelper.Deserialize<Dictionary<string, string>>(report.QueryArguments) ?? new Dictionary<string, string>();
                switch (columnNum)
                {
                    case 1:
                        columns = GetLevel1Columns(results, rowNum, colConditions, fixedDict);
                        break;
                    case 2:
                        columns = GetLevel2Columns(results, rowNum, colConditions, fixedDict);
                        break;
                    case 3:
                        columns = GetLevel3Columns(results, rowNum, colConditions, fixedDict);
                        break;
                }

                tableData.Columns = columns;

                tableData.Data = GetFixedData(results, colConditions, rowNum);
            }
            catch (Exception ex)
            {
                logger.LogError(ex.Message);
            }

            return tableData;
        }

        private (int rowNum, int columnNum) GetRowAndColumnLevel(List<QueryResult> results)
        {
            List<Func<QueryResult, bool>> rows = new List<Func<QueryResult, bool>>
            {
                t => !string.IsNullOrEmpty(t.Fixed1),
                t => !string.IsNullOrEmpty(t.Fixed2),
                t => !string.IsNullOrEmpty(t.Fixed3),
                t => !string.IsNullOrEmpty(t.Fixed4),
                t => !string.IsNullOrEmpty(t.Fixed5),
            };

            int rowIndex = 0;
            foreach (var item in rows)
            {
                if (!results.Any(item))
                    break;

                rowIndex++;
            }


            List<Func<QueryResult, bool>> columns = new List<Func<QueryResult, bool>>
            {
                t => !string.IsNullOrEmpty(t.Column1),
                t => !string.IsNullOrEmpty(t.Column2),
                t => !string.IsNullOrEmpty(t.Column3),
            };

            int columnIndex = 0;
            foreach (var item in columns)
            {
                if (!results.Any(item))
                    break;

                columnIndex++;
            }

            return (rowIndex, columnIndex);
        }

        private List<Column> GetLevel1Columns(List<QueryResult> results, int rowNum, Dictionary<string, Expression<Func<QueryResult, bool>>> colConditions, Dictionary<string, string> fixedDict)
        {
            List<Column> columns = new List<Column>();

            for (int i = 0; i < rowNum; i++)
            {
                string key = $"fixed_{i + 1}";
                columns.Add(new Column
                {
                    Label = fixedDict.ContainsKey(key) ? fixedDict[key] : "",
                    Prop = key
                });
            }

            var level1 = results.Where(t => !string.IsNullOrEmpty(t.Column1)).Select(t => t.Column1).Distinct();

            columns.AddRange(level1.Select(t =>
            {
                var col = new Column
                {
                    Label = t,
                    Prop = $"field_{t}"
                };
                colConditions.Add(col.Prop, exp => exp.Column1 == t);
                return col;
            }));

            return columns;
        }

        private List<Column> GetLevel2Columns(List<QueryResult> results, int rowNum, Dictionary<string, Expression<Func<QueryResult, bool>>> colConditions, Dictionary<string, string> fixedDict)
        {
            List<Column> columns = new List<Column>();

            for (int i = 0; i < rowNum; i++)
            {
                string key = $"fixed_{i + 1}";
                columns.Add(new Column
                {
                    Label = fixedDict.ContainsKey(key) ? fixedDict[key] : "",
                    Prop = key
                });
            }

            var columnData = results.Select(t => new { Column1 = t.Column1.NoBlank(), Column2 = t.Column2.NoBlank() }).Distinct();

            var level1 = columnData.Where(t => !string.IsNullOrEmpty(t.Column1))?.Select(t => t.Column1).Distinct();
            if (level1 == null || !level1.Any()) return columns;

            foreach (var item in level1)
            {
                var column = new Column { Label = item, Prop = $"field_{item}" };
                var level2 = columnData.Where(t => t.Column1 == item && !string.IsNullOrEmpty(t.Column2))?.Select(t => t.Column2).Distinct();
                if (level2 == null || !level2.Any()) continue;

                column.Children = level2.Select(item2 =>
                {
                    var col = new Column
                    {
                        Label = item2,
                        Prop = $"field_{item}_{item2}"
                    };
                    colConditions.Add(col.Prop, exp => exp.Column1 == item && exp.Column2 == item2);
                    return col;
                }).ToList();
                columns.Add(column);
            }

            return columns;
        }

        private List<Column> GetLevel3Columns(List<QueryResult> results, int rowNum, Dictionary<string, Expression<Func<QueryResult, bool>>> colConditions, Dictionary<string, string> fixedDict)
        {
            List<Column> columns = new List<Column>();

            for (int i = 0; i < rowNum; i++)
            {
                string key = $"fixed_{i + 1}";
                columns.Add(new Column
                {
                    Label = fixedDict.ContainsKey(key) ? fixedDict[key] : "",
                    Prop = key
                });
            }

            var columnData = results.Select(t => new { Column1 = t.Column1.NoBlank(), Column2 = t.Column2.NoBlank(), Column3 = t.Column3.NoBlank() }).Distinct();

            var level1 = columnData.Where(t => !string.IsNullOrEmpty(t.Column1))?.Select(t => t.Column1).Distinct();
            if (level1 == null || !level1.Any()) return columns;

            foreach (var item1 in level1)
            {
                var column1 = new Column { Label = item1, Prop = $"field_{item1}", Children = new List<Column>() };
                var level2 = columnData.Where(t => t.Column1 == item1 && !string.IsNullOrEmpty(t.Column2))?.Select(t => t.Column2).Distinct();
                if (level2 == null || !level2.Any()) continue;

                foreach (var item2 in level2)
                {
                    var column2 = new Column { Label = item1, Prop = $"field_{item1}_{item2}" };
                    var level3 = columnData.Where(t => t.Column1 == item1 && t.Column2 == item2 && !string.IsNullOrEmpty(t.Column3))?.Select(t => t.Column3).Distinct();
                    if (level3 == null || !level3.Any()) continue;

                    column2.Children = level3.Select(item3 =>
                    {
                        var col = new Column
                        {
                            Label = item2,
                            Prop = $"field_{item1}_{item2}_{item3}"
                        };
                        colConditions.Add(col.Prop, exp => exp.Column1 == item1 && exp.Column2 == item2 && exp.Column3 == item3);
                        return col;
                    }).ToList();

                    column1.Children.Add(column2);
                }
                columns.Add(column1);
            }

            return columns;
        }

        private JArray GetFixedData(List<QueryResult> results, Dictionary<string, Expression<Func<QueryResult, bool>>> expressions, int rowNum)
        {
            Dictionary<Expression<Func<QueryResult, bool>>, JObject> fixedexpressions = new Dictionary<Expression<Func<QueryResult, bool>>, JObject>();
            switch (rowNum)
            {
                case 1:
                    fixedexpressions = GetFixed1Data(results);
                    break;
                case 2:
                    fixedexpressions = GetFixed2Data(results);
                    break;
                case 3:
                    fixedexpressions = GetFixed3Data(results);
                    break;
                case 4:
                    fixedexpressions = GetFixed4Data(results);
                    break;
                case 5:
                    fixedexpressions = GetFixed5Data(results);
                    break;
            }

            if (fixedexpressions == null || !fixedexpressions.Any()) return new JArray();

            JArray jArray = new JArray();
            foreach (var item in fixedexpressions)
            {
                JObject jobj = item.Value;
                foreach (var expdic in expressions)
                {
                    var exp = item.Key.And(expdic.Value);
                    var value = results.Where(exp.Compile())?.Sum(t => t.Value);
                    jobj[expdic.Key] = value == 0 ? null : value;
                }
                jArray.Add(jobj);
            }
            return jArray;
        }

        private Dictionary<Expression<Func<QueryResult, bool>>, JObject> GetFixed1Data(List<QueryResult> results)
        {
            Dictionary<Expression<Func<QueryResult, bool>>, JObject> fixedexpressions = new Dictionary<Expression<Func<QueryResult, bool>>, JObject>();
            var fixedData = results.Select(t => t.Fixed1.NoBlank()).Distinct();
            if (fixedData != null && fixedData.Any())
            {
                int index = 1;
                foreach (var item in fixedData)
                {
                    Expression<Func<QueryResult, bool>> exp = (f) => f.Fixed1 == item;
                    JObject jobj = new JObject
                    {
                        ["rownumber"] = index,
                        ["fixed_1"] = item
                    };
                    fixedexpressions.Add(exp, jobj);
                    index++;
                }
            }
            return fixedexpressions;
        }

        private Dictionary<Expression<Func<QueryResult, bool>>, JObject> GetFixed2Data(List<QueryResult> results)
        {
            Dictionary<Expression<Func<QueryResult, bool>>, JObject> fixedexpressions = new Dictionary<Expression<Func<QueryResult, bool>>, JObject>();
            var fixedData = results.Select(t => new { Fixed1 = t.Fixed1.NoBlank(), Fixed2 = t.Fixed2.NoBlank() }).Distinct();
            if (fixedData != null && fixedData.Any())
            {
                int index = 1;
                foreach (var item in fixedData)
                {
                    Expression<Func<QueryResult, bool>> exp = (f) => f.Fixed1 == item.Fixed1 && f.Fixed2 == item.Fixed2;
                    JObject jobj = new JObject
                    {
                        ["rownumber"] = index,
                        ["fixed_1"] = item.Fixed1,
                        ["fixed_2"] = item.Fixed2,
                    };
                    fixedexpressions.Add(exp, jobj);
                    index++;
                }
            }
            return fixedexpressions;
        }

        private Dictionary<Expression<Func<QueryResult, bool>>, JObject> GetFixed3Data(List<QueryResult> results)
        {
            Dictionary<Expression<Func<QueryResult, bool>>, JObject> fixedexpressions = new Dictionary<Expression<Func<QueryResult, bool>>, JObject>();
            var fixedData = results.Select(t => new
            {
                Fixed1 = t.Fixed1.NoBlank(),
                Fixed2 = t.Fixed2.NoBlank(),
                Fixed3 = t.Fixed3.NoBlank()
            }).Distinct();
            if (fixedData != null && fixedData.Any())
            {
                int index = 1;
                foreach (var item in fixedData)
                {
                    Expression<Func<QueryResult, bool>> exp = (f) => f.Fixed1 == item.Fixed1 && f.Fixed2 == item.Fixed2 && f.Fixed3 == item.Fixed3;
                    JObject jobj = new JObject
                    {
                        ["rownumber"] = index,
                        ["fixed_1"] = item.Fixed1,
                        ["fixed_2"] = item.Fixed2,
                        ["fixed_3"] = item.Fixed3,
                    };
                    fixedexpressions.Add(exp, jobj);
                    index++;
                }
            }
            return fixedexpressions;
        }

        private Dictionary<Expression<Func<QueryResult, bool>>, JObject> GetFixed4Data(List<QueryResult> results)
        {
            Dictionary<Expression<Func<QueryResult, bool>>, JObject> fixedexpressions = new Dictionary<Expression<Func<QueryResult, bool>>, JObject>();
            var fixedData = results.Select(t => new
            {
                Fixed1 = t.Fixed1.NoBlank(),
                Fixed2 = t.Fixed2.NoBlank(),
                Fixed3 = t.Fixed3.NoBlank(),
                Fixed4 = t.Fixed4.NoBlank()
            }).Distinct();
            if (fixedData != null && fixedData.Any())
            {
                int index = 1;
                foreach (var item in fixedData)
                {
                    Expression<Func<QueryResult, bool>> exp = (f) => f.Fixed1 == item.Fixed1 && f.Fixed2 == item.Fixed2 && f.Fixed3 == item.Fixed3 && f.Fixed4 == item.Fixed4;
                    JObject jobj = new JObject
                    {
                        ["rownumber"] = index,
                        ["fixed_1"] = item.Fixed1,
                        ["fixed_2"] = item.Fixed2,
                        ["fixed_3"] = item.Fixed3,
                        ["fixed_4"] = item.Fixed4,
                    };
                    fixedexpressions.Add(exp, jobj);
                    index++;
                }
            }
            return fixedexpressions;
        }

        private Dictionary<Expression<Func<QueryResult, bool>>, JObject> GetFixed5Data(List<QueryResult> results)
        {
            Dictionary<Expression<Func<QueryResult, bool>>, JObject> fixedexpressions = new Dictionary<Expression<Func<QueryResult, bool>>, JObject>();
            var fixedData = results.Select(t => new
            {
                Fixed1 = t.Fixed1.NoBlank(),
                Fixed2 = t.Fixed2.NoBlank(),
                Fixed3 = t.Fixed3.NoBlank(),
                Fixed4 = t.Fixed4.NoBlank(),
                Fixed5 = t.Fixed5.NoBlank()
            }).Distinct();
            if (fixedData != null && fixedData.Any())
            {
                int index = 1;
                foreach (var item in fixedData)
                {
                    Expression<Func<QueryResult, bool>> exp = (f) => f.Fixed1 == item.Fixed1 && f.Fixed2 == item.Fixed2 && f.Fixed3 == item.Fixed3 && f.Fixed4 == item.Fixed4 && f.Fixed5 == item.Fixed5;
                    JObject jobj = new JObject
                    {
                        ["rownumber"] = index,
                        ["fixed_1"] = item.Fixed1,
                        ["fixed_2"] = item.Fixed2,
                        ["fixed_3"] = item.Fixed3,
                        ["fixed_4"] = item.Fixed4,
                        ["fixed_5"] = item.Fixed5,
                    };
                    fixedexpressions.Add(exp, jobj);
                    index++;
                }
            }
            return fixedexpressions;
        }

        #endregion

        #region special

        public TableData TableSpecial(ConditionRequest request)
        {
            TableData tableData = new TableData();

            var conditions = GetConditions(request);
            if (conditions == null || !conditions.Any()) return tableData;

            try
            {
                #region columns

                List<Column> columns = new List<Column>
                {
                    new Column
                    {
                        Label = "月份",
                        Children = new List<Column>
                        {
                            new Column { Label = "核算单元", Children = new List<Column> { new Column { Label = "核算组别", Prop = "itemname" } } }
                        }
                    }
                };

                foreach (var condition in conditions)
                {
                    string key = $"field_{condition.Year}_{condition.Month}_{condition.AccountingUnit}_{condition.UnitType}";
                    columns.Add(new Column
                    {
                        Label = $"{condition.Year}年{condition.Month}月",
                        Children = new List<Column>
                        {
                            new Column { Label = condition.AccountingUnit, Children = new List<Column> { new Column { Label = condition.UnitType, Prop = key } } }
                        }
                    });
                }

                tableData.Columns = columns;

                #endregion

                var report = repreportRepository.GetEntity(t => t.ID == request.ReportId);
                if (report == null) return tableData;

                var data = perforReportRepository.DapperQuery<QueryData>(report.Content, request);
                if (data == null || !data.Any()) return tableData;

                var type = data.Select(t => new { SourceType = t.SourceType.NoBlank(), Category = t.Category.NoBlank(), ItemName = t.ItemName.NoBlank() }).Distinct();

                #region data

                JArray jarray = new JArray();
                int index = 1;

                foreach (var sourcetype in type.Where(t => !string.IsNullOrEmpty(t.SourceType))?.Select(t => t.SourceType).Distinct())
                {
                    var conditionData = data.Where(t => t.SourceType == sourcetype);
                    if (conditionData == null || !conditionData.Any()) continue;

                    JObject level1 = new JObject { ["rownumber"] = index, ["itemname"] = sourcetype };
                    index++;
                    WriteDataToJObject(level1, conditionData, conditions);
                    JArray level1Arr = new JArray();

                    jarray.Add(level1);

                    var categories = type.Where(t => t.SourceType == sourcetype);
                    if (categories == null || !categories.Any(t => !string.IsNullOrEmpty(t.Category))) continue;

                    foreach (var category in categories.Select(t => t.Category).Distinct())
                    {
                        conditionData = data.Where(t => t.SourceType == sourcetype && t.Category == category);
                        if (conditionData == null || !conditionData.Any()) continue;

                        JObject level2 = new JObject { ["rownumber"] = index, ["itemname"] = category };
                        index++;
                        WriteDataToJObject(level2, conditionData, conditions);
                        JArray level2Arr = new JArray();

                        level1Arr.Add(level2);

                        var itemnames = categories.Where(t => t.Category == category);
                        if (itemnames == null || !itemnames.Any(t => !string.IsNullOrEmpty(t.ItemName))) continue;

                        foreach (var itemname in itemnames.Select(t => t.ItemName).Distinct())
                        {
                            conditionData = data.Where(t => t.SourceType == sourcetype && t.Category == category && t.ItemName == itemname);
                            if (conditionData == null || !conditionData.Any()) continue;

                            JObject level3 = new JObject { ["rownumber"] = index, ["itemname"] = itemname };
                            index++;
                            WriteDataToJObject(level3, conditionData, conditions);

                            level2Arr.Add(level3);
                        }

                        if (level2Arr != null && level2Arr.Any())
                            level2["children"] = level2Arr;
                    }

                    if (level1Arr != null && level1Arr.Any())
                        level1["children"] = level1Arr;
                }

                tableData.Data = jarray;

                #endregion
            }
            catch (Exception ex)
            {
                logger.LogError($"Table Error: {ex}");
            }

            return tableData;
        }

        private List<Condition> GetConditions(ConditionRequest request)
        {
            List<Condition> conditions = new List<Condition>();

            if (request.AccountingUnit == null || !request.AccountingUnit.Any())
                throw new PerformanceException("请选择核算单元");

            if (request.UnitType == null || !request.UnitType.Any())
                throw new PerformanceException("请选择核算组别");

            if (request.Year == null || !request.Year.Any())
                request.Year = new int[] { DateTime.Now.Year };

            if (request.Month == null || !request.Month.Any())
                request.Month = new int[] { DateTime.Now.Month };

            var departmentInfos = request.AccountingUnit.Join(request.UnitType, account => true, unit => true, (account, unit) => new { account, unit });
            var dateInfo = request.Year.Join(request.Month, year => true, month => true, (year, month) => new { year, month });

            conditions = departmentInfos.Join(dateInfo, outer => true, innner => true, (outer, innner) => new { outer, innner })
                .Select(t => new Condition
                {
                    AccountingUnit = t.outer.account,
                    UnitType = t.outer.unit,
                    Year = t.innner.year,
                    Month = t.innner.month
                }).OrderBy(t => t.Year).ThenBy(t => t.Month).ThenBy(t => t.AccountingUnit).ThenBy(t => t.UnitType).ToList();

            return conditions;
        }


        public void WriteDataToJObject(JObject jobj, IEnumerable<QueryData> data, List<Condition> conditions)
        {
            try
            {
                foreach (var item in conditions)
                {
                    string key = $"field_{item.Year}_{item.Month}_{item.AccountingUnit}_{item.UnitType}";

                    var value = data?.Where(t => t.Year == item.Year && t.Month == item.Month && t.AccountingUnit == item.AccountingUnit && t.UnitType == item.UnitType)?.Sum(t => t.Value);
                    jobj[key] = value == 0 ? null : value;
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"WriteDataToJObject: {ex}");
            }
        }

        #endregion
    }
}
