Jump to content
Sign in to follow this  
homewetbar

Product Sales and Stats v2.0 Contribution

Recommended Posts

Try the newest version 2.1c I just posted and let me know. I put group by p.products_id right before the order by in the mysql statement which should group all of the product ids together unless each of your product versions has a different product id?

 

Thanks - that seemed to do the job!

 

As a point of interest - I still have 8 pages of products (560) but 6 of the pages are blank now. I assume that the first 140 products (2 pages) are the correct ones and the following 6 pages are the duplicates but are now displayed as blanks?

 

Anyway this is much better than before!

 

Wayne Lynch

Visit My Website at www.wikidhair.co.uk

Edited by wlynch

Share this post


Link to post
Share on other sites
Thanks - that seemed to do the job!

 

As a point of interest - I still have 8 pages of products (560) but 6 of the pages are blank now. I assume that the first 140 products (2 pages) are the correct ones and the following 6 pages are the duplicates but are now displayed as blanks?

 

Anyway this is much better than before!

 

Wayne Lynch

Visit My Website at www.wikidhair.co.uk

 

Interesting, yes that would be it. It really doesn't hurt anything but it I could see how it would throw off the page count. For some reason even thought all items with the same product id are being grouped they are still each being counted as an item hence the extra pages. I fairly certain it has something to do with these 2 lines of the code but I honestly have never encountered this before and i cannot duplicate this on my site as we do not have multiple versions of items with the same product id.

 

Here are the 2 lines I think are causing this

   $products_query_raw = "select p.products_id, p.products_model, p.products_date_added, p.products_trans_20, p.products_trans_60, p.products_sales_60, p.products_trans_120, p.products_trans_365, p.supplier_id, m.manufacturers_name as suppliername, p.products_mpn, p.products_cost, pd.products_name, p.products_quantity, p.products_price, last_one, l.name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_LANGUAGES . " l where p.products_id = pd.products_id and p.products_id = pd.products_id and p.supplier_id = m.manufacturers_id and l.languages_id = pd.language_id group by p.products_id order by $db_orderby $sorted";
  $products_split = new splitPageResults($HTTP_GET_VARS['page'], 70, $products_query_raw, $products_query_numrows);

 

Maybe someone with a more in-depth knowledge of PHP can suggest a solution?


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Hey guys-

 

I'm getting a blank report, but I know the cron is working because the new fields are populated with the data.

 

Any ideas?

 

Thanks,

Robert

Share this post


Link to post
Share on other sites
Hey guys-

 

I'm getting a blank report, but I know the cron is working because the new fields are populated with the data.

 

Any ideas?

 

Thanks,

Robert

 

So the new fields are populated but they are blank? It should be either or, can you explain further?


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

i get this error when i go to run the cron_update_bestsellers.php file

 

1054 - Unknown column 'products_trans_365' in 'field list'

 

UPDATE products_yearly_sales SET products_trans_365 = '16' WHERE products_id = '28'

 

[TEP STOP]

 

 

i'm doing it from a fresh install

Share this post


Link to post
Share on other sites
i get this error when i go to run the cron_update_bestsellers.php file

 

1054 - Unknown column 'products_trans_365' in 'field list'

 

UPDATE products_yearly_sales SET products_trans_365 = '16' WHERE products_id = '28'

 

[TEP STOP]

 

 

i'm doing it from a fresh install

 

 

Change products_trans_365 to products_sales_365 .

 

Also I uploaded a new version with this fix.


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

I'm having the same problem with nothing showing up... I haven't altered anything. I just followed the install instructions, double checked my work - and still nothing. No errors of any kind. Just the table headers for the chart and no data. I made some sample orders, so I should have data to display. Any help would be great. Thanks.

 

scrnshot-nothing.jpg

Share this post


