Pharmacy Purchase Order Generator

Problem and Idea

While doing my last pharmacy year internship at the local Hospital’s Pharmacy I noticed that they had to manually duplicate and fill a Microsoft word template table everytime they needed to make a purchase requests for new drugs.

In addition ,the pharmacy had only one administrative computer (without internet) so there was no margin for delegating the work .

I was learning JavaScript at the time and decided to put it into action, transforming the workflow to few clicks and limiting the manual input to How much to order rather than What to order.

The idea is simple , one tab for data entry using html forms and another tab for preview, and a print button.

Data entry tab (Home)

After setting the Order number (1) the user can go and chose which product to add to the purchase list , products are organized inside categories (2)

Upon clicking on a product , fields are pre-filled and the cursor automatically moves to the quantity field (3) . After setting the appropriate values , user can go ahead and Add an entry to the orders list (4)

Scrolling down there is the List of orders , an element can be removed from the list using the delete button

Preview tab

The data is rendered inside a template , and can be previewed in the Preview tab :

When ready, the user can click the print button and chose either to print it directly or download it to local storage as pdf (recommended)

After clicking print ,the page title is changed to reflect the purchase order number, this will set the correct filename if the user decided to save the document locally .

An offline first solution

As mentioned above, the end-user had no internet access so the project had to be both lightweight and portable .

I used Bootstrap for styling (without js) and wrote my own vanilla Js for the described functionalities . As for the products data , the staff provided me with a Microsoft Spreadsheet file that has products list organized in different sheets .

In Google Colaboratory I used Python’s library openpyxl to access the file and map it to a JSON data structure : a dictionary of categories where each category is a list of dictionaries of products .

data = {
    "Category1": [
        {
            "code": 1111,
            "name": "Product 1",
            "uc": "B/100"
        },
        {
            "code": 2222,
            "name": "Product 2",
            "uc": "B/1"
        }],
    "Category2": [
        {
            "code": 3333,
            "name": "Product 3",
            "uc": "B/100"
        },
        {
            "code": 4444,
            "name": "Product 4",
            "uc": "B/1"
        }
	]
}

Google colab python code:

import openpyxl

# Load the file
filename = "nomnc_clean.xlsx"
wb = openpyxl.load_workbook(filename)

# Check the file is correctly loaded
wb.sheetnames

# > ['Consommables', 'Conservation FROIDE', 'Anti Cancereux', 'Anti Retroviraux', 'Anti Tuberculeux', 'Chimique', 'Dentaire', 'Réactif', 'Médicament', 'Soluté Massif', 'Psychotrope']

# This function takes a sheet instance of a category and return a dictionary that is used as a node in the datatype .
def sheetodict(sheet):
	sheetdict = {sheet.title : []}
	
	for i in range(4, sheet.max_row+1):
		sheetdict[sheet.title].append({
                "code": sheet.cell(i, 1).value,
                "name": sheet.cell(i, 2).value,
                "uc": sheet.cell(i, 3).value
                })

	return sheetdict

# The main dictionary
data = {}

# loop through sheets (categories) pass them to sheetodict , save result in data
for sheet in wb.sheetnames:
	data.update(sheetodict(wb[sheet]))
	
# output
import json

with open("data.json", "w") as outfile:
	json.dump(data, outfile, indent = 4)

Finally Packed the whole page inside one html file index.html and imported the needed assets in the head

<head>

	<title>Hospital Pharmacy Purchase Order Generator</title>
	
	<script type="text/javascript" src="data.js"></script>
	<script type="text/javascript" src="script.js"></script>
	<link rel="stylesheet" href="style.css">
	<link rel="stylesheet" href="bootstrap.css">
	<link rel="icon" href="/favicon.png">

</head>

Conclusion

This project still have many possibilities for enhancements and updates , one of the my wishlist features is implementing a local history database file for previous purshase lists that can be loaded and edited , though , I had some problems with saving a json file in background to local stoarge due to browsers security limitations .

Neverthless , this simple project is currently deployed at the local Hospital’s Pharmacy , saving the staff more time to spend improving patients health instead of fighting with MS word .