Sales report 2.0

[BlindSide]

Please excuse my ignorance if this has already been discussed... but one feature of the Sales Reports that would absolutely be phenomenal would be the option to show 'attributes'.

For example. I run a store that sells apparel items. Often I want to know what is my best selling sizes. I'd love to be able to select a group of item, and see what the best selling size is for those items (i.e. mens tshirts).

The Sales Report is designed to exactly that, report sales. Reporting based on attributes of a given product is completely different, logically speaking. Without getting into the nitty gritty, what you're looking for would require a complete rewrite to logic at work in building the report.

Also, one thing that always bugged me was there is no 'forever' option on the sales reports. I can pull a sales overall report by day, week, month, and year... but there is no 'forever' option to show all sales reported for the entire time I started running my store up to today. That would be incredibly useful. Especially given the option above... say I wanted to see how many XL's I've sold since I started the store.

Can someone clue me in? Is this possible? Or can it be?

That's a deliberate decision. The Sales Report queries are massive. If you don't specify a range you are very likely to hang and possibly crash your database. Unless things have changed since I last touched the code, there's actually a max date range limit hardcoded into it, to avoid screwing yourself. :)

[BEatMaKeR]

The Sales Report is designed to exactly that, report sales. Reporting based on attributes of a given product is completely different, logically speaking. Without getting into the nitty gritty, what you're looking for would require a complete rewrite to logic at work in building the report.

Gotcha. I understand.

Not sure if anyone knows of another Report add-on to Zencart that might allow for this type of search and reporting function?

[apsona]

Please excuse my ignorance if this has already been discussed... but one feature of the Sales Reports that would absolutely be phenomenal would be the option to show 'attributes'.

For example. I run a store that sells apparel items. Often I want to know what is my best selling sizes. I'd love to be able to select a group of item, and see what the best selling size is for those items (i.e. mens tshirts).

Also, one thing that always bugged me was there is no 'forever' option on the sales reports. I can pull a sales overall report by day, week, month, and year... but there is no 'forever' option to show all sales reported for the entire time I started running my store up to today. That would be incredibly useful. Especially given the option above... say I wanted to see how many XL's I've sold since I started the store.

Can someone clue me in? Is this possible? Or can it be?

You might try Apsona ShopAdmin, which includes a reporting facility for creating arbitrary ad-hoc reports. There is a tutorial on the website describing how to create specifically the sales-by-attributes report you mention.

Apsona
Twitter: http://twitter.com/apsona

[BEatMaKeR]

Dumb question... but on the general.ja file the additional lines for the roll over... can you tell me what this does exactly? I added them along with the updates but have not noticed any change? I was assuming this was to roll over the listings in case you wanted to go a level deeper in the reports to make it easier to see as you scroll over the listings?

[BEatMaKeR]

apsona GREAT LOOKING ADD-ON!!! THANKS!!! I have been looking for something like this for a looong time!

[rweeks]

Frank - thanks for a great add on.
It is time to pay sales tax to the state, so it would be nice to have an option to report only sales that were local and have sales tax.

These are my modifications to do this. If there is a more elegant method, let us know.

In the language file stats_sales_report.php ~line 207 - added:

// checkboxes
define('CHECKBOX_SALES_TAX', 'Only Orders with Sales Tax');

