<?php
/*
  $Id: stats_monthly_sales.php, v 2.2 2005/12/24  $

  contributed by Fritz Clapp <fritz@sonnybarger.com>

This report displays a summary of monthly or daily totals:
  gross income (order totals)
  subtotals of all orders in the selected period
  nontaxed sales subtotals
  taxed sales subtotals
  tax collected
  shipping/handling charges
  low order fees (if present)
  gift vouchers (or other addl order total component, if present)

The data comes from the orders and orders_total tables, therefore this report
works only for osCommerce snapshots since 2002/04/08 (including MS1 and MS2).

Data is reported as of order purchase date.

If an order status is chosen, the report summarizes orders with that status.

Version 2.0 introduces the capability to "drill down" on any month
to report the daily summary for that month.  

Report rows are initially shown in newest to oldest, top to bottom, 
but this order may be inverted by clicking the "Invert" control button.

Version 2.1 adds a popup display that lists the various types (and their
subtotals) comprising the tax values in the report rows.

**NOTE:
This Version 2.2 has columns that summarize nontaxed and taxed order subtotals.
The taxed column summarizes subtotals for orders in which tax was charged.
The nontaxed column is the subtotal for the row less the taxed column value.

  osCommerce, Open Source E-Commerce Solutions
  http://www.oscommerce.com
  Copyright (c) 2004 osCommerce
  Released under the GNU General Public License
*/

  require('includes/application_top.php');

  require(DIR_WS_CLASSES . 'currencies.php');
  $print = (isset($_GET['print']) ? $_GET['print']: '');
  $invert = (isset($_GET['invert']) ? $_GET['invert']: '');
  $month = (isset($_GET['month']) ? $_GET['month']: '');
  $status = (isset($_GET['status']) ? $_GET['status']: '');
  $year = (isset($_GET['year']) ? $_GET['year']: '');
  $currencies = new currencies();
//
// entry for help popup window
if (isset($_GET['help'])){ 
  echo TEXT_HELP;
  exit;
};
//
// entry for bouncing csv string back as file
if (isset($_POST['csv'])) {
if ($_POST['saveas']) {  // rebound posted csv as save file
    $savename= $_POST['saveas'] . ".csv";
    }
    else $savename='unknown.csv';
$csv_string = '';
if ($_POST['csv']) $csv_string=$_POST['csv'];
  if (strlen($csv_string)>0){
  header("Expires: Mon, 26 Nov 1962 00:00:00 GMT");
  header("Last-Modified: " . gmdate('D,d M Y H:i:s') . ' GMT');
  header("Cache-Control: no-cache, must-revalidate");
  header("Pragma: no-cache");
  header("Content-Type: Application/octet-stream");
  header("Content-Disposition: attachment; filename=$savename");
  echo $csv_string;
  }
  else echo "CSV string empty";
exit;
};
//
// entry for popup display of tax detail
// show=ot_tax 
if (isset($_GET['show'])) {

  $ot_type = tep_db_prepare_input($_GET['show']);
  $sel_month = tep_db_prepare_input($_GET['month']);
  $sel_year = tep_db_prepare_input($_GET['year']);
  $sel_day = 0;
  if (isset($_GET['day'])) $sel_day = tep_db_prepare_input($_GET['day']);
  $status = '';
  if ($_GET['status']) $status = tep_db_prepare_input($_GET['status']);
  // construct query for selected detail
  $detail_query_raw = "select sum(ot.value) amount, ot.title description from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
  if ($status<>'') $detail_query_raw .= "o.orders_status ='" . $status . "' and ";
  $detail_query_raw .= "ot.class = '" . $ot_type . "' and month(o.date_purchased)= '" . $sel_month . "' and year(o.date_purchased)= '" . $sel_year . "'";
  if ($sel_day<>0) $detail_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sel_day . "'";
  $detail_query_raw .= " group by ot.title";
  $detail_query = tep_db_query($detail_query_raw);
  echo "<!doctype html public \"-//W3C//DTD HTML 4.01 Transitional//EN\"><html " . HTML_PARAMS . "><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=" . CHARSET . "\">" . "<title>" . TEXT_DETAIL . "</title><link rel=\"stylesheet\" type=\"text/css\" href=\"includes/stylesheet.css\"><link rel=\"stylesheet\" type=\"text/css\" href=\"includes/headernavmenu.css\">
<script type=\"text/javascript\" src=\"includes/menu.js\"></script>
</head><body><br><table width=\"80%\" align=center><caption align=center>";
  //if ($sel_day<>0) echo $sel_day . "/" ;
  echo $sel_year . "/" . $sel_month;
  if ($sel_day<>0) echo "/" . $sel_day;
  if ($status<>'') echo "<br>" . HEADING_TITLE_STATUS . ":" . "&nbsp;" . $status;
  echo "</caption>";
 
  while ($detail_line = tep_db_fetch_array($detail_query)) {
  echo "<tr class=dataTableRow><td align=left width='75%'>" . $detail_line['description'] . "</td><td align=right>" . number_format($detail_line['amount'],2) . "</td></tr>";}
  echo "</table></body>";
exit;
};
//
// main entry for report display


