create or replace package display as
   procedure display_teachers (u_access in varchar2 default NULL,
			      id in varchar2 default NULL,
			      last_access in date);
			      
   procedure display_catalog(u_access in varchar2 default NULL, 
			    userid in varchar2 default NULL);
   procedure display_students(term_in in varchar2 default NULL,
			      lnum in varchar2 default NULL,
			      u_access in varchar2 default NULL,
			      userid in varchar2 default NULL);
   procedure display_courses(u_access in varchar2 default NULL,
			     userid in varchar2 default NULL);
   procedure display_components(u_access in varchar2 default NULL,
			     userid in varchar2 default NULL);
   procedure display_enrolls(u_access in varchar2 default NULL,
			     userid in varchar2 default NULL);
   procedure display_scores(id in varchar2 default NULL,
			 tcl in varchar2,
		  	 u_access in varchar2,
			 teachername in varchar2 default NULL);


   procedure display_own_scores(id in varchar2,
                                tcl in varchar2,
                                u_access in varchar2 default NULL,
				teachername in varchar2 default NULL
				);
   procedure display_graphs (id in varchar2 default NULL,
			  tcl in varchar2 default NULL,
			  u_access in varchar2 default NULL,
	  		  teachername in varchar2 default NULL);

   procedure graphs (id in varchar2,
       		   term in varchar2,
		   compname in varchar2,
		   lineno in varchar2,
		   u_access in varchar2,
		   teachername in varchar2 default NULL);

   procedure graphs_cumulative (id in varchar2,
       		   term in varchar2,
		   --compname in varchar2,
		   lineno in varchar2,
		   u_access in varchar2,
		   teachername in varchar2 default NULL);


end;
/
show errors

create or replace package body display as

procedure display_teachers( u_access in varchar2 default NULL,
			    id in varchar2 default NULL,
			    last_access in date
			) AS
   no_access exception;
   access_check varchar2(15);
   cursor c1 is
      select lname
      from teachers
      order by lname;
      --select userid
      --from whoami
      --where authority = 'Teacher'
      --order by userid;
