@jxdking @bytedotio @Dynamic_Gravity @MadMatt @TryTwiceMedia
So I implemented a solution and was curious to hear feedback regarding if there’s ways I could improve or optimize it. It’s HTML/PHP/SQL + Cronjobs.
I’m just going to go in the order of operation. First thing is I made a cronjob. This pulls the latest database.CSV file from the SMB server and puts it in the mysql-files working directory. Then imports the file to the database table.
0 22 * * * /root/sql-log.script
rsync -avhP -e ‘ssh -p 22’ [email protected]:/mnt/images/windows/database.csv /var/lib/mysql-files/
mysql -e “source /root/sql-query.sql”
rm /var/lib/mysql-files/database.csv
I had to put the actual SQL query in an external file because it uses a quotation mark which ruins using mysql -e " " directly.
Contents of sql-query.sql:
load data infile '/var/lib/mysql-files/database.csv' into table computers.windows fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows (DateImaged,SerialNumber,Model,PartNumber,MFRDate,Condition,Processor,Memory,HardDrive,OpticalDrive,FloppyDrive,OperatingSystem,NetworkCard,Controller,Wireless,VideoCard,USB,Firewire,Bluetooth,Battery,Power,Screen,Touchscreen,PageCount,JetDirect,Consumables,PaperTray,Other1,Other2,Other3,DetailCondition,Details,Rebill) set ID=null;
It’s a hideous command but it works. This then imports the CSV file into the table I created:
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| ID | int unsigned | NO | PRI | NULL | auto_increment |
| DateImaged | varchar(16) | YES | | NULL | |
| SerialNumber | varchar(12) | YES | | NULL | |
| Model | varchar(56) | YES | | NULL | |
| PartNumber | varchar(48) | YES | | NULL | |
| MFRDate | varchar(16) | YES | | NULL | |
| Condition | varchar(8) | YES | | NULL | |
| Processor | varchar(96) | YES | | NULL | |
| Memory | int | YES | | NULL | |
| HardDrive | varchar(74) | YES | | NULL | |
| OpticalDrive | varchar(8) | YES | | NULL | |
| FloppyDrive | varchar(8) | YES | | NULL | |
| OperatingSystem | varchar(16) | YES | | NULL | |
| NetworkCard | varchar(42) | YES | | NULL | |
| Controller | varchar(4) | YES | | NULL | |
| Wireless | varchar(36) | YES | | NULL | |
| VideoCard | varchar(96) | YES | | NULL | |
| USB | varchar(16) | YES | | NULL | |
| Firewire | varchar(8) | YES | | NULL | |
| Bluetooth | varchar(3) | YES | | NULL | |
| Battery | varchar(16) | YES | | NULL | |
| Power | varchar(6) | YES | | NULL | |
| Screen | varchar(14) | YES | | NULL | |
| Touchscreen | varchar(3) | YES | | NULL | |
| PageCount | varchar(8) | YES | | NULL | |
| JetDirect | varchar(8) | YES | | NULL | |
| Consumables | varchar(24) | YES | | NULL | |
| PaperTray | varchar(8) | YES | | NULL | |
| Other1 | varchar(42) | YES | | NULL | |
| Other2 | varchar(42) | YES | | NULL | |
| Other3 | varchar(42) | YES | | NULL | |
| DetailCondition | varchar(3) | YES | | NULL | |
| Details | varchar(64) | YES | | NULL | |
| Rebill | varchar(8) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
So with that sorted I have a automated way to upload the data. Now to read the data I used HTML (Apache2) and PHP to just build a small set of web pages from scratch using the previously posted tutorial as my template.
<?php
require "../config.php";
require "../common.php";
if (isset($_GET["ID"])) {
try {
$connection = new PDO($dsn, $username, $password, $options);
$id = $_GET["ID"];
$sql = "DELETE FROM windows WHERE ID = :ID";
$statement = $connection->prepare($sql);
$statement->bindValue(':ID', $id);
$statement->execute();
$success = "Computer successfully deleted";
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
if (isset($_POST['submit']) || isset($_GET['SerialNumber'])) {
try {
$connection = new PDO($dsn, $username, $password, $options);
if (isset($_POST['submit'])) {
$sql = "SELECT * FROM windows WHERE SerialNumber = :SerialNumber";
$SerialNumber = $_POST['SerialNumber']; }
else {
$sql = "SELECT * FROM windows WHERE SerialNumber = " + $_GET['SerialNumber'];
$SerialNumber = $_GET['SerialNumber']; }
$statement = $connection->prepare($sql);
if (isset($_POST['submit'])) { $statement->bindParam(':SerialNumber', $SerialNumber, PDO::PARAM_STR); }
else { $statement->bindParam($_GET['SerialNumber'], $SerialNumber, PDO::PARAM_STR); }
$statement->execute();
$result = $statement->fetchAll();
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php include "templates/header.php"; ?>
<?php if ($success) echo $success; ?>
<?php
if (isset($_POST['submit']) || isset($_GET['SerialNumber'])) {
if ($result && $statement->rowCount() > 0) { ?>
<h2>Results</h2>
<table>
<thead>
<tr>
<th>#</th>
<th>Date Imaged</th>
<th>Serial Number</th>
<th>Model</th>
<th>Part Number</th>
<th>MFR Date</th>
<th>Condition</th>
<th>Processor</th>
<th>Memory</th>
<th>Hard Drive</th>
<th>Optical Drive</th>
<th>Floppy Drive</th>
<th>Operating System</th>
<th>Network Card</th>
<th>Controller</th>
<th>Wireless</th>
<th>Video Card</th>
<th>USB</th>
<th>Firewire</th>
<th>Bluetooth</th>
<th>Battery</th>
<th>Power</th>
<th>Screen</th>
<th>Touchscreen</th>
<th>Page Count</th>
<th>Jet Direct</th>
<th>Consumables</th>
<th>Paper Tray</th>
<th>Other 1</th>
<th>Other 2</th>
<th>Other 3</th>
<th>Detail Condition</th>
<th>Details</th>
<th>Rebill</th>
<th>Update</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<?php foreach ($result as $row) { ?>
<tr>
<td><?php echo escape($row["ID"]); ?></td>
<td><?php echo escape($row["DateImaged"]); ?></td>
<td><?php echo escape($row["SerialNumber"]); ?></td>
<td><?php echo escape($row["Model"]); ?></td>
<td><?php echo escape($row["PartNumber"]); ?></td>
<td><?php echo escape($row["MFRDate"]); ?></td>
<td><?php echo escape($row["Condition"]); ?> </td>
<td><?php echo escape($row["Processor"]); ?> </td>
<td><?php echo escape($row["Memory"]); ?> </td>
<td><?php echo escape($row["HardDrive"]); ?> </td>
<td><?php echo escape($row["OpticalDrive"]); ?> </td>
<td><?php echo escape($row["FloppyDrive"]); ?> </td>
<td><?php echo escape($row["OperatingSystem"]); ?> </td>
<td><?php echo escape($row["NetworkCard"]); ?> </td>
<td><?php echo escape($row["Controller"]); ?> </td>
<td><?php echo escape($row["Wireless"]); ?> </td>
<td><?php echo escape($row["VideoCard"]); ?> </td>
<td><?php echo escape($row["USB"]); ?> </td>
<td><?php echo escape($row["Firewire"]); ?> </td>
<td><?php echo escape($row["Bluetooth"]); ?> </td>
<td><?php echo escape($row["Battery"]); ?> </td>
<td><?php echo escape($row["Power"]); ?> </td>
<td><?php echo escape($row["Screen"]); ?> </td>
<td><?php echo escape($row["Touchscreen"]); ?> </td>
<td><?php echo escape($row["PageCount"]); ?> </td>
<td><?php echo escape($row["JetDirect"]); ?> </td>
<td><?php echo escape($row["Consumables"]); ?> </td>
<td><?php echo escape($row["PaperTray"]); ?> </td>
<td><?php echo escape($row["Other1"]); ?> </td>
<td><?php echo escape($row["Other2"]); ?> </td>
<td><?php echo escape($row["Other3"]); ?> </td>
<td><?php echo escape($row["DetailCondition"]); ?> </td>
<td><?php echo escape($row["Details"]); ?> </td>
<td><?php echo escape($row["Rebill"]); ?> </td>
<td><a href="update-single.php?ID=<?php echo escape($row["ID"]); ?>">Edit</a></td>
<td><a href="read.php?ID=<?php echo escape($row["ID"]); ?>&SerialNumber=<?php echo escape($row["SerialNumber"]); ?>">Delete</a></td>
</tr>
<?php } ?>
</tbody>
</table>
<?php } else { ?>
> No results found for <?php echo escape($_POST['SerialNumber']); ?>.
<?php }
} ?>
<h2>Find computer based on serial number</h2>
<form method="post">
<label for="SerialNumber">Serial Number</label>
<input type="text" id="SerialNumber" name="SerialNumber">
<input type="submit" name="submit" value="View Results">
</form>
<a href="index.php">Back to home</a>
<?php include "templates/footer.php"; ?>
So HTML talks to PHP which talks to SQL and lets me run lookup queries on the uploaded .CSV files.
Right now I’m more worried about working proof-of-concept so there’s virtually nothing to the webpage but the data.
Going off the right of the screen is the option to let me edit and delete the entry. Delete only half works correctly right now but it’s a work in progress…
Today I did a bunch of reading up on .CSS and stylesheets so I was able to fix the layout just a little bit and also made my own dark mode.
If I want to view/edit the entry It lets me look at the data vertically.
<?php
/**
* Use an HTML form to edit an entry in the
* users table.
*
*/
require "../config.php";
require "../common.php";
if (isset($_POST['submit'])) {
try {
$connection = new PDO($dsn, $username, $password, $options);
$computer =[
"ID" => $_POST['ID'],
"DateImaged" => $_POST['DateImaged'],
"SerialNumber" => $_POST['SerialNumber'],
"Model" => $_POST['Model'],
"PartNumber" => $_POST['PartNumber'],
"MFRDate" => $_POST['MFRDate'],
"Condition" => $_POST['Condition'],
"Processor" => $_POST['Processor'],
"Memory" => $_POST['Memory'],
"HardDrive" => $_POST['HardDrive'],
"OpticalDrive" => $_POST['OpticalDrive'],
"FloppyDrive" => $_POST['FloppyDrive'],
"OperatingSystem" => $_POST['OperatingSystem'],
"NetworkCard" => $_POST['NetworkCard'],
"Controller" => $_POST['Controller'],
"Wireless" => $_POST['Wireless'],
"VideoCard" => $_POST['VideoCard'],
"USB" => $_POST['USB'],
"Firewire" => $_POST['Firewire'],
"Bluetooth" => $_POST['Bluetooth'],
"Battery" => $_POST['Battery'],
"Power" => $_POST['Power'],
"Screen" => $_POST['Screen'],
"Touchscreen" => $_POST['Touchscreen'],
"PageCount" => $_POST['PageCount'],
"JetDirect" => $_POST['JetDirect'],
"Consumables" => $_POST['Consumables'],
"PaperTray" => $_POST['PaperTray'],
"Other1" => $_POST['Other1'],
"Other2" => $_POST['Other2'],
"Other3" => $_POST['Other3'],
"DetailCondition" => $_POST['DetailCondition'],
"Details" => $_POST['Details'],
"Rebill" => $_POST['Rebill'],
];
$sql = "UPDATE computers.windows
SET ID = :ID,
DateImaged = :DateImaged,
SerialNumber = :SerialNumber,
Model = :Model,
PartNumber = :PartNumber,
MFRDate = :MFRDate,
`Condition` = :Condition,
Processor = :Processor,
Memory = :Memory,
HardDrive = :HardDrive,
OpticalDrive = :OpticalDrive,
FloppyDrive = :FloppyDrive,
OperatingSystem = :OperatingSystem,
NetworkCard = :NetworkCard,
Controller = :Controller,
Wireless = :Wireless,
VideoCard = :VideoCard,
USB = :USB,
Firewire = :Firewire,
Bluetooth = :Bluetooth,
Battery = :Battery,
Power = :Power,
Screen = :Screen,
Touchscreen = :Touchscreen,
PageCount = :PageCount,
JetDirect = :JetDirect,
Consumables = :Consumables,
PaperTray = :PaperTray,
Other1 = :Other1,
Other2 = :Other2,
Other3 = :Other3,
DetailCondition = :DetailCondition,
Details = :Details,
Rebill = :Rebill
WHERE ID = :ID";
$statement = $connection->prepare($sql);
$statement->execute($computer);
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
if (isset($_GET['ID'])) {
try {
$connection = new PDO($dsn, $username, $password, $options);
$id = $_GET['ID'];
$sql = "SELECT * FROM windows WHERE ID = :ID";
$statement = $connection->prepare($sql);
$statement->bindValue(':ID', $id);
$statement->execute();
$computer = $statement->fetch(PDO::FETCH_ASSOC);
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
} else {
echo "Something went wrong!";
exit;
}
?>
<?php require "templates/header.php"; ?>
<?php if (isset($_POST['submit']) && $statement) : ?>
<?php echo escape($_POST['Model']); ?> successfully updated.
<?php endif; ?>
<h2>Edit Computer</h2>
<form method="post">
<?php foreach ($computer as $key => $value) : ?>
<input type="text" name="<?php echo $key; ?>" id="<?php echo $key; ?>" size="27" value="<?php echo escape($value); ?>" <?php echo ($key === 'id' ? 'readonly' : null); ?><label for="<?php echo $key; ?>"> - <?php echo ucfirst($key); ?></label><br>
<?php endforeach; ?>
<br>
<input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
So with this data imports automatically, and I can Read/Update/Delete data just by going to this self-hosted website.
Just yesterday I imported our full back-log of computers which amounted to a little over 10,000 machines for the year so far. We will probably be close to 20,000 computers logged by years end.
Thoughts? Anything I should tweak? Anything obvious I should have done better?
Thank you.