include(DIR_WS_INCLUDES . 'html_top.php');
include(DIR_WS_INCLUDES . 'header.php');
include(DIR_WS_INCLUDES . 'column_left.php');
?>
<div id="content" class="content p-relative">         
  <h1 class="page-header"><i class="fa fa-laptop"></i> <?php echo HEADING_TITLE; ?> </h1>
  <?php if (file_exists(DIR_WS_INCLUDES . 'toolbar.php')) include(DIR_WS_INCLUDES . 'toolbar.php'); ?>
	<div class="col main-col">
	  <div class="row">
		<div class="col-9 pr-0 mb-2"></div>
		<div class="col-3 pr-1">

<?php 
// detect whether this is monthly detail request
$sel_month = 0;
  if ($month && $year) {
  $sel_month = tep_db_prepare_input($month);
  $sel_year = tep_db_prepare_input($year);
  };
// get list of orders_status names for dropdown selection
  $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'];
    };
// name of status selection
$orders_status_text = TEXT_ALL_ORDERS;
if ($status) {
  $status = tep_db_prepare_input($status);
  $orders_status_query = tep_db_query("select orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "' and orders_status_id =" . $status);
  while ($orders_status = tep_db_fetch_array($orders_status_query)) {
    $orders_status_text = $orders_status['orders_status_name'];}
        };  
if (!$print) { 
		echo tep_draw_form('status', FILENAME_STATS_MONTHLY_SALES, '', 'get');
        // get list of orders_status names for dropdown selection
          if (isset($_GET[tep_session_name()])) {
            echo tep_draw_hidden_field(tep_session_name(), $_GET[tep_session_name()]);
          }
          $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'];
          };
          ?>
           <div class="form-group row mb-2 pr-0">
            <label for="cPath" class="hidden-xs col-sm-3 col-form-label text-center m-t-10 pr-0"><?php echo HEADING_TITLE_STATUS; ?></label>
            <div class="col-sm-9 p-0 dark rounded">
            <?php echo tep_draw_pull_down_menu('status', array_merge(array(array('id' => '', 'text' => TEXT_ALL_ORDERS)), $orders_statuses), '', 'onChange="this.form.submit();"'); ?>
            </div>
            </div>
            
        	<input type="hidden" name="selected_box" value="reports">
        <?php
          if ($sel_month<>0) 
          echo "<input type='hidden' name='month' value='" . $sel_month . "'><input type='hidden' name='year' value='" . $sel_year . "'>";
          if ($invert) echo "<input type='hidden' name='invert' value='yes'>";
        ?>
              </form>
              
<?php   }; ?>

		</div>
	  </div>
	</div>

  <div class="col">   
    <!-- begin panel -->
    <div class="dark">
      <!-- body_text //-->     
      <div id="table-statsproductspurchased" class="table-statsproductspurchased">
        <div class="row">
          <div class="col-md-12 col-xl-12 dark panel-left rounded">



