using System; using System.Collections.Generic; using System.Linq; using System.Linq.Dynamic.Core; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using FMSAdmin.Data; using FMSAdmin.Helpers; using System.Collections; using System.Text.RegularExpressions; using FMSAdmin.Models; using FMSAdmin.Entities; using FMSApp.Services; using Microsoft.AspNetCore.Authorization; using FMSAdmin.Models.Formula; using FMSAdmin.Helpers.Formula; using FMSAdmin.Services; using System.IO; using OfficeOpenXml; using System.Data; namespace FMSAdmin.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/[controller]")] public class FacilityTimeController : Controller { private readonly ILogger<FacilityTimeController> _logger; private readonly EnergyUsageService _service; private readonly FMSContext _context; public FacilityTimeController( ILogger<FacilityTimeController> logger, EnergyUsageService service, FMSContext context ) { _logger = logger; _service = service; _context = context; } /** * 가동시간 대상 설비종류 */ [HttpGet("[action]")] public IActionResult FacilityTypeList(int siteId) { var query = from x in _context.BemsFacilityType where x.BemsMonitoringPoint.Where(p => p.PropertyId == 32 && p.SiteId == siteId).Any() select x; var list = query.Select(x => new { x.FacilityTypeId, x.Name, }); return Ok(list); } /** * 사이트별 월별 시설종류별 가동시간 */ [HttpGet("[action]")] public IActionResult Data(int siteId, int? facilityType, int? year, int? month, bool excel = false) { _logger.LogInformation("siteId:" + siteId); try { if (year == null) year = DateTime.Now.Year; if (month == null) month = DateTime.Now.Month; var start = new DateTime(year.Value, month.Value, 1); var end = start.AddMonths(1).AddDays(-1); _logger.LogInformation("start" + start.ToShortDateString()); _logger.LogInformation("end" + end.ToShortDateString()); var days = end.Subtract(start).Days + 1; var rawdata = _GetRawData(siteId, facilityType, start, end); var list_t = (from x in rawdata // orderby x.CmFacility.Name select new { x.CmFacility.Name, x.FacilityCode, x.CreatedDateTime.Day, x.DailyValue, Sort1 = x.CmFacility.Name.Substring(0, x.CmFacility.Name.IndexOf("-") != -1 ? x.CmFacility.Name.IndexOf("-") : x.CmFacility.Name.Length), Sort2 = Util.ToInt(x.CmFacility.Name.Substring(x.CmFacility.Name.LastIndexOf("-") + 1, x.CmFacility.Name.Length - x.CmFacility.Name.LastIndexOf("-") - 1)), }).ToList(); var list = list_t.OrderBy(x => x.Sort1).ThenBy(x => x.Sort2); //_logger.LogInformation(list.Dump()); var columns = new List<string>(); PagingRequest.Column[] pageColumn = new PagingRequest.Column[days + 2]; var table = new DataTable(); table.Columns.Add("설비명칭"); table.Columns.Add("가동시간(분)", typeof(Double)); columns.Add("설비명칭"); columns.Add("가동시간(분)"); pageColumn[0] = new PagingRequest.Column { name = "설비명칭", field = "설비명칭" }; pageColumn[1] = new PagingRequest.Column { name = "가동시간(분)", field = "가동시간(분)", format = "price2" }; for (var d = 1; d <= days; d++) { table.Columns.Add($"{d}일", typeof(Double)); columns.Add($"{d}일"); pageColumn[d + 1] = new PagingRequest.Column { name = $"{d}일", field = $"{d}일", format = "price2" }; } int currentCode = -1; DataRow row = null; double total = 0; foreach (var item in list) { if (currentCode != item.FacilityCode) { if (row != null) { row["가동시간(분)"] = total; total = 0; table.Rows.Add(row); row = null; } row = table.NewRow(); row["설비명칭"] = item.Name; for (var d = 1; d <= days; d++) { row[$"{d}일"] = 0; } } row[$"{item.Day}일"] = Math.Round(item.DailyValue); total += Math.Round(item.DailyValue); currentCode = item.FacilityCode; } if (row != null) { row["가동시간(분)"] = total; total = 0; table.Rows.Add(row); row = null; } if (excel) { var stream = new MemoryStream(); using (var package = new ExcelPackage(stream)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); workSheet.Cells.LoadFromDataTable(table, true); // workSheet.AddStyle(); workSheet.AddStyle(pageColumn, true, "설비 가동시간"); package.Save(); } stream.Position = 0; string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx"; return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); } return Ok(new { columns, data = table }); } catch (FormatException ex) { _logger.LogError(ex, ""); throw new Exception("날짜형식을 올바르게 입력해주세요"); } } private IQueryable<BemsMonitoringPointHistoryDaily> _GetRawData(int siteId, int? facilityType, DateTime startDate, DateTime endDate) { const int propertyId = 32; // 가동시간 var query = from x in _context.BemsMonitoringPointHistoryDaily where x.SiteId == siteId && //x.FacilityTypeId == facilityType && x.PropertyId == propertyId && startDate <= x.CreatedDateTime && x.CreatedDateTime <= endDate select x; if (facilityType != null) { query = query.Where(x => x.FacilityTypeId == facilityType.Value); } return query; } } }