Sales report 2.0

Quote:

---

Originally Posted by solosolera

I really like this report but I have found what I believe is another problem with this report.

Besides the “Goods Value” being the (order value – taxes) the report TOTAL is missing the taxes so there seems to be a tax issue in this report. Also the taxes that are subtracted from the “goods value” are taxes before any discount (I have a group discount).

As it stands now the report’s GOODS VALUE = (order subtotal – taxes (before discount)) and the TOTAL = (order subtotal – discount + shipping) no taxes.

I believe that the GOODS VALUE should be the orders sub-total only and the reports TOTAL should be the orders total (order subtotal – discount + taxes (after discount) + shipping).

Again maybe I’m misinterpreting how this report works so here is an example...

ORDER:

Sub-Total: $69.95

Group Discount: -$3.50

FL State and Local Tax (6.5%): $4.32

United Parcel Service (1 x 5.00lbs) (2nd Day Air): $11.93

Total: $82.70

This report shows:

Goods Value = 65.40 (should be 69.95)

Total = 78.38 (should be 82.70)

After several days of trying to figure out how ZC and this report work (I have very little clue) I made some changes to get that results I think the report should give me. Can anyone that knows how this report works and has PHP experience tell me if the changes I made are correct or if they will adversely affect the report in a manner that I’m not aware.

Changes to: admin\includes\classes\sales_report.php

I CHANGED :

Code:

---

$product_price_exc_tax = ($product_price_inc_tax - $product_tax);

---

TO:

Code:

---

$product_price_exc_tax = ($product_price_inc_tax);

---

I’m obviously screwing with some rules here by changing “$product_price_exc_tax” to equal “$product_price_inc_tax“ but the value used to get the “GOODS VALUE” seems to be "product_price_exc_tax”. So please advice on the consequences of this change.

AND BELOW:

// (goods + tax + shipping + gc_sold) - (discount + gc_used)

I ADDED:

Code:

---

$order_goods_tax = zen_calculate_tax(($order_goods - ($order_discount + $order_gc_used)), $tax) ;

---

To get the taxes after any discount.

Now the report gives me what I believe are the correct values for my scenario:

GOODS VALUE = 69.95

TOTAL = 82.70

Can anyone knowledgeable please let me know what they think or if maybe there is some tax setting in ZC that I’m not setting thus getting the results I was getting.


Thanks…

---

I just installed SALES REPORT Version 2.3.2. I have the same problem with Goods Value subtracting sales tax. Isn't this a basic bug that can be fixed?

I am curious if your changes are working properly and if you ever received feedback?

I appreciate your help.

Does the latest version of Sales Report, play nicely with Store Credit and will it display the figures correctly if a refund is made with zen vs 138a. Thanks Regards Renz

I really like this mod. Can someone help me filter by shipping method? Also can someone help me get the report to display the tax amount for each order?

Thank you!

I want to clarify my last post a little.
When I asked to filter by shipping method what I meant was at the top in Gather and Filter data you can filter by payment method or payment method to omit. I would like the exact same thing but instead of payment have shipping method.

Another thing is as I was completing my paperwork for the night I noticed that the sales do not add up. As an example order #98. He was charge for 2 items at $9.00 each. He picked them up at the store so no shipping cost. Fl sales tax is 7%. His emailed invoice was for $19.26. $9X2=$18 $18x1.07=$19.26 so emailed invoice was correct. However the sales report shows goods value $16.74, goods tax $1.26, order rec. tax $1.26, Shipping $0.00, discounts $0.00, Order total $18.00. This happened on several orders. Also I only have one tax which is Florida sales tax. So why do I see a goods tax and an order rec. tax? Shouldn't there just be one? Why are some of the amounts different on the same category?

I am running version 1.5. I have added the following modules tip_module, SalesTaxReportv2.1, edit_orders_v4.0, discount_1.11, and of course SalesReport310.

Thank you in advance for your help in understanding this.

Shoot. I am really sorry. I wanted to include the .csv file so you could see what I am looking at and then forgot to attach it.
Attachment 11318

Is there any support for this?

Sounds like the questions you're asking are really feature requests in terms of changing or enhancing the data being reported on.
Any adjustments you're looking for will probably require having a programmer look into the code and the data in order to deliver the adjusted output you seek.

As you say, the data in the store is correct, but the report may not be understanding the complexities of the way your mods work together or wasn't designed to report things the way you're looking for. And adding filtering by another set of factors requires someone rewriting the queries and adding the form controls to the page, etc.

@bseller, @southshorepizza -

Rather than changing the math in the salesreport, try the configuration option:

Configuration -> My Store -> Display Prices with Tax in Admin

I don't use taxes, but I modeled your situation and it seemed that in the function zen_add_tax in general.php, the tax wasn't getting added without this configuration option set.

Excellent module! I have a few questions which I hope aren't repeats. I'm running the latest version on ZC 1.5.1, and also have Super Orders installed if it makes a difference.

