Developing a Course Evaluation Tool

1. Developing the Resit Registration Tool- a Portal Module Developing aCourse EvaluationTool Dr Malcolm Murray E-Learning & Web Services

2. Caveat • For those who weren’t at my presentation: • Three “sorry’s”: • Sorry I am not Heather Natour (confused? I was - check the Programme) • Sorry I changed topic (but this is the better presentation) • Sorry that this presentation hasn’t had the “agent orange” treatment 

3. Thought for the Day… • RESIT REGISTRATION > INTRO • “Thinking about the use of computers in • education does not mean thinking about • computers. • It means thinking about education.” • Ellis (1974, p42) • Ellis, AB (1974) The Use and Misuse of Computers in Education. McGraw Hill, New York, USA. • “Thinking about developing a Building Block application does not (just) mean thinking about programming. • It means thinking about the process”

4. Learning Outcomes • RESIT REGISTRATION > LEARNING OUTCOMES • Portal Module Case Study • Insight into the development process • Example of using the Portal Module Framework • Escaping from the tab (but remembering how to get back) • Strategies for storing custom information • Leveraging Portal Roles to add functionality • Prompt a discussion of how best to connect to external databases

5. Resit Registration • RESIT REGISTRATION > THE PROCESS • Client: University Student Planning & Assessment Authority • Previously registration used a Notice Board and individual letters mailed to students with resits • High Stakes • Confidential Information • Volatile Data • appeal and concession processes • Students off-campus

6. Overview - Students • RESIT REGISTRATION > THE PROCESS > STUDENTS • In July undergraduate resit exam lists are published • Students listed need to choose which exams to resit • Complications: • Second years may not be allowed to resit everything and should seek advice! • Foreign students can apply to sit their resits in their home country • Some “resits” may actually be first attempts if the student was ill earlier in the year – if so these exams are free

7. Overview - Staff • RESIT REGISTRATION > THE PROCESS >STUDENTS • Staff need to be able to • grant concessions • be Alerted to applications to sit exams abroad • correct any errors in the data • confirm and process payments • predict exam attendance numbers

8. Data Sources blackboard.data.user.User malcolm.Student malcolm.Examinationmalcolm.ExamSummary • RESIT REGISTRATION > THE PROCESS > DATA User Data Exam Data Progress Data READ READ READ/WRITE

