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 System.Data; using System.IO; using OfficeOpenXml; using System.Text; using System.Collections.Generic; using Newtonsoft.Json; namespace FMSApp.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/app/[controller]")] public class DailyReportController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly DailyReportService _service; private readonly FMSAdmin.Services.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, DailyReportService service, FMSAdmin.Services.SiteWeatherInfoService siteWeatherInfoService, StorageHelper storage ) { _logger = logger; _context = context; _service = service; _siteWeatherInfoService = siteWeatherInfoService; _storage = storage; } /// /// 목록 /// [HttpGet] public IActionResult List([FromQuery] PagingRequest req, [FromQuery] string siteId, bool businessAuth) { 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, x.AddDate, x.BusinessFieldId, x.Name, x.Contents, x.RegisterUserId, RegisterUserName = _context.CmUser.FirstOrDefault(user => user.UserId == x.RegisterUserId).Name, x.ApproveDate, x.RegisterDate, x.ApproverUserId, ApproverUserName = _context.CmUser.FirstOrDefault(user => user.UserId == x.ApproverUserId).Name, }); var paging = PagingList.Pagination(list, req.page, req.limit); return Ok(paging); } [HttpGet("[action]")] public IActionResult Get([FromQuery] string siteId, [FromQuery] DateTime addDate, [FromQuery] string businessFieldId) { var query = _service.GetAll(); var item = query.Where( x => x.SiteId == Util.ToInt(siteId) && x.AddDate == addDate.Date && x.BusinessFieldId == Util.ToInt(businessFieldId) ).Select(x => new { x.SiteId, x.AddDate, x.BusinessFieldId, x.Name, x.Contents, x.Datas, x.RegisterUserId, x.RegisterDate, 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, CmFile = _storage.ParseFile(x.CmFile), }).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"]; */ _logger.LogInformation("========================== 에너지! ======================="); var energies = _context.BemsFormula.Where( x => x.SiteId == Util.ToInt(siteId) && x.FormulaId == 1 && (new int[] { 100, 101, 102, 104, 105, 106 }).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.BemsMonitoringPointHistoryDaily.Where( usage => usage.CreatedDateTime.Date == today.Date ).FirstOrDefault().DailyValue, //당월 MonthUsage = mp.BemsMonitoringPointHistoryDaily.Where( usage => usage.CreatedDateTime.Date >= currentMonthFirstDay.Date && usage.CreatedDateTime < tomorrow ).Sum(usage => usage.DailyValue) }).FirstOrDefault() }).Where(p => !p.MonitoringPoint.Name.Contains("과거")).ToList() }).ToList(); _logger.LogInformation("========================== 작업내역 ======================="); 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.SiteId == Util.ToInt(siteId) && ((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)) select res.WorkRequestId where req.SiteId == Util.ToInt(siteId) && ( (res.Contains(req.WorkRequestId) && req.BusinessFieldId == x.BusinessFieldId) || (req.WorkProgressId <= 5 && req.WorkProgressId >= 4 && req.BusinessFieldId == x.BusinessFieldId) && (req.StartWorkDate <= addDate.Date)) 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(); _logger.LogInformation("========================== 작업현황 ======================="); /* var workCounts = _context.CmBusinessField.Where( x => x.IsUse == true ).OrderBy(x => x.BusinessFieldId).Select(x => new { x.BusinessFieldId, BusinessFieldName = x.Name, RequestCount = _context.FmsWorkRequest.Where( req => req.SiteId == Util.ToInt(siteId) && req.StartWorkDate.Date == addDate.Date && req.BusinessFieldId == x.BusinessFieldId ).Count(), ApprovalCount = _context.FmsWorkResult.Where( res => res.SiteId == Util.ToInt(siteId) && res.FmsWorkRequest.StartWorkDate.Date == addDate.Date && res.FmsWorkRequest.WorkProgressId == 6 && res.FmsWorkRequest.BusinessFieldId == x.BusinessFieldId ).Count() }).ToList(); */ var workCounts = works.Select(x => new { x.BusinessFieldId, x.BusinessFieldName, RequestCount = x.List.Count, ApprovalCount = x.List.Where(x => x.WorkProgressId == 6).Count() }).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.SiteId == Util.ToInt(siteId) && 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 complaints = _context.FmsComplaints.Where( x => x.SiteId == Util.ToInt(siteId) && x.StartDate >= today && x.StartDate < tomorrow ).OrderBy(x => x.ComplaintId).Select(x => new { x.ComplaintTypeId, ComplaintTypeName = x.FmsComplaintCodeType.Name, x.ComplaintId, 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, CreatedDateTime = x.CreatedDateTime.ToString("yyyy-MM-dd HH:mm"), x.CurrentValue, }).ToList(); return Ok(new { Weather = weather, Energies = energies, Works = works, WorkCounts = workCounts, Materials = materials, Accidents = accidents, Complaints = complaints, 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(); } // 검색 & 정렬 공통 private IQueryable _FilterAndSort(PagingRequest req) { var query = _service.GetAll(); // 기본 Entity 검색 query = query.Filter(req.conditions); // 기본 Entity 정렬 query = query.Sort(req.sort); return query; } [HttpDelete("[action]")] public IActionResult Delete([FromQuery] string siteId, [FromQuery] DateTime addDate, [FromQuery] string businessFieldId) { _service.Delete(Util.ToInt(siteId), addDate, Util.ToInt(businessFieldId)); return Ok(); } [AllowAnonymous] [HttpGet("[action]")] public string GetJson([FromQuery] string siteId, [FromQuery] DateTime addDate) { var query = _service.GetAll(); var item = query.Where( x => x.SiteId == Util.ToInt(siteId) && x.AddDate.Date == addDate.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 json = ""; if (item != null) json = $@" {{ SiteNm : ""{item.CmSite.Name}"", Datas: {item.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 ""; } } }