Auto Notification for dotProject

From Superk

#!/usr/bin/env python

"""
This is my attempt at making an auto-notification system for dotProject. We use dotProject 
for our project management at work and the lack of a simple email reminder when tasks were 
coming due or past due was a problem. This script can be run as a cron job and will email 
the owners of all tasks that are due or past due. There is one caveat with this script, 
however. It relies on a custom field being added to the tasks module which allows a task 
creator to 'opt-out' of notifications. Of course this feature can be removed if it's not 
wanted.
"""

import MySQLdb
import MySQLdb.cursors
import smtplib
import datetime

class sql :
    """ Class to handle SQL methods """
    def dbConnect(self, host, user, passwd, db) :
        """ Create a DB connection """
        db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
        return db.cursor(cursorclass=MySQLdb.cursors.DictCursor)

    def getTasks(self, cur) :
        """ Get the overdue tasks """
        cur.execute("SELECT tasks.task_id, tasks.task_name, tasks.task_start_date, tasks.task_end_date, tasks.task_description, tasks.task_priority, contacts.contact_email, projects.project_name \
            FROM tasks \
            LEFT JOIN users ON tasks.task_owner=users.user_id \
            LEFT JOIN contacts ON users.user_contact=contacts.contact_id \
            LEFT JOIN projects ON tasks.task_project=projects.project_id \
            WHERE tasks.task_end_date <= NOW() \
            AND tasks.task_end_date > 0 \
            AND tasks.task_percent_complete < 100 \
            AND tasks.task_status >= 0 \
            ORDER BY tasks.task_priority DESC, tasks.task_end_date ASC")
        return cur.fetchall()
        
    def checkTaskNotify(self, cur, id) :
        """ Check if we're allowed to notify owner """
        cur.execute("SELECT custom_fields_values.* \
            FROM custom_fields_struct \
            LEFT JOIN custom_fields_values ON custom_fields_struct.field_id=custom_fields_values.value_field_id \
            WHERE custom_fields_struct.field_module LIKE \"TASKS\" \
            AND custom_fields_values.value_object_id = " + str(id) + " LIMIT 1")
        res = cur.fetchone()
        
        if res :
            return 1

class display :
    """ Class to handle visual methods """
    def sendTasks(self, res, cursor) :
        """ Display the tasks """
        sqldb = sql()
        fromaddr = "support@asdm.org"
        for i in res :
            if sqldb.checkTaskNotify(cursor, i['task_id']) :
                toaddr = i['contact_email']
                msg = "From: %s\nTo: %s\nSubject: %s\n" %(fromaddr, toaddr, "Project Task Due: " + i['task_name'])
                msg += "\nThe following task is due/past-due:\n\nTASK: " + i['task_name']
                msg += "\nPROJECT: " + i['project_name']
                msg += "\nSTART DATE: " + i['task_start_date'].strftime("%b %d, %Y") 
                msg += "\nDUE DATE: " + i['task_end_date'].strftime("%b %d, %Y") + "\n"
                if i['task_priority'] == -1 :
                    msg += "PRIORITY: LOW\n"
                elif i['task_priority'] == 0 :
                    msg += "PRIORITY: NORMAL\n"
                elif i['task_priority'] == 1 :
                    msg += "PRIORITY: HIGH\n"
                msg += "OWNER: " + i['contact_email'] + "\n"
                msg += "\nURL: http://dotproject.asdm.org/index.php?m=tasks&a=view&task_id=" + str(i['task_id'])
                
                server = smtplib.SMTP('smtp.server.com')
                server.set_debuglevel(1)
                server.sendmail(fromaddr, toaddr, msg)
                server.quit
    
def main() :
    """ Main program """
    sqldb = sql()
    show = display()
    
    cursor = sqldb.dbConnect("mysql.server.com", "dbuser", "password", "dotproject")
    rows = sqldb.getTasks(cursor)
    
    show.sendTasks(rows, cursor)

if __name__ == "__main__" :
    main()

When the script is run on an instance of dotProject that has tasks that are due, it produces as email that looks something like this:

	From: 	  support@server.com
	Subject:  Project Task Due: Replace drives in SERVER
	To: 	  taskowner@server.com

The following task is due/past-due:

TASK: Replace drives in SERVER
PROJECT: SERVER HDDs Failed/Failing
START DATE: May 12, 2005
DUE DATE: May 13, 2005
PRIORITY: NORMAL
OWNER: taskowner@server.com

URL: http://dotproject.server.com/index.php?m=tasks&a=view&task_id=22