private string getResourcesQueryForAdditionalObjects(AppointmentBookingRequest i_Request) { DateTime start = m_Start.AddDays(-1).Date; DateTime finish = m_Finish.AddDays(2).Date; string resourceIds = formatIdList(m_ABData.CandidatesById.Keys.ToList()); String innerAbsencesQuery = "(select Id, Estimated_Travel_Time_To_Source__c,Estimated_Travel_Time_From_Source__c," + "Type, RecordType.DeveloperName, AbsenceNumber, Start, EstTravelTimeFrom__c, " + "EstTravelTime__c, EstimatedTravelDistanceFrom__c, EstimatedTravelDistanceTo__c, " + "ResourceId, End,Latitude, Longitude, InternalSLRGeolocation__Latitude__s, " + "InternalSLRGeolocation__Longitude__s From ResourceAbsences"; innerAbsencesQuery += $" where ((Start >= {formatDate(start)} and Start <= {formatDate(finish)})or(End >= {formatDate(start)} and End <= {formatDate(finish)}) " + $"or (Start < {formatDate(start)} and End > {formatDate(finish)})) and RecordType.DeveloperName in ( \'Non_Availability\' ) "; if (i_Request.ApprovedAbsences) { innerAbsencesQuery += " and ( ( Approved__c = true and RecordType.DeveloperName = \'Non_Availability\')OR RecordType.DeveloperName = \'Break\')"; } innerAbsencesQuery += " order by Start, End)"; String innerShiftsQuery = "(SELECT Id, StartTime, EndTime, ServiceResourceId, ServiceTerritoryId, StatusCategory FROM ShiftServiceResources WHERE (" + $"(StartTime >= {formatDate(start)} AND StartTime <= {formatDate(finish)}) " + " OR " + $"(EndTime >= {formatDate(start)} AND EndTime <= {formatDate(finish)}) " + " OR " + $"(StartTime <= {formatDate(start)} AND EndTime > {formatDate(finish)}) " + ")" + "Order by StartTime)"; String externalQuery = "SELECT Id, " + innerAbsencesQuery + "," + innerShiftsQuery + $" FROM ServiceResource WHERE Id in ({resourceIds})"; return(externalQuery); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { string query; string serviceIdStr = formatIdList(new List <string>() { i_Request.ServiceID }); if (m_TimeDependencies == null) { query = "SELECT Id, Root_Service_Appointment__c, Dependency__c, Same_Resource__c, Service_Appointment_1__c, " + "Service_Appointment_2__c FROM Time_Dependency__c " + $"WHERE Service_Appointment_1__c = '{i_Request.ServiceID}' OR Service_Appointment_2__c = '{i_Request.ServiceID}'"; } else if (m_TimeDependencies.Any()) { query = "SELECT Id, Root_Service_Appointment__c, Dependency__c, Same_Resource__c, Service_Appointment_1__c, " + $"Service_Appointment_2__c FROM Time_Dependency__c WHERE Root_Service_Appointment__c IN ({serviceIdStr})"; } else { return(null); } return(formatQueryString(query)); }
private static SFDCScheduleRequest ParseRequestString(string i_RequestBody) { if (string.IsNullOrWhiteSpace(i_RequestBody)) { throw new ArgumentNullException("i_RequestBody"); } XmlDocument requestXML = new XmlDocument(); requestXML.LoadXml(i_RequestBody); //TODO: for testing the regular BGO optimization request XML is used //TODO: in the future, a different XML should be used with only the relevant info to access SF Type requestType = typeof(AppointmentBookingRequest); XmlSerializer xmlSerializer = new XmlSerializer(requestType); object deserializedRequest; using (MemoryStream memoryStream = new MemoryStream()) { using (StreamWriter writer = new StreamWriter(memoryStream)) { writer.Write(i_RequestBody); writer.Flush(); memoryStream.Position = 0; deserializedRequest = xmlSerializer.Deserialize(memoryStream); } } AppointmentBookingRequest optimizationRequest = deserializedRequest as AppointmentBookingRequest; return(optimizationRequest); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { ServiceParent parent = null; foreach (ServiceParent parentObj in m_ABData.ServiceParent.Values) { if (parentObj.Id == m_ABData.ServiceToSchedule.ParentRecordId) { parent = parentObj; break; } } if (parent == null) { throw new Exception("Service parent was not retrieved properly"); } //TODO: make generic for all types of parent string visitingHoursCalId = parent.VisitingHours__c; if (visitingHoursCalId == null) { return(String.Empty); } string query = "Select id,TimeZone, " + "(Select DayOfWeek, Type, StartTime, EndTime From TimeSlots order by DayOfWeek, StartTime) " + $"From OperatingHours where id = '{visitingHoursCalId}' limit 1]"; return(formatQueryString(query)); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { //TODO: WO priority and WOLI priority fields need to be queried. Field names are in the Logic settings string query = $"select id, MinimumCrewSize, scheduling_priority__c {addRequiredFiledsByRulesAndObjectives()} from WorkOrder where Id = '{m_ABData.ServiceToSchedule.ParentRecordId}'"; query = formatQueryString(query); return(query); }
private string getCalendarsQuery(AppointmentBookingRequest iRequest) { string calendarIds = formatIdList(m_CalendarIDs.ToList()); string query = "Select Id, TimeZone, (Select Designated_Work_Boolean_Fields__c,StartTime, EndTime, " + "DayOfWeek, Type From TimeSlots order by DayOfWeek, StartTime) " + $"From OperatingHours where id in ({calendarIds})"; return(query); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { string rulesQuery = "SELECT Maximum_Travel_From_Home__c,Maximum_Travel_From_Home_Type__c,Id,Enable_Overtime__c,Name,Object_Group_Field__c, Resource_Property__c" + ",Resource_Group_Field__c, RecordType.DeveloperName ,Start_of_Day__c,Break_Start__c,Break_Duration__c,Is_Fixed_Gap__c,Minimum_Gap__c," + $" {addRRequiredRuleFields()} FROM Work_Rule__c " + $"WHERE Id in (SELECT Work_Rule__c FROM Scheduling_Policy_Work_Rule__c WHERE Scheduling_Policy__c = '{i_Request.PolicyId}')"; return(formatQueryString(rulesQuery)); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { string objectivesQuery = "SELECT Service_Goal__r.Ignore_Home_Base_Coordinates__c,Service_Goal__r.Resource_Priority_Field__c," + "Service_Goal__r.Prioritize_Resource__c,Service_Goal__r.Object_Group_Field__c, Service_Goal__r.Resource_Group_Field__c," + "Weight__c, Service_Goal__r.RecordType.DeveloperName, Service_Goal__r.ID, Service_Goal__r.Name FROM Scheduling_Policy_Goal__c " + $"WHERE Scheduling_Policy__c = '{i_Request.PolicyId}' " + "AND (NOT Service_Goal__r.RecordType.DeveloperName = 'Objective_Custom_Logic') order by Weight__c DESC"; return(formatQueryString(objectivesQuery)); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { string serviceIdStr = formatIdList(m_MSTServiceIds.ToList()); string query = "SELECT Id,Related_Service__c,Same_Day__c,Same_Resource__c,Time_Dependency__c," + "AppointmentNumber,SchedStartTime, SchedEndTime,ServiceTerritory.OperatingHours.TimeZone, " + "ServiceTerritoryId, Duration, ParentRecordId, (SELECT AssignedResourceNumber" + ",ServiceResourceId,EstimatedTravelTimeFrom__c,EstimatedTravelTime,EstimatedTravelDistanceFrom__c," + " EstimatedTravelDistanceTo__c FROM ServiceResources ORDER BY CreatedDate ASC) " + $"FROM ServiceAppointment WHERE Id in ({serviceIdStr})"; return(formatQueryString(query)); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { ServiceAppointment service = m_ABData.ServiceToSchedule; DateTime minDate = service.EarliestStartTime.Value.AddDays(-7); DateTime maxDate = service.DueDate.Value.AddDays(7); string resourcesIds = formatIdList(m_ABData.STMResourcesIDs.ToList()); string query = "select Id, name, IsActive, IsCapacityBased, Efficiency__c, Travel_Speed__c, " + "RelatedRecordId, ResourceType, ServiceCrewId, (select ServiceResourceId," + "StartDate,EndDate,ServiceCrewId from ServiceCrewMembers " + $"where ((StartDate <= {formatDate(maxDate)}) AND (EndDate = null OR EndDate >= {formatDate(minDate)}))) {addRequiredFields(minDate, maxDate)}" + $" from ServiceResource Where IsActive = true And Id in ({resourcesIds})"; return(formatQueryString(query)); }
private string getCapacitiesQuery(AppointmentBookingRequest iRequest) { string start = m_Start.Date.ToString("yyyy-MM-dd"); string finish = m_Finish.Date.ToString("yyyy-MM-dd"); string query = "Select CapacityInHours,CapacityInWorkItems,TimePeriod,EndDate,MinutesUsed__c,HoursInUse__c" + ",StartDate,ServiceResourceId,Work_Items_Allocated__c From ServiceResourceCapacity " + $"where ((StartDate >= {start} " + $"AND StartDate <= {finish} AND TimePeriod = 'Day')" + $" or (StartDate >= {start}" + $" AND StartDate <= {finish} AND TimePeriod = 'Month') " + $"or (StartDate >= {start}" + $" AND StartDate <= {finish} AND TimePeriod = 'Week'))" + $" and (ServiceResourceId in ({m_Resources}))"; return(query); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { m_RelatedRecordIds = new HashSet <string>(); foreach (string resourceId in m_ABData.STMResourcesIDs) { if (m_ABData.CandidatesById.ContainsKey(resourceId)) { m_RelatedRecordIds.Add(m_ABData.CandidatesById[resourceId].UserId); } } string query = $"Select AssigneeId From PermissionSetAssignment Where AssigneeId in ({formatIdList(m_RelatedRecordIds.ToList())}) " + "and PermissionSet.IsOwnedByProfile = false and" + " PermissionSet.PermissionsFieldServiceScheduling = true"; return(formatQueryString(query)); }
private string getResourceTerritoriesQuery(AppointmentBookingRequest i_Request) { DateTime start = m_Start.AddDays(-1).Date; DateTime finish = m_Finish.AddDays(2).Date; string query = "Select Internal_SLR_HomeAddress_Geolocation__Latitude__s," + "Internal_SLR_HomeAddress_Geolocation__Longitude__s," + "ServiceTerritory.Internal_SLR_Geolocation__Latitude__s," + "ServiceTerritory.Internal_SLR_Geolocation__Longitude__s," + "ServiceTerritory.Longitude,ServiceTerritory.Latitude, Latitude, Longitude," + "OperatingHoursId,TerritoryType, ServiceResourceId, ServiceTerritory.OperatingHoursId," + " EffectiveEndDate, EffectiveStartDate, ServiceTerritory.OperatingHours.TimeZone " + $"From ServiceTerritoryMember where ServiceResourceId in ({m_Resources}) and " + $"((EffectiveStartDate >= {formatDate(start)} and EffectiveStartDate <= {formatDate(finish)}) or " + $"(EffectiveEndDate >= {formatDate(start)} and EffectiveEndDate <= {formatDate(finish)}) or " + $"((EffectiveStartDate < {formatDate(start)} or EffectiveStartDate = null) and" + $" (EffectiveEndDate > {formatDate(finish)} or EffectiveEndDate = null)))"; return(query); }
public override string getQuery(AppointmentBookingRequest i_Request = null, eAdditionalObjectQuery i_AdditionalObjQuery = default) { ServiceAppointment service = m_ABData.ServiceToSchedule; m_Start = service.EarliestStartTime.Value; m_Finish = service.DueDate.Value; calculateHorizonByMaxDaysSearchSlot(service, DateTime.Now, i_Request.SearchSlotsMaxDays, out m_Start, out m_Finish); string query; switch (i_AdditionalObjQuery) { case eAdditionalObjectQuery.ServicesInResourcesTimeDomain: query = getServicesInResourcesTimeDomainQuery(); break; case eAdditionalObjectQuery.ResourcesAdditionalObjects: query = getResourcesQueryForAdditionalObjects(i_Request); break; case eAdditionalObjectQuery.ResourceTerritories: query = getResourceTerritoriesQuery(i_Request); break; case eAdditionalObjectQuery.Capacities: query = getCapacitiesQuery(i_Request); break; case eAdditionalObjectQuery.Calendars: query = getCalendarsQuery(i_Request); break; default: query = String.Empty; break; } return(formatQueryString(query)); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { string serviceIdStr = formatIdList(new List <string>() { i_Request.ServiceID }); string query = "SELECT id,status,related_service__c,same_day__c,same_resource__c,time_dependency__c,appointmentnumber" + ",duedate,earlieststarttime,schedstarttime,schedendtime,duration,durationtype,latitude,longitude," + "internalslrgeolocation__latitude__s,internalslrgeolocation__longitude__s,serviceterritoryid," + "schedule_over_lower_priority_appointment__c,use_async_logic__c,mdt_operational_time__c,ismultiday__c" + ",parentrecordid, parentrecordtype,serviceterritory.operatinghours.timezone," + "(SELECT estimated_travel_time_to_source__c," + "estimated_travel_time_from_source__c,assignedresourcenumber,serviceresourceid,estimatedtraveltimefrom__c," + "estimatedtraveltime,estimatedtraveldistancefrom__c,estimatedtraveldistanceto__c,serviceresource.servicecrewid," + "serviceresource.resourcetype,serviceresource.iscapacitybased" + " FROM serviceresources ORDER BY serviceresource.resourcetype desc nulls last, createddate asc nulls last)" + ",(SELECT id FROM service_appointments__r),mds_calculated_length__c {0}" + $" FROM ServiceAppointment WHERE id in ({serviceIdStr})"; /* * query = * "select id,status,related_service__c,same_day__c,same_resource__c,time_dependency__c,appointmentnumber,duedate" + * ",earlieststarttime,schedstarttime,schedendtime,duration,durationtype,latitude,longitude," + * "internalslrgeolocation__latitude__s,internalslrgeolocation__longitude__s,serviceterritoryid," + * "schedule_over_lower_priority_appointment__c,use_async_logic__c,mdt_operational_time__c," + * "ismultiday__c,parentrecordid,serviceterritory.operatinghours.timezone," + * "(select estimated_travel_time_to_source__c,estimated_travel_time_from_source__c,assignedresourcenumber," + * "serviceresourceid,estimatedtraveltimefrom__c,estimatedtraveltime,estimatedtraveldistancefrom__c," + * "estimatedtraveldistanceto__c,serviceresource.servicecrewid,serviceresource.resourcetype,serviceresource.iscapacitybased " + * "from serviceresources order by serviceresource.resourcetype desc nulls last, createddate asc nulls last)" + * ",(select id from service_appointments__r),arrivalwindowstarttime,arrivalwindowendtime," + * $"mds_calculated_length__c from ServiceAppointment where id in ({serviceIdStr})"; */ string requiredRuleFields = addRRequiredRuleFields(query); query = string.Format(query, requiredRuleFields); return(formatQueryString(query)); }
public override string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default) { m_Request = i_Request; if (doAdditionalMembersQuery) { return(getAdditionalMembersQuery()); } string queryStr = "SELECT ServiceResource.Id, ServiceResource.ResourceType, ServiceResource.ServiceCrewId, ServiceResource.Travel_Speed__c" + ",ServiceResource.IsCapacityBased,ServiceResource.IsActive,ServiceResource.Name," + "ServiceTerritory.Address,ServiceTerritory.Internal_SLR_Geolocation__c,ServiceTerritory.Longitude" + ",ServiceTerritory.Latitude,Id,ServiceResourceId,ServiceTerritory.OperatingHours.TimeZone" + ",ServiceTerritory.Internal_SLR_Geolocation__Latitude__s,ServiceTerritory.Internal_SLR_Geolocation__Longitude__s" + ",OperatingHoursId,OperatingHours.TimeZone,EffectiveEndDate,EffectiveStartDate,Latitude,Longitude," + "Internal_SLR_HomeAddress_Geolocation__Latitude__s,Internal_SLR_HomeAddress_Geolocation__Longitude__s" + ",Internal_SLR_HomeAddress_Geolocation__c,TerritoryType,ServiceTerritoryId,Address"; m_AdditionalMembersQuery = "Select ServiceResource.Id, ServiceResource.IsActive,ServiceResource.Name,ServiceTerritory.Internal_SLR_Geolocation__c," + "ServiceTerritory.Longitude,ServiceTerritory.Latitude,Id,ServiceResourceId," + "ServiceTerritory.OperatingHours.TimeZone,ServiceTerritory.Internal_SLR_Geolocation__Latitude__s," + "ServiceTerritory.Internal_SLR_Geolocation__Longitude__s,OperatingHoursId,OperatingHours.TimeZone," + "EffectiveEndDate,EffectiveStartDate,Latitude,Longitude,Internal_SLR_HomeAddress_Geolocation__Latitude__s," + "Internal_SLR_HomeAddress_Geolocation__Longitude__s,Internal_SLR_HomeAddress_Geolocation__c," + "TerritoryType,ServiceTerritoryId "; HashSet <String> relevanceFields = new HashSet <String>(); ServiceAppointment service = m_ABData.ServiceToSchedule; string notPartOfRelevenceGroupStr = "("; bool atLeastOneLocationRuleIsActive = false; bool travelRuleIsActive = false; double?travelDistance = null; double?latestUpdatedTravelDistance; bool addedOneOfTheRulesToTheQuery = false; bool serviceHasGeo = service.Latitude != null && service.Longitude != null; List <Work_Rule__c> enhancedRules = new List <Work_Rule__c>(); List <Work_Rule__c> rulesListFileteredBySARelevance = new List <Work_Rule__c>(); string territoryId = m_ABData.ServiceToSchedule.ServiceTerritory.Id; rulesListFileteredBySARelevance = initiateRulesWithRelvanceGroupList(m_ABData.RulesByDevName, service); foreach (Work_Rule__c rule in rulesListFileteredBySARelevance) { string resGroupFieldValue = rule.Resource_Group_Field__c; if (rule.DeveloperName.Equals("Match_Location_Service") || rule.DeveloperName.Equals("Field_Working_Locations")) { atLeastOneLocationRuleIsActive = true; } if (serviceHasGeo && rule.DeveloperName.Equals("Max_Travel_From_Home_To_Service") && rule.Maximum_Travel_From_Home_Type__c.Equals("Distance") && rule.Maximum_Travel_From_Home__c != null) { travelRuleIsActive = true; latestUpdatedTravelDistance = (i_Request.TravelUnit == eTravelUnit.Miles.ToString()) ? (rule.Maximum_Travel_From_Home__c * MILE_TO_KM_RATIO) : rule.Maximum_Travel_From_Home__c; if (travelDistance == null || travelDistance < latestUpdatedTravelDistance) { travelDistance = latestUpdatedTravelDistance; travelDistance++; } } if (!string.IsNullOrEmpty(resGroupFieldValue)) { relevanceFields.Add(resGroupFieldValue.ToLower()); if (rule.DeveloperName.Equals("Match_Location_Service") || rule.DeveloperName.Equals("Field_Working_Locations") || (rule.DeveloperName.Equals("Max_Travel_From_Home_To_Service") && rule.Maximum_Travel_From_Home_Type__c.Equals("Distance"))) { if (notPartOfRelevenceGroupStr != "(") { notPartOfRelevenceGroupStr += " AND "; } notPartOfRelevenceGroupStr += resGroupFieldValue.ToLower() + " = false"; } } if (rule.DeveloperName.Equals("Enhanced_Match_Service")) { enhancedRules.Add(rule); } } if (notPartOfRelevenceGroupStr != "(") { notPartOfRelevenceGroupStr += $" AND TerritoryType != \'S\' AND ServiceTerritoryId = {territoryId} )"; } else { notPartOfRelevenceGroupStr += ")"; } foreach (KeyValuePair <string, List <Service_Goal__c> > objByDevName in m_ABData.ObjectivesByDevName) { foreach (Service_Goal__c obj in objByDevName.Value) { String objGroupFieldValue = obj.Resource_Group_Field__c; if (!string.IsNullOrEmpty(objGroupFieldValue)) { relevanceFields.Add(objGroupFieldValue.ToLower()); } } } if (relevanceFields.Any()) { foreach (String field in relevanceFields) { queryStr += "," + field; m_AdditionalMembersQuery += "," + field; } } calculateHorizonByMaxDaysSearchSlot(service, m_DateTimeNow, i_Request.SearchSlotsMaxDays, out m_Start, out m_Finish); notPartOfRelevenceGroupStr = notPartOfRelevenceGroupStr != "()" ? " OR " + notPartOfRelevenceGroupStr : string.Empty; queryStr += " FROM ServiceTerritoryMember WHERE "; if (atLeastOneLocationRuleIsActive && territoryId != null) { addedOneOfTheRulesToTheQuery = true; if (travelRuleIsActive) { queryStr += $"(( ServiceTerritoryId = '{territoryId}' And "; } else { queryStr += $"( ServiceTerritoryId = '{territoryId}' "; } } if (travelRuleIsActive) { if (!addedOneOfTheRulesToTheQuery) { queryStr += "("; } queryStr += $" (TerritoryType = \'S\' Or ( Latitude <> null And Latitude <> 0 And Longitude <> null And Longitude <> 0 And (DISTANCE(Address, GEOLOCATION(' + {service.Latitude} + ',' + {service.Longitude} + '),\'km\') < ' + travelDistance + ') ) OR "; queryStr += $" ( ServiceTerritory.Latitude <> null And ServiceTerritory.Latitude <> 0 And ServiceTerritory.Longitude <> null And ServiceTerritory.Longitude <> 0 And (DISTANCE(ServiceTerritory.Address, GEOLOCATION(' + {service.Latitude} + ',' + {service.Longitude} + '),\'km\') < ' + travelDistance + ') ))"; if (addedOneOfTheRulesToTheQuery) { queryStr += ')'; } addedOneOfTheRulesToTheQuery = true; } if (addedOneOfTheRulesToTheQuery) { queryStr += notPartOfRelevenceGroupStr + ") AND "; } queryStr += $"( NOT ((EffectiveStartDate <> NULL AND EffectiveStartDate > {formatDate(m_Finish)}) OR " + $"(EffectiveEndDate <> NULL AND EffectiveEndDate < {formatDate(m_Start)})) ) And ServiceResource.IsActive = true"; //TODO: implement for Enhanced Match Rule /* * if(enhancedRules != null && enhancedRules.Count > 0) * { * List<string> queriesToFilterResources = getQueriesToFilterResourcesAcoordingToEnhancedRules(service, enhancedRules); * foreach(string queryToFilterResource in queriesToFilterResources){ * queryStr += queryToFilterResource; * } * } */ doAdditionalMembersQuery = true; queryStr = formatQueryString(queryStr); return(queryStr); }
public DataProcessor(FSLClient i_FSLClient, SFDCScheduleRequest i_Request) { m_FSLClient = i_FSLClient; m_Request = i_Request as AppointmentBookingRequest; m_ABData = new AppointmentBookingData(m_Request); }
public abstract string getQuery(AppointmentBookingRequest i_Request = null, AdditionalObjectsUtils.eAdditionalObjectQuery i_AdditionalObjQuery = default);