class GSheetTest(unittest.TestCase):
def setUp(self):
self.gsheet = GSheet()
def ttest_init(self):
self.gsheet.setSpreadSheet('D2L Courses')
self.gsheet.setWorkSheet('test')
self.assertTrue(self.gsheet.curr_wksht_id == 'ocx')
class GSheetTest(unittest.TestCase):
def setUp(self):
self.gsheet = GSheet()
def ttest_init(self):
self.gsheet.setSpreadSheet('D2L Courses')
self.gsheet.setWorkSheet('test')
self.assertTrue(self.gsheet.curr_wksht_id == 'ocx')
sys.stderr.write('The number of values for PCT and EARNS must be the same, but we got %d in PCT and %d in EARNS\n' % (len(parms.pct), len(parms.earns)))
sys.exit(1)
if parms.name:
if len(parms.pct) != len(parms.name):
sys.stderr.write('The number of values for PCT and NAME must be the same, but we got %d in PCT and %d in NAME\n' % (len(parms.pct), len(parms.name)))
sys.exit(1)
else:
parms.name = len(parms.pct) * ('',)
levels = [level(parms.pct[i], parms.earns[i], parms.name[i]) for i in range(len(parms.pct))]
levels.sort(key=lambda l:l.pct, reverse=True)
# Now, get the clubs which qualify
for row in GSheet(parms.trackingsheet, parms.googlesheetsapikey, sheetname="March Madness-DO NOT EDIT"):
renewalcol = row.fieldnames.index('renewed') # Because of duplicate column headers
row.clubnumber = row.number # Compensate for naming changes
try:
int(row.clubnumber) # Skip non-club rows
except ValueError:
continue
if renewalcol > len(row.values):
print('Ignoring short row:', '; '.join([f'{item[0]}={item[1]}' for item in zip(row.fieldnames, row.values)]))
continue
curs.execute("SELECT area, division, clubname, asof FROM clubperf WHERE clubnumber = %s ORDER BY ASOF DESC LIMIT 1", (row.clubnumber,))
try:
(area, division, clubname, asof) = curs.fetchone()
except:
print(row)
if (clubname.strip() != row.clubname):
# Add current coordinates and remove clubs without coordinates (unless there's a new alignment)
setClubCoordinatesFromGEO(clubs, curs, removeNotInGeo=not parms.newAlignment)
# If there are overrides to club positioning, handle them now
if parms.mapoverride:
overrideClubPositions(clubs, parms.mapoverride, parms.googlemapsapikey)
# Now, assign clubs to Areas and Divisions
for c in sorted(clubs):
club = clubs[c]
Area.find(club.division, club.area).addclub(club)
# OK, now we have the club info. Let's get the Area Director/Division Director information.
for row in GSheet(parms.officers, apikey=parms.googlesheetsapikey):
for k in row.fieldnames:
setattr(row, k, ' '.join(str(getattr(row, k))).split())
if row.title and row.first:
Director(row)
# And now we go through the Divisions and Areas and build the output.
outfile = open(parms.outfile, 'w')
outfile.write(
"<p><b>Click on a Division to see the clubs and Areas it contains.</b></p>"
)
outfile.write(
"""[et_pb_tabs admin_label="Tabs" use_border_color="off" border_color="#ffffff" border_style="solid" tab_font_size="18"]
""")
for d in sorted(Division.divisions):
if d.lower() != 'new':
# 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
eligible.add(cn)
clubs[cn].openhouse = True
clubs[cn].earnings += 20 # Earn $20 for an Open House
# And build "IN" clause. We know all the items are numbers, so we don't have to worry about SQL injection.
if parms.requireopenhouse:
eligibilityclause = 'AND clubnum IN (' + ','.join(
list(eligible)) + ') '
else:
eligibilityclause = ''
from gsheet import GSheet
PLAN = 'https://docs.google.com/spreadsheets/d/e/your_key_sheet/pubhtml'
tabela = GSheet(PLAN)
tabela.select_table('plan1')
tabela.show_table()
tabela.save_table('my_table')
tabela.show_table()
tabela.load_table('mytable')
tabela.show_table()
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 = []
onlyOH = []
sheet = GSheet(parms.openhouseclubs,
parms.googlesheetsapikey,
sheetname=parms.sheetname)
# Now read the openhouse clubs and get their numbers
hadOH = set()
for row in sheet:
cn = '%s' % row.clubnumber
hadOH.add(cn)
clubs[cn].openhouse = True
clubs[cn].earnings += 20 # Earn $20 for an Open House
# And build "IN" clause. We know all the items are numbers, so we don't have to worry about SQL injection.
if parms.requireopenhouse:
eligibilityclause = 'AND clubnum IN (' + ','.join(list(hadOH)) + ') '
else:
eligibilityclause = ''
# Add current coordinates and remove clubs without coordinates (unless there's a new alignment)
setClubCoordinatesFromGEO(clubs, curs, removeNotInGeo=not parms.newalignment)
# If there are overrides to club positioning, handle them now
if parms.mapoverride:
overrideClubPositions(clubs, parms.mapoverride, parms.googlemapsapikey)
# Now, assign clubs to Areas and Divisions
for c in sorted(clubs):
club = clubs[c]
Area.find(club.division, club.area).addclub(club)
# OK, now we have the club info. Let's get the Area Director/Division Director information.
for row in GSheet(parms.officers,
apikey=parms.googlesheetsapikey,
sheetname="Current"):
for k in row.fieldnames:
setattr(row, k, ' '.join(str(getattr(row, k))).split())
if row.title and row.first:
Director(row)
# And now we go through the Divisions and Areas and build the output.
outfile = open(parms.outfile, 'w')
outfile.write(
"<p><b>Click on a Division to see the clubs and Areas it contains.</b></p>"
)
outfile.write(
"""[et_pb_tabs admin_label="Tabs" use_border_color="off" border_color="#ffffff" border_style="solid" tab_font_size="18"]
""")
for d in sorted(Division.divisions):
# Load students
if parms.students:
import csv
with open(parms.students, 'r') as csvfile:
srdr = csv.reader(csvfile)
next(srdr) # Throw away the headers
for row in srdr:
Student(row)
# Load all possible honors from the master file
master = GSheet(parms.honorsmaster, parms.apikey)
Honor.setlabels(master.labels)
for row in master:
Honor(row)
# Honor.all has all the honors.
# Process divided readings if we have them this year:
sheetname = getattr(parms, 'dividedsheetname', '')
if sheetname:
dr = GSheet(parms.honorsmaster, parms.apikey, sheetname=sheetname)
for row in dr:
DividedReading(row)
# Now, process the assignment file:
sheetname = getattr(parms, 'assignmentsheetname', '')
def setUp(self):
self.gsheet = GSheet()
def setUp(self): self.gsheet = GSheet()
