public function get_date1_date2_handler()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$dlog = DTransactionsModel::selectDlog($this->date1, $this->date2);
$dtrans = DTransactionsModel::selectDtrans($this->date1, $this->date2);
$openQ = '
SELECT YEAR(tdate) AS year,
MONTH(tdate) AS month,
DAY(tdate) AS day,
emp_no,
register_no,
trans_no
FROM ' . $dlog . ' AS d
INNER JOIN MasterSuperDepts AS m ON m.dept_ID=d.department
WHERE tdate BETWEEN ? AND ?
AND trans_type=\'D\'
AND m.superID <> 0
GROUP BY YEAR(tdate),
MONTH(tdate),
DAY(tdate),
emp_no,
register_no,
trans_no
HAVING SUM(total) <> 0';
$badQ = '
SELECT upc
FROM ' . $dtrans . ' AS d
WHERE datetime BETWEEN ? AND ?
AND emp_no=?
AND register_no=?
AND trans_no=?
AND trans_type=\'L\'
AND description=\'BADSCAN\'
AND d.upc LIKE \'0%\'
AND d.upc NOT LIKE \'00000000000%\'';
$openP = $dbc->prepare($openQ);
$badP = $dbc->prepare($badQ);
$filter = FormLib::get('badscans', false);
$this->receipts = array();
$openR = $dbc->execute($openP, array($this->date1 . ' 00:00:00', $this->date2 . ' 23:59:59'));
while ($openW = $dbc->fetchRow($openR)) {
$ts = mktime(0, 0, 0, $openW['month'], $openW['day'], $openW['year']);
if ($filter) {
$args = array(date('Y-m-d 00:00:00', $ts), date('Y-m-d 23:59:59', $ts), $openW['emp_no'], $openW['register_no'], $openW['trans_no']);
if (FormLib::get('upc') != '') {
$args[] = FormLib::get('upc');
}
$badR = $dbc->execute($badP, $args);
if (!$badR || $dbc->num_rows($badR) == 0) {
continue;
}
}
$this->receipts[] = array('date' => date('Y-m-d', $ts), 'trans_num' => $openW['emp_no'] . '-' . $openW['register_no'] . '-' . $openW['trans_no']);
}
return true;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('TRANS_DB'));
$dtrans = new DTransactionsModel($dbc);
$columns = $dtrans->getColumns();
foreach ($columns as $name => $info) {
$this->report_headers[] = $name;
}
list($emp, $reg, $trans) = explode('-', FormLib::get('trans'), 3);
$date = FormLib::get('date');
$table = DTransactionsModel::selectDtrans($date);
$query = $dbc->prepare('
SELECT *
FROM ' . $table . '
WHERE datetime BETWEEN ? AND ?
AND emp_no = ?
AND register_no = ?
AND trans_no = ?
ORDER BY trans_id
');
$result = $dbc->execute($query, array($date . ' 00:00:00', $date . ' 23:59:59', $emp, $reg, $trans));
$data = array();
while ($w = $dbc->fetchRow($result)) {
$record = array();
foreach ($columns as $c => $info) {
if (isset($w[$c])) {
$record[] = $w[$c];
} else {
$record[] = '';
}
}
$data[] = $record;
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$rate_models = new TaxRatesModel($dbc);
$rates = array();
foreach ($rate_models->find() as $obj) {
$rates[$obj->id()] = $obj;
}
$perRateSQL = '';
$collectedSQL = '';
foreach ($rates as $id => $obj) {
$perRateSQL .= sprintf('
SUM(CASE WHEN discountable=0 AND tax=%d THEN total ELSE 0 END) AS noDisc%d,
SUM(CASE WHEN discountable=1 AND tax=%d THEN total ELSE 0 END) AS yesDisc%d,', $id, $id, $id, $id);
$collectedSQL .= sprintf('
SUM(CASE WHEN upc=\'TAXLINEITEM\' AND numflag=%d THEN regPrice ELSE 0 END) AS collected%d,', $id, $id);
$this->report_headers[] = $obj->description() . ' Expected';
$this->report_headers[] = $obj->description() . ' Net Discount';
$this->report_headers[] = $obj->description() . ' Actual';
$this->report_headers[] = $obj->description() . ' Forgiven';
}
$this->report_headers[] = 'Total Expected';
$this->report_headers[] = 'Total Net Discount';
$this->report_headers[] = 'Total Actual';
$this->report_headers[] = 'Total Forgiven';
$dtrans = DTransactionsModel::selectDtrans($date1, $date2);
$query = '
SELECT YEAR(datetime) AS year,
MONTH(datetime) AS month,
DAY(datetime) AS day,
register_no,
emp_no,
trans_no,
' . $perRateSQL . '
MAX(percentDiscount) AS pd,
SUM(CASE WHEN trans_subtype IN (\'EF\',\'FS\') THEN -total ELSE 0 END) AS fsTender,
' . $collectedSQL . '
SUM(CASE WHEN upc=\'TAX\' THEN total ELSE 0 END) AS totalTax
FROM ' . $dtrans . ' AS d
LEFT JOIN taxrates AS t ON d.tax=t.id
WHERE datetime BETWEEN ? AND ?
AND trans_status NOT IN (\'X\',\'Z\')
AND emp_no <> 9999
AND register_no <> 99
GROUP BY
YEAR(datetime),
MONTH(datetime),
DAY(datetime),
register_no,
emp_no,
trans_no
HAVING SUM(CASE WHEN d.tax > 0 THEN 1 ELSE 0 END) <> 0
AND SUM(CASE WHEN trans_subtype IN (\'EF\',\'FS\') THEN -total ELSE 0 END) <> 0';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, array($date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$data = array();
while ($w = $dbc->fetchRow($res)) {
$record = array(date('Y-m-d', mktime(0, 0, 0, $w['month'], $w['day'], $w['year'])), $w['emp_no'] . '-' . $w['register_no'] . '-' . $w['trans_no'], sprintf('%d%%', $w['pd']), sprintf('%.2f', $w['fsTender']));
$all = new stdClass();
$all->total = 0;
$all->net = 0;
$all->actual = 0;
$all->forgiven = 0;
foreach ($rates as $id => $obj) {
$total = sprintf('%.2f', ($w['noDisc' . $id] + $w['yesDisc' . $id]) * $obj->rate());
$net = sprintf('%.2f', ($w['noDisc' . $id] + (1 - $w['pd'] / 100.0) * $w['yesDisc' . $id]) * $obj->rate());
$actual = sprintf('%.2f', $w['collected' . $id]);
$forgiven = $net - $actual;
$record[] = $total;
$record[] = $net;
$record[] = $actual;
$record[] = $forgiven;
$all->total += $total;
$all->net += $net;
$all->actual += $actual;
$all->forgiven += $forgiven;
}
$record[] = $all->total;
$record[] = $all->net;
$record[] = $all->actual;
$record[] = $all->forgiven;
$data[] = $record;
}
return $data;
}
function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_COOP_ID;
$d1 = $this->form->date1;
$d2 = $this->form->date2;
$dept = FormLib::get_form_value('dept', 0);
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$dtrans = DTransactionsModel::selectDtrans($d1, $d2);
$datestamp = $dbc->identifier_escape('datetime');
if (isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto') {
$shrinkageUsers = " AND t.card_no not between 99990 and 99998";
} else {
$shrinkageUsers = "";
}
// The eventual return value.
$data = array();
$taxNames = array(0 => '');
$tQ = $dbc->prepare_statement("SELECT id, rate, description FROM taxrates WHERE id > 0 ORDER BY id");
$tR = $dbc->exec_statement($tQ);
// Try generating code in this loop for use in SELECT and reporting.
// See SalesAndTaxTodayReport.php
while ($trow = $dbc->fetch_array($tR)) {
$taxNames[$trow['id']] = $trow['description'];
}
/* Using department settings at the time of sale.
* I.e. The department# from the transaction.
* If that department# no longer exists or is different then the report will be wrong.
* This does not use a departments table contemporary with the transactions.
* [0]Dept_name [1]Cost, [2]HST, [3]GST, [4]Sales, [x]Qty, [x]superID, [x]super_name
*/
$departments = $dbc->tableDefinition('departments');
if ($dept == 0) {
// Change varname to sales or totals
$costs = "SELECT\n d.dept_name dname,\n sum(CASE WHEN t.trans_type = 'I' THEN t.cost \n WHEN t.trans_type = 'D' AND d.margin > 0.00 \n THEN t.total - (t.total * d.margin) END) AS costs,\n sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,\n sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,\n sum(t.total) AS sales,\n sum(t.quantity) AS qty,\n s.superID AS sid,\n s.super_name AS sname\n FROM\n {$dtrans} AS t LEFT JOIN\n departments AS d ON d.dept_no=t.department LEFT JOIN\n MasterSuperDepts AS s ON t.department=s.dept_ID LEFT JOIN ";
// use margin column from departments if present
if (!isset($departments['margin']) && $dbc->tableExists('deptMargin')) {
$costs .= ' deptMargin AS m ON t.department=m.dept_id LEFT JOIN ';
$costs = str_replace('d.margin', 'm.margin', $costs);
}
$costs .= " taxrates AS x ON t.tax=x.id\n WHERE \n ({$datestamp} BETWEEN ? AND ?)\n AND (s.superID > 0 OR s.superID IS NULL) \n AND t.trans_type in ('I','D')\n AND t.trans_status not in ('D','X','Z')\n AND t.emp_no not in (9999){$shrinkageUsers}\n AND t.register_no != 99\n AND t.upc != 'DISCOUNT'\n AND t.trans_subtype not in ('CP','IC')\n GROUP BY\n s.superID, s.super_name, d.dept_name, t.department\n ORDER BY\n s.superID, t.department";
} elseif ($dept == 1) {
$costs = "SELECT\n CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name END AS dname,\n sum(CASE WHEN t.trans_type = 'I' THEN t.cost \n WHEN t.trans_type = 'D' AND d.margin > 0.00 \n THEN t.total - (t.total * d.margin) END) AS costs,\n sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,\n sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,\n sum(t.total) AS sales,\n sum(t.quantity) AS qty,\n CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID END AS sid,\n CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END AS sname\n FROM\n {$dlog} AS t LEFT JOIN\n products AS p ON t.upc=p.upc LEFT JOIN\n departments AS d ON d.dept_no=t.department LEFT JOIN\n departments AS e ON p.department=e.dept_no LEFT JOIN\n MasterSuperDepts AS s ON s.dept_ID=p.department LEFT JOIN\n MasterSuperDepts AS r ON r.dept_ID=t.department LEFT JOIN ";
// use margin column from departments if present
if (!isset($departments['margin']) && $dbc->tableExists('deptMargin')) {
$costs .= ' deptMargin AS m ON p.department=m.dept_id LEFT JOIN ';
$costs = str_replace('d.margin', 'm.margin', $costs);
}
$costs .= "taxrates AS x ON t.tax=x.id\n WHERE\n ({$datestamp} BETWEEN ? AND ?)\n AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)\n OR (s.superID IS NULL AND r.superID IS NULL))\n AND t.trans_type in ('I','D')\n AND t.trans_status not in ('D','X','Z')\n AND t.emp_no not in (9999){$shrinkageUsers}\n AND t.register_no != 99\n AND t.upc != 'DISCOUNT'\n AND t.trans_subtype not in ('CP','IC')\n GROUP BY\n CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,\n CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END,\n CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,\n CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end\n ORDER BY\n CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,\n CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end";
}
$costsP = $dbc->prepare_statement($costs);
$costArgs = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$costsR = $dbc->exec_statement($costsP, $costArgs);
// Array in which totals used in the report are accumulated.
$supers = array();
$curSuper = 0;
$grandTotal = 0;
$this->grandCostsTotal = 0;
$this->grandSalesTotal = 0;
$this->grandTax1Total = 0;
$this->grandTax2Total = 0;
while ($row = $dbc->fetch_array($costsR)) {
if ($curSuper != $row['sid']) {
$curSuper = $row['sid'];
}
if (!isset($supers[$curSuper])) {
$supers[$curSuper] = array('name' => $row['sname'], 'qty' => 0.0, 'costs' => 0.0, 'sales' => 0.0, 'taxes1' => 0.0, 'taxes2' => 0.0, 'depts' => array());
}
$supers[$curSuper]['qty'] += $row['qty'];
$supers[$curSuper]['costs'] += $row['costs'];
$supers[$curSuper]['sales'] += $row['sales'];
$supers[$curSuper]['taxes1'] += $row['taxes1'];
$supers[$curSuper]['taxes2'] += $row['taxes2'];
$this->grandCostsTotal += $row['costs'];
$this->grandSalesTotal += $row['sales'];
$this->grandTax1Total += $row['taxes1'];
$this->grandTax2Total += $row['taxes2'];
// GROUP BY produces 1 row per dept. Values are sums.
$supers[$curSuper]['depts'][] = array('name' => $row['dname'], 'qty' => $row['qty'], 'costs' => $row['costs'], 'sales' => $row['sales'], 'taxes1' => $row['taxes1'], 'taxes2' => $row['taxes2']);
}
$superCount = 1;
foreach ($supers as $s) {
if ($s['sales'] == 0) {
continue;
}
// add department records
$superCostsSum = $s['costs'];
$superSalesSum = $s['sales'];
foreach ($s['depts'] as $d) {
$record = array($d['name'], sprintf('%.2f', $d['qty']), sprintf('$%.2f', $d['costs']));
$costPercent = 'n/a';
if ($this->grandCostsTotal > 0) {
$costPercent = sprintf('%.2f %%', $d['costs'] / $this->grandCostsTotal * 100);
}
$record[] = $costPercent;
$costPercent = 'n/a';
if ($superCostsSum > 0) {
$costPercent = sprintf('%.2f %%', $d['costs'] / $superCostsSum * 100);
}
$record[] = $costPercent;
$record[] = sprintf('$%.2f', $d['sales']);
$salePercent = 'n/a';
if ($this->grandSalesTotal > 0) {
$salePercent = sprintf('%.2f %%', $d['sales'] / $this->grandSalesTotal * 100);
}
$record[] = $salePercent;
$salePercent = 'n/a';
if ($superSalesSum > 0) {
$salePercent = sprintf('%.2f %%', $d['sales'] / $superSalesSum * 100);
}
$record[] = $salePercent;
$margin = 'n/a';
if ($d['sales'] > 0 && $d['costs'] > 0) {
$margin = sprintf('%.2f %%', 100 * ($d['sales'] - $d['costs']) / $d['sales']);
}
$record[] = $margin;
$record[] = sprintf('%.2f', $d['taxes2']);
$record[] = sprintf('%.2f', $d['taxes1']);
$data[] = $record;
}
// add super record
$record = array($s['name'], sprintf('%.2f', $s['qty']), sprintf('%.2f', $s['costs']));
$costPercent = 'n/a';
if ($this->grandCostsTotal > 0) {
$costPercent = sprintf('%.2f %%', $s['costs'] / $this->grandCostsTotal * 100);
}
$record[] = $costPercent;
$record[] = '';
$record[] = sprintf('%.2f', $s['sales']);
$salePercent = 'n/a';
if ($this->grandSalesTotal > 0) {
$salePercent = sprintf('%.2f %%', $s['sales'] / $this->grandSalesTotal * 100);
}
$record[] = $salePercent;
$record[] = '';
$margin = 'n/a';
if ($s['sales'] > 0 && $s['costs'] > 0) {
$margin = sprintf('%.2f %%', 100 * ($s['sales'] - $s['costs']) / $s['sales']);
}
$record[] = $margin;
$record[] = sprintf('%.2f', $s['taxes2']);
$record[] = sprintf('%.2f', $s['taxes1']);
$record['meta'] = FannieReportPage::META_BOLD;
$data[] = $record;
// Rather than start a new report, I'm just inserting a blank
// line between supers
$data[] = array('meta' => FannieReportPage::META_BLANK);
if ($superCount < count($supers) - 1) {
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
}
$superCount++;
}
// The summary of grand totals proportions.
// Headings
$record = array('', '', 'Costs', '', '', 'Sales', 'Profit', '', 'Margin %', isset($taxNames['2']) ? $taxNames['2'] : 'n/a', isset($taxNames['1']) ? $taxNames['1'] : 'n/a');
$record['meta'] = FannieReportPage::META_BOLD;
$data[] = $record;
// Grand totals
$record = array('WHOLE STORE', '', '$ ' . number_format($this->grandCostsTotal, 2), '', '', '$ ' . number_format($this->grandSalesTotal, 2), '$ ' . number_format($this->grandSalesTotal - $this->grandCostsTotal, 2), '');
$margin = 'n/a';
if ($this->grandSalesTotal > 0) {
$margin = number_format(($this->grandSalesTotal - $this->grandCostsTotal) / $this->grandSalesTotal * 100, 2) . ' %';
}
$record[] = $margin;
$record[] = '$ ' . number_format($this->grandTax2Total, 2);
$record[] = '$ ' . number_format($this->grandTax1Total, 2);
$data[] = $record;
$this->grandTTL = $grandTotal;
return $data;
// fetch_report_data()
}
break;
case 'last_week':
$d1 = date("Y-m-d", strtotime('last monday - 7 days'));
$d2 = date("Y-m-d", strtotime('last sunday'));
break;
case 'this_month':
$d1 = date("Y-m-d", strtotime('first day of this month'));
$d2 = date("Y-m-d");
break;
case 'last_month':
$d1 = date("Y-m-d", strtotime('first day of last month'));
$d2 = date("Y-m-d", strtotime('last day of last month'));
break;
}
}
$dlog = DTransactionsModel::selectDtrans($d1, $d2);
if (isset($_REQUEST['excel'])) {
header("Content-Disposition: inline; filename=costs_{$d1}_{$d2}.xls");
header("Content-type: application/vnd.ms-excel; name='excel'");
} else {
printf("<H3>General Costs: %s </H3>\n", $d1 == $d2 ? "For {$d1}" : "From {$d1} to {$d2}");
printf("<a href=index.php?date1=%s&date2=%s&dept=%s&submit=yes&excel=yes>Save to Excel</a>", $d1, $d2, $dept);
}
/* Using department settings at the time of sale.
* I.e. The department# from the transaction.
* If that department# no longer exists or is different then the report will be wrong.
* This does not use a departments table contemporary with the transactions.
*/
if ($dept == 0) {
$costs = "SELECT\n d.Dept_name,\n sum(CASE WHEN t.trans_type = 'I' THEN t.cost WHEN t.trans_type = 'D' AND m.margin > 1.00 THEN t.total / m.margin ELSE 0.00 END),\n sum(t.quantity),\n s.superID,\n s.super_name\n FROM\n {$dlog} AS t LEFT JOIN\n departments AS d ON d.dept_no=t.department LEFT JOIN\n MasterSuperDepts AS s ON t.department=s.dept_ID LEFT JOIN\n deptMargin AS m ON t.department=m.dept_id\n WHERE \n (datetime BETWEEN ? AND ?)\n AND (s.superID > 0 OR s.superID IS NULL) \n AND (t.trans_type in ('I','D'))\n AND ((t.trans_status not in ('D','X','Z')) and (t.emp_no not in (7000, 9999)) and (t.register_no <> 99))\n GROUP BY\n s.superID,s.super_name,d.dept_name,t.department\n ORDER BY\n s.superID,t.department";
} elseif ($dept == 1) {
<head>
<title>MOFF Results</title>
</head>
<body>
<?php
if (!isset($_GET['excel'])) {
echo "<a href=index.php?excel=yes>Excel</a><p />";
}
if (!class_exists("SQLManager")) {
require_once $FANNIE_ROOT . "src/SQLManager.php";
}
include '../../db.php';
$date = '2011-08-06';
$args = array($date, 21, 22);
$dlog = DTransactionsModel::selectDtrans($date);
//$dlog = str_replace("dlog","transarchive",$dlog);
//$dlog = "trans_archive.dbo.transArchive201008";
$query = $sql->prepare("SELECT DISTINCT t.upc, min(t.description), SUM(t.quantity),SUM(t.total),d.dept_name,d.salesCode,u.likeCode\n FROM {$dlog} as t \n LEFT JOIN departments as d on d.dept_no = t.department\n LEFT JOIN upcLike as u on t.upc=u.upc\n WHERE t.trans_type in ('I','D') and t.upc <> 'DISCOUNT' and\n datediff(dd,?,t.datetime) = 0 AND t.register_no\n in ( ?, ? )\n and trans_Status <> 'X'\n and emp_no <> 9999\n GROUP BY t.upc, d.dept_name,d.salesCode,u.likeCode\n ORDER BY t.upc");
//echo $query;
$result = $sql->execute($query, $args);
echo "<table border=1>\n";
//create table
echo "<tr>";
echo "<td>UPC</td>";
echo "<td>Description</td>";
echo "<td>Qty</td>";
echo "<td>Sales</td>";
echo "<td>Department</td>";
echo "<td>pCode</td>";
echo "<td>LikeCode</td>";
function get_data()
{
global $FANNIE_OP_DB;
$dlog = DTransactionsModel::selectDlog($this->startDate, $this->endDate);
$dtrans = DTransactionsModel::selectDtrans($this->startDate, $this->endDate);
$records = array();
$dateID = date('ymd', strtotime($this->endDate));
$dateStr = date('m/d/y', strtotime($this->endDate));
$names = array('CA' => 'Deposit', 'EF' => 'EBT Food/Cash', '41201' => 'DELI PREPARED FOODS', '41205' => 'DELI CHEESE', '41300' => 'PRODUCE', '41305' => 'SEEDS', '41310' => 'TRANSPLANTS', '41315' => 'GEN MERC/FLOWERS', '41400' => 'GROCERY', '41405' => 'GROCERY CLEANING, PAPER', '41407' => 'GROCERY BULK WATER', '41410' => 'BULK A', '41415' => 'BULK B', '41420' => 'COOL', '41425' => 'COOL BUTTER', '41430' => 'COOL MILK', '41435' => 'COOL FROZEN', '41500' => 'HABA BULK/SPICES & HERBS', '41505' => 'HABA BULK/PKG COFFEE', '41510' => 'HABA BODY CARE', '41515' => 'HABA VIT/MN/HRB/HOMEOPA', '41520' => 'GEN MERC/BOOKS', '41600' => 'GROCERY BAKERY FROM VEN', '41605' => 'GEN MERC/HOUSEWARES', '41610' => 'MARKETING', '41640' => 'GEN MERC/CARDS', '41645' => 'GEN MERC/MAGAZINES', '41700' => 'MEAT/POULTRY/SEAFOOD FR', '41705' => 'MEAT/POULTRY/SEAFOOD FZ');
$codes = array('CP' => 10740, 'GD' => 21205, 'SC' => 21200, 'MI' => 10710, 'IC' => 67710, 'MA' => 66600, 'RR' => 63380, 'OB' => 66600, 'AD' => 66600, 'RB' => 31140, 'NCGA' => 66600, 'Member Discounts' => 66600, 'Staff Discounts' => 61170);
$dbc = FannieDB::get($FANNIE_OP_DB);
$args = array($this->startDate . ' 00:00:00', $this->endDate . ' 23:59:59');
$tenderQ = "SELECT SUM(total) AS amount,\n CASE WHEN description='REBATE CHECK' THEN 'RB'\n WHEN trans_subtype IN ('CA','CK') THEN 'CA'\n WHEN trans_subtype IN ('CC','AX') THEN 'CC'\n WHEN trans_subtype IN ('EF','EC') THEN 'EF'\n WHEN trans_subtype = 'IC' AND upc='0049999900001' THEN 'OB'\n WHEN trans_subtype = 'IC' AND upc='0049999900002' THEN 'AD'\n ELSE trans_subtype END as type,\n MAX(CASE WHEN d.upc IN ('0049999900001','0049999900002') OR description='REBATE CHECK' \n THEN d.description ELSE TenderName END) as name\n FROM {$dlog} AS d LEFT JOIN\n tenders AS t ON d.trans_subtype=t.TenderCode\n WHERE trans_type='T'\n AND tdate BETWEEN ? AND ?\n AND department <> 703\n GROUP BY type HAVING SUM(total) <> 0 ORDER BY type";
$tenderP = $dbc->prepare_statement($tenderQ);
$tenderR = $dbc->exec_statement($tenderP, $args);
while ($w = $dbc->fetch_row($tenderR)) {
$coding = isset($codes[$w['type']]) ? $codes[$w['type']] : 10120;
$name = isset($names[$w['type']]) ? $names[$w['type']] : $w['name'];
$credit = $w['amount'] < 0 ? -1 * $w['amount'] : 0;
$debit = $w['amount'] > 0 ? $w['amount'] : 0;
$row = array($dateID, $dateStr, str_pad($coding, 9, '0', STR_PAD_RIGHT), $credit, $debit, $name);
$records[] = $row;
}
$discountQ = "SELECT SUM(total) as amount,\n CASE WHEN staff=1 OR memType IN (1,3) THEN 'Staff Discounts'\n ELSE 'Member Discounts' END as name\n FROM {$dlog} WHERE upc='DISCOUNT'\n AND total <> 0 AND tdate BETWEEN ? AND ?\n GROUP BY name ORDER BY name";
$discountP = $dbc->prepare_statement($discountQ);
$discountR = $dbc->exec_statement($discountP, $args);
while ($w = $dbc->fetch_row($discountR)) {
$coding = isset($codes[$w['name']]) ? $codes[$w['name']] : 66600;
$name = $w['name'];
$credit = $w['amount'] < 0 ? -1 * $w['amount'] : 0;
$debit = $w['amount'] > 0 ? $w['amount'] : 0;
$row = array($dateID, $dateStr, str_pad($coding, 9, '0', STR_PAD_RIGHT), $credit, $debit, $name);
$records[] = $row;
}
$salesQ = "SELECT sum(total) as amount, salesCode,\n MIN(dept_name) as name\n FROM {$dlog} AS d \n INNER JOIN departments as t\n ON d.department = t.dept_no\n INNER JOIN MasterSuperDepts AS m\n ON d.department=m.dept_ID\n WHERE d.trans_type IN ('I','D')\n AND tdate BETWEEN ? AND ?\n AND m.superID > 0\n AND register_no <> 20\n GROUP BY salesCode HAVING sum(total) <> 0 \n ORDER BY salesCode";
$salesP = $dbc->prepare_statement($salesQ);
$salesR = $dbc->exec_statement($salesP, $args);
while ($w = $dbc->fetch_row($salesR)) {
$coding = isset($codes[$w['salesCode']]) ? $codes[$w['salesCode']] : $w['salesCode'];
$name = isset($names[$w['salesCode']]) ? $names[$w['salesCode']] : $w['name'];
$credit = $w['amount'] < 0 ? -1 * $w['amount'] : 0;
$debit = $w['amount'] > 0 ? $w['amount'] : 0;
$row = array($dateID, $dateStr, str_pad($coding, 9, '0', STR_PAD_RIGHT), $credit, $debit, $name);
$records[] = $row;
}
$taxQ = "SELECT SUM(total) FROM {$dlog} WHERE tdate BETWEEN ? AND ?\n AND upc='TAX'";
$taxP = $dbc->prepare_statement($taxQ);
$taxR = $dbc->exec_statement($taxP, $args);
$taxes = 0.0;
if ($dbc->num_rows($taxR) > 0) {
$taxW = $dbc->fetch_row($taxR);
$taxes = $taxW[0];
}
$records[] = array($dateID, $dateStr, 211800000, 0, $taxes, 'Sales Tax Collected');
$salesQ = "SELECT sum(total) as amount, salesCode,\n MIN(dept_name) as name\n FROM {$dlog} AS d \n INNER JOIN departments as t\n ON d.department = t.dept_no\n INNER JOIN MasterSuperDepts AS m\n ON d.department=m.dept_ID\n WHERE d.trans_type IN ('I','D')\n AND tdate BETWEEN ? AND ?\n AND m.superID = 0\n AND d.department <> 703\n AND register_no <> 20\n GROUP BY salesCode HAVING sum(total) <> 0 \n ORDER BY salesCode";
$salesP = $dbc->prepare_statement($salesQ);
$salesR = $dbc->exec_statement($salesP, $args);
while ($w = $dbc->fetch_row($salesR)) {
$coding = isset($codes[$w['salesCode']]) ? $codes[$w['salesCode']] : $w['salesCode'];
$name = isset($names[$w['salesCode']]) ? $names[$w['salesCode']] : $w['name'];
$credit = $w['amount'] < 0 ? -1 * $w['amount'] : 0;
$debit = $w['amount'] > 0 ? $w['amount'] : 0;
$row = array($dateID, $dateStr, str_pad($coding, 9, '0', STR_PAD_RIGHT), $credit, $debit, $name);
$records[] = $row;
}
$explorersQ = '
SELECT SUM(quantity) AS qty
FROM ' . $dlog . ' AS d
WHERE tdate BETWEEN ? AND ?
AND upc = ?';
$explorersP = $dbc->prepare($explorersQ);
$explorersR = $dbc->execute($explorersP, array_merge($args, array('0000000004792')));
$expQty = 0.0;
if ($explorersR && $dbc->numRows($explorersR)) {
$w = $dbc->fetchRow($explorersR);
$expQty = $w['qty'];
}
$records[] = array($dateID, $dateStr, '000000000', '0.00', '0.00', 'CO-OP EXPLORERS (' . $expQty . ')');
$miscQ = "SELECT total as amount, description as name,\n trans_num, tdate FROM {$dlog} WHERE department=703\n AND trans_subtype <> 'IC'\n AND tdate BETWEEN ? AND ? ORDER BY tdate";
$miscP = $dbc->prepare_statement($miscQ);
$miscR = $dbc->exec_statement($miscP, $args);
$detailP = $dbc->prepare_statement("SELECT description \n FROM {$dtrans} WHERE trans_type='C'\n AND trans_subtype='CM' AND datetime BETWEEN ? AND ?\n AND emp_no=? and register_no=? and trans_no=? ORDER BY trans_id");
while ($w = $dbc->fetch_row($miscR)) {
$coding = 63350;
list($date, $time) = explode(' ', $w['tdate']);
list($e, $r, $t) = explode('-', $w['trans_num']);
// lookup comments on the transaction
$detailR = $dbc->exec_statement($detailP, array($date . ' 00:00:00', $date . ' 23:59:59', $e, $r, $t));
if ($dbc->num_rows($detailR) > 0) {
$w['name'] = '';
while ($detail = $dbc->fetch_row($detailR)) {
$w['name'] .= $detail['description'];
}
if (is_numeric($w['name'])) {
$coding = trim($w['name']);
}
}
$name = $w['name'] . ' (' . $date . ' ' . $w['trans_num'] . ')';
$credit = $w['amount'] < 0 ? -1 * $w['amount'] : 0;
$debit = $w['amount'] > 0 ? $w['amount'] : 0;
$row = array($dateID, $dateStr, str_pad($coding, 9, '0', STR_PAD_RIGHT), $credit, $debit, $name);
$records[] = $row;
}
$miscQ = "SELECT SUM(-total) as amount\n FROM {$dlog} WHERE department=703\n AND trans_subtype = 'IC'\n AND tdate BETWEEN ? AND ? ORDER BY tdate";
$miscP = $dbc->prepare_statement($miscQ);
$miscR = $dbc->exec_statement($miscP, $args);
while ($w = $dbc->fetch_row($miscR)) {
$record = array($dateID, $dateStr, str_pad(66600, 9, '0', STR_PAD_RIGHT), sprintf('%.2f', $w['amount']), 0.0, 'MISC RECEIPT INSTORE COUPON');
$records[] = $record;
}
return $records;
}
function post_date_trans_handler()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_PLUGIN_SETTINGS;
$dbc = FannieDB::get($FANNIE_OP_DB);
$dlog = DTransactionsModel::selectDtrans($this->date);
$emp_no = $FANNIE_PLUGIN_SETTINGS['ReversalEmployee'];
$register_no = $FANNIE_PLUGIN_SETTINGS['ReversalLane'];
$trans_no = 1;
$transP = $dbc->prepare_statement('SELECT MAX(trans_no) FROM
' . $FANNIE_TRANS_DB . $dbc->sep() . 'dlog WHERE
emp_no=? AND register_no=?');
$transR = $dbc->exec_statement($transP, array($emp_no, $register_no));
while ($transW = $dbc->fetch_row($transR)) {
$trans_no = $transW[0] + 1;
}
list($old_emp, $old_reg, $old_trans) = explode("-", $this->trans);
$query = "select upc, description, trans_type, trans_subtype,\n trans_status, department, quantity, Scale, unitPrice,\n total, regPrice, tax, foodstamp, discount, memDiscount,\n discountable, discounttype, voided, PercentDiscount,\n ItemQtty, volDiscType, volume, volSpecial, mixMatch,\n matched, memType, staff, card_no, numflag, charflag, \n trans_id \n from {$dlog} where register_no = ?\n and emp_no = ? and trans_no = ?\n and datetime BETWEEN ? AND ?\n and trans_status <> 'X'\n order by trans_id";
$args = array($old_reg, $old_emp, $old_trans, $this->date . ' 00:00:00', $this->date . ' 23:59:59');
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$trans_id = 1;
$record = DTrans::$DEFAULTS;
$record['emp_no'] = $emp_no;
$record['register_no'] = $register_no;
$record['trans_no'] = $trans_no;
$record['trans_id'] = $trans_id;
$comment = $record;
$comment['description'] = 'VOIDING TRANSACTION ' . $this->trans;
$comment['trans_type'] = 'C';
$comment['trans_subtype'] = 'CM';
$comment['trans_status'] = 'D';
$params = DTrans::parameterize($comment, 'datetime', $dbc->now());
$table = $FANNIE_TRANS_DB . $dbc->sep() . 'dtransactions';
$prep = $dbc->prepare_statement("INSERT INTO {$table} ({$params['columnString']})\n VALUES ({$params['valueString']})");
$dbc->exec_statement($prep, $params['arguments']);
$record['trans_id'] += 1;
while ($w = $dbc->fetch_row($result)) {
$next = $record;
// copy base record
$next['upc'] = $w['upc'];
$next['description'] = $w['description'];
$next['trans_type'] = $w['trans_type'];
$next['trans_subtype'] = $w['trans_subtype'];
$next['trans_status'] = $w['trans_status'];
$next['department'] = $w['department'];
$next['quantity'] = -1 * $w['quantity'];
$next['Scale'] = $w['Scale'];
$next['unitPrice'] = -1 * $w['unitPrice'];
$next['total'] = -1 * $w['total'];
$next['regPrice'] = -1 * $w['regPrice'];
$next['tax'] = $w['tax'];
$next['foodstamp'] = $w['foodstamp'];
$next['discount'] = -1 * $w['discount'];
$next['memDiscount'] = -1 * $w['memDiscount'];
$next['discountable'] = $w['discountable'];
$next['discounttype'] = $w['discounttype'];
$next['voided'] = $w['voided'];
$next['PercentDiscount'] = $w['PercentDiscount'];
$next['ItemQtty'] = -1 * $w['ItemQtty'];
$next['volDiscType'] = $w['volDiscType'];
$next['volume'] = -1 * $w['volume'];
$next['volSpecial'] = -1 * $w['volSpecial'];
$next['mixMatch'] = $w['mixMatch'];
$next['matched'] = $w['matched'];
$next['memType'] = $w['memType'];
$next['staff'] = $w['staff'];
$next['numflag'] = $w['numflag'];
$next['charflag'] = $w['charflag'];
$next['card_no'] = $w['card_no'];
$params = DTrans::parameterize($next, 'datetime', $dbc->now());
$prep = $dbc->prepare_statement("INSERT INTO {$table} ({$params['columnString']})\n VALUES ({$params['valueString']})");
$dbc->exec_statement($prep, $params['arguments']);
$record['trans_id'] += 1;
}
// return a listing of the new, reversal transaction
$this->trans = $emp_no . '-' . $register_no . '-' . $trans_no;
$this->date = date('Y-m-d');
return $this->get_date_trans_handler();
}
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
include dirname(__FILE__) . '/../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
$dbc = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$dbc->query("TRUNCATE TABLE YTD_Patronage_MiddleStep");
$ts = mktime(0, 0, 0, date("n"), 1, date("Y") - 1);
for ($i = 0; $i < 12; $i++) {
$start = date("Y-m-d", $ts);
$end = date("Y-m-t", $ts);
$dlog = DTransactionsModel::selectDtrans($start, $end);
$ts = mktime(0, 0, 0, date("n", $ts) + 1, 1, date("Y", $ts));
$query = "INSERT INTO YTD_Patronage_MiddleStep \n select d.card_no,MONTH(d.datetime) as month_no,\n total,\n YEAR(d.datetime) AS year_no,\n DAY(d.datetime) AS day_no,\n " . $dbc->concat($dbc->convert('emp_no', 'char'), "'-'", $dbc->convert('register_no', 'char'), "'-'", $dbc->convert('trans_no', 'char'), '') . " as trans_num\n from " . $dlog . " as d\n WHERE datetime BETWEEN '{$start} 00:00:00' AND '{$end} 23:59:59'\n AND d.trans_type = 'T' AND total <> 0\n AND emp_no <> 9999 and register_no <> 99 AND trans_status NOT IN ('Z','X')";
$dbc->query($query);
}
$dbc->query("TRUNCATE TABLE YTD_Patronage_Speedup");
$query = "INSERT INTO YTD_Patronage_Speedup\n SELECT card_no,month_no,SUM(total) as total,year_no\n FROM YTD_Patronage_MiddleStep AS d\n LEFT JOIN custdata as c on c.CardNo=d.card_no and c.personNum=1 \n LEFT JOIN suspensions as s on s.cardno = d.card_no \n WHERE c.memType=1 or s.memtype1=1 \n GROUP BY d.card_no,\n year_no, month_no, day_no, trans_num";
$dbc->query($query);
if (stristr($query, "update")) {
$errors .= "Illegal term <b>update</b><br />";
}
if (stristr($query, "alter")) {
$errors .= "Illegal term <b>alter</b><br />";
}
}
if ($errors == "" && $query != "") {
$dlog = "";
$dtrans = "";
if (!empty($_REQUEST['date1']) && !empty($_REQUEST['date2'])) {
$dlog = DTransactionsModel::selectDlog($_REQUEST['date1'], $_REQUEST['date2']);
$dtrans = DTransactionsModel::selectDtrans($_REQUEST['date1'], $_REQUEST['date2']);
} elseif (!empty($_REQUEST['date1'])) {
$dlog = DTransactionsModel::selectDlog($_REQUEST['date1']);
$dtrans = DTransactionsModel::selectDtrans($_REQUEST['date1']);
}
if (!empty($dlog)) {
$query = str_ireplace(" dlog ", " " . $dlog . " ", $query);
}
if (!empty($dtrans)) {
$query = str_ireplace(" dtransactions ", " " . $dtrans . " ", $query);
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query);
if (!$result) {
echo "<i>Error occured</i>: " . $dbc->error();
echo "<hr />";
echo "<i>Your query</i>: " . $query;
} else {
if ($dbc->num_rows($result) == 0) {
public function run()
{
global $FANNIE_TRANS_DB, $FANNIE_ARCHIVE_DB, $FANNIE_ARCHIVE_METHOD;
/**
Find current maximum assigned store_row_id
Depending what time this is run, that might be
found in today's transactions or yesterday's transactions
*/
$currentMax = 0;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$maxR = $dbc->query('SELECT MAX(store_row_id) FROM dtransactions');
if ($dbc->num_rows($maxR) > 0) {
$maxW = $dbc->fetch_row($maxR);
if (!empty($maxW[0])) {
$currentMax = $maxW[0];
}
}
$yesterday = date('Y-m-d', strtotime('yesterday'));
$dtrans = DTransactionsModel::selectDtrans($yesterday);
$maxR = $dbc->query("SELECT MAX(store_row_id) FROM {$dtrans} WHERE datetime >= '{$yesterday} 00:00:00'");
if ($dbc->num_rows($maxR) > 0) {
$maxW = $dbc->fetch_row($maxR);
if (!empty($maxW[0]) && $maxW[0] > $currentMax) {
$currentMax = $maxW[0];
}
}
/* If for some reason you are not running this in the normal way,
* where currentMax is in dtransactions or yesterday's trans_archive,
* assign currentMax here.
* It may be in the possibly empty dtransactions in
* PhpMyAdmin > Operations > Table Options
* where AUTO_INCREMENT is the next value to be assigned,
* i.e. $currentMax + 1
* or in the last table effectively updated.
$currentMax = 9999;
*/
echo $this->cronMsg("Current maximum is " . $currentMax);
/* oldest known transaction data
* Adjust $year and $month for your database.
*/
$year = 2004;
$month = 9;
$new_id = $currentMax + 1;
// work in one month chunks
while ($year <= date('Y')) {
if ($year == date('Y') && $month > date('n')) {
break;
}
echo $this->cronMsg('Processing: ' . $year . ' ' . $month);
$table = $FANNIE_ARCHIVE_DB . $dbc->sep();
if ($FANNIE_ARCHIVE_METHOD == 'partitions') {
$table .= 'bigArchive';
} else {
$table .= 'transArchive' . $year . str_pad($month, 2, '0', STR_PAD_LEFT);
}
if ($dbc->table_exists("{$table}")) {
$lowerBound = date('Y-m-01 00:00:00', mktime(0, 0, 0, $month, 1, $year));
$upperBound = date('Y-m-t 23:59:59', mktime(0, 0, 0, $month, 1, $year));
$prep = $dbc->prepare('UPDATE ' . $table . ' SET store_row_id = ?
WHERE datetime = ?
AND emp_no = ?
AND register_no = ?
AND trans_no = ?
AND trans_id = ?');
$lookupQ = "SELECT datetime, emp_no, register_no, trans_no, trans_id\n FROM {$table} \n WHERE datetime BETWEEN '{$lowerBound}' AND '{$upperBound}'\n AND store_row_id IS NULL\n ORDER BY datetime";
$lookupR = $dbc->query($lookupQ);
$num_records = $dbc->num_rows($lookupR);
$count = 1;
// update records one at a time with incrementing IDs
while ($row = $dbc->fetch_row($lookupR)) {
// Original monitor interval: 100
if ($count == 1 || $count % 1000 == 0) {
echo $this->cronMsg(date('F Y', mktime(0, 0, 0, $month, 1, $year)) . ' ' . $count . '/' . $num_records);
}
$args = array($new_id, $row['datetime'], $row['emp_no'], $row['register_no'], $row['trans_no'], $row['trans_id']);
$dbc->execute($prep, $args);
$count++;
$new_id++;
}
} else {
echo $this->cronMsg("{$table} doesn't exist.");
}
$month++;
if ($month > 12) {
$month = 1;
$year++;
}
}
// advance dtransaction's increment counter so it will resume
// beyond all the IDs that were just used on archives
$alterQ = 'ALTER TABLE dtransactions AUTO_INCREMENT = ' . $new_id;
$rslt = $dbc->query($alterQ);
if ($rslt === False) {
echo $this->cronMsg("***Error: Attempt to: {$alterQ} failed.");
} else {
echo $this->cronMsg("Next dtransactions.store_row_id will be {$new_id}");
}
echo $this->cronMsg("Done.");
}
function receiptHeader($date, $trans)
{
global $FANNIE_ARCHIVE_DB, $FANNIE_TRANS_DB, $FANNIE_SERVER_DBMS, $FANNIE_ARCHIVE_METHOD;
$dbconn = $FANNIE_SERVER_DBMS == 'MSSQL' ? '.dbo.' : '.';
$totime = strtotime($date);
$month = date('m', $totime);
$year = date('Y', $totime);
$day = date('j', $totime);
$transact = explode('-', $trans);
if (count($transact) != 3) {
return '';
}
$emp_no = $transact[0];
$trans_no = $transact[2];
$reg_no = $transact[1];
$table = DTransactionsModel::selectDtrans(date('Y-m-d', $totime));
$query1 = "SELECT \n description,\n case \n when voided = 5 \n then 'Discount'\n when trans_status = 'M'\n then 'Mbr special'\n when scale <> 0 and quantity <> 0 \n then concat(convert(quantity,char), ' @ ', convert(unitPrice,char))\n when abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity = 1\n then concat(convert(volume,char), ' /', convert(unitPrice,char))\n when abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity <> 1\n then concat(convert(Quantity,char), ' @ ', convert(Volume,char), ' /', convert(unitPrice,char))\n when abs(itemQtty) > 1 and discounttype = 3\n then concat(convert(ItemQtty,char), ' /', convert(UnitPrice,char))\n when abs(itemQtty) > 1\n then concat(convert(quantity,char), ' @ ', convert(unitPrice,char)) \n when matched > 0\n then '1 w/ vol adj'\n else ''\n \n end\n as comment,\n total,\n case \n when trans_status = 'V' \n then 'VD'\n when trans_status = 'R'\n then 'RF'\n when tax <> 0 and foodstamp <> 0\n then 'TF'\n when tax <> 0 and foodstamp = 0\n then 'T' \n when tax = 0 and foodstamp <> 0\n then 'F'\n when tax = 0 and foodstamp = 0\n then '' \n end\n as Status,\n datetime, register_no, emp_no, trans_no, card_no as memberID,\n upc\n FROM {$table} \n WHERE datetime BETWEEN ? AND ? \n AND register_no=? AND emp_no=? and trans_no=?\n AND voided <> 5 and UPC <> 'TAX' and UPC <> 'DISCOUNT'\n ORDER BY trans_id";
$args = array("{$year}-{$month}-{$day} 00:00:00", "{$year}-{$month}-{$day} 23:59:59", $reg_no, $emp_no, $trans_no);
return $this->receipt_to_table($query1, $args, 0, 'FFFFFF');
}
