123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177 |
- 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;
- }
- }
- }
|