WX Inventory Map
Last year, after an exceedingly long rainy spell, we developed a water issue in the basement. Which just happened to be the primary storage location for the hospital's expendable items stock. We quickly moved the entire inventory to a suitably dry location. However, as it was quickly assembled and stock was now in a completely unfamiliar place and order, two tasks were started: while the manager started re-tagging the items and entering locations in the database, I started to work on another .NET Maui app. This time a mapping app for quickly locating products in their new location.
Having a hosted LAMP server, I started by modifying my MySQL database by adding a new table:
CREATE TABLE `primarybarsamian` ( `imf` varchar(6) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`description` tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`type_issue` char(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`location` varchar(13) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`vend_num` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`man_num` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
PRIMARY KEY (`imf`), FULLTEXT KEY `description` (`description`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
TIP: If using phpMyAdmin, after creating the table and filling it with your data, on the Structure tab under the Table structure section - click on the link Propose table structure. Your data will be examined and an optimal field type will be suggested for each column.
The next step was to create a REST API. My hosting service provides a few options for creating API's, including Node.js and Python, but I chose PHP because I am a little more familiar with the language. I created three folders in the web server's root html directory ('public_html' on Apache server): api, config, and class. In the config folder, define the connection:
In the config folder define the connection (dbclass.php):
<?php
class DBClass {
public $conn;
public function getConnection(){
$this->conn = null;
try{
$this->conn = new PDO("mysql:host=HOSTNAME;dbname=DATABASENAME;charset=UTF8", "USERNAME", "PASSWORD");
}
catch(PDOException $exception){
echo "Error: " . $exception->getMessage();
}
return $this->conn;
}
}
?>
In the class folder define the data and the query (findproduct.php):
<?php
class FindProduct{
// Connection
private $conn;
// Table
private $db_table = "primarybarsamian";
// Columns
public $imf;
public $description;
public $type_issue;
public $vend_num;
public $man_num;
public $location;
// Db connection
public function __construct($db){
$this->conn = $db;
}
// GET ALL
public function getProducts(){
$param = $_GET['v'] ?? null;
$param2 = $_GET['a'] ?? null;
$sqlQuery = "SELECT imf, description, type_issue, vend_num, man_num, location FROM primarybarsamian WHERE description LIKE CONCAT('%', ?, '%')
AND description LIKE CONCAT('%', ?, '%')";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->bindParam(1, $param);
$stmt->bindParam(2, $param2);
$stmt->execute();
return $stmt;
}
}
?>
In the api folder use a fetch statement to retrieve the data (read.php):
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../config/dbclass.php';
include_once '../class/findproduct.php';
$dbclass = new Dbclass();
$db = $dbclass->getConnection();
$items = new Findproduct($db);
$stmt = $items->getProducts();
$itemCount = $stmt->rowCount();
//echo json_encode($itemCount);
if($itemCount > 0){
$productArr = array();
//$productArr["itemCount"] = $itemCount;
//$productArr["body"] = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
$e = array(
"Imf" => $imf,
"Description" => $description,
"Type_issue" => $type_issue,
"Vend_num" => $vend_num,
"Man_num" => $man_num,
"Location" => $location
);
//array_push($productArr["body"], $e);
array_push($productArr, $e);
}
echo json_encode($productArr);
}
else{
http_response_code(404);
echo json_encode(
array("message" => "No record found.")
);
}
?>
To test if this is working correctly, open your web browser and navigate to your server with the following address:
https://YOURSERVER.com/api/read.php?v=TERM1&a=TERM2
You should see a list of items that match your search terms.
You can also download these files at GitHub.
Now I just needed to create an app that uses this REST API. For this I used .NET MAUI. I have really started to like this cross platform tool from Microsoft and hope it has a long future. The home page in my app provides a search function to look up a product. Because I used the MySQL LIKE clause in the query, the search terms do not have to be specific, yet query time remains pretty good having created a FULLTEXT index on the description column. The column for which the search terms are located in.
After locating the item you are looking for, clicking on it will take you to the map page:
The cart on which the item is located is highlighted with a three digit code (you may have to scroll to see it). Carts are set up with row letters, top row is A and goes up as the rows go down. Slots are numbered from one and up starting from the left. The 'D01' indicates the item is on row (D), or the 4th row, and the slot it is situated in is (01), the first slot.
The source code for this app can be found at GitHub. Before compiling, make sure to update the URL string in the MainPage.