1. We have two statuses which we regularly report on, shipped orders and confirmed orders (where payment has been received but the item hasn't yet shipped). With Sales Report I can see how to report on both individually, but being able to run a report with both status types included would be ideal. Is there an easy trick to get this set up?
2. Relatedly, is it possible to add an extra column in the Product/Order reports to show the order status, to allow us to run comparisons without downloading and merging separate reports or being confused if 1. is possible?
3. Is is possible to have a report which gives both products ordered and order references together? There would be some logistical issues with the display of totals where orders contain multiple line items, but it would still be helpful for my sales team.

I suppose my real question is how best to go about adding some custom reports of my own in order to avoid breaking anything. Any guidance or documentation pointers would be great, then I can start experimenting...

Can't edit my last post now, but I've solved most of my requirements by installing the separate Export Shipping Information mod which covers #3 in my list and gives me the best of both modules to play with. The only remaining query I have is whether it would be possible to run Sales Report on two order statuses at a time (at the moment it's a dropdown, so I can't). It's certainly not a critical feature, but I figured it might be handy to know there's demand if it was ever considered.

Thank you for making such a solid module!

I'm sure Sales Report must be a great mod and I would like to use it, but, unfortunately, nothing happens when I click the button "Show me the money"... The page remains blank.

Any clue of what I might have done wrong?

ZC 1.5.1 through Firefox

I'll mention that I have only a few recent orders, since I'm still testing the site, in case it has any influence...

Thanks

Quote:

---

Originally Posted by postig

I'm sure Sales Report must be a great mod and I would like to use it, but, unfortunately, nothing happens when I click the button "Show me the money"... The page remains blank.

Any clue of what I might have done wrong?

ZC 1.5.1 through Firefox

I'll mention that I have only a few recent orders, since I'm still testing the site, in case it has any influence...

Thanks

---

-------------------------

Problem solved. I knew I had done something wrong. Just a missing file. Thanks for this great mod!

Can anyone tell me the difference between the order received tax and the Goods Tax? Below is an example where i dont see how order received tax is 2.75?

Timeframe	Num Orders	Num Products		Goods Value	Goods Tax	Order Rec. Tax	Shipping	Discounts		Gift Cert. Sold		Gift Cert. Used		Total
2-10-2012	13	13	| Diff: 3	$317.60	$2.40	$2.75	$25.75	$20.00	| Qty: 1	$0.00	| Qty: 0	$0.00	| Qty: 0	$325.75
Prod ID Product Name https://www.robbell.com/work/admin61...s/up_arrow.gif 2 Model No. https://www.robbell.com/work/admin61...s/up_arrow.gif1 Base Price Qty. Tax Total Product Total 16 prod 1 $10.00 2 $0.00 $20.00 $20.00 9 prod 2 $20.00 7 $0.00 $140.00 $140.00 35 prod 3 $40.00 4 $2.40 $160.00 $162.40

Goods tax is the tax on full face value or before any discount. Order Receipts Tax is the tax collected after discounts are applied. If you have an item for $20 and discount it 50%, with a sales tax rate of 1% the Goods tax is $0.20 the order rec tax is $0.10.

Got it working, is there any way to make it show Address Shipped to? Or billed to?
Thanks

Every month I use the Sales Report to generate sales on specific products using the feature ' Only include specific Product IDs (comma separated list) '. I paste in different lists of the products to generate the report. It has been working great until my Hosting Service recently upgraded our servers and PHP version to 5.3.2, and MYSQL version 5.1.65. Now when I run the report, I am limited to what looks like 150 csv values???

If I paste in less than 150, the report generates.

I get the following error in /admin/stats_sales_report.php when I paste in more than 150 csv values:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
in:
[select distinct pd.products_name from zen_products_description pd where products_id = ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

Here is the code in question:

PHP Code:

---

<?php if ($_GET['doCustInc']== 'on' || $_GET['doProdInc']== 'on') {
          // if reporting for a specific product, build up a string of product descriptions
        $i = 0;
        $header_string = "";
        $include_products = explode(",",$_GET['prod_includes']);
        if ($_GET['doProdInc']== 'on' && DISPLAY_TABLE_HEADING_PRODUCTS) {
            foreach($include_products as $cID) {
                $tempAry = $db->Execute("select distinct pd.products_name from " .
                            TABLE_PRODUCTS_DESCRIPTION . " pd where products_id = " . trim($cID));
                if ($i == 0) {
                    $header_string = $tempAry->fields['products_name'] ;
                }
                else
                {
                    $header_string .= ", " . $tempAry->fields['products_name'];
                }
                $i++;
            }
        }

---

Anybody else have this problem and/or have a clue how to fix it?

PS. I also upgraded to the latest version and get the same error.

deleted

I would also like to be able to exclude orders from the Sales Report based on the current order status, however...

the change shown below doesn't accomplish that. Instead, the drop-down field used to select the Current Order Status no longer contains the orders_status_id of 5, but the report still includes orders with a current orders_status_id of 5. This is not what I want at all! :wacko:

Quote:

---

Originally Posted by wolfderby

If you'd like to remove "cancelled" orders from the sales report data (marked "cancelled" through a manually created order status, under admin > localization > order status > "insert")...

Go into your stats_sales_report.php file (in your admin folder) and around line 49 you'll find this line of code

Code:

---

    $orders_status = $db->Execute("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . "
                                  where language_id = '" . (int)$_SESSION['languages_id'] . "'
                                  order by orders_status_id asc");

---

you'll want to change it to:

Code:

---

$orders_status = $db->Execute("SELECT orders_status_id, orders_status_name FROM " . TABLE_ORDERS_STATUS . "
                                  WHERE language_id = '" . (int)$_SESSION['languages_id'] . "' AND NOT (orders_status_id = 5)
                                  ORDER BY orders_status_id asc");

---

---

Here is what I need ...

1. I'd like to be able to exclude any order that has a current orders_status_name that contains a string like %test%, %unused%, %voided%, or %RMA%.
2. Ideally, I'd like to have a new text field I can type into, kind of like the field called: Only include specific Customer IDs (comma separated list), only this new one would be called: Exclude current orders_status_names that contain.
3. In this new field, I would enter the following: test, unused, void, rma.
4. Also, I would like to be able to setup a default for it so I don't have to type those in all the time.
5. And I would like to be able to specify if my strings are case-sensitive or not.

Anybody have any idea about how to do this? Any/all help/suggestions would be graciously appreciated.

BTW: here are the specs of my cart:

• www.KeylessEntryLocks.com
• Zen Cart 1.5.1
• Sales Report - Version: 3.1.0
• Linux 2.6.32-279.19.1.el6.x86_64
• PHP Version 5.3.21
• Database Patch Level: 1.5.1
• anthing else you need, ju

Quote:

---

Originally Posted by ibuttons

I would also like to be able to exclude orders from the Sales Report based on the current order status, however...

the change shown below doesn't accomplish that. Instead, the drop-down field used to select the Current Order Status no longer contains the orders_status_id of 5, but the report still includes orders with a current orders_status_id of 5. This is not what I want at all! :wacko:



Here is what I need ...

1. I'd like to be able to exclude any order that has a current orders_status_name that contains a string like %test%, %unused%, %voided%, or %RMA%.
2. Ideally, I'd like to have a new text field I can type into, kind of like the field called: Only include specific Customer IDs (comma separated list), only this new one would be called: Exclude current orders_status_names that contain.
3. In this new field, I would enter the following: test, unused, void, rma.
4. Also, I would like to be able to setup a default for it so I don't have to type those in all the time.
5. And I would like to be able to specify if my strings are case-sensitive or not.

Anybody have any idea about how to do this? Any/all help/suggestions would be graciously appreciated.

BTW: here are the specs of my cart:

• www.KeylessEntryLocks.com
• Zen Cart 1.5.1
• Sales Report - Version: 3.1.0
• Linux 2.6.32-279.19.1.el6.x86_64
• PHP Version 5.3.21
• Database Patch Level: 1.5.1
• anthing else you need, ju

---

This is a pretty extensive customization, and I'm not sure you're gonna find anyone that is gonna post this kind of solutution here on the forum. You may have to consider hiring someone to code these modifications for you..

Trying to get the delivery country to display, I have managed to change the header in the languages version of the stats_sales_report but, can't seem to get the sql to return the actual country the sql seems to be pulling the infor from several different tables and not really sure where to interveen.

really need help with this issue.

I am having problems with this add-on. I made sure to download the v1.3.9h, as that is the version of ZenCart we are using, but I cannot get it to work! I unzipped the file and copied everything over as instructed... The problem is that when I pull the Sales Report in the Admin it is still the same old report from before the add-on, without the new filters and options. Is there something I'm missing? I'm VERY new to ZC so I have no idea where to look but HAVE tried to search the web and forums fir a possible solution...

The Sales Report will add a new item under your Report Menu called Sales Report. The default Zen Cart does not have such a menu item.

If you see Sales Report (and the admin url will end with /stats_sales_report.php), then it's installed correctly.

Hi,

I have installed the module and found it under the report tab in the admin area but when I click on it it gives me a 404 page not found error?

404 Page Not Found

Sorry! The page you requested is unavailable. Please use your browser's Back button, or go to the home page.

Is there something I have missed in the install?

Thanks

Quote:

---

Originally Posted by Tinderbox

Hi,

I have installed the module and found it under the report tab in the admin area but when I click on it it gives me a 404 page not found error?

404 Page Not Found

Sorry! The page you requested is unavailable. Please use your browser's Back button, or go to the home page.

Is there something I have missed in the install?

Thanks

---

I would suggest you open the zip then manually upload each and every file, making absolutely sure you put it in the right place.

sometimes a file will corrupt on upload. you might have one of these.

make sure you upload the loose file that sits inside the main admin folder too

I have installed the module and it looks fine. The output is almost perfect for me!

The one thing missing (for me) is a separation of the different taxes based on the orders_total title! This is because I have two areas, so I need separate totals for both. Do I stand a chance?

The CVS format somehow messes up - I can't get to be proper CommaSeparatedValues!

HELP! I used custom time and lost many values - tax values among others! How can I get them back?

I am having some issues. I need to include the following information:

Order ID, Product Name, Quantity, Price Sold for, Additional Fees, Total, Additional Discounts, Grand Total.

Currently, I have it showing all the above HOWEVER, if there were two orders the same day for the same product, it only shows 1 order number but adds both the orders together. I need it to show each and every product and order number... Suggestions? I have been trying to figure this out now for about 2 weeks to no avail.

Thanks.

I can clearly see there is no support for this plugin.

If I were to want the report to show at the top of the page instead of under the Gather & Filter data table, can someone provide the code I need to move.

I thought it was

Code:

---

if ($timeframe_sort == 'desc') {
      krsort($sr->timeframe);
    }

    // determine whether or not there are taxes
    $display_tax =  ($sr->grand_total['goods_tax'] > 0 ? true : false);
    // DEBUG
    //$display_tax = true;

    // determine if lines should have the rollover effect
    $rollover = "";
    if ($output_format == 'display') {
      $rollover = 'onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)"';
?>
  <tr>
      <?php if ($_GET['doCustInc']== 'on' || $_GET['doProdInc']== 'on') {
          // if reporting for a specific product, build up a string of product descriptions
        $i = 0;
        $header_string = "";
        $include_products = explode(",",$_GET['prod_includes']);
        if ($_GET['doProdInc']== 'on' && DISPLAY_TABLE_HEADING_PRODUCTS) {
            foreach($include_products as $cID) {
                $tempAry = $db->Execute("select distinct pd.products_name from " .
                            TABLE_PRODUCTS_DESCRIPTION . " pd where products_id = " . trim($cID));
                if ($i == 0) {
                    $header_string = $tempAry->fields['products_name'] ;
                }........

---

To the top by the </head> but the <tr> without a preceding <table> tells me I must have this wrong.
Thanks for your help!

Try this - in admin/stats_sales_report.php, move the table that draws the header to after the table that draws the data. In abstract, this file:
Calculates stuff
Prints out the header
Prints out the data

Take this (around line 325 - 582 - page numbers might not be exact based on revision etc, but should be close)

Code:

---

****** start here *******
  // display the print header
  if ($output_format == 'print') {
    if ($auto_print) {
      echo '<body onload="print();">';
    }
?>
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <!-- PRINT HEADER -->
.
.
.
  <tr>
    <td align="left"><?php echo zen_draw_separator('pixel_trans.gif', 1, 15); ?></td>
    <td align="right" valign="top" id="td_wait_text" class="alert" style="font-size:12px; visibility:hidden"><?php echo SEARCH_WAIT_TEXT; ?>&nbsp;&nbsp;</td>
  </tr>
<?php
  }  // END <?php if ( (!$output_format || $output_format = 'display') && $output_format != 'print')
****** cut here ********

---

And move it after around line 1140

Code:

---

.
.
.
  <tr>
    <td valign="bottom" class="smallText"><?php printf(TEXT_PARSE_TIME, number_format($parse_time, 5) ); ?></td>
    <td><?php echo zen_draw_separator('pixel_trans.gif', 1, 20); ?></td>
  </tr>
<?php
    }
  }  // END if ($output_format == 'print' || $output_format == 'display')
****** insert here *******

---

Exactly what I was trying to say and do but in a much easier and understandable way.

Thank you!

I keep getting syntax errors. Can someone please post the correct code for this?

Thank you!

I can't without knowing the exact version you're using, any mods made, etc. My own version is modified, so it might not work with the other files that make up the addon.

If you PM me that file, I could edit and return but probably couldn't test it. Or, let me know what syntax errors you're having.

One of the things I was watching for is whether you're inside a <php block or not at the points where you cut and paste. If this is mismatched, you'll have something like:

Code:

---

**php code**
<?php  ** start of php block

?>

---

I sent the pm over to you I think. When I check my sent box it is empty though. :( If you didn't get it the file is zipped at http://www.southshorepizza.net/stats...report.php.zip

I only made one mode which was a meta refresh in the html header. I'm running Zen 1.5.1 with sales report 3.1.

thank you for your help!!

Got it - From your pm it looks like a misalignment of <?php blocks. I PM'd a reply

Goods value doesn't seem to add up correctly. I assume that goods value = all products full retail price added together. However order 18755 for example has $13.49+$6.50+$1.65+$7.99 for a subtotal of $29.63 before discounts. Then the emailed receipt has shipping of $2.50, discount of $1.00, Sales Tax of $2.19 and Total of $36.32. However Sales report has Goods Value of $27.55, Tax of $2.19, Shipping of $2.50, Discount of $1.00, Order total of $31.13. I don't understand how mathematically we go from $27.55-$1.00+$2.50=$31.13 My calculator always tells me that equals $29.05. What doesn't get calculated into Goods Value in a product and how do I correct that?

Hi There,

can anyone help with my Store Credit, plug in issue....and sales report 2. It would appear that any store credits, are not recorded by the report.

Any assistance would be appreciated.

Thanks
Regards
Renz

@southshorepizza, First, my math doesn't agree anyway - where you said $36.32, I calculated $33.32.

I suspect there's a discrepancy in the tax - I have a non-profit so I don't deal with tax, so I'm not totally clear here. Also, the $2.08 missing from the goods_total is suspiciously close to the tax value.

It looks like SR both calculates its own tax, and also uses the tax from the orders table. I'd probably need to walk through step by step to see what's calculated during the order process, how it's stored in the database, and how those values are extracted by SR and used in the output.

Another suggestion would be to make some test orders with values like $10, $100, $1000 - to see how different values are calculated and used. Best if you have an offline system that you can play with.

@renz, I'm not familiar with the store credit plugin. If you're looking to show accumulated credits on the report, that'd be a programming task. It might be tough, as the report is order oriented, not customer oriented.

If you want to see credits when they're applied to orders - this should work if the store-credit plugin is utilizing gift-certificates or coupons. I've used both of these and the outputs are there. If it uses another mechanism, it'd be another programming task but I think this would be easier since coupons are already supported.

@carlvt88 $36.32 included a $3.00 tip. But that isn't an issue as this happens with or without the tip being involved.

I guess what I'm not following is in order confirmation it looks as though includes/classes/order.php that saves the order and generates the emailed receipt saves everything to table_order_totals But the SR does not use that instead it uses table_orders as shown in admin/includes/classes/sales_report.php. It looks at each ordered product and then recalculates the subtotals. However it appears to be missing something.

Thanks for this module; it has helped me with my reporting needs quite a bit and saved me from running SQL against the db. Here's a bug report and a request:

v1.5 original (no upgrade)
When exporting the report as a CSV, it appears to drop the "Goods Tax" column header. (I just need to add one space to the right in the header row to bring it in-line as a workaround.) I tested it on Chrome and IE and it seems to be consistent. I searched this thread to see if anyone else has reported it and didn't find anything, so sorry if this is a known issue.

Also it would be nice to have a way to omit specific order statuses, such as Cancelled from being returned, similar to the way you can choose to omit a payment method.

Regards,

Aaron

I don't get any canceled orders returned. For some reason all deleted or orders with a $0.00 total are skipped.

Also this report does not match the emailed receipt on a consistent basis. Usually the report is off $15-20 for every 30 orders.

Anyone having issues with the "Last Year" option displaying THIS YEAR (2014) and NOT 2013??

Quote:

---

Originally Posted by DivaVocals

Anyone having issues with the "Last Year" option displaying THIS YEAR (2014) and NOT 2013??

---

I can confirm, I'm seeing "THIS YEAR (2014)" with Sales Report version 3.1.0 under Zen Cart 1.5.1.

Quote:

---

Originally Posted by lhungil

I can confirm, I'm seeing "THIS YEAR (2014)" with Sales Report version 3.1.0 under Zen Cart 1.5.1.

---

Well I'm stumped on a fix.. (tried a few things.. none worked) **sigh**

Found the error - needs day and date in the mktime command:
stats_sales_report.php line 532:

Code:

---

sprintf(SEARCH_DATE_LAST_YEAR, date("Y", mktime(0,0,0,1,1,date("y")-1)));

---

Quote:

---

Originally Posted by carlvt88

Found the error - needs day and date in the mktime command:
stats_sales_report.php line 532:

Code:

---

sprintf(SEARCH_DATE_LAST_YEAR, date("Y", mktime(0,0,0,1,1,date("y")-1)));

---

---

I found this code in line 457 not 532.. Made the change and it made no difference..

Code:

---

              <td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date("Y", mktime(0,0,0,1,1,date("y")-1))); ?></td>

---

@aalthouse - I can see where the tax column is messed up. In the function output_csv, if there is any tax, the variable $display_tax is set to true. Now, when the columns are printed out an extra header is included:

Code:

---

            if ($display_tax) echo TABLE_HEADING_TAX . CSV_SEPARATOR;

---

but two extra data are written:

Code:

---

              if ($display_tax) {
                echo $o_data['goods_tax'] . CSV_SEPARATOR;
                echo $o_data['order_recorded_tax'] . CSV_SEPARATOR;
              }

---

Just need to add both elements to the header:

Code:

---

            if ($display_tax) {
                echo TABLE_HEADING_TAX . CSV_SEPARATOR;
                echo TABLE_HEADING_ORDER_RECORDED_TAX . CSV_SEPARATOR;
            }

---

Slightly different for the type of report detail (timeframe, order, or product), but the same concept holds for all.

While I was looking at it, I found another problem in the csv data - if you're looking at 'timeframe' level of detail, the taxes aren't written to the csv file. In sales_report.php line 992, add 'echo' to these lines:

Code:

---

            if ($display_tax) {
              echo $timeframe['total']['goods_tax'] . CSV_SEPARATOR;
              echo $timeframe['total']['order_recorded_tax'] . CSV_SEPARATOR;
            }

---

Quote:

---

Originally Posted by DivaVocals

I found this code in line 457 not 532.. Made the change and it made no difference..

Code:

---

              <td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date("Y", mktime(0,0,0,1,1,date("y")-1))); ?></td>

---

---

Try changing that line to read

Code:

---

              <td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date('Y', strtotime('-1 year'))); ?></td>

---

Quote:

---

Originally Posted by lat9

Try changing that line to read

Code:

---

              <td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date('Y', strtotime('-1 year'))); ?></td>

---

---

Still no dice and this is a variation of the code I eluded to in my earlier post that I tried and failed..

Here's what worked for me:

Code:

---

<td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date("Y") - 1 ); ?></td>

---

Quote:

---

Originally Posted by lat9

Here's what worked for me:

Code:

---

<td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date("Y") - 1 ); ?></td>

---

---

Yahtzee!! This did the trick!! You are the best!

Thanks, Diva.:blush: Sometimes the simplest solutions are the best!

Quote:

---

Originally Posted by lat9

Thanks, Diva.:blush: Sometimes the simplest solutions are the best!

---

Yep!! I agree!

RE: Last year (2014)

Worked! Thank you so much!

Quote:

---

Originally Posted by lat9

Here's what worked for me:

Code:

---

<td class="smallText" id="td_last_year"><?php echo zen_draw_radio_field('date_preset', 'last_year', false) . sprintf(SEARCH_DATE_LAST_YEAR, date("Y") - 1 ); ?></td>

---

---

My pleasure ...

I have a feature request for the next iteration of this great module. If there could be a function to select attributes it would make this sooooo much better. Perhaps an option to show all attribute variations and sales and an ability to select a certain product to see it's attribute sales. I think a lot of people could use and really benefit from this. If it would take a contribution for the amount of time and effort involved please PM me to discuss, I am more than happy to do this.

I'm using 1.3.9f and I'd like to add a total cost column to this report and also a profit column, but I can't figure out where to edit the php... I already added a product cost field to my products, so I have that data. Any guidance would be appreciated. Thanks!

So I'm pretty sure the math is done in includes/admin/classes/sales_report.php to answer my own question.

Does anyone know my states sales tax causes a "DON'T MATCH!" with the Output Order Total Validation Column checked? Aside from maybe the obvious of the order total not including the sales tax... like DON'T MATCH! 5.99 : 6.41 (We have 7% sales tax in PA)

I'm not following you exactly but I think you are saying that your totals don't match correctly. If so you have the same problem I had. I ended up changing the table orders over to the table orders_total to pul the report. This made everything line up perfectly. The issue I saw was that this report recalculated every order. I have a bunch of attributes on every order and was off several dollars every day. By switching this to pull data from the other table instead of recalculating every order I eliminated this variance. It's been quite a while since I did this with help of others so I'm not 100% certain where we modified the original report. After it was correct I heavily modified it to give me a more detailed report for my specific needs. Orders_total is the table that the customer emailed receipt is saved to. So the data in that table is exactly what the customer pays from.
I think this is what you may need from the admin/includes/classes/sales_report.php

PHP Code:

---

// pull shipping, discounts, tax, and gift certificates used from orders_total table
      $totals = $db->Execute("select * from " . TABLE_ORDERS_TOTAL . " where orders_id = '" . $oID . "'");
      while (!$totals->EOF) {
        $class = $totals->fields['class'];
        $value = $totals->fields['value'];
        if ($class != "ot_total1" && $class != "ot_subtotal1") {
          if($class == "ot_gv") {
            $order_gc_used += $value;
            $this->timeframe[$id]['total']['gc_used'] += $value;
            $this->build_li_orders($oID, 'gc_used', $value);

            $this->timeframe[$id]['total']['gc_used_qty']++;
            $this->build_li_orders($oID, 'gc_used_qty', 1);
          }
          elseif ($class == "ot_coupon" || $class == "ot_group_pricing" || $class == "ot_better_together" || $class == "ot_reward_points" || $class == "ot_quantity_discount" || $class == "ot_gv") {  
            $order_discount += $value;
            $this->timeframe[$id]['total']['discount'] += $value;
            $this->build_li_orders($oID, 'discount', $value);

            $this->timeframe[$id]['total']['discount_qty']++;
            $this->build_li_orders($oID, 'discount_qty', 1);
          }
          elseif ($class == "ot_tip") {
            $order_discount += $value;
            $this->timeframe[$id]['total']['ot_tip'] += $value;
            $this->build_li_orders($oID, 'ot_tip', $value);

            $this->timeframe[$id]['total']['tip_qty']++;
            $this->build_li_orders($oID, 'tip_qty', 1);
          }
          elseif ($class == "ot_cashback") {
            $order_discount += $value;
            $this->timeframe[$id]['total']['discount'] += $value;
            $this->build_li_orders($oID, 'discount', $value);

            $this->timeframe[$id]['total']['discount_qty']++;
            $this->build_li_orders($oID, 'discount_qty', 1);
          }
          elseif ($class == "ot_tax") {
            $order_recorded_tax += $value;
            $this->timeframe[$id]['total']['order_recorded_tax'] += $value;
            $this->build_li_orders($oID, 'order_recorded_tax', $value);
          }
          elseif ($class == "ot_shipping") {
            $order_shipping += $value;
            $this->timeframe[$id]['total']['shipping'] += $value;
            $this->build_li_orders($oID, 'shipping', $value);
          }
          elseif ($class == "ot_subtotal") {
            $order_shipping += $value;
            $this->timeframe[$id]['total']['subtotal'] += $value;
            $this->build_li_orders($oID, 'subtotal', $value);
          }
          elseif ($class == "ot_total") {
            $order_shipping += $value;
            $this->timeframe[$id]['total']['totalsales'] += $value;
            $this->build_li_orders($oID, 'totalsales', $value);
          }
          // this allows for a custom discount, a la Super Orders
          elseif ($value < 0) {
            $order_discount += abs($value);
            $this->timeframe[$id]['total']['discount'] += abs($value);
            $this->build_li_orders($oID, 'discount', abs($value) );

            $this->timeframe[$id]['total']['discount_qty']++;
            $this->build_li_orders($oID, 'discount_qty', 1);
          }
        }

        $totals->MoveNext();
      } 


---

Thanks southshorepizza.

So I've been trying to teach myself php using this add-on and I'm attempting to add product cost to the report (pulling from the product cost fields created by the profit margin calculator add-on for 1.3.9f) and I was able to integrate the product cost into the report for the product line stats. However, I'm unsure as to how to do the math to add a profit field line by line for each product.

I also don't know how to add the costs together for the timeframe total at the top of the product lines report.

Nor can I seem to figure out how to calculate total profit for each order because there isn't a total cost field in the database for each order, like a total rev and/or total sub-total. Should I attempt to add this database field and have it calculate in check out somehow, or have the script tabulate it?

Does anyone have any suggestions on how I might learn to do this, or the method I should go about trying to do this?

Thanks,
b

I know this is a bit old, but I was running into the same problem. The issue is, the report is deducting the sales tax from the product sales, which it should not.

In admin/includes/classes/sales_report.php line 385, I changed it from

$product_price_inc_tax = (zen_add_tax($final_price, $tax) * $quantity) + zen_add_tax($onetime_charges, $tax);

to

$product_price_exc_tax = ($product_price_inc_tax - $product_tax);

Now it calculates correctly. It's a quick hack, but it works. Someone could take the time to make a more elegant solution. I don't understand why it's doing this since Sales Tax is clearly additive to the total.

Hi wolfderby, I'm curious if you ever got anywhere with this? I've been misssing this feature for years, but I'm not really a programmer. I was thinking of installing the "Product Cost Display" (http://www.zen-cart.com/downloads.php?do=file&id=1444) or something similar to get my product costs into Zen Cart. Did you ever manage to get Sales Report to calculate the product costs correctly?

Best regards

Peter

Quote:

---

Originally Posted by wolfderby

So I've been trying to teach myself php using this add-on and I'm attempting to add product cost to the report (pulling from the product cost fields created by the profit margin calculator add-on for 1.3.9f) and I was able to integrate the product cost into the report for the product line stats. However, I'm unsure as to how to do the math to add a profit field line by line for each product.

I also don't know how to add the costs together for the timeframe total at the top of the product lines report.

Nor can I seem to figure out how to calculate total profit for each order because there isn't a total cost field in the database for each order, like a total rev and/or total sub-total. Should I attempt to add this database field and have it calculate in check out somehow, or have the script tabulate it?

Does anyone have any suggestions on how I might learn to do this, or the method I should go about trying to do this?

Thanks,
b

---

Not sure if anyone still checks up on this thread, but

I installed this add-on on my 1.5.1 installation, but when i click the "show me the money" button, nothing happens.
I've made sure i installed each file in its correct location (triple checked!), read through the readme again (i didnt complete any of the optional steps), and checked back about 15 pages in this thread.

Anyone have any idea whats going on?

Quote:

---

Originally Posted by PopularCrustacean

Not sure if anyone still checks up on this thread, but

I installed this add-on on my 1.5.1 installation, but when i click the "show me the money" button, nothing happens.
I've made sure i installed each file in its correct location (triple checked!), read through the readme again (i didnt complete any of the optional steps), and checked back about 15 pages in this thread.

Anyone have any idea whats going on?

---

There's a checkbox just above "Show me the money" that defaults to checked (much to my chagrin) and causes the result of the report to open in a new window.

Quote:

---

Originally Posted by lat9

There's a checkbox just above "Show me the money" that defaults to checked (much to my chagrin) and causes the result of the report to open in a new window.

---

Whether the "open in a new window" box is checked or not, nothing happens.
I tried disabling all my add-ons, that didnt work. I also tried it in firefox as well as chrome, neither works.

Any debug-log files in your /logs directory?

Good day,
This is a great plugin for sales reporting, but can i ask how i can add data from "Product Line Items" (namely Product Name and Model No. ) into the "Order Line Items" for CSV output?

Many thanks!
EngKiat

Any help please.. Thanks!

Quote:

---

Originally Posted by EngKiat

Any help please.. Thanks!

---

If you look at the file in the classes folder at line 879 you will see it includes the CSV output. You will need to modify that section to include the exact data you are looking to get.

Can Sales Report be easily modified to
sort based on Country-Shipped-To?

Dr Doug

Quote:

---

Originally Posted by d0ugparker

Can Sales Report be easily modified to
sort based on Country-Shipped-To?

Dr Doug

---

Please see the three posts prior to yours. Instead of looking for CSV you will do it outside of CSV.

I've suddenly started getting a blank page when trying to view the sales report by product line items or order line items for a month.

I'm using Zen 1.3.9 (working on the upgrade)
Sales Report 2.3.2

I've tried boosting the execution time up to 2400 from 600 and it still happens.

It works for Today or Yesterday or a range of dates as long as the range is less than a month.

I just downloaded the original mod to compare it to the files on my server and they are the same. No modifications or updates had been done close to the time this started happening.

The site is nearly 5 years old with lots of orders and products. There are over 70,000 entries in the orders table and over 300,000 in the orders_products.

Quote:

---

Originally Posted by joew36

I've suddenly started getting a blank page when trying to view the sales report by product line items or order line items for a month.

I'm using Zen 1.3.9 (working on the upgrade)
Sales Report 2.3.2

I've tried boosting the execution time up to 2400 from 600 and it still happens.

It works for Today or Yesterday or a range of dates as long as the range is less than a month.

I just downloaded the original mod to compare it to the files on my server and they are the same. No modifications or updates had been done close to the time this started happening.

The site is nearly 5 years old with lots of orders and products. There are over 70,000 entries in the orders table and over 300,000 in the orders_products.

---

Blank page = Error log

What does the error log you are getting say????

That took some looking, 500 server error.

Quote:

---

Originally Posted by joew36

That took some looking, 500 server error.

---

Is that what the error files in the cache folder are telling you.

I do know that the Edit mod is misbehaving a bit in our version of store---not in the use but it also coughs up an error and I have to clear the cache folder nearly every day just to get rid of them.

It might just be due to our store version, not sure, but I have no intention of upgrading until all mods we use are going to work properly too, thus far they do not...in our case and a few fresh installs that I have made.

That's from the top level server log files, in real time. I tried running the report while viewing the error log.

Quote:

---

Originally Posted by joew36

That's from the top level server log files, in real time. I tried running the report while viewing the error log.

---

well it sounds like there is an issue with your server which is preventing the report from completing..

Hello,

I have a problem with the goods value.
The program deducts the VAT from the goodsvalue (what is without vat.
Timeframe Num Orders Num Products Goods Value Goods Tax Order Rec. Tax Shipping Discounts Gift Cert. Sold Gift Cert. Used Total
12th-Feb-15 to
12th-Feb-15 1 3 | Diff: 1 €189.60 €50.40 €50.40 €0.00 €0.00 | Qty: 0 €0.00 | Qty: 0 €0.00 | Qty: 0 €240.00

Total value including tax is 290.40.
VAT is 50.40
Goods value is 240

How can I get this correct.
SALES REPORT Version 3.1.0
Thanks in advance for your reaction.

Richard.

Is there any one who can help me please?

Quote:

---

Originally Posted by ri-vier

Is there any one who can help me please?

---

You may just need to pay a developer to code the changes you want.

I just installed this excellent add-on and playing around with it I've noticed that when viewing "timeframe statistics" the rollover (rowOver) function doesn't seem to work. The function works just fine when viewing the other three "Displayed Information" options. Anybody know how to fix this?

Manufacturer name or id is not appearing in sale report? Is it possible to display manufacturer id /name in sale report and also sort order for high to low to see which manufacturer sales were highest to lowest?

I use this module a lot so thank you

However today I noticed something odd - I have the table discount module from thatsoftwareguy installed and its not including that data within the reports - it does however include the better together module from thatsoftwareguy ?

Does anyone have any ideas of how I can get it to include both discounts? My totals are off without it you see :)

ZC 154 clean shop from install but with additional mods?

I will ask thatsoftwareguy as well :)

Thank you Sarah

small bug...

in YOUR_ADMIN/includes/classes/sales_report.php, line 213 reads as the first line in the below code and it should read as the second line. not sure how many people are using the exclude products definition, but the sql statement will die the way it is currently written.

best.

PHP Code:

---

if ($_GET['doProdInc']== 'on' && is_array($include_products) && sizeof($include_products) > 0)

// should be

if (($_GET['doProdInc']== 'on' && is_array($include_products) && sizeof($include_products) > 0) || (isset($this->product_filter))) 


---

Quote:

---

Originally Posted by SarahL

I use this module a lot so thank you

However today I noticed something odd - I have the table discount module from thatsoftwareguy installed and its not including that data within the reports - it does however include the better together module from thatsoftwareguy ?

Does anyone have any ideas of how I can get it to include both discounts? My totals are off without it you see :)

