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 EnergyDayAnalysisController : Controller { private readonly ILogger _logger; private readonly EnergyDayAnalysisService _service; private readonly EnergyUsageService _serviceUsage; private readonly FMSContext _context; public EnergyDayAnalysisController( ILogger logger, EnergyDayAnalysisService service, EnergyUsageService serviceUsage, FMSContext context ) { _logger = logger; _service = service; _serviceUsage = serviceUsage; _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); var end = DateTime.Now.Date; if (!string.IsNullOrEmpty(startDate)) { start = DateTime.Parse(startDate); } if (!string.IsNullOrEmpty(endDate)) { end = DateTime.Parse(endDate); } if (interval == EnergyInterval.월) { end = end.AddMonths(1).AddDays(-1); } var lastStart = start.AddYears(-1); var lsastEnd = end.AddYears(-1); if (interval == EnergyInterval.일) { lastStart = GetLastYearSameWeekDate(start); lsastEnd = GetLastYearSameWeekDate(end); } _logger.LogInformation("start" + start.ToShortDateString()); IDictionary list = new Dictionary(); IDictionary energyMap = null; IDictionary energyLastMap = null; IDictionary electMap = null; IDictionary lastElectMap = null; IDictionary gasMap = null; IDictionary lastGasMap = null; IDictionary lampOilMap = null; IDictionary lastLampOilMap = null; IDictionary lpgMap = null; IDictionary lastLpgMap = null; IDictionary mediumWaterMap = null; IDictionary lastMediumWaterMap = null; try { if (type != null) { energyMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, (EnergyType)type, start, end); energyLastMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, (EnergyType)type, lastStart, lsastEnd); } else { //TOE electMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.전기, start, end); lastElectMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.전기, lastStart, lsastEnd); gasMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.가스, start, end); lastGasMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.가스, lastStart, lsastEnd); lampOilMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.등유, start, end); lastLampOilMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.등유, lastStart, lsastEnd); lpgMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, start, end); lastLpgMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, lastStart, lsastEnd); mediumWaterMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, start, end); lastMediumWaterMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, lastStart, lsastEnd); } } catch (Exception ex) { _logger.LogError(ex, ""); } if (type != null) { _DateFillAll(interval, start, end, lastStart, (EnergyType)type, energyMap, energyLastMap, list); } else { //TOE _DateFillAll(interval, start, end, lastStart, EnergyType.전기, electMap, lastElectMap, list); _DateFillAll(interval, start, end, lastStart, EnergyType.가스, gasMap, lastGasMap, list); _DateFillAll(interval, start, end, lastStart, EnergyType.등유, lampOilMap, lastLampOilMap, list); _DateFillAll(interval, start, end, lastStart, EnergyType.LPG, lpgMap, lastLpgMap, list); _DateFillAll(interval, start, end, lastStart, EnergyType.중온수, mediumWaterMap, lastMediumWaterMap, list); list = convertTOE(list, start, end, interval); } IDictionary energyList = new Dictionary(); _DataGrid(interval, start, end, lastStart, list, energyList, type); if (excel) { var stream = new MemoryStream(); using (var package = new ExcelPackage(stream)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); workSheet.Cells.LoadFromCollection(energyList.Values, true); workSheet.AddStyle(new PagingRequest.Column[] { new PagingRequest.Column{ name = "올해", field = "ShortDateTime", }, new PagingRequest.Column{ name = type.ToString(), field = "Value", align = "right", format = "pricePoint" }, new PagingRequest.Column{ name = "작년", field = "LastShortDateTime", }, new PagingRequest.Column{ name = type.ToString(), field = "LastValue", 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); } double maxValue = energyList.Select(x => x.Value.LastValue).Max() > energyList.Select(x => x.Value.Value).Max() ? energyList.Select(x => x.Value.LastValue).Max() : energyList.Select(x => x.Value.Value).Max(); //증감이유 조회 int fuelTypeId = type != null ? (int)type + 1 : 0; //var dataAnalysis = _service.GetAllAnalysis(115, 1, 2020, 6); var dataAnalysis = _service.GetAllAnalysis(siteId, fuelTypeId, end.Year, end.Month); var analysisGrid = dataAnalysis.Select(x => new { YearMonth = x.Year.ToString() + "." + x.Month.ToString("D2"), SiteId = x.SiteId, SiteName = x.CmSite.Name, Year = x.Year, Month = x.Month, FuelTypeId = x.FuelTypeId, x.FuelPriceId, // FuelTypePriceName = x.FuelType.Name + (x.FuelTypeId == 1 ? x.FuelPriceId == 1 ? " (일반용)" : x.FuelPriceId == 2 ? " (심야용)" : " (산업용)" : ""), FuelTypePriceName = x.FuelType.Name, x.PriceTypeId, PriceTypeName = x.PriceType.PriceTypeIdDesc, }); return Ok(new { type, list = list.Values, listGrid = energyList.Values, maxValue = maxValue, analysisGrid = analysisGrid }); } catch (FormatException ex) { _logger.LogError(ex, ""); throw new Exception("날짜형식을 올바르게 입력해주세요"); } } private void _DateFillAll(EnergyInterval interval, DateTime start, DateTime end, DateTime lastStart, EnergyType energyType, IDictionary source, IDictionary lastSource, IDictionary target) { var duration = (interval == EnergyInterval.일) ? end.Subtract(start).Days + 1 : (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1; var date = start; var lastDate = lastStart; for (var i = 0; i < duration; i++) { var dateStr = date.ToString("yyyy-MM-dd"); var lastStr = lastDate.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; //전년도 if (energyType == EnergyType.전기) target[dateStr].이전_전기 += lastSource?.GetValue(lastStr)?.Value ?? 0; if (energyType == EnergyType.가스) target[dateStr].이전_가스 += lastSource?.GetValue(lastStr)?.Value ?? 0; if (energyType == EnergyType.수도) target[dateStr].이전_수도 += lastSource?.GetValue(lastStr)?.Value ?? 0; if (energyType == EnergyType.등유) target[dateStr].이전_등유 += lastSource?.GetValue(lastStr)?.Value ?? 0; if (energyType == EnergyType.LPG) target[dateStr].이전_LPG += lastSource?.GetValue(lastStr)?.Value ?? 0; if (energyType == EnergyType.중온수) target[dateStr].이전_중온수 += lastSource?.GetValue(lastStr)?.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, 이전_전기 = energyType == EnergyType.전기 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0, 이전_가스 = energyType == EnergyType.가스 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0, 이전_수도 = energyType == EnergyType.수도 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0, 이전_등유 = energyType == EnergyType.등유 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0, 이전_LPG = energyType == EnergyType.LPG ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0, 이전_중온수 = energyType == EnergyType.중온수 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0, 이전_TOE = 0, }); } if (interval == EnergyInterval.일) { date = date.AddDays(1); lastDate = lastDate.AddDays(1); } else if (interval == EnergyInterval.월) { date = date.AddMonths(1); lastDate = lastDate.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; 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].중온수 = 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, DateTime lastStart, 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; var lastDate = lastStart; for (var i = 0; i < days; i++) { var dateStr = date.ToString("yyyy-MM-dd"); var lastStr = lastDate.ToString("yyyy-MM-dd"); double d_a = 0, dd_a = 0; if (type != null) { d_a = Math.Round((source[dateStr].전기 + source[dateStr].가스 + source[dateStr].수도 + source[dateStr].등유 + source[dateStr].LPG + source[dateStr].중온수), 2); dd_a = Math.Round((source[dateStr].이전_전기 + source[dateStr].이전_가스 + source[dateStr].이전_수도 + source[dateStr].이전_등유 + source[dateStr].이전_LPG + source[dateStr].이전_중온수), 2); } else { d_a = source[dateStr].TOE; dd_a = source[dateStr].이전_TOE; } target.Add(dateStr, new CalculationLastResult { DateTime = date, ShortDateTime = interval == EnergyInterval.일 ? dateStr : date.ToString("yyyy-MM"), Value = d_a, LastDateTime = lastDate, LastShortDateTime = interval == EnergyInterval.일 ? lastStr : lastDate.ToString("yyyy-MM"), LastValue = dd_a, DayOfWeek = interval == EnergyInterval.일 ? " (" + date.GetDayofWeek() + ")" : "" }); if (interval == EnergyInterval.일) { date = date.AddDays(1); lastDate = lastDate.AddDays(1); } else if (interval == EnergyInterval.월) { date = date.AddMonths(1); lastDate = lastDate.AddMonths(1); } } } private DateTime GetLastYearSameWeekDate(DateTime dDate) { DateTime rDate = dDate.AddYears(-1); for (var i = 0; i < 7; i++) { if (rDate.DayOfWeek == dDate.DayOfWeek) { break; } rDate = rDate.AddDays(1); } return rDate; } /// /// 저장 /// [HttpPost("{siteId}/{fuelTypeId}/{fuelPriceId}/{year}/{month}/{priceTypeId}")] public IActionResult Save(int siteId, int fuelTypeId, int fuelPriceId, string year, string month, int priceTypeId, [FromBody] FmsEnergyAnalysis data) { if (ModelState.IsValid) { _service.Save(siteId, fuelTypeId, fuelPriceId, Util.ToInt(year), Util.ToInt(month), priceTypeId, data); } else { return BadRequest(ModelState); } return Ok(); } [HttpGet("[action]")] public IActionResult GetAnalysisData(int siteId, int fuelTypeId, int fuelPriceId, string year, string month, int priceTypeId) { var data = _service.GetAnalysis(siteId, fuelTypeId, fuelPriceId, year, month, priceTypeId); var item = data.Select(x => new { x.SiteId, CmSite = new { x.CmSite.Name, }, x.FuelTypeId, BemsFuelType = new { x.FuelType.Name, }, x.FuelPriceId, x.Year, x.Month, x.Content, x.PriceTypeId, BemsPriceType = new { x.PriceType.PriceTypeIdDesc, }, }); return Ok(item); } [HttpGet("[action]")] public IActionResult GetPriceType(int siteId, int fuelTypeId) { var query = from x in _context.BemsPriceType join s in _context.BemsSitePrice on new { x.PriceTypeId } equals new { s.PriceTypeId } where s.SiteId == siteId where s.FuelTypeId == fuelTypeId where s.UseYn == "Y" select new { PriceTypeId = x.PriceTypeId, PriceTypeIdDesc = x.PriceTypeIdDesc }; var list = query.GroupBy(x => new { x.PriceTypeId, x.PriceTypeIdDesc }).Select(x => new { x.Key.PriceTypeId, x.Key.PriceTypeIdDesc }).ToList(); return Ok(list); } } }