SQL

The statements iws-mcu runs against each database, exactly as it runs them. Values are bound where the ? marks are.

Read from GMC

8 statements · flow gmc-to-rsa · adapter pgsimrs

Read patient Patient Every patient with an MCU visit admitted in the window

SELECT
    p.no_rm         AS patient_key,
    p.nik           AS nik,
    p.nama          AS name,
    p.jenis_kelamin AS gender,
    p.tanggal_lahir AS birth_date,
    p.alamat        AS address,
    p.no_telepon    AS phone
FROM pasien p
    INNER JOIN kunjungan k ON k.pasien_id = p.pasien_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
  AND k.status <> 'batal'
GROUP BY p.no_rm, p.nik, p.nama, p.jenis_kelamin, p.tanggal_lahir, p.alamat, p.no_telepon
ORDER BY MAX(k.tanggal_masuk)

Read visit Encounter Every MCU visit admitted in the window, with its diagnosis keys

Link

SELECT
    k.kunjungan_id::text AS visit_key,
    p.no_rm              AS patient_key,
    d.dokter_id::text    AS practitioner_key,
    pol.poli_id::text    AS location_key,
    k.tipe               AS visit_type,
    k.status             AS status,
    k.tanggal_masuk      AS admitted,
    k.tanggal_keluar     AS discharged,
    -- The visit's diagnosis Conditions, so the finished Encounter can carry them
    -- (the two-phase Encounter-finalization rule). Filtered exactly as
    -- fetchConditions is (kode_icd10 present), or a diagnosis that never stages
    -- would hold its Encounter in awaiting-finalize.
    (SELECT string_agg(dg.diagnosa_id::text, ',' ORDER BY dg.diagnosa_id)
     FROM diagnosa dg
     WHERE dg.kunjungan_id = k.kunjungan_id
       AND k.status <> 'batal'
       AND NULLIF(TRIM(dg.kode_icd10), '') IS NOT NULL) AS diagnosis_keys
FROM kunjungan k
    LEFT JOIN pasien p ON p.pasien_id = k.pasien_id
    LEFT JOIN dokter d ON d.dokter_id = k.dokter_id
    LEFT JOIN poli pol ON pol.poli_id = k.poli_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
ORDER BY k.tanggal_masuk

Read practitioner Practitioner The doctors who examined those MCU visits

Link

SELECT
    d.dokter_id::text AS practitioner_key,
    d.nik             AS nik,
    d.nama            AS name
FROM dokter d
    INNER JOIN kunjungan k ON k.dokter_id = d.dokter_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
  AND k.status <> 'batal'
GROUP BY d.dokter_id, d.nik, d.nama
ORDER BY MAX(k.tanggal_masuk)

Read clinic Location The poli those MCU visits were registered at

Link

SELECT
    pol.poli_id::text AS location_key,
    pol.nama          AS name
FROM poli pol
    INNER JOIN kunjungan k ON k.poli_id = pol.poli_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
  AND k.status <> 'batal'
GROUP BY pol.poli_id, pol.nama
ORDER BY MAX(k.tanggal_masuk)

Read vital-sign Observation Vital signs measured during those MCU visits

Link

SELECT
    v.vital_sign_id::text AS obs_key,
    k.kunjungan_id::text  AS visit_key,
    p.no_rm               AS patient_key,
    v.tipe                AS obs_type,
    v.nilai               AS value,
    v.satuan              AS unit,
    v.waktu_pengukuran    AS measured_at
FROM vital_sign v
    INNER JOIN kunjungan k ON k.kunjungan_id = v.kunjungan_id
    INNER JOIN pasien p ON p.pasien_id = k.pasien_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
  AND k.status <> 'batal'
ORDER BY v.waktu_pengukuran

Read result Observation Lab and exam results released for those MCU visits

Link

SELECT
    hl.hasil_lab_id::text AS obs_key,
    k.kunjungan_id::text  AS visit_key,
    p.no_rm               AS patient_key,
    hl.parameter          AS parameter,
    hl.kode_loinc         AS loinc,
    hl.kode_lokal         AS local_code,
    hl.kategori           AS category,
    hl.nilai              AS value,
    hl.satuan             AS unit,
    hl.nilai_normal       AS reference_range,
    hl.flag               AS flag,
    COALESCE(pl.waktu_pengambilan_spesimen, pl.waktu_permintaan) AS collected_at,
    hl.waktu_hasil        AS issued_at
FROM hasil_lab hl
    INNER JOIN pemeriksaan_lab pl ON pl.pemeriksaan_lab_id = hl.pemeriksaan_lab_id
    INNER JOIN kunjungan k ON k.kunjungan_id = pl.kunjungan_id
    INNER JOIN pasien p ON p.pasien_id = k.pasien_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
  AND k.status <> 'batal'
  AND NULLIF(TRIM(hl.nilai), '') IS NOT NULL
ORDER BY hl.waktu_hasil

Read diagnosis Condition ICD-10 diagnoses recorded on those MCU visits

Link

SELECT
    dg.diagnosa_id::text AS condition_key,
    k.kunjungan_id::text AS visit_key,
    p.no_rm              AS patient_key,
    dg.kode_icd10        AS code,
    dg.nama_diagnosa     AS name,
    dg.tipe_diagnosa     AS diag_type,
    dg.waktu_diagnosa    AS recorded_at