ZC 154 clean shop from install but with additional mods?

I will ask thatsoftwareguy as well :)

Thank you Sarah

---

Scott helpfully replied with a link to his blog post that resolves this:

http://www.thatsoftwareguy.com/blog/...counting-mods/

Also for me I also needed to add in

Code:

---

$class == "ot_table_discounts" ||

---

as well

All working happily now

thank you
Sarah

Getting a 500 error. Tried installing twice. Any ideas?

Not Found

The requested URL /Delta/stats_sales_report.php was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Apache/2 Server at ktm-ets-fuel.com Port 80

Quote:

---

Originally Posted by robbinsgj

Getting a 500 error. Tried installing twice. Any ideas?

Not Found

The requested URL /Delta/stats_sales_report.php was not found on this server.

Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.

Apache/2 Server at ktm-ets-fuel.com Port 80

---

First, rename your admin directory. When posting to the forum, always remove that from the path.

Second, is the file actually on your server in the admin directory. 404 means the file was not found.

with regards to jim's 2nd statement, i find it highly unlikely that the script is where it is supposed to be. if it was, then the 404 error could be caused by a .htaccess rule or a apache server configuration issue, or something like that. but the odds of that being the case are highly unlikely....

as your admin directory is compromised, i would agree that you should rename it (and change your config file if using v139 or older)

