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))