• Bui Nhien Loc (15635) (Team leader)
• Bui Le Phi Long (16619)
• Dinh Cong Minh (16047)
• Nguyen Tuan Ngoc (15809)
• Tran Ngan Thao (16301)
• Duong Ngoc Thao Dan (16079)
• Nguyen Anh Tai (16128)
• Cao Xuan Hai (16140)

The aim of this project is to develop an application for the effective management of a pharmaceutical store. It helps the pharmacist to maintain the records of the medicines/drugs and supplies sent in by the supplier. The admin who are handling the organization will be responsible to manage the record of the employee. Each employee will be given with a separate username and password. The users can communicate each other by using a built-in messaging system. Pharmacy management system deals with the maintenance of drugs and consumables in the pharmacy unit. It application can generate invoices, bills, receipts etc.

During this project, the tasks were divided as shown in the following table:

This final report for the Programming Exercise Project, MediMe, is submitted to partially satisfy the requirements of the Programming Exercise course at Vietnamese-German University, and includes a technical description and architecture of the program, the data flow diagram through the program. Our goal is to realize MediMe as a web-based platform where big-chain pharmaceutical company can used to manage their products. This will bring people the quality of user experience for employees and managers, especially during the Monkeypox outbreak is approaching.

The intended audience of this document is the course instructor, Prof. Huynh Trung Hieu, who will use it for the evaluation and determination of a portion of the grade for the course.

This report has been written to familiarize the audience with the features of the program while presenting the schedule and progress of the project. A detailed explanation of the framework used and the applications and functions are not included in this report. To mark this project as a success, we have to achieve the following requirements:

1. Log in and authentication for Admin and Employees.

2. Create, Update, Delete new account (only for Admin).

3. Product distribution.

4. Private Messaging system (include send, read, and view messsages).

5. Generate daily reports (Total sale of the day for Employees)

6. Generate monthly reports (Import and export products for Admin)

7. Notify Employees about expiring or out-of-stock Products.

During our time working as a team, we encountered the following setbacks:

• Lack of experience in working with industry and professional projects.

• Inefficient in working with Firebase and JavaScript.

The project is expected to be completed in 9 weeks. Progress is documented below. The process has been very smooth, with only minor setbacks along the way. Nevertheless, we believe that there is room for improvement in this project. With an extended deadline, the project can become a full-fledged website application.

Use Type: E-Employee; A-Admin, S-System;

Categorized: F-Functional, N-Nonfuctional.

This web application was created as a private system to manage a franchise of pharmacies.

With the purpose of security, the administrator is the only actor being able to create a new account for employees - new store of the franchise. Besides, only the administrator is granted the right for account modification including edit and delete functions.

In addition, the web application allows both admin and employees to log in to their respective accounts. Once accessed, the users can communicate with each other via a messaging system including read, write and send.

When an employee records a transaction from the customers (so-called product exportation) and from the suppliers (so-called production importation), the information is saved in the system. At the end of the day, a report is generated for the management of the business. Regarding this scenario, the system is an actor because it would automatically regulate the good's expiring date and the remaining stock as well as updating the employees on necessity.

On notification, the administrator will start the import process, which would be reported at the end of the month.

After having a group discussion, we decided that our approach is to design a web application focusing mostly on people who are currently at a certain age and have a lack of experience in technology and the internet. Hence, we have to choose a logo that not only represents the quality of the product but also means that we work for the people. That is why we decided to use the current logo for the MediMe pharmacy management system. The name MediMe itself can be divided into 2 phrases which are "Medi" for medicine and "Me" for the people. We use ReactJS and CSS for the overall design of the system. If ReactJS works as a skeleton of the system, CSS will work as the skin - show exactly how our product looks like. Most of the icons, buttons, banners, and cards in the system are self-designed; some were taken from Box-icons or Ant designs framework. For 9 weeks of the project, we try to stick to the WYSIWYG concept (what you see is what you get) to design an intuitive system for users to experience without having any problems. The members of the front-end team will discuss and find inspiration online to acquire the most suitable design for the system, then sketch the prototypes on Figma for better visualization. Subsequently, we will design the actual product based on it.

