jQuery/Ajax Advanced Statistics for osCommerce 2.3

zeberrun · March 29, 2015

http://addons.oscommerce.com/info/9286 zip file is damaged,
i have /advanced_statistics/general_functions.php on line 47
in order to replace oscommerce 2.3.4 database.php,
your new version is better to this version?

regards

Edited March 29, 2015 by zeberrun

discxpress · April 28, 2015

@@Dr. Rolex I installed this on OSC version 2.3.4 Bootstrap and I will admit my store has +150K products. The only problem I see so far is the current month isn't displayed properly. Instead of displaying April is showing March.

Dr. Rolex · April 28, 2015

http://addons.oscommerce.com/info/9286 zip file is damaged

It's actually a gzipped tar archive, not a zip archive. Try to extract the archive with a different app.

i have /advanced_statistics/general_functions.php on line 47

in order to replace oscommerce 2.3.4 database.php,

your new version is better to this version?

regards

If you already have the tep_db_fetch_all function in database.php, then replace it with the one from general_functions.php. I don't remember what the issue was, but this should solve some database issues that you might run into if you have e.g. the Advanced Order Handler Add-On installed.

It's been a month now so hopefully you solved it yourself. :thumbsup:

Dr. Rolex · April 28, 2015

@@Dr. Rolex I installed this on OSC version 2.3.4 Bootstrap and I will admit my store has +150K products. The only problem I see so far is the current month isn't displayed properly. Instead of displaying April is showing March.

The number of products in your store shouldn't be an issue, performance-wise that is.

Hm, is all statistics always shifted one month? Or does this only happen when you click the green + symbol to get detailed month stats?

Can you see if the number are for the correct month and only the name of the month is wrong?

I found some bugs in the last updated package which I have fixed for my store but haven't had time to upload yet. However, I'm not sure those were related to the problem you're having but I'm sure there's an easy fix for it though.

discxpress · April 28, 2015

The number of products in your store shouldn't be an issue, performance-wise that is.

Hm, is all statistics always shifted one month? Or does this only happen when you click the green + symbol to get detailed month stats?

Can you see if the number are for the correct month and only the name of the month is wrong?

I found some bugs in the last updated package which I have fixed for my store but haven't had time to upload yet. However, I'm not sure those were related to the problem you're having but I'm sure there's an easy fix for it though.

Yes the statistics are shifted one month. It names March twice and omits November all together. The sales numbers are correct. However, it seems the tax is not calculated after the 8th of the month. It shows that I collected tax on the 8th while I know I didn't collect any taxes that day. The taxes shown on the 8th are suppose to be for the 25th of April.

However, if I filter it by day, the correct taxes are shown but the wrong month is shown. Maybe it's because February has only 28 days and skew data for subsequent months.

This add-on is great. And I really appreciate the work you put into it. If that issue is resolved, it would be even greater. I would be more than happy to test your corrections if you allow it.

Edited April 28, 2015 by discxpress

Dr. Rolex · April 28, 2015

Yes the statistics are shifted one month. It names March twice and omits November all together. The sales numbers are correct. However, it seems the tax is not calculated after the 11th of the month. It shows that I collected tax on the 8th while I know I didn't collect any taxes that day. The taxes shown on the 8th are suppose to be for the 25th of April.

However, if I filter it by day, the correct taxes are shown but the wrong month is shown. Maybe it's because February has only 28 days and skew data for subsequent months.

This add-on is great. And I really appreciate the work you put into it. If that issue is resolved, it would be even greater. I would be more than happy to test your corrections if you allow it.

Well, this was indeed very strange..

Could you post the locale content of your admin/includes/languages/[your_language].php. Should look something like this:

setlocale(LC_TIME, 'en_US.ISO_8859-1');
define('DATE_FORMAT_SHORT', '%m/%d/%Y');  // this is used for strftime()
define('DATE_FORMAT_LONG', '%A %d %B, %Y'); // this is used for strftime()
define('DATE_FORMAT', 'm/d/Y'); // this is used for date()
define('PHP_DATE_TIME_FORMAT', 'm/d/Y H:i:s'); // this is used for date()
define('DATE_TIME_FORMAT', DATE_FORMAT_SHORT . ' %H:%M:%S');