In Classes file sales_report.php near top of file added variable $sales_tax:

  class sales_report {
    var $timeframe_group, $sd, $ed, $sd_raw, $ed_raw, $date_target, $date_status;
    var $payment_method, $current_status, $manufacturer, $sales_tax, $detail_level, $output_format;
    var $timeframe, $timeframe_id, $current_date, $product_filter;

    function sales_report($timeframe, $sd, $ed, $date_target, $date_status, $payment_method, $current_status, $manufacturer, $sales_tax, $detail_level, $output_format, $order_total_validation) {
      global $db;

      // place passed variables into class variables
      $this->timeframe_group = $timeframe;
      $this->date_target = $date_target;
      $this->date_status = $date_status;
      $this->payment_method = $payment_method;
      $this->current_status = $current_status;
      $this->manufacturer = $manufacturer;
      $this->sales_tax = $sales_tax;     
      $this->detail_level = $detail_level;
      $this->output_format = $output_format;
      $this->order_total_validation = $order_total_validation;

In Classes file sales_report.php near line 165 modify SQL query to include extra if else block. Else statement contains original query.

// build the SQL query of order numbers within the current timeframe
      if ($this->sales_tax =="true"){                 // if else statement added to sort only items that have sales tax. 7/16/10
                      $sql = "SELECT DISTINCT o.orders_id from " . TABLE_ORDERS . " o \n";
                      if ($this->date_target == 'status') {
                        $sql .= "LEFT JOIN " . TABLE_ORDERS_STATUS_HISTORY . " osh ON o.orders_id = osh.orders_id \n";
                        $sql .= "WHERE osh.date_added >= '" . date("Y-m-d H:i:s", $sd) . "' AND osh.date_added < '" . date("Y-m-d H:i:s", $ed) . "' \n";
                        $sql .= "AND order_tax != '$0.00' \n";
                        $sql .= "AND osh.orders_status_id = '" . $this->date_status . "' \n";
                      }
                      else {
                        $sql .= "WHERE o.date_purchased >= '" . date("Y-m-d H:i:s", $sd) . "' AND o.date_purchased < '" . date("Y-m-d H:i:s", $ed) . "' \n";
                        $sql .= "AND order_tax != '$0.00' \n";
                      }
                      if ($this->payment_method) $sql .= "AND o.payment_module_code LIKE '" . $this->payment_method . "' \n";
                      if ($this->current_status) $sql .= "AND o.orders_status = '" . $this->current_status . "' \n";
                      $sql .= "ORDER BY o.orders_id DESC";
      }  
      else {
      // start of original SQL query.
      $sql = "SELECT DISTINCT o.orders_id from " . TABLE_ORDERS . " o \n";
      if ($this->date_target == 'status') {
        $sql .= "LEFT JOIN " . TABLE_ORDERS_STATUS_HISTORY . " osh ON o.orders_id = osh.orders_id \n";
        $sql .= "WHERE osh.date_added >= '" . date("Y-m-d H:i:s", $sd) . "' AND osh.date_added < '" . date("Y-m-d H:i:s", $ed) . "' \n";
        $sql .= "AND osh.orders_status_id = '" . $this->date_status . "' \n";
      }
      else {
        $sql .= "WHERE o.date_purchased >= '" . date("Y-m-d H:i:s", $sd) . "' AND o.date_purchased < '" . date("Y-m-d H:i:s", $ed) . "' \n";
      }
      if ($this->payment_method) $sql .= "AND o.payment_module_code LIKE '" . $this->payment_method . "' \n";
      if ($this->current_status) $sql .= "AND o.orders_status = '" . $this->current_status . "' \n";
      $sql .= "ORDER BY o.orders_id DESC";
       // end of orgiginal SQL query

Modified stats_sales_report.php to add check box to top section of page

Add around line 174

$sales_tax= ($_GET['sales_tax'] != '' ? $_GET['sales_tax'] : false);

Pass new variable to function:

      $sr = new sales_report($timeframe,                  //* determines how sales tallies are grouped
                             $start_date, $end_date,      //* the date range
                             $date_target, $date_status,  //* what date field to search, and the status (if needed)
                             $payment_method,             //  payment method used for desired orders
                             $current_status,             //  currently assigned status to the order
                             $manufacturer,               //  only include orders with assigned manufacturer
                             $sales_tax,        //Added to find items with sales tax
                             $detail_level,               //* what information to output
                             $output_format,              //* how to display the results
                             $order_total_validation);

Check Box added above middle radio buttons around line 449

<td class="smallText">
              <input type="checkbox"  name="sales_tax" value="true"> <?php echo CHECKBOX_SALES_TAX; ?> <br /> 
              <input type="radio" name="date_target" value="purchased" onClick="hide('td_date_status', true)"><?php echo RADIO_DATE_TARGET_PURCHASED; ?><br />
              <input type="radio" name="date_target" value="status" onClick="show('td_date_status')"><?php echo RADIO_DATE_TARGET_STATUS; ?>            </td>

I think that was all the changes. Seems to work fine.

[BlindSide]

Glancing over your code, I don't see any glaring issues. Thanks for sharing your mod!

[Rony]

I don't know why its not working for me. I installed it without any problems. But when I click the link from Reports > Sales Report it redirect me to Control Panel Login Page. Not sure what is going on..! I am using ZenCart version 1.3.9e

[Rony]

I don't know why its not working for me. I installed it without any problems. But when I click the link from Reports > Sales Report it redirect me to Control Panel Login Page. Not sure what is going on..! I am using ZenCart version 1.3.9e

I have found the bug. It doesn't work in Chrome. When I tried it in Firefox it worked.

[carlvt88]

I use chrome with no problem. You might need to dump your internet cache.