For our project, we decided to use PostgreSQL as our DBMS because it is a powerful and reliable open-source object-relational database system, which provides us what we need to manage our data.

As being a SQL system, PostgreSQL allows us to retrieve data faster and more efficient via queries, especially with a large amount of data needed to be handled in our project.

An SQL-based system like PostgreSQL provides us the abilities to enhance our data integrity by using constraints and triggers. This will be mentioned more clear in the next sections.

The next important thing about our database is ElephantSQL. It is a PostgreSQL database hosting service which will give us 20Mb of database storage for free. As an online host service, ElephantSQL allows us to cross working by inviting other people to access the database, so that our team members can test and query data separately so that the efficiency will be higher.

Our database is created by mapping from ER diagram as shown in the previous part, to the Relational Database diagram. However, the first version of the database was poor and it was quite hard for our back-end team members to query. For satisfying user requirements as well as make it simpler for the members to work with the database, the next version of the database is developed. In this version, we have kept all the tables from the previous version while adding new attributes to some tables, coming with new user-defined SQL functions and triggers.

The database consists of 10 tables as follows:

• Product(product_id, category, product_name, unit, price, status, expiring_date)

• Supplier(supplier_id, supplier name, email, address, character, phone)

• Lot(lot_id, import date)

• Branch(branch_id, address)

• Employee(employee_id, role, branch_id, mail_addr)

• Customer(phone, fullname, expenditure)

• Invoice(invoice_id, export_date)

The tables above are to record the "entities" of our database, while the next 3 tables are to record the changes when importing and exporting the products to the system:

• Supply_line(supplier_id, product_id, lot_id, expiring date , quantity, price): this table is to record the action of importing product from a supplier to the system.

• Branch_lot_line(import_number, branch_id, lot_id, product_id, expiring_date, quantity): this table is to record the action of importing product from system to a specific branch.

• Serve line(employee_id, product_id, invoice id, cust phone, quantity): this table is to record the action of selling product to a customer.

Beside the table, another very important factor of the database is the triggers and functions. In our database, we have defined 14 functions, while 9 among them are trigger functions, which will be stated further in the next section. The remaining 7 functions are:

• get_remaining_quantity(br_id uuid, prod_id uuid): This function will return the remaining quantity of a product in a branch by taking the branch id and product id as parameters. The algorithm for this function is quite simple, as we just have to take the subtraction between the quantity imported to the branch and the sold quantity from that branch of the given product.

• get_product_expiring_date(br_id uuid, prod_id uuid): This function will return the expiring date of a product of a branch. The return expiring date is defined as the nearest expiring date, or the min expiring date of a product, as there will be the case that the Admin will import the product from several lots with different expiring dates to the branch. To calculate it, we define a rule that for a branch, when selling product, the nearest expiring date ones will be exported first, then we just have to compare the sold quantity of a product to the quantity of that product but of different expiring dates, then we can decide the expiring date based on the quantity range that the sold quantity have fallen into.

• get_product_status(br_id uuid, prod_id uuid): This function will return the status of a product in a branch. We checked the status of the product in the following order:

  • If the remaining quantity = 0 then the status is 'OUT OF STOCK'
  • If the remaining quantity < 10 then the status is 'NEARLY OUR OF STOCK'
  • If the expiring date - the current date <= 0 then the status is 'OUT DATED'
  • If the expiring date - the current date < 10 (days) then the status is 'NEARLY OUT DATE'
  • Else the status is 'NORMAL'

• get_product_expiring_date_admin(prod_id_uuid): This function is also get the expiring date of a product, but in the whole system rather than single branch. For this function, we have spotted out 3 cases:

  • The first case is when the Admin imported the product from supplier to the system, but has not exported it to any branch. Then the function will return the min expiring date of the product in the system found in the supply_table.

  • The second case is when all the imported products from supplier have been sent to the branches. Then the expiring date of the product is the min expiring date of that product among the expiring dates we got from all the branches that the product has been exported to.

  • The final case is when the product is exported to the branches partly, then there exists some product remaining in the Admin system. Then the expiring date now is the min(expiring_date_remaining_in_system, expiring_date_among_branches). The second one can be found similarly to the second case, while the first one is more complicated. We have to find out the expiring dates of the product that is exported in the branch_lot_line table, and all the expiring dates of that product in the supply_line table. The 2 sets are S1 and S2 respectively. Then we take the subtraction of 2 sets, S2 - S1 and the get the expiring dates that are not exported. The expiring_date_remaining_in_system is the min expiring date of that set.

