-- TaskFacadeEjbTest.testFilterTasksByUserJurisdiction -> 2b. Region user now sees tasks from district level

SELECT DISTINCT task0_.uuid AS col_0_0_,
                task0_.taskContext AS col_1_0_,
                case1_.uuid AS col_2_0_,
                person2_.firstName AS col_3_0_,
                person2_.lastName AS col_4_0_,
                event24_.uuid AS col_5_0_,
                event24_.eventTitle AS col_6_0_,
                event24_.disease AS col_7_0_,
                event24_.diseaseDetails AS col_8_0_,
                event24_.eventStatus AS col_9_0_,
                event24_.eventInvestigationStatus AS col_10_0_,
                event24_.startDate AS col_11_0_,
                contact11_.uuid AS col_12_0_,
                person12_.firstName AS col_13_0_,
                person12_.lastName AS col_14_0_,
                person18_.firstName AS col_15_0_,
                person18_.lastName AS col_16_0_,
                travelentr33_.uuid AS col_17_0_,
                travelentr33_.externalId AS col_18_0_,
                person37_.firstName AS col_19_0_,
                person37_.lastName AS col_20_0_,
                task0_.taskType AS col_21_0_,
                task0_.priority AS col_22_0_,
                task0_.dueDate AS col_23_0_,
                task0_.suggestedStart AS col_24_0_,
                task0_.taskStatus AS col_25_0_,
                CASE
                    WHEN case1_.id IS NOT NULL THEN case1_.disease
                    WHEN contact11_.id IS NOT NULL THEN contact11_.disease
                    WHEN event24_.id IS NOT NULL THEN event24_.disease
                    ELSE travelentr33_.disease
                END AS col_26_0_,
                user40_.uuid AS col_27_0_,
                user40_.firstName AS col_28_0_,
                user40_.lastName AS col_29_0_,
                task0_.creatorComment AS col_30_0_,
                user41_.uuid AS col_31_0_,
                user41_.firstName AS col_32_0_,
                user41_.lastName AS col_33_0_,
                task0_.assigneeReply AS col_34_0_,
                CASE
                    WHEN region4_.id IS NOT NULL THEN region4_.name
                    ELSE CASE
                             WHEN region5_.id IS NOT NULL THEN region5_.name
                             ELSE CASE
                                      WHEN region14_.id IS NOT NULL THEN region14_.name
                                      ELSE CASE
                                               WHEN region26_.id IS NOT NULL THEN region26_.name
                                               ELSE region34_.name
                                           END
                                  END
                         END
                END AS col_35_0_,
                CASE
                    WHEN district6_.id IS NOT NULL THEN district6_.name
                    ELSE CASE
                             WHEN district7_.id IS NOT NULL THEN district7_.name
                             ELSE CASE
                                      WHEN district15_.id IS NOT NULL THEN district15_.name
                                      ELSE CASE
                                               WHEN district27_.id IS NOT NULL THEN district27_.name
                                               ELSE district35_.name
                                           END
                                  END
                         END
                END AS col_36_0_,
                CASE
                    WHEN community8_.id IS NOT NULL THEN community8_.name
                    ELSE CASE
                             WHEN community9_.id IS NOT NULL THEN community9_.name
                             ELSE CASE
                                      WHEN community16_.id IS NOT NULL THEN community16_.name
                                      ELSE CASE
                                               WHEN community28_.id IS NOT NULL THEN community28_.name
                                               ELSE community36_.name
                                           END
                                  END
                         END
                END AS col_37_0_,
                CASE
                    WHEN (user40_.id IS NOT NULL)
                         AND user40_.uuid='WVSTX2-WKOL33-LQGNTW-7X7OSE7M'
                         OR (user41_.id IS NOT NULL)
                         AND user41_.uuid='WVSTX2-WKOL33-LQGNTW-7X7OSE7M'
                         OR 0=1
                         OR (case1_.reportingUser_id IS NOT NULL)
                         AND case1_.reportingUser_id=64
                         OR case1_.responsibleRegion_id=59
                         OR case1_.region_id=59
                         OR (contact11_.reportingUser_id IS NOT NULL)
                         AND contact11_.reportingUser_id=64
                         OR contact11_.region_id=59
                         OR case19_.responsibleRegion_id=59
                         OR case19_.region_id=59
                         OR (event24_.reportingUser_id IS NOT NULL)
                         AND event24_.reportingUser_id=64
                         OR (event24_.responsibleUser_id IS NOT NULL)
                         AND event24_.responsibleUser_id=64
                         OR location29_.region_id=59 THEN TRUE
                    ELSE FALSE
                END AS col_38_0_,
                CASE
                    WHEN (case1_.id IS NOT NULL)
                         AND ((case1_.reportingUser_id IS NOT NULL)
                              AND case1_.reportingUser_id=64
                              OR case1_.responsibleRegion_id=59
                              OR case1_.region_id=59) THEN TRUE
                    ELSE FALSE
                END AS col_39_0_,
                CASE
                    WHEN (contact11_.id IS NOT NULL)
                         AND ((contact11_.reportingUser_id IS NOT NULL)
                              AND contact11_.reportingUser_id=64
                              OR contact11_.region_id=59
                              OR case20_.responsibleRegion_id=59
                              OR case20_.region_id=59
                              OR (case20_.reportingUser_id IS NOT NULL)
                              AND case20_.reportingUser_id=64
                              OR case20_.responsibleRegion_id=59
                              OR case20_.region_id=59) THEN TRUE
                    ELSE FALSE
                END AS col_40_0_,
                CASE
                    WHEN (contact11_.id IS NOT NULL)
                         AND (case21_.id IS NOT NULL)
                         AND ((case21_.reportingUser_id IS NOT NULL)
                              AND case21_.reportingUser_id=64
                              OR case21_.responsibleRegion_id=59
                              OR case21_.region_id=59) THEN TRUE
                    ELSE FALSE
                END AS col_41_0_,
                CASE
                    WHEN (event24_.id IS NOT NULL)
                         AND ((event24_.reportingUser_id IS NOT NULL)
                              AND event24_.reportingUser_id=64
                              OR (event24_.responsibleUser_id IS NOT NULL)
                              AND event24_.responsibleUser_id=64
                              OR location30_.region_id=59) THEN TRUE
                    ELSE FALSE
                END AS col_42_0_,
                CASE
                    WHEN (travelentr33_.id IS NOT NULL)
                         AND ((travelentr33_.reportingUser_id IS NOT NULL)
                              AND travelentr33_.reportingUser_id=64
                              OR travelentr33_.responsibleRegion_id=59
                              OR travelentr33_.pointOfEntryRegion_id=59
                              OR (case38_.reportingUser_id IS NOT NULL)
                              AND case38_.reportingUser_id=64
                              OR case38_.responsibleRegion_id=59
                              OR case38_.region_id=59) THEN TRUE
                    ELSE FALSE
                END AS col_43_0_
