admin groups

administrators-group.sql

-- create groups
begin transaction;
insert into groups (uuid, name, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time)
    (select uuid_generate_v4(), 'Administrators', 1, id, 1, 1, current_timestamp, current_timestamp from organisation where id <> 1 and organisation.is_voided = false); -- 193

-- Without entity specific fields
insert into group_privilege (uuid, group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time)
select uuid_generate_v4(), groups.id, privilege.id, null, null, null, null, null, 1, groups.organisation_id, 1, 1, current_timestamp, current_timestamp
from privilege join groups on groups.name = 'Administrators'
where privilege.entity_type in ('Report', 'NonTransaction', 'Task'); -- 29*193

select count(*) from checklist_detail where is_voided = false; --28
select count(*) from encounter_type where is_voided = false; --452
select * from privilege
                  join checklist_detail cd on cd.is_voided = false
where privilege.entity_type in ('Checklist', 'ChecklistItem');

-- Checklist
insert into group_privilege (uuid, group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time, allow)
select uuid_generate_v4(), groups.id, privilege.id, null, null, null, null, cd.id, 1, groups.organisation_id, 1, 1, current_timestamp, current_timestamp, true
from privilege join groups on groups.name = 'Administrators'
               join checklist_detail cd on cd.is_voided = false and groups.organisation_id = cd.organisation_id
where privilege.entity_type in ('Checklist', 'ChecklistItem');

-- Subject
insert into group_privilege (uuid, group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time, allow)
select uuid_generate_v4(), groups.id, privilege.id, subject_type.id, null, null, null, null, 1, groups.organisation_id, 1, 1, current_timestamp, current_timestamp, true
from privilege join groups on groups.name = 'Administrators'
               join form_mapping on form_mapping.is_voided = false and form_mapping.entity_id is null and form_mapping.observations_type_entity_id is null
               join subject_type on subject_type.is_voided = false and form_mapping.subject_type_id = subject_type.id
where privilege.entity_type = 'Subject' and groups.organisation_id = subject_type.organisation_id;

-- general encounter
insert into group_privilege (uuid, group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time, allow)
select uuid_generate_v4(), groups.id, privilege.id, subject_type.id, null, null, encounter_type.id, null, 1, groups.organisation_id, 1, 1, current_timestamp, current_timestamp, true
from privilege join groups on groups.name = 'Administrators'
               join form_mapping on form_mapping.is_voided = false and form_mapping.entity_id is null
               join subject_type on subject_type.is_voided = false and subject_type.id = form_mapping.subject_type_id
               join encounter_type on encounter_type.is_voided = false and encounter_type.id = form_mapping.observations_type_entity_id
where privilege.entity_type = 'Encounter' and groups.organisation_id = subject_type.organisation_id;

-- program encounter
insert into group_privilege (uuid, group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time, allow)
select uuid_generate_v4(), groups.id, privilege.id, subject_type.id, program.id, encounter_type.id, null, null, 1, groups.organisation_id, 1, 1, current_timestamp, current_timestamp, true
from privilege join groups on groups.name = 'Administrators'
               join form_mapping on form_mapping.is_voided = false
               join subject_type on subject_type.is_voided = false and subject_type.id = form_mapping.subject_type_id
               join encounter_type on encounter_type.is_voided = false and encounter_type.id = form_mapping.observations_type_entity_id
                join program on program.is_voided = false and form_mapping.entity_id = program.id
where privilege.entity_type = 'Encounter' and groups.organisation_id = subject_type.organisation_id;

-- program enrolment
insert into group_privilege (uuid, group_id, privilege_id, subject_type_id, program_id, program_encounter_type_id, encounter_type_id, checklist_detail_id, version, organisation_id, created_by_id, last_modified_by_id, created_date_time, last_modified_date_time, allow)
select uuid_generate_v4(), groups.id, privilege.id, subject_type.id, program.id, null, null, null, 1, groups.organisation_id, 1, 1, current_timestamp, current_timestamp, true
from privilege join groups on groups.name = 'Administrators'
               join form_mapping on form_mapping.is_voided = false and form_mapping.observations_type_entity_id is null
               join subject_type on subject_type.is_voided = false and subject_type.id = form_mapping.subject_type_id
                join program on program.is_voided = false and form_mapping.entity_id = program.id
where privilege.entity_type = 'Enrolment' and groups.organisation_id = subject_type.organisation_id;

rollback;
--- end

@petmongrels I see you've already done everything in https://github.com/avniproject/avni-server/blob/76c6102d68d046769f88752d0b9c16f2891d5135/avni-server-api/src/main/resources/db/migration/V1_300__CreateAdminGroupAndAddAllExistingOrgAdminsToIt.sql

Why do we need this migration?

this is not needed. I realised after creating this gist that I can simply give all privileges instead of providing each one separately. ignore.

…

On Thu, Jul 20, 2023 at 9:43 AM Vinay Venu ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ @petmongrels <https://github.com/petmongrels> I see you've already done everything in https://github.com/avniproject/avni-server/blob/76c6102d68d046769f88752d0b9c16f2891d5135/avni-server-api/src/main/resources/db/migration/V1_300__CreateAdminGroupAndAddAllExistingOrgAdminsToIt.sql Why do we need this migration? — Reply to this email directly, view it on GitHub <https://gist.github.com/petmongrels/a41a68456df74cb4691639271d910d98#gistcomment-4634846> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAZ3CYBMGUINMEBTXTP75DXRCV47BFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTEMZVHA4DENRRU52HE2LHM5SXFJTDOJSWC5DF> . You are receiving this email because you were mentioned. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

-- Vivek Singh | +91 98452 32929 | @petmongrels <https://twitter.com/petmongrels>

We have used this as a reference to satisfy requirements raised as part of avni-server#588