Search the Community

I am using the contribution Sales Report Between Two Dates. You can find the contribution here On a Oscommerce Rc2a shop, the contrib works like a charm, but I would like to add the customers name to the report Unfortunately no luck eversince. I think it should be add in the file: catalog/admin/stats_sales.php Any-one?? <?php /* Sales Report Between Two Dates originally posted by Dhiman Pawan 7/15/2006 URL: www.oscommerce.com/community/contributions,4389 Updated by Lemon Yellow 9/13/2006 Further Updates: 2009-Sep-21 Monty Wilson -- corrected error where sales on the last day of specified date range were not listed unless sale was made in first second of the day. Also corrected error where the "sort by day" and "sort by model" links were broken. Changed message from "this month" to "specified time period," because the date range isn't necessarily a month. Installed a feature where the product model numbers are now links which when clicked will open the regular catalog page so the person reviewing the reports can see further product details such as categories. Changed message from "Product + Code Number" to "Product Model Number" since output shown to the user in this case is sorted by model. Changed format of table so totals are aligned under the net and gross columns. Changed table title from date of first sale to "Products Sold In Specified Time Period" in the sort-by-model case. Installed program header. Installed feature of excluding a status rather than showing a status. 2009-Oct-01 Monty Wilson -- in the "by date" option, added order subtotal, order grand total, and number of orders for the stated period. Known issues: The program is written with a languages-English include file, indicating that it could be internationalized; however, much of the content displayed to the user is hardcoded English. The program allows entry of illegal dates; for example, 29 February in non-leap years, or 31 April. The years allowed to be specified are 2004~2011. */ require('includes/application_top.php'); require(DIR_WS_CLASSES . 'currencies.php'); $currencies = new currencies(); if ($HTTP_GET_VARS['month'] == '') { $day1='1'; $month = date("m")-1; $year = '20' . date("y"); $date1=$year."-".$month."-"."$day1"; $day22 = '1'; $month22 = date("m"); $year22 = '20'.date("y"); $date2=$year22."-".$month22."-".$day22; //echo $date1."<br>"; //echo $date2; } else { $day1 = $HTTP_GET_VARS['day']; $month = $HTTP_GET_VARS['month']; $year = $HTTP_GET_VARS['year']; $date1=$year."-".$month."-"."$day1"; $day22 = $HTTP_GET_VARS['day2']; $month22 = $HTTP_GET_VARS['month2']; $year22 = $HTTP_GET_VARS['year2']; $date2=$year22."-".$month22."-"."$day22"; //echo $date1."<br>"; //echo $date2; } $day = array(); $day[] = array('id' => 1, 'text' => '1'); $day[] = array('id' => 2, 'text' => '2'); $day[] = array('id' => 3, 'text' => '3'); $day[] = array('id' => 4, 'text' => '4'); $day[] = array('id' => 5, 'text' => '5'); $day[] = array('id' => 6, 'text' => '6'); $day[] = array('id' => 7, 'text' => '7'); $day[] = array('id' => 8, 'text' => '8'); $day[] = array('id' => 9, 'text' => '9'); $day[] = array('id' => 10, 'text' => '10'); $day[] = array('id' => 11, 'text' => '11'); $day[] = array('id' => 12, 'text' => '12'); $day[] = array('id' => 13, 'text' => '13'); $day[] = array('id' => 14, 'text' => '14'); $day[] = array('id' => 15, 'text' => '15'); $day[] = array('id' => 16, 'text' => '16'); $day[] = array('id' => 17, 'text' => '17'); $day[] = array('id' => 18, 'text' => '18'); $day[] = array('id' => 19, 'text' => '19'); $day[] = array('id' => 20, 'text' => '20'); $day[] = array('id' => 21, 'text' => '21'); $day[] = array('id' => 22, 'text' => '22'); $day[] = array('id' => 23, 'text' => '23'); $day[] = array('id' => 24, 'text' => '24'); $day[] = array('id' => 25, 'text' => '25'); $day[] = array('id' => 26, 'text' => '26'); $day[] = array('id' => 27, 'text' => '27'); $day[] = array('id' => 28, 'text' => '28'); $day[] = array('id' => 29, 'text' => '29'); $day[] = array('id' => 30, 'text' => '30'); $day[] = array('id' => 31, 'text' => '31'); $months = array(); $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' => 2004, 'text' => '2004'); $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'); $years[] = array('id' => 2013, 'text' => '2013'); $years[] = array('id' => 2014, 'text' => '2014'); $day2 = array(); $day2[] = array('id' => 1, 'text' => '1'); $day2[] = array('id' => 2, 'text' => '2'); $day2[] = array('id' => 3, 'text' => '3'); $day2[] = array('id' => 4, 'text' => '4'); $day2[] = array('id' => 5, 'text' => '5'); $day2[] = array('id' => 6, 'text' => '6'); $day2[] = array('id' => 7, 'text' => '7'); $day2[] = array('id' => 8, 'text' => '8'); $day2[] = array('id' => 9, 'text' => '9'); $day2[] = array('id' => 10, 'text' => '10'); $day2[] = array('id' => 11, 'text' => '11'); $day2[] = array('id' => 12, 'text' => '12'); $day2[] = array('id' => 13, 'text' => '13'); $day2[] = array('id' => 14, 'text' => '14'); $day2[] = array('id' => 15, 'text' => '15'); $day2[] = array('id' => 16, 'text' => '16'); $day2[] = array('id' => 17, 'text' => '17'); $day2[] = array('id' => 18, 'text' => '18'); $day2[] = array('id' => 19, 'text' => '19'); $day2[] = array('id' => 20, 'text' => '20'); $day2[] = array('id' => 21, 'text' => '21'); $day2[] = array('id' => 22, 'text' => '22'); $day2[] = array('id' => 23, 'text' => '23'); $day2[] = array('id' => 24, 'text' => '24'); $day2[] = array('id' => 25, 'text' => '25'); $day2[] = array('id' => 26, 'text' => '26'); $day2[] = array('id' => 27, 'text' => '27'); $day2[] = array('id' => 28, 'text' => '28'); $day2[] = array('id' => 29, 'text' => '29'); $day2[] = array('id' => 30, 'text' => '30'); $day2[] = array('id' => 31, 'text' => '31'); $months2 = array(); $months2[] = array('id' => 1, 'text' => 'January'); $months2[] = array('id' => 2, 'text' => 'February'); $months2[] = array('id' => 3, 'text' => 'March'); $months2[] = array('id' => 4, 'text' => 'April'); $months2[] = array('id' => 5, 'text' => 'May'); $months2[] = array('id' => 6, 'text' => 'June'); $months2[] = array('id' => 7, 'text' => 'July'); $months2[] = array('id' => 8, 'text' => 'August'); $months2[] = array('id' => 9, 'text' => 'September'); $months2[] = array('id' => 10, 'text' => 'October'); $months2[] = array('id' => 11, 'text' => 'November'); $months2[] = array('id' => 12, 'text' => 'December'); $years2 = array(); $years2[] = array('id' => 2004, 'text' => '2004'); $years2[] = array('id' => 2005, 'text' => '2005'); $years2[] = array('id' => 2006, 'text' => '2006'); $years2[] = array('id' => 2007, 'text' => '2007'); $years2[] = array('id' => 2008, 'text' => '2008'); $years2[] = array('id' => 2009, 'text' => '2009'); $years2[] = array('id' => 2010, 'text' => '2010'); $years2[] = array('id' => 2011, 'text' => '2011'); $years2[] = array('id' => 2012, 'text' => '2012'); $years2[] = array('id' => 2013, 'text' => '2013'); $years2[] = array('id' => 2014, 'text' => '2014'); $status = (int)$HTTP_GET_VARS['status']; $statuses_query = tep_db_query("select * from orders_status where language_id = $languages_id order by orders_status_name"); $statuses = array(); $statuses[] = array('id' => 0, 'text' => 'Alle statussen'); while ($st = tep_db_fetch_array($statuses_query)) { $statuses[] = array('id' => $st['orders_status_id'], 'text' => 'Show ' . $st['orders_status_name']); $statuses[] = array('id' => $st['orders_status_id']*(-1), 'text' => 'Exclude ' . $st['orders_status_name']); } if ($status > 0) { $os = " and o.orders_status = " . $status . " "; } elseif ($status < 0) { $os = " and o.orders_status <> " . $status*(-1) . " "; } else { $os = ''; } if ($HTTP_GET_VARS['by']=='date') { $a="select sum(op.final_price*op.products_quantity) as daily_prod, sum(op.final_price*op.products_quantity*(1+op.products_tax/100)) as withtax, o.date_purchased, o.orders_id, op.products_name, op.products_id, sum(op.products_quantity) as qty, op.products_model from orders as o, orders_products as op where o.orders_id = op.orders_id and o.date_purchased between '".$date1."' and '".$date2." 23:59:59' $os GROUP by date_purchased,products_id"; //echo $a; $sales_products_query = tep_db_query($a); } else { $a="select sum(op.final_price*op.products_quantity) as daily_prod, sum(op.final_price*op.products_quantity*(1+op.products_tax/100)) as withtax, o.date_purchased, op.products_name, op.products_id, sum(op.products_quantity) as qty, op.products_model from orders as o, orders_products as op where o.orders_id = op.orders_id and o.date_purchased between '".$date1."' and '".$date2." 23:59:59' $os GROUP by products_id ORDER BY products_model"; //echo $a; $sales_products_query = tep_db_query($a); } ?> <!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"> <form action="stats_sales.php" method=get> <tr> <td class="pageHeading" height="50"><?php echo HEADING_TITLE; ?></td> </tr> <tr> <td colspan="2"class="main" align="left"><b>Start Datum :</b> <?='Dag: ' . tep_draw_pull_down_menu('day', $day, $day1, 'onchange=\'this.form.submit();\'') . ' Maand: ' . tep_draw_pull_down_menu('month', $months, $month, 'onchange=\'this.form.submit();\'') . ' Jaar: ' . tep_draw_pull_down_menu('year', $years, $year, 'onchange=\'this.form.submit();\'')?><br><b>Eind Datum : </b> <?='Dag: ' . tep_draw_pull_down_menu('day2', $day2, $day22, 'onchange=\'this.form.submit();\'') . ' Maand: ' . tep_draw_pull_down_menu('month2', $months2, $month22, 'onchange=\'this.form.submit();\'') . ' Jaar: ' . tep_draw_pull_down_menu('year2', $years2, $year22, 'onchange=\'this.form.submit();\'')?> </td> </tr> <tr> <td class="main" align="left" height="40"><?='<b>Status : </b>' . tep_draw_pull_down_menu('status', $statuses, $status, 'onchange=\'this.form.submit();\'')?></td> </tr> <input type="hidden" name="by" value="<?=$HTTP_GET_VARS['by']?>"> </form> </table></td> </tr> <tr> <td class="main"><b>Sorteer op : </b><a href=<?php echo tep_href_link('../../..'.$PHP_SELF, tep_get_all_get_params(array('by')).'&by=date', 'SSL') ?>><u>Per dag uitgesplitst</u></a> <a href=<?php echo tep_href_link('../../..'.$PHP_SELF, tep_get_all_get_params(array('by')), 'SSL') ?>><u>Lijst weergave</u></a> <br><br><?echo "<b>Start datum : </b>".$date1."<br>"; echo "<b>Eind datum : </b>".$date2;?></td> </tr> <?php //echo $table_title if (tep_db_num_rows($sales_products_query) > 0) { $dp = ''; $total=0; // init products sales total $total_orders = 0; // init orders total $grand_total = 0; // init total of order sales amts $total_wtax=0; while ($sales_products = tep_db_fetch_array($sales_products_query)) { if (($HTTP_GET_VARS['by']=='date')) { $ddp = tep_date_short($sales_products['date_purchased']); $table_title = tep_date_long($sales_products['date_purchased']); if ($cur_order && ($sales_products['orders_id'] != $cur_order)) { //if order number has changed and this is not the start of the // first order, close previous order and print subtotal ?> </td></tr> <tr><td></td><td colspan="3"> Order Total <br> </td><td align="right"> <?php $a="select value from orders_total where orders_id = '". $cur_order."' and class = 'ot_total'"; //echo $a; $order_total_query = tep_db_query($a); $order_total = tep_db_fetch_array($order_total_query); echo $currencies->display_price($order_total['value'],0); $total_orders++; // count orders $grand_total += $order_total['value']; // total of order sales amts ?> </td></tr> <tr> <?php } // end if order number has changed $cur_order = $sales_products['orders_id']; // init cur order } // end if showing by date else { $ddp = 'Products Sold In Specified Time Period'; $table_title = 'Verkochte producten binnen deze tijds periode'; } if (($dp != $ddp)) { //if day has changed (or first day) if ($dp != '') { //close previous day if not first one ?> </table></td> </tr> </table></td> </tr> <tr> <td><br></td> </tr> <?php } ?> <tr> <td class=main><b><?php echo $table_title ; // echo "Start date : ".$date1."<br>"; //echo "End date : ".$date2; ?></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td valign="top"><table border="0" width="60%" cellspacing="1" cellpadding="2"> <tr class="dataTableHeadingRow"> <td class="dataTableHeadingContent" width="15%"><?php echo TABLE_HEADING_MODEL; ?></td> <td class="dataTableHeadingContent" width="40%"><?php echo TABLE_HEADING_NAME; ?></td> <td class="dataTableHeadingContent" align=center width="15%"><?php echo TABLE_HEADING_QUANTITY; ?></td> <td class="dataTableHeadingContent" align="center" width="15%"><?php echo TABLE_HEADING_TOTAL; ?> </td> <td class="dataTableHeadingContent" align="center" width="15%"><?php echo TABLE_HEADING_TOTAL_TAX; ?> </td> </tr> <?php } ?> <tr class="dataTableRow"> <td class="dataTableContent"><?php echo '<a href="../../catalog/product_info.php?products_id=' . $sales_products ['products_id'] . '" target="_blank">' . $sales_products ['products_model']; ?></a></td> <td class="dataTableContent"><?php echo $sales_products ['products_name']; ?></td> <td class="dataTableContent" align=center><?php echo $sales_products ['qty']; ?></td> <td class="dataTableContent" align=right><?php echo $currencies->display_price($sales_products ['daily_prod'],0); ?> </td> <td class="dataTableContent" align=right><?php echo $currencies->display_price($sales_products ['withtax'],0); ?> </td> </tr> <?php $total+=$sales_products ['daily_prod']; $total_wtax+=$sales_products ['withtax']; $dp = $ddp; } // end while (sales_products) if (($HTTP_GET_VARS['by']=='date')) { // close order and print subtotal ?> </td></tr> <tr><td></td><td colspan="3"> Order Totaal <br> </td><td align="right"> <?php $a="select value from orders_total where orders_id = '". $cur_order."' and class = 'ot_total'"; //echo $a; $order_total_query = tep_db_query($a); $order_total = tep_db_fetch_array($order_total_query); echo $currencies->display_price($order_total['value'],0); $total_orders++; // count orders $grand_total += $order_total['value']; // total of order sales amts ?> </td></tr> <tr> <?php echo '<tr><td></td><td colspan="2">Hoeveelheid Orders:</td><td class="main" align="right"> </td><td class="main" align="right">'. $total_orders.'</td></tr>'; echo '<tr><td></td><td colspan="3">Totaal Product verkopen: (inclusief verzendkosten)</td><td class="main" align="right">'. $currencies->display_price($grand_total,0).'</td></tr>'; } // end if showing by date echo '<tr><td></td><td colspan="2">Totaal Product verkopen: (exclusief verzendkosten)</td><td class="main" align="right">'. $currencies->display_price($total,0).'</td><td class="main" align="right">'. $currencies->display_price($total_wtax,0).'</td></tr>'; } else { ?> <tr> <td class=main><b>Er zijn geen verkopen in de gekozen periode.</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'); ?>