Also, could you check if you're getting the same errors after you press the "Clear all filters" button (Red button with recycle icon on top navbar).

discxpress · April 28, 2015

@@Dr. Rolex

setlocale(LC_ALL, array('en_US.UTF-8', 'en_US.UTF8', 'enu_usa'));

define('DATE_FORMAT_SHORT', '%m/%d/%Y'); // this is used for strftime()

define('DATE_FORMAT_LONG', '%A %d %B, %Y'); // this is used for strftime()

define('DATE_FORMAT', 'm/d/Y'); // this is used for date()

define('PHP_DATE_TIME_FORMAT', 'm/d/Y H:i:s'); // this is used for date()

define('DATE_TIME_FORMAT', DATE_FORMAT_SHORT . ' %H:%M:%S');

define('JQUERY_DATEPICKER_I18N_CODE', ''); // leave empty for en_US; see http://jqueryui.com/demos/datepicker/#localization

define('JQUERY_DATEPICKER_FORMAT', 'mm/dd/yy'); // see http://docs.jquery.com/UI/Datepicker/formatDate

Dr. Rolex · April 29, 2015

@@Dr. Rolex

setlocale(LC_ALL, array('en_US.UTF-8', 'en_US.UTF8', 'enu_usa'));

define('DATE_FORMAT_SHORT', '%m/%d/%Y'); // this is used for strftime()

define('DATE_FORMAT_LONG', '%A %d %B, %Y'); // this is used for strftime()

define('DATE_FORMAT', 'm/d/Y'); // this is used for date()

define('PHP_DATE_TIME_FORMAT', 'm/d/Y H:i:s'); // this is used for date()

define('DATE_TIME_FORMAT', DATE_FORMAT_SHORT . ' %H:%M:%S');

define('JQUERY_DATEPICKER_I18N_CODE', ''); // leave empty for en_US; see http://jqueryui.com/demos/datepicker/#localization

define('JQUERY_DATEPICKER_FORMAT', 'mm/dd/yy'); // see http://docs.jquery.com/UI/Datepicker/formatDate

Did you try clicking the red button like I suggested in my previous post? :-

Do you know if you have done any structural changes to your MySQL database compared to a clean version of osC 2.3.4?

