def get_by_odscode(odscode, request_id, cursor):
try:
sql = "SELECT o.name, o.status, o.odscode, o.record_class, c.displayname, o.operational_start_date, " \
"o.operational_end_date, o.legal_start_date, o.legal_end_date, o.last_changed, o.ref_only " \
"FROM organisations o " \
"JOIN codesystems c " \
"ON o.record_class = c.id " \
"WHERE o.odscode = UPPER(%s) " \
"AND c.name = 'OrganisationRecordClass' " \
"LIMIT 1;"
data = (odscode, )
log_utils.log_database_query_statement(request_id,
str.format(sql, data))
cursor.execute(sql, data)
organisation = cursor.fetchone()
log_utils.log_database_return(request_id, organisation)
except Exception:
raise
return organisation
def get_for_odscode(odscode, request_id, cursor):
try:
sql = "SELECT r.code, r.unique_id, c.displayname, r.primary_role, r.status, r.operational_start_date, r.operational_end_date, " \
"r.legal_start_date, r.legal_end_date " \
"FROM roles r " \
"JOIN codesystems c " \
"ON r.code = c.id " \
"WHERE r.org_odscode = UPPER(%s) " \
"AND c.name = 'OrganisationRole';"
data = (odscode, )
log_utils.log_database_query_statement(request_id,
str.format(sql, data))
cursor.execute(sql, data)
roles = cursor.fetchall()
log_utils.log_database_return(request_id, roles)
except Exception:
raise
return roles
def get_latest_version(request_id):
try:
sql = "SELECT * " \
"FROM versions v " \
"ORDER BY v.file_creation_date DESC " \
"LIMIT 1"
with connection.get_db_cursor(request_id) as cursor:
log_utils.log_database_query_statement(request_id, sql)
cursor.execute(sql)
version = cursor.fetchone()
log_utils.log_database_return(request_id, version)
except Exception as e:
raise
return version
def get_fhir_ods_codes_from_db(select_sql, table_dict, count_data, result_data,
request_id):
try:
result_sql = "SELECT DISTINCT " + ORG_TABLE_ALIAS + ".odscode "
count_sql = "SELECT COUNT(DISTINCT(" + ORG_TABLE_ALIAS + ".odscode)) "
sql = "FROM " + ORG_TABLE_NAME + " " + ORG_TABLE_ALIAS + " "
if table_dict:
sql += create_joins(table_dict)
sql += generate_query_sql_AND_clauses(select_sql, startWithWhere=True)
count_sql += sql
sql += "ORDER BY " + ORG_TABLE_ALIAS + ".odscode "
sql += "OFFSET %s "
sql += "LIMIT %s"
result_sql += sql
with connection.get_db_cursor(request_id) as cursor:
log_utils.log_database_query_statement(
request_id, str.format(sql, result_data))
cursor.execute(result_sql, result_data)
organisations = cursor.fetchall()
log_utils.log_database_return(request_id, organisations)
log_utils.log_database_query_statement(request_id,
str.format(sql, count_data))
cursor.execute(count_sql, count_data)
count = cursor.fetchone()['count']
log_utils.log_database_return(request_id, count)
except Exception as e:
print(e)
raise
return organisations, count
def get_organisation_roles(request_id):
try:
sql = "SELECT id, displayname " \
"FROM codesystems c " \
"WHERE c.name = 'OrganisationRole'"
with connection.get_db_cursor(request_id) as cursor:
log_utils.log_database_query_statement(request_id, sql)
cursor.execute(sql)
organisation_roles = cursor.fetchall()
log_utils.log_database_return(request_id, organisation_roles)
except Exception:
raise
return organisation_roles
def get_orgs_last_changed_since(last_changed_since, request_id):
try:
sql = "SELECT odscode " \
"FROM organisations " \
"WHERE last_changed >= %s;"
data = (last_changed_since, )
with connection.get_db_cursor(request_id) as cursor:
log_utils.log_database_query_statement(request_id,
str.format(sql, data))
cursor.execute(sql, data)
organisation = cursor.fetchall()
log_utils.log_database_return(request_id, organisation)
except Exception:
raise
return organisation
def get_for_odscode(odscode, request_id, cursor):
try:
sql = "SELECT type, target_odscode, " \
"unique_id as uniqueId, legal_start_date, target_primary_role_code, " \
"target_unique_role_id " \
"FROM successors s " \
"WHERE s.org_odscode = UPPER(%s);"
data = (odscode,)
log_utils.log_database_query_statement(request_id, str.format(sql, data))
cursor.execute(sql, data)
successors = cursor.fetchall()
log_utils.log_database_return(request_id, successors)
except Exception:
raise
return successors
def get_for_odscode(odscode, request_id, cursor):
try:
sql = "SELECT rs.code, rs.unique_id, rs.target_odscode, rs.status, " \
"rs.operational_start_date, rs.operational_end_date, rs.legal_start_date, " \
"rs.legal_end_date, rs.target_primary_role_code, rs.target_unique_role_id " \
"FROM relationships rs " \
"WHERE rs.org_odscode = UPPER(%s);"
data = (odscode,)
log_utils.log_database_query_statement(request_id, str.format(sql, data))
cursor.execute(sql, data)
relationships = cursor.fetchall()
log_utils.log_database_return(request_id, relationships)
except Exception as e:
raise
return relationships
def get_for_odscode(odscode, request_id, cursor):
try:
sql = "SELECT type, " \
"value " \
"FROM contacts c " \
"WHERE c.org_odscode = UPPER(%s);"
data = (odscode, )
log_utils.log_database_query_statement(request_id,
str.format(sql, data))
cursor.execute(sql, data)
contacts = cursor.fetchall()
log_utils.log_database_return(request_id, contacts)
except Exception:
raise
return contacts
def get_for_odscode(odscode, request_id, cursor):
try:
sql = "SELECT address_line1, " \
"address_line2, " \
"address_line3, " \
"town, county, " \
"post_code, " \
"country " \
"FROM addresses a " \
"WHERE a.org_odscode = UPPER(%s);"
data = (odscode, )
log_utils.log_database_query_statement(request_id,
str.format(sql, data))
cursor.execute(sql, data)
address = cursor.fetchone()
log_utils.log_database_return(request_id, address)
except Exception:
raise
return address
def get_ord_orgs_summary_from_db(select_sql, result_data, count_data,
table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ORG_TABLE_NAME,
ORG_TABLE_ALIAS)
model_utils.add_table_and_alias_to_dict(table_dict, role.ROLE_TABLE_NAME,
role.ROLE_TABLE_ALIAS)
model_utils.add_table_and_alias_to_dict(table_dict,
address.ADDRESS_TABLE_NAME,
address.ADDRESS_TABLE_ALIAS)
model_utils.add_table_and_alias_to_dict(table_dict, "codesystems", "c")
organisations = {}
if len(select_sql) < 1:
return organisations, 0
try:
result_sql = "SELECT " + ORG_TABLE_ALIAS + ".name, "
result_sql += ORG_TABLE_ALIAS + ".odscode, "
result_sql += address.ADDRESS_TABLE_ALIAS + ".post_code, "
result_sql += ORG_TABLE_ALIAS + ".record_class, "
result_sql += ORG_TABLE_ALIAS + ".status, "
result_sql += ORG_TABLE_ALIAS + ".last_changed, "
result_sql += role.ROLE_TABLE_ALIAS + ".code, "
result_sql += "c.displayname "
count_sql = "SELECT COUNT(1) "
sql = "FROM " + hashset_csv(table_dict)
sql += " WHERE " + ORG_TABLE_ALIAS + ".odscode = " + role.ROLE_TABLE_ALIAS + ".org_odscode "
sql += "AND " + ORG_TABLE_ALIAS + ".odscode = " + address.ADDRESS_TABLE_ALIAS + ".org_odscode "
sql += "AND " + role.ROLE_TABLE_ALIAS + ".primary_role = 'TRUE' "
sql += "AND c.name = 'OrganisationRole' "
sql += "AND c.id = " + role.ROLE_TABLE_ALIAS + ".code "
sql += generate_query_sql_AND_clauses(select_sql, startWithWhere=False)
count_sql += sql
sql += "ORDER BY " + ORG_TABLE_ALIAS + ".name, " + ORG_TABLE_ALIAS + ".odscode "
sql += "OFFSET %s "
sql += "LIMIT %s"
result_sql += sql
with connection.get_db_cursor(request_id) as cursor:
log_utils.log_database_query_statement(
request_id, str.format(count_sql, count_data))
cursor.execute(count_sql, count_data)
count = cursor.fetchone()['count']
log_utils.log_database_return(request_id, count)
log_utils.log_database_query_statement(
request_id, str.format(result_sql, result_data))
cursor.execute(result_sql, result_data)
organisations = cursor.fetchall()
log_utils.log_database_return(request_id, organisations)
except Exception:
raise
return organisations, count