MonthlyReportService.cs 38 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729
  1. using System;
  2. using System.Collections.Generic;
  3. using System.IdentityModel.Tokens.Jwt;
  4. using System.Linq;
  5. using System.Security.Claims;
  6. using System.Text;
  7. using FMSAdmin.Data;
  8. using FMSAdmin.Helpers;
  9. using FMSAdmin.Entities;
  10. using Microsoft.EntityFrameworkCore;
  11. using Microsoft.Extensions.Logging;
  12. using Microsoft.Extensions.Options;
  13. using Microsoft.IdentityModel.Tokens;
  14. using FMSAdmin.Models;
  15. using SelectPdf;
  16. using System.IO;
  17. using System.Data;
  18. using FMSAdmin.Models.Formula;
  19. namespace FMSApp.Services {
  20. public class MonthlyReportService {
  21. private readonly ILogger<MonthlyReportService> _logger;
  22. private readonly FMSContext _context;
  23. private readonly AppSettings _appSettings;
  24. private readonly StorageHelper _storage;
  25. public MonthlyReportService(
  26. ILogger<MonthlyReportService> logger,
  27. FMSContext context,
  28. IOptions<AppSettings> appSettings,
  29. StorageHelper storage
  30. ) {
  31. _logger = logger;
  32. _context = context;
  33. _appSettings = appSettings.Value;
  34. _storage = storage;
  35. }
  36. public IQueryable<FmsMonthlyReport> GetAll() {
  37. var query = _context.FmsMonthlyReport;
  38. return query;
  39. }
  40. public void Approve(int siteId, string approverUserId, DateTime addDate, int businessFieldId) {
  41. var persist = _context.FmsMonthlyReport.FirstOrDefault(
  42. x => x.SiteId == siteId
  43. && x.AddDate == addDate.Date
  44. // && x.BusinessFieldId == businessFieldId 20200625 요청
  45. );
  46. if (persist == null) throw new ServiceException("해당일자의 일간보고서가 존재하지 않습니다.");
  47. if (string.IsNullOrEmpty(persist.ApproverUserId)) {
  48. persist.ApproverUserId = approverUserId;
  49. persist.ApproveDate = DateTime.Now;
  50. } else if (string.IsNullOrEmpty(persist.ApproverUserId2)) {
  51. persist.ApproverUserId2 = approverUserId;
  52. persist.ApproveDate2 = DateTime.Now;
  53. } else if (string.IsNullOrEmpty(persist.ApproverUserId3)) {
  54. persist.ApproverUserId3 = approverUserId;
  55. persist.ApproveDate3 = DateTime.Now;
  56. } else if (string.IsNullOrEmpty(persist.ApproverUserId4)) {
  57. persist.ApproverUserId4 = approverUserId;
  58. persist.ApproveDate4 = DateTime.Now;
  59. } else if (string.IsNullOrEmpty(persist.ApproverUserId5)) {
  60. persist.ApproverUserId5 = approverUserId;
  61. persist.ApproveDate5 = DateTime.Now;
  62. } else {
  63. throw new ServiceException("결재 한도를 초과하였습니다.");
  64. }
  65. _context.FmsMonthlyReport.Update(persist);
  66. _context.SaveChanges();
  67. }
  68. public void Delete(int siteId, DateTime addDate, int businessFieldId) {
  69. var persist = _context.FmsMonthlyReport.FirstOrDefault(
  70. x => x.SiteId == siteId
  71. && x.AddDate.Year == addDate.Year
  72. && x.AddDate.Month == addDate.Month
  73. && x.BusinessFieldId == businessFieldId
  74. );
  75. if (persist == null) throw new ServiceException("해당일자의 일간보고서가 존재하지 않습니다.");
  76. _context.FmsMonthlyReport.Remove(persist);
  77. _context.SaveChanges();
  78. }
  79. public FmsMonthlyReport checkFile(FmsMonthlyReport data) {
  80. if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
  81. data.ParkingManagement.SiteId = data.SiteId;
  82. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  83. data.ParkingManagement.FileCategory = category;
  84. data.ParkingManagement.CreatedDate = DateTime.Now;
  85. } else {
  86. data.ParkingManagement = null;
  87. }
  88. if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
  89. data.RentalManagement.SiteId = data.SiteId;
  90. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  91. data.RentalManagement.FileCategory = category;
  92. data.RentalManagement.CreatedDate = DateTime.Now;
  93. } else {
  94. data.RentalManagement = null;
  95. }
  96. if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
  97. data.MonthlyPriorityWork.SiteId = data.SiteId;
  98. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  99. data.MonthlyPriorityWork.FileCategory = category;
  100. data.MonthlyPriorityWork.CreatedDate = DateTime.Now;
  101. } else {
  102. data.MonthlyPriorityWork = null;
  103. }
  104. if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
  105. data.ImprovementReport.SiteId = data.SiteId;
  106. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  107. data.ImprovementReport.FileCategory = category;
  108. data.ImprovementReport.CreatedDate = DateTime.Now;
  109. } else {
  110. data.ImprovementReport = null;
  111. }
  112. if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
  113. data.NextMonthPriorityWork.SiteId = data.SiteId;
  114. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  115. data.NextMonthPriorityWork.FileCategory = category;
  116. data.NextMonthPriorityWork.CreatedDate = DateTime.Now;
  117. } else {
  118. data.NextMonthPriorityWork = null;
  119. }
  120. if (data.Cost != null && data.Cost.IsUpload) {
  121. data.Cost.SiteId = data.SiteId;
  122. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  123. data.Cost.FileCategory = category;
  124. data.Cost.CreatedDate = DateTime.Now;
  125. } else {
  126. data.Cost = null;
  127. }
  128. if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
  129. data.SurroundingStatus.SiteId = data.SiteId;
  130. var category = _context.CmFileCategory.First(x => x.Name == "mothlyReport");
  131. data.SurroundingStatus.FileCategory = category;
  132. data.SurroundingStatus.CreatedDate = DateTime.Now;
  133. } else {
  134. data.SurroundingStatus = null;
  135. }
  136. return data;
  137. }
  138. public void copyFile(FmsMonthlyReport data, FmsMonthlyReport persist) {
  139. if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
  140. _storage.CopyEntity(data.ParkingManagement.Path, persist.ParkingManagement);
  141. _storage.PdfConvertImage(data.ParkingManagement);
  142. }
  143. if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
  144. _storage.CopyEntity(data.RentalManagement.Path, persist.RentalManagement);
  145. _storage.PdfConvertImage(data.RentalManagement);
  146. }
  147. if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
  148. _storage.CopyEntity(data.MonthlyPriorityWork.Path, persist.MonthlyPriorityWork);
  149. _storage.PdfConvertImage(data.MonthlyPriorityWork);
  150. }
  151. if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
  152. _storage.CopyEntity(data.ImprovementReport.Path, persist.ImprovementReport);
  153. _storage.PdfConvertImage(data.ImprovementReport);
  154. }
  155. if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
  156. _storage.CopyEntity(data.NextMonthPriorityWork.Path, persist.NextMonthPriorityWork);
  157. _storage.PdfConvertImage(data.NextMonthPriorityWork);
  158. }
  159. if (data.Cost != null && data.Cost.IsUpload) {
  160. _storage.CopyEntity(data.Cost.Path, persist.Cost);
  161. _storage.PdfConvertImage(data.Cost);
  162. }
  163. if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
  164. _storage.CopyEntity(data.SurroundingStatus.Path, persist.SurroundingStatus);
  165. _storage.PdfConvertImage(data.SurroundingStatus);
  166. }
  167. }
  168. public void Save(FmsMonthlyReport data, bool isCreate) {
  169. var persist = _context.FmsMonthlyReport.FirstOrDefault(
  170. x => x.SiteId == data.SiteId
  171. && x.AddDate.Year == data.AddDate.Year
  172. && x.AddDate.Month == data.AddDate.Month);
  173. data = checkFile(data);
  174. if (persist == null) {
  175. if (data.OrganizationChartStatusFileId != null) {
  176. data.OrganizationChartStatus = _context.CmFile.Where(x => x.SiteId == data.SiteId && x.FileId == data.OrganizationChartStatusFileId.Value).FirstOrDefault();
  177. }
  178. if (data.RentalStatusFileId != null) {
  179. data.RentalStatus = _context.CmFile.Where(x => x.SiteId == data.SiteId && x.FileId == data.RentalStatusFileId.Value).FirstOrDefault();
  180. }
  181. data.RegisterDate = DateTime.Now;
  182. _context.FmsMonthlyReport.Add(data);
  183. _context.SaveChanges();
  184. } else {
  185. if (isCreate) throw new ServiceException("해당일자의 보고서가 이미 등록되어있습니다.");
  186. persist.Name = data.Name;
  187. persist.Contents = data.Contents;
  188. persist.SiteInfoDatas = data.SiteInfoDatas;
  189. persist.FacilityStandards = data.FacilityStandards;
  190. persist.RentalStandards = data.RentalStandards;
  191. persist.HumanCountStatus = data.HumanCountStatus;
  192. persist.ManagementOfficeContact = data.ManagementOfficeContact;
  193. persist.RelatedInstitutionsContact = data.RelatedInstitutionsContact;
  194. persist.UserChangeStatus = data.UserChangeStatus;
  195. persist.LayoutStatus = data.LayoutStatus;
  196. persist.TenantContact = data.TenantContact;
  197. persist.FacilityTime = data.FacilityTime;
  198. persist.FacilityTimeGraph = data.FacilityTimeGraph;
  199. persist.EnergyUsage = data.EnergyUsage;
  200. persist.EnergyUsageGraph = data.EnergyUsageGraph;
  201. persist.EnergyCost = data.EnergyCost;
  202. persist.EnergyCharge = data.EnergyCharge;
  203. persist.EnergyChargeGraph = data.EnergyChargeGraph;
  204. persist.EnergyAnalysis = data.EnergyAnalysis;
  205. if (data.ParkingManagement != null && data.ParkingManagement.IsUpload) {
  206. persist.ParkingManagement = data.ParkingManagement;
  207. }
  208. if (data.RentalManagement != null && data.RentalManagement.IsUpload) {
  209. persist.RentalManagement = data.RentalManagement;
  210. }
  211. if (data.MonthlyPriorityWork != null && data.MonthlyPriorityWork.IsUpload) {
  212. persist.MonthlyPriorityWork = data.MonthlyPriorityWork;
  213. }
  214. if (data.ImprovementReport != null && data.ImprovementReport.IsUpload) {
  215. persist.ImprovementReport = data.ImprovementReport;
  216. }
  217. if (data.NextMonthPriorityWork != null && data.NextMonthPriorityWork.IsUpload) {
  218. persist.NextMonthPriorityWork = data.NextMonthPriorityWork;
  219. }
  220. if (data.Cost != null && data.Cost.IsUpload) {
  221. persist.Cost = data.Cost;
  222. }
  223. if (data.SurroundingStatus != null && data.SurroundingStatus.IsUpload) {
  224. persist.SurroundingStatus = data.SurroundingStatus;
  225. }
  226. _context.FmsMonthlyReport.Update(persist);
  227. _context.SaveChanges();
  228. }
  229. copyFile(data, data);
  230. }
  231. public void Delete(int siteId, DateTime addData) {
  232. try {
  233. var data = _context.FmsMonthlyReport.First(x => x.SiteId == siteId && x.AddDate == addData);
  234. _context.FmsMonthlyReport.Remove(data);
  235. //data.IsUse = false;
  236. _context.SaveChanges();
  237. } catch {
  238. throw new ServiceException("이미 다른 화면에서 사용중이므로 삭제할 수 없습니다.");
  239. }
  240. }
  241. /// <summary>
  242. /// 법정검사
  243. /// </summary>
  244. public DataTable GetLegalExamination(int siteId, DateTime addDate) {
  245. var legalExaminationList = _context.FmsWorkRequest.Where(x =>
  246. x.SiteId == Util.ToInt(siteId) &&
  247. x.WorkTypeId == 2 &&
  248. x.StartWorkDate.Year == addDate.Year
  249. ).OrderBy(x => x.WorkScheduleId).Select(x => new {
  250. x.WorkScheduleId,
  251. Type = x.CmBusinessField.Name,
  252. x.Title,
  253. WorkProgress = x.WorkProgressId == 5 ? "작업완료" : x.WorkProgress.Name,
  254. Cycle = x.FmsWorkSchedule.CycleSize + x.FmsWorkSchedule.FmsWorkCodeCycleUnit.Name.Replace("간", ""),
  255. Month = x.StartWorkDate.Month,
  256. Day = x.StartWorkDate.Day
  257. }).ToList();
  258. DataTable LegalExamination = new DataTable();
  259. DataRow legalExaminationRow = null;
  260. LegalExamination.Columns.Add("Type");
  261. LegalExamination.Columns.Add("Title");
  262. for (var m = 1; m <= 12; m++) {
  263. LegalExamination.Columns.Add($"M{m}");
  264. }
  265. LegalExamination.Columns.Add("Progress");
  266. LegalExamination.Columns.Add("Comment");
  267. var WorkScheduleId = -1;
  268. foreach (var item in legalExaminationList.OrderBy(x => x.WorkScheduleId)) {
  269. if (WorkScheduleId != item.WorkScheduleId) {
  270. if (legalExaminationRow != null) {
  271. LegalExamination.Rows.Add(legalExaminationRow);
  272. }
  273. legalExaminationRow = LegalExamination.NewRow();
  274. legalExaminationRow["Type"] = item.Type;
  275. legalExaminationRow["Title"] = item.Title;
  276. legalExaminationRow["Progress"] = item.WorkProgress;
  277. legalExaminationRow["Comment"] = item.Cycle;
  278. }
  279. for (var m = 1; m <= 12; m++) {
  280. if (item.Month == m) {
  281. legalExaminationRow[$"M{m}"] = item.Day;
  282. } else {
  283. legalExaminationRow[$"M{m}"] = "";
  284. }
  285. }
  286. WorkScheduleId = item.WorkScheduleId == null ? -1 : item.WorkScheduleId.Value;
  287. }
  288. if (legalExaminationRow != null)
  289. LegalExamination.Rows.Add(legalExaminationRow);
  290. return LegalExamination;
  291. }
  292. /// <summary>
  293. /// 설비가동시간
  294. /// </summary>
  295. public DataTable GetFacilityTime(int siteId, DateTime today) {
  296. DataTable facilityTime = new DataTable();
  297. DataRow facilityTimeRow = null;
  298. DataRow facilityTimeRowPre = null;
  299. var facilityTime_now = _context.BemsMonitoringPointHistoryDaily.Where(
  300. x => x.SiteId == Util.ToInt(siteId) &&
  301. x.PropertyId == 32 &&
  302. x.CreatedDateTime.Year == today.Year &&
  303. x.CreatedDateTime.Month == today.Month
  304. ).OrderBy(x => x.FacilityCode).ThenBy(x => x.CreatedDateTime).Select(x => new {
  305. FacilityName = x.CmFacility.Name,
  306. FloorName = x.CmFacility.CmFloor.Name,
  307. x.FacilityCode,
  308. Year = x.CreatedDateTime.Year,
  309. Day = x.CreatedDateTime.Day,
  310. x.DailyValue,
  311. 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(),
  312. }).ToList();
  313. if (facilityTime_now == null) return facilityTime;
  314. int currentCode = -1;
  315. DateTime tmpDt = DateTime.Parse(today.Year.ToString() + "-" + today.AddMonths(1).Month.ToString() + "-01");
  316. var day = tmpDt.AddDays(-1).Day;
  317. double total = 0, total_pre = 0;
  318. string[] colName = { "FacilityName", "FacilityCode", "YearName" };
  319. foreach (var item in colName) {
  320. facilityTime.Columns.Add(item);
  321. }
  322. facilityTime.Columns.Add("IsType", typeof(int)); //1:당월, 2:전월
  323. facilityTime.Columns.Add("Average", typeof(Double));
  324. for (var d = 1; d <= day; d++) {
  325. facilityTime.Columns.Add($"D{d}", typeof(Double));
  326. }
  327. foreach (var item in facilityTime_now) {
  328. if (currentCode != item.FacilityCode) {
  329. if (facilityTimeRow != null) {
  330. facilityTimeRow["Average"] = (total / day).ToString("0.##");
  331. facilityTimeRowPre["Average"] = (total_pre / day).ToString("0.##");
  332. facilityTime.Rows.Add(facilityTimeRowPre);
  333. facilityTime.Rows.Add(facilityTimeRow);
  334. facilityTimeRow = null; facilityTimeRowPre = null;
  335. total = 0; total_pre = 0;
  336. }
  337. facilityTimeRow = facilityTime.NewRow();
  338. facilityTimeRowPre = facilityTime.NewRow();
  339. facilityTimeRow["FacilityName"] = item.FacilityName + " (" + item.FloorName + ")";
  340. facilityTimeRow["FacilityCode"] = item.FacilityCode;
  341. facilityTimeRow["IsType"] = 1;
  342. facilityTimeRow["YearName"] = item.Year + "년";
  343. facilityTimeRowPre["FacilityName"] = item.FacilityName + " (" + item.FloorName + ")";
  344. facilityTimeRowPre["FacilityCode"] = item.FacilityCode;
  345. facilityTimeRowPre["IsType"] = 2;
  346. facilityTimeRowPre["YearName"] = (item.Year - 1) + "년";
  347. for (var d = 1; d <= day; d++) {
  348. facilityTimeRow[$"D{d}"] = 0;
  349. facilityTimeRowPre[$"D{d}"] = 0;
  350. }
  351. }
  352. facilityTimeRow[$"D{item.Day}"] = Math.Round(item.DailyValue);
  353. facilityTimeRowPre[$"D{item.Day}"] = Math.Round(item.LastDailyValue);
  354. total += Math.Round(item.DailyValue);
  355. total_pre += Math.Round(item.LastDailyValue);
  356. currentCode = item.FacilityCode;
  357. }
  358. return facilityTime;
  359. }
  360. /// <summary>
  361. /// 설비가동현황 그래프
  362. /// </summary>
  363. public DataTable GetFacilityTimeGraph(int siteId, DateTime today) {
  364. DataTable dt = new DataTable();
  365. var facilityTime_now = _context.BemsMonitoringPointHistoryDaily.Where(
  366. x => x.SiteId == Util.ToInt(siteId) &&
  367. x.PropertyId == 32 &&
  368. x.CreatedDateTime.Year == today.Year &&
  369. x.CreatedDateTime.Month == today.Month
  370. ).Select(x => new {
  371. FacilityName = x.CmFacility.Name,
  372. FloorName = x.CmFacility.CmFloor.Name,
  373. x.FacilityCode,
  374. Year = x.CreatedDateTime.Year,
  375. Day = x.CreatedDateTime.Day,
  376. x.DailyValue,
  377. 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(),
  378. }).ToList();
  379. if (facilityTime_now.Count > 0) {
  380. var facilityTimeGraph = facilityTime_now.GroupBy(x => new { x.Year, x.Day }).Select(x => new {
  381. Day = x.Key.Day,
  382. Value = x.Sum(f => f.DailyValue),
  383. LastDay = x.Key.Day,
  384. LastValue = x.Sum(f => f.LastDailyValue)
  385. }).Select(x => new EnergyReportGrid {
  386. FuelTypeId = 0,
  387. PriceTypeId = 0,
  388. PriceTypeName = "설비가동시간",
  389. Date = x.Day,
  390. Value = x.Value,
  391. LastValue = x.LastValue
  392. }).ToList();
  393. if (facilityTimeGraph.Count > 0) dt = _GetEnergyGraph(facilityTimeGraph, today, 0);
  394. }
  395. return dt;
  396. }
  397. /// <summary>
  398. /// 에너지 사용량
  399. /// </summary>
  400. public DataTable GetEnergyData(int siteId, DateTime today, int type, bool isGraph) {
  401. DataTable dt = new DataTable();
  402. string[] arrPriceCode = null;
  403. string[] arrPriceCode1 = { "P003", "G001", "W007", "W008", "O002", "M010", "L002" }; // 사용량
  404. string[] arrPriceCode2 = { "P022", "P024", "P026", "P028", "P038", "G005", "G020", "W009", "W001", "W003", "W002", "M005", "M006", "O006", "O007", "L007", "L006" }; // 단가변동
  405. string[] arrPriceCode3 = { "P038", "G005", "W001", "W002", "O007", "M006", "L007" }; //요금분석
  406. if (type == 1) arrPriceCode = arrPriceCode1;
  407. else if (type == 2) arrPriceCode = arrPriceCode2;
  408. else arrPriceCode = arrPriceCode3;
  409. var query = _context.BemsSitePriceHistory.Where(
  410. x => x.SiteId == Util.ToInt(siteId) &&
  411. (x.StartDate.Year == today.Year || x.StartDate.Year == today.AddYears(-1).Year) &&
  412. x.StartDate.Month <= today.Month &&
  413. arrPriceCode.Contains(x.PriceCode) &&
  414. x.PriceTypeId == _context.BemsSitePrice.Where(f => f.SiteId == x.SiteId && f.FuelTypeId == x.FuelTypeId && f.PriceTypeId == x.PriceTypeId).Select(f => f.PriceTypeId).FirstOrDefault()
  415. ).OrderBy(x => x.PriceTypeId).Select(x => new EnergyReportGrid {
  416. Type = type,
  417. FuelTypeId = x.FuelTypeId,
  418. PriceTypeId = x.PriceTypeId,
  419. PriceTypeName = x.PriceType.PriceTypeIdDesc,
  420. PriceCode = x.PriceCode,
  421. PriceCodeName = x.PriceCodeNavigation.PriceCodeDesc,
  422. Year = today.Year,
  423. Date = x.StartDate.Month,
  424. Value = x.StartDate.Year == today.Year ? Util.ToDouble(x.PriceValue) : 0,
  425. LastValue = x.StartDate.Year == today.AddYears(-1).Year ? Util.ToDouble(x.PriceValue) : 0,
  426. }).ToList();
  427. var data = query.GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.PriceCode, x.Year, x.Date }).Select(x => new EnergyReportGrid {
  428. Type = type,
  429. FuelTypeId = x.Key.FuelTypeId,
  430. PriceTypeId = x.Key.PriceTypeId,
  431. PriceTypeName = x.Max(f => f.PriceTypeName),
  432. PriceCode = x.Key.PriceCode,
  433. PriceCodeName = x.Max(f => f.PriceCodeName),
  434. Year = x.Key.Year,
  435. Date = x.Key.Date,
  436. Value = x.Sum(f => f.Value),
  437. LastValue = x.Sum(f => f.LastValue),
  438. }).ToList();
  439. // 단가변동
  440. if (type == 2) {
  441. string[] arrCost1 = { "P022", "P024", "P026", "P028" };
  442. data = data.Where(x =>
  443. (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력일반용" && arrCost1.Contains(x.PriceCode)) ||
  444. (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력산업용" && (x.PriceCode == "P022" || x.PriceCode == "P038")) ||
  445. (x.FuelTypeId == 1 && x.PriceTypeName.Substring(0, 5) == "전력심야용" && (x.PriceCode == "P022" || x.PriceCode == "P024" || x.PriceCode == "P026")) ||
  446. (x.FuelTypeId == 2 && (x.PriceCode == "G020" || x.PriceCode == "G005")) ||
  447. (x.FuelTypeId == 4 && (x.PriceCode == "O006" || x.PriceCode == "O007")) ||
  448. (x.FuelTypeId == 5 && (x.PriceCode == "L007" || x.PriceCode == "L006")) ||
  449. (x.FuelTypeId == 6 && (x.PriceCode == "M005" || x.PriceCode == "M006")))
  450. .Concat(
  451. data.Where(f =>
  452. (f.FuelTypeId == 3 && (f.PriceCode == "W009" || f.PriceCode == "W001")) ||
  453. (f.FuelTypeId == 3 && (f.PriceCode == "W003" || f.PriceCode == "W002")))
  454. .GroupBy(f => new { f.Type, f.FuelTypeId, f.PriceCode, f.Year, f.Date }).Select(f => new EnergyReportGrid {
  455. Type = f.Key.Type,
  456. FuelTypeId = f.Key.FuelTypeId,
  457. PriceTypeId = (f.Key.PriceCode == "W009" || f.Key.PriceCode == "W001") ? 0 : 1,
  458. PriceTypeName = (f.Key.PriceCode == "W009" || f.Key.PriceCode == "W001") ? "상수도" : "하수도",
  459. PriceCode = f.Key.PriceCode,
  460. PriceCodeName = f.Max(v => v.PriceCodeName),
  461. Year = f.Key.Year,
  462. Date = f.Key.Date,
  463. Value = f.Sum(v => v.Value),
  464. LastValue = f.Sum(v => v.LastValue),
  465. })
  466. ).ToList();
  467. }
  468. if (!isGraph) {
  469. dt = _GetEnergyGrid(siteId, today, type, data);
  470. } else {
  471. if (data != null) {
  472. var data2 = data.Where(x => x.Type == type && x.FuelTypeId != 3).GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.Date }).Select(x => new EnergyReportGrid {
  473. FuelTypeId = x.Key.FuelTypeId,
  474. PriceTypeId = x.Key.PriceTypeId,
  475. PriceTypeName = x.Max(f => f.PriceTypeName),
  476. PriceCode = "",
  477. PriceCodeName = "",
  478. Date = x.Key.Date,
  479. Value = x.Sum(f => f.Value),
  480. LastValue = x.Sum(f => f.LastValue)
  481. }).Concat(
  482. data.Where(f => f.Type == type && f.FuelTypeId == 3).GroupBy(f => new { f.FuelTypeId, f.PriceTypeId, f.PriceCode, f.Date }).Select(f => new EnergyReportGrid {
  483. FuelTypeId = f.Key.FuelTypeId,
  484. PriceTypeId = f.Key.PriceTypeId,
  485. PriceTypeName = f.Max(f => f.PriceTypeName),
  486. PriceCode = f.Key.PriceCode,
  487. PriceCodeName = f.Max(f => f.PriceCodeName),
  488. Date = f.Key.Date,
  489. Value = f.Sum(f => f.Value),
  490. LastValue = f.Sum(f => f.LastValue)
  491. })
  492. ).ToList();
  493. if (data2.Count > 0) dt = _GetEnergyGraph(data2, today, 1);
  494. }
  495. }
  496. return dt;
  497. }
  498. /// <summary>
  499. /// 에너지 사용량 그리드
  500. /// </summary>
  501. public DataTable _GetEnergyGrid(int siteId, DateTime today, int type, List<EnergyReportGrid> data) {
  502. DataTable dt = new DataTable();
  503. DataRow dr = null, drPre = null, drCmp = null, drCmpRate = null;
  504. int currentCode = -1, day = 12, fuelTypeId = 0;
  505. string currentCode2 = "";
  506. double total = 0, total_pre = 0;
  507. //Sort (1:전월, 2:당월, 3:증감, 4:증감%)
  508. string[] colName = { "Type", "FuelTypeId", "PriceTypeId", "YearName", "PriceTypeName", "PriceCode", "PriceCodeName", "IsTypeFirst", "Sort" };
  509. foreach (var item in colName) {
  510. dt.Columns.Add(item);
  511. }
  512. dt.Columns.Add("Total", typeof(Double));
  513. for (var d = 1; d <= day; d++) {
  514. dt.Columns.Add($"D{d}", typeof(Double));
  515. }
  516. var data2 = data.Where(x => x.Type == type).OrderBy(x => x.FuelTypeId).ThenBy(x => x.PriceTypeId).ThenBy(x => x.PriceCode).ToList();
  517. if (data2.Count > 0) {
  518. foreach (var item in data2) {
  519. if (currentCode != item.PriceTypeId || currentCode2 != item.PriceCode) {
  520. if (dr != null) {
  521. dr["Total"] = total;
  522. drPre["Total"] = total_pre;
  523. dt.Rows.Add(drPre);
  524. dt.Rows.Add(dr);
  525. //증감
  526. if (!(fuelTypeId == 1 && type == 1) && !(fuelTypeId == 2 && type == 1)) {
  527. drCmp = dt.NewRow();
  528. drCmp.ItemArray = dr.ItemArray;
  529. for (var d = 1; d <= day; d++) {
  530. drCmp[$"D{d}"] = (double)dr[$"D{d}"] - (double)dr[$"D{d}"];
  531. }
  532. drCmp["YearName"] = "증감";
  533. drCmp["Sort"] = 3;
  534. drCmp["Total"] = total - total_pre;
  535. dt.Rows.Add(drCmp);
  536. }
  537. drCmpRate = dt.NewRow();
  538. drCmpRate.ItemArray = dr.ItemArray;
  539. for (var d = 1; d <= day; d++) {
  540. drCmpRate[$"D{d}"] = (((double)dr[$"D{d}"] - (double)dr[$"D{d}"]) / 100).ToString("0.##");
  541. }
  542. drCmpRate["YearName"] = "증감%";
  543. drCmpRate["Sort"] = 4;
  544. drCmpRate["Total"] = ((total - total_pre) / 100).ToString("0.##");
  545. dt.Rows.Add(drCmpRate);
  546. dr = null; drPre = null; drCmp = null; drCmpRate = null;
  547. total = 0; total_pre = 0;
  548. }
  549. dr = dt.NewRow();
  550. drPre = dt.NewRow();
  551. int lenName = item.PriceTypeName.Length >= 8 ? 8 : item.PriceTypeName.Length;
  552. dr["Type"] = type;
  553. dr["FuelTypeId"] = item.FuelTypeId;
  554. dr["PriceTypeId"] = item.PriceTypeId;
  555. dr["PriceTypeName"] = item.PriceTypeName.Substring(0, lenName);
  556. dr["PriceCode"] = item.PriceCode;
  557. dr["PriceCodeName"] = item.PriceCodeName;
  558. dr["IsTypeFirst"] = 0;
  559. for (var d = 1; d <= day; d++) {
  560. dr[$"D{d}"] = 0;
  561. }
  562. drPre.ItemArray = dr.ItemArray;
  563. dr["YearName"] = item.Year + "년";
  564. dr["Sort"] = 2;
  565. drPre["YearName"] = (item.Year - 1) + "년";
  566. drPre["Sort"] = (item.FuelTypeId == 3 && currentCode == item.PriceTypeId && currentCode2 != item.PriceCode) ? 0 : 1; //수도 - 상/하수도 구분 때문..
  567. drPre["IsTypeFirst"] = (currentCode != item.PriceTypeId) ? 1 : 0;
  568. }
  569. dr[$"D{item.Date}"] = Math.Round(item.Value);
  570. drPre[$"D{item.Date}"] = Math.Round(item.LastValue);
  571. total += Math.Round(item.Value);
  572. total_pre += Math.Round(item.LastValue);
  573. currentCode = item.PriceTypeId;
  574. currentCode2 = item.PriceCode;
  575. fuelTypeId = item.FuelTypeId;
  576. }
  577. //마지막 데이터 안 들어감
  578. dr["Total"] = total;
  579. drPre["Total"] = total_pre;
  580. dt.Rows.Add(drPre);
  581. dt.Rows.Add(dr);
  582. //증감
  583. if (!(fuelTypeId == 1 && type == 1) && !(fuelTypeId == 2 && type == 1)) {
  584. drCmp = dt.NewRow();
  585. drCmp.ItemArray = dr.ItemArray;
  586. for (var d = 1; d <= day; d++) {
  587. drCmp[$"D{d}"] = (double)dr[$"D{d}"] - (double)dr[$"D{d}"];
  588. }
  589. drCmp["YearName"] = "증감";
  590. drCmp["Sort"] = 3;
  591. drCmp["Total"] = total - total_pre;
  592. dt.Rows.Add(drCmp);
  593. }
  594. drCmpRate = dt.NewRow();
  595. drCmpRate.ItemArray = dr.ItemArray;
  596. for (var d = 1; d <= day; d++) {
  597. drCmpRate[$"D{d}"] = (((double)dr[$"D{d}"] - (double)dr[$"D{d}"]) / 100).ToString("0.##");
  598. }
  599. drCmpRate["YearName"] = "증감%";
  600. drCmpRate["Sort"] = 4;
  601. drCmpRate["Total"] = ((total - total_pre) / 100).ToString("0.##");
  602. dt.Rows.Add(drCmpRate);
  603. }
  604. return dt;
  605. }
  606. /// <summary>
  607. /// 에너지 사용량 그래프
  608. /// </summary>
  609. public DataTable _GetEnergyGraph(List<EnergyReportGrid> data, DateTime today, int type) {
  610. DataTable dt = new DataTable();
  611. DataRow row = null;
  612. string[] colName = { "FuelTypeId", "Title", "TitleId", "PriceCode", "TabType", "LineName", "LineLastName", "Day", "Value", "LastValue", "MaxValue", "MinValue" };
  613. foreach (var item in colName) {
  614. dt.Columns.Add(item);
  615. }
  616. var day = 12;
  617. if (type == 0) {
  618. //설비가동현황
  619. DateTime tmpDt = DateTime.Parse(today.Year.ToString() + "-" + today.AddMonths(1).Month.ToString() + "-01");
  620. day = tmpDt.AddDays(-1).Day;
  621. }
  622. var data2 = data.GroupBy(x => new { x.FuelTypeId, x.PriceTypeId, x.PriceCode }).Select(x => new {
  623. FuelTypeId = x.Key.FuelTypeId,
  624. PriceTypeId = x.Key.PriceTypeId,
  625. PriceTypeName = x.Max(f => f.PriceTypeName),
  626. PriceCode = x.Key.PriceCode,
  627. PriceCodeName = x.Max(f => f.PriceCodeName),
  628. }).ToList();
  629. foreach (var item in data2) {
  630. if (item.FuelTypeId == 3) {
  631. //수도 - 상수도, 하수도로 구분
  632. var maxValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Max(x => x.Value);
  633. var maxLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Max(x => x.LastValue);
  634. var minValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Min(x => x.Value);
  635. var minLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode).Min(x => x.LastValue);
  636. for (var i = 1; i <= day; i++) {
  637. row = null;
  638. var tmpA = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.PriceCode == item.PriceCode && x.Date == i).FirstOrDefault();
  639. row = dt.NewRow();
  640. row["FuelTypeId"] = item.FuelTypeId;
  641. row["Title"] = item.PriceCodeName.Replace("사용량", "");
  642. row["TitleId"] = item.PriceTypeId;
  643. row["PriceCode"] = item.PriceCode;
  644. row["TabType"] = type;
  645. row["LineName"] = today.Year.ToString() + "년";
  646. row["LineLastName"] = today.AddYears(-1).Year.ToString() + "년";
  647. row["Day"] = i;
  648. row["Value"] = tmpA != null ? Math.Round(tmpA.Value) : 0;
  649. row["LastValue"] = tmpA != null ? Math.Round(tmpA.LastValue) : 0;
  650. row["MaxValue"] = maxValue > maxLastValue ? maxValue : maxLastValue;
  651. row["MinValue"] = minValue > minLastValue ? minValue : minLastValue;
  652. dt.Rows.Add(row);
  653. }
  654. } else {
  655. var maxValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Max(x => x.Value);
  656. var maxLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Max(x => x.LastValue);
  657. var minValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Min(x => x.Value);
  658. var minLastValue = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId).Min(x => x.LastValue);
  659. for (var i = 1; i <= day; i++) {
  660. row = null;
  661. var tmpA = data.Where(x => x.FuelTypeId == item.FuelTypeId && x.PriceTypeId == item.PriceTypeId && x.Date == i).FirstOrDefault();
  662. row = dt.NewRow();
  663. row["FuelTypeId"] = item.FuelTypeId;
  664. row["Title"] = item.PriceTypeName;
  665. row["TitleId"] = item.PriceTypeId;
  666. row["TabType"] = type;
  667. row["LineName"] = today.Year.ToString() + "년";
  668. row["LineLastName"] = today.AddYears(-1).Year.ToString() + "년";
  669. row["Day"] = i;
  670. row["Value"] = tmpA != null ? Math.Round(tmpA.Value) : 0;
  671. row["LastValue"] = tmpA != null ? Math.Round(tmpA.LastValue) : 0;
  672. row["MaxValue"] = maxValue > maxLastValue ? maxValue : maxLastValue;
  673. row["MinValue"] = minValue > minLastValue ? minValue : minLastValue;
  674. dt.Rows.Add(row);
  675. }
  676. }
  677. }
  678. return dt;
  679. }
  680. }
  681. }