<?php if ($print) { ?>
        <table>
        <tr><td class="smallText"><?php echo HEADING_TITLE_REPORTED . ": "; ?></td>
        <td width="8"></td>
        <td class="smallText" align="left"><?php echo date(ltrim(TEXT_REPORT_DATE_FORMAT)); ?></td>
        </tr>
        <tr><td class="smallText" align="left">
        <?php echo HEADING_TITLE_STATUS . ": ";  ?></td>
        <td width="8"></td>
        <td class="smallText" align="left">
        <?php echo $orders_status_text;?>
        </td>
        </tr>
        <table>
<?php   };   
if(!$print) { ?>
<!--
row for buttons to print, save, and help
-->
        <table align=right cellspacing="10"><tr>
        <td align="left" class="smallText">
        <?php  
        if ($sel_month<>0)
        {
        echo "<a class=\"btn btn-success btn-sm mt-2 mb-2\" href='" . tep_href_link(basename($PHP_SELF),'selected_box=reports') . "";
        if (isset($_GET['status'])) echo "&status=" . $status;
        if (isset($_GET['invert'])) echo "&invert=yes";
        echo "' title='" . TEXT_BUTTON_REPORT_BACK_DESC . "'>" . TEXT_BUTTON_REPORT_BACK . "</a>";
        };
        ?>
        </td>
        <td class="smallText"><a class="btn btn-success btn-sm mt-2 mb-2" href="<?php echo tep_href_link(basename($PHP_SELF), $_SERVER['QUERY_STRING']."&print=yes"); ?>" target="print" title="<?php echo TEXT_BUTTON_REPORT_PRINT_DESC . "\">" . TEXT_BUTTON_REPORT_PRINT; ?></a> </td>
        <td class="smallText"><a class="btn btn-success btn-sm mt-2 mb-2" href='<?php echo tep_href_link(basename($PHP_SELF), preg_replace('/&invert=yes/', '', $_SERVER['QUERY_STRING'])); if (!$invert) echo "&invert=yes"; echo "' title= '" . TEXT_BUTTON_REPORT_INVERT_DESC . "'>" . TEXT_BUTTON_REPORT_INVERT; ?></a> </td>
        <td class="smallText"><a class="btn btn-success btn-sm mt-2 mb-2" href="#" onClick="window.open('<?php echo tep_href_link(basename($PHP_SELF), 'help=yes'); ?>','help',config='height=400,width=600,scrollbars=1, resizable=1')" title="<?php echo TEXT_BUTTON_REPORT_HELP_DESC . "\">" . TEXT_BUTTON_REPORT_HELP; ?></a> </td>
       </tr>
      </table>
<?php };  
//
// determine if loworder fee is enabled in configuration, include/omit the column
$loworder_query_raw = "select configuration_value from " . TABLE_CONFIGURATION . " where configuration_key =" . "'MODULE_ORDER_TOTAL_LOWORDERFEE_LOW_ORDER_FEE'";
$loworder = false;
$loworder_query = tep_db_query($loworder_query_raw);
if (tep_db_num_rows($loworder_query)>0) {
  $low_setting=tep_db_fetch_array($loworder_query);
  if ($low_setting['configuration_value']=='true') $loworder=true;
};
//
// if there are extended class values in orders_table
// create extra column so totals are comprehensively correct
$class_val_subtotal = "'ot_subtotal'";
$class_val_tax = "'ot_tax'";
$class_val_shiphndl = "'ot_shipping'";
$class_val_loworder = "'ot_loworderfee'";
$class_val_total = "'ot_total'";
$extra_class_query_raw = "select value from " . TABLE_ORDERS_TOTAL . " where class <> " . $class_val_subtotal . " and class <>" . $class_val_tax . " and class <>" . $class_val_shiphndl . " and class <>" . $class_val_loworder . " and class <>" . $class_val_total;
$extra_class = false;
$extra_class_query = tep_db_query($extra_class_query_raw);
if (tep_db_num_rows($extra_class_query)>0) $extra_class = true;
// start accumulator for the report content mirrored in CSV
$csv_accum = '';
?>

