using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using FMSAdmin.Data;
using FMSAdmin.Helpers;
using System.Collections;
using System.Text.RegularExpressions;
using FMSAdmin.Models;
using FMSAdmin.Entities;
using Microsoft.AspNetCore.Authorization;
using FMSAdmin.Models.Formula;
using FMSAdmin.Helpers.Formula;
using FMSAdmin.Services;
using LinqKit;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;

namespace FMSAdmin.Controllers {
    [Authorize]
    [ApiController]
    [ApiVersion("1")]
    [Route("api/[controller]")]
    public class DashboardController : Controller {
        private readonly ILogger<DashboardController> _logger;
        private readonly AlarmPointService _serviceAlarmPoint;
        private readonly EnergyUsageService _serviceEnergyUsage;
        private readonly MonthWorkService _serviceMonthWork;
        private readonly MaterialStockService _serviceMaterialStock;
        private readonly StorageHelper _storageHelper;
        private readonly FMSContext _context;

        public DashboardController(
            ILogger<DashboardController> logger,
            EnergyUsageService serviceEnergyUsage,
            AlarmPointService serviceAlarmPoint,
            MonthWorkService serviceMonthWork,
            MaterialStockService serviceMaterialStock,
            StorageHelper storageHelper,
            FMSContext context
            ) {
            _logger = logger;
            _serviceEnergyUsage = serviceEnergyUsage;
            _serviceAlarmPoint = serviceAlarmPoint;
            _serviceMonthWork = serviceMonthWork;
            _serviceMaterialStock = serviceMaterialStock;
            _storageHelper = storageHelper;
            _context = context;
        }


        /** 
         * 작업 달력 현황
         */
        [HttpGet("[action]")]
        public IActionResult WorkCalendar(int? year, int? month, int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }

            DateTime selectMonth;
            if (year == null || month == null) {
                selectMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            } else {
                selectMonth = new DateTime(year.Value, month.Value, 1);
            }
            var startDate = selectMonth.AddDays(-7);
            var endDate = selectMonth.AddDays(45);

            // 작업예정
            var requestList = _GetRequestEventList(curSiteId, startDate, endDate);

            // 작업완료
            var resultList = _GetResultEventList(curSiteId, startDate, endDate);

            // 휴일
            var holidayList = _GetHolidayList(curSiteId, selectMonth.Year, selectMonth.Month, startDate, endDate);

            // 주말
            var weekend = _context.CmHolidayWeekend.Where(
                x => x.SiteId == curSiteId
                && x.IsUse == true
            ).FirstOrDefault();

            // 주별 중점업무
            var weekList = _GetWeekKeyWorkEventList(curSiteId, startDate, endDate);

            var workName = _serviceMonthWork.GetKeyWork(curSiteId, selectMonth.Month);


            if (weekend != null) {
            }

            var result = new {
                Year = selectMonth.Year,
                Month = selectMonth.Month,
                Theme = workName,
                StartDate = startDate.ToString("yyyy-MM-dd"),
                EndDate = endDate.ToString("yyyy-MM-dd"),
                RequestList = requestList,
                ResultList = resultList,
                HolidayList = holidayList,
                Saturday = weekend?.Saturday ?? false,
                Sunday = weekend?.Sunday ?? false,
                WeekList = weekList,
            };

            return Ok(result);
        }

        /** 
         * 공지목록
         */
        [HttpGet("[action]")]
        public IActionResult SiteAnnouncement(int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }

