Advanced Statistics Rev2 for osCommerce 2.3.4

- Compatible Without mysqlnd -

LICENSE

Advanced Statistics Rev2 for osCommerce 2.3
Copyright (C) 2015 Jonas jonas@jholmster.com

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/

NEWS - Rev2

Advanced Statistics Rev2

If you're upgrading from Rev1, then replace your files with the ones in the new_files directory and do Step #5 from the installation instructions and you're good to go.
Note that you need to have flash installed and activated for the Export to Clipboard, CSV, XLS and PDF buttons to display.

Lot's of new filters in this revision and also more stats..

New features:

NEWS - Rev1

Advanced Statistics Rev1

Please Post Questions on the Forum Support thread (link below) instead of sending PM.

So.. Having used a modified version of the Monthly Sales & Tax Report Add-On for a couple of years I figured that it was time to make a more modern sales reporting Add-On.

Unlike Monthly Sales & Tax Report which calculate sales out of every product in table orders_products this Add-On will query the database and calculate sales based on each orders_total class which greatly increases query time. Example: with a database of 30 000 example orders, this Add-On will finish in just 1.5 seconds.

As usual though, if you need any information, then ask whatever it is you want to know on the forum support thread.

DataTables Table plug-in for jQuery

This Add-On utilizes DataTables Table plug-in for jQuery to display it's statistics. Here is a list of some of the features:

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.

DataTables

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.

Performance

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.

Important!

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.

TableTools

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.

Print View

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.

Theming

jQuery DataTables comes with some options to implement themes, so I fixed support for five different layouts:

  1. jTable - Looks a bit like the Advanced Order Handler and uses Bootstrap buttons for the seven leftmost buttons.
  2. Original - DataTables as it comes out of the box (more or less)
  3. Bootstrap - Like Original but with the DataTables Bootstrap css & js
  4. jQuery UI - Will adapt to your jQuery-UI Admin Theme (default is Redmond)
  5. jQuery UI Smoothness - Uses Smoothness theme instead of Redmond

Screenshots of all Themes can be found at the bottom of this page.

Theme-creator
http://www.datatables.net/manual/styling/theme-creator

Internationalization

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).

amCharts

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

New Features and changes in Revision 2


Forum Support Link
  • Before Posting any Problems you might have, check your PHP Error log for messages and attach them to your post.
  • Also make sure to check your browser console for error messages and include them as well. To open Chrome Console press:
    Mac: Cmd + Opt + J
    Windows: Ctrl + Shift + J

http://forums.oscommerce.com/topic/401423-jqueryajax-advanced-statistics-for-oscommerce-23/

Installation Instructions [Show/Hide]

Step #1 - Backup files

Note: If you have an unmodified store -OR- know that the files listed below are unmodified on your store, then you can replace your files with the ones included with this Add-On. If so, copy the files located in 'catalog' directory to your shop directory.
Make a backup of the files that are going to be changed:

Step #2 - Copy new files

Copy the files located in 'new_files' directory to your shop directory:

Step #3 - in ./admin/includes/boxes/reports.php

Find the following code around line 26

      array(
        'code' => FILENAME_STATS_CUSTOMERS,
        'title' => BOX_REPORTS_ORDERS_TOTAL,
        'link' => tep_href_link(FILENAME_STATS_CUSTOMERS)
      )
Replace with: 
      array(
        'code' => FILENAME_STATS_CUSTOMERS,
        'title' => BOX_REPORTS_ORDERS_TOTAL,
        'link' => tep_href_link(FILENAME_STATS_CUSTOMERS)
      ),
      array(
        'code' => FILENAME_ADVANCED_STATISTICS,
        'title' => BOX_REPORTS_ADVANCED_STATISTICS,
        'link' => tep_href_link(FILENAME_ADVANCED_STATISTICS)
      ),

Step #4 - in ./admin/includes/filenames.php

Find the following code around line 14

  define('FILENAME_ACTION_RECORDER', 'action_recorder.php');
Replace with: 
  define('FILENAME_ACTION_RECORDER', 'action_recorder.php');
  define('FILENAME_ADVANCED_STATISTICS', 'advanced_statistics.php');

Step #5 - in ./admin/includes/functions/html_output.php

Find the following code around line 241

  function tep_draw_selection_field($name, $type, $value = '', $checked = false, $compare = '') {
Replace with: 
  function tep_draw_selection_field($name, $type, $value = '', $checked = false, $compare = '', $parameters = '') {

Find the following code around line 249

      $selection .= ' checked="checked"';
    }
Replace with: 
      $selection .= ' checked="checked"';
    }

    if (tep_not_null($parameters)) $selection .= ' ' . $parameters;

Find the following code around line 353

      if ($default == $values[$i]['id']) {
Replace with: 
      // Fix for Arrays
      if ($default == $values[$i]['id'] || (is_array($default) && in_array($values[$i]['id'], $default))) {

Step #6 - in ./admin/includes/languages/english.php

Find the following code around line 97

define('BOX_HEADING_REPORTS', 'Reports');
Replace with: 
define('BOX_HEADING_REPORTS', 'Reports');
define('BOX_REPORTS_ADVANCED_STATISTICS', 'Advanced Statistics');

Screenshots [Show/Hide]

Main view with example data for 2015. (All Screenshots are from Rev1, I'm too lazy to take new ones..)

amCharts line graph over 2015 data.

amCharts line graph over daily data per month.

Child row with detailed data per month.

"Automatic" Print View with CSS

Select Specific Rows to produce a amChart from them.

One-Click-Copy with a Flash plugin. Easy way to copy to spreadsheet.

Enable/Disable Display of columns.

Top Navigation - Click the leftmost icon to toggle Left Slide-In Menu.

On Rev1, you only get a Filter for date range.

Themes!

Themes - jTable

Themes - Original

Themes - Bootstrap

Themes - jQuery UI

Themes - jQuery UI Smoothness