9. Banner Query • RESIT REGISTRATION > THE PROCESS > DATA SELECT S.STUDENT_ID, SADD.PERM_SPRADDR_COUNTRY, S.CLASS_CODE, S.PROGRAMME_CODE, S.SURNAME, S.FIRST_NAME, SA.ANONYMOUS_NUMBER, P.FACULTY_CODE, P.LEVEL_CODE, S.COLLEGE_CODE, SM.SUBJECT_CODE, SM.COURSE_CODE, AD.EXAM_REFERENCE, MD.CREDIT_HIGH, SM.TITLE, SATURN.SGBSTDN.SGBSTDN_ASTD_CODE FROM BANINST1.V_STUDENT S, BANINST1.V_STUDENT_ANONYMOUS_CODE SA, BANINST1.V_STUDENT_MARKS SM, BANINST1.V_ASSESSMENT_DETAILS AD, BANINST1.V_STUDENT_ADDRESS SADD, BANINST1.V_PROGRAMME P, BANINST1.V_MODULE_DETAILS MD, SATURN.SGBSTDN WHERE S.PIDM = SM.PIDM AND S.TERM_CODE = SM.TERM_CODE AND S.PIDM = SA.PIDM AND S.TERM_CODE = SA.TERM_CODE AND SA.TERM_CODE = SM.TERM_CODE AND AD.CRN = SM.CRN AND AD.TERM_CODE = SM.TERM_CODE AND SA.TERM_CODE = AD.TERM_CODE AND S.TERM_CODE = AD.TERM_CODE AND S.PIDM = SADD.PIDM AND SA.PIDM = SADD.PIDM AND AD.TERM_CODE = P.TERM_CODE AND SM.TERM_CODE = P.TERM_CODE AND SA.TERM_CODE = P.TERM_CODE AND S.TERM_CODE = P.TERM_CODE AND S.PROGRAMME_CODE = P.PROGRAMME_CODE AND P.TERM_CODE = SATURN.SGBSTDN.SGBSTDN_TERM_CODE_EFF AND SM.TERM_CODE = SATURN.SGBSTDN.SGBSTDN_TERM_CODE_EFF AND AD.TERM_CODE = SATURN.SGBSTDN.SGBSTDN_TERM_CODE_EFF AND SA.TERM_CODE = SATURN.SGBSTDN.SGBSTDN_TERM_CODE_EFF AND S.TERM_CODE = SATURN.SGBSTDN.SGBSTDN_TERM_CODE_EFF AND SADD.PIDM = SATURN.SGBSTDN.SGBSTDN_PIDM AND P.LEVEL_CODE = SATURN.SGBSTDN.SGBSTDN_LEVL_CODE AND SM.PIDM = SATURN.SGBSTDN.SGBSTDN_PIDM AND SA.PIDM = SATURN.SGBSTDN.SGBSTDN_PIDM AND S.PIDM = SATURN.SGBSTDN.SGBSTDN_PIDM AND SM.COURSE_CODE = MD.COURSE_NUMBER AND SM.TERM_CODE = MD.TERM_CODE AND SM.SUBJECT_CODE = MD.SUBJECT_CODE AND SM.RESIT_INDICATOR='Y' AND S.TERM_CODE='?' AND S.STUDENT_ID = '?' SELECT BANINST1_V_STUDENT.STUDENT_ID, BANINST1_V_STUDENT_ADDRESS.PERM_SPRADDR_COUNTRY, BANINST1_V_STUDENT.CLASS_CODE, BANINST1_V_STUDENT.PROGRAMME_CODE, BANINST1_V_STUDENT.SURNAME, BANINST1_V_STUDENT.FIRST_NAME, BANINST1_V_STUDENT_ANONYMOUS_CODE.ANONYMOUS_NUMBER, BANINST1_V_PROGRAMME.FACULTY_CODE, BANINST1_V_PROGRAMME.LEVEL_CODE, BANINST1_V_STUDENT.COLLEGE_CODE, BANINST1_V_STUDENT_MARKS.SUBJECT_CODE, BANINST1_V_STUDENT_MARKS.COURSE_CODE, BANINST1_V_ASSESSMENT_DETAILS.EXAM_REFERENCE, BANINST1_V_MODULE_DETAILS.CREDIT_HIGH, BANINST1_V_STUDENT_MARKS.TITLE, SATURN_SGBSTDN.SGBSTDN_ASTD_CODE FROM ((((((BANINST1_V_STUDENT INNER JOIN BANINST1_V_STUDENT_ANONYMOUS_CODE ON (BANINST1_V_STUDENT.TERM_CODE = BANINST1_V_STUDENT_ANONYMOUS_CODE.TERM_CODE) AND (BANINST1_V_STUDENT.PIDM = BANINST1_V_STUDENT_ANONYMOUS_CODE.PIDM)) INNER JOIN BANINST1_V_STUDENT_MARKS ON (BANINST1_V_STUDENT_ANONYMOUS_CODE.PIDM = BANINST1_V_STUDENT_MARKS.PIDM) AND (BANINST1_V_STUDENT.PIDM = BANINST1_V_STUDENT_MARKS.PIDM) AND (BANINST1_V_STUDENT_ANONYMOUS_CODE.TERM_CODE = BANINST1_V_STUDENT_MARKS.TERM_CODE) AND (BANINST1_V_STUDENT.TERM_CODE = BANINST1_V_STUDENT_MARKS.TERM_CODE)) INNER JOIN BANINST1_V_ASSESSMENT_DETAILS ON (BANINST1_V_ASSESSMENT_DETAILS.CRN = BANINST1_V_STUDENT_MARKS.CRN) AND (BANINST1_V_ASSESSMENT_DETAILS.TERM_CODE = BANINST1_V_STUDENT_MARKS.TERM_CODE) AND (BANINST1_V_STUDENT_ANONYMOUS_CODE.TERM_CODE = BANINST1_V_ASSESSMENT_DETAILS.TERM_CODE) AND (BANINST1_V_STUDENT.TERM_CODE = BANINST1_V_ASSESSMENT_DETAILS.TERM_CODE)) INNER JOIN BANINST1_V_STUDENT_ADDRESS ON (BANINST1_V_STUDENT_MARKS.PIDM = BANINST1_V_STUDENT_ADDRESS.PIDM) AND (BANINST1_V_STUDENT_ANONYMOUS_CODE.PIDM = BANINST1_V_STUDENT_ADDRESS.PIDM) AND (BANINST1_V_STUDENT.PIDM = BANINST1_V_STUDENT_ADDRESS.PIDM)) INNER JOIN BANINST1_V_PROGRAMME ON (BANINST1_V_ASSESSMENT_DETAILS.TERM_CODE = BANINST1_V_PROGRAMME.TERM_CODE) AND (BANINST1_V_STUDENT_MARKS.TERM_CODE = BANINST1_V_PROGRAMME.TERM_CODE) AND (BANINST1_V_STUDENT_ANONYMOUS_CODE.TERM_CODE = BANINST1_V_PROGRAMME.TERM_CODE) AND (BANINST1_V_STUDENT.TERM_CODE = BANINST1_V_PROGRAMME.TERM_CODE) AND (BANINST1_V_STUDENT.PROGRAMME_CODE = BANINST1_V_PROGRAMME.PROGRAMME_CODE)) INNER JOIN SATURN_SGBSTDN ON (BANINST1_V_PROGRAMME.TERM_CODE = SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF) AND (BANINST1_V_STUDENT_MARKS.TERM_CODE = SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF) AND (BANINST1_V_ASSESSMENT_DETAILS.TERM_CODE = SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF) AND (BANINST1_V_STUDENT_ANONYMOUS_CODE.TERM_CODE = SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF) AND (BANINST1_V_STUDENT.TERM_CODE = SATURN_SGBSTDN.SGBSTDN_TERM_CODE_EFF) AND (BANINST1_V_STUDENT_ADDRESS.PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM) AND (BANINST1_V_PROGRAMME.LEVEL_CODE = SATURN_SGBSTDN.SGBSTDN_LEVL_CODE) AND (BANINST1_V_STUDENT_MARKS.PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM) AND (BANINST1_V_STUDENT_ANONYMOUS_CODE.PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM) AND (BANINST1_V_STUDENT.PIDM = SATURN_SGBSTDN.SGBSTDN_PIDM)) INNER JOIN BANINST1_V_MODULE_DETAILS ON (BANINST1_V_STUDENT_MARKS.COURSE_CODE = BANINST1_V_MODULE_DETAILS.COURSE_NUMBER) AND (BANINST1_V_STUDENT_MARKS.TERM_CODE = BANINST1_V_MODULE_DETAILS.TERM_CODE) AND (BANINST1_V_STUDENT_MARKS.SUBJECT_CODE = BANINST1_V_MODULE_DETAILS.SUBJECT_CODE) WHERE (((BANINST1_V_STUDENT_MARKS.RESIT_INDICATOR)="Y") AND ((BANINST1_V_STUDENT.TERM_CODE)="200400"));

