We currently have 5 projects on Trac. We have set up seperate jobs for each of
these projects to receive email notificationto for any changes that are made to
the tickets.
The programmer that set up the initial job is no longer with our company and
the knowledge of the remaining team is limited in Trac and Puthon. We have set
up one new job successfully by basically just duping the job information and
the python script to point to the appropriate folders for the new project.
We are having a problem with the additional three jobs. We are running them
with the Microsoft Job Scheduler and are continually recieving a 1 return
code. We suspect that there is an error in the Python script attached. Any
input would be greatly appreciated.
Thank you.
Geri
from pysqlite2 import dbapi2 as sqlite
from optparse import OptionParser
from smtplib import SMTP
from cgi import escape
from time import strftime
from time import gmtime
from time import localtime
def cleanse(text):
text = text.replace("\r", "").replace("\n\n", "\n").replace("\n\n", "\n")
return escape(text).replace("\n", "<br>")
#get arguments from command line
parser = OptionParser()
parser.add_option("-t", "--hours", dest="hoursBack", type="int",
help="# of hours back from now to look for changes")
parser.add_option("-e", "--email", dest="emailAddress",
help="email address to send the report to")
parser.add_option("-s", "--server", dest="emailServer",
help="smtp server address")
parser.add_option("-d", "--db", dest="tracDBPath",
help="path to trac sqlite database file")
(options, args) = parser.parse_args()
#connect to db
conn = sqlite.connect(options.tracDBPath)
cur = conn.cursor()
cur2 = conn.cursor()
#get tickets that have changed in the last 24 hrs
sqlTickets = \
"""
SELECT
id, summary, status,
CASE resolution is null WHEN 1 THEN 'open' ELSE resolution END,
CASE milestone is null WHEN 1 THEN 'unscheduled' ELSE milestone END,
CASE owner is null WHEN 1 THEN 'unassigned' ELSE owner END,
CASE component is null WHEN 1 THEN 'unclassified' ELSE component END,
CASE tc_t.value is null WHEN 1 THEN 'untested' ELSE tc_t.value END,
CASE tc_d.value is null WHEN 1 THEN 'unspecified' ELSE tc_d.value END
FROM ticket t, ticket_custom tc_t, ticket_custom tc_d
WHERE changetime > strftime('%%s', 'now', '-%(hours)d hours')
AND tc_t.name = 'testing' AND tc_t.ticket = t.id
AND tc_d.name = 'deployment' AND tc_d.ticket = t.id
ORDER BY changetime desc
""" % {"hours":options.hoursBack}
cur.execute(sqlTickets)
tickets = cur.fetchall()
#create email header
header = "Abbyy Trac activity %dhrs back from %s" % (options.hoursBack,
strftime("%m-%d-%Y %I:%M %p", localtime()))
html = "<link rel='stylesheet' type='text/css'
href='http://10.0.0.247:8080/css/Layout.css'>" + \
"<link rel='stylesheet' type='text/css'
href='http://10.0.0.247:8080/css/FrontPage.css'>" + \
"<link rel='stylesheet' type='text/css'
href='http://10.0.0.247:8080/css/Content.css'>" + \
"<link rel='stylesheet' type='text/css'
href='http://10.0.0.247:8080/css/White.css'>";
html += "<br /><br />"
html += "<div id='FeaturedArticlesBlock'>" + \
"<h2 style='MARGIN-TOP: 10px;MARGIN-BOTTOM: 0px'>" + header + "</h2>" +
\
"<div style='MARGIN-LEFT: 5px'><ul style='MARGIN-TOP: 0px; PADDING-TOP:
0px'>"
for (id,summary, status, resolution,milestone,owner,component,tested,deployed)
in tickets:
html += ("<li style='MARGIN-BOTTOM: 1px'>" + \
"<a href='http://10.0.0.247:8080/trac/Abbyy/ticket/%(id)d'
style='CURSOR:hand;COLOR:black;TEXT-DECORATION:underline'>" + \
"#%(id)d</a> - %(summary)s (%(status)s)") % \
{"id":id, "summary":escape(summary), "status":escape(status)}
html += "<img src='http://10.0.0.247:8080/images/blank.gif' class='hr' />\n"
html += "</ul></div>\n"
#create email detail area
for (id,summary,status,resolution,milestone,owner,component,tested,deployed) in
tickets:
#get the ticket changes for the ticket
sqlTicketChanges = \
"""
select time, author, field, oldvalue, newvalue
from ticket_change
where ticket = %(id)d and
time > strftime('%%s', 'now', '-%(hours)d hour')
order by time, CASE field WHEN 'comment' THEN 1 ELSE 0 END
""" % {"id":id, "hours":options.hoursBack}
cur.execute(sqlTicketChanges)
#create the ticket summary
if status == "assigned":
owner += "*"
html += \
"""
<h2>Ticket %(id)d</h2>
<p>
<strong>%(summary)s (%(status)s, %(resolution)s)</strong>
</p>
<p>
owner: <b>%(assigned)s</b>, component: <b>%(component)s</b>,
milestone: <b>%(milestone)s</b><br>
verified through: <b>%(tested)s</b>, deployed through:
<b>%(deployed)s</b>
</p>
""" % {"id":id, "summary":escape(summary), "status":escape(status),
"resolution":escape(resolution), "assigned":escape(owner),
"component":escape(component), "milestone":escape(milestone),
"tested":escape(tested), "deployed":escape(deployed)}
lastTimestamp = 0
for (timestamp,author,field,oldvalue,newvalue) in cur:
#under a ticket, a new modification/ticket change
if lastTimestamp != timestamp:
lastTimestamp = timestamp
html += \
"""
<p>
%(author)s (%(timestamp)s)
</p>
""" % {"author":escape(author), "timestamp":strftime("%m-%d-%Y
%I:%M %p", localtime(timestamp))}
#under a ticket change, one of many possible field changes
if field != "comment":
prefixVals = ""
if field == "summary" or field == "description":
prefixVals = "<br> "
html += \
"""
<p>
- changed <b>%(field)s:</b> <i>"%(oldvalue)s"</i>
%(preVals)s<b>to</b> <i>"%(newvalue)s"</i><br>
</p>
""" \
% \
{"field":escape(field),
"oldvalue":cleanse(oldvalue),
"newvalue":cleanse(newvalue),
"preVals":prefixVals}
continue
#under a ticket change, new commentary
if newvalue == "":
continue
html += \
"""
<p>
<strong>Comment: </strong>%(comment)s
</p>
""" \
% \
{"comment":cleanse(newvalue)}
html += \
"""
<p class='ArticleNav'>
<a href='http://10.0.0.247:8080/trac/Abbyy/ticket/%(id)d'
style='CURSOR:hand;'>» View Ticket</a>
<a href='http://10.0.0.247:8080/trac/Abbyy/ticket/%(id)d'
style='CURSOR:hand;'>» Email Owner</a>
<img src='http://10.0.0.247:8080/images/blank.gif' class='hr' />
</p>
""" \
% \
{"id":id}
#get svn commits to the trunk
sqlCommits = \
"""
select distinct revision.*
from revision
inner join node_change on revision.rev = node_change.rev
where time > strftime('%%s', 'now', '-%(hours)d hour')
and substr(node_change.name, 1, 6) = 'trunk/'
order by revision.rev desc
""" % {"hours":options.hoursBack}
cur.execute(sqlCommits)
#write commit info into email
for (rev,time,author,message) in cur:
html += \
"""
<p>
<a href='http://10.0.0.247:8080/trac/Abbyy/changeset/%(rev)d'
style='text-decoration:underline;color:white;cursor:pointer;cursor:hand;'>%(rev)d</a>
-
%(author)s (%(time)s)<br>
%(message)s
</p>
""" \
% \
{"rev":rev,
"time":strftime("%m-%d-%Y %I:%M %p", localtime(time)),
"author":escape(author),
"message":cleanse(message)}
html += "</div>"
#close out the connections
cur.close()
conn.close()
#close out the html
html += "</table>"
#prepare msg
msg = "From: %s\r\n" + \
"To: %s\r\n" + \
"Subject: %s\r\n" + \
"Content-Type: text/html; charset=\"us-ascii\"\r\n\r\n"
msg %= (options.emailAddress, options.emailAddress, header)
msg += html
#send off the email
smtp = SMTP(options.emailServer)
smtp.sendmail(options.emailAddress, options.emailAddress, msg)
smtp.quit()
print "done"
_______________________________________________
Trac mailing list
[email protected]
http://lists.edgewall.com/mailman/listinfo/trac
