#!/usr/bin/env python
"""[options] <tablename>
       downloads table for campaign monitor"""
import MySQLdb, sys
from optparse import OptionParser

#note that there will usually be a "to_name" as well
sql="""SELECT DISTINCT p.email, p.name, p.greeting AS first, 
CONCAT(v.Name, ', ', IFNULL(v.City,v.Country)) AS to_firm 
FROM pevcf_people AS p JOIN pevcf_raw AS v ON v.ID1 = p.ID1
LEFT JOIN mast_firm f ON f.website = v.Website
WHERE f.website IS NULL AND v.Country in ('Canada','United States')"""

parser = OptionParser('usage: %prog ' + __doc__)
parser.add_option("-n","--numlines",default=0,dest="num",type="int",help="number of lines to print (default all)")
parser.add_option("-r","--raw",action="store_true",dest="raw",help="raw print of data")
parser.add_option("-q","--quiet",action="store_true",dest="quiet",help="no title line")
(options,args) = parser.parse_args()
#if len(args) < 1:
#    parser.error("incorrect number of parameters")
#dbtable=args[0]
limit = ""
if options.num != 0:
    limit = " LIMIT " + str(options.num)
hostname='stillwaterfs.cfaw.info'
dbuser='cfaw'
dbpass='xxxx'
dbname='cfaw'
db=MySQLdb.connect(host=hostname, user=dbuser, passwd=dbpass, db=dbname)
cursor=db.cursor()

cursor.execute(sql + limit)
names = [f[0] for f in cursor.description]
#for row in cursor.fetchall():
#    for pair in zip(names, row):
#        print '%s: %s' % pair
if not options.quiet:
    print '\t'.join(names)
printformat = '\t'.join(['%s']*len(names))  #contstruct a format string with enough %s's

for row in cursor.fetchall():
    if options.raw:
        print row
    else:
         print printformat % row
cursor.close()
db.close()