set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 23-APR-2002
-- 
-- This package creates a Banner Web page that allows a faculty member to
-- view the final grades for a selected course.  The most recent grades are
-- pulled from history (SHRTCKG) or, if none exist, from SFRSTCR.  In addition,
-- the user can download the information into an Excel file.  This package is
-- available in the Faculty Web module.
--

create or replace package lmuhwskfgra is

procedure LMUFinalGrades (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
	crn in sfrstcr.sfrstcr_crn%type default null,
	error_mess in varchar2 default null);

procedure LMUDownloadFGrd (term in stvterm.stvterm_code%type default null,
						crn in ssbsect.ssbsect_crn%type default null);

END lmuhwskfgra;
/
show errors

CREATE OR REPLACE PACKAGE BODY lmuhwskfgra AS
/* make sure registered then continue to process */

  /* Global type and variable declarations for package */

 /* The following broken line is required */
    NEWLINE      constant varchar2(1) := '
';
    pidm spriden.spriden_pidm%TYPE;
	
/****************************** LMU Final Grades ***********************/
procedure LMUFinalGrades (term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
	crn in sfrstcr.sfrstcr_crn%type default null,
	error_mess in varchar2 default null)
is
	curr_release  varchar2(10) := '4.4';
	hold_term     stvterm.stvterm_code%TYPE;
	hold_crn	  sfrstcr.sfrstcr_crn%type;
	update_ind          varchar2(1);
	crn_subj	  ssbsect.ssbsect_subj_code%type default null;
	crn_crse	  ssbsect.ssbsect_crse_numb%type default null;
	crn_sec	  	  ssbsect.ssbsect_seq_numb%type default null;
	crn_title	  ssbsect.ssbsect_crse_title%type default null;
	term_desc	  stvterm.stvterm_desc%type default null;
    msg           varchar2(255);
	rcount		  number;
		
	cursor gradeinfo
	is
   select sfrstcr_term_code term,
   			sfrstcr_pidm pidm,
			spriden_id id,
			spriden_last_name||', '||spriden_first_name||' '||spriden_mi name,
			f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, sfrstcr_term_code) class,
			a.sgbstdn_majr_code_1 major1,
			a.sgbstdn_majr_code_2 major2,
			sfrstcr_crn crn,
			ssbsect_subj_code subj,
			ssbsect_crse_numb crs,
			ssbsect_seq_numb sec,
			sfrstcr_reg_seq reg_seq,
			sfrstcr_rsts_code rsts,
			to_char(sfrstcr_rsts_date, 'MM/DD/YYYY') rsts_date,
			stvrsts_desc rsts_desc,
			sfrstcr_credit_hr hrs,
			lmu_f_get_final_grade(sfrstcr_pidm, sfrstcr_crn, sfrstcr_term_code) grade
		  from sfrstcr,
		    stvrsts,
			ssbsect,
			spriden,
			sgbstdn a
		  where sfrstcr_term_code = hold_term
		  and sfrstcr_crn = hold_crn
		  and sfrstcr_rsts_code=stvrsts_code
		  and stvrsts_incl_sect_enrl='Y'
		  and sfrstcr_crn=ssbsect_crn
		  and sfrstcr_term_code=ssbsect_term_code
		  and sfrstcr_pidm=spriden_pidm
		  and spriden_change_ind is null
		  and sfrstcr_pidm=a.sgbstdn_pidm
		  and a.sgbstdn_term_code_eff=
		   (select max(b.sgbstdn_term_code_eff) from sgbstdn b
		    where b.sgbstdn_term_code_eff<=sfrstcr_term_code
			and a.sgbstdn_pidm=b.sgbstdn_pidm)
		  order by name;

