using System; using System.Collections; using System.Collections.Generic; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Linq; using System.Net; using System.Web; using System.Web.Http; using System.Web.Http.Description; using DevExpress.Office.Utils; using iBemsDataService.Model; using iBemsDataService.Util; using System.Transactions; using System.Collections.Specialized; namespace iBemsDataService.Controllers { public class DailyReportController : ApiController { private iBemsEntities db = new iBemsEntities(); public enum WorkTypeId { PeriodicInspection = 1, ForensicExamination = 2, CivilComplaint = 3, Etc1 = 4, Occasional = 5 } public enum WorkProgressId { WorkStatusPending = 3, WorkStatusInProgress = 4, WorkStatusComplete = 5, WorkStatusApproved = 6, WorkStatusDeferred = 7, } public enum AdjustmentTypeId { Regular = 0, Loss = 1, Return = 2 } // jhLee 2016-05-12 [ActionName("CurrentDate")] public DateTime GetCurrentDate() { return DateTime.Now; } [ActionName("WorkStatus")] public WorkStatus GetWorkStatus() { Uri uri = Request.RequestUri; NameValueCollection uriQuery = HttpUtility.ParseQueryString(uri.Query); WorkStatusGenerator workStatusGenerator = new WorkStatusGenerator(db, uriQuery); return workStatusGenerator.GetWorkStatus(); } [ActionName("FmsWorkRequestForDailyReport")] public List GetFmsWorkRequestForDailyReport() { Uri uri = Request.RequestUri; NameValueCollection uriQuery = HttpUtility.ParseQueryString(uri.Query); WorkStatusGenerator workStatusGenerator = new WorkStatusGenerator(db, uriQuery); return workStatusGenerator.GetFmsWorkRequestForDailyReport(); } [ActionName("FmsWorkOrderForDailyReport")] public List GetFmsWorkOrderForDailyReport() { Uri uri = Request.RequestUri; NameValueCollection uriQuery = HttpUtility.ParseQueryString(uri.Query); WorkStatusGenerator workStatusGenerator = new WorkStatusGenerator(db, uriQuery); return workStatusGenerator.GetFmsWorkOrderForDailyReport(WorkTypeId.PeriodicInspection, WorkTypeId.ForensicExamination); } [ActionName("FmsWorkEtcForDailyReport")] public List GetFmsWorkEtcForDailyReport() { Uri uri = Request.RequestUri; NameValueCollection uriQuery = HttpUtility.ParseQueryString(uri.Query); WorkStatusGenerator workStatusGenerator = new WorkStatusGenerator(db, uriQuery); return workStatusGenerator.GetFmsWorkOrderForDailyReport(WorkTypeId.Etc1, WorkTypeId.Occasional); } [ActionName("FmsMaterialForDailyReport")] public List GetFmsMaterialForDailyReport() { Uri uri = Request.RequestUri; NameValueCollection uriQuery = HttpUtility.ParseQueryString(uri.Query); WorkStatusGenerator workStatusGenerator = new WorkStatusGenerator(db, uriQuery); return workStatusGenerator.GetFmsMaterialForDailyReport(); } [ActionName("FileUpload")] public String FileUpload() { //Uri uri = Request.RequestUri; //NameValueCollection uriQuery = HttpUtility.ParseQueryString(uri.Query); //String siteId = uriQuery.Get("SiteId"); //WorkStatusGenerator workStatusGenerator = new WorkStatusGenerator(db, uriQuery); var httpPostedFile = HttpContext.Current.Request.Files[0]; if (httpPostedFile != null) { try { var fileSavePath = System.IO.Path.Combine(HttpContext.Current.Server.MapPath("..\\..\\App_Data\\files\\report2"), httpPostedFile.FileName); //var fileSavePath = System.IO.Path.Combine("../", httpPostedFile.FileName); // Save the uploaded file to "UploadedFiles" folder httpPostedFile.SaveAs(fileSavePath); return fileSavePath; } catch (Exception e) { throw new Exception(e.Message.ToString()); } } else { throw new Exception("File length error"); } } class WorkStatusGenerator { iBemsEntities db; int siteId; int year; int month; int day; int businessFieldId; public WorkStatusGenerator(iBemsEntities db, NameValueCollection uriQuery) { int siteId, businessFieldId; String addDate; if (int.TryParse(uriQuery.Get("SiteId"), out siteId) == false || (addDate = uriQuery.Get("AddDate")) == null || int.TryParse(uriQuery.Get("BusinessFieldId"), out businessFieldId) == false) { throw new Exception(string.Format("Not Found Parameters: {0} {1} {2}", "SiteId", "addDate", "BusinessFieldId")); } this.db = db; this.siteId = siteId; String[] addDateArrayStr = addDate.Split('-'); if (int.TryParse(addDateArrayStr[0], out year) == false || int.TryParse(addDateArrayStr[1], out month) == false || int.TryParse(addDateArrayStr[2], out day) == false) { throw new Exception(string.Format("addDate Parsing Error: {0}", addDate)); } this.businessFieldId = businessFieldId; } /* 작업 현황 테이블 정보 추출 */ public WorkStatus GetWorkStatus() { WorkStatus workStatus = new WorkStatus(); workStatus.WorkStatusTypeOccasional = GetWorkStatusByWorkTypeId(WorkTypeId.Occasional); workStatus.WorkStatusTypePeriodicInspection = GetWorkStatusByWorkTypeId(WorkTypeId.PeriodicInspection); workStatus.WorkStatusTypeForensicExamination = GetWorkStatusByWorkTypeId(WorkTypeId.ForensicExamination); workStatus.WorkStatusTypeEtc = GetWorkStatusByWorkTypeId(WorkTypeId.Etc1) + GetWorkStatusByWorkTypeId(WorkTypeId.CivilComplaint); workStatus.WorkStatusTypeTotal = workStatus.WorkStatusTypePeriodicInspection + workStatus.WorkStatusTypeForensicExamination + workStatus.WorkStatusTypeEtc + workStatus.WorkStatusTypeOccasional; workStatus.WorkStatusPending = GetWorkStatusByWorkProgressId(WorkProgressId.WorkStatusPending); workStatus.WorkStatusInProgress = GetWorkStatusByWorkProgressId(WorkProgressId.WorkStatusInProgress); workStatus.WorkStatusDeferred = GetWorkStatusByWorkProgressId(WorkProgressId.WorkStatusDeferred); workStatus.WorkStatusComplete = GetFmsWorkResultComplete(WorkProgressId.WorkStatusComplete); workStatus.WorkStatusApproved = GetFmsWorkResultApproval(WorkProgressId.WorkStatusApproved); workStatus.WorkStatusTotal = workStatus.WorkStatusPending + workStatus.WorkStatusInProgress + workStatus.WorkStatusDeferred + workStatus.WorkStatusComplete + workStatus.WorkStatusApproved; return workStatus; } private int GetWorkStatusByWorkTypeId(WorkTypeId workTypeId) { return (from x in db.FmsWorkOrder let y = from y in db.FmsWorkRequest where y.SiteId == siteId && // 2019.07.12 y.WorkTypeId == (int)workTypeId select y.WorkRequestId where x.SiteId == siteId && x.OrderDate.Year == year && x.OrderDate.Month == month && x.OrderDate.Day == day && y.Contains(x.WorkRequestId) select x ).Count(); } private int GetWorkStatusByWorkTypeIdEtc() { return (from x in db.FmsWorkOrder let y = from y in db.FmsWorkRequest where y.SiteId == siteId && //2019.07.12 y.WorkTypeId >= (int)WorkTypeId.Etc1 select y.WorkRequestId where x.SiteId == siteId && x.OrderDate.Year == year && x.OrderDate.Month == month && x.OrderDate.Day == day && y.Contains(x.WorkRequestId) select x ).Count(); } private int GetWorkStatusByWorkProgressId(WorkProgressId workProgressId) { return (from x in db.FmsWorkOrder let y = from y in db.FmsWorkRequest where y.SiteId == siteId && //2019.07.12 y.WorkProgressId == (int)workProgressId && y.StartWorkDate <= new DateTime(year, month, day) select y.WorkRequestId where x.SiteId == siteId && y.Contains(x.WorkRequestId) select x ).Count(); } private int GetFmsWorkResultComplete(WorkProgressId workProgressId) { return (from x in db.FmsWorkResult let y = from y in db.FmsWorkRequest where y.SiteId == siteId && //2019.07.12 y.WorkProgressId == (int)workProgressId && y.StartWorkDate <= new DateTime(year, month, day) select y.WorkRequestId where x.SiteId == siteId && y.Contains(x.WorkRequestId) select x ).Count(); } private int GetFmsWorkResultApproval(WorkProgressId workProgressId) { return (from x in db.FmsWorkResult let y = from y in db.FmsWorkRequest where y.SiteId == siteId && //2019.07.12 y.WorkProgressId == (int)workProgressId select y.WorkRequestId where x.SiteId == siteId && y.Contains(x.WorkRequestId) && x.ConfirmedDate.Value.Year == year && x.ConfirmedDate.Value.Month == month && x.ConfirmedDate.Value.Day == day select x ).Count(); } /* 공통 업무 테이블 정보 추출 */ public List GetFmsWorkRequestForDailyReport() { var list = (from x in db.FmsWorkRequest let y = from y in db.FmsWorkResult where (y.IsConfirmed == false && (y.StartDate <= new DateTime(year, month, day))) || //&& (y.StartDate <= new DateTime(year, month, day))) 추가 (y.ConfirmedDate.Value.Year == year &&//ConfirmedDate.Value y.ConfirmedDate.Value.Month == month &&//ConfirmedDate.Value y.ConfirmedDate.Value.Day == day) && y.SiteId == siteId select y.WorkRequestId where (y.Contains(x.WorkRequestId) && x.BusinessFieldId == businessFieldId) || (x.WorkProgressId <= (int)WorkProgressId.WorkStatusComplete && x.WorkProgressId >= (int)WorkProgressId.WorkStatusInProgress && x.BusinessFieldId == businessFieldId) && (x.StartWorkDate <= new DateTime(year, month, day)) && x.SiteId == siteId orderby x.RequestDate, x.WorkProgressId ascending select new FmsWorkRequestForDailyReport { WorkRequestId = x.WorkRequestId, BusinessFieldId = x.BusinessFieldId, WorkProgressId = x.FmsWorkCodeProgress.Name, WorkTypeId = x.FmsWorkCodeType.Name, RequestDate = x.RequestDate, Title = x.Title }).ToList(); for (int i = 0; i < list.Count; i++) { list[i].No = i + 1; int tempWorkRequestId = list[i].WorkRequestId; ///////////////////////////////////////// db = new iBemsEntities(); var TextResultData = db.FmsWorkResultCheckItem.Where(x => x.SiteId == siteId && x.WorkRequestId == tempWorkRequestId) .ToList();//list[i].WorkRequestId if (TextResultData == null) { } else { using (var transaction = new TransactionScope()) { var j = 0; foreach (var TextData in TextResultData) { if (list[i].TextResult == null) list[i].TextResult = new String[TextResultData.Count]; if (list[i].TextResultDate == null) list[i].TextResultDate = new DateTime[TextResultData.Count]; list[i].TextResult[j] = TextData.TextResult; list[i].TextResultDate[j] = TextData.CheckDate; j++; } transaction.Complete(); } } } return list; } /* 정기/법정 테이블 정보 추출 */ /* 기타 테이블 정보 추출 */ public List GetFmsWorkOrderForDailyReport(WorkTypeId workTypeId1, WorkTypeId workTypeId2) { var list = (from x in db.FmsWorkOrder let y = from y in db.FmsWorkRequest where y.SiteId == siteId && (y.WorkTypeId == (int)workTypeId1 || y.WorkTypeId == (int)workTypeId2) select y.WorkRequestId let z = from z in db.FmsWorkResult where z.SiteId == siteId && z.ConfirmedDate.Value.Year == year && z.ConfirmedDate.Value.Month == month && z.ConfirmedDate.Value.Day == day select z.WorkRequestId where x.SiteId == siteId && x.BusinessFieldId == businessFieldId && y.Contains(x.WorkRequestId) && z.Contains(x.WorkRequestId) orderby x.OrderDate ascending select new FmsWorkOrderForDailyReport { OrderDate = x.OrderDate, Title = x.Title }).ToList(); for (int i = 0; i < list.Count; i++) { list[i].No = i + 1; } return list; } /* 자재사용현황 */ public List GetFmsMaterialForDailyReport() { var list = (from x in db.FmsMaterialRelease where x.SiteId == siteId && x.ReleaseDate.Year == year && x.ReleaseDate.Month == month && x.ReleaseDate.Day == day && x.BusinessFieldId == businessFieldId && (x.AdjustmentTypeId == (int)AdjustmentTypeId.Regular || x.AdjustmentTypeId == (int)AdjustmentTypeId.Loss) orderby x.ReleaseDate ascending select new FmsMaterialForDailyReport { ReleaseType = x.FmsMaterialCodeReleaseType.Name, //MaterialCode = x.FmsMaterial.MaterialCode, FirstClassId = x.FmsMaterial.FirstClassId, SecondClassId = x.FmsMaterial.SecondClassId, ThirdClassId = x.FmsMaterial.ThirdClassId, Standard = x.FmsMaterial.Standard, MaterialName = x.FmsMaterial.Name, WorkName = x.FmsWorkResult.FmsWorkOrder.Title, Unit = x.FmsMaterial.Unit, MaterialCount = (int)x.MaterialCount, WorkerName = x.FmsWorkResult.CmUser.Name }).ToList(); for (int i = 0; i < list.Count; i++) { list[i].No = i + 1; } return list; } } } public class WorkStatus { public int WorkStatusTypeOccasional { get; set; } public int WorkStatusTypePeriodicInspection { get; set; } public int WorkStatusTypeForensicExamination { get; set; } public int WorkStatusTypeEtc { get; set; } public int WorkStatusTypeTotal { get; set; } public int WorkStatusPending { get; set; } public int WorkStatusInProgress { get; set; } public int WorkStatusDeferred { get; set; } public int WorkStatusComplete { get; set; } public int WorkStatusApproved { get; set; } public int WorkStatusTotal { get; set; } } public class FmsEnergyDailyReport { //public int FacilityCode { get; set; } //public int PropertyId { get; set; } public String Name { get; set; } //public int FuelTypeId { get; set; } public string Today { get; set; } public string Daybefore { get; set; } public string Dailyusage { get; set; } public string Monthusage { get; set; } } public class FmsWorkRequestForDailyReport { public int No { get; set; } public int WorkRequestId { get; set; } public int BusinessFieldId { get; set; } public DateTime RequestDate { get; set; } public String WorkTypeId { get; set; } public String WorkProgressId { get; set; } public String Title { get; set; } public DateTime[] TextResultDate { get; set; } public String[] TextResult { get; set; } } public class FmsWorkOrderForDailyReport { public int No { get; set; } public DateTime OrderDate { get; set; } public String Title { get; set; } } public class FmsMaterialForDailyReport { public int No { get; set; } public String ReleaseType { get; set; } //public String MaterialCode { get; set; } public Nullable FirstClassId { get; set; } public Nullable SecondClassId { get; set; } public Nullable ThirdClassId { get; set; } public string StringFirstClassId { get; set; } public string StringSecondClassId { get; set; } public string StringThirdClassId { get; set; } public String MaterialName { get; set; } public String Standard { get; set; } public String WorkName { get; set; } public String Unit { get; set; } public int MaterialCount { get; set; } public String WorkerName { get; set; } } public class FmsMaterialForDailyReport2 { public int No { get; set; } public string ReleaseType { get; set; } public string StringFirstClassId { get; set; } public string StringSecondClassId { get; set; } public string StringThirdClassId { get; set; } public string MaterialName { get; set; } public string Standard { get; set; } public string WorkName { get; set; } public string Unit { get; set; } public string MaterialCount { get; set; } public string WorkerName { get; set; } } public class CurrentDate { public DateTime Date { get; set; } } }