Project on Hospital Management using Oracle Forms

HOSPITAL PATIENT BOOKING SYSTEM

GROUP MEMBERS

FORM NO.

SAHELI BHATTACHARYA

28119

POULAMEE MUKHERJEE

27242

PRIYANKA SEN

26765

PATRALI DUTTA

26764

DEBASMITA DEB

26766

POUSHALI DUTTA

27448

GROUP MEMBERS

FORM NO.

SAHELI BHATTACHARYA

28119

POULAMEE MUKHERJEE

27242

PRIYANKA SEN

26765

PATRALI DUTTA

26764

DEBASMITA DEB

26766

POUSHALI DUTTA

27448

ACKNOWLEDGEMENT We take upon this opportunity endowed upon us by the grace of the Almighty , to thank all those who have been part of this endeavour . Firstly, we would like to thank GLOBSYN FINISHING SCHOOL for Giving us the opportunity as well as lending us the resources to fulfill our work. We would like to thank our mentor PROF. ARNAB CHAKRABORTY for giving us the right direction to follow and proper guidance regarding the topic. Without his active involvement and the right guidance this would not have been possible .we sincerely thank him for giving us the chance as well as the support for all the time being. Last but not the least, we heartily appreciate all those people who have helped us directly or indirectly in making these task a success.

INDEX 1.INTRODUCTION 2. REQUIREMENT SPECIFICATION: 2.1 Domain description 2.2 Problem definition 2.3 Functional requirements 2.4 Hardware & Software requirements

3. DATABASE DESIGN 3.1 ER diagram 3.2 Table description

4. SCREEN SHOTS 5. FUTURE SCOPE OF IMPROVEMENTS 6. CODE

INTRODUCTION This is a project work undertaken during SUMMER TRAINING 2015 at GLOBSYN FINISHING SCHOOL. We have tried our best to make this project which is HOSPITAL PATIENT BOOKING SYSTEM using ORACLE 10g and ORACLE DEVELOPER SUITE as simple as possible which could help the administrators and staffs to use and to manipulate different field values easily. This project consists of two types of users: 1: Staff 2: Administrator (Who are involved in the updation and manipulation of the tables)

REQUIREMENT SPECIFICATION

Domain Descriptions The main aim of the entire activity is to automate the process of day to day activities of Hospital like Ward activities, Admission of a New Patient, Assigning a bed according to availability, Patient’s test details, Entering test details, Discharge description and finally Computing the bill etc. The limited time and resources have restricted us to incorporate, in this project, only main activities that are performed in a HOSPITAL PATIENT BOOKING SYSTEM , but utmost care has been taken to make the system efficient and user friendly. “HOSPITAL PATIENT BOOKING SYSTEM” has been designed to computerize all the functions that are performed by the system.

Problem Description Problem: A Project on HOSPITAL PATIENT BOOKING SYSTEM Problem Definition:

This is a Project work undertaken in context of partial fulfilment of PROJECT. We have tried our best to make the complicated process of HOSPITAL PATIENT BOOKING SYSTEM as simple as possible using Sql, triggers, forms builder. We have tried to design the software in such a way that the user may not have any difficulty in using this package & further expansion is possible without much effort. Even though we cannot claim this work to be entirely exhaustive, the main purpose of our exercise is to perform each Employee’s activity in computerized way rather than manually which is time consuming. We are confident that this software package can be readily used by non-programming personal avoiding human handled chance of error. This project is used by Administrator (management of the Hospital).Administrator must be an authorized user who can maintain daily updates in the hospital records. He can further change the password. There is the facility for password changing, logout etc.Also a Nurse must be an authorised user who can maintain the treatment aspect of the patient. There is a facility for login,signup,test entries,billing etc.

Functionality Required:

Online Hospital management system is a web based software system that will control every aspect of a hospital. This system should have following facilities:

Superintendent of the hospital must have the following functionalities:  see status of beds at each ward,  patient list in a ward ,  View patient details given the patient id.

Nurses use the system to do the following:  information about a pathological test(ex what is

the price of USG)  View list of pathological test given a patient id.  Entry of pathological test for a patient.  View patient details given the patient id. Accountant’s task:

 Generate bill for a patient (this means discharging)

HARDWARE/ SOFTWARE REQUIREMENTS: HARDWARE REQUIREMENTS:

Processor: 568 MHz Pentium OR More Memory: RAM: 256MB(min) Hard Disk: 20GB(min) Display: Monitor SOFTWARE REQUIREMENTS: 

 

OPERATING SYSTEM :- WINDOWS XP/ WINDOWS 7/WINDOWS 8 PLATFORM :- ORACLE 10G EXPRESS EDITION FILE SYSTEM :- ORACLE DEVELOPER SUITE,FORMS BUILDER



BROWSER :-MOZILLA FIREFOX 2.0.0.2, 3 or 3.5



MICROSOFT WORD, MICROSOFT VISIO 2013

E-R DIAGRAM

