create or replace package user_access as
   procedure get_access;
   procedure get_access_st;
   function  check_access(u_access in varchar2 default NULL) return varchar2;
   procedure logout(u_access in varchar2 default NULL);
   procedure start_session(usid in varchar2 default NULL,
                           passwd in varchar2 default NULL);
   procedure start_session_st(usid in varchar2 default NULL,
                             passwd in varchar2 default NULL,
                             id in varchar2);
   procedure change_password(u_access in varchar2 default NULL);
   procedure change_it(u_access in varchar2 default NULL,
                       u_id in varchar2,
                       u_passwd varchar2,
                       old_password in varchar2,
                       new_password1 in varchar2,
                       new_password2 in varchar2);
   procedure new_user;
   procedure create_user (   opt in varchar2,
			     sid in varchar2,
			     usid in varchar2,
			     passwd1 in varchar2,
			     passwd2 in varchar2);
end user_access;
/
show errors


create or replace package body user_access as

procedure get_access as
begin
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Get password');
   htp.header(2, 'Teacher Access');
   htp.headClose;
   htp.bodyOpen;
   htp.formOpen(owa_util.get_owa_service_path || 
                'user_access.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, 15));
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData(htf.strong('PASSWORD: '));
   htp.tableData(htf.formPassword('passwd', 20, 15));
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData;
   htp.tableData(htf.formSubmit(null, ' Proceed ') || htf.formReset);
   htp.tableRowClose;
   htp.tableClose;
   htp.formClose;
   htp.para;
   htp.bodyClose;
   htp.htmlClose;
Exception
   when others then
      htp.print('There is something wrong in user_access.get_access');
end get_access;

procedure get_access_st as
begin
--   htp.htmlOpen;
--   htp.headOpen;
--   htp.title('Get password');
   htp.header(2, ' Student Access');
--   htp.headClose;
--   htp.bodyOpen;
   htp.formOpen(owa_util.get_owa_service_path || 
                'user_access.start_session_st');
--   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, 15));
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData(htf.strong('PASSWORD: '));
   htp.tableData(htf.formPassword('passwd', 20, 15));
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData(htf.strong('STUDENT ID: '));
   htp.tableData(htf.formPassword('id', 20, 9));
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData;
   htp.tableData(htf.formSubmit(NULL, ' Proceed ') || htf.formReset);
   htp.tableRowClose;
   htp.tableClose;
   htp.formClose;
   htp.para;
--START ADDING NEW PROCEDURES BY HUNG NGUYEN 10/31/98
Exception
   when others then
      htp.print('There is something wrong in user_access.get_access_st');
end get_access_st;

