<?php
/*
  $Id: stats_monthly_sales.php,v 1.1 2002/12/05 22:07:43 wilt Exp $
  adapted from stats_customers.php
  contributed by Fritz Clapp <osc@sonnybargercycles.com>

This report displays a summary of monthly totals:
	gross income (product sales + tax collected + shipping/handling charges + low order fees)
	product sales (exempt + taxable)
	product sales exempt
	product sales taxable
	sales tax collected
	shipping/handling charges
	low order fees
The data comes from the orders and orders_total tables.

added 1.1:	annual footers for all columns;
			summary prepared for all orders or by status;

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

  Copyright (c) 2002 osCommerce

  Released under the GNU General Public License
*/

  require('includes/application_top.php');

  require(DIR_WS_CLASSES . 'currencies.php');
  $currencies = new currencies();
  $orders_statuses = array();
  $orders_status_array = array();
  $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "'");
  while ($orders_status = tep_db_fetch_array($orders_status_query)) {
    $orders_statuses[] = array('id' => $orders_status['orders_status_id'],
                               'text' => $orders_status['orders_status_name']);
    $orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];
  }
?>
<!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">
</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"><?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>
		    <td align="right">
			<table border="0" width="100%" cellspacing="0" cellpadding="0">
              <tr><?php echo tep_draw_form('status', FILENAME_STATS_MONTHLY_SALES, '', 'get'); ?>
                <td class="smallText" align="right"><?php echo HEADING_TITLE_STATUS . ' ' . tep_draw_pull_down_menu('status', tep_array_merge(array(array('id' => '', 'text' => TEXT_ALL_ORDERS)), $orders_statuses), '', 'onChange="this.form.submit();"'); ?><input type="hidden" name="selected_box" value="reports"></td>
              </form></tr>
             </table></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="2">
          <tr>
            <td valign="top"><table border="0" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent" width='60' align='left' colspan='2'><?php echo TABLE_HEADING_MONTH; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_INCOME; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_SALES; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_NONTAXED; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_TAXED; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_TAX_COLL; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_SHIPHNDL; ?></td>
                <td class="dataTableHeadingContent" width='76' align='right'><?php echo TABLE_HEADING_LOWORDER; ?></td>
              </tr>
<?php
// footer totals
	$ytd_gross = 0;
	$ytd_sales = 0;
	$ytd_sales_exempt = 0;
	$ytd_sales_taxable = 0;
	$ytd_tax_coll = 0;
	$ytd_shiphndl = 0;
	$ytd_loworder = 0;
// order subtotals, the driving force
$status = '';
if ($HTTP_GET_VARS['status']) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
  $sales_query_raw = "select truncate(sum(ot.value),2) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where o.orders_status =" . $status . " and ot.class = 'ot_subtotal' group by year(o.date_purchased), month(o.date_purchased) order by o.date_purchased desc";
	} else {
  $sales_query_raw = "select truncate(sum(ot.value),2) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_subtotal' group by year(o.date_purchased), month(o.date_purchased) order by o.date_purchased desc";
	};
$sales_query = tep_db_query($sales_query_raw);
$num_rows = tep_db_num_rows($sales_query);
if ($num_rows==0) echo '<tr><td class="smalltext">No orders with selected status</td></tr>';
$rows=0;
while ($sales = tep_db_fetch_array($sales_query)) {
	$rows++;
// tax collected
if ($status<>'') {
	$tax_coll_query_raw = "select sum(ot.value) tax_coll from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where o.orders_status =" . $status . " and ot.class = 'ot_tax' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	} else {
	$tax_coll_query_raw = "select sum(ot.value) tax_coll from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_tax' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	};
	$tax_coll_query = tep_db_query($tax_coll_query_raw);
	$tax_this_month = tep_db_fetch_array($tax_coll_query);
// shipping and handling charges
if ($status<>'') {
	$shiphndl_query_raw = "select sum(ot.value) shiphndl from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where o.orders_status =" . $status . " and ot.class = 'ot_shipping' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	} else {
	$shiphndl_query_raw = "select sum(ot.value) shiphndl from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_shipping' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	};
	$shiphndl_query = tep_db_query($shiphndl_query_raw);
	$shiphndl_this_month = tep_db_fetch_array($shiphndl_query);
// low order fees
if ($status<>'') {
	$loworder_query_raw = "select sum(ot.value) loworder from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where o.orders_status =" . $status . " and ot.class = 'ot_loworderfee' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	} else {
	$loworder_query_raw = "select sum(ot.value) loworder from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_loworderfee' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	};
	$loworder_query = tep_db_query($loworder_query_raw);
	$loworder_this_month = tep_db_fetch_array($loworder_query);
// sum of orders not taxed
if ($status<>'') {
	$nontaxable_query_raw = "select sum(ot.value) nontaxable_sales, monthname(o.date_purchased) row_month, month(o.date_purchased) i_month from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where o.orders_status =" . $status . " and ot.class = 'ot_subtotal' and o.delivery_state <> '" . tep_get_zone_name(STORE_ZONE) . "' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'] . " group by month(o.date_purchased)";
	} else {
	$nontaxable_query_raw = "select sum(ot.value) nontaxable_sales, monthname(o.date_purchased) row_month, month(o.date_purchased) i_month from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_subtotal' and o.delivery_state <> '" . tep_get_zone_name(STORE_ZONE) . "' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'] . " group by month(o.date_purchased)";
	};
	$nontaxable_query = tep_db_query($nontaxable_query_raw);
	$nontaxable_this_month = tep_db_fetch_array($nontaxable_query);
// sum of orders taxed
if ($status<>'') {
	$taxable_query_raw = "select sum(ot.value) taxable_sales, monthname(o.date_purchased) row_month, month(o.date_purchased) i_month from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where o.orders_status =" . $status . " and ot.class = 'ot_subtotal' and o.delivery_state = '" . tep_get_zone_name(STORE_ZONE) . "' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'] . " group by month(o.date_purchased)";
	} else {
	$taxable_query_raw = "select sum(ot.value) taxable_sales, monthname(o.date_purchased) row_month, month(o.date_purchased) i_month from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ot.class = 'ot_subtotal' and o.delivery_state = '" . tep_get_zone_name(STORE_ZONE) . "' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'] . " group by month(o.date_purchased)";
	};
	$taxable_query = tep_db_query($taxable_query_raw);
	$taxable_this_month = tep_db_fetch_array($taxable_query);
// accumulate footer
	$ytd_gross += $sales['gross_sales'] + $tax_this_month['tax_coll'] + $shiphndl_this_month['shiphndl'] + $loworder_this_month['loworder'];
	$ytd_sales += $sales['gross_sales'];
	$ytd_sales_exempt += $nontaxable_this_month['nontaxable_sales'];
	$ytd_sales_taxable += $taxable_this_month['taxable_sales'];
	$ytd_tax_coll += $tax_this_month['tax_coll'];
	$ytd_shiphndl += $shiphndl_this_month['shiphndl'];
	$ytd_loworder += $loworder_this_month['loworder'];
?>
              <tr class="dataTableRow">
				<td class="dataTableContent" align="left"><?php echo substr($sales['row_month'],0,3); ?></td>
				<td class="dataTableContent" align="left"><?php echo $sales['row_year']; ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($sales['gross_sales'] + $tax_this_month['tax_coll'] + $shiphndl_this_month['shiphndl'] + $loworder_this_month['loworder'],2); ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($sales['gross_sales'],2); ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($nontaxable_this_month['nontaxable_sales'],2); ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($taxable_this_month['taxable_sales'],2); ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($tax_this_month['tax_coll'],2); ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($shiphndl_this_month['shiphndl'],2); ?></td>
				<td class="dataTableContent" align="right"><?php echo number_format($loworder_this_month['loworder'],2); ?></td>
              </tr>
<?php
// output footer after last month (Jan) or ending row
	if ($sales['i_month']==01 || $rows==$num_rows){
?>
			<tr class="dataTableHeadingRow">
				<td class="dataTableHeadingContent" align="left">
				<?php echo "Year "; ?>
				</td>
				<td class="dataTableHeadingContent" align="left">
				<?php echo $sales['row_year']; ?></td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_gross,2); ?>
				</td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_sales,2); ?>
				</td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_sales_exempt,2); ?>
				</td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_sales_taxable,2); ?>
				</td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_tax_coll,2); ?>
				</td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_shiphndl,2); ?>
				</td>
				<td class="dataTableHeadingContent" align="right">
				<?php echo number_format($ytd_loworder,2); ?>
				</td>
			</tr>
<?php
// reset footers
	$ytd_gross = 0;
	$ytd_sales = 0;
	$ytd_sales_exempt = 0;
	$ytd_sales_taxable = 0;
	$ytd_tax_coll = 0;
	$ytd_shiphndl = 0;
	$ytd_loworder = 0;
	};
  }
?>
            </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'); ?>