using System; using System.Linq; using System.Threading.Tasks; using FMSAdmin.Data; using FMSAdmin.Models; using FMSAdmin.Entities; //using FMSApp.Services; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using Microsoft.AspNetCore.Authorization; using FMSAdmin.Helpers; using FMSAdmin.Services; using System.Data; using System.IO; using OfficeOpenXml; using System.Text; using System.Collections.Generic; using Newtonsoft.Json; using System.Text.RegularExpressions; using Newtonsoft.Json.Linq; namespace FMSAdmin.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/[controller]")] public class DailyReportController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly FMSApp.Services.DailyReportService _service; private readonly SiteWeatherInfoService _siteWeatherInfoService; private readonly StorageHelper _storage; public enum WorkTypeId { PeriodicInspection = 1, ForensicExamination = 2, CivilComplaint = 3, SiAlarm = 4, Occasional = 5, Construct = 6, ForensicEducation = 7, } public enum WorkProgressId { WorkStatusPending = 3, WorkStatusInProgress = 4, WorkStatusComplete = 5, WorkStatusApproved = 6, WorkStatusDeferred = 7, } public enum AdjustmentTypeId { Regular = 0, Loss = 1, Return = 2 } public DailyReportController( ILogger logger, FMSContext context, FMSApp.Services.DailyReportService service, SiteWeatherInfoService siteWeatherInfoService, StorageHelper storage ) { _logger = logger; _context = context; _service = service; _siteWeatherInfoService = siteWeatherInfoService; _storage = storage; } /// /// 목록 /// [HttpGet] public IActionResult List([FromQuery] PagingRequest req) { var query = _FilterAndSort(req); var list = _ListSelect(query); var paging = PagingList.Pagination(list, req.page, req.limit); return Ok(paging); } public dynamic _ListSelect(IQueryable query) { // var query = _FilterAndSort(req); // query = BusinessFieldHelper.Apply(_context, User, businessAuth, query); // query = query.Where(x => x.SiteId == Util.ToInt(siteId)); var list = query.Select(x => new { x.SiteId, SiteName = _context.CmSite.FirstOrDefault(site => site.SiteId == x.SiteId).Name, AddDate = x.AddDate.ToString("yyyy-MM-dd"), x.BusinessFieldId, x.Name, x.Contents, x.RegisterUserId, RegisterUserName = _context.CmUser.FirstOrDefault(user => user.UserId == x.RegisterUserId).Name, x.ApproveDate, RegisterDate = x.RegisterDate.Value.ToString("yyyy-MM-dd"), x.ApproverUserId, ApproverUserName = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId).Name, }); return list; } [HttpGet("[action]")] public IActionResult Get([FromQuery] string siteId, [FromQuery] DateTime addDate, [FromQuery] string businessFieldId) { var query = _service.GetAll(); // 업무분야 필드가 있는경우만 추가 if (businessFieldId != null) { query = query.Where(x => x.BusinessFieldId == Util.ToInt(businessFieldId) || x.CmBusinessField.Name == "공통"); } var item = query.Where( x => x.SiteId == Util.ToInt(siteId) && x.AddDate == addDate.Date //&& x.BusinessFieldId == Util.ToInt(businessFieldId) ).Select(x => new { x.SiteId, SiteName = _context.CmSite.FirstOrDefault(site => site.SiteId == x.SiteId).Name, AddDate = x.AddDate.ToString("yyyy-MM-dd"), x.BusinessFieldId, x.Name, x.Contents, x.Datas, x.RegisterUserId, RegisterDate = x.RegisterDate.Value.ToString("yyyy-MM-dd"), x.ApproveDate, x.ApproverUserId, ApproverUserName = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId).Name, x.ApproverUserId2, ApproverUserName2 = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId2).Name, x.ApproverUserId3, ApproverUserName3 = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId3).Name, x.ApproverUserId4, ApproverUserName4 = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId4).Name, x.ApproverUserId5, ApproverUserName5 = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId5).Name, }).FirstOrDefault(); return Ok(item); } [HttpGet("[action]")] public IActionResult GetCurrentInfo([FromQuery] string siteId, [FromQuery] DateTime addDate, [FromQuery] string businessFieldId) { var today = addDate.Date; var yesterday = addDate.Date.AddDays(-1); var tomorrow = addDate.Date.AddDays(1); var currentMonthFirstDay = new DateTime(addDate.Year, addDate.Month, 1); // var prevMonthFirstDay = new DateTime(addDate.Year, addDate.Month - 1, 1); var prevMonthFirstDay = addDate.Date.AddMonths(-1).AddDays(1 - addDate.Day); var site = _context.CmSite.FirstOrDefault(x => x.SiteId == Util.ToInt(siteId)); var sido = _siteWeatherInfoService.ReplaceSido(site.ApiSido); var sigun = site.ApiSigun; var weatherDateTime = new DateTime(addDate.Year, addDate.Month, addDate.Day, 10, 0, 0); SiteWeatherInfo weather = null; if (!string.IsNullOrEmpty(sido) && !string.IsNullOrEmpty(sigun)) { weather = _context.SiteWeatherInfo.Where( x => x.Sido == sido && x.Sigun == sigun && x.BaseTime >= weatherDateTime && x.BaseTime < weatherDateTime.Date.AddDays(1) ).OrderBy(x => x.BaseTime).FirstOrDefault(); } var workStatus = new Dictionary { ["WorkStatusTypePeriodicInspection"] = _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.PeriodicInspection, addDate), ["WorkStatusTypeForensicExamination"] = _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.ForensicExamination, addDate), ["WorkStatusTypeForensicEducation"] = _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.ForensicEducation, addDate), ["WorkStatusTypeOccasional"] = _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.Occasional, addDate), ["WorkStatusTypeConstruct"] = _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.Construct, addDate), ["WorkStatusTypeEtc"] = _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.SiAlarm, addDate) + _service.GetWorkStatusByWorkTypeId(Util.ToInt(siteId), (int)WorkTypeId.CivilComplaint, addDate), ["WorkStatusPending"] = _service.GetWorkStatusByWorkProgressId(Util.ToInt(siteId), (int)WorkProgressId.WorkStatusPending, addDate), ["WorkStatusInProgress"] = _service.GetWorkStatusByWorkProgressId(Util.ToInt(siteId), (int)WorkProgressId.WorkStatusInProgress, addDate), ["WorkStatusDeferred"] = _service.GetWorkStatusByWorkProgressId(Util.ToInt(siteId), (int)WorkProgressId.WorkStatusDeferred, addDate), ["WorkStatusComplete"] = _service.GetFmsWorkResultComplete(Util.ToInt(siteId), (int)WorkProgressId.WorkStatusComplete, addDate), ["WorkStatusApproved"] = _service.GetFmsWorkResultApproval(Util.ToInt(siteId), (int)WorkProgressId.WorkStatusApproved, addDate), }; workStatus["WorkStatusTypeTotal"] = workStatus["WorkStatusTypeOccasional"] + workStatus["WorkStatusTypePeriodicInspection"] + workStatus["WorkStatusTypeForensicExamination"] + workStatus["WorkStatusTypeEtc"]; workStatus["WorkStatusTotal"] = workStatus["WorkStatusPending"] + workStatus["WorkStatusInProgress"] + workStatus["WorkStatusDeferred"] + workStatus["WorkStatusComplete"] + workStatus["WorkStatusApproved"]; var energies = _context.BemsFormula.Where( x => x.SiteId == Util.ToInt(siteId) && x.FormulaId == 1 && (new int[] { 100, 101, 102 }).Contains(x.FacilityTypeId) ).Select(x => new { x.FacilityTypeId, FacilityTypeName = _context.BemsFacilityType.FirstOrDefault(ft => ft.FacilityTypeId == x.FacilityTypeId).Name, x.FacilityCode, List = x.BemsFormulaParameter.Select(p => new { Parameter = new { p.ParameterFacilityCode, p.ParameterPropertyId, p.FormulaId, p.ParameterId, }, MonitoringPoint = _context.BemsMonitoringPoint.Where( mp => mp.SiteId == p.SiteId && mp.FacilityCode == p.ParameterFacilityCode && mp.PropertyId == p.ParameterPropertyId ).Select(mp => new { mp.FuelTypeId, mp.Name, // 당일(15minRaw 에서 해당 일자의 마지막 검침값) Today = mp.BemsMonitoringPointHistory15minRawData.Where( raw => raw.CreatedDateTime >= today && raw.CreatedDateTime < tomorrow ).OrderByDescending(raw => raw.CreatedDateTime).FirstOrDefault().CurrentValue, // 전일(15minRaw 에서 해당 일자 전날의 마지막 검침값) DayBefore = mp.BemsMonitoringPointHistory15minRawData.Where( raw => raw.CreatedDateTime >= yesterday && raw.CreatedDateTime < today ).OrderByDescending(raw => raw.CreatedDateTime).FirstOrDefault().CurrentValue, // 당일 - 전일(당일 사용한 값이므로 당일 - 전일) DailyUsage = mp.BemsMonitoringPointHistory15minRawData.Where( raw => raw.CreatedDateTime >= today && raw.CreatedDateTime < tomorrow ).OrderByDescending(raw => raw.CreatedDateTime).FirstOrDefault().CurrentValue - mp.BemsMonitoringPointHistory15minRawData.Where( raw => raw.CreatedDateTime >= yesterday && raw.CreatedDateTime < today ).OrderByDescending(raw => raw.CreatedDateTime).FirstOrDefault().CurrentValue, // 당월(당월 사용한 값이므로 15minRaw 의 [당월 마지막 검침값] - [전월 마지막 검침값]) // 기존에는 Daily 테이블의 DailyValue 를 1일 - 당일 까지 합산하였으나, // Daily 에는 값이 들어오지 않을거라 하여 15minRaw 로 계산. MonthUsage = mp.BemsMonitoringPointHistory15minRawData.Where( raw => raw.CreatedDateTime >= currentMonthFirstDay && raw.CreatedDateTime < tomorrow ).OrderByDescending(raw => raw.CreatedDateTime).FirstOrDefault().CurrentValue - mp.BemsMonitoringPointHistory15minRawData.Where( raw => raw.CreatedDateTime >= prevMonthFirstDay && raw.CreatedDateTime < currentMonthFirstDay ).OrderByDescending(raw => raw.CreatedDateTime).FirstOrDefault().CurrentValue, }).FirstOrDefault() }).ToList() }).ToList(); var works = _context.CmBusinessField.Where( x => x.IsUse == true ).OrderBy(x => x.BusinessFieldId).Select(x => new { x.BusinessFieldId, BusinessFieldName = x.Name, /* 기존 Bems 소스 그대로 가져 넣음... where 절의 and 와 or 가 난해함... 의도한건지 정확하지 않음. */ List = (from req in _context.FmsWorkRequest let res = from res in _context.FmsWorkResult where (res.IsConfirmed == false && (res.StartDate <= addDate.Date)) || (res.ConfirmedDate.Value.Year == addDate.Year && res.ConfirmedDate.Value.Month == addDate.Month && res.ConfirmedDate.Value.Day == addDate.Day) && res.SiteId == Util.ToInt(siteId) select res.WorkRequestId where (res.Contains(req.WorkRequestId) && req.BusinessFieldId == x.BusinessFieldId) || (req.WorkProgressId <= 5 && req.WorkProgressId >= 4 && req.BusinessFieldId == x.BusinessFieldId) && (req.StartWorkDate <= addDate.Date) && req.SiteId == Util.ToInt(siteId) orderby req.RequestDate, req.WorkProgressId ascending select new { WorkRequestId = req.WorkRequestId, BusinessFieldId = req.BusinessFieldId, WorkProgressId = req.WorkProgressId, WorkProgressName = req.WorkProgress.Name, WorkTypeId = req.WorkTypeId, WorkTypeName = req.WorkType.Name, RequestDate = req.RequestDate, Title = req.Title, WorkUsers = req.WorkUsers, WorkPartnerId = req.WorkPartnerId, WorkPartnerName = req.WorkPartner.Name, req.FmsWorkResult.StartDate, req.FmsWorkResult.EndDate, CheckItems = _context.FmsWorkResultCheckItem.Where( check => check.SiteId == req.SiteId && check.WorkRequestId == req.WorkRequestId ).OrderBy(check => check.CheckItemId).Select(check => new { check.CheckDate, check.TextResult, }).ToList() }).ToList() }).ToList(); var materials = _context.CmBusinessField.Where( x => x.IsUse == true ).OrderBy(x => x.BusinessFieldId).Select(x => new { x.BusinessFieldId, BusinessFieldName = x.Name, List = _context.FmsMaterialRelease.Where( release => release.SiteId == Util.ToInt(siteId) && release.ReleaseDate.Date == addDate.Date && release.BusinessFieldId == x.BusinessFieldId && (release.AdjustmentTypeId == 0 || release.AdjustmentTypeId == 1) ).OrderBy(release => release.ReleaseDate).Select(release => new { ReleaseTypeName = release.ReleaseType.Name, FirstClassId = release.FmsMaterial.FirstClassId, FirstClassName = release.FmsMaterial.FmsMaterialCodeClass.Name, SecondClassId = release.FmsMaterial.SecondClassId, SecondClassName = release.FmsMaterial.FmsMaterialCodeClass1.Name, ThirdClassId = release.FmsMaterial.ThirdClassId, ThirdClassName = release.FmsMaterial.FmsMaterialCodeClass2.Name, MaterialName = release.FmsMaterial.Name, Standard = release.FmsMaterial.Standard, WorkName = release.FmsWorkResult.FmsWorkOrder.Title, Unit = release.FmsMaterial.Unit, MaterialCount = (int)release.MaterialCount, WorkerName = release.FmsWorkResult.CmUser.Name }).ToList() }).ToList(); var accidents = _context.FmsAccident.Where( x => x.StartDate >= today && x.StartDate < tomorrow ).OrderBy(x => x.AccidentId).Select(x => new { x.AccidentTypeId, AccidentTypeName = x.FmsAccidentCodeType.Name, x.AccidentId, x.Name, x.DepartmentId, DepartmentName = x.CmDepartment.Name, x.StartDate, x.EndDate, }).ToList(); var alarms = _context.FmsAlarmLog.Where( x => x.SiteId == Util.ToInt(siteId) && x.CreatedDateTime >= today && x.CreatedDateTime < tomorrow && x.CurrentValue != 0 ).OrderBy(x => x.CreatedDateTime).Select(x => new { x.FacilityTypeId, FacilityTypeName = x.AlarmPoint.FacilityType.Name, x.FacilityCode, x.PropertyId, MonitoringPointName = x.AlarmPoint.BemsMonitoringPoint.Name, FacilityName = x.AlarmPoint.Facility.Name, x.CreatedDateTime, x.CurrentValue, }).ToList(); return Ok(new { Weather = weather, WorkStatus = workStatus, Energies = energies, Works = works, Materials = materials, Accidents = accidents, Alarms = alarms, }); } /// /// 저장 /// /// /// [HttpPost("[action]")] public IActionResult Save([FromBody] FmsDailyReport data, [FromQuery] bool isCreate) { _logger.LogInformation($"Save isCreate({isCreate})"); //_logger.LogInformation(data.Dump()); if (ModelState.IsValid) { _service.Save(data, isCreate); } else { return BadRequest(ModelState); } return Ok(); } [HttpPost("[action]")] public IActionResult Approve([FromQuery] string siteId, [FromQuery] string approverUserId, [FromQuery] DateTime addDate, [FromQuery] string businessFieldId) { _service.Approve(Util.ToInt(siteId), approverUserId, addDate, Util.ToInt(businessFieldId)); return Ok(); } /// /// 삭제 /// /// /// /// [HttpDelete("{siteId}/{addData}")] public IActionResult Delete(int siteId, DateTime addData) { _service.Delete(siteId, addData); return Ok(); } // 검색 & 정렬 공통 private IQueryable _FilterAndSort(PagingRequest req) { var query = _service.GetAll(); // 기본 Entity 검색 query = query.Filter(req.conditions); if (req.siteId != null) { query = query.Where(x => x.SiteId == Util.ToInt(req.siteId)); } if (req.siteIds != null && req.siteIds.Length > 0) { query = query.Where(x => req.siteIds.Contains(x.SiteId)); } // 업무분야 필드가 있는경우만 추가 if (req.businessFieldId != null) { query = query.Where(x => x.SiteId == User.GetSiteId()); query = query.Where(x => x.BusinessFieldId == req.businessFieldId || x.CmBusinessField.Name == "공통"); } // 기본 Entity 정렬 query = query.Sort(req.sort); return query; } [AllowAnonymous] [HttpGet("GetJson/{siteId}/{date}")] public string GetJson(string siteId, DateTime date) { var query = _service.GetAll(); var item = query.Where( x => x.SiteId == Util.ToInt(siteId) && x.AddDate.Date == date.Date ).Select(x => new { CmSite = new { x.Site.Name }, AddDate = x.AddDate.ToString("yyyy-MM-dd"), x.Name, x.Contents, x.Datas, x.ApproveDate, x.ApproveDate2, x.ApproveDate3, x.ApproverUserId, x.ApproverUserId2, x.ApproverUserId3 }).FirstOrDefault(); string approve = ""; if (item != null) { approve += "[" + getApproverInfo(item.ApproverUserId, item.ApproveDate); approve += getApproverInfo(item.ApproverUserId2, item.ApproveDate2); approve += getApproverInfo(item.ApproverUserId3, item.ApproveDate3); approve += "]"; } string datas = "{}"; if (!string.IsNullOrEmpty(item.Datas)) { dynamic jsonObject = JObject.Parse(item.Datas); var energiesTotal = new JArray(); foreach (dynamic energy in jsonObject.Energies) { if (energy != null) { energy.List = null; // 에너지 합계만 표출 if (energy.List == null) { energy.List = new JArray(); } else { foreach (dynamic i in energy.List) { energiesTotal.Add(JToken.FromObject(new { Name = i.Name, Today = i.Today, DayBefore = i.DayBefore, DailyUsage = i.DailyUsage, MonthUsage = i.MonthUsage, })); } } energiesTotal.Add(JToken.FromObject(new { Name = energy.Total.Name, Today = energy.Total.Today, DayBefore = energy.Total.DayBefore, DailyUsage = energy.Total.DailyUsage, MonthUsage = energy.Total.MonthUsage, })); } } var workList = new JArray(); foreach (dynamic work in jsonObject.Works) { if (work != null) { if (work.List == null) { work.List = new JArray(); } foreach (dynamic i in work.List) { workList.Add(JToken.FromObject(new { BusinessFieldName = work.BusinessFieldName, WorkProgressName = i.WorkProgressName, WorkTypeName = i.WorkTypeName, Title = i.Title, })); } } } var materialList = new JArray(); if (jsonObject.Materials != null) foreach (dynamic material in jsonObject.Materials) { if (material != null) { if (material.List == null) { material.List = new JArray(); } foreach (dynamic i in material.List) { materialList.Add(JToken.FromObject(new { BusinessFieldName = material.BusinessFieldName, ReleaseType = i.ReleaseTypeName, FirstClass = i.FirstClassName, SecondClass = i.SecondClassName, ThirdClass = i.ThirdClassName, MaterialName = i.MaterialName, Standard = i.Standard, Unit = i.Unit, MaterialCount = i.MaterialCount, WorkName = i.WorkName, WorkerName = i.WorkerName })); } } } jsonObject.EnergiesTotal = energiesTotal; jsonObject.WorkList = workList; jsonObject.MaterialList = materialList; datas = JsonConvert.SerializeObject(jsonObject); } string json = ""; if (item != null) json = $@" {{ SiteNm : ""{item.CmSite.Name}"", Datas: {datas}, Approver : {approve} }}"; return json; } public string getApproverInfo(string id, DateTime? date) { if (!Util.IsNullOrEmpty(id)) { var userInfo = _context.CmUser.Where(x => x.UserId == id).Select(x => new { GroupNm = x.CmUserGroup.Name, Date = date.Value.ToString("yyyy-MM-dd"), image = x.CmFile1 != null ? _storage.GetFullUrl(x.CmFile1.Path) + _storage.getFilePath(x.CmFile1) : "" }).SingleOrDefault(); return JsonConvert.SerializeObject(userInfo) + ","; } else return ""; } } }