procedure new_user as
begin
   htp.htmlOpen;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.fontOpen('blue');
   htp.header(1,'Choose Your Own UserID and Password','center');
   htp.fontClose;
   htp.para;
   htp.print('If you are a <b>teacher</b>, please contact the system
		administrator for a <b>system ID</b> and <b>UserID</b>.');
   htp.br;
   htp.print('If you are a <b>student</b>, your system ID is your 9 digits 
		<b>student ID</b>');
   htp.formOpen(owa_util.get_owa_service_path ||'user_access.create_user');
   htp.para;
   htp.print('<h4>Please check one of the following: ');
   htp.formRadio('opt','Teacher');
   htp.print('Teacher ');
   htp.formRadio('opt','Student','checked');
   htp.print('Student <br></h4>');
  

   htp.tableOpen(cattributes=>'border=0');
   htp.tableRowOpen('left','top');
   htp.tableData('Enter the system ID:');
   htp.tableData(htf.formText('sid',15,9));
   htp.tableRowClose;
   htp.tableRowOpen('left','top');
   htp.tableData('Choose an userid:');
   htp.tableData(htf.formText('usid',15,15));
   htp.tableRowClose;
   htp.tableRowOpen('left','top');
   htp.tableData('Choose a password:');
   htp.tableData(htf.formPassword('passwd1',15,15));
   htp.tableRowClose;
   htp.tableRowOpen('left','top');
   htp.tableData('Re-type password: ');
   htp.tableData(htf.formPassword('passwd2',15,15));
   htp.tableRowClose;
   htp.tableRowOpen('left','top');
   htp.tableData(' ');
   htp.tableData(htf.formSubmit(NULL,'Proceed')||' '||htf.formReset);
   htp.tableClose;
   htp.formClose;
   goback;
   htp.bodyClose;
   htp.htmlClose;
   EXCEPTION
     when others then
       htp.print('Something goes wrong in user_access.new_user');
       htp.print(SQLERRM);
END new_user;
   
PROCEDURE create_user (   opt in varchar2 default 'Student',
   			  sid in varchar2 default NULL,
			  usid in varchar2 default NULL,
			  passwd1 in varchar2 default NULL,
			  passwd2 in varchar2 default NULL) AS
access_buffer varchar2(30);
passwd_mismatch exception;
blank_field exception;
no_opt exception;
student_fname varchar2(20);
student_lname varchar2(20);
user_sid varchar2(9);
teacher_fname varchar2(20);
teacher_lname varchar2(20);

user_buffer varchar2(15);
BEGIN
   if (opt is NULL) then
     raise no_opt;
   end if;
   
   if ( (sid is null) or (usid is null) or 
         (passwd1 is NULL) or (passwd2 is NULL) ) then 
   raise blank_field;
   end if;
   if (passwd1 != passwd2) then
      raise passwd_mismatch;
   end if;

   
   If (opt = 'Student') then
     --select students.sid, students.fname, students.lname 
     select distinct students.sid
     into user_sid
     from students
     where students.sid = create_user.sid ;
   elsif (opt ='Teacher') then
     select teachers.sid, teachers.fname, teachers.lname
     into user_sid, teacher_fname, teacher_lname
     from teachers
     where teachers.sid = create_user.sid;
   end if;
   
   insert into whoami values 
     (user_sid,usid, passwd1, NULL, NULL, opt);
   
   htp.htmlOpen;
   htp.bodyOpen('/ows-img/raj/bgchalk.jpg');
   if (opt = 'Student') then
   htp.header(2,'Welcome to the Grade Book system.');
   else
   htp.header(2,'Welcome, professor '||teacher_fname||' '||teacher_lname||
		' to the Grade Book system.');
   end if;
   htp.para;
   htp.print('Your Userid and Password have been added successfully.');
   htp.br;
   htp.para;
   htp.print('Now, you can go back to the login page to log in the system.');
   htp.para;
   htp.anchor2(owa_util.get_owa_service_path||'gradebook','Back to Log in
	Page');
   htp.bodyClose;
   htp.htmlClose;
   
   EXCEPTION
     when no_opt then
       htp.print('Please select an option.');
     when No_Data_Found then
      if (opt='Student') then
       htp.print('We can not find your name in our database. If you have register');
       htp.print('for the class, please contact your instructor for help.');
       htp.print('<p><form><input type=button Onclick=history.go(-1) value="Back"></form>');
      else
       htp.print('We can not find your name in our database. Please
	contact the system administrator for a verification of system ID, first
	name, and last name.');
       htp.print('<p><form><input type=button Onclick=history.go(-1) value="Back"></form>');
      end if;
     when passwd_mismatch then
       htp.print('Your passwords do not match with each other. Please go back
         to the previous page to re-type them and hit Proceed again.');
       goback;  
     when blank_field then
       htp.print('Sorry, we can not process your request at this time.Reason:
 	    some of the fields are blank.');
       htp.print('<br>Please go back to the previous page to fill out ALL fields
		and hit Proceed again.');
       goback;
     when DUP_VAL_ON_INDEX then
       htp.print('The System ID, '||htf.bold(sid)|| ', has been used by
	  somebody. Please check your System ID for mistyping. If you
	  still receive this message, contact your instructor or system
	  administrator (teacher only) to verify your System ID.');
       htp.print('<p><form><input type=button Onclick=history.go(-1) value="Back"></form>');

     when others then
       htp.print('Something goes wrong in user_access.create_user.<br>');
       htp.print(SQLERRM);
END create_user;


-- END OF ADDING BY HUNG NGUYEN 10/31/98

procedure start_session(usid in varchar2   default NULL,
                        passwd in varchar2 default NULL) as

   user_buffer     varchar(15);
   passwd_buffer   varchar(15);
   time_buffer     date;
   auth_buffer     varchar(15);
   random_num     varchar(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 whoami A
      where A.userid = 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;


-- updating the user record
   update whoami
      set u_access = access_buffer,
          lastAccess = sysdate
   where whoami.userid=user_buffer;
   commit;

   menu.teacher_menu(access_buffer,user_buffer);

Exception
   when bad_passwd then
      htp.print('Bad password ');
   when no_data_found then
      htp.print('We cannot find you in my database as a user');
   when others then
      htp.print('something wrong in user_access.start_session');
      htp.br;
      htp.print(SQLERRM);
end start_session;   -- end start_session

procedure start_session_st(usid in varchar2   default NULL,
                           passwd in varchar2 default NULL,
                           id in varchar2) as

   user_buffer     varchar(15);
   passwd_buffer   varchar(15);
   time_buffer     date;
   auth_buffer     varchar(15);
   sid_buffer      varchar(9);
   random_num     varchar(10);
   access_buffer  varchar2(30);
   bad_passwd     exception;
   bad_id    	  exception;
   nteachers      integer;
   tname     varchar(20);

begin
   select A.sid, A.userid, A.password, A.lastAccess, A.authority
      into sid_buffer, user_buffer, passwd_buffer, time_buffer,auth_buffer
      from whoami A
      where (A.userid = start_session_st.usid);
   if start_session_st.passwd != passwd_buffer then
      raise bad_passwd;
   end if;
--CHECKING FOR SID AND USERID IF THEY ARE MATCHED (Hung Nguyen 10/31/98)
   if (sid_buffer != id) then
     raise bad_id;
   end if; 
   random_num := to_char(sysdate, 'SSSSS');
   access_buffer := user_buffer || ',' || random_num;
-- updating the user record
   update whoami
      set u_access = access_buffer,
          lastAccess = sysdate
   where whoami.userid=user_buffer;
   commit;

      select count(userid)
      into nteachers
      from whoami
      where authority = 'Teacher'
      order by userid;

    if (nteachers = 1) then
--      select t.lname
--      into tname
--      from teachers t,whoami w
--      where (t.sid = w.sid) and (w.authority = 'Teacher');

--      menu.student_menu(access_buffer,tname,id,time_buffer);
      menu.student_menu(access_buffer,auth_buffer,id,time_buffer);
    else
      display.display_teachers(access_buffer,id,time_buffer);
    end if;

Exception
   When bad_id then
      htp.print('Your UserID does not match with your ID. Please recheck
	 UserID and ID.');
   when bad_passwd then
      htp.print('Bad password ');
   when no_data_found then
      htp.print('I cannot find you in my database as a user');
   when others then
      htp.print('something wrong in user_access.start_session_st');
      htp.print(SQLERRM);
end start_session_st;

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 whoami 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('some thing is wrong in user_access.check_access');

end check_access;

procedure logout(u_access in varchar2 default NULL) as

   auth_buffer varchar(20);
   no_access   exception;
begin
   auth_buffer := check_access(u_access);
   if (auth_buffer < '1')
      then raise no_access;
   end if;

   update whoami
      set u_access = null
      where u_access = logout.u_access;
   commit;
   htp.htmlOpen;
   htp.headOpen;
   htp.print('<META HTTP-EQUIV=REFRESH
   	CONTENT="5;URL=http://tinman.cs.gsu.edu/~raj">');
   htp.headClose;
   htp.bodyOpen('ows-img/raj/fleck.jpg');
   htp.print('Thank you for using the Grade Book Program');
   htp.print('<p>This program was developed by:
	<UL type=circle>
		<LI><A href="mailto:raj@tinman.cs.gsu.edu">Dr. Raj
			Sunderraman</a>
		<LI><A href="mailto:mathqnx@tinman.cs.gsu.edu">Hung Nguyen</a>
		<LI><A href="mailto:matlntx@tinman.cs.gsu.edu">Louis Toyama</a>
	</UL>');
    htp.para;
    htp.print('Any question concerning this system can be directed to one
	of the name above.');
    htp.para;
    htp.anchor2(owa_util.get_owa_service_path||'gradebook','Go To Log In Page');
    htp.bodyClose;
    htp.htmlClose;
Exception
   when no_access then
     raise_no_access('gradebook');
   when others then
      htp.print('There is a problem in users_access.logout');

end logout;

procedure change_password(u_access in varchar2 default NULL) as

   access_check varchar2(30);
   no_access    exception;
   u_id         varchar2(30);
   u_passwd     varchar2(30);

begin  -- change_password
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if  ((access_check != 'Developer')  and
        (access_check != 'Teacher') and
        (access_check != 'Student')) then
      raise no_access;
   end if;
   select U.userid, U.password 
      into u_id, u_passwd 
      from whoami U
      where change_password.u_access = U.u_access;
   htp.htmlOpen;
   htp.headOpen;
   htp.htitle('Change Password');
   htp.headClose;
   htp.bodyOpen('/ows-img/raj/fleck.jpg');
   htp.formOpen(owa_util.get_owa_service_path ||'user_access.change_it');
   
   htp.nl;
   htp.print('Enter your old password :        ');
   htp.formPassword('old_password', 15, 15);
   htp.nl;
   htp.print('Enter your new password:        ');
   htp.formPassword('new_password1', 15, 15);
   htp.nl;
   htp.print('Re-Enter your new password:        ');
   htp.formPassword('new_password2', 15, 15);
   htp.nl;
   htp.formHidden('u_access', u_access);
   htp.formHidden('u_id', u_id);
   htp.formHidden('u_passwd', u_passwd);
   htp.nl;
   htp.nl;
   htp.formSubmit(NULL, 'Proceed');
Exception
   when no_access then
	Raise_no_access('gradebook');
   when others then
      htp.print('something is wrong in user_access.change_password');

end change_password;

procedure change_it(u_access in varchar2 default NULL,
                    u_id in varchar2,
                    u_passwd in varchar2,
                    old_password in varchar2,
                    new_password1 in varchar2,
                    new_password2 in varchar2) as

   access_check   varchar2(30);
   no_access      exception;
   wrong_password exception;

begin   -- change_it
   if (u_access is NULL) then
      raise no_access;
   end if;
   access_check := user_access.check_access(u_access);
   if  ((access_check != 'Developer')  and
        (access_check != 'Teacher') and
        (access_check != 'Student')) then
      raise no_access;
   end if;
   if ((new_password1 != new_password2) or
       (u_passwd != old_password))then
      raise wrong_password;
   end if;
   update whoami
      set password = new_password1
      where whoami.userid = u_id;

   commit;
   htp.nl;
   htp.print('Your password was changed');
   htp.nl;
   htp.nl;
   if (access_check = 'Developer') then
     htp.anchor2(owa_util.get_owa_service_path ||
		'menu.developer_menu?u_access='||u_access,
                  'Back to the Main Menu');
   elsif (access_check = 'Teacher') then
    htp.anchor2(owa_util.get_owa_service_path ||

'menu.Teacher_menu?u_access='||u_access||'&'||'userid='||u_id,
                  'Back to the Main Menu');
   else
    htp.print('<form><input type=button onclick=history.go(-2) value="Back to the Main Menu"></form>');

   end if;

Exception
   when no_access then
	Raise_no_access('gradebook');
   when wrong_password then
      htp.print('You mistyped one of the passwords');
   when others then
      htp.print('something wrong in user_access.change_it');

end change_it;


end user_access;   -- end package
/
show errors