10. External Links • Will the database beavailable 24 hoursa day? • RESIT REGISTRATION > THE PROCESS > DATA • Will data be returned quickly enough? • [ live test – link removed: sorry but you don’t have an account on the server  ] GregorianCalendar beforeBanner = new GregorianCalendar(); // run Query GregorianCalendar afterBanner = new GregorianCalendar(); // calculate difference long queryTime = ( afterBanner.getTimeInMillis() - beforeBanner.getTimeInMillis() );

11. Modelling the Process • RESIT REGISTRATION > THE PROCESS > MODELLING START progress = 0 Developed in discussion with the client Largely linear  But applications to sit exams abroad may not be approved/rejected immediately … 1. Any resits? progress = 1 YES NO Provide contact details STOP progress = -1 progress = 2 2. Need advice? YES NO progress = 3 3. Take advice? YES NO progress = 4 Record advice progress = 5 4. Abroad Option? YES NO progress = 6 progress = 7 5. Sit Abroad? YES NO progress = 8 progress = 7 Pick Exams to Sit/Carry/Fail NO 6. Choice OK? YES 7. Need to Pay? Green boxes indicate choices or actions on the part of the student NO YES progress = 10 Select Payment Method STOP progress = 20 progress = 11 Make Payment Yellow shapes indicate decisions based on logic and/or data supplied by SPA progress = 12 Receive Payment STOP progress = 21

12. Development Document • RESIT REGISTRATION > THE PROCESS > DOCUMENTATION Version 62 !!

13. Start: a Portal Module • RESIT REGISTRATION > IMPLEMENTATION • Portal Module • Ideally would displayprogress/instructions • But couldn’t afford the overhead of querying the database each time • Considered storing latest instructions as customData -but rejected: • volatile data • do the APIs exist to do this ? • Finally opted for a bland generic “Click OK” option • user.StudentId passed by the form

14. Personal Data Student.getTasksMessage() Student.getResitList() Examination Student.getTasksTable() • RESIT REGISTRATION > IMPLEMENTATION > STUDENT TASKS

15. Where am I ? • RESIT REGISTRATION > IMPLEMENTATION > NAVIGATION • You need to find this from view.jsp • import blackboard.portal.servlet.*; // not public • PortalRequestContext prc = PortalUtil.getPortalRequestContext(pageContext); • String tabUrl = prc.getRootRelativeUrl("index.jsp"); • String tabName = prc.getAreaName(); Pass these to other JSPs <bbUI:breadcrumbBar environment="PORTAL"> <bbUI:breadcrumb href="<%=tabUrl%>"> <%=tabName%></bbUI:breadcrumb> <bbUI:breadcrumb> RESIT REGISTRATION</bbUI:breadcrumb> </bbUI:breadcrumbBar>

