EnergyDayAnalysisController.cs 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Linq.Dynamic.Core;
  5. using Microsoft.AspNetCore.Mvc;
  6. using Microsoft.Extensions.Logging;
  7. using FMSAdmin.Data;
  8. using FMSAdmin.Helpers;
  9. using System.Collections;
  10. using System.Text.RegularExpressions;
  11. using FMSAdmin.Models;
  12. using FMSAdmin.Entities;
  13. using FMSApp.Services;
  14. using Microsoft.AspNetCore.Authorization;
  15. using FMSAdmin.Models.Formula;
  16. using FMSAdmin.Helpers.Formula;
  17. using FMSAdmin.Services;
  18. using System.IO;
  19. using OfficeOpenXml;
  20. namespace FMSAdmin.Controllers {
  21. [Authorize]
  22. [ApiController]
  23. [ApiVersion("1")]
  24. [Route("api/[controller]")]
  25. public class EnergyDayAnalysisController : Controller {
  26. private readonly ILogger<EnergyDayAnalysisController> _logger;
  27. private readonly EnergyDayAnalysisService _service;
  28. private readonly EnergyUsageService _serviceUsage;
  29. private readonly FMSContext _context;
  30. public EnergyDayAnalysisController(
  31. ILogger<EnergyDayAnalysisController> logger,
  32. EnergyDayAnalysisService service,
  33. EnergyUsageService serviceUsage,
  34. FMSContext context
  35. ) {
  36. _logger = logger;
  37. _service = service;
  38. _serviceUsage = serviceUsage;
  39. _context = context;
  40. }
  41. /**
  42. * 사이트별 일별 에너지
  43. */
  44. [HttpGet("[action]")]
  45. public IActionResult Data(int siteId, EnergyInterval interval, EnergyType? type, string startDate, string endDate, bool excel = false) {
  46. _logger.LogInformation("siteId:" + siteId);
  47. try {
  48. var start = DateTime.Now.Date.AddDays(-2);
  49. var end = DateTime.Now.Date;
  50. if (!string.IsNullOrEmpty(startDate)) {
  51. start = DateTime.Parse(startDate);
  52. }
  53. if (!string.IsNullOrEmpty(endDate)) {
  54. end = DateTime.Parse(endDate);
  55. }
  56. if (interval == EnergyInterval.월) {
  57. end = end.AddMonths(1).AddDays(-1);
  58. }
  59. var lastStart = start.AddYears(-1);
  60. var lsastEnd = end.AddYears(-1);
  61. if (interval == EnergyInterval.일) {
  62. lastStart = GetLastYearSameWeekDate(start);
  63. lsastEnd = GetLastYearSameWeekDate(end);
  64. }
  65. _logger.LogInformation("start" + start.ToShortDateString());
  66. IDictionary<string, EnergyDaily> list = new Dictionary<string, EnergyDaily>();
  67. IDictionary<string, CalculationResult> energyMap = null;
  68. IDictionary<string, CalculationResult> energyLastMap = null;
  69. IDictionary<string, CalculationResult> electMap = null;
  70. IDictionary<string, CalculationResult> lastElectMap = null;
  71. IDictionary<string, CalculationResult> gasMap = null;
  72. IDictionary<string, CalculationResult> lastGasMap = null;
  73. IDictionary<string, CalculationResult> lampOilMap = null;
  74. IDictionary<string, CalculationResult> lastLampOilMap = null;
  75. IDictionary<string, CalculationResult> lpgMap = null;
  76. IDictionary<string, CalculationResult> lastLpgMap = null;
  77. IDictionary<string, CalculationResult> mediumWaterMap = null;
  78. IDictionary<string, CalculationResult> lastMediumWaterMap = null;
  79. try {
  80. if (type != null) {
  81. energyMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, (EnergyType)type, start, end);
  82. energyLastMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, (EnergyType)type, lastStart, lsastEnd);
  83. } else {
  84. //TOE
  85. electMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.전기, start, end);
  86. lastElectMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.전기, lastStart, lsastEnd);
  87. gasMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.가스, start, end);
  88. lastGasMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.가스, lastStart, lsastEnd);
  89. lampOilMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.등유, start, end);
  90. lastLampOilMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.등유, lastStart, lsastEnd);
  91. lpgMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, start, end);
  92. lastLpgMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, lastStart, lsastEnd);
  93. mediumWaterMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, start, end);
  94. lastMediumWaterMap = _serviceUsage.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, lastStart, lsastEnd);
  95. }
  96. } catch (Exception ex) {
  97. _logger.LogError(ex, "");
  98. }
  99. if (type != null) {
  100. _DateFillAll(interval, start, end, lastStart, (EnergyType)type, energyMap, energyLastMap, list);
  101. } else {
  102. //TOE
  103. _DateFillAll(interval, start, end, lastStart, EnergyType.전기, electMap, lastElectMap, list);
  104. _DateFillAll(interval, start, end, lastStart, EnergyType.가스, gasMap, lastGasMap, list);
  105. _DateFillAll(interval, start, end, lastStart, EnergyType.등유, lampOilMap, lastLampOilMap, list);
  106. _DateFillAll(interval, start, end, lastStart, EnergyType.LPG, lpgMap, lastLpgMap, list);
  107. _DateFillAll(interval, start, end, lastStart, EnergyType.중온수, mediumWaterMap, lastMediumWaterMap, list);
  108. list = convertTOE(list, start, end, interval);
  109. }
  110. IDictionary<string, CalculationLastResult> energyList = new Dictionary<string, CalculationLastResult>();
  111. _DataGrid(interval, start, end, lastStart, list, energyList, type);
  112. if (excel) {
  113. var stream = new MemoryStream();
  114. using (var package = new ExcelPackage(stream)) {
  115. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  116. workSheet.Cells.LoadFromCollection(energyList.Values, true);
  117. workSheet.AddStyle(new PagingRequest.Column[] {
  118. new PagingRequest.Column{
  119. name = "올해",
  120. field = "ShortDateTime",
  121. },
  122. new PagingRequest.Column{
  123. name = type.ToString(),
  124. field = "Value",
  125. align = "right",
  126. format = "pricePoint"
  127. },
  128. new PagingRequest.Column{
  129. name = "작년",
  130. field = "LastShortDateTime",
  131. },
  132. new PagingRequest.Column{
  133. name = type.ToString(),
  134. field = "LastValue",
  135. align = "right",
  136. format = "pricePoint"
  137. },
  138. });
  139. package.Save();
  140. }
  141. stream.Position = 0;
  142. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  143. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  144. }
  145. 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();
  146. //증감이유 조회
  147. int fuelTypeId = type != null ? (int)type + 1 : 0;
  148. //var dataAnalysis = _service.GetAllAnalysis(115, 1, 2020, 6);
  149. var dataAnalysis = _service.GetAllAnalysis(siteId, fuelTypeId, end.Year, end.Month);
  150. var analysisGrid = dataAnalysis.Select(x => new {
  151. YearMonth = x.Year.ToString() + "." + x.Month.ToString("D2"),
  152. SiteId = x.SiteId,
  153. SiteName = x.CmSite.Name,
  154. Year = x.Year,
  155. Month = x.Month,
  156. FuelTypeId = x.FuelTypeId,
  157. x.FuelPriceId,
  158. // FuelTypePriceName = x.FuelType.Name + (x.FuelTypeId == 1 ? x.FuelPriceId == 1 ? " (일반용)" : x.FuelPriceId == 2 ? " (심야용)" : " (산업용)" : ""),
  159. FuelTypePriceName = x.FuelType.Name,
  160. x.PriceTypeId,
  161. PriceTypeName = x.PriceType.PriceTypeIdDesc,
  162. });
  163. return Ok(new {
  164. type,
  165. list = list.Values,
  166. listGrid = energyList.Values,
  167. maxValue = maxValue,
  168. analysisGrid = analysisGrid
  169. });
  170. } catch (FormatException ex) {
  171. _logger.LogError(ex, "");
  172. throw new Exception("날짜형식을 올바르게 입력해주세요");
  173. }
  174. }
  175. private void _DateFillAll(EnergyInterval interval, DateTime start, DateTime end, DateTime lastStart, EnergyType energyType, IDictionary<string, CalculationResult> source, IDictionary<string, CalculationResult> lastSource, IDictionary<string, EnergyDaily> target) {
  176. var duration = (interval == EnergyInterval.일) ? end.Subtract(start).Days + 1 : (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
  177. var date = start;
  178. var lastDate = lastStart;
  179. for (var i = 0; i < duration; i++) {
  180. var dateStr = date.ToString("yyyy-MM-dd");
  181. var lastStr = lastDate.ToString("yyyy-MM-dd");
  182. if (target.ContainsKey(dateStr)) {
  183. if (energyType == EnergyType.전기) target[dateStr].전기 += source?.GetValue(dateStr)?.Value ?? 0;
  184. if (energyType == EnergyType.가스) target[dateStr].가스 += source?.GetValue(dateStr)?.Value ?? 0;
  185. if (energyType == EnergyType.수도) target[dateStr].수도 += source?.GetValue(dateStr)?.Value ?? 0;
  186. if (energyType == EnergyType.등유) target[dateStr].등유 += source?.GetValue(dateStr)?.Value ?? 0;
  187. if (energyType == EnergyType.LPG) target[dateStr].LPG += source?.GetValue(dateStr)?.Value ?? 0;
  188. if (energyType == EnergyType.중온수) target[dateStr].중온수 += source?.GetValue(dateStr)?.Value ?? 0;
  189. //전년도
  190. if (energyType == EnergyType.전기) target[dateStr].이전_전기 += lastSource?.GetValue(lastStr)?.Value ?? 0;
  191. if (energyType == EnergyType.가스) target[dateStr].이전_가스 += lastSource?.GetValue(lastStr)?.Value ?? 0;
  192. if (energyType == EnergyType.수도) target[dateStr].이전_수도 += lastSource?.GetValue(lastStr)?.Value ?? 0;
  193. if (energyType == EnergyType.등유) target[dateStr].이전_등유 += lastSource?.GetValue(lastStr)?.Value ?? 0;
  194. if (energyType == EnergyType.LPG) target[dateStr].이전_LPG += lastSource?.GetValue(lastStr)?.Value ?? 0;
  195. if (energyType == EnergyType.중온수) target[dateStr].이전_중온수 += lastSource?.GetValue(lastStr)?.Value ?? 0;
  196. } else {
  197. target.Add(dateStr, new EnergyDaily {
  198. Date = dateStr,
  199. 전기 = energyType == EnergyType.전기 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  200. 가스 = energyType == EnergyType.가스 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  201. 수도 = energyType == EnergyType.수도 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  202. 등유 = energyType == EnergyType.등유 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  203. LPG = energyType == EnergyType.LPG ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  204. 중온수 = energyType == EnergyType.중온수 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  205. TOE = 0,
  206. 이전_전기 = energyType == EnergyType.전기 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0,
  207. 이전_가스 = energyType == EnergyType.가스 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0,
  208. 이전_수도 = energyType == EnergyType.수도 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0,
  209. 이전_등유 = energyType == EnergyType.등유 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0,
  210. 이전_LPG = energyType == EnergyType.LPG ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0,
  211. 이전_중온수 = energyType == EnergyType.중온수 ? lastSource?.GetValue(lastStr)?.Value ?? 0 : 0,
  212. 이전_TOE = 0,
  213. });
  214. }
  215. if (interval == EnergyInterval.일) {
  216. date = date.AddDays(1);
  217. lastDate = lastDate.AddDays(1);
  218. } else if (interval == EnergyInterval.월) {
  219. date = date.AddMonths(1);
  220. lastDate = lastDate.AddMonths(1);
  221. }
  222. }
  223. }
  224. public IDictionary<string, EnergyDaily> convertTOE(IDictionary<string, EnergyDaily> list, DateTime start, DateTime end, EnergyInterval interval) {
  225. var date = start;
  226. var days = interval == EnergyInterval.월 ? (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1 : end.Subtract(start).Days;
  227. for (var i = 0; i < days; i++) {
  228. var dateStr = date.ToString("yyyy-MM-dd");
  229. if (list.ContainsKey(dateStr)) {
  230. list[dateStr].TOE = getTOE(EnergyType.전기, list.GetValue(dateStr).전기);
  231. list[dateStr].TOE += getTOE(EnergyType.가스, list.GetValue(dateStr).가스);
  232. list[dateStr].TOE += getTOE(EnergyType.수도, list.GetValue(dateStr).수도);
  233. list[dateStr].TOE += getTOE(EnergyType.등유, list.GetValue(dateStr).등유);
  234. list[dateStr].TOE += getTOE(EnergyType.LPG, list.GetValue(dateStr).LPG);
  235. list[dateStr].중온수 = getTOE(EnergyType.중온수, list.GetValue(dateStr).중온수);
  236. list[dateStr].이전_TOE = getTOE(EnergyType.전기, list.GetValue(dateStr).이전_전기);
  237. list[dateStr].이전_TOE += getTOE(EnergyType.가스, list.GetValue(dateStr).이전_가스);
  238. list[dateStr].이전_TOE += getTOE(EnergyType.수도, list.GetValue(dateStr).이전_수도);
  239. list[dateStr].이전_TOE += getTOE(EnergyType.등유, list.GetValue(dateStr).이전_등유);
  240. list[dateStr].이전_TOE += getTOE(EnergyType.LPG, list.GetValue(dateStr).이전_LPG);
  241. list[dateStr].이전_TOE += getTOE(EnergyType.중온수, list.GetValue(dateStr).이전_중온수);
  242. list[dateStr].TOE = Math.Round(list[dateStr].TOE, 2);
  243. list[dateStr].이전_TOE = Math.Round(list[dateStr].이전_TOE, 2);
  244. }
  245. date = interval == EnergyInterval.월 ? date.AddMonths(1) : date.AddDays(1);
  246. }
  247. return list;
  248. }
  249. public double getTOE(EnergyType? type, double val) {
  250. var exp = 0.0;
  251. if (val == 0) return 0;
  252. if (type == EnergyType.전기) {
  253. exp = 0.000215;
  254. } else if (type == EnergyType.가스)
  255. exp = 0.001055;
  256. else if (type == EnergyType.등유)
  257. exp = 0.000895;
  258. else if (type == EnergyType.LPG)
  259. exp = 0.0015;
  260. else if (type == EnergyType.중온수)
  261. exp = 0.001055;
  262. else
  263. return val;
  264. return Math.Round(val * exp, 2);
  265. }
  266. private void _DataGrid(EnergyInterval interval, DateTime start, DateTime end, DateTime lastStart, IDictionary<string, EnergyDaily> source, IDictionary<string, CalculationLastResult> target, EnergyType? type) {
  267. if (target == null) {
  268. target = new Dictionary<string, CalculationLastResult>();
  269. }
  270. var days = (interval == EnergyInterval.일) ? end.Subtract(start).Days + 1 : (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
  271. var date = start;
  272. var lastDate = lastStart;
  273. for (var i = 0; i < days; i++) {
  274. var dateStr = date.ToString("yyyy-MM-dd");
  275. var lastStr = lastDate.ToString("yyyy-MM-dd");
  276. double d_a = 0, dd_a = 0;
  277. if (type != null) {
  278. d_a = Math.Round((source[dateStr].전기 + source[dateStr].가스 + source[dateStr].수도 + source[dateStr].등유 + source[dateStr].LPG + source[dateStr].중온수), 2);
  279. dd_a = Math.Round((source[dateStr].이전_전기 + source[dateStr].이전_가스 + source[dateStr].이전_수도 + source[dateStr].이전_등유 + source[dateStr].이전_LPG + source[dateStr].이전_중온수), 2);
  280. } else {
  281. d_a = source[dateStr].TOE;
  282. dd_a = source[dateStr].이전_TOE;
  283. }
  284. target.Add(dateStr, new CalculationLastResult {
  285. DateTime = date,
  286. ShortDateTime = interval == EnergyInterval.일 ? dateStr : date.ToString("yyyy-MM"),
  287. Value = d_a,
  288. LastDateTime = lastDate,
  289. LastShortDateTime = interval == EnergyInterval.일 ? lastStr : lastDate.ToString("yyyy-MM"),
  290. LastValue = dd_a,
  291. DayOfWeek = interval == EnergyInterval.일 ? " (" + date.GetDayofWeek() + ")" : ""
  292. });
  293. if (interval == EnergyInterval.일) {
  294. date = date.AddDays(1);
  295. lastDate = lastDate.AddDays(1);
  296. } else if (interval == EnergyInterval.월) {
  297. date = date.AddMonths(1);
  298. lastDate = lastDate.AddMonths(1);
  299. }
  300. }
  301. }
  302. private DateTime GetLastYearSameWeekDate(DateTime dDate) {
  303. DateTime rDate = dDate.AddYears(-1);
  304. for (var i = 0; i < 7; i++) {
  305. if (rDate.DayOfWeek == dDate.DayOfWeek) {
  306. break;
  307. }
  308. rDate = rDate.AddDays(1);
  309. }
  310. return rDate;
  311. }
  312. /// <summary>
  313. /// 저장
  314. /// </summary>
  315. [HttpPost("{siteId}/{fuelTypeId}/{fuelPriceId}/{year}/{month}/{priceTypeId}")]
  316. public IActionResult Save(int siteId, int fuelTypeId, int fuelPriceId, string year, string month, int priceTypeId, [FromBody] FmsEnergyAnalysis data) {
  317. if (ModelState.IsValid) {
  318. _service.Save(siteId, fuelTypeId, fuelPriceId, Util.ToInt(year), Util.ToInt(month), priceTypeId, data);
  319. } else {
  320. return BadRequest(ModelState);
  321. }
  322. return Ok();
  323. }
  324. [HttpGet("[action]")]
  325. public IActionResult GetAnalysisData(int siteId, int fuelTypeId, int fuelPriceId, string year, string month, int priceTypeId) {
  326. var data = _service.GetAnalysis(siteId, fuelTypeId, fuelPriceId, year, month, priceTypeId);
  327. var item = data.Select(x => new {
  328. x.SiteId,
  329. CmSite = new {
  330. x.CmSite.Name,
  331. },
  332. x.FuelTypeId,
  333. BemsFuelType = new {
  334. x.FuelType.Name,
  335. },
  336. x.FuelPriceId,
  337. x.Year,
  338. x.Month,
  339. x.Content,
  340. x.PriceTypeId,
  341. BemsPriceType = new {
  342. x.PriceType.PriceTypeIdDesc,
  343. },
  344. });
  345. return Ok(item);
  346. }
  347. [HttpGet("[action]")]
  348. public IActionResult GetPriceType(int siteId, int fuelTypeId) {
  349. var query = from x in _context.BemsPriceType
  350. join s in _context.BemsSitePrice on new { x.PriceTypeId } equals new { s.PriceTypeId }
  351. where s.SiteId == siteId
  352. where s.FuelTypeId == fuelTypeId
  353. where s.UseYn == "Y"
  354. select new {
  355. PriceTypeId = x.PriceTypeId,
  356. PriceTypeIdDesc = x.PriceTypeIdDesc
  357. };
  358. var list = query.GroupBy(x => new { x.PriceTypeId, x.PriceTypeIdDesc }).Select(x => new { x.Key.PriceTypeId, x.Key.PriceTypeIdDesc }).ToList();
  359. return Ok(list);
  360. }
  361. }
  362. }