Monthly Sales and Tax Summary Report [support thread]

dbltoe:

1054:Unknown column 'o.delivery_state' in 'where clause'
There's no 'delivery_state' field in the orders_status table in any version of Zen Cart, so this isn't a v1.5.4 issue.
Looks more like a failed/not-completed SQL schema change during installation of the related addon.

This is in the installed file.```php
//Zone Only SQL appendage
$zone_only_sql = '';
$zone_selected = false;
if ($_POST['zone_only'] != '' && !in_array('all', $_POST['zone_only'])) {
$get_zone_only = $_POST['zone_only'];
$zone_selected = true;
$zoned_sql = '';
foreach ($get_zone_only as $selectedZone) {
$zoned_sql .= "o.delivery_state LIKE '" . $selectedZone . "' OR ";
}
$zoned_sql = rtrim($zoned_sql, "OR ");
$zone_only_sql = " AND (" . $zoned_sql . ") ";
}

FWIW, that state filter is part of the v1.5.3 update for the plugin (which pretty much wiped out the changes that I made for v1.5.0 of the plugin). Hopefully, the plugin updater will chime in and correct the issue that's been created.

I am translating the module. The months remains in English. Where does the module pull the months from?

The plugin works well but it's generating the following error every time I access the admin:

PHP Warning:  unlink(/admin/includes/functions/extra_functions/stats_monthly_sales.php): Permission denied in /admin/includes/functions/extra_functions/stats_monthly_sales.php on line 27

That's what I read on line 27:

unlink(__FILE__);

Any idea?

Yes. Delete that line.

swguy:

Yes. Delete that line.

Thank you!

Getting an "illegal Access" after clicking on Monthly Sales link under admin-reports. This seemed pretty straight forward. Any Ideas?

I am using 1.54, and thought the installation was quite straight forward and simple, upload into admin folder... But when I go to reports>monthly Sales the list is "blank". Meaning it shows the headings, the filters and the print/invert/help, but there is no data displayed. If I select a status that none of my orders have ("pending" for example, then I get a report of "No income for this date/status selection" But if I select ones I do have status of (or all) then I just get blank below the column headings (even the zencart footer that is normally on all the admin pages is gone. The footer only displays when it also reports "No income for this date/status selection" as mentioned above).

Any thoughts to get it to display something, even an error message would be helpful I guess:frusty:? the print button "works" in that it opens a new tab with the blank columns, the help button brings up the help popup... But it just doesn't display anything.

Any help is very much appreciated!!!

Blank Page or Partial Blank Page

The debug logs (if present) should help identify the cause.

lhungil:

Blank Page or Partial Blank Page

The debug logs (if present) should help identify the cause.
You rock!:cheers:

So here was my issue, in the log file I found:

PHP Fatal error:  Call to undefined method queryFactory::Exccute() in ...snip... [MyAdminDir]/stats_monthly_sales.php on line 607

(I've "...snip'ed... out the directory path to my admin dir)
Looks like the current version has a typo on line 607. I replaced the "c" to an "e" so it read Ex**e**cute and re-uploaded and now the script works. So whomever can, should probably fix that in the downloadable.

Thanks again for the heads up!!!!

Glad you were able to resolve the issue and Thank You for letting us know the resolution!

:-)

I can't find a resolution to this problem, did I miss it somewhere? I'm having the same issue with group pricing. My group discount shows up in the Gift Vouchers column (which is fine) but the sales tax is calculated on the total sale without the discount.

municycle:

We are finding the same issue - the taxes collected in this mod are based on the full retail price not the price charged with the discount coupon (etc). As stated it does not match the pop up box.

The product sales (taxed or no taxed) should show the actual sales as shown on the invoices.

This is a great mod but needs a tweek.

thanks in advance if you can help steer us to a solution.

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

On the last update of this mod on December 4, 2016 there is a typo around line 607 in the file YOUR_ADMIN/stats_monthly_sales.php

The problem in red

    
if ($sel_month<>0) $loworder_query_raw .= " AND  dayofmonth(o.date_purchased) = '" . $sales->fields['row_day'] . "'";
    $loworder_query = $db->[B]Exccute[/B]($loworder_query_raw);
    if ($loworder_query->RecordCount()>0)    
    $loworder_this_row = $loworder_query;
}

The fix in Blue

   
 if ($sel_month<>0) $loworder_query_raw .= " AND dayofmonth(o.date_purchased) = '" . $sales->fields['row_day'] . "'";
    $loworder_query = $db->[B]Execute[/B]($loworder_query_raw);
    if ($loworder_query->RecordCount()>0)    
    $loworder_this_row = $loworder_query;
}

In case someone ran into the error PHP Fatal error: Call to undefined method queryFactory::[COLOR=#ff0000]Exccute/COLOR display when trying to display the results of monthly sales in the admin

Hoping that @mprough steps in here, since she's the last updater.

I've fixed the typo and replaced the zip in that Dec 2016 version.

I've just installed this plugin. Thanks to all who have worked on it. What I would like to do is see sales via country. From what I read, this plugin should do that, but in the list (view image) what I get is towns / counties / states etc and not countries. Is it possible to view sales via country with this plugin?

Running SQL 5.6 this mod works great.

**5.7 and this lookup is no longer supported:
**
admin/stats_monthly_sales.php

line 470 - 482
$sales_query_raw = "SELECT sum(ot.value) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month, dayofmonth(o.date_purchased) row_day from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) WHERE ";
if ($get_status) {
$status = zen_db_prepare_input($get_status);
$sales_query_raw .= "o.orders_status =" . $status . " AND ";
}
$sales_query_raw .= "ot.class = " . $class_val_total;
$sales_query_raw .= $zone_only_sql;
if ($sel_month<>0) $sales_query_raw .= " AND month(o.date_purchased) = " . $sel_month;
$sales_query_raw .= "** group by **year(o.date_purchased), month(o.date_purchased)";
if ($sel_month<>0) $sales_query_raw .= ", dayofmonth(o.date_purchased)";
$sales_query_raw .= " order by o.date_purchased ";
if ($invert) $sales_query_raw .= "asc"; else $sales_query_raw .= "desc";
$sales_query = $db->Execute($sales_query_raw);

Admin output looks like:
WARNING: An Error occurred, please refresh the page and try again.

Logs error looks like:
PHP Fatal error: 1055:Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'njpbusin_155f_2018.o.date_purchased' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ::

SELECT sum(ot.value) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month, dayofmonth(o.date_purchased) row_day from zen_orders o left join zen_orders_total ot on (o.orders_id = ot.orders_id) WHERE ot.class = 'ot_total' group by year(o.date_purchased), month(o.date_purchased) order by o.date_purchased desc

==> (as called by) /home/njpbusiness/public_html/shop/bRawn-aBc-scOot/stats_monthly_sales.php on line 482

You have to change the query to include o. date_purchased. Just add this after the first field.

SELECT sum(ot.value) gross_sales, o. date_purchased, ...