Custom Search by Task Parameters: Time, UCRClearanceStatus, Task Type, & Task Assigned To
/**
Custom Search by Task
Parameters: Time, UCRClearanceStatus, Task Type, & Task Assigned To
**/
Main.ACCDomain = ValueSearchParameter ( 'pACCDomain' )
/** Searches ReportedTime field **/
AND ExpressionSearchParameter ( 'reportedtime', 'reportedtimetzv2g', 'Occurrence' ) = 1
AND ExpressionSearchParameter ( 'clearancestatus', 'ucrclearancestatusg', 'Occurrence' ) = 1
AND EXISTS (
SELECT Id FROM TaskSubjectGOccurrence
WHERE EXISTS (
SELECT Id FROM GTask
WHERE ExpressionSearchParameter ( 'tasktype', 'Type1G', 'GTask' ) = 1
AND EXISTS (
SELECT Id FROM TaskAssignedTo
WHERE ExpressionSearchParameter ('taskassignedto','RId','TaskAssignedTo') = 1
AND AutoLink ('GTask', 'TaskAssignedTo' ) = 1
)
AND AutoLink ( 'TaskSubjectGOccurrence','GTask' ) = 1
)
AND AutoLink ( 'main', 'TaskSubjectGOccurrence' ) = 1
)
AND EXISTS (
SELECT Id FROM GOccInvGPerson
WHERE EXISTS (
SELECT Id FROM GPerson
WHERE ExpressionSearchParameter ( 'badgeunitno', 'EmployeeNumber_cache', 'GPerson' ) = 1
AND AutoLink ( 'GOccInvGPerson', 'GPerson' ) = 1
)
AND AutoLink ( 'main', 'GOccInvGPerson' ) = 1
)
/***** Occurrence details and officer names (non-hierarchial) *****/
&columnlist&
Main.OccurrenceFileNoG AS INCIDENT,
Main.DispatchOccTypeG AS DISPATCH_TYPE,
Main.OccurrenceStdOccTypeRId_L AS OCCURRENCE_TYPE,
Main.ReportedTimeTZV2G AS REPORTED_TIME,
Main.UCRClearanceStatusG AS UCR_CLEARANCE_STATUS,
Main.UCRClearanceStatusDateG AS UCR_CLEARANCE_DATE,
TaskSubjectGOccurrence.StatusTaskG AS TASK_STATUS,
GTask.TaskNumber AS TASK_NUMBER,
GTask.DueTimeTZV2G AS TASK_DUE_DATE,
GTask.TaskAssignedToRId_L AS OFFICER
&endcolumnlist&
&joinlist&
LEFT JOIN ( TaskSubjectGOccurrence
LEFT JOIN GTask )
ON ChoiceHasProperty (TaskSubjectGOccurrence.StatusTask, 'cl_TaskStatus', 'clvIsNew', '1' ) = 1
OR ChoiceHasProperty (TaskSubjectGOccurrence.StatusTask, 'cl_TaskStatus', 'clvIsOpen', '1' ) = 1
OR ChoiceHasProperty (TaskSubjectGOccurrence.StatusTask, 'cl_TaskStatus', 'clvIsRework', '1' ) = 1
OR ChoiceHasProperty (TaskSubjectGOccurrence.StatusTask, 'cl_TaskStatus', 'clvIsSupervisorRework', '1' ) = 1
&endjoinlist&
&orderbylist&
GTask.TaskAssignedToRId_L,
GTask.DueTime
&endorderbylist&
&columnlist&
main.OccurrenceFileNoG,
main.DispatchOccTypeG,
main.OccurrenceStdOccTypeRId_L,
main.ReportedTimeTZV2G,
main.UCRClearanceStatusG,
task.Type1G(0),
task.StatusG(0)
&endcolumnlist&
&joinlist&
LEFT JOIN ( TaskSubjectGOccurrence
LEFT JOIN GTask task )
&endjoinlist&
&orderbylist&
&endorderbylist&
SELECT
Occurrence__Id,
Occurrence__OccurrenceFileNoG,
Occurrence__StartTimeTZV2G,
Occurrence__EndTimeTZV2G,
Occurrence__CreTimeDG,
Occurrence__OccurrenceStdOccTypeRId_L,
Occurrence__DispatchOccTypeG,
Occurrence__UCRClearanceStatusG,
PhysicalAddress__LabelAddressOnly__0,
PhysicalAddress__ESAreaLevel3G__0,
GTask__Id__0,
GTask__TaskNumber__0
FROM Occurrence
LEFT JOIN (GOccIvPA
LEFT JOIN PhysicalAddress
) ON GOccIvPA.IsESAreaLevelSource = 1
LEFT JOIN (TaskSubjectGoccurrence
LEFT JOIN GTask) ON TaskSubjectGOccurrence.RId = Occurrence.Id
SELECT
ni.Id FROM NIBRSIncident ni
WHERE EXISTS (
SELECT Id FROM Occurrence
WHERE ExpressionSearchParameter('reportedtime', 'ReportedTimeTZV2G', 'Occurrence') = 1
AND AutoLink('ni', 'Occurrence') = 1)
AND AutoLink('main', 'ni') = 1
FROM Occurrence o
LEFT JOIN NIBRSIncident ni
WHERE o.ReportedTimeTZV2I = '[20200301 00:00:00:000 CST, 20200310 00:00:00:000 CST]'
Main.ACCDomain = ValueSearchParameter('pACCDomain') AND
ExpressionSearchParameter('reportedtime', 'ReportedTimeTZV2G', 'Occurrence') = 1
AND EXISTS (
SELECT
ni.Id FROM NIBRSIncident ni
WHERE EXISTS (
SELECT Id FROM Occurrence
WHERE ExpressionSearchParameter('reportedtime', 'ReportedTimeTZV2G', 'Occurrence') = 1
AND AutoLink('ni', 'Occurrence') = 1)
AND AutoLink('main', 'ni') = 1
)