<?php

/*

  osCommRes, Services Online
  http://www.oscommres.com

  Copyright (c) 2005 osCommRes

  Released under the GNU General Public License
  
  Freeway eCommerce from ZacWare
http://www.zac-ware.com/freeway

Copyright 2007 ZacWare Pty. Ltd 
*/
// Set flag that this is a parent file
	define( '_FEXEC', 1 );
////////////////////////// 
// Disable CSV file capability for systems lacking permission
$csv_enabled = false;
//////////////////////////

  require('includes/application_top.php');
  tep_get_last_access_file();require(DIR_WS_CLASSES . 'currencies.php');
  $currencies = new currencies();
?>
<!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="<?php if(!$print) {
	echo 'includes/stylesheet.css';}
	else echo 'includes/printer.css'; ?>">
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<?php
// printer-friendly version
($HTTP_GET_VARS['print']=='yes') ? $print=true : $print=false;
?>
<!-- header //-->
<?php if(!$print) require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>

<?php if(!$print) {?> 
<?php	};	?>

<!-- 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">
<?php if ($print) {
	echo "<tr><td class=\"pageHeading\">" . STORE_NAME ."</td></tr>";
	};
?>
		  <tr>
            <td class="pageHeading">
			<?php echo HEADING_TITLE; ?></td>
<?php 
// get list of orders_status names for dropdown selection
  $orders_statuses = array();
  $orders_status_array = array();
  $orders_statuses=tep_get_orders_status();
  $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 ($HTTP_GET_VARS['status']) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['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) { ?>
			<td align="right">
			<table border="0" width="100%" cellspacing="0" cellpadding="0">
			  <tr><td class="smallText" align="right">
				<?php echo tep_draw_form('status', FILENAME_STATS_MONTHLY_SALES, '', 'get');
				// get list of orders_status names for dropdown selection
				  $orders_statuses = array();
				  $orders_status_array = array();
				  $orders_statuses=tep_get_orders_status();
				  $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'];
				  };
                echo HEADING_TITLE_STATUS . ': ' . tep_draw_pull_down_menu('status', 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>
<?php		}; ?>

<?php if ($print) { ?>
			<td>
			</td>
		<tr><td>
				<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>
			</td><td></td>
		</tr>
<?php 	};	 ?>
        </table></td>
      </tr>
<?php if(!$print) { ?>
<!--
row for buttons to print, save, and help
-->
			<tr><td colspan="2" align="right">
				<table align=right cellspacing="10">
					<tr>
						<td class="smallText"><a href="<?php  echo $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING'] . "&print=yes";?>" target="print" title="<?php echo TEXT_BUTTON_REPORT_PRINT_DESC;?>"><?php echo  TEXT_BUTTON_REPORT_PRINT; ?></a>
						</td>
					</tr>
			   	</table>
			</td>
		</tr>
<?php	};	?>
<?php
//
// if loworder fee not enabled in configuration, 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;
// active row, user wants to display days of month
$sel_month = 0;
	if ($HTTP_GET_VARS['month']&& $HTTP_GET_VARS['year']) {
	$sel_month = tep_db_prepare_input($HTTP_GET_VARS['month']);
	$sel_year = tep_db_prepare_input($HTTP_GET_VARS['year']);
	};
// start accumulator for the report content mirrored in CSV
$csv_accum = '';
?>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="2">
          <tr>
            <td valign="top">
			<table border="0" width='100%' cellspacing="0" cellpadding="2">
<tr class="dataTableHeadingRow">
<td class="dataTableHeadingContent" width='45' align='left' valign="bottom"><?php 
if ($sel_month == 0) mirror_out(TABLE_HEADING_MONTH); else mirror_out(TABLE_HEADING_DAYS); ?>
</td>
<td class="dataTableHeadingContent" width='35' align='left' valign="bottom"><?php 
if ($sel_month == 0) mirror_out(TABLE_HEADING_YEAR); else mirror_out(TABLE_HEADING_MONTH); ?></td>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_INCOME); ?></td>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_SALES); ?></td>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_NONTAXED); ?></td>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_TAXED); ?></td>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_TAX_COLL); ?></td>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_SHIPHNDL); ?></td>
<?php 
if ($loworder) { ?>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_LOWORDER); ?></td>
<?php }; ?>
<?php
if ($extra_class) { ?>
<td class="dataTableHeadingContent" width='70' align='right' valign="bottom"><?php mirror_out(TABLE_HEADING_OTHER); ?></td>
<?php }; ?>
</tr>
<?php 
// clear 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;
	$ytd_other = 0;
// new line for CSV
$csv_accum .= "\n";
// order subtotals, the driving force 
$status = '';
$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,ot.orders_id  from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) where ";
if ($HTTP_GET_VARS['status']) {
  $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
  $sales_query_raw .= "o.orders_status =" . $status . " and ";
	};
$sales_query_raw .= "ot.class = " . $class_val_subtotal;
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 .= ", o.date_purchased";
$sales_query_raw .=  " 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" colspan="8">' . TEXT_NOTHING_FOUND . '</td></tr>';
$rows=0;
//
// loop here for each row reported
while ($sales = tep_db_fetch_array($sales_query)) {
	$rows++;

// determine tax collected for row
$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 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 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 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 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);
	};
