コード例 #1
0
ファイル: gmATC.py プロジェクト: ncqgm/gnumed
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
コード例 #2
0
ファイル: gmAutoHints.py プロジェクト: sk/gnumed
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
コード例 #3
0
ファイル: gmDemographicRecord.py プロジェクト: ncqgm/gnumed
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
コード例 #4
0
ファイル: gmDemographicRecord.py プロジェクト: sk/gnumed
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
コード例 #5
0
ファイル: gmOrganization.py プロジェクト: ncqgm/gnumed
	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}])
コード例 #6
0
ファイル: gmAutoHints.py プロジェクト: sk/gnumed
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
コード例 #7
0
ファイル: gmDocuments.py プロジェクト: ncqgm/gnumed
	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
コード例 #8
0
ファイル: gmDocuments.py プロジェクト: sk/gnumed
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
コード例 #9
0
ファイル: gmCfg.py プロジェクト: sk/gnumed
	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
コード例 #10
0
ファイル: gmPraxis.py プロジェクト: sk/gnumed
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)
コード例 #11
0
ファイル: gmCfg.py プロジェクト: sk/gnumed
	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)
コード例 #12
0
ファイル: gmPraxis.py プロジェクト: sk/gnumed
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
コード例 #13
0
ファイル: gmPraxis.py プロジェクト: sk/gnumed
	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}])
コード例 #14
0
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
コード例 #15
0
ファイル: gmDocuments.py プロジェクト: ncqgm/gnumed
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
コード例 #16
0
ファイル: gmKeywordExpansion.py プロジェクト: sk/gnumed
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
コード例 #17
0
ファイル: gmBilling.py プロジェクト: ncqgm/gnumed
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}])
コード例 #18
0
ファイル: gmVaccination.py プロジェクト: ncqgm/gnumed
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
コード例 #19
0
ファイル: gmDemographicRecord.py プロジェクト: sk/gnumed
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])
コード例 #20
0
ファイル: gmDemographicRecord.py プロジェクト: sk/gnumed
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
コード例 #21
0
ファイル: gmDemographicRecord.py プロジェクト: sk/gnumed
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
コード例 #22
0
ファイル: gmBilling.py プロジェクト: ncqgm/gnumed
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])
コード例 #23
0
ファイル: gmStaff.py プロジェクト: sk/gnumed
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
コード例 #24
0
ファイル: gmOrganization.py プロジェクト: ncqgm/gnumed
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
コード例 #25
0
ファイル: gmCoding.py プロジェクト: ncqgm/gnumed
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']
コード例 #26
0
ファイル: gmExternalCare.py プロジェクト: ncqgm/gnumed
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'])
コード例 #27
0
ファイル: gmDocuments.py プロジェクト: ncqgm/gnumed
	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()
コード例 #28
0
ファイル: gmDataMining.py プロジェクト: ncqgm/gnumed
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
コード例 #29
0
ファイル: gmProviderInbox.py プロジェクト: ncqgm/gnumed
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'])
コード例 #30
0
ファイル: gmClinNarrative.py プロジェクト: ncqgm/gnumed
	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
コード例 #31
0
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
コード例 #32
0
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'})
コード例 #33
0
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
コード例 #34
0
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
コード例 #35
0
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
コード例 #36
0
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
コード例 #37
0
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 ]
コード例 #38
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': '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
コード例 #39
0
ファイル: gmClinNarrative.py プロジェクト: rockdriven/gnumed
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
コード例 #40
0
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'])
コード例 #41
0
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'})
コード例 #42
0
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
コード例 #43
0
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)
コード例 #44
0
ファイル: gmExportArea.py プロジェクト: jeromecc/gnumed
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'])
コード例 #45
0
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
コード例 #46
0
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
コード例 #47
0
	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
コード例 #48
0
    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
コード例 #49
0
ファイル: gmIncomingData.py プロジェクト: jeromecc/gnumed
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
コード例 #50
0
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'})
コード例 #51
0
ファイル: gmProviderInbox.py プロジェクト: jeromecc/gnumed
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'])
コード例 #52
0
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'])
コード例 #53
0
ファイル: gmVaccination.py プロジェクト: mpdo2017/gnumed
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'])
コード例 #54
0
ファイル: gmCoding.py プロジェクト: ncqgm/gnumed
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']
コード例 #55
0
ファイル: gmDocuments.py プロジェクト: mpdo2017/gnumed
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
コード例 #56
0
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)
コード例 #57
0
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}])
コード例 #58
0
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
コード例 #59
0
ファイル: gmDemographicRecord.py プロジェクト: weeksjm/gnumed
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
コード例 #60
0
ファイル: gmDemographicRecord.py プロジェクト: weeksjm/gnumed
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