Could you check & post your PHP and MySQL version numbers (You'll find them in your admin under 'Tools' => 'Server Info'). Don't post any more than up to the first decimal, i.e. PHP 5.4 and MySQL 5.5. It's probably better to omit the rest for security reasons.

Like I wrote earlier, this is a very strange problem. From what you're telling me it sounds like your server handles dates different from my servers.

To be able to determine if this is the case I need you to temporarily modify your ./admin/advanced_statistics.php and then copy-paste the output your getting and post it here.

This modified code will output the date arrays that the module is using to create the tables, it should zero out any sensitive data but, just to be safe; make sure to check that the total keys on both the displayed arrays are 0 and not your actual sales.

The output should look something like this

string 'Start date: 2015-1-1' (length=20)
string 'End date: 2016-11-1' (length=19)
array (size=19)
  0 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '1' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  1 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '2' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  2 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '3' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '7' (length=1)
      'total' => int 0
  3 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '4' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '7' (length=1)
      'total' => int 0
  4 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '5' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '7' (length=1)
      'total' => int 0
  5 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '6' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  6 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '7' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  7 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '8' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  8 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '9' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  9 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '10' (length=2)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  10 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '11' (length=2)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  11 => 
    array (size=5)
      'year' => string '2015' (length=4)
      'month' => string '12' (length=2)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  12 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '1' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  13 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '2' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  14 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '3' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  15 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '4' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  16 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '5' (length=1)
      'day' => string '1' (length=1)
      'hour' => string '10' (length=2)
      'total' => int 0
  17 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '8' (length=1)
      'day' => string '16' (length=2)
      'hour' => string '10' (length=2)
      'total' => int 0
  18 => 
    array (size=5)
      'year' => string '2016' (length=4)
      'month' => string '11' (length=2)
      'day' => string '16' (length=2)
      'hour' => string '10' (length=2)
      'total' => int 0
array (size=23)
  '2015-01' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '01' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-02' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '02' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-03' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '03' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-04' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '04' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-05' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '05' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-06' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '06' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-07' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '07' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-08' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '08' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-09' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '09' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-10' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '10' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-11' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '11' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2015-12' => 
    array (size=6)
      'year' => string '2015' (length=4)
      'month' => string '12' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-01' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '01' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-02' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '02' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-03' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '03' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-04' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '04' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-05' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '05' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-06' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '06' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-07' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '07' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-08' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '08' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-09' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '09' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-10' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '10' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0
  '2016-11' => 
    array (size=6)
      'year' => string '2016' (length=4)
      'month' => string '11' (length=2)
      'day' => string '01' (length=2)
      'hour' => string '00' (length=2)
      'total' => int 0
      'products_sold' => int 0

Alright, make a backup of your admin/advanced_statistics.php and then modify it with these changes

Find this code

  if ( isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && $_SERVER['HTTP_X_REQUESTED_WITH'] == "XMLHttpRequest" && ! isset( $_GET['new_win'] ) ) { // AJAX Request

Replace With

  /* TESTING - Remove BELOW when DONE
   *********************************************************/
  $_GET = array();
  $_SERVER['HTTP_X_REQUESTED_WITH'] = "XMLHttpRequest";
  $_GET['action'] = "get_data";
  /* TESTING - Remove ABOVE when DONE
   *********************************************************/

  if ( isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && $_SERVER['HTTP_X_REQUESTED_WITH'] == "XMLHttpRequest" && ! isset( $_GET['new_win'] ) ) { // AJAX Request

Find this code

      $shipping_month    = getMonthArray( $start_date, $end_date );

Replace With

      $shipping_month    = getMonthArray( $start_date, $end_date );


      /* TESTING - Remove BELOW when DONE
       *********************************************************/

      // Copy Total Sales array and zero out result except dates
      $temp_total_sales = $total_sales;

      for ( $i=0; $i < count( $temp_total_sales ); $i++ ) {
        $temp_total_sales[$i]['total'] = 0;
      }

      ini_set('html_errors', 1);

      if ( extension_loaded( 'xdebug' ) ) {

        // Dump Start & End Date
        var_dump( "Start date: " . $start_date );
        var_dump( "End date: " . $end_date );

        echo "<hr>";

        // Dump Dates from Total Sales
        var_dump($temp_total_sales);

        echo "<hr>";

        // Dump Constructed Date Array
        var_dump($total_sales_month);
      } else {

        function prettify( $message ) {
          echo "<pre style='background-color: rgb(245, 245, 245);
                  border: 1px solid rgb(204, 204, 204);
                  border-radius: 4px;
                  padding-bottom:8px;
                  max-width:80%;
                  word-wrap:break-word;
                  font-size: 10px;
                  margin: 0 0 10px;
                  padding-left: 9.5px;
                  display: block;
                  font-family: monospace;
                  overflow: auto;
                  color: rgb(204, 0, 0);
                   font-face:monospace;
                '>";
          echo $message;
          echo "</pre>";
        }

        // Dump Start & End Date
        prettify( print_r( "Start date: " . $start_date, true ) );
        prettify( print_r( "End date: " . $end_date, true ) );

        echo "<hr>";

        // Dump Dates from Total Sales
        prettify( print_r( $temp_total_sales, true ) );

        echo "<hr>";

        // Dump Constructed Date Array
        prettify( print_r( $total_sales_month, true ) );
      }


      die;

      /* TESTING - Remove ABOVE when DONE
       *********************************************************/

Go to advanced_statistics.php in your browser, check so that the total keys is 0, copy the output and paste it here.

Edited April 29, 2015 by Dr. Rolex

discxpress · April 30, 2015

1) Yes I did click that red button several times to clear filers.

2) PHP 5.3; mySQL 5.5

3) No structural changes. However, I did upgrade my store from OSCommerce version 2.3.3 to OSCommerce version 2.3.4 Bootstrap.