16. Minimising Database Connections • RESIT REGISTRATION > IMPLEMENTATION > CONNECTIONS • Press the OK button on view.jsp • Query runs – results shown in tasks.jsp • We need to save this • and prevent the query running again when we return to tasks.jsp

17. Solution • RESIT REGISTRATION > IMPLEMENTATION > SESSION DATA 1 • Store the data in the session • Pass a flag “caller” from view.jsp • Only query the database if the flag is found • otherwise retrieve it from the session • Store other items as a custom Housekeeping object…

18. Staff Access from the Module • RESIT REGISTRATION > IMPLEMENTATION > STAFF • Staff see what students see • Common access point • Staff can impersonate individual users • Form passes an extra flag to show user is a staff member

19. Seeing what a student sees More later… • RESIT REGISTRATION > IMPLEMENTATION > STAFF TASKS

20. Changing buttons displayed • RESIT REGISTRATION > CODE DIGRESSION > IF STATEMENTS WITHIN TAGLIB • <bbUI:actionBar action="Tools"> • <bbUI:actionItem title='Contacting SPA‘ href='<%=contactURL%>' imgUrl='../images/icon16.gif'/> • <bbUI:actionItem title='Help‘ href='<%=helpURL%>' imgUrl='../images/help.gif'/> • <% • if(housekeeping.getStaffFromSPA() ) • { • %> • <bbUI:actionItem title='SPA User Tools' href='spaTools.jsp' imgUrl='../images/user16.gif'/> • <% • } • %> • </bbUI:actionBar>