NOTE: * Admit_id:(pid,sid,did,bedno)

DATABASE TABLES: DATABASE DESIGN: STAFF TABLE

ATTRIBUTES ID NAME AGE GENDER ADDRESS CONTACT DSGN

CONSTRAINT PRIMARY KEY

DATATYPE INTEGER VARCHAR2(10) INTEGER VARCHAR2(10) VARCHAR2(15) VARCHAR2(15) VARCHAR2(15)

PERSON TABLE(LOGIN_PAGE) ATTRIBUTES PERSON_ID

CONSTRAINT

PERSON_PASSWORD ID FOREIGN KEY

DATATYPE VARCHAR2(15) VARCHAR2(15) INTEGER

ROOM_TABLE ATTRIBUTES ROOM_ID ROOM_TYPE AC_NAC FLOOR_NO

CONSTRAINT PRIMARY KEY

DATATYPE NUMBER(10) VARCHAR2(10) VARCHAR2(10) NUMBER(10)

CONSTRAINT primary key Foreign key

DATATYPE number(10) number(10) Varchar2(20)

BED_TABLE ATTRIBUTES bed_no room_id bed_availability bed_charge

number(10)

DOCTOR_TABLE ATTRIBUTES doctor_id Id Dname Gender designation ph_no

CONSTRAINT primary key Foreign key

DATATYPE integer integer varchar2(20) varchar2(20) varchar2(20) varchar2(20)

SUR_MAS TABLE (SURGERY MASTER) ATTRIBUTES s_id s_type fees

CONSTRAINT primary key

DATATYPE number(10) varchar2(50) number(10,2)

CONSTRAINT primary key

DATATYPE integer varchar2(10) varchar2(10) varchar2(10) varchar2(10) varchar2(30) number (10)

PATIENT_TABLE

ATTRIBUTES Pid Pname address gender ptype email cont_no

ADMIT TABLE(ADMIT AND RELEASE FORM) ATTRIBUTES PID PNAME ADMDATE RELDATE DID ADMITID BED_CHARGE BED_NO MED_COST OTHER_COST PATHOLOGY_COST TOTAL COST

CONSTRAINT foreign key

PRIMARY KEY

DATATYPE INTEGER VARCHAR(20) DATE DATE INTEGER NUMBER(10) NUMBER(10,2) NUMBER(10,2) NUMBER(10,2) NUMBER(10,2) NUMBER(10,2) NUMBER(10,2)

PROGRAM CODE: TABLE CREATION CODE TABLE CREATION CODE:

--------STAFF TABLE------------------------------------

drop table staff; create table staff( id integer primary key, name varchar2(20), age integer, gender varchar2(8), address varchar2(20), contact varchar2(15), dsgn varchar2(15));

STAFF SEQUENCE--drop sequence staffseq;

create sequence staffseq increment by 1 start with 1 maxvalue 999 cycle;

commit;

INSERTING VALUES----

insert into staff values(1,'sajal mukherjee',65,'male','kolkata','9876543210','adminis trator');

insert into staff values(2,'sohini dutta',35,'female','mumbai','7654329810','nurse');

insert into staff values(3,'puja thakur',43,'female','bihar','6789054321','doctor');

