[2024-07-11 16:45:41] Connected openchs.public> set role mc [2024-07-11 16:45:41] completed in 32 ms openchs: mc, public> SELECT organisation.name as "Organisation", g."name" as "Group", p."name" as "Privilege", st."name" as "Subject type", prg.name as "Program", penct.name as "Program encounter type", enct.name as "Encounter type", '' as "Checklist details", '' as "Allow Access", CONCAT('"', string_agg(group_privilege.id::text, ', '), '"') as "GP Ids" FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id left join encounter_type enct on enct.id = group_privilege.encounter_type_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id WHERE group_privilege.is_voided = false GROUP BY organisation.name, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, penct.name, encounter_type_id, enct.name HAVING COUNT(allow) > 1 [2024-07-11 16:45:46] 299 rows retrieved starting from 1 in 1 s 730 ms (execution: 1 s 288 ms, fetching: 442 ms) openchs: mc, public> UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), allow = false, last_modified_by_id = 5516 WHERE is_voided = true and allow = true [2024-07-11 16:46:08] completed in 224 ms openchs: mc, public> SELECT organisation.name as "Organisation", g."name" as "Group", p."name" as "Privilege", st."name" as "Subject type", prg.name as "Program", penct.name as "Program encounter type", enct.name as "Encounter type", '' as "Checklist details", '' as "Allow Access", CONCAT('"', string_agg(group_privilege.id::text, ', '), '"') as "GP Ids" FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id left join encounter_type enct on enct.id = group_privilege.encounter_type_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id WHERE organisation.db_user in ( 'mc' ) and group_privilege.is_voided = false GROUP BY organisation.name, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, penct.name, encounter_type_id, enct.name HAVING COUNT(distinct allow) > 1 [2024-07-11 16:47:25] 7 rows retrieved starting from 1 in 166 ms (execution: 146 ms, fetching: 20 ms) openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id JOIN public.encounter_type et ON et.id = gp.encounter_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Creche Supervisor' AND priv.name = 'Schedule visit' AND st.name = 'School Going' AND et.name = 'Daily morning attendance for school going' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:53:53] 2 rows affected in 98 ms openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id JOIN public.encounter_type et ON et.id = gp.encounter_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Creche Supervisor' AND priv.name = 'Approve Encounter' AND st.name = 'Creche' AND et.name = 'Daily morning attendance for creche' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:53:59] 2 rows affected in 57 ms openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id JOIN public.encounter_type et ON et.id = gp.encounter_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Creche Supervisor' AND priv.name = 'Reject Encounter' AND st.name = 'Creche' AND et.name = 'Daily morning attendance for creche' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:54:01] 2 rows affected in 57 ms openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id JOIN public.encounter_type et ON et.id = gp.encounter_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Creche Supervisor' AND priv.name = 'Void visit' AND st.name = 'Creche' AND et.name = 'Daily morning attendance for creche' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:54:04] 2 rows affected in 48 ms openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Program team' AND priv.name = 'Approve Subject' AND st.name = 'Balwadi' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:54:06] 2 rows affected in 74 ms openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id JOIN public.encounter_type et ON et.id = gp.encounter_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Program team' AND priv.name = 'Approve Encounter' AND st.name = 'Balwadi' AND et.name = 'Daily morning attendance for balwadi' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:54:09] 3 rows affected in 48 ms openchs: mc, public> with cte as (select gp.id gpid, RANK() OVER ( ORDER BY gp.id DESC) AS rnk from group_privilege gp JOIN public.organisation o ON o.id = gp.organisation_id JOIN public.groups g ON g.id = gp.group_id JOIN public.privilege priv ON priv.id = gp.privilege_id JOIN public.subject_type st ON st.id = gp.subject_type_id JOIN public.encounter_type et ON et.id = gp.encounter_type_id WHERE o.name = 'Mobile Creches' AND g.name = 'Program team' AND priv.name = 'Approve Encounter' AND st.name = 'Balwadi' AND et.name = 'Daily evening attendance for balwadi' ) UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = cte.rnk > 1, allow = FALSE from cte WHERE id = cte.gpid [2024-07-11 16:54:13] 3 rows affected in 63 ms openchs: mc, public> SELECT organisation.name as "Organisation", g."name" as "Group", p."name" as "Privilege", st."name" as "Subject type", prg.name as "Program", penct.name as "Program encounter type", enct.name as "Encounter type", '' as "Checklist details", '' as "Allow Access", CONCAT('"', string_agg(group_privilege.id::text, ', '), '"') as "GP Ids" FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id left join encounter_type enct on enct.id = group_privilege.encounter_type_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id WHERE group_privilege.is_voided = false GROUP BY organisation.name, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, penct.name, encounter_type_id, enct.name HAVING COUNT(allow) > 1 [2024-07-11 16:54:33] 292 rows retrieved starting from 1 in 370 ms (execution: 222 ms, fetching: 148 ms) openchs: mc, public> with aggregate_gp_ids_data as ( SELECT max(group_privilege.id) max_gp_ids, unnest(array_agg(group_privilege.id)) duplicate_gp_ids, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, group_privilege.checklist_detail_id, encounter_type_id, group_privilege.is_voided, organisation.name as ogname FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id where group_privilege.is_voided = false GROUP BY organisation.name, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, group_privilege.checklist_detail_id, encounter_type_id, group_privilege.is_voided HAVING COUNT(allow) > 1 ) select distinct duplicate_gp_ids from aggregate_gp_ids_data EXCEPT select distinct max_gp_ids from aggregate_gp_ids_data [2024-07-11 16:54:51] 325 rows retrieved starting from 1 in 239 ms (execution: 96 ms, fetching: 143 ms) openchs: mc, public> Select SUM(duplicate_gp_ids - 1) from ( SELECT (count(group_privilege.id)) duplicate_gp_ids FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id where group_privilege.is_voided = false GROUP BY group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, group_privilege.checklist_detail_id, encounter_type_id, group_privilege.is_voided, organisation.name HAVING COUNT(allow) > 1) aggreg [2024-07-11 16:55:11] 1 row retrieved starting from 1 in 98 ms (execution: 75 ms, fetching: 23 ms) openchs: mc, public> UPDATE group_privilege SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')), last_modified_by_id = 5516, is_voided = true, allow = FALSE WHERE id in (with aggregate_gp_ids_data as ( SELECT max(group_privilege.id) max_gp_ids, unnest(array_agg(group_privilege.id)) duplicate_gp_ids, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, group_privilege.checklist_detail_id, encounter_type_id, group_privilege.is_voided, organisation.name as ogname FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id where group_privilege.is_voided = false GROUP BY organisation.name, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, group_privilege.checklist_detail_id, encounter_type_id, group_privilege.is_voided HAVING COUNT(allow) > 1 ) select distinct duplicate_gp_ids from aggregate_gp_ids_data EXCEPT select distinct max_gp_ids from aggregate_gp_ids_data) [2024-07-11 16:55:43] 325 rows affected in 451 ms openchs: mc, public> SELECT case when count(orgName) > 0 then 'Duplicates exist' else 'No more dupes' end FROM ( SELECT organisation.name orgName FROM group_privilege join "groups" g on g.id = group_privilege.group_id left join subject_type st on st.id = group_privilege.subject_type_id left join program prg on prg.id = group_privilege.program_id left join encounter_type penct on penct.id = group_privilege.program_encounter_type_id left join encounter_type enct on enct.id = group_privilege.encounter_type_id join organisation on group_privilege.organisation_id = organisation.id join privilege p on p.id = group_privilege.privilege_id WHERE group_privilege.is_voided = false GROUP BY organisation.name, group_id, g."name", privilege_id, p."name", subject_type_id, st."name", program_id, prg.name, program_encounter_type_id, penct.name, encounter_type_id, enct.name HAVING COUNT(allow) > 1) as duplicatePrivOrgs [2024-07-11 16:56:01] 1 row retrieved starting from 1 in 155 ms (execution: 130 ms, fetching: 25 ms)