using System; using System.Linq; using System.Threading.Tasks; using FMSAdmin.Data; using FMSAdmin.Models; using FMSAdmin.Entities; using FMSApp.Services; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using Microsoft.AspNetCore.Authorization; using FMSAdmin.Helpers; using System.Data; using System.IO; using OfficeOpenXml; using System.Collections.Generic; using System.Dynamic; namespace FMSApp.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/app/[controller]")] public class ManualMeterController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly ManualMeterService _service; private readonly StorageHelper _storage; public ManualMeterController( ILogger logger, FMSContext context, ManualMeterService service, StorageHelper storage ) { _logger = logger; _context = context; _service = service; _storage = storage; } [HttpGet] public IActionResult List([FromQuery] PagingRequest req, [FromQuery] string siteId) { var query = from facility in _context.CmFacility join point in _context.BemsMonitoringPoint on new { facility.SiteId, facility.FacilityCode } equals new { point.SiteId, point.FacilityCode } //into _point //from point in _point.DefaultIfEmpty() where facility.SiteId == Util.ToInt(siteId) orderby facility.FacilityCode select new { facility.SiteId, facility.FacilityTypeId, FacilityTypeName = facility.BemsFacilityType.Name, facility.FacilityCode, facility.BuildingId, BuildingName = facility.CmBuilding.Name, facility.FloorId, FloorName = facility.CmFloor.Name, facility.ZoneId, ZoneName = facility.CmZone.Name, facility.FirstClassId, FirstClassName = facility.FmsFacilityCodeClass.Name, facility.SecondClassId, SecondClassName = facility.FmsFacilityCodeClass1.Name, facility.ThirdClassId, ThirdClassName = facility.FmsFacilityCodeClass2.Name, facility.Name, facility.Nickname, facility.Manufacturer, facility.FacilityCapacity, facility.Rfid, point.PropertyId, MonitoringPointName = point.Name, }; query = query.Filter(req.conditions); query = query.Sort(req.sort); var paging = PagingList.Pagination(query, req.page, req.limit); return Ok(paging); } [HttpGet("[action]")] public IActionResult Get([FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date) { var now = DateTime.Now; var time = new DateTime(date.Year, date.Month, date.Day, now.Hour, 0, 0); var query = from facility in _context.CmFacility join point in _context.BemsMonitoringPoint on new { facility.SiteId, facility.FacilityCode } equals new { point.SiteId, point.FacilityCode } //into _point select new { facility.SiteId, facility.FacilityTypeId, FacilityTypeName = facility.BemsFacilityType.Name, facility.FacilityCode, facility.BuildingId, BuildingName = facility.CmBuilding.Name ?? "", facility.FloorId, FloorName = facility.CmFloor.Name ?? "", facility.ZoneId, ZoneName = facility.CmZone.Name ?? "", facility.FirstClassId, FirstClassName = facility.FmsFacilityCodeClass.Name ?? "", facility.SecondClassId, SecondClassName = facility.FmsFacilityCodeClass1.Name ?? "", facility.ThirdClassId, ThirdClassName = facility.FmsFacilityCodeClass2.Name ?? "", facility.Name, facility.Nickname, facility.Manufacturer, facility.FacilityCapacity, facility.Rfid, point.PropertyId, MonitoringPointName = point.Name, Hour = time.Hour, facility.Multiple, }; var item = query.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) ).FirstOrDefault(); var expansion = new BemsMonitoringPointHistoryHourly() { PrevPinValue = (_context.BemsMonitoringPointHistory15minRawData.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime < time ).OrderByDescending(x => x.CreatedDateTime).FirstOrDefault() ?? new BemsMonitoringPointHistory15minRawData() { CurrentValue = 0 }).CurrentValue, CurrentPinValue = (_context.BemsMonitoringPointHistory15minRawData.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime == time ).FirstOrDefault() ?? new BemsMonitoringPointHistory15minRawData() { CurrentValue = 0 }).CurrentValue, CurrentValue = (_context.BemsMonitoringPointHistoryHourly.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime == time ).FirstOrDefault() ?? new BemsMonitoringPointHistoryHourly() { CurrentValue = 0 }).CurrentValue, }; // 사용량 이나 검침값이 있는데 이전검침값이 없는경우 // 이전검침값을 계산해서 넣어준다. if ((Math.Abs(expansion.CurrentValue) > Double.Epsilon || Math.Abs(expansion.CurrentPinValue) > Double.Epsilon) && Math.Abs(expansion.PrevPinValue) <= Double.Epsilon) { // 이전검침값 : 검침값 - (사용량 / 배수) expansion.PrevPinValue = expansion.CurrentPinValue - (expansion.CurrentValue / item.Multiple); } return Ok(new { Item = item, Expansion = new { expansion.PrevPinValue, expansion.CurrentPinValue, expansion.CurrentValue, } }); } [HttpGet("[action]")] public IActionResult GetCurrentValue([FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date) { var time = new DateTime(date.Year, date.Month, date.Day, date.Hour, 0, 0); var facility = _context.CmFacility.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) ).FirstOrDefault(); if (facility == null) throw new ServiceException("존재하지 않는 설비 입니다."); var currentRawData = _context.BemsMonitoringPointHistory15minRawData.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime == time ).FirstOrDefault(); if (currentRawData == null) { currentRawData = new BemsMonitoringPointHistory15minRawData() { CurrentValue = 0, }; } var prevRawData = _context.BemsMonitoringPointHistory15minRawData.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime < time ).OrderByDescending(x => x.CreatedDateTime).FirstOrDefault(); if (prevRawData == null) { prevRawData = new BemsMonitoringPointHistory15minRawData() { CurrentValue = 0, }; } var hourly = _context.BemsMonitoringPointHistoryHourly.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime == time ).FirstOrDefault(); if (hourly == null) { hourly = new BemsMonitoringPointHistoryHourly() { CreatedDateTime = time, CurrentValue = 0, }; } // 사용량 이나 검침값이 있는데 이전검침값이 없는경우 // 이전검침값을 계산해서 넣어준다. if ((Math.Abs(hourly.CurrentValue) > Double.Epsilon || Math.Abs(currentRawData.CurrentValue) > Double.Epsilon) && Math.Abs(prevRawData.CurrentValue) <= Double.Epsilon) { // 이전검침값 : 검침값 - (사용량 / 배수) prevRawData.CurrentValue = currentRawData.CurrentValue - (hourly.CurrentValue / facility.Multiple); } return Ok(new { Hour = hourly.CreatedDateTime.Hour, PrevPinValue = prevRawData.CurrentValue, CurrentPinValue = currentRawData.CurrentValue, CurrentValue = hourly.CurrentValue, }); } [HttpGet("[action]")] public IActionResult GetLastMeteredHour([FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date) { var time = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0); int? hour = null; var hourly = _context.BemsMonitoringPointHistoryHourly.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime.Date == time ).OrderByDescending(x => x.CreatedDateTime).FirstOrDefault(); if (hourly != null) { hour = hourly.CreatedDateTime.Hour; } return Ok(new { Hour = hour, }); } [HttpPost("[action]")] public IActionResult Save([FromBody] BemsMonitoringPointHistoryHourly data) { _logger.LogInformation("Save"); //_logger.LogInformation(data.Dump()); if (ModelState.IsValid) { _service.Save(data); } else { return BadRequest(ModelState); } return Ok(); } [HttpGet("[action]")] public IActionResult MeteredList([FromQuery] PagingRequest req, [FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] string propertyId, [FromQuery] DateTime date, [FromQuery] string period) { var startDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0); var endDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0); startDate = startDate.AddDays(Util.ToInt(period) * -1); endDate = endDate.AddDays(1); var query = _context.BemsMonitoringPointHistoryHourly.Where( x => x.SiteId == Util.ToInt(siteId) && x.FacilityCode == Util.ToInt(facilityCode) && x.PropertyId == Util.ToInt(propertyId) && x.CreatedDateTime >= startDate && x.CreatedDateTime < endDate ); query = query.Filter(req.conditions); var list = query.Select(x => new { x.CreatedDateTime, x.CurrentValue, }).ToList(); return Ok(list); } [HttpGet("[action]")] public IActionResult ReportList([FromQuery] PagingRequest req, [FromQuery] string siteId, [FromQuery] string facilityCode, [FromQuery] DateTime date, [FromQuery] string period) { var startDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0); var endDate = new DateTime(date.Year, date.Month, date.Day, 0, 0, 0); startDate = startDate.AddDays(Util.ToInt(period) * -1); endDate = endDate.AddDays(1); var query = from report in _context.BemsReportHistory join format in _context.BemsReportFormat on new { report.SiteId, report.ReportFormatId } equals new { format.SiteId, format.ReportFormatId } join ff in _context.BemsReportFormatToFacility on new { format.SiteId, format.ReportFormatId } equals new { ff.SiteId, ff.ReportFormatId } join facility in _context.CmFacility on new { ff.SiteId, ff.FacilityCode } equals new { facility.SiteId, facility.FacilityCode } where facility.SiteId == Util.ToInt(siteId) && facility.FacilityCode == Util.ToInt(facilityCode) && report.CreateDate >= startDate && report.CreateDate < endDate select report; var list = query.Select(x => new { x.ReportName, x.Writer, x.CreateDate, }).ToList(); return Ok(list); } } }