Move grade 6 back to Secondary #61

gistfile1.txt

set role aptuat;

select name, uuid
from concept
where uuid in ('dd0bc18d-0661-45d5-84cc-45b1e0d4fba6', 'c2698021-44dc-4b43-a877-3801d4a8c20b',
               '5e37269c-1b30-4766-b48a-893b77fd9a1a', 'ad12fc9b-cbdf-4ba4-ab27-e03e89a9af23',
               '9a2bf64b-41b1-485e-a6dd-ceb1b75a8397', 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739');

-- Select the count of rows with "Grade" = '6' and "Type of School" = 'Primary' before the update
-- Secondary Grade 6 count should be 0
SELECT 'primary grade 6 count' category,
       COUNT(*)
FROM encounter
WHERE observations ->> '5e37269c-1b30-4766-b48a-893b77fd9a1a' = 'ad12fc9b-cbdf-4ba4-ab27-e03e89a9af23'
  AND (observations ->> 'dd0bc18d-0661-45d5-84cc-45b1e0d4fba6' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]'
    OR observations -> 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]')
UNION ALL
SELECT 'secondary grade 6 count' category, COUNT(*)
FROM encounter
WHERE observations ->> '5e37269c-1b30-4766-b48a-893b77fd9a1a' = '9a2bf64b-41b1-485e-a6dd-ceb1b75a8397'
  AND (observations ->> 'dd0bc18d-0661-45d5-84cc-45b1e0d4fba6' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]'
    OR observations -> 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]');

-- Update the "Type of School" as 'Secondary' for rows with "Grade" = '6'
UPDATE encounter
SET observations            = observations ||
                              '{"5e37269c-1b30-4766-b48a-893b77fd9a1a":"9a2bf64b-41b1-485e-a6dd-ceb1b75a8397"}',
    last_modified_date_time = current_timestamp + interval '1 millisecond',
    last_modified_by_id     = (select id from users where username = 'beulah@aptuat')
WHERE observations ->> '5e37269c-1b30-4766-b48a-893b77fd9a1a' = 'ad12fc9b-cbdf-4ba4-ab27-e03e89a9af23'
  AND (observations ->> 'dd0bc18d-0661-45d5-84cc-45b1e0d4fba6' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]'
    OR observations -> 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]');

-- Verify that the "Type of School" has been successfully reverted to 'Secondary'
-- Primary Grade 6 count should be 0
-- The Secondary Grade 6 count should be the same as the Primary Grade 6 count before the update
SELECT 'primary grade 6 count' category,
       COUNT(*)
FROM encounter
WHERE observations ->> '5e37269c-1b30-4766-b48a-893b77fd9a1a' = 'ad12fc9b-cbdf-4ba4-ab27-e03e89a9af23'
  AND (observations ->> 'dd0bc18d-0661-45d5-84cc-45b1e0d4fba6' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]'
    OR observations -> 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]')
UNION ALL
SELECT 'secondary grade 6 count' category, COUNT(*)
FROM encounter
WHERE observations ->> '5e37269c-1b30-4766-b48a-893b77fd9a1a' = '9a2bf64b-41b1-485e-a6dd-ceb1b75a8397'
  AND (observations ->> 'dd0bc18d-0661-45d5-84cc-45b1e0d4fba6' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]'
    OR observations -> 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739' = '["c2698021-44dc-4b43-a877-3801d4a8c20b"]');