CK Data Quries

-- Pre Scripts

CREATE EXTENSION "uuid-ossp";
ALTER TABLE mother
  ADD COLUMN uuid VARCHAR(255) NOT NULL DEFAULT uuid_generate_v4();
ALTER TABLE child
  ADD COLUMN uuid VARCHAR(255) NOT NULL DEFAULT uuid_generate_v4();
ALTER TABLE child_registration
  ADD COLUMN enrolment_uuid VARCHAR(255) NOT NULL DEFAULT uuid_generate_v4();
ALTER TABLE pregnancy_registration
  ADD COLUMN enrolment_uuid VARCHAR(255) NOT NULL DEFAULT uuid_generate_v4();

-- Mother Registration
SELECT
  m.uuid                                               AS "Individual UUID",
  m.first_name                                         AS "First Name",
  m.last_name                                          AS "Last Name",
  'Female'                                             AS "Gender",
  m.area                                               AS "Address Level",
  coalesce(m.date_of_birth, approxdateofbirth :: DATE) AS "Date Of Birth",
  coalesce(m.beneficiary_id, '')                       AS "Beneficiary id",
  coalesce(m.mychi_id, '')                             AS "myChi id",
  substring(m.address, '([0-9/]+)')                    AS "Household number",
  initcap(wr.floor)                                    AS "Floor",
  substring(wr.address, '\((.*)\)')                    AS "Room number",
  m.phone_number                                       AS "Phone number",
  wr.altphone                                          AS "Alternate phone number",
  CASE WHEN wr.primarycaregiver = 'TRUE'
    THEN 'Yes'
  WHEN 'FALSE'
    THEN 'No'
  END                                                  AS "Is mother the primary caregiver",
  wr.husband_name || ' ' || wr.husband_last_name       AS "Father/Husband",
  initcap(wr.medicalhistory)                           AS "Other medical history"
FROM mother m
  INNER JOIN woman_registration wr ON wr.entity_id != '' AND wr.entity_id :: INT = m.id;

-- Pregnancy Enrolment

SELECT
  m.uuid                                               AS "Individual UUID",
  m.first_name                                         AS "First Name",
  m.last_name                                          AS "Last Name",
  'Female'                                             AS "Gender",
  m.area                                               AS "Address Level",
  coalesce(m.date_of_birth, approxdateofbirth :: DATE) AS "Date Of Birth",
  coalesce(m.beneficiary_id, '')                       AS "Beneficiary id",
  coalesce(m.mychi_id, '')                             AS "myChi id",
  substring(m.address, '([0-9/]+)')                    AS "Household number",
  initcap(wr.floor)                                    AS "Floor",
  substring(wr.address, '\((.*)\)')                    AS "Room number",
  m.phone_number                                       AS "Phone number",
  wr.altphone                                          AS "Alternate phone number",
  CASE WHEN wr.primarycaregiver = 'TRUE'
    THEN 'Yes'
  WHEN 'FALSE'
    THEN 'No'
  END                                                  AS "Is mother the primary caregiver",
  wr.husband_name || ' ' || wr.husband_last_name       AS "Father/Husband",
  initcap(wr.medicalhistory)                           AS "Other medical history"
FROM mother m
  INNER JOIN woman_registration wr ON wr.entity_id != '' AND wr.entity_id :: INT = m.id;

-- Child Registration and Enrolment
SELECT
  c.uuid                                                                       AS "Individual UUID",
  cr.enrolment_uuid                                                            AS "Enrolment UUID",
  c.first_name                                                                 AS "First Name",
  c.last_name                                                                  AS "Last Name",
  c.sex                                                                        AS "Gender",
  m.area                                                                       AS "Address Level",
  coalesce(c.date_of_birth, cr.dateofbirth :: DATE, cr.dateofdelivery :: DATE) AS "Date Of Birth",
  coalesce(c.beneficiary_id, '')                                               AS "Beneficiary id",
  coalesce(c.mychi_id, '')                                                     AS "myChi id",
  substring(m.address, '([0-9/]+)')                                            AS "Household number",
  initcap(wr.floor)                                                            AS "Floor",
  substring(m.address, '\((.*)\)')                                             AS "Room number",
  m.phone_number                                                               AS "Phone number",
  wr.altphone                                                                  AS "Alternate phone number",
  CASE WHEN wr.primarycaregiver = 'TRUE'
    THEN 'Yes'
  WHEN 'FALSE'
    THEN 'No'
  END                                                                          AS "Is mother the primary caregiver",
  wr.husband_name || ' ' || wr.husband_last_name                               AS "Father/Husband",
  initcap(cr.medicalhistory)                                                   AS "Other medical history",
  cr.babybirthweight                                                           AS "Birth weight",
  cr.babyweight                                                                AS "Weight"
FROM child c
  INNER JOIN child_registration cr ON c.id = cr.entity_id :: INT
  INNER JOIN mother m ON m.id = c.id
  INNER JOIN woman_registration wr ON wr.entity_id != '' AND wr.entity_id :: INT = m.id;