<table class="table table-hover w-100 mt-2">
 <thead>
  <tr class="th-row">
	<th scope="col" class="th-col dark text-left"><?php if ($sel_month == 0) mirror_out(TABLE_HEADING_MONTH); else mirror_out(TABLE_HEADING_MONTH); ?></th>
	<th scope="col" class="th-col dark text-left"><?php if ($sel_month == 0) mirror_out(TABLE_HEADING_YEAR); else mirror_out(TABLE_HEADING_DAY); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_INCOME); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_SALES); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_NONTAXED); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_TAXED); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_TAX_COLL); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_SHIPHNDL); ?></th>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_SHIP_TAX); ?></th>
<?php 
if ($loworder) { ?>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_LOWORDER); ?></th>
<?php } 
if ($extra_class) { ?>
	<th scope="col" class="th-col dark text-right"><?php mirror_out(TABLE_HEADING_OTHER); ?></th>
<?php }; ?>
	</tr>
 <thead>
<?php 
// clear footer totals
  $footer_gross = 0;
  $footer_sales = 0;
  $footer_sales_nontaxed = 0;
  $footer_sales_taxed = 0;
  $footer_tax_coll = 0;
  $footer_shiphndl = 0;
  $footer_shipping_tax = 0;
  $footer_loworder = 0;
  $footer_other = 0;
// new line for CSV
$csv_accum .= "\n";
// order totals, the driving force 

$sales_query_raw = "select sum(ot.value) gross_sales, monthname(o.date_purchased) row_month, year(o.date_purchased) row_year, month(o.date_purchased) i_month, dayofmonth(o.date_purchased) row_day  from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
if ($status) {
  $status = tep_db_prepare_input($_GET['status']);
  $sales_query_raw .= "o.orders_status =" . $status . " and ";
  };
$sales_query_raw .= "ot.class = " . $class_val_total;
if ($sel_month<>0) $sales_query_raw .= " and month(o.date_purchased) = " . $sel_month;
$sales_query_raw .= " group by year(o.date_purchased), month(o.date_purchased)";
if ($sel_month<>0) $sales_query_raw .= ", dayofmonth(o.date_purchased)";
$sales_query_raw .=  " order by o.date_purchased ";
if ($invert) $sales_query_raw .= "asc"; else $sales_query_raw .= "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">' . TEXT_NOTHING_FOUND . '</td></tr>';
$rows=0;
//
// loop here for each row reported
while ($sales = tep_db_fetch_array($sales_query)) {
  $rows++;
  if ($rows>1 && $sales['row_year']<>$last_row_year) {  // emit annual footer
?>
<tr class="dataTableHeadingRow">
<td class="table-col dark text-left">
<?php 
  if ($sales['row_year']==date("Y")) mirror_out(TABLE_FOOTER_YTD); 
  else 
    if ($sel_month==0) mirror_out(TABLE_FOOTER_YEAR);
    else
      mirror_out(strtoupper(substr($sales['row_month'],0,3)));
?>
</td>
<td class="table-col dark text-left"> <?php mirror_out($last_row_year); ?></td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_gross,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_sales,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_sales_nontaxed,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_sales_taxed,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_tax_coll,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_shiphndl,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format(($footer_shipping_tax <= 0) ? 0 : $footer_shipping_tax,2)); ?> </td>
<?php if ($loworder) { ?> <td class="table-col dark text-right"> <?php mirror_out(number_format($footer_loworder,2)); ?> </td> <?php }; ?>
<?php if ($extra_class) { ?> <td class="table-col dark text-right"> <?php mirror_out(number_format($footer_other,2)); ?> </td>
<?php }; 
// clear footer totals
$footer_gross = 0;
$footer_sales = 0;
$footer_sales_nontaxed = 0;
$footer_sales_taxed = 0;
$footer_tax_coll = 0;
$footer_shiphndl = 0;
$footer_shipping_tax = 0;
$footer_loworder = 0;
$footer_other = 0;
// new line for CSV
$csv_accum .= "\n";
?>
</tr>
<?php };
//

