create or replace package reports as
   procedure lookup ( u_access in varchar2 default NULL,
		      userid in varchar2 default NULL);
   procedure search_userid ( u_access in varchar2 default NULL,
		      teachername in varchar2 default NULL,
		      id in varchar2 default NULL);
   procedure display_students_in_database(u_access in varchar2 default NULL,
   					  userid in varchar2 
					);
   procedure display_own_scores(id in varchar2,
                                tcl in varchar2,
                                u_access in varchar2 default NULL);  
   procedure display_scores(u_access in varchar2 default NULL,
                          id in varchar2 default NULL,
                          term_in in varchar2,
                          lno in number,
                          cnum in varchar2);
   procedure display_report(u_access in varchar2 default NULL,
			  userid in varchar2 default NULL,
                          term_in in varchar2,
                          lno in number,
                          cnum in varchar2);

end reports;
/
show errors

create or replace package body reports as
   
procedure lookup ( u_access in varchar2 default NULL,
		      userid in varchar2 default NULL) AS
no_access exception;
access_check varchar(9);

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

  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.header(1,'Look Up Student''s Userid and Password.');
  htp.para;
  htp.formOpen(owa_util.get_owa_service_path||'reports.search_userid');
  htp.formHidden('u_access',u_access);
  htp.formHidden('teachername',userid);
  htp.print('This page allows you to search for a userid and password of
		students. Enter the student''s ID in the
		form below:');
  htp.para;
  htp.bold('Student ID: ');
  htp.formText('id',10,9);
  htp.formSubmit(NULL,'Search');
  htp.formReset;
  htp.formClose;
  htp.para;
  htp.anchor2(owa_util.get_owa_service_path||'menu.teacher_menu?'||
		'u_access='||u_access||'&'||'userid='||userid,'Back to
		Main Menu');
  htp.bodyClose;
  htp.htmlClose;
   EXCEPTION
      when no_access then
	Raise_no_access('gradebook');
      When others then
        htp.print('Errors in report.lookup.<p>');
        htp.print(SQLERRM);

End LookUp;

procedure search_userid ( u_access in varchar2 default NULL,
		      teachername in varchar2 default NULL,
		      id in varchar2 default NULL) AS
no_access exception;
no_id 		exception;
access_check varchar2(9);
firstname	varchar2(15);
lastname	varchar2(15);
username	varchar2(15);
password	varchar2(15);
middle 		varchar2(2);
	

BEGIN
  htp.htmlOpen;
  htp.headOpen;
  htp.print('<Meta HTTP-EQUIV=Pragma Content="no-cache">');
  htp.headClose;
  htp.bodyOpen('/ows-img/raj/fleck.jpg');

  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;
  if (id is NULL) then
    raise no_id;
  end if;

	  select A.fname, A.lname,A.minit, B.userid, B.password
 	  into firstname, lastname, middle, username, password
	  from Students A, whoami B
	  where (A.sid = id) and 
		(A.authority = search_userid.teachername) 
		and (B.sid = A.sid);

  htp.header(1,'Search Results: Student''s UserID and Password');
  htp.para;
  htp.bold('Student Name: ');
  htp.print(Lastname||', '||Firstname||' '||middle);
  htp.nl;
  htp.bold('UserID: ');
  htp.print(username);
  htp.nl;
  htp.bold('Password: ');
  htp.print(password);
  htp.nl;
  htp.para;
  htp.anchor2(owa_util.get_owa_service_path||'menu.teacher_menu?'||
		'u_access='||u_access||'&'||'userid='||teachername,'Back
			to Main Menu');
  htp.bodyClose;
  htp.htmlClose;
   EXCEPTION
      when no_access then
	Raise_no_access('gradebook');
     when no_id then
        htp.print('Student ID appear to be blank. Please back up and enter
		the Student ID.<p>');
 	htp.print('<form><input type=button Onclick=history.go(-1)
		Value="Back"></form>');
     When No_Data_Found then
        htp.print('We can not find the student ID: '||htf.bold(id)||' in
	  	our users database. This message may be a result of the
student not creating a userid and password or student not enrolling in the
class. Please back up and check the student id again.');
      GOBACK;

      When others then
        htp.print('Errors in reports.search_userid.<p>');
        htp.print(SQLERRM);
END SEARCH_USERID;

procedure display_students_in_database(u_access in varchar2 default NULL,
   					  userid in varchar2 default NULL
					) AS
no_access exception;
access_check varchar2(9);
cursor c1 is

  select distinct *
  from students A
  where (A.authority != userid)
  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.bodyOpen('/ows-img/raj/fleck.jpg');
  htp.header(1,'Students in Database');
  htp.para;
  htp.print('The following are names and IDs of students who are currently');
  htp.print('registered in the main database.');
  htp.para;
  htp.print('You can add these students into your database by checking in');
  htp.print('the box at the front of students names.');
  htp.para;


htp.formOpen(owa_util.get_owa_service_path||'insert_all.insert_own_database');
  htp.formHidden('u_access',u_access);
  htp.formHidden('userid',userid);
   htp.formHidden('chk', '0000');
   htp.tableOpen(Cattributes=>'border=1');
   htp.TableHeader;
   htp.tableHeader('Student ID');
   htp.tableHeader('Last Name');
   htp.tableHeader('First Name');
   
   for sid_rec in c1 loop
      htp.tableRowOpen('left','top');
      htp.tableData(htf.formCheckbox('chk',sid_rec.sid));
      htp.tableData(sid_rec.sid);
     
      htp.tableData(sid_rec.lname);
      htp.tableData(sid_rec.fname);
      htp.tableRowClose;
   end loop;
   htp.tableClose;
   
   htp.formSubmit(NULL,'Add these students to course');
   htp.formClose;
 htp.print('<form><input type=button onclick=history.go(-1) value="Back">
		</form>');
   htp.bodyClose;
   htp.htmlClose;
   EXCEPTION
      when no_access then
	Raise_no_access('gradebook');
      When others then
        htp.print('Errors in report.display_students_in_database.<p>');
        htp.print(SQLERRM);
END;

procedure display_own_scores(id in varchar2,
                             tcl in varchar2,
                             u_access in varchar2 default NULL) as
 
   access_check    varchar2(30);
   no_access       exception;
   p1 integer;
   p2 integer;
   term_in courses.term%type;
   lnum courses.lineno%type;
   cnum courses.cno%type;
 
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;
 
   p1 := instr(tcl,',',1,2)+1;
   p2 := instr(tcl,')',1,1)-1;
   term_in := substr(tcl,2,instr(tcl,',',1)-2);
   lnum := to_number(substr(tcl,p1,p2-p1+1));
   cnum := substr(tcl,instr(tcl,',',1,1)+1,
                      p1-instr(tcl,',',1,1)-2);

   reports.display_scores(u_access,id,term_in,lnum,cnum);   

Exception
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('Error in select_course');
end display_own_scores;


procedure display_scores(u_access in varchar2 default NULL,
                       id in varchar2 default NULL,
                       term_in in varchar2,
                       lno  in number,
                       cnum in varchar2) as

cursor chs is
   select F.term, F.lineno, F.compname, F.maxpoints, F.weight 
   from components F
   where F.term = term_in and
         F.lineno = lno;
chs_row chs%rowtype;

cursor rep1 is
   select E.sid, S.lname, S.fname
   from enrolls E, students S
   where S.sid = E.sid and
         E.term = term_in and
         E.lineno = lno;
rep1_row rep1%rowtype;

cursor rep2 is
   select C.compname, C.maxpoints, C.weight
   from components C
   where C.term = term_in and
         C.lineno  = lno;
rep2_row rep2%rowtype;

cursor rep3 is
   select points
   from scores
   where term = term_in and
         lineno = lno and
         sid = rep1_row.sid and
         compname = rep2_row.compname;
rep3_row rep3%rowtype;
access_check      varchar2(30);
name		  varchar2(40);
no_access         exception;
total             number;
scaleA            number;
scaleB            number;
scaleC            number;
scaleD            number;

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 a, b, c, d into scaleA, scaleB, scaleC, scaleD
   from courses
   where term = term_in and
         lineno = lno;

   htp.htmlOpen;
   htp.headOpen;
   htp.title('Generating a report');
   htp.headClose;

   select fname || ' ' || lname 
   into name
   from students
   where sid=id;

   htp.print('<h3>' || name || '''s Grade Report</h3>');
   htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
   htp.nl;
   htp.line;
   htp.print('COURSE NO: ' || cnum || '<br>');
   htp.print('TERM     : ' || term_in || '<br>');
   htp.print('LINE NUMBER: ' || lno);
   htp.line;

   htp.tableOpen(cattributes => 'border=2 width=60%');
   htp.tableRowOpen;
   htp.tableHeader('SID');
   htp.tableHeader('LNAME');
   htp.tableHeader('FNAME');

   open chs;
   loop
     fetch chs into chs_row;
     exit when chs%notfound;
     htp.tableHeader(UPPER(chs_row.compname)||'('||
                     chs_row.maxpoints||'/'||chs_row.weight||')');
   end loop;
   close chs;

   htp.tableHeader('AVERAGE');
   htp.tableHeader('GRADE');

   open rep1;
   loop 
      fetch rep1 into rep1_row;
      exit when rep1%notfound;

      total := 0.0;
      htp.tableRowOpen;

      htp.tableData(rep1_row.sid);
      htp.tableData(rep1_row.lname);
      htp.tableData(rep1_row.fname);

      open rep2;
      loop
         fetch rep2 into rep2_row;
         exit when rep2%notfound;
         open rep3;
         loop
            fetch rep3 into rep3_row;
            exit when rep3%notfound;
            total := total + 
                     ((rep3_row.points/rep2_row.maxpoints)*rep2_row.weight);
            htp.tableData(rep3_row.points);
         end loop;
         close rep3;
      end loop;
      close rep2;
      
      htp.tableData(round(total,2));

      if total >= scaleA then
         htp.tableData('A');
      elsif total >= scaleB then
         htp.tableData('B');
      elsif total >= scaleC then
         htp.tableData('C');
      elsif total >= scaleD then
         htp.tableData('D');
      else
         htp.tableData('F');
      end if;
      
   end loop;
   htp.tableClose;
   close rep1;
   
   htp.br;
   htp.br;
  htp.anchor2(owa_util.get_owa_service_path ||
                     'add_all.select_course?' ||
                     'u_access='||u_access,
                     'Back to the Previous Menu');

Exception
  when no_access then
	Raise_no_access('gradebook');
  when others then
    htp.print('Other error');
    htp.br;
   htp.anchor2(owa_util.get_owa_service_path ||
                'add_all.select_course?' ||
                'u_access='||u_access,
                'Back to the Previous Menu');
end display_scores;

procedure display_report(u_access in varchar2 default NULL,
		       userid in varchar2 default NULL,
                       term_in in varchar2,
                       lno  in number,
                       cnum in varchar2) as

cursor chs is
   select F.term, F.lineno, F.compname, F.maxpoints, F.weight 
   from components F
   where F.term = term_in and
         F.lineno = lno and
	 (F.authority = userid);
chs_row chs%rowtype;

cursor rep1 is
   select E.sid, S.lname, S.fname
   from enrolls E, students S
   where S.sid = E.sid and (S.authority = userid) and
         E.term = term_in and
         E.lineno = lno
   order by lname;

rep1_row rep1%rowtype;

cursor rep2 is
   select C.compname, C.maxpoints, C.weight
   from components C
   where C.term = term_in and (C.authority = userid) and
         C.lineno  = lno;
rep2_row rep2%rowtype;

cursor rep3 is
   select points
   from scores
   where term = term_in and (authority = userid) and
         lineno = lno and
         sid = rep1_row.sid and
         compname = rep2_row.compname;
rep3_row rep3%rowtype;
access_check      varchar2(30);
no_access         exception;
total             number;
scaleA            number;
scaleB            number;
scaleC            number;
scaleD            number;
Weight_count      number := 0.0;
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;

   select a, b, c, d into scaleA, scaleB, scaleC, scaleD
   from courses
   where term = term_in and
         lineno = lno;

   htp.htmlOpen;
   htp.headOpen;
   htp.title('Generating a report');
   htp.headClose;
   htp.header(2,'Grade Book Report');
   htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
   htp.nl;
   htp.line;
   htp.print('<b>COURSE NO:</b> ' || cnum || '<br>');
   htp.print('<b>TERM     :</b> ' || term_in || '<br>');
   htp.print('<b>LINE NUMBER:</b> ' || lno);
   htp.line;

   htp.tableOpen(cattributes => 'border=2 width=60%');
   htp.tableRowOpen;
   htp.tableHeader('SID');
   htp.tableHeader('LNAME');
   htp.tableHeader('FNAME');

   open chs;
   loop
     fetch chs into chs_row;
     exit when chs%notfound;
     htp.tableHeader(UPPER(chs_row.compname)||'('||
                     chs_row.maxpoints||'/'||chs_row.weight||')');

     Weight_count := Weight_count + chs_row.weight;
   end loop;
   close chs;

   htp.tableHeader('AVERAGE');
   htp.tableHeader('GRADE');

   open rep1;
   loop 
      fetch rep1 into rep1_row;
      exit when rep1%notfound;

      total := 0.0;
      htp.tableRowOpen;

      htp.tableData(rep1_row.sid);
      htp.tableData(rep1_row.lname);
      htp.tableData(rep1_row.fname);

      open rep2;
      loop
         fetch rep2 into rep2_row;
         exit when rep2%notfound;
         open rep3;
         loop
            fetch rep3 into rep3_row;
            exit when rep3%notfound;
            total := total + 
                     ((rep3_row.points/rep2_row.maxpoints)*rep2_row.weight);
            htp.tableData(rep3_row.points);
         end loop;
         close rep3;
      end loop;
      close rep2;
      
      htp.tableData(round(total,2));
    if (Weight_Count = 100) then
      if total >= scaleA then
         htp.tableData('A');
      elsif total >= scaleB then
         htp.tableData('B');
      elsif total >= scaleC then
         htp.tableData('C');
      elsif total >= scaleD then
         htp.tableData('D');
      else
         htp.tableData('F');
      end if;
   Else
     htp.tableData('N/A');
   END IF;
      
   end loop;
   htp.tableClose;
   close rep1;
   
   htp.br;
   htp.br;
	goback('-3','Back to Main Menu');
Exception
  when no_access then
	Raise_no_access('gradebook');
  when others then
    htp.print('Other error');
    htp.br;
	goback('-3','Back to Main Menu');
end display_report;

end reports;  -- end package body
/ 
show errors