//
// sum of orders out of zone
	$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 ";
	if ($status<>'') $nontaxable_query_raw .= "o.orders_status =" . $status . " and ";
	$nontaxable_query_raw .= "ot.class = " . $class_val_subtotal . " and o.delivery_state <> '" . tep_get_zone_name(tep_get_country_name($country_id), STORE_ZONE, STORE_ZONE) . "' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	if ($sel_month<>0) {
		$nontaxable_query_raw .= " and dayofmonth(o.date_purchased) = " . $sales['row_day'] . " group by o.date_purchased";
	} else {
		$nontaxable_query_raw .= " group by month(o.date_purchased)";
	};
	$nontaxable_query = tep_db_query($nontaxable_query_raw);
	$nontaxable_this_row = tep_db_fetch_array($nontaxable_query);
//
// sum of orders in zone
	$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 ";
	if ($status<>'') $taxable_query_raw .= "o.orders_status =" . $status . " and ";
	$taxable_query_raw .= "ot.class = " . $class_val_subtotal . " and o.delivery_state = '" . tep_get_zone_name(tep_get_country_name($country_id), STORE_ZONE, STORE_ZONE) . "' and month(o.date_purchased)= " . $sales['i_month'] . " and year(o.date_purchased)= " . $sales['row_year'];
	if ($sel_month<>0) {
		$taxable_query_raw .= " and dayofmonth(o.date_purchased) = " . $sales['row_day'] . " group by o.date_purchased";
	} else {
		$taxable_query_raw .= " group by month(o.date_purchased)";
	};
	$taxable_query = tep_db_query($taxable_query_raw);
	$taxable_this_row = tep_db_fetch_array($taxable_query);
//
// accumulate row results in footer
	$ytd_gross += $sales['gross_sales'] + $tax_this_row['tax_coll'] + $shiphndl_this_row['shiphndl'] + $loworder_this_row['loworder'];
	$ytd_sales += $sales['gross_sales'];
	$ytd_sales_exempt += $nontaxable_this_row['nontaxable_sales'];
	$ytd_sales_taxable += $taxable_this_row['taxable_sales'];
	$ytd_tax_coll += $tax_this_row['tax_coll'];
	$ytd_shiphndl += $shiphndl_this_row['shiphndl'];
	$ytd_loworder += $loworder_this_row['loworder'];
	if ($extra_class) { 
		$ytd_gross += $other_this_row['other'];
		$ytd_other += $other_this_row['other'];
	};
?>
<tr class="dataTableRow">
<td class="dataTableContent" align="left">
<?php mirror_out(substr($sales['row_month'],0,3)); ?>
</td>
<td class="dataTableContent" align="left">
<?php 
if ($sel_month==0) mirror_out($sales['row_year']);
else mirror_out($sales['row_day']); 
?>
</td>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($sales['gross_sales'] + $tax_this_row['tax_coll'] + $shiphndl_this_row['shiphndl'] + $loworder_this_row['loworder'],2)); ?></td>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($sales['gross_sales'],2)); ?></td>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($nontaxable_this_row['nontaxable_sales'],2)); ?></td>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($taxable_this_row['taxable_sales'],2)); ?></td>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($tax_this_row['tax_coll'],2)); ?></td>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($shiphndl_this_row['shiphndl'],2)); ?></td>
<?php if ($loworder) { ?>
<td class="dataTableContent" width='70' align="right"><?php mirror_out(number_format($loworder_this_row['loworder'],2)); ?></td>
<?php }; ?>
<?php
if ($extra_class) { ?>
<td class="dataTableContent" width='70' align="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 month 01 of monthly rows view, or ending row
if (($sel_month==0) && ($sales['i_month']==01) || $rows==$num_rows){
?>
<tr class="dataTableHeadingRow">
<td class="dataTableHeadingContent" align="left">
<?php if ($sales['row_year']==date("Y")) mirror_out(TABLE_FOOTER_YTD); 
	else mirror_out(TABLE_FOOTER_YEAR);?>
</td>
<td class="dataTableHeadingContent" align="left">
<?php mirror_out($sales['row_year']); ?></td>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_gross,2)); ?>
</td>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($sales['gross_sales'],2)); ?>
</td>

<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_sales_exempt,2)); ?>
</td>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_sales_taxable,2)); ?>
</td>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_tax_coll,2)); ?>
</td>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_shiphndl,2)); ?>
</td>
<?php if ($loworder) { ?>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_loworder,2)); ?>
</td>
<?php }; ?>
<?php if ($extra_class) { ?>
<td class="dataTableHeadingContent" width='70' align="right">
<?php mirror_out(number_format($ytd_other,2)); ?>
</td>
<?php }; 
// clear 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;
$ytd_other = 0;
// new line for CSV
$csv_accum .= "\n";
?>
</tr>
<?php };
  };
// done with report body
// save CSV as file
if ($num_rows>0 && $csv_enabled) {
	$f=fopen('temp.csv','w');
	fwrite($f,$csv_accum);
	fclose($f);
}
?>

            </table></td>
          </tr>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php if(!$print) require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); 

function mirror_out ($field) {
	global $csv_accum;
	echo $field;
	$field = strip_tags($field);
	$field = ereg_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;
};

?>