public function TaxStatsByDateGrid()
{
$GLOBALS['TaxGrid'] = "";
if(!(isset($_GET['From']) && isset($_GET['To']))) {
return;
}
$from_stamp = (int)$_GET['From'];
$to_stamp = (int)$_GET['To'];
// How many records per page?
if(isset($_GET['Show'])) {
$per_page = (int)$_GET['Show'];
}
else {
$per_page = 20;
}
$GLOBALS['TaxPerPage'] = $per_page;
$GLOBALS["IsShowPerPage" . $per_page] = 'selected="selected"';
// Should we limit the records returned?
if(isset($_GET['Page'])) {
$page = (int)$_GET['Page'];
}
else {
$page = 1;
}
$GLOBALS['TaxByDateCurrentPage'] = $page;
// Workout the start and end records
$start = ($per_page * $page) - $per_page;
$end = $start + ($per_page - 1);
// Only fetch data this user can actually see
$vendorRestriction = $this->GetVendorRestriction();
$vendorSql = '';
if($vendorRestriction !== false) {
$vendorSql = " AND ordvendorid='".(int)$vendorRestriction."'";
}
// Calculate the number of seconds from GMT +0 that we are in. We'll be adjusting
// the orddate in the query below so that it becomes timezone specific (remember, MySQL thinks we're +0)
$timezoneAdjustment = GetConfig('StoreTimeZone');
if(GetConfig('StoreDSTCorrection')) {
++$timezoneAdjustment;
}
$timezoneAdjustment *= 3600;
if (empty($_GET['TaxListBy'])) {
$groupBy = 'Day';
}
else {
$groupBy = $_GET['TaxListBy'];
}
$fieldSQL = '';
$addDay = 0;
$addMonth = 0;
$addYear = 0;
$startStamp = $from_stamp;
$endStamp = mktime(23, 59, 59, date('m', $to_stamp), date('j', $to_stamp), date('Y', $to_stamp));
switch ($groupBy) {
case 'Day':
$fieldSQL = "DATE_FORMAT(FROM_UNIXTIME(orddate+".$timezoneAdjustment."), '%Y-%m-%d') AS formatteddate";
$addDay = 1;
$currentStamp = $startStamp;
$dateFormat = 'jS M Y';
break;
case 'Month':
$fieldSQL = "DATE_FORMAT(FROM_UNIXTIME(orddate+".$timezoneAdjustment."), '%Y-%m-1') AS formatteddate";
$addMonth = 1;
$currentStamp = mktime(0, 0, 0, date('m', $from_stamp) + $start, 1, date('Y', $from_stamp));
$dateFormat = 'F Y';
break;
case 'Year':
$fieldSQL = "DATE_FORMAT(FROM_UNIXTIME(orddate+".$timezoneAdjustment."), '%Y-1-1') AS formatteddate";
$addYear = 1;
$currentStamp = mktime(0, 0, 0, 1, 1, date('Y', $from_stamp));
$dateFormat = 'Y';
break;
}
$query = "
SELECT
t.name,
t.class,
t.rate,
SUM(t.line_amount) AS amount,
COUNT(DISTINCT t.order_id) AS numorders,
".$fieldSQL."
FROM [|PREFIX|]order_taxes t
JOIN [|PREFIX|]orders o ON (o.orderid=t.order_id)
WHERE
t.line_amount > 0 AND
o.ordstatus IN (".implode(',', GetPaidOrderStatusArray()).") AND
o.deleted = 0 AND
orddate >= '" . $startStamp . "' AND
orddate <= '" . $endStamp . "'
" . $vendorSql . "
GROUP BY
formatteddate,
t.name,
t.class,
t.rate
ORDER BY
formatteddate
";
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$dataRows = array();
while($row = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) {
$date = strtotime($row['formatteddate']);
$dataRows[$date][] = $row;
}
$lastdate = '';
$outputRows = array();
while (true) {
if (!isset($dataRows[$currentStamp])) {
$outputRows[] = array(
'period' => $currentStamp,
'taxtype' => '',
'taxrate' => '',
'numorders' => 0,
'taxamount' => 0
);
}
else {
foreach ($dataRows[$currentStamp] as $row) {
$outputRows[] = array(
'period' => $currentStamp,
'taxtype' => $row['name'].' - '.$row['class'],
'taxrate' => $row['rate'],
'numorders' => $row['numorders'],
'taxamount' => $row['amount']
);
}
}
$currentStamp = mktime(0,0,0,date('m', $currentStamp) + $addMonth, date('d',$currentStamp) + $addDay, date('Y', $currentStamp) + $addYear);
if ($currentStamp > $endStamp) {
break;
}
}
// Workout the paging
$num_pages = ceil(count($outputRows) / $per_page);
$paging = sprintf(GetLang('PageXOfX'), $page, $num_pages);
$paging .= " ";
// Is there more than one page? If so show the « to jump back to page 1
if($num_pages > 1) {
$paging .= "<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(1)'>«</a> | ";
}
else {
$paging .= "« | ";
}
// Are we on page 2 or above?
if($page > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>%s</a> | ", $page-1, GetLang('Prev'));
}
else {
$paging .= sprintf("%s | ", GetLang('Prev'));
}
for($i = 1; $i <= $num_pages; $i++) {
// Only output paging -5 and +5 pages from the page we're on
if($i >= $page-6 && $i <= $page+5) {
if($page == $i) {
$paging .= sprintf("<strong>%d</strong> | ", $i);
}
else {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>%d</a> | ", $i, $i);
}
}
}
// Are we on page 2 or above?
if($page < $num_pages) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>%s</a> | ", $page+1, GetLang('Next'));
}
else {
$paging .= sprintf("%s | ", GetLang('Next'));
}
// Is there more than one page? If so show the » to go to the last page
if($num_pages > 1) {
$paging .= sprintf("<a href='javascript:void(0)' onclick='ChangeTaxByDatePage(%d)'>»</a> | ", $num_pages);
}
else {
$paging .= "» | ";
}
$paging = rtrim($paging, ' |');
$GLOBALS['Paging'] = $paging;
if(isset($_GET['SortOrder']) && $_GET['SortOrder'] == "desc") {
$sortOrder = "desc";
}
else {
$sortOrder = "asc";
}
$sortFields = array('period','taxtype','taxrate','numorders','taxamount');
if(isset($_GET['SortBy']) && in_array($_GET['SortBy'], $sortFields)) {
$sortField = $_GET['SortBy'];
SaveDefaultSortField("TaxStats", $_REQUEST['SortBy'], $sortOrder);
}
else {
list($sortField, $sortOrder) = GetDefaultSortField("TaxStats", "period", $sortOrder);
}
$sortLinks = array(
"Period" => "period",
"TaxType" => "taxtype",
"TaxRate" => "taxrate",
"NumOrders" => "numorders",
"TaxAmount" => "taxamount"
);
BuildAdminSortingLinks($sortLinks, "javascript:SortTaxStats('%%SORTFIELD%%', '%%SORTORDER%%');", $sortField, $sortOrder);
if ($sortOrder == "asc") {
$arraySort = SORT_ASC;
}
else {
$arraySort = SORT_DESC;
}
// now lets sort the rows we've built
$outputRows = Interspire_Array::msort($outputRows, array($sortField => $arraySort));
$offset = ($page - 1) * $per_page;
// lets grab the subset of rows for the current page
$outputRows = array_slice($outputRows, $offset, $per_page);
$lastStamp = 0;
$output = '';
foreach ($outputRows as $row) {
$output .= "<tr class=\"GridRow\" onmouseover=\"this.className='GridRowOver';\" onmouseout=\"this.className='GridRow';\">";
if ($lastStamp != $row['period']) {
$output .= '<td class="'.$GLOBALS['SortedFieldPeriodClass'].'">' . date($dateFormat, $row['period']) . '</td>';
}
else {
$output .= '<td class="'.$GLOBALS['SortedFieldPeriodClass'].'"> </td>';
}
if ($row['taxamount'] == 0) {
$output .= '<td align="left" colspan="2" class="'.$GLOBALS['SortedFieldTaxTypeClass'].'"><em>' . GetLang('NoTaxCollected') . '</em></td>';
$output .= '<td align="center" class="'.$GLOBALS['SortedFieldNumOrdersClass'].'">0</td>';
$output .= '<td align="right" class="'.$GLOBALS['SortedFieldTaxAmountClass'].'">' . FormatPrice(0) . '</td>';
}
else {
$output .= '<td class="'.$GLOBALS['SortedFieldTaxTypeClass'].'">' . $row['taxtype'] . '</td>';
$output .= '<td align="center" class="'.$GLOBALS['SortedFieldTaxRateClass'].'">' . ($row['taxrate']/1) .'%</td>';
$output .= '<td align="center" class="'.$GLOBALS['SortedFieldNumOrdersClass'].'">' . number_format($row['numorders']) . '</td>';
$output .= '<td align="right" class="'.$GLOBALS['SortedFieldTaxAmountClass'].'">' . FormatPrice($row['taxamount']) . '</td>';
}
$output .= '</tr>';
$lastStamp = $row['period'];
}
$GLOBALS['TaxGrid'] = $output;
// Add the limit
$result = $GLOBALS['ISC_CLASS_DB']->Query($query);
$this->template->display('stats.orders.salestax.tpl');
}