begin

   IF NOT twbkwbis.F_ValidUser(pidm) THEN
        return;
   END IF;

   /* If you came from the menu, try to select the term from the */
   /*  general table, gorwprm, by using F_GetParam.              */
   IF TERM IS NULL THEN
       hold_term := twbkwbis.F_GetParam(pidm,'TERM');
   /* otherwise, you came from P_FacSelTerm, and need to write      */
   /*  the param to the gorwprm table and set your local, hold_term */
   ELSE
       twbkwbis.P_SetParam(pidm,'TERM',term);
       hold_term := term;
   END IF;
   /* Make sure a term has been selected */
   IF hold_term is null THEN
       bwlkostm.P_FacSelTerm(calling_proc_name=> 'lmuhwskfgra.LMUFinalGrades');
       RETURN;
   END IF;

   /* If crn has not been passed as a param, then try to get */
   /*  it from the general table, gorwprm   */
   IF CRN IS NULL THEN
      hold_crn := twbkwbis.F_GetParam(pidm,'CRN');
      update_ind := twbkwbis.F_GetParam(pidm, 'CRNPRIV');
   /* Otherwise, store the value of the param in GORWPRM */
   ELSE
      twbkwbis.P_SetParam(pidm,'CRN',CRN);
      hold_crn := CRN;
   END IF;
   /* Make sure a CRN has been selected */
   IF hold_crn IS NULL THEN
      twbkwbis.P_SetParam(pidm,'PROC_NAME', 'lmuhwskfgra.LMUFinalGrades');
      twbkwbis.P_SetParam(pidm,'PROC_NAME2', 'lmuhwskfgra.LMUFinalGrades'); 
      bwlkocrn.P_FacCrnSel(hold_term, 'lmuhwskfgra.LMUFinalGrades',
                            'lmuhwskfgra.LMUFinalGrades');
      RETURN;
   END IF;

   /* set paramater to indicate we are a faculty user */
   twbkwbis.P_SetParam(pidm,'STUFAC_IND','FAC');

   /*  If the user is not a valid faculty member for the selected */
   /*   term, print a message, close the page, and exit.          */
   IF NOT BWLKILIB.F_ValidFac(hold_term,pidm) THEN
      twbkwbis.P_OpenDoc('lmuhwskfgra.LMUFinalGrades',header_text=>
                      '*** Invalid Faculty ID ***');
      msg := 'You must be a valid faculty member for the selected term'||
              ' to access this page.';
      HTP.hr;
      twbkfrmt.P_PrintImage(twbklibs.twgbwrul_rec.twgbwrul_error_gif);
      HTP.bold(msg);
      HTP.para;
      twbkwbis.P_CloseDoc(curr_release);
      RETURN;
   END IF;

   /* Get Course Information for Header */
   begin
   	select ssbsect_subj_code,
	  ssbsect_crse_numb,
	  ssbsect_seq_numb,
	  decode(ssbsect_crse_title,
	    '', a.scbcrse_title, ssbsect_crse_title)
	  into crn_subj, crn_crse, crn_sec, crn_title
	from ssbsect, scbcrse a
	where ssbsect_crn = hold_crn
	and ssbsect_term_code = hold_term
	and ssbsect_subj_code=a.scbcrse_subj_code
	and ssbsect_crse_numb=a.scbcrse_crse_numb
	and a.scbcrse_eff_term=
	 (select max(b.scbcrse_eff_term) from scbcrse b
	  where b.scbcrse_eff_term<=ssbsect_term_code
	  and a.scbcrse_subj_code=b.scbcrse_subj_code
	  and a.scbcrse_crse_numb=b.scbcrse_crse_numb);
   exception when others then
   	crn_subj := null;
	crn_crse := null;
	crn_sec := null;
	crn_title := null;
   end;
   
   /* Get Term Description */
   begin
   	select stvterm_desc
	into term_desc
	from stvterm
	where stvterm_code = hold_term;
   exception when others then
   	term_desc := 'Unknown Term';
   end;
   
   twbkwbis.P_OpenDoc('lmuhwskfgra.LMUFinalGrades',header_text=>'For: '||
   		crn_subj||' '||crn_crse||' '||crn_sec||' '||crn_title||' ('||term_desc||')');
	
   twbkwbis.P_DispInfo('lmuhwskfgra.LMUFinalGrades','GENERAL');

   /* Show Final Grades */
   if error_mess is not null then
		htp.nl;
		htp.p('Error: '||error_mess||'<br>');
   end if;

   htp.nl;
	
   	htp.anchor(twbklibs.twgbwrul_rec.twgbwrul_cgibin_dir ||
	              '/lmuhwskfgra.LMUDownloadFGrd'||
	              '?term='   || hold_term||
				  '&crn='	||hold_crn,
	              'DOWNLOAD FINAL GRADES');

	rcount:=0;

   FOR mystuff IN gradeinfo
   LOOP
		if rcount=0 then
			twbkfrmt.P_TableOpen('DATADISPLAY');
   			twbkfrmt.P_TableRowOpen('left');
   			twbkfrmt.P_TableDataLabel('ID',calign=>'center');
   			twbkfrmt.P_TableDataLabel('NAME',calign=>'center');
			twbkfrmt.P_TableDataLabel('CLASS',calign=>'center');
   			twbkfrmt.P_TableDataLabel('MAJOR1',calign=>'center');
			twbkfrmt.P_TableDataLabel('MAJOR2',calign=>'center');
   			twbkfrmt.P_TableDataLabel('SEQ',calign=>'center');
   			twbkfrmt.P_TableDataLabel('STATUS',calign=>'center');
   			twbkfrmt.P_TableDataLabel('DATE',calign=>'center');
			twbkfrmt.P_TableDataLabel('HOURS',calign=>'center');
			twbkfrmt.P_TableDataLabel('GRADE',calign=>'center');
   			twbkfrmt.P_TableRowClose;
			rcount:=1;
		end if;
		twbkfrmt.P_TableRowOpen('left');	
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.id),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.name),calign=>'left');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.class),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.major1),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.major2),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.reg_seq),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.rsts_desc),calign=>'left');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.rsts_date),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.hrs),calign=>'center');
		twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.grade),calign=>'center');
		twbkfrmt.P_TableRowClose;
   END LOOP;
   twbkfrmt.P_TableClose;
   
   twbkwbis.P_CloseDoc;