// determine net sales for row
$cached = 'YES';
// determine net sales for row
$query = "SELECT * FROM monthly_sales_tax_report WHERE `month`='". $sales['i_month'] ."' AND `year`='". $sales['row_year'] ."'";
$rs_report = tep_db_query($query);
if(tep_db_num_rows($rs_report) <= 0) {
	$cached = 'NO';
}
if($sales['row_year'] == date('Y') || $cached == 'NO')
{

	// Retrieve totals for products that are zero VAT rated
	$net_sales_query_raw = "select sum(op.final_price * op.products_quantity) net_sales from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) where op.products_tax = 0 and ";
	if ($status<>'') $net_sales_query_raw .= "o.orders_status ='" . $status . "' and ";
	$net_sales_query_raw .= " month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
	if ($sel_month<>0) $net_sales_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";

	$net_sales_query = tep_db_query($net_sales_query_raw);
	$net_sales_this_row = 0;
	if (tep_db_num_rows($net_sales_query) > 0)
	  $zero_rated_sales_this_row = tep_db_fetch_array($net_sales_query);

	// Retrieve totals for products that are NOT zero VAT rated
	$net_sales_query_raw = "select sum(op.final_price * op.products_quantity) net_sales, sum(op.final_price * op.products_quantity * (1 + (op.products_tax / 100.0))) gross_sales, sum((op.final_price * op.products_quantity * (1 + (op.products_tax / 100.0))) - (op.final_price * op.products_quantity)) tax from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) where op.products_tax <> 0 and ";
	if ($status<>'') $net_sales_query_raw .= "o.orders_status ='" . $status . "' and ";
	$net_sales_query_raw .= " month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
	if ($sel_month<>0) $net_sales_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";

	$net_sales_query = tep_db_query($net_sales_query_raw);
	$net_sales_this_row = 0;
	if (tep_db_num_rows($net_sales_query) > 0)
	  $net_sales_this_row = tep_db_fetch_array($net_sales_query);

	// Total tax. This is needed so we can calculate any tax that has been added to the postage
	$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 ";
	if ($status<>'') $tax_coll_query_raw .= "o.orders_status ='" . $status . "' and ";
	$tax_coll_query_raw .= "ot.class = " . $class_val_tax . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
	if ($sel_month<>0) $tax_coll_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
	$tax_coll_query = tep_db_query($tax_coll_query_raw);
	$tax_this_row = 0;
	if (tep_db_num_rows($tax_coll_query)>0) 
	  $tax_this_row = tep_db_fetch_array($tax_coll_query);

	//
	// shipping and handling charges for row
	$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 ";
	if ($status<>'') $shiphndl_query_raw .= "o.orders_status ='" . $status . "' and ";
	$shiphndl_query_raw .= "ot.class = " . $class_val_shiphndl . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
	if ($sel_month<>0) $shiphndl_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
	$shiphndl_query = tep_db_query($shiphndl_query_raw);
	$shiphndl_this_row = 0;
	if (tep_db_num_rows($shiphndl_query)>0) 
	  $shiphndl_this_row = tep_db_fetch_array($shiphndl_query);
	//
	// low order fees for row
	$loworder_this_row = 0;
	if ($loworder) {
	  $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 ";
	  if ($status<>'') $loworder_query_raw .= "o.orders_status ='" . $status . "' and ";
	  $loworder_query_raw .= "ot.class = " . $class_val_loworder . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
	  if ($sel_month<>0) $loworder_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
	  $loworder_query = tep_db_query($loworder_query_raw);
	  if (tep_db_num_rows($loworder_query)>0) 
	  $loworder_this_row = tep_db_fetch_array($loworder_query);
	};
	//
	// additional column if extra class value in orders_total table
	$other_this_row = 0;
	if ($extra_class) { 
	  $other_query_raw = "select sum(ot.value) other from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
	  if ($status<>'') $other_query_raw .= "o.orders_status ='" . $status . "' and ";
	  $other_query_raw .= "ot.class <> " . $class_val_subtotal . " and class <> " . $class_val_tax . " and class <> " . $class_val_shiphndl . " and class <> " . $class_val_loworder . " and class <> " . $class_val_total . " and month(o.date_purchased)= '" . $sales['i_month'] . "' and year(o.date_purchased)= '" . $sales['row_year'] . "'";
	  if ($sel_month<>0) $other_query_raw .= " and dayofmonth(o.date_purchased) = '" . $sales['row_day'] . "'";
	  $other_query = tep_db_query($other_query_raw);
	  if (tep_db_num_rows($other_query)>0)  
	  $other_this_row = tep_db_fetch_array($other_query);
	  };

	if(tep_db_num_rows($rs_report) > 0) {
		//record exists
		$rw_report = tep_db_fetch_array($rs_report);
		
		$query = "UPDATE monthly_sales_tax_report SET gross_income='". $sales['gross_sales'] ."', nontaxed_sales='". $zero_rated_sales_this_row['net_sales'] ."', taxed_sales='". $net_sales_this_row['net_sales'] ."'
					, tax='". $net_sales_this_row['tax'] ."', tax_collected='". $tax_this_row['tax_coll'] ."', shipping_handling='". $shiphndl_this_row['shiphndl'] ."'
					, loworder='". $loworder_this_row['loworder'] ."', other='". $other_this_row['other'] ."' WHERE id='". $rw_report['id'] ."'";
		tep_db_query($query);					
	} else {
		//record not exists
		$query = "INSERT INTO monthly_sales_tax_report SET month_name='". $sales['row_month'] ."', month='". $sales['i_month'] ."', year='". $sales['row_year'] ."'
					, gross_income='". $sales['gross_sales'] ."', nontaxed_sales='". $zero_rated_sales_this_row['net_sales'] ."', taxed_sales='". $net_sales_this_row['net_sales'] ."'
					, tax='". $net_sales_this_row['tax'] ."', tax_collected='". $tax_this_row['tax_coll'] ."', shipping_handling='". $shiphndl_this_row['shiphndl'] ."'
					, loworder='". $loworder_this_row['loworder'] ."', other='". $other_this_row['other'] ."'";
		tep_db_query($query);					
	}

} else {
	$rw_report = tep_db_fetch_array($rs_report);
	$net_sales_this_row['net_sales'] = $rw_report['taxed_sales'];
	$net_sales_this_row['tax'] = $rw_report['tax'];
	$zero_rated_sales_this_row['net_sales'] = $rw_report['nontaxed_sales'];
	$tax_this_row['tax_coll'] = $rw_report['tax_collected'];
	$shiphndl_this_row['shiphndl'] = $rw_report['shipping_handling'];
	$loworder_this_row['loworder'] = $rw_report['loworder'];
	$other_this_row['other'] = $rw_report['other'];
}

