org_export.py

# Web: Exporty

from flask import request, g
import locale
from sqlalchemy import and_, func
from sqlalchemy.orm import aliased, joinedload
import werkzeug.exceptions

import mo.db as db
from mo.rights import Right
from mo.web import app
from mo.web.table import Table, Row, Column


school_export_columns = (
    Column(key='code', name='kod'),
    Column(key='name', name='nazev'),
    Column(key='town_code', name='kod_obce'),
    Column(key='town', name='obec'),
    Column(key='okres_code', name='kod_okresu'),
    Column(key='kraj_code', name='kod_kraje'),
    Column(key='red_izo', name='red_izo'),
    Column(key='ico', name='ico'),
    Column(key='official_name', name='ofic_nazev'),
    Column(key='address', name='adresa'),
    Column(key='is_zs', name='typ_zs'),
    Column(key='is_ss', name='typ_ss'),
)


@app.route('/org/export/schools')
def org_export_schools():
    sess = db.get_session()
    format = request.args.get('format', 'en_csv')

    def gen_rows():
        town = aliased(db.Place)
        okres = aliased(db.Place)
        kraj = aliased(db.Place)
        for p, s, t, o, k in (
                sess.query(db.Place, db.School, town, okres, kraj)
                .filter(db.Place.type == db.PlaceType.school)
                .filter(db.Place.place_id == db.School.place_id)
                .filter(db.Place.parent == town.place_id)
                .filter(town.parent == okres.place_id)
                .filter(okres.parent == kraj.place_id)
                .yield_per(100)):
            yield Row(keys={
                'code': p.get_code(),
                'name': p.name,
                'red_izo': s.red_izo,
                'ico': s.ico,
                'official_name': s.official_name,
                'address': s.address,
                'is_zs': int(s.is_zs),
                'is_ss': int(s.is_ss),
                'town_code': t.get_code(),
                'town': t.name,
                'okres_code': o.get_code(),
                'kraj_code': k.get_code(),
            })

    table = Table(
        id="skoly",
        columns=school_export_columns,
        rows=gen_rows(),
        filename='skoly',
    )
    return table.send_as(format, streaming=True)


school_orgs_export_columns = (
    Column(key='obec'),
    Column(key='skola'),
    Column(key='kod_skoly'),
    Column(key='red_izo'),
    Column(key='typ'),
    Column(key='garant'),
    Column(key='garant_email'),
)


@app.route('/org/export/school-orgs/<region>')
def org_export_school_orgs(region: str):
    format = request.args.get('format', 'en_csv')
    reg = db.get_place_by_code(region)
    if reg is None:
        raise werkzeug.exceptions.NotFound()

    rr = g.gatekeeper.rights_for(reg)
    if not rr.have_right(Right.edit_orgs):
        raise werkzeug.exceptions.Forbidden()

    sess = db.get_session()
    schools = (sess.query(db.School, db.UserRole)
                   .join(db.RegionDescendant, and_(db.RegionDescendant.region == reg.place_id, db.RegionDescendant.descendant == db.School.place_id))
                   .outerjoin(db.UserRole, and_(db.UserRole.role == db.RoleType.garant_skola, db.UserRole.place_id == db.School.place_id))
                   .options(joinedload(db.School.place).joinedload(db.Place.parent_place), joinedload(db.UserRole.user))
                   .all())

    def key(item):
        s, ur = item
        town = locale.strxfrm(s.place.parent_place.name)
        school = locale.strxfrm(s.place.name)
        garant = ur.user.sort_key() if ur else ("", "")
        return town, school, garant

    def gen_rows():
        for s, ur in sorted(schools, key=key):
            place = s.place
            town = s.place.parent_place
            user = ur.user if ur else None

            typy = []
            if s.is_zs:
                typy.append('ZŠ')
            if s.is_ss:
                typy.append('SŠ')

            yield Row(keys={
                'obec': town.name,
                'skola': place.name,
                'kod_skoly': place.get_code(),
                'red_izo': s.red_izo,
                'typ': '+'.join(typy),
                'garant': user.full_name() if user else "",
                'garant_email': user.email if user else "",
            })

    table = Table(
        id="skoly-organizatori",
        columns=school_orgs_export_columns,
        rows=gen_rows(),
        filename='skoly-organizatori',
    )
    return table.send_as(format, streaming=True)


@app.route('/org/export/z-stats/<int:year>/<int:seq>')
def org_export_z_stats(year: int, seq: int):
    format = request.args.get('format', 'en_csv')
    sess = db.get_session()

    regions = (sess.query(db.Place)
               .filter_by(level=1)
               .all())
    regions.sort(key=lambda p: locale.strxfrm(p.name or ""))

    rounds = (sess.query(db.Round)
              .filter(db.Round.category.like('Z%'))
              .filter_by(year=year, seq=seq)
              .order_by(db.Round.category)
              .all())

    data = {}

    for rnd in rounds:
        if not g.gatekeeper.rights_for_round(rnd).have_right(Right.view_contestants):
            continue
        for successful in [False, True]:
            min_pts = rnd.points_step
            if successful and rnd.score_successful_limit is not None:
                min_pts = rnd.score_successful_limit
            reg = aliased(db.Place)
            stats = (sess.query(reg, func.count(db.Participation.user_id))
                     .select_from(db.Participation)
                     .join(db.Contest, db.Contest.contest_id == db.Participation.contest_id)
                     .filter(db.Contest.round == rnd)
                     .join(db.RegionDescendant, db.RegionDescendant.descendant == db.Contest.place_id)
                     .join(reg, reg.place_id == db.RegionDescendant.region)
                     .filter(reg.level == 1)
                     .filter(sess.query(db.Solution)
                             .with_entities(func.sum(db.Solution.points))
                             .select_from(db.Solution)
                             .filter(db.Solution.user_id == db.Participation.user_id)
                             .join(db.Task, db.Task.round == rnd)
                             .filter(db.Solution.task_id == db.Task.task_id)
                             .scalar_subquery() >= min_pts)
                     .group_by(reg.place_id)
                     .all())
            for reg, cnt in stats:
                data[rnd, reg, successful] = cnt

    columns = [Column(key='kraj', name='kraj')]
    for rnd in rounds:
        columns.append(Column(key=f'total-{rnd.category}', name=f'{rnd.category}-celkem'))
        columns.append(Column(key=f'succ-{rnd.category}', name=f'{rnd.category}-úspěšní'))

    rows = []
    for reg in regions:
        keys = {'kraj': reg.name}
        for rnd in rounds:
            keys[f'total-{rnd.category}'] = data.get((rnd, reg, False), 0)
            keys[f'succ-{rnd.category}'] = data.get((rnd, reg, True), 0)
        rows.append(Row(keys=keys))

    total_keys = {'kraj': 'Celkem'}
    for rnd in rounds:
        total_keys[f'total-{rnd.category}'] = sum(data.get((rnd, reg, False), 0) for reg in regions)
        total_keys[f'succ-{rnd.category}'] = sum(data.get((rnd, reg, True), 0) for reg in regions)
    rows.append(Row(keys=total_keys))

    table = Table(
        id="z-stats",
        columns=columns,
        rows=rows,
        filename=f'stats-z-{year}-{seq}',
    )

    return table.send_as(format)