create or replace procedure get_access as
begin
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Get password');
   htp.header(1, 'Grade Book Access Page');
   htp.headClose;
   htp.bodyOpen;
   htp.formOpen(owa_util.get_owa_service_path ||
                'start_session');
   htp.nl;
   htp.nl;
   htp.print('Today is ' || 
             to_char(sysdate, 'Day') || '  ' || 
             to_char(sysdate, 'Dd / Mon / YYYY HH:MI AM'));
   htp.nl;
   htp.nl;

   htp.tableOpen;
   htp.tableRowOpen;
   htp.tableData(htf.strong('USER ID: '));
   htp.tableData(htf.formText('usid', 20, 50));
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData(htf.strong('PASSWORD: '));
   htp.tableData(htf.formPassword('passwd', 20, 50));
   htp.tableRowClose;
   htp.tableClose;

   htp.formSubmit(NULL, ' Proceed ');
   htp.formReset;
   htp.formClose;
   htp.bodyClose;
   htp.htmlClose;
Exception
   when others then
      htp.print('error in get_access');
end get_access;
/
show errors
create or replace procedure start_session(usid in varchar2   DEFAULT NULL,
                        passwd in varchar2 DEFAULT NULL) AS

   user_buffer    varchar2(15);
   passwd_buffer  varchar2(15);
   time_buffer    date;
   auth_buffer    varchar2(15);
   random_num     varchar2(10);
   access_buffer  varchar2(30);
   bad_passwd     exception;

begin
   select A.userid, A.password, A.lastAccess, A.authority
   into   user_buffer, passwd_buffer, time_buffer, 
          auth_buffer
   from   users A
   where  upper(A.userid)=upper(start_session.usid);

   if start_session.passwd != passwd_buffer then
      raise bad_passwd;
   end if;
   random_num := to_char(sysdate, 'SSSSS');
   access_buffer := user_buffer || ',' || random_num;
   update users
   set u_access = access_buffer,
       lastAccess = sysdate
   where users.userid=user_buffer;
   commit;

   teacher_menu(access_buffer,user_buffer);

Exception
   when bad_passwd then
      htp.print('Bad password ');
   when no_data_found then
      htp.print('Invalid user');
   when others then
      htp.print('Error in start_session');
end start_session; 
/
show errors
create or replace function check_access(u_access in varchar2 default NULL) return varchar2
as

   return_value varchar(30);
   auth_buffer  varchar(20);
   no_access    exception;

begin
   if u_access is null
      then raise no_access;
   end if;
   select authority into auth_buffer
   from users U
   where U.u_access = check_access.u_access;
   return_value := auth_buffer;
        
   return(return_value);
   Exception
      when no_data_found then return('0');
      when no_access then return ('0');
      when others then
         htp.print('error in check_access');

end check_access;
/
show errors
create or replace procedure teacher_menu(u_access in varchar2 default NULL,
		 userid in varchar2 default NULL)

as
   access_check varchar2(30);
   no_access    exception;
begin
   if (u_access is NULL) then
      raise no_access;   
   end if;
   access_check := check_access(u_access);
   if  (access_check != 'Teacher') then
      raise no_access;
   end if;
   htp.htmlOpen;
   htp.headOpen;
   htp.htitle('Teacher Menu');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.formOpen(owa_util.get_owa_service_path ||
               'process_teacher_option');
   htp.formHidden('u_access', u_access);
   htp.formHidden('userid', userid);
   htp.formSubmit('opt','Add Catalog Entry'); htp.br;
   htp.formSubmit('opt','Add Course Entry'); htp.br;
   htp.formSubmit('opt','Select a Course'); htp.br;
   htp.formSubmit('opt','Search Userid-Password'); htp.br;
   htp.formSubmit('opt','Change Password'); htp.br;
   htp.formSubmit('opt','Logout'); htp.br;
   htp.formclose;
   htp.bodyClose;
   htp.htmlClose;
Exception
   when no_access then
	htp.print('No Access to Gradebook');
   when others then
      htp.print('Error in teacher_menu');
      htp.br;
      htp.print(SQLERRM);
end teacher_menu;
/
show errors
create or replace procedure process_teacher_option(
   opt in varchar2 default null,
   u_access in varchar2 default null,
   userid in varchar2 default null) as
begin
--   if (opt = 'Add Catalog Entry') then
--     add_all.create_catalog(u_access);
--   elsif (opt = 'Add Student') then
--     add_all.create_student(u_access);
--   elsif (opt = 'Add Course Entry') then
--     add_all.create_course(u_access);
   if (opt = 'Select a Course') then
     select_course(u_access,userid);
