Re: [Gnumed-devel] Adding procedure - clarification needed about Locations

[Karsten Hilbert]

The hospital stay table has been re-worked to properly point
to an org unit rather than store the hospital name in
.narrative

Also, a proper phrasewheel has been added which lists org units thusly:

1st: org units already in use in hospital stays

2nd: "Ward" type units

3rd: units of orgs of type "Hospital"

4th: any other units


                SELECT data, list_label, field_label FROM (
                        SELECT DISTINCT ON (data) * FROM ((

                                -- already-used org_units
                                SELECT
                                        pk_org_unit
                                                AS data,
                                        ward || ' @ ' || hospital
                                                AS list_label,
                                        ward || ' @ ' || hospital
                                                AS field_label,
                                        1
                                                AS rank
                                FROM
                                        clin.v_hospital_stays
                                WHERE
                                        ward %(fragment_condition)s
                                                OR
                                        hospital %(fragment_condition)s

                                ) UNION ALL (
                                -- wards
                                SELECT
                                        pk_org_unit
                                                AS data,
                                        unit || ' (' || l10n_unit_category || 
') @ ' || organization
                                                AS list_label,
                                        unit || ' @ ' || organization
                                                AS field_label,
                                        2
                                                AS rank
                                FROM
                                        dem.v_org_units
                                WHERE
                                        unit_category = 'Ward'
                                                AND
                                        unit %(fragment_condition)s
                                                AND
                                        NOT EXISTS (
                                                SELECT 1 FROM 
clin.v_hospital_stays WHERE clin.v_hospital_stays.pk_org_unit = 
dem.v_org_units.pk_org_unit
                                        )

                                ) UNION ALL (
                                -- hospital units
                                SELECT
                                        pk_org_unit
                                                AS data,
                                        unit || coalesce(' (' || 
l10n_unit_category || ')', '') || ' @ ' || organization || ' (' || 
l10n_organization_category || ')'
                                                AS list_label,
                                        unit || ' @ ' || organization
                                                AS field_label,
                                        3
                                                AS rank
                                FROM
                                        dem.v_org_units
                                WHERE
                                        unit_category <> 'Ward'
                                                AND
                                        organization_category = 'Hospital'
                                                AND
                                        unit %(fragment_condition)s
                                                AND
                                        NOT EXISTS (
                                                SELECT 1 FROM 
clin.v_hospital_stays WHERE clin.v_hospital_stays.pk_org_unit = 
dem.v_org_units.pk_org_unit
                                        )

                                ) UNION ALL (
                                -- any other units
                                SELECT
                                        pk_org_unit
                                                AS data,
                                        unit || coalesce(' (' || 
l10n_unit_category || ')', '') || ' @ ' || organization || ' (' || 
l10n_organization_category || ')'
                                                AS list_label,
                                        unit || ' @ ' || organization
                                                AS field_label,
                                        3
                                                AS rank
                                FROM
                                        dem.v_org_units
                                WHERE
                                        unit_category <> 'Ward'
                                                AND
                                        organization_category <> 'Hospital'
                                                AND
                                        unit %(fragment_condition)s
                                                AND
                                        NOT EXISTS (
                                                SELECT 1 FROM 
clin.v_hospital_stays WHERE clin.v_hospital_stays.pk_org_unit = 
dem.v_org_units.pk_org_unit
                                        )
                        )) AS all_matches
                        ORDER BY data, rank
                ) AS distinct_matches
                ORDER BY rank, list_label
                LIMIT 50


Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

screenshot_001.png
Description: PNG image