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 _logger; private readonly FMSContext _context; private readonly AppSettings _appSettings; private readonly StorageHelper _storage; public MonthlyReportService( ILogger logger, FMSContext context, IOptions appSettings, StorageHelper storage ) { _logger = logger; _context = context; _appSettings = appSettings.Value; _storage = storage; } public IQueryable 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("이미 다른 화면에서 사용중이므로 삭제할 수 없습니다."); } } /// /// 법정검사 /// 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; } /// /// 설비가동시간 /// 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; } /// /// 설비가동현황 그래프 /// 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; } /// /// 에너지 사용량 /// 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; } /// /// 에너지 사용량 그리드 /// public DataTable _GetEnergyGrid(int siteId, DateTime today, int type, List 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; } /// /// 에너지 사용량 그래프 /// public DataTable _GetEnergyGraph(List 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; } } }