Advanced Statistics Rev2 for osCommerce 2.3
Copyright (C) 2015 Jonas firstname.lastname@example.org
This file is part of Advanced Statistics Rev2.
Advanced Statistics Rev2 is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
Advanced Statistics Rev2 is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with Advanced Statistics Rev2. If not, see http://www.gnu.org/licenses/
Notes by Dr. Rolex
Hello and welcome!
Having made another module for handling products/categories which also utilize DataTables, I came up with the idea that my store needed a new reporting "engine" to produce statistics/sales data for me so that I don't have to make a new spreadsheet every time I want some statistics.
As of Revision 1 there's still a lot to work on but the Add-On has come to a point of being useful so I thought I share it with the rest of the osC community.
If you think you have a good idea on new features/stats that could be useful, please share them on the support forum.
You can Drag & Drop Column Headers to reorder the table the way you like it. It’s also possible to toggle column visibility.
Clicking on the columns will sort the column ascending/descending.
Clicking on the leftmost plus (+) sign will expand the column to detailed day-by-day sales data.
Doing so will get you another DataTable with new buttons.
The former Reporting Add-On I used was very slow, the amount of queries done to calculate sales for a period was massive!
Data will be loaded from JSON source which also helps improve Performance, especially on slow connections.
Of course there’re situations when you want to calculate sales that way but as long as your taxes/sales add up I don’t see the point.
Make very sure if you are going to use this Add-On that it calculates your data correctly! You have to both double and triple check the reports with another reporting system so you know for sure that everything works.
This is how the Advanced Statistics Add-On calculates your sales data, it’s quite simple:
If you have a tax rate set to 20% and the summary for this tax for let’s say 1 day is $200 then it will calculate generated sales this way:
[Total Tax] / [Tax Rate] * 100 = Sold Goods with 20% Tax
200 / 20 * 100 = 1000. So Sales 20% will be 1000$.
As of Rev1, order_totals classes that will be counted are ot_total, ot_tax & ot_shipping.
The TableTools Extension comes with a swf (flash) file which helps e.g. to copy table data to clipboard. Unfortunately, flash is the only way to do this right now..
Copying Data and then Pasting it in your spreadsheet editor is a neat way to quickly create custom reports.
TableTools also comes with buttons to automatically create CSV files you might need to configure the CSV field separator in advanced_statistics.js. Search for the sFieldSeperator option and set correct separator if you can’t get the CSV to import correctly.
Using a CSS with media="print”, a print view is automatically created when you select to print the page. If you have selected to filter orders by status, then there will be an added heading with Status Information, as of Rev1 however, you need to reload the page to get it to display. I’ll fix this later..
You can also use the TableTools ”Print” Button which essentially makes the same thing.
You can also have TableTools create a PDF for you, but it looks like sh*t if you ask me.
Check DataTables homepage on how to configure the layout for this PDF, I will not work on this as I don’t need it (will use CMD/CTRL + P instead..).
Menus & Navigation
I always wanted one of those Slide-In menus so I finally got one; jQuery mmenu. It’s still rather empty, but should fill upp with more filtering options and other knobs, buttons & of course whistles soon enough.
To open it; click on the top left icon on the top navigation, it will randomly pick one out of 37 icons on each load but shouldn’t be hard to find as long as you’re not completely ”challenged” by the world around you…
There’s a couple of more buttons in the Bootstrap top navigation which will toggle the admin menu and header and also a button to clear all currently applied filters.
To the right on the top navigation you’ll have two dropdowns which will switch theme and filter by orders status.
jQuery DataTables comes with some options to implement themes, so I fixed support for five different layouts:
jTable - Looks a bit like the Advanced Order Handler and uses Bootstrap buttons for the seven leftmost buttons.
Original - DataTables as it comes out of the box (more or less)
Bootstrap - Like Original but with the DataTables Bootstrap css & js
jQuery UI - Will adapt to your jQuery-UI Admin Theme (default is Redmond)
jQuery UI Smoothness - Uses Smoothness theme instead of Redmond
Screenshots of all Themes can be found at the bottom of this page.
Both amCharts and DataTables comes with many language definitions which is included and should automatically load the same language as the one you have set in your admin (assuming the definition exist).
I plan to make a lot of use of amCharts to get easy to interpret charts out of store sales etc..
As of Revision 1, you can get a line graph for each of the visible columns and for either the specific rows that you have selected (OS style selection, shift/control/CMD click to select multiple rows) or for the entire table(s).
The chart modal is movable (only one move per page load) and also resizable.
Click on the Graph Names at the bottom of the modal to hide/show different line graphs.
Security & Compatibility
All querying with parameters/input variables to your database will be made with MySQLi Prepared Statements. Preferably you should use the MySQL Native Driver (mysqlnd), but I have added support and tested the Add-On without mysqlnd and everything should work.
Prepared Statements removes the threat of MySQL Injections. Not that it matters that much on the admin side of the shop since only authorized users have access, but you can never be too secure, right? ☺
For simple installation I have not messed with more osC files than necessary and instead included necessary functions in general_functions.php.
If you have any of my previous Add-Ons installed then you might run into a problem under certain conditions when the mysqli_prepared_query and tep_db_fetch_all functions will fail.
If so, then try with replacing the current functions that you have in ./admin/includes/functions/database.php with the ones that you find in general_functions.php from this Add-on.
Features for Revision 1
Sales/Tax Statistics/Report Generator for osC 2.3.x
Only tested on osC 2.3.4 but should work on any version
Simple five step Installation - Only three files needs to be modified!
Automatically creates HTML table and loads orders from server using AJAX.
Export Options: CSV, Copy/Paste, XLS, PDF
"Automatic" Print View
amCharts Graphs over sales & taxes
Very Fast Operation, a shop with 30 000 orders loads in under 2 seconds.
jQuery DataTables - http://datatables.net/
Drag & Drop Columns
Automatic Column Sorting
Toggle Column Visibility
Detailed day-to-day sales
Slide-In Menu with mmenu - http://mmenu.frebsite.nl/
Fixed Bootstrap Top Navigation
jQuery UI Smoothness
DataTables Internationalization - 63 Language Definitions Included
amCharts Internationalization - 29 Language Definitions Included
100% MySQLi Prepared Statements
Toggleable Visibility of both your store header and Admin menu using CSS3 classes
OS style selection, shift/control/CMD click to select multiple rows
New Features and changes in Revision 2
Fixed so it's possible to display more than one table at a time
Two levels of child tables - Months => Days => Hours
jQuery DateRange Picker
New Filter - Categories - Filter Statistics from selected categories (multiple selections possible)
New Filter - Products - Filter Statistics from selected product(s) (multiple selections possible)
New Filter - Customers - Filter Statistics from selected customer(s) (multiple selections possible)
New Filter - Custom Filter - Filter anything you want from any column in the 'orders' table ( E.g. Name, Company, City, Postcode, State, Country, Telephone, Payment Method, Currency.. )
Group by week
New Column - Display total # of products sold
Tabletools - Export to CSV/XLS Settings
AmCharts - Fixed charts with Pan/Zoom
AmCharts - Export to PNG/JPG/SVG
AmCharts - ChartCursor
AmCharts - Separate Axis for Products Sold
Settings - Your settings will now be saved with a Cookie and in the localStorage object aStat. So everytime you get back to the Advanced Statistics page your previosly selected filters and options will be selected.