def getCountryNameByIso2(self, countryCode, lang):
"""Returns the country name by an ISO2 code."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select
short_name_{}1 as name
from iepg_data.master_country
where
iso_3166_1_2_code=%s;
""".format(lang)
return(self.query(sql, bindings=[countryCode]).result())
def countryFilter(self, lang):
"""Returns the list of countries."""
dv = DataValidator()
dv.checkLang(lang)
sql = """
select distinct
iso_3166_1_2_code as id,
short_name_{}1 as name,
short_name_{}_order
from
iepg_data.iepg_final_data a inner join
iepg_data.master_country b on
a.id_master_country=b.id_master_country
where country
order by short_name_{}_order;""".format(lang, lang, lang)
return(self.query(sql).result())
def ranking(self, lang, countryCode, family, variable, year, filter=None, toolFilter=None):
"""Returns the ranking and the value for a variable and a country."""
dv = DataValidator()
dv.checkVariable(family, variable)
dv.checkLang(lang)
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
where c.iso_3166_1_2_code=%s
order by ranking;
""".format(var["column"], var["column"])
return(self.query(sql, bindings=[year, year, year, countryCode]).result())