Modifying Product Pruchased report....

reggiewjr1 · May 21, 2009

I'm using a modified version of the Products Purchased Report with Date Range contrib

I've trimmed out anything to do with costs as this particular osC store is a fulfillment center not a 'store' in the stricter sense of the word.

What I would like to do now is to modify this so that when you click on an item in the report it shows a list of all the orders w/ customer name, date, etc (same basic layout as the Customer>Orders page basically) while still maintaining the the parameters set in the form at the top.

Here is the current code I am using. (cont. below code section)

<?php
/*
 $Id: stats_products_purchased.php, v1.29 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();

 if ($_GET['month'] == '') {
$month = 0;
$year = 0;
 } else {
$month = $_GET['month'];
$year = $_GET['year'];
 }

 if(tep_not_null($_GET['gross']))
  $gross = $_GET['gross'];

 $months = array();
 $months[] = array('id' => 0, 'text' => TEXT_SELECT_MONTH);
 $months[] = array('id' => 1, 'text' => 'January');
 $months[] = array('id' => 2, 'text' => 'February');
 $months[] = array('id' => 3, 'text' => 'March');
 $months[] = array('id' => 4, 'text' => 'April');
 $months[] = array('id' => 5, 'text' => 'May');
 $months[] = array('id' => 6, 'text' => 'June');
 $months[] = array('id' => 7, 'text' => 'July');
 $months[] = array('id' => 8, 'text' => 'August');
 $months[] = array('id' => 9, 'text' => 'September');
 $months[] = array('id' => 10, 'text' => 'October');
 $months[] = array('id' => 11, 'text' => 'November');
 $months[] = array('id' => 12, 'text' => 'December');

 $years = array();

 $years[] = array('id' => 0, 'text' => TEXT_SELECT_YEAR);
 $years[] = array('id' => 2005, 'text' => '2005');
 $years[] = array('id' => 2006, 'text' => '2006');
 $years[] = array('id' => 2007, 'text' => '2007');
 $years[] = array('id' => 2008, 'text' => '2008');
 $years[] = array('id' => 2009, 'text' => '2009');
 $years[] = array('id' => 2010, 'text' => '2010');
 $years[] = array('id' => 2011, 'text' => '2011');
 $years[] = array('id' => 2012, 'text' => '2012');

 $status = (int)$_GET['status'];

 $statuses_query = tep_db_query("select * from orders_status where language_id = '" . (int)$languages_id . "' order by orders_status_id");
 $statuses = array();
 $statuses[] = array('id' => 0, 'text' => TEXT_ORDERS_STATUS);
 while ($st = tep_db_fetch_array($statuses_query)) {
 $statuses[] = array('id' => $st['orders_status_id'], 'text' => $st['orders_status_name']);
 }


 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>
<br>
<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" onLoad="SetFocus();">
<!-- header //-->
<?php
 if ($printable != 'on') {
 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>

<!-- body_text //-->
<td width="100%" valign="top"><table border="0" align="center" width="95%" 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>
	  <table border="0" align="left" width="100%" cellspacing="0" cellpadding="2"><?php echo tep_draw_form('date_range', FILENAME_STATS_PRODUCTS_PURCHASED, '', 'get'); ?><?php echo tep_hide_session_id(); ?>
		<tr>
		  <td class="smallText">

<?php
$manufacturers_query = tep_db_query("select manufacturers_id, manufacturers_name from " . TABLE_MANUFACTURERS . " order by manufacturers_name");
 $manufacturers_array = array();
 $manufacturers_array[] = array('id' => '0', 'text' => TEXT_SELECT_MANUFACTURER);
  while ($manufacturers = tep_db_fetch_array($manufacturers_query)) {
	$manufacturers_name = $manufacturers['manufacturers_name'];
	$manufacturers_array[] = array('id' => $manufacturers['manufacturers_id'],
								   'text' => $manufacturers_name);
}	

echo ENTRY_KEYWORDS . '  ' . tep_draw_input_field('keywords', $keywords, 'size="20"');
echo  '   ' . '<a href="java script:document.forms[\'date_range\'].submit();">Search ></a>';
echo '<br><br>';

echo ENTRY_YEAR . '  ' . tep_draw_pull_down_menu('year', $years, $year, 'onchange=\'this.form.submit();\'') . '    ';

echo ENTRY_MONTH . '  ' . tep_draw_pull_down_menu('month', $months, $month, 'onchange=\'this.form.submit();\'') . '    ';	

echo ENTRY_STATUS . '  ' . tep_draw_pull_down_menu('status', $statuses, $status, 'onchange=\'this.form.submit();\'') . '    ';

echo ENTRY_MANUFACTURER . '  ' . tep_draw_pull_down_menu('manufacturers_id', $manufacturers_array, (isset($_GET['manufacturers_id']) ? $_GET['manufacturers_id'] : ''), 'onChange="this.form.submit();" size="1"') . '   ';

echo ' ' . ENTRY_PRINTABLE . tep_draw_checkbox_field('printable', $print) . '   ';


echo '</td></form>';


$totalquantity = 0;
?>

	  </tr>
	</table></td>
  </tr>

  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></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"></td>
			<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
		   <td class="dataTableHeadingContent"></td>
			<td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PURCHASED; ?> </td>
			<td class="dataTableHeadingContent" align="right"></td>
	  </tr>
<?php

// generate query string
$products_query_raw = "select op.products_id, manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE ";

if($month > 0)
	$products_query_raw .= " month(o.date_purchased) = " . $month . " and ";

if($year > 0)
	$products_query_raw .= " year(o.date_purchased) = " . $year . " and ";

if($status > 0)
	$products_query_raw .= "o.orders_status = " . $status . " and ";

$products_query_raw .= " o.orders_id = op.orders_id and op.products_id = p.products_id ";

if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
	$products_query_raw .= " and p.manufacturers_id = m.manufacturers_id ";
	$products_query_raw .= " and p.manufacturers_id = " . $_GET['manufacturers_id'] . " ";
}
else $products_query_raw .= " and p.manufacturers_id = m.manufacturers_id ";

$products_query_raw .=(isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR manufacturers_name LIKE '%" . $keywords . "%' OR manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY ";

$products_query_raw .= " quantitysum DESC";

if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
	$products_query_raw .= " , op.products_model ";
}
// end of generate query string

 if ($debug) echo $products_query_raw;

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

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

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

if (strlen($rows) < 2) {
 $rows = '0' . $rows;
}
?>
		  <tr bgcolor="<?php echo ((++$cnt)%2 == 0) ? '#E0E0E0' : '#FFFFFF' ?>" id="defaultSelected" class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)">
		  <td class="dataTableContent"> <?php echo $rows; ?>.</td>
  <td class="dataTableContent"></td>
		  <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=' . (int)$_GET['page']) . '">' . $products['products_name'] . '</a>'; ?></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent" align="center"><?php echo $products['quantitysum']; ?> </td>
		  <td class="dataTableContent" align="right"></td>
		</tr>
<?php
  }
?>

		<tr>
		  <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
		</tr>

		<tr>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent" align="left"><b><?php echo ENTRY_TOTAL . ' ' . $totalquantity; ?></b>  </td>
		  <td class="dataTableContent" align="right"></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 //-->

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

What I was thinking I could do was to just add:

c.customers_firstname, c.customers_lastname, c.customers_id, c.customers_company, o.customers_id

to:

	// generate query string
$products_query_raw = "select op.products_id, manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE ";

Which would become:

	// generate query string
$products_query_raw = "select c.customers_firstname, c.customers_lastname, c.customers_id, c.customers_company, o.customers_id, op.products_id, manufacturers_name, op.products_model, op.products_name, sum(op.products_quantity) as quantitysum FROM " . TABLE_ORDERS . " as o, " . TABLE_ORDERS_PRODUCTS . " AS op, " . TABLE_MANUFACTURERS . " as m, " . TABLE_PRODUCTS . " as p WHERE ";

I would then need to 'atttach' the customer info to each record in the 'Where' clause of course:

	if($month > 0)
	$products_query_raw .= " month(o.date_purchased) = " . $month . " and ";

if($year > 0)
	$products_query_raw .= " year(o.date_purchased) = " . $year . " and ";

if($status > 0)
	$products_query_raw .= "o.orders_status = " . $status . " and ";

$products_query_raw .= " o.orders_id = op.orders_id and op.products_id = p.products_id and c.customers_id = o.customers_id ";

if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
	$products_query_raw .= " and p.manufacturers_id = m.manufacturers_id ";
	$products_query_raw .= " and p.manufacturers_id = " . $_GET['manufacturers_id'] . " ";
}
else $products_query_raw .= " and p.manufacturers_id = m.manufacturers_id ";

$products_query_raw .=(isset($keywords) ? " AND (op.products_name LIKE '%" . $keywords . "%' OR op.products_model LIKE '%" . $keywords . "%' OR manufacturers_name LIKE '%" . $keywords . "%' OR manufacturers_name LIKE '%" . $keywords . "%') " : '') . " GROUP BY op.products_id ORDER BY ";

$products_query_raw .= " quantitysum DESC";

if (isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
	$products_query_raw .= " , op.products_model ";
}
// end of generate query string

Am I on the right track so far?

Where I get stuck is on the next two pieces of the puzzle.... (I know this is not the right syntax hence my request for help)

i need to add an

If(isset($_GET['Pid'] && $_GET['Pid'] > 0) {
	$products_query_raw .= " , op.products_id = $Pid ";
}

in there somewhere. I also need to update the link on this:

			  <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=' . (int)$_GET['page']) . '">' . $products['products_name'] . '</a>'; ?></td>
		  <td class="dataTableContent"></td>

so that it returns me to this page to update the query to single out the product requested instead (using what I am calling the $Pid) of to a 'preview' of the product listing itself.

After that, I will need to IF ELSE this whole section

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

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

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

if (strlen($rows) < 2) {
 $rows = '0' . $rows;
}
?>
		  <tr bgcolor="<?php echo ((++$cnt)%2 == 0) ? '#E0E0E0' : '#FFFFFF' ?>" id="defaultSelected" class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)">
		  <td class="dataTableContent"> <?php echo $rows; ?>.</td>
  <td class="dataTableContent"></td>
		  <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=' . (int)$_GET['page']) . '">' . $products['products_name'] . '</a>'; ?></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent" align="center"><?php echo $products['quantitysum']; ?> </td>
		  <td class="dataTableContent" align="right"></td>
		</tr>
<?php
  }
?>

		<tr>
		  <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '5'); ?></td>
		</tr>

		<tr>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent"></td>
		  <td class="dataTableContent" align="left"><b><?php echo ENTRY_TOTAL . ' ' . $totalquantity; ?></b>  </td>
		  <td class="dataTableContent" align="right"></td>

		  </tr>

and replace it with a list of the orders if / when a specific product is chosen....

I think my thought process is 1/2 way there, anyone have any advice or can help me get this done...?

Modifying Product Pruchased report....

Recommended Posts

reggiewjr1

Link to comment

Share on other sites

Archived

Browse

Activity