Business Activity Report 1.5.0 Support Thread

[gharls]

I installed and it works but it shows $0.00 tax and $0.00 coupons no matter what the timeframe and I know several have used coupons and many paid tax. Any idea why it isn't picking up that info?

Thanks!

Blessings,
Krisann

Your case sounds the most baffling ... based on the SELECT SQL that resides behind the report, it's 'supposedly' impossible not to pick up tax on the order line.

First things first ... do you know how to use SQL against your database? (We can also communicate via e-mail; I'm at [email protected]).

[shartlesville]

Your case sounds the most baffling ... based on the SELECT SQL that resides behind the report, it's 'supposedly' impossible not to pick up tax on the order line.

First things first ... do you know how to use SQL against your database? (We can also communicate via e-mail; I'm at [email protected]).

I can do basic stuff what did you have in mind?

I mean, I can copy, paste, and click 'go' I can also usually search through to find what I'm looking for if I know approximately what table something should be in.

I am running zcv1.3.8a in case it matters. :-)

[gharls]

I can do basic stuff what did you have in mind?

I mean, I can copy, paste, and click 'go' I can also usually search through to find what I'm looking for if I know approximately what table something should be in.

I am running zcv1.3.8a in case it matters. :-)

If not too much a bother, would you please cut and paste the following SQL statement into your phpAdmin query tool for your database, hit the GO button, and then send me a picture of your query results. I'm at [email protected].


SELECT date(a.date_purchased) orderdate, count(a.orders_id) ordercount, max(b.value) ordermax, sum(round(b.value, 2)) subtotal, IFNULL(sum(round(c.value, 2)),0) coupons, IFNULL(sum(round(d.value, 2)),0) discounts, sum(round(a.order_tax, 2)) tax, IFNULL(sum(round(e.value, 2)),0) shipping, sum(a.order_total) ordertotal FROM orders a JOIN orders_total b ON a.orders_id=b.orders_id AND b.class='ot_subtotal' LEFT JOIN orders_total c ON a.orders_id=c.orders_id AND c.class='ot_coupon' LEFT JOIN orders_total d ON a.orders_id=d.orders_id AND d.class='ot_gv' LEFT JOIN orders_total e ON a.orders_id=e.orders_id AND e.class='ot_shipping' WHERE date_purchased >= '2009-01-01' AND date_purchased < DATE_ADD('2009-07-31', INTERVAL 1 DAY) GROUP BY orderdate

Thanks.

[rleepac]

[QUOTE=gharls;756315]1. You must execute the SQL script that comes with the report. Instructions can be found in the README file.


I'm having the same problem. When I hit Detail I get a message that the dg_businessTS_detail table doesn't exist.

I've run the SQL. I've checked in my SQL and the table is there but it's empty.

What am I doing wrong? Or more importantly....how do I fix it?

Anyone's help is appreciated here. In the meantime I'm going to remove all the files and start over again. I'll post if that works...

Thanks!
Bekah

[rleepac]

[QUOTE=rleepac;764620]

1. You must execute the SQL script that comes with the report. Instructions can be found in the README file.


I'm having the same problem. When I hit Detail I get a message that the dg_businessTS_detail table doesn't exist.

I've run the SQL. I've checked in my SQL and the table is there but it's empty.

What am I doing wrong? Or more importantly....how do I fix it?

Anyone's help is appreciated here. In the meantime I'm going to remove all the files and start over again. I'll post if that works...

Thanks!
Bekah

Figured it out. For some reason a prefix of zen_ was added to the name of the table when I ran the SQL patch - so it kept saying the table wasn't there. I renamed the table in my SQL taking out the zen_ from the beginning and it works now. Yay!

Bekah

[rleepac]

Got a question about getting this data out to a csv file.

I look up the info in my store/admin and it says: Records Displayed Below are Stored in Database Table "dg_businessTS_detail

So I go into my SQL database and open that table and it is exactly as it says. But, I want to be able to pull ALL the information from my search but instead it only puts in the 20 records that are showing on the search results (detail) page.

How do I get all the data instead of just 20 records at a time?

Thanks,
Bekah

[jcit]

1.
i'm probably not doing something right. i got the business activity to installed, and when i click on the 'details' button, i get this on the page :

