A few weeks ago, my department asked me to gather and compile a comprehensive list of equipment that has been borrowed by other engineers or contractors. Unfortunately, the list I received was recorded in an Excel file. This means that whenever I need to check the status of the equipment, I have to wait for the engineers or contractors to send me an updated list by email or through a shared drive. This can be quite tedious.
So, I decided to create a web app for this purpose. Instead of using an Excel file, the engineers and contractors can simply go to the web app to update the equipment record or status. This way, whenever I need the latest list for an audit, I can easily export the data from the app. This is how I spent my weekend and the result of my efforts.
Screenshots
The app name is Equipment Tracking or Etrac for short, and successfully deployed on my company internal server.
About Etrac
Etrac is a custom-built PHP app that uses DataTables, jQuery, and MySQL as its database. The UI is designed with Bootstrap and a Google-style theme.
I hosted Etrac on an IIS7 virtual machine, where it is only accessible within the company’s intranet. Since the app is running on IIS, I can use the session to capture the user’s Active Directory (AD) account, which is used on the company network. This means that I don’t need to implement a user authentication module.
Etrac also supports exporting data to PDF, Excel, and CSV files.
Code snippets
DataTables JS code that I wrote for Etrac:
var table = $('.datatables-table').DataTable({
fixedHeader: true,
"processing": true,
"serverSide": true,
"ajax": {
"url": "datatables.php",
"type": "POST"
},
"columns": [{
"data": "id"
},
{
"data": "model"
},
{
"data": "description"
},
{
"data": "project"
},
{
"data": "serial_number"
},
{
"data": "owner"
},
{
"data": "current_owner"
},
{
"data": "remark"
},
{
"data": "id"
}
],
"order": [
[0, "desc"]
],
aLengthMenu: [
[10, 25, 50, 100, 200, -1],
[10, 25, 50, 100, 200, "All"]
],
iDisplayLength: 10, // default display
// Enable mark.js search term highlighting
mark: true,
dom: 'lBfrtip',
buttons: [{
extend: 'copy',
text: '<span class="hand-pointer" title="Copy to clipboard"><i class="fa fa-clipboard" aria-hidden="true"></i> Copy</span>',
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7]
}
},
{
extend: 'csv',
text: '<span class="hand-pointer" title="Export to CSV file"><i class="fa fa-file-text-o" aria-hidden="true"></i> CSV</span>',
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7]
}
},
{
extend: 'excel',
text: '<span class="hand-pointer" title="Export to Excel file"><i class="fa fa-file-excel-o" aria-hidden="true"></i> Excel</span>',
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7]
}
},
{
extend: 'pdf',
text: '<span class="hand-pointer" title="Export as PDF file"><i class="fa fa-file-pdf-o" aria-hidden="true"></i> PDF</span>',
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7]
}
},
{
extend: 'print',
text: '<span class="hand-pointer" title="Print"><i class="fa fa-print" aria-hidden="true"></i> Print</span>',
exportOptions: {
columns: [1, 2, 3, 4, 5, 6, 7]
}
},
{
text: '<span class="hand-pointer" title="Select all visible rows"><i class="fa fa-check-square-o" aria-hidden="true"></i> Select All</span>',
action: function() {
table.rows().select();
}
},
{
text: '<span class="hand-pointer" title="Deselect all"><i class="fa fa-square-o" aria-hidden="true"></i> Select None</span>',
action: function() {
table.rows().deselect();
}
}
],
select: {
style: 'multi'
},
columnDefs: [{
targets: 8,
render: function(data, type, full, meta) {
if (type === 'display') {
if (userlevel == 0 || userlevel == 1) {
data = '<div class="action-links"><button type="button" class="btn btn-default btn-xs disabled" data-eqid="' + data + '" id="btnUpdate"><i class="fa fa-pencil-square-o" aria-hidden="true"></i> Update</button> <button type="button" class="btn btn-default btn-xs disabled" data-eqid="' + data + '" id="btnDelete" title="Delete"><i class="fa fa-trash" aria-hidden="true"></i></button> <a href="equipment.php?id=' + data + '" class="btn btn-primary btn-xs hand-pointer" data-eqid="' + data + '" title="View details"><i class="fa fa-eye" aria-hidden="true"></i></a></div>';
}
if (userlevel == 2) {
data = '<div class="action-links"><button type="button" class="btn btn-info btn-xs btnUpdate hand-pointer" data-eqid="' + data + '" id="btnUpdate"><i class="fa fa-pencil-square-o" aria-hidden="true"></i> Update</button> <button type="button" class="btn btn-default btn-xs disabled" data-eqid="' + data + '" id="btnDelete" title="Delete"><i class="fa fa-trash" aria-hidden="true"></i></button> <a href="equipment.php?id=' + data + '" class="btn btn-primary btn-xs hand-pointer" data-eqid="' + data + '" title="View details"><i class="fa fa-eye" aria-hidden="true"></i></a></div>';
}
if (userlevel == 3 || userlevel == 4) {
data = '<div class="action-links"><button type="button" class="btn btn-info btn-xs btnUpdate hand-pointer" data-eqid="' + data + '" id="btnUpdate"><i class="fa fa-pencil-square-o" aria-hidden="true"></i> Update</button> <button type="button" class="btn btn-danger btn-xs btnDelete hand-pointer" data-eqid="' + data + '" id="btnDelete" title="Delete"><i class="fa fa-trash" aria-hidden="true"></i></button> <a href="equipment.php?id=' + data + '" class="btn btn-primary btn-xs hand-pointer" data-eqid="' + data + '" title="View details"><i class="fa fa-eye" aria-hidden="true"></i></a></div>';
}
}
return data;
}
}],
"language": {
"search": "<strong>Search / filter records:</strong>",
"lengthMenu": "<strong>Display:</strong> _MENU_ records per page",
"zeroRecords": "Nothing found - sorry!",
"info": "Showing page _PAGE_ of _PAGES_",
"infoEmpty": "No records available",
"infoFiltered": "(filtered from _MAX_ total records)"
}
});
This is how the PHP code looked like:
<?php
// Comments are removed for the sake of brevity
require_once 'config.php';
$table = TABLE_EQUIPMENTS;
$primaryKey = 'id';
$columns = array(
array( 'db' => 'id', 'dt' => 'id'),
array( 'db' => 'model', 'dt' => 'model' ),
array( 'db' => 'description', 'dt' => 'description' ),
array( 'db' => 'project', 'dt' => 'project' ),
array( 'db' => 'serial_number','dt' => 'serial_number' ),
array( 'db' => 'owner', 'dt' => 'owner' ),
array( 'db' => 'current_owner','dt' => 'current_owner' ),
array( 'db' => 'remark', 'dt' => 'remark' )
);
$sql_details = array(
'user' => DB_USER,
'pass' => DB_PASS,
'db' => DB_NAME,
'host' => DB_HOST
);
require( 'ssp.class.php' );
echo json_encode(
SSP::complex( $_POST, $sql_details, $table, $primaryKey, $columns, null, 'deleted=0' )
);
Please note that the PHP code above requires a library class provided by DataTables called ssp.class.php. You can get this class source code from DataTables’s repository on GitHub.