Attached is a zip file of the student schedule portlet used at the University of St. Francis in uPortal 2.5.2 which displays a student schedule from our Banner ERP system.
The primary component of the portlet queries sfrstcr to get the courses a student is registered for. It then loops through each course the student is in and gets additional details such as the instructor(s) from sirasgn, and meeting times from ssrmeet.
...
The outer query to get the basic courses registered for from sfrstcr used within the portlet is:
| Code Block | |
|---|---|
| title | sfrstcr query | borderStyle | solid
SELECT sfr.sfrstcr_term_code as term_code,
stv.stvterm_desc as term_desc,
stv.stvterm_start_date as term_start_date,
stv.stvterm_end_date as term_end_date,
ptrm.sobptrm_start_date as sobptrm_start_date,
ptrm.sobptrm_end_date as sobptrm_end_date,
sfr.sfrstcr_crn as crn,
sfr.sfrstcr_levl_code as levl_code,
sfr.sfrstcr_credit_hr as credit_hr,
sfr.sfrstcr_grde_code as grde_code,
ssb.ssbsect_subj_code as subj_code,
ssb.ssbsect_crse_numb as crse_numb,
ssb.ssbsect_seq_numb as seq_numb,
NVL(ssb.ssbsect_crse_title, scbcrse_title) as crse_title,
sfr.sfrstcr_rsts_code as rsts_code
FROM stvterm stv,
sobptrm ptrm,
scbcrse scb,
ssbsect ssb,
sfrstcr sfr
WHERE sfr.sfrstcr_pidm = ?
<sql:param value="${model.pidm}" />
AND stvterm_code = ?
<sql:param value="${term.stvterm_code}" />
AND (sfr.SFRSTCR_ERROR_FLAG \!= 'F' OR sfr.SFRSTCR_ERROR_FLAG IS NULL)
AND sfr.sfrstcr_term_code = stv.stvterm_code
AND sfr.sfrstcr_rsts_code NOT IN ('DD') \--Dont get dropped or withdrawn courses
AND sfr.sfrstcr_term_code = ptrm.sobptrm_term_code
AND sfr.sfrstcr_ptrm_code = ptrm.sobptrm_ptrm_code
AND sfr.sfrstcr_crn = ssb.ssbsect_crn
AND sfr.sfrstcr_term_code = ssb.ssbsect_term_code
AND ssb.ssbsect_subj_code = scb.scbcrse_subj_code
AND ssb.ssbsect_crse_numb = scb.scbcrse_crse_numb
AND scb.scbcrse_eff_term = (
SELECT max(scb1.scbcrse_eff_term)
FROM scbcrse scb1
WHERE scb1.scbcrse_subj_code = ssb.ssbsect_subj_code
AND scb1.scbcrse_crse_numb = ssb.ssbsect_crse_numb
AND scb1.scbcrse_eff_term <= sfr.sfrstcr_term_code
)
ORDER BY sfr.sfrstcr_term_code,
ssb.ssbsect_subj_code,
ssb.ssbsect_crse_numb
|
Some things you will likely have to customize for your environment:
- Have a method to grab the pidm of the user who is logged in, which is used to lookup that persons data within Banner.
- Have a central spot to store the silk icons where they can be referenced by the portlet.
- Implement your own method for logging within your portlet if you choose to do so. Where you see us inserting into the table activity_log, you can safely remove it. It is our custom logging code to track who accessed the portlet.
- Ensure your portal supports JSTL. At USF, we took a "get it done" approach and didn't spend the extra time generating a specific database layer and such. We were able to build the portlet in far less time just using a simplified approach that runs the queries using JSTL. All data retrieval and rendering logic is stored right in the JSP page.
- Depending on how your banner system is setup, you may have to adjust the main query above, or any of the sub queries used within the portlet.