21. Housekeeping • RESIT REGISTRATION > IMPLEMENTATION > SESSION DATA 2 • A class used to keep track of things… • public class Housekeeping • { • String module_id; • String tabUrl; • String tabName; • String academicYear; • String theUser; • String theStudent; • String bannerID; • String studentFirstName; • String studentLastName; • String lastPageVisited; • boolean staffFromSPA;

22. Storing these Objects • RESIT REGISTRATION > IMPLEMENTATION > SESSION DATA 2 • Saved in the session • String housekeepingLabel = thisUser.getUserName() + "Housekeeping"; • If the users re-enters the tool from view.jsp this object is repopulated, ensuring data are only shown to staff which relate to the currently selected student

23. Completing the Process • RESIT REGISTRATION > CONFIGURATION • So far we have seen how students (and staff) can supply data and make choices • How do we set system-wide variables ? • e.g. deadline for making choices • How do staff • manage the process ? • change individual entries ?

24. System-wide variables • RESIT REGISTRATION > CONFIGURATION > MODULE ADMIN • Configure messages, dates, etc. using the standard Module Admin pages

25. <bbUI:moduleAdminPage> • RESIT REGISTRATION > CONFIGURATION > MODULE ADMIN • Data exchange managed using a custom classmalcolm.ModuleOptions • getFrontPageMessage() • setFrontPageMessage() ModuleOptions mOptions = new ModuleOptions(); String frontPageMessage = mOptions.getFrontPageMessage(); boolean showFrontPageMessage = mOptions.getShowFrontPageMessage();

26. malcolm.ModuleOptions • RESIT REGISTRATION > CONFIGURATION > MODULE ADMIN > CUSTOM CLASS public class ModuleOptions() frontPageMessage = “”; // others deleted … File theConfigFile = null; PlugInConfig piConfig = null; try { piConfig = new PlugInConfig( "lttM", "resits" ); theConfigFile = new File(piConfig.getConfigDirectory(), "config.properties"); } catch (blackboard.platform.plugin.PlugInException pppE) { exceptionThrown = true; // other code removed } if(!exceptionThrown) { Properties props = new Properties(); FileInputStream fiStream = null; if(theConfigFile.exists() ) { try { fiStream = new FileInputStream(theConfigFile); props.load(fiStream); // get the values frontPageMessage = props.getProperty("prop_frontPageMessage"); etc..

27. Gotchas • RESIT REGISTRATION > CONFIGURATION > GOTCHAS • Properties entries cannot spread over a line in the file – so entries which include characters such as \n cannot be stored • Could write <textarea> contents into a separate file or • parse it into chunks and replace \n with another delimiter, to be recombined later

28. Other Staff Tasks • RESIT REGISTRATION > CONFIGURATION > STAFF TASKS • Version 1 had a simple portal module – visible only to students. Staff used an actionBar on admin.jsp to perform tasks • A lot of duplicate coding • Not very intuitive to find • Administrators had to be added individuallyand each time the tool was updated  • Version 2 displays extra information on view.jsp to users with a portal role of SPA staff • Uses the same code • Intuitive • Staff see what students see • Staff can mimic a given student’s view

29. Single User Tasks • RESIT REGISTRATION > CONFIGURATION > STAFF TASKS > SINGLE USER

30. Multi-User Tasks • No Exams Abroad Requests • 1 Exam Abroad Request • X Exam Abroad Requests • RESIT REGISTRATION > CONFIGURATION > STAFF TASKS > MULTI USER • <bbUI:actionBar> image and title values populated dynamically

31. Displaying Lists - BbList • RESIT REGISTRATION > CODE DIGRESSION > LISTS • Use the API and tag library to providedisplay and sorting tools where possible • blackboard.base.BbList • blackboard.base.GenericFieldComparator • <bbUI:list> • <% • BbList processedPaymentsList = new BbList(); • // now populate it…(code omitted) • // define some comparators for sorting the list • GenericFieldComparator compBannerID = new GenericFieldComparator( BaseComparator.ASCENDING, "getBannerID", malcolm.Student.class ); • %> • <bbUI:list • collection="<%=processedPaymentsList%>" • collectionLabel="Processed Payments" • objectId="eList" • className="malcolm.Student" • resultsPerPage="-1" • description="A list of processed payments, • one per row“ > • <bbUI:list • (parameters defined as previous slide) • > • <bbUI:listElement • width="" • label="BannerID" • name="Banner ID" • comparator="<%=compBannerID%>" • valign="top" • > • <%=eList.getBannerID()%> • </bbUI:listElement> • </bbUI:list>

32. Data Export • Solution • Allow the users to export data to Excel… • RESIT REGISTRATION > CONFIGURATION > STAFF TASKS > DATA EXPORT • Staff want to analyse data using their own tools and other data sources • Probably will use Access with an ODBClink to Banner • Couldn’t specify their exact requirements 

33. Export to Excel • RESIT REGISTRATION > CONFIGURATION > STAFF TASKS > DATA EXPORT > SCREENSHOTS

34. How it’s done… examChoiceXL.jsp • RESIT REGISTRATION > CONFIGURATION > STAFF TASKS > DATA EXPORT > CODE • <html> • <head> • <title>Exam Choices</title> • <% • response.setContentType("application/vnd.ms-excel"); • response.setHeader("Content-Disposition", "attachment;filename=ExamChoice.xls"); • %> • <style type="text/css"> • br { mso-data-placement:same-cell; } • </style> • </head> • <body> • <%@page import="malcolm.*"%> • <% • // get the rest from a query • ExcelQuery eQuery = new ExcelQuery(); • String tableRows = eQuery.getExamChoiceRows(); • out.println(tableRows); • %> • </table> • </body> • </html> • <table width='100%' cellspacing='0' border='1'> • <tr> • <th align='left' bgcolor='#CC99FF'><b><font size='1' color='#FFFFFF'>Counter</font></b></th> • <th align='left' bgcolor='#CC99FF'><b><font size='1' color='#FFFFFF'>Banner ID</font></b></th> • <th align='left' bgcolor='#CC99FF'><b><font size='1' color='#FFFFFF'>ExamRef</font></b></th> • <th align='left' bgcolor='#CC99FF'><b><font size='1' color='#FFFFFF'>AcademicYear</font></b></th> • <th align='left' bgcolor='#CC99FF'><b><font size='1' color='#FFFFFF'>LastChanged</font></b></th> • <th align='left' bgcolor='#CC99FF'><b><font size='1' color='#FFFFFF'>Decision</font></b></th> • </tr>

35. Database Connections • RESIT REGISTRATION > DATABASE CONNECTIONS • Class to handle usernames, password etc • DatabaseConnect • getUserName() • getPassword() • getServer() • getJDBCDriver() • Populated depending on the String supplied • DatabaseConnect bConnect = new DatabaseConnect("Banner"); • Connection c = null; • try • { • Class.forName(bConnect.getJDBCDriver()).newInstance(); • } • catch (SQLException E) • { • // deal with exception… • } • try • { • c = DriverManager.getConnection(bConnect.getServer(), bConnect.getUserName(), bConnect.getPassword() ); • // now go to it… • }

36. Improving the use of Connections • RESIT REGISTRATION > DATABASE CONNECTIONS > HELP Database Pooling ? Use of Singleton ? Any Suggestions ?

37. malcolm.murray@durham.ac.uk