with regards to your specific problem, i think you are probably uploading the files into the wrong directory. i would re-read the install very carefully and try again. the script that the system is complaining about is no doubt part of the plug-in, and you have probably uploaded that file into the wrong spot. which probably means other files are also uploaded incorrectly. just a guess...

good luck!

Hey all! I'm running Zencart 1.5.4 with Sales Report 3.1.0 and I'm wondering how I can add a "payment method" heading to the report. I have experience with programming but this is my first Zencart site so I'm still finding my way around. Any help would be much appreciated! Great module btw :)

I guess the easy answer would be to modify the PAGE_HEADING in admin/includes/languages/english/stats_sales_report.php. Or, to get fancier, you could modify admin/stats_sales_report.php. Search for where PAGE_HEADING is written out and put a little php logic around it - if $payment_method != false then use it as part of the page header.
Hope this helps...

So I took what you suggested and stretched it a bit, I almost have it working but I cant seem to populate that column with the payment_method data. For the total's column I copied the code for the num_products as that's fine for that purpose. I've screen caped my report output as well as the section of code that I think populates the table. Please, any help would be awesome! I'll continue working on this and I figure it out i'll be sure to post the solution.

Attachment 16259
Attachment 16258

you need to modify:

YOUR_ADMIN/includes/classes/sales_report.php

