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 FMSApp.Services; using Microsoft.AspNetCore.Authorization; using FMSAdmin.Models.Formula; using FMSAdmin.Helpers.Formula; using FMSAdmin.Services; using System.IO; using OfficeOpenXml; namespace FMSAdmin.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/[controller]")] public class EnergyUsageController : Controller { private readonly ILogger _logger; private readonly EnergyUsageService _service; private readonly FMSContext _context; public EnergyUsageController( ILogger logger, EnergyUsageService service, FMSContext context ) { _logger = logger; _service = service; _context = context; } /** * 사이트별 일별 에너지 */ [HttpGet("[action]")] public IActionResult Data(int siteId, EnergyInterval interval, EnergyType? type, string startDate, string endDate, bool excel = false) { _logger.LogInformation("siteId:" + siteId); 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); } if (interval == EnergyInterval.월) { end = end.AddMonths(1).AddDays(-1); } _logger.LogInformation("start" + start.ToShortDateString()); IDictionary list = new Dictionary(); IDictionary energyMap = null; IDictionary electMap = null; IDictionary gasMap = null; IDictionary waterMap = null; IDictionary lampOilMap = null; IDictionary lpgMap = null; IDictionary mediumWaterMap = null; try { if (type != null) { energyMap = _service.GetEnergyDailyDic(siteId, interval, (EnergyType)type, start, end); } else { //TOE electMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.전기, start, end); gasMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.가스, start, end); waterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.수도, start, end); lampOilMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.등유, start, end); lpgMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, start, end); mediumWaterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, start, end); } } catch (Exception ex) { _logger.LogError(ex, ""); } if (type != null) { _DateFillAll(interval, start, end, (EnergyType)type, energyMap, list); } else { //TOE _DateFillAll(interval, start, end, EnergyType.전기, electMap, list); _DateFillAll(interval, start, end, EnergyType.가스, gasMap, list); _DateFillAll(interval, start, end, EnergyType.등유, lampOilMap, list); _DateFillAll(interval, start, end, EnergyType.LPG, lpgMap, list); _DateFillAll(interval, start, end, EnergyType.중온수, mediumWaterMap, list); list = convertTOE(list, start, end, interval); } IDictionary energyList = new Dictionary(); _DataGrid(interval, start, end, list, energyList, type); if (excel) { var stream = new MemoryStream(); using (var package = new ExcelPackage(stream)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); string typeName = type.ToString(); if (type == null) typeName = "TOE"; workSheet.Cells.LoadFromCollection(energyList.Values, true); workSheet.AddStyle(new PagingRequest.Column[] { new PagingRequest.Column{ name = "날짜", field = "ShortDateTime", }, new PagingRequest.Column{ name = typeName, field = "Value", align = "right", format = "pricePoint" }, }); package.Save(); } stream.Position = 0; string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx"; return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); } return Ok(new { type, list = list.Values, listGrid = energyList.Values, maxValue = energyList.Select(x => x.Value.Value).Max() }); } catch (FormatException ex) { _logger.LogError(ex, ""); throw new Exception("날짜형식을 올바르게 입력해주세요"); } } /** * 전체 일별 에너지 */ [HttpGet("[action]")] public IActionResult WholeData(EnergyInterval interval, EnergyType? type, string startDate, string endDate, bool excel = false) { IDictionary list = new Dictionary(); IDictionary energyList = new Dictionary(); 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); } if (interval == EnergyInterval.월) { end = end.AddMonths(1).AddDays(-1); } var siteList = _context.CmSite.ToList(); foreach (var site in siteList) { var siteId = site.SiteId; IDictionary energyMap = null; IDictionary electMap = null; IDictionary gasMap = null; IDictionary waterMap = null; IDictionary lampOilMap = null; IDictionary lpgMap = null; IDictionary mediumWaterMap = null; try { if (type != null) { energyMap = _service.GetEnergyDailyDic(siteId, interval, (EnergyType)type, start, end); } else { //TOE electMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.전기, start, end); gasMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.가스, start, end); waterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.수도, start, end); lampOilMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.등유, start, end); lpgMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, start, end); mediumWaterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, start, end); } } catch (Exception ex) { _logger.LogError(ex, ""); } if (type != null) { _DateFillAll(interval, start, end, (EnergyType)type, energyMap, list); } else { //TOE _DateFillAll(interval, start, end, EnergyType.전기, electMap, list); _DateFillAll(interval, start, end, EnergyType.가스, gasMap, list); _DateFillAll(interval, start, end, EnergyType.등유, lampOilMap, list); _DateFillAll(interval, start, end, EnergyType.LPG, lpgMap, list); _DateFillAll(interval, start, end, EnergyType.중온수, mediumWaterMap, list); } } if (type == null) list = convertTOE(list, start, end, interval); _DataGrid(interval, start, end, list, energyList, type); if (excel) { var stream = new MemoryStream(); using (var package = new ExcelPackage(stream)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); string typeName = type.ToString(); if (type == null) typeName = "TOE"; workSheet.Cells.LoadFromCollection(energyList.Values, true); workSheet.AddStyle(new PagingRequest.Column[] { new PagingRequest.Column{ name = "날짜", field = "ShortDateTime", }, new PagingRequest.Column{ name = typeName, field = "Value", align = "right", format = "pricePoint" }, }); package.Save(); } stream.Position = 0; string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx"; return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); } return Ok(new { type, list = list.Values, listGrid = energyList.Values, maxValue = energyList.Select(x => x.Value.Value).Max() }); } catch (FormatException ex) { _logger.LogError(ex, ""); throw new Exception("날짜형식을 올바르게 입력해주세요"); } } private void _DateFill(EnergyInterval interval, DateTime start, DateTime end, IDictionary source, IDictionary target) { if (target == null) { target = new Dictionary(); } if (interval == EnergyInterval.일) { var days = end.Subtract(start).Days + 1; var date = start; for (var i = 0; i < days; i++) { var dateStr = date.ToString("yyyy-MM-dd"); if (target.ContainsKey(dateStr)) { target[dateStr].Value += source?.GetValue(dateStr)?.Value ?? 0; } else { target.Add(dateStr, new CalculationResult { DateTime = date, ShortDateTime = dateStr, Value = source?.GetValue(dateStr)?.Value ?? 0, }); } date = date.AddDays(1); } } else if (interval == EnergyInterval.월) { var months = (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1; _logger.LogInformation("months:" + months); var date = start; for (var i = 0; i < months; i++) { var dateStr = date.ToString("yyyy-MM-dd"); if (target.ContainsKey(dateStr)) { target[dateStr].Value += source?.GetValue(dateStr)?.Value ?? 0; } else { target.Add(dateStr, new CalculationResult { DateTime = date, ShortDateTime = dateStr, Value = source?.GetValue(dateStr)?.Value ?? 0, }); } date = date.AddMonths(1); } } } private void _DateFillAll(EnergyInterval interval, DateTime start, DateTime end, EnergyType energyType, IDictionary source, IDictionary target) { if (interval == EnergyInterval.일) { var days = end.Subtract(start).Days + 1; var date = start; for (var i = 0; i < days; i++) { var dateStr = date.ToString("yyyy-MM-dd"); if (target.ContainsKey(dateStr)) { if (energyType == EnergyType.전기) target[dateStr].전기 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.가스) target[dateStr].가스 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.수도) target[dateStr].수도 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.등유) target[dateStr].등유 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.LPG) target[dateStr].LPG += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.중온수) target[dateStr].중온수 += source?.GetValue(dateStr)?.Value ?? 0; } else { target.Add(dateStr, new EnergyDaily { Date = dateStr, 전기 = energyType == EnergyType.전기 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 가스 = energyType == EnergyType.가스 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 수도 = energyType == EnergyType.수도 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 등유 = energyType == EnergyType.등유 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, LPG = energyType == EnergyType.LPG ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 중온수 = energyType == EnergyType.중온수 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, TOE = 0, }); } date = date.AddDays(1); } } else if (interval == EnergyInterval.월) { var months = (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1; _logger.LogInformation("months:" + months); var date = start; for (var i = 0; i < months; i++) { var dateStr = date.ToString("yyyy-MM-dd"); if (target.ContainsKey(dateStr)) { if (energyType == EnergyType.전기) target[dateStr].전기 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.가스) target[dateStr].가스 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.수도) target[dateStr].수도 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.등유) target[dateStr].등유 += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.LPG) target[dateStr].LPG += source?.GetValue(dateStr)?.Value ?? 0; if (energyType == EnergyType.중온수) target[dateStr].중온수 += source?.GetValue(dateStr)?.Value ?? 0; } else { target.Add(dateStr, new EnergyDaily { Date = dateStr, 전기 = energyType == EnergyType.전기 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 가스 = energyType == EnergyType.가스 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 수도 = energyType == EnergyType.수도 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 등유 = energyType == EnergyType.등유 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, LPG = energyType == EnergyType.LPG ? source?.GetValue(dateStr)?.Value ?? 0 : 0, 중온수 = energyType == EnergyType.중온수 ? source?.GetValue(dateStr)?.Value ?? 0 : 0, TOE = 0, }); } date = date.AddMonths(1); } } } public IDictionary convertTOE(IDictionary 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 + 1; for (var i = 0; i < days; i++) { var dateStr = date.ToString("yyyy-MM-dd"); if (list.ContainsKey(dateStr)) { list[dateStr].TOE = getTOE(EnergyType.전기, list.GetValue(dateStr).전기); list[dateStr].TOE += getTOE(EnergyType.가스, list.GetValue(dateStr).가스); list[dateStr].TOE += getTOE(EnergyType.수도, list.GetValue(dateStr).수도); list[dateStr].TOE += getTOE(EnergyType.등유, list.GetValue(dateStr).등유); list[dateStr].TOE += getTOE(EnergyType.LPG, list.GetValue(dateStr).LPG); list[dateStr].TOE += getTOE(EnergyType.중온수, list.GetValue(dateStr).중온수); list[dateStr].이전_TOE = getTOE(EnergyType.전기, list.GetValue(dateStr).이전_전기); list[dateStr].이전_TOE += getTOE(EnergyType.가스, list.GetValue(dateStr).이전_가스); list[dateStr].이전_TOE += getTOE(EnergyType.수도, list.GetValue(dateStr).이전_수도); list[dateStr].이전_TOE += getTOE(EnergyType.등유, list.GetValue(dateStr).이전_등유); list[dateStr].이전_TOE += getTOE(EnergyType.LPG, list.GetValue(dateStr).이전_LPG); list[dateStr].이전_TOE += getTOE(EnergyType.중온수, list.GetValue(dateStr).이전_중온수); list[dateStr].TOE = Math.Round(list[dateStr].TOE, 2); list[dateStr].이전_TOE = Math.Round(list[dateStr].이전_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 Math.Round(val * exp, 2); } private void _DataGrid(EnergyInterval interval, DateTime start, DateTime end, IDictionary source, IDictionary target, EnergyType? type) { if (target == null) { target = new Dictionary(); } var days = (interval == EnergyInterval.일) ? end.Subtract(start).Days + 1 : (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1; var date = start; for (var i = 0; i < days; i++) { var dateStr = date.ToString("yyyy-MM-dd"); double d_a = 0; if (type != null) { d_a = Math.Round((source[dateStr].전기 + source[dateStr].가스 + source[dateStr].수도 + source[dateStr].등유 + source[dateStr].LPG + source[dateStr].중온수), 2); } else { d_a = source[dateStr].TOE; } target.Add(dateStr, new CalculationResult { DateTime = date, ShortDateTime = dateStr, Value = d_a, }); if (interval == EnergyInterval.일) { date = date.AddDays(1); } else if (interval == EnergyInterval.월) { date = date.AddMonths(1); } } } [HttpGet("[action]")] public IActionResult GetFuelType() { var query = _service.GetFuelType(); var list = query.Select(x => new { x.FuelTypeId, x.Name }).ToList(); return Ok(list); } } }