Link to post
Share on other sites
I'm having the same problem with nothing showing up... I haven't altered anything. I just followed the install instructions, double checked my work - and still nothing. No errors of any kind. Just the table headers for the chart and no data. I made some sample orders, so I should have data to display. Any help would be great. Thanks.

 

scrnshot-nothing.jpg

 

I had the same problem.

It seems that the problem happens for me as I don't use manufacturers. My manufacturers table is blank.

 

IF this is the case with you, find the following line in admin/stats_products.php

   $products_query_raw = "select p.products_id, p.products_model, p.products_date_added, ps.products_trans_20, ps.products_trans_60, ps.products_sales_60, ps.products_trans_120, ps.products_sales_365, m.manufacturers_name as suppliername, p.products_model, pd.products_name, p.products_quantity, p.products_price, l.name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_YEARLY_SALES . " ps, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_LANGUAGES . " l where p.products_id = pd.products_id and p.products_id = pd.products_id and ps.products_id = p.products_id and l.languages_id = pd.language_id group by p.products_id order by $db_orderby $sorted";

 

And replace with:

 

   $products_query_raw = "select p.products_id, p.products_model, p.products_date_added, ps.products_trans_20, ps.products_trans_60, ps.products_sales_60, ps.products_trans_120, ps.products_sales_365, p.products_model, pd.products_name, p.products_quantity, p.products_price, l.name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_YEARLY_SALES . " ps, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LANGUAGES . " l where p.products_id = pd.products_id and ps.products_id = p.products_id and l.languages_id = pd.language_id and p.products_id = ps.products_id group by p.products_id order by $db_orderby $sorted";

 

 

Delete this row:

 

				<td class="main"><?php  if (!isset($orderby) or ($orderby == "manu" and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; echo '<a href="' . tep_href_link('stats_products.php', 'orderby=manu&sorted='. $to_sort) . '" class="main"><b>' . 'Manufacture' . '</b></a>';  ?></td>

 

 

Delete this row:

 

				<td class="list" valign="top"><?php echo substr($products['suppliername'], 0, 20); ?></td>

 

 

Replace this:

 

				<td colspan="12"><?php echo tep_draw_separator(); ?></td>

With this:

 

				<td colspan="11"><?php echo tep_draw_separator(); ?></td>

 

 

That sorted it for me!

I added some row striping to pretty the page up and it's looking good.


Read the forum rules...

Share this post


Link to post
Share on other sites

Hi ,

 

i am getting the following error in the admin section

 

Parse error: syntax error, unexpected ')' in C:\xxxxx\xxxxx\xxxxxxxxxxxxxx\xxxxxxx\admin\includes\boxes\reports.php on line 35

 

Can anyone help me i followed the instructions , i am just a bit rusty with the old php

 

<?php
/*
 $Id: reports.php,v 1.5 2003/07/09 01:18:53 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 [url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

 Copyright © 2002 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- reports //-->
         <tr>
           <td>
<?php
 $heading = array();
 $contents = array();

 $heading[] = array('text'  => BOX_HEADING_REPORTS,
                    'link'  => tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, 'selected_box=reports'));

if ($selected_box == 'reports') {
   $contents[] = array('text'  => '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_VIEWED . '</a><br>' .
                                  '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_PURCHASED . '</a><br>' .
                                  /*mod for heardabout conribution:*/
                                  '<a href="' . tep_href_link(FILENAME_HEARDABOUT, '', 'NONSSL') . '" class="menuBoxContentLink">Heard About</a><br>' .
                                  /*end of mod for heardabout conribution:*/

'<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a><br>' .
'<a href="' . tep_href_link(FILENAME_STATS_LOW_STOCK_ATTRIB, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_STATS_LOW_STOCK_ATTRIB . '</a>');

'<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a><br>' .
'<a href="' . tep_href_link('stats_products.php', '', 'NONSSL') . '" class="menuBoxContentLink">Product Sales v2.0</a>'
	);

}

 $box = new box;
 echo $box->menuBox($heading, $contents);
