#!/usr/bin/env python

import string
from pyPgSQL import PgSQL
from Message import Message

noreminder = ["dupont", "durand"]
rt_reminder = "Request Tracker <tech@example.com>"
db = PgSQL.connect("::rt2")
st = db.cursor()
# Find real users
st.execute("SELECT name,emailaddress FROM users WHERE password != '*NO-PASSWORD*' AND name NOT LIKE 'RT_%' AND name != 'root'")
res = st.fetchone()
while res:
    user = res[0]
    if user in noreminder:
        res = st.fetchone()
        continue
    email = res[1]
    tickets = db.cursor()
    tickets.execute("""SELECT Tickets.id,Tickets.subject,Tickets.created,
                              Tickets.priority
                           FROM Tickets,Users
                           WHERE Users.Name = '""" + user + """'
                                 AND Users.id=Tickets.owner
                                 AND (status='open' OR status='new')
                                 ORDER BY priority DESC, Tickets.created ASC
                                 LIMIT 10""")
    ticket = tickets.fetchone()
    if ticket:
        msg = Message (charset='iso-8859-15',sender=rt_reminder,recipients=[email],server='mail.staff')
        msg.set_subject ("Rappel du travail à faire aujourd'hui")
        body = """
Voici vos tickets les plus importants (ou les plus anciens) enregistrés dans
Request Tracker.

Bon courage.

"""
        while ticket:
            (id, subject, created, priority) = ticket
            body = body + str(id) + ": " + subject + "\r\n"
            ticket = tickets.fetchone()
        body = body + """

http://rt.example.com/
"""
        msg.set_body (body)
        msg.send()
    else:
        msg = Message (charset='iso-8859-15',sender=rt_reminder,recipients=[email],server='mail.staff')
        msg.set_subject ("Aucun travail à faire aujourd'hui ?")
        body = """
Request Tracker ne trouve pas de travail pour vous dans sa base. Est-ce normal ?

http://rt.example.com/
"""        
    res = st.fetchone()
st.close()
db.close()