PharmacEUtical Management System in Hospital

itals in Ha Noi with a thousand patients everyday, so the medicine management is very important. Medicine Management System in Hospital includes five main parts: System Management, List Management, Medicine Expectation and Bidding Management, Medicine Management and Report System. System System Management: Each User have their own role assignment made by admin to log-in the system, User have to log-in with a valid user name and password. Besides they can change their password, so the security for system is very good. List Management: This function provides all necessary information about Medicine, Medicine Unit, Medicine Specification, Medicine Classification, Store, Department, Producing Country, Manufacturer, Supplier. User can add new, search, update and delete information in each list. Medicine Expectation and Bidding Management: This part includes two main functions: Yearly Medicine Expectation Management and Medicine Bidding Management. Yearly Medicine Expectation Management: To have the Yearly Medicine Expectation the user must bases on the medicine intake amount and the amount of medicine distributed to the substore and the health insurance store, the amount of the medicine in stock in a year. User who has the role assignment can add new, update, search, and delete the information. Medicine Bidding Management includes: Bidding Document: The hospital sends the bidding document to each supplier. Price Quotation Input: When the supplier received the bidding document they will fill in the document the price of each medicine and send back to the hospital. Bidding Open: After have the price quotation, the hospital will inputs the bidding information of suppliers and then decides which supplier win and get the medicine bidding contract. Bidding Result Announcement: The hospital will announce the bidding result to suppliers Medicine Management: Medicine Management is very important in hospital so this function is given to do all the works related to medicine helps user easier in medicine keeping track and management. Medicine in stock watching and management: The user must keep track of the amount of medicine intake and the amount of medicine distributed to calculate the medicine in stock and how to handle it. Besides the user can make the medicine inventory. Medicine Intake Management: User can add new, view, search, update and delete the medicine intaked from suppliers. Medicine Distribution Management: This includes: Main store distributes to substore and health insurance store. Substore and health insurance store distribute to departments. User also can add new, search, update and delete any information which is available in database. Substore makes the Monthly Medicine Expectation and Requests Medicine: User makes the medicine expectation every month bases on the medicine intaked amount, the amount of Medicine distributed to the department and the amount of the medicine in stock in a month. After that they will request the main store to distribute medicine for the next month. Look up Medicine Information: This part is like a dictionary providing all necessary information about medicine. User can add new, view, search, update, delete any information related to medicine in database. Expired date medicine keeping track: Keeping track of expired date medicine and show an alert when the medicine is near expired date. Discarded Medicine Management: All the expired Medicine or has any problem are discarded. This function also allows to add new, view, search, update and delete the discarded Medicine information. Returned Medicine Management: This function is to manage the medicine returned to the substore, health insurance store and main store when they are not used, expired date or have any problem. Other Distribution Management: Manage the medicine distributed in the special case. Report System: This system allows user to view and print any information relating to Lists, Medicine, Medicine Expectation and Bidding. 1.1.2. QUESTION PAPER Questionnaires are an effective way to survey, conduct information and gathering background information. We have some questions helping for analyzing: 1)How is the framework of organization in Pharmacy Department? The framework of organization in Pharmacy Department includes: Main unit Clinic unit Made-up medicine unit Distribution unit 2)How many stores are there in Pharmacy Department? There are: 1 main store 1 substore 1 health insurance store 1 discarded medicine store about 50 stores of department 3)What medicine’s information need to be considered? The related-to medicine information is: Medicine Unit Medicine Specification Medicine Classification Producing Country Manufacturer Supplier Expired Date 4)Where are medicine intaken from? The Medicine is intaked from Suppliers after organizing bid every one year. Made-up Medicine Unit. 5)How does Bidding process take place? There are 4 steps: Make the bidding document and invite bid to suppliers Suppliers send back the price quotation to hospital Open Bidding to evaluate the bidding package Announce bidding result to suppliers 6)Which Store is responsible for medicine expectation? The main store will make the medicine expectation bases on the amount of medicine intake and distribution as well as the medicine in stock. 7)After bidding, how long does hospital request for medicine? Based on the medicine requirement of substore, main store make monthly expectation and call medicine of the winning supplier. 8)In which case may the medicine be returned? Medicine returned when it is expired date, broken, or have any problem… 9)Which report related to medicine? The Staff must send the report related to medicine to the leaders of hospital every day, week, month, year or any time they require. They are: Medicine intake report Medicine distribution report Medicine in stock report Medicine inventory report Yearly expectation report Discarded medicine report Returned medicine report 10) Who will use this system? Only Administrator and the Delegated User can use this system. Administrator is responsible for assign role to Delegate User and has the full control to the system. Delegate user is the staff of the hospital who directly use this system. BACKGROUND We are living in the era of information technology, so the applications of IT are very commonly used. Medicine Management System software is developed used by computer instead of the traditional manual management which is less efficient. Bach Mai Hospital has been treated and cured for a large number of patients so the medicine management in the traditional method is becoming overload. Our software program is an efficient solution for medicine management in hospital. Medicine Management System in Hospital is developed follows the real time method which helps the updating information fastest, most convenient and most complete at any time. This software program has the friendly interface, easy to use. Furthermore, it helps hospital in understanding the situation fast, reporting, minimizing the using of papers by exchanging information between the departments through the Lan network. Pharmaceutical Management System software is developed using the newest programming language of Oracle Developer 6i( Form Builder, Report Builder) and the powerful database management system Oracle SQL*PLUS 8i. INFRASTRUCTURE This system is based on LAN with the Database Server Figure 1.1: Infrastructure of Pharmaceutical Management System in Hospital CONTEXT DIAGRAM Definition: The first step in constructing a set of data flow diagrams for an information system is to draw a DFD called the context diagram. A context diagram is a data flow diagram that shows the boundaries of the information system. The context diagram is a top – level view of the information system. The figure below will show the context diagram of the system. It includes one process: Multi device service online system; and four external entities: Admin,delegate user and admin,staff. This context diagram will show the relationship between three external entities: staff of hosital(include staff uses program), the administrator of the system ,the users who use our system through data flows. (one staff have many users, supplier is proviede medicine for supplier) Figure 1.2: The context diagram of Pharmaceutical Management System In Hospital FUNCTIONAL HIERARCHY DIAGRAM FUNCTIONAL DIAGRAM Definition A functional diagram is used to show system’s functions that will be constructed and the implementation process of data diagram. Moreover, function diagram will also be used to determine the appearance frequency of smaller process in the data flow chart. If during the construction of functional diagram, analysts identify new functions, the analysts need to determine if it is a wrong move to ignore the discovered functions. It is necessary to decide to add or remove in the most appropriate way. Functional analysis with the help of modeling tools provides important details that will be used often in later stages of analysis. With detailed job description, information processing and exchanging process, input and output of every function will help analysts under stand more clearly system’s requirements. However, it is necessary to note that function approach to issue is not a comprehensive approach. A function diagram only shows what to do not how to do. In a functional diagram, a function is divided into many smaller functions and each smaller function contains even smaller ones. Constructing diagram is a process of division, from a higher function to appropriate smaller functions. Diagrams need to be presented clearly, simply, exactly, fully and well-balanced. Function of the same level of difficulty need to be on the same page. Figure 1.3: The Functional Hierarchy Diagram of Pharmaceutical Management System In Hospital Pharmaceutical Management System in Hospital has 5 main functions System management Each user have their own role assignment made by admin to login the system, user have to login with a valid user name and password. Besides user can change their password, so the security for system is very good. List management This function provides all necessary information about Medicines, Medicine Unit, Medicine Specification, Medicine Classification, Store, Department, Producing Country, Manufacture, Supplier. User can add new, search, update and delete information in each list. Medicine expectation and bidding management Medicine Expectation Management: To have the yearly medicine expectation the user must bases on the medicine imported amount and the amount of medicine exported to the substore and the health insurance store, the amount of the medicine in stock in a year. User who has the role assignment can add new, update, search, and delete the information. Medicine Bidding Management includes: Bidding Document: The user sends the bidding document to each supplier. Price Quotation Input: When the supplier received the bidding document they will fill in the document the price of each medicine and send back to the hospital. Bidding Open: After have the price quotation, the user will inputs the bidding information of suppliers and then decides which supplier win and get the medicine bidding contract. Bidding Result Announcement: The user will announce the bidding result to suppliers Medicine management Medicine Management is very important so this function is given to do all the works related to medicine. Medicine in stock keeping track and management: The user must watch for the amount of medicine got in store and the amount of medicine distributed to calculate the medicine in stock and make an inventory. Medicine Intaket Management: User can add new, view, search, update and delete the medicine. Medicine Distribution Management: This includes: Main store distribute to substore and health insurance store. Substore and health insurance store distributet to departments. User also can add new, search, update and delete any information which is available in database. Make monthly medicine expectation and call for medicine: Hospital makes the medicine expectation every month bases on the medicine imported amount, the amount of medicine exported to the department and the amount of the medicine in stock in a month. After that they will demand the main store to export medicine for the next month. Look up medicine information: This part is like a dictionary providing all necessary information about medicines. User can add new, view, search, update, delete any information related to medicine in database. Keeping track of expired date: keeping track of expired date to show an alert. DiscardedMedicine Management: All the expired medicines or has any problem are canceled. This function also allows to add new, view, search, update and delete the canceled medicine information. Returned Medicine Management: This function is to manage the medicine returned to the substore, health insurance store and main store when they are not used or due to a certain reason. Other Medicine Distribution Management: Distribution according to requirement of the leader or for a certain program Report system This system allows user view and print any information relating to List, Medicine, Medicine Expectation and Bidding. DATA FLOW DIAGRAM Definition Data flow diagram describes the information flow in the system. The next step of system analysis is to consider in detail the information necessary for the implementation of functions discussed above and the one necessary for the improvement of the functions. Modeling tool frequently used for this purpose is data flow diagrams. Data flow diagram will support 4 main activities: Analysis: DFD is used to determine requirements of users. Design: DFD is used to map out plan and illustrate solutions to analysts and users while designing a new system. Communication: One of the strength of DFD is its simplicity and easy to understand to analysts and users. Documents: DFD is used to provide special description of requirements and system design. DFD provide an over view of key functional components of the system but it does not provide any detail on these components. We have to use other tools like database dictionary, process specification to get an idea of which information will be exchanged and how. Components of DFD: Process Data Data External After analyzing the requirements of the system, all the functions of the system (be shown in figure 1.3) will be clarified by those data flow diagram. Below is the DFD level 0 with 8 processes (1.System Management, List Management, , 2.Manage account, 3Medicine Expectation and Bidding management, 4.Medicine Mangement 5.Report System, 4 external entities (Admin ,supplier,deleghateuser,staff) and 9datastores DFD Level 0 of Pharmaceutical Management System in Hospital Figure 1.4 - DFD level 0 for Pharmaceutical Management System in Hospital DFD level 1 The following DFD is for System management function. When admin or user want to login to the system, they have to input username and password. One of the advantages of our system is it allow one user can login to the system with several registed username. It includes: Four processes : Login, Manage user, Change password, Login with other user Two extenal entities: Admin and Delegated user One data store: User Figure 1.5 - DFD level 1 for SystemManagement Figure 1.6- DFD level 2 for SystemManagement The figure (level 2) above allow admin to manage users, including six functions: View, Search, Add, Update, Delete, Role Assignment, one external entity: Admin, and one data store: Users Figure 1.7- DFD level 1 for List Management The above DFD diagram is for System management function. Users can manage all the information related to Pharmacy department including: List of medicine,…. etc. It involves Nine process: Manage List of Medicine, Mange List Store, …etc. Two extenal entities: Admin and Delegated user One data store: User Figure 1.8- DFD level 2 for List Management This function allows admin and delegated users to Search, Add new, Update, Delete an item in a list. Information can get and set from (to) data store D2:List Figure 1.9 - DFD level 1 for Medicine Expectation and Bidding Management The figure above has two main processes: Yearly Medicine Expectation and Manage Bidding. The user must base on the intake amount and the amount of medicine distribute to the substore and health insurance store, the amount of medicine in stock in one year. User who has the role assigment can get info and make an expectation amount of medicine. The hospital will consider the expectation for openning bidding Two process: Make yearly medicine Expectation and Manage Bidding Two extenal entities: Admin and Delegated user Five data store: D3:Receip, D4: Medicine, D6: Yearly Medicine Expectation, D7: Bidding package, D8: Price Quotation Figure 1.10- DFD level 2 for Yearly Medicine Expectation Management The figure above show that admin and delegated user can do four function: Add new, Search , Update, Delete. Four processes: Add new, Search , Update, Delete. Two extenal entities: Admin and Delegated user Five data store: D3:Receip, D4: Medicine, D6: Yearly Medicine Expectation Figure 1.11. DFD Level 2 for Bidding Manaement Four processes: Make Bidding Document, Input Price Quotation, open Bidding,Announce Bidding Winner Two extenal entities: Admin and Delegated user Two data store: D7:Bidding Package, D8:Price Quotation Figure 1.12 - DFD level 3 for Bidding document Bidding Management The figure above allow delegated user make bidding document to each supplier. Three processes: View Bidding Document, Add New Bidding Document, and Imvite Bidding. Two extenal entities: Admin and Delegated user Two data store: D7:Bidding Package Figure 1.13- DFD level 3 for Price quotation of Bidding Management The figure includes: Three processes: View Price Quotation, Add New Price Quotation, Update Price Quotation Two external entity: Admin and Delegated user Three data stores: D3: Receipt, D4: Medice, D9: Department Figure 3.14 - DFD level 3 for Bidding open of Bidding Management Figure above shows the Bidding Open. After have the price quotation, the user will input the bidding information of suppliers and then decides which supplier win and get the medicine bidding contract Three processes: View Bidding Document, Add New Bidding Document, Quotation Invite supplier for bidding. Two external entity: Admin and Delegated user Three data stores: D7: Bidding Package Figure 1.15- DFD level 3 for Bidding result of Bidding Management Figure above show the bidding result announcement. The user will announce the bidding result to supplier. Two processes: View Bidding Result, Inform Bidding Result Three external entity: Admin and Delegated user, Supplier Three data stores: D8:Price Qutation Figure 1.16. - DFD level 1 for Medicine Management The figure above allows admin and delegated users manage medicine in store, including: Nine processes: Manage and keep track of medicine in stock, Manage medicine intake, Manage medicine distribution, Look up medicine information, keep track of expiry date, manage dicarded medicine, manage returned medicin, manage other distribution. Four stores: D3: Receipt, D4: Medicine, D5: Monthly Expectation, D9: Department Figure 1.17- DFD level 2 for Medicine- in- stock keeping track and Medicine Inventory The figure above show Medicine in stock keeping track and make an inventory. The user must keeping track for the amount of medicine got in store and the amount of medicine distributed to calculate medicine in stock and make an inventory. Two processes: View medicine in stock, Inventory Medicine Three external entity: Admin and Delegated user Three data stores: D8:Price Qutation, :D3: Receipt Figure 1.18 - DFD level 3 for Medicine Inventory of Medicine Management Admin and Delegated Users can make an inventory to compare the real medicine in stock with the amount of medicine in receipt. Four processes: Search, Add new, Update, Delete an Inventory receipt Three external entity: Admin and Delegated user Three data stores: D4:Medicine, D3: Receipt Figure1.19 DFD Level 2 for Medicine Intake Management of Medicine Management Admin or delegated user can Add New, Search, Update, View, Delete an Receipt. Information can set to or get from data store D3: Receipt, D4: Medicine Figure 1.20 - DFD level 2 for Medicine distribution management of Medicine Management This figure include: Main store distribute to substore and Subtore and health insurance store distributed to department.Admin and delegate users can add new, search, update and delete any information which is availbale in database. Two processes: Distributed to substore and health insurance sote & distributed to store of departments Three external entity: Admin and Delegated user Four data stores: D4:Medicine, D3: Receipt,D9:Department. Figure 1.21. - DFD level 2 for Monthly medicine expectation and medicine calling of Medicine Management The figure above show monthly medicine expectation and medicine calling of medicine management. Hospital makes the medicine expectatin every month bases ont the medicine intaked amount, the amount of medicine distributed to the department and the amount of the medicine in stock in a month. After that they will request the main store to distribute medicine for the next month. Two processes: Make monthly medicine expectation , request medicine . Three external entity: Admin ,Delegated user,Supplier Three data stores: D4:Medicine, D3: Receipt,D5:Monthly Medicine Expectation Figure 1.22.- DFD level 1 for System Management METHOD OF INVESTIGATION Data collection method:collecting and obtaining copies of the actual forms and operating documents that are currently used in the system. Survey method: We survey in the Bach Mai hospital to get information for the system. Preliminary Investigation: involves the system’s anylysis and feasibility. It defines what need to be done and scale of the problem that need to be solved in particular issues of the system. Detailed Investigation: Direct interview: The objective of the detailed investigation is to find out the best solution of the system and to make the good plan for our project. Moreover we can gather the necessary information to carry out the phases of the system development lifecycle. We contacted the doctors and staff of the hospital and make an appointment with them. Indirect Interview: We send email to the staff and they reply us the necessary information relating to medical knowledge. Telephone is also a good method for us to make an appointments. SCHEDULE OF PROJECT Figure 1.23 - Schedule of the project In the first three phases (Primary investigation, System analysis and System design phases), all project members work together. The last two phases (system development and test & evaluation phases), each member has developed his/her own functions as listed in the following table (Table 1.2 – System development schedule). Thieu bang DATABASE DESIGN Definition of normalization Normalization is a process by which you identify and correct inherent problems and complexities in your record designs. You start with a collection of record designs and through normalization produce a new collection of records equivalent to the original but as free of potential processing problems as possible. The normalization process involves three types of normal forms: 1st normal form, 2nd normal form, and 3rd normal form. These three normal forms constitute a progression in which record that is in 1st normal form is better than one that is unnormalized; a record that is in 2nd normal form is better yet; an a record that is in 3rd normal form is the best. First Normal Form: A record is in first normal form (1NF) if it does not contain a repeating group. A repeating group is a set of one or more data items that can occur a variable number of ties in a record. Second Normal Form: A record design is in second normal form (2NF) if it is in 1NF and if all fields that are not part of the primary key are dependent on the entire primary key. If any field in a 1NF record depends on only one of the fields in a combination primary key, then the record is not in 2NF. A 1NF record with a primary key that is a single field is automatically in 2NF. Third Normal Form: A record design is in third normal form (3NF), if it is in 2NF and if no nonkey field is dependent on another nonkey field. Definition of Entity Relational Diagram ERD is network model that describes stored data of a system at a high level of abstraction. For system analyst, ERD has a major benefit: it highlights the relationship between data stores on DFD which would otherwise only be seen in the specification process. The main components of an EFD include: Entity: is a subject, a duty, or an event that has a significant meaning to the future system and is displayed by a rectangle with round corners. Each entity has its own name. Attribute: are the characteristics of the entity displayed by fields or columns of a table. Relationship: shows connections among the system’s entities. These connections are displayed by triangle headed arrows. There are 3 major types of relationship used in ERDs: One – one relationship One – many relationship Many – many relationship THIRD NORMAL FORM Table Medicine Table General Medicine Table Producing Country Table Store Table Store Medicine Table Bidding Expectation Table Bidding Package Table Monthly Expectation Table Price Quotation Table Medicine Intake Table Medicine Request Table Medicine Inventory Table Medicine Discarded Table Medicine Return Figure : Entity Relationship Diagram CHAPTER 2 - PROGRAM SPECIFICATION PROGRAM IDENTIFCATION Project Name: PHARMACEUTICAL MANAGEMENT SYSTEM IN HOSPITAL Programming Language: Oracle Developer 6i Database: Oracle 8i SQL*PLUS Platform: Windows 2000 Advanced Server and Windows XP Design Tools: Microsoft Office Visio 2003 SUPERVISOR: Dr.PHAM CONG NGO DEVELOPER: Project by Nguyen Tuan Manh Start Date: December 2006 End Date: May 2007 Program Size: PROGRAM DESCRIPTION Overview Pharmaceutical Management System in Hospital is developed with the aim to solve many difficult in medicine management. This software is support the nurses and doctors in medicine management efficiently. Beside, this system support administrator to manage users, assign role for each user to perform a certain function such as manage medicine in stores, make a medicine expectation or give a report. This system is developed by Oracle Developer 6i (Form builder, Report builder) and database of Oracle 8i. Advantage of using the system Security using Username and Password authentication Oracle help to save a huge data and execute command fast and effectively Used for a large system and organization Friendly GUI based on well-designed Oracle Form and Report Role-based security using a fixed principal for role authorization Easy and save Backup database and easy manipulation How the program works The operation starts with a staff click on icon of software system, the main form of program will display. Unregistered staffs are able to view general information such as:general medicine information, staff information. Registered staffs log in system can manipulate with functions of system that they are granted permission. Admin has full control in managing the system Delegated user a staff in the hospital to whom admin assigns role for performing a certain function of the system Supplier provides medicine to the hospital Staff is a person (may be a nurse, a doctor or leader of the hospital) who will make request (about the quantity of medine in a receipt or about report…) PROGRAM OUTLINE Here, we will show you how data stored in our program. Input Table Process Output Staff_ID, Dept_ID, Staff_Code, Staff_Name, Birth_Date, Sex,Position, Staff_Address Staff Add Update Delete Search View - Display Medicine Information - Report about Medicine Intaked - Report about Medicine Requested - Report about Medicine Inventory - Report about Medicine in Stock - Report about Medicine Discarded - Report about Medicine Returned - Report about Expired Date Medicine - Display Price Quotation Information - Display Monthly Expectation Information - Display Bidding Expectation Dept_ID, Dept_Code, Dept_Name, Dept_Parent_ID, Dept_Address, Dept_Type Department Supplier_Code, Supplier_Name, Supplier_Address, Phone_Number, Fax, Website, Email, Delegate, Note, Abbreviate_Name, Decision, Licence, Tax_Code Supplier Unit_Code , Unit_Name Unit Specification_Code, Specification_Name Specification Store_Code, Dept_ID, Store_Name, Category, Health_Insurance, Intake, Distribute Store Medicine_Code, Store_Code, Stock_Amount, FIFO_Amount, Total_Curency, Use Store_Medicine Medicine_Code, General_Code, Country_Code, Medicine_Name, Use, Manufacture_Code, Effect_Code, Specification_Code Medicine General_Code, International_Name, Effectively, Unit_Code, Classifying_Code General_Medicine Bidding_Expectation_ID, Classifying_Code, Year, Expectation_Staff_ID, Expectation_Date, Note Bidding_Expectation Bidding_Package_ID, Year, Bidding_Package_Name, Open_Date, Council, Opinion, Chairman, Bidding_Package Monthly_Expectaion_ID, Expectation_Date, Year, Month, Classifying_Code, Expectation_Staff_ID, State, Note Monthly_Expectation Price_Quotation_ID, Bidding_Package_ID, From_Date, To_Date, Supplier_Code Price_Quotation Medicine_Discarded_ID, Discarding_Date, Discarding_Staff_ID, Store_Code, Note Medicine_Discarded Medicine_Intaked_ID, Store_Code, Intake_Da._.