<html><head><meta name="color-scheme" content="light dark"></head><body><pre style="word-wrap: break-word; white-space: pre-wrap;">from mm_stats.auth import PostgresDb
from mm_stats.definitions import logger
from mm_stats.config import FILTER_CONFIG
from psycopg2.extensions import AsIs
from mm_stats.filter.filter_utils import (
    get_all_project_ids,
    get_project_ids_by_keywords,
    get_project_ids_from_excel_column,
    get_project_ids_by_countries,
    create_filter_by_project_ids,
)
from mm_stats.config import HOT_AUDACIOUS_COUNTRIES

from mm_stats.filter.sql_templates import filter_sessions, filter_osm


def run_filter_init(_filter: str) -&gt; None:
    """Filter projects and initialize new schema."""
    logger.info(f"{_filter}: starting run_filter_init")

    if _filter != "all":
        if "not_" in _filter[:4]:
            config = FILTER_CONFIG[_filter[4:]]
        else:
            config = FILTER_CONFIG[_filter]
        method = config["method"]

    if _filter == "all":
        project_ids = get_all_project_ids()
    elif method == "filter_by_keywords":
        project_ids = get_project_ids_by_keywords(keywords=config["keywords"])
    elif method == "filter_by_excel_column":
        project_ids = get_project_ids_from_excel_column(
            file_name=config["file_name"], column=config["column"]
        )
    elif method == "filter_by_countries":
        project_ids = get_project_ids_by_countries(countries=config["countries"])

    # init schema and create filter table
    create_filter_by_project_ids(filter_name=_filter, project_ids=project_ids)

    logger.info(f"{_filter}: finished run_filter_init")


def run_filter_sessions(_filter: str) -&gt; None:
    """Copy sessions, tasks and projects to new schema."""
    logger.info(f"{_filter}: start run_filter_sessions")
    sql = f"SET SCHEMA '{_filter}';"
    sql += filter_sessions.load_sql()
    db = PostgresDb()
    db.query(sql, {"_filter": AsIs(_filter)})
    logger.info(f"{_filter}: end run_filter_sessions")


def run_filter_osm(_filter: str) -&gt; None:
    """Copy OSM contributions to new schema."""
    logger.info(f"{_filter}: start run_filter_osm")
    sql = f"SET SCHEMA '{_filter}';"
    sql += filter_osm.load_sql()
    db = PostgresDb()
    db.query(sql, {"_filter": AsIs(_filter)})
    logger.info(f"{_filter}: end run_filter_osm")


def run_filter_countries(_filter: str) -&gt; None:
    """Filter countries by Tasking Manager projects or by country list."""
    logger.info(f"{_filter}: start run_filter_countries")
    sql = f"SET SCHEMA '{_filter}';"
    if _filter in ["hot-audacious"]:
        sql += """
            DROP TABLE IF EXISTS ne_10m_admin_0_countries;
            CREATE TABLE ne_10m_admin_0_countries as
            (
            with country_ids as (
                select
                  c.id
                from data_preparation.ne_10m_admin_0_countries c
                where
                    c.name ilike ANY( %(countries)s )
                    OR
                    c.name_alt ilike ANY( %(countries)s )
                    OR
                    c.name_long ilike ANY( %(countries)s )
                    OR
                    c.name_en ilike ANY( %(countries)s )
                group by c.id
            )
            select a.* from data_preparation.ne_10m_admin_0_countries a, country_ids b
            where a.id = b.id
            );
        """
    else:
        # here we filter countries by Tasking Manager projects
        sql += """
            -- copy countries
            DROP TABLE IF EXISTS ne_10m_admin_0_countries;
            CREATE TABLE ne_10m_admin_0_countries as
            (
            with country_ids as
                (
                select
                  c.id
                from data_preparation.ne_10m_admin_0_countries c, projects p
                where c.id = p.ne_id
                group by c.id
                )
            select a.* from data_preparation.ne_10m_admin_0_countries a, country_ids b
            where a.id = b.id
            );
        """

    db = PostgresDb()
    db.query(sql, {"countries": HOT_AUDACIOUS_COUNTRIES})
    logger.info(f"{_filter}: end run_filter_countries")
</pre></body></html>