def get_list_presentations_xlsx(sets):
"""
Lists the presentations planning with a set on each tab. Same formatting as before marketplace
:param sets:
:return:
"""
wb = Workbook()
wb.remove_sheet(wb.active)
wss = []
for pset in sets:
try:
track = pset.Track.Name
thead = pset.Track.Head.usermeta.get_nice_name()
except (AttributeError, ValueError):
track = 'No track'
thead = 'No track head'
tit = str(pset.id) + " (" + track + ")"
ws = wb.create_sheet(title=tit)
wss.append(ws)
ws['C1'] = timezone.localtime(
pset.DateTime).date().strftime("%A %d %B %Y")
ws['C3'] = "Track: " + track
ws['C4'] = "Track responsible staff: " + thead
ws['C5'] = "Exported on: " + timestamp()
ws['C6'] = f'Presentation room: {pset.PresentationRoom}'
if pset.PresentationRoom and pset.PresentationRoom.JoinLink:
ws['C6'].hyperlink = pset.PresentationRoom.JoinLink
ws['C6'].style = 'Hyperlink'
ws['C7'] = f'Assessment room: {pset.AssessmentRoom}'
if pset.AssessmentRoom and pset.AssessmentRoom.JoinLink:
ws['C7'].hyperlink = pset.AssessmentRoom.JoinLink
ws['C7'].style = 'Hyperlink'
if pset.Assessors.exists():
assessors = ''
for a in pset.Assessors.all():
assessors += a.usermeta.get_nice_name() + "; "
assessors = assessors[:-2]
else:
assessors = '-'
ws['C8'] = f'Assessors: {assessors}'
if pset.PresentationAssessors.exists():
passessors = ''
for a in pset.PresentationAssessors.all():
passessors += a.usermeta.get_nice_name() + "; "
passessors = passessors[:-2]
else:
passessors = '-'
ws['C9'] = f'Presentation Assessors: {passessors}'
ws['C10'] = ''
ws['C1'].style = 'Headline 2'
# courses span two columns
ws['G10'] = 'Courses'
ws.merge_cells('G9:H9')
ws['G10'].style = 'Headline 3'
ws['H10'].style = 'Headline 3'
# custom dimensions
ws.column_dimensions['C'].width = 25 # std name
ws.column_dimensions['E'].width = 25 # responsible name
ws.column_dimensions['F'].width = 25 # assistants
ws.column_dimensions['G'].width = 6 # codeBEP
ws.column_dimensions['H'].width = 6 # codeExt
ws.column_dimensions['I'].width = 50 # project name
header = [
"Type", "Stud. id", "Full Name", "Name", "Responsible teacher",
"Assistants", settings.COURSE_CODE_BEP, settings.COURSE_CODE_EXT,
"Project", "Time", "Duration"
]
ws.append(header)
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K']:
ws[col + '11'].style = 'Headline 3'
for slot in pset.timeslots.all():
if slot.CustomType:
row = [
slot.get_CustomType_display(), '', '', '', '', '', '', '',
''
]
else:
d = slot.Distribution
row = [
'', d.Student.usermeta.Studentnumber,
d.Student.usermeta.get_nice_fullname(),
d.Student.usermeta.get_nice_name(),
d.Proposal.ResponsibleStaff.usermeta.get_nice_name()
]
assistants = ''
for a in d.Proposal.Assistants.all():
assistants += a.usermeta.get_nice_name() + "; "
row.append(assistants[:-2])
row.append('x' if d.Student.usermeta.EnrolledBEP else '')
row.append('x' if d.Student.usermeta.EnrolledExt else '')
row.append(d.Proposal.Title)
row.append(
timezone.localtime(slot.DateTime).time().strftime("%H:%M"))
row.append(slot.Duration())
ws.append(row)
return save_virtual_workbook(wb)
def get_prv_type_xlsx(prv, des):
"""
:param prv:
:return:
"""
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws.title = prv.Name
ws['A1'] = f'Students grades from {settings.NAME_PRETTY} of {prv.Name} in {prv.TimeSlot.Name}'
ws['A1'].style = 'Headline 2'
ws['F1'] = "Exported on: " + timestamp()
ws['A2'] = 'Name'
ws['B2'] = 'Final Bachelor Project'
ws['A3'] = 'Time slot'
ws['B3'] = prv.TimeSlot.Name
ws['A4'] = 'Test'
ws['B4'] = prv.Name
ws['A5'] = 'Deadline'
ws['B5'] = prv.Deadline.strftime("%A %d %B %Y")
ws['A6'] = 'Opportunity'
ws['B6'] = ''
ws['A7'] = ''
ws['B7'] = ''
header = ["Student id", "Student name", "Hand-in", "Grade", "Comment", ]
aspects = prv.aspects.all().order_by('id')
for a in aspects:
header.append(a.Name)
ws.column_dimensions['B'].width = 25 # name
# ws.column_dimensions['C'].width = 25 # Project
# ws.column_dimensions['D'].width = 25 # responsible
ws.column_dimensions['E'].width = 25 # comment
ws.append(header)
cols = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
'V', 'W', 'X', 'Y', 'Z'][:len(header)]
for col in cols:
ws[col + '8'].style = 'Headline 3'
for d in des:
row = [d.Student.usermeta.Studentnumber, d.Student.usermeta.Fullname]
files = d.files.filter(Type=prv).order_by('-id')
if files.exists():
file = files.first()
row.append(timezone.localtime(file.TimeStamp).strftime("%Y-%m-%d %H:%M"))
try:
row.append(StaffResponse.StatusOptionsOsiris[file.staffresponse.Status])
row.append(file.staffresponse.Explanation)
for aspect in aspects:
try:
ar = file.staffresponse.aspects.get(Aspect=aspect)
row.append(ar.get_Grade_display())
except StaffResponseFileAspectResult.DoesNotExist:
row.append('-')
except StaffResponse.DoesNotExist:
row.append('-')
if files.count() > 1:
row.append('Warning, multiple files, last one shown.')
else: # no file
row.append('No file')
ws.append(row)
return save_virtual_workbook(wb)
def get_list_students_xlsx(des, typ):
"""
Export students and their grades.
List all students with their proposal and grades.
:param des:
:param typ:
:return:
"""
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws.title = "grades"
ws['A1'] = "Students grades from Bep Marketplace"
ws['A1'].style = 'Headline 2'
ws['F1'] = "Exported on: " + timestamp()
header = [
"Student id", "Student name", "Project", "Responsible teacher",
"Assistants", "ECTS", "Track"
]
for t in typ:
header.append(t.Name + " (" + str(t.Weight) + "%)")
header.append("Total")
header.append("Total rounded")
prvs = list(FileType.objects.filter(TimeSlot=get_timeslot()))
for prv in prvs:
header.append(prv.Name)
ws.column_dimensions['B'].width = 25 # name
ws.column_dimensions['C'].width = 25 # Project
ws.column_dimensions['D'].width = 25 # responsible
ws.column_dimensions['E'].width = 25 # assistants
ws.append(header)
cols = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
][:len(header)]
for col in cols:
ws[col + '2'].style = 'Headline 3'
cats = GradeCategory.objects.filter(TimeSlot=get_timeslot())
for d in des:
reslist = []
for c in cats:
try:
reslist.append(d.results.get(Category=c).Grade)
except:
reslist.append('-')
row = [
d.Student.usermeta.Studentnumber,
d.Student.usermeta.get_nice_fullname(), d.Proposal.Title,
d.Proposal.ResponsibleStaff.usermeta.get_nice_name()
]
assistants = ''
for a in d.Proposal.Assistants.all():
assistants += a.usermeta.get_nice_name() + "; "
row.append(assistants)
if d.Student.usermeta.EnrolledExt:
row.append(15)
else:
row.append(10)
row.append(d.Proposal.Track.Name)
for r in reslist:
row.append(r)
row.append(round(d.TotalGrade(), 2))
row.append(d.TotalGradeRounded())
for prv in prvs:
try:
row.append(
d.files.filter(
Type=prv).order_by('-id')[0].staffresponse.Status)
except IndexError:
row.append('no file')
except StaffResponse.DoesNotExist:
row.append('no grading')
ws.append(row)
# second tab with prv data
ws = wb.create_sheet(title='prv-aspects')
ws['A1'] = "Students grades from Bep Marketplace (only Aspects related to PRV)"
ws['A1'].style = 'Headline 2'
ws['F1'] = "Exported on: " + timestamp()
header = ["Student id", "Student name", "Project", "Responsible teacher"]
aspects = [] # list of PRV related aspects.
for cat in cats: # each category
for aspect in cat.aspects.all():
if 'prv' in aspect.Name.lower(
) or 'prv' in aspect.Description.lower():
aspects.append([cat, aspect])
header.append(aspect.Name)
ws.append(header)
cols = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N',
'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
][:len(header)]
for col in cols:
ws[col + '2'].style = 'Headline 3'
for colnr in range(1, len(header)): # set width
ws.column_dimensions[cols[colnr]].width = 25
for d in des:
reslist = []
for c, a in aspects:
try:
reslist.append(
d.results.get(Category=c).aspectresults.get(
CategoryAspect=a).Grade)
except:
reslist.append('-')
row = [
d.Student.usermeta.Studentnumber,
d.Student.usermeta.get_nice_fullname(), d.Proposal.Title,
d.Proposal.ResponsibleStaff.usermeta.get_nice_name()
]
for r in reslist:
row.append(r)
ws.append(row)
return save_virtual_workbook(wb)