Dr. Rolex Posted February 17, 2015 Share Posted February 17, 2015 Advanced Statistics Rev1 for osCommerce 2.3- Compatible Without mysqlnd - - Support thread - Having made another module for handling products/categories which utilize DataTables, I realized 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 the module has come to a point of being useful so I thought I share it with the rest of the osC community as an Add-On. If you think you have a good idea on new features/stats that could be useful, please share them here on the support forum. DataTables DataTables requests the sales data from your server with Ajax and then builds you a table with a action bar and selectable rows. 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 per month sales & tax data. Performance The former Reporting Add-On I used was very slow, it queried each row in table orders_products and the amount of queries done to calculate sales & tax for a period was massive! Data will be loaded from JSON source which also helps improve Performance, especially on slow connections. 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 1 day is $200 then it will calculate generated sales with 20% tax this way: [Total Tax] / [Tax Rate] * 100 = Sold Goods with 20% Tax200 / 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..). Note also that some of the themes probably needs some CSS styling fixes if you want to print e.g. background colors of header. Make sure to add these to advanced_statistics_print_view.css. 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 up 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… :P 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: 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 the ReadMe file in the package. Theme-creator https://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 from store sales etc..As of Revision 1, you can get a line, area, column, bar, step line, smoothed line, candlestick and OHLC 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 & Compatability 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 native 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 database 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 - https://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 Themes jTable Original Bootstrap jQuery UI jQuery UI Smoothness DataTables Internationalization - 63 Language Definitions Included amCharts Internationalization - 29 Language Definitions Included 100% MySQLi Prepared Statements Bootstrap Tooltips Toggleable Visibility of both your store header and Admin menu using CSS3 classes OS style selection, shift/control/CMD click to select multiple rows - Plenty of screenshots in package! - Quote osC OpenSSL Encryption with jCryptionSupport Forum jQuery/Ajax Advanced Statistics 2.3Support Forum jQuery/Ajax Advanced Order Handler 2.3.3Support ForumjQuery/Ajax Advanced Caching System 2.3.3Support ForumjQuery/Ajax Fast checkout/Shopping Cart/Login/Create Account 2.3.3Support ForumjQuery/Ajax Shopping Cart 2.3.3Support ForumjQuery/Ajax Dynamic Checkout 2.3.3Support ForumjQuery/Ajax Mini Cart for osCommerce 2.3.3Support ForumjQuery Cycle What's New InfoboxAuto Out Of Stock CSS Image OverlayjQuery-UI Autocomplete Product Search with Links & MySQLi support for osCommerce 2.X Link to comment Share on other sites More sharing options...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.