#!/usr/bin/python
#
# invmail.py:    Invoice Sponsors who have not already been billed.
#
from time import sleep, ctime
import smtplib
import sys
from db import conn, curs
from time import strftime
import email.Message
import email.Encoders
from email.MIMEMultipart import MIMEMultipart

#
# Set these email addresses and other parameters
#
ORGNAME = 'Steve Holden'
SPONORG = 'steve@holdenweb.com'
CHAIRMAN = 'amk@amk.ca'
SERVER = 'smtp.1and1.com'
USERNAME = "XXXXXXX"
PASSWORD = "XXXXXXX"


today = strftime("%d %b %Y")

def usage():
    print """Usage: 
%s [-a]
         Sends an invoice to each sponsor who has not already received it
         -a:    Send mail without SMTP server authentication
""" % sys.argv[0]
    sys.exit(-1)

testing = False # Set True to enable debug functionality
if testing:
    print "++++++ TESTING ++++++"

anonymous = False
while len(sys.argv) > 1 and sys.argv[1].startswith("-"):
    anonymous = sys.argv[1].startswith("-a")
    if anonymous:
        del sys.argv[1]
    else:
        usage()

msgname = "07Invoice1"
curs.execute("SELECT msgID, msgSubject, msgBody FROM sponsorship_message WHERE msgName=%s", (msgname, ))
row = curs.fetchone()
if row:
    msgid, subject , body = row
else:
    print "No message named '%s'" % msgname
    sys.exit(2)

print ctime(), "Sending message %s" % msgname
msgtmpl = """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
"""
invtmpl = open("data/invoice-template.rtf", 'rb').read()

# XXX: Need to parameterise for personal/server details
if anonymous:
    server = smtplib.SMTP(SERVER)
else:
    server = smtplib.SMTP(SERVER)
    try:
        server.login(USERNAME, PASSWORD)
    except:
        print >> sys.stderr, "Could not authenticate to server"
        sys.exit(-1)
FIELDS = "cntname, cntemail, cnterrflag, orgid, orgname, orgaddr1, orgaddr2, orgaddr3, orgaddr4, orgaddr5, orginvno, spcname, spccost, orgporder".split(", ")
sql = """SELECT %s FROM sponsorship_contact
            JOIN sponsorship_organization on orgbillcntid=cntid
            JOIN sponsorship_spclass ON orgspcid=spcid
            WHERE spccost <> 0
            AND orginvno IS NOT NULL
            AND orginvdt IS NULL""" % ", ".join(FIELDS)
curs.execute(sql, (msgname, ))
rows = curs.fetchall()
addresses = [row[0] for row in rows]

print "About to mail"
for addr in addresses:
    print "    ", addr
if not raw_input("Please confirm [y/N]: ").lower().startswith("y"):
    print "\n*** Run aborted ***\n"
    usage()

for row in rows:
    subdict = dict(zip(FIELDS, row))
    if not subdict['cnterrflag']:
        subdict['subject'] = subject
        subdict['recipient'] =subdict['cntemail'].rstrip(),
        subdict['name'] = subdict['cntname'].split()[0]
        subdict['level'] = subdict['spcname']
        subdict['message'] = body % subdict
        subdict['orginvdt'] = today
        if testing:
            print "DEBUG: sending to test address, not %(cntemail)s" % subdict
            subdict['recipient'] = 'steve@holdenweb.com'
            #subdict['recipient'] = 'barry@python.org'
            #subdict['recipient'] = 'testmail@holdenweb.com'
            #subdict['recipient'] = 'twl@osafoundation.org'
        msg = MIMEMultipart()
        msg.add_header("From", "%s <pycon-sponsors@python.org>" % ORGNAME)
        msg.add_header("Subject", "[PyCon: Sponsorship] %(subject)s" % subdict)
        msg.add_header("To", "%(recipient)s" % subdict)
        msg.add_header("Reply-To",  "pycon-sponsors@python.org")
        msg.add_header("Errors-To", "pycon-sponsors@python.org")
        msg["Mime-version"]="1.0"
        msg.preamble = """\
This is a MIME message. If you see this text then you may not
be able to decode the attached invoice. In this case please reply
with an explanation and we will send an invoice some other way.
"""
        mbody = email.Message.Message()
        mbody["Content-type"] = 'text/plain'
        mbody["Content-transfer-encoding"] = "7bit"
        mbody.set_payload(msgtmpl % subdict)
        msg.attach(mbody)

        inv = invtmpl % subdict
        invfname = "data/psf/invoices/%(orginvno)d.rtf" % subdict
        open(invfname, 'wb').write(inv)
        attachment = email.Message.Message()
        attachment.set_payload(inv)
        attachment.set_type("application/rtf")
        attachment.set_param("name", "inv%(orginvno)d.rtf" % subdict)
        attachment.add_header("Content-Disposition", "inline", filename="inv%(orginvno)d.rtf" % subdict)
        email.Encoders.encode_base64(attachment)
        # XXXfor some reason attachments do not always appear correctly
        msg.attach(attachment)
        print ctime(), "Attempting mail to %(recipient)s" % subdict
        try:
            result = server.sendmail(SPONORG, [subdict['recipient'], SPONORG, CHAIRMAN], str(msg).replace("\n", "\r\n"))
            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("UPDATE sponsorship_organization SET orginvdt=%s WHERE orgid=%s", (subdict['orginvdt'], subdict['orgid']))
            conn.commit()

        except smtplib.SMTPException:
            print "\tError sending mail to \"%s\" - flagged for no more mail" % subdict['name']
            curs.execute("UPDATE sponsorship_contact SET cntErrFlag=1 WHERE cntID=%s", (cntid, ))
            conn.commit()
    else:
        print "%(cntname)s (%(cntemail)s) is flagged for no mail" % subdict
server.quit()