Dr. Rolex · April 30, 2015

[...]
1) Yes I did click that red button several times to clear filers.

2) PHP 5.3; mySQL 5.5

3) No structural changes. However, I did upgrade my store from OSCommerce version 2.3.3 to OSCommerce version 2.3.4 Bootstrap.

Well, that looks like it's supposed to..

Okay, remove/change back the second block of code but keep the first block you changed in advanced_statistics.php and then do the following modification

    die( json_encode( $json_array ) );

Replace With

    /* TESTING - Remove BELOW when DONE
     *********************************************************/
    if ( extension_loaded( 'xdebug' ) ) {
      var_dump($json_array);
    } else {
      function prettify( $message ) {
        echo "<pre style='background-color: rgb(245, 245, 245);
                border: 1px solid rgb(204, 204, 204);
                border-radius: 4px;
                padding-bottom:8px;
                max-width:80%;
                word-wrap:break-word;
                font-size: 10px;
                margin: 0 0 10px;
                padding-left: 9.5px;
                display: block;
                font-family: monospace;
                overflow: auto;
                color: rgb(204, 0, 0);
                 font-face:monospace;
              '>";
        echo $message;
        echo "</pre>";
      }

      // Dump Start & End Date
      prettify( print_r( $json_array, true ) );
    }
    die;
    /* TESTING - Remove ABOVE when DONE
     *********************************************************/

    die( json_encode( $json_array ) );

Now check if the data is correct, correct tax for tax rates etc.. It should look something like this and the dates should be in order.

array (size=1)
  'data' => 
    array (size=25)
      0 => 
        array (size=12)
          0 => string '' (length=0) // Empty
          1 => string '2015' (length=4)  // Year
          2 => string 'January' (length=7) // Month
          3 => string '01' (length=2) // Month number
          4 => string '$1,132,636.24' (length=13) // Total Sales
          5 => string '$28,030.00' (length=10) // Shipping
          6 => string '$0.00' (length=5)  // Sales excl. tax for Tax rate 1
          7 => string '$0.00' (length=5) // Tax for Tax rate 1
          8 => string '$560,600.00' (length=11)  // Sales excl. tax for Tax rate 2
          9 => string '$112,120.00' (length=11) // Tax for Tax rate 2
          10 => string '$403,632.00' (length=11) // Sales excl. tax for Tax rate 3
          11 => string '$28,254.24' (length=10) // Tax for Tax rate 3 (And so on..)
      1 => 
        array (size=12)
          0 => string '' (length=0)
          1 => string '2015' (length=4)
          2 => string 'February' (length=8)
          3 => string '02' (length=2)
          4 => string '$1,428,165.20' (length=13)
          5 => string '$35,040.00' (length=10)
          6 => string '$9,900.00' (length=9)
          7 => string '$2,970.00' (length=9)
          8 => string '$700,600.00' (length=11)
          9 => string '$140,120.00' (length=11)
          10 => string '$504,504.00' (length=11)
          11 => string '$35,315.28' (length=10)
      2 => 
        array (size=12)
          0 => string '' (length=0)
          1 => string '2015' (length=4)
          2 => string 'March' (length=5)
          3 => string '03' (length=2)
          4 => string '$1,177,893.20' (length=13)
          5 => string '$29,150.00' (length=10)
          6 => string '$0.00' (length=5)
          7 => string '$0.00' (length=5)
          8 => string '$582,900.00' (length=11)
          9 => string '$116,580.00' (length=11)
          10 => string '$419,760.00' (length=11)
          11 => string '$29,383.20' (length=10)

hej

discxpress · May 15, 2015

