public ActionResult Edit(ServiceRequestsModels ServiceRequest) { if (ModelState.IsValid) { var RequestId = Convert.ToInt32(ServiceRequest.RequestType); var requesttype = db.tblServiceTypes.Where(x => x.Id == RequestId).FirstOrDefault()?.ServiceDescription; if (!string.IsNullOrEmpty(ServiceRequest.Area)) { var TblArea = new tblArea { FacilityId = ServiceRequest.FacilityId, BuildingId = ServiceRequest.BuildingId, FloorId = ServiceRequest.FloorId, AreaName = ServiceRequest.Area }; //TblArea.Id = db.tblAreas.Max(p => p.Id) + 1; db.tblAreas.Add(TblArea); db.SaveChanges(); ServiceRequest.AreaId = TblArea.Id; } if (!string.IsNullOrEmpty(ServiceRequest.Room)) { var TblRoom = new tblRoom { FacilityId = ServiceRequest.FacilityId, BuildingId = ServiceRequest.BuildingId, FloorId = ServiceRequest.FloorId, AreaId = ServiceRequest.AreaId, RoomName = ServiceRequest.Room }; //TblRoom.Id = db.tblRooms.Max(p => p.Id) + 1; db.tblRooms.Add(TblRoom); db.SaveChanges(); ServiceRequest.RoomId = TblRoom.Id; } var TblServiceRequest = db.tblServiceRequests.Where(x => x.ServiceRequestNumber == ServiceRequest.ServiceRequestNumber).FirstOrDefault(); tblServiceRequestStatu TBLStatus = new tblServiceRequestStatu(); TBLStatus.dtTimeStamp = DateTime.Now; TBLStatus.iServiceRequestId = ServiceRequest.Id; TBLStatus.iStatusId = db.TblServiceStates.Where(x => x.ServiceState == "Open").FirstOrDefault().id; db.tblServiceRequestStatus.Add(TBLStatus); TblServiceRequest.OccupantName = ServiceRequest.OccupantName; TblServiceRequest.ContactName = ServiceRequest.ContactName; TblServiceRequest.ContactNumber = ServiceRequest.ContactNumber; TblServiceRequest.ContactEmail = ServiceRequest.ContactEmail; TblServiceRequest.FacilityId = ServiceRequest.FacilityId; TblServiceRequest.BuildingId = ServiceRequest.BuildingId; TblServiceRequest.FloorId = ServiceRequest.FloorId; TblServiceRequest.AreaId = ServiceRequest.AreaId; TblServiceRequest.RoomId = ServiceRequest.RoomId; TblServiceRequest.RequestType = Convert.ToString(ServiceRequest.RequestType); TblServiceRequest.CategoryIdParentId = Convert.ToInt32(db.tblServiceTypes.Where(x => x.Id == ServiceRequest.RequestType).FirstOrDefault()?.ServiceType); TblServiceRequest.AssetGroupId = ServiceRequest.AssetGroupId; TblServiceRequest.CategoryId = ServiceRequest.CategoryId; TblServiceRequest.AssetTypeId = ServiceRequest.CategoryId; TblServiceRequest.RequestDescription = ServiceRequest.RequestDescription; TblServiceRequest.ISeniorTechId = ServiceRequest.ISeniorTechId ?? TblServiceRequest.ISeniorTechId; TblServiceRequest.Severity = ServiceRequest.Severity ?? (TblServiceRequest.Severity ?? 3); db.SaveChanges(); List <int> ResourcesList = new List <int>(); var WorkOrder = db.tblWorkOrders.Where(x => x.ServiceRequestId == TblServiceRequest.Id).FirstOrDefault(); WorkOrder.CategoryTypeId = ServiceRequest.CategoryId; WorkOrder.CategoryIdParentId = Convert.ToInt32(db.tblServiceTypes.Where(x => x.Id == ServiceRequest.RequestType).FirstOrDefault()?.ServiceType); var ExistingResources = db.tblWorkOrderResources.Where(x => x.WorkOrderId == WorkOrder.Id).Select(y => y.WorkforceId)?.ToList(); //Adding moddified reosurces in tblWorkOrderResource if (ServiceRequest.ResourceID != null) { var NewResources = ServiceRequest.ResourceID.ToList(); if (NewResources.Any()) { foreach (var resource in NewResources) { if (!ExistingResources.Contains(resource)) { var Tblworkorderresources = new tblWorkOrderResource { WorkforceId = resource, WorkOrderId = WorkOrder.Id }; var TblServiceRequestResources = new tblServiceRequestResource { ServiceRequestId = TblServiceRequest.Id, workforceId = resource }; ResourcesList.Add(resource); db.tblServiceRequestResources.Add(TblServiceRequestResources); db.tblWorkOrderResources.Add(Tblworkorderresources); } } foreach (var resource in ExistingResources) { if (!NewResources.Contains(Convert.ToInt32(resource))) { var Tblworkorderresources = db.tblWorkOrderResources.Where(x => x.WorkforceId == resource && x.WorkOrderId == WorkOrder.Id).FirstOrDefault(); var TblServiceRequestResources = db.tblServiceRequestResources.Where(x => x.workforceId == resource && x.ServiceRequestId == TblServiceRequest.Id).FirstOrDefault(); db.tblWorkOrderResources.Remove(Tblworkorderresources); db.tblServiceRequestResources.Remove(TblServiceRequestResources); } } db.SaveChanges(); } } foreach (var resource in ResourcesList) { if (resource != 0) { tblNotificationQueue notification = new tblNotificationQueue(); notification.ContactNumber = db.tblWorkForces.Where(x => x.Id == resource).FirstOrDefault().ContactNumber; notification.MessageBody = WorkOrder.WorkOrderRefNo + " " + ServiceRequest.RequestDescription + " " + ServiceRequest.ContactName + " " + ServiceRequest.ContactNumber + Convert.ToString(ServiceRequest.Severity ?? 3) + " " + db.tblBuildings.Where(x => x.Id == ServiceRequest.BuildingId).FirstOrDefault()?.BuildingName + "/" + db.tblFloors.Where(x => x.Id == ServiceRequest.FloorId).FirstOrDefault()?.FloorName + "/" + db.tblAreas.Where(x => x.Id == ServiceRequest.AreaId).FirstOrDefault()?.AreaName + "/" + db.tblRooms.Where(x => x.Id == ServiceRequest.RoomId).FirstOrDefault()?.RoomName; notification.IsSMSSent = false; notification.SMSSentDateTime = null; notification.FormRequestType = "WorkOrderUpdate"; notification.QueuedDate = DateTime.Now; db.tblNotificationQueues.Add(notification); } } db.SaveChanges(); //Adding files to table if (ServiceRequest.Files != null && ServiceRequest.Files.Any()) { foreach (var file in ServiceRequest.Files) { if (file != null && file.ContentLength > 0) { string newfilename = Path.GetFileNameWithoutExtension(file.FileName) + "_" + Guid.NewGuid() + "SREQ" + Path.GetExtension(file.FileName); string path = Path.Combine("C:\\Program Files (x86)\\Johnson Controls\\EFIRM\\ImagesHelpDesk\\ServiceRequest", newfilename); file.SaveAs(path); var TblServiceRequestImages = new tblServiceRequestImage { sImageName = newfilename, nServiceRequestId = TblServiceRequest.Id }; db.tblServiceRequestImages.Add(TblServiceRequestImages); } } db.SaveChanges(); } return(RedirectToAction("Index")); } ViewBag.Occupants = new SelectList(db.tblOccupants, "Id", "OccupantName"); ViewBag.Facilities = new SelectList(db.tblFacilities, "Id", "FacilityName"); ViewBag.Groups = new SelectList(db.tblAssetGroups, "Id", "AssetGroup"); ViewBag.RequestTypes = new SelectList(db.tblServiceTypes, "Id", "ServiceDescription"); ViewBag.Severities = new SelectList(db.tblSeverities, "Id", "SeverityLevel"); ViewBag.LeadResources = new SelectList(db.tblWorkForces, "Id", "PersonName"); return(View(ServiceRequest)); }
public ActionResult Edit(ServiceRequestsModels Model) { if (ModelState.IsValid) { var TblServiceReport = db.tblServiceReports.Where(x => x.WorkOrderId == Model.WorkOrderId)?.FirstOrDefault(); var tblServiceRequest = db.tblServiceRequests. Where(x => x.ServiceRequestNumber == Model.ServiceRequestNumber).FirstOrDefault(); var TblWorkOrder = db.tblWorkOrders.Where(x => x.ServiceRequestId == tblServiceRequest.Id)?.FirstOrDefault(); if (tblServiceReport == null) { var tblServiceReport = new tblServiceReport(); tblServiceReport.ContactNo = tblServiceRequest.ContactNumber; tblServiceReport.ContactEmail = tblServiceRequest.ContactEmail; tblServiceReport.FacilityId = tblServiceRequest.FacilityId; tblServiceReport.BuildingId = tblServiceRequest.BuildingId; tblServiceReport.FloorId = tblServiceRequest.FloorId; tblServiceReport.AreaId = tblServiceRequest.AreaId; tblServiceReport.RoomId = tblServiceRequest.RoomId; tblServiceReport.RequestType = tblServiceRequest.RequestType; tblServiceReport.AssetTypeId = tblServiceRequest.AssetTypeId; tblServiceReport.CategoryIdParentId = tblServiceRequest.CategoryIdParentId; tblServiceReport.ServiceReportNo = Model.ServiceRequestNumber; tblServiceReport.ReportDate = DateTime.Now; tblServiceReport.Status = "Acknowledged"; tblServiceReport.WorkOrderId = TblWorkOrder.Id; tblServiceReport.WORefNo = TblWorkOrder.WorkOrderRefNo; tblServiceRequest.Status = "Acknowledged"; TblWorkOrder.Status = "Acknowledged"; tblServiceRequest.AcknowledgeDate = DateTime.Now; tblServiceRequestStatu TBLStatus = new tblServiceRequestStatu(); TBLStatus.dtTimeStamp = DateTime.Now; TBLStatus.iServiceRequestId = tblServiceRequest.Id; TBLStatus.iServiceRequestStatusId = db.TblServiceStates.Where(x => x.ServiceState == "Acknowledged").FirstOrDefault().id; db.tblServiceRequestStatus.Add(TBLStatus); db.tblServiceReports.Add(tblServiceReport); db.SaveChanges(); TblServiceReport = tblServiceReport; } if (!string.IsNullOrEmpty(Model.DigitalSignatureImageData)) { Model.DigitalSignatureImageData = Model.DigitalSignatureImageData.Replace("data:image/png;base64,", String.Empty); string filename = DateTime.Now.Day.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString(); var UniqueNumber = Convert.ToString(Guid.NewGuid()); string path = Path.Combine("C:\\Program Files (x86)\\Johnson Controls\\EFIRM\\ImagesSignature", Path.GetFileName(filename + UniqueNumber) + ".png"); using (FileStream fs = new FileStream(path, FileMode.Create)) { using (BinaryWriter bw = new BinaryWriter(fs)) { byte[] data = Convert.FromBase64String(Model.DigitalSignatureImageData); bw.Write(data); bw.Close(); } } TblServiceReport.SignaturePath = filename + UniqueNumber + ".png"; TblServiceReport.Status = "Completed"; TblWorkOrder.Status = "Completed"; tblServiceRequest.Status = "Completed"; tblServiceRequest.ClosingDate = DateTime.Now; tblServiceRequestStatu TBLStatus = new tblServiceRequestStatu(); TBLStatus.dtTimeStamp = DateTime.Now; TBLStatus.iServiceRequestId = tblServiceRequest.Id; TBLStatus.iStatusId = db.TblServiceStates.Where(x => x.ServiceState == "Completed").FirstOrDefault().id; db.tblServiceRequestStatus.Add(TBLStatus); } TblServiceReport.Comments = Model.Comments; db.SaveChanges(); //Adding files to table if (Model.Files != null && Model.Files.Any()) { foreach (var file in Model.Files) { if (file != null && file.ContentLength > 0) { string newfilename = Path.GetFileNameWithoutExtension(file.FileName) + "_" + Guid.NewGuid() + "SREP" + Path.GetExtension(file.FileName); string path = Path.Combine("C:\\Program Files (x86)\\Johnson Controls\\EFIRM\\ImagesHelpDesk\\ServiceReport", newfilename); file.SaveAs(path); var TblServiceRequestImages = new tblServiceRequestImage { sImageName = newfilename, nServiceRequestId = tblServiceRequest.Id }; db.tblServiceRequestImages.Add(TblServiceRequestImages); } } db.SaveChanges(); } } return(RedirectToAction("Edit", new { WORefNo = Model.WorkOrderRef })); }
public ActionResult Create(ServiceRequestsModels ServiceRequest) { if (ModelState.IsValid) { var RequestId = Convert.ToInt32(ServiceRequest.RequestType); var requesttype = db.tblServiceTypes.Where(x => x.Id == RequestId).FirstOrDefault()?.ServiceDescription; if (!string.IsNullOrEmpty(ServiceRequest.Area)) { var TblArea = new tblArea { FacilityId = ServiceRequest.FacilityId, BuildingId = ServiceRequest.BuildingId, FloorId = ServiceRequest.FloorId, AreaName = ServiceRequest.Area }; //TblArea.Id = db.tblAreas.Max(p => p.Id) + 1; db.tblAreas.Add(TblArea); db.SaveChanges(); ServiceRequest.AreaId = TblArea.Id; } if (!string.IsNullOrEmpty(ServiceRequest.Room)) { var TblRoom = new tblRoom { FacilityId = ServiceRequest.FacilityId, BuildingId = ServiceRequest.BuildingId, FloorId = ServiceRequest.FloorId, AreaId = ServiceRequest.AreaId, RoomName = ServiceRequest.Room }; //TblRoom.Id = db.tblRooms.Max(p => p.Id) + 1; db.tblRooms.Add(TblRoom); db.SaveChanges(); ServiceRequest.RoomId = TblRoom.Id; } var TblServiceRequest = new tblServiceRequest { OccupantName = ServiceRequest.OccupantName, ContactName = ServiceRequest.ContactName, ContactNumber = ServiceRequest.ContactNumber, ContactEmail = ServiceRequest.ContactEmail, FacilityId = ServiceRequest.FacilityId, BuildingId = ServiceRequest.BuildingId, FloorId = ServiceRequest.FloorId, AreaId = ServiceRequest.AreaId, RoomId = ServiceRequest.RoomId, RequestType = Convert.ToString(ServiceRequest.RequestType), AssetGroupId = ServiceRequest.AssetGroupId, CategoryId = ServiceRequest.CategoryId, AssetTypeId = ServiceRequest.CategoryId, RequestDescription = ServiceRequest.RequestDescription, ISeniorTechId = ServiceRequest.ISeniorTechId, Severity = ServiceRequest.Severity ?? 3, WhoLogged = System.Web.HttpContext.Current.User.Identity.Name, Status = "Open", RequestDate = System.DateTime.Now, IsThroughService = false, CategoryIdParentId = Convert.ToInt32(db.tblServiceTypes.Where(x => x.Id == ServiceRequest.RequestType).FirstOrDefault()?.ServiceType) }; string ServiceRequestNumber = string.Empty; var LastServiceRequestNumber = (db.tblServiceRequests.Where(y => y.ServiceRequestNumber.Contains(requesttype)).OrderByDescending(x => x.Id).FirstOrDefault()?.ServiceRequestNumber).Split('/');; DateTime LastServiceRequestNumberDate = DateTime.ParseExact(LastServiceRequestNumber[2], "yyMMdd", CultureInfo.InvariantCulture); if (LastServiceRequestNumberDate.Year == DateTime.Today.Year && LastServiceRequestNumberDate.Month == DateTime.Today.Month) { ServiceRequestNumber = "SR/" + requesttype + "/" + DateTime.Today.ToString("yy") + DateTime.Now.Month.ToString("d2") + DateTime.Now.Day.ToString("d2") + "/" + (Convert.ToInt32(LastServiceRequestNumber[3]) + 1); } else { ServiceRequestNumber = "SR/" + requesttype + "/" + DateTime.Today.ToString("yy") + DateTime.Now.Month.ToString("d2") + DateTime.Now.Day.ToString("d2") + "/" + 1; } TblServiceRequest.ServiceRequestNumber = ServiceRequestNumber; db.tblServiceRequests.Add(TblServiceRequest); db.SaveChanges(); string WorkOrderRefNumber = string.Empty; var LastWorkOrderRefNumber = (db.tblWorkOrders.Where(y => y.WorkOrderRefNo.Contains(requesttype)).OrderByDescending(x => x.Id).FirstOrDefault()?.WorkOrderRefNo).Split('/');; DateTime LastWorkOrderRefNumberDate = DateTime.ParseExact(LastWorkOrderRefNumber[2], "yyMMdd", CultureInfo.InvariantCulture); var WorkOrderId = Convert.ToInt32(ServiceRequest.RequestType); if (LastWorkOrderRefNumberDate.Year == DateTime.Today.Year && LastWorkOrderRefNumberDate.Month == DateTime.Today.Month) { WorkOrderRefNumber = "WO/" + requesttype + "/" + DateTime.Today.ToString("yy") + DateTime.Now.Month.ToString("d2") + DateTime.Now.Day.ToString("d2") + "/" + (Convert.ToInt32(LastWorkOrderRefNumber[3]) + 1); } else { WorkOrderRefNumber = "WO/" + requesttype + "/" + DateTime.Today.ToString("yy") + DateTime.Now.Month.ToString("d2") + DateTime.Now.Day.ToString("d2") + "/" + 1; } var TblWorkOrders = new tblWorkOrder { WorkOrderRefNo = WorkOrderRefNumber, ServiceRequestId = TblServiceRequest.Id, WorkOrderDate = DateTime.Now, Status = "Open", RequesterId = Convert.ToInt32(Session["UserID"]), CategoryTypeId = ServiceRequest.CategoryId, IsThroughService = false, CategoryIdParentId = Convert.ToInt32(db.tblServiceTypes.Where(x => x.Id == ServiceRequest.RequestType).FirstOrDefault()?.ServiceType) }; db.tblWorkOrders.Add(TblWorkOrders); db.SaveChanges(); var NewServiceRequest = tblServiceRequest.Where(x => x.ServiceRequestNumber == ServiceRequestNumber)?.FirstOrDefault(); List <int> ResourcesList = new List <int>(); //Adding reosurces in tblWorkOrderResource if (ServiceRequest.ResourceID != null && ServiceRequest.ResourceID.Any()) { foreach (var resource in ServiceRequest.ResourceID) { ResourcesList.Add(resource); var Tblworkorderresources = new tblWorkOrderResource { WorkforceId = resource, WorkOrderId = TblWorkOrders.Id }; var TblServiceRequestResources = new tblServiceRequestResource { ServiceRequestId = NewServiceRequest.Id, workforceId = resource }; db.tblWorkOrderResources.Add(Tblworkorderresources); db.tblServiceRequestResources.Add(TblServiceRequestResources); } db.SaveChanges(); } else { //Service Request is not created from Admin mode so we need to so auto assign of resource var TblShifts = db.tblShifts?.ToArray(); var TblShiftRostering = db.tblShiftRosterings; var TblShiftException = db.tblShiftExceptions; var TimeNow = TimeSpan.Parse(DateTime.Now.ToString("HH:mm")); var ShiftNow = (TimeNow.Hours > 17 || TimeNow.Hours < 7) ? TblShifts[1] : TblShifts[0]; var ResouceonLeaveInCurrentShiftToday = TblShiftException.Where(x => x.ShiftId == ShiftNow.Id && x.FromDate <= DateTime.Now && x.ToDate >= DateTime.Now).Select(x => x.WorkForceId); foreach (var resource in ResouceonLeaveInCurrentShiftToday) { var res = db.tblShiftRosterings.FirstOrDefault(x => x.WorkForceId == resource); TblShiftRostering.Remove(res); } var dummy = TblShifts.ToList(); var LeastUsedResourceInCurrentShift = TblShiftRostering.Where(x => x.ShiftId == ShiftNow.Id).OrderBy(x => x.LastUsedDateTime).FirstOrDefault(); ResourcesList.Add(Convert.ToInt32(LeastUsedResourceInCurrentShift?.WorkForceId)); var Tblworkorderresources = new tblWorkOrderResource { WorkforceId = LeastUsedResourceInCurrentShift?.WorkForceId, WorkOrderId = TblWorkOrders.Id }; var TblServiceRequestResources = new tblServiceRequestResource { ServiceRequestId = NewServiceRequest.Id, workforceId = LeastUsedResourceInCurrentShift?.WorkForceId, }; db.tblServiceRequestResources.Add(TblServiceRequestResources); db.tblWorkOrderResources.Add(Tblworkorderresources); NewServiceRequest.ISeniorTechId = LeastUsedResourceInCurrentShift?.WorkForceId; LeastUsedResourceInCurrentShift.LastUsedDateTime = DateTime.Now; db.SaveChanges(); } foreach (var resource in ResourcesList) { if (resource != 0) { tblNotificationQueue notification = new tblNotificationQueue(); notification.ContactNumber = db.tblWorkForces.Where(x => x.Id == resource).FirstOrDefault().ContactNumber; notification.MessageBody = TblWorkOrders.WorkOrderRefNo + " " + ServiceRequest.RequestDescription + " " + ServiceRequest.ContactName + " " + ServiceRequest.ContactNumber + Convert.ToString(ServiceRequest.Severity ?? 3) + " " + db.tblBuildings.Where(x => x.Id == ServiceRequest.BuildingId).FirstOrDefault()?.BuildingName + "/" + db.tblFloors.Where(x => x.Id == ServiceRequest.FloorId).FirstOrDefault()?.FloorName + "/" + db.tblAreas.Where(x => x.Id == ServiceRequest.AreaId).FirstOrDefault()?.AreaName + "/" + db.tblRooms.Where(x => x.Id == ServiceRequest.RoomId).FirstOrDefault()?.RoomName; notification.IsSMSSent = false; notification.SMSSentDateTime = null; notification.QueuedDate = DateTime.Now; notification.FormRequestType = "WorkOrderAdd"; db.tblNotificationQueues.Add(notification); } } db.SaveChanges(); //Adding files to table if (ServiceRequest.Files != null && ServiceRequest.Files.Any()) { foreach (var file in ServiceRequest.Files) { if (file != null && file.ContentLength > 0) { string newfilename = Path.GetFileNameWithoutExtension(file.FileName) + "_" + Guid.NewGuid() + "SREQ" + Path.GetExtension(file.FileName); string path = Path.Combine("C:\\Program Files (x86)\\Johnson Controls\\EFIRM\\ImagesHelpDesk\\ServiceRequest", newfilename); file.SaveAs(path); var TblServiceRequestImages = new tblServiceRequestImage { sImageName = newfilename, nServiceRequestId = TblServiceRequest.Id }; db.tblServiceRequestImages.Add(TblServiceRequestImages); } } db.SaveChanges(); } tblServiceRequestStatu TBLStatus = new tblServiceRequestStatu(); TBLStatus.dtTimeStamp = DateTime.Now; TBLStatus.iServiceRequestId = NewServiceRequest.Id; TBLStatus.iStatusId = db.TblServiceStates.Where(x => x.ServiceState == "Open").FirstOrDefault().id; db.tblServiceRequestStatus.Add(TBLStatus); return(RedirectToAction("Index", new { ServiceRequestCreated = true })); } ViewBag.Occupants = new SelectList(db.tblOccupants, "Id", "OccupantName"); ViewBag.Facilities = new SelectList(db.tblFacilities, "Id", "FacilityName"); ViewBag.Groups = new SelectList(db.tblAssetGroups, "Id", "AssetGroup"); ViewBag.RequestTypes = new SelectList(db.tblServiceTypes, "Id", "ServiceDescription"); ViewBag.Severities = new SelectList(db.tblSeverities, "Id", "SeverityLevel"); ViewBag.LeadResources = new SelectList(db.tblWorkForces, "Id", "PersonName"); return(View(ServiceRequest)); }