// Correct any rounding errors
  $net_sales_this_row['net_sales'] = (floor(($net_sales_this_row['net_sales'] * 100) + 0.5)) / 100;
  $net_sales_this_row['tax'] = (floor(($net_sales_this_row['tax'] * 100) + 0.5)) / 100;
  $zero_rated_sales_this_row['net_sales'] = (floor(($zero_rated_sales_this_row['net_sales'] * 100) + 0.5)) / 100;
  $tax_this_row['tax_coll'] = (floor(($tax_this_row['tax_coll'] * 100) + 0.5)) / 100;

// accumulate row results in footer
  $footer_gross += $sales['gross_sales']; // Gross Income
  $footer_sales += $net_sales_this_row['net_sales'] + $zero_rated_sales_this_row['net_sales']; // Product Sales
  $footer_sales_nontaxed += $zero_rated_sales_this_row['net_sales']; // Nontaxed Sales
  $footer_sales_taxed += $net_sales_this_row['net_sales']; // Taxed Sales
  $footer_tax_coll += $net_sales_this_row['tax']; // Taxes Collected
  $footer_shiphndl += $shiphndl_this_row['shiphndl']; // Shipping & handling
        $footer_shipping_tax += ($tax_this_row['tax_coll'] - $net_sales_this_row['tax']); // Shipping Tax
  $footer_loworder += $loworder_this_row['loworder'];
  if ($extra_class) $footer_other += $other_this_row['other'];
