def variableData(self, family, variable, year, filter=None, toolFilter=None): """Returns variable data for a year.""" dv = DataValidator() dv.checkVariable(family, variable) dv.checkYear(year) var = common.helpers.getVariableData(family, variable) if filter: f = "(" for fi in filter: f=f+"'"+fi+"'," f = f.rstrip(",")+")" if toolFilter: tf = "(" for fi in toolFilter: tf=tf+"'"+fi+"'," tf = tf.rstrip(",")+")" sql = """ select a.iso_3166_1_2_code as code, {} as value from iepg_data.master_country a inner join {} b on a.id_master_country=b.id_master_country where date_part('YEAR', b.date_in)=%s """.format(var["column"], var["table"]) if toolFilter: sql += " and iso_3166_1_2_code in {}".format(tf) if filter: sql += " and iso_3166_1_2_code in {}".format(f) sql += ';' return(self.query(sql, bindings=[year]).result())
def rankingComplete(self, lang, family, variable, year, filter=None, toolFilter=None): """Returns the ranking and the value for a variable for all countries.""" dv = DataValidator() dv.checkVariable(family, variable) dv.checkLang(lang) dv.checkYear(year) var = common.helpers.getVariableData(family, variable) if filter: f = "array[" for fi in filter: f=f+"'"+fi+"'," f = f.rstrip(",")+"]::varchar[]" if toolFilter: tf = "array[" for fi in toolFilter: tf=tf+"'"+fi+"'," tf = tf.rstrip(",")+"]::varchar[]" sql = """ select c.iso_3166_1_2_code as code, '{}' as variable, b.ranking as ranking, a.{} as value, %s as year from {} a inner join ( select row_number() over (order by {} desc) as ranking, {} from ( select {} from {} a inner join iepg_data.master_country b on a.id_master_country=b.id_master_country where {} is not null and """.format(var["name_"+lang], var["column"], var["table"], var["column"], var["column"], var["column"], var["table"], var["column"]) if filter: sql += """ array[b.iso_3166_1_2_code]::varchar[] <@ {} and """.format(f) if toolFilter: sql += """ array[b.iso_3166_1_2_code]::varchar[] <@ {} and """.format(tf) sql += """ date_part('year', a.date_in)=%s ) as f) as b on a.{}=b.{} and date_part('year', a.date_in)=%s inner join iepg_data.master_country c on a.id_master_country=c.id_master_country order by ranking; """.format(var["column"], var["column"]) return(self.query(sql, bindings=[year, year, year]).result())