using System;
using System.Collections.Generic;
using System.IdentityModel.Tokens.Jwt;
using System.Linq;
using System.Security.Claims;
using System.Text;

using FMSAdmin.Data;
using FMSAdmin.Helpers;
using FMSAdmin.Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Microsoft.IdentityModel.Tokens;
using FMSAdmin.Models;
using SelectPdf;
using System.IO;
using System.Data;
using FMSAdmin.Models.Formula;

namespace FMSApp.Services {
    public class MonthlyReportService {
        private readonly ILogger<MonthlyReportService> _logger;
        private readonly FMSContext _context;

        private readonly AppSettings _appSettings;
        private readonly StorageHelper _storage;

        public MonthlyReportService(
            ILogger<MonthlyReportService> logger,
            FMSContext context,
            IOptions<AppSettings> appSettings,
            StorageHelper storage
            ) {
            _logger = logger;
            _context = context;
            _appSettings = appSettings.Value;
            _storage = storage;
        }

        public IQueryable<FmsMonthlyReport> GetAll() {
            var query = _context.FmsMonthlyReport;
            return query;
        }

        public void Approve(int siteId, string approverUserId, DateTime addDate, int businessFieldId) {
            var persist = _context.FmsMonthlyReport.FirstOrDefault(
                x => x.SiteId == siteId
                && x.AddDate == addDate.Date
            // && x.BusinessFieldId == businessFieldId 20200625 요청
            );
            if (persist == null) throw new ServiceException("해당일자의 일간보고서가 존재하지 않습니다.");

            if (string.IsNullOrEmpty(persist.ApproverUserId)) {
                persist.ApproverUserId = approverUserId;
                persist.ApproveDate = DateTime.Now;
            } else if (string.IsNullOrEmpty(persist.ApproverUserId2)) {
                persist.ApproverUserId2 = approverUserId;
                persist.ApproveDate2 = DateTime.Now;
            } else if (string.IsNullOrEmpty(persist.ApproverUserId3)) {
                persist.ApproverUserId3 = approverUserId;
                persist.ApproveDate3 = DateTime.Now;
            } else if (string.IsNullOrEmpty(persist.ApproverUserId4)) {
                persist.ApproverUserId4 = approverUserId;
                persist.ApproveDate4 = DateTime.Now;
            } else if (string.IsNullOrEmpty(persist.ApproverUserId5)) {
                persist.ApproverUserId5 = approverUserId;
                persist.ApproveDate5 = DateTime.Now;
            } else {
                throw new ServiceException("결재 한도를 초과하였습니다.");
            }

            _context.FmsMonthlyReport.Update(persist);
            _context.SaveChanges();
        }


        public void Delete(int siteId, DateTime addDate, int businessFieldId) {
            var persist = _context.FmsMonthlyReport.FirstOrDefault(
                x => x.SiteId == siteId
                && x.AddDate.Year == addDate.Year
                && x.AddDate.Month == addDate.Month
                && x.BusinessFieldId == businessFieldId
            );
            if (persist == null) throw new ServiceException("해당일자의 일간보고서가 존재하지 않습니다.");

            _context.FmsMonthlyReport.Remove(persist);
            _context.SaveChanges();
        }

