<?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("index.php","",$_SERVER['REQUEST_URI']);
} else {
    $storeInfo = FormLib::storePicker();
    echo '<form action="index.php" method="get">'
        . '<input type="date" name="start" />'
        . '<input type="date" name="end" />'
        . $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;

$year = date('Y');
$month = date('n');
$stamp = mktime(0,0,0,$month-1,1,$year);
$dlog = "is4c_trans.dlog_90_view";
$start = date("Y-m-01",$stamp);
if (FormLib::get('start') != '') {
    $start = FormLib::get('start');
}
$end = date("Y-m-t",$stamp);
if (FormLib::get('end') != '') {
    $end = FormLib::get('end');
}
$args = array($start.' 00:00:00',$end.' 23:59:59', $store);

$output = \COREPOS\Fannie\API\data\DataCache::getFile("monthly");
if (!$output || isset($_REQUEST['recache'])){
    if (isset($_REQUEST['recache'])) {
        $_SERVER['REQUEST_URI'] = $_SERVER['PHP_SELF']; // remove recache from URI
        $_SERVER['REQUEST_URI'] = str_replace("index.php","",$_SERVER['REQUEST_URI']);
    }
    ob_start();

    echo ' for period <br>from: <b>'. $start . '</b> to: <b>' . $end . '</b><br>';

    $query1="select t.department,
    s.superID,
    d.salesCode,d.dept_name,
    SUM(t.total),
    t.store_id
    FROM $dlog as t 
        INNER JOIN departments as d ON t.department = d.dept_no
        LEFT JOIN MasterSuperDepts AS s ON s.dept_ID = d.dept_no    
    WHERE tdate BETWEEN ? AND ?
        AND " . DTrans::isStoreID($store, 't') . "
        AND t.department <> 0
        AND t.trans_type <> 'T'
        AND t.trans_type IN ('I', 'D')
    GROUP BY
    s.superID,t.department,d.dept_name,d.salesCode,t.store_id
    order by s.superID,t.department";

    $query2 = "SELECT 
        CASE WHEN d.description='WIC' THEN 'WIC' ELSE t.TenderName END as TenderName,
        -sum(d.total) as total, COUNT(d.total)
    FROM $dlog AS d
        left join tenders as t ON d.trans_subtype=t.TenderCode
    WHERE d.tdate BETWEEN ? AND ?
        AND " . DTrans::isStoreID($store, 'd') . "
    AND d.trans_type='T'
    AND d.trans_subtype <> 'MA'
    and t.TenderName <> 'MAD Coupon'
    AND d.trans_subtype <> 'IC'
    and d.total <> 0
    GROUP BY CASE WHEN d.description='WIC' THEN 'WIC' ELSE t.TenderName END";

    $queryStoreCoupons = "
        SELECT 
            CASE 
                WHEN h.description is NOT NULL THEN h.description
                WHEN d.upc <> '0' THEN d.upc
                ELSE 'Generic InStore Coupon'
            END as TenderName,
            -sum(d.total) as total, 
            COUNT(d.total)
        FROM $dlog AS d
            LEFT JOIN houseCoupons AS h ON d.upc=concat('00499999', lpad(convert(h.coupID, char), 5, '0'))
        WHERE d.tdate BETWEEN ? AND ?
            AND " . DTrans::isStoreID($store, 'd') . "
            AND d.trans_type='T'
            AND d.trans_subtype = 'IC'
            and d.total <> 0
        GROUP BY TenderName";

    $query3 = "SELECT c.salesCode,s.superID,sum(l.total) as total 
    FROM $dlog as l left join MasterSuperDepts AS s ON
    l.department = s.dept_ID INNER JOIN departments AS c
    ON l.department = c.dept_no
    WHERE l.tdate BETWEEN ? AND ?
        AND " . DTrans::isStoreID($store, 'l') . "
    AND l.department < 600 AND l.department <> 0
    AND l.trans_type <> 'T'
    AND l.trans_type IN ('I','D')
    GROUP BY c.salesCode,s.superID
    order by c.salesCode,s.superID";

    $query13 = "SELECT   m.memDesc,SUM(d.total) AS Sales
    FROM         $dlog d INNER JOIN
                  custdata c ON d.card_no = c.CardNo INNER JOIN
                  memtype m ON c.memType = m.memtype
    WHERE d.tdate BETWEEN ? AND ?
        AND " . DTrans::isStoreID($store, 'd') . "
    AND (d.department < 600) AND d.department <> 0 AND (c.personnum= 1 or c.personnum is null)
    AND d.trans_type <> 'T'
    GROUP BY m.memDesc
    ORDER BY m.memDesc";

    $query21 = "SELECT m.memdesc, COUNT(d.card_no)
    FROM is4c_trans.transarchive AS d left join memtype m on d.memType = m.memtype
    WHERE datetime BETWEEN ? AND ? AND (d.memType <> 4)
        AND " . DTrans::isStoreID($store, 'd') . "
    AND register_no<>99 and emp_no<>9999 AND trans_status NOT IN ('X','Z')
    AND trans_id=1 AND upc <> 'RRR'
    GROUP BY m.memdesc";

    $query20 = "SELECT   SUM(d.total) AS Sales 
            FROM $dlog d LEFT JOIN
            custdata c ON d.card_no = c.CardNo LEFT JOIN
            memtype m ON c.memType = m.memtype
            WHERE d.tdate BETWEEN ? AND ?
            AND " . DTrans::isStoreID($store, 'd') . "
            AND (d.department < 600) AND d.department <> 0 
            AND d.trans_type <> 'T'
            AND (c.personnum= 1 or c.personnum is null)";

    $query8 = "SELECT     m.memDesc, SUM(d.total) AS Discount 
    FROM         $dlog d INNER JOIN
                  custdata c ON d.card_no = c.CardNo INNER JOIN
                  memtype m ON c.memType = m.memtype
    WHERE d.tdate BETWEEN ? AND ?
    AND " . DTrans::isStoreID($store, 'd') . "
    AND (d.upc = 'DISCOUNT') AND c.personnum= 1
    GROUP BY c.memType, m.memDesc, d.upc
    ORDER BY c.memType";

    $query9 = "SELECT     d.upc, SUM(d.total) AS discount
    FROM         $dlog d INNER JOIN
                  custdata c ON d.card_no = c.CardNo INNER JOIN
                  memtype m ON c.memType = m.memtype
    WHERE d.tdate BETWEEN ? AND ?
    AND " . DTrans::isStoreID($store, 'd') . "
    AND (d.upc = 'DISCOUNT') AND c.personnum = 1
    GROUP BY d.upc";

    $query11 = "SELECT  sum(total) as tax_collected
    FROM $dlog as d 
    WHERE d.tdate BETWEEN ? AND ?
    AND " . DTrans::isStoreID($store, 'd') . "
    AND (d.upc = 'tax')
    GROUP BY d.upc";

    $query23="SELECT d.salesCode,sum(l.total) as total,card_no, 
    (sum(l.total)-(sum(l.total) * d.margin)) as cost
    FROM $dlog as l left join departments as d on l.department = d.dept_no
        INNER JOIN custdata AS c ON c.CardNo=l.card_no AND c.personNum=1
    WHERE l.tdate BETWEEN ? AND ?
    AND " . DTrans::isStoreID($store, 'l') . "
    AND (l.department < 600 or l.department = 902) AND l.department <> 0
    AND l.trans_type <> 'T'
    AND card_no BETWEEN 5500 AND 5950
    AND c.memType=4
    GROUP BY d.salesCode,card_no,d.margin
    order by card_no,d.salesCode";

    $queryRRR = "
    SELECT sum(case when volSpecial is null then 0 
        when volSpecial > 100 then 1
        else volSpecial end) as qty
    from
    is4c_trans.transarchive as t
    where upc = 'RRR'
    and t.datetime BETWEEN ? AND ?
    AND " . DTrans::isStoreID($store, 't') . "
    and emp_no <> 9999 and register_no <> 99
    and trans_status <> 'X'";


    echo '<font size = 3>';
    echo '<br>';
    echo 'Sales by department';
    echo '<br>---------------------------';
    echo '<table><td width=120><u><font size=2><b>Dept No</b></u></font></td>
          <td width=120><u><font size=2><b>Department</b></u></font></td>
          <td width=120><u><font size=2><b>pCode</b></u></font></td>
        <td width=120><u><font size=2><b>Group</b></u></font></td>
          <td width=120><u><font size=2><b>Sales</b></u></font></td>
        </table>';
    $prep = $dbc->prepare($query1);
    $res =  $dbc->execute($query1, $args);
    $depts = array();
    $supers = array();
    $misc = array();
    while ($w = $dbc->fetchRow($res)) {
        $code = StandardAccounting::extend($w['salesCode'], $w['store_id']);
        $w['salesCode'] = $code;
        $w[2] = $code;
        $s = $w['superID'];
        if ($s > 0) {
            $depts[] = $w;
        } else {
            $misc[] = $w;
        }
        if (!isset($supers[$s])) {
            $supers[$s] = array($s, 0.0);
        } 
        $supers[$s][1] += $w[4];
    }
    if ($store == 50) {
        $depts = array();
        $amts = array(
            '41201-1' => 0,
            '41201-2' => 0,
            '41205-1' => 0,
            '41205-2' => 0,
            '41600-1' => 0,
            '41600-2' => 0,
        );
        $webQ="select t.department,
        s.superID,
        d.salesCode,d.dept_name,
        SUM(t.total),
        YEAR(tdate), MONTH(tdate), DAY(tdate), emp_no, register_no, trans_no
        FROM $dlog as t 
            INNER JOIN departments as d ON t.department = d.dept_no
            LEFT JOIN MasterSuperDepts AS s ON s.dept_ID = d.dept_no    
        WHERE tdate BETWEEN ? AND ?
            AND " . DTrans::isStoreID($store, 't') . "
            AND t.department <> 0
            AND s.superID > 0
            AND t.trans_type <> 'T'
            AND t.trans_type IN ('I', 'D')
        GROUP BY
        s.superID,t.department,d.dept_name,d.salesCode,
        YEAR(tdate), MONTH(tdate), DAY(tdate), emp_no, register_no, trans_no
        order by s.superID,t.department";
        $storeP = $dbc->prepare("SELECT description FROM {$dlog} WHERE trans_subtype='CM' AND description LIKE '%STORE%'
            AND tdate BETWEEN ? AND ? AND emp_no=? AND register_no=? AND trans_no=?");
        $webP = $dbc->prepare($webQ);
        $webR = $dbc->execute($webP, $args);
        while ($row = $dbc->fetchRow($webR)) {
            $key = $row['salesCode'] . '-';
            $tdate = date('Y-m-d', mktime(0, 0, 0, $row[6], $row[7], $row[5]));
            $storeCM = $dbc->getValue($storeP, array($tdate, $tdate . ' 23:59:59', $row['emp_no'], $row['register_no'], $row['trans_no']));
            list(,$storeID) = explode(' ', $storeCM);
            $key .= $storeID;
            $amts[$key] += $row[4];
        }
        $depts = array(
            array(80, 3, '41205-01-20', 'DOutsideBakery', $amts['41205-1']),
            array(80, 3, '41205-02-20', 'DOutsideBakery', $amts['41205-2']),
            array(83, 3, '41201-01-20', 'DTHANKSGIVING', $amts['41201-1']),
            array(83, 3, '41201-02-20', 'DTHANKSGIVING', $amts['41201-2']),
            array(83, 3, '41600-01-20', 'BREAD', $amts['41600-1']),
            array(83, 3, '41600-02-20', 'BREAD', $amts['41600-2']),
        );
    }
    unset($supers[0]);
    select_to_table3($depts,5,0,'ffffff');
    echo '<b>Total Sales by Group</b>';
    select_to_table3($supers,2,0,'ffffff');

    echo '<font size = 2>';
    echo '<br>';
    echo 'Tenders';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>Type</b></u></font></td>
          <td width=120><u><font size=2><b>Amount</b></u></font></td>
          <td width=120><u><font size=2><b>Count</b></u></font></td></table>';
    select_to_table($query2,$args,0,'ffffff', true);
    select_to_table($queryStoreCoupons,$args,0,'ffffff');
    echo '<br>';
    echo 'Sales';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>pCode</b></u></font></td>
          <td width=120><u><font size=2><b>Sales</b></u></font></td></table>';
    $prep = $dbc->prepare($query3);
    $res = $dbc->execute($query3, $args);
    $sales = array();
    $ttl = 0.0;
    while ($w = $dbc->fetchRow($res)) {
        $sales[] = $w;
        $ttl += $w[2];
    }
    select_to_table3($sales,3,0,'ffffff');
    echo '<b>Total Sales</b>';

    select_to_table3(array(array($ttl)),1,0,'ffffff');

    echo '<br>';
    echo 'Other income';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>Dept</b></u></font></td>
          <td width=120><u><font size=2><b>Description</b></u></font></td>
          <td width=120><u><font size=2><b>Amount</b></u></font></td></table>';
    select_to_table3($misc,5,0,'ffffff');
    echo 'Discounts';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>Mem Type</b></u></font></td>
          <td width=120><u><font size=2><b>Discounts</b></u></font></td></table>';
    select_to_table($query8,$args,0,'ffffff');
    select_to_table($query9,$args,0,'ffffff');
    echo '<br>';
    echo 'Member Sales';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>Mem Type</b></u></font></td>
          <td width=120><u><font size=2><b>Sales</b></u></font></td>
          <td width=120><u><font size=2><b>Transactions</b></u></font></td>
        </table>';
    $prep = $dbc->prepare($query13);
    $res = $dbc->execute($prep, $args);
    $mems = array();
    $ttl = 0.0;
    while ($w = $dbc->fetchRow($res)) {
        $mems[] = $w;
        $ttl += $w[1];
    }
    $query13 = "SELECT   m.memDesc,SUM(d.total) AS Sales,
        YEAR(tdate), MONTH(tdate), DAY(tdate), emp_no, register_no, trans_no
    FROM         $dlog d INNER JOIN
                  custdata c ON d.card_no = c.CardNo INNER JOIN
                  memtype m ON c.memType = m.memtype
    WHERE d.tdate BETWEEN ? AND ?
        AND " . DTrans::isStoreID($store, 'd') . "
    AND (d.department < 600) AND d.department <> 0 AND (c.personnum= 1 or c.personnum is null)
    AND d.trans_type <> 'T'
    GROUP BY m.memDesc,
        YEAR(tdate), MONTH(tdate), DAY(tdate), emp_no, register_no, trans_no
    ORDER BY m.memDesc";
    if ($store == 50) {
        $storeP = $dbc->prepare("SELECT description FROM {$dlog} WHERE trans_subtype='CM' AND description LIKE '%STORE%'
            AND tdate BETWEEN ? AND ? AND emp_no=? AND register_no=? AND trans_no=?");
        $prep = $dbc->prepare($query13);
        $res = $dbc->execute($prep, $args);
        $mSums = array();
        $mCounts = array();
        while ($w = $dbc->fetchRow($res)) {
            $key = $w['memDesc'];
            $tdate = date('Y-m-d', mktime(0, 0, 0, $w[3], $w[4], $w[2]));
            $storeCM = $dbc->getValue($storeP, array($tdate, $tdate . ' 23:59:59', $w['emp_no'], $w['register_no'], $w['trans_no']));
            list(,$storeID) = explode(' ', $storeCM);
            if ($storeID == 1) {
                $key .= ' HS';
            } elseif ($storeID == 2) {
                $key .= ' DN';
            }
            if (!isset($mSums[$key])) {
                $mSums[$key] = 0;
                $mCounts[$key] = 0;
            }
            $mSums[$key] += $w[1];
            $mCounts[$key] += 1;
        }
        $mems = array();
        foreach ($mSums as $k => $v) {
            $mems[] = array($k, $v, $mCounts[$k]);
        }
    }
    select_to_table3($mems,3,0,'ffffff');
    select_to_table3(array(array($ttl)),1,0,'ffffff');
    echo '<br>';
    echo 'Nabs';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>pCode</b></u></font></td>
          <td width=120><u><font size=2><b>Retail</b></u></font></td>
          <td>Dept Number</td><td>WholeSale</td></table>';
    $prep = $dbc->prepare($query23);
    $res = $dbc->execute($prep, $args);
    $nabs = array();
    $bycode = array();
    while ($w = $dbc->fetchRow($res)) {
        $nabs[] = $w;
        $code = $w[0];
        if (!isset($bycode[$code])) {
            $bycode[$code] = array($code, 0.0, 0.0);
        }
        $bycode[$code][1] += $w['total'];
        $bycode[$code][2] += $w['cost'];
    }
    select_to_table3($nabs,4,0,'ffffff');
    select_to_table3($bycode,3,0,'ffffff');
    echo '<br>';
    echo 'Transactions';
    echo '<br>------------------------------';
    echo '<table><td width=120><u><font size=2><b>Mem Type</b></u></font></td>
          <td width=120><u><font size=2><b>Transactions</b></u></font></td></table>';
    select_to_table($query21,$args,0,'ffffff');
    echo '<br>';
    echo '<br>';
    echo '<br>';
    echo '<b>Actual Tax Collected</b>';
    select_to_table($query11,$args,0,'ffffff');

    echo '<br>';
    echo '<b>RRR Coupons Redeemed</b>';
    select_to_table($queryRRR,$args,0,'ffffff');

    echo '</font>';
    echo "</font>
        </body>
        </html>";

    $output = ob_get_contents();
    \COREPOS\Fannie\API\data\DataCache::putFile("monthly",$output);
    ob_end_clean();
}
echo $output;

    $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 is4c_trans.transarchive AS t
                WHERE datetime BETWEEN ? AND ?
                    AND ' . DTrans::isStoreID($store, 't') . '
                    AND upc=\'TAXLINEITEM\'
                    AND trans_status <> \'X\'
                    AND ' . DTrans::isNotTesting() . '
                GROUP BY taxID';
    $prep = $sql->prepare($newTaxQ);
    $res = $sql->execute($prep, $args);
    $collected = array(1 => 0.00, 2=>0.00, 3=>0.00);
    while ($row = $sql->fetch_row($res)) {
        $collected[$row['taxID']] = $row['ttl'];
    }
    $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 >= $noCounty) {
        //$county = 0;
    }
    echo '<table border="1" cellspacing="0" cellpadding="4">';
    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>';
    }

