def getreport(report, district, monthend, tmyearpiece, asof=""):
""" Returns (data, reportdate, reportmonthend) tuple from report """
reportdate = None
reportmonthend = None
url = makeurl(report,
district,
monthend=monthend,
tmyearpiece=tmyearpiece,
asof=asof)
data = getresponse(url)
if data:
while not data[-1].strip():
data = data[:-1]
dateline = data[-1].replace(',', '')
reportdate = datetime.strptime(cleandate(
dateline.split()[-1]), '%Y-%m-%d').date(
) # "Month of Jun, as of 07/02/2015" => '2015-07-02'
# Figure out the last day of the month for which the report applies
reportmonth = datetime.strptime(
dateline.split()[2], "%b").month # Extract the month of the report
if reportmonth == reportdate.month:
reportmonthend = getmonthend(reportmonth, reportdate.year)
else:
reportmonthend = getmonthend(
reportmonth,
reportdate.year if reportmonth != 12 else reportdate.year - 1)
return (data, reportdate, reportmonthend)
def getasof(infile):
""" Gets the "as of" information from a Toastmasters' report.
Returns a tuple: (monthstart, date) (both as characters)
If there is no "as of" information, returns False.
Seeks the file back to the current position.
"""
retval = False
filepos = infile.tell()
for line in infile:
if not line:
break
if not line.startswith("Month of"):
continue
(mpart, dpart) = line.split(',')
month = 1+ ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'].index(mpart.split()[-1].lower()[0:3])
date = cleandate(dpart.split()[-1])
asofyear = int(date[0:4])
asofmon = int(date[5:7])
if month == 12 and asofmon == 1:
asofyear = asofyear - 1
monthstart = '%04d-%02d-%02d' % (asofyear, month, 1)
retval = (monthstart, date)
break
infile.seek(filepos)
return retval
def dolatest(district, altdistrict, finals, tmyearpiece):
for monthend in finals:
for (urlpart, filepart) in (('clubperformance', 'clubperf'),
('divisionperformance', 'areaperf'),
('districtperformance', 'distperf')):
url = makeurl(urlpart, district, monthend=monthend, tmyearpiece=tmyearpiece)
data = getresponse(url)
if not data and altdistrict:
url = makeurl(urlpart, altdistrict, monthend=monthend, tmyearpiece=tmyearpiece)
data = getresponse(url)
if data:
thedate = datetime.strptime(cleandate(data[-1].split()[-1]), '%Y-%m-%d').date() # "Month of Jun, as of 07/02/2015" => '2015-07-02'
with open(makefilename(filepart, thedate), 'w') as f:
f.write(''.join(data).replace('\r',''))
print 'Fetched %s for %s (month: %s, year: %s)' % (filepart, thedate, monthend, tmyearpiece)
def getasof(infile):
""" Gets the "as of" information from a Toastmasters' report.
Returns a tuple: (monthstart, date) (both as characters)
If there is no "as of" information, returns False.
Seeks the file back to the current position.
"""
retval = False
filepos = infile.tell()
for line in infile:
if not line:
break
if not line.startswith("Month of"):
continue
(mpart, dpart) = line.split(",")
month = 1 + [
"jan",
"feb",
"mar",
"apr",
"may",
"jun",
"jul",
"aug",
"sep",
"oct",
"nov",
"dec",
].index(mpart.split()[-1].lower()[0:3])
date = cleandate(dpart.split()[-1])
asofyear = int(date[0:4])
asofmon = int(date[5:7])
if month == 12 and asofmon == 1:
asofyear = asofyear - 1
monthstart = "%04d-%02d-%02d" % (asofyear, month, 1)
retval = (monthstart, date)
break
infile.seek(filepos)
return retval
def getasof(infile):
""" Gets the "as of" information from a Toastmasters' report.
Returns a tuple: (monthstart, date) (both as characters)
If there is no "as of" information, returns False.
Seeks the file back to the current position.
"""
retval = False
filepos = infile.tell()
for line in infile:
if not line:
break
if not line.startswith("Month of"):
continue
(mpart, dpart) = line.split(",")
month = 1 + [
"jan",
"feb",
"mar",
"apr",
"may",
"jun",
"jul",
"aug",
"sep",
"oct",
"nov",
"dec",
].index(mpart.split()[-1].lower()[0:3])
date = cleandate(dpart.split()[-1])
asofyear = int(date[0:4])
asofmon = int(date[5:7])
if month == 12 and asofmon == 1:
asofyear = asofyear - 1
monthstart = "%04d-%02d-%02d" % (asofyear, month, 1)
retval = (monthstart, date)
break
infile.seek(filepos)
return retval
def getreport(report, district, monthend, tmyearpiece, asof=""):
""" Returns (data, reportdate, reportmonthend) tuple from report """
reportdate = None
reportmonthend = None
url = makeurl(report, district, monthend=monthend, tmyearpiece=tmyearpiece, asof=asof)
data = getresponse(url)
if data:
while not data[-1].strip():
data = data[:-1]
dateline = data[-1].replace(',','')
reportdate = datetime.strptime(cleandate(dateline.split()[-1]), '%Y-%m-%d').date() # "Month of Jun, as of 07/02/2015" => '2015-07-02'
# Figure out the last day of the month for which the report applies
reportmonth = datetime.strptime(dateline.split()[2], "%b").month # Extract the month of the report
if reportmonth == reportdate.month:
reportmonthend = getmonthend(reportmonth, reportdate.year)
else:
reportmonthend = getmonthend(reportmonth, reportdate.year if reportmonth != 12 else reportdate.year-1)
return (data, reportdate, reportmonthend)
curs = globals.curs
conn = globals.conn
# See if we have the data needed to run and build the base part of the query
if parms.basedate.upper().startswith('M'):
basemonth = int(parms.basedate[1:])
if not isMonthFinal(basemonth, curs):
sys.exit(1)
basepart = 'monthstart = "%s" AND entrytype = "M"' % getMonthStart(basemonth, curs)
friendlybase = 'New Members on %s' % neaten(getMonthEnd(basemonth))
msgdate = datetime.date(globals.today.year, basemonth+1, 1)
if basemonth == 12:
msgdate = datetime.date(globals.today.year, 1, 1)
else:
basedate = cleandate(parms.basedate)
if not haveDataFor(basedate, curs):
sys.exit(1)
basepart = 'asof = "%s"' % basedate
msgdate = datetime.datetime.strptime(basedate, '%Y-%m-%d')
friendlybase = 'New Members on %s' % neaten(msgdate)
msgbase = dateAsWords(msgdate)
# Figure out the end date and build that part of the query
yesterday = cleandate('yesterday')
if parms.finaldate.upper().startswith('M'):
finalmonth = int(parms.finaldate[1:])
msgdate = getMonthEnd(finalmonth)
if isMonthFinal(finalmonth, curs):
finalpart = 'monthstart = "%s" AND entrytype = "M"' % getMonthStart(finalmonth, curs)
final = True
eligiblethroughcol = labels.index('eligiblethrough') + 1
# Get the final date in the database - we don't ask for data later than we have!
curs.execute("SELECT MAX(asof) FROM clubperf")
maxasof = curs.fetchone()[0]
for rownum, row in enumerate(values[1:], start=2):
club = Clubinfo(labels, row)
clubnumber = club.clubnumber.strip()
if not clubnumber:
continue
ohdate = datetime.strptime(cleandate(club.openhousedate), "%Y-%m-%d").date()
startdate = ohdate - timedelta(1)
enddate = ohdate
# Need to compute the same day in the next month - if there is no same day, go to the 1st of the following
(m, d, y) = (ohdate.month+1, ohdate.day, ohdate.year)
if (m == 13):
m = 1
y += 1
try:
enddate = ohdate.replace(y, m, d)
except ValueError:
enddate = ohdate.replace(y, m+1, 1) # We need not worry about year overflow because November has fewer days than December
# But in case something was specified, use it.
if club.eligiblethrough.strip():
def doDailyClubs(infile, conn, cdate, firsttime=False):
""" infile is a file-like object """
global changecount
from datetime import datetime, timedelta
curs = conn.cursor()
reader = csv.reader(infile)
hline = reader.next()
headers = cleanheaders(hline)
try:
clubcol = headers.index('clubnumber')
except ValueError:
if not hline[0].startswith('{"Message"'):
print "'clubnumber' not in '%s'" % hline
return
try:
prospectiveclubcol = headers.index('prospectiveclub')
except ValueError:
prospectiveclubcol = False
# Find out what fields we have in the database itself
dbfields = []
curs.execute("describe clubs")
for l in curs.fetchall():
dbfields.append(l[0])
inform("clubs for", cdate, suppress=1)
dbheaders = [p for p in headers]
# Convert between Toastmasters' names for address and location and ours; they've changed it a few times. *sigh*
if 'address1' in dbheaders:
addrcol1 = dbheaders.index('address1')
else:
addrcol1 = dbheaders.index('location')
if 'address2' in dbheaders:
addrcol2 = dbheaders.index('address2')
else:
addrcol2 = dbheaders.index('address')
dbheaders[addrcol1] = 'place'
dbheaders[addrcol2] = 'address'
expectedheaderscount = len(dbheaders)
dbheaders.append('firstdate')
dbheaders.append('lastdate') # For now...
areacol = dbheaders.index('area')
divisioncol = dbheaders.index('division')
statecol = dbheaders.index('state')
# Now, suppress anything in the file that's not in the database:
suppress = []
oldheaders = dbheaders
dbheaders = []
for i in xrange(len(oldheaders)):
if oldheaders[i] in dbfields:
dbheaders.append(oldheaders[i])
else:
suppress.append(i)
suppress.reverse() # We remove these columns from the input
Club.setfieldnames(dbheaders)
# We need to get clubs for the most recent update so we know whether to update an entry
# or start a new one.
yesterday = datetime.strftime(datetime.strptime(cdate, '%Y-%m-%d') - timedelta(1),'%Y-%m-%d')
clubhist = Club.getClubsOn(curs, date=yesterday)
for row in reader:
if len(row) < expectedheaderscount:
break # we're finished
if prospectiveclubcol is not None and row[prospectiveclubcol]:
continue # Ignore prospective clubs
for i in suppress:
del row[i]
# Convert to unicode. Toastmasters usually uses UTF-8 but occasionally uses Windows CP1252 on the wire.
try:
row = [unicode(t.strip(), "utf8") for t in row]
except UnicodeDecodeError:
row = [unicode(t.strip(), "CP1252") for t in row]
if len(row) > expectedheaderscount:
# Special case...Millbrae somehow snuck two club websites in!
row[16] = row[16] + ',' + row[17]
del row[17]
#print row[addrcol1]
#print row[addrcol2]
# Now, clean up the address:
# Address line 1 is "place" information and can be multiple lines.
# Address line 2 is the real address and should be treated as one line, with spaces normalized.
place = '\n'.join([x.strip() for x in row[addrcol1].split(' ')])
row[addrcol1] = place
address = normalize(row[addrcol2])
row[addrcol2] = address
# Toastmasters is currently reversing the "Area" and "Division" items. "Area" should be a
# number; if not, swap the two.
try:
thearea = row[areacol]
thedivision = row[divisioncol]
areanum = int(row[areacol])
except ValueError:
row[areacol] = thedivision
row[divisioncol] = thearea
# Collapse state names into their abbreviations
if row[statecol] in statelist:
row[statecol] = statelist[row[statecol]]
# Get the right number of items into the row by setting today as the
# tentative first and last date
row.append(cdate)
row.append(cdate)
# And create the object
club = Club(row)
# Now, clean up things coming from Toastmasters
if club.clubstatus.startswith('Open') or club.clubstatus.startswith('None'):
club.clubstatus = 'Open'
else:
club.clubstatus = 'Restricted'
# Clean up the club and district numbers and the area
club.clubnumber = cleanitem(club.clubnumber)
club.district = cleanitem(club.district)
club.area = cleanitem(club.area)
# If a club is partially unassigned, mark it as completely unassigned.
if (club.area == '0A') or (club.area == '0D') or (club.division == '0D') or (club.division == '0A'):
club.area = '0A'
club.division = '0D'
# Clean up the charter date
club.charterdate = cleandate(club.charterdate)
# Clean up advanced status
club.advanced = '1' if (club.advanced != '') else '0'
# Clean up online status
club.allowsonlineattendance = '1' if (club.allowsonlineattendance != '') else '0'
# Now, take care of missing latitude/longitude
if ('latitude') in dbheaders:
try:
club.latitude = float(club.latitude)
except ValueError:
club.latitude = 0.0
else:
club.latitude = 0.0
if ('longitude') in dbheaders:
try:
club.longitude = float(club.longitude)
except ValueError:
club.longitude = 0.0
else:
club.longitude = 0.0
# Sometimes, Toastmasters gets the latitude and longitude backwards
# If that turns out to create an impossible location (which it will in California),
# let's swap them.
if abs(club.latitude) > 90.0:
(club.latitude, club.longitude) = (club.longitude, club.latitude)
# And put it into the database if need be
if club.clubnumber in clubhist:
changes = different(club, clubhist[club.clubnumber], dbheaders[:-2])
else:
changes = []
if club.clubnumber not in clubhist and not firsttime:
# This is a new (or reinstated) club; note it in the changes database.
curs.execute('INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, "New Club", "", "")', (club.clubnumber, cdate))
if club.clubnumber not in clubhist or changes:
club.firstdate = club.lastdate
# Encode newlines in the place as double-semicolons for the database
club.place = club.place.replace('\n',';;')
values = [club.__dict__[x] for x in dbheaders]
# And then put the place back into normal form
club.place = club.place.replace(';;','\n')
thestr = 'INSERT IGNORE INTO clubs (' + ','.join(dbheaders) + ') VALUES (' + ','.join(['%s' for each in values]) + ');'
try:
changecount += curs.execute(thestr, values)
except Exception, e:
print e
# Capture changes
for (item, old, new) in changes:
if (item == 'place'):
# Clean up the place (old and new) for the database
old = old.replace('\n', ';;')
new = new.replace('\n', ';;')
try:
curs.execute('INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, %s, %s, %s)', (club.clubnumber, cdate, item, old, new))
except Exception, e:
print e
clubhist[club.clubnumber] = club
if different(club, clubhist[club.clubnumber], dbheaders[:-2]):
print 'it\'s different after being set.'
sys.exit(3)
print("Getting the latest performance info")
dolatest(district)
# And get and write current club data unless told not to
# WHQ doesn't supply date information, but it's always as of yesterday
if not parms.skipclubs:
url = "https://www.toastmasters.org/api/sitecore/FindAClub/DownloadCsv?district=%s&advanced=1&latitude=0&longitude=0" % district
clubdata = getresponse(url)
if clubdata:
with open(makefilename('clubs', date.today() - timedelta(1)), 'w') as f:
f.write('\n'.join(clubdata).replace('\r',''))
print("Fetched clubs")
else:
print('No data received from %s' % url)
else:
# We are getting historical data
startdate = datetime.strptime(cleandate(parms.startdate), '%Y-%m-%d').date()
if parms.enddate:
enddate = datetime.strptime(cleandate(parms.enddate), '%Y-%m-%d').date()
else:
enddate = startdate
d = startdate
while d <= enddate:
doreportsfor(district, d)
d += timedelta(1)
dolatest(district)
# And get and write current club data unless told not to
# WHQ doesn't supply date information, but it's always as of yesterday
if not parms.skipclubs:
url = "https://www.toastmasters.org/api/sitecore/FindAClub/DownloadCsv?district=%s&advanced=1&latitude=0&longitude=0" % district
clubdata = getresponse(url)
if clubdata:
with open(makefilename('clubs',
date.today() - timedelta(1)), 'w') as f:
f.write('\n'.join(clubdata).replace('\r', ''))
print("Fetched clubs")
else:
print('No data received from %s' % url)
else:
# We are getting historical data
startdate = datetime.strptime(cleandate(parms.startdate),
'%Y-%m-%d').date()
if parms.enddate:
enddate = datetime.strptime(cleandate(parms.enddate),
'%Y-%m-%d').date()
else:
enddate = startdate
d = startdate
while d <= enddate:
doreportsfor(district, d)
d += timedelta(1)
parms.parser.add_argument(
'--requiremembership',
action='store_true',
help='Specify to require that clubs meet membership goals to qualify.')
# Do global setup
myglobals.setup(parms)
curs = myglobals.curs
conn = myglobals.conn
# Your main program begins here.
# We want data from either the final date or the latest available, whichever is earlier
curs.execute("SELECT MAX(asof) FROM clubperf")
latest = stringify(curs.fetchone()[0])
parms.finaldate = cleandate(parms.finaldate)
targetdate = min(latest, parms.finaldate)
final = (targetdate == parms.finaldate)
status = "final" if final else "updated daily"
# Open the output file
outfile = open(parms.outfile, 'w')
winners = []
if parms.requiremembership:
# See if WHQ has posted any President's Distinguished Clubs; if so,
# use their information. If not, calculate on our own.
curs.execute(
eligiblethroughcol = labels.index('eligiblethrough') + 1
# Google has introduced rate limits for updates, so we need to batch them
updatelist = []
# Get the final date in the database - we don't ask for data later than we have!
curs.execute("SELECT MAX(asof) FROM clubperf")
maxasof = curs.fetchone()[0]
for rownum, row in enumerate(values[1:], start=2):
club = Clubinfo(labels, row)
clubnumber = club.clubnumber.strip()
if not clubnumber:
continue
ohdate = datetime.strptime(cleandate(club.openhousedate),
"%Y-%m-%d").date()
startdate = ohdate - timedelta(1)
enddate = ohdate
# Need to compute the same day in the next month - if there is no same day, go to the 1st of the following
(m, d, y) = (ohdate.month + 1, ohdate.day, ohdate.year)
if (m == 13):
m = 1
y += 1
try:
enddate = ohdate.replace(y, m, d)
except ValueError:
enddate = ohdate.replace(
y, m + 1, 1
) # We need not worry about year overflow because November has fewer days than December
parms.add_argument('--quiet', '-q', action='count')
parms.parser.add_argument("--mailYML", dest='mailyml', default="awardmail.yml")
parms.parser.add_argument("--mailserver", dest='mailserver')
parms.parser.add_argument("--mailpw", dest='mailpw')
parms.parser.add_argument("--mailport", dest='mailport')
parms.parser.add_argument("--sender", dest='sender')
parms.parser.add_argument("--reply-to", dest='replyto')
parms.parser.add_argument("--to", dest='to', nargs='+', default=[], action='append')
parms.parser.add_argument("--cc", dest='cc', nargs='+', default=[], action='append')
parms.parser.add_argument("--bcc", dest='bcc', nargs='+', default=[], action='append')
parms.add_argument('--fromdate', default='yesterday', dest='fromdate', help="First date for congrats.")
parms.add_argument('--todate', default='yesterday', dest='todate', help="Last date for congrats")
# Add other parameters here
parms.parse()
parms.fromdate = cleandate(parms.fromdate)
parms.todate = cleandate(parms.todate)
# If there are mail-related values not yet resolved, get them from the mailYML file.
ymlvalues = yaml.load(open(parms.mailyml, 'r'))
for name in ['mailserver', 'mailpw', 'mailport', 'from', 'replyto']:
if name not in parms.__dict__ or not parms.__dict__[name]:
parms.__dict__[name] = ymlvalues[name]
parms.sender = parms.__dict__['from']
# Connect to the database
conn = dbconn.dbconn(parms.dbhost, parms.dbuser, parms.dbpass, parms.dbname)
curs = conn.cursor()
def doDailyClubs(infile, conn, cdate, firsttime=False):
""" infile is a file-like object """
global changecount
from datetime import datetime, timedelta
curs = conn.cursor()
reader = csv.reader(infile)
hline = next(reader)
headers = cleanheaders(hline)
try:
clubcol = headers.index("clubnumber")
except ValueError:
if not hline[0].startswith('{"Message"'):
print("'clubnumber' not in '%s'" % hline)
return
try:
prospectiveclubcol = headers.index("prospectiveclub")
except ValueError:
prospectiveclubcol = False
# Find out what fields we have in the database itself
dbfields = []
curs.execute("describe clubs")
for l in curs.fetchall():
dbfields.append(l[0])
inform("clubs for", cdate, suppress=1)
dbheaders = [p for p in headers]
# Convert between Toastmasters' names for address and location and ours; they've changed it a few times. *sigh*
if "address1" in dbheaders:
addrcol1 = dbheaders.index("address1")
else:
addrcol1 = dbheaders.index("location")
if "address2" in dbheaders:
addrcol2 = dbheaders.index("address2")
else:
addrcol2 = dbheaders.index("address")
dbheaders[addrcol1] = "place"
dbheaders[addrcol2] = "address"
expectedheaderscount = len(dbheaders)
dbheaders.append("firstdate")
dbheaders.append("lastdate") # For now...
areacol = dbheaders.index("area")
divisioncol = dbheaders.index("division")
statecol = dbheaders.index("state")
# Now, suppress anything in the file that's not in the database:
suppress = []
oldheaders = dbheaders
dbheaders = []
for i in range(len(oldheaders)):
if oldheaders[i] in dbfields:
dbheaders.append(oldheaders[i])
else:
suppress.append(i)
suppress.reverse() # We remove these columns from the input
Club.setfieldnames(dbheaders)
# We need to get clubs for the most recent update so we know whether to update an entry
# or start a new one.
yesterday = datetime.strftime(
datetime.strptime(cdate, "%Y-%m-%d") - timedelta(1), "%Y-%m-%d"
)
clubhist = Club.getClubsOn(curs, date=yesterday)
for row in reader:
if len(row) < expectedheaderscount:
break # we're finished
if prospectiveclubcol is not None and row[prospectiveclubcol]:
continue # Ignore prospective clubs
for i in suppress:
del row[i]
if len(row) > expectedheaderscount:
# Special case...Millbrae somehow snuck two club websites in!
row[16] = row[16] + "," + row[17]
del row[17]
# print row[addrcol1]
# print row[addrcol2]
# Now, clean up the address:
# Address line 1 is "place" information and can be multiple lines.
# Address line 2 is the real address and should be treated as one line, with spaces normalized.
place = "\n".join([x.strip() for x in row[addrcol1].split(" ")])
row[addrcol1] = place
address = normalize(row[addrcol2])
row[addrcol2] = address
# Toastmasters is currently reversing the "Area" and "Division" items. "Area" should be a
# number; if not, swap the two.
try:
thearea = row[areacol]
thedivision = row[divisioncol]
areanum = int(row[areacol])
except ValueError:
row[areacol] = thedivision
row[divisioncol] = thearea
# Collapse state names into their abbreviations
if row[statecol] in statelist:
row[statecol] = statelist[row[statecol]]
# Get the right number of items into the row by setting today as the
# tentative first and last date
row.append(cdate)
row.append(cdate)
# And create the object
club = Club(row)
# Now, clean up things coming from Toastmasters
if club.clubstatus.startswith("Open") or club.clubstatus.startswith("None"):
club.clubstatus = "Open"
else:
club.clubstatus = "Restricted"
# Clean up the club and district numbers and the area
club.clubnumber = cleanitem(club.clubnumber)
club.district = cleanitem(club.district)
club.area = cleanitem(club.area)
# If a club is partially unassigned, mark it as completely unassigned.
if (
(club.area == "0A")
or (club.area == "0D")
or (club.division == "0D")
or (club.division == "0A")
):
club.area = "0A"
club.division = "0D"
# Clean up the charter date
club.charterdate = cleandate(club.charterdate)
# Clean up advanced status
club.advanced = "1" if (club.advanced != "") else "0"
# Clean up online status
club.allowsonlineattendance = (
"1" if (club.allowsonlineattendance != "") else "0"
)
# Add missing schemes to any URLs
club.fixURLSchemes()
# Now, take care of missing latitude/longitude
if ("latitude") in dbheaders:
try:
club.latitude = float(club.latitude)
except ValueError:
club.latitude = 0.0
else:
club.latitude = 0.0
if ("longitude") in dbheaders:
try:
club.longitude = float(club.longitude)
except ValueError:
club.longitude = 0.0
else:
club.longitude = 0.0
# Sometimes, Toastmasters gets the latitude and longitude backwards
# If that turns out to create an impossible location (which it will in California),
# let's swap them.
if abs(club.latitude) > 90.0:
(club.latitude, club.longitude) = (club.longitude, club.latitude)
# And put it into the database if need be
if club.clubnumber in clubhist:
changes = different(club, clubhist[club.clubnumber], dbheaders[:-2])
else:
changes = []
if club.clubnumber not in clubhist and not firsttime:
# This is a new (or reinstated) club; note it in the changes database.
curs.execute(
'INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, "New Club", "", "")',
(club.clubnumber, cdate),
)
if club.clubnumber not in clubhist or changes:
club.firstdate = club.lastdate
# Encode newlines in the place as double-semicolons for the database
club.place = club.place.replace("\n", ";;")
values = [club.__dict__[x] for x in dbheaders]
# And then put the place back into normal form
club.place = club.place.replace(";;", "\n")
thestr = (
"INSERT IGNORE INTO clubs ("
+ ",".join(dbheaders)
+ ") VALUES ("
+ ",".join(["%s" for each in values])
+ ");"
)
try:
changecount += curs.execute(thestr, values)
except Exception as e:
print(e)
# Capture changes
for (item, old, new) in changes:
if item == "place":
# Clean up the place (old and new) for the database
old = old.replace("\n", ";;")
new = new.replace("\n", ";;")
try:
curs.execute(
"INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, %s, %s, %s)",
(club.clubnumber, cdate, item, old, new),
)
except Exception as e:
print(e)
clubhist[club.clubnumber] = club
if different(club, clubhist[club.clubnumber], dbheaders[:-2]):
print("it's different after being set.")
sys.exit(3)
else:
# update the lastdate
changecount += curs.execute(
"UPDATE clubs SET lastdate = %s WHERE clubnumber = %s AND lastdate = %s;",
(cdate, club.clubnumber, clubhist[club.clubnumber].lastdate),
)
# If all the files were processed, today's work is done.
curs.execute(
'INSERT IGNORE INTO loaded (tablename, loadedfor) VALUES ("clubs", %s)',
(cdate,),
)
# Do global setup
myglobals.setup(parms)
conn = myglobals.conn
curs = myglobals.curs
# Let's see if we're supposed to run today.
if parms.runon:
weekday = datetime.datetime.today().strftime('%A')
run = [True for w in parms.runon if weekday.startswith(w)] # Note: T means Tuesday OR Thursday; S is Saturday OR Sunday
if not run:
#sys.stderr.write('Not running because today is %s but --runon=%s was specified\n' % (weekday, ' '.join(parms.runon)))
sys.exit(0) # Not running is a normal exit.
# OK, we are running. Figure out the dates to use.
if parms.todate:
cleanedtodate = cleandate(parms.todate, usetmyear=False)
# Go forward to the first date with data on or after the date specified
curs.execute("SELECT MIN(loadedfor) FROM loaded where tablename = 'clubs' AND loadedfor >= %s", (cleanedtodate,))
todate = stringify(curs.fetchone()[0])
if todate is None:
todate = cleanedtodate
else:
# We want the last date in the database
curs.execute("SELECT MAX(loadedfor) FROM loaded WHERE tablename = 'clubs'")
todate = stringify(curs.fetchone()[0])
if parms.fromdate:
fromdate = cleandate(parms.fromdate,usetmyear=False)
# Go backwards to the last date with data on or before the date specified
curs.execute("SELECT MAX(loadedfor) FROM loaded where tablename = 'clubs' AND loadedfor <= %s", (fromdate,))
fromdate = stringify(curs.fetchone()[0])
parms.parser.add_argument('--outfile', default='presidentsclub.txt')
parms.parser.add_argument('--earning', default='$101 in District Credit')
parms.parser.add_argument('--requiremembership', action='store_true', help='Specify to require that clubs meet membership goals to qualify.')
# Do global setup
globals.setup(parms)
curs = globals.curs
conn = globals.conn
# Your main program begins here.
# We want data from either the final date or the latest available, whichever is earlier
curs.execute("SELECT MAX(asof) FROM clubperf")
latest = stringify(curs.fetchone()[0])
parms.finaldate = cleandate(parms.finaldate)
targetdate = min(latest, parms.finaldate)
final = (targetdate == parms.finaldate)
status = "final" if final else "updated daily"
# Open the output file
outfile = open(parms.outfile, 'w')
winners = []
if parms.requiremembership:
# See if WHQ has posted any President's Distinguished Clubs; if so,
# use their information. If not, calculate on our own.
curs.execute("SELECT clubnumber, clubname FROM clubperf WHERE clubdistinguishedstatus = 'P' AND asof = %s", (targetdate,))
yesterday = yesterday.strftime('%Y-%m-%d')
parms.parser.add_argument("--date", dest='date', default=None)
# Do global setup
myglobals.setup(parms)
conn = myglobals.conn
curs = myglobals.curs
date = parms.date
have = {}
want = ['clubs', 'clubperf', 'distperf', 'areaperf']
for x in want:
have[x] = False
count = 0
if date:
date = tmutil.cleandate(date)
# All tables have to be there for the specified date.
curs.execute("SELECT tablename FROM loaded WHERE loadedfor='%s'" % (date))
for x in curs.fetchall():
have[x[0]] = True
count += 1
else:
curs.execute(
"SELECT tablename FROM loaded WHERE (loadedfor=%s OR loadedfor=%s) AND tablename IN ('clubperf', 'distperf', 'areaperf', 'clubs') GROUP BY tablename",
(today, yesterday))
for x in curs.fetchall():
have[x[0]] = True
count += 1
if count == len(want):
sys.exit(0) # Success!
if count == 3 and not have['clubs']:
clauses = []
# Figure out the timeframe for the queries.
today = datetime.datetime.today()
if parms.lastmonth:
month = today.month - 1
year = today.year
if month <= 0:
month = 12
year = year - 1
clauses.append('MONTH(awarddate) = %d' % month)
timestamp = 'during ' + datetime.date(year, month, 1).strftime('%B, %Y')
else:
firstdate = datetime.datetime.strptime(tmutil.cleandate(parms.since), '%Y-%m-%d')
clauses.append("awarddate >= '%s'" % firstdate.strftime('%Y-%m-%d'))
timestamp = 'since {d:%B} {d.day}, {d.year}'.format(d=firstdate)
if not parms.include_hpl:
clauses.append('award != "LDREXC"')
if parms.district:
clauses.append('district = %s' % parms.district)
### Bypass 'name unavailable'
clauses.append('membername NOT LIKE "%unavailable%"')
curs.execute("SELECT COUNT(DISTINCT membername) FROM awards WHERE " + ' AND '.join(clauses))
count = curs.fetchone()[0]
def cleandate(s):
return datetime.datetime.strptime(tmutil.cleandate(s, usetmyear=False),
'%Y-%m-%d').date()
# Do global setup
globals.setup(parms)
conn = globals.conn
curs = globals.curs
# Let's see if we're supposed to run today.
if parms.runon:
weekday = datetime.datetime.today().strftime('%A')
run = [True for w in parms.runon if weekday.startswith(w)] # Note: T means Tuesday OR Thursday; S is Saturday OR Sunday
if not run:
#sys.stderr.write('Not running because today is %s but --runon=%s was specified\n' % (weekday, ' '.join(parms.runon)))
sys.exit(0) # Not running is a normal exit.
# OK, we are running. Figure out the dates to use.
if parms.todate:
cleanedtodate = cleandate(parms.todate, usetmyear=False)
# Go forward to the first date with data on or after the date specified
curs.execute("SELECT MIN(loadedfor) FROM loaded where tablename = 'clubs' AND loadedfor >= %s", (cleanedtodate,))
todate = stringify(curs.fetchone()[0])
if todate is None:
todate = cleanedtodate
else:
# We want the last date in the database
curs.execute("SELECT MAX(loadedfor) FROM loaded WHERE tablename = 'clubs'")
todate = stringify(curs.fetchone()[0])
if parms.fromdate:
fromdate = cleandate(parms.fromdate,usetmyear=False)
# Go backwards to the last date with data on or before the date specified
curs.execute("SELECT MAX(loadedfor) FROM loaded where tablename = 'clubs' AND loadedfor <= %s", (fromdate,))
fromdate = stringify(curs.fetchone()[0])
if 'TM_DIRECTORY' in os.environ:
os.chdir(os.path.join(os.environ['TM_DIRECTORY'],'data'))
# Handle parameters
parms = tmparms.tmparms()
parms.parser.add_argument("--date", dest='date', default='today')
parms.add_argument('--newAlignment', dest='newAlignment', default=None, help='Overrides area/division data from the CLUBS table.')
parms.add_argument('--mapoverride', dest='mapoverride', default=None, help='Google spreadsheet with overriding address and coordinate information')
# Do global setup
globals.setup(parms)
conn = globals.conn
curs = globals.curs
parms.date = cleandate(parms.date)
# Promote information from parms.makemap if not already specified
parms.mapoverride = parms.mapoverride if parms.mapoverride else parms.makemap.get('mapoverride',None)
# Get the club information for the specified date
clubs = Club.getClubsOn(curs, parms.date)
# And remove suspended clubs.
clubs = removeSuspendedClubs(clubs, curs)
# And override it if needed.
if parms.newAlignment:
overrideClubs(clubs, parms.newAlignment, exclusive=False)
clauses = []
# Figure out the timeframe for the queries.
today = datetime.datetime.today()
if parms.lastmonth:
month = today.month - 1
year = today.year
if month <= 0:
month = 12
year = year - 1
clauses.append('MONTH(awarddate) = %d' % month)
timestamp = 'during ' + datetime.date(year, month, 1).strftime('%B, %Y')
else:
firstdate = datetime.datetime.strptime(tmutil.cleandate(parms.since), '%Y-%m-%d')
clauses.append("awarddate >= '%s'" % firstdate.strftime('%Y-%m-%d'))
timestamp = 'since {d:%B} {d.day}, {d.year}'.format(d=firstdate)
if not parms.include_hpl:
clauses.append('award != "LDREXC"')
# if parms.district:
# clauses.append('district = %s' % parms.district)
#remove non pathways:
clauses.append('award!="CL"')
clauses.append('award!="CC"')
clauses.append('award!="ALB"')
clauses.append('award!="ACB"')
# Handle parameters
parms = tmparms.tmparms()
parms.add_argument('--quiet', '-q', action='count')
parms.add_argument('--clubsonly', action='store_true')
parms.add_argument('resetto',
default='yesterday',
help="Date to reset the database to.")
# Add other parameters here
# Do global setup
globals.setup(parms)
curs = globals.curs
conn = globals.conn
# Your main program begins here.
resetdate = tmutil.cleandate(parms.resetto)
print('Resetting %sdatabase to %s' %
('CLUBS and CLUBCHANGES tables in ' if parms.clubsonly else '',
resetdate))
# Reset LOADED; reset the performance tables (if called for)
# NOTE: Does NOT reset the entrytype in the performance tables to match the latest for each club.
if parms.clubsonly:
curs.execute(
"DELETE FROM loaded WHERE loadedfor > %s AND tablename = 'clubs'",
(resetdate, ))
else:
curs.execute("DELETE FROM loaded WHERE loadedfor > %s", (resetdate, ))
curs.execute(
"DELETE FROM loaded WHERE loadedfor > %s AND tablename LIKE '%%perf'",
(resetdate, ))
reload(sys).setdefaultencoding('utf8')
# Handle parameters
parms = tmparms.tmparms()
parms.add_argument('--quiet', '-q', action='count')
parms.add_argument('resetto', default='yesterday', help="Date to reset the database to.")
# Add other parameters here
parms.parse()
# Connect to the database
conn = dbconn.dbconn(parms.dbhost, parms.dbuser, parms.dbpass, parms.dbname)
curs = conn.cursor()
# Your main program begins here.
resetdate = tmutil.cleandate(parms.resetto)
print 'Resetting database to', resetdate
# Do the straightforward resets
curs.execute("DELETE FROM loaded WHERE loadedfor > %s", (resetdate,))
curs.execute("DELETE FROM areaperf WHERE asof > %s", (resetdate, ))
curs.execute("DELETE FROM clubperf WHERE asof > %s", (resetdate, ))
curs.execute("DELETE FROM distperf WHERE asof > %s", (resetdate, ))
curs.execute("DELETE FROM clubchanges WHERE changedate > %s", (resetdate,))
# Take care of clubs
# First, remove any items whose first date is after the resetdate
curs.execute("DELETE FROM clubs WHERE firstdate > %s", (resetdate,))
# And now, reset any items whose last date is after the resetdate
curs.execute("UPDATE clubs SET lastdate = %s where lastdate > %s", (resetdate, resetdate))
parms.parser.add_argument("--date", dest='date', default=None)
# Do global setup
globals.setup(parms)
conn = globals.conn
curs = globals.curs
date = parms.date
have = {}
want = ['clubs', 'clubperf', 'distperf', 'areaperf']
for x in want:
have[x] = False
count = 0
if date:
date = tmutil.cleandate(date)
# All tables have to be there for the specified date.
curs.execute("SELECT tablename FROM loaded WHERE loadedfor='%s'"% (date))
for x in curs.fetchall():
have[x[0]] = True
count += 1
else:
curs.execute("SELECT tablename FROM loaded WHERE (loadedfor=%s OR loadedfor=%s) AND tablename IN ('clubperf', 'distperf', 'areaperf', 'clubs') GROUP BY tablename", (today, yesterday))
for x in curs.fetchall():
have[x[0]] = True
count += 1
if count == len(want):
sys.exit(0) # Success!
if count == 3 and not have['clubs']:
print('Only clubs table is not current')
sys.exit(1)
# Establish parameters
parms = tmparms.tmparms()
parms.parser.add_argument("--enddate", dest='enddate', type=str, default='6/30')
parms.parser.add_argument("--startdate", dest="startdate", type=str, default="M5")
parms.parser.add_argument("--outfileprefix", dest='outfileprefix', type=str, default='revup')
parms.parser.add_argument("--required", dest="required", type=int, default=3)
# Set up global environment
myglobals = tmglobals.tmglobals(parms)
conn = myglobals.conn
curs = myglobals.curs
today = myglobals.today
enddate = cleandate(parms.enddate, usetmyear=True)
qparms = []
qpart1 = "SELECT clubperf.clubnumber, clubperf.clubname, clubperf.activemembers - old.activemembers AS delta FROM clubperf INNER JOIN "
qpart2 = "(SELECT clubnumber, clubname, activemembers FROM clubperf WHERE "
if parms.startdate.startswith('M'):
qpart2 += "entrytype = 'M' AND district = %s AND monthstart = %s"
qparms += [parms.district, f"{today.year}-{('0'+parms.startdate[1:])[-2:]}-01"]
else:
qpart2 += "entrytype = 'L' AND district = %s"
qparms += [parms.district]
qpart2 += ") old ON old.clubnumber = clubperf.clubnumber WHERE "
parms.add_argument('--requireopenhouse', action='store_true')
#Do global setup
globals.setup(parms)
curs = globals.curs
conn = globals.conn
# Connect to the database
conn = dbconn.dbconn(parms.dbhost, parms.dbuser, parms.dbpass, parms.dbname)
curs = conn.cursor()
# Your main program begins here.
# Figure out the full base and final dates, anchoring them in the current TM year
basedate = tmutil.cleandate(parms.basedate)
finaldate = tmutil.cleandate(parms.finaldate)
# Also figure out the term end date we need, anchored to the calendar year
renewtodate = tmutil.cleandate(parms.renewto, usetmyear=False)
# Get all clubs for this year; we'll sort out suspended clubs later if need be
clubs = Club.getClubsOn(curs)
# And index them by name as well as number; set memdiff = 0 for each club.
clubsByName = {}
for c in list(clubs.keys()):
clubs[c].memdiff = 0
clubs[c].openhouse = False
clubs[c].earnings = 0
clubname = simplify(clubs[c].clubname)
clubsByName[clubname] = clubs[c]
parms.parser.add_argument("--sender", dest='sender')
parms.parser.add_argument("--reply-to", dest='replyto')
parms.parser.add_argument("--to", dest='to', nargs='+', default=[], action='append')
parms.parser.add_argument("--cc", dest='cc', nargs='+', default=[], action='append')
parms.parser.add_argument("--bcc", dest='bcc', nargs='+', default=[], action='append')
parms.add_argument('--fromdate', default='yesterday', dest='fromdate', help="First date for congrats.")
parms.add_argument('--todate', default='yesterday', dest='todate', help="Last date for congrats")
parms.add_argument('--dryrun', action='store_true', help="Don't send letters; do say who they'd go to.")
# Do global setup
myglobals.setup(parms, sections='awardmail')
curs = myglobals.curs
conn = myglobals.conn
parms.fromdate = cleandate(parms.fromdate)
parms.todate = cleandate(parms.todate)
parms.sender = parms.__dict__['from']
report = []
# Find anyone who earned one or more awards in the time period in question
curs.execute("SELECT awarddate, membername FROM awards WHERE awarddate >= %s AND awarddate <= %s AND acknowledged = 0 AND award != 'LDREXC' GROUP BY awarddate, membername ORDER BY awarddate, membername", (parms.fromdate, parms.todate))
targetlist = curs.fetchall()
# Now, for each person/date, get all of their info and generate the letter:
for (awarddate, membername) in targetlist:
def doDailyClubs(infile, conn, cdate, firsttime=False):
""" infile is a file-like object """
global changecount
from datetime import datetime, timedelta
curs = conn.cursor()
reader = csv.reader(infile)
hline = next(reader)
headers = cleanheaders(hline)
try:
clubcol = headers.index("clubnumber")
except ValueError:
if not hline[0].startswith('{"Message"'):
print("'clubnumber' not in '%s'" % hline)
return
try:
prospectiveclubcol = headers.index("prospectiveclub")
except ValueError:
prospectiveclubcol = False
# Find out what fields we have in the database itself
dbfields = []
curs.execute("describe clubs")
for l in curs.fetchall():
dbfields.append(l[0])
inform("clubs for", cdate, suppress=1)
dbheaders = [p for p in headers]
# Convert between Toastmasters' names for address and location and ours; they've changed it a few times. *sigh*
if "address1" in dbheaders:
addrcol1 = dbheaders.index("address1")
else:
addrcol1 = dbheaders.index("location")
if "address2" in dbheaders:
addrcol2 = dbheaders.index("address2")
else:
addrcol2 = dbheaders.index("address")
dbheaders[addrcol1] = "place"
dbheaders[addrcol2] = "address"
expectedheaderscount = len(dbheaders)
dbheaders.append("firstdate")
dbheaders.append("lastdate") # For now...
areacol = dbheaders.index("area")
divisioncol = dbheaders.index("division")
statecol = dbheaders.index("state")
# Now, suppress anything in the file that's not in the database:
suppress = []
oldheaders = dbheaders
dbheaders = []
for i in range(len(oldheaders)):
if oldheaders[i] in dbfields:
dbheaders.append(oldheaders[i])
else:
suppress.append(i)
suppress.reverse() # We remove these columns from the input
Club.setfieldnames(dbheaders)
# We need to get clubs for the most recent update so we know whether to update an entry
# or start a new one.
yesterday = datetime.strftime(
datetime.strptime(cdate, "%Y-%m-%d") - timedelta(1), "%Y-%m-%d")
clubhist = Club.getClubsOn(curs, date=yesterday)
for row in reader:
if len(row) < expectedheaderscount:
break # we're finished
if prospectiveclubcol is not None and row[prospectiveclubcol]:
continue # Ignore prospective clubs
for i in suppress:
del row[i]
if len(row) > expectedheaderscount:
# Special case...Millbrae somehow snuck two club websites in!
row[16] = row[16] + "," + row[17]
del row[17]
# print row[addrcol1]
# print row[addrcol2]
# Now, clean up the address:
# Address line 1 is "place" information and can be multiple lines.
# Address line 2 is the real address and should be treated as one line, with spaces normalized.
place = "\n".join([x.strip() for x in row[addrcol1].split(" ")])
row[addrcol1] = place
address = normalize(row[addrcol2])
row[addrcol2] = address
# Toastmasters is currently reversing the "Area" and "Division" items. "Area" should be a
# number; if not, swap the two.
try:
thearea = row[areacol]
thedivision = row[divisioncol]
areanum = int(row[areacol])
except ValueError:
row[areacol] = thedivision
row[divisioncol] = thearea
# Collapse state names into their abbreviations
if row[statecol] in statelist:
row[statecol] = statelist[row[statecol]]
# Get the right number of items into the row by setting today as the
# tentative first and last date
row.append(cdate)
row.append(cdate)
# And create the object
club = Club(row)
# Now, clean up things coming from Toastmasters
if club.clubstatus.startswith("Open") or club.clubstatus.startswith(
"None"):
club.clubstatus = "Open"
else:
club.clubstatus = "Restricted"
# Clean up the club and district numbers and the area
club.clubnumber = cleanitem(club.clubnumber)
club.district = cleanitem(club.district)
club.area = cleanitem(club.area)
# If a club is partially unassigned, mark it as completely unassigned.
if ((club.area == "0A") or (club.area == "0D")
or (club.division == "0D") or (club.division == "0A")):
club.area = "0A"
club.division = "0D"
# Clean up the charter date
if not club.charterdate.strip():
continue # This is a prospective club that Toastmasters didn't mark properly; ignore it.
club.charterdate = cleandate(club.charterdate)
# Clean up advanced status
club.advanced = "1" if (club.advanced != "") else "0"
# Clean up online status
club.allowsonlineattendance = ("1" if
(club.allowsonlineattendance != "") else
"0")
# Add missing schemes to any URLs
club.fixURLSchemes()
# Now, take care of missing latitude/longitude
if ("latitude") in dbheaders:
try:
club.latitude = float(club.latitude)
except ValueError:
club.latitude = 0.0
else:
club.latitude = 0.0
if ("longitude") in dbheaders:
try:
club.longitude = float(club.longitude)
except ValueError:
club.longitude = 0.0
else:
club.longitude = 0.0
# Sometimes, Toastmasters gets the latitude and longitude backwards
# If that turns out to create an impossible location (which it will in California),
# let's swap them.
if abs(club.latitude) > 90.0:
(club.latitude, club.longitude) = (club.longitude, club.latitude)
# And put it into the database if need be
if club.clubnumber in clubhist:
changes = different(club, clubhist[club.clubnumber],
dbheaders[:-2])
else:
changes = []
if club.clubnumber not in clubhist and not firsttime:
# This is a new (or reinstated) club; note it in the changes database.
curs.execute(
'INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, "New Club", "", "")',
(club.clubnumber, cdate),
)
if club.clubnumber not in clubhist or changes:
club.firstdate = club.lastdate
# Encode newlines in the place as double-semicolons for the database
club.place = club.place.replace("\n", ";;")
values = [club.__dict__[x] for x in dbheaders]
# And then put the place back into normal form
club.place = club.place.replace(";;", "\n")
thestr = ("INSERT IGNORE INTO clubs (" + ",".join(dbheaders) +
") VALUES (" + ",".join(["%s"
for each in values]) + ");")
try:
changecount += curs.execute(thestr, values)
except Exception as e:
print(e)
# Capture changes
for (item, old, new) in changes:
if item == "place":
# Clean up the place (old and new) for the database
old = old.replace("\n", ";;")
new = new.replace("\n", ";;")
try:
curs.execute(
"INSERT IGNORE INTO clubchanges (clubnumber, changedate, item, old, new) VALUES (%s, %s, %s, %s, %s)",
(club.clubnumber, cdate, item, old, new),
)
except Exception as e:
print(e)
clubhist[club.clubnumber] = club
if different(club, clubhist[club.clubnumber], dbheaders[:-2]):
print("it's different after being set.")
sys.exit(3)
else:
# update the lastdate
changecount += curs.execute(
"UPDATE clubs SET lastdate = %s WHERE clubnumber = %s AND lastdate = %s;",
(cdate, club.clubnumber, clubhist[club.clubnumber].lastdate),
)
# If all the files were processed, today's work is done.
curs.execute(
'INSERT IGNORE INTO loaded (tablename, loadedfor) VALUES ("clubs", %s)',
(cdate, ),
)
parms.add_argument('--requireopenhouse', action='store_true')
#Do global setup
globals.setup(parms)
curs = globals.curs
conn = globals.conn
# Connect to the database
conn = dbconn.dbconn(parms.dbhost, parms.dbuser, parms.dbpass,
parms.dbname)
curs = conn.cursor()
# Your main program begins here.
# Figure out the full base and final dates, anchoring them in the current TM year
basedate = tmutil.cleandate(parms.basedate)
finaldate = tmutil.cleandate(parms.finaldate)
# Also figure out the term end date we need, anchored to the calendar year
renewtodate = tmutil.cleandate(parms.renewto, usetmyear=False)
# And get the clubs on the base date
clubs = Club.getClubsOn(curs, date=basedate)
# And index them by name as well as number; set memdiff = 0 for each club.
clubsByName = {}
for c in list(clubs.keys()):
clubs[c].memdiff = 0
clubs[c].openhouse = False
clubs[c].earnings = 0
clubname = simplify(clubs[c].clubname)
clubsByName[clubname] = clubs[c]
curs = globals.curs
conn = globals.conn
# See if we have the data needed to run and build the base part of the query
if parms.basedate.upper().startswith('M'):
basemonth = int(parms.basedate[1:])
if not isMonthFinal(basemonth, curs):
sys.exit(1)
basepart = 'monthstart = "%s" AND entrytype = "M"' % getMonthStart(
basemonth, curs)
friendlybase = 'New Members on %s' % neaten(getMonthEnd(basemonth))
msgdate = datetime.date(globals.today.year, basemonth + 1, 1)
if basemonth == 12:
msgdate = datetime.date(globals.today.year, 1, 1)
else:
basedate = cleandate(parms.basedate)
if not haveDataFor(basedate, curs):
sys.exit(1)
basepart = 'asof = "%s"' % basedate
msgdate = datetime.datetime.strptime(basedate, '%Y-%m-%d')
friendlybase = 'New Members on %s' % neaten(msgdate)
msgbase = dateAsWords(msgdate)
# Figure out the end date and build that part of the query
yesterday = cleandate('yesterday')
if parms.finaldate.upper().startswith('M'):
finalmonth = int(parms.finaldate[1:])
msgdate = getMonthEnd(finalmonth)
if isMonthFinal(finalmonth, curs):
finalpart = 'monthstart = "%s" AND entrytype = "M"' % getMonthStart(
finalmonth, curs)
def cleandate(s):
return datetime.datetime.strptime(tmutil.cleandate(s, usetmyear=False), '%Y-%m-%d').date()
