EnergyUsageController.cs 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430
  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 EnergyUsageController : Controller {
  26. private readonly ILogger<EnergyUsageController> _logger;
  27. private readonly EnergyUsageService _service;
  28. private readonly FMSContext _context;
  29. public EnergyUsageController(
  30. ILogger<EnergyUsageController> logger,
  31. EnergyUsageService service,
  32. FMSContext context
  33. ) {
  34. _logger = logger;
  35. _service = service;
  36. _context = context;
  37. }
  38. /**
  39. * 사이트별 일별 에너지
  40. */
  41. [HttpGet("[action]")]
  42. public IActionResult Data(int siteId, EnergyInterval interval, EnergyType? type, string startDate, string endDate, bool excel = false) {
  43. _logger.LogInformation("siteId:" + siteId);
  44. try {
  45. var start = DateTime.Now.Date.AddDays(-2);
  46. if (!string.IsNullOrEmpty(startDate)) {
  47. start = DateTime.Parse(startDate);
  48. }
  49. var end = DateTime.Now.Date;
  50. if (!string.IsNullOrEmpty(endDate)) {
  51. end = DateTime.Parse(endDate);
  52. }
  53. if (interval == EnergyInterval.월) {
  54. end = end.AddMonths(1).AddDays(-1);
  55. }
  56. _logger.LogInformation("start" + start.ToShortDateString());
  57. IDictionary<string, EnergyDaily> list = new Dictionary<string, EnergyDaily>();
  58. IDictionary<string, CalculationResult> energyMap = null;
  59. IDictionary<string, CalculationResult> electMap = null;
  60. IDictionary<string, CalculationResult> gasMap = null;
  61. IDictionary<string, CalculationResult> waterMap = null;
  62. IDictionary<string, CalculationResult> lampOilMap = null;
  63. IDictionary<string, CalculationResult> lpgMap = null;
  64. IDictionary<string, CalculationResult> mediumWaterMap = null;
  65. try {
  66. if (type != null) {
  67. energyMap = _service.GetEnergyDailyDic(siteId, interval, (EnergyType)type, start, end);
  68. } else {
  69. //TOE
  70. electMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.전기, start, end);
  71. gasMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.가스, start, end);
  72. waterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.수도, start, end);
  73. lampOilMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.등유, start, end);
  74. lpgMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, start, end);
  75. mediumWaterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, start, end);
  76. }
  77. } catch (Exception ex) {
  78. _logger.LogError(ex, "");
  79. }
  80. if (type != null) {
  81. _DateFillAll(interval, start, end, (EnergyType)type, energyMap, list);
  82. } else {
  83. //TOE
  84. _DateFillAll(interval, start, end, EnergyType.전기, electMap, list);
  85. _DateFillAll(interval, start, end, EnergyType.가스, gasMap, list);
  86. _DateFillAll(interval, start, end, EnergyType.등유, lampOilMap, list);
  87. _DateFillAll(interval, start, end, EnergyType.LPG, lpgMap, list);
  88. _DateFillAll(interval, start, end, EnergyType.중온수, mediumWaterMap, list);
  89. list = convertTOE(list, start, end, interval);
  90. }
  91. IDictionary<string, CalculationResult> energyList = new Dictionary<string, CalculationResult>();
  92. _DataGrid(interval, start, end, list, energyList, type);
  93. if (excel) {
  94. var stream = new MemoryStream();
  95. using (var package = new ExcelPackage(stream)) {
  96. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  97. string typeName = type.ToString();
  98. if (type == null) typeName = "TOE";
  99. workSheet.Cells.LoadFromCollection(energyList.Values, true);
  100. workSheet.AddStyle(new PagingRequest.Column[] {
  101. new PagingRequest.Column{
  102. name = "날짜",
  103. field = "ShortDateTime",
  104. },
  105. new PagingRequest.Column{
  106. name = typeName,
  107. field = "Value",
  108. align = "right",
  109. format = "pricePoint"
  110. },
  111. });
  112. package.Save();
  113. }
  114. stream.Position = 0;
  115. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  116. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  117. }
  118. return Ok(new {
  119. type,
  120. list = list.Values,
  121. listGrid = energyList.Values,
  122. maxValue = energyList.Select(x => x.Value.Value).Max()
  123. });
  124. } catch (FormatException ex) {
  125. _logger.LogError(ex, "");
  126. throw new Exception("날짜형식을 올바르게 입력해주세요");
  127. }
  128. }
  129. /**
  130. * 전체 일별 에너지
  131. */
  132. [HttpGet("[action]")]
  133. public IActionResult WholeData(EnergyInterval interval, EnergyType? type, string startDate, string endDate, bool excel = false) {
  134. IDictionary<string, EnergyDaily> list = new Dictionary<string, EnergyDaily>();
  135. IDictionary<string, CalculationResult> energyList = new Dictionary<string, CalculationResult>();
  136. try {
  137. var start = DateTime.Now.Date.AddDays(-2);
  138. if (!string.IsNullOrEmpty(startDate)) {
  139. start = DateTime.Parse(startDate);
  140. }
  141. var end = DateTime.Now.Date;
  142. if (!string.IsNullOrEmpty(endDate)) {
  143. end = DateTime.Parse(endDate);
  144. }
  145. if (interval == EnergyInterval.월) {
  146. end = end.AddMonths(1).AddDays(-1);
  147. }
  148. var siteList = _context.CmSite.ToList();
  149. foreach (var site in siteList) {
  150. var siteId = site.SiteId;
  151. IDictionary<string, CalculationResult> energyMap = null;
  152. IDictionary<string, CalculationResult> electMap = null;
  153. IDictionary<string, CalculationResult> gasMap = null;
  154. IDictionary<string, CalculationResult> waterMap = null;
  155. IDictionary<string, CalculationResult> lampOilMap = null;
  156. IDictionary<string, CalculationResult> lpgMap = null;
  157. IDictionary<string, CalculationResult> mediumWaterMap = null;
  158. try {
  159. if (type != null) {
  160. energyMap = _service.GetEnergyDailyDic(siteId, interval, (EnergyType)type, start, end);
  161. } else {
  162. //TOE
  163. electMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.전기, start, end);
  164. gasMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.가스, start, end);
  165. waterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.수도, start, end);
  166. lampOilMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.등유, start, end);
  167. lpgMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.LPG, start, end);
  168. mediumWaterMap = _service.GetEnergyDailyDic(siteId, interval, EnergyType.중온수, start, end);
  169. }
  170. } catch (Exception ex) {
  171. _logger.LogError(ex, "");
  172. }
  173. if (type != null) {
  174. _DateFillAll(interval, start, end, (EnergyType)type, energyMap, list);
  175. } else {
  176. //TOE
  177. _DateFillAll(interval, start, end, EnergyType.전기, electMap, list);
  178. _DateFillAll(interval, start, end, EnergyType.가스, gasMap, list);
  179. _DateFillAll(interval, start, end, EnergyType.등유, lampOilMap, list);
  180. _DateFillAll(interval, start, end, EnergyType.LPG, lpgMap, list);
  181. _DateFillAll(interval, start, end, EnergyType.중온수, mediumWaterMap, list);
  182. }
  183. }
  184. if (type == null) list = convertTOE(list, start, end, interval);
  185. _DataGrid(interval, start, end, list, energyList, type);
  186. if (excel) {
  187. var stream = new MemoryStream();
  188. using (var package = new ExcelPackage(stream)) {
  189. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  190. string typeName = type.ToString();
  191. if (type == null) typeName = "TOE";
  192. workSheet.Cells.LoadFromCollection(energyList.Values, true);
  193. workSheet.AddStyle(new PagingRequest.Column[] {
  194. new PagingRequest.Column{
  195. name = "날짜",
  196. field = "ShortDateTime",
  197. },
  198. new PagingRequest.Column{
  199. name = typeName,
  200. field = "Value",
  201. align = "right",
  202. format = "pricePoint"
  203. },
  204. });
  205. package.Save();
  206. }
  207. stream.Position = 0;
  208. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  209. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  210. }
  211. return Ok(new {
  212. type,
  213. list = list.Values,
  214. listGrid = energyList.Values,
  215. maxValue = energyList.Select(x => x.Value.Value).Max()
  216. });
  217. } catch (FormatException ex) {
  218. _logger.LogError(ex, "");
  219. throw new Exception("날짜형식을 올바르게 입력해주세요");
  220. }
  221. }
  222. private void _DateFill(EnergyInterval interval, DateTime start, DateTime end, IDictionary<string, CalculationResult> source, IDictionary<string, CalculationResult> target) {
  223. if (target == null) {
  224. target = new Dictionary<string, CalculationResult>();
  225. }
  226. if (interval == EnergyInterval.일) {
  227. var days = end.Subtract(start).Days + 1;
  228. var date = start;
  229. for (var i = 0; i < days; i++) {
  230. var dateStr = date.ToString("yyyy-MM-dd");
  231. if (target.ContainsKey(dateStr)) {
  232. target[dateStr].Value += source?.GetValue(dateStr)?.Value ?? 0;
  233. } else {
  234. target.Add(dateStr, new CalculationResult {
  235. DateTime = date,
  236. ShortDateTime = dateStr,
  237. Value = source?.GetValue(dateStr)?.Value ?? 0,
  238. });
  239. }
  240. date = date.AddDays(1);
  241. }
  242. } else if (interval == EnergyInterval.월) {
  243. var months = (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
  244. _logger.LogInformation("months:" + months);
  245. var date = start;
  246. for (var i = 0; i < months; i++) {
  247. var dateStr = date.ToString("yyyy-MM-dd");
  248. if (target.ContainsKey(dateStr)) {
  249. target[dateStr].Value += source?.GetValue(dateStr)?.Value ?? 0;
  250. } else {
  251. target.Add(dateStr, new CalculationResult {
  252. DateTime = date,
  253. ShortDateTime = dateStr,
  254. Value = source?.GetValue(dateStr)?.Value ?? 0,
  255. });
  256. }
  257. date = date.AddMonths(1);
  258. }
  259. }
  260. }
  261. private void _DateFillAll(EnergyInterval interval, DateTime start, DateTime end, EnergyType energyType, IDictionary<string, CalculationResult> source, IDictionary<string, EnergyDaily> target) {
  262. if (interval == EnergyInterval.일) {
  263. var days = end.Subtract(start).Days + 1;
  264. var date = start;
  265. for (var i = 0; i < days; i++) {
  266. var dateStr = date.ToString("yyyy-MM-dd");
  267. if (target.ContainsKey(dateStr)) {
  268. if (energyType == EnergyType.전기) target[dateStr].전기 += source?.GetValue(dateStr)?.Value ?? 0;
  269. if (energyType == EnergyType.가스) target[dateStr].가스 += source?.GetValue(dateStr)?.Value ?? 0;
  270. if (energyType == EnergyType.수도) target[dateStr].수도 += source?.GetValue(dateStr)?.Value ?? 0;
  271. if (energyType == EnergyType.등유) target[dateStr].등유 += source?.GetValue(dateStr)?.Value ?? 0;
  272. if (energyType == EnergyType.LPG) target[dateStr].LPG += source?.GetValue(dateStr)?.Value ?? 0;
  273. if (energyType == EnergyType.중온수) target[dateStr].중온수 += source?.GetValue(dateStr)?.Value ?? 0;
  274. } else {
  275. target.Add(dateStr, new EnergyDaily {
  276. Date = dateStr,
  277. 전기 = energyType == EnergyType.전기 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  278. 가스 = energyType == EnergyType.가스 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  279. 수도 = energyType == EnergyType.수도 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  280. 등유 = energyType == EnergyType.등유 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  281. LPG = energyType == EnergyType.LPG ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  282. 중온수 = energyType == EnergyType.중온수 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  283. TOE = 0,
  284. });
  285. }
  286. date = date.AddDays(1);
  287. }
  288. } else if (interval == EnergyInterval.월) {
  289. var months = (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
  290. _logger.LogInformation("months:" + months);
  291. var date = start;
  292. for (var i = 0; i < months; i++) {
  293. var dateStr = date.ToString("yyyy-MM-dd");
  294. if (target.ContainsKey(dateStr)) {
  295. if (energyType == EnergyType.전기) target[dateStr].전기 += source?.GetValue(dateStr)?.Value ?? 0;
  296. if (energyType == EnergyType.가스) target[dateStr].가스 += source?.GetValue(dateStr)?.Value ?? 0;
  297. if (energyType == EnergyType.수도) target[dateStr].수도 += source?.GetValue(dateStr)?.Value ?? 0;
  298. if (energyType == EnergyType.등유) target[dateStr].등유 += source?.GetValue(dateStr)?.Value ?? 0;
  299. if (energyType == EnergyType.LPG) target[dateStr].LPG += source?.GetValue(dateStr)?.Value ?? 0;
  300. if (energyType == EnergyType.중온수) target[dateStr].중온수 += source?.GetValue(dateStr)?.Value ?? 0;
  301. } else {
  302. target.Add(dateStr, new EnergyDaily {
  303. Date = dateStr,
  304. 전기 = energyType == EnergyType.전기 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  305. 가스 = energyType == EnergyType.가스 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  306. 수도 = energyType == EnergyType.수도 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  307. 등유 = energyType == EnergyType.등유 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  308. LPG = energyType == EnergyType.LPG ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  309. 중온수 = energyType == EnergyType.중온수 ? source?.GetValue(dateStr)?.Value ?? 0 : 0,
  310. TOE = 0,
  311. });
  312. }
  313. date = date.AddMonths(1);
  314. }
  315. }
  316. }
  317. public IDictionary<string, EnergyDaily> convertTOE(IDictionary<string, EnergyDaily> list, DateTime start, DateTime end, EnergyInterval interval) {
  318. var date = start;
  319. var days = interval == EnergyInterval.월 ? (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1 : end.Subtract(start).Days + 1;
  320. for (var i = 0; i < days; i++) {
  321. var dateStr = date.ToString("yyyy-MM-dd");
  322. if (list.ContainsKey(dateStr)) {
  323. list[dateStr].TOE = getTOE(EnergyType.전기, list.GetValue(dateStr).전기);
  324. list[dateStr].TOE += getTOE(EnergyType.가스, list.GetValue(dateStr).가스);
  325. list[dateStr].TOE += getTOE(EnergyType.수도, list.GetValue(dateStr).수도);
  326. list[dateStr].TOE += getTOE(EnergyType.등유, list.GetValue(dateStr).등유);
  327. list[dateStr].TOE += getTOE(EnergyType.LPG, list.GetValue(dateStr).LPG);
  328. list[dateStr].TOE += getTOE(EnergyType.중온수, list.GetValue(dateStr).중온수);
  329. list[dateStr].이전_TOE = getTOE(EnergyType.전기, list.GetValue(dateStr).이전_전기);
  330. list[dateStr].이전_TOE += getTOE(EnergyType.가스, list.GetValue(dateStr).이전_가스);
  331. list[dateStr].이전_TOE += getTOE(EnergyType.수도, list.GetValue(dateStr).이전_수도);
  332. list[dateStr].이전_TOE += getTOE(EnergyType.등유, list.GetValue(dateStr).이전_등유);
  333. list[dateStr].이전_TOE += getTOE(EnergyType.LPG, list.GetValue(dateStr).이전_LPG);
  334. list[dateStr].이전_TOE += getTOE(EnergyType.중온수, list.GetValue(dateStr).이전_중온수);
  335. list[dateStr].TOE = Math.Round(list[dateStr].TOE, 2);
  336. list[dateStr].이전_TOE = Math.Round(list[dateStr].이전_TOE, 2);
  337. }
  338. date = interval == EnergyInterval.월 ? date.AddMonths(1) : date.AddDays(1);
  339. }
  340. return list;
  341. }
  342. public double getTOE(EnergyType? type, double val) {
  343. var exp = 0.0;
  344. if (val == 0) return 0;
  345. if (type == EnergyType.전기) {
  346. exp = 0.000215;
  347. } else if (type == EnergyType.가스)
  348. exp = 0.001055;
  349. else if (type == EnergyType.등유)
  350. exp = 0.000895;
  351. else if (type == EnergyType.LPG)
  352. exp = 0.0015;
  353. else if (type == EnergyType.중온수)
  354. exp = 0.001055;
  355. else
  356. return val;
  357. return Math.Round(val * exp, 2);
  358. }
  359. private void _DataGrid(EnergyInterval interval, DateTime start, DateTime end, IDictionary<string, EnergyDaily> source, IDictionary<string, CalculationResult> target, EnergyType? type) {
  360. if (target == null) {
  361. target = new Dictionary<string, CalculationResult>();
  362. }
  363. var days = (interval == EnergyInterval.일) ? end.Subtract(start).Days + 1 : (end.Month - start.Month) + 12 * (end.Year - start.Year) + 1;
  364. var date = start;
  365. for (var i = 0; i < days; i++) {
  366. var dateStr = date.ToString("yyyy-MM-dd");
  367. double d_a = 0;
  368. if (type != null) {
  369. d_a = Math.Round((source[dateStr].전기 + source[dateStr].가스 + source[dateStr].수도 + source[dateStr].등유 + source[dateStr].LPG + source[dateStr].중온수), 2);
  370. } else {
  371. d_a = source[dateStr].TOE;
  372. }
  373. target.Add(dateStr, new CalculationResult {
  374. DateTime = date,
  375. ShortDateTime = dateStr,
  376. Value = d_a,
  377. });
  378. if (interval == EnergyInterval.일) {
  379. date = date.AddDays(1);
  380. } else if (interval == EnergyInterval.월) {
  381. date = date.AddMonths(1);
  382. }
  383. }
  384. }
  385. [HttpGet("[action]")]
  386. public IActionResult GetFuelType() {
  387. var query = _service.GetFuelType();
  388. var list = query.Select(x => new {
  389. x.FuelTypeId,
  390. x.Name
  391. }).ToList();
  392. return Ok(list);
  393. }
  394. }
  395. }