Page Comparison

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.

You'll notice that the portlet also does an insert into a table called activity_log. This is just our custom logging table where we insert the pidm of the currently logged in user, an event_code of every item rendered in our portal, and a description about what was rendered. This allows us to really analyze the usage of our portal, and join it to all of our banner data to look at trends by age, major, student type, or any other attribute in the system.

It requires a jdbc connection to the Banner database, called BannerDb, and it requires the pidm of the student to be obtainable by the portlet. The implementation details of how to do that are surely going to be different at each school. At USF, we had the students pidm stored in the workforceId attribute in Novell eDirectory, and our portal would grab that to use.

While we used this code in production for 3 years, I am sure any school consuming it would have to make modifications. Use it with the assumption that it will be a starting point for you, and you'll likely need to do some work to improve it.
\\

The outer query to get the basic courses registered for from sfrstcr used within the portlet is:
{{
        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    
}}