Monthly Sales and Tax Summary Report [support thread]

twitchtoo:

Running SQL 5.6 this mod works great.

**5.7 and this lookup is no longer supported:
**

i'm sorry, i just do not agree with that statement....

twitchtoo:

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 ::

as you can see from the error log, you no doubt have a different setting for mysql 5.6 v mysql 5.7. these setting do not carry over from version to version.

you can change this mode from the mysql console:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

you can read a bit about it here:

https://goo.gl/pnB1HW

else, you can make the change suggested by swguy... although you may have other mysql queries that could fail, as you have different settings in the versions.

best.

@CarlWhat These changes will certainly work in local testing but do they work in all hosting environments?

@swguy if you are asking about a shared hosting environment, and the settings of a global config for mysql, we are now getting beyond the resources i have available to test and check. as well as the energy.

i suppose you bring up an interesting point. hard to imagine a host having different settings for different versions.... but no doubt possible/probable. and pretty easy to tell the host that they have changed the settings across versions.

that said, i am sure in a shared hosting environment that is properly setup, one could change the config to only affect your account as opposed to the whole server. similar to having one's own php config, i'm positive one could globally change mysql mode configs to ONLY affect their account.... but don't quote me on it...

best.

Hello (V1.5.5),

I made a mistake and uploaded [monthly_sales_report-master-patched] folder to admin.

After I realized it, I deleted it using C-Panel File manager. So, everything should be removed.

Then I uploaded contents from [YOUR_ADMIN] folder to admin.

For some reason I have now twice "Monthly Report" menu in admin:

But everything is working.

Is it possible to remove redundant menu, or just leave it along?

Thank you for your time upfront.

Looks like the admin/includes/functions/extra_functions/stats_monthly_sales.php is the culprit.

It checks to see if the stats_monthly_sales page key exists and adds the extra report menu item. It apparently does not check to see if the specific menu item (FILENAME_STATS_MONTHLY_SALES) already exists. Thus your second menu item is registered in the admin_pages table of the database.

You can use the phpMyAdmin to search the table for FILENAME_STATS_MONTHLY_SALES and delete one of the entries. That should fix it.

dbltoe:

Looks like the admin/includes/functions/extra_functions/stats_monthly_sales.php is the culprit.

It checks to see if the stats_monthly_sales page key exists and adds the extra report menu item. It apparently does not check to see if the specific menu item (FILENAME_STATS_MONTHLY_SALES) already exists. Thus your second menu item is registered in the admin_pages table of the database.

You can use the phpMyAdmin to search the table for FILENAME_STATS_MONTHLY_SALES and delete one of the entries. That should fix it.

Thank you for the suggestion.

I have the dilemma which line to delete, top or bottom?

Why, do you think, the file stats_monthly_sales.php was not copied to admin/includes/functions/extra_functions/ in first place?

All other files were. Is it permission issue?

Should I copy it manually now?

Looks like a bigger question as they both should be sort 17 if it was actually a duplicate. Instead, it looks like you have another program (mod) loaded that uses the same menu name.

Never said the file was not copied. I thought the file had created a duplicate entry. Now I see you have two mods loaded.

No files need to be copied.

As to a fix....

Do both the menu items show a result?

When you click on the entries in the menu, is either result correct, is either incorrect, are both incorrect, or are both correct outputs?

Using the Developer's Tool Kit, search for the define of FILENAME_STATS_MONTHLY_SALES. There should be two. Post the names of the two files with the define.

dbltoe:

Looks like a bigger question as they both should be sort 17 if it was actually a duplicate. Instead, it looks like you have another program (mod) loaded that uses the same menu name.

Never said the file was not copied. I thought the file had created a duplicate entry. Now I see you have two mods loaded.

No files need to be copied.

As to a fix....

Do both the menu items show a result?

When you click on the entries in the menu, is either result correct, is either incorrect, are both incorrect, or are both correct outputs?

Using the Developer's Tool Kit, search for the define of FILENAME_STATS_MONTHLY_SALES. There should be two. Post the names of the two files with the define.

