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');
$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 ($HTTP_POST_VARS['saveas']) { // rebound posted csv as save file
$savename= $HTTP_POST_VARS['saveas'] . ".csv";
}
else $savename='unknown.csv';
$csv_string = '';
if ($HTTP_POST_VARS['csv']) $csv_string=$HTTP_POST_VARS['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($HTTP_GET_VARS['show'])) {
$ot_type = tep_db_prepare_input($HTTP_GET_VARS['show']);
$sel_month = tep_db_prepare_input($HTTP_GET_VARS['month']);
$sel_year = tep_db_prepare_input($HTTP_GET_VARS['year']);
$sel_day = 0;
if (isset($HTTP_GET_VARS['day'])) $sel_day = tep_db_prepare_input($HTTP_GET_VARS['day']);
$status = '';
if ($HTTP_GET_VARS['status']) $status = tep_db_prepare_input($HTTP_GET_VARS['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 "" . "" . TEXT_DETAIL . "
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 = '';
?>
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 '
' . TEXT_NOTHING_FOUND . '
';
$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
?>
'') $net_sales_query_raw .= " o.orders_status ='" . $status . "' AND ";
$net_sales_query_raw .= " o.date_purchased BETWEEN '" . $sales['row_year'] . "-" . $sales['i_month'] . "-01' AND '" . $sales['row_year'] . "-" . $sales['i_month'] . "-30 23:59'";
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 INNER 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 .= " o.date_purchased BETWEEN '" . $sales['row_year'] . "-" . $sales['i_month'] . "-01' AND '" . $sales['row_year'] . "-" . $sales['i_month'] . "-30 23:59'";
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 INNER 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 o.date_purchased BETWEEN '" . $sales['row_year'] . "-" . $sales['i_month'] . "-01' AND '" . $sales['row_year'] . "-" . $sales['i_month'] . "-30 23:59'";
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 INNER 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 o.date_purchased BETWEEN '" . $sales['row_year'] . "-" . $sales['i_month'] . "-01' AND '" . $sales['row_year'] . "-" . $sales['i_month'] . "-30 23:59'";
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 INNER 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 o.date_purchased BETWEEN '" . $sales['row_year'] . "-" . $sales['i_month'] . "-01' AND '" . $sales['row_year'] . "-" . $sales['i_month'] . "-30 23:59'";
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 INNER 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 o.date_purchased BETWEEN '" . $sales['row_year'] . "-" . $sales['i_month'] . "-01' AND '" . $sales['row_year'] . "-" . $sales['i_month'] . "-30 23:59'";
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);
};
// 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'];
?>