@@Dr. Rolex

It's been a long and stressful 2 weeks but I'm back on task. I made the changes you suggested and here's the output:

As you can see, the month of March appears twice and the month of November is omitted. Thanks for your help. Edited May 15, 2015 by discxpress

Dr. Rolex · May 15, 2015

@@Dr. Rolex

It's been a long and stressful 2 weeks but I'm back on task. I made the changes you suggested and here's the output:
[ ... ]
As you can see, the month of March appears twice and the month of November is omitted. Thanks for your help.

Yes, this was very strange indeed.

My best guess is that this happens because of some issue with PHP 5.4 not being properly backwards compatible with PHP 5.3.

This is one of the real shitty things about PHP that they change how functions work between different versions.

Can't you upgrade to PHP 5.4?

I'm not sure that's really the problem but these weird problems can many times happen because of PHP 5.3 issues.

discxpress · May 15, 2015

@@Dr. Rolex Thank you for helping to diagnose my issue. I think my host doesn't upgrade PHP upon request.

Dr. Rolex · May 22, 2015

@@Dr. Rolex Thank you for helping to diagnose my issue. I think my host doesn't upgrade PHP upon request.

One more thing you could try, to rule out any problems with SQL, is to run this query on your database (e.g. thorugh phpMyAdmin).

Make sure that the result you get looks OK, as in not getting multiple rows for the same month.

SELECT 
    YEAR(MIN(o.date_purchased)) AS year,
    MONTH(MIN(o.date_purchased)) AS month,
    DAY(MIN(o.date_purchased)) AS day,
    HOUR(MIN(o.date_purchased)) AS hour,
    SUM(ot.value) AS total
FROM
    orders o
        LEFT JOIN
    orders_total ot USING (orders_id)
WHERE
    ot.class = 'ot_total'
GROUP BY YEAR(o.date_purchased) , MONTH(o.date_purchased)
ORDER BY o.date_purchased

discxpress · May 22, 2015

@@Dr. Rolex

Everything checks out just fine. Each month is correct with their respective rows.

Thanks

Dr. Rolex · May 22, 2015

@@Dr. Rolex

Everything checks out just fine. Each month is correct with their respective rows.

Thanks

All right, then at least something works! ;)

Let's try again, we just need to find where in advanced_statistics.php it fails, when e.g. the extra month are added to the array.

Do the following modifications to advanced_statistics.php and then again check each of the three arrays displayed when you visit the page in your browser. Report back if and when any of the three arrays get the double month or something else looks messed up.

First, remove the modifications you made from the previous troubleshooting posts in this thread.

Find this

  require_once 'advanced_statistics/general_functions.php';
  require_once 'advanced_statistics/database_tables.php';

Replace with

  require_once 'advanced_statistics/general_functions.php';
  require_once 'advanced_statistics/database_tables.php';


  /* TESTING - Remove BELOW when DONE
   *********************************************************/
  ini_set('html_errors', 1);

  $_GET = array();
  $_SERVER['HTTP_X_REQUESTED_WITH'] = "XMLHttpRequest";
  $_GET['action'] = "get_data";

  tep_preload_scripts();

  function prettify_message( $message ) {
    echo "<pre style='background-color: rgb(245, 245, 245);
            border: 1px solid rgb(204, 204, 204);
            border-radius: 4px;
            padding-bottom:8px;
            max-width:80%;
            word-wrap:break-word;
            font-size: 10px;
            margin: 0 0 10px;
            padding-left: 9.5px;
            display: block;
            font-family: monospace;
            overflow: auto;
            color: rgb(204, 0, 0);
             font-face:monospace;
          '>";
    echo $message;
    echo "</pre>";
  }

  function prettify( $message, $header = '' ) {
    echo "<div><h3 class='alert alert-info'>$header</h3></div>";
    if ( extension_loaded( 'xdebug' ) ) {
      var_dump($message);
    } else {
      // Dump Start & End Date
      prettify_message( print_r( $message, true ) );
    }
    echo "<hr >";
  }

  /* TESTING - Remove ABOVE when DONE
   *********************************************************/

