documentation/zencart/stats__products__purchased_8php_source.html

00001 <?php
00010   require('includes/application_top.php');
00011
00012   $products_filter = (isset($_GET['products_filter']) ? $_GET['products_filter'] : $products_filter);
00013   $products_filter = str_replace(' ', ',', $products_filter);
00014   $products_filter = str_replace(',,', ',', $products_filter);
00015   $products_filter_name_model = (isset($_GET['products_filter_name_model']) ? $_GET['products_filter_name_model'] : $products_filter_name_model);
00016 ?>
00017 <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
00018 <html <?php echo HTML_PARAMS; ?>>
00019 <head>
00020 <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
00021 <title><?php echo TITLE; ?></title>
00022 <link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
00023 <link rel="stylesheet" type="text/css" media="print" href="includes/stylesheet_print.css">
00024 <link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
00025 <script language="javascript" src="includes/menu.js"></script>
00026 <script language="javascript" src="includes/general.js"></script>
00027 <script type="text/javascript">
00028   <!--
00029   function init()
00030   {
00031     cssjsmenu('navbar');
00032     if (document.getElementById)
00033     {
00034       var kill = document.getElementById('hoverJS');
00035       kill.disabled = true;
00036     }
00037   }
00038   // -->
00039 </script>
00040 </head>
00041 <body onload="init()">
00042 <!-- header //-->
00043 <div class="header-area">
00044 <?php require(DIR_WS_INCLUDES . 'header.php'); ?>
00045 </div>
00046 <!-- header_eof //-->
00047
00048 <!-- body //-->
00049 <table border="0" width="100%" cellspacing="2" cellpadding="2">
00050   <tr>
00051 <!-- body_text //-->
00052     <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
00053       <tr>
00054         <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
00055           <tr>
00056             <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
00057             <td class="pageHeading" align="right"><?php echo zen_draw_separator('pixel_trans.gif', 1, HEADING_IMAGE_HEIGHT); ?></td>
00058             <td class="smallText" align="right">
00059 <?php
00060 // show reset search
00061     echo zen_draw_form('search', FILENAME_STATS_PRODUCTS_PURCHASED, '', 'get', '', true);
00062     echo HEADING_TITLE_SEARCH_DETAIL_REPORTS . ' ' . zen_draw_input_field('products_filter') . zen_hide_session_id();
00063     if (isset($products_filter) && zen_not_null($products_filter)) {
00064       $products_filter = preg_replace('/[^0-9,]/', '', $products_filter);
00065       $products_filter = zen_db_input(zen_db_prepare_input($products_filter));
00066       echo '<br/ >' . TEXT_INFO_SEARCH_DETAIL_FILTER . $products_filter;
00067     }
00068     if (isset($products_filter) && zen_not_null($products_filter)) {
00069       echo '<br/ >' . '<a href="' . zen_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '">' . zen_image_button('button_reset.gif', IMAGE_RESET) . '</a>&nbsp;&nbsp;';
00070     }
00071     echo '</form>';
00072
00073 // show reset search
00074     echo zen_draw_form('search', FILENAME_STATS_PRODUCTS_PURCHASED, '', 'get', '', true);
00075     echo '<br/ >' . HEADING_TITLE_SEARCH_DETAIL_REPORTS_NAME_MODEL . ' ' . zen_draw_input_field('products_filter_name_model') . zen_hide_session_id();
00076     if (isset($products_filter_name_model) && zen_not_null($products_filter_name_model)) {
00077       $products_filter_name_model = zen_db_input(zen_db_prepare_input($products_filter_name_model));
00078       echo '<br/ >' . TEXT_INFO_SEARCH_DETAIL_FILTER . zen_db_prepare_input($products_filter_name_model);
00079     }
00080     if (isset($products_filter_name_model) && zen_not_null($products_filter_name_model)) {
00081       echo '<br/ >' . '<a href="' . zen_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '">' . zen_image_button('button_reset.gif', IMAGE_RESET) . '</a>&nbsp;&nbsp;';
00082     }
00083     echo '</form>';
00084 ?>
00085             </td>
00086           </tr>
00087         </table></td>
00088       </tr>
00089 <?php
00090 if ($products_filter > 0 or $products_filter_name_model != '') {
00091   if ($products_filter > 0) {
00092     // by products_id
00093     $chk_orders_products_query = "SELECT o.customers_id, op.orders_id, op.products_id, op.products_quantity, op.products_name, op.products_model,
00094                                   o.customers_name, o.customers_company, o.customers_email_address, o.date_purchased
00095                                   FROM " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op
00096                                   WHERE op.products_id in (" . $products_filter . ")
00097                                   and op.orders_id = o.orders_id
00098                                   ORDER by op.products_id, o.date_purchased DESC";
00099   } else {
00100     // by products name or model
00101     $chk_orders_products_query = "SELECT o.customers_id, op.orders_id, op.products_id, op.products_quantity, op.products_name, op.products_model,
00102                                   o.customers_name, o.customers_company, o.customers_email_address, o.date_purchased
00103                                   FROM " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op
00104                                   WHERE ((op.products_model LIKE '%" . $products_filter_name_model . "%')
00105                                   or (op.products_name LIKE '%" . $products_filter_name_model . "%'))
00106                                   and op.orders_id = o.orders_id
00107                                   ORDER by op.products_id, o.date_purchased DESC";
00108 }
00109   $chk_orders_products_query_numrows='';
00110   $chk_orders_products_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $chk_orders_products_query, $chk_orders_products_query_numrows);
00111
00112   $rows = 0;
00113   $chk_orders_products = $db->Execute($chk_orders_products_query);
00114 ?>
00115       <tr>
00116         <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
00117           <tr>
00118             <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
00119               <tr class="dataTableHeadingRow">
00120                 <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS_ID; ?></td>
00121                 <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_ORDERS_ID; ?></td>
00122                 <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_ORDERS_DATE_PURCHASED; ?></td>
00123                 <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS_INFO; ?></td>
00124                 <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PRODUCTS_QUANTITY; ?>&nbsp;</td>
00125                 <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PRODUCTS_NAME; ?>&nbsp;</td>
00126                 <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PRODUCTS_MODEL; ?>&nbsp;</td>
00127               </tr>
00128
00129 <?php
00130   if ($chk_orders_products->EOF) {
00131 ?>
00132               <tr class="dataTableRowSelectedBot">
00133                 <td colspan="7" class="dataTableContent" align="center"><?php echo NONE; ?></td>
00134               </tr>
00135 <?php } ?>
00136 <?php
00137   while (!$chk_orders_products->EOF) {
00138     $rows++;
00139
00140     if (strlen($rows) < 2) {
00141       $rows = '0' . $rows;
00142     }
00143     if ($products_filter != '') {
00144     // products_id
00145       $cPath = zen_get_product_path($products_filter);
00146     } else {
00147     // products_name or products_model
00148       $cPath = zen_get_product_path($chk_orders_products->fields['products_id']);
00149     }
00150 ?>
00151               <tr class="dataTableRow">
00152                 <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_CUSTOMERS, zen_get_all_get_params(array('cID', 'action', 'page', 'products_filter')) . 'cID=' . $chk_orders_products->fields['customers_id'] . '&action=edit', 'NONSSL') . '">' . $chk_orders_products->fields['customers_id'] . '</a>'; ?></td>
00153                 <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_ORDERS, zen_get_all_get_params(array('oID', 'action', 'page', 'products_filter')) . 'oID=' . $chk_orders_products->fields['orders_id'] . '&action=edit', 'NONSSL') . '">' . $chk_orders_products->fields['orders_id'] . '</a>'; ?></td>
00154                 <td class="dataTableContent"><?php echo zen_date_short($chk_orders_products->fields['date_purchased']); ?></td>
00155                 <td class="dataTableContent"><?php echo $chk_orders_products->fields['customers_name'] . ($chk_orders_products->fields['customers_company'] !='' ? '<br />' . $chk_orders_products->fields['customers_company'] : '') . '<br />' . $chk_orders_products->fields['customers_email_address']; ?></td>
00156                 <td class="dataTableContent" align="center"><?php echo $chk_orders_products->fields['products_quantity']; ?>&nbsp;</td>
00157                 <td class="dataTableContent" align="center"><?php echo '<a href="' . zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products_filter) . '">' . $chk_orders_products->fields['products_name'] . '</a>'; ?>&nbsp;</td>
00158                 <td class="dataTableContent" align="center"><?php echo $chk_orders_products->fields['products_model']; ?>&nbsp;</td>
00159
00160               </tr>
00161 <?php
00162     $chk_orders_products->MoveNext();
00163   }
00164 ?>
00165             </table></td>
00166           </tr>
00167           <tr>
00168             <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
00169               <tr>
00170                 <td class="smallText" valign="top"><?php echo $chk_orders_products_split->display_count($chk_orders_products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
00171                 <td class="smallText" align="right"><?php echo $chk_orders_products_split->display_links($chk_orders_products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page'], zen_get_all_get_params(array('page', 'x', 'y'))); ?>&nbsp;</td>
00172               </tr>
00173             </table></td>
00174           </tr>
00175
00176 <?php
00177 } else {
00178 // all products by name and quantity display
00179 ?>
00180       <tr>
00181         <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
00182           <tr>
00183             <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
00184               <tr class="dataTableHeadingRow">
00185                 <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td>
00186                 <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PRODUCTS; ?></td>
00187                 <td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_PURCHASED; ?>&nbsp;</td>
00188               </tr>
00189 <?php
00190   if (isset($_GET['page']) && ($_GET['page'] > 1)) $rows = $_GET['page'] * MAX_DISPLAY_SEARCH_RESULTS_REPORTS - MAX_DISPLAY_SEARCH_RESULTS_REPORTS;
00191 // The following OLD query only considers the "products_ordered" value from the products table.
00192 // Thus this older query is somewhat deprecated
00193   $products_query_raw = "select p.products_id, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $_SESSION['languages_id']. "' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name";
00194
00195 // The new query uses real order info from the orders_products table, and is theoretically more accurate.
00196 // To use this newer query, remove the "1" from the following line ($products_query_raw1 becomes $products_query_raw )
00197     $products_query_raw1 =
00198       "select sum(op.products_quantity) as products_ordered, pd.products_name, op.products_id
00199       from ".TABLE_ORDERS_PRODUCTS." op
00200       left join " . TABLE_PRODUCTS_DESCRIPTION . " pd
00201         on (pd.products_id = op.products_id )
00202       where pd.language_id = '" . $_SESSION['languages_id']. "'
00203       group by pd.products_id
00204       order by products_ordered DESC, products_name";
00205
00206   $products_query_numrows='';
00207   $products_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $products_query_raw, $products_query_numrows);
00208
00209   $rows = 0;
00210   $products = $db->Execute($products_query_raw);
00211   while (!$products->EOF) {
00212     $rows++;
00213
00214     if (strlen($rows) < 2) {
00215       $rows = '0' . $rows;
00216     }
00217     $cPath = zen_get_product_path($products->fields['products_id']);
00218 ?>
00219               <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href='<?php echo zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id'] . '&page='); ?>'">
00220                 <td class="dataTableContent" align="right"><?php echo '<a href="' . zen_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, zen_get_all_get_params(array('oID', 'action', 'page', 'products_filter')) . 'products_filter=' . $products->fields['products_id']) . '">' . $products->fields['products_id'] . '</a>'; ?>&nbsp;&nbsp;</td>
00221                 <td class="dataTableContent"><?php echo '<a href="' . zen_href_link(FILENAME_CATEGORIES, 'cPath=' . $cPath . '&pID=' . $products->fields['products_id'] . '&page=') . '">' . $products->fields['products_name'] . '</a>'; ?></td>
00222                 <td class="dataTableContent" align="center"><?php echo $products->fields['products_ordered']; ?>&nbsp;</td>
00223               </tr>
00224 <?php
00225     $products->MoveNext();
00226   }
00227 ?>
00228             </table></td>
00229           </tr>
00230           <tr>
00231             <td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
00232               <tr>
00233                 <td class="smallText" valign="top"><?php echo $products_split->display_count($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
00234                 <td class="smallText" align="right"><?php echo $products_split->display_links($products_query_numrows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page']); ?>&nbsp;</td>
00235               </tr>
00236             </table></td>
00237           </tr>
00238 <?php
00239 } // $products_filter > 0
00240 ?>
00241         </table></td>
00242       </tr>
00243     </table></td>
00244 <!-- body_text_eof //-->
00245   </tr>
00246 </table>
00247 <!-- body_eof //-->
00248
00249 <!-- footer //-->
00250 <div class="footer-area">
00251 <?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
00252 </div>
00253 <!-- footer_eof //-->
00254 </body>
00255 </html>
00256 <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>