• get_product_status_admin(prod_id uuid): This function is like the same as the get_product_status(br_id uuid, prod_id uuid) function, but for Admin and it uses the function get_product_expiring_date_admin(prod_id_uuid) instead.

• update_product_status_admin(): This function is to update the status of all product, it calls the function get_product_status_admin(prod_id uuid) for updating.

• calculate_invoice(inv_id_int): This function is simply used to calculate the money got from selling product, based on the invoice id.

To maintain the consistency of our database, various method has been used, but mainly CONSTRAINT and TRIGGER

As shown in the Relational Database Diagram, our constraints are mainly PRIMARY KEY and FOREIGN KEY, where the PRIMARY KEY attributes are presented by the yellow key in the diagram and the FOREIGN KEY attributes are presented by the lines. Another CONSTRAINT that we used is the UNIQUE constraint. We also defined the constraint UNIQUE on the address attributes and the phone number attributes. In this database, the id attributes except invoice_id are of the type UUID, which will be automatically created by the built-in function uuid_generate_v1() of PostgreSQL for the referenced ids.

Another way that we have used to maintaining the consistency is the TRIGGER and TRIGGER FUNCTION. In PostgreSQL, we can not directly put code in the TRIGGER but execute the TRIGGER FUNCTION. Then we have defined our 7 trigger functions ourselves. The detailed functions can be found in the database.sql script file. The functions and triggers are:

• FUNCTION : import_from_supply()
  TRIGGER: import_product
  triggered when: after insert on Supply_line
  This function is to automatically update the unit of the product when the admin import product from the supplier to the system.

• FUNCTION : export_to_customer()
  TRIGGER: export_product_customer
  triggered when: before insert on Serve_line
  This function is to automatically update the unit of the product when a product is sold to customer.

• FUNCTION : check_exist_supply()
  TRIGGER: check_exists
  triggered when: before insert on Branch_lot_line
  This function is to prevent the insertion on the Branch_lot_line table the tuple that have the combination of product_id, lot_id, expiring_date that did not appeared in the Supply_line table.

• FUNCTION : calculate_import_price()
  TRIGGER: calculate_price
  triggered when: before insert on Supply_line
  This function is to automatically calculate the price of importing product to the system.

• FUNCTION : check_expiring_date()
  TRIGGER: check_expire
  triggered when: before insert on Supply_line
  This function is to check whether the expiring date of a product is larger than the import date of the lot containing it or not.

• FUNCTION : check_quantity_branch()
  TRIGGER: check_quantity
  triggered when: before insert on Branch_lot_line
  This function is to check whether the quantity of product being exported to a branch is larger than the remaining quantity in the system.

• FUNCTION : check_quantity_export()
  TRIGGER: check_quantity
  triggered when: before insert on Serve_line
  This function is to check whether the quantity of product being sold is different to 0 or not.

Users without an account will not be able to log in. Users also can not create new account to access the system as this a private company's application. The functionality of creating new account is reserved for only Admin. To do this, the Admin have to login to their account in the MediMe app, When login, on the dashboard page, a unique function 'Manage accounts' that exclusive to Admin is selectable. This will lead the Admin to a page that they can see all the member in a list. From here, they can choose to view, edit, delete or in this case, create new account. To do this, they have to select 'Add user', which a window will appear with 4 empty fields for 'Name', 'Branch', 'Email', 'Password', respectively. Fill in all the needed information and press the 'OK' button and a new account has been created, with an email sent to the entered email about the info needed to access the app. In the other hand, if the info is incorrect or invalid, an error window will pop up and let the user knows which information needed to be changed.

