def get_role_types(): conn = connect.get_connection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute("SELECT displayname, id from codesystems " "where name = 'OrganisationRole' "\ "order by displayname;") rows = cur.fetchall() result = [] for row in rows: role_code = row['id'] role_display_name = row['displayname'] link_self_href = str.format('http://{0}/role-types/{1}', config.APP_HOSTNAME, role_code) link_search_primary_role_code_href = str.format('http://{0}/organisations?primaryRoleCode={1}', config.APP_HOSTNAME, role_code) link_search_role_code_href = str.format('http://{0}/organisations?roleCode={1}', config.APP_HOSTNAME, role_code) result.append({ 'name': role_display_name, 'code': role_code, 'links': [{ 'rel':'self', 'href': link_self_href }, { 'rel':'organisations.searchByPrimaryRoleCode', 'href': link_search_primary_role_code_href }, { 'rel':'organisations.searchByRoleCode', 'href': link_search_role_code_href }] }) return result
def search_organisation(search_text, offset=0, limit=1000,): # Get a database connection conn = connect.get_connection() # Use the RealDictCursor to return data as a python dictionary type cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: search_term = str.format("%{0}%", search_text) sql = "SELECT * from organisations " \ "WHERE name like UPPER(%s) and status = 'Active' " \ "ORDER BY name OFFSET %s LIMIT %s;;" data = (search_term, offset, limit) cur.execute(sql, data) rows = cur.fetchall() print(rows) # Raise an exception if the organisation record is not found if rows == []: raise Exception("Record Not Found") result = [] for row in rows: link_self_href = str.format('http://{0}/organisations/{1}', config.APP_HOSTNAME, row['odscode']) item = { 'odsCode': row['odscode'], 'name': row['name'], 'recordClass': row['record_class'], 'links': [{ 'rel': 'self', 'href': link_self_href }] } result.append(item) return result except Exception as e: log.error(e)
def get_org_list(offset=0, limit=20, recordclass='both', primary_role_code=None, role_code=None, query=None): """Retrieves a list of organisations Parameters ---------- q = search term offset = the record from which to start limit = the maximum number of records to return recordclass = the type of record to return (HSCSite, HSCOrg, Both) primary_role_code = filter organisations to only those where this is their primary role code role_code = filter organisations to only those a role with this code Returns ------- List of organisations filtered by provided parameters """ log.debug(str.format("Offset: {0} Limit: {1}, RecordClass: {2}, Query: {3}", offset, limit, recordclass, query)) conn = connect.get_connection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) record_class_param = '%' if recordclass == 'both' else recordclass # Start the select statement with the field list and from clause sql = "SELECT odscode, name, record_class from organisations WHERE TRUE " sql_count = "SELECT COUNT(*) from organisations WHERE TRUE " data = () # If a record_class parameter was specified, add that to the statement if recordclass: log.debug('add record_class parameter') sql = str.format("{0} {1}", sql, "AND record_class LIKE %s ") sql_count = str.format("{0} {1}", sql_count, "AND record_class LIKE %s ") data = (recordclass,) # If a query parameter was specified, add that to the statement if query: print('add search query') sql = str.format("{0} {1}", sql, "AND name like UPPER(%s) ") sql_count = str.format("{0} {1}", sql_count, "AND name like UPPER(%s) ") search_query = str.format("%{0}%", query) data = data + (search_query,) # If a role_code parameter was specified, add that to the statement if role_code: print('add role_code') sql = str.format("{0} {1}", sql, "AND odscode in " "(SELECT org_odscode from roles " "WHERE status = 'Active' " "AND code = %s) ") sql_count = str.format("{0} {1}", sql_count, "AND odscode in " "(SELECT org_odscode from roles " "WHERE status = 'Active' " "AND code = %s) ") data = data + (role_code,) # Or if a primary_role_code parameter was specified, add that to the statement elif primary_role_code: print('add primary_role_code') sql = str.format("{0} {1}", sql, "AND odscode in " "(SELECT org_odscode from roles WHERE primary_role = TRUE " "AND status = 'Active' " "AND code = %s) " ) sql_count = str.format("{0} {1}", sql_count, "AND odscode in " "(SELECT org_odscode from roles WHERE primary_role = TRUE " "AND status = 'Active' " "AND code = %s) " ) data = data + (primary_role_code,) # Quickly get total number of query results before applying offset and limit log.debug(sql_count) log.debug(data) cur.execute(sql_count, data) count = cur.fetchone()['count'] # Lastly, add the offset and limit clauses to the main select statement sql = str.format("{0} {1}", sql, "ORDER BY name OFFSET %s LIMIT %s;") data = data + (offset, limit) log.debug(sql) log.debug(data) # Execute the main query cur.execute(sql, data) rows = cur.fetchall() log.debug(str.format("{0} rows in result", len(rows))) result = [] for row in rows: link_self_href = str.format('http://{0}/organisations/{1}', config.APP_HOSTNAME, row['odscode']) item = { 'odsCode': row['odscode'], 'name': row['name'], 'recordClass': row['record_class'], 'links': [{ 'rel':'self', 'href': link_self_href }] } result.append(item) # Return both the paged results and the count of total results return result, count
def get_organisation_by_odscode(odscode): # Get a database connection conn = connect.get_connection() # Use the RealDictCursor to return data as a python dictionary type cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # Try and retrieve the organisation record for the provided ODS code try: sql = "SELECT * from organisations " \ "WHERE odscode = %s "\ "limit 1;" data = (odscode,) cur.execute(sql, data) row_org = cur.fetchone() log.debug(str.format("Organisation Record: {0}", row_org)) # Raise an exception if the organisation record is not found if row_org is None: raise Exception("Record Not Found") row_org = remove_none_values_from_dictionary(row_org) # Get the organisation_ref from the retrieved record organisation_odscode = row_org['odscode'] # Retrieve the roles for the organisation try: sql = "SELECT r.code, csr.displayname, r.unique_id, r.status, " \ "r.operational_start_date, r.operational_end_date, r.legal_start_date, " \ "r.legal_end_date, r.primary_role from roles r " \ "left join codesystems csr on r.code = csr.id " \ "WHERE r.org_odscode = %s; " data = (organisation_odscode,) cur.execute(sql, data) rows_roles = cur.fetchall() log.debug(rows_roles) except Exception as e: raise # Retrieve the relationships for the organisation try: sql = "SELECT rs.code, csr.displayname, rs.unique_id, rs.target_odscode, rs.status, " \ "rs.operational_start_date, rs.operational_end_date, rs.legal_start_date, " \ "rs.legal_end_date, o.name from relationships rs " \ "left join codesystems csr on rs.code = csr.id " \ "left join organisations o on rs.target_odscode = o.odscode " \ "WHERE rs.org_odscode = %s; " data = (organisation_odscode,) cur.execute(sql, data) rows_relationships = cur.fetchall() log.debug(rows_relationships) except Exception as e: raise # Retrieve the addresses for the organisation try: sql = "SELECT street_address_line1, " \ "street_address_line2, " \ "street_address_line3, " \ "town, county, " \ "postal_code, " \ "country, uprn, " \ "location_id " \ "FROM addresses a " \ "WHERE a.org_odscode = %s;" data = (organisation_odscode,) cur.execute(sql, data) rows_addresses = cur.fetchall() log.debug("Addresses: %s" % rows_addresses) except Exception as e: raise # Retrieve the successors / predecessors for the organisation try: sql = "SELECT type, target_odscode as targetOdsCode, " \ "o.name as targetName, " \ "target_primary_role_code as targetPrimaryRoleCode, " \ "unique_id as uniqueId " \ "FROM successors s " \ "LEFT JOIN organisations o on s.target_odscode = o.odscode " \ "WHERE s.org_odscode = %s;" data = (organisation_odscode,) cur.execute(sql, data) rows_successors = cur.fetchall() log.debug("Successors: %s" % rows_successors) except Exception as e: raise # Create an object from the returned organisation record to hold the data to be returned result_data = row_org # Add the retrieved relationships data to the object relationships = [] for relationship in rows_relationships: relationship = remove_none_values_from_dictionary(relationship) link_target_href = str.format('http://{0}/organisations/{1}', config.APP_HOSTNAME, relationship['target_odscode']) relationship['uniqueId'] = int(relationship.pop('unique_id')) relationship['relatedOdsCode'] = relationship.pop('target_odscode') relationship['relatedOrganisationName'] = relationship.pop('name') relationship['description'] = relationship.pop('displayname') relationship['status'] = relationship.pop('status') try: relationship['operationalStartDate'] = relationship.pop('operational_start_date').isoformat() except: pass try: relationship['legalEndDate'] = relationship.pop('legal_end_date').isoformat() except: pass try: relationship['legalStartDate'] = relationship.pop('legal_start_date').isoformat() except: pass try: relationship['operationalEndDate'] = relationship.pop('operational_end_date').isoformat() except: pass relationship['links'] = [{ 'rel': 'related-organisation', 'href': link_target_href }] relationships.append(relationship) result_data['relationships'] = relationships # Add the retrieved roles data to the object roles = [] for role in rows_roles: role = remove_none_values_from_dictionary(role) link_role_href = str.format('http://{0}/role-types/{1}', config.APP_HOSTNAME, role['code']) role['code'] = role.pop('code') role['description'] = role.pop('displayname') role['primaryRole'] = role.pop('primary_role') try: role['status'] = role.pop('status') except: pass try: role['uniqueId'] = int(role.pop('unique_id')) except: pass try: role['operationalStartDate'] = role.pop('operational_start_date').isoformat() except Exception as e: pass try: role['legalEndDate'] = role.pop('legal_end_date').isoformat() except Exception as e: pass try: role['legalStartDate'] = role.pop('legal_start_date').isoformat() except Exception as e: pass try: role['operationalEndDate'] = role.pop('operational_end_date').isoformat() except Exception as e: pass role['links'] = [{ 'rel': 'role-type', 'href': link_role_href }] roles.append(role) result_data['roles'] = roles # Add the addresses to the object addresses = [] for address in rows_addresses: address = remove_none_values_from_dictionary(address) address_lines = [] try: address_lines.append(address.pop('street_address_line1')) except: pass try: address_lines.append(address.pop('street_address_line2')) except: pass try: address_lines.append(address.pop('street_address_line3')) except: pass if len(address_lines) > 0: address['addressLines'] = address_lines try: address['postalCode'] = address.pop('postal_code') except: pass addresses.append(address) result_data['addresses'] = addresses # Add the successors to the object successors = [] for successor in rows_successors: link_successor_href = str.format('http://{0}/organisations/{1}', config.APP_HOSTNAME, successor['targetodscode']) successor = remove_none_values_from_dictionary(successor) successor['targetOdsCode'] = successor.pop('targetodscode') successor['targetPrimaryRoleCode'] = successor.pop('targetprimaryrolecode') successor['targetName'] = successor.pop('targetname') successor['uniqueId'] = successor.pop('uniqueid') successor['links'] = [{ 'rel': str.lower(successor['type']), 'href': link_successor_href }] successors.append(successor) result_data['successors'] = successors # Tidy up the field names etc. in the organisation dictionary before it's returned result_data['odsCode'] = result_data.pop('odscode') result_data['lastChanged'] = result_data.pop('last_changed') result_data['refOnly'] = bool(result_data.pop('ref_only')) result_data['recordClass'] = result_data.pop('record_class') result_data.pop('ref') link_self_href = str.format('http://{0}/organisations/{1}', config.APP_HOSTNAME, result_data['odsCode']) result_data['links'] = [ {'rel': 'self', 'href': link_self_href }] try: result_data['operationalStartDate'] = result_data.pop('operational_start_date').isoformat() except: pass try: result_data['legalEndDate'] = result_data.pop('legal_end_date').isoformat() except: pass try: result_data['legalStartDate'] = result_data.pop('legal_start_date').isoformat() except: pass try: result_data['operationalEndDate'] = result_data.pop('operational_end_date').isoformat() except: pass return result_data except psycopg2.DatabaseError as e: log.error(str.format("Error {0}", e)) except Exception as e: log.error(e)