#!/bin/env python # -*- coding: iso-8859-15 -*- #---------------------------------------------------------------------------- # Name: managePyCon.py # Author: Steve Holden # Created: 01/02/2007 # Copyright: Holden Web for Python Software Foundation #---------------------------------------------------------------------------- import sys import wx import hwWx from db import conn, curs, Error as dbError #--------------------------------------------------------------------------- class myApp(wx.App): def OnInit(self): d = ContactEdit(None, -1, "Edit Contacts") d.Show(True) return True #--------------------------------------------------------------------------- import time, datetime import smtplib from dialogs_wdr import * from db import conn, curs, Error as dbError, pmark from hwWx.mixin import * setPmark(pmark) # WDR: classes class SetInvoiceNo(wx.Dialog): def __init__(self, parent, id, title, pos = wx.DefaultPosition, size = wx.DefaultSize, style = wx.DEFAULT_DIALOG_STYLE ): wx.Dialog.__init__(self, parent, id, title, pos, size, style) # WDR: dialog function InvoiceNoDialog for SetInvoiceNo InvoiceNoDialog( self, True ) # WDR: handler declarations for SetInvoiceNo # WDR: methods for SetInvoiceNo # WDR: handler implementations for SetInvoiceNo class MessageName(wx.Dialog): def __init__(self, parent, id, title, pos = wx.DefaultPosition, size = wx.DefaultSize, style = wx.DEFAULT_DIALOG_STYLE ): wx.Dialog.__init__(self, parent, id, title, pos, size, style) # WDR: handler declarations for MessageName MessageNameDialog(self, True) # WDR: methods for MessageName # WDR: handler implementations for MessageName class SendMail(wx.Dialog, hwDataDialog): FIELDS = ( (txtmsgsubject, "msgsubject", tpTXT), (txtmsgbody, "msgbody", tpTXT), (stxmsgname, "msgname", tpSTX) ) table = "sponsorship_message" keyfield = "msgid" selfield = "msgname" uniquefields = ("msgname", ) selector = lbxmsgname def __init__(self, parent, id, title, contactIDs, pos = wx.DefaultPosition, size = wx.DefaultSize, style = wx.DEFAULT_DIALOG_STYLE ): wx.Dialog.__init__(self, parent, id, title, pos, size, style) self.curs = curs self.conn = conn self.parent = parent self.contactIDs = contactIDs # WDR: dialog function SendMailDialog for SendMail SendMailDialog( self, True ) self.SetSelector() self.SetContents(self.row) self.newrecord = False # WDR: handler declarations for SendMail wx.EVT_BUTTON(self, btnDone, self.OnDone) wx.EVT_BUTTON(self, btnMsgDelete, self.OnDelete) wx.EVT_BUTTON(self, btnNew, self.OnNew) wx.EVT_BUTTON(self, btnSend, self.OnSend) wx.EVT_BUTTON(self, btnSaveAs, self.OnSaveAs) wx.EVT_BUTTON(self, btnSave, self.OnSave) wx.EVT_LISTBOX(self, lbxmsgname, self.OnSelect) # WDR: methods for SendMail def SetSelector(self, selected=None): self.fkByCol(""" SELECT msgname, msgid FROM sponsorship_message WHERE msgname NOT LIKE '%#' ORDER BY msgname """, self.FindWindowById(lbxmsgname)) self.ID = self.SelectorLoad(selected=selected) if self.ID: self.row = dict(zip( [f[1] for f in self.FIELDS], self.readRow(self.ID, self.keyfield, self.table))) else: self.row = self.EmptyRow() def SelectorKeys(self): sql = "SELECT msgname, msgid FROM sponsorship_message WHERE msgname NOT LIKE '%#%' ORDER BY msgname" curs.execute(sql) nv = curs.fetchall() vals = [n[1] for n in nv] names = [n[0] for n in nv] return names, vals # WDR: handler implementations for SendMail def OnDone(self, event): self.EndModal(wx.ID_OK) def OnSend(self, event): msgtmpl = """\ From: Steve Holden Subject: [PyCon: Sponsorship] %(subject)s To: %(recipient)s Cc: pycon-sponsors@python.org Reply-To: pycon-sponsors@python.org Errors-To: steve@holdenweb.com Dear %(name)s: %(message)s regards Steve Holden Sponsorship Coordinator -- PyCon TX 2007 : The fifth Python Community Conference http://www.pycon.org/ http://www.python.org/pycon/ The scoop on Python implementations and applications! """ testing = self.FindWindowById(chkTestMail).IsChecked() contact = self.GetParent().GetContents() message = self.GetContents() server = smtplib.SMTP('smtp.1and1.com') #server = smtplib.SMTP('smtp.orange.fr') AUTH_REQUIRED = True try: # XXX move to "with" statement? if AUTH_REQUIRED: try: server.login("m35582809-2", "spdney11") except: print >> sys.stderr, "Could not authenticate to server" return for cntID in self.contactIDs: curs.execute(""" SELECT cntID, cntName, cntemail, cntErrFlag, spcname, mlgtime FROM sponsorship_contact JOIN sponsorship_organization ON cntorgid = orgid LEFT OUTER JOIN sponsorship_spclass ON orgspcid = spcid LEFT OUTER JOIN sponsorship_mailing ON cntid=mlgcntid AND mlgmsgid=%s WHERE cntID=%s""", (self.ID, cntID)) row = curs.fetchone() if row: cntid, name, cntemail, errflag, spcname, mlgtime = row if errflag == 0 and (not self.FindWindowById(chkNewOnly).IsChecked() or mlgtime is None): subdict = { 'subject': message['msgsubject'], 'recipient': cntemail, 'message': message['msgbody'], 'name': name.split()[0]} if testing: print "DEBUG: sending to self, not", cntemail cntemail = subdict['recipient'] = 'steve@holdenweb.com' msg = msgtmpl % subdict print time.ctime(), "Attempting mail to", cntemail try: result = server.sendmail( 'pycon-sponsors@python.org', [cntemail, 'pycon-sponsors@python.org'], msg) if result.keys(): # Unlikely to be exercised with single recipients for r in result.keys(): print "\tError", result[r][0], ":", result[r][1] curs.execute("UPDATE sponsorship_contact SET cntErrFlag=1 WHERE cntID=%s", (cntid, )) else: if not testing: curs.execute("INSERT INTO sponsorship_mailing (mlgMsgID, mlgCntID) VALUES(%s, %s)", (self.ID, cntid)) conn.commit() except smtplib.SMTPException: print "\tError sending mail to \"%s\" - flagged for no more mail" % cntemail curs.execute("UPDATE sponsorship_contact SET cntErrFlag=1 WHERE cntID=%s", (cntid, )) conn.commit() else: if errflag: print "Not sent: %s (%s) is flagged for no mail" % (name, cntemail) if mlgtime: print "Not sent: %s received message %d on %s" % \ (name, self.ID, mlgtime.strftime("%d-%b-%Y %H:%M:%S")) else: print "Contact %d not found - please add to sponsorship_contact table" % cntID finally: server.quit() def OnSaveAs(self, event): "Save message under a newly-assigned name" dialog = MessageName(self, -1, "Enter New Message Name") if dialog.ShowModal() == wx.ID_OK: # XXX make provision for one-off temporary names row = self.GetContents() row['msgname'] = name = dialog.FindWindowById(txtnewmsgname).GetValue() self.ID = self.InsertRow(row, "msgid", "message") self.SetContents(row) self.row = row self.SetSelector(name) self.newrecord = False dialog.Destroy() # XXX Should have OnSave that calls OnSaveAs for new records ... def UpdateRow(self, row, keyfield, table): """Updates an existing row in the database if it passes uniqueness checks.""" if self.CheckUnique(row, self.ID): msgname = row['msgname'] self.curs.execute("SELECT max(msgname) FROM sponsorship_message WHERE msgname LIKE '%s#%%'" % msgname) maxnum = self.curs.fetchone()[0] if maxnum is None: maxnum = 0 else: maxnum = int(maxnum[-4:]) curs.execute("UPDATE sponsorship_message SET msgname=%s WHERE msgname=%s", ("%s#%04d" % (msgname, maxnum+1), msgname)) result = self.InsertRow(row, keyfield, table) self.row = row self.SetSelector() return result class OrganizationEdit(wx.Dialog, hwDataDialog): FIELDS = ( (txtorgname, 'orgname', tpTXT), (txtorgaddr1, 'orgaddr1', tpNTX), (txtorgaddr2, 'orgaddr2', tpNTX), (txtorgaddr3, 'orgaddr3', tpNTX), (txtorgaddr4, 'orgaddr4', tpNTX), (txtorgaddr5, 'orgaddr5', tpNTX), (stxorginvno, 'orginvno', tpSIN), (stxorginvdt, 'orginvdt', tpSDN), (stxorgspridate, 'orgspridate', tpSTS), (chcorgcntid, 'orgcntid', tpICH), (chcorgbillcntid, 'orgbillcntid', tpICH), (chcorgspcid, 'orgspcid', tpICH), (txtorgporder, 'orgporder', tpNTX) ) table = "sponsorship_organization" keyfield = "orgID" selfield = None uniquefields = ("orgname", ) selector = None def __init__(self, parent, id, title, orgid, cntid, pos = wx.DefaultPosition, size = wx.DefaultSize, style = wx.DEFAULT_DIALOG_STYLE ): wx.Dialog.__init__(self, parent, id, title, pos, size, style) self.curs = curs self.conn = conn # WDR: dialog function OrganizationDialog for OrganizationEdit OrganizationDialog( self, True ) self.fkByCol("SELECT cntname, cntid FROM sponsorship_contact WHERE cntorgid=%d OR cntid=%s" % (orgid or 0, cntid or 0), self.FindWindowById(chcorgcntid), NoneOption=("*None*", None)) self.fkByCol("SELECT cntname, cntid FROM sponsorship_contact WHERE cntorgid=%d OR cntid=%s" % (orgid or 0, cntid or 0), self.FindWindowById(chcorgbillcntid), NoneOption=("*None*", None)) self.fkByCol("SELECT spcname, spcid FROM sponsorship_spclass", self.FindWindowById(chcorgspcid), NoneOption=("*None*", None)) # WDR: handler declarations for OrganizationEdit wx.EVT_BUTTON(self, btnInvNo, self.SetInvoiceNo) # WDR: methods for OrganizationEdit # WDR: handler implementations for OrganizationEdit def SetInvoiceNo(self, event): dialog = SetInvoiceNo(self, -1, "Enter Invoice Number for %s" % self.row['orgname']) if dialog.ShowModal() == wx.ID_OK: self.FindWindowById(stxorginvno).SetLabel(dialog.FindWindowById(txtInvoiceNo).GetValue()) dialog.Destroy() class MailingsShow(wx.Dialog): def __init__(self, parent, id, title, pos = wx.DefaultPosition, size = wx.DefaultSize, style = wx.DEFAULT_DIALOG_STYLE ): wx.Dialog.__init__(self, parent, id, title, pos, size, style) # WDR: dialog function MailingsDialog for MailingsShow MailingsDialog( self, True ) # Create columns and populate with database data cntID = parent.ID curs.execute(""" SELECT mlgid, mlgtime, msgname, msgsubject, msgbody FROM sponsorship_mailing JOIN sponsorship_message ON sponsorship_mailing.mlgmsgid=sponsorship_message.msgid WHERE sponsorship_mailing.mlgcntid=%s ORDER BY mlgtime""", (cntID, )) self.lst = lst = self.FindWindowById(lstMailings) lst.InsertColumn(0, "Date") lst.SetColumnWidth(0, 75) lst.InsertColumn(1, "Message") lst.SetColumnWidth(1, 175) self.data = curs.fetchall() for mlgid, mlgtime, msgname, msgsubject, msgbody in self.data: # add item and set column data index = lst.InsertStringItem(sys.maxint, mlgtime.strftime("%d %b %y")) lst.SetStringItem(index, 1, msgname) # WDR: handler declarations for MailingsShow wx.EVT_LIST_ITEM_SELECTED(self, lstMailings, self.OnMailingSelected) # WDR: methods for MailingsShow # WDR: handler implementations for MailingsShow def OnMailingSelected(self, event): "Show the text of a selected mailing" currentItem = event.m_itemIndex mlgid, mlgtime, msgname, msgsubject, msgbody = self.data[currentItem] txt = self.FindWindowById(txtMessageDisplay) txt.SetValue("%s\n\n%s" % (msgsubject, msgbody)) class ContactEdit(wx.Dialog, hwDataDialog): FIELDS = ( (txtCntName, "cntname", tpTXT), (txtCntEmail, "cntemail", tpTXT), (txtCntTelephone, "cnttelephone", tpNTX), (cmbOrgID, "cntorgid", tpICH), (txtCntNotes, "cntnotes", tpNTX), (chkNoMail, "cnterrflag", tpCHK) ) table = "sponsorship_contact" keyfield = "cntID" selfield = "cntname" uniquefields = ("cntname", ) selector = lbxSelectByName def __init__(self, parent, id, title, pos = wx.DefaultPosition, size = wx.DefaultSize, style = wx.DEFAULT_DIALOG_STYLE ): wx.Dialog.__init__(self, parent, id, title, pos, size, style) self.curs = curs self.conn = conn # WDR: dialog function ContactDialog for Contact ContactDialog( self, True ) self.fkByCol("SELECT orgName, orgID FROM sponsorship_organization ORDER BY orgName", self.FindWindowById(cmbOrgID)) choices = ( ("All Contacts", "1=1"), ("All Sponsors", "orgspcid IS NOT NULL"), ("Non-Sponsors", "orgspcid IS NULL"), ("Platinum Sponsors", "spcname='Platinum'"), ("Gold Sponsors", "spcname='Gold'"), ("Silver Sponsors", "spcname='Silver'"), ("Bag Insert Sponsors", "spcname='Insert'"), ("Media Sponsors", "spcname='Media'") ) pulldown = self.FindWindowById(chcContactType) pulldown.Clear() pulldown.SetSelection(0) self.choiceClauses = [] self.choiceNames = [] for choice, clause in choices: pulldown.Append(choice) self.choiceClauses.append(clause) self.choiceNames.append(choice) pulldown.SetSelection(0) self.OnContactTypeSelect(None) # WDR: handler declarations for Contact wx.EVT_RADIOBOX(self, rdbContactType, self.OnContactTypeSelect) wx.EVT_CHOICE(self, chcContactType, self.OnContactTypeSelect) wx.EVT_BUTTON(self, btnMailAll, self.OnMailAll) wx.EVT_BUTTON(self, btnMailSelected, self.OnMailSelected) wx.EVT_BUTTON(self, bntOrgCreate, self.OnNewOrg) wx.EVT_BUTTON(self, bntOrgDetails, self.OnOrgData) wx.EVT_BUTTON(self, btnMailings, self.OnMailings) wx.EVT_LISTBOX(self, lbxSelectByName, self.OnSelect) wx.EVT_BUTTON(self, btnDelete, self.OnDelete) wx.EVT_BUTTON(self, btnNew, self.OnNew) wx.EVT_BUTTON(self, wx.ID_OK, self.OnClose) wx.EVT_BUTTON(self, btnSave, self.OnSave) wx.EVT_CLOSE(self, self.OnClose) def OnClose(self, event): self.Destroy() # WDR: methods for Contact def SelectorKeys(self): choice = self.FindWindowById(chcContactType).GetSelection() clause1 = self.choiceClauses[choice] radio = self.FindWindowById(rdbContactType) clause2 = ("1=1", "orgcntid=cntid", "orgbillcntid=cntid")[radio.GetSelection()] sql = """ SELECT cntname, cntID FROM sponsorship_contact LEFT OUTER JOIN sponsorship_organization ON cntorgid=orgid LEFT OUTER JOIN sponsorship_spclass ON orgspcid=spcid WHERE %s AND %s ORDER BY cntname""" % (clause1, clause2) curs.execute(sql) nv = curs.fetchall() self.fkByColData(self.FindWindowById(lbxSelectByName), nv) vals = [n[1] for n in nv] names = [n[0] for n in nv] return names, vals def DeleteHook(self, id): self.curs.execute("DELETE FROM sponsorship_mailing WHERE mlgcntid=%s", (id, )) self.curs.execute("UPDATE sponsorship_organization SET orgcntid=NULL where orgcntid=%s", (id, )) self.curs.execute("UPDATE sponsorship_organization SET orgbillcntid=NULL where orgbillcntid=%s", (id, )) self.conn.commit() # WDR: handler implementations for Contact def OnContactTypeSelect(self, event): # XXX if contact data exists and has changed it should be written back here ... self.ID = self.SelectorLoad() if self.ID: self.row = dict(zip( [f[1] for f in self.FIELDS], self.readRow(self.ID, self.keyfield, self.table))) else: self.row = self.EmptyRow() # Select something self.SetContents(self.row) self.newrecord = False # XXX examine use of newrecord - bogus? def OnMailAll(self, event): dialog = SendMail(self, -1, "Send Mail to %s" % self.choiceNames[self.FindWindowById(chcContactType).GetSelection()], self.SelectorKeys()[1]) dialog.ShowModal() dialog.Destroy() def OnMailSelected(self, event): dialog = SendMail(self, -1, "Send Mail to %s" % self.FindWindowById(txtCntName).GetValue(), [self.ID]) dialog.ShowModal() dialog.Destroy() def OnNewOrg(self, event): dialog = OrganizationEdit(self, -1, "Add New Organization Details", None, self.ID) dialog.ID = None row = dialog.EmptyRow() #dialog.row = dict(zip((f[1] for f in dialog.FIELDS), row)) assert isinstance(dialog, hwDataDialog) dialog.SetContents(row) result = dialog.ShowModal() if result == wx.ID_OK: row = dialog.GetContents() orgID = dialog.InsertRowIfAsk(row, "orgid", "sponsorship_organization") if orgID: pulldown = self.FindWindowById(cmbOrgID) self.fkByCol("SELECT orgName, orgID FROM sponsorship_organization ORDER BY orgName", pulldown) pulldown.SetStringSelection(pulldown.hwRevLookup[orgID]) dialog.Destroy() def OnMailings(self, event): dialog = MailingsShow(self, -1, "Mailings for %s" % self.FindWindowById(txtCntName).GetValue()) dialog.ShowModal() dialog.Destroy() def OnOrgData(self, event): # Use org from pulldown, as may have changed during editing pulldown = self.FindWindowById(cmbOrgID) orgID = pulldown.hwFwdLookup[pulldown.GetStringSelection()] # XXX If current contact row has changed organization then # the contact should be (but isn't) listed as a possible # contact for the organization dialog = OrganizationEdit(self, -1, "Edit Organization Details", orgID, self.ID) dialog.ID = orgID row = dialog.readRow(orgID, "orgid", "sponsorship_organization") dialog.row = dict(zip((f[1] for f in dialog.FIELDS), row)) dialog.SetContents(dialog.row) result = dialog.ShowModal() if result == wx.ID_OK: row = dialog.GetContents() if row != dialog.row: if row['orgspcid'] is not None and dialog.row['orgspcid'] is None: # They just became a sponsor! row['orgspridate'] = datetime.datetime.now() dialog.UpdateRow(row, "orgid", "sponsorship_organization") dialog.Destroy() if __name__ == '__main__': import sys app = myApp(False) app.MainLoop()