            var query = from x in _context.CmAnnouncement
                        where ((x.SiteId == curSiteId || x.IsAllView == true) && x.UpdateDate.Date >= DateTime.Now.Date)
                        orderby x.AddDate descending
                        select new {
                            x.AnnouncementId,
                            x.SiteId,
                            SiteName = x.CmSite.Name,
                            x.Title,
                            x.Contents,
                            x.AddDate,
                            x.IsAllView,
                            IsSysop = x.CmUser.IsSysop
                        };
            return Ok(query.Take(5));
        }

        /** 
         * 공지목록 (관리자)
         */
        [HttpGet("[action]")]
        public IActionResult Announcement() {

            var query = from x in _context.CmAnnouncement
                        where (x.UpdateDate.Date >= DateTime.Now.Date)
                        orderby x.AddDate descending
                        select new {
                            x.AnnouncementId,
                            x.SiteId,
                            SiteName = x.CmSite.Name,
                            x.Title,
                            x.Contents,
                            x.AddDate,
                            x.IsAllView,
                            IsSysop = x.CmUser.IsSysop
                        };
            return Ok(query.Take(5));
        }

        /** 
         * 경보상태 (관리자)
         */
        [HttpGet("[action]")]
        public IActionResult AlarmMonitoringHistory() {
            var list = from x in _context.FmsAlarmLog
                       orderby x.CreatedDateTime descending
                       where x.CreatedDateTime.Date == DateTime.Now.Date
                       select new {
                           x.CreatedDateTime,
                           x.CurrentValue,
                           x.FacilityCode,
                           x.PropertyId,
                           Name = x.AlarmPoint.BemsMonitoringPoint.Name,
                           FacilityName = x.AlarmPoint.BemsMonitoringPoint.CmFacility.Name,
                           x.FacilityTypeId,
                           FacilityTypeName = x.AlarmPoint.FacilityType.Name,
                           x.SiteId,
                           SiteName = x.Site.Name,
                           Class1 = x.AlarmPoint.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass.Name,
                           Class2 = x.AlarmPoint.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass1.Name,
                           Class3 = x.AlarmPoint.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass2.Name,
                       };
            var returnList = list.ToList().Where(x => (int)x.CurrentValue == 1 &&
                                                        _context.FmsAlarmLog.Where(l => l.SiteId == x.SiteId
                                                                                    && l.FacilityCode == x.FacilityCode
                                                                                    && l.PropertyId == x.PropertyId
                                                                                    && l.FacilityTypeId == x.FacilityTypeId
                                                                                    && l.CreatedDateTime > x.CreatedDateTime
                                                                                    && l.CurrentValue == 0
                                                                                    ).Count() == 0).Take(5);

            return Ok(returnList);
        }

        /** 
         * 경보상태
         */
        [HttpGet("[action]")]
        public IActionResult SiteAlarmMonitoringHistory(int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }
            //var query = _serviceAlarmPoint.GetPointHistoryList();
            //query = query.Where(x => x.SiteId == curSiteId);
            var list = from x in _context.FmsAlarmLog
                       orderby x.CreatedDateTime descending
                       where x.SiteId == curSiteId
                       where x.CreatedDateTime.Date == DateTime.Now.Date
                       select new {
                           x.CreatedDateTime,
                           x.CurrentValue,
                           Name = x.AlarmPoint.BemsMonitoringPoint.Name,
                           FacilityName = x.AlarmPoint.BemsMonitoringPoint.CmFacility.Name,
                           x.FacilityTypeId,
                           FacilityTypeName = x.AlarmPoint.FacilityType.Name,
                           x.SiteId,
                           SiteName = x.Site.Name,
                           Class1 = x.AlarmPoint.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass.Name,
                           Class2 = x.AlarmPoint.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass1.Name,
                           Class3 = x.AlarmPoint.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass2.Name,
                       };
            // var list= from x in _context.BemsMonitoringPointHistory15min
            //             orderby x.CreatedDateTime descending
            //             where x.FmsAlarmPoint != null //&& x.CurrentValue != 0
            //             where x.SiteId == curSiteId
            //             where x.CreatedDateTime.Date == DateTime.Now.Date
            //             select new {
            //                 x.CreatedDateTime,
            //                 x.CurrentValue,
            //                 Name = x.BemsMonitoringPoint.Name,
            //                 FacilityName = x.BemsMonitoringPoint.CmFacility.Name,
            //                 x.FacilityTypeId,
            //                 FacilityTypeName = x.FacilityType.Name,
            //                 x.SiteId,
            //                 SiteName = x.Site.Name,
            //                 Class1 = x.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass.Name,
            //                 Class2 = x.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass1.Name,
            //                 Class3 = x.BemsMonitoringPoint.CmFacility.FmsFacilityCodeClass2.Name,
            //             };
            return Ok(list.ToList().Where(x => (int)x.CurrentValue == 1).Take(5));
        }

        /** 
         * 지도 검색 (관리자)
         */
        [HttpGet("[action]")]
        public IActionResult ImageMap() {
            var query = from x in _context.CmSite
                        where x.ImageMapX != null && x.ImageMapY != null && x.IsUse == true
                        select new {
                            x.SiteId,
                            x.Name,
                            x.ImageMapX,
                            x.ImageMapY,
                        };
            return Ok(query);
        }

        /** 
           * 오늘 작업 현황
           */
        [HttpGet("[action]")]
        public IActionResult TodayWorkSummary(string date, int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }

            var today = DateTime.Now.Date;
            if (!string.IsNullOrEmpty(date)) {
                try {
                    today = DateTime.Parse(date);
                } catch (Exception ex) {
                    today = DateTime.Now.Date;
                }
            }

            // 작업 종류별
            var resultQuery = from x in _context.FmsWorkResult
                              where x.SiteId == curSiteId
                              where x.StartDate.Date <= today && today <= x.EndDate.Date
                              where new int[] { 5, 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 5:완료, 6:확인
                              select new {
                                  x.FmsWorkRequest.WorkProgress.WorkProgressId,
                                  x.FmsWorkRequest.WorkType,
                              };

            var requestQuery = from x in _context.FmsWorkRequest
                               where x.SiteId == curSiteId
                               where x.StartWorkDate.Date >= today && x.StartWorkDate.Date <= today
                               select new {
                                   WorkProgressId = 1,
                                   x.WorkType,
                               };

            var groupQuery = from x in resultQuery.Concat(requestQuery)
                             group x by new { x.WorkType.WorkTypeId, x.WorkProgressId } into g
                             select new {
                                 WorkTypeId = g.Key.WorkTypeId,
                                 WorkProgressId = g.Key.WorkProgressId,
                                 WorkTypeName = g.Min(x => x.WorkType.Name),
                                 Count = g.Count(),
                             };


            var groupTodaySum = from x in groupQuery
                                group x by x.WorkTypeId into g
                                select new {
                                    WorkTypeId = g.Key,
                                    WorkTypeName = g.Min(x => x.WorkTypeName),
                                    계획 = g.Sum(x => x.WorkProgressId == 1 ? x.Count : 0),
                                    완료 = g.Sum(x => x.WorkProgressId != 1 ? x.Count : 0),
                                };

            var groupTodayList = from x in groupTodaySum
                                 select new {
                                     WorkTypeId = x.WorkTypeId,
                                     WorkTypeName = x.WorkTypeName,
                                     계획 = 100 - (x.계획 != 0 ? (double)x.완료 / (double)x.계획 * 100 : 0),
                                     완료 = x.계획 != 0 ? (double)x.완료 / (double)x.계획 * 100 : 0,
                                 };



            var groupToday = from x in groupQuery
                             group x by x into g
                             select new {
                                 정기점검 = g.Sum(x => x.WorkTypeId == 1 && x.WorkProgressId != 1 ? x.Count : 0),
                                 정기점검계획 = g.Sum(x => x.WorkTypeId == 1 && x.WorkProgressId == 1 ? x.Count : 0),
                                 법정검사 = g.Sum(x => x.WorkTypeId == 2 && x.WorkProgressId != 1 ? x.Count : 0),
                                 법정검사계획 = g.Sum(x => x.WorkTypeId == 2 && x.WorkProgressId == 1 ? x.Count : 0),
                                 수시점검 = g.Sum(x => x.WorkTypeId == 5 && x.WorkProgressId != 1 ? x.Count : 0),
                                 수시점검계획 = g.Sum(x => x.WorkTypeId == 5 && x.WorkProgressId == 1 ? x.Count : 0),
                                 공사작업 = g.Sum(x => x.WorkTypeId == 6 && x.WorkProgressId != 1 ? x.Count : 0),
                                 공사작업계획 = g.Sum(x => x.WorkTypeId == 6 && x.WorkProgressId == 1 ? x.Count : 0),
                                 법정교육 = g.Sum(x => x.WorkTypeId == 7 && x.WorkProgressId != 1 ? x.Count : 0),
                                 법정교육계획 = g.Sum(x => x.WorkTypeId == 7 && x.WorkProgressId == 1 ? x.Count : 0),
                             };


            var todayInfo = new {
                정기점검 = groupToday.Sum(x => x.정기점검),
                정기점검계획 = groupToday.Sum(x => x.정기점검계획),
                법정검사 = groupToday.Sum(x => x.법정검사),
                법정검사계획 = groupToday.Sum(x => x.법정검사계획),
                수시점검 = groupToday.Sum(x => x.수시점검),
                수시점검계획 = groupToday.Sum(x => x.수시점검계획),
                공사작업 = groupToday.Sum(x => x.공사작업),
                공사작업계획 = groupToday.Sum(x => x.공사작업계획),
                법정교육 = groupToday.Sum(x => x.법정교육),
                법정교육계획 = groupToday.Sum(x => x.법정교육계획),
            };



            return Ok(new {
                todayInfo = todayInfo,
                list = groupTodayList,
            });
        }

        /** 
         * 승인대기란
         */
        [HttpGet("[action]")]
        public IActionResult ApporovalSummary(int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }

            var today = DateTime.Now.Date;
            var startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);


            // 작업 진행  -- 업무 
            var working = (from x in _context.FmsWorkResult
                           where x.SiteId == curSiteId
                           where x.StartDate.Date <= today && today <= x.EndDate.Date
                           where new int[] { 4 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 4:진행
                           select x).Count();

            // 자재 입고 
            var materialStored = (from x in _context.FmsMaterialStored
                                  where x.SiteId == curSiteId
                                  where x.StoredDate >= today
                                  where x.IsApproval == true
                                  select x).Count();

            //자재조정
            var materialRelease = (from x in _context.FmsMaterialRelease
                                   where x.SiteId == curSiteId
                                   where new int[] { 1, 2 }.Contains(x.AdjustmentTypeId) // 반납,손실
                                   where x.ReleaseDate >= today
                                   select x).Count();

            //공기구
            var equipment = (from x in _context.FmsEquipment
                             where x.SiteId == curSiteId
                             where x.AddDate >= today
                             select x).Count();

            //일지
            var reportJob = (from x in _context.BemsReportHistory
                             where x.SiteId == curSiteId
                             where x.CreateDate >= today
                             select x).Count();

            //보고서 
            var report = (from x in _context.FmsDailyReport
                          where x.SiteId == curSiteId
                          where x.AddDate >= today
                          select x).Count();

            //자재부족
            var materialStock = _serviceMaterialStock.Stock();
            materialStock = materialStock.Where(x => x.Material.SiteId == curSiteId);
            var stockList = materialStock.Select(x => new {
                InsufficientStock = Util.ToLong(x.Material.ReasonableStockCount) - x.StockCount
            }).ToList();
            var InsufficientStock = stockList.Count() != 0 ? stockList.Where(x => x.InsufficientStock > 0).Count() : 0;


            return Ok(new {
                work = new {
                    업무 = working,
                    자재입고 = materialStored,
                    자재조정 = materialRelease,
                    공기구 = equipment,
                    일지 = reportJob,
                    보고서 = report,
                    자재부족 = InsufficientStock
                }
            });
        }


        /** 
         * 작업 현황(상태별)
         */
        [HttpGet("[action]")]
        public IActionResult WorkStateSummary(int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }

            var today = DateTime.Now.Date;
            var startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);

            //작업 대기
            var wating = (from x in _context.FmsWorkRequest
                          where x.SiteId == curSiteId
                          where x.StartWorkDate.Date == today
                          where new int[] { 3 }.Contains(x.WorkProgress.WorkProgressId) // 3:대기
                          select x).Count();

            // 작업 진행
            var working = (from x in _context.FmsWorkResult
                           where x.SiteId == curSiteId
                           where x.StartDate.Date <= today && today <= x.EndDate.Date
                           where new int[] { 4 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 4:진행
                           select x).Count();

            // 작업 보류 (2020-03-12요청 전체로)
            var holding = (from x in _context.FmsWorkResult
                           where x.SiteId == curSiteId
                           //where x.StartDate.Date <= today && today <= x.EndDate.Date
                           where new int[] { 7 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 7:보류
                           select x).Count();

            // 작업 완료
            var completed = (from x in _context.FmsWorkResult
                             where x.SiteId == curSiteId
                             where x.EndDate.Date == today
                             where new int[] { 5 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 5:완료
                             select x).Count();

            // 작업 승인
            var approvaled = (from x in _context.FmsWorkResult
                              where x.SiteId == curSiteId
                              where x.ConfirmedDate.Value.Date == today
                              where new int[] { 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 6:승인
                              select x).Count();

            // 미결 작업 (2020-03-12요청 전체로)
            var unapprovaled = (from x in _context.FmsWorkResult
                                where x.SiteId == curSiteId
                                where new int[] { 5 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 5:완료
                                select x).Count();

            // 미결 일별보고서 (2020-03-12요청 전체로)
            var unapprovalReport = (from x in _context.FmsDailyReport
                                    where x.SiteId == curSiteId
                                    //where x.AddDate.Date == today
                                    where x.ApproverUserId == null
                                    select x).Count();

            return Ok(new {
                work = new {
                    작업대기 = wating,
                    작업진행 = working,
                    작업완료 = completed,
                    작업승인 = approvaled,
                    작업보류 = holding,
                    작업미승인 = unapprovaled,
                },
                report = new {
                    미결일지 = unapprovalReport
                }
            });
        }


        /** 
         * (시설관리>업무분야별 작업현황)
         */
        [HttpGet("[action]")]
        public IActionResult DailyWorkSummary(int? siteId) {
            if (siteId == null) siteId = User.GetSiteId();

            var startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            var endDate = startDate.AddMonths(1).AddDays(-1);

            var query = from x in _context.FmsWorkResult
                        where x.SiteId == siteId
                        where x.EndDate.Date >= startDate && x.EndDate.Date <= endDate
                        select new {
                            x.FmsWorkRequest,
                            x.FmsWorkRequest.WorkProgress,
                            x.FmsWorkRequest.CmBusinessField,
                        };

            var groupQuery = from x in query
                             group x by new { x.FmsWorkRequest.WorkProgressId, x.FmsWorkRequest.BusinessFieldId } into g
                             select new {
                                 BusinessFieldId = g.Key.BusinessFieldId,
                                 BusinessFieldName = g.Min(x => x.CmBusinessField.Name),
                                 WorkProgressId = g.Key.WorkProgressId,
                                 WorkProgressName = g.Min(x => x.WorkProgress.Name),
                                 Count = g.Count(),
                             };

            var groupBusiness = from x in groupQuery
                                group x by x.BusinessFieldId into g
                                select new {
                                    BusinessFieldId = g.Key,
                                    BusinessFieldName = g.Min(x => x.BusinessFieldName),
                                    작업대기 = g.Sum(x => x.WorkProgressId == 3 ? x.Count : 0),
                                    작업진행 = g.Sum(x => x.WorkProgressId == 4 ? x.Count : 0),
                                    작업완료 = g.Sum(x => x.WorkProgressId == 5 ? x.Count : 0),
                                    작업승인 = g.Sum(x => x.WorkProgressId == 6 ? x.Count : 0),
                                    작업보류 = g.Sum(x => x.WorkProgressId == 7 ? x.Count : 0),
                                };

            return Ok(new {
                year = startDate.Year,
                month = startDate.Month,
                list = groupBusiness
            });
        }

        /** 
         * 월별 작업 현황 (현장용)
         */
        [HttpGet("[action]")]
        public IActionResult MonthlyWorkSummary(int? year, int? month, int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }

            DateTime startDate;
            if (year == null || month == null) {
                startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            } else {
                startDate = new DateTime(year.Value, month.Value, 1);
            }
            var endDate = startDate.AddMonths(1).AddDays(-1);

            var query = from x in _context.FmsWorkResult
                        where x.SiteId == curSiteId
                        where x.EndDate.Date >= startDate && x.EndDate.Date <= endDate
                        where new int[] { 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 07/28 승인건만 완료로 노출
                        select new {
                            x.FmsWorkRequest,
                            x.FmsWorkRequest.WorkType,
                            x.FmsWorkRequest.CmBusinessField,
                        };

            var groupQuery = from x in query
                             group x by new { x.FmsWorkRequest.WorkTypeId, x.FmsWorkRequest.BusinessFieldId } into g
                             select new {
                                 BusinessFieldId = g.Key.BusinessFieldId,
                                 BusinessFieldName = g.Min(x => x.CmBusinessField.Name),
                                 WorkTypeId = g.Key.WorkTypeId,
                                 WorkTypeName = g.Min(x => x.WorkType.Name),
                                 Count = g.Count(),
                             };

            var groupBusiness = from x in groupQuery
                                group x by x.BusinessFieldId into g
                                select new {
                                    BusinessFieldId = g.Key,
                                    BusinessFieldName = g.Min(x => x.BusinessFieldName),
                                    정기점검 = g.Sum(x => x.WorkTypeId == 1 ? x.Count : 0),
                                    법정검사 = g.Sum(x => x.WorkTypeId == 2 ? x.Count : 0),
                                    수시점검 = g.Sum(x => x.WorkTypeId == 5 ? x.Count : 0),
                                    공사작업 = g.Sum(x => x.WorkTypeId == 6 ? x.Count : 0),
                                    법정교육 = g.Sum(x => x.WorkTypeId == 7 ? x.Count : 0),
                                };

            return Ok(new {
                year = startDate.Year,
                month = startDate.Month,
                list = groupBusiness
            });
        }

        /** 
         * 월별 작업 현황 (관리자)
         */
        [HttpGet("[action]")]
        public IActionResult MonthlySiteWorkSummary(int? year, int? month) {
            DateTime startDate;
            if (year == null || month == null) {
                startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            } else {
                startDate = new DateTime(year.Value, month.Value, 1);
            }
            var endDate = startDate.AddMonths(1).AddDays(-1);

            var query = from x in _context.FmsWorkResult
                        where x.EndDate.Date >= startDate && x.EndDate.Date <= endDate
                        where new int[] { 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 07/28 승인건만 완료로 노출
                        select new {
                            x.FmsWorkRequest,
                            x.FmsWorkRequest.Site,
                            x.FmsWorkRequest.WorkType,
                            x.FmsWorkRequest.CmBusinessField,
                        };

            var groupQuery = from x in query
                             group x by new { x.FmsWorkRequest.WorkTypeId, x.Site.SiteId } into g
                             select new {
                                 SiteId = g.Key.SiteId,
                                 SiteName = g.Min(x => x.Site.Name),
                                 SortOrderNo = g.Min(x => x.Site.SortOrderNo),
                                 WorkTypeId = g.Key.WorkTypeId,
                                 WorkTypeName = g.Min(x => x.WorkType.Name),
                                 Count = g.Count(),
                             };

            var groupBusiness = from x in groupQuery
                                group x by x.SiteId into g
                                select new {
                                    SiteId = g.Key,
                                    SiteName = g.Min(x => x.SiteName),
                                    SortOrderNo = g.Min(x => x.SortOrderNo),
                                    정기점검 = g.Sum(x => x.WorkTypeId == 1 ? x.Count : 0),
                                    법정검사 = g.Sum(x => x.WorkTypeId == 2 ? x.Count : 0),
                                    수시점검 = g.Sum(x => x.WorkTypeId == 5 ? x.Count : 0),
                                    공사작업 = g.Sum(x => x.WorkTypeId == 6 ? x.Count : 0),
                                    법정교육 = g.Sum(x => x.WorkTypeId == 7 ? x.Count : 0),
                                };

            return Ok(new {
                year = startDate.Year,
                month = startDate.Month,
                list = groupBusiness.OrderBy(x => x.SortOrderNo)
            });
        }

        /** 
        * 월별 작업 현황 상세 (관리자)
        */
        [HttpGet("[action]")]
        public IActionResult MonthlySiteWorkDetail(int? siteId, int? year, int? month) {
            DateTime startDate;
            if (year == null || month == null) {
                startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            } else {
                startDate = new DateTime(year.Value, month.Value, 1);
            }
            var endDate = startDate.AddMonths(1).AddDays(-1);

            var resultQuery = from x in _context.FmsWorkResult
                              where x.EndDate.Date >= startDate && x.EndDate.Date <= endDate
                              where new int[] { 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 승인건만 완료로노출
                              where x.SiteId == siteId
                              select new {
                                  x.FmsWorkRequest.Site,
                                  x.FmsWorkRequest.WorkType,
                                  x.FmsWorkRequest.WorkProgress.WorkProgressId,
                              };

            var requestQuery = from x in _context.FmsWorkRequest
                               where x.StartWorkDate.Date >= startDate && x.StartWorkDate.Date <= endDate
                               //  where new int[] { 1 }.Contains(x.WorkProgress.WorkProgressId) // 1: 계획
                               where x.SiteId == siteId
                               select new {
                                   x.Site,
                                   x.WorkType,
                                   WorkProgressId = 1,
                               };

            var groupQuery = from x in resultQuery.Concat(requestQuery)
                             group x by new { x.WorkType.WorkTypeId } into g
                             select new {
                                 SiteId = g.Min(x => x.Site.SiteId),
                                 SiteName = g.Min(x => x.Site.Name),
                                 TypeName = g.Min(x => x.WorkType.Name),
                                 계획 = g.Sum(x => x.WorkProgressId == 1 ? 1 : 0),
                                 완료 = g.Sum(x => x.WorkProgressId == 6 ? 1 : 0),
                             };
            return Ok(new {
                year = startDate.Year,
                month = startDate.Month,
                list = groupQuery,
            });
        }

        /** 
                    * 금일 작업 현황 상세 (현장)
                    */
        [HttpGet("[action]")]
        public IActionResult DailiySiteWorkDetail(int? siteId, string date, int? businessId) {
            var today = DateTime.Now.Date;
            if (!string.IsNullOrEmpty(date)) {
                try {
                    today = DateTime.Parse(date);
                } catch (Exception ex) {
                    today = DateTime.Now.Date;
                }
            }

            var resultQuery = from x in _context.FmsWorkResult
                              where x.EndDate.Date == today.Date
                              where new int[] { 5, 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 5:완료, 6:확인]
                              where x.SiteId == siteId
                              where x.FmsWorkRequest.BusinessFieldId == businessId
                              select new {
                                  x.FmsWorkRequest.Site,
                                  x.FmsWorkRequest.WorkType,
                                  x.FmsWorkRequest.WorkProgress.WorkProgressId,
                                  x.FmsWorkRequest.CmBusinessField,
                              };

            var requestQuery = from x in _context.FmsWorkRequest
                               where x.StartWorkDate.Date == today.Date
                               where x.SiteId == siteId
                               where x.BusinessFieldId == businessId
                               select new {
                                   x.Site,
                                   x.WorkType,
                                   WorkProgressId = 1,
                                   x.CmBusinessField
                               };

            var groupQuery = from x in resultQuery.Concat(requestQuery)
                             group x by new { x.CmBusinessField.BusinessFieldId, x.WorkType.WorkTypeId } into g
                             select new {
                                 SiteId = g.Min(x => x.Site.SiteId),
                                 SiteName = g.Min(x => x.Site.Name),
                                 BusinessName = g.Min(x => x.CmBusinessField.Name),
                                 TypeName = g.Min(x => x.WorkType.Name),
                                 계획 = g.Sum(x => x.WorkProgressId == 1 ? 1 : 0),
                                 완료 = g.Sum(x => x.WorkProgressId == 5 ? 1 : 0),
                                 승인 = g.Sum(x => x.WorkProgressId == 6 ? 1 : 0),
                             };

            return Ok(new {
                list = groupQuery,
            });
        }


        /** 
            * 월별 작업 현황 상세 (현장)
            */
        [HttpGet("[action]")]
        public IActionResult MonthlyWorkDetail(int? siteId, int? year, int? month, int? businessId) {
            DateTime startDate;
            if (year == null || month == null) {
                startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            } else {
                startDate = new DateTime(year.Value, month.Value, 1);
            }
            var endDate = startDate.AddMonths(1).AddDays(-1);

            var resultQuery = from x in _context.FmsWorkResult
                              where x.EndDate.Date >= startDate && x.EndDate.Date <= endDate
                              where new int[] { 6 }.Contains(x.FmsWorkRequest.WorkProgress.WorkProgressId) // 승인건만 완료로노출
                              where x.SiteId == siteId
                              where x.FmsWorkRequest.BusinessFieldId == businessId
                              select new {
                                  x.FmsWorkRequest.Site,
                                  x.FmsWorkRequest.WorkType,
                                  x.FmsWorkRequest.WorkProgress.WorkProgressId,
                                  x.FmsWorkRequest.CmBusinessField,
                              };

            var requestQuery = from x in _context.FmsWorkRequest
                               where x.StartWorkDate.Date >= startDate && x.StartWorkDate.Date <= endDate
                               //where new int[] { 1 }.Contains(x.WorkProgress.WorkProgressId) // 1: 계획
                               where x.SiteId == siteId
                               where x.BusinessFieldId == businessId
                               select new {
                                   x.Site,
                                   x.WorkType,
                                   WorkProgressId = 1,
                                   x.CmBusinessField
                               };

            var groupQuery = from x in resultQuery.Concat(requestQuery)
                             group x by new { x.CmBusinessField.BusinessFieldId, x.WorkType.WorkTypeId } into g
                             select new {
                                 SiteId = g.Min(x => x.Site.SiteId),
                                 SiteName = g.Min(x => x.Site.Name),
                                 BusinessName = g.Min(x => x.CmBusinessField.Name),
                                 TypeName = g.Min(x => x.WorkType.Name),
                                 계획 = g.Sum(x => x.WorkProgressId == 1 ? 1 : 0),
                                 완료 = g.Sum(x => x.WorkProgressId == 6 ? 1 : 0),
                             };

            return Ok(new {
                year = startDate.Year,
                month = startDate.Month,
                list = groupQuery,
            });
        }


        /**
         * 사이트별 월별 에너지
         */
        [HttpGet("[action]")]
        public IActionResult EnergyWholeMonthSite(int? siteId, EnergyType? type, string month) {
            var start = DateTime.Parse(month + "-01");
            var end = start.Date.AddMonths(1).AddDays(-1);

            return EnergyDaily(type, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"), siteId);
        }


        /**
            * 사이트별 연도별 에너지
            */
        [HttpGet("[action]")]
        public IActionResult EnergyWholeYearSite(int? siteId, EnergyType? type, string year) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }
            if (Util.IsNullOrEmpty(year)) year = DateTime.Now.ToString("yyyy");
            var start = DateTime.Parse(year + "-01-01");
            var end = DateTime.Parse(year + "-12-31");

            var lastStart = start.Date.AddYears(-1);
            var lastEnd = end.Date.AddYears(-1);
            IList<EnergyDaily> list = new List<EnergyDaily>();

            var cacheFile = $"/temp/_EnergyWholeYearSite_{siteId ?? 0}_{(type == null ? "전체" : type.ToString())}_{year}.json";
            if (_storageHelper.FileExists(cacheFile)) {
                var jsonText = _storageHelper.ReadAllText(cacheFile);
                return Ok(new {
                    type,
                    list = JsonConvert.DeserializeObject(jsonText)
                });
            }

            try {
                IDictionary<string, CalculationResult> electMap = null;
                IDictionary<string, CalculationResult> lastelectMap = null;
                IDictionary<string, CalculationResult> gasMap = null;
                IDictionary<string, CalculationResult> lastgasMap = null;
                IDictionary<string, CalculationResult> waterMap = null;
                IDictionary<string, CalculationResult> lastwaterMap = null;
                IDictionary<string, CalculationResult> lampOilMap = null;
                IDictionary<string, CalculationResult> lastlampOilMap = null;
                IDictionary<string, CalculationResult> lpgMap = null;
                IDictionary<string, CalculationResult> lastlpgMap = null;
                IDictionary<string, CalculationResult> mediumWaterMap = null;
                IDictionary<string, CalculationResult> lastmediumWaterMap = null;

                try {
                    if (type == null || type.Value == EnergyType.전기) {
                        electMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.전기, start, end);
                        lastelectMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.전기, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.가스) {
                        gasMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.가스, start, end);
                        lastgasMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.가스, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.수도) {
                        waterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.수도, start, end);
                        lastwaterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.수도, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.등유) {
                        lampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.등유, start, end);
                        lastlampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.등유, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.LPG) {
                        lpgMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.LPG, start, end);
                        lastlpgMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.LPG, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.중온수) {
                        mediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.중온수, start, end);
                        lastmediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.월, EnergyType.중온수, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }

                var months = (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
                var date = start;
                var lastDate = lastStart;
                for (var i = 0; i < months; i++) {
                    var dateStr = date.ToString("yyyy-MM-dd");
                    var lastStr = lastDate.ToString("yyyy-MM-dd");

                    list.Add(new EnergyDaily {
                        Date = date.ToString("yyyy-MM"),
                        전기 = electMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_전기 = lastelectMap?.GetValue(lastStr)?.Value ?? 0,
                        가스 = gasMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_가스 = lastgasMap?.GetValue(lastStr)?.Value ?? 0,
                        수도 = waterMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_수도 = lastwaterMap?.GetValue(lastStr)?.Value ?? 0,
                        등유 = lampOilMap?.GetValue(dateStr).Value ?? 0,
                        이전_등유 = lastlampOilMap?.GetValue(lastStr).Value ?? 0,
                        LPG = lpgMap?.GetValue(dateStr).Value ?? 0,
                        이전_LPG = lastlpgMap?.GetValue(lastStr).Value ?? 0,
                        중온수 = mediumWaterMap?.GetValue(dateStr).Value ?? 0,
                        이전_중온수 = lastmediumWaterMap?.GetValue(lastStr).Value ?? 0
                    });
                    date = date.AddMonths(1);
                    lastDate = lastDate.AddMonths(1);
                }

                if (type == null)
                    list = convertTOESite(list, start, end, EnergyInterval.월);

                if (list != null) {
                    _storageHelper.WriteAllText(cacheFile, JsonConvert.SerializeObject(list));
                }

                return Ok(new {
                    type,
                    list
                });
            } catch (FormatException ex) {
                _logger.LogError("", ex.ToString());
                throw new Exception("날짜형식을 올바르게 입력해주세요");
            }
        }


        public IList<EnergyDaily> convertTOESite(IList<EnergyDaily> list, DateTime start, DateTime end, EnergyInterval interval) {
            var date = start;
            var returnList = new List<EnergyDaily>();
            var days = interval == EnergyInterval.월 ? (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1 : end.Subtract(start).Days;
            var dateStrFormat = interval == EnergyInterval.월 ? "yyyy-MM" : "yyyy-MM-dd";

            for (var i = 0; i < days; i++) {
                var dateStr = date.ToString(dateStrFormat);
                var temp = list.Where(x => x.Date == dateStr).FirstOrDefault();
                var toe = 0.0;
                var pre_toe = 0.0;
                toe += getTOE(EnergyType.전기, temp.전기);
                pre_toe += getTOE(EnergyType.전기, temp.이전_전기);
                toe += getTOE(EnergyType.가스, temp.가스);
                pre_toe += getTOE(EnergyType.가스, temp.이전_가스);
                toe += getTOE(EnergyType.등유, temp.등유);
                pre_toe += getTOE(EnergyType.등유, temp.이전_등유);
                toe += getTOE(EnergyType.LPG, temp.LPG);
                pre_toe += getTOE(EnergyType.LPG, temp.이전_LPG);
                toe += getTOE(EnergyType.중온수, temp.중온수);
                pre_toe += getTOE(EnergyType.중온수, temp.이전_중온수);

                returnList.Add(new EnergyDaily {
                    Date = dateStr,
                    TOE = Math.Round(toe, 2),
                    이전_TOE = Math.Round(pre_toe, 2),
                });
                date = interval == EnergyInterval.월 ? date.AddMonths(1) : date.AddDays(1);
            }
            return returnList;
        }

        /**
         * 사이트별 일별 에너지
         */
        [HttpGet("[action]")]
        public IActionResult EnergyDaily(EnergyType? type, string startDate, string endDate, int? siteId) {
            var curSiteId = User.GetSiteId();
            if (siteId != null) {
                curSiteId = siteId.Value;
            }
            _logger.LogInformation("siteId:" + curSiteId);

            IList<EnergyDaily> list = new List<EnergyDaily>();

            try {
                var start = DateTime.Now.Date.AddDays(-2);
                if (!string.IsNullOrEmpty(startDate)) {
                    start = DateTime.Parse(startDate);
                }
                var end = DateTime.Now.Date;
                if (!string.IsNullOrEmpty(endDate)) {
                    end = DateTime.Parse(endDate);
                }

                var lastStart = start.Date.AddMonths(-1);
                var lastEnd = end.Date.AddMonths(-1);
                IDictionary<string, CalculationResult> electMap = null;
                IDictionary<string, CalculationResult> lastelectMap = null;
                IDictionary<string, CalculationResult> gasMap = null;
                IDictionary<string, CalculationResult> lastgasMap = null;
                IDictionary<string, CalculationResult> waterMap = null;
                IDictionary<string, CalculationResult> lastwaterMap = null;
                IDictionary<string, CalculationResult> lampOilMap = null;
                IDictionary<string, CalculationResult> lastlampOilMap = null;
                IDictionary<string, CalculationResult> lpgMap = null;
                IDictionary<string, CalculationResult> lastlpgMap = null;
                IDictionary<string, CalculationResult> mediumWaterMap = null;
                IDictionary<string, CalculationResult> lastmediumWaterMap = null;


                try {
                    if (type == null || type.Value == EnergyType.전기) {
                        electMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.전기, start, end);
                        lastelectMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.전기, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.가스) {
                        gasMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.가스, start, end);
                        lastgasMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.가스, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.수도) {
                        waterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.수도, start, end);
                        lastwaterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.수도, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.등유) {
                        lampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.등유, start, end);
                        lastlampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.등유, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.LPG) {
                        lpgMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.LPG, start, end);
                        lastlpgMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.LPG, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                try {
                    if (type == null || type.Value == EnergyType.중온수) {
                        mediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.중온수, start, end);
                        lastmediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(curSiteId, EnergyInterval.일, EnergyType.중온수, lastStart, lastEnd);
                    }
                } catch (Exception ex) {
                    _logger.LogInformation(ex, "");
                }
                // 취합
                var days = end.Subtract(start).Days + 1;
                _logger.LogInformation("days " + days);
                var date = start;

                for (var i = 0; i < days; i++) {
                    var dateStr = date.ToString("yyyy-MM-dd");
                    var lastStr = date.Date.AddMonths(-1).ToString("yyyy-MM-dd");

                    list.Add(new EnergyDaily {
                        Date = dateStr,
                        전기 = electMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_전기 = lastelectMap?.GetValue(lastStr)?.Value ?? 0,
                        가스 = gasMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_가스 = lastgasMap?.GetValue(lastStr)?.Value ?? 0,
                        수도 = waterMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_수도 = lastwaterMap?.GetValue(lastStr)?.Value ?? 0,
                        등유 = lampOilMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_등유 = lastlampOilMap?.GetValue(lastStr)?.Value ?? 0,
                        LPG = lpgMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_LPG = lastlpgMap?.GetValue(lastStr)?.Value ?? 0,
                        중온수 = mediumWaterMap?.GetValue(dateStr)?.Value ?? 0,
                        이전_중온수 = lastmediumWaterMap?.GetValue(lastStr)?.Value ?? 0

                    });
                    date = date.AddDays(1);
                }

                if (type == null)
                    list = convertTOESite(list, start, end, EnergyInterval.일);
                return Ok(new {
                    type,
                    list
                });
            } catch (FormatException ex) {
                _logger.LogInformation(ex, "");
                throw new Exception("날짜형식을 올바르게 입력해주세요");
            }
        }



        /**
        * 전체 연도별 에너지
        */
        [HttpGet("[action]")]
        public IActionResult EnergyWholeYear(EnergyType? type, string year) {

            if (Util.IsNullOrEmpty(year)) year = DateTime.Now.ToString("yyyy");
            var start = DateTime.Parse(year + "-01-01");
            var end = DateTime.Parse(year + "-12-31");
            var lastStart = start.Date.AddYears(-1);
            var lastEnd = end.Date.AddYears(-1);
            IDictionary<string, EnergyDaily> list = new Dictionary<string, EnergyDaily>();

            var cacheFile = $"/temp/_EnergyWholeYear_{(type == null ? "전체" : type.ToString())}_{year}.json";
            if (_storageHelper.FileExists(cacheFile)) {
                var jsonText = _storageHelper.ReadAllText(cacheFile);
                return Ok(new {
                    type,
                    list = JsonConvert.DeserializeObject(jsonText)
                });
            }

            try {
                var isTOE = type == null ? true : false;
                var siteList = _context.CmSite.ToList();
                foreach (var site in siteList) {
                    var siteId = site.SiteId;

                    //_logger.LogInformation("siteId:" + siteId);

                    IDictionary<string, CalculationResult> electMap = null;
                    IDictionary<string, CalculationResult> lastelectMap = null;
                    IDictionary<string, CalculationResult> gasMap = null;
                    IDictionary<string, CalculationResult> lastgasMap = null;
                    IDictionary<string, CalculationResult> waterMap = null;
                    IDictionary<string, CalculationResult> lastwaterMap = null;
                    IDictionary<string, CalculationResult> lampOilMap = null;
                    IDictionary<string, CalculationResult> lastlampOilMap = null;
                    IDictionary<string, CalculationResult> lpgMap = null;
                    IDictionary<string, CalculationResult> lastlpgMap = null;
                    IDictionary<string, CalculationResult> mediumWaterMap = null;
                    IDictionary<string, CalculationResult> lastmediumWaterMap = null;

                    try {
                        if (type == null || type.Value == EnergyType.전기) {
                            electMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.전기, start, end);
                            lastelectMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.전기, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.가스) {
                            gasMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.가스, start, end);
                            lastgasMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.가스, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.수도) {
                            waterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.수도, start, end);
                            lastwaterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.수도, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.등유) {
                            lampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.등유, start, end);
                            lastlampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.등유, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.LPG) {
                            lpgMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.LPG, start, end);
                            lastlpgMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.LPG, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.중온수) {
                            mediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.중온수, start, end);
                            lastmediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.월, EnergyType.중온수, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }

                    var months = (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
                    var date = start;
                    var lastDate = lastStart;

                    for (var i = 0; i < months; i++) {
                        var dateStr = date.ToString("yyyy-MM-dd");
                        var lastStr = lastDate.ToString("yyyy-MM-dd");

                        if (list.ContainsKey(dateStr)) {
                            list[dateStr].전기 += electMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].가스 += gasMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].수도 += waterMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].등유 += lampOilMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].LPG += lpgMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].중온수 += mediumWaterMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].이전_전기 += lastelectMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_가스 += lastgasMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_수도 += lastwaterMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_등유 += lastlampOilMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_LPG += lastlpgMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_중온수 += lastmediumWaterMap?.GetValue(lastStr)?.Value ?? 0;
                        } else {
                            list.Add(dateStr, new EnergyDaily {
                                Date = date.ToString("yyyy-MM"),
                                전기 = electMap?.GetValue(dateStr)?.Value ?? 0,
                                이전_전기 = lastelectMap?.GetValue(lastStr)?.Value ?? 0,
                                가스 = gasMap?.GetValue(dateStr)?.Value ?? 0,
                                이전_가스 = lastgasMap?.GetValue(lastStr)?.Value ?? 0,
                                수도 = waterMap?.GetValue(dateStr)?.Value ?? 0,
                                이전_수도 = lastwaterMap?.GetValue(lastStr)?.Value ?? 0,
                                등유 = lampOilMap?.GetValue(dateStr).Value ?? 0,
                                이전_등유 = lastlampOilMap?.GetValue(lastStr).Value ?? 0,
                                LPG = lpgMap?.GetValue(dateStr).Value ?? 0,
                                이전_LPG = lastlpgMap?.GetValue(lastStr).Value ?? 0,
                                중온수 = mediumWaterMap?.GetValue(dateStr).Value ?? 0,
                                이전_중온수 = lastmediumWaterMap?.GetValue(lastStr).Value ?? 0
                            });
                        }
                        date = date.AddMonths(1);
                        lastDate = lastDate.AddMonths(1);
                    }
                }
                if (type == null)
                    list = convertTOE(list, start, end, EnergyInterval.월);


                if (list != null) {
                    _storageHelper.WriteAllText(cacheFile, JsonConvert.SerializeObject(list.Values));
                }
                return Ok(new {
                    type,
                    list = list.Values
                });
            } catch (FormatException ex) {
                _logger.LogError("", ex.ToString());
                throw new Exception("날짜형식을 올바르게 입력해주세요");
            }
        }

        /**
       * 전체 월별 에너지
       */
        [HttpGet("[action]")]
        public IActionResult EnergyWholeMonth(EnergyType? type, string month) {
            var start = DateTime.Parse(month + "-01");
            var end = start.Date.AddMonths(1).AddDays(-1);

            return EnergyWholeDaily(type, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));
        }

        /**
         * 전체 일별 에너지
         */
        [HttpGet("[action]")]
        public IActionResult EnergyWholeDaily(EnergyType? type, string startDate, string endDate) {

            IDictionary<string, EnergyDaily> list = new Dictionary<string, EnergyDaily>();

            try {
                var isTOE = type == null ? true : false;

                var start = DateTime.Now.Date.AddDays(-2);
                if (!string.IsNullOrEmpty(startDate)) {
                    start = DateTime.Parse(startDate);
                }
                var end = DateTime.Now.Date;
                if (!string.IsNullOrEmpty(endDate)) {
                    end = DateTime.Parse(endDate);
                }

                var lastStart = start.Date.AddMonths(-1);
                var lastEnd = end.Date.AddMonths(-1);

                var siteList = _context.CmSite.ToList();
                _logger.LogInformation("site count:" + siteList.Count());
                foreach (var site in siteList) {
                    var siteId = site.SiteId;

                    _logger.LogInformation("siteId:" + siteId);

                    IDictionary<string, CalculationResult> electMap = null;
                    IDictionary<string, CalculationResult> lastelectMap = null;
                    IDictionary<string, CalculationResult> gasMap = null;
                    IDictionary<string, CalculationResult> lastgasMap = null;
                    IDictionary<string, CalculationResult> waterMap = null;
                    IDictionary<string, CalculationResult> lastwaterMap = null;
                    IDictionary<string, CalculationResult> lampOilMap = null;
                    IDictionary<string, CalculationResult> lastlampOilMap = null;
                    IDictionary<string, CalculationResult> lpgMap = null;
                    IDictionary<string, CalculationResult> lastlpgMap = null;
                    IDictionary<string, CalculationResult> mediumWaterMap = null;
                    IDictionary<string, CalculationResult> lastmediumWaterMap = null;

                    try {
                        if (type == null || type.Value == EnergyType.전기) {
                            electMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.전기, start, end);
                            lastelectMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.전기, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.가스) {
                            gasMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.가스, start, end);
                            lastgasMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.가스, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.수도) {
                            waterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.수도, start, end);
                            lastwaterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.수도, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.등유) {
                            lampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.등유, start, end);
                            lastlampOilMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.등유, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.LPG) {
                            lpgMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.LPG, start, end);
                            lastlpgMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.LPG, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    try {
                        if (type == null || type.Value == EnergyType.중온수) {
                            mediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.중온수, start, end);
                            lastmediumWaterMap = _serviceEnergyUsage.GetEnergyDailyDic(siteId, EnergyInterval.일, EnergyType.중온수, lastStart, lastEnd);
                        }
                    } catch (Exception ex) {
                        _logger.LogInformation(ex, "");
                    }
                    // 취합
                    var days = end.Subtract(start).Days + 1;
                    _logger.LogInformation("days:" + days);
                    var date = start;
                    var lastDate = lastStart;

                    for (var i = 0; i < days; i++) {
                        var dateStr = date.ToString("yyyy-MM-dd");
                        var lastStr = lastDate.ToString("yyyy-MM-dd");
                        if (list.ContainsKey(dateStr)) {
                            list[dateStr].전기 += electMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].가스 += gasMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].수도 += waterMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].등유 += lampOilMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].LPG += lpgMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].중온수 += mediumWaterMap?.GetValue(dateStr)?.Value ?? 0;
                            list[dateStr].이전_전기 += lastelectMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_가스 += lastgasMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_수도 += lastwaterMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_등유 += lastlampOilMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_LPG += lastlpgMap?.GetValue(lastStr)?.Value ?? 0;
                            list[dateStr].이전_중온수 += lastmediumWaterMap?.GetValue(lastStr)?.Value ?? 0;

                        } else {
                            list.Add(dateStr, new EnergyDaily {
                                Date = date.ToString("yyyy-MM-dd"),
                                전기 = electMap?.GetValue(dateStr)?.Value ?? 0,
                                이전_전기 = lastelectMap?.GetValue(lastStr)?.Value ?? 0,
                                가스 = gasMap?.GetValue(dateStr)?.Value ?? 0,
                                이전_가스 = lastgasMap?.GetValue(lastStr)?.Value ?? 0,
                                수도 = waterMap?.GetValue(dateStr)?.Value ?? 0,
                                이전_수도 = lastwaterMap?.GetValue(lastStr)?.Value ?? 0,
                                등유 = lampOilMap?.GetValue(dateStr).Value ?? 0,
                                이전_등유 = lastlampOilMap?.GetValue(lastStr).Value ?? 0,
                                LPG = lpgMap?.GetValue(dateStr).Value ?? 0,
                                이전_LPG = lastlpgMap?.GetValue(lastStr).Value ?? 0,
                                중온수 = mediumWaterMap?.GetValue(dateStr).Value ?? 0,
                                이전_중온수 = lastmediumWaterMap?.GetValue(lastStr).Value ?? 0
                            });
                        }
                        date = date.AddDays(1);
                        lastDate = lastDate.AddDays(1);
                    }

                }
                if (type == null)
                    list = convertTOE(list, start, end, EnergyInterval.일);
                return Ok(new {
                    type,
                    list = list.Values
                });
            } catch (FormatException ex) {
                _logger.LogError("", ex.ToString());
                throw new Exception("날짜형식을 올바르게 입력해주세요");
            }
        }

        public IDictionary<string, EnergyDaily> convertTOE(IDictionary<string, EnergyDaily> list, DateTime start, DateTime end, EnergyInterval interval) {
            var date = start;
            var days = interval == EnergyInterval.월 ? (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1 : end.Subtract(start).Days;

            for (var i = 0; i < days; i++) {
                var dateStr = date.ToString("yyyy-MM-dd");
                var toe = 0.0;
                var pre_toe = 0.0;
                toe += getTOE(EnergyType.전기, list.GetValue(dateStr).전기);
                pre_toe += getTOE(EnergyType.전기, list.GetValue(dateStr).이전_전기);
                toe += getTOE(EnergyType.가스, list.GetValue(dateStr).가스);
                pre_toe += getTOE(EnergyType.가스, list.GetValue(dateStr).이전_가스);
                toe += getTOE(EnergyType.등유, list.GetValue(dateStr).등유);
                pre_toe += getTOE(EnergyType.등유, list.GetValue(dateStr).이전_등유);
                toe += getTOE(EnergyType.LPG, list.GetValue(dateStr).LPG);
                pre_toe += getTOE(EnergyType.LPG, list.GetValue(dateStr).이전_LPG);
                toe += getTOE(EnergyType.중온수, list.GetValue(dateStr).중온수);
                pre_toe += getTOE(EnergyType.중온수, list.GetValue(dateStr).이전_중온수);

                if (list.ContainsKey(dateStr)) {
                    list[dateStr].TOE = Math.Round(toe, 2);
                    list[dateStr].이전_TOE = Math.Round(pre_toe, 2);
                }
                date = interval == EnergyInterval.월 ? date.AddMonths(1) : date.AddDays(1);
            }
            return list;
        }
        public double getTOE(EnergyType? type, double val) {
            var exp = 0.0;
            if (val == 0) return 0;
            if (type == EnergyType.전기) {
                exp = 0.000215;
            } else if (type == EnergyType.가스)
                exp = 0.001055;
            else if (type == EnergyType.등유)
                exp = 0.000895;
            else if (type == EnergyType.LPG)
                exp = 0.0015;
            else if (type == EnergyType.중온수)
                exp = 0.001055;
            else
                return val;
            return val * exp;
        }


        private IQueryable<EnergyDaily> _GetEnergyOld(int? siteId, DateTime startDate, DateTime endDate) {
            var energy = _context.BemsEnergyDaily.Select(x => x);

            if (siteId != null) {
                energy = energy.Where(x => x.SiteId == siteId.Value);
            }

            var query = from x in energy
                        where x.CreatedDate.Date >= startDate.Date && x.CreatedDate.Date <= endDate.Date
                        select new {
                            x.CreatedDate.Date,
                            x.FuelType,
                            x.Measurement,
                        };

            var groupQuery = from x in query
                             group x by new { x.FuelType.FuelTypeId, x.Date } into g
                             orderby g.Key.Date, g.Key.FuelTypeId
                             select new {
                                 Date = g.Key.Date,
                                 FuelTypeId = g.Key.FuelTypeId,
                                 FuelTypeName = g.Min(x => x.FuelType.Name),
                                 Measurement = g.Sum(x => x.Measurement)
                             };


            var groupDate = from x in groupQuery
                            group x by x.Date into g
                            select new EnergyDaily {
                                Date = g.Key.Date.ToString("yyyy-MM-dd"),
                                전기 = g.Sum(x => x.FuelTypeId == 1 ? x.Measurement : 0) ?? 0,
                                가스 = g.Sum(x => x.FuelTypeId == 2 ? x.Measurement : 0) ?? 0,
                                수도 = g.Sum(x => x.FuelTypeId == 3 ? x.Measurement : 0) ?? 0,
                            };

            return groupDate;
        }

        private IQueryable<CalendarEvent> _GetRequestEventList(int? siteId, DateTime startDate, DateTime endDate) {
            var events = _context.FmsWorkRequest.Select(x => x);
            if (siteId != null) {
                events = events.Where(x => x.SiteId == siteId.Value);
            }

            var query = from x in events
                        where x.FmsWorkResult == null
                        where x.StartWorkDate.Date >= startDate.Date && x.StartWorkDate.Date <= endDate.Date
                        && (x.FmsWorkSchedule == null || (x.FmsWorkSchedule != null && x.FmsWorkSchedule.IsUse == true))
                        select x;

            var list = query.Select(x => new CalendarEvent {
                Title = x.Title,
                Start = x.StartWorkDate.ToString("yyyy-MM-dd"),
                End = x.StartWorkDate.ToString("yyyy-MM-dd"),
                WorkType = x.WorkType.Name,
                Type = "예정",
                WorkRequestId = x.WorkRequestId,
                SiteId = x.SiteId,
            });

            return list;
        }

        private IQueryable<CalendarEvent> _GetResultEventList(int? siteId, DateTime startDate, DateTime endDate) {
            var events = _context.FmsWorkResult.Select(x => x);
            if (siteId != null) {
                events = events.Where(x => x.SiteId == siteId.Value);
            }

            var query = from x in events
                        where x.StartDate.Date >= startDate.Date && x.StartDate <= endDate.Date
                        select x;

            var list = query.Select(x => new CalendarEvent {
                Title = x.FmsWorkRequest.Title,
                Start = x.StartDate.ToString("yyyy-MM-dd"),
                End = x.EndDate.ToString("yyyy-MM-dd"),
                WorkType = x.FmsWorkRequest.WorkType.Name,
                Type = "진행",
                WorkRequestId = x.WorkRequestId,
                SiteId = x.SiteId,
            });

            return list;
        }

        private IList<CalendarBackground> _GetHolidayList(int siteId, int year, int month, DateTime startDate, DateTime endDate) {
            var list = new List<CalendarBackground>();

            var holidays = _context.CmHoliday.Where(
                x => x.SiteId == siteId
                && x.IsUse == true
            );

            foreach (var item in holidays) {
                if (item.IsLunar) {
                    var solar = LunarSolarConverter.LunarToSolar(new Lunar() {
                        lunarYear = year,
                        lunarMonth = item.HolidayMonth,
                        lunarDay = item.HolidayDay
                    });
                    list.Add(new CalendarBackground {
                        Title = item.Name + "(음력)",
                        Date = new DateTime(solar.solarYear, solar.solarMonth, solar.solarDay).ToString("yyyy-MM-dd"),
                        Type = "공휴일",
                    });
                } else {
                    list.Add(new CalendarBackground {
                        Title = item.Name,
                        Date = new DateTime(year, item.HolidayMonth, item.HolidayDay).ToString("yyyy-MM-dd"),
                        Type = "공휴일",
                    });
                }
            }

            var query = from x in _context.CmHolidayCustom
                        where x.SiteId == siteId
                        where x.HolidayDate.Date >= startDate.Date && x.HolidayDate <= endDate.Date
                        select x;

            var customs = query.Select(x => new CalendarBackground {
                Title = x.Name,
                Date = x.HolidayDate.ToString("yyyy-MM-dd"),
                Type = "휴일",
            });
            list.AddRange(customs);

            return list;
        }

        private IQueryable<CalendarEvent> _GetWeekKeyWorkEventList(int? siteId, DateTime startDate, DateTime endDate) {
            var query = _context.FmsWeekKeyWork.Select(x => x);
            if (siteId != null) {
                query = query.Where(x => x.SiteId == siteId.Value);
            }
            query = query.Where(x => x.WorkDate.Date >= startDate.Date && x.WorkDate.Date <= endDate.Date);

            var list = query.Select(x => new CalendarEvent {
                Title = x.WorkName,
                Start = x.WorkDate.ToString("yyyy-MM-dd"),
                End = x.WorkDate.ToString("yyyy-MM-dd"),
                WorkType = "주별중점업무",
                Type = "주별 중점업무",
                WorkRequestId = x.WeekKeyWorkId,
                SiteId = x.SiteId,
            });

            return list;
        }

    }

}