?>
<tr class="dataTableRow">
<td class="table-col dark text-left">
<?php  // live link to report monthly detail
if ($sel_month == 0 && !$print) {
  echo "<a href='" . tep_href_link(basename($PHP_SELF), $_SERVER['QUERY_STRING'] . "&month=" . $sales['i_month'] . "&year=" . $sales['row_year'] . "' title='" . TEXT_BUTTON_REPORT_GET_DETAIL) . "'>";
  }
mirror_out(substr($sales['row_month'],0,3)); 
if ($sel_month == 0 && !$print) echo '</a>';
?>
</td>
<td class="table-col dark text-left">
<?php 
if ($sel_month==0) mirror_out($sales['row_year']);
else mirror_out($sales['row_day']);
$last_row_year = $sales['row_year']; // save this row's year to check for annual footer
?>
</td>
<td class="table-col dark text-right"><?php mirror_out(number_format($sales['gross_sales'],2)); ?></td>
<td class="table-col dark text-right"><?php mirror_out(number_format($net_sales_this_row['net_sales'] + $zero_rated_sales_this_row['net_sales'],2)); ?></td>
<td class="table-col dark text-right"><?php mirror_out(number_format($zero_rated_sales_this_row['net_sales'],2)); ?></td>
<td class="table-col dark text-right"><?php mirror_out(number_format($net_sales_this_row['net_sales'],2)); ?></td>
<td class="table-col dark text-right">
<?php 
  // make this a link to the detail popup if nonzero
  if (!$print && ($net_sales_this_row['tax']>0)) {
    $tmp_link = '';
    $tmp_link = "show=ot_tax&year=" . $sales['row_year'] . "&month=" . $sales['i_month'];
    if ($sel_month<>0) $tmp_link.= "&day=" . $sales['row_day'];
    if ($status<>'') $tmp_link.= "&status=" . $status;
    echo "<a href=\"#\" onClick=\"window.open('" . tep_href_link(basename($PHP_SELF),$tmp_link);    
    echo "','detail',config='height=200,width=400,scrollbars=1, resizable=1')\" title=\"Show detail\">";
  };
  mirror_out(number_format($net_sales_this_row['tax'],2)); 
  if (!$print && $net_sales_this_row['tax']>0) echo "</a>";