Find this

    $day  = "";
    $hour = "";
    for ( $i=0; $i < count( $total_sales ); $i++ ) {
      $year = $total_sales[ $i ]['year'];
      $month = str_pad( $total_sales[ $i ]['month'], 2, 0, STR_PAD_LEFT );
      if ( ! empty( $group_by_day ) ) {
        $day = "-" . str_pad( $total_sales[ $i ]['day'], 2, 0, STR_PAD_LEFT );
      }
      if ( ! empty( $group_by_hour ) ) {
        $hour = " " . str_pad( $total_sales[ $i ]['hour'], 2, 0, STR_PAD_LEFT );
      }

      $total_sales_month[ $year . "-" . $month . $day . $hour ]['total'] = $total_sales[ $i ]['total'];
      $total_sales_month[ $year . "-" . $month . $day . $hour ]['products_sold'] = number_format( $total_sales[ $i ]['products_sold'], 0, $default_currency['decimal_point'], $default_currency['thousands_point'] );
    }

Replace with

    /* TESTING - Remove BELOW when DONE
     *********************************************************/
    prettify( $total_sales, '1: $total_sales' );
    prettify( $total_sales_month, '2: $total_sales_month'  );
    /* TESTING - Remove ABOVE when DONE
     *********************************************************/

    $day  = "";
    $hour = "";
    for ( $i=0; $i < count( $total_sales ); $i++ ) {
      $year = $total_sales[ $i ]['year'];
      $month = str_pad( $total_sales[ $i ]['month'], 2, 0, STR_PAD_LEFT );
      if ( ! empty( $group_by_day ) ) {
        $day = "-" . str_pad( $total_sales[ $i ]['day'], 2, 0, STR_PAD_LEFT );
      }
      if ( ! empty( $group_by_hour ) ) {
        $hour = " " . str_pad( $total_sales[ $i ]['hour'], 2, 0, STR_PAD_LEFT );
      }

      $total_sales_month[ $year . "-" . $month . $day . $hour ]['total'] = $total_sales[ $i ]['total'];
      $total_sales_month[ $year . "-" . $month . $day . $hour ]['products_sold'] = number_format( $total_sales[ $i ]['products_sold'], 0, $default_currency['decimal_point'], $default_currency['thousands_point'] );
    }

    /* TESTING - Remove BELOW when DONE
     *********************************************************/
    prettify( $total_sales_month, '3: $total_sales_month'  );
    /* TESTING - Remove ABOVE when DONE
     *********************************************************/

discxpress · May 24, 2015

@@Dr. Rolex

1. $total_sales

2. $total_sales_month

3. $total_sales_month

Thanks. Edited May 24, 2015 by discxpress

Dr. Rolex · May 26, 2015

@@Dr. Rolex

[...]

Thanks.

Okay, looks ok.

Find this

    die( json_encode( $json_array ) );

Replace With

    /* TESTING - Remove BELOW when DONE
     *********************************************************/
    prettify( $json_array, '4: $json_array'  );
    /* TESTING - Remove ABOVE when DONE
     *********************************************************/

    die( json_encode( $json_array ) );

Now, does the last array (4: $json_array) have double months?

discxpress · May 26, 2015

@@Dr. Rolex Thanks for the reply.

As you can see, the month of March appears twice and the month of November is omitted. The stats for those months are there it's just the names are thrown off.

Dr. Rolex · May 27, 2015

@@Dr. Rolex Thanks for the reply.

As you can see, the month of March appears twice and the month of November is omitted. The stats for those months are there it's just the names are thrown off.

Ok, try with this mod and try again. Find this

        $month_name = strftime( "%B", strtotime( $total_sales[ $i ]['year'] . '-' . $total_sales[ $i ]['month'] ) );

