So finally this project has reached it’s end. CHEP PALLETS REPORT (flickerbook.co.uk)
As the last bit, I added a guide to the page on how to properly download Transaction Report. Also included cleaned Excel file that can be downloaded and used as a test to render charts on the website. Finished styling and removing redundant code and unnecessary comments in all scripts. Don’t store any data in database (moved the deletion to end of the script), this means that the database is only used to store data for a moment until all the queries are executed and then it is cleared. The summary of last work done is below:
- Added a user guide how to correctly download “Transaction Report” from CHEP portal.
- Added a link to download a test excel file with transaction report, so it can be used to test the app.
- Moved the deletion of the data in database to the end of the PHP script.
- Clear charts on every reload (render empty charts while loading).
- Created a copy database, where I store sample data to render sample charts.
- Created a PHP file to fetch data from the sample database.
- Added a button to the page, where on click it loads sample charts.
- Fixed Animations
- Finished styling and removed redundant code and unnecessary comments.
The last significant feature I added was the ability to load sample chart. Just thought it would be cool to load it on a click and show how the website works, in case there is no ability to load an excel file. Plus empty charts looks boring and this button can fill them and show that the app works.
In order for this feature to work, I created an additional table and filled it with the same test data I have in a sample excel file. Then copied all the queries from updateDatabase.php file and created a new PHP file and used the copied code to query sample SQL table and send the data to front end for the charts to be rendered.
I also noticed that animations on charts wasn’t working, and after some testing, I found out that this was caused by this line of code:
chart.title.set();As this code resets chart title, right before it is rendered, it interferes with animations, so I just placed it in setTimout() function and added animationDuration property to every chart, this way I ensure, that first the animation is run and only then the title with date is being set. Steps taken to fix animations:
- Added “animationDuration: 2900” property to every chart.
- Set a default title text to every chart. (f.e. text: “Chart title “).
- Placed the chart.title.set() inside setTimeout() function with 3000 delay.
setTimeout(function(){chart.title.set("text", "Monthly pallet transfers by supplier "+startAndEndDate);},3000);And this fix did the trick! 😉
Chart does it’s job!
I’ve uploaded excel file that contained one year worth of transfers and I did spotted one unusual transfer, demonstrated in the picture below.
This transfer is very unusual as it means that approximately 130 pallets were delivered every day in December 2022. That is full 5 lorries a day (26 pallets per trailer). We do not have such a supplier who delivers 5 loads every day 😉 so this transfer looks like an error.
I had a peak in the Excel file, and I can see that all transfers were created on 2022-12-31, which most likely means that supplier wanted to get it’s accounts right before new year. After looking at the “transfers by type” chart, I can see that there is a high number of “Reversed Transfers IN” transfers.
After sorting excel file by “Reversed Transfer IN” column, I found that most of those reversed transfers are done by the same supplier on 2023-01-23.
These reverse transfers are identical to the incorrect transfers done, so it means that this issue was picked up and sorted. In one way this shows that these charts I created are helpful to identify unusual transfers, however if they have been rectified, it does not display it. I had to investigate the excel file with transfers data to figure out this has been rectified. However if this chart was used at 1st of January of 2023 it would have picked up the incorrect transfers and that was the whole purpose of this project.
What I learned
While developing this project I improved:
- CSS Flexbox
- SQL GROUP BY QUERY
- EXCEL charts
- CanvasJS
- Organising data with arrays and objects in PHP and JavaScript.
- WordPress
The most beneficial part for me was practise on using SQL Group By statement. I know have a couple ideas of how I’m gonna use this on Flickerbook, as for a long time I wanted to have data on earnings grouped by employer or by job position, and this will enable me to do it.
Second beneficial thing is that I wrote more then 20 blog posts on my WordPress page and become familiar with using it. I also have content for “Data Analysis”, “Excel” and “Web Projects” sections gained from developing this project.
What I could have done better
After looking back on this project, there are a few things I could have done better like:
- Spend more time investigating data.
- Organise data in a different way at back end.
- Use flex box properly.
- Come up with a way to show rectified transfers.
I’ve mentioned that I should have spent more time investigating the data and CHEP website in mid summary. Another thing I could have done better is organising data on back end for the charts. I organised it in this way:
when actually to render chart 1 I should have done it like this:
To get around it, I had to write additional JavaScript functions were I reorganised data. It would have been better to organise data properly for the chart on back end and just pass a JSON data straight to chart rendering JavaScript function. However this happened because I first decided to organise data on back end, and only then decided what chart library I’ll be using. Since my current code works, I will leave it as it is as I want to move on to doing something else 😉
I now have a few ideas for Flickerbook that I will want to work on. But before that will need to do some work on my shortage report, as I want to add one more feature and fix a few bugs. So I’m happy this CHEP report is finished and working, but I will not spend more time on it trying to improve the code 😉
Github.com repository has been updated.