Пример #1
0
    def getCountriesData(self, countries, year, family, variable):
        """Returns the selected variable for a year and selected countries."""
        dv = DataValidator()
        dv.checkVariable(family, variable)
        var = common.helpers.getVariableData(family, variable)

        f = "("
        for c in countries:
            f += "'"+c+"',"
        f = f.rstrip(",")+")"

        sql = """
        select
        a.iso_3166_1_2_code as code,
        b.{} as value
        from
        iepg_data.master_country a inner join
        {} b on
        a.id_master_country=b.id_master_country
        where
        a.iso_3166_1_2_code in {} and
        date_part('year', b.date_in)=%s;
        """.format(var["column"], var["table"], f)

        return(self.query(sql, bindings=[year]).result())
Пример #2
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())
Пример #3
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())
Пример #4
0
    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())
Пример #5
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())