export-okresni-anketa

#!/usr/bin/env python3
# Předvyplněné statistiky pro anketu mezi okresními garanty

from dataclasses import dataclass
from sqlalchemy import and_
from sqlalchemy.orm import joinedload, aliased
import sys
from typing import Dict

import mo.config as config
import mo.csv
import mo.db as db


@dataclass
class Row(mo.csv.Row):
    jmeno: str
    okres: str
    kraj: str
    dom_ucastnici: int = 0
    okr_ucastnici: int = 0
    dom_skoly: int = 0
    skolni_garanti: int = 0
    dom_ucastnici_s_body: int = 0


stats: Dict[int, Row] = {}
sess = db.get_session()

for okres in sess.query(db.Place).filter_by(level=2).options(joinedload(db.Place.parent_place)).all():
    stats[okres.place_id] = Row("", okres.name, okres.parent_place.name)


def kolo(seq: int, region_id: int):
    print(seq, region_id, file=sys.stderr)
    row = stats[region_id]

    ctq = (sess.query(db.Contest)
           .join(db.Contest.round)
           .filter(db.Round.year == config.CURRENT_YEAR)
           .filter(db.Round.category.in_(['Z6', 'Z7', 'Z8']))
           .filter(db.Round.seq == seq)
           .join(db.RegionDescendant, and_(db.RegionDescendant.descendant == db.Contest.place_id,
                                           db.RegionDescendant.region == region_id))
           .filter(sess.query(db.Participation)
                   .filter(db.Participation.contest_id == db.Contest.contest_id)
                   .filter(db.Participation.state.in_((db.PartState.registered, db.PartState.active)))
                   .exists())
           .subquery())
    cts = aliased(db.Contest, ctq)

    pant_q = (sess.query(db.Participation.user_id)
              .join(cts, cts.contest_id == db.Participation.contest_id)
              .filter(db.Participation.state.in_((db.PartState.registered, db.PartState.active))))

    if seq == 1:
        row.dom_ucastnici = pant_q.distinct().count()

        row.dom_ucastnici_s_body = (pant_q
                                    .filter(sess.query(db.Solution)
                                            .filter(db.Solution.user_id == db.Participation.user_id)
                                            .filter(db.Solution.points != None)
                                            .exists())
                                    .distinct()
                                    .count())

        row.dom_skoly = sess.query(cts.place_id).distinct().count()

        skg = (sess.query(cts.place_id)
               .filter(sess.query(db.UserRole)
                       .filter(db.UserRole.role == db.RoleType.garant_skola)
                       .join(db.Place, db.Place.place_id == db.UserRole.place_id)
                       .join(db.RegionDescendant, and_(db.RegionDescendant.region == db.Place.place_id,
                                                       db.RegionDescendant.descendant == cts.place_id))
                       .exists())
               .distinct()
               .count())
        row.skolni_garanti = skg

    else:
        row.okr_ucastnici = pant_q.distinct().count()


for seq in [1, 2]:
    for reg in stats.keys():
        kolo(seq, reg)

mo.csv.write(sys.stdout, mo.csv.FileFormat.en_csv, Row, sorted(stats.values(), key=lambda r: (r.kraj, r.okres)))