        public FmsMonthlyReport checkFile(FmsMonthlyReport data) {
            if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
                data.ParkingManagement.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.ParkingManagement.FileCategory = category;
                data.ParkingManagement.CreatedDate = DateTime.Now;
            } else {
                data.ParkingManagement = null;
            }
            if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
                data.RentalManagement.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.RentalManagement.FileCategory = category;
                data.RentalManagement.CreatedDate = DateTime.Now;
            } else {
                data.RentalManagement = null;
            }
            if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
                data.MonthlyPriorityWork.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.MonthlyPriorityWork.FileCategory = category;
                data.MonthlyPriorityWork.CreatedDate = DateTime.Now;
            } else {
                data.MonthlyPriorityWork = null;
            }
            if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
                data.ImprovementReport.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.ImprovementReport.FileCategory = category;
                data.ImprovementReport.CreatedDate = DateTime.Now;
            } else {
                data.ImprovementReport = null;
            }
            if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
                data.NextMonthPriorityWork.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.NextMonthPriorityWork.FileCategory = category;
                data.NextMonthPriorityWork.CreatedDate = DateTime.Now;
            } else {
                data.NextMonthPriorityWork = null;
            }
            if (data.Cost != null && data.Cost.IsUpload) {
                data.Cost.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.Cost.FileCategory = category;
                data.Cost.CreatedDate = DateTime.Now;
            } else {
                data.Cost = null;
            }
            if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
                data.SurroundingStatus.SiteId = data.SiteId;
                var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
                data.SurroundingStatus.FileCategory = category;
                data.SurroundingStatus.CreatedDate = DateTime.Now;
            } else {
                data.SurroundingStatus = null;
            }
            return data;
        }

        public void copyFile(FmsMonthlyReport data, FmsMonthlyReport persist) {

            if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
                _storage.CopyEntity(data.ParkingManagement.Path, persist.ParkingManagement);
                _storage.PdfConvertImage(data.ParkingManagement);
            }
            if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
                _storage.CopyEntity(data.RentalManagement.Path, persist.RentalManagement);
                _storage.PdfConvertImage(data.RentalManagement);
            }
            if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
                _storage.CopyEntity(data.MonthlyPriorityWork.Path, persist.MonthlyPriorityWork);
                _storage.PdfConvertImage(data.MonthlyPriorityWork);
            }
            if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
                _storage.CopyEntity(data.ImprovementReport.Path, persist.ImprovementReport);
                _storage.PdfConvertImage(data.ImprovementReport);
            }
            if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
                _storage.CopyEntity(data.NextMonthPriorityWork.Path, persist.NextMonthPriorityWork);
                _storage.PdfConvertImage(data.NextMonthPriorityWork);
            }
            if (data.Cost != null && data.Cost.IsUpload) {
                _storage.CopyEntity(data.Cost.Path, persist.Cost);
                _storage.PdfConvertImage(data.Cost);
            }
            if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
                _storage.CopyEntity(data.SurroundingStatus.Path, persist.SurroundingStatus);
                _storage.PdfConvertImage(data.SurroundingStatus);
            }
        }
        public void Save(FmsMonthlyReport data, bool isCreate) {
            var persist = _context.FmsMonthlyReport.FirstOrDefault(
                x => x.SiteId == data.SiteId
                && x.AddDate.Year == data.AddDate.Year
                && x.AddDate.Month == data.AddDate.Month);

            data = checkFile(data);

            if (persist == null) {
                if (data.OrganizationChartStatusFileId != null) {
                    data.OrganizationChartStatus = _context.CmFile.Where(x => x.SiteId == data.SiteId && x.FileId == data.OrganizationChartStatusFileId.Value).FirstOrDefault();
                }
                if (data.RentalStatusFileId != null) {
                    data.RentalStatus = _context.CmFile.Where(x => x.SiteId == data.SiteId && x.FileId == data.RentalStatusFileId.Value).FirstOrDefault();
                }
                data.RegisterDate = DateTime.Now;
                _context.FmsMonthlyReport.Add(data);
                _context.SaveChanges();
            } else {
                if (isCreate) throw new ServiceException("해당일자의 보고서가 이미 등록되어있습니다.");
                persist.Name = data.Name;
                persist.Contents = data.Contents;
                persist.SiteInfoDatas = data.SiteInfoDatas;
                persist.FacilityStandards = data.FacilityStandards;
                persist.RentalStandards = data.RentalStandards;
                persist.HumanCountStatus = data.HumanCountStatus;
                persist.ManagementOfficeContact = data.ManagementOfficeContact;
                persist.RelatedInstitutionsContact = data.RelatedInstitutionsContact;
                persist.UserChangeStatus = data.UserChangeStatus;
                persist.LayoutStatus = data.LayoutStatus;
                persist.TenantContact = data.TenantContact;
                persist.FacilityTime = data.FacilityTime;
                persist.FacilityTimeGraph = data.FacilityTimeGraph;
                persist.EnergyUsage = data.EnergyUsage;
                persist.EnergyUsageGraph = data.EnergyUsageGraph;
                persist.EnergyCost = data.EnergyCost;
                persist.EnergyCharge = data.EnergyCharge;
                persist.EnergyChargeGraph = data.EnergyChargeGraph;
                persist.EnergyAnalysis = data.EnergyAnalysis;

                if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
                    persist.ParkingManagement = data.ParkingManagement;
                }
                if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
                    persist.RentalManagement = data.RentalManagement;
                }
                if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
                    persist.MonthlyPriorityWork = data.MonthlyPriorityWork;
                }
                if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
                    persist.ImprovementReport = data.ImprovementReport;
                }
                if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
                    persist.NextMonthPriorityWork = data.NextMonthPriorityWork;
                }
                if (data.Cost != null && data.Cost.IsUpload) {
                    persist.Cost = data.Cost;
                }
                if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
                    persist.SurroundingStatus = data.SurroundingStatus;
                }
                _context.FmsMonthlyReport.Update(persist);
                _context.SaveChanges();
            }
            copyFile(data, data);
        }


        public void Delete(int siteId, DateTime addData) {
            try {
                var data = _context.FmsMonthlyReport.First(x => x.SiteId == siteId && x.AddDate == addData);
                _context.FmsMonthlyReport.Remove(data);
                //data.IsUse = false;
                _context.SaveChanges();
            } catch {
                throw new ServiceException("이미 다른 화면에서 사용중이므로 삭제할 수 없습니다.");
            }
        }

        /// <summary>
        /// 법정검사
        /// </summary>
        public DataTable GetLegalExamination(int siteId, DateTime addDate) {

            var legalExaminationList = _context.FmsWorkRequest.Where(x =>
                x.SiteId == Util.ToInt(siteId) &&
                x.WorkTypeId == 2 &&
                x.StartWorkDate.Year == addDate.Year
            ).OrderBy(x => x.WorkScheduleId).Select(x => new {
                x.WorkScheduleId,
                Type = x.CmBusinessField.Name,
                x.Title,
                WorkProgress = x.WorkProgressId == 5 ? "작업완료" : x.WorkProgress.Name,
                Cycle = x.FmsWorkSchedule.CycleSize + x.FmsWorkSchedule.FmsWorkCodeCycleUnit.Name.Replace("간", ""),
                Month = x.StartWorkDate.Month,
                Day = x.StartWorkDate.Day
            }).ToList();


            DataTable LegalExamination = new DataTable();
            DataRow legalExaminationRow = null;
            LegalExamination.Columns.Add("Type");
            LegalExamination.Columns.Add("Title");
            for (var m = 1; m <= 12; m++) {
                LegalExamination.Columns.Add($"M{m}");
            }
            LegalExamination.Columns.Add("Progress");
            LegalExamination.Columns.Add("Comment");
            var WorkScheduleId = -1;
            foreach (var item in legalExaminationList.OrderBy(x => x.WorkScheduleId)) {
                if (WorkScheduleId != item.WorkScheduleId) {
                    if (legalExaminationRow != null) {
                        LegalExamination.Rows.Add(legalExaminationRow);
                    }
                    legalExaminationRow = LegalExamination.NewRow();
                    legalExaminationRow["Type"] = item.Type;
                    legalExaminationRow["Title"] = item.Title;
                    legalExaminationRow["Progress"] = item.WorkProgress;
                    legalExaminationRow["Comment"] = item.Cycle;
                }
                for (var m = 1; m <= 12; m++) {
                    if (item.Month == m) {
                        legalExaminationRow[$"M{m}"] = item.Day;
                    } else {
                        legalExaminationRow[$"M{m}"] = "";
                    }
                }
                WorkScheduleId = item.WorkScheduleId == null ? -1 : item.WorkScheduleId.Value;
            }
            if (legalExaminationRow != null)
                LegalExamination.Rows.Add(legalExaminationRow);
            return LegalExamination;
        }

        /// <summary>
        /// 설비가동시간
        /// </summary>
        public DataTable GetFacilityTime(int siteId, DateTime today) {
            DataTable facilityTime = new DataTable();
            DataRow facilityTimeRow = null;
            DataRow facilityTimeRowPre = null;
            var facilityTime_now = _context.BemsMonitoringPointHistoryDaily.Where(
                x => x.SiteId == Util.ToInt(siteId) &&
                x.PropertyId == 32 &&
                x.CreatedDateTime.Year == today.Year &&
                x.CreatedDateTime.Month == today.Month
            ).OrderBy(x => x.FacilityCode).ThenBy(x => x.CreatedDateTime).Select(x => new {
                FacilityName = x.CmFacility.Name,
                FloorName = x.CmFacility.CmFloor.Name,
                x.FacilityCode,
                Year = x.CreatedDateTime.Year,
                Day = x.CreatedDateTime.Day,
                x.DailyValue,
                LastDailyValue = _context.BemsMonitoringPointHistoryDaily.Where(f => f.SiteId == Util.ToInt(siteId) && f.PropertyId == 32 && f.FacilityCode == x.FacilityCode && f.CreatedDateTime.Year == today.AddYears(-1).Year && f.CreatedDateTime.Month == today.Month).Select(f => f.DailyValue).FirstOrDefault(),
            }).ToList();

            if (facilityTime_now == null) return facilityTime;

            int currentCode = -1;
            DateTime tmpDt = DateTime.Parse(today.Year.ToString() + "-" + today.AddMonths(1).Month.ToString() + "-01");
            var day = tmpDt.AddDays(-1).Day;
            double total = 0, total_pre = 0;
            string[] colName = { "FacilityName", "FacilityCode", "YearName" };
            foreach (var item in colName) {
                facilityTime.Columns.Add(item);
            }
            facilityTime.Columns.Add("IsType", typeof(int));        //1:당월, 2:전월
            facilityTime.Columns.Add("Average", typeof(Double));
            for (var d = 1; d <= day; d++) {
                facilityTime.Columns.Add($"D{d}", typeof(Double));
            }
            foreach (var item in facilityTime_now) {
                if (currentCode != item.FacilityCode) {
                    if (facilityTimeRow != null) {
                        facilityTimeRow["Average"] = (total / day).ToString("0.##");
                        facilityTimeRowPre["Average"] = (total_pre / day).ToString("0.##");
                        facilityTime.Rows.Add(facilityTimeRowPre);
                        facilityTime.Rows.Add(facilityTimeRow);
                        facilityTimeRow = null; facilityTimeRowPre = null;
                        total = 0; total_pre = 0;
                    }
                    facilityTimeRow = facilityTime.NewRow();
                    facilityTimeRowPre = facilityTime.NewRow();
                    facilityTimeRow["FacilityName"] = item.FacilityName + " (" + item.FloorName + ")";
                    facilityTimeRow["FacilityCode"] = item.FacilityCode;
                    facilityTimeRow["IsType"] = 1;
                    facilityTimeRow["YearName"] = item.Year + "년";
                    facilityTimeRowPre["FacilityName"] = item.FacilityName + " (" + item.FloorName + ")";
                    facilityTimeRowPre["FacilityCode"] = item.FacilityCode;
                    facilityTimeRowPre["IsType"] = 2;
                    facilityTimeRowPre["YearName"] = (item.Year - 1) + "년";

                    for (var d = 1; d <= day; d++) {
                        facilityTimeRow[$"D{d}"] = 0;
                        facilityTimeRowPre[$"D{d}"] = 0;
                    }
                }
                facilityTimeRow[$"D{item.Day}"] = Math.Round(item.DailyValue);
                facilityTimeRowPre[$"D{item.Day}"] = Math.Round(item.LastDailyValue);
                total += Math.Round(item.DailyValue);
                total_pre += Math.Round(item.LastDailyValue);
                currentCode = item.FacilityCode;
            }

            return facilityTime;
        }

        /// <summary>
        /// 설비가동현황 그래프
        /// </summary>
        public DataTable GetFacilityTimeGraph(int siteId, DateTime today) {
            DataTable dt = new DataTable();
            var facilityTime_now = _context.BemsMonitoringPointHistoryDaily.Where(
                x => x.SiteId == Util.ToInt(siteId) &&
                x.PropertyId == 32 &&
                x.CreatedDateTime.Year == today.Year &&
                x.CreatedDateTime.Month == today.Month
            ).Select(x => new {
                FacilityName = x.CmFacility.Name,
                FloorName = x.CmFacility.CmFloor.Name,
                x.FacilityCode,
                Year = x.CreatedDateTime.Year,
                Day = x.CreatedDateTime.Day,
                x.DailyValue,
                LastDailyValue = _context.BemsMonitoringPointHistoryDaily.Where(f => f.SiteId == Util.ToInt(siteId) && f.PropertyId == 32 && f.FacilityCode == x.FacilityCode && f.CreatedDateTime.Year == today.AddYears(-1).Year && f.CreatedDateTime.Month == today.Month).Select(f => f.DailyValue).FirstOrDefault(),
            }).ToList();

            if (facilityTime_now.Count > 0) {
                var facilityTimeGraph = facilityTime_now.GroupBy(x => new { x.Year, x.Day }).Select(x => new {
                    Day = x.Key.Day,
                    Value = x.Sum(f => f.DailyValue),
                    LastDay = x.Key.Day,
                    LastValue = x.Sum(f => f.LastDailyValue)
                }).Select(x => new EnergyReportGrid {
                    FuelTypeId = 0,
                    PriceTypeId = 0,
                    PriceTypeName = "설비가동시간",
                    Date = x.Day,
                    Value = x.Value,
                    LastValue = x.LastValue
                }).ToList();
                if (facilityTimeGraph.Count > 0) dt = _GetEnergyGraph(facilityTimeGraph, today, 0);
            }

            return dt;
        }

        /// <summary>
        /// 에너지 사용량
        /// </summary>
        public DataTable GetEnergyData(int siteId, DateTime today, int type, bool isGraph) {
            DataTable dt = new DataTable();
            string[] arrPriceCode = null;
            string[] arrPriceCode1 = { "P003", "G001", "W007", "W008", "O002", "M010", "L002" };    // 사용량
            string[] arrPriceCode2 = { "P022", "P024", "P026", "P028", "P038", "G005", "G020", "W009", "W001", "W003", "W002", "M005", "M006", "O006", "O007", "L007", "L006" };   // 단가변동
            string[] arrPriceCode3 = { "P038", "G005", "W001", "W002", "O007", "M006", "L007" };     //요금분석
            if (type == 1) arrPriceCode = arrPriceCode1;
            else if (type == 2) arrPriceCode = arrPriceCode2;
            else arrPriceCode = arrPriceCode3;

            var query = _context.BemsSitePriceHistory.Where(
                    x => x.SiteId == Util.ToInt(siteId) &&
                    (x.StartDate.Year == today.Year || x.StartDate.Year == today.AddYears(-1).Year) &&
                    x.StartDate.Month <= today.Month &&
                    arrPriceCode.Contains(x.PriceCode) &&
                    x.PriceTypeId == _context.BemsSitePrice.Where(f => f.SiteId == x.SiteId && f.FuelTypeId == x.FuelTypeId && f.PriceTypeId == x.PriceTypeId).Select(f => f.PriceTypeId).FirstOrDefault()
                ).OrderBy(x => x.PriceTypeId).Select(x => new EnergyReportGrid {
                    Type = type,
                    FuelTypeId = x.FuelTypeId,
                    PriceTypeId = x.PriceTypeId,
                    PriceTypeName = x.PriceType.PriceTypeIdDesc,
                    PriceCode = x.PriceCode,
                    PriceCodeName = x.PriceCodeNavigation.PriceCodeDesc,
                    Year = today.Year,
                    Date = x.StartDate.Month,
                    Value = x.StartDate.Year == today.Year ? Util.ToDouble(x.PriceValue) : 0,
                    LastValue = x.StartDate.Year == today.AddYears(-1).Year ? Util.ToDouble(x.PriceValue) : 0,
                }).ToList();

            var data = query.GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.PriceCode, x.Year, x.Date }).Select(x => new EnergyReportGrid {
                Type = type,
                FuelTypeId = x.Key.FuelTypeId,
                PriceTypeId = x.Key.PriceTypeId,
                PriceTypeName = x.Max(f => f.PriceTypeName),
                PriceCode = x.Key.PriceCode,
                PriceCodeName = x.Max(f => f.PriceCodeName),
                Year = x.Key.Year,
                Date = x.Key.Date,
                Value = x.Sum(f => f.Value),
                LastValue = x.Sum(f => f.LastValue),
            }).ToList();


            // 단가변동
            if (type == 2) {
                string[] arrCost1 = { "P022", "P024", "P026", "P028" };
                data = data.Where(x =>
                        (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력일반용" && arrCost1.Contains(x.PriceCode)) ||
                        (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력산업용" && (x.PriceCode == "P022" || x.PriceCode == "P038")) ||
                        (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력심야용" && (x.PriceCode == "P022" || x.PriceCode == "P024" || x.PriceCode == "P026")) ||
                        (x.FuelTypeId == 2 && (x.PriceCode == "G020" || x.PriceCode == "G005")) ||
                        (x.FuelTypeId == 4 && (x.PriceCode == "O006" || x.PriceCode == "O007")) ||
                        (x.FuelTypeId == 5 && (x.PriceCode == "L007" || x.PriceCode == "L006")) ||
                        (x.FuelTypeId == 6 && (x.PriceCode == "M005" || x.PriceCode == "M006")))
                    .Concat(
                        data.Where(f =>
                                (f.FuelTypeId == 3 && (f.PriceCode == "W009" || f.PriceCode == "W001")) ||
                                (f.FuelTypeId == 3 && (f.PriceCode == "W003" || f.PriceCode == "W002")))
                            .GroupBy(f => new { f.Type, f.FuelTypeId, f.PriceCode, f.Year, f.Date }).Select(f => new EnergyReportGrid {
                                Type = f.Key.Type,
                                FuelTypeId = f.Key.FuelTypeId,
                                PriceTypeId = (f.Key.PriceCode == "W009" || f.Key.PriceCode == "W001") ? 0 : 1,
                                PriceTypeName = (f.Key.PriceCode == "W009" || f.Key.PriceCode == "W001") ? "상수도" : "하수도",
                                PriceCode = f.Key.PriceCode,
                                PriceCodeName = f.Max(v => v.PriceCodeName),
                                Year = f.Key.Year,
                                Date = f.Key.Date,
                                Value = f.Sum(v => v.Value),
                                LastValue = f.Sum(v => v.LastValue),
                            })
                    ).ToList();
            }

            if (!isGraph) {
                dt = _GetEnergyGrid(siteId, today, type, data);
            } else {
                if (data != null) {
                    var data2 = data.Where(x => x.Type == type && x.FuelTypeId != 3).GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.Date }).Select(x => new EnergyReportGrid {
                        FuelTypeId = x.Key.FuelTypeId,
                        PriceTypeId = x.Key.PriceTypeId,
                        PriceTypeName = x.Max(f => f.PriceTypeName),
                        PriceCode = "",
                        PriceCodeName = "",
                        Date = x.Key.Date,
                        Value = x.Sum(f => f.Value),
                        LastValue = x.Sum(f => f.LastValue)
                    }).Concat(
                        data.Where(f => f.Type == type && f.FuelTypeId == 3).GroupBy(f => new { f.FuelTypeId, f.PriceTypeId, f.PriceCode, f.Date }).Select(f => new EnergyReportGrid {
                            FuelTypeId = f.Key.FuelTypeId,
                            PriceTypeId = f.Key.PriceTypeId,
                            PriceTypeName = f.Max(f => f.PriceTypeName),
                            PriceCode = f.Key.PriceCode,
                            PriceCodeName = f.Max(f => f.PriceCodeName),
                            Date = f.Key.Date,
                            Value = f.Sum(f => f.Value),
                            LastValue = f.Sum(f => f.LastValue)
                        })
                    ).ToList();
                    if (data2.Count > 0) dt = _GetEnergyGraph(data2, today, 1);
                }
            }

            return dt;
        }

        /// <summary>
        /// 에너지 사용량 그리드
        /// </summary>
        public DataTable _GetEnergyGrid(int siteId, DateTime today, int type, List<EnergyReportGrid> data) {
            DataTable dt = new DataTable();
            DataRow dr = null, drPre = null, drCmp = null, drCmpRate = null;
            int currentCode = -1, day = 12, fuelTypeId = 0;
            string currentCode2 = "";
            double total = 0, total_pre = 0;
            //Sort (1:전월, 2:당월, 3:증감, 4:증감%)
            string[] colName = { "Type", "FuelTypeId", "PriceTypeId", "YearName", "PriceTypeName", "PriceCode", "PriceCodeName", "IsTypeFirst", "Sort" };
            foreach (var item in colName) {
                dt.Columns.Add(item);
            }
            dt.Columns.Add("Total", typeof(Double));
            for (var d = 1; d <= day; d++) {
                dt.Columns.Add($"D{d}", typeof(Double));
            }

            var data2 = data.Where(x => x.Type == type).OrderBy(x => x.FuelTypeId).ThenBy(x => x.PriceTypeId).ThenBy(x => x.PriceCode).ToList();

            if (data2.Count > 0) {
                foreach (var item in data2) {
                    if (currentCode != item.PriceTypeId || currentCode2 != item.PriceCode) {
                        if (dr != null) {
                            dr["Total"] = total;
                            drPre["Total"] = total_pre;
                            dt.Rows.Add(drPre);
                            dt.Rows.Add(dr);
                            //증감
                            if (!(fuelTypeId == 1 && type == 1) && !(fuelTypeId == 2 && type == 1)) {
                                drCmp = dt.NewRow();
                                drCmp.ItemArray = dr.ItemArray;
                                for (var d = 1; d <= day; d++) {
                                    drCmp[$"D{d}"] = (double)dr[$"D{d}"] - (double)dr[$"D{d}"];
                                }
                                drCmp["YearName"] = "증감";
                                drCmp["Sort"] = 3;
                                drCmp["Total"] = total - total_pre;
                                dt.Rows.Add(drCmp);
                            }
                            drCmpRate = dt.NewRow();
                            drCmpRate.ItemArray = dr.ItemArray;
                            for (var d = 1; d <= day; d++) {
                                drCmpRate[$"D{d}"] = (((double)dr[$"D{d}"] - (double)dr[$"D{d}"]) / 100).ToString("0.##");
                            }
                            drCmpRate["YearName"] = "증감%";
                            drCmpRate["Sort"] = 4;
                            drCmpRate["Total"] = ((total - total_pre) / 100).ToString("0.##");
                            dt.Rows.Add(drCmpRate);
                            dr = null; drPre = null; drCmp = null; drCmpRate = null;
                            total = 0; total_pre = 0;
                        }
                        dr = dt.NewRow();
                        drPre = dt.NewRow();
                        int lenName = item.PriceTypeName.Length >= 8 ? 8 : item.PriceTypeName.Length;
                        dr["Type"] = type;
                        dr["FuelTypeId"] = item.FuelTypeId;
                        dr["PriceTypeId"] = item.PriceTypeId;
                        dr["PriceTypeName"] = item.PriceTypeName.Substring(0, lenName);
                        dr["PriceCode"] = item.PriceCode;
                        dr["PriceCodeName"] = item.PriceCodeName;
                        dr["IsTypeFirst"] = 0;
                        for (var d = 1; d <= day; d++) {
                            dr[$"D{d}"] = 0;
                        }
                        drPre.ItemArray = dr.ItemArray;
                        dr["YearName"] = item.Year + "년";
                        dr["Sort"] = 2;
                        drPre["YearName"] = (item.Year - 1) + "년";
                        drPre["Sort"] = (item.FuelTypeId == 3 && currentCode == item.PriceTypeId && currentCode2 != item.PriceCode) ? 0 : 1;    //수도 - 상/하수도 구분 때문..
                        drPre["IsTypeFirst"] = (currentCode != item.PriceTypeId) ? 1 : 0;
                    }
                    dr[$"D{item.Date}"] = Math.Round(item.Value);
                    drPre[$"D{item.Date}"] = Math.Round(item.LastValue);
                    total += Math.Round(item.Value);
                    total_pre += Math.Round(item.LastValue);
                    currentCode = item.PriceTypeId;
                    currentCode2 = item.PriceCode;
                    fuelTypeId = item.FuelTypeId;
                }
                //마지막 데이터 안 들어감
                dr["Total"] = total;
                drPre["Total"] = total_pre;
                dt.Rows.Add(drPre);
                dt.Rows.Add(dr);
                //증감
                if (!(fuelTypeId == 1 && type == 1) && !(fuelTypeId == 2 && type == 1)) {
                    drCmp = dt.NewRow();
                    drCmp.ItemArray = dr.ItemArray;
                    for (var d = 1; d <= day; d++) {
                        drCmp[$"D{d}"] = (double)dr[$"D{d}"] - (double)dr[$"D{d}"];
                    }
                    drCmp["YearName"] = "증감";
                    drCmp["Sort"] = 3;
                    drCmp["Total"] = total - total_pre;
                    dt.Rows.Add(drCmp);
                }
                drCmpRate = dt.NewRow();
                drCmpRate.ItemArray = dr.ItemArray;
                for (var d = 1; d <= day; d++) {
                    drCmpRate[$"D{d}"] = (((double)dr[$"D{d}"] - (double)dr[$"D{d}"]) / 100).ToString("0.##");
                }
                drCmpRate["YearName"] = "증감%";
                drCmpRate["Sort"] = 4;
                drCmpRate["Total"] = ((total - total_pre) / 100).ToString("0.##");
                dt.Rows.Add(drCmpRate);
            }

            return dt;
        }

        /// <summary>
        /// 에너지 사용량 그래프
        /// </summary>
        public DataTable _GetEnergyGraph(List<EnergyReportGrid> data, DateTime today, int type) {
            DataTable dt = new DataTable();
            DataRow row = null;
            string[] colName = { "FuelTypeId", "Title", "TitleId", "PriceCode", "TabType", "LineName", "LineLastName", "Day", "Value", "LastValue", "MaxValue", "MinValue" };
            foreach (var item in colName) {
                dt.Columns.Add(item);
            }
            var day = 12;
            if (type == 0) {
                //설비가동현황
                DateTime tmpDt = DateTime.Parse(today.Year.ToString() + "-" + today.AddMonths(1).Month.ToString() + "-01");
                day = tmpDt.AddDays(-1).Day;
            }

            var data2 = data.GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.PriceCode }).Select(x => new {
                FuelTypeId = x.Key.FuelTypeId,
                PriceTypeId = x.Key.PriceTypeId,
                PriceTypeName = x.Max(f => f.PriceTypeName),
                PriceCode = x.Key.PriceCode,
                PriceCodeName = x.Max(f => f.PriceCodeName),
            }).ToList();

            foreach (var item in data2) {
                if (item.FuelTypeId == 3) {
                    //수도 - 상수도, 하수도로 구분
                    var maxValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Max(x => x.Value);
                    var maxLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Max(x => x.LastValue);
                    var minValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Min(x => x.Value);
                    var minLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Min(x => x.LastValue);

                    for (var i = 1; i <= day; i++) {
                        row = null;
                        var tmpA = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode && x.Date == i).FirstOrDefault();
                        row = dt.NewRow();
                        row["FuelTypeId"] = item.FuelTypeId;
                        row["Title"] = item.PriceCodeName.Replace("사용량", "");
                        row["TitleId"] = item.PriceTypeId;
                        row["PriceCode"] = item.PriceCode;
                        row["TabType"] = type;
                        row["LineName"] = today.Year.ToString() + "년";
                        row["LineLastName"] = today.AddYears(-1).Year.ToString() + "년";
                        row["Day"] = i;
                        row["Value"] = tmpA != null ? Math.Round(tmpA.Value) : 0;
                        row["LastValue"] = tmpA != null ? Math.Round(tmpA.LastValue) : 0;
                        row["MaxValue"] = maxValue > maxLastValue ? maxValue : maxLastValue;
                        row["MinValue"] = minValue > minLastValue ? minValue : minLastValue;
                        dt.Rows.Add(row);
                    }
                } else {
                    var maxValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Max(x => x.Value);
                    var maxLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Max(x => x.LastValue);
                    var minValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Min(x => x.Value);
                    var minLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Min(x => x.LastValue);

                    for (var i = 1; i <= day; i++) {
                        row = null;
                        var tmpA = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.Date == i).FirstOrDefault();
                        row = dt.NewRow();
                        row["FuelTypeId"] = item.FuelTypeId;
                        row["Title"] = item.PriceTypeName;
                        row["TitleId"] = item.PriceTypeId;
                        row["TabType"] = type;
                        row["LineName"] = today.Year.ToString() + "년";
                        row["LineLastName"] = today.AddYears(-1).Year.ToString() + "년";
                        row["Day"] = i;
                        row["Value"] = tmpA != null ? Math.Round(tmpA.Value) : 0;
                        row["LastValue"] = tmpA != null ? Math.Round(tmpA.LastValue) : 0;
                        row["MaxValue"] = maxValue > maxLastValue ? maxValue : maxLastValue;
                        row["MinValue"] = minValue > minLastValue ? minValue : minLastValue;
                        dt.Rows.Add(row);
                    }
                }
            }

            return dt;
        }
    }
}