def find_emplid(userid): """ Find emplid from userid by looking at email addresses: incorrect in general but works for the few people needed here. """ db = SIMSConn() db.execute("""SELECT emplid FROM ps_email_addresses WHERE email_addr=%s""", (userid+'@sfu.ca',)) return db.fetchone()[0]
def find_emplid(userid): """ Find emplid from userid by looking at email addresses: incorrect in general but works for the few people needed here. """ db = SIMSConn() db.execute("""SELECT emplid FROM ps_email_addresses WHERE email_addr=%s""", (userid+'@sfu.ca',)) return db.fetchone()[0]
def guess_userid(emplid): """ Find userid from emplid by looking at email addresses: incorrect in general but works enough for some test data. """ db = SIMSConn() db.execute("""SELECT email_addr FROM ps_email_addresses WHERE emplid=%s AND email_addr LIKE '%%@sfu.ca %%'""", (emplid,)) row = db.fetchone() if row: email = row[0] return email[:-7]
def guess_userid(emplid): """ Find userid from emplid by looking at email addresses: incorrect in general but works enough for some test data. """ db = SIMSConn() db.execute("""SELECT email_addr FROM ps_email_addresses WHERE emplid=%s AND email_addr LIKE '%%@sfu.ca %%'""", (emplid,)) row = db.fetchone() if row: email = row[0] return email[:-7]
def main(): db = SIMSConn() out = csv.writer(open(acad_plan + ".csv", 'wb')) out.writerow(['Emplid', 'Name', 'Attempted Cred', 'As Of', 'CGPA']) query = "SELECT ap.emplid " \ "FROM ps_acad_plan ap " \ "WHERE ap.acad_plan=%s " \ "GROUP BY ap.emplid ORDER BY ap.emplid" db.execute(query, (acad_plan, )) all_emplids = [row[0] for row in list(db)] #all_emplids = all_emplids[:30] count = 0 for emplid in all_emplids: query = "SELECT ap.stdnt_car_nbr " \ "FROM ps_acad_plan ap, " + max_effdt + " as maxeffdt " \ "WHERE maxeffdt.emplid=ap.emplid " \ " AND maxeffdt.stdnt_car_nbr=ap.stdnt_car_nbr" \ " AND ap.effdt=maxeffdt.effdt AND ap.acad_plan=%s " \ "GROUP BY ap.stdnt_car_nbr" db.execute(query, (emplid, acad_plan)) #print emplid, list(db) for (car_nbr, ) in list(db): query = "SELECT ct.cum_gpa, ct.strm, ct.tot_taken_gpa, pd.name " \ "FROM ps_stdnt_car_term ct, ps_personal_data pd " \ "WHERE ct.emplid=pd.emplid AND ct.emplid=%s " \ " AND stdnt_car_nbr=%s " \ " AND tot_taken_gpa>0 " \ "ORDER BY strm DESC FETCH FIRST 1 ROWS ONLY" db.execute(query, (emplid, car_nbr)) row = db.fetchone() #print emplid, car_nbr, row if not row: continue gpa, strm, taken, name = row out.writerow([emplid, name, taken, strm, gpa]) count += 1 out.writerow( ['Average', None, None, None, "=AVERAGE(E2:E%i)" % (count + 1)])
def get_unit(acad_org, create=False): """ Get the corresponding Unit """ # there are some inconsistent acad_org values: normalize. if acad_org == 'GERON': acad_org = 'GERONTOL' elif acad_org == 'GEOG': acad_org = 'GEOGRAPH' elif acad_org == 'BUS': acad_org = 'BUS ADMIN' elif acad_org == 'HUM': acad_org = 'HUMANITIES' elif acad_org == 'EVSC': acad_org = 'ENVIRO SCI' try: unit = Unit.objects.get(acad_org=acad_org) except Unit.DoesNotExist: db = SIMSConn() db.execute( "SELECT descrformal FROM ps_acad_org_tbl " "WHERE eff_status='A' and acad_org=%s", (acad_org, )) name, = db.fetchone() if acad_org == 'COMP SCI': # for test/demo imports label = 'CMPT' elif acad_org == 'ENG SCI': # for test/demo imports label = 'ENSC' elif acad_org == 'ENVIRONMEN': # for test/demo imports label = 'FENV' elif acad_org == 'DEAN GRAD': # for test/demo imports label = 'GRAD' else: label = acad_org[:4].strip() if create: unit = Unit(acad_org=acad_org, label=label, name=name, parent=None) unit.save() else: raise KeyError("Unknown unit: acad_org=%s, label~=%s, name~=%s." % (acad_org, label, name)) return unit
def main(): db = SIMSConn() out = csv.writer(open(acad_plan+".csv", 'wb')) out.writerow(['Emplid', 'Name', 'Attempted Cred', 'As Of', 'CGPA']) query = "SELECT ap.emplid " \ "FROM ps_acad_plan ap " \ "WHERE ap.acad_plan=%s " \ "GROUP BY ap.emplid ORDER BY ap.emplid" db.execute(query, (acad_plan,)) all_emplids = [row[0] for row in list(db)] #all_emplids = all_emplids[:30] count = 0 for emplid in all_emplids: query = "SELECT ap.stdnt_car_nbr " \ "FROM ps_acad_plan ap, " + max_effdt + " as maxeffdt " \ "WHERE maxeffdt.emplid=ap.emplid " \ " AND maxeffdt.stdnt_car_nbr=ap.stdnt_car_nbr" \ " AND ap.effdt=maxeffdt.effdt AND ap.acad_plan=%s " \ "GROUP BY ap.stdnt_car_nbr" db.execute(query, (emplid,acad_plan)) #print emplid, list(db) for (car_nbr,) in list(db): query = "SELECT ct.cum_gpa, ct.strm, ct.tot_taken_gpa, pd.name " \ "FROM ps_stdnt_car_term ct, ps_personal_data pd " \ "WHERE ct.emplid=pd.emplid AND ct.emplid=%s " \ " AND stdnt_car_nbr=%s " \ " AND tot_taken_gpa>0 " \ "ORDER BY strm DESC FETCH FIRST 1 ROWS ONLY" db.execute(query, (emplid,car_nbr)) row = db.fetchone() #print emplid, car_nbr, row if not row: continue gpa, strm, taken, name = row out.writerow([emplid, name, taken, strm, gpa]) count += 1 out.writerow(['Average', None, None, None, "=AVERAGE(E2:E%i)"%(count+1)])
def get_unit(acad_org, create=False): """ Get the corresponding Unit """ # there are some inconsistent acad_org values: normalize. if acad_org == 'GERON': acad_org = 'GERONTOL' elif acad_org == 'GEOG': acad_org = 'GEOGRAPH' elif acad_org == 'BUS': acad_org = 'BUS ADMIN' elif acad_org == 'HUM': acad_org = 'HUMANITIES' elif acad_org == 'EVSC': acad_org = 'ENVIRO SCI' try: unit = Unit.objects.get(acad_org=acad_org) except Unit.DoesNotExist: db = SIMSConn() db.execute("SELECT descrformal FROM ps_acad_org_tbl " "WHERE eff_status='A' and acad_org=%s", (acad_org,)) name, = db.fetchone() if acad_org == 'COMP SCI': # for test/demo imports label = 'CMPT' elif acad_org == 'ENG SCI': # for test/demo imports label = 'ENSC' elif acad_org == 'ENVIRONMEN': # for test/demo imports label = 'FENV' elif acad_org == 'DEAN GRAD': # for test/demo imports label = 'GRAD' else: label = acad_org[:4].strip() if create: unit = Unit(acad_org=acad_org, label=label, name=name, parent=None) unit.save() else: raise KeyError("Unknown unit: acad_org=%s, label~=%s, name~=%s." % (acad_org, label, name)) return unit