Re: [Gnumed-devel] Measurement workflows - units phrasewheel

[Karsten Hilbert]

On Tue, Jul 09, 2013 at 05:13:57AM +0000, Jim Busser wrote:

> 2. In the second screenshot, inputting into the units
> 
> kg
> 
> invokes the phrasewheel so as to offer matches which I could use some help to 
> understand (Screenshot 2). Notice that I am offered
> 
> kg (weight (body mass))
> kg (weight (body mass)) (weight (body mass))
> 
> --> what is the origin of the double (weight (body mass))
> (weight (body mass)) since it does not obviously derive from
> any error or redundancy in prior inputs into my test types
> (see Screenshot 3).

The SQL used to find matches does not fully accomplish to
make results unique. Here's the code for amusement:

#================================================================
_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 || ' (LOINC.ipcc: ' || 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 || ' (LOINC.submitted:' || 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 || ' (LOINC.example: ' || 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 ref.atc.unit
        SELECT
                unit AS data,
                unit AS field_label,
                unit || ' (ATC: ' || term || ')' AS list_label,
                2 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 || ' (' || description || ')' AS list_label,
                2 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, list_label

) 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, abbrev_meta)',
                                'placeholder': u'test_name'
                        },
                        'ctxt_ctt': {
                                'where_part': u'AND %(test_name)s IN (name, 
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'
                        }
                }

#----------------------------------------------------------------

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