I have made a recent update to this application where I had removed the user authentication module, instead I hosted the application using IIS and get the Windows logged in username. The intention is to make the application simpler to use where the user can directly add the record, no login or registration required.
A couple of weeks ago I was asked to gather and compile a proper list of equipment that have been borrowed by any engineer who is working with my department. However, the list that I received is recorded in a MS Excel file. So, I have thought that instead of waiting any of the engineers to send me the updated list file whenever I need to check the status of the equipment, I should create a simple web application hosted in my company network for them to easily update the equipment records instantly, and they can do that at anywhere and anytime. Finally, I decided to spend some hours during my weekend to develop that kind of application.
The idea is finally realized! I called it “Equipment Tracking” or in short is “Etrac”. Etrac is built from scratch using DataTables, jQuery, PHP, MySQL and Bootstrap with Google-style theme. Etrac is featured with a very simple user authentication module that I created before to use in old PHP projects. Now, with this application set up on intranet webserver, whenever I need to get the latest and updated list of the borrowed equipment, I can just visit the application URL, export/print the list immediately. Etrac also supports exporting to PDF, Excel and CSV file format. Below are some screenshots showing how Etrac looked like.
Equipment Tracking (Etrac) v1.0 Screenshots
Figure 1 (above): List of equipment displayed using DataTables jQuery plugin without user login.
Figure 2 (above): Login page.
Figure 3 (above): List of equipment with logged user. If the user has the right permission given, the user can click CRUD buttons such as “Add new record”, “Batch delete…” or “Update” button.
Figure 4 (above): Adding new record via Modal dialog.
Figure 5 (above): When user clicks on delete button, a confirmation will pop up to double-confirm before the delete action can be performed.
Figure 6 (above): Search or filter can be performed easily when using this DataTables jQuery plugin.
Figure 7 (above): Super simple user profile settings page.
Figure 8 (above): List of registered users.
Etrac v1.0 DataTables Code Snippets
This is the customized version of DataTable JavaScript 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)"
}
});
Below is the PHP code to work with DataTable for server-side processing:
<?php
// Global config
require_once 'config.php';
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
// DB table to use
$table = TABLE_EQUIPMENTS;
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case object
// parameter names
$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 server connection information
$sql_details = array(
'user' => DB_USER,
'pass' => DB_PASS,
'db' => DB_NAME,
'host' => DB_HOST
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'ssp.class.php' );
echo json_encode(
SSP::complex( $_POST, $sql_details, $table, $primaryKey, $columns, null, 'deleted=0' )
);
NOTE: ssp.class.php is available on DataTables’ repo on GitHub here.