--   elsif (opt = 'Change Password') then
--     user_access.change_password(u_access);
--   elsif (opt = 'Logout') then
--     user_access.logout(u_access);
   end if;
end;
/
show errors
create or replace procedure select_course(
    u_access in varchar2 default NULL,
    userid in varchar2 default NULL) as

   access_check    varchar2(30);
   no_access       exception;
   cursor c1 is
     select courses.term,cno,courses.lineno
     from courses;
begin
   if (u_access = NULL) then
      raise no_access;
   end if;
   access_check := check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Select Course');
   htp.header(1,'Select Course ');
   htp.headClose;
   htp.bodyOpen;
   htp.FormOpen(owa_util.get_owa_service_path || 
                'process_teacher_sub_option');
   htp.nl;
   htp.formSelectOpen('tcl','Select Course: ');
   for c1_rec in c1 loop
     htp.formSelectOption('(' ||c1_rec.term||','||
                          c1_rec.cno||','||
                          c1_rec.lineno || ')');
   end loop;
   htp.formSelectClose;
   htp.br; htp.br;
   htp.formHidden('u_access', u_access);
   htp.formHidden('userid', userid);
   htp.formSubmit('opt','Add Students to Course');  
   htp.br;
   htp.formSubmit('opt','Add Component to Course');  
   htp.br;
   htp.formSubmit('opt','Add Student Score');  
   htp.br;
   htp.formSubmit('opt','Modify Student Score');  
   htp.br;
   htp.formSubmit('opt','Drop Student from Course');  
   htp.br;
   htp.formSubmit('opt','Print Course Report');  
   htp.br;
   htp.formClose;
   htp.nl;
   htp.nl;
   htp.anchor2('http://tinman.cs.gsu.edu:7777/' ||
     'pls/book/Teacher_menu?' ||
     'u_access='||u_access,
     'Back to the Main Menu');
   htp.bodyClose;
   htp.htmlClose;
Exception
   when no_access then
      htp.print('You are not logged on');
   when others then 
      htp.print('Error in select_course');
end select_course;
/
show errors
create or replace procedure process_teacher_sub_option(u_access in varchar2,
                      userid in varchar2,
                      opt in varchar2,
                      tcl in varchar2) 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 = NULL) then 
      raise no_access;
   end if;
   access_check := check_access(u_access);
   if (access_check != 'Teacher') 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);
--   if (opt = 'Add Students to Course') then
--     add_enrolled_students(
--         u_access,term_in,lnum,cnum);
--   elsif (opt = 'Add Component to Course') then
--     add_course_components(
--         u_access,term_in,lnum,cnum);
   if (opt = 'Add Student Score') then
     add_scores(
         u_access,userid,term_in,lnum,cnum);
--   elsif (opt = 'Modify Student Score') then
--     modify_student_score(
--         u_access,term_in,lnum,cnum);
--   elsif (opt = 'Drop Student from Course') then
--     drop_enrolled_student(
--         u_access,term_in,lnum,cnum);
--   elsif (opt = 'Print Course Report') then
--     display_report(
--         u_access,term_in,lnum,cnum);
   end if;
Exception
   when no_access then
      htp.print('You are not logged on');
   when others then 
      htp.print('error in process_teacher_option');
end process_teacher_sub_option;
/
show errors
create or replace procedure add_scores(
    u_access in varchar2 default NULL,
    userid in varchar2 default NULL,
    term_in in varchar2 default NULL,
    lnum in number default NULL,
    cnum in varchar2 default NULL) as

   access_check       varchar2(30);
   no_access	      exception;

   cursor c1 is
      select term, lineno, compname 
      from   components
      where  term = term_in and lineno = lnum;
begin
   if (u_access = NULL) then
      raise no_access;
   end if;

   access_check := check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;

   htp.htmlOpen;
   htp.headOpen; 
   htp.title('Selecting Component');
   htp.header(1,'Selecting Component');
   htp.headClose;
   htp.bodyOpen;
   htp.formOpen(owa_util.get_owa_service_path || 
                'process_scores');
   htp.nl;

   htp.formSelectOpen('comp',
            'Choose the course component: ');
   for crec in c1 loop
     htp.formSelectOption(crec.compname);
   end loop;
   htp.formSelectClose;
   htp.formHidden('term_in', term_in);
   htp.formHidden('cnum', cnum);
   htp.formHidden('lnum', lnum);
   htp.formHidden('u_access', u_access);
   htp.formHidden('userid', userid);

   htp.formSubmit;
   htp.formClose;
   htp.bodyClose;
   htp.htmlClose;