Replace With

        //$month_name = strftime( "%B", strtotime( $total_sales[ $i ]['year'] . '-' . $total_sales[ $i ]['month'] ) );
        $datetime = (string)$total_sales[ $i ]['year'] . '-' . $total_sales[ $i ]['month'] . '-01';
        $timestamp = strtotime( $datetime );

        $month_name = date( "F", $timestamp );
        $month_name_gm = gmdate( "F", $timestamp );
        var_dump($total_sales[ $i ]['year'] . '-' . $total_sales[ $i ]['month']);
        echo "<br>";
        var_dump($month_name);
        echo "<br>";
        var_dump($month_name_gm);
        echo "<hr>";

Check if any of the two month names are the correct month (compare to the first numeric date). I.e.:

string '2015-01' (length=7)
string 'January' (length=7) <-- Correct
string 'December' (length=8)

Dr. Rolex · May 27, 2015

@@discxpress

You might as well check so that you have the correct timezone configured, Find this

    $json_array = array();
    $row        = 0;

Replace with

    $json_array = array();
    $row        = 0;
    prettify( date_default_timezone_get(), '0 Timezone: ' );

interbiolab · June 1, 2015

Hi, I'm having a problem.

I'm installing the Advanced Statistics following all steps, but I can run the reports. I don't get any error message after copy and modify the files.

Dr. Rolex · June 2, 2015

Hi, I'm having a problem.

I'm installing the Advanced Statistics following all steps, but I can run the reports. I don't get any error message after copy and modify the files.

Hello interbiolab,

Could you be a little more specific to what your problem is? What happens when you browse to advanced_statistics.php; empty page, no stats even though there should be?

Dr. Rolex · June 3, 2015

@@discxpress

So, did you solve your problem, Mr. Butler?

discxpress · July 14, 2015

@@Dr. Rolex

I apologize for dropping off. Been trying to increase organic traffic to my site. In reply to your post #45, here's the output:

{"data":[["","2013","July","08","$30.82","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2013","August","09","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2013","September","10","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2013","October","11","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2013","December","12","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2013","","$30.82","$30.82","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","January","01","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","February","02","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","March","03","$222.50","$21.90","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","March","04","$89.43","$32.90","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","April","05","$453.27","$53.80","$0.00","$0.00","$0.00","$0.00","$23.54","$0.94"],["","2014","May","06","$125.23","$5.95","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","June","07","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","July","08","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","August","09","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","September","10","$166.88","$40.75","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","October","11","$446.36","$57.60","$0.00","$0.00","$0.00","$0.00","$0.00","$0.00"],["","2014","December","12","$1,958.33","$228.90","$116.41","$8.73","$0.00","$0.00","$27.38","$1.10"],["","2014","","$3,462.01","$3,462.01","$441.80","$116.41","$8.73","$0.00","$0.00","$50.92","$2.04"],["","2015","January","01","$936.46","$210.90","$140.16","$10.51","$72.00","$5.04","$29.62","$1.18"],["","2015","February","02","$1,128.62","$219.20","$18.00","$1.35","$122.75","$8.59","$0.00","$0.00"],["","2015","March","03","$2,383.82","$238.45","$236.29","$17.72","$0.00","$0.00","$242.24","$9.69"],["","2015","March","04","$2,795.28","$489.32","$191.01","$14.33","$239.02","$16.73","$21.99","$0.88"],["","2015","April","05","$6,115.92","$910.93","$193.78","$14.53","$42.81","$3.00","$203.55","$8.14"],["","2015","May","06","$2,979.23","$450.87","$229.11","$17.18","$0.00","$0.00","$53.72","$2.15"],["","2015","June","07","$1,775.47","$167.10","$1,059.59","$79.47","$16.68","$1.17","$0.00","$0.00"],["","2015","","$18,114.80","$18,114.80","$2,686.77","$2,067.95","$155.10","$493.26","$34.53","$551.12","$22.04"]]}

It's still showing March twice, so I'm stumped. I'll try your mod to check the timezone and the results.

Thanks for your help.

jQuery/Ajax Advanced Statistics for osCommerce 2.3

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Ben23

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation