Jump to content



Photo
- - - - -

Add name in contrib Sales Report Between Two Dates

sales report between two date

  • Please log in to reply
No replies to this topic

#1   abdelatif

abdelatif
  • Members
  • 15 posts

Posted 13 March 2012 - 09:31

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();\'') . '&nbsp;Maand: ' . tep_draw_pull_down_menu('month', $months, $month, 'onchange=\'this.form.submit();\'') . '&nbsp;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();\'') . '&nbsp;Maand: ' . tep_draw_pull_down_menu('month2', $months2, $month22, 'onchange=\'this.form.submit();\'') . '&nbsp;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>&nbsp;&nbsp;
			<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; ?>&nbsp;</td>
								<td class="dataTableHeadingContent" align="center" width="15%"><?php echo TABLE_HEADING_TOTAL_TAX; ?>&nbsp;</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); ?>&nbsp;</td>
								<td class="dataTableContent" align=right><?php echo $currencies->display_price($sales_products ['withtax'],0); ?>&nbsp;</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'); ?>