Firebase Authentication uses an internally modified version of scrypt to hash account passwords. Even when an account is uploaded with a password using a different algorithm, Firebase Authentication will rehash the password the first time that account successfully logs in. Scrypt is one of the first hashing algorithms implemented on blockchain networks. It is an attempt to improve upon an earlier hashing algorithm. Scrypt is a password-based key derivation function (KDF),generally efficient at preventing brute force password guessing attacks. Scrypt was developed as a solution to mitigate the rise and dominance of ASIC mining rigs and subsequent centralization of cryptocurrency mining Scrypt’s design requires miners to generate random numbers rapidly. These numbers need to be stored in the Random Access Memory (RAM) of the processor, which must be accessed on a continuous basis before submitting a result.

One of the important factors which differentiate MediMe from other pharmacy management systems. Providing a simple and minimal user interface unlike any other system, employees are able to keep track of the product easily and conveniently. Thanks to the built-in components - react-table from ReactJS, the product information can be displayed in the form of a table. The data for display is received from the JSON file (which is regularly exported from the database). From the storage, the employee has the freedom to customize what to display on the table by using the following functions which have been implemented for use:

• Sorting: At default, the data will be displayed based on the order of the product in the database. By pressing the column, the data will be displayed in ascending (a to z) or descending (z to a) order. There will the a small notation (up or down arrow), to notify user that they are currently using sorting function.
• Search bar: Users can search for a product by using the search bar implemented in the system. The search bar work as a filtering function, but globally. This means that you can search for anything to find the product you want since the limitation now is not only name-searching. (e.g. you can search for products that are nearly out of stock by typing exactly the same phrase into the search bar.)
• Pagination: When there are too many products in a branch, the pagination function will divide the total products into different pages. Users are able to choose how many products to display on a page, go between pages, go to a specific page, and go directly to the end or the beginning of the page instantly.
• Row Selection: Additionally, we have created an extra check-box column for users to select products and it will display in detail the information of the product in case of the name of the product is too long to be displayed on the table. Each product will be provided with a unique ID to distinguish itself from other products; product name; a number of products; the expiry date; the price per unit; and the status which is indicated based on the current stock and the expiry date, using the function get_product_status() to update the status every day and after a number of product is sold:
• If the current stock is below 10: NEARLY OUT OF STOCK
• If the current stock is 0: OUT OF STOCK
• If the expiry date is 10 days close to the current date: NEARLY OUT DATE
• If the expiry date is due: OUTDATED There is also an "Update Stock" button to update the product stock and status after a certain amount of product have been sold (employee) or when the user wants to import a certain amount of product into a specific branch (admin)

Expanding from the storage system, on the same data table, depending on your role, you can have a column for ’Export’ or ’Import’.

Import products is an Admin’s exclusive function, as for a real company to stay up-float, only the Admin can import the product and distribute it to the branches. As the storage is running low or the expiring date is approaching, The Admin can access the specific branch’s storage and choose which item and the amount by entering or scrolling the value in the ’Import’ column. By pressing the ’Update Stock’ button, a function from the database will be invoked to update the amount according to the input. This will also invoke the checking for expiration and stock function of the database to display the status of each product.

On the opposite end, export products is an employee’s exclusive function. From the point of view of the employee, the ’Import’ column from before will be replaced with the ’Export’ column. For real working pharmacy, they can not sold more than the amount they have in stock, so the front-end will prevent this from happening. To execute the import function, the employee have to input or scroll to the value for each product they has sold. Afterward, by pressing the ’Update Stock’ button, the system will generate a new invoice, add the sold product’s info along with the amount exported respectively. This invoice is then saved to the database for further reports. The amount and status of each sold medicine is then updated to the table.

After doing a research with the customer, it was dictated that the messaging system is used primarily for the staffs to communicate upon the requirments from the customers buying the products. For instance, if one product is out of stock at a specific branch and the customer does require that product in his order, the staff must communicate directly with other staffs to find the nearest branch where that product is still available. This is in the interest of increasing customer experience at Medime. Besides, Medime does require the integration of group chat so that it would be convenient for the staffs, especially the admin to deliver the necessary contents without having to search for every recipient.

After the discussion, there are 6 main components that we would integrate so as to not only satisfy customer's requirements but also enhance their user experiences.

• Display of active and inactive users
• Display of recent personal chats and the notification of new messages
• Display of group chats and the notification of new messages
• Adding members to group chat, viewing the room's members and removing specific members
• Search bars to find the recipients and targeted group chats
• Chat window where the users can send and view their messages

