Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Best sellers - how to show the ranking by month?


daddy

Recommended Posts

Hi there!

 

I'm not a php expert, so I'd like to ask for support here.. I'd like to change my best seller listing.

 

at the moment it ranks the products purchased since 2005, when I opened my store, so it's not very useful - a lot of them don´t sell anymore but still appears in the rank because used to in the past.

 

I'd like to maket it shows only the product purchased in the last 30 days, I'm sure it can´t be impossible to do :blush:

 

Can somebody help me sorting it out?

 

Thanks

Link to comment
Share on other sites

I just installed a tweaked best sellers for a customer of ours... lets see if I can dig it out.

 

-R

 

Hi there!

 

I'm not a php expert, so I'd like to ask for support here.. I'd like to change my best seller listing.

 

at the moment it ranks the products purchased since 2005, when I opened my store, so it's not very useful - a lot of them don´t sell anymore but still appears in the rank because used to in the past.

 

I'd like to maket it shows only the product purchased in the last 30 days, I'm sure it can´t be impossible to do :blush:

 

Can somebody help me sorting it out?

 

Thanks

Link to comment
Share on other sites

The change we implemented (may have been from another contrib... don't remember if we had to code it up ourselves. This addition lets you select a start and end date for the query.

 

I found at least the stats_products_purchased.php file... I am pasting in the whole thing, make sure to backup before you edit, use at your own risk, blah, blah, blah:

 

<?php

/*
 $Id: stats_products_purchased.php,v 1.29 2003/06/29 22:50:52 hpdl Exp $

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

 Copyright © 2003 osCommerce

 Released under the GNU General Public License */

 require('includes/application_top.php');

 if (isset($HTTP_GET_VARS['start_date'])) {
   $start_date = $HTTP_GET_VARS['start_date'];
 } else {
   $start_date = date('Y-m-01');
 }

 if (isset($HTTP_GET_VARS['end_date'])) {
   $end_date = $HTTP_GET_VARS['end_date'];
 } else {
   $end_date = date('Y-m-d');
 }
 if(isset($_GET['keywords']) && $_GET['keywords'] != ''){
 	$keywords = trim($_GET['keywords']);
 }
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> <script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php
 if ($printable != 'on') {
 require(DIR_WS_INCLUDES . 'header.php');
 }; ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
<?php 
  if ($printable != 'on') {;?>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php
require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
       </table>
<?php }; ?>
</td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
           <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
         </tr>
       </table></td>
     </tr>
<tr><td>
<?php     echo tep_draw_form('date_range','stats_products_purchased.php' , '', 'get');
?>
<table>
<tr><td class="main">
<?php
   echo ENTRY_STARTDATE . tep_draw_input_field('start_date', $start_date);
   echo ' '.ENTRY_TODATE . tep_draw_input_field('end_date', $end_date). ' ';
   echo ' </tr><tr><td class="main"> ';
echo ENTRY_KEYWORDS. tep_draw_input_field('keywords', $keywords). ' ';
   echo ENTRY_PRINTABLE . tep_draw_checkbox_field('printable', $print). ' ';
   echo ENTRY_SORTGROSS . tep_draw_checkbox_field('gross', $gross). ' ';
   echo '<input type="submit" value="'. ENTRY_SUBMIT .'">';
   echo '</td>';

   $totalgross = 0;
?>
</td></tr>
</table></form></td></tr>

     <tr>
       <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td>
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_MODEL; ?></td>
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
               <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PURCHASED; ?> </td>
               <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_GROSS; ?> </td>
         </tr>
<?php
if ($gross =='on') {
    $products_query_raw = "select op.products_id, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum , sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op WHERE o.date_purchased BETWEEN '" . $start_date . "' AND '" . $end_date . " 23:59:59' AND o.orders_id = op.orders_id ".(isset($keywords) ? "AND (op.products_name LIKE '%".$keywords."%' OR op.products_model LIKE '%".$keywords."%') " : '')."GROUP BY op.products_id ORDER BY gross DESC,quantitysum DESC, op.products_model";
  } else {
    $products_query_raw = "select op.products_id, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum , sum(op.products_price*op.products_quantity)as gross FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op WHERE o.date_purchased BETWEEN '" . $start_date . "' AND '" . $end_date . " 23:59:59' AND o.orders_id = op.orders_id ".(isset($keywords) ? "AND (op.products_name LIKE '%".$keywords."%' OR op.products_model LIKE '%".$keywords."%') " : '')."GROUP BY op.products_id ORDER BY quantitysum DESC, op.products_model";
  }

 $rows = 0;
 $products_query = tep_db_query($products_query_raw);

 while ($products = tep_db_fetch_array($products_query)) {
   $rows ++;

   $totalgross = $totalgross + $products['gross']; 


   if(strlen($rows) < 2) {
    $rows = '0' . $rows;
   }
?>
               <tr bgcolor="<?php echo ((++$cnt)%2==0) ? '#e0e0e0' : '#ffffff' ?>">
               <td class="dataTableContent"><?php echo $rows  ; ?>.</td>
               <td class="dataTableContent"><?php echo $products['products_model']; ?>
               <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only&pID=' . $products['products_id'] . '&origin=' . FILENAME_STATS_PRODUCTS_PURCHASED . '?page=' . $HTTP_GET_VARS['page'], 'NONSSL') . '">' . $products['products_name'] . '</a>'; ?></td>
               <td class="dataTableContent" align="center"><?php echo $products['quantitysum']; ?> </td>
               <td class="dataTableContent" align="right"><?php echo sprintf("%01.2f", $products['gross']); ?> </td>
             </tr>
<?php
 }
?>
           <tr><td></td><td></td><td class="dataTableContent" align="right"><b><?php echo(ENTRY_TOTAL) ?>:</b></td><td></td><td class="dataTableContent" align="right"><b><?php echo sprintf("%01.2f", $totalgross); ?></b></td></tr> 
           </table></td>
         </tr>
         <tr>
           <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">

           </table></td>
         </tr>
       </table></td>
     </tr>
   </table></td>
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php
 if ($printable != 'on') {
  require(DIR_WS_INCLUDES . 'footer.php');
 }
?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

 

Here is the new language file, the whole thing again:

 

<?php
/*
 $Id: stats_products_purchased.php, v1.5 2002/03/30 15:52:31 harley_vb 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
*/

define('HEADING_TITLE', '<font color="0000FF">Products Purchased Report</font>');
define('ENTRY_STARTDATE', 'Start Date:');
define('ENTRY_TODATE', 'End Date:');
define('ENTRY_KEYWORDS', '<font color="0000FF">Keywords:</font>');
define('ENTRY_PRINTABLE', '<font color="0000FF">Printable: </font>');
define('ENTRY_SORTGROSS', '<font color="0000FF">Sort by Gross:</font>');
define('ENTRY_SUBMIT', 'Submit');

define('TEXT_ORDERS_STATUS', ' > All Orders < ');
define('TEXT_SELECT_MANUFACTURER', '> All Manufacturers <');

define('TABLE_HEADING_NUMBER', '<font color="0000FF">No.</font>');
define('TABLE_HEADING_MODEL', '<font color="0000FF">Model</font>');
define('TABLE_HEADING_PRODUCTS', '<font color="0000FF">Product Name</font>');
define('TABLE_HEADING_PURCHASED', '<font color="0000FF">Purchased</font>');
define('TABLE_HEADING_GROSS', '<font color="0000FF">Gross</font>');
?>

 

There may have been an sql change, but do not know for sure, try this and let me know if you have trouble.

Link to comment
Share on other sites

Hi Rebecca,

 

Is the second [ code] you shared related to the /admin/languages.php file, isnt it?

 

My Report page was updated, very useful contrib!

 

Problem is that the best seller ranking that my users get when seing my store still reflects the complete best sellers list - not that one by period.

 

Maybe I need to edit the files best_sellers.php and best_sellers_scroll.php under the folders /includes/boxes/ ?

 

do you know how I can adjust it?

 

Thank you!

Link to comment
Share on other sites

Second code is for the admin/includes/languages/english/stats_products_purchased.php language file.

 

I have never changed the best sellers on the catalog side... probably a good change. I will see if one of our customers wants to implement it and see if I can get it on my to do list for next week, otherwise I will have to work it into my free time.

 

-R

 

Hi Rebecca,

 

Is the second [ code] you shared related to the /admin/languages.php file, isnt it?

 

My Report page was updated, very useful contrib!

 

Problem is that the best seller ranking that my users get when seing my store still reflects the complete best sellers list - not that one by period.

 

Maybe I need to edit the files best_sellers.php and best_sellers_scroll.php under the folders /includes/boxes/ ?

 

do you know how I can adjust it?

 

Thank you!

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...