FacilityTimeController.cs 7.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177
  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. using System.Data;
  21. namespace FMSAdmin.Controllers {
  22. [Authorize]
  23. [ApiController]
  24. [ApiVersion("1")]
  25. [Route("api/[controller]")]
  26. public class FacilityTimeController : Controller {
  27. private readonly ILogger<FacilityTimeController> _logger;
  28. private readonly EnergyUsageService _service;
  29. private readonly FMSContext _context;
  30. public FacilityTimeController(
  31. ILogger<FacilityTimeController> logger,
  32. EnergyUsageService service,
  33. FMSContext context
  34. ) {
  35. _logger = logger;
  36. _service = service;
  37. _context = context;
  38. }
  39. /**
  40. * 가동시간 대상 설비종류
  41. */
  42. [HttpGet("[action]")]
  43. public IActionResult FacilityTypeList(int siteId) {
  44. var query = from x in _context.BemsFacilityType
  45. where x.BemsMonitoringPoint.Where(p => p.PropertyId == 32 && p.SiteId == siteId).Any()
  46. select x;
  47. var list = query.Select(x => new {
  48. x.FacilityTypeId,
  49. x.Name,
  50. });
  51. return Ok(list);
  52. }
  53. /**
  54. * 사이트별 월별 시설종류별 가동시간
  55. */
  56. [HttpGet("[action]")]
  57. public IActionResult Data(int siteId, int? facilityType, int? year, int? month, bool excel = false) {
  58. _logger.LogInformation("siteId:" + siteId);
  59. try {
  60. if (year == null) year = DateTime.Now.Year;
  61. if (month == null) month = DateTime.Now.Month;
  62. var start = new DateTime(year.Value, month.Value, 1);
  63. var end = start.AddMonths(1).AddDays(-1);
  64. _logger.LogInformation("start" + start.ToShortDateString());
  65. _logger.LogInformation("end" + end.ToShortDateString());
  66. var days = end.Subtract(start).Days + 1;
  67. var rawdata = _GetRawData(siteId, facilityType, start, end);
  68. var list_t = (from x in rawdata
  69. // orderby x.CmFacility.Name
  70. select new {
  71. x.CmFacility.Name,
  72. x.FacilityCode,
  73. x.CreatedDateTime.Day,
  74. x.DailyValue,
  75. Sort1 = x.CmFacility.Name.Substring(0, x.CmFacility.Name.IndexOf("-") != -1 ? x.CmFacility.Name.IndexOf("-") : x.CmFacility.Name.Length),
  76. Sort2 = Util.ToInt(x.CmFacility.Name.Substring(x.CmFacility.Name.LastIndexOf("-") + 1, x.CmFacility.Name.Length - x.CmFacility.Name.LastIndexOf("-") - 1)),
  77. }).ToList();
  78. var list = list_t.OrderBy(x => x.Sort1).ThenBy(x => x.Sort2);
  79. //_logger.LogInformation(list.Dump());
  80. var columns = new List<string>();
  81. PagingRequest.Column[] pageColumn = new PagingRequest.Column[days + 2];
  82. var table = new DataTable();
  83. table.Columns.Add("설비명칭");
  84. table.Columns.Add("가동시간(분)", typeof(Double));
  85. columns.Add("설비명칭");
  86. columns.Add("가동시간(분)");
  87. pageColumn[0] = new PagingRequest.Column { name = "설비명칭", field = "설비명칭" };
  88. pageColumn[1] = new PagingRequest.Column { name = "가동시간(분)", field = "가동시간(분)", format = "price2" };
  89. for (var d = 1; d <= days; d++) {
  90. table.Columns.Add($"{d}일", typeof(Double));
  91. columns.Add($"{d}일");
  92. pageColumn[d + 1] = new PagingRequest.Column { name = $"{d}일", field = $"{d}일", format = "price2" };
  93. }
  94. int currentCode = -1;
  95. DataRow row = null;
  96. double total = 0;
  97. foreach (var item in list) {
  98. if (currentCode != item.FacilityCode) {
  99. if (row != null) {
  100. row["가동시간(분)"] = total;
  101. total = 0;
  102. table.Rows.Add(row);
  103. row = null;
  104. }
  105. row = table.NewRow();
  106. row["설비명칭"] = item.Name;
  107. for (var d = 1; d <= days; d++) {
  108. row[$"{d}일"] = 0;
  109. }
  110. }
  111. row[$"{item.Day}일"] = Math.Round(item.DailyValue);
  112. total += Math.Round(item.DailyValue);
  113. currentCode = item.FacilityCode;
  114. }
  115. if (row != null) {
  116. row["가동시간(분)"] = total;
  117. total = 0;
  118. table.Rows.Add(row);
  119. row = null;
  120. }
  121. if (excel) {
  122. var stream = new MemoryStream();
  123. using (var package = new ExcelPackage(stream)) {
  124. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  125. workSheet.Cells.LoadFromDataTable(table, true);
  126. // workSheet.AddStyle();
  127. workSheet.AddStyle(pageColumn, true, "설비 가동시간");
  128. package.Save();
  129. }
  130. stream.Position = 0;
  131. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  132. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  133. }
  134. return Ok(new {
  135. columns,
  136. data = table
  137. });
  138. } catch (FormatException ex) {
  139. _logger.LogError(ex, "");
  140. throw new Exception("날짜형식을 올바르게 입력해주세요");
  141. }
  142. }
  143. private IQueryable<BemsMonitoringPointHistoryDaily> _GetRawData(int siteId, int? facilityType, DateTime startDate, DateTime endDate) {
  144. const int propertyId = 32; // 가동시간
  145. var query = from x in _context.BemsMonitoringPointHistoryDaily
  146. where x.SiteId == siteId &&
  147. //x.FacilityTypeId == facilityType &&
  148. x.PropertyId == propertyId &&
  149. startDate <= x.CreatedDateTime && x.CreatedDateTime <= endDate
  150. select x;
  151. if (facilityType != null) {
  152. query = query.Where(x => x.FacilityTypeId == facilityType.Value);
  153. }
  154. return query;
  155. }
  156. }
  157. }