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
