# 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]
# Build the result arrays
only3 = []
only5 = []
OHand3 = []
OHand5 = []
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('--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)
# If there are overrides to club positioning, handle them now
if parms.mapoverride:
overrideClubPositions(clubs, parms.mapoverride, parms.googlemapsapikey)
cities = {}
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)
# Make it easy to run under TextMate
if 'TM_DIRECTORY' in os.environ:
os.chdir(os.path.join(os.environ['TM_DIRECTORY'],'data'))
reload(sys).setdefaultencoding('utf8')
# Handle parameters
parms = tmparms.tmparms()
parms.parser.add_argument("--date", dest='date', default=datetime.today().strftime('%Y-%m-%d'))
parms.parse()
#print 'Connecting to %s:%s as %s' % (parms.dbhost, parms.dbname, parms.dbuser)
conn = dbconn.dbconn(parms.dbhost, parms.dbuser, parms.dbpass, parms.dbname)
curs = conn.cursor()
# Get club information from the database as of the date requested (or today)
clubs = Club.getClubsOn(curs)
# Now, add relevant club performance information. If there are clubs in the
# performance data which aren't in the master list from TMI, note it and add
# them anyway.
perffields = ['clubnumber', 'clubname', 'district', 'area', 'division', 'eligibility', 'color', 'membase', 'activemembers', 'goalsmet']
print parms.date
curs.execute("SELECT clubnumber, clubname, district, area, division, clubstatus as eligibility, color, membase, activemembers, goalsmet FROM clubperf WHERE asof = %s", (parms.date,))
for info in curs.fetchall():
clubnum = Club.stringify(info[0])
try:
club = clubs[clubnum]
club.addvalues(info, perffields)
def overrideClubs(clubs, newAlignment, exclusive=True):
""" Updates 'clubs' to reflect the alignment in the newAlignment spreadsheet.
Typically used at a TM year boundary or for planning realignment.
newAlignment is a "test alignment" file - a CSV with the clubs in the new
alignment. Non-blank columns in the file override information from the database,
with the following exceptions:
"likelytoclose" is used during planning to identify clubs in danger.
"clubname" does NOT override what comes from Toastmasters.
"newarea" is of the form Xn (X is the Division; n is the area).
Specify exclusive=False to keep clubs not included in the spreadsheet;
otherwise, clubs not in the spreadsheet are discarded.
"""
pfile = open(newAlignment, 'r')
reader = csv.DictReader(pfile)
keepers = set()
# Sadly, the callers of this routine have different types of key.
# We have to be resilient against this.
if isinstance(list(clubs.keys())[0], int):
fixup = lambda x: int(x)
elif isinstance(list(clubs.keys())[0], (int, float)):
fixup = lambda x: float(x)
else:
fixup = lambda x: x
for row in reader:
clubnum = fixup(row['clubnumber'])
try:
club = clubs[clubnum]
except KeyError:
from simpleclub import Club
club = Club(list(row.values()),
fieldnames=list(row.keys()),
fillall=True)
if 'district' not in list(row.keys()):
club.district = globals.parms.district
clubs[clubnum] = club
keepers.add(clubnum)
if row['newarea'] and row['newarea'] != '0D0A':
club.division = row['newarea'][0]
club.area = row['newarea'][1:]
for item in reader.fieldnames:
if row[item].strip() and item not in [
'clubnumber', 'clubname', 'newarea'
]:
# Maintain the same type
if item in club.__dict__ and isinstance(
club.__dict__[item], numbers.Number):
if isinstance(club.__dict__[item], int):
club.__dict__[item] = int(row[item])
elif isinstance(club.__dict__[item], float):
club.__dict__[item] = float(row[item])
else:
club.__dict__[item] = int(row[item])
else:
club.__dict__[item] = row[item]
pfile.close()
# Remove clubs not in the file:
if exclusive:
clubnumbers = list(clubs.keys())
for c in clubnumbers:
if c not in keepers:
del clubs[c]
return clubs
# 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]
sheet = GSheet(parms.openhouseclubs, parms.googlesheetsapikey)
# Now read the openhouse clubs and get their numbers
eligible = set()
for row in sheet:
cn = '%s' % row.clubnumber
"""select clubs.clubnumber, clubs.charterdate, clubs.clubname from clubs inner join (select clubnumber, max(lastdate) as mld from clubs group by clubnumber) a on clubs.clubnumber = a.clubnumber and clubs.lastdate = a.mld where charterdate >= %s and charterdate <= %s """,
('%d-07-01' % tmyear, '%d-06-30' % (tmyear + 1)))
for (clubnumber, charterdate, clubname) in curs.fetchall():
try:
clubs[clubnumber].charterdate = charterdate.strftime('%m/%d/%y')
except KeyError:
print('Club %s (%d) not in performance reports, ignored.' %
(clubname, clubnumber))
# Test alignment processing
if parms.testalignment:
# If any clubs get created by overrideClubs, they are of the standard
# simpleclub.Club type. We need to create objects of the local Club
# type instead, but keep the values.
from simpleclub import Club as sClub
sClub.getClubsOn(curs) # Ensure everything is defined
from tmutil import overrideClubs
oldkeys = set(clubs.keys())
clubs = overrideClubs(clubs, parms.testalignment)
for c in list(clubs.keys()):
if c not in oldkeys:
nclub = clubs[c]
# We must replace this with a club of the local type
clubs[c] = Club(nclub.clubnumber, nclub.clubname, nclub.area,
nclub.division, nclub.district, nclub.suspenddate)
# And now, finish setting up the structure (creating Areas and Divisions, for example)
for club in list(clubs.values()):
club.finishSettingUp()
# Now, get information from the Area/Division performance table. We only need the latest one.
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,),
)
# to the performance files.
curs.execute("""select clubnumber, charterdate, clubname from clubs where charterdate >= %s and charterdate <= %s group by clubnumber """, ('%d-07-01' % tmyear, '%d-06-30' % (tmyear+1)))
for (clubnumber, charterdate, clubname) in curs.fetchall():
try:
clubs[clubnumber].charterdate = charterdate.strftime('%m/%d/%y')
except KeyError:
print 'Club %s (%d) not in performance reports, ignored.' % (clubname, clubnumber)
# New alignment processing
if parms.newAlignment:
# If any clubs get created by overrideClubs, they are of the standard
# simpleclub.Club type. We need to create objects of the local Club
# type instead, but keep the values.
from simpleclub import Club as sClub
sClub.getClubsOn(curs) # Ensure everything is defined
from tmutil import overrideClubs
oldkeys = set(clubs.keys())
clubs = overrideClubs(clubs, parms.newAlignment)
for c in clubs.keys():
if c not in oldkeys:
nclub = clubs[c]
# We must replace this with a club of the local type
clubs[c] = Club(nclub.clubnumber, nclub.clubname, nclub.area, nclub.division, nclub.district, nclub.suspenddate)
# And now, finish setting up the structure (creating Areas and Divisions, for example)
for club in clubs.values():
club.finishSettingUp()
if __name__ == "__main__":
import tmparms
# Establish parameters
parms = tmparms.tmparms()
parms.add_argument('--featured', default='https://docs.google.com/spreadsheets/d/19-imepV9YNq5N_g9GcBP5Ynq9BjTZP7kC_rLPnFAK7w/')
parms.add_argument('--outfile', default='featuredclubs.shtml')
# Add other parameters here
# Do global setup
globals.setup(parms)
allclubs = Club.getClubsOn(globals.curs)
# Open the spreadsheet
gc = gspread.authorize(tmutil.getGoogleCredentials())
book = gc.open_by_url(parms.featured)
# Get totals
sheet = book.sheet1
clubs = []
for row in sheet.get_all_records():
if (not row['Club Name'].strip()):
break
clubs.append(FeaturedClub(row))
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])
else:
# We want the most recent date with data before the todate
curs.execute("SELECT MAX(loadedfor) FROM loaded WHERE tablename = 'clubs' AND loadedfor < %s", ((todate,)))
fromdate = stringify(curs.fetchone()[0])
namestocompare = ['place', 'address', 'city', 'state', 'zip', 'country', 'meetingday', 'meetingtime', 'area', 'division', 'district']
# Get information for clubs as of the "from" date:
oldclubs = Club.getClubsOn(curs, date=fromdate, goodnames=namestocompare)
oldclubs = removeSuspendedClubs(oldclubs, curs, date=fromdate)
newclubs = {} # Where clubs created during the period go
changedclubs = {} # Where clubs changed during the period go
# And compare to the the list of clubs at the end of the period
allclubs = Club.getClubsOn(curs, date=todate)
allclubs = removeSuspendedClubs(allclubs, curs, date=todate)
for club in list(allclubs.values()):
if club.clubnumber not in oldclubs:
club.info = 'New Club'
newclubs[club.clubnumber] = club
elif club == oldclubs[club.clubnumber]:
# Club is unchanged; just remove it
def overrideClubPositions(clubs, overridefile, apikey, log=False, ignorefields=[], donotlog=[], createnewclubs=False):
""" Updates 'clubs' with information from the override spreadsheet
Note: the same apikey is used for Google Maps and the Google spreadsheet """
from geocode import myclub
gmaps = googlemaps.Client(key=apikey)
myclub.setgmaps(gmaps)
# Get the data from the spreadsheet
# We may be passed a whole URL or just the key
if '/' in overridefile:
# Have a whole URL; get the key
overridefile = re.search(r'/spreadsheets/d/([a-zA-Z0-9-_]+)', overridefile).groups()[0]
# Connect to the spreadsheet and get the values
service = discovery.build('sheets', 'v4', developerKey=apikey)
request = service.spreadsheets().values().get(spreadsheetId=overridefile, range='a1:zz999')
values = request.execute()['values']
keys = values[0]
# Make sure all clubs have all the keys in our override, plus "touchedby"
requiredkeys = list(keys)
requiredkeys.append('touchedby')
# We may be creating new keys; the default for each is None.
for c in clubs:
for k in requiredkeys:
club = clubs[c]
if k not in club.__dict__:
club.__dict__[k] = None
# Now, work through the overrides
linenum = 0
for line in values[1:]:
linenum += 1
# Convert the values to a dictionary, based on the keys.
row = {}
for i, key in enumerate(keys):
try:
# Normalize whitespace, including non-breaking spaces
row[key] = normalizespaces(line[i])
except IndexError:
row[key] = ''
# Now, process the data.
clubnumber = row['clubnumber'].strip()
if not clubnumber:
clubnumber = '%s' % (0 - linenum)
row['clubnumber'] = clubnumber
if clubnumber not in clubs and createnewclubs:
club = Club([normalizespaces(f) for f in line], fieldnames=keys, fillall=True)
club.clubnumber = clubnumber
clubs[clubnumber] = club
if log:
print(("%8s/%s *** New Club ***" % (club.clubnumber, club.clubname)))
if clubnumber in clubs:
club = clubs[clubnumber]
# Indicate we touched this club
club.touchedby = overridefile
# Override anything specified; add anything new
for key in keys:
if key not in ignorefields and (row[key] or key not in club.__dict__):
if log and key not in donotlog and key in club.__dict__ and club.__dict__[key] != row[key]:
print(("%8s/%s: Updating '%s' from '%s' to '%s'" % (club.clubnumber, club.clubname, key, club.__dict__[key], row[key])))
club.__dict__[key] = row[key]
# Now, compute latitude and longitude if need be
try:
# Use explicit coordinates if we find them
club.latitude = float(club.latitude)
club.longitude = float(club.longitude)
# Both are specified, so we use them
except: # latitude and longitude not both specified
if row['address']:
parts = ['address', 'city', 'state', 'zip', 'country']
address = ', '.join([row[p] for p in parts])
# No coordinates provided but an address is here; use it.
gres = gmaps.geocode(address) # Includes city, state, zip, country if needed
# Make a dummy club entry for geocoding
reloinfo = myclub(clubnumber, club.clubname, club.place, club.address, club.city, club.state, club.zip, club.country, 0.0, 0.0)
# TODO: get rid of the need for a dummy club entry - have geocode return coordinates.
reloinfo.process(gres)
club.latitude = reloinfo.latitude
club.longitude = reloinfo.longitude
if __name__ == "__main__":
import tmparms
# Establish parameters
parms = tmparms.tmparms()
parms.add_argument(
'--featured',
default=
'https://docs.google.com/spreadsheets/d/19-imepV9YNq5N_g9GcBP5Ynq9BjTZP7kC_rLPnFAK7w/'
)
parms.add_argument('--outfile', default='${workdir}/featuredclubs.shtml')
# Add other parameters here
# Do global setup
myglobals.setup(parms)
allclubs = Club.getClubsOn(myglobals.curs)
# Open the spreadsheet
gc = gspread.authorize(tmutil.getGoogleCredentials())
book = gc.open_by_url(parms.featured)
# Get totals
sheet = book.sheet1
clubs = []
for row in sheet.get_all_records():
if (not row['Club Name'].strip()):
break
clubs.append(FeaturedClub(row))
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])
else:
# We want the most recent date with data before the todate
curs.execute("SELECT MAX(loadedfor) FROM loaded WHERE tablename = 'clubs' AND loadedfor < %s", ((todate,)))
fromdate = stringify(curs.fetchone()[0])
namestocompare = ['place', 'address', 'city', 'state', 'zip', 'country', 'meetingday', 'meetingtime', 'area', 'division', 'district']
# Get information for clubs as of the "from" date:
oldclubs = Club.getClubsOn(curs, date=fromdate, goodnames=namestocompare)
oldclubs = removeSuspendedClubs(oldclubs, curs, date=fromdate)
newclubs = {} # Where clubs created during the period go
changedclubs = {} # Where clubs changed during the period go
# And compare to the the list of clubs at the end of the period
allclubs = Club.getClubsOn(curs, date=todate)
allclubs = removeSuspendedClubs(allclubs, curs, date=todate)
for club in list(allclubs.values()):
if club.clubnumber not in oldclubs:
club.info = 'New Club'
newclubs[club.clubnumber] = club
elif club == oldclubs[club.clubnumber]:
# Club is unchanged; just remove it
)
parms.add_argument(
'--includeprecharter',
dest='includeprecharter',
action='store_true',
help=
'Specify this to include "pre-charter" clubs (ones with negative club numbers in the newalignment file)'
)
# Do global setup
myglobals.setup(parms, sections='alignment')
conn = myglobals.conn
curs = myglobals.curs
# Get all defined clubs from the database.
clubs = Club.getClubsOn(curs)
# Get coordinates
setClubCoordinatesFromGEO(clubs, curs, removeNotInGeo=False)
# If there are overrides to club positioning, handle them now
if parms.mapoverride:
print(('Processing general overrides from %s' % parms.mapoverride))
overrideClubPositions(clubs,
parms.mapoverride,
parms.googlemapsapikey,
log=True,
createnewclubs=True)
# Now, add info from clubperf (and create club.oldarea for each club)
# We use the 'lastfor' table to get information for all clubs, even those which Toastmasters dropped from
# the list because they were suspended for two renewal cycles.
curs.execute(
def overrideClubPositions(clubs, overridefile, apikey, log=False, ignorefields=[], donotlog=[], createnewclubs=False):
""" Updates 'clubs' with information from the override spreadsheet
Note: the same apikey is used for Google Maps and the Google spreadsheet """
from geocode import myclub
gmaps = googlemaps.Client(key=apikey)
myclub.setgmaps(gmaps)
# Get the data from the spreadsheet
# We may be passed a whole URL or just the key
if '/' in overridefile:
# Have a whole URL; get the key
overridefile = re.search(r'/spreadsheets/d/([a-zA-Z0-9-_]+)', overridefile).groups()[0]
# Connect to the spreadsheet and get the values
service = discovery.build('sheets', 'v4', developerKey=apikey)
request = service.spreadsheets().values().get(spreadsheetId=overridefile, range='a1:zz999')
values = request.execute()['values']
keys = values[0]
# Make sure all clubs have all the keys in our override, plus "touchedby"
requiredkeys = list(keys)
requiredkeys.append('touchedby')
# We may be creating new keys; the default for each is None.
for c in clubs:
for k in requiredkeys:
club = clubs[c]
if k not in club.__dict__:
club.__dict__[k] = None
# Now, work through the overrides
for line in values[1:]:
# Convert the values to a dictionary, based on the keys.
row = {}
for i, key in enumerate(keys):
try:
# Normalize whitespace, including non-breaking spaces
row[key] = normalizespaces(line[i])
except IndexError:
row[key] = ''
# Now, process the data.
clubnumber = row['clubnumber']
if clubnumber not in clubs and createnewclubs:
club = Club([normalizespaces(f) for f in line], fieldnames=keys)
clubs[clubnumber] = club
if log:
print(("%8s/%s *** New Club ***" % (club.clubnumber, club.clubname)))
if clubnumber in clubs:
club = clubs[clubnumber]
# Indicate we touched this club
club.touchedby = overridefile
# Override anything specified
for key in keys:
if key not in ignorefields and row[key]:
if log and key not in donotlog and club.__dict__[key] != row[key]:
print(("%8s/%s: Updating '%s' from '%s' to '%s'" % (club.clubnumber, club.clubname, key, club.__dict__[key], row[key])))
club.__dict__[key] = row[key]
# Now, compute latitude and longitude if need be
try:
# Use explicit coordinates if we find them
club.latitude = float(club.latitude)
club.longitude = float(club.longitude)
# Both are specified, so we use them
except: # latitude and longitude not both specified
if row['address']:
parts = ['address', 'city', 'state', 'zip', 'country']
address = ', '.join([row[p] for p in parts])
# No coordinates provided but an address is here; use it.
gres = gmaps.geocode(address) # Includes city, state, zip, country if needed
# Make a dummy club entry for geocoding
reloinfo = myclub(clubnumber, club.clubname, club.place, club.address, club.city, club.state, club.zip, club.country, 0.0, 0.0)
# TODO: get rid of the need for a dummy club entry - have geocode return coordinates.
reloinfo.process(gres)
club.latitude = reloinfo.latitude
club.longitude = reloinfo.longitude
.areainfo { width: 95%; float: left;}
""")
outfile.write("""
.clearfix:after {
content: "";
display: table;
clear: both;
}
</style>
</head>
<body>
""")
# Get club information from the database as of today
clubs = Club.getClubsOn(curs, date=datetime.today().strftime('%y-%m-%d'))
# Now, add relevant club performance information. If there are clubs in the
# performance data which aren't in the master list from TMI, note it and add
# them anyway.
perffields = ['clubnumber', 'clubname', 'district', 'area', 'division', 'eligibility', 'color', 'membase', 'activemembers', 'goalsmet']
curs.execute("SELECT clubnumber, clubname, district, area, division, clubstatus as eligibility, color, membase, activemembers, goalsmet FROM clubperf WHERE entrytype = 'L' and district = %s", (parms.district,))
for info in curs.fetchall():
clubnum = Club.stringify(info[0])
try:
club = clubs[clubnum]
club.addvalues(info, perffields)
except KeyError:
def overrideClubs(clubs, newAlignment, exclusive=True):
""" Updates 'clubs' to reflect the alignment in the newAlignment spreadsheet.
Typically used at a TM year boundary or for planning realignment.
newAlignment is a "test alignment" file - a CSV with the clubs in the new
alignment. Non-blank columns in the file override information from the database,
with the following exceptions:
"likelytoclose" is used during planning to identify clubs in danger.
"clubname" does NOT override what comes from Toastmasters.
"newarea" is of the form Xn (X is the Division; n is the area).
Specify exclusive=False to keep clubs not included in the spreadsheet;
otherwise, clubs not in the spreadsheet are discarded.
"""
pfile = open(newAlignment, 'rbU')
reader = csv.DictReader(pfile)
keepers = set()
# Sadly, the callers of this routine have different types of key.
# We have to be resilient against this.
if isinstance(clubs.keys()[0], int):
fixup = lambda x:int(x)
elif isinstance(clubs.keys()[0], (long, float)):
fixup = lambda x:float(x)
else:
fixup = lambda x:x
for row in reader:
clubnum = fixup(row['clubnumber'])
try:
club = clubs[clubnum]
except KeyError:
from simpleclub import Club
club = Club(row.values(), fieldnames=row.keys(), fillall=True)
clubs[clubnum] = club
keepers.add(clubnum)
if row['newarea'] and row['newarea'] != '0D0A':
club.division = row['newarea'][0]
club.area = row['newarea'][1:]
for item in reader.fieldnames:
if row[item].strip() and item not in ['clubnumber', 'clubname', 'newarea']:
# Maintain the same type
if item in club.__dict__ and isinstance(club.__dict__[item], numbers.Number):
if isinstance(club.__dict__[item], int):
club.__dict__[item] = int(row[item])
elif isinstance(club.__dict__[item], float):
club.__dict__[item] = float(row[item])
else:
club.__dict__[item] = long(row[item])
else:
club.__dict__[item] = row[item]
pfile.close()
# Remove clubs not in the file:
if exclusive:
clubnumbers = clubs.keys()
for c in clubnumbers:
if c not in keepers:
del clubs[c]
return clubs
