def get_where_statement_for_fhir_role(rolearg, primaryRole, role_idx,
table_dict, where_sql, request_id):
sql = ""
query_param = ()
alias = ROLE_TABLE_ALIAS + str(role_idx)
model_utils.add_table_and_alias_to_dict(table_dict, ROLE_TABLE_NAME, alias)
if "|" in rolearg:
rolearg = rolearg.split("|")[1]
roles = rolearg.split(",")
sql += alias + ".code in ("
count = 0
for role in roles:
query_param = query_param + (str.format("RO{0}", role), )
if count > 0:
sql += ","
sql += "%s"
count += 1
sql += ")"
if primaryRole is not None:
sql += " AND " + alias + ".primary_role = %s"
query_param = query_param + (str.format("{0}", primaryRole), )
where_sql.append(sql)
return query_param
def get_where_statement_for_fhir_postcode_exact_search(postcode, where_sql,
table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ADDRESS_TABLE_NAME,
ADDRESS_TABLE_ALIAS)
query_param = str.format("{0}", postcode)
where_sql.append(ADDRESS_TABLE_ALIAS + ".post_code = %s")
return query_param
def get_where_statement_for_fhir_city_contains_search(city, where_sql,
table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ADDRESS_TABLE_NAME,
ADDRESS_TABLE_ALIAS)
query_param = str.format("%{0}%", city)
where_sql.append("UPPER(" + ADDRESS_TABLE_ALIAS + ".town) LIKE UPPER(%s)")
return query_param
def get_where_statement_for_ord_status_search(status, where_sql, table_dict,
request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ORG_TABLE_NAME,
ORG_TABLE_ALIAS)
query_param = str.format("{0}", status)
where_sql.append("UPPER(" + ORG_TABLE_ALIAS + ".status) LIKE UPPER(%s)")
return query_param
def get_where_statement_for_ord_last_changed_since_search(
last_changed_since, where_sql, table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ORG_TABLE_NAME,
ORG_TABLE_ALIAS)
query_param = str.format("{0}", last_changed_since)
where_sql.append("%s <= " + ORG_TABLE_ALIAS + ".last_changed")
return query_param
def get_where_statement_for_ord_primary_role_code_search(
primary_role_code, where_sql, table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ROLE_TABLE_NAME,
ROLE_TABLE_ALIAS)
query_param = str.format("{0}", primary_role_code)
where_sql.append("UPPER(" + ROLE_TABLE_ALIAS + ".code) LIKE UPPER(%s)")
return query_param
def get_where_statement_for_ord_non_primary_role_code_search(
non_primary_role_code, where_sql, table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ROLE_TABLE_NAME,
ROLE_TABLE_ALIAS2)
query_param = str.format("{0}", non_primary_role_code)
where_sql.append(organisation.ORG_TABLE_ALIAS + ".odscode = " +
ROLE_TABLE_ALIAS2 + ".org_odscode")
where_sql.append(ROLE_TABLE_ALIAS2 + ".primary_role = 'FALSE'")
where_sql.append("UPPER(" + ROLE_TABLE_ALIAS2 + ".code) LIKE UPPER(%s)")
return query_param
def get_where_statement_for_ord_postcode_search(postcode, where_sql,
table_dict, request_id):
model_utils.add_table_and_alias_to_dict(table_dict, ADDRESS_TABLE_NAME,
ADDRESS_TABLE_ALIAS)
outbound_postcode = r'^((([A-Za-z][0-9][A-Za-z])|([A-Za-z][A-Ha-hJ-Yj-y][0-9][A-Za-z]))|(([A-Za-z][A-Ha-hJ-Yj-y][0-9]{1,2}))|([A-Za-z][0-9]{1,2}))$'
if (len(postcode.replace(" ", "")) != len(postcode)):
query_param = str.format("{0}%", postcode)
where_sql.append("UPPER(" + ADDRESS_TABLE_ALIAS +
".post_code) LIKE UPPER(%s)")
else:
match = re.match(outbound_postcode, postcode)
if (match and (len(postcode.replace(" ", "")) == len(
(match.group(0)).replace(" ", "")))):
query_param = str.format("{0} %", postcode)
where_sql.append("UPPER(" + ADDRESS_TABLE_ALIAS +
".post_code) LIKE UPPER(%s)")
else:
query_param = str.format("{0}%", postcode)
where_sql.append("REPLACE(UPPER(" + ADDRESS_TABLE_ALIAS +
".post_code),' ','') LIKE UPPER(%s)")
return query_param
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
