def get_constituency_wards_ids_json(constituency_id): '''return ids for wards in a constituency''' conn = connection.get_connection() all_wards = DataFrame() for chunk in pd.read_sql('SELECT * FROM common_ward', con=conn, chunksize=100): all_wards = all_wards.append(chunk) constituency = constituencies.get_constituency_by_id(constituency_id) constituency = constituency.rename(index=str, columns={ 'id': 'constituency_id', 'name': "constituency_name" }) # import pdb # pdb.set_trace() constituency_wards = pd.merge(all_wards, constituency, on='constituency_id') constituency_wards = constituency_wards[['name', 'id', 'constituency_id']] return constituency_wards.to_json(orient='records')
def get_county_facilities(county_id, in_json=False): conn = connection.get_connection() county = counties.get_county_code_by_id(county_id) #get constituencies in the county county_constituencies = constituencies.get_county_constituency_codes( county_id) county_constituencies = county_constituencies.rename( index=str, columns={ 'id': 'constituency_id', 'name': "constituency_name" }) #get all the wards for the county all_wards = DataFrame() for index, constituency in county_constituencies.iterrows(): all_wards = all_wards.append( wards.get_constituency_wards(constituency['constituency_id'])) all_facilities = DataFrame() for index, ward in all_wards.iterrows(): all_facilities = all_facilities.append(get_ward_facilities(ward['id'])) all_facilities['county_name'] = county['name'] all_facilities['county_id'] = county['id'] if in_json: return all_facilities.to_json(orient='records') else: return all_facilities
def get_ward_facilities(ward_id, in_json=False, filters=None): '''return ids for facilities in a ward''' conn = connection.get_connection() all_facilities = DataFrame() ward = wards.get_ward_by_id(ward_id) constituency = constituencies.get_constituency_by_id( ward['constituency_id'].values[0]) county = counties.get_county_code_by_id( constituency['county_id'].values[0]) query = "SELECT * FROM facilities_facility WHERE ward_id = '%s' ;" % ( ward_id) for chunk in pd.read_sql(query, con=conn, chunksize=100): all_facilities = all_facilities.append(chunk) if filters: for key, value in filters.iteritems(): all_facilities = all_facilities[all_facilities[key] == value] all_facilities['ward_name'] = ward['name'][0] all_facilities['constituency_name'] = constituency['name'][0] all_facilities['constituency_id'] = constituency['id'][0] all_facilities['county_name'] = county['name'][0] all_facilities['county_id'] = county['id'][0] if in_json: return all_facilities.to_json(orient='records') else: return all_facilities
def get_facility_dataelement_datavalues(dataelement_id, source_id, period_id, category_combo_id=None, in_json=False): conn = connection.get_connection() all_datavalues = DataFrame() if category_combo_id: query = ''' SELECT a.* FROM fact_dhis_datavalue a , dim_dhis_dataelement b WHERE a.dataelementid = b.dataelementid AND b.uid = '%s' AND a.sourceid = %s AND a.periodid = '%s' AND a.categoryoptioncomboid = '%s' ''' % ( dataelement_id, source_id, period_id, category_combo_id) else: query = ''' SELECT a.* FROM fact_dhis_datavalue a , dim_dhis_dataelement b WHERE a.dataelementid = b.dataelementid AND b.uid = '%s' AND a.sourceid = %s AND a.periodid = '%s'; ''' % (dataelement_id, source_id, period_id) all_datavalues = pd.read_sql(query, con=conn) if in_json: return all_datavalues.to_json(orient='records') else: return all_datavalues
def get_job_types(in_json=False): ''' return a list of all job types returns a dataframe or json string in record orientation ''' query = ''' SELECT dataelementname as name, dataelementid as id, uid, cadreid as cadreId FROM dim_ihris_dataelement ''' all_job_types = pd.read_sql(query, connection.get_connection()) if in_json: return all_job_types.to_json(orient='records') else: return all_job_types
def get_period_details(period_id, in_json=False): ''' return period id details''' conn = connection.get_connection() all_periods = pd.DataFrame() query = "SELECT * FROM dim_dhis_peroid WHERE period_id = %s ;" % (in_json) for chunk in pd.read_sql(query, con=conn, chunksize=10000): all_periods = all_periods.append(chunk) for column, row in all_periods.iterrows(): row['year']
def get_ward_by_id(ward_id, in_json=False): all_wards = pd.DataFrame() query = "SELECT * FROM common_ward WHERE id = '%s' ;" % (ward_id) all_wards = df = pd.read_sql(query, con=connection.get_connection()) all_wards = all_wards.head(1) if in_json: return all_wards.to_json(orient='records') else: return all_wards
def get_county_code_by_id(county_id): conn = connection.get_connection() all_counties = pd.DataFrame() query = "SELECT * FROM common_county WHERE id = '%s' ;" % (county_id) for chunk in pd.read_sql(query, con=conn, chunksize=100): all_counties = all_counties.append(chunk) all_counties = all_counties[all_counties['id'] == county_id] return all_counties.head(1)
def get_all_facilities(): conn = connection.get_connection() #get all facilieits and merge all_facilities = DataFrame() for chunk in pd.read_sql('SELECT * FROM facilities_facility', con=conn, chunksize=100): all_facilities = all_facilities.append(chunk) return all_facilities
def get_all_wards(): '''return a dataframe of all wards''' conn = connection.get_connection() all_wards = DataFrame() for chunk in pd.read_sql('SELECT * FROM common_ward', con=conn, chunksize=100): all_wards = all_wards.append(chunk) return all_wards
def get_all_products(in_json=False): ''' Get all kemsa products ''' conn = connection.get_connection() all_products = pd.read_sql( 'SELECT DISTINCT(m_product_id) as id, product as name FROM fact_kemsa_order', conn) if in_json: return all_products.to_json(orient='records') else: return all_products
def get_facility_staff(facility_id, in_json=False): ''' Return a list of all products that have been ordered by the facility''' query = """ SELECT a.value, b.dataelementname as jobType, c.name FROM fact_ihris_datavalue a, dim_ihris_dataelement b, facilities_facility c WHERE a.mflcode = c.code AND a.dataelementid = b.uid AND c.id = '%s' """ % (facility_id) staff = pd.read_sql(query, connection.get_connection()) if in_json: return staff.to_json(orient='records') else: return staff
def get_period_types(in_json=False): '''return all period types''' conn = connection.get_connection() all_period_types = DataFrame() query = "select DISTINCT(periodtypename) as name, periodtypeid as id from dim_dhis_period;" for chunk in pd.read_sql(query, con=conn, chunksize=10000): all_period_types = all_period_types.append(chunk) if in_json: return all_period_types.to_json(orient='records') else: return all_period_types
def get_organization_units(in_json=False): '''returns a list of all organization units''' conn = connection.get_connection() all_orgunits = pd.DataFrame() for chunk in pd.read_sql('SELECT * FROM dim_dhis_organisationunit', con=conn, chunksize=10000): all_orgunits = all_orgunits.append(chunk) if in_json: return all_orgunits.to_json(orient='records') else: return all_orgunits
def get_indicator_by_id(indicator_id, in_json=False): ''' get specific indicator by id returns a dataframe of json string''' conn = connection.get_connection() query = "SELECT * FROM dim_dhis_indicator WHERE indicatorid = '%s';" % ( indicator_id) indicator = pd.read_sql(query, conn) if in_json: return indicator.to_json(orient='records') else: return indicator
def get_facility_products(facility_id, in_json=False): ''' Return a list of all products that have been ordered by the facility returns a dataframe or json string in records orientation''' query = """ SELECT a.product as name, a.m_product_id as id, b.id as facility_id, b.name as facility_name FROM fact_kemsa_order a, facilities_facility b WHERE a.facilitycode = b.code AND b.id = '%s' """ % (facility_id) facility_products = pd.read_sql(query, connection.get_connection()) if in_json: return facility_products.to_json(orient='records') else: return facility_products
def get_all_datavalues(in_json=False): '''return all datavalues in the datase''' conn = connection.get_connection() all_datavalues = DataFrame() for chunk in pd.read_sql('SELECT * FROM fact_dhis_datavalue LIMIT 50000', con=conn, chunksize=1000): all_datavalues = all_datavalues.append(chunk) if in_json: return all_datavalues.to_json(orient='records') else: return all_datavalues
def get_country_jobtypes(in_json=False): '''return a summaryy of all staff in the country''' query = """ SELECT a.value, b.dataelementname as jobtype, b.uid, c.name FROM fact_ihris_datavalue a, dim_ihris_dataelement b, facilities_facility c WHERE a.mflcode = c.code AND a.dataelementid = b.uid""" staff = pd.read_sql(query, connection.get_connection()) staff['value'] = pd.to_numeric(staff['value'], downcast='float') staff = staff.groupby(['jobtype']).sum() if in_json: return staff.to_json() else: return staff
def get_indicator_group_by_id(indicator_group_id): '''return a single indicator group matching the id''' conn = connection.get_connection() all_indicator_groups = DataFrame() for chunk in pd.read_sql('SELECT * FROM dim_dhis_indicatorgroup', con=conn, chunksize=100): all_indicator_groups = all_indicator_groups.append(chunk) single_indicator = all_indicator_groups[ all_indicator_groups['indicatorgroupid'] == indicator_group_id] return single_indicator
def get_facility_keph_levels_codes(in_json=False): '''return all facility keph levels''' conn = connection.get_connection() all_keph_levels = DataFrame() for chunk in pd.read_sql('SELECT * FROM facilities_kephlevel', con=conn, chunksize=100): all_keph_levels = all_keph_levels.append(chunk) all_keph_levels = all_keph_levels[['id', 'name']] if in_json: return all_keph_levels.to_json(orient='records') else: return all_keph_levels
def get_ward_facility_type_summary(ward_id, in_json=False): conn = connection.get_connection() facility_types_query = '''SELECT COUNT(facilities_facility.id) as value, facilities_facilitytype.name as facility_type_name FROM facilities_facility , common_ward , facilities_facilitytype WHERE facilities_facility.ward_id = common_ward.id AND facilities_facilitytype.id = facilities_facility.facility_type_id AND common_ward.id = '%s' GROUP BY (facilities_facilitytype.name)''' % (ward_id) all_facilities = pd.read_sql(facility_types_query, con=conn) all_facilities = all_facilities.set_index('facility_type_name') if in_json: return all_facilities.to_json() else: return all_facilities.to_json()
def get_facility_by_id(facility_id, in_json=False): '''returns a facility matching the facility id ''' conn = connection.get_connection() all_facilities = pd.DataFrame() query = "SELECT * FROM facilities_facility WHERE id = '%s' ;" % ( facility_id) for chunk in pd.read_sql(query, con=conn, chunksize=10000): all_facilities = all_facilities.append(chunk) facility = all_facilities.head(1) if in_json: return facility.to_json(orient='records') else: return facility
def get_indicator_groups(in_json=False): '''return a list of all indicator groups''' conn = connection.get_connection() all_indicator_groups = DataFrame() for chunk in pd.read_sql('SELECT * FROM dim_dhis_indicatorgroup', con=conn, chunksize=100): all_indicator_groups = all_indicator_groups.append(chunk) if in_json: return all_indicator_groups.to_json(orient='records') else: return all_indicator_groups
def get_facility_org_units(facility_id, in_json=False): '''returns an orgunit with the code matching that of the facility retruns a dataframe object or json with record orientation''' conn = connection.get_connection() query = ''' SELECT a.organisationunitid, a.code, b.name FROM dim_dhis_organisationunit a , facilities_facility b WHERE a.code = CAST(b.code as VarChar) AND b.id = '%s' ''' % ( facility_id) facility_orgunit = pd.read_sql(query, conn) if in_json: return facility_orgunit.to_json(orient='records') else: return facility_orgunit
def get_year_periods(year_id, in_json=False): '''return all periods in the year specified ''' conn = connection.get_connection() all_periods = DataFrame() query = "SELECT * FROM dim_dhis_period WHERE yearmonth @@ to_tsquery('%s')" % ( year_id) for chunk in pd.read_sql(query, con=conn, chunksize=10000): all_periods = all_periods.append(chunk) # all_periods['year'] = int(all_periods['year'.split(' ')[0]) # all_periods['month'] = int(string.split(' ')[1]) if in_json: return all_periods.to_json(orient='records') else: return all_periods
def get_facility_job_type(facility_id, job_type_id, in_json=False): ''' Return a list of all staff in the facility''' query = """ SELECT a.value, b.dataelementname, b.uid, c.name FROM fact_ihris_datavalue a, dim_ihris_dataelement b, facilities_facility c WHERE a.mflcode = c.code AND a.dataelementid = b.uid AND b.uid = '%s' AND c.id = '%s'""" % (job_type_id, facility_id) staff = pd.read_sql(query, connection.get_connection()) staff['value'] = pd.to_numeric(staff['value'], downcast='float') staff = staff.groupby(['dataelementname']).sum() if in_json: return staff.to_json(orient='records') else: return staff
def get_ward_staff(ward_id, in_json=False): ''' Return a list of all staff in the facility''' query = """ SELECT a.value, b.dataelementname, b.uid, c.name FROM fact_ihris_datavalue a, dim_ihris_dataelement b, facilities_facility c, common_ward d WHERE a.mflcode = c.code AND a.dataelementid = b.uid and c.ward_id = d.id AND d.id = '%s'""" % (ward_id) staff = pd.read_sql(query, connection.get_connection()) staff['value'] = pd.to_numeric(staff['value'], downcast='float') staff = staff.groupby(['dataelementname']).sum() if in_json: return staff.to_json() else: return staff
def get_constituency_by_id(constituency_id, in_json=False): conn = connection.get_connection() all_constituencies = pd.DataFrame() query = "SELECT * FROM common_constituency WHERE id = '%s' ;" % ( constituency_id) for chunk in pd.read_sql(query, con=conn, chunksize=100): all_constituencies = all_constituencies.append(chunk) # all_constituencies = all_constituencies[all_constituencies['id']==constituency_id] # import pdb # pdb.set_trace() if in_json: return all_constituencies.head(1).to_json(orient="records") else: return all_constituencies.head(1)
def get_constituency_ward_number_of_staff(constituency_id, in_json=False): '''return a number of all staff in the ward''' query = """ SELECT a.value, b.dataelementname as jobtype, b.uid, d.name, c.ward_id, d.id as id FROM fact_ihris_datavalue a, dim_ihris_dataelement b, facilities_facility c, common_ward d, common_constituency e WHERE a.mflcode = c.code AND a.dataelementid = b.uid AND c.ward_id = d.id AND d.constituency_id = e.id AND e.id = '%s' """ % (constituency_id) staff = pd.read_sql(query, connection.get_connection()) staff['value'] = pd.to_numeric(staff['value'], downcast='float') staff = staff.groupby(['name']).sum()[['value']] if in_json: return staff.to_json() else: return staff
def get_country_county_number_of_staff(in_json=False): '''return a summaryy of all staff in the country''' query = """ SELECT a.value, b.dataelementname as jobtype, b.uid, c.name , f.name as county FROM fact_ihris_datavalue a, dim_ihris_dataelement b, facilities_facility c, common_ward d, common_constituency e, common_county f WHERE a.mflcode = c.code AND a.dataelementid = b.uid AND c.ward_id = d.id AND d.constituency_id = e.id AND e.county_id = f.id""" staff = pd.read_sql(query, connection.get_connection()) staff['value'] = pd.to_numeric(staff['value'], downcast='float') staff = staff.groupby(['county']).sum()[['value']] if in_json: return staff.to_json() else: return staff