?></td>
<td class="table-col dark text-right"><?php mirror_out(number_format($shiphndl_this_row['shiphndl'],2)); ?></td>
<td class="table-col dark text-right"><?php $sh_tax = $tax_this_row['tax_coll'] - $net_sales_this_row['tax']; mirror_out(number_format(($sh_tax <= 0) ? 0 : $sh_tax,2)); ?></td>
<?php if ($loworder) { ?> <td class="table-col dark text-right"><?php mirror_out(number_format($loworder_this_row['loworder'],2)); ?></td> <?php }; ?>
<?php if ($extra_class) { ?> <td class="table-col dark text-right"><?php mirror_out(number_format($other_this_row['other'],2)); ?></td> <?php }; ?>
</tr>
<?php 
// new line for CSV
$csv_accum .= "\n";
//
//
// output footer below ending row
if ($rows==$num_rows){
?>
<tr class="dataTableHeadingRow">
<td class="table-col dark text-left">
<?php 
  if ($sel_month<>0) 
  mirror_out(strtoupper(substr($sales['row_month'],0,3)));
  else
  {if ($sales['row_year']==date("Y")) mirror_out(TABLE_FOOTER_YTD); 
   else mirror_out(TABLE_FOOTER_YEAR);};
?>
</td>
<td class="table-col dark text-left"> <?php mirror_out($sales['row_year']); ?></td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_gross,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_sales,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_sales_nontaxed,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_sales_taxed,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_tax_coll,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format($footer_shiphndl,2)); ?> </td>
<td class="table-col dark text-right"> <?php mirror_out(number_format(($footer_shipping_tax <= 0) ? 0 : $footer_shipping_tax,2)); ?> </td>
<?php if ($loworder) { ?> <td class="table-col dark text-right"> <?php mirror_out(number_format($footer_loworder,2)); ?> </td> <?php }; ?>
<?php if ($extra_class) { ?> <td class="table-col dark text-right"> <?php mirror_out(number_format($footer_other,2)); ?> </td>
<?php }; 
// clear footer totals
$footer_gross = 0;
$footer_sales = 0;
$footer_sales_nontaxed = 0;
$footer_sales_taxed = 0;
$footer_tax_coll = 0;
$footer_shiphndl = 0;
$footer_shipping_tax = 0;
$footer_loworder = 0;
$footer_other = 0;
// new line for CSV
$csv_accum .= "\n";
?>
</tr>
<table>
<?php };
  };
// done with report body
//
// button for Save CSV
if ($num_rows>0 && !$print) {
?>
          <table class="data-table-foot" border="0" cellpadding="0" cellspacing="0" width="100%">
            <tr>            
              <td align="right" class="button-container">
                <form action="<?php echo tep_href_link(basename($PHP_SELF)); ?>" method=post><input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'><input type='hidden' name='saveas' value='sales_report_<?php
            //suggested file name for csv, include year and month if detail
            //include status if selected, end with date and time of report
          if ($sel_month<10) $sel_month_2 = "0" . $sel_month; 
          else $sel_month_2 = $sel_month;
          if ($sel_month<>0) echo $sel_year . $sel_month_2 . "_";
          if (strpos($orders_status_text,' ')) echo substr($orders_status_text, 0, strpos($orders_status_text,' ')) . "_" . date("YmdHi"); else echo $orders_status_text . "_" . date("YmdHi"); 
          ?>'><?php echo '<button class="btn btn-success btn-sm" type="submit">' . TEXT_BUTTON_REPORT_SAVE . '</button>'; ?></form>
              </td>
            </tr>
          </table>
<?php }; // end button for Save CSV ?>



			<div class="mb-1">&nbsp;</div>
		 </div>
        </div>
      </div>   
      <!-- end body_text //-->
    </div>
    <!-- end panel -->
  </div>
</div>
<!-- body_eof //-->
<?php 
include(DIR_WS_INCLUDES . 'html_bottom.php');
require(DIR_WS_INCLUDES . 'application_bottom.php'); 

function mirror_out ($field) {
  global $csv_accum;
  echo $field;
  $field = strip_tags($field);
  $field = preg_replace ("/,/","",$field);
  if ($csv_accum=='') $csv_accum=$field; 
  else 
  {if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= $field;
    else $csv_accum .= "," . $field; };
  return;
};

?>
