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 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 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 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 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 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))
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 upload(request):
# get parameters
expression = get_variable(request,
'expression',
def_value=DEFAULT_EXPRESSION)
casesensitive_matching = (get_variable(request,
'casesensitive_matching',
def_value='off') == 'on')
exclude_completions = (get_variable(request,
'exclude_completions',
def_value='off') == 'on')
pdf = request.FILES['datafile']
newpdf = pdf.temporary_file_path() + "-linked.pdf"
csv = pdf.temporary_file_path() + "-linked.csv"
params = [
reporting.settings.JAVA,
"-cp",
reporting.settings.DOC_MOD_LIB + "/*",
"nz.ac.waikato.cms.doc.HyperLinkGrades",
pdf.temporary_file_path(),
expression,
newpdf,
csv,
]
if casesensitive_matching:
params = params + ["--casesensitive", "true"]
if exclude_completions:
params = params + ["--nocompletions", "true"]
logger.info("Command: {0}".format(" ".join(params)))
retval = subprocess.call(params, )
if retval != 0:
msg = 'Failed to execute HyperLinkGrades! exit code: {1}, command: {0}'.format(
" ".join(params), retval)
logger.error(msg)
return create_error_response(request, 'hyperlinkgrades', msg)
if not os.path.exists(newpdf):
return create_error_response(request, 'hyperlinkgrades',
'Failed to generate output!')
write_last_parameter(request.user, 'hyperlinkgrades.last_expression',
expression)
write_last_parameter(request.user,
'hyperlinkgrades.last_casesensitive_matching',
casesensitive_matching)
write_last_parameter(request.user,
'hyperlinkgrades.last_exclude_completions',
exclude_completions)
data = list()
with open(newpdf, "rb") as f:
byte = f.read(1)
while byte != b"":
data.append(byte)
byte = f.read(1)
# clean up
if os.path.exists(newpdf):
os.remove(newpdf)
if os.path.exists(csv):
os.remove(csv)
response = HttpResponse(data, content_type='application/pdf')
response['Content-Disposition'] = 'attachment; filename="{0}"'.format(
os.path.basename(pdf.name))
return response
