list all customers who bought a product

opensrc · January 30, 2004

Hello...

I'm looking for a way to make a report that shows all customers who bought a product. I would also like it to show how many each customer bought, their name, and order number. I am selling tickets and need to make this list of people who bought tickets (product).

Also any way of entering a cut off time for selling a product?

Thanks!

January 30, 2004

It wouldn't be too hard...take a look at the reports that exist already, copy to a new file and modify to suit your needs.

opensrc · January 30, 2004

Thanks Luc's Music...

Any idea or link to an example of doing something like this? I'm new to OSC and need an example...

January 30, 2004

I don't know of an example...but I would do something like this.

Find the file that creates the report for "customers orders total" that's included in the standard osC package.

Make a copy and rename it to something else.

Find the line about:

$variable = tep_db_query(blahblahblah)

Change it to:

$varialbe = tep_db_query(blahblahblah where products_id = <product id of the product you need>);

Then find the section where it echoes the information about the orders and add in a quantity field and an order number field if that's necessary.

You could make it even more suave by creating a dialog to ask the user which product he wants a report on, then using the variable in your database query.

I'm really just shooting from the hip here, I haven't looked at the code nearly enough (not at all, actually) to give you sound advice. That's a starting point, though.

opensrc · February 4, 2004

Luc's Music Thanks! That got me rolling in the right direction.....

this is what I did:

in admin/stats_products_purchased.php:

I added....

  
<?php echo '<a href="' . tep_href_link(FILENAME_STATS_WILLCALL, 'pID=' . $products['products_id'] . '&product_title=' . $products['products_name'] . '&origin=' . FILENAME_STATS_PRODUCTS_PURCHASED . '?page=' . $HTTP_GET_VARS['page'], 'NONSSL') . '">view Will Call</a>'; ?>

after the product name - this gives a link to a new page I made that will show all products purchased by customers...

new page admin/stats_willcall.php:

<?php
/*
 $Id: stats_customers.php,v 1.31 2003/06/29 22:50:52 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

 require(DIR_WS_CLASSES . 'currencies.php');
 $currencies = new currencies();
?>
<!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 require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <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></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">Will Call: <?php echo $HTTP_GET_VARS['product_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><table border="0" width="100%" cellspacing="0" cellpadding="2">
         <tr>
           <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
             <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent">Pick up</td>
               <td class="dataTableHeadingContent">Name</td>
   <td class="dataTableHeadingContent">Order Number</td>
               <td class="dataTableHeadingContent" align="right">Price </td>
             </tr>
<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 // query to group all customers
 //$customers_query_raw = "select c.customers_firstname, c.customers_lastname, op.orders_id, sum(op.products_quantity) as ordertotal, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id and op.products_id = ".$HTTP_GET_VARS['pID']." group by c.customers_firstname, c.customers_lastname order by c.customers_lastname ASC";
 // query to list all orders
 $customers_query_raw = "select c.customers_firstname, c.customers_lastname, op.orders_id, op.products_quantity as ordertotal, op.products_quantity * op.final_price as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id and op.products_id = ".$HTTP_GET_VARS['pID']." order by c.customers_lastname, c.customers_firstname ASC";
 $customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows);
// fix counted customers
 //$customers_query_numrows = tep_db_query("select customers_id from " . TABLE_ORDERS . " group by customers_id");
 //$customers_query_numrows = tep_db_num_rows($customers_query_numrows);

 $rows = 0;
 $customers_query = tep_db_query($customers_query_raw);
 while ($customers = tep_db_fetch_array($customers_query)) {
   $rows++;

   if (strlen($rows) < 2) {
     $rows = '0' . $rows;
   }
?>
             <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL'); ?>'">
               <td class="dataTableContent">[   ]   <?php echo $customers['ordertotal']; ?></td>
               <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL') . '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>
               <td class="dataTableContent"><?php echo $customers['orders_id']; ?> </td>
   <td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td>
             </tr>
<?php
 }
?>
           </table></td>
         </tr>
       </table></td>
     </tr>
   </table></td>
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

remember to update admin/includes/filenames.php and add this entry:

define('FILENAME_STATS_WILLCALL', 'stats_willcall.php');

That should give you a list of all the people who bought a product along with the order number and total quantity ordered (if someone has three orders buying the same product they will show up three times - if you want each customer displayed once - switch query in stats_willcall.php to group all customer orders)

If anyone improves on this or has ideas let me know! And hopefully this will help other people....

Thanks....

swrawls · May 13, 2004

Thanks for this wonderful addition. I really needed this report.

FYI: I had to make some modifications and get rid of the 'Page Splitting" part of the code, because the report didn't have the Next Page links on the bottom as all my other reports do. When I tried to add the code, it didn't work. I got an error.

Wither way...for this report, i'd rather have all names listed on one single page anyhow. Easier to print.

- Shannon

list all customers who bought a product

Recommended Posts

opensrc

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

opensrc

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

opensrc

Link to comment

Share on other sites

swrawls

Link to comment

Share on other sites

Archived

Browse

Activity