Messaging is a function used for every employee at Medime, irrespective of their roles. On entering the messaging page, the users can see 5 main components which are recent chat, group chat, chat window, active users display and inactive users display.

All the personal chats between the users would be displayed in the recent chat according to the users' latest chatting history. Thanks to this integration, the user would know which messages come first and not miss out on the newest ones. Besides, the notifications of new messages would be displayed here if the users do not click on the chat box. As a result, the current user would be updated if he received the messages from other senders.

In order to start the conversation, there are 3 ways that we had integrated so as to facilitate user's experience.

• The user can click on the chat box currently existing in the recent chat corner and interact with other users
• The user can click on the recipients displayed in the status corner in order to search for the employees who are currently active to interact with in case of emergencies. In case two users have the same name and avatar, which makes it difficult to differentiate between the recipients. A popover which displays the email and name of the user was integrated because each user was assigned a unique email and password.
• The user can search for the targeted recipients in the search bar if there are many users in the user history. The user just needs to input the name of the recipient in the search bar and find the one they would like to start the conversation. Likewise, if there are two identical users because of similar name and avatar, the presence of email on hovering would be helpful in differentiating among the targeted recipients.

Upon choosing the recipient, the chat window would appear. In the chat window, the user would see 3 components

• The name and status of whom the user is chatting
• The message sending corner where the user can write and send their messages
• The display of chat history including the message content, the sender and the time the messages were sent

With respect to the mechanism of the personal messaging, when the user sends a message, the last message between the two users would be updated in the database. Then, the content of the message would be stored in the messageusers table in firestore database. Concurrently, if the two users have never had a conversation before, the information of the new user would be added to the userhistory table for querying.

Reports are considered as important documents that can reflect the current situation of our business. As the database stores a lot of data, reports are introduced to provide a brief of information on what are concerned. In this case, the main focus is on the total sales of each day, imports and exports of each month. Therefore, there are two types of report that are included in our application which are daily report and monthly report. Within our concerns, the daily report contains the information on the total sales of that day, while the monthly report summarizes the information on imports and exports. Since there are many branches in our system, each report is specified for one branch only.

The daily report can be generated by an employee at the end of the day. The neccessary information for the report is provided by excecuting a rather complex query. The report has a list of products with their total quantity that was sold in the day. From the email of the employee, we can get the branch_id of the branch where they are working. Then, we can get the current date by create a new Date object in Nodejs and then use its day, month and year for filter. By joining the Serve_line table, the Invoice table, the Product table and the Employee table, we get the product name, its price and the total quantity for each time that product is served. Then we filter the result with the branch_id and the export_date to get the right branch and the right date for the report. The daily report can be exported to PDF format, which is discussed in the next section.

As for the monthly report, we do not want to include too many numbers so we decided to use line graph to demonstrate the trend on the amount of money for imports and exports. Similar to the case of daily report, the queries for the information of imports and exports have high complexity. We just need the information on the date and the amount of money for both cases. We need to get the branch_id, the month and the year of the current date and use them as filters like the daily report. First, we need sub queries on the imports of each time a product is imported to the branch and the exports of each time a product is sold filter by the month and year and the branch_id. Then, we use the sum function and group the result from the sub queries by date. Finally, we get the day and the total amount of money spend on imports and earn from exports for each day. The data is written to json file for the front-end to read and display them as line graph on the web. The PDF is not supported for this report due to some limitations.

Furthermore, since the reports only show some signficant information, we also include the details on imports and exports for checking inventory. Employee can click on the details tab to see the details of imports and exports of the branch. The details information is display on the web as tables. Users can choose whether they want to see the imports or the exports table. The queries for the information in the tables are simpliers compared to the reports queries. By joining multiple tables and select the needed column, we can get the the information. The data is also written to json files for the front-end to read and display them as tables.

Our app supports the functionality of export the daily report as a PDF file. We use the PDFKit library to generate PDF document. With the information gained from executing the related query, we put them on the PDF file by using the functions provided by PDFKit library. For displaying and allowing users to download the PDF file, the document is push to a buffer and then pipe it to the response of ExpressJS. Similarly, our app supports printable PDF format for invoices too.