from the look of your screen shots, i'm guessing you need to modify the build_li_orders function. here is the function and i've attempted to highlight in red what i think the change would be to address this issue:

Code:

---

  function build_li_orders($oID, $field, $value) {
      $id = $this->timeframe_id;
      // first check to see if we even need to do anything
      if ($this->detail_level == 'order' || $this->detail_level == 'matrix') {
        // create the array if it doesn't already exist
        if (!is_array($this->timeframe[$id]['orders'][$oID]) ) {
          $this->timeframe[$id]['orders'][$oID] = array('oID' => $oID,
                                                        // the $oID key will be reset when we sort the array at
                                                        // display, so we store it as a part of the array as well
                                                        'goods' => 0,
                                                        'num_products' => 0,
                                                        'diff_products' => array(),
                                                        'shipping' => 0,
                                                        'goods_tax' => 0,
                                                        'order_recorded_tax' => 0,
                                                        'discount' => 0,
                                                        'discount_qty' => 0,
                                                        'gc_sold' => 0,
                                                        'gc_sold_qty' => 0,
                                                        'gc_used' => 0,
                                                        'gc_used_qty' => 0,
                                                        'grand' => 0,
                                                        'order_total_validation' => ''
                                                        );

          // get the customer data
          global $db;
          $c_data = $db->Execute("select c.*, o.payment_method from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS . " o
                                  where o.customers_id = c.customers_id
                                  and o.orders_id = '" . $oID . "' limit 1");

          $customers_id = $c_data->fields['customers_id'];
          $first_name = zen_db_output($c_data->fields['customers_firstname']);
          $last_name = zen_db_output($c_data->fields['customers_lastname']);

          $this->timeframe[$id]['orders'][$oID]['customers_id'] = $customers_id;
          $this->timeframe[$id]['orders'][$oID]['first_name'] = $first_name;
          $this->timeframe[$id]['orders'][$oID]['last_name'] = $last_name;
          $this->timeframe[$id]['orders'][$oID]['payment_method'] = $c_data->fields['payment_method'];
        }

        // add the passed $value to the passed $field in the ['orders'] array
        if ($field != 'order_total_validation') {
          $this->timeframe[$id]['orders'][$oID][$field] += $value;
        } else {
          $this->timeframe[$id]['orders'][$oID][$field] = $value;
        }
      }
    }

---

your other code should then correctly get the payment method. i hope!

good luck!

That was exactly it! Thanks so much, this is an awesome module and I'm glad we get to keep it.

I'm using different tax classes and I was wondering, is there a way you could see the sales separately. At this moment the system counts them all together, but I would like to able to see them separately.

Like:
I sell item thats 5,5£ (tax 10%) and other one that's 100£ (tax 24).

The systems shows total 24,50£, but I would need it to show it like 0,50£ and 24£.

I would like to add / edit to limit the sales report to certain categories.

Any ideas?

Quote:

---

Originally Posted by ehdesign

I would like to add / edit to limit the sales report to certain categories.

Any ideas?

---

Follow the same logic as used for the products, Only include specific Product IDs (comma separated list), and add a new checkbox option for categories.