FROM Task task0_
LEFT OUTER JOIN cases case1_ ON task0_.caze_id=case1_.id
LEFT OUTER JOIN Person person2_ ON case1_.person_id=person2_.id
LEFT OUTER JOIN LOCATION location3_ ON person2_.address_id=location3_.id
LEFT OUTER JOIN Region region4_ ON case1_.responsibleRegion_id=region4_.id
LEFT OUTER JOIN Region region5_ ON case1_.region_id=region5_.id
LEFT OUTER JOIN District district6_ ON case1_.responsibleDistrict_id=district6_.id
LEFT OUTER JOIN District district7_ ON case1_.district_id=district7_.id
LEFT OUTER JOIN Community community8_ ON case1_.responsibleCommunity_id=community8_.id
LEFT OUTER JOIN Community community9_ ON case1_.community_id=community9_.id
LEFT OUTER JOIN Contact contacts10_ ON case1_.id=contacts10_.caze_id
LEFT OUTER JOIN Contact contact11_ ON task0_.contact_id=contact11_.id
LEFT OUTER JOIN Person person12_ ON contact11_.person_id=person12_.id
LEFT OUTER JOIN LOCATION location13_ ON person12_.address_id=location13_.id
LEFT OUTER JOIN Region region14_ ON contact11_.region_id=region14_.id
LEFT OUTER JOIN District district15_ ON contact11_.district_id=district15_.id
LEFT OUTER JOIN Community community16_ ON contact11_.community_id=community16_.id
LEFT OUTER JOIN cases case17_ ON contact11_.caze_id=case17_.id
LEFT OUTER JOIN Person person18_ ON case17_.person_id=person18_.id
LEFT OUTER JOIN cases case19_ ON contact11_.caze_id=case19_.id
LEFT OUTER JOIN cases case20_ ON contact11_.caze_id=case20_.id
LEFT OUTER JOIN cases case21_ ON contact11_.caze_id=case21_.id
LEFT OUTER JOIN cases case22_ ON contact11_.caze_id=case22_.id
LEFT OUTER JOIN Contact contacts23_ ON case22_.id=contacts23_.caze_id
LEFT OUTER JOIN EVENTS event24_ ON task0_.event_id=event24_.id
LEFT OUTER JOIN LOCATION location25_ ON event24_.eventLocation_id=location25_.id
LEFT OUTER JOIN Region region26_ ON location25_.region_id=region26_.id
LEFT OUTER JOIN District district27_ ON location25_.district_id=district27_.id
LEFT OUTER JOIN Community community28_ ON location25_.community_id=community28_.id
LEFT OUTER JOIN LOCATION location29_ ON event24_.eventLocation_id=location29_.id
LEFT OUTER JOIN LOCATION location30_ ON event24_.eventLocation_id=location30_.id
LEFT OUTER JOIN EventParticipant eventperso31_ ON event24_.id=eventperso31_.event_id
LEFT OUTER JOIN LOCATION location32_ ON event24_.eventLocation_id=location32_.id
LEFT OUTER JOIN travelentry travelentr33_ ON task0_.travelEntry_id=travelentr33_.id
LEFT OUTER JOIN Region region34_ ON travelentr33_.responsibleRegion_id=region34_.id
LEFT OUTER JOIN District district35_ ON travelentr33_.responsibleDistrict_id=district35_.id
LEFT OUTER JOIN Community community36_ ON travelentr33_.responsibleCommunity_id=community36_.id
LEFT OUTER JOIN Person person37_ ON travelentr33_.person_id=person37_.id
LEFT OUTER JOIN cases case38_ ON travelentr33_.resultingCase_id=case38_.id
LEFT OUTER JOIN cases case39_ ON travelentr33_.resultingCase_id=case39_.id
LEFT OUTER JOIN users user40_ ON task0_.creatorUser_id=user40_.id
LEFT OUTER JOIN users user41_ ON task0_.assigneeUser_id=user41_.id
WHERE (task0_.creatorUser_id=?
       OR task0_.assigneeUser_id=?
       OR case1_.region_id=59
       OR case1_.responsibleRegion_id=59
       OR contacts10_.reportingUser_id=?
       OR contacts10_.contactOfficer_id=?
       OR contacts10_.region_id=?
       OR case1_.sharedToCountry=TRUE
       OR case1_.reportingUser_id=64
       OR case1_.surveillanceOfficer_id=64
       OR case1_.caseOfficer_id=64
       OR contact11_.reportingUser_id=?
       OR contact11_.contactOfficer_id=?
       OR contact11_.region_id=?
       OR case22_.region_id=59
       OR case22_.responsibleRegion_id=59
       OR contacts23_.reportingUser_id=?
       OR contacts23_.contactOfficer_id=?
       OR contacts23_.region_id=?
       OR case22_.sharedToCountry=TRUE
       OR case22_.reportingUser_id=64
       OR case22_.surveillanceOfficer_id=64
       OR case22_.caseOfficer_id=64
       OR location32_.region_id=?
       OR event24_.reportingUser_id=?
       OR event24_.responsibleUser_id=?
       OR (travelentr33_.reportingUser_id IS NOT NULL)
       AND travelentr33_.reportingUser_id=64
       OR travelentr33_.responsibleRegion_id=59
       OR travelentr33_.pointOfEntryRegion_id=59
       OR (case39_.reportingUser_id IS NOT NULL)
       AND case39_.reportingUser_id=64
       OR case39_.responsibleRegion_id=59
       OR case39_.region_id=59)
  AND (user41_.uuid IS NULL
       OR user41_.jurisdictionLevel=?
       OR user41_.region_id=?)
ORDER BY task0_.dueDate DESC
LIMIT ?