set scan off
set echo on
-- Created for Loyola Marymount University
-- By Robert M. Nitsos
-- 21-MAR-2001
-- 
-- This package creates a Banner Web page that displays test score information in the
-- Faculty Module. The page displays the Test Code, Test Name, Test Score and Test
-- Date for each entry in SOATEST.
--
-- Modified 24-FEB-2003
-- By Robert M. Nitsos
-- Updated package to use FacWeb 5.3 UI and procedures.
--

create or replace package lmuhwsktest is

    TYPE varchar2_tabtype IS TABLE OF varchar2(26)
      INDEX BY BINARY_INTEGER;

procedure LMUTestScores (STUPIDM in spriden.spriden_pidm%type default null,
					    term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
                        error_mess in varchar2 default null);

END lmuhwsktest;
/
show errors

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

  /* Global type and variable declarations for package */
    pidm spriden.spriden_pidm%TYPE;
	
/****************************** LMU Show Test Scores ***********************/
procedure LMUTestScores (STUPIDM in spriden.spriden_pidm%type default null,
					    term IN STVTERM.STVTERM_CODE%TYPE DEFAULT NULL,
                        error_mess in varchar2 default null)
 is
 	    curr_release  varchar2(10) := '3.2';
	    hold_term     stvterm.stvterm_code%TYPE;
        hold_stupidm  spriden.spriden_pidm%TYPE;
        hold_stupidm_char varchar2(30) DEFAULT NULL;
        confid_msg    varchar2(30) default null;
        student_name  varchar2(124);
        msg           varchar2(255);
        term_rec      stvterm%rowtype;
		rcount		  number;
		
   /* Get information that is to be displayed */
   cursor testinfo
   is
   select sortest_tesc_code tcode,
			stvtesc_desc test,
			sortest_test_score score,
			to_char(sortest_test_date, 'MM/DD/YYYY') tdate
		  from sortest,
		    stvtesc
		  where sortest_pidm = hold_stupidm
		  and sortest_tesc_code=stvtesc_code(+)
		  order by tcode,
		    score DESC;

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=> 'lmuhwsktest.LMUTestScores');
       RETURN;
   END IF;

   /* set paramater to indicate we are a faculty user */

   twbkwbis.P_SetParam(pidm,'STUFAC_IND','FAC');

   /* If stupidm has not been passed as a param, then try to get */
   /*  it from the general table, gorwprm   */
   IF STUPIDM IS NULL THEN
      hold_stupidm_char := twbkwbis.F_GetParam(pidm,'STUPIDM');
   /* Otherwise, store the value of the param in GORWPRM */
   ELSE
      twbkwbis.P_SetParam(pidm,'STUPIDM',to_char(STUPIDM,'999999999'));
      bwlkoids.P_FacResetPin(pidm);
      hold_stupidm := STUPIDM;
   END IF;
   /* If stupidm came from the table, then change it to a number */
   if hold_stupidm_char is not null then
      hold_stupidm := to_number(hold_stupidm_char,'999999999');
   end if;

   /* Make sure a student PIDM has been selected */
   IF hold_stupidm IS NULL THEN
      bwlkoids.P_FacIDSel(hold_term, 'lmuhwsktest.LMUTestScores');
      RETURN;
   END IF;


   /*  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('lmuhwsktest.LMUTestScores',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;

   /* Check to see if there are any test records to display */
   BEGIN
      select count(*)
      into   rcount
      from   sortest
      where  sortest_pidm          = hold_stupidm;
   EXCEPTION WHEN OTHERS THEN
      rcount := 0;
   END;

   /* Display message if no test records found */
   IF rcount = 0 THEN
      twbkwbis.P_OpenDoc('lmuhwsktest.LMUTestScores',header_text=>
                      '*** No Test Score Information Exists ***');
	  /* Be sure to enter appropriate info text in Web Tailor for NO_TESTS */
      twbkwbis.P_DispInfo('lmuhwsktest.LMUTestScores','NO_TESTS');
      twbkwbis.P_CloseDoc(curr_release);
      return;
   END IF;

   /* Show Tesc Score Information For Student */
   twbkwbis.P_OpenDoc('lmuhwsktest.LMUTestScores',header_text=>
                      'For: '||
                      f_format_name(hold_stupidm, 'FMIL'));
   /* You can enter info text for GENERAL if you want to display any additional information */
   twbkwbis.P_DispInfo('lmuhwsktest.LMUTestScores','GENERAL');
   
   twbkfrmt.P_TableOpen('DATADISPLAY');
   twbkfrmt.P_TableRowOpen('left');
   twbkfrmt.P_TableDataLabel('Test',calign=>'center');
   twbkfrmt.P_TableDataLabel('Description',calign=>'left',ccolspan=>'3');
   twbkfrmt.P_TableDataLabel('Score',calign=>'center');
   twbkfrmt.P_TableDataLabel('Test Date',calign=>'center');
   twbkfrmt.P_TableRowClose;

   FOR mystuff IN testinfo
   LOOP
      twbkfrmt.P_TableRowOpen('left');

      twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.tcode),calign=>'center');
	  twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.test),calign=>'left',ccolspan=>'3');
	  twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.score),calign=>'center');      
	  twbkfrmt.P_TableData(twbkfrmt.F_PrintBold(mystuff.tdate),calign=>'center');
	  
      twbkfrmt.P_TableRowClose;
   END LOOP;
   twbkfrmt.P_TableClose;
   
   twbkwbis.P_CloseDoc;
end LMUTestScores;

end lmuhwsktest;
/

show errors

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

set scan on