Product Sales and Stats v2.0 Contribution

[Guest]

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 July 23, 2008 by wlynch

[homewetbar 1]

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?

[overdark 0]

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

[homewetbar 1]

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?

[Guest]

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

[homewetbar 1]

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.

[digitalkemical 0]

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.

 

[Hade 1]

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.

 

 

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.

[toefinger 0]

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

[homewetbar 1]

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

[stugutz 0]

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

[stugutz 0]

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

[zanare 0]

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

[zanare 0]

Update:

 

Page count error solved.

After reading this post http://forums.oscommerce.com/index.php?showtopic=290110 and made changes to admin/includes/modules/classes/split_page_results.php.

[yonc 0]

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

[sgostelow 0]

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

[shankscomp 0]

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

[guiltarra 0]

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 November 7, 2012 by guiltarra