Records Displayed Below are Stored in Database Table "dg_businessTS_detail"
1146 Table 'yabycosm_zc1.dg_businessTS_detail' doesn't exist
in:
[TRUNCATE dg_businessTS_detail]
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.


does that mean i didn't install the module correctly?

I had this happen when I installed BAR. The problem is that "zen" (default) gets suffixed in MySQL, making the call wrong.

Here's the fix for the basic (zen) install:

In the DG_BusActDetail.php change line 115 from:

$truncate = "TRUNCATE dg_businessTS_detail";

to

$truncate = "TRUNCATE zen_dg_businessTS_detail";

and also do the same on line 189:

$insert = "INSERT INTO zen_dg_businessTS_detail (order_date, orders_id, customers_name, payment_method, subtotal, coupon, discount, tax, shipping, order_total) VALUES ('" . $dataset->fields['orderdate'] . "', " . $dataset->fields['orders_id'] . ", '" . $dataset->fields['customers_name'] . "', '". $dataset->fields['payment_method'] . "', " . $dataset->fields['subtotal'] . ", " . $dataset->fields['coupons'] . ", " . $dataset->fields['discounts'] . ", " . $dataset->fields['tax'] . ", " . $dataset->fields['shipping'] . ", " . $dataset->fields['ordertotal'] . ")";

Cheers.

[mooomers]

1. You must execute the SQL script that comes with the report. Instructions can be found in the README file.

2. Is your Zen Cart customized to separately capture and store both tax amounts? If so, I can perhaps give you a suggestion as to how to modify the report code to include both ... redesigning the report layout will be a messy job.

Hi!

1. I did, but it's still giving me the same error.

2. That would be fantastic!! Anything to make tax time less stressful would be awesome~

The way my taxes are setup in zencart is like this:
Priority Tax Class Zone Tax Rate
1 GST & PST Class Canada GST zone 5%
1 GST & PST Class Ontario PST Zone 8%

does this help?

[seancon1]

i was having a problem with "[INSERT INTO dg_businessTS_detail" and found a customer had an apostrophe in his name,
i corrected it by adding mysql_real_escape_string() to the customers name in the insert query.

changed line 189

$insert = "INSERT INTO dg_businessTS_detail (order_date, orders_id, customers_name, payment_method, subtotal, coupon, discount, tax, shipping, order_total) VALUES ('" . $dataset->fields['orderdate'] . "', " . $dataset->fields['orders_id'] . ", '" . $dataset->fields['customers_name'] . "', '". $dataset->fields['payment_method'] . "', " . $dataset->fields['subtotal'] . ", " . $dataset->fields['coupons'] . ", " . $dataset->fields['discounts'] . ", " . $dataset->fields['tax'] . ", " . $dataset->fields['shipping'] . ", " . $dataset->fields['ordertotal'] . ")";

to

$insert = "INSERT INTO dg_businessTS_detail (order_date, orders_id, customers_name, payment_method, subtotal, coupon, discount, tax, shipping, order_total) VALUES ('" . $dataset->fields['orderdate'] . "', " . $dataset->fields['orders_id'] . ", '" . mysql_real_escape_string($dataset->fields['customers_name']) . "', '". $dataset->fields['payment_method'] . "', " . $dataset->fields['subtotal'] . ", " . $dataset->fields['coupons'] . ", " . $dataset->fields['discounts'] . ", " . $dataset->fields['tax'] . ", " . $dataset->fields['shipping'] . ", " . $dataset->fields['ordertotal'] . ")";

probably should correct this elsewhere so it gets properly inserted, but that's for another post!

hope this helps someone,

Sean

[IceBox Coolers]

Hi,

When I run the SQL patch I receive the following errors
Warning: file() [function.file]: open_basedir restriction in effect. File(/tmp/php_upload/phpDnOeA3) is not within the allowed path(s): (VIRTUAL_DOCUMENT_ROOT:/usr/local/lib/php:/usr/local/lib/ph:/home/chroot_lib/phplib/:/usr/local/bin/) in /home/c-web/cf/fb/ iceboxcoolers.com.au / public / www / admin / sqlpatch.php on line 693

Warning: file(/tmp/php_upload/phpDnOeA3) [function.file]: failed to open stream: Operation not permitted in /home/c-web/cf/fb/ iceboxcoolers.com.au / public / www / admin/sqlpatch.php on line 693

Anyone have any idas on how to fix this ?
Alan