ManualMeterController.cs 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309
  1. using System;
  2. using System.Linq;
  3. using System.Threading.Tasks;
  4. using FMSAdmin.Data;
  5. using FMSAdmin.Models;
  6. using FMSAdmin.Entities;
  7. using FMSApp.Services;
  8. using Microsoft.AspNetCore.Mvc;
  9. using Microsoft.Extensions.Logging;
  10. using Microsoft.AspNetCore.Authorization;
  11. using FMSAdmin.Helpers;
  12. using System.Data;
  13. using System.IO;
  14. using OfficeOpenXml;
  15. using System.Collections.Generic;
  16. using System.Dynamic;
  17. namespace FMSApp.Controllers {
  18. [Authorize]
  19. [ApiController]
  20. [ApiVersion("1")]
  21. [Route("api/app/[controller]")]
  22. public class ManualMeterController : Controller {
  23. private readonly ILogger<ManualMeterController> _logger;
  24. private readonly FMSContext _context;
  25. private readonly ManualMeterService _service;
  26. private readonly StorageHelper _storage;
  27. public ManualMeterController(
  28. ILogger<ManualMeterController> logger,
  29. FMSContext context,
  30. ManualMeterService service,
  31. StorageHelper storage
  32. ) {
  33. _logger = logger;
  34. _context = context;
  35. _service = service;
  36. _storage = storage;
  37. }
  38. [HttpGet]
  39. public IActionResult List([FromQuery] PagingRequest req, [FromQuery] string siteId) {
  40. var query = from facility in _context.CmFacility
  41. join point in _context.BemsMonitoringPoint on new { facility.SiteId, facility.FacilityCode } equals new { point.SiteId, point.FacilityCode } //into _point
  42. //from point in _point.DefaultIfEmpty()
  43. where facility.SiteId == Util.ToInt(siteId)
  44. orderby facility.FacilityCode
  45. select new {
  46. facility.SiteId,
  47. facility.FacilityTypeId,
  48. FacilityTypeName = facility.BemsFacilityType.Name,
  49. facility.FacilityCode,
  50. facility.BuildingId,
  51. BuildingName = facility.CmBuilding.Name,
  52. facility.FloorId,
  53. FloorName = facility.CmFloor.Name,
  54. facility.ZoneId,
  55. ZoneName = facility.CmZone.Name,
  56. facility.FirstClassId,
  57. FirstClassName = facility.FmsFacilityCodeClass.Name,
  58. facility.SecondClassId,
  59. SecondClassName = facility.FmsFacilityCodeClass1.Name,
  60. facility.ThirdClassId,
  61. ThirdClassName = facility.FmsFacilityCodeClass2.Name,
  62. facility.Name,
  63. facility.Nickname,
  64. facility.Manufacturer,
  65. facility.FacilityCapacity,
  66. facility.Rfid,
  67. point.PropertyId,
  68. MonitoringPointName = point.Name,
  69. };
  70. query = query.Filter(req.conditions);
  71. query = query.Sort(req.sort);
  72. var paging = PagingList.Pagination(query, req.page, req.limit);
  73. return Ok(paging);
  74. }
  75. [HttpGet("[action]")]
  76. public IActionResult Get([FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date) {
  77. var now = DateTime.Now;
  78. var time = new DateTime(date.Year, date.Month, date.Day, now.Hour, 0, 0);
  79. var query = from facility in _context.CmFacility
  80. join point in _context.BemsMonitoringPoint on new { facility.SiteId, facility.FacilityCode } equals new { point.SiteId, point.FacilityCode } //into _point
  81. select new {
  82. facility.SiteId,
  83. facility.FacilityTypeId,
  84. FacilityTypeName = facility.BemsFacilityType.Name,
  85. facility.FacilityCode,
  86. facility.BuildingId,
  87. BuildingName = facility.CmBuilding.Name ?? "",
  88. facility.FloorId,
  89. FloorName = facility.CmFloor.Name ?? "",
  90. facility.ZoneId,
  91. ZoneName = facility.CmZone.Name ?? "",
  92. facility.FirstClassId,
  93. FirstClassName = facility.FmsFacilityCodeClass.Name ?? "",
  94. facility.SecondClassId,
  95. SecondClassName = facility.FmsFacilityCodeClass1.Name ?? "",
  96. facility.ThirdClassId,
  97. ThirdClassName = facility.FmsFacilityCodeClass2.Name ?? "",
  98. facility.Name,
  99. facility.Nickname,
  100. facility.Manufacturer,
  101. facility.FacilityCapacity,
  102. facility.Rfid,
  103. point.PropertyId,
  104. MonitoringPointName = point.Name,
  105. Hour = time.Hour,
  106. facility.Multiple,
  107. };
  108. var item = query.Where(
  109. x => x.SiteId == Util.ToInt(siteId)
  110. && x.FacilityCode == Util.ToInt(facilityCode)
  111. && x.PropertyId == Util.ToInt(propertyId)
  112. ).FirstOrDefault();
  113. var expansion = new BemsMonitoringPointHistoryHourly() {
  114. PrevPinValue = (_context.BemsMonitoringPointHistory15minRawData.Where(
  115. x => x.SiteId == Util.ToInt(siteId)
  116. && x.FacilityCode == Util.ToInt(facilityCode)
  117. && x.PropertyId == Util.ToInt(propertyId)
  118. && x.CreatedDateTime < time
  119. ).OrderByDescending(x => x.CreatedDateTime).FirstOrDefault() ?? new BemsMonitoringPointHistory15minRawData() { CurrentValue = 0 }).CurrentValue,
  120. CurrentPinValue = (_context.BemsMonitoringPointHistory15minRawData.Where(
  121. x => x.SiteId == Util.ToInt(siteId)
  122. && x.FacilityCode == Util.ToInt(facilityCode)
  123. && x.PropertyId == Util.ToInt(propertyId)
  124. && x.CreatedDateTime == time
  125. ).FirstOrDefault() ?? new BemsMonitoringPointHistory15minRawData() { CurrentValue = 0 }).CurrentValue,
  126. CurrentValue = (_context.BemsMonitoringPointHistoryHourly.Where(
  127. x => x.SiteId == Util.ToInt(siteId)
  128. && x.FacilityCode == Util.ToInt(facilityCode)
  129. && x.PropertyId == Util.ToInt(propertyId)
  130. && x.CreatedDateTime == time
  131. ).FirstOrDefault() ?? new BemsMonitoringPointHistoryHourly() { CurrentValue = 0 }).CurrentValue,
  132. };
  133. // 사용량 이나 검침값이 있는데 이전검침값이 없는경우
  134. // 이전검침값을 계산해서 넣어준다.
  135. if ((Math.Abs(expansion.CurrentValue) > Double.Epsilon || Math.Abs(expansion.CurrentPinValue) > Double.Epsilon)
  136. && Math.Abs(expansion.PrevPinValue) <= Double.Epsilon) {
  137. // 이전검침값 : 검침값 - (사용량 / 배수)
  138. expansion.PrevPinValue = expansion.CurrentPinValue - (expansion.CurrentValue / item.Multiple);
  139. }
  140. return Ok(new {
  141. Item = item,
  142. Expansion = new {
  143. expansion.PrevPinValue,
  144. expansion.CurrentPinValue,
  145. expansion.CurrentValue,
  146. }
  147. });
  148. }
  149. [HttpGet("[action]")]
  150. public IActionResult GetCurrentValue([FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date) {
  151. var time = new DateTime(date.Year, date.Month, date.Day, date.Hour, 0, 0);
  152. var facility = _context.CmFacility.Where(
  153. x => x.SiteId == Util.ToInt(siteId)
  154. && x.FacilityCode == Util.ToInt(facilityCode)
  155. ).FirstOrDefault();
  156. if (facility == null) throw new ServiceException("존재하지 않는 설비 입니다.");
  157. var currentRawData = _context.BemsMonitoringPointHistory15minRawData.Where(
  158. x => x.SiteId == Util.ToInt(siteId)
  159. && x.FacilityCode == Util.ToInt(facilityCode)
  160. && x.PropertyId == Util.ToInt(propertyId)
  161. && x.CreatedDateTime == time
  162. ).FirstOrDefault();
  163. if (currentRawData == null) {
  164. currentRawData = new BemsMonitoringPointHistory15minRawData() {
  165. CurrentValue = 0,
  166. };
  167. }
  168. var prevRawData = _context.BemsMonitoringPointHistory15minRawData.Where(
  169. x => x.SiteId == Util.ToInt(siteId)
  170. && x.FacilityCode == Util.ToInt(facilityCode)
  171. && x.PropertyId == Util.ToInt(propertyId)
  172. && x.CreatedDateTime < time
  173. ).OrderByDescending(x => x.CreatedDateTime).FirstOrDefault();
  174. if (prevRawData == null) {
  175. prevRawData = new BemsMonitoringPointHistory15minRawData() {
  176. CurrentValue = 0,
  177. };
  178. }
  179. var hourly = _context.BemsMonitoringPointHistoryHourly.Where(
  180. x => x.SiteId == Util.ToInt(siteId)
  181. && x.FacilityCode == Util.ToInt(facilityCode)
  182. && x.PropertyId == Util.ToInt(propertyId)
  183. && x.CreatedDateTime == time
  184. ).FirstOrDefault();
  185. if (hourly == null) {
  186. hourly = new BemsMonitoringPointHistoryHourly() {
  187. CreatedDateTime = time,
  188. CurrentValue = 0,
  189. };
  190. }
  191. // 사용량 이나 검침값이 있는데 이전검침값이 없는경우
  192. // 이전검침값을 계산해서 넣어준다.
  193. if ((Math.Abs(hourly.CurrentValue) > Double.Epsilon || Math.Abs(currentRawData.CurrentValue) > Double.Epsilon)
  194. && Math.Abs(prevRawData.CurrentValue) <= Double.Epsilon) {
  195. // 이전검침값 : 검침값 - (사용량 / 배수)
  196. prevRawData.CurrentValue = currentRawData.CurrentValue - (hourly.CurrentValue / facility.Multiple);
  197. }
  198. return Ok(new {
  199. Hour = hourly.CreatedDateTime.Hour,
  200. PrevPinValue = prevRawData.CurrentValue,
  201. CurrentPinValue = currentRawData.CurrentValue,
  202. CurrentValue = hourly.CurrentValue,
  203. });
  204. }
  205. [HttpGet("[action]")]
  206. public IActionResult GetLastMeteredHour([FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date) {
  207. var time = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
  208. int? hour = null;
  209. var hourly = _context.BemsMonitoringPointHistoryHourly.Where(
  210. x => x.SiteId == Util.ToInt(siteId)
  211. && x.FacilityCode == Util.ToInt(facilityCode)
  212. && x.PropertyId == Util.ToInt(propertyId)
  213. && x.CreatedDateTime.Date == time
  214. ).OrderByDescending(x => x.CreatedDateTime).FirstOrDefault();
  215. if (hourly != null) {
  216. hour = hourly.CreatedDateTime.Hour;
  217. }
  218. return Ok(new {
  219. Hour = hour,
  220. });
  221. }
  222. [HttpPost("[action]")]
  223. public IActionResult Save([FromBody] BemsMonitoringPointHistoryHourly data) {
  224. _logger.LogInformation("Save");
  225. //_logger.LogInformation(data.Dump());
  226. if (ModelState.IsValid) {
  227. _service.Save(data);
  228. } else {
  229. return BadRequest(ModelState);
  230. }
  231. return Ok();
  232. }
  233. [HttpGet("[action]")]
  234. public IActionResult MeteredList([FromQuery] PagingRequest req, [FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date, [FromQuery] string period) {
  235. var startDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
  236. var endDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
  237. startDate = startDate.AddDays(Util.ToInt(period) * -1);
  238. endDate = endDate.AddDays(1);
  239. var query = _context.BemsMonitoringPointHistoryHourly.Where(
  240. x => x.SiteId == Util.ToInt(siteId)
  241. && x.FacilityCode == Util.ToInt(facilityCode)
  242. && x.PropertyId == Util.ToInt(propertyId)
  243. && x.CreatedDateTime >= startDate
  244. && x.CreatedDateTime < endDate
  245. );
  246. query = query.Filter(req.conditions);
  247. var list = query.Select(x => new {
  248. x.CreatedDateTime,
  249. x.CurrentValue,
  250. }).ToList();
  251. return Ok(list);
  252. }
  253. [HttpGet("[action]")]
  254. public IActionResult ReportList([FromQuery] PagingRequest req, [FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] DateTime date, [FromQuery] string period) {
  255. var startDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
  256. var endDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0);
  257. startDate = startDate.AddDays(Util.ToInt(period) * -1);
  258. endDate = endDate.AddDays(1);
  259. var query = from report in _context.BemsReportHistory
  260. join format in _context.BemsReportFormat on new { report.SiteId, report.ReportFormatId } equals new { format.SiteId, format.ReportFormatId }
  261. join ff in _context.BemsReportFormatToFacility on new { format.SiteId, format.ReportFormatId } equals new { ff.SiteId, ff.ReportFormatId }
  262. join facility in _context.CmFacility on new { ff.SiteId, ff.FacilityCode } equals new { facility.SiteId, facility.FacilityCode }
  263. where facility.SiteId == Util.ToInt(siteId)
  264. && facility.FacilityCode == Util.ToInt(facilityCode)
  265. && report.CreateDate >= startDate && report.CreateDate < endDate
  266. select report;
  267. var list = query.Select(x => new {
  268. x.ReportName,
  269. x.Writer,
  270. x.CreateDate,
  271. }).ToList();
  272. return Ok(list);
  273. }
  274. }
  275. }