create or replace package insert_all as
procedure insert_catalog(u_access in varchar2,
 			 userid in varchar2 default NULL,
                         cnum     in varchar2,
                         cname    in varchar2);
procedure insert_course(u_access in varchar2,
 			 userid in varchar2 default NULL,
                        term_in in varchar2,
                        lnum in integer,
                        cnum in varchar2,
                        aa in integer,
                        bb in integer,
                        cc in integer,
                        dd in integer);
procedure insert_student(u_access in varchar2 default NULL,
 			 userid in varchar2 default NULL,
			 sid in varchar2,
			 fname in varchar2,
			 lname in varchar2,
			 middle in varchar2);

procedure insert_enrolls(u_access in varchar2 default NULL,
 			 userid in varchar2 default NULL,
		  term_in in varchar2 default NULL,
		  lnum in number default NULL,
		  chk in owa_util.ident_arr);

procedure insert_component(u_access in varchar2 default NULL,
 			 userid in varchar2 default NULL,
			term_in in varchar2,
			lnum in number,
			cname in varchar2,
			maxpts in integer,
			wght in integer);

procedure delete_enrolled_student(
			idlf in varchar2,
                         u_access in varchar2 default NULL,
 			 userid in varchar2 default NULL,
			term_in in varchar2,
			lnum in number);

procedure insert_scores(u_access in varchar2 default NULL,
 			 userid in varchar2 default NULL,
		  term_in in varchar2 default NULL,
		  lnum in number default NULL,
		  comp in varchar2 default NULL,
		  score_type in varchar2 default NULL,
   		  score_arr in owa_util.ident_arr,
		  ids in owa_util.ident_arr);


procedure insert_own_database(u_access in varchar2 default NULL,
			      userid in varchar2,
			      chk in owa_util.ident_arr);	
				
end ;

/
show errors


create or replace package body insert_all as
procedure insert_catalog(u_access in varchar2,
  			 userid in varchar2,
                         cnum     in varchar2,
                         cname    in varchar2) as
   access_check    varchar2(30);
   no_access       exception;
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('Insert Catalog');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;
   insert into catalog values 
    (upper(cnum),upper(cname),userid);
   commit;
   htp.print('Inserted (' || cnum || ',' || 
              cname || ') successfully');

   htp.nl;
   htp.nl;
   htp.formOpen(owa_util.get_owa_service_path ||
                 'menu.Teacher_menu');
   htp.formHidden('u_access',u_access);
   htp.formHidden('userid',userid);
   htp.formSubmit(NULL,'Back to the Main Menu');

   htp.bodyClose;
   htp.htmlClose;
   Exception
     when no_access then
	Raise_no_access('gradebook');
     when dup_val_on_index then
       htp.print('Duplicate catalog entry');
       htp.br;
       htp.anchor2(owa_util.get_owa_service_path ||
                     'menu.Teacher_menu?' ||
                     'u_access='||u_access||'&'||'userid='||userid,
                     'Back to the Main Menu');
       htp.bodyClose;
       htp.htmlClose;
     when others then
       htp.print('Other Error in insert_catalog');
       htp.print(SQLERRM);
       htp.br; 
       htp.anchor2(owa_util.get_owa_service_path ||
                     'menu.Teacher_menu?' ||
                     'u_access='||u_access||'&'||'userid='||userid,
                     'Back to the Main Menu');
       htp.bodyClose;
       htp.htmlClose;
end insert_catalog;


procedure insert_course(u_access in varchar2,
  			userid in varchar2,
                        term_in in varchar2,
                        lnum in integer,
                        cnum in varchar2,
                        aa in integer,
                        bb in integer,
                        cc in integer,
                        dd in integer) as

   access_check    varchar2(30);
   no_access       exception;

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('Insert Courses');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;

   insert into courses values 
     (upper(term_in),lnum,upper(cnum),aa,bb,cc,dd,userid);
   commit;
   htp.print('Inserted (' || term_in || ',' || lnum || ',' || cnum ||
             ',' || aa || ',' || bb || ',' || cc || ',' || dd || 
             ') successfully');

   htp.nl;

   htp.anchor2(owa_util.get_owa_service_path ||
                 'menu.Teacher_menu?' ||
                 'u_access='||u_access||'&'||'userid='||userid,
                 'Back to the Main Menu');
   htp.bodyClose;
   htp.htmlClose;

   Exception
     when no_access then
	Raise_no_access('gradebook');
     when dup_val_on_index then
       htp.print('Duplicate course entry');
       htp.br;
  htp.anchor2(owa_util.get_owa_service_path ||
                     'menu.Teacher_menu?' ||
                     'u_access='||u_access||'&'||'userid='||userid,
                     'Back to the Main Menu');
       htp.bodyClose;
       htp.htmlClose;
     when others then
       htp.print('Other Error in insert_course');
       htp.print(SQLERRM);
       htp.br;
       htp.anchor2(owa_util.get_owa_service_path ||
                     'menu.Teacher_menu?' ||
                     'u_access='||u_access||'&'||'userid='||userid,
                     'Back to the Main Menu');
       htp.bodyClose;
       htp.htmlClose;
