def rio_add_bay_lookup(viewmaker: ViewMaker, basecolumn_ward: str,
basecolumn_bay: str, column_prefix: str,
internal_alias_prefix: str) -> None:
assert basecolumn_ward, "Missing basecolumn_ward"
assert basecolumn_bay, "Missing basecolumn_bay"
assert internal_alias_prefix, "Missing internal_alias_prefix"
if column_prefix:
column_prefix += '_'
viewmaker.add_select("""
{basetable}.{basecolumn_ward} AS {cp}Ward_Code,
{ap}_ward.WardDescription AS {cp}Ward_Description,
{basetable}.{basecolumn_bay} AS {cp}Bay_Code,
{ap}_bay.BayDescription AS {cp}Bay_Description
""".format(
basetable=viewmaker.basetable,
basecolumn_ward=basecolumn_ward,
basecolumn_bay=basecolumn_bay,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN (
ImsBay {ap}_bay
INNER JOIN ImsWard {ap}_ward
ON {ap}_ward.WardCode = {ap}_bay.WardCode
) ON {ap}_bay.WardCode = {basetable}.{basecolumn_ward}
AND {ap}_bay.BayCode = {basetable}.{basecolumn_bay}
""".format(
ap=internal_alias_prefix,
basetable=viewmaker.basetable,
basecolumn_ward=basecolumn_ward,
basecolumn_bay=basecolumn_bay,
))
viewmaker.record_lookup_table_keyfield('ImsBay', ['WardCode', 'BayCode'])
viewmaker.record_lookup_table_keyfield('ImsWard', ['WardCode'])
def rio_add_gp_practice_lookup(viewmaker: ViewMaker, basecolumn: str,
column_prefix: str,
internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert column_prefix, "Missing column_prefix"
assert internal_alias_prefix, "Missing internal_alias_prefix"
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Code,
{ap}_prac.CodeDescription AS {cp}_Description,
{ap}_prac.AddressLine1 AS {cp}_Address_Line_1,
{ap}_prac.AddressLine2 AS {cp}_Address_Line_2,
{ap}_prac.AddressLine3 AS {cp}_Address_Line_3,
{ap}_prac.AddressLine4 AS {cp}_Address_Line_4,
{ap}_prac.AddressLine5 AS {cp}_Address_Line_5,
{ap}_prac.PostCode AS {cp}_Post_Code,
{ap}_prac.NationalCode AS {cp}_National_Code
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN GenGPPractice {ap}_prac
ON {ap}_prac.Code = {basetable}.{basecolumn}
""".format(
ap=internal_alias_prefix,
basetable=viewmaker.basetable,
basecolumn=basecolumn,
))
viewmaker.record_lookup_table_keyfield('GenGPPractice', 'Code')
def rio_add_ims_event_lookup(viewmaker: ViewMaker, basecolumn_event_num: str,
column_prefix: str,
internal_alias_prefix: str) -> None:
# There is a twin key: ClientID and EventNumber
# However, we have made crate_rio_number, so we'll use that instead.
# Key to the TABLE, not the VIEW.
assert basecolumn_event_num, "Missing basecolumn_event_num"
assert column_prefix, "Missing column_prefix"
assert internal_alias_prefix, "Missing internal_alias_prefix"
viewmaker.add_select("""
{basetable}.{basecolumn_event_num} AS {cp}_Event_Number,
{ap}_evt.{CRATE_COL_PK} AS {cp}_Inpatient_Stay_PK
""".format(
basetable=viewmaker.basetable,
basecolumn_event_num=basecolumn_event_num,
cp=column_prefix,
ap=internal_alias_prefix,
CRATE_COL_PK=CRATE_COL_PK,
))
viewmaker.add_from("""
LEFT JOIN ImsEvent {ap}_evt
ON {ap}_evt.{CRATE_COL_RIO_NUMBER} = {basetable}.{CRATE_COL_RIO_NUMBER}
AND {ap}_evt.EventNumber = {basetable}.{basecolumn_event_num}
""".format( # noqa
basetable=viewmaker.basetable,
ap=internal_alias_prefix,
CRATE_COL_RIO_NUMBER=CRATE_COL_RIO_NUMBER,
basecolumn_event_num=basecolumn_event_num,
))
viewmaker.record_lookup_table_keyfield(
'ImsEvent', [CRATE_COL_RIO_NUMBER, 'EventNumber'])
def rio_add_gp_lookup(viewmaker: ViewMaker, basecolumn: str,
column_prefix: str, internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert column_prefix, "Missing column_prefix"
assert internal_alias_prefix, "Missing internal_alias_prefix"
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Code,
{ap}_gp.CodeDescription AS {cp}_Description,
{ap}_gp.NationalCode AS {cp}_National_Code,
{ap}_gp.Title AS {cp}_Title,
{ap}_gp.Forename AS {cp}_Forename,
{ap}_gp.Surname AS {cp}_Surname
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN GenGP {ap}_gp
ON {ap}_gp.Code = {basetable}.{basecolumn}
""".format(
ap=internal_alias_prefix,
basetable=viewmaker.basetable,
basecolumn=basecolumn,
))
viewmaker.record_lookup_table_keyfield('GenGP', 'Code')
def standard_rio_code_lookup_with_national_code(
viewmaker: ViewMaker, basecolumn: str, lookup_table: str,
column_prefix: str, internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert lookup_table, "Missing lookup_table"
assert column_prefix, "Missing column_prefix"
assert internal_alias_prefix, "Missing internal_alias_prefix"
aliased_lookup_table = internal_alias_prefix + "_" + lookup_table
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Code,
{aliased_lookup_table}.CodeDescription AS {cp}_Description,
{aliased_lookup_table}.NationalCode AS {cp}_National_Code
""".format( # noqa
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
aliased_lookup_table=aliased_lookup_table,
))
lookup_pk = 'Code'
viewmaker.add_from(
lookup_from_fragment_first_row_outer_apply(
lookup_fields=['CodeDescription', 'NationalCode'],
lookup_table=lookup_table,
aliased_lookup_table=aliased_lookup_table,
lookup_key=lookup_pk,
basetable=viewmaker.basetable,
basecolumn=basecolumn))
viewmaker.record_lookup_table_keyfield(lookup_table, lookup_pk)
def simple_lookup_join(viewmaker: ViewMaker, basecolumn: str,
lookup_table: str, lookup_pk: str,
lookup_fields_aliases: Dict[str, str],
internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert lookup_table, "Missing lookup_table"
assert lookup_pk, "Missing lookup_pk"
assert lookup_fields_aliases, "lookup_fields_aliases column_prefix"
assert internal_alias_prefix, "Missing internal_alias_prefix"
aliased_lookup_table = internal_alias_prefix + "_" + lookup_table
for column, alias in lookup_fields_aliases.items():
viewmaker.add_select(
"{aliased_lookup_table}.{column} AS {alias}".format(
aliased_lookup_table=aliased_lookup_table,
column=column,
alias=alias))
viewmaker.add_from(
lookup_from_fragment_first_row_outer_apply(
lookup_fields=lookup_fields_aliases.keys(),
lookup_table=lookup_table,
aliased_lookup_table=aliased_lookup_table,
lookup_key=lookup_pk,
basetable=viewmaker.basetable,
basecolumn=basecolumn))
viewmaker.record_lookup_table_keyfield(lookup_table, lookup_pk)
def where_clindocs_current(viewmaker: ViewMaker) -> None:
if not viewmaker.progargs.clindocs_current_only:
return
viewmaker.add_where(
"{bt}.{last_doc_col} = 1 AND {bt}.DeletedDate IS NULL".format(
bt=viewmaker.basetable, last_doc_col=CRATE_COL_LAST_DOC))
viewmaker.record_lookup_table_keyfield(viewmaker.basetable, 'DeletedDate')
viewmaker.enforce_same_n_rows_as_base = False
def simple_view_where(viewmaker: ViewMaker,
where_clause: str,
index_cols: Iterable[str] = None) -> None:
assert where_clause, "Missing where_clause"
index_cols = index_cols or []
viewmaker.add_where(where_clause)
# noinspection PyTypeChecker
for col in index_cols:
viewmaker.record_lookup_table_keyfield(viewmaker.basetable, col)
def where_prognotes_current(viewmaker: ViewMaker) -> None:
if not viewmaker.progargs.prognotes_current_only:
return
viewmaker.add_where(
"({bt}.EnteredInError <> 1 OR {bt}.EnteredInError IS NULL) "
"AND {bt}.{last_note_col} = 1".format(
bt=viewmaker.basetable, last_note_col=CRATE_COL_LAST_NOTE))
viewmaker.record_lookup_table_keyfield(viewmaker.basetable,
'EnteredInError')
viewmaker.enforce_same_n_rows_as_base = False
def rio_amend_standard_noncore(viewmaker: ViewMaker) -> None:
# Add user:
rio_add_user_lookup(viewmaker,
"type12_UpdatedBy",
column_prefix="Updated_By",
internal_alias_prefix="ub")
# Omit deleted:
viewmaker.add_where(
"{bt}.type12_DeletedDate IS NULL".format(bt=viewmaker.basetable))
viewmaker.record_lookup_table_keyfield(viewmaker.basetable,
'type12_DeletedDate')
viewmaker.enforce_same_n_rows_as_base = False
def get_pcmis_views(engine: Engine, progargs: Any,
ddhint: DDHint) -> List[ViewMaker]: # ddhint modified
def q(identifier: str) -> str:
return grammar.quote_identifier(identifier)
grammar = make_grammar(engine.dialect.name)
views = [] # type: List[ViewMaker]
tables = get_table_names(engine, sort=True)
for tablename in tables:
need_view = True
viewname = tablename + CRATE_VIEW_SUFFIX
viewmaker = ViewMaker(viewname=viewname,
engine=engine,
basetable=tablename,
rename=None,
progargs=progargs,
enforce_same_n_rows_as_base=True)
# 1. SELECT all the table's own columns
# ... done automatically by the viewmaker
# 2. If the patient ID isn't present, link it in.
columns = get_column_names(engine, tablename, sort=True)
if PCMIS_COL_PATIENT_ID not in columns:
need_view = True
# Not specifically for this, but we'll need it everywhere:
viewmaker.record_lookup_table_keyfield(PCMIS_TABLE_MASTER_PATIENT,
PCMIS_COL_PATIENT_ID)
if PCMIS_COL_CASE_NUMBER in columns:
viewmaker.add_select("{referrals}.{pid} AS {pid}".format(
referrals=PCMIS_TABLE_REFERRAL_DETAILS,
pid=PCMIS_COL_PATIENT_ID))
viewmaker.add_from(
"LEFT JOIN {referrals} ON {t}.{case} = {referrals}.{case}".
format( # noqa
referrals=PCMIS_TABLE_REFERRAL_DETAILS,
t=q(tablename),
case=PCMIS_COL_CASE_NUMBER))
viewmaker.record_lookup_table_keyfield(
PCMIS_TABLE_REFERRAL_DETAILS, PCMIS_COL_CASE_NUMBER)
viewmaker.request_index(tablename, PCMIS_COL_CASE_NUMBER)
elif PCMIS_COL_CONTACT_NUMBER in columns:
# ... and PCMIS_COL_CASE_NUMBER is not...
viewmaker.add_select("{contacts}.{case} AS {case}".format(
contacts=PCMIS_TABLE_CASE_CONTACTS,
case=PCMIS_COL_CASE_NUMBER))
def rio_add_diagnosis_lookup(viewmaker: ViewMaker,
basecolumn_scheme: str,
basecolumn_code: str,
alias_scheme: str,
alias_code: str,
alias_description: str,
internal_alias_prefix: str = None) -> None:
# Can't use simple_lookup_join as we have to join on two fields,
# diagnostic scheme and diagnostic code.
assert basecolumn_scheme, "Missing basecolumn_scheme"
assert basecolumn_code, "Missing basecolumn_code"
assert alias_scheme, "Missing alias_scheme"
assert alias_code, "Missing alias_code"
assert alias_description, "Missing alias_description"
assert internal_alias_prefix, "Missing internal_alias_prefix"
internal_alias_prefix = internal_alias_prefix or "t"
viewmaker.add_select("""
{basetable}.{basecolumn_scheme} AS {alias_scheme},
{basetable}.{basecolumn_code} AS {alias_code},
{ap}_diag.CodeDescription AS {alias_description}
""".format(
basetable=viewmaker.basetable,
basecolumn_scheme=basecolumn_scheme,
alias_scheme=alias_scheme,
basecolumn_code=basecolumn_code,
alias_code=alias_code,
ap=internal_alias_prefix,
alias_description=alias_description,
))
# - RECP had "speciality" / "specialty" inconsistency.
# - {cp}_location... ?? Presumably from GenLocation, but via what? Seems
# meaningless. In our snapshut, all are NULL anyway.
# - User codes are keyed to GenUser.GenUserID, but also to several other
# tables, e.g. GenHCP.GenHCPCode; GenPerson.GenPersonID
# - We use unique table aliases here, so that overall we can make >1 sets
# of different "user" joins simultaneously.
viewmaker.add_from("""
LEFT JOIN DiagnosisCode {ap}_diag
ON {ap}_diag.CodingScheme = {basetable}.{basecolumn_scheme}
AND {ap}_diag.Code = {basetable}.{basecolumn_code}
""".format(
basetable=viewmaker.basetable,
basecolumn_scheme=basecolumn_scheme,
basecolumn_code=basecolumn_code,
ap=internal_alias_prefix,
))
viewmaker.record_lookup_table_keyfield('DiagnosisCode',
['CodingScheme', 'Code'])
def rio_add_location_lookup(viewmaker: ViewMaker, basecolumn: str,
column_prefix: str,
internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert column_prefix, "Missing column_prefix"
assert internal_alias_prefix, "Missing internal_alias_prefix"
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Code,
{ap}_loc.CodeDescription AS {cp}_Description,
{ap}_loc.NationalCode AS {cp}_National_Code,
{ap}_loc.AddressLine1 as {cp}_Address_1,
{ap}_loc.AddressLine2 as {cp}_Address_2,
{ap}_loc.AddressLine3 as {cp}_Address_3,
{ap}_loc.AddressLine4 as {cp}_Address_4,
{ap}_loc.AddressLine5 as {cp}_Address_5,
{ap}_loc.Postcode as {cp}_Post_Code,
{ap}_loc.LocationType as {cp}_Type_Code,
{ap}_loctype.CodeDescription as {cp}_Type_Description,
{ap}_loctype.NationalCode as {cp}_Type_National_Code
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN (
GenLocation {ap}_loc
INNER JOIN GenLocationType {ap}_loctype
ON {ap}_loctype.Code = {ap}_loc.LocationType
) ON {ap}_loc.Code = {basetable}.{basecolumn}
""".format( # noqa
ap=internal_alias_prefix,
basetable=viewmaker.basetable,
basecolumn=basecolumn,
))
viewmaker.record_lookup_table_keyfield('GenLocation', ['Code'])
viewmaker.record_lookup_table_keyfield('GenLocationType', ['Code'])
def get_pcmis_views(engine: Engine, configoptions: PcmisConfigOptions,
ddhint: DDHint) -> List[ViewMaker]:
"""
Gets all PCMIS view definitions.
Args:
engine: an SQLAlchemy Engine
configoptions:
an instance of :class:`PcmisConfigOptions`
ddhint: a :class:`crate_anon/preprocess/ddhint.DDHint`, which will be
modified
Returns:
a list of :class:`crate_anon.common.sql.ViewMaker` objects
"""
def q(identifier: str) -> str:
return grammar.quote_identifier(identifier)
grammar = make_grammar(engine.dialect.name)
views = [] # type: List[ViewMaker]
tables = get_table_names(engine, sort=True)
for tablename in tables:
need_view = True
viewname = tablename + CRATE_VIEW_SUFFIX
viewmaker = ViewMaker(viewname=viewname,
engine=engine,
basetable=tablename,
rename=None,
userobj=None,
enforce_same_n_rows_as_base=True)
# 1. SELECT all the table's own columns
# ... done automatically by the viewmaker
# 2. If the patient ID isn't present, link it in.
columns = get_column_names(engine, tablename, sort=True)
if PCMIS_COL_PATIENT_ID not in columns:
need_view = True
# Not specifically for this, but we'll need it everywhere:
viewmaker.record_lookup_table_keyfield(PCMIS_TABLE_MASTER_PATIENT,
PCMIS_COL_PATIENT_ID)
if PCMIS_COL_CASE_NUMBER in columns:
viewmaker.add_select("{referrals}.{pid} AS {pid}".format(
referrals=PCMIS_TABLE_REFERRAL_DETAILS,
pid=PCMIS_COL_PATIENT_ID))
viewmaker.add_from(
"LEFT JOIN {referrals} ON {t}.{case} = {referrals}.{case}".
format( # noqa
referrals=PCMIS_TABLE_REFERRAL_DETAILS,
t=q(tablename),
case=PCMIS_COL_CASE_NUMBER))
viewmaker.record_lookup_table_keyfield(
PCMIS_TABLE_REFERRAL_DETAILS, PCMIS_COL_CASE_NUMBER)
viewmaker.request_index(tablename, PCMIS_COL_CASE_NUMBER)
elif PCMIS_COL_CONTACT_NUMBER in columns:
# ... and PCMIS_COL_CASE_NUMBER is not...
viewmaker.add_select("{contacts}.{case} AS {case}".format(
contacts=PCMIS_TABLE_CASE_CONTACTS,
case=PCMIS_COL_CASE_NUMBER))
def where_not_deleted_flag(viewmaker: ViewMaker, basecolumn: str) -> None:
assert basecolumn, "Missing basecolumn"
viewmaker.add_where("({table}.{col} IS NULL OR {table}.{col} = 0)".format(
table=viewmaker.basetable, col=basecolumn))
viewmaker.record_lookup_table_keyfield(viewmaker.basetable, basecolumn)
viewmaker.enforce_same_n_rows_as_base = False
def add_index_only(viewmaker: ViewMaker, table: str,
column_or_columns: Union[str, Iterable[str]]) -> None:
viewmaker.record_lookup_table_keyfield(table, column_or_columns)