Do both the menu items show a result? -> YES

When you click on the entries in the menu, is either result correct -> YES

is either incorrect -> NO

are both incorrect -> NO

or are both correct outputs? -> YES

Search for define('FILENAME_STATS_MONTHLY_SALES' returns one file:

admin/includes/languages/english/extra_definitions/stats_monthly_sales.php

Search for FILENAME_STATS_MONTHLY_SALES adds second file:

admin/stats_monthly_sales.php

Both admin files are correct. One is the define of the variable and the other places the variable on the report page. HOWEVER>> If you searched for FILENAME_STATS_MONTHLY_SALES, you should have come up with 6 times it shows up. Only one of those should have been the define in admin/includes/languages/english/extra_definitions/stats_monthly_sales.php (which you said you found).

Your response makes me ask if you saw the other five places the variable is used in the mod.

And, the one question I left out before was:

Do the two menu entries bring up the same page? IOW, does it make no difference which item you click on? Or, does one bring up different information than the other.

If they both bring up the same page, you probably have a holdover from an earlier mod. And that brings us back to the five times the variable should have shown up without the define.

If --

1. Both menu items bring up the same data AND
2. You have five variable locations and 1 define when searching with Developers Tool
   Then, I'd delete the item in the table that has the sort_order of 1000.

If they don't bring up the same data, we'll need to look further.

dbltoe:

Both admin files are correct. One is the define of the variable and the other places the variable on the report page. HOWEVER>> If you searched for FILENAME_STATS_MONTHLY_SALES, you should have come up with 6 times it shows up. Only one of those should have been the define in admin/includes/languages/english/extra_definitions/stats_monthly_sales.php (which you said you found).

Your response makes me ask if you saw the other five places the variable is used in the mod.

And, the one question I left out before was:

Do the two menu entries bring up the same page? IOW, does it make no difference which item you click on? Or, does one bring up different information than the other.

If they both bring up the same page, you probably have a holdover from an earlier mod. And that brings us back to the five times the variable should have shown up without the define.

If --

2. Both menu items bring up the same data AND

3. You have five variable locations and 1 define when searching with Developers Tool

Then, I'd delete the item in the table that has the sort_order of 1000.

If they don't bring up the same data, we'll need to look further.

Thank you for looking at this.

Back to beginning.
When I scan (search) on my hard drive for key word **stats_monthly_sales **original installation folder of module (after download) I get this result:

1. /YOUR_ADMIN/includes/languages/english/extra_definitions/stats_monthly_sales.php

2. /YOUR_ADMIN/includes/languages/english/stats_monthly_sales.php

3. /YOUR_ADMIN/includes/functions/extra_functions/stats_monthly_sales.php

4. /YOUR_ADMIN/includes/boxes/extra_boxes/stats_monthly_sales_reports_dhtml.php

5. /YOUR_ADMIN/stats_monthly_sales.php

There are 5 files found.

After I FTP files to Zen Cart admin folder and make search in Zen Cart:

I get this result:

One file is missing: /YOUR_ADMIN/includes/functions/extra_functions/stats_monthly_sales.php

Zen Car does not see file in extra_functions folder (?)

If I search as you advise for FILENAME_STATS_MONTHLY_SALES

The result is:

Now 2 files are missing in /english/ and /extra_functions/ folders

I do not see any different permission on these folders compare to others.

Both menus when clicked return absolutely the same result.

I am afraid to delete the line 1000 due to my illiteracy.
But 2 menus, if there is no harm to other areas, will not stop me sleeping at night.

If there is no damage, may be we should stay as is (?):smile:

I experimented a little and methodically deleted all files which came with this module.
Both menus disappeared.
After uploading all files again they appeared again.

Thank you.

I made search also using C-Panel file manager:

All 5 files are found:

So, looks like Zen Cart does not see some files.

idtags:

I experimented a little and methodically deleted all files which came with this module.
Both menus disappeared.
After uploading all files again they appeared again.This happens because the leftover MonthlyStats has no define without the files, AND nothing to fire a debug file because no file is asking to use the variable. When the files are loaded back, the define defines that variable. The other files fulfill the NEED by asking for the variable.

Thus, the both come and go result when files are removed.

Back up the database just cause you should anytime you touch it.

Delete the line in the table that has MonthlyStats for the page_key and 1000 for the sort_order.

Load all mod files (if not already there).

That should do it.

dbltoe:

This happens because the leftover MonthlyStats has no define without the files, AND nothing to fire a debug file because no file is asking to use the variable. When the files are loaded back, the define defines that variable. The other files fulfill the NEED by asking for the variable.

Thus, the both come and go result when files are removed.

Back up the database just cause you should anytime you touch it.

Delete the line in the table that has MonthlyStats for the page_key and 1000 for the sort_order.

Load all mod files (if not already there).

That should do it.

In fact removing the line in the table that has FILENAME_STATS_MONTHLY_SALES for the page_key and 1000 for the sort_order fixed the problem!

Thank you very much for help. :smile:

In fact removing the line in the table that has FILENAME_STATS_MONTHLY_SALES for the page_key and 1000 for the sort_order fixed the problem!You're welcome. I think you may mean the main_page entry. The page_key for the sort_order was MonthlyStats.

Figured it was a leftover from the start, but you always want to be careful when the database is in play.

bonheddwr:

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?

It actually only shows places; towns, cities, countries, ect. from which you have had sales in the past.

I understand the consensus against bumps BUT, I have PMed Melanie requesting any possible solution to the problem initiated in post #140.

I did not request a personal answer but hoped for something in this thread.

Apparently, the signature is law and no answer provided for a PM even if referencing the thread.

Perhaps a glimmer of hope can be provided with this bump.

Just hire her. I'm sure she'll be glad to take your paid professional engagement. Easy peasy!

Okay, I'm going to bring this here as I've worn out the trifocals looking at this thing. I've attached the whole file but, I'll address two areas of YOUR_ADMIN/stats_monthly_sales.php here for brevity.

As previously stated and verified, the code at lines 126 thru 138 incorrectly identify o.delivery_state in that this causes the code to look in the wrong table when accessing mysql.```
//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 . ") ";
}

Apparently, most of those occurrences work just fine.  It's only when selecting a particular state, city, or country that the unfound delivery_state breaks the operation.

I know what information I need (delivery_state) and I know which table it needs to come from (orders) but I can't figure out how to change "o.delivery_state LIKE '" to get the info from the correct table.

Here's an opportunity to teach me to fish beyond just baiting the hook and throwing it in the water.

THANX for any assistance.

PS for anyone wanting to go the extra mile, it would be nice to sort by country as well:yes:

dbltoe:

I understand the consensus against bumps BUT, I have PMed Melanie requesting any possible solution to the problem initiated in post #140.

I did not request a personal answer but hoped for something in this thread.

Apparently, the signature is law and no answer provided for a PM even if referencing the thread.

Perhaps a glimmer of hope can be provided with this bump.

I do not answer PMs for module issues, I really can't manage them. I used to, but no more. It's not my module, I've updated it a few times =) I did research your issue a bit since I stumbled on to this as you called me out.... What version of PHP? Zen Cart 1.5.4 correct? I do not have any logging error on Zen Cart 1.5.4 & PHP 5.6.40 & MySQL 5.5.5-10.1.41-MariaDB.

I should apologize for the bump and call out. As I look back through I find that, although you updated the mod twice since 2014, you have never made a post in the support thread. Therefore, my assumption (I know) that you had to be subscribed and receiving notices was totally incorrect.:blush:

The information mentioned in post #178 is the main problem. It is doing the same in 1.5.5f with PHP 5.6 and MySQL 5.7 as it did in 1.5.4. Naturally, I have not loaded it on 1.5.6#.

If you don't have a problem with your version, did you see drbyte's comment in 143> 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.I cannot find any instance of this field in previous or later versions of Zen Cart. Therefore, I thought you might have had some mod connected with it that needed an extra field. Was just grasping at straws as this would be perfect for our site if...

Again, as stated in #178, I know what we need. Just not how to get it into the mix.