def propagate_atc(substance=None, atc=None, link_obj=None):
_log.debug('substance <%s>, ATC <%s>', substance, atc)
if atc is not None:
if atc.strip() == '':
atc = None
if atc is None:
atcs = text2atc(text = substance, fuzzy = False, link_obj = link_obj)
if len(atcs) == 0:
_log.debug('no ATC found, aborting')
return atc
if len(atcs) > 1:
_log.debug('non-unique ATC mapping, aborting')
return atc
atc = atcs[0][0].strip()
args = {'atc': atc, 'term': substance.strip()}
queries = [
{'cmd': "UPDATE ref.substance SET atc = %(atc)s WHERE lower(description) = lower(%(term)s) AND atc IS NULL",
'args': args},
{'cmd': "UPDATE ref.drug_product SET atc_code = %(atc)s WHERE lower(description) = lower(%(term)s) AND atc_code IS NULL",
'args': args}
]
gmPG2.run_rw_queries(link_obj = link_obj, queries = queries)
return atc
def invalidate_hint_suppression(pk_hint=None, pk_encounter=None):
_log.debug('invalidating suppression of hint #%s', pk_hint)
args = {
'pk_hint': pk_hint,
'enc': pk_encounter,
'fake_md5': '***INVALIDATED***' # only needs to NOT match ANY md5 sum
}
cmd = u"""
UPDATE clin.suppressed_hint SET
fk_encounter = %(enc)s,
md5_sum = %(fake_md5)s
WHERE
pk = (
SELECT pk_suppressed_hint
FROM clin.v_suppressed_hints
WHERE
pk_hint = %(pk_hint)s
AND
pk_identity = (
SELECT fk_patient FROM clin.encounter WHERE pk = %(enc)s
)
)
"""
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
return True
def delete_region(region=None, delete_urbs=False):
args = {'region': region}
queries = []
if delete_urbs:
queries.append ({
'cmd': """
delete from dem.urb du
where
du.fk_region = %(region)s
and
not exists (select 1 from dem.street ds where ds.id_urb = du.id)""",
'args': args
})
queries.append ({
'cmd': """
DELETE FROM dem.region d_r
WHERE
d_r.pk = %(region)s
AND
NOT EXISTS (SELECT 1 FROM dem.urb du WHERE du.fk_region = d_r.pk)""",
'args': args
})
gmPG2.run_rw_queries(queries = queries)
return True
def delete_region(region=None, delete_urbs=False):
args = {'region': region}
queries = []
if delete_urbs:
queries.append ({
'cmd': u"""
delete from dem.urb du
where
du.id_state = %(region)s
and
not exists (select 1 from dem.street ds where ds.id_urb = du.id)""",
'args': args
})
queries.append ({
'cmd': u"""
delete from dem.state ds
where
ds.id = %(region)s
and
not exists (select 1 from dem.urb du where du.id_state = ds.id)""",
'args': args
})
gmPG2.run_rw_queries(queries = queries)
return True
def delete_external_id(self, pk_ext_id=None):
cmd = """
DELETE FROM dem.lnk_org_unit2ext_id
WHERE fk_org_unit = %(unit)s AND pk = %(pk)s
"""
args = {'unit': self.pk_obj, 'pk': pk_ext_id}
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
def suppress_dynamic_hint(pk_hint=None, rationale=None, pk_encounter=None):
args = {
'hint': pk_hint,
'rationale': rationale,
'enc': pk_encounter
}
cmd = u"""
DELETE FROM clin.suppressed_hint
WHERE
fk_hint = %(hint)s
AND
fk_encounter IN (
SELECT pk FROM clin.encounter WHERE fk_patient = (
SELECT fk_patient FROM clin.encounter WHERE pk = %(enc)s
)
)
"""
queries = [{'cmd': cmd, 'args': args}]
cmd = u"""
INSERT INTO clin.suppressed_hint (
fk_encounter,
fk_hint,
rationale,
md5_sum
) VALUES (
%(enc)s,
%(hint)s,
%(rationale)s,
(SELECT r_vah.md5_sum FROM ref.v_auto_hints r_vah WHERE r_vah.pk_auto_hint = %(hint)s)
)
"""
queries.append({'cmd': cmd, 'args': args})
gmPG2.run_rw_queries(queries = queries)
return True
def add_part(self, file=None, link_obj=None):
"""Add a part to the document."""
# create dummy part
cmd = """
INSERT INTO blobs.doc_obj (
fk_doc, data, seq_idx
) VALUES (
%(doc_id)s,
''::bytea,
(SELECT coalesce(max(seq_idx)+1, 1) FROM blobs.doc_obj WHERE fk_doc = %(doc_id)s)
) RETURNING pk"""
rows, idx = gmPG2.run_rw_queries (
link_obj = link_obj,
queries = [{'cmd': cmd, 'args': {'doc_id': self.pk_obj}}],
return_data = True
)
# init document part instance
pk_part = rows[0][0]
new_part = cDocumentPart(aPK_obj = pk_part, link_obj = link_obj)
if not new_part.update_data_from_file(link_obj = link_obj, fname = file):
_log.error('cannot import binary data from [%s] into document part' % file)
gmPG2.run_rw_queries (
link_obj = link_obj,
queries = [{'cmd': "DELETE FROM blobs.doc_obj WHERE pk = %s", 'args': [pk_part]}]
)
return None
new_part['filename'] = file
new_part.save_payload(conn = link_obj)
return new_part
def delete_document_type(document_type=None):
if document_type["is_in_use"]:
return False
gmPG2.run_rw_queries(
queries=[{"cmd": u"delete from blobs.doc_type where pk=%s", "args": [document_type["pk_doc_type"]]}]
)
return True
def delete_old(self, workplace = None, cookie = None, option = None):
"""
Deletes an option or a whole group.
Note you have to call store() in order to save
the changes.
"""
if option is None:
raise ValueError('<option> cannot be None')
if cookie is None:
cmd = u"""
delete from cfg.cfg_item where
fk_template=(select pk from cfg.cfg_template where name = %(opt)s) and
owner = CURRENT_USER and
workplace = %(wp)s and
cookie is Null
"""
else:
cmd = u"""
delete from cfg.cfg_item where
fk_template=(select pk from cfg.cfg_template where name = %(opt)s) and
owner = CURRENT_USER and
workplace = %(wp)s and
cookie = %(cookie)s
"""
args = {'opt': option, 'wp': workplace, 'cookie': cookie}
gmPG2.run_rw_queries(queries=[{'cmd': cmd, 'args': args}])
return True
def delete_workplace(workplace=None, delete_config=False, conn=None):
args = {'wp': workplace}
# delete workplace itself (plugin load list, that is)
queries = [
{'cmd': u"""
delete from cfg.cfg_item
where
fk_template = (
select pk
from cfg.cfg_template
where name = 'horstspace.notebook.plugin_load_order'
)
and
workplace = %(wp)s""",
'args': args
}
]
# delete other config items associated with this workplace
if delete_config:
queries.append ({
'cmd': u"""
delete from cfg.cfg_item
where
workplace = %(wp)s""",
'args': args
})
gmPG2.run_rw_queries(link_obj = conn, queries = queries, end_tx = True)
def delete(self, conn=None, pk_option=None):
if conn is None:
# without a gm-dbo connection you can only delete your own options :-)
cmd = u"DELETE FROM cfg.cfg_item WHERE pk = %(pk)s AND owner = CURRENT_USER"
else:
cmd = u"DELETE FROM cfg.cfg_item WHERE pk = %(pk)s"
args = {'pk': pk_option}
gmPG2.run_rw_queries(link_obj = conn, queries = [{'cmd': cmd, 'args': args}], end_tx = True)
def delete_praxis_branch(pk_praxis_branch=None):
if not lock_praxis_branch(pk_praxis_branch = pk_praxis_branch, exclusive = True):
return False
args = {'pk': pk_praxis_branch}
cmd = u"DELETE FROM dem.praxis_branch WHERE pk = %(pk)s"
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
unlock_praxis_branch(pk_praxis_branch = pk_praxis_branch, exclusive = True)
return True
def raise_in_waiting_list(self, current_position=None):
if current_position == 1:
return
cmd = u'select clin.move_waiting_list_entry(%(pos)s, (%(pos)s - 1))'
args = {'pos': current_position}
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
def run(conn=None):
for filename in template_files:
args = {}
args['sname'] = os.path.splitext(filename)[0].replace('-', ', ').replace('_', ', ')
args['lname'] = u'%s (GNUmed Default)' % args['sname']
args['ttype'] = template_type
args['fname'] = filename
queries = []
# delete this template
cmd = u"""delete from ref.paperwork_templates where name_long = %(lname)s"""
queries.append({'cmd': cmd, 'args': args})
# and (re-)import it
# - template
cmd = u"""
INSERT INTO ref.paperwork_templates (
fk_template_type,
instance_type,
name_short,
name_long,
external_version,
filename,
engine,
data
) VALUES (
(SELECT pk FROM ref.form_types WHERE name = %(ttype)s),
%(sname)s,
%(sname)s,
%(lname)s,
'16.0'::TEXT,
%(fname)s,
'I'::TEXT,
'image data missing'::BYTEA
)"""
queries.append({'cmd': cmd, 'args': args})
gmPG2.run_rw_queries(link_obj = conn, queries = queries, end_tx = False)
# - data
cmd = u"""
UPDATE ref.paperwork_templates
SET data = %(data)s::BYTEA
WHERE name_long = %(lname)s"""
gmPG2.file2bytea (
query = cmd,
filename = os.path.join('..', 'sql', 'v15-v16', 'data', filename),
conn = conn,
args = args
)
conn.commit()
return True
def delete_document_type(document_type=None):
if document_type['is_in_use']:
return False
gmPG2.run_rw_queries (
queries = [{
'cmd': 'delete from blobs.doc_type where pk=%s',
'args': [document_type['pk_doc_type']]
}]
)
return True
def delete_keyword_expansion(pk=None):
args = {'pk': pk}
cmd = u"DELETE FROM ref.keyword_expansion WHERE pk = %(pk)s"
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
global __textual_expansion_keywords
__textual_expansion_keywords = None
global __keyword_expansions
__keyword_expansions = None
return True
def delete_billable(pk_billable=None):
cmd = """
DELETE FROM ref.billable
WHERE
pk = %(pk)s
AND
NOT EXISTS (
SELECT 1 FROM bill.bill_item WHERE fk_billable = %(pk)s
)
"""
args = {'pk': pk_billable}
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
def delete_vaccine(vaccine=None):
cmd = 'DELETE FROM ref.vaccine WHERE pk = %(pk)s'
args = {'pk': vaccine}
try:
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
except gmPG2.dbapi.IntegrityError:
_log.exception('cannot delete vaccine [%s]', vaccine)
return False
return True
def delete_comm_channel(pk=None, pk_patient=None, pk_org_unit=None):
if pk_patient is not None:
query = {
'cmd': u"DELETE FROM dem.lnk_identity2comm WHERE pk = %(pk)s AND fk_identity = %(pat)s",
'args': {'pk': pk, 'pat': pk_patient}
}
if pk_org_unit is not None:
query = {
'cmd': u"DELETE FROM dem.lnk_org_unit2comm WHERE pk = %(pk)s AND fk_org_unit = %(unit)s",
'args': {'pk': pk, 'unit': pk_org_unit}
}
gmPG2.run_rw_queries(queries = [query])
def create_address(country_code=None, region_code=None, urb=None, suburb=None, postcode=None, street=None, number=None, subunit=None):
if suburb is not None:
suburb = gmTools.none_if(suburb.strip(), u'')
pk_address = address_exists (
country_code = country_code,
region_code = region_code,
urb = urb,
# suburb = suburb,
postcode = postcode,
street = street,
number = number,
subunit = subunit
)
if pk_address is not None:
return cAddress(aPK_obj = pk_address)
cmd = u"""
SELECT dem.create_address (
%(number)s,
%(street)s,
%(postcode)s,
%(urb)s,
%(region_code)s,
%(country_code)s,
%(subunit)s
)"""
args = {
'number': number,
'street': street,
'postcode': postcode,
'urb': urb,
'region_code': region_code,
'country_code': country_code,
'subunit': subunit
}
queries = [{'cmd': cmd, 'args': args}]
rows, idx = gmPG2.run_rw_queries(queries = queries, return_data = True)
adr = cAddress(aPK_obj = rows[0][0])
if suburb is not None:
queries = [{
# CAVE: suburb will be ignored if there already is one
'cmd': u"UPDATE dem.street SET suburb = %(suburb)s WHERE id = %(pk_street)s AND suburb IS NULL",
'args': {'suburb': suburb, 'pk_street': adr['pk_street']}
}]
rows, idx = gmPG2.run_rw_queries(queries = queries)
return adr
def delete_comm_channel_type(pk_channel_type=None):
cmd = u"""
DELETE FROM dem.enum_comm_types
WHERE
pk = %(pk)s
AND NOT EXISTS (
SELECT 1 FROM dem.lnk_identity2comm WHERE fk_type = %(pk)s
)
AND NOT EXISTS (
SELECT 1 FROM dem.lnk_org_unit2comm WHERE fk_type = %(pk)s
)
"""
gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': {'pk': pk_channel_type}}])
return True
def create_bill_item(pk_encounter=None, pk_billable=None, pk_staff=None):
billable = cBillable(aPK_obj = pk_billable)
cmd = """
INSERT INTO bill.bill_item (
fk_provider,
fk_encounter,
net_amount_per_unit,
currency,
fk_billable
) VALUES (
%(staff)s,
%(enc)s,
%(val)s,
%(curr)s,
%(billable)s
)
RETURNING pk"""
args = {
'staff': pk_staff,
'enc': pk_encounter,
'val': billable['raw_amount'],
'curr': billable['currency'],
'billable': pk_billable
}
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], return_data = True)
return cBillItem(aPK_obj = rows[0][0])
def create_staff(conn=None, db_account=None, password=None, identity=None, short_alias=None):
args = {
'pg_usr': db_account,
'pwd': password,
'person_id': identity,
'sig': short_alias
}
queries = [
{'cmd': u'SELECT gm.create_user(%(pg_usr)s, %(pwd)s)', 'args': args},
{'cmd': u"""
INSERT INTO dem.staff
(fk_identity, db_user, short_alias)
VALUES (
%(person_id)s,
%(pg_usr)s,
%(sig)s
)""",
'args': args
}
]
try:
rows, idx = gmPG2.run_rw_queries(link_obj = conn, queries = queries, end_tx = True)
except gmPG2.dbapi.IntegrityError, e:
if e.pgcode == gmPG2.sql_error_codes.UNIQUE_VIOLATION:
msg = _(
'Cannot add GNUmed user.\n'
'\n'
'The database account [%s] is already listed as a\n'
'GNUmed user. There can only be one GNUmed user\n'
'for each database account.\n'
) % db_account
return False, msg
raise
def delete_org_unit(unit=None):
args = {'pk': unit}
cmd = """DELETE FROM dem.org_unit WHERE
pk = %(pk)s
AND
NOT EXISTS (
SELECT 1 FROM clin.encounter where fk_location = %(pk)s
) AND
NOT EXISTS (
SELECT 1 FROM clin.hospital_stay where fk_org_unit = %(pk)s
) AND
NOT EXISTS (
SELECT 1 FROM clin.procedure where fk_org_unit = %(pk)s
) AND
NOT EXISTS (
SELECT 1 FROM clin.test_org where fk_org_unit = %(pk)s
) AND
NOT EXISTS (
SELECT 1 FROM dem.lnk_org_unit2comm where fk_org_unit = %(pk)s
) AND
NOT EXISTS (
SELECT 1 FROM dem.lnk_org_unit2ext_id where fk_org_unit = %(pk)s
)
"""
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], get_col_idx = False)
return True
def create_data_source(long_name=None, short_name=None, version=None, source=None, language=None):
args = {
'lname': long_name,
'sname': short_name,
'ver': version,
'src': source,
'lang': language
}
cmd = "SELECT pk FROM ref.data_source WHERE name_long = %(lname)s AND name_short = %(sname)s AND version = %(ver)s"
rows, idx = gmPG2.run_ro_queries(queries = [{'cmd': cmd, 'args': args}])
if len(rows) > 0:
return rows[0]['pk']
cmd = """
INSERT INTO ref.data_source (name_long, name_short, version, source, lang)
VALUES (
%(lname)s,
%(sname)s,
%(ver)s,
%(src)s,
%(lang)s
)
RETURNING pk
"""
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], return_data = True)
return rows[0]['pk']
def create_external_care_item(pk_health_issue=None, issue=None, pk_org_unit=None, pk_encounter=None):
args = {
'pk_health_issue': pk_health_issue,
'issue': issue,
'pk_org_unit': pk_org_unit,
'enc': pk_encounter
}
cmd = """
INSERT INTO clin.external_care (
issue,
fk_health_issue,
fk_encounter,
fk_org_unit
) VALUES (
gm.nullify_empty_string(%(issue)s),
(CASE
WHEN gm.is_null_or_blank_string(%(issue)s) IS TRUE THEN %(pk_health_issue)s
ELSE NULL
END)::integer,
%(enc)s,
%(pk_org_unit)s
)
RETURNING pk"""
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], return_data = True, get_col_idx = False)
return cExternalCareItem(aPK_obj = rows[0]['pk'])
def set_translation(self, translation=None):
if translation.strip() == '':
return False
if translation.strip() == self._payload[self._idx['l10n_type']].strip():
return True
rows, idx = gmPG2.run_rw_queries (
queries = [
{'cmd': 'select i18n.i18n(%s)', 'args': [self._payload[self._idx['type']]]},
{'cmd': 'select i18n.upd_tx((select i18n.get_curr_lang()), %(orig)s, %(tx)s)',
'args': {
'orig': self._payload[self._idx['type']],
'tx': translation
}
}
],
return_data = True
)
if not rows[0][0]:
_log.error('cannot set translation to [%s]' % translation)
return False
return self.refetch_payload()
def delete_report_definition(name=None):
queries = [{
'cmd': 'DELETE FROM cfg.report_query WHERE label = %(name)s',
'args': {'name': name}
}]
rows, idx = gmPG2.run_rw_queries(queries=queries)
return True
def create_inbox_message(message_type=None, subject=None, patient=None, staff=None, message_category='clinical'):
success, pk_type = gmTools.input2int(initial = message_type)
if not success:
pk_type = create_inbox_item_type(message_type = message_type, category = message_category)
cmd = """
INSERT INTO dem.message_inbox (
fk_staff,
fk_patient,
fk_inbox_item_type,
comment
) VALUES (
%(staff)s,
%(pat)s,
%(type)s,
gm.nullify_empty_string(%(subject)s)
)
RETURNING pk
"""
args = {
'staff': staff,
'pat': patient,
'type': pk_type,
'subject': subject
}
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], return_data = True, get_col_idx = False)
return cInboxMessage(aPK_obj = rows[0]['pk'])
def add_code(self, pk_code=None):
"""<pk_code> must be a value from ref.coding_system_root.pk_coding_system (clin.lnk_code2item_root.fk_generic_code)"""
if pk_code in self._payload[self._idx['pk_generic_codes']]:
return
cmd = """
INSERT INTO clin.lnk_code2narrative
(fk_item, fk_generic_code)
SELECT
%(item)s,
%(code)s
WHERE NOT EXISTS (
SELECT 1 FROM clin.lnk_code2narrative
WHERE
fk_item = %(item)s
AND
fk_generic_code = %(code)s
)"""
args = {
'item': self._payload[self._idx['pk_narrative']],
'code': pk_code
}
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}])
return
def deactivate_staff(conn=None, pk_staff=None):
# 1) inactivate staff entry
staff = cStaff(aPK_obj=pk_staff)
staff['is_active'] = False
staff.save_payload(conn=conn) # FIXME: error handling
# 2) disable database account login
rows, idx = gmPG2.run_rw_queries(link_obj=conn,
queries=[{
'cmd': 'select gm.disable_user(%s)',
'args': [staff['db_user']]
}],
end_tx=True)
return True
def create_praxis_branch(pk_org_unit=None):
args = {'fk_unit': pk_org_unit}
cmd1 = """
INSERT INTO dem.praxis_branch (fk_org_unit)
SELECT %(fk_unit)s WHERE NOT EXISTS (
SELECT 1 FROM dem.praxis_branch WHERE fk_org_unit = %(fk_unit)s
)
"""
cmd2 = """SELECT * from dem.v_praxis_branches WHERE pk_org_unit = %(fk_unit)s"""
queries = [
{'cmd': cmd1, 'args': args},
{'cmd': cmd2, 'args': args}
]
rows, idx = gmPG2.run_rw_queries(queries = queries, return_data = True, get_col_idx = True)
return cPraxisBranch(row = {'data': rows[0], 'idx': idx, 'pk_field': 'pk_praxis_branch'})
def save_report_definition(name=None, query=None, overwrite=False):
if not overwrite:
if report_exists(name=name):
return False
args = {'name': name, 'query': query}
queries = [{
'cmd': u'DELETE FROM cfg.report_query WHERE label = %(name)s',
'args': args
}, {
'cmd':
u'INSERT INTO cfg.report_query (label, cmd) VALUES (%(name)s, %(query)s)',
'args': args
}]
rows, idx = gmPG2.run_rw_queries(queries=queries)
return True
def delete_org(organization=None):
args = {'pk': organization}
cmd = u"""
DELETE FROM dem.org
WHERE
pk = %(pk)s
AND NOT EXISTS (
SELECT 1 FROM dem.org_unit WHERE fk_org = %(pk)s
)
"""
rows, idx = gmPG2.run_rw_queries(queries=[{
'cmd': cmd,
'args': args
}],
get_col_idx=False)
return True
def delete_address(pk_address=None):
cmd = """
DELETE FROM dem.address
WHERE
id = %(pk)s
AND
NOT EXISTS ((
SELECT 1 FROM dem.org_unit WHERE fk_address = %(pk)s LIMIT 1
) UNION (
SELECT 1 FROM dem.lnk_identity2comm WHERE fk_address = %(pk)s LIMIT 1
) UNION (
SELECT 1 FROM dem.lnk_person_org_address WHERE id_address = %(pk)s LIMIT 1
))
"""
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': {'pk': pk_address}}])
return True
def activate_staff(conn=None, pk_staff=None):
# 1) activate staff entry
staff = cStaff(aPK_obj=pk_staff)
staff['is_active'] = True
staff.save_payload(conn=conn) # FIXME: error handling
# 2) enable database account login
rowx, idx = gmPG2.run_rw_queries(
link_obj=conn,
# password does not matter because PG account must already exist
queries=[{
'cmd': 'select gm.create_user(%s, %s)',
'args': [staff['db_user'], 'flying wombat']
}],
end_tx=True)
return True
def create_praxis_branches(pk_org_units=None):
queries = []
for pk in pk_org_units:
args = {'fk_unit': pk}
cmd = """
INSERT INTO dem.praxis_branch (fk_org_unit)
SELECT %(fk_unit)s WHERE NOT EXISTS (
SELECT 1 FROM dem.praxis_branch WHERE fk_org_unit = %(fk_unit)s
)
"""
queries.append({'cmd': cmd, 'args': args})
args = {'fk_units': pk_org_units}
cmd = """SELECT * from dem.v_praxis_branches WHERE pk_org_unit = ANY(%(fk_units)s)"""
queries.append({'cmd': cmd, 'args': args})
rows, idx = gmPG2.run_rw_queries(queries = queries, return_data = True, get_col_idx = True)
return [ cPraxisBranch(row = {'data': r, 'idx': idx, 'pk_field': 'pk_praxis_branch'}) for r in rows ]
def create_staff(conn=None,
db_account=None,
password=None,
identity=None,
short_alias=None):
args = {
'pg_usr': db_account,
'pwd': password,
'person_id': identity,
'sig': short_alias
}
queries = [{
'cmd': 'SELECT gm.create_user(%(pg_usr)s, %(pwd)s)',
'args': args
}, {
'cmd': """
INSERT INTO dem.staff
(fk_identity, db_user, short_alias)
VALUES (
%(person_id)s,
%(pg_usr)s,
%(sig)s
)""",
'args': args
}]
created = False
try:
rows, idx = gmPG2.run_rw_queries(link_obj=conn,
queries=queries,
end_tx=True)
created = True
except gmPG2.dbapi.IntegrityError as e:
if e.pgcode != gmPG2.sql_error_codes.UNIQUE_VIOLATION:
raise
if created:
return True, None
msg = _('Cannot add GNUmed user.\n'
'\n'
'The database account [%s] is already listed as a\n'
'GNUmed user. There can only be one GNUmed user\n'
'for each database account.\n') % db_account
return False, msg
def search_text_across_emrs(search_term=None):
if search_term is None:
return []
if search_term.strip() == '':
return []
#cmd = u'select * from clin.v_narrative4search where narrative ~* %(term)s order by pk_patient limit 1000'
#rows, idx = gmPG2.run_ro_queries(queries = [{'cmd': cmd, 'args': {'term': search_term}}], get_col_idx = False)
cmd = u'select * from v_narrative4search where narrative ~* %(term)s order by pk_patient limit 1000'
queries = [
{'cmd': _VIEW_clin_v_narrative4search},
{'cmd': cmd, 'args': {'term': search_term}}
]
rows, idx = gmPG2.run_rw_queries(queries = queries, get_col_idx = True, return_data = True)
return rows
def create_bill(conn=None, invoice_id=None):
args = {'inv_id': invoice_id}
cmd = """
INSERT INTO bill.bill (invoice_id)
VALUES (gm.nullify_empty_string(%(inv_id)s))
RETURNING pk
"""
rows, idx = gmPG2.run_rw_queries(link_obj=conn,
queries=[{
'cmd': cmd,
'args': args
}],
return_data=True,
get_col_idx=False)
return cBill(aPK_obj=rows[0]['pk'])
def create_org_unit(pk_organization=None, unit=None, link_obj=None):
_log.debug('creating org unit [%s:%s]', unit, pk_organization)
args = {'desc': unit, 'pk_org': pk_organization}
cmd1 = """
INSERT INTO dem.org_unit (description, fk_org) SELECT
%(desc)s,
%(pk_org)s
WHERE NOT EXISTS (
SELECT 1 FROM dem.org_unit WHERE description = %(desc)s AND fk_org = %(pk_org)s
)"""
cmd2 = _SQL_get_org_unit % 'unit = %(desc)s AND pk_org = %(pk_org)s'
queries = [
{'cmd': cmd1, 'args': args},
{'cmd': cmd2, 'args': args}
]
rows, idx = gmPG2.run_rw_queries(link_obj = link_obj, queries = queries, get_col_idx = True, return_data = True)
return cOrgUnit(row = {'data': rows[0], 'idx': idx, 'pk_field': 'pk_org_unit'})
def delete_staff(conn=None, pk_staff=None):
queries = [{'cmd': 'DELETE FROM dem.staff WHERE pk = %(pk)s', 'args': {'pk': pk_staff}}]
try:
rows, idx = gmPG2.run_rw_queries(link_obj = conn, queries = queries, end_tx = True)
except gmPG2.dbapi.IntegrityError as e:
if e.pgcode == gmPG2.sql_error_codes.FOREIGN_KEY_VIOLATION: # 23503 foreign_key_violation
msg = _(
'Cannot delete GNUmed staff member because the\n'
'database still contains data linked to it.\n'
'\n'
'The account was deactivated instead.'
)
deactivate_staff(conn = conn, pk_staff = pk_staff)
return False, msg
raise
return True, None
def create_org(organization=None, category=None, link_obj=None):
org = org_exists(link_obj = link_obj, organization = organization, category = category)
if org is not None:
return org
args = {'desc': organization, 'cat': category}
if isinstance(category, str):
cat_part = '(SELECT pk FROM dem.org_category WHERE description = %(cat)s)'
else:
cat_part = '%(cat)s'
cmd = 'INSERT INTO dem.org (description, fk_category) VALUES (%%(desc)s, %s) RETURNING pk' % cat_part
rows, idx = gmPG2.run_rw_queries(link_obj = link_obj, queries = [{'cmd': cmd, 'args': args}], get_col_idx = False, return_data = True)
return cOrg(aPK_obj = rows[0][0], link_obj = link_obj)
def create_export_item(description=None,
pk_identity=None,
pk_doc_obj=None,
filename=None):
args = {
u'desc': description,
u'pk_obj': pk_doc_obj,
u'pk_pat': pk_identity,
u'fname': filename
}
cmd = u"""
INSERT INTO clin.export_item (
description,
fk_doc_obj,
fk_identity,
data,
filename
) VALUES (
gm.nullify_empty_string(%(desc)s),
%(pk_obj)s,
(CASE
WHEN %(pk_obj)s IS NULL THEN %(pk_pat)s
ELSE NULL::integer
END),
(CASE
WHEN %(pk_obj)s IS NULL THEN %(fname)s::bytea
ELSE NULL::bytea
END),
(CASE
WHEN %(pk_obj)s IS NULL THEN gm.nullify_empty_string(%(fname)s)
ELSE NULL
END)
)
RETURNING pk
"""
rows, idx = gmPG2.run_rw_queries(queries=[{
'cmd': cmd,
'args': args
}],
return_data=True,
get_col_idx=False)
return cExportItem(aPK_obj=rows[0]['pk'])
def create_allergy(allergene=None, allg_type=None, episode_id=None, encounter_id=None):
"""Creates a new allergy clinical item.
allergene - allergic substance
allg_type - allergy or sensitivity, pk or string
encounter_id - encounter's primary key
episode_id - episode's primary key
"""
cmd = u"""
SELECT pk_allergy
FROM clin.v_pat_allergies
WHERE
pk_patient = (SELECT fk_patient FROM clin.encounter WHERE pk = %(enc)s)
AND
allergene = %(allergene)s
"""
#args = {'enc': encounter_id, 'substance': substance}
args = {'enc': encounter_id, 'allergene': allergene}
rows, idx = gmPG2.run_ro_queries(queries = [{'cmd': cmd, 'args': args}])
if len(rows) > 0:
# don't implicitely change existing data
return cAllergy(aPK_obj = rows[0][0])
# insert new allergy
queries = []
if type(allg_type) == types.IntType:
cmd = u"""
insert into clin.allergy (fk_type, fk_encounter, fk_episode, allergene, substance)
values (%s, %s, %s, %s, %s)"""
else:
cmd = u"""
insert into clin.allergy (fk_type, fk_encounter, fk_episode, allergene, substance)
values ((select pk from clin._enum_allergy_type where value = %s), %s, %s, %s, %s)"""
queries.append({'cmd': cmd, 'args': [allg_type, encounter_id, episode_id, allergene, allergene]})
cmd = u"select currval('clin.allergy_id_seq')"
queries.append({'cmd': cmd})
rows, idx = gmPG2.run_rw_queries(queries=queries, return_data=True)
allergy = cAllergy(aPK_obj = rows[0][0])
return allergy
def delete_tag_image(tag_image=None):
args = {'pk': tag_image}
cmd = """
DELETE FROM ref.tag_image
WHERE
pk = %(pk)s
AND
NOT EXISTS (
SELECT 1
FROM dem.identity_tag
WHERE fk_tag = %(pk)s
LIMIT 1
)
RETURNING 1
"""
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], return_data = True)
if len(rows) == 0:
return False
return True
def set_role(self, conn=None, role=None):
if role.strip() == self._payload[self._idx['role']]:
return True
cmd = 'SELECT gm.add_user_to_permission_group(%(usr)s::name, %(grp)s::name)'
args = {
'usr': self._payload[self._idx['db_user']],
'grp': _map_gm_role2pg_group[role.strip()]
}
rows, idx = gmPG2.run_rw_queries (
link_obj = conn,
queries = [{'cmd': cmd, 'args': args}],
get_col_idx = False,
return_data = True,
end_tx = True
)
if not rows[0][0]:
return False
self.refetch_payload()
return True
def reattach(self, pk_doc=None):
if pk_doc == self._payload[self._idx['pk_doc']]:
return True
cmd = """
UPDATE blobs.doc_obj SET
fk_doc = %(pk_doc_target)s,
-- coalesce needed for no-parts target docs
seq_idx = (SELECT coalesce(max(seq_idx) + 1, 1) FROM blobs.doc_obj WHERE fk_doc = %(pk_doc_target)s)
WHERE
EXISTS(SELECT 1 FROM blobs.doc_med WHERE pk = %(pk_doc_target)s)
AND
pk = %(pk_obj)s
AND
xmin = %(xmin_doc_obj)s
RETURNING fk_doc
"""
args = {
'pk_doc_target': pk_doc,
'pk_obj': self.pk_obj,
'xmin_doc_obj': self._payload[self._idx['xmin_doc_obj']]
}
rows, idx = gmPG2.run_rw_queries(queries=[{
'cmd': cmd,
'args': args
}],
return_data=True,
get_col_idx=False)
if len(rows) == 0:
return False
# The following should never hold true because the target
# fk_doc is returned from the query and it is checked for
# equality before the UPDATE already. Assuming the update
# failed to update a row because the target fk_doc did
# not exist we would not get *any* rows in return - for
# which condition we also already checked
if rows[0]['fk_doc'] == self._payload[self._idx['pk_doc']]:
return False
self.refetch_payload()
return True
def create_incoming_data(data_type, filename):
args = {'typ': data_type}
cmd = u"""
INSERT INTO clin.incoming_data_unmatched (type, data)
VALUES (%(typ)s, 'new data'::bytea)
RETURNING pk"""
rows, idx = gmPG2.run_rw_queries(queries=[{
'cmd': cmd,
'args': args
}],
return_data=True,
get_col_idx=False)
pk = rows[0]['pk']
incoming = cIncomingData(aPK_obj=pk)
if not incoming.update_data_from_file(fname=filename):
_log.debug(
'cannot update newly created incoming_data record from file, deleting stub'
)
delete_incoming_data(incoming_data=pk)
return None
return incoming
def create_billable(code=None, term=None, data_source=None, return_existing=False):
args = {
'code': code.strip(),
'term': term.strip(),
'data_src': data_source
}
cmd = """
INSERT INTO ref.billable (code, term, fk_data_source)
SELECT
%(code)s,
%(term)s,
%(data_src)s
WHERE NOT EXISTS (
SELECT 1 FROM ref.billable
WHERE
code = %(code)s
AND
term = %(term)s
AND
fk_data_source = %(data_src)s
)
RETURNING pk"""
rows, idx = gmPG2.run_rw_queries(queries = [{'cmd': cmd, 'args': args}], get_col_idx = False, return_data = True)
if len(rows) > 0:
return cBillable(aPK_obj = rows[0]['pk'])
if not return_existing:
return None
cmd = """
SELECT * FROM ref.v_billables
WHERE
code = %(code)s
AND
term = %(term)s
AND
pk_data_source = %(data_src)s
"""
rows, idx = gmPG2.run_ro_queries(queries = [{'cmd': cmd, 'args': args}], get_col_idx = True)
return cBillable(row = {'data': rows[0], 'idx': idx, 'pk_field': 'pk_billable'})
def create_inbox_message(message_type=None,
subject=None,
patient=None,
staff=None,
message_category=u'clinical'):
success, pk_type = gmTools.input2int(initial=message_type)
if not success:
pk_type = create_inbox_item_type(message_type=message_type,
category=message_category)
cmd = u"""
INSERT INTO dem.message_inbox (
fk_staff,
fk_patient,
fk_inbox_item_type,
comment
) VALUES (
%(staff)s,
%(pat)s,
%(type)s,
gm.nullify_empty_string(%(subject)s)
)
RETURNING pk
"""
args = {
u'staff': staff,
u'pat': patient,
u'type': pk_type,
u'subject': subject
}
rows, idx = gmPG2.run_rw_queries(queries=[{
'cmd': cmd,
'args': args
}],
return_data=True,
get_col_idx=False)
return cInboxMessage(aPK_obj=rows[0]['pk'])
def create_tag_image(description=None, link_obj=None):
args = {'desc': description, 'img': ''}
cmd = """
INSERT INTO ref.tag_image (
description,
image
) VALUES (
%(desc)s,
%(img)s::bytea
)
RETURNING pk
"""
rows, idx = gmPG2.run_rw_queries (
link_obj = link_obj,
queries = [{'cmd': cmd, 'args': args}],
end_tx = True,
return_data = True,
get_col_idx = False
)
return cTagImage(aPK_obj = rows[0]['pk'])
def create_vaccine(pk_drug_product=None,
product_name=None,
indications=None,
is_live=None):
assert (is_live is not None), '<is_live> must not be <None>'
conn = gmPG2.get_connection(readonly=False)
if pk_drug_product is None:
#prep = _('vaccine')
prep = 'vaccine'
_log.debug('creating vaccine drug product [%s %s]', product_name, prep)
vacc_prod = gmMedication.create_drug_product(
product_name=product_name,
preparation=prep,
return_existing=True,
# indications are ref.dose rows
doses=indications,
link_obj=conn)
#conn.commit()
vacc_prod['atc'] = 'J07'
vacc_prod.save(conn=conn)
pk_drug_product = vacc_prod['pk_drug_product']
cmd = 'INSERT INTO ref.vaccine (fk_drug_product, is_live) values (%(pk_drug_product)s, %(live)s) RETURNING pk'
queries = [{
'cmd': cmd,
'args': {
'pk_drug_product': pk_drug_product,
'live': is_live
}
}]
rows, idx = gmPG2.run_rw_queries(link_obj=conn,
queries=queries,
get_col_idx=False,
return_data=True,
end_tx=True)
conn.close()
return cVaccine(aPK_obj=rows[0]['pk'])
def create_data_source(long_name=None,
short_name=None,
version=None,
source=None,
language=None):
args = {
'lname': long_name,
'sname': short_name,
'ver': version,
'src': source,
'lang': language
}
cmd = "SELECT pk FROM ref.data_source WHERE name_long = %(lname)s AND name_short = %(sname)s AND version = %(ver)s"
rows, idx = gmPG2.run_ro_queries(queries=[{'cmd': cmd, 'args': args}])
if len(rows) > 0:
return rows[0]['pk']
cmd = """
INSERT INTO ref.data_source (name_long, name_short, version, source, lang)
VALUES (
%(lname)s,
%(sname)s,
%(ver)s,
%(src)s,
%(lang)s
)
RETURNING pk
"""
rows, idx = gmPG2.run_rw_queries(queries=[{
'cmd': cmd,
'args': args
}],
return_data=True)
return rows[0]['pk']
def create_document(document_type=None, encounter=None, episode=None, link_obj=None):
"""Returns new document instance or raises an exception."""
try:
int(document_type)
cmd = """INSERT INTO blobs.doc_med (fk_type, fk_encounter, fk_episode) VALUES (%(type)s, %(enc)s, %(epi)s) RETURNING pk"""
except ValueError:
create_document_type(document_type = document_type)
cmd = """
INSERT INTO blobs.doc_med (
fk_type,
fk_encounter,
fk_episode
) VALUES (
coalesce (
(SELECT pk from blobs.doc_type bdt where bdt.name = %(type)s),
(SELECT pk from blobs.doc_type bdt where _(bdt.name) = %(type)s)
),
%(enc)s,
%(epi)s
) RETURNING pk"""
args = {'type': document_type, 'enc': encounter, 'epi': episode}
rows, idx = gmPG2.run_rw_queries(link_obj = link_obj, queries = [{'cmd': cmd, 'args': args}], return_data = True)
doc = cDocument(aPK_obj = rows[0][0], link_obj = link_obj)
return doc
def create_dynamic_hint(link_obj=None,
query=None,
title=None,
hint=None,
source=None):
args = {
'query': query,
'title': title,
'hint': hint,
'source': source,
'usr': gmStaff.gmCurrentProvider()['db_user']
}
cmd = """
INSERT INTO ref.auto_hint (
query,
title,
hint,
source,
lang
) VALUES (
gm.nullify_empty_string(%(query)s),
gm.nullify_empty_string(%(title)s),
gm.nullify_empty_string(%(hint)s),
gm.nullify_empty_string(%(source)s),
i18n.get_curr_lang(%(usr)s)
)
RETURNING pk
"""
rows, idx = gmPG2.run_rw_queries(link_obj=link_obj,
queries=[{
'cmd': cmd,
'args': args
}],
return_data=True,
get_col_idx=True)
return cDynamicHint(aPK_obj=rows[0]['pk'], link_obj=link_obj)
def delete_vaccination(vaccination=None):
cmd = u"""DELETE FROM clin.vaccination WHERE pk = %(pk)s"""
args = {'pk': vaccination}
gmPG2.run_rw_queries(queries=[{'cmd': cmd, 'args': args}])
def loinc_import(data_fname=None,
license_fname=None,
version=None,
conn=None,
lang='en_EN'):
if version is None:
version = get_version(license_fname=license_fname)
if version is None:
raise ValueError('cannot detect LOINC version')
_log.debug('importing LOINC version [%s]', version)
# clean out staging area
curs = conn.cursor()
cmd = """DELETE FROM staging.loinc_staging"""
gmPG2.run_rw_queries(link_obj=curs, queries=[{'cmd': cmd}])
curs.close()
conn.commit()
_log.debug('staging table emptied')
# import data from csv file into staging table
csv_file = io.open(data_fname,
mode='rt',
encoding='utf8',
errors='replace')
loinc_reader = gmTools.unicode_csv_reader(csv_file,
delimiter="\t",
quotechar='"')
curs = conn.cursor()
cmd = """INSERT INTO staging.loinc_staging values (%s%%s)""" % (
'%s, ' * (len(loinc_fields) - 1))
first = False
for loinc_line in loinc_reader:
if not first:
first = True
continue
gmPG2.run_rw_queries(link_obj=curs,
queries=[{
'cmd': cmd,
'args': loinc_line
}])
curs.close()
conn.commit()
csv_file.close()
_log.debug('staging table loaded')
# create data source record
in_file = io.open(license_fname,
mode='rt',
encoding='utf8',
errors='replace')
desc = in_file.read()
in_file.close()
args = {
'ver': version,
'desc': desc,
'url': origin_url,
'name_long': name_long,
'name_short': name_short,
'lang': lang
}
queries = [
# insert if not existing
{
'args':
args,
'cmd':
"""
INSERT INTO ref.data_source (name_long, name_short, version) SELECT
%(name_long)s,
%(name_short)s,
%(ver)s
WHERE NOT EXISTS (
SELECT 1 FROM ref.data_source WHERE
name_long = %(name_long)s
AND
name_short = %(name_short)s
AND
version = %(ver)s
)"""
},
# update non-unique fields
{
'args':
args,
'cmd':
"""
UPDATE ref.data_source SET
description = %(desc)s,
source = %(url)s,
lang = %(lang)s
WHERE
name_long = %(name_long)s
AND
name_short = %(name_short)s
AND
version = %(ver)s
"""
},
# retrieve PK of data source
{
'args':
args,
'cmd':
"""SELECT pk FROM ref.data_source WHERE name_short = %(name_short)s AND version = %(ver)s"""
}
]
curs = conn.cursor()
rows, idx = gmPG2.run_rw_queries(link_obj=curs,
queries=queries,
return_data=True)
data_src_pk = rows[0][0]
curs.close()
_log.debug('data source record created or updated, pk is #%s', data_src_pk)
# import from staging table to real table
args = {'src_pk': data_src_pk}
queries = []
queries.append({
'args':
args,
'cmd':
"""
INSERT INTO ref.loinc (
fk_data_source, term, code
)
SELECT
%(src_pk)s,
coalesce (
nullif(long_common_name, ''),
(
coalesce(nullif(component, '') || ':', '') ||
coalesce(nullif(property, '') || ':', '') ||
coalesce(nullif(time_aspect, '') || ':', '') ||
coalesce(nullif(system, '') || ':', '') ||
coalesce(nullif(scale_type, '') || ':', '') ||
coalesce(nullif(method_type, '') || ':', '')
)
),
nullif(loinc_num, '')
FROM
staging.loinc_staging st_ls
WHERE NOT EXISTS (
SELECT 1 FROM ref.loinc r_l WHERE
r_l.fk_data_source = %(src_pk)s
AND
r_l.code = nullif(st_ls.loinc_num, '')
AND
r_l.term = coalesce (
nullif(st_ls.long_common_name, ''),
(
coalesce(nullif(st_ls.component, '') || ':', '') ||
coalesce(nullif(st_ls.property, '') || ':', '') ||
coalesce(nullif(st_ls.time_aspect, '') || ':', '') ||
coalesce(nullif(st_ls.system, '') || ':', '') ||
coalesce(nullif(st_ls.scale_type, '') || ':', '') ||
coalesce(nullif(st_ls.method_type, '') || ':', '')
)
)
)"""
})
queries.append({
'args':
args,
'cmd':
"""
UPDATE ref.loinc SET
comment = nullif(st_ls.comments, ''),
component = nullif(st_ls.component, ''),
property = nullif(st_ls.property, ''),
time_aspect = nullif(st_ls.time_aspect, ''),
system = nullif(st_ls.system, ''),
scale_type = nullif(st_ls.scale_type, ''),
method_type = nullif(st_ls.method_type, ''),
related_names_1_old = nullif(st_ls.related_names_1_old, ''),
grouping_class = nullif(st_ls.class, ''),
loinc_internal_source = nullif(st_ls.source, ''),
dt_last_change = nullif(st_ls.dt_last_change, ''),
change_type = nullif(st_ls.change_type, ''),
answer_list = nullif(st_ls.answer_list, ''),
code_status = nullif(st_ls.status, ''),
maps_to = nullif(st_ls.map_to, ''),
scope = nullif(st_ls.scope, ''),
normal_range = nullif(st_ls.normal_range, ''),
ipcc_units = nullif(st_ls.ipcc_units, ''),
reference = nullif(st_ls.reference, ''),
exact_component_synonym = nullif(st_ls.exact_component_synonym, ''),
molar_mass = nullif(st_ls.molar_mass, ''),
grouping_class_type = nullif(st_ls.class_type, '')::smallint,
formula = nullif(st_ls.formula, ''),
species = nullif(st_ls.species, ''),
example_answers = nullif(st_ls.example_answers, ''),
acs_synonyms = nullif(st_ls.acs_synonyms, ''),
base_name = nullif(st_ls.base_name, ''),
final = nullif(st_ls.final, ''),
naa_ccr_id = nullif(st_ls.naa_ccr_id, ''),
code_table = nullif(st_ls.code_table, ''),
is_set_root = nullif(st_ls.is_set_root, '')::boolean,
panel_elements = nullif(st_ls.panel_elements, ''),
survey_question_text = nullif(st_ls.survey_question_text, ''),
survey_question_source = nullif(st_ls.survey_question_source, ''),
units_required = nullif(st_ls.units_required, ''),
submitted_units = nullif(st_ls.submitted_units, ''),
related_names_2 = nullif(st_ls.related_names_2, ''),
short_name = nullif(st_ls.short_name, ''),
order_obs = nullif(st_ls.order_obs, ''),
cdisc_common_tests = nullif(st_ls.cdisc_common_tests, ''),
hl7_field_subfield_id = nullif(st_ls.hl7_field_subfield_id, ''),
external_copyright_notice = nullif(st_ls.external_copyright_notice, ''),
example_units = nullif(st_ls.example_units, ''),
inpc_percentage = nullif(st_ls.inpc_percentage, ''),
long_common_name = nullif(st_ls.long_common_name, '')
FROM
staging.loinc_staging st_ls
WHERE
fk_data_source = %(src_pk)s
AND
code = nullif(st_ls.loinc_num, '')
AND
term = coalesce (
nullif(st_ls.long_common_name, ''),
(
coalesce(nullif(st_ls.component, '') || ':', '') ||
coalesce(nullif(st_ls.property, '') || ':', '') ||
coalesce(nullif(st_ls.time_aspect, '') || ':', '') ||
coalesce(nullif(st_ls.system, '') || ':', '') ||
coalesce(nullif(st_ls.scale_type, '') || ':', '') ||
coalesce(nullif(st_ls.method_type, '') || ':', '')
)
)
"""
})
curs = conn.cursor()
gmPG2.run_rw_queries(link_obj=curs, queries=queries)
curs.close()
conn.commit()
_log.debug('transfer from staging table to real table done')
# clean out staging area
curs = conn.cursor()
cmd = """DELETE FROM staging.loinc_staging"""
gmPG2.run_rw_queries(link_obj=curs, queries=[{'cmd': cmd}])
curs.close()
conn.commit()
_log.debug('staging table emptied')
return True
def get_address_types(identity=None):
cmd = 'select id as pk, name, _(name) as l10n_name from dem.address_type'
rows, idx = gmPG2.run_rw_queries(queries=[{'cmd': cmd}])
return rows
def create_address(country_code=None,
region_code=None,
urb=None,
suburb=None,
postcode=None,
street=None,
number=None,
subunit=None):
if suburb is not None:
suburb = gmTools.none_if(suburb.strip(), '')
pk_address = address_exists(
country_code=country_code,
region_code=region_code,
urb=urb,
# suburb = suburb,
postcode=postcode,
street=street,
number=number,
subunit=subunit)
if pk_address is not None:
return cAddress(aPK_obj=pk_address)
cmd = """
SELECT dem.create_address (
%(number)s,
%(street)s,
%(postcode)s,
%(urb)s,
%(region_code)s,
%(country_code)s,
%(subunit)s
)"""
args = {
'number': number,
'street': street,
'postcode': postcode,
'urb': urb,
'region_code': region_code,
'country_code': country_code,
'subunit': subunit
}
queries = [{'cmd': cmd, 'args': args}]
rows, idx = gmPG2.run_rw_queries(queries=queries, return_data=True)
adr = cAddress(aPK_obj=rows[0][0])
if suburb is not None:
queries = [{
# CAVE: suburb will be ignored if there already is one
'cmd':
"UPDATE dem.street SET suburb = %(suburb)s WHERE id = %(pk_street)s AND suburb IS NULL",
'args': {
'suburb': suburb,
'pk_street': adr['pk_street']
}
}]
rows, idx = gmPG2.run_rw_queries(queries=queries)
return adr