def rio_add_team_lookup(viewmaker: ViewMaker,
basecolumn: str,
column_prefix: str = None,
internal_alias_prefix: str = None) -> None:
assert basecolumn, "Missing basecolumn"
column_prefix = column_prefix or basecolumn
internal_alias_prefix = internal_alias_prefix or "t_" + column_prefix
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Code,
{ap}_team.CodeDescription AS {cp}_Description,
{ap}_classif.Code AS {cp}_Classification_Group_Code,
{ap}_classif.CodeDescription AS {cp}_Classification_Group_Description
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN (
GenServiceTeam {ap}_team
INNER JOIN GenServiceTeamClassification {ap}_classif
ON {ap}_classif.Code = {ap}_team.ClassificationGroup
) ON {basetable}.{basecolumn} = {ap}_team.Code
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
ap=internal_alias_prefix,
))
viewmaker.record_lookup_table_keyfields([
('GenServiceTeam', 'Code'),
('GenServiceTeamClassification', 'Code'),
])
def rio_add_consultant_lookup(viewmaker: ViewMaker,
basecolumn: str,
column_prefix: str = None,
internal_alias_prefix: str = None) -> None:
assert basecolumn, "Missing basecolumn"
column_prefix = column_prefix or basecolumn
internal_alias_prefix = internal_alias_prefix or "t_" + column_prefix
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_ID,
{ap}_cons.Firstname AS {cp}_First_Name,
{ap}_cons.Surname AS {cp}_Surname,
{ap}_cons.SpecialtyID AS {cp}_Specialty_Code,
{ap}_spec.CodeDescription AS {cp}_Specialty_Description
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN (
GenHospitalConsultant {ap}_cons
LEFT JOIN GenSpecialty {ap}_spec
ON {ap}_spec.Code = {ap}_cons.SpecialtyID
) ON {ap}_cons.ConsultantID = {basetable}.{basecolumn}
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
ap=internal_alias_prefix,
))
viewmaker.record_lookup_table_keyfields([
('GenHospitalConsultant', 'ConsultantID'),
('GenSpecialty', 'Code'),
])
def rio_add_audit_info(viewmaker: ViewMaker) -> None:
# - In RCEP: lots of tables have Created_Date, Updated_Date with no source
# column; likely from audit table.
# - Here: Audit_Created_Date, Audit_Updated_Date
ap1 = "_au_cr"
ap2 = "_au_up"
viewmaker.add_select("""
{ap1}_subq.Audit_Created_Date AS Audit_Created_Date,
{ap2}_subq.Audit_Updated_Date AS Audit_Updated_Date
""".format(
ap1=ap1,
ap2=ap2,
))
viewmaker.add_from("""
LEFT JOIN (
SELECT {ap1}_audit.RowID,
MIN({ap1}_audit.ActionDateTime) AS Audit_Created_Date
FROM AuditTrail {ap1}_audit
INNER JOIN GenTable {ap1}_table
ON {ap1}_table.TableNumber = {ap1}_audit.TableNumber
WHERE {ap1}_table.GenTableCode = {literal}
AND {ap1}_audit.AuditAction = 2 -- INSERT
GROUP BY {ap1}_audit.RowID
) {ap1}_subq
ON {ap1}_subq.RowID = {basetable}.{CRATE_COL_PK}
LEFT JOIN (
SELECT {ap2}_audit.RowID,
MAX({ap2}_audit.ActionDateTime) AS Audit_Updated_Date
FROM AuditTrail {ap2}_audit
INNER JOIN GenTable {ap2}_table
ON {ap2}_table.TableNumber = {ap2}_audit.TableNumber
WHERE {ap2}_table.GenTableCode = {literal}
AND {ap2}_audit.AuditAction = 3 -- UPDATE
GROUP BY {ap2}_audit.RowID
) {ap2}_subq
ON {ap2}_subq.RowID = {basetable}.{CRATE_COL_PK}
""".format(
ap1=ap1,
ap2=ap2,
basetable=viewmaker.basetable,
literal=sql_string_literal(viewmaker.basetable),
CRATE_COL_PK=CRATE_COL_PK,
))
viewmaker.record_lookup_table_keyfields([
('AuditTrail', ['AuditAction', 'RowID', 'TableNumber']),
('GenTable', 'GenTableCode'),
])
def rio_add_gp_lookup_with_practice(viewmaker: ViewMaker, basecolumn: str,
column_prefix: str,
internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert internal_alias_prefix, "Missing internal_alias_prefix"
if column_prefix:
column_prefix += '_'
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}GP_Code,
{ap}_gp.CodeDescription AS {cp}GP_Description,
{ap}_gp.NationalCode AS {cp}GP_National_Code,
{ap}_gp.Title AS {cp}GP_Title,
{ap}_gp.Forename AS {cp}GP_Forename,
{ap}_gp.Surname AS {cp}GP_Surname,
{ap}_prac.Code AS {cp}Practice_Code,
{ap}_prac.CodeDescription AS {cp}Practice_Description,
{ap}_prac.AddressLine1 AS {cp}Practice_Address_Line_1,
{ap}_prac.AddressLine2 AS {cp}Practice_Address_Line_2,
{ap}_prac.AddressLine3 AS {cp}Practice_Address_Line_3,
{ap}_prac.AddressLine4 AS {cp}Practice_Address_Line_4,
{ap}_prac.AddressLine5 AS {cp}Practice_Address_Line_5,
{ap}_prac.PostCode AS {cp}Practice_Post_Code,
{ap}_prac.NationalCode AS {cp}Practice_National_Code
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN (
GenGP {ap}_gp
INNER JOIN GenGPGPPractice -- linking table
ON GenGPPractice.GenGPCode = {ap}_gp.Code
INNER JOIN GenGPPractice {ap}_prac
ON {ap}_prac.Code = GenGPPractice.GenPracticeCode
) ON {ap}_gp.Code = {basetable}.{basecolumn}
""".format(
ap=internal_alias_prefix,
basetable=viewmaker.basetable,
basecolumn=basecolumn,
))
viewmaker.record_lookup_table_keyfields([
('GenGP', 'Code'),
('GenGPPractice', 'Code'),
('GenGPGPPractice', 'GenGPCode'),
])
def rio_add_carespell_lookup(viewmaker: ViewMaker,
basecolumn: str,
column_prefix: str = None,
internal_alias_prefix: str = None) -> None:
assert basecolumn, "Missing basecolumn"
column_prefix = column_prefix or basecolumn
internal_alias_prefix = internal_alias_prefix or "t_" + column_prefix
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Number,
{ap}_spell.StartDate AS {cp}_Start_Date,
{ap}_spell.EndDate AS {cp}_End_Date,
{ap}_spell.MentalHealth AS {cp}_Mental_Health,
{ap}_spell.GenSpecialtyCode AS {cp}_Specialty_Code,
{ap}_spec.CodeDescription AS {cp}_Specialty_Description,
{ap}_spec.NationalCode AS {cp}_Specialty_National_Code
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
viewmaker.add_from("""
LEFT JOIN (
ClientCareSpell {ap}_spell
INNER JOIN GenSpecialty {ap}_spec
ON {ap}_spec.Code = {ap}_spell.GenSpecialtyCode
) ON {basetable}.{basecolumn} = {ap}_spell.CareSpellNum
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
ap=internal_alias_prefix,
))
viewmaker.record_lookup_table_keyfields([
('ClientCareSpell', 'CareSpellNum'),
('GenSpecialty', 'Code'),
])
def rio_add_org_contact_lookup(viewmaker: ViewMaker, basecolumn: str,
column_prefix: str,
internal_alias_prefix: str) -> None:
assert basecolumn, "Missing basecolumn"
assert column_prefix, "Missing column_prefix"
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_ID,
{ap}_con.ContactType AS {cp}_Contact_Type_Code,
{ap}_ct.CodeDescription AS {cp}_Contact_Type_Description,
{ap}_ct.NationalCode AS {cp}_Contact_Type_National_Code,
{ap}_con.Title AS {cp}_Title,
{ap}_con.FirstName AS {cp}_First_Name,
{ap}_con.Surname AS {cp}_Surname,
{ap}_con.JobTitle AS {cp}_Job_Title,
{ap}_con.MainPhoneNo AS {cp}_Main_Phone_Number,
{ap}_con.OtherPhoneNo AS {cp}_Other_Phone_Number,
{ap}_con.FaxNo AS {cp}_Fax_Number,
{ap}_con.EmailAddress AS {cp}_Email_Address,
{ap}_con.Comments AS {cp}_Comments,
{ap}_con.OrganisationID AS {cp}_Organisation_ID,
{ap}_org.OrganisationCode AS {cp}_Organisation_Code,
{ap}_org.OrganisationName AS {cp}_Organisation_Name,
{ap}_org.OrganisationType AS {cp}_Organisation_Type_Code,
{ap}_orgtype.CodeDescription AS {cp}_Organisation_Type_Description,
{ap}_org.DepartmentName AS {cp}_Organisation_Department_Name,
{ap}_org.MainPhoneNo AS {cp}_Organisation_Main_Phone_Number,
{ap}_org.OtherPhoneNo AS {cp}_Organisation_Other_Phone_Number,
{ap}_org.FaxNo AS {cp}_Organisation_Fax_Number,
{ap}_org.EmailAddress AS {cp}_Organisation_Email_Address,
{ap}_org.AddressLine1 AS {cp}_Organisation_Address_Line_1,
{ap}_org.AddressLine2 AS {cp}_Organisation_Address_Line_2,
{ap}_org.AddressLine3 AS {cp}_Organisation_Address_Line_3,
{ap}_org.AddressLine4 AS {cp}_Organisation_Address_Line_4,
{ap}_org.AddressLine5 AS {cp}_Organisation_Address_Line_5,
{ap}_org.PostCode AS {cp}_Organisation_Post_Code
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
# Phone/fax/email/comments not in RCEP
viewmaker.add_from("""
LEFT JOIN (
OrgContact {ap}_con
INNER JOIN OrgContactType {ap}_ct
ON {ap}_ct.Code = {ap}_con.ContactType
INNER JOIN OrgOrganisation {ap}_org
ON {ap}_org.SequenceID = {ap}_con.OrganisationID -- ?
INNER JOIN OrgType {ap}_orgtype
ON {ap}_orgtype.Code = {ap}_org.OrganisationType
) ON {ap}_con.OrganisationID = {basetable}.{basecolumn}
""".format(
ap=internal_alias_prefix,
basetable=viewmaker.basetable,
basecolumn=basecolumn,
))
viewmaker.record_lookup_table_keyfields([
('OrgContact', 'OrganisationID'),
('OrgContactType', 'Code'),
('OrgOrganisation', 'SequenceID'),
('OrgType', 'Code'),
])
def rio_add_user_lookup(viewmaker: ViewMaker,
basecolumn: str,
column_prefix: str = None,
internal_alias_prefix: str = None) -> None:
# NOT VERIFIED IN FULL - insufficient data with just top 1000 rows for
# each table (2016-07-12).
assert basecolumn, "Missing basecolumn"
column_prefix = column_prefix or basecolumn
internal_alias_prefix = internal_alias_prefix or "t_" + column_prefix
# ... table alias
viewmaker.add_select("""
{basetable}.{basecolumn} AS {cp}_Code,
{ap}_genhcp.ConsultantFlag AS {cp}_Consultant_Flag,
{ap}_genperson.Email AS {cp}_Email,
{ap}_genperson.Title AS {cp}_Title,
{ap}_genperson.FirstName AS {cp}_First_Name,
{ap}_genperson.Surname AS {cp}_Surname,
{ap}_prof.Code AS {cp}_Resp_Clinician_Profession_Code,
{ap}_prof.CodeDescription AS {cp}_Resp_Clinician_Profession_Description,
{ap}_serviceteam.Code AS {cp}_Primary_Team_Code,
{ap}_serviceteam.CodeDescription AS {cp}_Primary_Team_Description,
{ap}_genspec.Code AS {cp}_Main_Specialty_Code,
{ap}_genspec.CodeDescription AS {cp}_Main_Specialty_Description,
{ap}_genspec.NationalCode AS {cp}_Main_Specialty_National_Code,
{ap}_profgroup.Code AS {cp}_Professional_Group_Code,
{ap}_profgroup.CodeDescription AS {cp}_Professional_Group_Description,
{ap}_genorg.Code AS {cp}_Organisation_Type_Code,
{ap}_genorg.CodeDescription AS {cp}_Organisation_Type_Description
""".format( # noqa
basetable=viewmaker.basetable,
basecolumn=basecolumn,
cp=column_prefix,
ap=internal_alias_prefix,
))
# - 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 (
GenUser {ap}_genuser
LEFT JOIN GenPerson {ap}_genperson
ON {ap}_genperson.GenPersonID = {ap}_genuser.GenUserID
LEFT JOIN GenHCP {ap}_genhcp
ON {ap}_genhcp.GenHCPCode = {ap}_genuser.GenUserID
LEFT JOIN GenHCPRCProfession {ap}_prof
ON {ap}_prof.Code = {ap}_genhcp.RCProfession
LEFT JOIN GenServiceTeam {ap}_serviceteam
ON {ap}_serviceteam.Code = {ap}_genhcp.PrimaryTeam
LEFT JOIN GenSpecialty {ap}_genspec
ON {ap}_genspec.Code = {ap}_genhcp.MainGenSpecialtyCode
LEFT JOIN GenStaffProfessionalGroup {ap}_profgroup
ON {ap}_profgroup.Code = {ap}_genhcp.StaffProfessionalGroup
LEFT JOIN GenOrganisationType {ap}_genorg
ON {ap}_genorg.Code = {ap}_genuser.OrganisationType
) ON {ap}_genuser.GenUserID = {basetable}.{basecolumn}
""".format(
basetable=viewmaker.basetable,
basecolumn=basecolumn,
ap=internal_alias_prefix,
))
# OTHER THINGS:
# - GenHCP.Occupation is listed in the RiO docs but doesn't actually seem
# to exist. (Perhaps explaining why it's not linked in the RCEP output.)
# I had tried to link it to CareCoordinatorOccupation.Code.
# If you use:
# SELECT *
# FROM information_schema.columns
# WHERE column_name LIKE '%Occup%'
# you only get Client_Demographic_Details.Occupation and
# Client_Demographic_Details.Partner_Occupation
viewmaker.record_lookup_table_keyfields([
('GenHCP', 'GenHCPCode'),
('GenUser', 'GenUserID'),
('GenPerson', 'GenPersonID'),
('GenHCPRCProfession', 'Code'),
('GenServiceTeam', 'Code'),
('GenSpecialty', 'Code'),
('GenStaffProfessionalGroup', 'Code'),
('GenOrganisationType', 'Code'),
])