begin
   if (u_access is NULL) then
     raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Student') then
     raise no_access;
   end if;
   htp.htmlOpen;
   htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
   htp.header(3,'Select the teacher''s last name that you are CURRENTLY
            taking classes.');  
   htp.para;
   htp.formOpen(owa_util.get_owa_service_path||'menu.student_menu');
   htp.formHidden('u_access',u_access);
 

   for crec in c1 loop
     htp.print(crec.lname);
--     htp.print(crec.userid);
     htp.formRadio('teachername', crec.lname);
--     htp.formRadio('teachername', crec.userid);
     htp.print(' ');
  end loop;   

   htp.formHidden('id',id);
   htp.formHidden('last_access',to_char(last_access,'DD-MON-YY, HH:MI:SS AM'));
   htp.para;
   htp.formSubmit(NULL,'Proceed');
   Exception
     when no_access then
	Raise_no_access('gradebook');
     when others then
       htp.print(SQLERRM);
END display_teachers;
   

procedure display_catalog(u_access in varchar2 default NULL,
			  userid in varchar2 default NULL) as
   counter      integer := 0;
   access_check varchar2(30);
   no_access    exception;
   cursor c1 is 
      select   *
      from     catalog
      where authority = userid;

begin
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;   

   htp.htmlOpen;

   htp.headOpen;
   htp.title('Display Catalog');
   htp.header(1,'Catalog Courses');
   htp.headClose;

   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;

   htp.tableOpen(cattributes => 'border=2 width=60%' );
   htp.tableRowOpen;
   htp.tableHeader('Course Number');
   htp.tableHeader('Course Title');
   htp.tableRowClose;

   for cat_rec in c1 loop
     htp.tableRowOpen;
     htp.tableData(cat_rec.cno);
     htp.tableData(cat_rec.ctitle);
     htp.tableRowClose;
     counter := counter + 1;
   end loop;
   htp.tableClose;

   htp.nl;
   htp.print('Number of records: ');
	htp.print(counter);
   htp.bodyClose;
   htp.htmlClose;
Exception
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display_catalog');
end display_catalog;

procedure display_students(term_in varchar2 default NULL,
			   lnum in varchar2 default NULL,
			   u_access in varchar2 default NULL,
  		           userid in varchar2 default NULL) AS
   counter      integer := 0;
   access_check varchar2(30);
   no_access    exception;
   cursor c1 is 
      select  A.sid, A.fname, A.lname, A.minit 
      from     students A, enrolls B
      where (A.sid = B.sid) and (A.authority = userid) and
 	    (B.term = term_in) and
	    (B.lineno = lnum)
      order by lname;

begin
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;   

   htp.htmlOpen;

   htp.headOpen;
   htp.title('Display Students');
   htp.fontOpen('Blue');
   htp.header(1,'Students');
   htp.fontclose;
   htp.headClose;

   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;

   htp.tableOpen(cattributes => 'border=2 width=60%' );
   htp.tableRowOpen;
   htp.tableHeader('Student ID');
   htp.tableHeader('First Name');
   htp.tableHeader('Last Name');
   htp.tableHeader('Middle Initial');
   htp.tableRowClose;

   for stu_rec in c1 loop
     htp.tableRowOpen;
     htp.tableData(stu_rec.sid);
     htp.tableData(stu_rec.fname);
     htp.tableData(stu_rec.lname);
     htp.tableData(stu_rec.minit);
     htp.tableRowClose;
     counter := counter + 1;
   end loop;
   htp.tableClose;

   htp.nl;
   htp.print('Number of records: ');
	htp.print(counter);
   htp.bodyClose;
   htp.htmlClose;
Exception
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display_students');
end display_students;

procedure display_courses(u_access in varchar2 default NULL,
			     userid in varchar2 default NULL) as

   counter integer := 0;
   access_check varchar2(30);
   no_access    exception;

cursor c1 is 
   select   *
   from     courses C
   where C.authority = userid
   order by term;

begin
   if (u_access is NULL) then
      raise no_access;
   end if;
	access_check := user_access.check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;

   htp.htmlOpen;

   htp.headOpen;
   htp.title('Display Courses');
   htp.headClose;

   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.fontOpen('blue');
    htp.header(1,'Courses Taught');
   htp.fontclose;
   htp.nl;

   htp.tableOpen(cattributes => 'border=2 width=60%' );
   htp.tableRowOpen;
   htp.tableHeader('Term');
   htp.tableHeader('Line Number');
   htp.tableHeader('Cno');
   htp.tableHeader('A');
   htp.tableHeader('B');
   htp.tableHeader('C');
   htp.tableHeader('D');
   htp.tableRowClose;

   for crs_rec in c1 loop
     htp.tableRowOpen;
     htp.tableData(crs_rec.term);
     htp.tableData(crs_rec.lineno);
     htp.tableData(crs_rec.cno);
     htp.tableData(crs_rec.a);
     htp.tableData(crs_rec.b);
     htp.tableData(crs_rec.c);
     htp.tableData(crs_rec.d);
     htp.tableRowClose;
     counter := counter + 1;
   end loop;
   htp.tableClose;
   htp.nl;
   htp.print('Number of records: ');
   htp.print(counter);
   htp.bodyClose;
   htp.htmlClose;
Exception
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display_courses');
end display_courses;

procedure display_components(u_access in varchar2 default NULL,
			     userid in varchar2 default NULL) as

   counter integer := 0;
   access_check varchar2(30);
   no_access    exception;

cursor c1 is 
   select   *
   from     components A
   where A.authority = userid
   order by term;

begin
   if (u_access is NULL) then
      raise no_access;
	end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;

   htp.htmlOpen;
   htp.headOpen;
   htp.title('Display Components');
   htp.header(1,'Course Components');
   htp.headClose;

   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;

   htp.tableOpen(cattributes => 'border=2 width=60%' );
   htp.tableRowOpen;
   htp.tableHeader('Term');
   htp.tableHeader('Line Number');
   htp.tableHeader('Component Name');
   htp.tableHeader('Maximum Points');
   htp.tableHeader('Weight');
   htp.tableRowClose;

   for cmp_rec in c1 loop
     htp.tableRowOpen;
     htp.tableData(cmp_rec.term);
     htp.tableData(cmp_rec.lineno);
     htp.tableData(cmp_rec.compname);
     htp.tableData(cmp_rec.maxpoints);
     htp.tableData(cmp_rec.weight);
     htp.tableRowClose;
     counter := counter + 1;
   end loop;
   htp.tableClose;
   htp.nl;
   htp.print('Number of records: ');
   htp.print(counter);
   htp.bodyClose;
   htp.htmlClose;
Exception
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display_components');
end display_components;

procedure display_enrolls(u_access in varchar2 default NULL,
			     userid in varchar2 default NULL) as

   counter integer := 0;
   access_check varchar2(30);
   no_access    exception;

   cursor c1 is
      select * from enrolls E
      where (E.authority = userid)
      order by term;

begin
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;

   htp.htmlOpen;

   htp.headOpen;
   htp.title('Display Enrolls');
   htp.header(1,'Students Enrolled');
   htp.headClose;

   htp.bodyOpen('/ows-img/raj/fleck.jpg');

   htp.tableOpen(cattributes => 'border=2 width=60%' );
   htp.tableRowOpen;
   htp.tableHeader('Term');
   htp.tableHeader('Line Number');
   htp.tableHeader('Student ID');
   htp.tableRowClose;

   for enl_rec in c1 loop
     htp.tableRowOpen;
     htp.tableData(enl_rec.term);
     htp.tableData(enl_rec.lineno); 
     htp.tableData(enl_rec.sid);
     htp.tableRowClose;
     counter := counter + 1;
   end loop;
   htp.tableClose;
   htp.nl;
   htp.print('Number of records: ');
   htp.print(counter);
   htp.bodyClose;
   htp.htmlClose;
Exception
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display.display_enrolls');
end display_enrolls;

procedure display_scores(id in varchar2 default NULL,
			 tcl in varchar2,
			 u_access in varchar2,
			 teachername in varchar2 default NULL) AS
   term scores.term%type;
   lineno scores.lineno%type;
   p1 integer;
   p2 integer;
  
   counter integer := 0;
   access_check varchar2(30);
   no_access    exception;

   cursor c1 is 
      select A.compname, A.points, B.weight
      from scores A, components B
      where A.sid = display_scores.id and A.term = display_scores.term and
	(A.lineno = display_scores.lineno) and
   	(B.compname = A.compname) and 
	(B.term = display_scores.term) and (A.authority = teachername) and
 	(B.lineno = A.lineno);
    
   fname students.fname%type;
   lname students.lname%type;
   total number(5,2) := 0;
   final number(1);
   no_course exception;
begin
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Student') then
	raise no_access;
   end if;

   select C.fname, C.lname into fname, lname
   from Students C
   where C.sid = id;

   htp.htmlOpen;

   htp.headOpen;
   htp.title('Display Scores');
   htp.header(2,fname ||' '||lname||' Grade Report.');
   htp.headClose;
   htp.nl;
   htp.bodyOpen;
   if (tcl is NULL) then
     raise no_course;
   end if;
   p1 := instr(tcl,',',1,2)+1;
   p2 := instr(tcl,')',1,1)-1;
   term := substr(tcl,2,instr(tcl,',',1)-2);
   lineno := to_number(substr(tcl,p1,p2-p1+1));
 
   htp.bodyOpen;
   htp.print('Student ID: '||htf.bold(id)); htp.br;
   htp.print('Term: '||htf.bold(term)||'<br>');
   htp.print('Line Number: '||htf.bold(lineno)||'<br>');
   htp.para;
   htp.tableOpen(cattributes => 'border=2' );

   htp.tableRowOpen;
   for scr_rec in c1 loop
     htp.tableHeader(scr_rec.compname||'('||scr_rec.weight||')');
     if (upper(scr_rec.compname) = 'FINAL') then
       final := 1; -- 1 True, 0 False
     end if;

   end loop;
     htp.tableHeader('Average');
   htp.tableRowClose;
   htp.tableRowOpen('middle','top');
   for scr_rec in c1 loop
     htp.tableData(scr_rec.points);
     total := total + (scr_rec.points * scr_rec.weight) / 100;
   end loop;
   if (final = 1) then
     htp.tableData(total);
   else
     htp.tableData;
   end if;

   htp.tableRowClose;
   htp.tableClose;
   htp.nl;
   htp.print('<form><input type=button Onclick=history.go(-1) value="Back to the Main Menu"></form>');
   htp.bodyClose;
   htp.htmlClose;
Exception
   when No_course then
      htp.print('The instructor has not added your name to any class.
	Please contact your instructor for help.');
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display_scores');
end display_scores;

procedure display_own_scores(id in varchar2,
                             tcl in varchar2,
                             u_access in varchar2 default NULL,
			     teachername in varchar2 default NULL) AS

   counter integer := 0;
   access_check varchar2(30);
   no_access    exception;
   no_course    exception;
   trm scores.term%type;
   lnum scores.lineno%type;
   p1 integer;
   p2 integer;
   fname students.fname%type;
   lname students.lname%type;
   total number(5,2) := 0;
   comp_points integer := 0;
   aa integer;
   bb integer;
   cc integer;
   dd integer;
   best number(5,2);
   current number(5,2);
   
   cursor c1 is 
      select components.compname,points,maxpoints , weight
      from   scores,components
      where  sid = id and scores.term = trm and scores.lineno = lnum and
             components.compname = scores.compname and
             components.term = scores.term and
             components.lineno = scores.lineno and
 	     (components.authority = teachername);

begin
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if (access_check != 'Student') then
		raise no_access;
   end if;

   
   select C.fname, C.lname into fname, lname
   from Students C
   where (C.sid = id) and (C.authority = teachername);

   htp.htmlOpen;
   
   htp.headOpen;
   htp.title('Display Scores');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
   htp.header(2,fname|| ' '||lname||'''s Grade Report');
   htp.nl;
     if (tcl is NULL) then
     raise no_course;
   end if;
   p1 := instr(tcl,',',1,2)+1;
   p2 := instr(tcl,')',1,1)-1;
   trm := substr(tcl,2,instr(tcl,',',1)-2);
   lnum := to_number(substr(tcl,p1,p2-p1+1));

select C.a,C.b,C.c,C.d
into   aa,bb,cc,dd
from   courses C
where  C.term = trm and
       C.lineno = lnum;
 
   htp.print('Student ID: '||htf.bold(id)); htp.br;
   htp.print('Term: '||htf.bold(trm)||'<br>');
   htp.print('Computer Number: '||htf.bold(lnum)||'<br>');
   htp.para;

   htp.tableOpen(cattributes => 'border=2 width=60%' );
   htp.tableRowOpen;
   htp.tableHeader('Component Name');
   htp.tableHeader('Points');
   htp.tableHeader('Max Points');
   htp.tableHeader('Weight');
   htp.tableRowClose;
   for scr_rec in c1 loop
     htp.tableRowOpen;
     htp.tableData(scr_rec.compname,'left');
     htp.tableData(scr_rec.points,'right');
     htp.tableData(scr_rec.maxpoints,'right');
     htp.tableData(scr_rec.weight||'%','right');
     total := total + (scr_rec.points / scr_rec.maxpoints)* scr_rec.weight;
     comp_points := comp_points + scr_rec.weight;
     htp.tableRowClose;
     counter := counter + 1;
   end loop;
   htp.tableClose;
   htp.nl;
   htp.print('Number of records: ');
   htp.print(counter);
   htp.br;
   htp.print('Your points average is: '||htf.bold(total) || '/' ||
                                         htf.bold(comp_points));
   htp.br;
   best := round(total + (100.0 - comp_points));
   current := (total/comp_points)*100;
   htp.print('Current Average/Grade: ');
   htp.print(htf.bold(current));
   if (current >= aa) then
     htp.print(htf.bold('/ A'));
   elsif (current >=bb) then
     htp.print(htf.bold('/ B'));
   elsif (current >=cc) then
     htp.print(htf.bold('/ C'));
   elsif (current >=dd) then
     htp.print(htf.bold('/ D'));
   else
     htp.print(htf.bold('/ F'));
   end if;
   htp.br;
   htp.print('Best Average/Grade Possible: ');
   htp.print(htf.bold(best));
   if (best >= aa) then
     htp.print(htf.bold('/ A'));
   elsif (best >=bb) then
     htp.print(htf.bold('/ B'));
   elsif (best >=cc) then
     htp.print(htf.bold('/ C'));
   elsif (best >=dd) then
     htp.print(htf.bold('/ D'));
   else
     htp.print(htf.bold('/ F'));
   end if;


   htp.para;
   htp.print('<form><input type=button Onclick=history.go(-1) value="Back to the Main Menu"></form>');
   htp.bodyClose;
   htp.htmlClose;
Exception
   when No_course then
      htp.print('The instructor has not added your name to any class.
	Please contact your instructor for help.');
   when NO_DATA_FOUND then
      htp.print('No records found');
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Something wrong in display_own_scores');
      htp.br;
      htp.print('SQLCODE = ' || SQLCODE);
      htp.br;
      htp.print('SQLERRM = ' || SQLERRM);
end display_own_scores;

procedure display_graphs (id in varchar2 default NULL,
			  tcl in varchar2 default NULL,
			  u_access in varchar2 default NULL,
			  teachername in varchar2 default NULL
			) AS

   trm scores.term%type;
   lnum scores.lineno%type;
   p1 integer;
   p2 integer; 
   nscores integer; 
no_access exception;
access_check varchar2(9);
 cursor c1 is
  select A.compname
  from scores A
  where (A.sid = id) and (A.term = trm) and
	(A.lineno = lnum) and
 	(A.authority = teachername);


BEGIN
  if (u_access is NULL) then
    raise no_access;
  end if;
  access_check := user_access.check_access(u_access);
  if (access_check != 'Student') then
    raise no_access;
  end if;
  if (tcl is NULL) then
    raise No_Data_Found;
  end if;
   p1 := instr(tcl,',',1,2)+1;
   p2 := instr(tcl,')',1,1)-1;
   trm := substr(tcl,2,instr(tcl,',',1)-2);
   lnum := to_number(substr(tcl,p1,p2-p1+1));
  htp.htmlOpen;
  htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
  htp.header(1,'Student''s Standing in the Class');

  select count(A.compname)
  into nscores
  from scores A
  where (A.sid = id) and (A.term = trm) and
	(A.lineno = lnum) and
 	(A.authority = teachername);
  if (nscores = 0) then
     raise no_data_found;
  end if;
  htp.formOpen(owa_util.get_owa_service_path ||'display.graphs');
  htp.print('Select the graph you want to see:<p>');
  htp.formHidden('id',id);
  htp.formHidden('term',trm);
  for crec in c1 loop
    htp.print(crec.compname);
    htp.formRadio('compname', crec.compname);
    htp.print(' ');
  end loop;
  htp.print('Cumulative Score');
  htp.formRadio('compname', 'cumulative');
  htp.print(' ');
  htp.formHidden('lineno',lnum);
  htp.formHidden('u_access',u_access);
  htp.formHidden('teachername',teachername);
  htp.para;
  htp.formSubmit(NULL,'Proceed');
  htp.formClose;

  htp.bodyClose;
  htp.htmlClose;
  EXCEPTION
    when no_data_found then
       htp.print('No information is available. Please check back later.');
       htp.print('<form><input type="button" Onclick="history.go(-1)" Value="Back to Main Menu"></form>');
 
       htp.bodyClose;
       htp.htmlClose;
    when no_access then
	Raise_no_access('gradebook');
    when others then
       htp.print('There is something wrong in the display_graphs.');
       htp.para;
       htp.print('SQLERRM : ' ||SQLERRM);


END display_graphs;

procedure graphs (id in varchar2,
		   term in varchar2,
		   compname in varchar2 default NULL,
		   lineno in varchar2,
		   u_access in varchar2,
		   teachername in varchar2 default NULL
		) AS


no_access exception;
no_compname exception;

access_check varchar2(9);
student_points number(4);
comp_points number(4);
total_points number(4);
counter number(2);
average number(5,2);
cursor c1 is
  select A.sid, A.points
  from scores A 
  where A.term = graphs.term and A.lineno = graphs.lineno
        and A.compname = graphs.compname and
  	(A.authority = teachername)
  order by A.points desc;

Begin
if (compname = 'cumulative') then
  display.graphs_cumulative(id,term,lineno,u_access,teachername);
else  
  if (u_access is null) then
   raise no_access;
  end if;
  access_check := user_access.check_access(u_access);
  if (access_check != 'Student') then
    raise no_access;
  end if;
  if (compname is NULL) then
    raise no_compname;
  end if;
  htp.htmlOpen;
  htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
  htp.header(2,compname||' Comparison Graph');
  htp.print(htf.bold('Term: ')||term);
  htp.br;
  htp.print(htf.bold('Line Number: ')||lineno);
  htp.br;
  htp.print(htf.bold('Student ID: ')||id);
  htp.br;
  htp.print(htf.bold('Component Name: ')||compname);
  total_points := 0;
  student_points := 0;

select C.maxpoints 
into comp_points 
from components C 
where C.term = graphs.term and
      C.lineno = graphs.lineno and
      C.compname = graphs.compname;

  counter := 0;
  htp.para;
  htp.hr;
  htp.bold('Graph of Student Standing Point in the Class vs Classmates');
  htp.tableOpen(cattributes=>'border=0, align=center');
  htp.TableRowOpen;
  for crec in c1 loop
    total_points := total_points + crec.points;
    counter := counter + 1;
    if (crec.sid = id) then
      if (crec.points > 100) then
       htp.tableData(crec.points ||'<br><img src="/ows-img/raj/bar1.gif" width="15"
            height="' ||crec.points||'" ALT="'||crec.points||'">',
	   'center', cattributes=>'valign="bottom"');
      else
       htp.tableData(crec.points ||'<br><img src="/ows-img/raj/bar1.gif" width="15"
            height="' ||crec.points||'" ALT="'||crec.points||'">',
	   'center', cattributes=>'valign="bottom"');
      end if;
      student_points := crec.points;
    else
      if (crec.points > 100) then
       htp.tableData(crec.points ||'<br><img src="/ows-img/raj/bar.gif" width="15"
            height="' ||crec.points||'" ALT="'||crec.points||'">',
	    'center',cattributes=>'valign="bottom"');
      else
       htp.tableData(crec.points ||'<br><img src="/ows-img/raj/bar.gif" width="15"
            height="' ||crec.points||'" ALT="'||crec.points||'">',
	    'center',cattributes=>'valign="bottom"');
      end if; 
    end if;
  end loop;
  htp.tableRowClose;
  htp.tableClose;
  htp.br;

  htp.fontOpen(null,null,-2);
  htp.print('<B>Legend:</b> <font color=Red>Red</font> bar stands for
	current user. <font color=blue>Blue</font> bars stand for other students
	in the class.');
  htp.fontClose;
  htp.hr;
  htp.para;
  htp.print('Your score is: '||htf.bold(student_points) || '/' ||
                                htf.bold(comp_points));
  htp.br;
  average := total_points/counter;
  htp.print('The average of the class is: '||htf.bold(average) || '/' ||
                                htf.bold(comp_points));
  htp.para;
  htp.print('<form><input type=button onclick=history.go(-2)
		value="back"></form>');
  htp.bodyClose;
  htp.htmlClose;
end if;

  EXCEPTION
   when no_compname then
     htp.print('You forgot to check a component. Please go back and choose
	one.');
   when no_access then
	Raise_no_access('gradebook');
   when No_Data_Found then
     htp.print('We can not find your grades. Please contact your 
		instructor for help.');
   when others then
     htp.print('There is something wrong in display.graph.sql<br>');
     htp.print('SQLERRM = '||SQLERRM);
end graphs;


procedure graphs_cumulative (id in varchar2,
		   term in varchar2,
		   --compname in varchar2 default NULL,
		   lineno in varchar2,
		   u_access in varchar2,
		   teachername in varchar2 default NULL
		) AS


no_access exception;
--no_compname exception;

access_check varchar2(9);
student_points number(5,2);
comp_points number(4);
total_points number(4);
counter number(2);
average number(5,2);
cursor c1 is
  select A.sid, round(sum((A.points/B.maxpoints)*B.weight)) cumulative_score
  from scores A, components B 
  where A.term = graphs_cumulative.term and 
        A.lineno = graphs_cumulative.lineno and 
        A.term = B.term and A.lineno = B.lineno and A.compname = B.compname and
        B.authority = teachername and
        A.authority = teachername
  group by A.sid
  order by cumulative_score desc;

Begin
  if (u_access is null) then
   raise no_access;
  end if;
  access_check := user_access.check_access(u_access);
  if (access_check != 'Student') then
    raise no_access;
  end if;
--  if (compname is NULL) then
--    raise no_compname;
--  end if;
  htp.htmlOpen;
  htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
  htp.header(2,'Cumulative Score Comparison Graph');
  htp.print(htf.bold('Term: ')||term);
  htp.br;
  htp.print(htf.bold('Line Number: ')||lineno);
  htp.br;
  htp.print(htf.bold('Student ID: ')||id);
  htp.br;
  --htp.print(htf.bold('Component Name: ')||compname);
  total_points := 0;
  student_points := 0;

select sum(C.weight) 
into comp_points 
from components C 
where C.term = graphs_cumulative.term and
      C.lineno = graphs_cumulative.lineno;

  counter := 0;
  htp.para;
  htp.hr;
  htp.bold('Graph of Student Standing Point in the Class vs Classmates');
  htp.tableOpen(cattributes=>'border=0, align=center');
  htp.TableRowOpen;
  for crec in c1 loop
    total_points := total_points + crec.cumulative_score;
    counter := counter + 1;
    if (crec.sid = id) then
      if (crec.cumulative_score > 100) then
       htp.tableData(crec.cumulative_score ||'<br><img src="/ows-img/raj/bar1.gif" width="15"
            height="' ||crec.cumulative_score||'" ALT="'||crec.cumulative_score||'">',
	   'center', cattributes=>'valign="bottom"');
      else
       htp.tableData(crec.cumulative_score ||'<br><img src="/ows-img/raj/bar1.gif" width="15"
            height="' ||2*crec.cumulative_score||'" ALT="'||crec.cumulative_score||'">',
	   'center', cattributes=>'valign="bottom"');
      end if;
      student_points := crec.cumulative_score;
    else
      if (crec.cumulative_score > 100) then
       htp.tableData(crec.cumulative_score ||'<br><img src="/ows-img/raj/bar.gif" width="15"
            height="' ||crec.cumulative_score||'" ALT="'||crec.cumulative_score||'">',
	    'center',cattributes=>'valign="bottom"');
      else
       htp.tableData(crec.cumulative_score ||'<br><img src="/ows-img/raj/bar.gif" width="15"
            height="' ||2*crec.cumulative_score||'" ALT="'||crec.cumulative_score||'">',
	    'center',cattributes=>'valign="bottom"');
      end if; 
    end if;
  end loop;
  htp.tableRowClose;
  htp.tableClose;
  htp.br;

  htp.fontOpen(null,null,-2);
  htp.print('<B>Legend:</b> <font color=Red>Red</font> bar stands for
	current user. <font color=blue>Blue</font> bars stand for other students
	in the class.');
  htp.fontClose;
  htp.hr;
  htp.para;
  htp.print('Your score is: '||htf.bold(student_points) || '/' ||
                                htf.bold(comp_points));
  htp.br;
  average := total_points/counter;
  htp.print('The average of the class is: '||htf.bold(average) || '/' ||
                                htf.bold(comp_points));
  htp.para;
  htp.print('<form><input type=button onclick=history.go(-2)
		value="back"></form>');
  htp.bodyClose;
  htp.htmlClose;

  EXCEPTION
--   when no_compname then
--     htp.print('You forgot to check a component. Please go back and choose
--	one.');
   when no_access then
	Raise_no_access('gradebook');
   when No_Data_Found then
     htp.print('We can not find your grades. Please contact your 
		instructor for help.');
   when others then
     htp.print('There is something wrong in display.graph.sql<br>');
     htp.print('SQLERRM = '||SQLERRM);
end graphs_cumulative;


end display;
/
show errors
