fannie/reports/Store-Specific/WFC/EOMreport/taxonly.php
<?php
use COREPOS\Fannie\API\item\StandardAccounting;
//header('Content-Type: application/ms-excel');
//header('Content-Disposition: attachment; filename="EOMreport.xls"');
include('../../../../config.php');
if (!class_exists('FannieAPI')) {
include(dirname(__FILE__).'/../../../../classlib2.0/FannieAPI.php');
}
include(__DIR__ . '/../../../../src/functions.php');
$dbc = FannieDB::get($FANNIE_OP_DB);
if (isset($_GET["excel"])){
header('Content-Type: application/ms-excel');
header('Content-Disposition: attachment; filename="EOMreport.xls"');
$_SERVER['REQUEST_URI'] = $_SERVER['PHP_SELF']; // grab excel from cache
$_SERVER['REQUEST_URI'] = str_replace("taxonly.php","",$_SERVER['REQUEST_URI']);
} else {
$storeInfo = FormLib::storePicker();
echo '<form action="taxonly.php" method="get">'
. $storeInfo['html'] .
'<input type="submit" value="Change" />
</form>';
echo '<p><a href="../../../../modules/plugins2.0/CoreWarehouse/reports/EOMReport.php">Or use the newer one</a></p>';
}
$store = FormLib::get('store', false);
if ($store === false) {
$clientIP = filter_input(INPUT_SERVER, 'REMOTE_ADDR');
foreach ($FANNIE_STORE_NETS as $storeID => $range) {
if (
class_exists('\\Symfony\\Component\\HttpFoundation\\IpUtils')
&& \Symfony\Component\HttpFoundation\IpUtils::checkIp($clientIP, $range)
) {
$store = $storeID;
}
}
if ($store === false) {
$store = 0;
}
}
$today = date("m/j/y");
$uoutput = "<html>
<body bgcolor='#ffffff'> <font size=2>";
$uoutput .= '<br>Report run ' . $today;
echo $uoutput;
$dlog = "trans_archive.dlogBig";
$start = '2017-07-01';
$end = '2017-07-31';
$args = array($start.' 00:00:00',$end.' 23:59:59', $store);
$date = substr($start,0,strpos($start,":")-3);
$date1 = substr($end,0,strpos($end,":")-3);
echo ' for period <br>from: <b>'. $date . '</b> to: <b>' . $date1 . '</b><br>';
$sql = FannieDB::get($FANNIE_OP_DB);
if ($store == 50) {
for ($i=1; $i<=2; $i++) {
$tranP = $sql->prepare("SELECT YEAR(tdate), MONTH(tdate), DAY(tdate), emp_no, register_no, trans_no
FROM {$dlog} WHERE trans_subtype='CM' AND description='STORE {$i}'
AND tdate BETWEEN ? AND ?
AND " . DTrans::isStoreID($store));
$tranR = $sql->execute($tranP, $args);
$collected = array(1 => 0, 2 => 0, 3 => 0);
$taxP = $sql->prepare("SELECT total FROM {$dlog} WHERE tdate BETWEEN ? AND ?
AND emp_no=? AND register_no=? AND trans_no=? AND upc='TAX'");
while ($w = $sql->fetchRow($tranR)) {
$tdate = date('Y-m-d', mktime(0, 0, 0, $w[1], $w[2], $w[0]));
$tax = $sql->getValue($taxP, array($tdate, $tdate . ' 23:59:59', $w['emp_no'], $w['register_no'], $w['trans_no']));
$collected[2] += $tax;
}
$state = 0.06875;
$city = 0.015;
$deli = 0.0225;
$county = 0.005;
$canna = 0.10;
$startDT = new DateTime($start);
$noCounty = new DateTime('2017-10-01');
if ($startDT >= $noCount) {
//$county = 0;
}
echo '<table border="1" cellspacing="0" cellpadding="4">';
echo '<tr><th colspan="4">' . ($i == 1 ? 'Hillside' : 'Denfeld') . ' Sales Tax</th></tr>';
echo '<tr><th>Tax Collected on Regular rate items</th>
<th>' . sprintf('%.2f', $collected[1]) . '</th>
<th>Regular Taxable Sales</th>
<th>' . sprintf('%.2f', $collected[1]/($state+$city+$county)) . '</th>
</tr>';
$stateTax = $collected[1] * ($state/($state+$city+$county));
$cityTax = $collected[1] * ($city/($state+$city+$county));
$countyTax = $collected[1] * ($county/($state+$city+$county));
echo '<tr>
<td align="right">State Tax Amount</td>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td align="right">State Taxable Sales</td>
<td>' . sprintf('%.2f', $stateTax / $state) . '</td>
</tr>';
echo '<tr>
<td align="right">City Tax Amount</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td align="right">City Taxable Sales</td>
<td>' . sprintf('%.2f', $cityTax / $city) . '</td>
</tr>';
echo '<tr>
<td align="right">County Tax Amount</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td align="right">County Taxable Sales</td>
<td>' . sprintf('%.2f', $countyTax / $county) . '</td>
</tr>';
echo '<tr><th>Tax Collected on Deli rate items</th>
<th>' . sprintf('%.2f', $collected[2]) . '</th>
<th>Deli Taxable Sales</th>
<th>' . sprintf('%.2f', $collected[2]/($state+$city+$deli+$county)) . '</th>
</tr>';
$stateTax = $collected[2] * ($state/($state+$city+$deli+$county));
$cityTax = $collected[2] * ($city/($state+$city+$deli+$county));
$deliTax = $collected[2] * ($deli/($state+$city+$deli+$county));
$countyTax = $collected[2] * ($county/($state+$city+$deli+$county));
echo '<tr>
<td align="right">State Tax Amount</td>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td align="right">State Taxable Sales</td>
<td>' . sprintf('%.2f', $stateTax / $state) . '</td>
</tr>';
echo '<tr>
<td align="right">City Tax Amount</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td align="right">City Taxable Sales</td>
<td>' . sprintf('%.2f', $cityTax / $city) . '</td>
</tr>';
echo '<tr>
<td align="right">County Tax Amount</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td align="right">County Taxable Sales</td>
<td>' . sprintf('%.2f', $countyTax / $county) . '</td>
</tr>';
echo '<tr>
<td align="right">Deli Tax Amount</td>
<td>' . sprintf('%.2f', $deliTax) . '</td>
<td align="right">Deli Taxable Sales</td>
<td>' . sprintf('%.2f', $deliTax / $deli) . '</td>
</tr>';
echo '<tr><th>Tax Collected on Cannabis rate items</th>
<th>' . sprintf('%.2f', $collected[3]) . '</th>
<th>Cannabis Taxable Sales</th>
<th>' . sprintf('%.2f', $collected[3]/($canna)) . '</th>
</tr>';
$stateTax = ($collected[1] * ($state/($state+$city+$county)))
+ ($collected[2] * ($state/($state+$city+$deli+$county)));
$cityTax = ($collected[1] * ($city/($state+$city+$county)))
+ ($collected[2] * ($city/($state+$city+$deli+$county)));
$countyTax = ($collected[1] * ($county/($state+$city+$county)))
+ ($collected[2] * ($county/($state+$city+$deli+$county)));
$deliTax = $collected[2] * ($deli/($state+$city+$deli+$county));
echo '<tr><th colspan="4">State Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $stateTax / $state) . '</td>
</tr>';
echo '<tr><th colspan="4">City Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $cityTax / $city) . '</td>
</tr>';
echo '<tr><th colspan="4">County Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $countyTax / $county) . '</td>
</tr>';
echo '<tr><th colspan="4">Deli Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $deliTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $deliTax / $deli) . '</td>
</tr>';
echo '</table><br />';
}
} else {
$newTaxQ = 'SELECT MAX(description) AS description,
SUM(regPrice) AS ttl,
numflag AS taxID
FROM trans_archive.bigArchive AS t
WHERE datetime BETWEEN ? AND ?
AND ' . DTrans::isStoreID($store, 't') . '
AND upc=\'TAXLINEITEM\'
AND ' . DTrans::isNotTesting() . '
GROUP BY taxID';
$prep = $sql->prepare($newTaxQ);
$res = $sql->execute($prep, $args);
$collected = array(1 => 0.00, 2=>0.00);
while ($row = $sql->fetch_row($res)) {
$collected[$row['taxID']] = $row['ttl'];
}
$state = 0.06875;
$city = 0.010;
$deli = 0.0225;
$county = 0.005;
var_dump($state + $city + $county);
var_dump($state + $city + $county + $deli);
$startDT = new DateTime($start);
$noCounty = new DateTime('2017-10-01');
if ($startDT >= $noCount) {
//$county = 0;
}
$stateTax = $collected[1] * ($state/($state+$city+$county));
$cityTax = $collected[1] * ($city/($state+$city+$county));
$countyTax = $collected[1] * ($county/($state+$city+$county));
echo '<table border="1" cellspacing="0" cellpadding="4">';
echo '<tr>
<th>Tax Collected on Regular rate items</th>
<th>Regular Taxable Sales</th>
<th>Regular State Amount</th>
<th>Regular City Amount</th>
<th>Regular County Amount</th>
<th>Tax Collected on Deli rate items</th>
<th>Deli Taxable Sales</th>
<th>Deli State Amount</th>
<th>Deli City Amount</th>
<th>Deli County Amount</th>
<th>Deli Prepared Foods Amount</th>
</tr>
<tr>
<th>' . sprintf('%.2f', $collected[1]) . '</th>
<th>' . sprintf('%.2f', $collected[1]/($state+$city+$county)) . '</th>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>';
$stateTax = $collected[2] * ($state/($state+$city+$deli+$county));
$cityTax = $collected[2] * ($city/($state+$city+$deli+$county));
$deliTax = $collected[2] * ($deli/($state+$city+$deli+$county));
$countyTax = $collected[2] * ($county/($state+$city+$deli+$county));
echo '
<th>' . sprintf('%.2f', $collected[2]) . '</th>
<th>' . sprintf('%.2f', $collected[2]/($state+$city+$deli+$county)) . '</th>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td>' . sprintf('%.2f', $deliTax) . '</td>
</tr>';
$stateTax = $collected[1] * ($state/($state+$city+$county));
$cityTax = $collected[1] * ($city/($state+$city+$county));
$countyTax = $collected[1] * ($county/($state+$city+$county));
echo '<tr>
<td align="right">State Tax Amount</td>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td align="right">State Taxable Sales</td>
<td>' . sprintf('%.2f', $stateTax / $state) . '</td>
</tr>';
echo '<tr>
<td align="right">City Tax Amount</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td align="right">City Taxable Sales</td>
<td>' . sprintf('%.2f', $cityTax / $city) . '</td>
</tr>';
echo '<tr>
<td align="right">County Tax Amount</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td align="right">County Taxable Sales</td>
<td>' . sprintf('%.2f', $countyTax / $county) . '</td>
</tr>';
echo '<tr><th>Tax Collected on Deli rate items</th>
<th>' . sprintf('%.2f', $collected[2]) . '</th>
<th>Deli Taxable Sales</th>
<th>' . sprintf('%.2f', $collected[2]/($state+$city+$deli+$county)) . '</th>
</tr>';
$stateTax = $collected[2] * ($state/($state+$city+$deli+$county));
$cityTax = $collected[2] * ($city/($state+$city+$deli+$county));
$deliTax = $collected[2] * ($deli/($state+$city+$deli+$county));
$countyTax = $collected[2] * ($county/($state+$city+$deli+$county));
echo '<tr>
<td align="right">State Tax Amount</td>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td align="right">State Taxable Sales</td>
<td>' . sprintf('%.2f', $stateTax / $state) . '</td>
</tr>';
echo '<tr>
<td align="right">City Tax Amount</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td align="right">City Taxable Sales</td>
<td>' . sprintf('%.2f', $cityTax / $city) . '</td>
</tr>';
echo '<tr>
<td align="right">County Tax Amount</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td align="right">County Taxable Sales</td>
<td>' . sprintf('%.2f', $countyTax / $county) . '</td>
</tr>';
echo '<tr>
<td align="right">Deli Tax Amount</td>
<td>' . sprintf('%.2f', $deliTax) . '</td>
<td align="right">Deli Taxable Sales</td>
<td>' . sprintf('%.2f', $deliTax / $deli) . '</td>
</tr>';
$stateTax = ($collected[1] * ($state/($state+$city+$county)))
+ ($collected[2] * ($state/($state+$city+$deli+$county)));
$cityTax = ($collected[1] * ($city/($state+$city+$county)))
+ ($collected[2] * ($city/($state+$city+$deli+$county)));
$countyTax = ($collected[1] * ($county/($state+$city+$county)))
+ ($collected[2] * ($county/($state+$city+$deli+$county)));
$deliTax = $collected[2] * ($deli/($state+$city+$deli+$county));
echo '<tr><th colspan="4">State Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $stateTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $stateTax / $state) . '</td>
</tr>';
echo '<tr><th colspan="4">City Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $cityTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $cityTax / $city) . '</td>
</tr>';
echo '<tr><th colspan="4">County Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $countyTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $countyTax / $county) . '</td>
</tr>';
echo '<tr><th colspan="4">Deli Totals</th></tr>';
echo '<tr>
<td align="right">Tax Collected</td>
<td>' . sprintf('%.2f', $deliTax) . '</td>
<td align="right">Taxable Sales</td>
<td>' . sprintf('%.2f', $deliTax / $deli) . '</td>
</tr>';
echo '</table>';
}