FROM diagnosa dg
    INNER JOIN kunjungan k ON k.kunjungan_id = dg.kunjungan_id
    INNER JOIN pasien p ON p.pasien_id = k.pasien_id
    INNER JOIN poli mcu ON mcu.poli_id = k.poli_id AND mcu.nama IN ?
WHERE (k.tanggal_masuk >= ? AND k.tanggal_masuk < ?)
  AND k.status <> 'batal'
  AND NULLIF(TRIM(dg.kode_icd10), '') IS NOT NULL
ORDER BY k.tanggal_masuk

Presence check visit-presence Encounter The presence sweep: which staged visits GMC still holds (a missing one was deleted)

Link

SELECT kunjungan_id::text FROM kunjungan WHERE kunjungan_id::text IN ?

Written to RSA

6 statements · flow gmc-to-rsa · adapter pgsink

Write patient Patient Writes one Patient: inserted, or updated in place when its FHIR resource changed

Link

INSERT INTO patient (id, source, source_key, resource, mrn, nik, name, birth_date, gender) VALUES (?, ?, ?, ?::jsonb, ?, ?, ?, ?, ?) ON CONFLICT (id) DO UPDATE SET source = EXCLUDED.source, source_key = EXCLUDED.source_key, resource = EXCLUDED.resource, mrn = EXCLUDED.mrn, nik = EXCLUDED.nik, name = EXCLUDED.name, birth_date = EXCLUDED.birth_date, gender = EXCLUDED.gender, version = patient.version + 1, updated_at = now() WHERE patient.resource IS DISTINCT FROM EXCLUDED.resource

Write practitioner Practitioner Writes one Practitioner: inserted, or updated in place when its FHIR resource changed

Link

INSERT INTO practitioner (id, source, source_key, resource, name, nik) VALUES (?, ?, ?, ?::jsonb, ?, ?) ON CONFLICT (id) DO UPDATE SET source = EXCLUDED.source, source_key = EXCLUDED.source_key, resource = EXCLUDED.resource, name = EXCLUDED.name, nik = EXCLUDED.nik, version = practitioner.version + 1, updated_at = now() WHERE practitioner.resource IS DISTINCT FROM EXCLUDED.resource

Write location Location Writes one Location: inserted, or updated in place when its FHIR resource changed

Link

INSERT INTO location (id, source, source_key, resource, name) VALUES (?, ?, ?, ?::jsonb, ?) ON CONFLICT (id) DO UPDATE SET source = EXCLUDED.source, source_key = EXCLUDED.source_key, resource = EXCLUDED.resource, name = EXCLUDED.name, version = location.version + 1, updated_at = now() WHERE location.resource IS DISTINCT FROM EXCLUDED.resource

Write encounter Encounter Writes one Encounter: inserted, or updated in place when its FHIR resource changed

Link

INSERT INTO encounter (id, source, source_key, resource, patient_id, practitioner_id, location_id, period_start, period_end, status) VALUES (?, ?, ?, ?::jsonb, ?, ?, ?, ?, ?, ?) ON CONFLICT (id) DO UPDATE SET source = EXCLUDED.source, source_key = EXCLUDED.source_key, resource = EXCLUDED.resource, patient_id = EXCLUDED.patient_id, practitioner_id = EXCLUDED.practitioner_id, location_id = EXCLUDED.location_id, period_start = EXCLUDED.period_start, period_end = EXCLUDED.period_end, status = EXCLUDED.status, version = encounter.version + 1, updated_at = now() WHERE encounter.resource IS DISTINCT FROM EXCLUDED.resource

Write observation Observation Writes one Observation: inserted, or updated in place when its FHIR resource changed

Link

INSERT INTO observation (id, source, source_key, resource, patient_id, encounter_id, code_system, code, display, category, effective_at) VALUES (?, ?, ?, ?::jsonb, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT (id) DO UPDATE SET source = EXCLUDED.source, source_key = EXCLUDED.source_key, resource = EXCLUDED.resource, patient_id = EXCLUDED.patient_id, encounter_id = EXCLUDED.encounter_id, code_system = EXCLUDED.code_system, code = EXCLUDED.code, display = EXCLUDED.display, category = EXCLUDED.category, effective_at = EXCLUDED.effective_at, version = observation.version + 1, updated_at = now() WHERE observation.resource IS DISTINCT FROM EXCLUDED.resource

Write condition Condition Writes one Condition: inserted, or updated in place when its FHIR resource changed

Link

INSERT INTO condition (id, source, source_key, resource, patient_id, encounter_id, icd10, display, recorded_at) VALUES (?, ?, ?, ?::jsonb, ?, ?, ?, ?, ?) ON CONFLICT (id) DO UPDATE SET source = EXCLUDED.source, source_key = EXCLUDED.source_key, resource = EXCLUDED.resource, patient_id = EXCLUDED.patient_id, encounter_id = EXCLUDED.encounter_id, icd10 = EXCLUDED.icd10, display = EXCLUDED.display, recorded_at = EXCLUDED.recorded_at, version = condition.version + 1, updated_at = now() WHERE condition.resource IS DISTINCT FROM EXCLUDED.resource