end insert_course;

procedure insert_student (u_access in varchar2,
       			  userid in varchar2 default NULL,
			  sid in varchar2 default null,
			  fname in varchar2,
			  lname in varchar2,
			  middle in varchar2) as
   access_check    varchar2(30);
   no_access       exception;
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;
   insert into students

values(upper(sid),upper(fname),upper(lname),upper(middle),userid);
   commit;
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Insert students');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;

   htp.print('Inserted (' || sid || ',' || fname || ',' || lname ||
             ',' || middle || ') successfully');
   htp.nl;
   htp.anchor2(owa_util.get_owa_service_path ||
               'add_all.create_student?' ||
               'u_access='||u_access||'&'||'userid='||userid,
	      'Add another student');
   htp.br;
  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 dup_val_on_index then
        htp.print('Duplicate Student Entry');
        htp.br;
        htp.anchor2(owa_util.get_owa_service_path ||
               'add_all.create_student?' ||
               'u_access='||u_access||'&'||'userid='||userid,
	       'Add another student');
        htp.br;
        htp.anchor2(owa_util.get_owa_service_path ||
               'menu.teacher_menu?' ||
               'u_access='||u_access||'&'||'userid='||userid,
		 'Back to main menu');
     when others then
       htp.print('Other Error insert student');
       htp.print(SQLERRM);
       htp.br;
	 htp.anchor2(owa_util.get_owa_service_path ||
                     'menu.Teacher_menu?' ||
                     'u_access='||u_access||'&'||'userid='||userid,
                     'Back to the Main Menu');
end insert_student;

procedure insert_enrolls(u_access in varchar2 default NULL,
		  userid in varchar2 default NULL,
		  term_in in varchar2 default NULL,
		  lnum in number default NULL,
		  chk in owa_util.ident_arr) as

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

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;
   counter := 2;
   loop
      insert into enrolls(sid, term, lineno, authority)
         values(chk(counter), term_in, lnum, userid);
      counter := counter + 1;
   end loop;
