def database_bulk(request):
template = loader.get_template('dbbackend/import_bulk.html')
context = applist.template_context()
context['title'] = 'Bulk import'
context['email_notification'] = read_last_parameter(
request.user, 'dbbackend.database_bulk.email', '')
return HttpResponse(template.render(context, request))
def search_by_faculty(request):
# get parameters
response, schools = get_variable_with_error(request, 'supervisors', 'school', as_list=True)
if response is not None:
return response
# get year from earliest start date
max_years = get_max_years()
# configure template
template = loader.get_template('supervisors/search_by_faculty.html')
context = applist.template_context('supervisors')
context['schools'] = schools
context['departments'] = get_departments(schools)
context['max_years'] = int(max_years) if max_years is not None else YEARS_BACK
context['scholarships'] = get_scholarships()
context['last_programs'] = read_last_parameter(request.user, 'supervisors.search_by_paper.programs', PROGRAM_TYPES)
context['last_supervisor_type'] = read_last_parameter(request.user, 'supervisors.search_by_paper.supervisor_type', SUPERVISOR_TYPES)
context['last_study_type'] = read_last_parameter(request.user, 'supervisors.search_by_paper.study_type', STUDY_TYPES)
context['last_only_current'] = read_last_parameter(request.user, 'supervisors.search_by_paper.only_current', True)
context['last_min_months'] = read_last_parameter(request.user, 'supervisors.search_by_paper.min_months', -1)
context['last_scholarship'] = read_last_parameter(request.user, 'supervisors.search_by_paper.scholarship', DEFAULT_SCHOLARSHIP)
context['last_sort_column'] = read_last_parameter(request.user, 'supervisors.search_by_paper.sort_column', "supervisor")
context['last_sort_order'] = read_last_parameter(request.user, 'supervisors.search_by_paper.sort_order', "asc")
return HttpResponse(template.render(context, request))
def upload(request):
# get parameters
response, schools = get_variable_with_error(request, 'leave', 'school', as_list=True)
if response is not None:
return response
response, minimum_str = get_variable_with_error(request, 'leave', 'minimum', def_value="0")
if response is not None:
return response
minimum = int(minimum_str)
csv = request.FILES['datafile']
order = ['school', 'employee', 'manager', 'actual_balance', 'leave_accrued', 'future_leave_bookings', 'current_balance', 'current_allocated_balance']
header = dict()
header['school'] = 'Faculty/School'
header['employee'] = 'Employee'
header['manager'] = 'Manager'
header['actual_balance'] = 'Actual balance'
header['leave_accrued'] = 'Leave accrued'
header['future_leave_bookings'] = 'Future bookings'
header['current_balance'] = 'Current balance'
header['current_allocated_balance'] = 'Current allocated balance'
result = list()
try:
with open(csv.temporary_file_path(), encoding='ISO-8859-1') as csvfile:
reader = DictReader(csvfile)
reader.fieldnames = [name.lower().replace(" ", "_") for name in reader.fieldnames]
for row in reader:
school = row['main_clevel']
if school not in schools:
continue
leave_type = row['leave_type']
if leave_type != "AL": # TODO any other types of leave to add/include?
continue
rrow = dict()
rrow['school'] = school
rrow['employee'] = row['employee_name']
rrow['manager'] = row['manager']
rrow['actual_balance'] = float(row['current_allocated_balance']) - float(row['future_leave_bookings'])
rrow['current_balance'] = row['current_balance']
rrow['current_allocated_balance'] = row['current_allocated_balance']
rrow['leave_accrued'] = row['accrual']
rrow['future_leave_bookings'] = row['future_leave_bookings']
if int(rrow['actual_balance']) >= minimum:
result.append(rrow)
except Exception as ex:
traceback.print_exc(file=sys.stdout)
return create_error_response(request, 'leave', 'Failed to read uploaded CSV file: ' + str(ex))
# sort
result.sort(key=actual_balance_key, reverse=True)
# configure template
template = loader.get_template('leave/output.html')
context = applist.template_context('leave')
context['table'] = result
context['header'] = header
context['order'] = order
return HttpResponse(template.render(context, request))
def index(request):
# get all schools
cursor = connection.cursor()
cursor.execute("""
SELECT DISTINCT(owning_school_clevel)
FROM %s
ORDER BY owning_school_clevel ASC
""" % GradeResults._meta.db_table)
schools = []
for row in cursor.fetchall():
schools.append(row[0])
# get query date
query_date = None
rs = TableStatus.objects.all().filter(table=GradeResults._meta.db_table)
for r in rs:
query_date = r.timestamp
break
# configure template
template = loader.get_template('al/index.html')
context = applist.template_context('al')
context['schools'] = schools
context['query_date'] = query_date
context['last_school'] = read_last_parameter(request.user, 'al.school', "")
context['last_cutoff'] = read_last_parameter(request.user, 'al.cutoff', "")
context['last_min_age'] = read_last_parameter(request.user, 'al.min_age',
MIN_AGE)
context['last_min_gpa'] = read_last_parameter(request.user, 'al.min_gpa',
"")
context['last_min_points'] = read_last_parameter(request.user,
'al.min_points',
MIN_CREDITS)
return HttpResponse(template.render(context, request))
def database_studentdates(request):
template = loader.get_template('dbbackend/update_studentdates.html')
tablestatus = dbimport.get_tablestatus(StudentDates._meta.db_table)
context = applist.template_context()
context['title'] = 'Update student dates'
context['active_import'] = tablestatus is not None
context['email_notification'] = read_last_parameter(
request.user, 'dbbackend.database_studentdates.email', '')
return HttpResponse(template.render(context, request))
def database_associatedrole(request):
template = loader.get_template('dbbackend/import_associatedrole.html')
tablestatus = dbimport.get_tablestatus(AssociatedRole._meta.db_table)
context = applist.template_context()
context['title'] = 'Import associated role'
context['active_import'] = tablestatus is not None
context['email_notification'] = read_last_parameter(
request.user, 'dbbackend.database_associatedrole.email', '')
return HttpResponse(template.render(context, request))
def database_coursedefs(request):
years = []
for year in range(2003, date.today().year + 1):
years.append(year)
years.reverse()
tablestatus = dbimport.get_tablestatus(CourseDefs._meta.db_table)
template = loader.get_template('dbbackend/import_coursedefs.html')
context = applist.template_context()
context['title'] = 'Import course definitions'
context['years'] = years
context['active_import'] = tablestatus is not None
context['email_notification'] = read_last_parameter(
request.user, 'dbbackend.database_coursedefs.email', '')
return HttpResponse(template.render(context, request))
def database_tablestatus(request):
template = loader.get_template('dbbackend/table_status.html')
tables = []
for t in TableStatus.objects.all().order_by('table'):
row = dict()
row['table'] = t.table
row['timestamp'] = t.timestamp
row['message'] = t.message
tables.append(row)
context = applist.template_context()
context['title'] = 'Table status'
context['tables'] = tables
context[
'refresh_interval'] = reporting.settings.TABLE_STATUS_REFRESH_INTERVAL
return HttpResponse(template.render(context, request))
def index(request):
# configure template
template = loader.get_template('hyperlinkgrades/index.html')
context = applist.template_context('hyperlinkgrades')
context['last_expression'] = read_last_parameter(
request.user,
'hyperlinkgrades.expression',
def_value=DEFAULT_EXPRESSION)
context['last_casesensitive_matching'] = bool(
read_last_parameter(request.user,
'hyperlinkgrades.casesensitive_matching',
def_value='False'))
context['last_exclude_completions'] = bool(
read_last_parameter(request.user,
'hyperlinkgrades.exclude_completions',
def_value='True'))
return HttpResponse(template.render(context, request))
def index(request):
# get all schools
cursor = connection.cursor()
cursor.execute("""
SELECT DISTINCT(owning_school_clevel)
FROM %s
ORDER BY owning_school_clevel ASC
""" % GradeResults._meta.db_table)
schools = list()
for row in cursor.fetchall():
schools.append(row[0])
# configure template
template = loader.get_template('leave/index.html')
context = applist.template_context('leave')
context['schools'] = schools
context['minimum'] = MINIMUM_DAYS
return HttpResponse(template.render(context, request))
def search_by_student(request):
# get parameters
response, name = get_variable_with_error(request, 'supervisors', 'name')
if response is not None:
return response
if len(name) == 0:
return create_error_response(request, 'supervisors', 'No student name/id provided!')
if name.isdigit():
sql = """
select a.student_id, sd.program, a.student
from %s sd, %s a
where a.student_id = '%s'
and a.active = True
and a.student_id = sd.student_id
and a.program = sd.program
group by a.student_id, sd.program, a.student
order by a.student_id, sd.program, a.student
""" % (StudentDates._meta.db_table, AssociatedRole._meta.db_table, name)
else:
sql = """
select a.student_id, sd.program, a.student
from %s sd, %s a
where lower(a.student) like '%%%s%%'
and a.active = True
and a.student_id = sd.student_id
and a.program = sd.program
group by a.student_id, sd.program, a.student
order by a.student_id, sd.program, a.student
""" % (StudentDates._meta.db_table, AssociatedRole._meta.db_table, escape_quotes(name.lower()))
cursor = connection.cursor()
cursor.execute(sql)
results = list()
for row in cursor.fetchall():
data = dict()
data['student_id'] = row[0]
data['program'] = row[1]
data['student'] = row[2]
results.append(data)
# configure template
template = loader.get_template('supervisors/search_by_student.html')
context = applist.template_context('supervisors')
context['results'] = results
return HttpResponse(template.render(context, request))
def import_bulk(request):
# configure template
csv = request.FILES['datafile'].temporary_file_path()
email = get_variable(request, 'email_notification')
write_last_parameter(request.user, 'dbbackend.database_bulk.email', email)
if len(email) == 0:
email = None
msg = dbimport.import_bulk(csv, email)
template = loader.get_template('message.html')
context = applist.template_context()
if msg is None:
context['message'] = "Successful bulk import!"
else:
context['message'] = "Failed to bulk import: " + msg
context['back_link'] = "/"
context['back_text'] = "Start"
return HttpResponse(template.render(context, request))
def index(request):
# get all years
cursor = connection.cursor()
cursor.execute("""
SELECT DISTINCT(year)
FROM %s
ORDER BY year DESC
""" % GradeResults._meta.db_table)
years = []
for row in cursor.fetchall():
years.append(str(row[0]))
# get all schools
cursor = connection.cursor()
cursor.execute("""
SELECT DISTINCT(owning_school_clevel)
FROM %s
ORDER BY owning_school_clevel ASC
""" % GradeResults._meta.db_table)
schools = []
for row in cursor.fetchall():
schools.append(row[0])
# get query date
query_date = None
rs = TableStatus.objects.all().filter(table=GradeResults._meta.db_table)
for r in rs:
query_date = r.timestamp
break
# configure template
template = loader.get_template('lpp/index.html')
context = applist.template_context('lpp')
context['years'] = years
context['schools'] = schools
context['query_date'] = query_date
context['last_year'] = read_last_parameter(request.user, 'lpp.year', str(years[0]))
context['last_schools'] = read_last_parameter(request.user, 'lpp.schools', schools)
context['last_paper'] = read_last_parameter(request.user, 'lpp.paper', "")
context['last_type'] = read_last_parameter(request.user, 'lpp.type', DEFAULT_TYPE)
context['last_columns'] = read_last_parameter(request.user, 'lpp.columns', DEFAULT_COLUMNS)
context['last_filter1'] = read_last_parameter(request.user, 'lpp.filter1', "")
context['last_operator1'] = read_last_parameter(request.user, 'lpp.operator1', "lt")
context['last_value1'] = read_last_parameter(request.user, 'lpp.value1', "")
return HttpResponse(template.render(context, request))
def update_studentdates(request):
# configure template
email = get_variable(request, 'email_notification')
write_last_parameter(request.user, 'dbbackend.database_studentdates.email',
email)
if len(email) == 0:
email = None
t = threading.Thread(target=dbimport.queue_populate_student_dates,
args=(),
kwargs={'email': email})
t.setDaemon(True)
t.start()
template = loader.get_template('message.html')
context = applist.template_context()
context[
'message'] = "Started student dates recalculation... Check 'Table status' page for progress."
context['back_link'] = "/dbbackend/tablestatus"
context['back_text'] = "Table status"
return HttpResponse(template.render(context, request))
def search_by_supervisor(request):
# get parameters
response, name = get_variable_with_error(request, 'supervisors', 'name')
if response is not None:
return response
if len(name) == 0:
return create_error_response(request, 'supervisors', 'No supervisor name provided!')
# get year from earliest start date
max_years = get_max_years()
sql = """
select distinct(a.person)
from %s sd, %s a
where lower(a.person) like '%%%s%%'
and a.active = True
order by a.person
""" % (StudentDates._meta.db_table, AssociatedRole._meta.db_table, escape_quotes(name.lower()))
cursor = connection.cursor()
cursor.execute(sql)
results = list()
for row in cursor.fetchall():
data = dict()
data['supervisor'] = row[0]
results.append(data)
# configure template
template = loader.get_template('supervisors/search_by_supervisor.html')
context = applist.template_context('supervisors')
context['results'] = results
context['max_years'] = max_years
context['scholarships'] = get_scholarships()
context['last_programs'] = read_last_parameter(request.user, 'search_by_supervisor.programs', PROGRAM_TYPES)
context['last_supervisor_type'] = read_last_parameter(request.user, 'search_by_supervisor.supervisor_type', SUPERVISOR_TYPES)
context['last_study_type'] = read_last_parameter(request.user, 'search_by_supervisor.study_type', STUDY_TYPES)
context['last_only_current'] = read_last_parameter(request.user, 'search_by_supervisor.only_current', True)
context['last_min_months'] = read_last_parameter(request.user, 'search_by_supervisor.min_months', -1)
context['last_scholarship'] = read_last_parameter(request.user, 'search_by_supervisor.scholarship', DEFAULT_SCHOLARSHIP)
context['last_sort_column'] = read_last_parameter(request.user, 'search_by_supervisor.sort_column', "supervisor")
context['last_sort_order'] = read_last_parameter(request.user, 'search_by_supervisor.sort_order', "asc")
return HttpResponse(template.render(context, request))
def import_associatedrole(request):
# configure template
csv = create_temp_copy(request.FILES['datafile'].temporary_file_path())
enc = get_variable(request, 'encoding')
email = get_variable(request, 'email_notification')
write_last_parameter(request.user,
'dbbackend.database_associatedrole.email', email)
if len(email) == 0:
email = None
t = threading.Thread(target=dbimport.queue_import_associatedrole,
args=(csv, enc),
kwargs={'email': email})
t.setDaemon(True)
t.start()
template = loader.get_template('message.html')
context = applist.template_context()
context[
'message'] = "Started import of associate role... Check 'Table status' page for progress."
context['back_link'] = "/dbbackend/tablestatus"
context['back_text'] = "Table status"
return HttpResponse(template.render(context, request))
def import_graderesults(request):
# configure template
csv = create_temp_copy(request.FILES['datafile'].temporary_file_path())
year = int(get_variable(request, 'year', def_value='1900'))
isgzip = (get_variable(request, 'gzip', def_value='off') == 'on')
enc = get_variable(request, 'encoding')
email = get_variable(request, 'email_notification')
write_last_parameter(request.user, 'dbbackend.database_graderesults.email',
email)
if len(email) == 0:
email = None
t = threading.Thread(target=dbimport.queue_import_grade_results,
args=(year, csv, isgzip, enc),
kwargs={'email': email})
t.setDaemon(True)
t.start()
template = loader.get_template('message.html')
context = applist.template_context()
context[
'message'] = "Started import of grade results... Check 'Table status' page for progress."
context['back_link'] = "/dbbackend/tablestatus"
context['back_text'] = "Table status"
return HttpResponse(template.render(context, request))
def list_by_student(request):
# get parameters
response, studentid = get_variable_with_error(request, 'supervisors', 'student')
if response is not None:
return response
formattype = get_variable(request, 'format')
# supervisors
supervisors = list()
sname = None
for ar in AssociatedRole.objects.all().filter(student_id=studentid):
for g in GradeResults.objects.all().filter(student_id=studentid):
sname = g.name
break
data = dict()
data["studentid"] = studentid
data["studentname"] = sname
data["supervisor"] = ar.person
data["role"] = ar.role
if (data["role"] is None) or (len(data["role"]) == 0):
data["role"] = "N/A"
data["active"] = ar.active
supervisors.append(data)
# scholarships
scholarships = []
for sc in Scholarship.objects.all().filter(student_id=studentid).order_by('-year'):
data = dict()
data["studentid"] = studentid
data["studentname"] = sname
data["scholarship"] = sc.name
data["status"] = sc.status
data["decision"] = sc.decision
data["year"] = sc.year
scholarships.append(data)
# papers
papers = []
for gr in GradeResults.objects.all().filter(student_id=studentid).order_by('-occurrence_startdate'):
data = dict()
data["studentid"] = studentid
data["studentname"] = sname
data["program"] = gr.prog_abbr
data["school"] = gr.school_of_study_clevel
data["paper"] = gr.paper_master_code
data["start_date"] = gr.occurrence_startdate
data["classification"] = gr.classification
data["credits"] = gr.credits
data["student_credit_points"] = gr.student_credit_points
data["grade"] = gr.grade
data["grade_status"] = gr.grade_status
papers.append(data)
# CSV or HTML?
if formattype in ['csv', 'xls']:
content = OrderedDict()
# supervisors
data = list()
data.append([
'ID',
'Name',
'Supervisor',
'Role',
'Current',
])
for row in supervisors:
data.append([
row["studentid"],
row["studentname"],
row["supervisor"],
row["role"],
"Yes" if row["active"] else "No",
])
content['Supervisors'] = data
# scholarships
data = list()
data.append([
'ID',
'Name',
'Scholarship',
'Status',
'Decision',
'Year',
])
for row in scholarships:
data.append([
row["studentid"],
row["studentname"],
row["scholarship"],
row["status"],
row["decision"],
row["year"],
])
content['Scholarships'] = data
# papers
data = list()
data.append([
'ID',
'Name',
'Program',
'Faculty/School',
'Paper code',
'Start date',
'Classification',
'Credits',
'Student credit points',
'Grade',
'Grade status',
])
for row in papers:
data.append([
row["studentid"],
row["studentname"],
row["program"],
row["school"],
row["paper"],
row["start_date"],
row["classification"],
row["credits"],
row["student_credit_points"],
row["grade"],
row["grade_status"],
])
content['Academic history'] = data
book = excel.pe.Book(content)
response = excel.make_response(book, formattype, file_name="student-{0}.{1}".format(studentid, formattype))
return response
else:
template = loader.get_template('supervisors/list_by_student.html')
context = applist.template_context('supervisors')
context['supervisors'] = supervisors
context['scholarships'] = scholarships
context['papers'] = papers
form_utils.add_export_urls(request, context, "/supervisors/list-by-student", ['csv', 'xls'])
return HttpResponse(template.render(context, request))
def details(request):
# get parameters
response, studentid = get_variable_with_error(request, 'al', 'student')
if response is not None:
return response
formattype = get_variable(request, 'format')
# get name
sql = """
select name
from {1}
where student_id = '{0}'
order by year desc
limit 1
""".format(studentid, GradeResults._meta.db_table)
logger.debug(sql)
cursor = connection.cursor()
cursor.execute(sql)
sname = ""
for row2 in cursor.fetchall():
sname = row2[0]
break
# papers
papers = []
for gr in GradeResults.objects.all().filter(
student_id=studentid).order_by('-occurrence_startdate'):
data = dict()
data["studentid"] = studentid
data["studentname"] = sname
data["program"] = gr.prog_abbr
data["school"] = gr.school_of_study_clevel
data["paper"] = gr.paper_master_code
data["start_date"] = gr.occurrence_startdate
data["classification"] = gr.classification
data["credits"] = gr.credits
data["student_credit_points"] = gr.student_credit_points
data["grade"] = gr.grade
data["grade_status"] = gr.grade_status
papers.append(data)
# CSV or HTML?
if formattype in ['csv', 'xls']:
content = dict()
# papers
data = list()
data.append([
'ID',
'Name',
'Program',
'Faculty/School',
'Paper code',
'Start date',
'Classification',
'Credits',
'Student credit points',
'Grade',
'Grade status',
])
for row in papers:
data.append([
row["studentid"],
row["studentname"],
row["program"],
row["school"],
row["paper"],
row["start_date"],
row["classification"],
row["credits"],
row["student_credit_points"],
row["grade"],
row["grade_status"],
])
content['Academic history'] = data
book = excel.pe.Book(content)
response = excel.make_response(book,
formattype,
file_name="al-student-{0}.{1}".format(
studentid, formattype))
return response
else:
template = loader.get_template('al/details.html')
context = applist.template_context('al')
context['papers'] = papers
form_utils.add_export_urls(request, context, "/al/details",
['csv', 'xls'])
return HttpResponse(template.render(context, request))
def output(request):
# get parameters
response, school = get_variable_with_error(request, 'al', 'school')
if response is not None:
return response
response, cutoff = get_variable_with_error(request, 'al', 'cutoff')
if response is not None:
return response
cutoff_date = datetime.strptime(cutoff + "-01", "%Y-%m-%d")
min_age = get_variable(request, 'min_age')
if (min_age is None) or (len(min_age) == 0):
min_age = MIN_AGE
else:
min_gpa = float(min_age)
min_gpa = get_variable(request, 'min_gpa')
if (min_gpa is None) or (len(min_gpa) == 0):
min_gpa = 0.0
else:
min_gpa = float(min_gpa)
min_points = get_variable(request, 'min_points')
if (min_points is None) or (len(min_points) == 0):
min_points = MIN_CREDITS
else:
min_points = float(min_points)
formattype = get_variable(request, 'format')
# save parameters
write_last_parameter(request.user, 'al.school', school)
write_last_parameter(request.user, 'al.cutoff', cutoff)
write_last_parameter(request.user, 'al.min_age', min_age)
write_last_parameter(request.user, 'al.min_gpa', min_gpa)
write_last_parameter(request.user, 'al.min_points', min_points)
cursor = connection.cursor()
last_year = cutoff_date.year - 1
curr_year = cutoff_date.year
last_year_where = "year = {0}".format(last_year)
curr_year_where = "(year = {0}) and (issemesteracourse = 1)".format(
curr_year)
cols = "owning_school_clevel,paper_occurrence,credits,name,student_id,school_of_study_clevel,dateofbirth,prog_abbr,result_status,grade"
query = """
select
{7}
from
{6}
where
({0})
and (dateofbirth < date '{1}-01' - interval '{2} year')
and (credits_per_student >= {3})
and (programme_type_code in ('{4}'))
and (school_of_study_clevel = '{5}')
order by student_id
"""
# last year: query
sql = query.format(last_year_where, cutoff, min_age, min_points,
"', '".join(PROGRAM_CODES), school,
GradeResults._meta.db_table, cols)
logger.debug(sql)
cursor.execute(sql)
# last year: generate CSV
fd, outname = tempfile.mkstemp(suffix=".csv",
prefix="reporting-",
dir=tempfile_utils.gettempdir())
query_to_csv(cursor, cols, outname)
os_utils.close_file(fd)
# last year: calculate GPA
last_header = []
last_body = []
compute_gpa(outname, school, cutoff_date, min_gpa, last_header, last_body)
# current year: query
sql = query.format(curr_year_where, cutoff, min_age, min_points,
"', '".join(PROGRAM_CODES), school,
GradeResults._meta.db_table, cols)
logger.debug(sql)
cursor.execute(sql)
# current year: generate CSV
fd, outname = tempfile.mkstemp(suffix=".csv",
prefix="reporting-",
dir=tempfile_utils.gettempdir())
query_to_csv(cursor, cols, outname)
# current year: calculate GPA
curr_header = []
curr_body = []
compute_gpa(outname, school, cutoff_date, min_gpa, curr_header, curr_body)
# students have to be good in both years
final_header = last_header[:]
final_body = []
id_idx = last_header.index(HEADER_ID)
gpa_idx = last_header.index(HEADER_GPA)
points_idx = last_header.index(HEADER_POINTS)
final_header.remove(HEADER_GPA)
final_header.remove(HEADER_POINTS)
last_ids = [row[id_idx] for row in last_body]
curr_ids = [row[id_idx] for row in curr_body]
both = set(last_ids).intersection(set(curr_ids))
for row in curr_body:
if row[id_idx] in both:
arow = []
for i, c in enumerate(row):
if (i == gpa_idx) or (i == points_idx):
continue
arow.append(c)
final_body.append(arow)
# generate output
if formattype in ["csv", "xls"]:
data = OrderedDict()
data['AL - Recommendations - {0}'.format(school)] = [final_header
] + final_body
data['AL - {1} - {0}'.format(school,
curr_year)] = [curr_header] + curr_body
data['AL - {1} - {0}'.format(school,
last_year)] = [last_header] + last_body
book = excel.pe.Book(data)
response = excel.make_response(book,
formattype,
file_name="al-{0}.{1}".format(
cutoff, formattype))
return response
else:
template = loader.get_template('al/list.html')
context = applist.template_context('al')
context['header'] = final_header
context['body'] = final_body
context['curr_year'] = curr_year
context['curr_header'] = curr_header
context['curr_body'] = curr_body
context['last_year'] = last_year
context['last_header'] = last_header
context['last_body'] = last_body
context['school'] = school
form_utils.add_export_urls(request, context, "/al/output",
['csv', 'xls'])
response = HttpResponse(template.render(context, request))
return response
def index(request):
# configure template
template = loader.get_template('hr_forms/index.html')
context = applist.template_context('hr_forms')
return HttpResponse(template.render(context, request))
def output(request):
# get parameters
response, year = get_variable_with_error(request, 'lpp', 'year')
if response is not None:
return response
response, school = get_variable_with_error(request, 'lpp', 'school', as_list=True)
if response is not None:
return response
response, ptype = get_variable_with_error(request, 'lpp', 'type')
if response is not None:
return response
if ptype not in ["master", "occurrence"]:
return create_error_response(request, 'lpp', 'Unsupported type: {0}'.format(ptype))
response, paper = get_variable_with_error(request, 'lpp', 'paper')
if paper is None:
paper = ''
response, columns = get_variable_with_error(request, 'lpp', 'columns', as_list=True)
if response is not None:
return response
response, filter1 = get_variable_with_error(request, 'lpp', 'filter1')
if filter1 is None:
filter1 = ''
response, operator1 = get_variable_with_error(request, 'lpp', 'operator1')
if operator1 is None:
operator1 = 'lt'
response, value1 = get_variable_with_error(request, 'lpp', 'value1')
if (value1 is None) or (len(value1) == 0):
value1 = '0'
formattype = get_variable(request, 'format')
# save parameters
write_last_parameter(request.user, 'lpp.year', str(year))
write_last_parameter(request.user, 'lpp.schools', school)
write_last_parameter(request.user, 'lpp.type', ptype)
write_last_parameter(request.user, 'lpp.paper', paper)
write_last_parameter(request.user, 'lpp.columns', columns)
write_last_parameter(request.user, 'lpp.filter1', filter1)
write_last_parameter(request.user, 'lpp.operator1', operator1)
write_last_parameter(request.user, 'lpp.value1', value1)
# add paper code
columns.append("Paper Code")
# load data from DB
if len(paper) > 0:
if ptype == "master":
where = "AND paper_master_code LIKE '" + paper + "' "
else:
where = "AND paper_occurrence LIKE '" + paper + "' "
elif len(school) > 0:
where = "AND owning_school_clevel IN ('" + "','".join(school) + "')"
else:
where = ""
cols = "owning_school_clevel,paper_master_code,paper_occurrence,prog_abbr,grade,isdomestic,result_status"
cursor = connection.cursor()
sql = """
SELECT
{0}
FROM
{3}
WHERE
year = {1}
{2}
ORDER BY
paper_occurrence ASC
""".format(cols, year, where, GradeResults._meta.db_table)
logger.debug(sql)
cursor.execute(sql)
# generate CSV
fd, outname = tempfile.mkstemp(suffix=".csv", prefix="reporting-", dir=tempfile_utils.gettempdir())
logger.info("Generating CSV: {0}".format(outname))
with open(outname, 'w') as outfile:
writer = csv.writer(outfile, quoting=csv.QUOTE_NONNUMERIC)
writer.writerow(cols.split(","))
for row in cursor.fetchall():
writer.writerow(row)
outfile.flush()
logger.info("Generated CSV ({0}) exists: ".format(outname, os.path.isfile(outname)))
# file names
genname = outname.replace(".csv", "-gen.csv")
stdoutname = outname.replace(".csv", "-stdout.csv")
stderrname = outname.replace(".csv", "-stderr.txt")
tmpfiles = [outname, genname, stdoutname, stderrname]
# file descriptors
stdoutfile = open(stdoutname, 'wb')
stderrfile = open(stderrname, 'wb')
tmpfds = [fd, stdoutfile, stderrfile]
# call LPP
if reporting.settings.PERLLIB is not None:
env = dict(os.environ)
env['PERL5LIB'] = reporting.settings.PERLLIB
else:
env = None
params = [
reporting.settings.PERL,
reporting.settings.LPP_SCRIPT,
outname,
genname,
]
if ptype == "occurrence":
params.append("-o")
logger.info("Command: {0}".format(" ".join(params)))
retval = subprocess.call(
params,
stdout=stdoutfile,
stderr=stderrfile,
env=env,
)
os_utils.close_files(tmpfds)
if not os.path.isfile(genname):
msg = 'Failed to execute lpp! exit code: {1}, command: {0}'.format(" ".join(params), retval)
logger.error(msg)
os_utils.remove_files(tmpfiles)
return create_error_response(request, 'lpp', msg)
# read data
header = []
body = []
display = []
filter1_idx = -1
value1_num = float(value1)
with open(genname, 'r') as infile:
reader = csv.reader(infile)
first = True
for row in reader:
if first:
for i, c in enumerate(row):
if c in columns:
display.append(i)
if c == filter1:
filter1_idx = i
header = [row[i] for i in display]
first = False
else:
arow = [row[i] for i in display]
if filter1_idx > -1:
if len(row[filter1_idx]) == 0:
continue
try:
curr1 = float(row[filter1_idx].replace('%', ''))
except Exception as e:
continue
if (operator1 == "lt") and (curr1 >= value1_num):
continue
if (operator1 == "le") and (curr1 > value1_num):
continue
if (operator1 == "eq") and (curr1 != value1_num):
continue
if (operator1 == "ge") and (curr1 < value1_num):
continue
if (operator1 == "gt") and (curr1 <= value1_num):
continue
body.append(arow)
# generate output
if formattype in ["csv", "xls"]:
book = excel.pe.Book({'LPP': [header] + body})
response = excel.make_response(book, formattype, file_name="lpp-{0}.{1}".format(year, formattype))
return response
else:
template = loader.get_template('lpp/list.html')
context = applist.template_context('lpp')
context['header'] = header
context['body'] = body
form_utils.add_export_urls(request, context, "/lpp/output", ['csv', 'xls'])
response = HttpResponse(template.render(context, request))
# remove temp files again
os_utils.remove_files(tmpfiles)
return response
def disclaimer(request):
template = loader.get_template('lpp/disclaimer.html')
context = applist.template_context('lpp')
context['disclaimer_title'] = 'Information & Disclaimer'
return HttpResponse(template.render(context, request))
def index(request):
# configure template
template = loader.get_template('supervisors/index.html')
context = applist.template_context('supervisors')
context['schools'] = get_schools()
return HttpResponse(template.render(context, request))
def list_by_supervisor(request):
# get parameters
response, name = get_variable_with_error(request, 'supervisors', 'name')
if response is not None:
return response
years_back_str = get_variable(request, 'years_back', def_value=str(get_max_years()))
years_back = int(years_back_str)
start_year = date.today().year - years_back
programs = get_variable(request, 'program', as_list=True, def_value=PROGRAM_TYPES)
if REPORTING_OPTIONS['supervisor.only_phd']:
programs = ['DP']
supervisor_type = get_variable(request, 'supervisor_type', as_list=True, def_value=SUPERVISOR_TYPES)
study_type = get_variable(request, 'study_type', as_list=True, def_value=STUDY_TYPES)
only_current = get_variable(request, 'only_current', def_value="off") == "on"
min_months = float(get_variable(request, 'min_months', def_value="-1", blank=False))
scholarship = str(get_variable(request, 'scholarship', def_value="NO_SCHOLARSHIP"))
sort_column = get_variable(request, 'sort_column', def_value="supervisor")
sort_order = get_variable(request, 'sort_order', def_value="asc")
formattype = get_variable(request, 'format')
# save parameters
write_last_parameter(request.user, 'search_by_supervisor.programs', programs)
write_last_parameter(request.user, 'search_by_supervisor.supervisor_type', supervisor_type)
write_last_parameter(request.user, 'search_by_supervisor.study_type', study_type)
write_last_parameter(request.user, 'search_by_supervisor.only_current', only_current)
write_last_parameter(request.user, 'search_by_supervisor.min_months', min_months)
write_last_parameter(request.user, 'search_by_supervisor.scholarship', scholarship)
write_last_parameter(request.user, 'search_by_supervisor.sort_column', sort_column)
write_last_parameter(request.user, 'search_by_supervisor.sort_order', sort_order)
sql_active = " and a.active = True " if only_current else ""
sql = """
select sd.school, sd.department, a.person, a.student_id, sd.program
from %s sd, %s a
where sd.student_id = a.student_id
and sd.program = a.program
and a.person = '%s'
and sd.start_date >= '%s-01-01'
%s
and sd.months >= %f
group by sd.school, sd.department, a.person, a.student_id, sd.program
order by sd.school, sd.department, a.person, a.student_id, sd.program
""" % (StudentDates._meta.db_table, AssociatedRole._meta.db_table, escape_quotes(name), start_year, sql_active, min_months)
cursor = connection.cursor()
cursor.execute(sql)
result = dict()
for row in cursor.fetchall():
try:
if len(row[0]) < 1:
logger.warning("empty school: " + str(row))
continue
if row[4] not in programs:
continue
add_student(data=result, school=row[0], department=row[1], supervisor=row[2], studentid=row[3],
program=row[4], supervisor_type=supervisor_type, study_type=study_type,
only_current=only_current, scholarship=scholarship)
except Exception as ex:
logger.exception("row=" + str(row))
# sort
for school in result:
school_data = result[school]
school_data_sorted = sorted(school_data, key=lambda row: row[sort_column], reverse=(sort_order == "desc"))
result[school] = school_data_sorted
# CSV or HTML?
# generate output
if formattype in ["csv", "xls"]:
data = list()
data.append([
'Faculty/School',
'Department',
'Paper',
'Supervisor',
'Program',
'ID',
'Name',
'Start date',
'End date',
'Months',
'Full time',
'Chief supervisor',
'Status',
'Scholarship (' + scholarship + ')',
])
for school in result:
for row in result[school]:
data.append([
school,
row['department'],
row['paper'],
row['supervisor'],
row['program'],
row['id'],
row['name'],
row['start_date'],
row['end_date'],
row['months'],
row['full_time'],
row['chief_supervisor'],
row['status'],
row['scholarship'],
])
book = excel.pe.Book({'Supervisor': data})
response = excel.make_response(book, formattype, file_name="supervisor-{0}.{1}".format(date.today().strftime("%Y-%m-%d"), formattype))
return response
else:
template = loader.get_template('supervisors/list_by_supervisor.html')
context = applist.template_context('supervisors')
context['results'] = result
context['scholarship'] = scholarship
context['show_scholarship'] = scholarship != NO_SCHOLARSHIP
form_utils.add_export_urls(request, context, "/supervisors/list-by-supervisor", ['csv', 'xls'])
return HttpResponse(template.render(context, request))
