.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:
)
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(
"""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
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, ),
)
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))
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)
# 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 = []
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('--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 = {}
# 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
# 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()
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,),
)
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
