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 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)