def locker_tenants(credentials):
gc = get_drive_conn(credentials)
try:
_locker_rentals = sheet2lod(get_spreadsheet_fromusr(
"Locker_Rentals",
gc=gc
))
contact_form = sheet2dict(get_spreadsheet_fromusr(
"ECESS 2015W Student Contact Form (Responses)",
gc=gc
), "Google_Email")
except gspread.SpreadsheetNotFound:
return "Unauthorized" # TODO
l = []
for entry in _locker_rentals:
locker_number = entry["Locker_Number"]
try:
legal_name = contact_form[entry["Google_Email"]]["Full_Legal_Name"]
except KeyError:
continue
if locker_number:
l.append(
"{} {}".format(str(locker_number).zfill(3),
legal_name)
)
return "\n<br>".join(sorted(l))
def invoices_to_send(credentials):
gc = get_drive_conn(credentials)
try:
locker_rentals = sheet2lod(get_spreadsheet_fromusr(
"Locker_Rentals",
gc=gc
))
locker_form = sheet2dict(get_spreadsheet_fromusr(
"[ECESS] MCLD Locker Rental 2015W1 (Responses)",
gc=gc
), "Google_Email")
contact_form = sheet2dict(get_spreadsheet_fromusr(
"ECESS 2015W Student Contact Form (Responses)",
gc=gc
), "Google_Email")
except gspread.SpreadsheetNotFound:
return "Unauthorized" # TODO return a 401 here
l = []
for entry in locker_rentals:
gmail = entry["Google_Email"]
form_entry = locker_form.get(gmail)
if form_entry is None:
# l.append("Could not find {} in rental form responses.".format(gmail))
continue
payment_type = form_entry["Payment_Method"]
if payment_type == "PayPal_Invoice" and entry["Paid"] == "Not_Paid":
l.append(
"Email_Address: {}, Google_Email: {}".format(
contact_form[gmail]["Email_Address"],
contact_form[gmail]["Google_Email"]
))
return "\n<br>".join(l)
def get_db(cache_period=180):
# top = flask._app_ctx_stack
# if not hasattr(top, 'drive_conn'):
# top.drive_conn = time(), get_drive_conn()
#
# t, gc = top.drive_conn
# if time() - t > cache_period:
# top.drive_conn = time(), get_drive_conn()
#
# return top.drive_conn[1]
return get_drive_conn()
def _admin_seattle_review(credentials, spreadsheet="Seattle Trip 2015 Sign-Up (Responses)"):
gc = get_drive_conn(credentials)
try:
seattle_form = sheet2dict(get_spreadsheet_fromusr(
spreadsheet,
gc=gc
), "Google_Email")
contact_form = sheet2dict(get_spreadsheet_fromusr(
"ECESS 2015W Student Contact Form (Responses)",
gc=gc
), "Google_Email")
except gspread.SpreadsheetNotFound:
return "Unauthorized" # TODO return a 401 here
l = []
stats = {
"Dept": defaultdict(int),
"Year": defaultdict(int)
}
names = []
for gmail, entry in seattle_form.items():
try:
usr = contact_form[gmail]
except KeyError:
print("Invalid email: {}".format(gmail))
continue
l.append((entry, usr))
stats["Dept"]["{}_{}".format(usr["Dept"], usr["Program"])] += 1
stats["Year"][usr["Academic_Year"]] += 1
names.append(usr["Full_Legal_Name"])
res = [json.dumps(stats, indent=4).replace(
"\n", "<br>")]
res += ["<br>".join(json.dumps(d, indent=4).replace(
"\n", "<br>")
for d in
t)
for t in l]
res += ["<br>".join(names)]
return "\n<br><br><br>".join(res)
def locker_queue(credentials):
gc = get_drive_conn(credentials)
try:
_locker_rentals = sheet2lod(get_spreadsheet_fromusr(
"Locker_Rentals",
gc=gc
))
locker_rentals = defaultdict(list)
for lr in _locker_rentals:
locker_rentals[lr["Google_Email"].lower()].append(lr)
locker_form = sheet2lod(get_spreadsheet_fromusr(
"[ECESS] MCLD Locker Rental 2015W1 (Responses)",
gc=gc
))
contact_form = sheet2dict(get_spreadsheet_fromusr(
"ECESS 2015W Student Contact Form (Responses)",
gc=gc
), "Google_Email")
except gspread.SpreadsheetNotFound:
return "Unauthorized" # TODO return a 401 here
d = {
"pre_150_ece_renewal": [],
"ece": [],
"non_ece": [],
"no_contact_email": [],
"unpaid_over_4d_no_email": []
}
for i, entry in enumerate(locker_form):
gmail = entry["Google_Email"].lower()
try:
email = contact_form[gmail]["Email_Address"]
except KeyError:
email = None
dln = entry["Desired_Locker_Number"]
# TODO XXX Handle multiple terms
if gmail not in locker_rentals:
contact_user = contact_form.get(gmail)
if contact_user is None:
d["no_contact_email"].append(gmail)
continue
if contact_form[gmail]["Dept"] == "ECE":
if i < 150 and entry["Renewal"] == "Yes":
d["pre_150_ece_renewal"].append("{} {}".format(gmail, dln))
else:
d["ece"].append(gmail)
else:
d["non_ece"].append(gmail)
else:
for lr_entry in locker_rentals[gmail]:
if lr_entry["Warning_Email_Sent"] != "Yes" \
and lr_entry["Paid"] == "Not_Paid":
try:
parsed = arrow.get(entry["Timestamp"], "M/DD/YYYY HH:mm:ss")
diff = (arrow.utcnow() - parsed).days
print(diff)
if diff >= 4:
d["unpaid_over_4d_no_email"]\
.append("{}".format(email))
except arrow.parser.ParserError as e:
print("{}: {}".format(entry["Timestamp"], e))
l = []
l.append("<br><br>== Pre-150 ECE Renewals ==<br>")
l.extend(d["pre_150_ece_renewal"])
l.append("<br><br>== ECE students ==<br>")
l.extend(d["ece"])
l.append("<br><br>== Non-ECE Students ==<br>")
l.extend(d["non_ece"])
l.append("<br><br>== These students' Google_Emails are not on the Contact sheet, i.e., the"
"y have not filled out the Contact form ==<br>")
l.extend(d["no_contact_email"])
l.append("<br><br>== Warning Emails to send ==<br>")
l.extend(d["unpaid_over_4d_no_email"])
return "\n<br>".join(l)
