Had some spare time during the last couple days so started to code CHEP pallets report website. Things I’ve done so far are:
- Set up a domain name on my server for it.
- Created HTML, CSS, JavaScript, PHP files for it.
- Set up database.
- Added additional column to database on my server, as I intend to group pallet quantities by supplier and month they were delivered.
- Created a github repository for this project.
I’m now able to upload the excel file on this website and extract the selected columns from it and upload it to database. I’ll go in to some details on how this has been achieved.
HTML/CSS
After setting up domain, I coded simple website which currently only contains two parts: one part is where a user can upload an excel file and the other is were a chart will be displayed. I won’t work on the chart now, as first I will need to sort out a way to aggregate the data required for it at back end. So the most important part of HTML/CSS at present is the upload form:
<form enctype="multipart/form-data" class="topBottom5px">
<label for="uploadButton">Upload Transaction Report</label>
<input id="uploadButton" type=file name="files[]">
</form>JavaScript
In order to be able to extract the data from excel file through JavaScript I used these scripts, I don’t know who wrote them, but they are extremely useful for my project, so I’ll give credit where credit is due! 😉
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/jszip.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.js"></script>Basically with the help of these scripts all excel datasheet columns are stored in an object:
this.parseExcel = function(file) {
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
var workbook = XLSX.read(data, {
type: 'binary'
});
workbook.SheetNames.forEach(function(sheetName) {
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
var json_object = JSON.stringify(XL_row_object);
let parsedObject = JSON.parse(json_object);
let size = Object.keys(parsedObject).length;If I console.log(parsedObject), it is clear that all the columns and their values from excel file are stored in this object.
Then I’m able to access values in this object, for example if I want to get the first value in the “Transaction Type” column I can do it like this:
parsedObject[0]["Transaction Type"]So knowing that I can access every value in this object by it’s index and column name, I use a loop to extract the values from the columns I want, and store them in separate arrays. I also do some sanitizing to prevent symbols that might cause the SQL query or PHP explode() function not to work as intended. So the crucial part of JavaScript looks like this:
//I must make sure each time the previuos values that were stored in these arrays are deleted.
transactionTypeAray = []; //Transaction Type
otherPartyArray = [] //Other Party ---> suppliers name
quantityArray = []; //Quantity
shipmentDateArray = []; //Shipment Date
for (let i = 0; i < size; i++) {
//since in back end I use explode function and a comma as a seperator, I must make sure there are no comas left in any of the strings
//hence will replace all comas with empty space;
//remove commas from all rows
//this is only neccesasry for suppliers name, but just in case I remove it from all values.
parsedObject[i]["Transaction Type"] = parsedObject[i]["Transaction Type"].replace(/,/g, "");
parsedObject[i]["Other Party"] = parsedObject[i]["Other Party"].replace(/,/g, "");
parsedObject[i]["Quantity"] = parsedObject[i]["Quantity"].replace(/,/g, "");
parsedObject[i]["Shipment Date"] = parsedObject[i]["Shipment Date"].replace(/,/g, "");
//remove '&' from strings as in php it stops the explode function.
//this is only neccesasry for suppliers name, but just in case I remove it from all values.
parsedObject[i]["Other Party"] = parsedObject[i]["Other Party"].replace(/&/g, "");
parsedObject[i]["Transaction Type"] = parsedObject[i]["Transaction Type"].replace(/&/g, "");
parsedObject[i]["Quantity"] = parsedObject[i]["Quantity"].replace(/&/g, "");
parsedObject[i]["Shipment Date"] = parsedObject[i]["Shipment Date"].replace(/&/g, "");
//replace ' with '' as it acts an sql injection
//this is only neccesasry for suppliers name, but just in case I remove it from all values.
parsedObject[i]["Other Party"] = parsedObject[i]["Other Party"].replace(/'/g, "''");
parsedObject[i]["Transaction Type"] = parsedObject[i]["Transaction Type"].replace(/'/g, "''");
parsedObject[i]["Quantity"] = parsedObject[i]["Quantity"].replace(/'/g, "''");
parsedObject[i]["Shipment Date"] = parsedObject[i]["Shipment Date"].replace(/'/g, "''");
//add values to arrays
transactionTypeAray.push(parsedObject[i]["Transaction Type"]);
otherPartyArray.push(parsedObject[i]["Other Party"]);
quantityArray.push(parsedObject[i]["Quantity"]);
shipmentDateArray.push(parsedObject[i]["Shipment Date"]);
} The for loop iterates by the length of the parsedObject (let size = Object.keys(parsedObject).length; ). After the loop I store the array’s in a string and send them to back end via AJAX.
//fetch all arrays and add them to the string that will be sent over to back end.
let str ='transactionTypeAray='+transactionTypeAray+'&'+'otherPartyArray='+otherPartyArray+'&';
str+='quantityArray='+quantityArray+'&'+'shipmentDateArray='+shipmentDateArray+'&';
sendValuesToServer(str);PHP/SQL
On back end I use PHP explode() function to convert a string that has been sent into a PHP array for each column. Also at the beginning of the script I check if the arrays sent to back end are not empty. If they are, an error message will be sent back to front end.
$errorTrue = 0;
if(!empty($_POST['transactionTypeAray'])){
$transactionTypeAray = explode(",", $_POST['transactionTypeAray']);
}else{
$errorTrue++;
}
if(!empty($_POST['otherPartyArray'])){
$otherPartyArray = explode(",", $_POST['otherPartyArray']);
}else{
$errorTrue++;
}
if(!empty($_POST['quantityArray'])){
$quantityArray = explode(",", $_POST['quantityArray']);
}else{
$errorTrue++;
}
if(!empty($_POST['shipmentDateArray'])){
$shipmentDateArray = explode(",", $_POST['shipmentDateArray']);
}else{
$errorTrue++;
}After the arrays are created the last step is to insert the values into database. I do that again with a for loop.
//Delete values from the current table
//this needs to be done to avoid duplicate data
$queryDelete = "DELETE FROM ChepReport WHERE 1 = 1";
$resultDelete = mysqli_query($DBConnection, $queryDelete);
//find the length of array;
$arrayLength = Count($transactionTypeAray );
//Insert data into database by using for loop.
for ($i = 0; $i <$arrayLength; $i++){
//I want to extract year and month from date as I intend to group results by month
$splitShipmentDateArray= explode("-", $shipmentDateArray[$i]);
//compose proper date
$yearMonthDate = $splitShipmentDateArray[0].''.$splitShipmentDateArray[1];
$queryInsertData2 = "INSERT INTO ChepReport (
transactionType,
otherParty,
quantity,
shipmentDate,
yearMonthDate)
VALUES (
'$transactionTypeAray[$i]',
'$otherPartyArray[$i]',
'$quantityArray[$i]',
'$shipmentDateArray[$i]',
'$yearMonthDate'
)";
$resultInsertdata2 = mysqli_query($DBConnection, $queryInsertData2);
}Conclusion
So the first part of this project is done and I think this was the easiest one, as I had worked with similar code on my Shortage Report, so I had a lot of code already done for it, just had to arrange it to suit it. Now a harder part awaits 🙂
All code for this project can be found at https://github.com/kachiuz/chep-report