Exception
   when no_access then
     htp.print('You are not logged on');
   when others then 
     htp.print('Something is wrong in add_scores');
end add_scores;
/
show errors
create or replace procedure process_scores (
    u_access in varchar2 default NULL,
    userid in varchar2 default NULL,
    term_in in varchar2 default NULL,
    lnum in integer default NULL,
    comp in varchar2 default NULL,
    cnum in varchar2 default NULL) as

   access_check    varchar2(30);
   no_access       exception;

   cursor c1 is
      select distinct B.lname, B.fname, 
             A.sid, A.term, A.lineno  
      from enrolls A, students B
      where (A.term = term_in) and 
            (A.lineno = lnum) and
            (A.sid = B.sid);
begin 

   if (u_access = NULL) then
      raise no_access;
   end if;

   access_check := check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;

   htp.htmlOpen;
   htp.headOpen;
   htp.title('Entering Scores');
   htp.header(1,'Entering Scores for ' || cnum || 
                 ',' || lnum || ',' || term_in);
   htp.headClose;
   htp.bodyOpen;
   htp.formOpen(owa_util.get_owa_service_path || 
	        'insert_scores');
   htp.formHidden('u_access', u_access);
   htp.formHidden('userid', userid);
   htp.formHidden('term_in', term_in);
   htp.formHidden('lnum', lnum);
   htp.formHidden('comp', comp);
   htp.formHidden('score_arr', '000');
   htp.formHidden('ids', '0000');

   htp.print('Component: ');
   htp.print(comp);
   htp.nl;
   htp.nl;

   htp.tableOpen;
   for crec in c1 loop
     htp.tableRowopen;
     htp.tableData(htf.strong(crec.lname||', ' 
                              || crec.fname));
     htp.tableData(htf.formText('score_arr', 3, 3));
         htp.formHidden('ids', crec.sid);
     htp.tableRowClose;
   end loop;
   htp.tableClose;
   htp.formSubmit(NULL,'Enter the scores');
   htp.formclose;
   htp.bodyClose;
   htp.htmlClose;
Exception
   when no_access then
     htp.print('You are not logged on');
   when others then 
     htp.print('Something is wrong in process_scores');

end process_scores;
/
show errors
create or replace procedure insert_scores(u_access in varchar2 default NULL,
   userid in varchar2,
		  term_in in varchar2 default NULL,
		  lnum in number default NULL,
		  comp in varchar2 default NULL,
		  score_arr in owa_util.ident_arr,
		  ids in owa_util.ident_arr) as

   access_check    varchar2(30);
   no_access       exception;
   counter         integer;

begin
   if (u_access = NULL) then
      raise no_access;
   end if;

   access_check := check_access(u_access);
   if (access_check != 'Teacher') then
      raise no_access;
   end if;

   htp.htmlOpen;
   htp.headOpen;
   htp.title('Inserting Scores');
   htp.headClose;
   htp.bodyOpen;
   htp.nl;

   counter := 2;
   loop
     insert into scores(sid, term, lineno, compname, points)
	 values(ids(counter), term_in, lnum, comp, score_arr(counter));
htp.print(ids(counter)||','||term_in||','||lnum||','||comp||','||score_arr(counter));
htp.nl;
     counter := counter+1;
   end  loop;
   commit;

   EXCEPTION
     when no_access then
        htp.print('You are not logged on');
        htp.anchor2('http://tinman.cs.gsu.edu:7777/' ||
          'pls/book/get_access',
          'Back to sign in page');
        htp.bodyClose;
        htp.htmlClose;
     when no_data_found then 
       if (counter = 2) then
         htp.print('No DATA');
         htp.nl;
         htp.anchor2('http://tinman.cs.gsu.edu:7777/' ||
          'pls/book/select_course?' ||
          'u_access='||u_access, 'Back to Previous Menu');
         htp.nl;
         htp.bodyClose;
         htp.htmlClose;
       else
         htp.print('Inserted scores successfully');
         htp.nl;
         htp.anchor2('http://tinman.cs.gsu.edu:9999/' ||
          'ows-bin/book/owa/add_all.select_course?' ||
          'u_access='||u_access, 'Back to Previous Menu');
         htp.nl;
         htp.bodyClose;
         htp.htmlClose;
       end if;
     when others then
       htp.print('Other Error');
       htp.br;
       htp.anchor2('http://tinman.cs.gsu.edu:9999/' ||
          'ows-bin/book/owa/add_all.select_course?' ||
                     'u_access='||u_access,
                     'Back to the Main Menu');
       htp.bodyClose;
       htp.htmlClose;
end insert_scores;
/
show errors