Exception
   when no_data_found then
      if (counter = 2) then
         htp.print('NO NAMES WERE CHECKED');
         htp.nl;
	 goback('-1','Back to insert enrolls page');
      else
         htp.print(counter-2 || ' RECORDS ENTERED');
         htp.nl;
	 goback('-2','Back to Previous Page');
      end if;
   when no_access then
	Raise_no_access('gradebook');
   when DUP_VAL_ON_INDEX then
      htp.bodyOpen('ows-img/raj/fleck.jpg');
      htp.print('<h3>One or more students was added in the class. To
	prevent this error, you need to enroll one student at the time</h3>');
      htp.bodyClose;
   when others then
      htp.print('Error in insert_all.insert_enrolls');
      htp.para;
      htp.print(SQLERRM);
      htp.nl;
      goback;

end insert_enrolls;

procedure insert_component(u_access in varchar2 default NULL,
			userid in varchar2 default NULL,
			term_in in varchar2,
			lnum in number,
			cname in varchar2,
			maxpts in integer,
			wght in integer) as

   access_check       varchar2(30);
   no_access          exception;

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('Insert Course Component');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;


   insert into components values 
      (upper(term_in),upper(lnum),upper(cname),
      upper(maxpts),upper(wght), userid);
   commit;
   htp.print('Insert (' || term_in || ',' || lnum || ',' ||
                           cname || ',' || maxpts || ',' ||
                           wght || ') successful');
   htp.nl;
   htp.nl;
   goback;
   htp.bodyClose;
   htp.htmlClose;

   EXCEPTION
     when no_access then
	Raise_no_access('gradebook');
     when dup_val_on_index then
        htp.print('Duplicate Component Entry');
        htp.br;
	Goback('-2','Back to Main Menu');
     when others then
       htp.print('Other Error in insert_component');
       htp.print(SQLERRM);
       htp.br;

       goback('-2','Back to the Main Menu');
end insert_component;

procedure delete_enrolled_student(
			idlf in varchar2,
                        u_access in varchar2 default NULL,
			userid in varchar2 default NULL,
			term_in varchar2,
			lnum in number
) as

   access_check       varchar2(30);
   no_access          exception;
   id varchar2(9);

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('Delete enrolled student');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;
--htp.print('idlf='||idlf);
   id := substr(idlf,1,instr(idlf,' ')-1);
--htp.print('id='||id);

   delete from scores where
     sid = id and upper(term) = upper(term_in) and upper(lineno) = upper(lnum)
	and authority = userid;
   commit;     
 
   delete from enrolls where
     sid = id and upper(term) = upper(term_in) and upper(lineno) = upper(lnum)
	and authority = userid;
   commit;
   
   htp.print('Deleted Student ' || id || ' successfully');
   htp.nl;
   Goback('-2','Back to Main Menu');
   htp.nl;

   htp.bodyClose;
   htp.htmlClose;

   EXCEPTION
     when no_access then
	Raise_no_access('gradebook');
     when others then
       htp.print('Other Error; Could not delete student ' || id);
       htp.para;
       htp.print(SQLERRM);
       htp.br;
	Goback('-2','Back to Main Menu');
       htp.bodyClose;
       htp.htmlClose;
end delete_enrolled_student;


procedure insert_scores (u_access in varchar2 default NULL,
			userid in varchar2 default NULL,
		  term_in in varchar2 default NULL,
		  lnum in number default NULL,
		  comp in varchar2 default NULL,
		  score_type 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 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('Inserting Scores');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.nl;
   
   counter := 2;
   if (score_type = 'add') then
     loop
       insert into scores(sid, term, lineno, compname, points,authority)
	 values(ids(counter), term_in, lnum, comp,score_arr(counter),userid);
       counter := counter+1;
     end loop;
     commit;
   else
     loop
       if (score_arr(counter) is not NULL) then
       update scores
       set points=score_arr(counter)
       where sid=ids(counter) and term=term_in and lineno=lnum and
		compname=comp and authority=userid;
       end if;
       counter := counter+1;
     end loop;
   end if;


   EXCEPTION
     when no_access then
	Raise_no_access('gradebook');
     when no_data_found then 
       if (counter = 2) then
         htp.print('No DATA');
         htp.nl;
	 Goback('-3','Back to Main Menu');
         htp.nl;
         htp.bodyClose;
         htp.htmlClose;
       else 
	 if (score_type = 'add') then
           htp.print('<h3>Inserted scores successfully</h3>');
         else
           htp.print('<h3>Modified scores successfully</h3>');
         end if;
         htp.nl;
	 Goback('-3','Back to Main Menu');
         htp.nl;
         htp.bodyClose;
         htp.htmlClose;
       end if;
     when others then
       htp.print('<h3>You had added these scores previously. If you
	  want to update these score, you have to select the <font color=red>
	  Modify</font> button</h3>');
       htp.br;
	Goback('-3','Back to Main Menu');
       htp.bodyClose;
       htp.htmlClose;
end insert_scores;

procedure insert_own_database(u_access in varchar2 default NULL,
			      userid in varchar2,
			      chk in owa_util.ident_arr) AS


   counter 	integer;
   access_check	varchar2(30);
   no_access	exception;
   fname 	varchar2(30);
   lname 	varchar2(30);
   middle	varchar2(2);

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;
   counter := 2;
   loop
      select A.lname, A.fname, A.minit
      into lname, fname, middle
      from students A
      where A.sid = chk(counter);

      insert into students(sid, fname,lname, minit, authority)
         values(chk(counter), fname, lname, middle, userid);
      counter := counter + 1;
   end loop;
Exception
   when no_data_found then
      if (counter = 2) then
         htp.print('NO NAMES WERE CHECKED');
         htp.nl;
         htp.print('<FORM><INPUT TYPE=BUTTON ONCLICK=history.go(-1)
		Value="Back to Previous Section"></form>');
      else
         htp.print(counter-2 || ' RECORDS ENTERED');
         htp.nl;
         htp.print('<FORM><INPUT TYPE=BUTTON ONCLICK=history.go(-2)
		Value="Back to Previous Section"></form>');
      end if;
   when no_access then
	Raise_no_access('gradebook');
   when DUP_VAL_ON_INDEX then
      htp.print('<h3>One or more students was added in the database. To
	prevent this error, you need to insert one student at the time</h3>');
   when others then
      htp.print('Error in insert_all.insert_own_database');
      htp.para;
      htp.print(SQLERRM);
      htp.nl;


end insert_own_database;

end insert_all;
/
show errors
