83 lines
2.3 KiB
Python
Executable File
83 lines
2.3 KiB
Python
Executable File
#!/usr/bin/python3
|
|
import datetime
|
|
import email.message
|
|
import smtplib
|
|
|
|
import psycopg
|
|
|
|
import config
|
|
|
|
min_distance = datetime.timedelta(minutes=60)
|
|
|
|
def simple_sum(meet_id, kind):
|
|
q = \
|
|
f"""
|
|
select k.*, sum(preference) as preference
|
|
from {kind} k join {kind}_vote v on k.id = v.{kind}
|
|
where k.meet = %s
|
|
group by k.id order by preference desc
|
|
"""
|
|
csr.execute(q, (meet_id,))
|
|
return csr.fetchall()
|
|
|
|
db = psycopg.connect(dbname=config.dbname, user=config.dbuser)
|
|
csr = db.cursor(row_factory=psycopg.rows.namedtuple_row)
|
|
|
|
csr.execute("select * from meet where active")
|
|
for meet in csr.fetchall():
|
|
|
|
csr.execute(
|
|
"""
|
|
select distinct email, short
|
|
from date_vote
|
|
join date on date_vote.date = date.id
|
|
join bod on date_vote.bod = bod.id
|
|
where meet = %s
|
|
order by email
|
|
""",
|
|
(meet.id,))
|
|
voters = csr.fetchall()
|
|
dates = simple_sum(meet.id, "date")
|
|
times = simple_sum(meet.id, "time")
|
|
places = simple_sum(meet.id, "place")
|
|
|
|
msg = "Aktueller Favorit:\n\n" \
|
|
f" {dates[0].date}, {times[0].time}\n" \
|
|
f" im {places[0].name}\n\n\n" \
|
|
"Abgestimmt haben:\n\n"
|
|
for v in voters:
|
|
msg += f" * {v.email}\n"
|
|
|
|
csr.execute(
|
|
"""
|
|
select *, now() - ts as age
|
|
from news
|
|
where meet = %s
|
|
order by ts desc
|
|
limit 1
|
|
""",
|
|
(meet.id,))
|
|
last_news = csr.fetchone()
|
|
if not last_news or last_news.content != msg and last_news.age >= min_distance:
|
|
print(msg)
|
|
|
|
csr.execute(
|
|
"""
|
|
insert into news(meet, ts, content) values(%s, now(), %s)
|
|
returning id
|
|
""",
|
|
(meet.id, msg))
|
|
new_news = csr.fetchone()
|
|
db.commit()
|
|
|
|
emsg = email.message.EmailMessage()
|
|
emsg["From"] ="noreply@hjp.at"
|
|
emsg["To"] = ", ".join(v.email for v in voters)
|
|
emsg["Subject"] = "Neuer Zwischenstand: " + meet.title
|
|
emsg["Message-ID"] = f"<meeat.{new_news.id}@hjp.at>"
|
|
if last_news:
|
|
emsg["In-Reply-To"] = f"<meeat.{last_news.id}@hjp.at>"
|
|
emsg.set_content(msg)
|
|
mta = smtplib.SMTP(host="localhost")
|
|
mta.send_message(emsg)
|