?>
           </td>
         </tr>
<!-- reports_eof //-->

Share this post


Link to post
Share on other sites

No problem, it's exactly what your error message is saying you probably just have an extra ) somewhere, just make sure all of the ( match a ) in your code on that boxes\reports.php page...


Most Valuable OsCommerce Contributions:

Also Purchased (AP) Preselection (cuts this resource hogging query down to nothing) -- Contribution 3294

FedEx Automated Labels -- Contribution 2244

RMA Returns system -- Contribution 1136

Sort Products By Dropdown -- Contribution 4312

Ultimate SEO URLs -- Contribution 2823

Credit Class & Gift Voucher -- Contribution 282

Cross-Sell -- Contribution 5347

Share this post


Link to post
Share on other sites

Hi

This contributions seems to install ok ....

 

However, I do not get any output from the following....

20T 60T 60S 120T 365S

 

everything else is ok.

 

Any ideas?

 

cheers vjc

Share this post


Link to post
Share on other sites

Hi again

My bad! I should have read the instructions a little better ..... cron job to run! (da)

 

However do I need to run a cron job each time I want to use this ?

 

thx vjc

Share this post


Link to post
Share on other sites

Hi all

 

After install this great contrib i still have a few issues...

Manufacturers col doesn't work. (All products with same manuf.)

In query why the double check "where p.products_id = pd.products_id and p.products_id = pd.products_id" ?

 

 

thanks

Share this post


Link to post
Share on other sites

Hi there,

 

great contrib!

 

Everything is working fine for, besides that it only shows 1 manufacturer for all products, that's a bit strange. Would be great if I can sort by manufacturer for a better overview.

Anyone an idea?

 

Thanks in advance!

 

Regards

Share this post


Link to post
Share on other sites

I've just discovered that the cron job was not adding new products that were added after installation of the contribution. I fixed this by adding the following lines to cron_update_bestsellers.php:

 

tep_db_query("delete from products_yearly_sales");

tep_db_query("INSERT INTO products_yearly_sales ( products_id ) SELECT products_id FROM products");

 

below the following line:

 

require('includes/application_top.php');

 

I hope it helps someone!

 

Shaun

Share this post


Link to post
Share on other sites

I think I killed my store, but I can't find where I went wrong... Here is the error I get when I run the script from cron...

 

Output from command /usr/bin/php /var/www/web6/web/catalog/cron_update_bestsellers.php ..

 

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /var/www/web6/web/catalog/includes/application_top.php on line 28

 

Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/bin/PEAR') in /var/www/web6/web/catalog/includes/application_top.php on line 28

 

-----------

 

Any help would be greatly appreciate to get this going.

 

Also, the report opens in a new window, not in the same window as other reports normally would... Is there a fix for that?

 

Also, when I do open the report, it's completely blank... Even though, my store currently only as 1 active item, and 1 disabled.

 

Thanks

Tim

Share this post


Link to post
Share on other sites

I think I killed my store, but I can't find where I went wrong... Here is the error I get when I run the script from cron...

 

Output from command /usr/bin/php /var/www/web6/web/catalog/cron_update_bestsellers.php ..

 

Warning: require(includes/configure.php): failed to open stream: No such file or directory in /var/www/web6/web/catalog/includes/application_top.php on line 28

 

Fatal error: require(): Failed opening required 'includes/configure.php' (include_path='.:/usr/bin/PEAR') in /var/www/web6/web/catalog/includes/application_top.php on line 28

 

-----------

 

Any help would be greatly appreciate to get this going.

 

Also, the report opens in a new window, not in the same window as other reports normally would... Is there a fix for that?

 

Also, when I do open the report, it's completely blank... Even though, my store currently only as 1 active item, and 1 disabled.

 

Thanks

Tim

 

try the command: cd /var/www/web6/web/catalog/ && /usr/bin/php cron_update_bestsellers.php

Edited by guiltarra

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×