export-pion

#!/usr/bin/env python3
# Účast ve všech soutěžích ročníku (anonymně)

from dataclasses import dataclass
from sqlalchemy import and_
from sqlalchemy.orm import joinedload
import sys

import mo.csv
import mo.db as db

sess = db.get_session()

res = (sess.query(db.Participant, db.Participation, db.Contest, db.Round)
       .select_from(db.Participant)
       .join(db.Participation, db.Participation.user_id == db.Participant.user_id)
       .join(db.Contest, db.Contest.contest_id == db.Participation.contest_id)
       .join(db.Round, and_(db.Round.round_id == db.Contest.round_id, db.Round.year == db.Participant.year))
       .options(joinedload(db.Participant.user))
       .options(joinedload(db.Participant.school_place).joinedload(db.Place.parent_place).joinedload(db.Place.parent_place).joinedload(db.Place.parent_place))
       .options(joinedload(db.Participation.place))
       .filter(db.Round.master_round_id == db.Round.round_id)
       .all())


@dataclass
class Row:
    rocnik: str = ""
    kategorie: str = ""
    kolo_seq: str = ""
    kolo: str = ""
    misto: str = ""
    kod_mista: str = ""
    kod_ucastnika: str = ""
    nazev_skoly: str = ""
    kod_skoly: str = ""
    mesto_skoly: str = ""
    kraj_skoly: str = ""


output = []
for pant, pion, ct, rnd in res:
    output.append(Row(
        rocnik=str(rnd.year),
        kategorie=rnd.category,
        kolo_seq=str(rnd.seq),
        kolo=rnd.name,
        misto=ct.place.name,
        kod_mista=str(ct.place.place_id),
        kod_ucastnika=str(pion.user_id),
        nazev_skoly=pant.school_place.name,
        kod_skoly=str(pant.school),
        mesto_skoly=pant.school_place.parent_place.name,
        kraj_skoly=pant.school_place.parent_place.parent_place.parent_place.name,
    ))

output.sort(key=lambda o: (o.rocnik, o.kategorie, o.kolo_seq, o.kod_mista, o.kod_ucastnika))
mo.csv.write(sys.stdout, mo.csv.FileFormat.en_csv, Row, output)