insert into staff values(4,'payel saha',23,'female','kolkata','9786542310','receptionis t);

insert into staff values(5,'shreyam paul',45,'male','pune','8975643210','doctor'); commit;

-----------------------login-----------------------

drop table person;

create table person( person_id varchar2(15), person_password varchar2(15), id integer);

alter table person add constraint fkey foreign key(id) references staff(id);

insert into person values('admin','admin',1); insert into person values('payelsaha','payel',4);

commit;

------------------------room details---------------------------

create table room ( room_id number(10) primary key, room_typ varchar2(10), ac_nac varchar2(10), floor_no number(10));

insert into room values(1,'general','nac',1); insert into room values(2,'general','nac',1);

insert into room values(21,'cabin','nac',2); insert into room values(23,'cabin','ac',2); insert into room values(32,'icu','nac',3);

commit; ------------------bed details----------------------------------------

drop table bed;

create table bed ( bed_no number(10) primary key, room_id number(10), bed_availability varchar2(10), bed_charge number(10,2));

alter table bed add constraints frkey foreign key(room_id) references room(room_id);

insert into bed(1,1,'yes',3000); insert into bed(5,32,'no',3500); insert into bed(7,1,'yes',2000); insert into bed(3,21,'yes',5000);

commit;

-----------------doctor details-------------------------------------

drop sequence docseq;

create sequence docseq increment by 1 start with 1 maxvalue 999 cycle;

drop table doctor;

create table doctor ( doctor_id integer primary key, id integer, dname varchar2(20), gender varchar2(10), designation varchar2(10), ph_no varchar2(10) );

alter table doctor add constraints forkey foreign key(id) references staff(id);

insert into doctor values(1,3,'puja thakur','female','cardiac','6789054321');

insert into doctor values(2,5,'shreyam paul','male','cardiac','8975643210');

-------------------surgery master details-----------------------

drop sequence surseq;

create sequence surseq increment by 1 start with 1 maxvalue 999 cycle;

drop table sur_mas;

create table sur_mas ( s_id number(10) primary key, s_type varchar2(50), fees number(10,2));

insert into sur_mas(1,'Cardiac Surgery',50000); insert into sur_mas(2,'Neuro Surgery',48000);

-------------------patient details------------------------

drop sequence patseq;

create sequence patseq increment by 1 start with 1 maxvalue 999

cycle;

drop table patient;

create table patient( Pid integer primary key, Pname varchar2(10), address varchar2(10), gender varchar2(10), ptype varchar2(10), email varchar2(30), cont_no number(10));

commit;

ADMIT TABLE-----------

drop sequence adseq; create sequence adseq increment by 1 start with 1 maxvalue 999 cycle;

drop table admit;

create table admit (Pid integer, Pname varchar2(20),

admdate date , reldate date, did integer, admitid number(10) primary key, bed_charge number(10,2), bed_no number(10), s_id number(10), s_type varchar2(50), fees number(10,2), med_cost number(10,2), other_cost number(10,2), pathology_cost number(10,2), total_cost number(10,2) );

alter table admit add constraints fo_key foreign key(Pid) references Patient(Pid); alter table admit add constraints for_ikey foreign key(did) references doctor(doctor_id); alter table admit add constraints fon_key foreign key(bed_no) references bed(bed_no); alter table admit add constraints fong_key foreign key(s_id) references sur_mas(s_id);

select * from admit;

SQL> select * from admit;

PID ADMDATE RELDATE BED_NO

DID

ADMITID BED_CHARGE

---------- --------- --------- ---------- ---------- ---------- ---------MED_COST OTHER_COST PATHOLOGY_COST

---------- ---------- -------------1 07-JUL-15

1

1

1

SQL> select * from bed;

BED_NO

ROOM_ID BED_AVAILA BED_CHARGE

---------- ---------- ---------- ---------1

1 no

5

32 no

7

1 yes

3

21 yes

3000 3500 2000 5000


BED_NO


---------- ---------- ---------- ---------1

1 no

3000

5

32 no

3500

7

1 no

3

21 yes

2000 5000

SQL> select * from admit;

PID ADMDATE RELDATE BED_NO

DID

ADMITID BED_CHARGE

---------- --------- --------- ---------- ---------- ---------- ---------MED_COST OTHER_COST PATHOLOGY_COST ---------- ---------- -------------1 07-JUL-15

1

1

1

4 07-JUL-15

2

2

7

SQL> select * from admit; PID ADMDATE RELDATE BED_NO

DID

ADMITID BED_CHARGE

---------- --------- --------- ---------- ---------- ---------- ---------MED_COST OTHER_COST PATHOLOGY_COST ---------- ---------- -------------1 07-JUL-15 07-JUL-15 500

500

4 07-JUL-15

1

1

3000

1000

2

2

7


BED_NO


---------- ---------- ---------- ---------1

1 yes

5

32 no

7

1 no

3

21 yes

3000 3500 2000 5000

1

--------PERSON DETAILS-----

drop table person; create table person( person_id varchar2(15), person_password varchar2(15), id integer);

alter table person add constraint fkey foreign key(id) references staff(id);

insert into person values('admin','admin',1);

insert into person values('payelsaha','payel',4);

CODES: CODE FOR BACKGROUND IMAGE: read_image_file('C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\image.tif','tif','image47');

CODE FOR LOGIN_PAGE SUBMIT BUTTON---

declare rec_user person%rowtype; num integer; a integer; no_uname exception; no_pass exception; wrong_input exception; no_match exception; begin if (:block13.ti1) is null then raise no_uname; elsif (:block13.ti2) is null then raise no_pass; else select count(*) into num from person where person_id=:block13.ti1 and person_password=:block13.ti2;

if num=1 then select * into rec_user from person where person_id=:block13.ti1 and person_password=:block13.ti2; :global.uname:=rec_user.person_id;

if (:global.uname is not null) then open_form('C:\Documents and Settings\Administrator\Desktop\hospital\staff.fmx', activate,no_session); else raise wrong_input;

end if; else raise no_match;

end if; end if; exception when no_uname then ----message('no user id...'); a:=show_alert('nouserid'); when no_pass then ---message('no password...'); a:=show_alert('nopassword'); when wrong_input then ----message('wrong input...'); a:=show_alert('wronginput'); when no_match then -----message('no match...'); a:=show_alert('nomatch');

end;

CODE FOR NEW_USER_CREATION-------

open_form('C:\Documents and Settings\Administrator\Desktop\hospital\LOGIN.fmx', activate,no_session);

CODE FOR USER_SIGNUP SUBMIT BUTTON--------

declare rec_user person%rowtype; num integer; a integer; no_record exception; no_pass exception; begin if (:block7.ti1) is null or (:block7.ti2) is null or (:block7.ti3) is null or (:block7.ti4) is null or (:block7.ti5) is null or (:block7.ti6) is null then raise no_record;

elsif(:ti1<>'admin') and (:ti2<>'admin') then raise no_pass; else

insert into person values(:ti3,:ti4,:ti6); if(:ti4=:ti5) then standard.commit; open_form('C:\Documents and Settings\Administrator\Desktop\hospital\USER.fmx',a ctivate,no_session); else message('password & retype password are not same');

message('password & retype password are not same'); end if;

end if; exception when no_record then message('some fields remain blank...'); message('some fields remain blank...'); when no_pass then message('user id & password of admin are wrong'); message('user id & password of admin are wrong');

end;

CODE FOR INSERTING STAFF_DETAILS:(INSERT BUTTON)------

:di1:=:global.rectotno+1;

:ti2:=' '; :ti3:=' '; :ti4:=' '; :ti5:=' '; :li1:=' '; :li2:=' '; go_item('ti1');

CODE FOR DELETING STAFF_DETAILS(DELETE BUTTON)-------

begin delete from staff where id=:di1; :global.rectotno:=:global.rectotno-1; :di1:=' '; :ti2:=' '; :ti3:=' '; :ti4:=' '; :ti5:=' '; :li1:=' '; :li2:=' '; go_item('ti1'); standard.commit; end;

CODE FOR UPDATING STAFF_DETAILS(UPDATE BUTTON)-----

declare a number; b number; begin if(:ti2 is null or :ti3 is null or :ti4 is null or :ti5 is null or :li1 is null or :li2 is null) then a:=show_alert('blankdetails'); go_item('ti1'); else insert into staff values(staffseq.nextval,:ti2,:ti3,:li1,:ti4,:ti5,:li2); standard.commit; :global.rectotno:=:global.rectotno+1; :global.recno:=:global.rectotno; :di2:=:global.recno||'/'||:global.rectotno; b:=show_alert('successfulinsertion'); end if; end;

CODE FOR MOVE FIRST BUTTON OF STAFF_DETAILS-------

declare

staffid staff.id%type; staffname staff.name%type; staffage staff.age%type; staffgender staff.gender%type; staffaddress staff.address%type; staffcontact staff.contact%type; staffdsgn staff.dsgn%type;

begin

:global.recno:=1;

select id,name,age,gender,address,contact,dsgn into

staffid,staffname,staffage,staffgender,staffaddre ss,staffcontact,staffdsgn from ( select rownum rn,id,name,age,gender,address,contact,dsgn from staff order by rn asc ) where rn=:global.recno;

:di1:=staffid; :ti2:=staffname; :ti3:=staffage; :li1:=staffgender; :ti4:=staffaddress; :ti5:=staffcontact; :li2:=staffdsgn; :di2:=:global.recno||'/'||:global.rectotno;

end;

CODE FOR MOVE LAST BUTTON OF STAFF_DETAILS-------

declare staffid staff.id%type; staffname staff.name%type; staffage staff.age%type; staffgender staff.gender%type; staffaddress staff.address%type;

staffcontact staff.contact%type; staffdsgn staff.dsgn%type; a number; begin if (:global.recno = :global.rectotno) then a:=show_alert('lastposition'); else :global.recno:=:global.rectotno;

select id,name,age,gender,address,contact,dsgn into staffid,staffname,staffage,staffgender,staffaddre ss,staffcontact,staffdsgn from ( select rownum rn,id,name,age,gender,address,contact,dsgn from staff order by rn asc ) where rn=:global.recno;

:di1:=staffid; :ti2:=staffname; :ti3:=staffage; :li1:=staffgender; :ti4:=staffaddress; :ti5:=staffcontact; :li2:=staffdsgn; :di2:=:global.recno||'/'||:global.rectotno; end if; end;

CODE FOR MOVE PREVIOUS BUTTON------

declare staffid staff.id%type; staffname staff.name%type; staffage staff.age%type; staffgender staff.gender%type; staffaddress staff.address%type; staffcontact staff.contact%type; staffdsgn staff.dsgn%type; a number; begin if (:global.recno = 1) then a:=show_alert('firstposition'); else :global.recno:=:global.recno-1;



CODE FOR MOVE PREVIOUS BUTTON-----

declare staffid staff.id%type; staffname staff.name%type; staffage staff.age%type; staffgender staff.gender%type; staffaddress staff.address%type; staffcontact staff.contact%type; staffdsgn staff.dsgn%type; a number; begin if (:global.recno = :global.rectotno) then a:=show_alert('lastposition'); else :global.recno:=:global.recno+1;



CODE FOR PATIENT DETAILS---------

CODE FOR INSERTION IN PATIENT_DETAILS----------------

:di1 := :global.reccount+1; :ti1:=''; :ti2:=''; :li1:=''; :ti4:=''; :ti5:=''; :ti6:=''; go_item('ti1');

CODE FOR DELETION IN PATIENT_DETAILS----------------

begin delete from patient where Pid=:di1; :global.reccount:=:global.reccount-1;

:di1:=''; :ti1:=''; :ti2:=''; :li1:=''; :ti4:=''; :ti5:=''; :ti6:=''; go_item('ti1');

standard.commit; end;

CODE FOR UPDATION IN PATIENT_DETAILS----------------

declare a number; begin if (:ti1 is null or :ti2 is null or :li1 is null or :ti4 is null or :ti5 is null or :ti6 is null) then a:=show_alert('blankfield'); go_item('ti1'); else insert into patient values (patseq.nextval,:ti1,:ti2,:li1,:ti4,:ti5,:ti6); standard.commit;

:global.reccount:=:global.reccount+1; :global.currecno:=:global.reccount;

:di2:=:global.currecno||'/'||:global.reccount; end if;

end;

CODE FOR MOVE FIRST IN PATIENT_DETAILS----------------

declare a number; pat_id patient.Pid%type; pat_name patient.Pname%type; pat_add patient.address%type; pat_gen patient.gender%type; pat_type patient.ptype%type; pat_email patient.email%type; pat_contno patient.cont_no%type;

begin select count(*) into a from patient;

if (a=0) then :global.reccount:=0;

:global.currecno:=0; ----- message('I am in the then block...'); else ----message('I am in the else block...');

:global.currecno:=1; :global.reccount:=a;

:di2:=:global.currecno||'/'||:global.reccount;

select Pid,Pname,address,gender,ptype,email,cont_no into pat_id,pat_name,pat_add,pat_gen,pat_type,pat_email,p at_contno from ( select rownum rn,Pid,Pname,address,gender,ptype,email,cont_no from patient order by rownum ) where rn=1;

:di1:=pat_id; :ti1:=pat_name; :ti2:=pat_add; :li1:=pat_gen; :ti4:=pat_type; :ti5:=pat_email; :ti6:=pat_contno; end if;

end;

CODE FOR MOVE LAST IN PATIENT_DETAILS----------------



if (a=0) then :global.reccount:=0;

:global.currecno:=0; ---message('I am in the then block...'); else ---message('I am in the else block...');

:global.currecno:=a; :global.reccount:=a;

:di2:=:global.currecno||'/'||:global.reccount;

select Pid,Pname,address,gender,ptype,email,cont_no into pat_id,pat_name,pat_add,pat_gen,pat_type, pat_email, pat_contno from ( select rownum rn,Pid,Pname,address,gender,ptype,email,cont_no from patient order by rownum ) where rn=:global.reccount;

:di1:=pat_id; :ti1:=pat_name; :ti2:=pat_add; :li1:=pat_gen; :ti4:=pat_type; :ti5:=pat_email; :ti6:=pat_contno; end if;

end;

CODE FOR MOVE NEXT IN PATIENT_DETAILS---------------declare

a number; pat_id patient.Pid%type; pat_name patient.Pname%type; pat_add patient.address%type; pat_gen patient.gender%type; pat_type patient.ptype%type; pat_email patient.email%type; pat_contno patient.cont_no%type; begin select count(*) into a from patient;

if (a=0) then :global.reccount:=0; :global.currecno:=0; else

if (:global.currecno=:global.reccount) then a := show_alert('lastrecord'); else

:global.currecno:=:global.currecno + 1;

select Pid,Pname,address,gender,ptype,email,cont_no into pat_id,pat_name,pat_add,pat_gen,pat_type,pat_email,p at_contno from ( select rownum rn,Pid,Pname,address,gender,ptype,email,cont_no from patient order by rownum ) where rn=:global.currecno;

:di1:=pat_id; :ti1:=pat_name; :ti2:=pat_add; :li1:=pat_gen; :ti4:=pat_type; :ti5:=pat_email; :ti6:=pat_contno;

:di2:=:global.currecno||'/'||:global.reccount; end if; end if;

end;

CODE FOR MOVE PREVIOUS IN PATIENT_DETAILS----------------



if (a=0) then :global.reccount:=0; :global.currecno:=0;

else

if (:global.currecno=1) then a := show_alert('firstrecord'); else

:global.currecno:=:global.currecno - 1;

select Pid,Pname,address,gender,ptype,email,cont_no into pat_id,pat_name,pat_add,pat_gen,pat_type,pat_email,p at_contno from ( select rownum rn,Pid,Pname,address,gender,ptype,email,cont_no from patient order by rownum ) where rn=:global.currecno;

:di1:=pat_id; :ti1:=pat_name; :ti2:=pat_add; :li1:=pat_gen; :ti4:=pat_type; :ti5:=pat_email; :ti6:=pat_contno; :di2:=:global.currecno||'/'||:global.reccount; end if; end if; end;

CODE FOR DOCTOR DETAILS-------

INSERTION CODE----------------------------------

:di2:=:global.reccount+1; :ti6:=''; :ti7:=''; :li8:=''; :ti9:=''; :ti10:=''; go_item('ti6');

DELETION CODE----------------------------------

declare a number; begin delete from doctor where doctor_id=:di2; a:=show_alert('recorddelete'); :global.reccount:=:global.reccount-1; :di2:=''; :ti6:=''; :ti7:=''; :li8:=''; :ti9:=''; :ti10:=''; go_item('ti7'); standard.commit; end;

UPDATION CODE----------------------------------

declare a number;

begin if(:ti6 is null or :ti7 is null or :li8 is null or :ti9 is null or :ti10 is null)then a:=show_alert('blankfield');

else insert into doctor values(docseq.nextval,:ti6,:ti7,:li8,:ti9,:ti10); standard.commit; a:=show_alert('successfulinsertion'); :global.reccount:=:global.reccount+1; :global.currecno:=:global.reccount; :di1:=:global.currecno||'/'||:global.reccount; end if; end;

MOVE FIRST CODE---

declare a number; docid doctor.doctor_id%type; id doctor.id%type; docname doctor.dname%type; gen doctor.gender%type; des doctor.designation%type; phno doctor.ph_no%type; begin select count(*) into a from doctor; if(a=0) then :global.reccount:=0; :global.currecno:=0;

---------

message('I am in the then block......');

else --------

message('I am in th else block......');

:global.currecno:=1;

:global.reccount:=a; :di1:=:global.currecno||'/'||:global.reccount;

select doctor_id,id,dname,gender,designation,ph_no into docid,id,docname,gen,des,phno from ( select rownum rn,doctor_id,id,dname,gender,designation,ph_no from doctor order by rownum ) where rn=1;

:di2:=docid; :ti6:=id; :ti7:=docname; :li8:=gen; :ti9:=des; :ti10:=phno; end if; end;

MOVE LAST CODE------

declare a number; docid doctor.doctor_id%type; id doctor.id%type; docname doctor.dname%type; gen doctor.gender%type; des doctor.designation%type; phno doctor.ph_no%type; begin select count(*) into a from doctor;

if(a=0) then :global.reccount:=0; :global.currecno:=0;

---

message('I am in the then block......');

else -------message('I am in th else block......');

:global.currecno:=a; :global.reccount:=a; :di1:=:global.currecno||'/'||:global.reccount;

select doctor_id,id,dname,gender,designation,ph_no into docid,id,docname,gen,des,phno from ( select rownum rn,doctor_id,id,dname,gender,designation,ph_no from doctor order by rownum ) where rn=:global.reccount;

:di2:=docid; :ti6:=id; :ti7:=docname; :li8:=gen; :ti9:=des; :ti10:=phno; end if; end;

MOVE PREVIOUS CODE-------

declare a number; docid doctor.doctor_id%type; id doctor.id%type; docname doctor.dname%type; gen doctor.gender%type; des doctor.designation%type; phno doctor.ph_no%type; begin select count(*) into a from doctor; if(a=0)then :global.reccount:=0; :global.currecno:=0; else

if(:global.currecno=1)then a:=show_alert('FIRST'); else

:global.currecno:=:global.currecno-1;

select doctor_id,id,dname,gender,designation,ph_no into docid,id,docname,gen,des,phno from ( select rownum rn,doctor_id,id,dname,gender,designation,ph_no from doctor order by rownum ) where rn=:global.currecno;

:di2:=docid; :ti6:=id; :ti7:=docname; :li8:=gen; :ti9:=des; :ti10:=phno; :di1:=:global.currecno||'/'||:global.reccount; end if; end if; end;

MOVE NEXT CODE------

declare a number; docid doctor.doctor_id%type; id doctor.id%type; docname doctor.dname%type; gen doctor.gender%type; des doctor.designation%type; phno doctor.ph_no%type; begin message('I am here-1'); :di1:=:global.currecno||'/'||:global.reccount; select count(*) into a from doctor; if(a=0) then :global.reccount:=0; :global.currecno:=0; ---message('I am here-2'); else if(:global.currecno=:global.reccount)then

a:=show_alert('ALERT47'); else ----- message('I am here-3'); :global.currecno:=:global.currecno + 1;

select doctor_id,id,dname,gender,designation,ph_no into docid,id,docname,gen,des,phno from ( select rownum rn,doctor_id,id,dname,gender,designation,ph_no from doctor order by rownum ) where rn=:global.currecno; ----

message('I am here-4'); :di2:=docid; :ti6:=id; :ti7:=docname; :li8:=gen; :ti9:=des; :ti10:=phno; :di1:=:global.currecno||'/'||:global.reccount; end if; end if;

end;

CODE FOR INSERTION OF ROOM_DETAILS----

declare a number; b number; begin if(:ti1 is null or :li2 is null or :li3 is null or :ti4 is null) then a:=show_alert('blankdetails'); go_item('ti1'); else insert into room values (:ti1,:li2,:li3,:ti4 ); b:=show_alert('insertionsuccessful'); standard.commit; :ti1:='';

:li2:=''; :li3:=''; :ti4:='';

go_item('ti1');

end if; end;

CODE FOR INSERTION OF ROOM_DETAILS----

declare a number; b number; begin if(:ti1 is null or :li2 is null or :li3 is null or :ti4 is null) then a:=show_alert('blankdetails'); go_item('ti1'); else insert into room values (:ti1,:li2,:li3,:ti4 ); b:=show_alert('insertionsuccessful'); standard.commit; :ti1:='';

:li2:=''; :li3:=''; :ti4:='';

go_item('ti1');

end if; end;

CODE FOR BED_DETAILS---CODE FOR bed insert-------------------:bi1:=''; :ri1:=''; :bc1:=''; :di7:='';

update-------------------declare a number; b number; begin if(:bi1 is null or :ri1 is null or :li1 is null or :bc1 is null) then

a:=show_alert('blankdetails'); go_item('bi1'); else insert into bed values(:bi1,:ri1,:li1,:bc1); standard.commit; :global.rectotno:=:global.rectotno+1; :global.recno:=:global.rectotno; :di7:=:global.recno||'/'||:global.rectotno; b:=show_alert('successfulinsertion'); end if;

end;

-----------delete------------------

begin delete from bed where bed_no=:bi1; :global.rectotno:=:global.rectotno-1; :bi1:=' '; :ri1:=' '; :li1:=' '; :bc1:=' ';

go_item('bi1'); standard.commit; end;

------------------movefirst---------------------------------declare

bedno bed.bed_no%type; roomid bed.room_id%type; bedavail bed.bed_availability%type; bedcharge bed.bed_charge%type;

begin

:global.recno:=1;

select bed_no,room_id,bed_availability,bed_charge into bedno,roomid,bedavail,bedcharge from ( select rownum rn,bed_no,room_id,bed_availability,bed_charge from bed order by rn asc

) where rn=:global.recno; :bi1:=bedno; :ri1:=roomid; :li1:=bedavail; :bc1:=bedcharge; :di7:=:global.recno||'/'||:global.rectotno;

end;

----------Move next------------------------declare bedno bed.bed_no%type; roomid bed.room_id%type; bedavail bed.bed_availability%type; bedcharge bed.bed_charge%type;

a number; begin if (:global.recno = :global.rectotno) then a:=show_alert('lastposition'); else :global.recno:=:global.recno+1;

select bed_no,room_id,bed_availability,bed_charge into bedno,roomid,bedavail,bedcharge from ( select rownum rn,bed_no,room_id,bed_availability,bed_charge

from bed order by rn asc ) where rn=:global.recno;

:bi1:=bedno; :ri1:=roomid; :li1:=bedavail; :bc1:=bedcharge; :di7:=:global.recno||'/'||:global.rectotno; end if; end; -------------------------Move preVIOUS--------------------------

declare bedno bed.bed_no%type; roomid bed.room_id%type; bedavail bed.bed_availability%type; bedcharge bed.bed_charge%type;

a number; begin if (:global.recno = 1) then a:=show_alert('firstposition'); else :global.recno:=:global.recno-1;

select bed_no,room_id,bed_availability,bed_charge into bedno,roomid,bedavail,bedcharge from

( select rownum rn,bed_no,room_id,bed_availability,bed_charge from bed order by rn asc ) where rn=:global.recno; :bi1:=bedno; :ri1:=roomid; :li1:=bedavail; :bc1:=bedcharge; :di7:=:global.recno||'/'||:global.rectotno; end if; end;

------------------Move last----------------------

declare bedno bed.bed_no%type; roomid bed.room_id%type; bedavail bed.bed_availability%type; bedcharge bed.bed_charge%type; a number; begin if (:global.recno = :global.rectotno) then a:=show_alert('lastposition'); else :global.recno:=:global.rectotno;

select bed_no,room_id,bed_availability,bed_charge into bedno,roomid,bedavail,bedcharge from ( select rownum rn,bed_no,room_id,bed_availability,bed_charge from bed order by rn asc ) where rn=:global.recno;

:bi1:=bedno; :ri1:=roomid; :li1:=bedavail; :bc1:=bedcharge; :di7:=:global.recno||'/'||:global.rectotno; end if; end;

trigger----------------drop trigger bed_nonavail;

create or replace trigger bed_noavail after insert on admit for each row

begin update bed set bed_availability='no' where bed_no=:new.bed_no; end; /

drop trigger bed_avail;

create or replace trigger bed_avail before update on admit for each row begin update bed set bed_availability='yes' where bed_no=:old.bed_no; end; /

------RELEASE FORM insert CODE--------------------------:di1:=''; :di2:='';

:di4:=''; :di5:=''; :di6:=''; :di20:=''; :di21:=''; :ti1:='';

:di1:=''; :di10:=''; :ti2:=''; :ti3:=''; standard.commit;

-------------RELEASE FORM update-----------declare a number; b number; c number; n number; begin

select reldate-admdate into n from admit where admitid=:li2;

:di2:=:di1+:ti1+:ti2+:ti3+:di6*(n+1);

update admit set s_id=:li1,s_type=:di10,fees=:di1,Pname=:di5,reldate=:ti4,b ed_charge=:di6,med_cost=:ti1,other_cost=:ti3,patholo gy_cost=:ti2,total_cost=:di2 where admitid=:li2;

standard.commit;

:global.reccount:=:global.reccount+1; :global.currecno:=:global.currecno;

end;

FORM exit------------------exit_form;

--------------------admit FORM----------------------------------------insert CODE------------------:di2:=:global.rectotno+1; :ti2:=' '; :ti3:=' '; :ti4:=' '; go_item('ti1');

-------update CODE--------------------------------

declare ---

a number; ----b number; begin if(:ti2 is null or :ti3 is null) then ----a:=show_alert('blankdetails'); message('ooooooooooo'); go_item('ti2'); else

insert into admit(Pid,admitid,did,admdate,bed_no)values(:ti2,ads eq.nextval,:ti3,:di1,:ti4); message('yyyyyyyyyyyyyyyy'); standard.commit; :global.rectotno:=:global.rectotno+1; :global.recno:=:global.rectotno;

b:=show_alert('successfulinsertion'); end if; end;

CODE FOR INSERTION OF SURGERY_MASTER----

:di1:=:global.rectotno+1;

:si2:=''; :fi1:=''; go_item('si2');

CODE FOR INSERTION OF SURGERY_DETAILS---declare a number; b number; begin if (:di1 is null or :si2 is null or :fi1 is null) then a:=show_alert('al1'); go_item('si1'); else insert into sur_mas values (surseq.nextval,:si2,to_number(:fi1)); b:=show_alert('al2'); :global.rectotno:=:global.rectotno+1; :global.recno:=:global.rectotno; :di1:=''; :si2:=''; :fi1:='';

standard.commit; end if; end; CODE FOR RECORD GROUPS: RELEASE FORM: QUERY FOR RG1: select to_char(s_id),to_char(s_id) from sur_mas ;

QUERY FOR RG2: select to_char(admitid),to_char(admitid) from admit where total_cost is null;

SCREENSHOTS:

INSERTION IN DOCTOR FORM:

LOGIN FORM:

INSERTION OF PATIENT DETAILS:

INSERTION IN ROOM_DETAILS FORM:

MOVE FIRST IN STAFF_DETAILS FORM:

ADMIT FORM:

MOVE FIRST IN BED DETAILS

INSERTION IN DOCTOR FORM

RELEASE FORM

INSERTION IN ROOM FORM

SIGN UP FORM

SURGERY DETAILS FORM

CONCLUSION: The HOSPITAL PATIENT BOOKING SYSTEM is a step in the direction to ease the structure of hospital management. Some of the advantages that it enjoys over Manual System are: Data retrieval process becomes easy when it is needed, if we use computer management instead of manually. Storage capacity of the computer is also excellent. Updating of data is easy in computerized system. Data consistency is required for neat and proper management that is achieved by computer easily. Time is precious and speed is the order of today. Our software supports this statement. With the help of software, data redundancy reduces as compared to manual.

 Developing an interface so that staff can admit and release

patients ,assign them bed,doctor and calculate bill easily using this software.  In this project one patient can go through one surgery at a

time. so it has to be developed so that multiple surgeries can be done at a time . 

Ambulance and blood banks have to be developed.

REPORT: BED REPORT

BILL REPORT

DAYWISE BILL

DURATION REPORT

MENU FORMAT :

Certificate THIS IS TO CERTIFY THAT

OF HERITAGE INSTITUTE OF TECHNOLOGY HAS SUCCESSFULLY COMPLETED THE PROJECT ON HOSPITAL PATIENT BOOKING SYSTEM, UNDER THE GUIDANCE OF PROF.ARNAB CHAKRABORTY USING ORACLE 10g FROM GLOBSYN FINISHING SCHOOL IN SUMMER TRAINING PROGRAM 2015.

___________________ Signature and date

SAHELI BHATTACHARYA

___________________________ PROF.ARNAB CHAKRABORTY


POULAMEE MUKHERJEE

OF

B.P.PODDAR INSTITUTE OF MANAGEMENT AND TECHNOLOGY

HAS SUCCESSFULLY COMPLETED THE PROJECT ON HOSPITAL PATIENT BOOKING SYSTEM, UNDER THE GUIDANCE OF PROF.ARNAB CHAKRABORTY USING ORACLE 10g FROM GLOBSYN FINISHING SCHOOL IN SUMMER TRAINING PROGRAM 2015.




PRIYANKA SEN

OF

CALCUTTA INSTITUTE OF ENGINEERING AND MANAGEMENT





PATRALI DUTTA

OF






DEBASMITA DEB

OF





Project on Hospital Management using Oracle Forms

Recommend Documents