end LMUFinalGrades;

procedure LMUDownloadFGrd (term in stvterm.stvterm_code%type default null,
						crn in ssbsect.ssbsect_crn%type default null)

is

   mytab	varchar2(1);
   
   cursor dlgradeinfo
   is
   select sfrstcr_term_code term,
   			sfrstcr_pidm pidm,
			spriden_id id,
			spriden_last_name||', '||spriden_first_name||' '||spriden_mi name,
			f_class_calc_fnc(a.sgbstdn_pidm, a.sgbstdn_levl_code, sfrstcr_term_code) class,
			a.sgbstdn_majr_code_1 major1,
			a.sgbstdn_majr_code_2 major2,
			sfrstcr_crn crn,
			ssbsect_subj_code subj,
			ssbsect_crse_numb crs,
			ssbsect_seq_numb sec,
			decode(ssbsect_crse_title,
			  '', x.scbcrse_title, ssbsect_crse_title) title,
			sfrstcr_reg_seq reg_seq,
			sfrstcr_rsts_code rsts,
			to_char(sfrstcr_rsts_date, 'MM/DD/YYYY') rsts_date,
			stvrsts_desc rsts_desc,
			sfrstcr_credit_hr hrs,
			lmu_f_get_final_grade(sfrstcr_pidm, sfrstcr_crn, sfrstcr_term_code) grade
		  from sfrstcr,
		    stvrsts,
			ssbsect,
			scbcrse x,
			spriden,
			sgbstdn a
		  where sfrstcr_term_code = term
		  and sfrstcr_crn = crn
		  and sfrstcr_rsts_code=stvrsts_code
		  and stvrsts_incl_sect_enrl='Y'
		  and sfrstcr_crn=ssbsect_crn
		  and sfrstcr_term_code=ssbsect_term_code
		  and ssbsect_subj_code=x.scbcrse_subj_code
		  and ssbsect_crse_numb=x.scbcrse_crse_numb
		  and x.scbcrse_eff_term=
		   (select max(y.scbcrse_eff_term) from scbcrse y
		    where y.scbcrse_eff_term<=ssbsect_term_code
			and x.scbcrse_subj_code=y.scbcrse_subj_code
			and x.scbcrse_crse_numb=y.scbcrse_crse_numb)
		  and sfrstcr_pidm=spriden_pidm
		  and spriden_change_ind is null
		  and sfrstcr_pidm=a.sgbstdn_pidm
		  and a.sgbstdn_term_code_eff=
		   (select max(b.sgbstdn_term_code_eff) from sgbstdn b
		    where b.sgbstdn_term_code_eff<=sfrstcr_term_code
			and a.sgbstdn_pidm=b.sgbstdn_pidm)
		  order by name;
			
begin
	mytab:='	';
	
	owa_util.mime_header('application/vnd.ms-excel');
	owa_util.http_header_close;

	htp.print('TERM'||mytab||'CRN'||mytab||'SUBJ'||mytab||'CRS'||mytab||'SEC'||mytab||'TITLE'||mytab||'ID'||mytab||'NAME'||mytab||
	          'CLASS'||mytab||'MAJOR1'||mytab||'MAJOR2'||mytab||'SEQ'||mytab||'STATUS'||mytab||'DATE'||mytab||'HRS'||mytab||'GRADE');

	FOR mystuff in dlgradeinfo
	LOOP
		htp.print(mystuff.term||mytab||mystuff.crn||mytab||mystuff.subj||mytab||mystuff.crs||mytab||mystuff.sec||mytab||
		          mystuff.title||mytab||''''||mystuff.id||mytab||mystuff.name||mytab||mystuff.class||mytab||mystuff.major1||mytab||
				  mystuff.major2||mytab||mystuff.reg_seq||mytab||mystuff.rsts_desc||mytab||mystuff.rsts_date||mytab||
				  mystuff.hrs||mytab||mystuff.grade);
	END LOOP;

end LMUDownloadFGrd;

end lmuhwskfgra;
/

show errors

whenever sqlerror continue;
drop public synonym lmuhwskfgra;
whenever sqlerror exit rollback;
create public synonym lmuhwskfgra for lmuhwskfgra;
grant execute on lmuhwskfgra to public;

set scan on