jma4227
11/22/2019 - 2:07 PM

Custom Search Queries

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
)