Re: [Gnumed-devel] Measurements - adding and abbreviations

[Karsten Hilbert]

On Thu, Jul 28, 2011 at 01:00:06AM -0700, Jim Busser wrote:

> by the way, what is the source of that long list of units
> when few records exist in my
> 
>       clin.test_type

#================================================================
_SQL_units_from_test_results = u"""
        -- via clin.v_test_results.pk_type (for types already used in results)
        SELECT
                val_unit AS data,
                val_unit AS field_label,
                val_unit || ' (' || name_tt || ')' AS list_label,
                1 AS rank
        FROM
                clin.v_test_results
        WHERE
                (
                        val_unit %(fragment_condition)s
                                OR
                        conversion_unit %(fragment_condition)s
                )
                %(ctxt_type_pk)s
                %(ctxt_test_name)s
"""

_SQL_units_from_test_types = u"""
        -- via clin.test_type (for types not yet used in results)
        SELECT
                conversion_unit AS data,
                conversion_unit AS field_label,
                conversion_unit || ' (' || name || ')' AS list_label,
                2 AS rank
        FROM
                clin.test_type
        WHERE
                conversion_unit %(fragment_condition)s
                %(ctxt_ctt)s
"""

_SQL_units_from_loinc_ipcc = u"""
        -- via ref.loinc.ipcc_units
        SELECT
                ipcc_units AS data,
                ipcc_units AS field_label,
                ipcc_units || ' (' || term || ')' AS list_label,
                3 AS rank
        FROM
                ref.loinc
        WHERE
                ipcc_units %(fragment_condition)s
                %(ctxt_loinc)s
                %(ctxt_loinc_term)s
"""

_SQL_units_from_loinc_submitted = u"""
        -- via ref.loinc.submitted_units
        SELECT
                submitted_units AS data,
                submitted_units AS field_label,
                submitted_units || ' (' || term || ')' AS list_label,
                3 AS rank
        FROM
                ref.loinc
        WHERE
                submitted_units %(fragment_condition)s
                %(ctxt_loinc)s
                %(ctxt_loinc_term)s
"""

_SQL_units_from_loinc_example = u"""
        -- via ref.loinc.example_units
        SELECT
                example_units AS data,
                example_units AS field_label,
                example_units || ' (' || term || ')' AS list_label,
                3 AS rank
        FROM
                ref.loinc
        WHERE
                example_units %(fragment_condition)s
                %(ctxt_loinc)s
                %(ctxt_loinc_term)s
"""

_SQL_units_from_atc = u"""
        -- via rev.atc.unit
        SELECT
                unit AS data,
                unit AS field_label,
                unit AS list_label,
                1 AS rank
        FROM
                ref.atc
        WHERE
                unit IS NOT NULL
                        AND
                unit %(fragment_condition)s
"""

_SQL_units_from_consumable_substance = u"""
        -- via ref.consumable_substance.unit
        SELECT
                unit AS data,
                unit AS field_label,
                unit AS list_label,
                1 AS rank
        FROM
                ref.consumable_substance
        WHERE
                unit %(fragment_condition)s
                %(ctxt_substance)s
"""
#================================================================
class cUnitPhraseWheel(gmPhraseWheel.cPhraseWheel):

        def __init__(self, *args, **kwargs):

                query = u"""
SELECT DISTINCT ON (data)
        data,
        field_label,
        list_label
FROM (

        SELECT
                data,
                field_label,
                list_label,
                rank
        FROM (
                (%s) UNION ALL
                (%s) UNION ALL
                (%s) UNION ALL
                (%s) UNION ALL
                (%s) UNION ALL
                (%s) UNION ALL
                (%s)
        ) AS all_matching_units
        WHERE data IS NOT NULL
        ORDER BY rank

) AS ranked_matching_units
LIMIT 50""" % (
                        _SQL_units_from_test_results,
                        _SQL_units_from_test_types,
                        _SQL_units_from_loinc_ipcc,
                        _SQL_units_from_loinc_submitted,
                        _SQL_units_from_loinc_example,
                        _SQL_units_from_atc,
                        _SQL_units_from_consumable_substance
                )

                ctxt = {
                        'ctxt_type_pk': {
                                'where_part': u'AND pk_test_type = %(pk_type)s',
                                'placeholder': u'pk_type'
                        },
                        'ctxt_test_name': {
                                'where_part': u'AND %(test_name)s IN (name_tt, 
name_meta, code_tt, abbrev_meta)',
                                'placeholder': u'test_name'
                        },
                        'ctxt_ctt': {
                                'where_part': u'AND %(test_name)s IN (name, 
code, abbrev)',
                                'placeholder': u'test_name'
                        },
                        'ctxt_loinc': {
                                'where_part': u'AND code = %(loinc)s',
                                'placeholder': u'loinc'
                        },
                        'ctxt_loinc_term': {
                                'where_part': u'AND term ~* %(test_name)s',
                                'placeholder': u'test_name'
                        },
                        'ctxt_substance': {
                                'where_part': u'AND description ~* 
%(substance)s',
                                'placeholder': u'substance'
                        }
                }


> and nothing exists is my
> 
>       public.unit
> 
>       public.test_norm
> 
>       public.basic_unit

They aren't used ATM.

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