Page History

Turn Off History

  #!/usr/bin/python

  """
  Given a CVSTrac Ticket Number, load the record from the SQLite database and
  send out an email.

  """
  import sys
  import types
  import getopt
  import sqlite
  import smtplib

  # See #355 for an enhancement to CVSTrac that would improve this script

  # These are the parameters you are likely to want to change
  DEFAULT_DBFILE = "/var/cvstrac/trac.db"

  smtpServer = "mail.mydomain.com"
  fromAddress = "cvstrac@mydomain.com"
  cclist = "supervisor@mydomain.com"
  replyTo = "discussion@mydomain.com"
  subjectTemplate = "New ticket has been added for your attention: %d"
  ticketUrl = "http://tickets.mydomain.com/cgi-bin/cvstrac.cgi/trac/tktview?tn=%d"

  # This is the body of the email. If you change it, you may need to change
  # the parameters used on the template in the getEmailContents() function below.
  bodyTemplate = """
                    TICKET ADD/CHANGE

      Ticket #%d

      Ticket URL: %s

      Title: %s
      Status: %s
      Type: %s

      Assignee: %s (%s)
      Requester: %s (%s)
      Contact: %s

      Description: %s

      ---- Remarks ----

      %s

  """

  # This ends the user configurable section

  dbfile = DEFAULT_DBFILE
  tolist = None
  verbosity = 1
  logfile = None
  pretend = False

  class TicketModel:
      """Keeps track of information about a ticket
      """
      def __init__(self, tktnum):
          self.tktnum = tktnum
          self.type = ""
          self.status = ""
          self.origtime = ""
          self.changetime = ""
          self.derivedfrom = ""
          self.version = ""
          self.assignedto = ""
          self.assignemail = ""
          self.severity = ""
          self.priority = ""
          self.subsystem = ""
          self.owner = ""
          self.owneremail = ""
          self.title = ""
          self.contact = ""
          self.extra1 = ""
          self.extra2 = ""
          self.extra3 = ""
          self.extra4 = ""
          self.extra5 = ""
          self.description = ""
          self.remarks = ""

  def getTicketFromDatabase(tktnum, dbfile):
      """Takes a Ticket Number and returns a Ticket Model."""

      model = TicketModel(tktnum)

      tktConnection = sqlite.connect(dbfile)
      if tktConnection is None:
          error("Could not connect to database")
          sys.exit(1)
      tktCursor = tktConnection.cursor()
      if tktCursor is None:
          error("Could not get Cursor from Connection")
          sys.exit(2)
      sqlstr = (
          "select " +
          "tn, " +
          "type, " +
          "status, " +
          "origtime, " +
          "changetime, " +
          "derivedfrom, " +
          "version, " +
          "assignedto, " +
          "u.email, " +
          "severity, " +
          "priority, " +
          "subsystem, " +
          "owner, " +
          "o.email, " +
          "title, " +
          "contact, " +
          "extra1, " +
          "extra2, " +
          "extra3, " +
          "extra4, " +
          "extra5, " +
          "description, " +
          "remarks " +
          "from ticket t, user u, user o " +
          "where u.id=t.assignedto and o.id=t.owner and tn='%d'") % (tktnum)
      tktCursor.execute(sqlstr)
      row = tktCursor.fetchone()
      if row is None or len(row) == 0:
          error("Could not find Ticket #" + `tktnum` + " in database")
          sys.exit(3)

      (
          model.tn,
          model.type,
          model.status,
          model.origtime,
          model.changetime,
          model.derivedfrom,
          model.version,
          model.assignedto,
          model.assignemail,
          model.severity,
          model.priority,
          model.subsystem,
          model.owner,
          model.owneremail,
          model.title,
          model.contact,
          model.extra1,
          model.extra2,
          model.extra3,
          model.extra4,
          model.extra5,
          model.description,
          model.remarks
      ) = row

      tktCursor.close()
      tktConnection.close()
      return model

  def getEmailContents(ticket):
      """This creates the body of the email as a single string"""

      ticketLink = ticketUrl % (ticket.tn)
      body = bodyTemplate % (
          ticket.tn, ticketLink, ticket.title, ticket.status,
          ticket.type, ticket.assignedto, ticket.assignemail, ticket.owner,
          ticket.owneremail, ticket.contact, ticket.description,
          ticket.remarks)
      return body

  def sendEmail(ticket, contents, tolist, cclist):
      """Sends an email about a ticket add or change"""

      # There is only one list of to addresses
      if tolist is None and ticket is not None and ticket.assignemail is not None:
          tolist = ticket.assignemail

      # The CC addresses are whatever is passed in plus the ticket owner
      ccSet = []
      if cclist is not None and ticket.owneremail is not None and ticket.owneremail not in cclist:
          cclist += "," + ticket.owneremail

      subject = subjectTemplate % (ticket.tn)
      msgBody = "From: %s\r\nTo: %s\r\nCc: %s\r\nSubject: %s\r\nReply-To: %s\r\n\r\n%s" % (
              fromAddress, tolist, cclist, subject, replyTo, contents)
      recipients = (tolist + "," + cclist).split(",")
      if not pretend:
          session = smtplib.SMTP(smtpServer)
          session.sendmail(fromAddress, recipients, msgBody)
          session.quit()
      debug(msgBody)

  ### FUNCTIONS FOR RUNNING AS A COMMAND

  def usage(scriptname):
      print """%s

      Email a CVSTrac Ticket to users by reading a SQLite database.

      Usage: %s [option] ticket-number

      where "option" is one of:
          -h, --help         Get this message
          -v, --version      Print the version info
          -q, --quiet        Quiet mode, only errors printed
          -d, --debug        Debug mode, display debugging messages
          -p, --pretend      Do everything except sending the email
          -f, --dbfile       SQLite database file for CVSTrac
                             Default is %s
          -t, --tolist       List of email addresses to send email to
          -c, --cclist       List of email addresses CC email to
          -l, --logfile      Informational messages from this program

           """ % (getversion(), scriptname, DEFAULT_DBFILE)

  def getversion():
       return "ticket-email V1.0"

  def error(str):
      log(str)

  def warn(str):
      if verbosity > 0:
          log(str)

  def debug(str):
      if verbosity > 1:
          log(str)

  def log(str):
      if logfile is None:
          print str
      else:
          logfile.write(str + "\n")

  def main(argv):
      assert argv, "Argument to ticket-email.main() left blank"
      assert type(argv) == types.ListType, "Wrong type passed as argument to session.main()"

      # Initialize main variables with default values
      global dbfile,tolist,cclist,verbosity,logfile,pretend

      SHORTOPTLIST = "hvqdpf:t:c:l:"
      LONGOPTLIST = "help version quiet debug pretend dbfile= tolist= cclist= logfile=".split()

      # Process command line parameters, replacing defaults
      try:
          optlist, remainder = getopt.getopt(argv[1:], SHORTOPTLIST, LONGOPTLIST)
      except getopt.GetoptError:
          usage(argv[0])
          sys.exit(8)

      for opt, val in optlist:
          if opt in ["-h", "--help"]:
              usage(argv[0])
              sys.exit(7)
          if opt in ["-v", "--version"]:
              print getversion()
          if opt in ["-q", "--quiet"]:
              verbosity = 0
          if opt in ["-d", "--debug"]:
              verbosity = 2
          if opt in ["-p", "--pretend"]:
              pretend  = True
          if opt in ["-f", "--dbfile"]:
              dbfile = val
          if opt in ["-c", "--cclist"]:
              cclist = val
          if opt in ["-t", "--tolist"]:
              tolist = val
          if opt in ["-l", "--logfile"]:
              logname = val
              if logname is not None and len(logname) > 0:
                  logfile = open(logname, "a")

      if len(remainder) < 1:
          print "Error: You must specify the Ticket #"
          usage(argv[0])
          sys.exit(9)

      debug("Running %s with parameters %s" % (argv[0], argv))
      try:
          tktnum = int(remainder[0])
          ticket = getTicketFromDatabase(tktnum, dbfile)
          contents = getEmailContents(ticket)
          sendEmail(ticket, contents, tolist, cclist)
          debug("Email sent")
      except:
          errstr = str(sys.exc_info()[0]) + "\n"
          errstr = errstr + str(sys.exc_info()[1]) + "\n"
          error(errstr)

  if __name__ == '__main__':
      main(sys.argv)