Untitled

SELECT 

    r.id 

FROM 

    responsibilities r 

    INNER JOIN responsibility_assignees ra ON r.id = ra.responsibility_id 

    JOIN responsibility_assignee_due_dates radd ON radd.resp_id = r.id 

    AND( 

        ( 

            r.assignee_type = 'all' 

            AND r.rc_type = 'all' 

            AND ra.assignee_id = radd.assignee_id 

            AND ra.responsibility_center_id = radd.responsibility_center_id 

        ) 

        OR( 

            r.assignee_type = 'all' 

            AND( 

                r.rc_type = 'any' 

                OR r.rc_type IS NULL 

            ) 

            AND ra.assignee_id = radd.assignee_id 

        ) 

        OR( 

            r.assignee_type = 'any' 

            AND r.rc_type = 'all' 

            AND ra.responsibility_center_id = radd.responsibility_center_id 

        ) 

        OR( 

            r.assignee_type = 'any' 

            AND( 

                r.rc_type = 'any' 

                OR r.rc_type IS NULL 

            ) 

        ) 

    ) 

    LEFT JOIN responsibility_assignee_delegations rad ON r.id = rad.resp_id 

    and rad.status = 'active' 

    LEFT JOIN program_entity_map pem ON r.id = pem.entity_id 

    AND pem.entity_type = 'responsibility'  

    --joins for search 

    LEFT JOIN programs p ON pem.program_id = p.id 

    AND p.active = 1 

    AND p.trashed = 0 

    LEFT JOIN users u ON ( 

        ra.assignee_id = u.id 

        OR r.assignor_id = u.id 

    ) 

    AND u.status = 'active' 

    LEFT JOIN responsibility_centers rc ON rc.id = ANY(r.responsibility_centers) 

    AND rc.status = 'active' 

WHERE 

    ra.status IN ('active') 

    AND r.status IN ('active', 'lc_completed') 

    AND r.is_folder = 0 

    AND r.organization_id = 1 

    AND r.trashed_at IS NULL 

    AND ( 

        radd.window_end_at > '2023-02-01' 

        OR r.created_at > '2023-02-01' 

    ) 

    AND ( 

        radd.window_start_at < '2023-11-30' 

        OR r.created_at < '2023-11-30' 

    ) 

    AND ( 

        ra.assignee_id = 2 

        OR rad.to_user_id = 2 

    )  

    -- conditions for search 

    AND ( 

        r.control_id LIKE '%searchText%' 

        OR p.name LIKE '%searchText%' 

        OR rc.name LIKE '%searchText%' 

        OR u.name LIKE '%searchText%' 

        OR r.title LIKE '%searchText%'  

        --ToDo frequency text search 

    )  

    -- filter conditions 

    AND pem.program_id IN (1, 2) 

    AND r.responsibility_centers && ARRAY [1, 2] 

    AND ( 

        ra.assignee_id IN (1, 2) 

        OR ra.assignee_id IN ( 

            SELECT 

                user_id 

            FROM 

                user_group_map 

            WHERE 

                group_id IN (1, 2) 

        ) 

    ) 

GROUP BY 

    r.id 

ORDER BY 

    min(radd.due_date) ASC 

LIMIT 

    100 OFFSET ((1 -1) * 100)