def get_role_types(): conn = connect.get_connection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute("SELECT codesystem_displayname, codesystem_id from codesystems " "where codesystem_name = 'OrganisationRole' "\ "order by codesystem_displayname;") rows = cur.fetchall() result = [] for row in rows: role_code = row['codesystem_id'] role_display_name = row['codesystem_displayname'] link_self_href = str.format('http://{0}/role-types/{1}', config.APP_HOSTNAME, role_code) link_search_href = str.format('http://{0}/organisations?primaryRoleCode={1}', config.APP_HOSTNAME, role_code) result.append({ 'name': role_display_name, 'code': role_code, 'links': [{ 'rel':'self', 'href': link_self_href }, { 'rel':'organisations.searchByRoleCode', 'href': link_search_href }] }) return result
def get_latest_org(): conn = connect.get_connection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute("SELECT * from organisations order by org_lastchanged desc limit 1;") rows = cur.fetchall() for row in rows: print(row) return row
def search_organisation(search_text): # 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 org_name like UPPER(%s) and org_status = 'ACTIVE';" data = (search_term,) 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['org_odscode']) item = { 'code': row['org_odscode'], 'name': row['org_name'], 'recordClass': row['org_recordclass'], '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=1000, recordclass='both', primary_role_code=None): log.debug(str.format("Offset: {0} Limit: {1}, RecordClass: {2}", offset, limit, recordclass)) conn = connect.get_connection() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) record_class_param = '%' if recordclass == 'both' else recordclass if primary_role_code: sql = "SELECT org_odscode, org_name, org_recordclass from organisations_primary_roles " \ "WHERE org_recordclass LIKE %s AND role_code = %s " \ "order by org_name OFFSET %s LIMIT %s;" data = (record_class_param, primary_role_code, offset, limit) else: sql = "SELECT org_odscode, org_name, org_recordclass from organisations " \ "WHERE org_recordclass LIKE %s " \ "order by org_name OFFSET %s LIMIT %s;" data = (record_class_param, offset, limit) log.debug(sql) 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['org_odscode']) item = { 'odsCode': row['org_odscode'], 'name': row['org_name'], 'recordClass': row['org_recordclass'], 'links': [{ 'rel':'self', 'href': link_self_href }] } result.append(item) return result
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 org_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") # Get the organisation_ref from the retrieved record organisation_ref = row_org['organisation_ref'] # Retrieve the roles for the organisation sql = "SELECT r.role_code, csr.codesystem_displayname, r.role_unique_id, r.role_status, " \ "r.role_operational_start_date, r.role_operational_end_date, r.role_legal_start_date, " \ "r.role_legal_end_date, r.primary_role from roles r " \ "left join codesystems csr on r.role_code = csr.codesystem_id " \ "WHERE r.organisation_ref = %s; " data = (organisation_ref,) cur.execute(sql, data) rows_roles = cur.fetchall() log.debug(rows_roles) # Retrieve the relationships for the organisation sql = "SELECT rs.relationship_code, csr.codesystem_displayname, rs.target_odscode, rs.relationship_status, " \ "o.org_name from relationships rs " \ "left join codesystems csr on rs.relationship_code = csr.codesystem_id " \ "left join organisations o on rs.target_odscode = o.org_odscode " \ "WHERE rs.organisation_ref = %s; " data = (organisation_ref,) cur.execute(sql, data) rows_relationships = cur.fetchall() log.debug(rows_relationships) # 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: link_target_href = str.format('http://{0}/organisations/{1}', config.APP_HOSTNAME, relationship['target_odscode']) relationship['relatedOdsCode'] = relationship.pop('target_odscode') relationship['code'] = relationship.pop('relationship_code') relationship['relatedOrganisationName'] = relationship.pop('org_name') relationship['description'] = relationship.pop('codesystem_displayname') relationship['status'] = relationship.pop('relationship_status') relationship['links'] = [{ 'rel': 'target', 'href': link_target_href }] relationships.append({'relationship': relationship}) result_data['relationships'] = relationships # Add the retrieved roles data to the object roles = [] for role in rows_roles: link_role_href = str.format('http://{0}/role-types/{1}', config.APP_HOSTNAME, role['role_code']) role['code'] = role.pop('role_code') role['description'] = role.pop('codesystem_displayname') role['primaryRole'] = role.pop('primary_role') try: role['status'] = role.pop('role_status') except: pass try: role['uniqueId'] = role.pop('role_unique_id') except: pass if role['role_operational_start_date']: role['operationalStartDate'] = role.pop('role_operational_start_date').isoformat() elif role['role_operational_start_date'] is None: role['operationalStartDate'] = role.pop('role_operational_start_date') if role['role_legal_end_date']: role['legalEndDate'] = role.pop('role_legal_end_date').isoformat() elif role['role_legal_end_date'] is None: role['legalEndDate'] = role.pop('role_legal_end_date') if role['role_legal_start_date']: role['legalStartDate'] = role.pop('role_legal_start_date').isoformat() elif role['role_legal_start_date'] is None: role['legalStartDate'] = role.pop('role_legal_start_date') if role['role_operational_end_date']: role['operationalEndDate'] = role.pop('role_operational_end_date').isoformat() elif role['role_operational_end_date'] is None: role['operationalEndDate'] = role.pop('role_operational_end_date') role['links'] = [{ 'rel': 'role-type', 'href': link_role_href }] roles.append({'role': role}) # Tidy up the field names etc. in the organisation dictionary before it's returned result_data['roles'] = roles result_data['name'] = result_data.pop('org_name') result_data['odsCode'] = result_data.pop('org_odscode') result_data['recordClass'] = result_data.pop('org_recordclass') result_data['status'] = result_data.pop('org_status') result_data.pop('organisation_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 }] return result_data except psycopg2.DatabaseError as e: log.error(str.format("Error {0}", e)) except Exception as e: log.error(e)