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 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));
}
