예제 #1
0
    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())
예제 #2
0
 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())
예제 #3
0
    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())