123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729 |
- using System;
- using System.Collections.Generic;
- using System.IdentityModel.Tokens.Jwt;
- using System.Linq;
- using System.Security.Claims;
- using System.Text;
- using FMSAdmin.Data;
- using FMSAdmin.Helpers;
- using FMSAdmin.Entities;
- using Microsoft.EntityFrameworkCore;
- using Microsoft.Extensions.Logging;
- using Microsoft.Extensions.Options;
- using Microsoft.IdentityModel.Tokens;
- using FMSAdmin.Models;
- using SelectPdf;
- using System.IO;
- using System.Data;
- using FMSAdmin.Models.Formula;
- namespace FMSApp.Services {
- public class MonthlyReportService {
- private readonly ILogger<MonthlyReportService> _logger;
- private readonly FMSContext _context;
- private readonly AppSettings _appSettings;
- private readonly StorageHelper _storage;
- public MonthlyReportService(
- ILogger<MonthlyReportService> logger,
- FMSContext context,
- IOptions<AppSettings> appSettings,
- StorageHelper storage
- ) {
- _logger = logger;
- _context = context;
- _appSettings = appSettings.Value;
- _storage = storage;
- }
- public IQueryable<FmsMonthlyReport> GetAll() {
- var query = _context.FmsMonthlyReport;
- return query;
- }
- public void Approve(int siteId, string approverUserId, DateTime addDate, int businessFieldId) {
- var persist = _context.FmsMonthlyReport.FirstOrDefault(
- x => x.SiteId == siteId
- && x.AddDate == addDate.Date
- // && x.BusinessFieldId == businessFieldId 20200625 요청
- );
- if (persist == null) throw new ServiceException("해당일자의 일간보고서가 존재하지 않습니다.");
- if (string.IsNullOrEmpty(persist.ApproverUserId)) {
- persist.ApproverUserId = approverUserId;
- persist.ApproveDate = DateTime.Now;
- } else if (string.IsNullOrEmpty(persist.ApproverUserId2)) {
- persist.ApproverUserId2 = approverUserId;
- persist.ApproveDate2 = DateTime.Now;
- } else if (string.IsNullOrEmpty(persist.ApproverUserId3)) {
- persist.ApproverUserId3 = approverUserId;
- persist.ApproveDate3 = DateTime.Now;
- } else if (string.IsNullOrEmpty(persist.ApproverUserId4)) {
- persist.ApproverUserId4 = approverUserId;
- persist.ApproveDate4 = DateTime.Now;
- } else if (string.IsNullOrEmpty(persist.ApproverUserId5)) {
- persist.ApproverUserId5 = approverUserId;
- persist.ApproveDate5 = DateTime.Now;
- } else {
- throw new ServiceException("결재 한도를 초과하였습니다.");
- }
- _context.FmsMonthlyReport.Update(persist);
- _context.SaveChanges();
- }
- public void Delete(int siteId, DateTime addDate, int businessFieldId) {
- var persist = _context.FmsMonthlyReport.FirstOrDefault(
- x => x.SiteId == siteId
- && x.AddDate.Year == addDate.Year
- && x.AddDate.Month == addDate.Month
- && x.BusinessFieldId == businessFieldId
- );
- if (persist == null) throw new ServiceException("해당일자의 일간보고서가 존재하지 않습니다.");
- _context.FmsMonthlyReport.Remove(persist);
- _context.SaveChanges();
- }
- public FmsMonthlyReport checkFile(FmsMonthlyReport data) {
- if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
- data.ParkingManagement.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.ParkingManagement.FileCategory = category;
- data.ParkingManagement.CreatedDate = DateTime.Now;
- } else {
- data.ParkingManagement = null;
- }
- if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
- data.RentalManagement.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.RentalManagement.FileCategory = category;
- data.RentalManagement.CreatedDate = DateTime.Now;
- } else {
- data.RentalManagement = null;
- }
- if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
- data.MonthlyPriorityWork.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.MonthlyPriorityWork.FileCategory = category;
- data.MonthlyPriorityWork.CreatedDate = DateTime.Now;
- } else {
- data.MonthlyPriorityWork = null;
- }
- if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
- data.ImprovementReport.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.ImprovementReport.FileCategory = category;
- data.ImprovementReport.CreatedDate = DateTime.Now;
- } else {
- data.ImprovementReport = null;
- }
- if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
- data.NextMonthPriorityWork.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.NextMonthPriorityWork.FileCategory = category;
- data.NextMonthPriorityWork.CreatedDate = DateTime.Now;
- } else {
- data.NextMonthPriorityWork = null;
- }
- if (data.Cost != null && data.Cost.IsUpload) {
- data.Cost.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.Cost.FileCategory = category;
- data.Cost.CreatedDate = DateTime.Now;
- } else {
- data.Cost = null;
- }
- if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
- data.SurroundingStatus.SiteId = data.SiteId;
- var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
- data.SurroundingStatus.FileCategory = category;
- data.SurroundingStatus.CreatedDate = DateTime.Now;
- } else {
- data.SurroundingStatus = null;
- }
- return data;
- }
- public void copyFile(FmsMonthlyReport data, FmsMonthlyReport persist) {
- if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
- _storage.CopyEntity(data.ParkingManagement.Path, persist.ParkingManagement);
- _storage.PdfConvertImage(data.ParkingManagement);
- }
- if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
- _storage.CopyEntity(data.RentalManagement.Path, persist.RentalManagement);
- _storage.PdfConvertImage(data.RentalManagement);
- }
- if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
- _storage.CopyEntity(data.MonthlyPriorityWork.Path, persist.MonthlyPriorityWork);
- _storage.PdfConvertImage(data.MonthlyPriorityWork);
- }
- if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
- _storage.CopyEntity(data.ImprovementReport.Path, persist.ImprovementReport);
- _storage.PdfConvertImage(data.ImprovementReport);
- }
- if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
- _storage.CopyEntity(data.NextMonthPriorityWork.Path, persist.NextMonthPriorityWork);
- _storage.PdfConvertImage(data.NextMonthPriorityWork);
- }
- if (data.Cost != null && data.Cost.IsUpload) {
- _storage.CopyEntity(data.Cost.Path, persist.Cost);
- _storage.PdfConvertImage(data.Cost);
- }
- if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
- _storage.CopyEntity(data.SurroundingStatus.Path, persist.SurroundingStatus);
- _storage.PdfConvertImage(data.SurroundingStatus);
- }
- }
- public void Save(FmsMonthlyReport data, bool isCreate) {
- var persist = _context.FmsMonthlyReport.FirstOrDefault(
- x => x.SiteId == data.SiteId
- && x.AddDate.Year == data.AddDate.Year
- && x.AddDate.Month == data.AddDate.Month);
- data = checkFile(data);
- if (persist == null) {
- if (data.OrganizationChartStatusFileId != null) {
- data.OrganizationChartStatus = _context.CmFile.Where(x => x.SiteId == data.SiteId && x.FileId == data.OrganizationChartStatusFileId.Value).FirstOrDefault();
- }
- if (data.RentalStatusFileId != null) {
- data.RentalStatus = _context.CmFile.Where(x => x.SiteId == data.SiteId && x.FileId == data.RentalStatusFileId.Value).FirstOrDefault();
- }
- data.RegisterDate = DateTime.Now;
- _context.FmsMonthlyReport.Add(data);
- _context.SaveChanges();
- } else {
- if (isCreate) throw new ServiceException("해당일자의 보고서가 이미 등록되어있습니다.");
- persist.Name = data.Name;
- persist.Contents = data.Contents;
- persist.SiteInfoDatas = data.SiteInfoDatas;
- persist.FacilityStandards = data.FacilityStandards;
- persist.RentalStandards = data.RentalStandards;
- persist.HumanCountStatus = data.HumanCountStatus;
- persist.ManagementOfficeContact = data.ManagementOfficeContact;
- persist.RelatedInstitutionsContact = data.RelatedInstitutionsContact;
- persist.UserChangeStatus = data.UserChangeStatus;
- persist.LayoutStatus = data.LayoutStatus;
- persist.TenantContact = data.TenantContact;
- persist.FacilityTime = data.FacilityTime;
- persist.FacilityTimeGraph = data.FacilityTimeGraph;
- persist.EnergyUsage = data.EnergyUsage;
- persist.EnergyUsageGraph = data.EnergyUsageGraph;
- persist.EnergyCost = data.EnergyCost;
- persist.EnergyCharge = data.EnergyCharge;
- persist.EnergyChargeGraph = data.EnergyChargeGraph;
- persist.EnergyAnalysis = data.EnergyAnalysis;
- if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
- persist.ParkingManagement = data.ParkingManagement;
- }
- if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
- persist.RentalManagement = data.RentalManagement;
- }
- if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
- persist.MonthlyPriorityWork = data.MonthlyPriorityWork;
- }
- if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
- persist.ImprovementReport = data.ImprovementReport;
- }
- if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
- persist.NextMonthPriorityWork = data.NextMonthPriorityWork;
- }
- if (data.Cost != null && data.Cost.IsUpload) {
- persist.Cost = data.Cost;
- }
- if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
- persist.SurroundingStatus = data.SurroundingStatus;
- }
- _context.FmsMonthlyReport.Update(persist);
- _context.SaveChanges();
- }
- copyFile(data, data);
- }
- public void Delete(int siteId, DateTime addData) {
- try {
- var data = _context.FmsMonthlyReport.First(x => x.SiteId == siteId && x.AddDate == addData);
- _context.FmsMonthlyReport.Remove(data);
- //data.IsUse = false;
- _context.SaveChanges();
- } catch {
- throw new ServiceException("이미 다른 화면에서 사용중이므로 삭제할 수 없습니다.");
- }
- }
- /// <summary>
- /// 법정검사
- /// </summary>
- public DataTable GetLegalExamination(int siteId, DateTime addDate) {
- var legalExaminationList = _context.FmsWorkRequest.Where(x =>
- x.SiteId == Util.ToInt(siteId) &&
- x.WorkTypeId == 2 &&
- x.StartWorkDate.Year == addDate.Year
- ).OrderBy(x => x.WorkScheduleId).Select(x => new {
- x.WorkScheduleId,
- Type = x.CmBusinessField.Name,
- x.Title,
- WorkProgress = x.WorkProgressId == 5 ? "작업완료" : x.WorkProgress.Name,
- Cycle = x.FmsWorkSchedule.CycleSize + x.FmsWorkSchedule.FmsWorkCodeCycleUnit.Name.Replace("간", ""),
- Month = x.StartWorkDate.Month,
- Day = x.StartWorkDate.Day
- }).ToList();
- DataTable LegalExamination = new DataTable();
- DataRow legalExaminationRow = null;
- LegalExamination.Columns.Add("Type");
- LegalExamination.Columns.Add("Title");
- for (var m = 1; m <= 12; m++) {
- LegalExamination.Columns.Add($"M{m}");
- }
- LegalExamination.Columns.Add("Progress");
- LegalExamination.Columns.Add("Comment");
- var WorkScheduleId = -1;
- foreach (var item in legalExaminationList.OrderBy(x => x.WorkScheduleId)) {
- if (WorkScheduleId != item.WorkScheduleId) {
- if (legalExaminationRow != null) {
- LegalExamination.Rows.Add(legalExaminationRow);
- }
- legalExaminationRow = LegalExamination.NewRow();
- legalExaminationRow["Type"] = item.Type;
- legalExaminationRow["Title"] = item.Title;
- legalExaminationRow["Progress"] = item.WorkProgress;
- legalExaminationRow["Comment"] = item.Cycle;
- }
- for (var m = 1; m <= 12; m++) {
- if (item.Month == m) {
- legalExaminationRow[$"M{m}"] = item.Day;
- } else {
- legalExaminationRow[$"M{m}"] = "";
- }
- }
- WorkScheduleId = item.WorkScheduleId == null ? -1 : item.WorkScheduleId.Value;
- }
- if (legalExaminationRow != null)
- LegalExamination.Rows.Add(legalExaminationRow);
- return LegalExamination;
- }
- /// <summary>
- /// 설비가동시간
- /// </summary>
- public DataTable GetFacilityTime(int siteId, DateTime today) {
- DataTable facilityTime = new DataTable();
- DataRow facilityTimeRow = null;
- DataRow facilityTimeRowPre = null;
- var facilityTime_now = _context.BemsMonitoringPointHistoryDaily.Where(
- x => x.SiteId == Util.ToInt(siteId) &&
- x.PropertyId == 32 &&
- x.CreatedDateTime.Year == today.Year &&
- x.CreatedDateTime.Month == today.Month
- ).OrderBy(x => x.FacilityCode).ThenBy(x => x.CreatedDateTime).Select(x => new {
- FacilityName = x.CmFacility.Name,
- FloorName = x.CmFacility.CmFloor.Name,
- x.FacilityCode,
- Year = x.CreatedDateTime.Year,
- Day = x.CreatedDateTime.Day,
- x.DailyValue,
- LastDailyValue = _context.BemsMonitoringPointHistoryDaily.Where(f => f.SiteId == Util.ToInt(siteId) && f.PropertyId == 32 && f.FacilityCode == x.FacilityCode && f.CreatedDateTime.Year == today.AddYears(-1).Year && f.CreatedDateTime.Month == today.Month).Select(f => f.DailyValue).FirstOrDefault(),
- }).ToList();
- if (facilityTime_now == null) return facilityTime;
- int currentCode = -1;
- DateTime tmpDt = DateTime.Parse(today.Year.ToString() + "-" + today.AddMonths(1).Month.ToString() + "-01");
- var day = tmpDt.AddDays(-1).Day;
- double total = 0, total_pre = 0;
- string[] colName = { "FacilityName", "FacilityCode", "YearName" };
- foreach (var item in colName) {
- facilityTime.Columns.Add(item);
- }
- facilityTime.Columns.Add("IsType", typeof(int)); //1:당월, 2:전월
- facilityTime.Columns.Add("Average", typeof(Double));
- for (var d = 1; d <= day; d++) {
- facilityTime.Columns.Add($"D{d}", typeof(Double));
- }
- foreach (var item in facilityTime_now) {
- if (currentCode != item.FacilityCode) {
- if (facilityTimeRow != null) {
- facilityTimeRow["Average"] = (total / day).ToString("0.##");
- facilityTimeRowPre["Average"] = (total_pre / day).ToString("0.##");
- facilityTime.Rows.Add(facilityTimeRowPre);
- facilityTime.Rows.Add(facilityTimeRow);
- facilityTimeRow = null; facilityTimeRowPre = null;
- total = 0; total_pre = 0;
- }
- facilityTimeRow = facilityTime.NewRow();
- facilityTimeRowPre = facilityTime.NewRow();
- facilityTimeRow["FacilityName"] = item.FacilityName + " (" + item.FloorName + ")";
- facilityTimeRow["FacilityCode"] = item.FacilityCode;
- facilityTimeRow["IsType"] = 1;
- facilityTimeRow["YearName"] = item.Year + "년";
- facilityTimeRowPre["FacilityName"] = item.FacilityName + " (" + item.FloorName + ")";
- facilityTimeRowPre["FacilityCode"] = item.FacilityCode;
- facilityTimeRowPre["IsType"] = 2;
- facilityTimeRowPre["YearName"] = (item.Year - 1) + "년";
- for (var d = 1; d <= day; d++) {
- facilityTimeRow[$"D{d}"] = 0;
- facilityTimeRowPre[$"D{d}"] = 0;
- }
- }
- facilityTimeRow[$"D{item.Day}"] = Math.Round(item.DailyValue);
- facilityTimeRowPre[$"D{item.Day}"] = Math.Round(item.LastDailyValue);
- total += Math.Round(item.DailyValue);
- total_pre += Math.Round(item.LastDailyValue);
- currentCode = item.FacilityCode;
- }
- return facilityTime;
- }
- /// <summary>
- /// 설비가동현황 그래프
- /// </summary>
- public DataTable GetFacilityTimeGraph(int siteId, DateTime today) {
- DataTable dt = new DataTable();
- var facilityTime_now = _context.BemsMonitoringPointHistoryDaily.Where(
- x => x.SiteId == Util.ToInt(siteId) &&
- x.PropertyId == 32 &&
- x.CreatedDateTime.Year == today.Year &&
- x.CreatedDateTime.Month == today.Month
- ).Select(x => new {
- FacilityName = x.CmFacility.Name,
- FloorName = x.CmFacility.CmFloor.Name,
- x.FacilityCode,
- Year = x.CreatedDateTime.Year,
- Day = x.CreatedDateTime.Day,
- x.DailyValue,
- LastDailyValue = _context.BemsMonitoringPointHistoryDaily.Where(f => f.SiteId == Util.ToInt(siteId) && f.PropertyId == 32 && f.FacilityCode == x.FacilityCode && f.CreatedDateTime.Year == today.AddYears(-1).Year && f.CreatedDateTime.Month == today.Month).Select(f => f.DailyValue).FirstOrDefault(),
- }).ToList();
- if (facilityTime_now.Count > 0) {
- var facilityTimeGraph = facilityTime_now.GroupBy(x => new { x.Year, x.Day }).Select(x => new {
- Day = x.Key.Day,
- Value = x.Sum(f => f.DailyValue),
- LastDay = x.Key.Day,
- LastValue = x.Sum(f => f.LastDailyValue)
- }).Select(x => new EnergyReportGrid {
- FuelTypeId = 0,
- PriceTypeId = 0,
- PriceTypeName = "설비가동시간",
- Date = x.Day,
- Value = x.Value,
- LastValue = x.LastValue
- }).ToList();
- if (facilityTimeGraph.Count > 0) dt = _GetEnergyGraph(facilityTimeGraph, today, 0);
- }
- return dt;
- }
- /// <summary>
- /// 에너지 사용량
- /// </summary>
- public DataTable GetEnergyData(int siteId, DateTime today, int type, bool isGraph) {
- DataTable dt = new DataTable();
- string[] arrPriceCode = null;
- string[] arrPriceCode1 = { "P003", "G001", "W007", "W008", "O002", "M010", "L002" }; // 사용량
- string[] arrPriceCode2 = { "P022", "P024", "P026", "P028", "P038", "G005", "G020", "W009", "W001", "W003", "W002", "M005", "M006", "O006", "O007", "L007", "L006" }; // 단가변동
- string[] arrPriceCode3 = { "P038", "G005", "W001", "W002", "O007", "M006", "L007" }; //요금분석
- if (type == 1) arrPriceCode = arrPriceCode1;
- else if (type == 2) arrPriceCode = arrPriceCode2;
- else arrPriceCode = arrPriceCode3;
- var query = _context.BemsSitePriceHistory.Where(
- x => x.SiteId == Util.ToInt(siteId) &&
- (x.StartDate.Year == today.Year || x.StartDate.Year == today.AddYears(-1).Year) &&
- x.StartDate.Month <= today.Month &&
- arrPriceCode.Contains(x.PriceCode) &&
- x.PriceTypeId == _context.BemsSitePrice.Where(f => f.SiteId == x.SiteId && f.FuelTypeId == x.FuelTypeId && f.PriceTypeId == x.PriceTypeId).Select(f => f.PriceTypeId).FirstOrDefault()
- ).OrderBy(x => x.PriceTypeId).Select(x => new EnergyReportGrid {
- Type = type,
- FuelTypeId = x.FuelTypeId,
- PriceTypeId = x.PriceTypeId,
- PriceTypeName = x.PriceType.PriceTypeIdDesc,
- PriceCode = x.PriceCode,
- PriceCodeName = x.PriceCodeNavigation.PriceCodeDesc,
- Year = today.Year,
- Date = x.StartDate.Month,
- Value = x.StartDate.Year == today.Year ? Util.ToDouble(x.PriceValue) : 0,
- LastValue = x.StartDate.Year == today.AddYears(-1).Year ? Util.ToDouble(x.PriceValue) : 0,
- }).ToList();
- var data = query.GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.PriceCode, x.Year, x.Date }).Select(x => new EnergyReportGrid {
- Type = type,
- FuelTypeId = x.Key.FuelTypeId,
- PriceTypeId = x.Key.PriceTypeId,
- PriceTypeName = x.Max(f => f.PriceTypeName),
- PriceCode = x.Key.PriceCode,
- PriceCodeName = x.Max(f => f.PriceCodeName),
- Year = x.Key.Year,
- Date = x.Key.Date,
- Value = x.Sum(f => f.Value),
- LastValue = x.Sum(f => f.LastValue),
- }).ToList();
- // 단가변동
- if (type == 2) {
- string[] arrCost1 = { "P022", "P024", "P026", "P028" };
- data = data.Where(x =>
- (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력일반용" && arrCost1.Contains(x.PriceCode)) ||
- (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력산업용" && (x.PriceCode == "P022" || x.PriceCode == "P038")) ||
- (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력심야용" && (x.PriceCode == "P022" || x.PriceCode == "P024" || x.PriceCode == "P026")) ||
- (x.FuelTypeId == 2 && (x.PriceCode == "G020" || x.PriceCode == "G005")) ||
- (x.FuelTypeId == 4 && (x.PriceCode == "O006" || x.PriceCode == "O007")) ||
- (x.FuelTypeId == 5 && (x.PriceCode == "L007" || x.PriceCode == "L006")) ||
- (x.FuelTypeId == 6 && (x.PriceCode == "M005" || x.PriceCode == "M006")))
- .Concat(
- data.Where(f =>
- (f.FuelTypeId == 3 && (f.PriceCode == "W009" || f.PriceCode == "W001")) ||
- (f.FuelTypeId == 3 && (f.PriceCode == "W003" || f.PriceCode == "W002")))
- .GroupBy(f => new { f.Type, f.FuelTypeId, f.PriceCode, f.Year, f.Date }).Select(f => new EnergyReportGrid {
- Type = f.Key.Type,
- FuelTypeId = f.Key.FuelTypeId,
- PriceTypeId = (f.Key.PriceCode == "W009" || f.Key.PriceCode == "W001") ? 0 : 1,
- PriceTypeName = (f.Key.PriceCode == "W009" || f.Key.PriceCode == "W001") ? "상수도" : "하수도",
- PriceCode = f.Key.PriceCode,
- PriceCodeName = f.Max(v => v.PriceCodeName),
- Year = f.Key.Year,
- Date = f.Key.Date,
- Value = f.Sum(v => v.Value),
- LastValue = f.Sum(v => v.LastValue),
- })
- ).ToList();
- }
- if (!isGraph) {
- dt = _GetEnergyGrid(siteId, today, type, data);
- } else {
- if (data != null) {
- var data2 = data.Where(x => x.Type == type && x.FuelTypeId != 3).GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.Date }).Select(x => new EnergyReportGrid {
- FuelTypeId = x.Key.FuelTypeId,
- PriceTypeId = x.Key.PriceTypeId,
- PriceTypeName = x.Max(f => f.PriceTypeName),
- PriceCode = "",
- PriceCodeName = "",
- Date = x.Key.Date,
- Value = x.Sum(f => f.Value),
- LastValue = x.Sum(f => f.LastValue)
- }).Concat(
- data.Where(f => f.Type == type && f.FuelTypeId == 3).GroupBy(f => new { f.FuelTypeId, f.PriceTypeId, f.PriceCode, f.Date }).Select(f => new EnergyReportGrid {
- FuelTypeId = f.Key.FuelTypeId,
- PriceTypeId = f.Key.PriceTypeId,
- PriceTypeName = f.Max(f => f.PriceTypeName),
- PriceCode = f.Key.PriceCode,
- PriceCodeName = f.Max(f => f.PriceCodeName),
- Date = f.Key.Date,
- Value = f.Sum(f => f.Value),
- LastValue = f.Sum(f => f.LastValue)
- })
- ).ToList();
- if (data2.Count > 0) dt = _GetEnergyGraph(data2, today, 1);
- }
- }
- return dt;
- }
- /// <summary>
- /// 에너지 사용량 그리드
- /// </summary>
- public DataTable _GetEnergyGrid(int siteId, DateTime today, int type, List<EnergyReportGrid> data) {
- DataTable dt = new DataTable();
- DataRow dr = null, drPre = null, drCmp = null, drCmpRate = null;
- int currentCode = -1, day = 12, fuelTypeId = 0;
- string currentCode2 = "";
- double total = 0, total_pre = 0;
- //Sort (1:전월, 2:당월, 3:증감, 4:증감%)
- string[] colName = { "Type", "FuelTypeId", "PriceTypeId", "YearName", "PriceTypeName", "PriceCode", "PriceCodeName", "IsTypeFirst", "Sort" };
- foreach (var item in colName) {
- dt.Columns.Add(item);
- }
- dt.Columns.Add("Total", typeof(Double));
- for (var d = 1; d <= day; d++) {
- dt.Columns.Add($"D{d}", typeof(Double));
- }
- var data2 = data.Where(x => x.Type == type).OrderBy(x => x.FuelTypeId).ThenBy(x => x.PriceTypeId).ThenBy(x => x.PriceCode).ToList();
- if (data2.Count > 0) {
- foreach (var item in data2) {
- if (currentCode != item.PriceTypeId || currentCode2 != item.PriceCode) {
- if (dr != null) {
- dr["Total"] = total;
- drPre["Total"] = total_pre;
- dt.Rows.Add(drPre);
- dt.Rows.Add(dr);
- //증감
- if (!(fuelTypeId == 1 && type == 1) && !(fuelTypeId == 2 && type == 1)) {
- drCmp = dt.NewRow();
- drCmp.ItemArray = dr.ItemArray;
- for (var d = 1; d <= day; d++) {
- drCmp[$"D{d}"] = (double)dr[$"D{d}"] - (double)dr[$"D{d}"];
- }
- drCmp["YearName"] = "증감";
- drCmp["Sort"] = 3;
- drCmp["Total"] = total - total_pre;
- dt.Rows.Add(drCmp);
- }
- drCmpRate = dt.NewRow();
- drCmpRate.ItemArray = dr.ItemArray;
- for (var d = 1; d <= day; d++) {
- drCmpRate[$"D{d}"] = (((double)dr[$"D{d}"] - (double)dr[$"D{d}"]) / 100).ToString("0.##");
- }
- drCmpRate["YearName"] = "증감%";
- drCmpRate["Sort"] = 4;
- drCmpRate["Total"] = ((total - total_pre) / 100).ToString("0.##");
- dt.Rows.Add(drCmpRate);
- dr = null; drPre = null; drCmp = null; drCmpRate = null;
- total = 0; total_pre = 0;
- }
- dr = dt.NewRow();
- drPre = dt.NewRow();
- int lenName = item.PriceTypeName.Length >= 8 ? 8 : item.PriceTypeName.Length;
- dr["Type"] = type;
- dr["FuelTypeId"] = item.FuelTypeId;
- dr["PriceTypeId"] = item.PriceTypeId;
- dr["PriceTypeName"] = item.PriceTypeName.Substring(0, lenName);
- dr["PriceCode"] = item.PriceCode;
- dr["PriceCodeName"] = item.PriceCodeName;
- dr["IsTypeFirst"] = 0;
- for (var d = 1; d <= day; d++) {
- dr[$"D{d}"] = 0;
- }
- drPre.ItemArray = dr.ItemArray;
- dr["YearName"] = item.Year + "년";
- dr["Sort"] = 2;
- drPre["YearName"] = (item.Year - 1) + "년";
- drPre["Sort"] = (item.FuelTypeId == 3 && currentCode == item.PriceTypeId && currentCode2 != item.PriceCode) ? 0 : 1; //수도 - 상/하수도 구분 때문..
- drPre["IsTypeFirst"] = (currentCode != item.PriceTypeId) ? 1 : 0;
- }
- dr[$"D{item.Date}"] = Math.Round(item.Value);
- drPre[$"D{item.Date}"] = Math.Round(item.LastValue);
- total += Math.Round(item.Value);
- total_pre += Math.Round(item.LastValue);
- currentCode = item.PriceTypeId;
- currentCode2 = item.PriceCode;
- fuelTypeId = item.FuelTypeId;
- }
- //마지막 데이터 안 들어감
- dr["Total"] = total;
- drPre["Total"] = total_pre;
- dt.Rows.Add(drPre);
- dt.Rows.Add(dr);
- //증감
- if (!(fuelTypeId == 1 && type == 1) && !(fuelTypeId == 2 && type == 1)) {
- drCmp = dt.NewRow();
- drCmp.ItemArray = dr.ItemArray;
- for (var d = 1; d <= day; d++) {
- drCmp[$"D{d}"] = (double)dr[$"D{d}"] - (double)dr[$"D{d}"];
- }
- drCmp["YearName"] = "증감";
- drCmp["Sort"] = 3;
- drCmp["Total"] = total - total_pre;
- dt.Rows.Add(drCmp);
- }
- drCmpRate = dt.NewRow();
- drCmpRate.ItemArray = dr.ItemArray;
- for (var d = 1; d <= day; d++) {
- drCmpRate[$"D{d}"] = (((double)dr[$"D{d}"] - (double)dr[$"D{d}"]) / 100).ToString("0.##");
- }
- drCmpRate["YearName"] = "증감%";
- drCmpRate["Sort"] = 4;
- drCmpRate["Total"] = ((total - total_pre) / 100).ToString("0.##");
- dt.Rows.Add(drCmpRate);
- }
- return dt;
- }
- /// <summary>
- /// 에너지 사용량 그래프
- /// </summary>
- public DataTable _GetEnergyGraph(List<EnergyReportGrid> data, DateTime today, int type) {
- DataTable dt = new DataTable();
- DataRow row = null;
- string[] colName = { "FuelTypeId", "Title", "TitleId", "PriceCode", "TabType", "LineName", "LineLastName", "Day", "Value", "LastValue", "MaxValue", "MinValue" };
- foreach (var item in colName) {
- dt.Columns.Add(item);
- }
- var day = 12;
- if (type == 0) {
- //설비가동현황
- DateTime tmpDt = DateTime.Parse(today.Year.ToString() + "-" + today.AddMonths(1).Month.ToString() + "-01");
- day = tmpDt.AddDays(-1).Day;
- }
- var data2 = data.GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.PriceCode }).Select(x => new {
- FuelTypeId = x.Key.FuelTypeId,
- PriceTypeId = x.Key.PriceTypeId,
- PriceTypeName = x.Max(f => f.PriceTypeName),
- PriceCode = x.Key.PriceCode,
- PriceCodeName = x.Max(f => f.PriceCodeName),
- }).ToList();
- foreach (var item in data2) {
- if (item.FuelTypeId == 3) {
- //수도 - 상수도, 하수도로 구분
- var maxValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Max(x => x.Value);
- var maxLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Max(x => x.LastValue);
- var minValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Min(x => x.Value);
- var minLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Min(x => x.LastValue);
- for (var i = 1; i <= day; i++) {
- row = null;
- var tmpA = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode && x.Date == i).FirstOrDefault();
- row = dt.NewRow();
- row["FuelTypeId"] = item.FuelTypeId;
- row["Title"] = item.PriceCodeName.Replace("사용량", "");
- row["TitleId"] = item.PriceTypeId;
- row["PriceCode"] = item.PriceCode;
- row["TabType"] = type;
- row["LineName"] = today.Year.ToString() + "년";
- row["LineLastName"] = today.AddYears(-1).Year.ToString() + "년";
- row["Day"] = i;
- row["Value"] = tmpA != null ? Math.Round(tmpA.Value) : 0;
- row["LastValue"] = tmpA != null ? Math.Round(tmpA.LastValue) : 0;
- row["MaxValue"] = maxValue > maxLastValue ? maxValue : maxLastValue;
- row["MinValue"] = minValue > minLastValue ? minValue : minLastValue;
- dt.Rows.Add(row);
- }
- } else {
- var maxValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Max(x => x.Value);
- var maxLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Max(x => x.LastValue);
- var minValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Min(x => x.Value);
- var minLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Min(x => x.LastValue);
- for (var i = 1; i <= day; i++) {
- row = null;
- var tmpA = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.Date == i).FirstOrDefault();
- row = dt.NewRow();
- row["FuelTypeId"] = item.FuelTypeId;
- row["Title"] = item.PriceTypeName;
- row["TitleId"] = item.PriceTypeId;
- row["TabType"] = type;
- row["LineName"] = today.Year.ToString() + "년";
- row["LineLastName"] = today.AddYears(-1).Year.ToString() + "년";
- row["Day"] = i;
- row["Value"] = tmpA != null ? Math.Round(tmpA.Value) : 0;
- row["LastValue"] = tmpA != null ? Math.Round(tmpA.LastValue) : 0;
- row["MaxValue"] = maxValue > maxLastValue ? maxValue : maxLastValue;
- row["MinValue"] = minValue > minLastValue ? minValue : minLastValue;
- dt.Rows.Add(row);
- }
- }
- }
- return dt;
- }
- }
- }
|