function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $dlog = DTransactionsModel::selectDlog($date1, $date2); $date1 .= ' 00:00:00'; $date2 .= ' 23:59:59'; $sales = "SELECT year(tdate) as year, month(tdate) as month,\n day(tdate) as day,max(memType) as memType,trans_num\n FROM {$dlog} as t\n WHERE \n tdate BETWEEN ? AND ?\n and trans_type = 'T'\n AND upc <> 'RRR'\n group by year(tdate),month(tdate),day(tdate),trans_num\n order by year(tdate),month(tdate),day(tdate),max(memType)"; $salesP = $dbc->prepare_statement($sales); $result = $dbc->exec_statement($salesP, array($date1, $date2)); /** Create result records based on date and increment them when the same type is encountered again */ $ret = array(); while ($row = $dbc->fetch_array($result)) { $stamp = date("M j, Y", mktime(0, 0, 0, $row['month'], $row['day'], $row['year'])); if (!isset($ret[$stamp])) { $ret[$stamp] = array("date" => $stamp); foreach ($this->memtypes as $id => $desc) { $ret[$stamp][$id] = 0; } $ret[$stamp]['ttl'] = 0; } $ret[$stamp]["ttl"]++; if (!isset($ret[$stamp][$row['memType']])) { $ret[$stamp][$row['memType']] = 0; } $ret[$stamp][$row['memType']]++; } $ret = $this->dekey_array($ret); return $ret; }
public function fetch_report_data() { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $date1 = $this->form->date1; $date2 = $this->form->date2; $qty = FormLib::get('qty', 1); $create = $dbc->prepare_statement("CREATE TABLE groupingTempBS (upc VARCHAR(13), quantity double, total decimal(10,2), trans_num varchar(50))"); $dbc->exec_statement($create); $dlog = DTransactionsModel::selectDlog($date1, $date2); $setupQ = $dbc->prepare_statement("INSERT INTO groupingTempBS\n SELECT upc, quantity, total, trans_num\n FROM {$dlog} AS d WHERE tdate BETWEEN ? AND ?\n AND trans_type IN ('I','D')\n GROUP BY year(tdate),month(tdate),day(tdate),trans_num \n HAVING COUNT(*) <= ?"); $dbc->exec_statement($setupQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59', $qty)); $reportQ = $dbc->prepare_statement(' SELECT g.upc, p.description, SUM(g.quantity) AS qty, COUNT(DISTINCT trans_num) AS num, SUM(total) AS ttl FROM groupingTempBS as g ' . DTrans::joinProducts('g', 'p') . ' GROUP BY g.upc, p.description HAVING sum(total) <> 0 ORDER BY count(*) DESC '); $reportR = $dbc->exec_statement($reportQ); $data = array(); while ($w = $dbc->fetch_row($reportR)) { $record = array($w['upc'], empty($w['description']) ? 'n/a' : $w['description'], $w[3], sprintf('%.2f', $w[2]), sprintf('%.2f', $w[4])); $data[] = $record; } $drop = $dbc->prepare_statement("DROP TABLE groupingTempBS"); $dbc->exec_statement($drop); return $data; }
function fetch_report_data() { $dbc = $this->connection; $FANNIE_OP_DB = $this->config->get('OP_DB'); $dbc->selectDB($FANNIE_OP_DB); $date1 = $this->form->date1; $date2 = $this->form->date2; $card_no = FormLib::get_form_value('card_no', '0'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "select month(t.tdate),day(t.tdate),year(t.tdate),\n t.upc,p.description,\n t.department,d.dept_name,m.super_name,\n sum(t.quantity) as qty,\n sum(t.total) as ttl from\n {$dlog} as t left join {$FANNIE_OP_DB}.products as p on t.upc = p.upc \n left join {$FANNIE_OP_DB}.departments AS d ON t.department=d.dept_no\n left join {$FANNIE_OP_DB}.MasterSuperDepts AS m ON t.department=m.dept_ID\n where t.card_no = ? AND\n trans_type IN ('I','D') AND\n tdate BETWEEN ? AND ?\n group by year(t.tdate),month(t.tdate),day(t.tdate),\n t.upc,p.description\n order by year(t.tdate),month(t.tdate),day(t.tdate)"; $args = array($card_no, $date1 . ' 00:00:00', $date2 . ' 23:59:59'); $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); /** Simple report Issue a query, build array of results */ $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); $record[] = $row[0] . "/" . $row[1] . "/" . $row[2]; $record[] = $row['upc']; $record[] = $row['description']; $record[] = $row['department'] . ' ' . $row['dept_name']; $record[] = $row['super_name']; $record[] = $row['qty']; $record[] = $row['ttl']; $ret[] = $record; } return $ret; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $upc = BarcodeLib::padUPC(FormLib::get('upc')); $date1 = $this->form->date1; $date2 = $this->form->date2; $dlog = DTransactionsModel::selectDlog($date1, $date2); $q = $dbc->prepare("\n SELECT d.card_no,\n sum(quantity) as qty,\n sum(total) as amt\n FROM {$dlog} AS d \n WHERE d.upc=? AND \n tdate BETWEEN ? AND ?\n GROUP BY d.card_no\n ORDER BY d.card_no"); $r = $dbc->exec_statement($q, array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59')); $data = array(); while ($w = $dbc->fetch_row($r)) { $account = \COREPOS\Fannie\API\member\MemberREST::get($w['card_no']); if ($account == false) { continue; } $customer = array(); foreach ($account['customers'] as $c) { if ($c['accountHolder']) { $customer = $c; break; } } $record = array($w['card_no'], $customer['lastName'] . ', ' . $customer['firstName'], $account['addressFirstLine'] . ' ' . $account['addressSecondLine'], $account['city'], $account['state'], $account['zip'], $customer['phone'], $customer['altPhone'], $customer['email'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['amt'])); $data[] = $record; } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); $dlog = DTransactionsModel::selectDlog($date1, $date2); $queryAll = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d \n WHERE tdate BETWEEN ? AND ?\n GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)\n ORDER BY YEAR(tdate), MONTH(tdate), DAY(tdate)"; $argsAll = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $querySelected = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d "; if ($buyer !== '' && $buyer > -1) { $querySelected .= " LEFT JOIN superdepts AS s ON d.department=s.dept_ID "; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $querySelected .= " WHERE tdate BETWEEN ? AND ? "; $argsSel = $argsAll; if ($buyer !== '') { if ($buyer == -2) { $querySelected .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $querySelected .= ' AND s.superID=? '; $argsSel[] = $buyer; } } if ($buyer != -1) { list($conditional, $argsSel) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $argsSel); $querySelected .= $conditional; } $querySelected .= " GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)"; $dataset = array(); $prep = $dbc->prepare_statement($queryAll); $result = $dbc->exec_statement($prep, $argsAll); while ($row = $dbc->fetch_row($result)) { $datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']); $dataset[$datestr] = array('ttl' => $row['trans_count'], 'sub' => 0); } $prep = $dbc->prepare_statement($querySelected); $result = $dbc->exec_statement($prep, $argsSel); while ($row = $dbc->fetch_row($result)) { $datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']); if (isset($dataset[$datestr])) { $dataset[$datestr]['sub'] = $row['trans_count']; } } $data = array(); foreach ($dataset as $date => $count) { $record = array($date, $count['sub'], $count['ttl']); $record[] = sprintf('%.2f%%', $count['sub'] / $count['ttl'] * 100); $data[] = $record; } return $data; }
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() { $items = explode("\n", $this->form->items); $ts1 = strtotime('last tuesday'); // -13 => previous wednesday and then back another full week $ts2 = mktime(0, 0, 0, date('n', $ts1), date('j', $ts1) - 13, date('Y', $ts1)); $dlog = DTransactionsModel::selectDlog(date('Y-m-d', $ts2), date('Y-m-d', $ts1)); switch ($this->form->type) { case 'lc': list($query, $args) = $this->lcQuery($dlog, $items); break; case 'scale': list($query, $args) = $this->scaleQuery($dlog, $items); break; default: case 'upc': list($query, $args) = $this->upcQuery($dlog, $items); break; } $dbc = $this->connection; $prep = $dbc->prepare($query); // force output order to match input order $order = array(); for ($i = 0; $i < count($args); $i++) { $order[$args[$i]] = $i + 1; } $args[] = date('Y-m-d 00:00:00', $ts2); $args[] = date('Y-m-d 23:59:59', $ts1); $res = $dbc->execute($prep, $args); $data = array(); $cpt = array(); $cpi = array(); while ($row = $dbc->fetchRow($res)) { $data[] = array($order[$row['upc']], $row['upc'], $row['brand'], $row['description'], sprintf('%.2f', $row['qty'] / 2)); $cpt[$order[$row['upc']]] = $row['qty'] / 2; $cpi[$order[$row['upc']]] = array($row['brand'], $row['description'], $row['upc'], $row['size'], $row['cost'], $row['normal_price']); } $table = '<table class="table small table-bordered"> <tr><th>Copy/Paste Items</th>'; ksort($cpi); foreach ($cpi as $id => $row) { $table .= sprintf('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%.2f</td><td>%.2f</td></tr>', $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]); } $table .= '</table>'; $table .= '<table class="table small table-bordered"> <tr><th>Copy/Paste Movement</th>'; ksort($cpt); foreach ($cpt as $id => $qty) { $table .= sprintf('<tr><td>%.2f</td></tr>', $qty); } $table .= '</table>'; if ($this->report_format == 'html') { echo $table; } return $data; }
function fetch_report_data() { $dbc = $this->connection; $dbc->setDefaultDB($this->config->get('OP_DB')); $d1 = $this->form->date1; $d2 = $this->form->date2; $dates = array($d1 . ' 00:00:00', $d2 . ' 23:59:59'); $today = date('Ymd'); $data = array(); $dlog = DTransactionsModel::selectDlog($d1); $tenderQ = $dbc->prepare("\n SELECT TenderName,\n COUNT(d.total) AS num,\n SUM(d.total) as total,\n t.SalesCode\n FROM {$dlog} AS d\n LEFT JOIN tenders as t ON d.trans_subtype=t.TenderCode\n WHERE d.tdate BETWEEN ? AND ?\n AND d.trans_type = 'T'\n AND d.total <> 0\n GROUP BY t.TenderName,\n t.SalesCode \n ORDER BY TenderName"); $tenderR = $dbc->execute($tenderQ, $dates); while ($tenderW = $dbc->fetch_row($tenderR)) { $credit = $tenderW['total'] <= 0 ? abs($tenderW['total']) : 0.0; $debit = $tenderW['total'] > 0 ? $tenderW['total'] : 0.0; $data[] = array($today, $tenderW['SalesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $tenderW['TenderName']); } $salesQ = ' SELECT t.salesCode AS category, SUM(d.quantity) AS qty, SUM(d.total) AS total FROM ' . $dlog . ' AS d LEFT JOIN departments AS t ON d.department=t.dept_no WHERE d.department <> 0 AND d.trans_type <> \'T\' AND d.tdate BETWEEN ? AND ? GROUP BY t.salesCode ORDER BY t.salesCode'; $salesP = $dbc->prepare($salesQ); $salesR = $dbc->execute($salesP, $dates); $report = array(); while ($salesW = $dbc->fetch_row($salesR)) { $credit = $salesW['total'] < 0 ? abs($salesW['total']) : 0.0; $debit = $salesW['total'] >= 0 ? $salesW['total'] : 0.0; $data[] = array($today, $salesW['category'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), 'Sales ' . $salesW['category']); } $discQ = $dbc->prepare("\n SELECT m.memDesc, \n SUM(d.total) AS total,\n count(*) AS num,\n m.salesCode\n FROM {$dlog} d \n LEFT JOIN memtype m ON d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc = 'DISCOUNT'\n AND total <> 0\n GROUP BY m.memDesc,\n m.salesCode \n ORDER BY m.memDesc"); $discR = $dbc->execute($discQ, $dates); while ($discW = $dbc->fetch_row($discR)) { $credit = $discW['total'] <= 0 ? abs($discW['total']) : 0.0; $debit = $discW['total'] > 0 ? $discW['total'] : 0.0; $data[] = array($today, $discW['salesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $discW['memDesc'] . ' Discount'); } $report = array(); $trans = DTransactionsModel::selectDTrans($d1); $lineItemQ = $dbc->prepare("\n SELECT d.description,\n SUM(regPrice) AS ttl,\n t.salesCode\n FROM {$trans} AS d\n LEFT JOIN taxrates AS t ON d.numflag=t.id\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . DTrans::isNotTesting('d') . "\n GROUP BY d.description\n "); $lineItemR = $dbc->execute($lineItemQ, $dates); while ($lineItemW = $dbc->fetch_row($lineItemR)) { $credit = $lineItemW['ttl'] < 0 ? abs($lineItemW['ttl']) : 0.0; $debit = $lineItemW['ttl'] >= 0 ? $lineItemW['ttl'] : 0.0; $data[] = array($today, $lineItemW['salesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $lineItemW['description'] . ' tax'); } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "\n SELECT d.upc,\n p.brand,\n p.description," . DTrans::sumQuantity('d') . " AS qty,\n CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n d.department, \n t.dept_name, \n SUM(total) AS total\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't'); // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "\n WHERE tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY d.upc,p.description,price,d.department,t.dept_name\n ORDER BY d.upc"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); while ($row = $dbc->fetch_row($result)) { $record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total'])); $data[] = $record; } // bold items that sold at multiple prices for ($i = 0; $i < count($data); $i++) { if (!isset($data[$i + 1])) { continue; } if ($data[$i][0] == $data[$i + 1][0]) { $data[$i]['meta'] = FannieReportPage::META_BOLD; $data[$i + 1]['meta'] = FannieReportPage::META_BOLD; } } return $data; }
public function fetch_report_data() { global $FANNIE_OP_DB, $FANNIE_SERVER_DBMS; // creates a temporary table so requesting a writable connection // does make sense here $dbc = FannieDB::get($FANNIE_OP_DB); $depts = FormLib::get('depts', array()); $upc = FormLib::get('upc'); $date1 = $this->form->date1; $date2 = $this->form->date2; $filters = FormLib::get('filters', array()); list($dClause, $dArgs) = $dbc->safeInClause($depts); $where = "d.department IN ({$dClause})"; $inv = "d.department NOT IN ({$dClause})"; if ($upc != "") { $upc = BarcodeLib::padUPC($upc); $where = "d.upc = ?"; $inv = "d.upc <> ?"; $dArgs = array($upc); } $dlog = DTransactionsModel::selectDlog($date1, $date2); $filter = ""; $fArgs = array(); if (is_array($filters) && count($filters) > 0) { $fClause = ""; foreach ($filters as $f) { $fClause .= "?,"; $fArgs[] = $f; } $fClause = "(" . rtrim($fClause, ",") . ")"; $filter = "AND d.department IN {$fClause}"; } $query = $dbc->prepare_statement("CREATE TABLE groupingTemp (tdate varchar(11), emp_no int, register_no int, trans_no int)"); $dbc->exec_statement($query); $dateConvertStr = $FANNIE_SERVER_DBMS == 'MSSQL' ? 'convert(char(11),d.tdate,110)' : 'convert(date(d.tdate),char)'; $loadQ = $dbc->prepare_statement("INSERT INTO groupingTemp\n SELECT {$dateConvertStr} as tdate,\n emp_no,register_no,trans_no FROM {$dlog} AS d\n WHERE {$where} AND tdate BETWEEN ? AND ?\n GROUP BY {$dateConvertStr}, emp_no,register_no,trans_no"); $dArgs[] = $date1 . ' 00:00:00'; $dArgs[] = $date2 . ' 23:59:59'; $dbc->exec_statement($loadQ, $dArgs); $dataQ = $dbc->prepare_statement("\n SELECT d.upc,\n p.description,\n t.dept_no,\n t.dept_name,\n SUM(d.quantity) AS quantity\n FROM {$dlog} AS d \n INNER JOIN groupingTemp AS g ON \n {$dateConvertStr} = g.tdate\n AND g.emp_no = d.emp_no\n AND g.register_no = d.register_no\n AND g.trans_no = d.trans_no " . DTrans::joinProducts('d', 'p') . DTrans::joinDepartments('d', 't') . "\n WHERE {$inv} \n AND trans_type IN ('I','D')\n AND d.tdate BETWEEN ? AND ?\n AND d.trans_status=''\n {$filter}\n GROUP BY d.upc,\n p.description,\n t.dept_no,\n t.dept_name\n ORDER BY SUM(d.quantity) DESC"); foreach ($fArgs as $f) { $dArgs[] = $f; } $dataR = $dbc->exec_statement($dataQ, $dArgs); $data = array(); while ($dataW = $dbc->fetch_row($dataR)) { $record = array($dataW['upc'], $dataW['description'], $dataW['dept_no'] . ' ' . $dataW['dept_name'], sprintf('%.2f', $dataW['quantity'])); $data[] = $record; } $drop = $dbc->prepare_statement("DROP TABLE groupingTemp"); $dbc->exec_statement($drop); return $data; }
protected function get_date1_date2_mtype_stype_handler() { global $FANNIE_TRANS_DB, $FANNIE_ROOT, $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_TRANS_DB); include $FANNIE_ROOT . 'install/db.php'; $mtype = "("; $mArgs = array(); foreach ($this->mtype as $m) { $mtype .= '?,'; $mArgs[] = (int) $m; } $mtype = rtrim($mtype, ",") . ")"; $stype = '('; $sArgs = array(); foreach ($this->stype as $s) { $stype .= '?,'; $sArgs[] = (int) $s; } $stype = rtrim($stype, ',') . ')'; if (count($sArgs) == 0) { $stype = '(?)'; $sArgs = array('-9999'); } $dlog = DTransactionsModel::selectDlog($this->date1, $this->date2); if ($dbc->table_exists("dlog_patronage")) { $drop = $dbc->prepare_statement("DROP TABLE dlog_patronage"); $dbc->exec_statement($drop); } $create = $dbc->prepare_statement('CREATE TABLE dlog_patronage (card_no INT, trans_type VARCHAR(2), trans_subtype VARCHAR(2), total DECIMAL(10,2), min_year INT, max_year INT, primary key (card_no, trans_type, trans_subtype))'); $dbc->exec_statement($create); $insQ = sprintf("\n INSERT INTO dlog_patronage\n SELECT d.card_no,\n trans_type,\n trans_subtype,\n sum(total),\n YEAR(MIN(tdate)) AS firstDate, \n YEAR(MAX(tdate)) AS lastDate\n FROM %s AS d\n LEFT JOIN %s%scustdata AS c ON c.CardNo=d.card_no AND c.personNum=1 \n LEFT JOIN %s%ssuspensions AS s ON d.card_no=s.cardno\n LEFT JOIN %s%sMasterSuperDepts AS m ON d.department=m.dept_ID\n WHERE d.trans_type IN ('I','D','S','T')\n AND d.total <> 0 \n AND (s.memtype1 IN %s OR c.memType IN %s)\n AND (m.superID IS NULL OR m.superID NOT IN %s)\n AND d.tdate BETWEEN ? AND ?\n GROUP BY d.card_no, trans_type, trans_subtype", $dlog, $FANNIE_OP_DB, $dbc->sep(), $FANNIE_OP_DB, $dbc->sep(), $FANNIE_OP_DB, $dbc->sep(), $mtype, $mtype, $stype); $args = $mArgs; foreach ($mArgs as $m) { $args[] = $m; } // need them twice foreach ($sArgs as $s) { $args[] = $s; } $args[] = $this->date1 . ' 00:00:00'; $args[] = $this->date2 . ' 23:59:59'; $prep = $dbc->prepare_statement($insQ); $worked = $dbc->exec_statement($prep, $args); if ($worked) { $this->add_onload_command("showBootstrapAlert('#alert-area', 'success', 'Patronage Snapshot Created');\n"); } else { $this->add_onload_command("showBootstrapAlert('#alert-area', 'danger', 'Error creating snapshot');\n"); } return true; }
function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $month1 = $this->form->month1; $month2 = $this->form->month2; $year1 = FormLib::get_form_value('year1', date('Y')); $year2 = FormLib::get_form_value('year2', date('Y')); $date1 = date('Y-m-d', mktime(0, 0, 0, $month1, 1, $year1)); $date2 = date('Y-m-t', mktime(0, 0, 0, $month2, 1, $year2)); $dlog = DTransactionsModel::selectDlog($date1, $date2); $date1 .= ' 00:00:00'; $date2 .= ' 00:00:00'; $qArgs = array($date1, $date2); $query = ""; $type = FormLib::get_form_value('mtype', 'upc'); if ($type == 'upc') { $inClause = "("; $vals = preg_split("/\\D+/", FormLib::get_form_value('upcs', '')); foreach ($vals as $v) { $qArgs[] = BarcodeLib::padUPC($v); $inClause .= "?,"; } $inClause = rtrim($inClause, ",") . ")"; $query = "SELECT t.upc,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS sales, \n MONTH(tdate) AS month, \n YEAR(tdate) AS year\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . " \n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.upc IN {$inClause}\n GROUP BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description\n ORDER BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description"; } else { $dept1 = FormLib::get_form_value('dept1', 1); $dept2 = FormLib::get_form_value('dept2', 1); $qArgs[] = $dept1; $qArgs[] = $dept2; $query = "SELECT t.department,d.dept_name,SUM(t.quantity) as qty,\n SUM(total) as sales, MONTH(tdate) as month, YEAR(tdate) as year\n FROM {$dlog} AS t\n LEFT JOIN departments AS d ON t.department=d.dept_no\n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.department BETWEEN ? AND ?\n GROUP BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name\n ORDER BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name"; } $queryP = $dbc->prepare_statement($query); $result = $dbc->exec_statement($queryP, $qArgs); $ret = array(); while ($row = $dbc->fetch_array($result)) { if (!isset($ret[$row[0]])) { $ret[$row[0]] = array('num' => $row[0], 'desc' => $row[1]); foreach ($this->months as $mkey) { $ret[$row[0]][$mkey] = 0; } } if (FormLib::get_form_value('results', 'Sales') == 'Sales') { $ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['sales']; } else { $ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['qty']; } } return $this->dekey_array($ret); }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $manu = FormLib::get_form_value('manu', ''); $type = FormLib::get_form_value('type', ''); $groupby = FormLib::get_form_value('groupby', 'upc'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $type_condition = "p.brand LIKE ?"; $args = array('%' . $manu . '%'); if ($type == 'prefix') { $type_condition = 't.upc LIKE ?'; } $query = ""; $args[] = $date1 . ' 00:00:00'; $args[] = $date2 . ' 23:59:59'; switch ($groupby) { case 'upc': $query = "\n SELECT t.upc,\n p.brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n p.description,\n d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC"; break; case 'date': $query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . "\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)"; break; case 'dept': $query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC"; break; } $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); if ($groupby == "date") { $record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year']; $record[] = number_format($row['qty'], 2); $record[] = number_format($row['ttl'], 2); } else { for ($i = 0; $i < $dbc->num_fields($result); $i++) { if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') { $row[$i] = sprintf('%.2f', $row[$i]); } $record[] .= $row[$i]; } } $ret[] = $record; } return $ret; }
public function run() { $dbc = FannieDB::get($this->config->get('OP_DB')); $p_def = $dbc->tableDefinition('products'); if (!isset($p_def['last_sold'])) { $this->logger->warning('products table does not have a last_sold column'); return; } $update = $dbc->prepare(' UPDATE products SET last_sold=? WHERE upc=?'); // really old date to ensure we get the whole history $dlog = DTransactionsModel::selectDlog('1950-01-01', date('Y-m-d')); $missingR = $dbc->query(' SELECT upc FROM products WHERE last_sold IS NULL'); /** Lookup each transaction containing the UPC instead of just MAX(tdate) over the time period. This is to adjust for voids. We want the lastest transaction where the item had a non-zero total. */ $lastSoldP = $dbc->prepare(' SELECT upc, YEAR(tdate), MONTH(tdate), DAY(tdate), trans_num, MAX(tdate) AS last_sold FROM ' . $dlog . ' WHERE trans_type=\'I\' AND upc=? GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate), trans_num, upc HAVING SUM(total) <> 0 ORDER BY tdate '); while ($missingW = $dbc->fetchRow($missingR)) { echo "Scanning sales for {$missingW['upc']}\n"; $res = $dbc->execute($lastSoldP, $missingW['upc']); while ($w = $dbc->fetchRow($res)) { $dbc->execute($update, array($w['last_sold'], $w['upc'])); } } }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $d1 = $this->form->date1; $d2 = $this->form->date2; $dlog = DTransactionsModel::selectDlog($d1, $d2); $query = $dbc->prepare_statement("SELECT \n CASE WHEN upc='0' THEN 'NOT SCANNED' ELSE upc END as upc, \n sum(CASE WHEN upc='0' THEN 1 ELSE quantity END) as qty,\n sum(-total) as ttl FROM {$dlog}\n WHERE trans_subtype='CP'\n AND tdate BETWEEN ? AND ?\n GROUP BY upc\n ORDER BY upc"); $result = $dbc->exec_statement($query, array($d1 . ' 00:00:00', $d2 . ' 23:59:59')); $data = array(); while ($row = $dbc->fetch_row($result)) { $data[] = array($row['upc'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['ttl'])); } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $d1 = FormLib::get('date1', date('Y-m-d')); $d2 = FormLib::get('date2', date('Y-m-d')); $dlog = DTransactionsModel::selectDlog($d1, $d2); $query = $dbc->prepare_statement("\n SELECT m.memDesc,\n SUM(total) AS total \n FROM {$dlog} AS d\n LEFT JOIN memtype AS m ON d.memType=m.memtype\n WHERE d.upc='DISCOUNT'\n AND tdate BETWEEN ? AND ?\n GROUP BY m.memDesc\n ORDER BY m.memDesc"); $result = $dbc->exec_statement($query, array($d1 . ' 00:00:00', $d2 . ' 23:59:59')); $data = array(); while ($row = $dbc->fetch_row($result)) { $data[] = array($row['memDesc'], sprintf('%.2f', $row['total'])); } return $data; }
public function refresh_data($trans_db, $month, $year, $day = False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year)); $end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year)); $end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year)); if ($day !== False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year)); $end_id = $start_id; $end_date = $start_date; } $target_table = DTransactionsModel::selectDlog($start_date, $end_date); /* clear old entries */ $sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?'; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_id, $end_id)); /* reload table from transarction archives The process for this controller is iterative because of an old bug that assigns incorrect values to the transaction's memType column on records with trans_status 'M'. Using aggregates directly on the table over-counts memType zero so instead we count transactions one at a time. */ $sql = "SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n MAX(memType) as memType,\n CONVERT(SUM(total),DECIMAL(10,2)) as total,\n CONVERT(SUM(CASE WHEN trans_status='M' THEN itemQtty \n WHEN unitPrice=0.01 THEN 1 ELSE quantity END),DECIMAL(10,2)) as quantity\n FROM {$target_table} WHERE\n tdate BETWEEN ? AND ? AND\n trans_type IN ('I','D') AND upc <> 'RRR'\n AND card_no <> 0 AND memType IS NOT NULL\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), trans_num\n ORDER BY DATE_FORMAT(tdate,'%Y%m%d'), MAX(memType)"; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59')); $this->reset(); while ($row = $this->connection->fetch_row($result)) { if ($this->date_id() != $row['date_id'] || $this->memType() != $row['memType']) { if ($this->date_id() !== 0) { $this->save(); } $this->reset(); $this->date_id($row['date_id']); $this->memType($row['memType']); $this->total(0.0); $this->quantity(0.0); $this->transCount(0); } $this->total($this->total() + $row['total']); $this->quantity($this->quantity() + $row['quantity']); $this->transCount($this->transCount() + 1); } if ($this->date_id() !== '') { $this->save(); } }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $dlog = DTransactionsModel::selectDlog($date1, $date2); $q = $dbc->prepare_statement("select emp_no,sum(-total),count(DISTINCT trans_num),\n year(tdate),month(tdate),day(tdate)\n from {$dlog} as d where\n tdate BETWEEN ? AND ?\n AND trans_type='T'\n GROUP BY year(tdate),month(tdate),day(tdate),emp_no\n ORDER BY sum(-total) DESC"); $r = $dbc->exec_statement($q, array($date1 . ' 00:00:00', $date2 . ' 23:59:59')); $data = array(); while ($row = $dbc->fetch_row($r)) { $record = array($row['emp_no'], sprintf('%d/%d/%d', $row[4], $row[5], $row[3]), sprintf('%.2f', $row[1]), $row[2]); $data[] = $record; } return $data; }
protected function get_id_view() { $URL = $this->config->get('URL'); $table = DTransactionsModel::selectDlog($this->__models['start'], $this->__models['end']); $monthYear = date('Ym', strtotime($this->__models['start'])); $dbc = $this->connection; $dbc->selectDB($this->config->get('TRANS_DB')); $query = "SELECT month(tdate),day(tdate),year(tdate),trans_num,\n sum(case when trans_type='T' then -total else 0 end) as tenderTotal\n FROM {$table} as t\n WHERE card_no=?\n AND tdate BETWEEN ? AND ?\n GROUP BY year(tdate),month(tdate),day(tdate),trans_num\n ORDER BY year(tdate) DESC, month(tdate) DESC,\n day(tdate) DESC"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, array($this->id, $this->__models['start'] . ' 00:00:00', $this->__models['end'] . ' 23:59:59')); ob_start(); echo "<form action=\"MemPurchasesPage.php\" id=myform method=get>"; echo "<input type=hidden name=id value=\"" . $this->id . "\" />"; $tstamp = time(); echo '<div class="form-group">'; echo "<select class=\"form-control\" name=my onchange=\"\$('#myform').submit();\">"; $count = 0; while (true) { $val = date("Ym", $tstamp); printf("<option value=\"%d\" %s>%s %d</option>", $val, $val == $monthYear ? "selected" : "", date("F", $tstamp), date("Y", $tstamp)); $tstamp = mktime(0, 0, 0, date("n", $tstamp) - 1, 1, date("Y", $tstamp)); // cuts off at 5 years if ($count++ > 60) { break; } } echo "</select>"; echo '</div>'; $visits = 0; $spending = 0.0; echo "<table class=\"table table-bordered\">"; while ($row = $dbc->fetch_row($result)) { echo "<tr>"; printf("<td>%d/%d/%d</td>", $row[0], $row[1], $row[2]); printf("<td><a href=\"{$URL}admin/LookupReceipt/RenderReceiptPage.php?receipt=%s&month=%d&day=%d&year=%d\">%s</a></td>", $row[3], $row[0], $row[1], $row[2], $row[3]); printf("<td>\$%.2f</td>", $row[4]); echo "</tr>"; $spending += $row[4]; $visits += 1; } echo "</table>"; echo '<p>'; printf("<b>Visits</b>: %d<br /><b>Spending</b>: \$%.2f\n <br /><b>Avg</b>: \$%.2f", $visits, $spending, $visits > 0 ? $spending / $visits : 0); echo '</p>'; return ob_get_clean(); }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $code = FormLib::get('tendercode'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = $dbc->prepare_statement("select tdate,trans_num,-total as total,emp_no, register_no\n FROM {$dlog} as t \n where t.trans_subtype = ? AND\n trans_type='T' AND\n tdate BETWEEN ? AND ?\n AND total <> 0\n order by tdate"); $result = $dbc->exec_statement($query, array($code, $date1 . ' 00:00:00', $date2 . ' 23:59:59')); $data = array(); while ($row = $dbc->fetch_array($result)) { $record = array(date('Y-m-d', strtotime($row['tdate'])), $row['trans_num'], $row['emp_no'], $row['register_no'], $row['total']); $data[] = $record; } return $data; }
function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $upc = $this->form->upc; if (is_numeric($upc)) { $upc = BarcodeLib::padUPC($upc); } $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "SELECT \n MONTH(t.tdate),\n DAY(t.tdate),\n YEAR(t.tdate),\n t.upc,\n p.brand,\n p.description,\n " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS total\n FROM {$dlog} AS t \n " . DTrans::joinProducts('t', 'p', 'LEFT') . "\n WHERE t.upc = ? AND\n t.tdate BETWEEN ? AND ?\n GROUP BY \n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n t.upc,\n p.description\n ORDER BY year(t.tdate),month(t.tdate),day(t.tdate)"; $args = array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59'); if (strtolower($upc) == "rrr" || $upc == "0000000000052") { if ($dlog == "dlog_90_view" || $dlog == "dlog_15") { $dlog = "transarchive"; } else { $dlog = "trans_archive.bigArchive"; } $query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand,'RRR' AS description,\n sum(case when upc <> 'rrr' then quantity when volSpecial is null or volSpecial > 9999 then 0 else volSpecial end) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and trans_status <> 'X'\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)\n ORDER BY YEAR(datetime),MONTH(datetime),DAY(datetime)"; } else { if (!is_numeric($upc)) { $dlog = DTransactionsModel::selectDTrans($date1, $date2); $query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand, description,\n sum(CASE WHEN quantity=0 THEN 1 ELSE quantity END) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and (trans_status <> 'X' || trans_type='L')\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)"; } } $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); /** Simple report Issue a query, build array of results */ $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); $record[] = $row[0] . "/" . $row[1] . "/" . $row[2]; $record[] = $row['upc']; $record[] = $row['brand'] === null ? '' : $row['brand']; $record[] = $row['description'] === null ? '' : $row['description']; $record[] = sprintf('%.2f', $row['qty']); $record[] = sprintf('%.2f', $row['total']); $ret[] = $record; } return $ret; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer > -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } elseif ($buyer == -2) { $where .= ' AND s.superID <> 0 '; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $tempTables = array('connection' => $dbc, 'clauses' => array(array('sql' => 'trans_type IN (?, ?)', 'params' => array('I', 'D')))); $dlog = DTransactionsModel::selectDlog($date1, $date2, $tempTables); $query = "SELECT year(tdate),month(tdate),day(tdate),\n SUM(CASE WHEN trans_type='D' THEN total ELSE 0 END) as total,\n SUM(CASE WHEN trans_type='D' THEN abs(quantity) ELSE 0 END) as qty,\n SUM(CASE WHEN trans_type='D' THEN 1.0 ELSE 0.0 END) /\n SUM(CASE WHEN trans_type IN ('I','D') THEN 1.0 ELSE 0.0 END) as percentage\n FROM {$dlog} as d "; // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "WHERE trans_type IN ('I','D')\n AND tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY year(tdate),month(tdate),day(tdate)\n ORDER BY year(tdate),month(tdate),day(tdate)"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); while ($row = $dbc->fetch_row($result)) { $record = array(sprintf('%d/%d/%d', $row[1], $row[2], $row[0]), sprintf('%.2f', $row['total']), sprintf('%.2f', $row['qty']), sprintf('%.2f%%', $row['percentage'] * 100)); $data[] = $record; } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $vendor = FormLib::get_form_value('vendor', ''); $groupby = FormLib::get_form_value('groupby', 'upc'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = ""; switch ($groupby) { case 'upc': $query = "\n SELECT t.upc,\n COALESCE(p.brand, x.manufacturer) AS brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n COALESCE(p.brand, x.manufacturer),\n p.description,\n d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC"; break; case 'date': $query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)"; break; case 'dept': $query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC"; break; } $args = array('%' . $vendor . '%', '%' . $vendor . '%', $date1 . ' 00:00:00', $date2 . ' 23:59:59'); $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); $ret = array(); while ($row = $dbc->fetch_array($result)) { $record = array(); if ($groupby == "date") { $record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year']; $record[] = number_format($row['qty'], 2); $record[] = number_format($row['ttl'], 2); } else { for ($i = 0; $i < $dbc->num_fields($result); $i++) { if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') { $row[$i] = number_format($row[$i], 2); } $record[] .= $row[$i]; } } $ret[] = $record; } return $ret; }
public function refresh_data($trans_db, $month, $year, $day = False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year)); $end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year)); $end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year)); if ($day !== False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year)); $end_id = $start_id; $end_date = $start_date; } $target_table = DTransactionsModel::selectDlog($start_date, $end_date); /* clear old entries */ $sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?'; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_id, $end_id)); /* reload table from transarction archives */ $sql = "INSERT INTO " . $this->name . "\n SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n memType,\n CONVERT(SUM(total),DECIMAL(10,2)) as total,\n COUNT(DISTINCT trans_num) as transCount\n FROM {$target_table} WHERE\n tdate BETWEEN ? AND ? AND\n trans_type IN ('S') AND total <> 0\n AND upc='DISCOUNT' AND card_no <> 0\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), memType"; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59')); }
public function refresh_data($trans_db, $month, $year, $day = False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year)); $end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year)); $end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year)); if ($day !== False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year)); $end_id = $start_id; $end_date = $start_date; } $target_table = DTransactionsModel::selectDlog($start_date, $end_date); /* clear old entries */ $sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?'; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_id, $end_id)); /* reload table from transarction archives */ $sql = "INSERT INTO " . $this->name . "\n SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n upc,department,\n CONVERT(SUM(total),DECIMAL(10,2)) as total,\n CONVERT(SUM(CASE WHEN trans_status='M' THEN itemQtty \n WHEN unitPrice=0.01 THEN 1 ELSE quantity END),DECIMAL(10,2)) as quantity\n FROM {$target_table} WHERE\n tdate BETWEEN ? AND ? AND\n trans_type IN ('I','D') AND upc <> '0'\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), upc, department"; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59')); }
public function fetch_report_data() { $date1 = $this->form->date1; $date2 = $this->form->date2; $dlog = DTransactionsModel::selectDlog($date1, $date2); $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $m = new MasterSuperDeptsModel($dbc); $m->superID(0); $dept_list = '?,'; $args = array(0); foreach ($m->find() as $obj) { $dept_list .= '?,'; $args[] = $obj->dept_ID(); } $dept_list = substr($dept_list, 0, strlen($dept_list) - 1); $prep = $dbc->prepare(' SELECT SUM(d.total) AS ttl FROM ' . $dlog . ' AS d WHERE d.department NOT IN (' . $dept_list . ') AND d.trans_type IN (\'I\',\'D\') AND d.tdate BETWEEN ? AND ?'); $start = strtotime($date1); $end = strtotime($date2); $data = array(); $i = 0; while ($start <= $end) { $d1 = date('Y-m-d 00:00:00', $start); $d2 = date('Y-m-d 23:59:59', mktime(0, 0, 0, date('n', $start), date('j', $start) + 6, date('Y', $start))); $record = array(date('Y-m-d', strtotime($d1)) . ' to ' . date('Y-m-d', strtotime($d2))); $week_args = array_merge($args, array($d1, $d2)); $result = $dbc->execute($prep, $week_args); $row = $dbc->fetch_row($result); $record[] = sprintf('%.2f', $row['ttl']); $data[] = $record; $start = mktime(0, 0, 0, date('n', $start), date('j', $start) + 7, date('Y', $start)); } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date = $this->form->date; $dlog = DTransactionsModel::selectDlog($date); $hour = $dbc->hour('tdate'); $q = $dbc->prepare_statement("select {$hour} as hour,\n count(distinct trans_num)\n from {$dlog} where\n tdate BETWEEN ? AND ?\n group by {$hour}\n order by {$hour}"); $r = $dbc->exec_statement($q, array($date . ' 00:00:00', $date . ' 23:59:59')); $data = array(); while ($row = $dbc->fetch_array($r)) { $hour = $row[0]; if ($hour > 12) { $hour -= 12; } $record = array(); $record[] = $hour . ($row[0] < 12 ? ':00 am' : ':00 pm'); $record[] = $row[1]; $data[] = $record; } return $data; }
public function refresh_data($trans_db, $month, $year, $day = False) { global $FANNIE_OP_DB; $start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year)); $end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year)); $end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year)); if ($day !== False) { $start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year)); $start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year)); $end_id = $start_id; $end_date = $start_date; } $target_table = DTransactionsModel::selectDlog($start_date, $end_date); /* clear old entries */ $sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?'; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_id, $end_id)); // 5Jul2013 - percentDiscount not currently exposed via dlog $sql = "INSERT INTO " . $this->name . " \n SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n trans_num,\n register_no,\n emp_no,\n SUM(CASE WHEN trans_type='T' THEN total ELSE 0 END) as tenderTotal,\n SUM(CASE WHEN upc='TAX' THEN total ELSE 0 END) as taxTotal,\n SUM(CASE WHEN upc='DISCOUNT' THEN total ELSE 0 END) as discountTotal,\n 0 as percentDiscount,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID <> 0 THEN total else 0 END) as retailTotal,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID <> 0 AND trans_status='M' THEN itemQtty \n WHEN trans_type IN ('I','D') AND m.superID <> 0 AND unitPrice=0.01 THEN 1 \n WHEN trans_type IN ('I','D') AND m.superID <> 0 AND trans_status<>'M'\n AND unitPrice<>0.01 THEN quantity ELSE 0 END) as retailQty,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID = 0 THEN total else 0 END) as retailTotal,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID = 0 AND trans_status='M' THEN itemQtty \n WHEN trans_type IN ('I','D') AND m.superID = 0 AND unitPrice=0.01 THEN 1 \n WHEN trans_type IN ('I','D') AND m.superID = 0 AND trans_status<>'M'\n AND unitPrice<>0.01 THEN quantity ELSE 0 END) as retailQty,\n SUM(CASE WHEN trans_type in ('I','D') THEN 1 ELSE 0 END) as ringCount,\n MIN(tdate) as start_time,\n MAX(tdate) as end_time, " . $this->connection->seconddiff('MIN(tdate)', 'MAX(tdate)') . " as duration,\n MAX(card_no) as card_no,\n MAX(memType) as memType\n FROM {$target_table} as t LEFT JOIN " . $FANNIE_OP_DB . $this->connection->sep() . "MasterSuperDepts as m\n ON t.department=m.dept_ID\n WHERE tdate BETWEEN ? AND ? AND upc <> 'RRR'\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), trans_num"; $prep = $this->connection->prepare_statement($sql); $result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59')); }
function mainDisplay($date, $checkForSave = True) { global $sql; $ret = ""; $FROM_SAVED = False; if ($checkForSave) { $checkQ = $sql->prepare("select * from dailyJournal where datediff(dd,tdate,?) = 0"); $checkR = $sql->execute($checkQ, array($date)); if ($sql->num_rows($checkR) > 0) { $FROM_SAVED = True; } } $ret .= "<a href=\"\" onclick=\"save(); return false;\">Save</a> | "; $ret .= "<a href=\"\" onclick=\"reInit(); return false;\">Reload from POS</a> | "; $ret .= "<a href=\"\" onclick=\"csv(); return false;\">Export to CSV</a><br />"; $ret .= "<table id=thetable cellpadding=3 cellspacing=0 border=1>"; $ret .= "<tr><td> </td><th>Input</th><th colspan=3>Journal Entries</th></tr>"; $ret .= "<tr><td> </td><td> </td>"; $ret .= "<td style=\"width:7em; text-align: center;\"><i>Debit</i></td>"; $ret .= "<td style=\"width:7em; text-align: center;\"><i>Credit</i></td>"; $ret .= "<td style=\"width:7em; text-align: center;\"><i>Account</i></td></tr>"; $dlog = DTransactionsModel::selectDlog($date); $tenderQ = "select t.tenderName,-sum(d.total) as total \n from {$dlog} as d, Tenders as t\n where datediff(dd,?,d.tDate) = 0\n and d.trans_status <> 'X'\n and d.trans_subtype = t.tenderCode\n group by t.tenderName"; if ($FROM_SAVED) { $tenderQ = "select sub_type,value from dailyJournal\n where datediff(dd,tdate,?) = 0 and\n type = 'T'"; } $tenderP = $sql->prepare($tenderQ); $tenderR = $sql->execute($tenderP, array($date)); $cash = 0; $check = 0; $MAD = 0; $RRR = 0; $coupons = 0; $GC = 0; $TC = 0; $storecharge = 0; $EBT = 0; $MC = 0; $Visa = 0; $Disc1 = 0; $Disc2 = 0; $instoreCoup = 0; while ($w = $sql->fetch_row($tenderR)) { if ($w[0] == "Cash") { $cash = $w[1]; } elseif ($w[0] == "Check") { $check = $w[1]; } elseif ($w[0] == "MAD Coupon") { $MAD = $w[1]; } elseif ($w[0] == "RRR Coupon") { $RRR = $w[1]; } elseif ($w[0] == "Coupons") { $coupons = $w[1]; } elseif ($w[0] == "Gift Card") { $GC = $w[1]; } elseif ($w[0] == "GIFT CERT") { $TC = $w[1]; } elseif ($w[0] == "InStore Charges") { $storecharge = $w[1]; } elseif ($w[0] == "EBT") { $EBT = $w[1]; } elseif ($w[0] == "MC") { $MC = $w[1]; } elseif ($w[0] == "Visa") { $Visa = $w[1]; } elseif ($w[0] == "Discover1") { $Disc1 = $w[1]; } elseif ($w[0] == "Discover2") { $Disc2 = $w[1]; } elseif ($w[0] == "InStoreCoupon") { $instoreCoup = $w[1]; } } $ret .= "<tr><td>Deposit</td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Cash</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCash type=text value=\"{$cash}\" size=8 /></td>"; $ret .= "<td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Check</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCheck type=text value=\"{$check}\" size=8 /></td>"; $ret .= "<td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>TOTAL Deposit</td><td id=depositTotal> </td>"; $ret .= "<td name=jDebit id=jDepositTotal align=right> </td><td> </td><td align=right>10120</td></tr>"; $ret .= "<tr><td>Credit Cards/EBT</td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>EBT/Debit Approved</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputEBT type=text size=8 value=\"{$EBT}\" /></td>"; $ret .= "<td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>MasterCard</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCMC type=text size=8 value=\"{$MC}\" /></td>"; $ret .= "<td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>VISA</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCVisa type=text size=8 value=\"{$Visa}\" /></td>"; $ret .= "<td name=jDebit id=jCCMain align=right> </td><td> </td><td align=right> </td></tr>"; $style = ""; if ($Disc1 == 0) { $style = "display:none;"; } $ret .= "<tr style=\"{$style}\"><td>Discover Discount</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCDisc1 type=text size=8 value=\"{$Disc1}\" /></td>"; $ret .= "<td name=jDebit id=jCCDisc1 align=right> </td><td> </td><td align=right> </td></tr>"; $ret .= "<tr><td>Discover Outlet Total</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCDisc2 type=text size=8 value=\"{$Disc2}\" /></td>"; $ret .= "<td name=jDebit id=jCCDisc2 align=right> </td><td> </td><td align=right> </td></tr>"; $ret .= "<tr><td>Total FAPS</td><td id=totalFAPs> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Total All Credit Cards/EBT</td><td id=totalCCEBT> </td><td name=jDebit id=jTotalCCEBT align=right> </td>"; $ret .= "<td> </td><td align=right>10120</td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>RRR Coupon</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputRRR type=text value=\"{$RRR}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jRRR align=right> </td><td> </td><td align=right>63380</td></tr>"; $ret .= "<tr><td>Coupons</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCoupons type=text value=\"{$coupons}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jCoupons align=right> </td><td> </td><td align=right>10740</td></tr>"; $ret .= "<tr><td>Gift Card as Tender</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputGC type=text value=\"{$GC}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jGC align=right> </td><td> </td><td align=right>21205</td></tr>"; $ret .= "<tr><td>Gift Cert as Tender</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputTC type=text value=\"{$TC}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jTC align=right> </td><td> </td><td align=right>21200</td></tr>"; $ret .= "<tr><td>InStore Charges</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputStoreCharge type=text value=\"{$storecharge}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jStoreCharge align=right> </td><td> </td><td align=right>10710</td></tr>"; $ret .= "<tr><td>InStore Coupons</td><td>"; $ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputInStoreCoup type=text value=\"{$instoreCoup}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jInStoreCoup align=right> </td><td> </td><td align=right>67710</td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Tenders Total</td><td id=tenderTotal> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>SALES</td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>pCode</td><td> </td><td> </td><td> </td><td> </td></tr>"; $salesQ = "select d.pCode,sum(l.total) as total from\n {$dlog} as l join departments as d on l.department = d.dept_no\n where datediff(dd,?,tDate) = 0\n and l.department < 600 and l.department <> 0\n and l.trans_type <> 'T'\n group by d.pCode\n order by d.pCode"; if ($FROM_SAVED) { $salesQ = "select sub_type,value from dailyJournal where\n datediff(dd,tdate,?) = 0 and type='P' order by sub_type"; } $pCodes = array(41201, 41205, 41300, 41305, 41310, 41315, 41400, 41405, 41407, 41410, 41415, 41420, 41425, 41430, 41435, 41440, 41500, 41505, 41510, 41515, 41520, 41525, 41530, 41600, 41605, 41610, 41640, 41645, 41700, 41705); $i = 0; $slaesP = $sql->prepare($salesQ); $salesR = $sql->execute($salesP, array($date)); while ($w = $sql->fetch_row($salesR)) { if ($i >= count($pCodes)) { break; } while ($w[0] > $pCodes[$i]) { $ret .= "<tr><td>{$pCodes[$i]}</td>"; $ret .= "<td><input onchange=\"resumSales();resumTotals();\" name=input id=inputPcode{$pCodes[$i]} type=text size=8 value=0 /></td>"; $ret .= "<td name=jDebit id=jDebit{$pCodes[$i]} align=right> </td>"; $ret .= "<td name=jCredit id=jCredit{$pCodes[$i]} align=right> </td>"; $ret .= "<td align=right>{$pCodes[$i]}</td></tr>"; $i++; } if ($w[0] == $pCodes[$i]) { $ret .= "<tr><td>{$pCodes[$i]}</td>"; $ret .= "<td><input onchange=\"resumSales();resumTotals();\" name=input id=inputPcode{$pCodes[$i]} type=text size=8 value=\"{$w['1']}\" /></td>"; $ret .= "<td name=jDebit id=jDebit{$pCodes[$i]} align=right> </td>"; $ret .= "<td name=jCredit id=jCredit{$pCodes[$i]} align=right> </td>"; $ret .= "<td align=right>{$pCodes[$i]}</td></tr>"; $i++; } } while ($i < count($pCodes)) { $ret .= "<tr><td>{$pCodes[$i]}</td>"; $ret .= "<td><input onchange=\"resumSales();resumTotals();\" name=input id=inputPcode{$pCodes[$i]} type=text size=8 value=0 /></td>"; $ret .= "<td name=jDebit id=jDebit{$pCodes[$i]} align=right> </td>"; $ret .= "<td name=jCredit id=jCredit{$pCodes[$i]} align=right> </td>"; $ret .= "<td align=right>{$pCodes[$i]}</td></tr>"; $i++; } $ret .= "<tr><td>column TOTAL</td><td id=totalPcode> </td><td> </td><td> </td><td> </td></tr>"; $totalQ = $sql->prepare("select sum(l.total) as totalSales from {$dlog} as l\n where datediff(dd,?,tDate) = 0\n and l.department < 600 and l.department <> 0\n and l.trans_type <> 'T'"); $totalR = $sql->execute($totalQ, array($date)); $totalW = $sql->fetch_row($totalR); $ret .= "<tr><td>Total Sales POS report</td><td id=totalPOS>{$totalW['0']}</td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Difference</td><td id=salesDiff> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $otherQ = "select d.department,sum(total) as total from {$dlog} as d\n where datediff(dd,?,tDate) = 0\n and d.department > 300 and d.department <> 0 and d.register_no <> 20\n group by d.department order by d.department"; if ($FROM_SAVED) { $otherQ = "select sub_type,value from dailyJournal where\n datediff(dd,tdate,?) = 0 and type = 'O'"; } $otherP = $sql->prepare($otherQ); $otherR = $sql->execute($otherP, array($date)); $gcSales = 0; $tcSales = 0; $miscPO = 0; $classA = 0; $classB = 0; $ar = 0; $ITCorrections = 0; $misc1 = 0; $misc2 = 0; $supplies = 0; $class = 0; $foundMoney = 0; $totes = 0; while ($w = $sql->fetch_row($otherR)) { if ($w[0] == 902) { $gcSales = $w[1]; } elseif ($w[0] == 900) { $tcSales = $w[1]; } elseif ($w[0] == 604) { $miscPO = $w[1]; } elseif ($w[0] == 992) { $classA = $w[1]; } elseif ($w[0] == 991) { $classB = $w[1]; } elseif ($w[0] == 990) { $ar = $w[1]; } elseif ($w[0] == 800) { $ITCorrections = $w[1]; } elseif ($w[0] == 801) { $misc1 = $w[1]; } elseif ($w[0] == 802) { $misc2 = $w[1]; } elseif ($w[0] == 600) { $supplies = $w[1]; } elseif ($w[0] == 708) { $class = $w[1]; } elseif ($w[0] == 700) { $totes = $w[1]; } elseif ($w[0] == "FOUND") { $foundMoney = $w[1]; } } $ret .= "<tr><td>Gift Card Sales</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputGCSales type=text size=8 value=\"{$gcSales}\" /></td>"; $ret .= "<td name=jDebit id=jDebitGCSales align=right> </td>"; $ret .= "<td name=jCredit id=jCreditGCSales align=right> </td><td align=right>21205</td></tr>"; $ret .= "<tr><td>Gift Certificate Sales</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputTCSales type=text size=8 value=\"{$tcSales}\" /></td>"; $ret .= "<td name=jDebit id=jDebitTCSales align=right> </td>"; $ret .= "<td name=jCredit id=jCreditTCSales align=right> </td><td align=right>21200</td></tr>"; $ret .= "<tr><td>Misc PO</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMiscPO type=text size=8 value=\"{$miscPO}\" /></td>"; $ret .= "<td name=jDebit id=jDebitMiscPO align=right> </td>"; $ret .= "<td name=jCredit id=jCreditMiscPO align=right> </td><td> </td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>EQUITY</td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Class A Equity</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputClassA type=text size=8 value=\"{$classA}\" /></td>"; $ret .= "<td name=jDebit id=jDebitClassA align=right> </td>"; $ret .= "<td name=jCredit id=jCreditClassA align=right> </td><td align=right>31100</td></tr>"; $ret .= "<tr><td>Class B Equity</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputClassB type=text size=8 value=\"{$classB}\" /></td>"; $ret .= "<td name=jDebit id=jDebitClassB align=right> </td>"; $ret .= "<td name=jCredit id=jCreditClassB align=right> </td><td align=right>31110</td></tr>"; $ret .= "<tr><td>TOTAL Equity</td><td id=totalEquity> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>AR Payments</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputAR type=text size=8 value=\"{$ar}\" /></td>"; $ret .= "<td name=jDebit id=jDebitAR align=right> </td>"; $ret .= "<td name=jCredit id=jCreditAR align=right> </td><td align=right>10710</td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Discounts</td><td> </td><td> </td><td> </td><td> </td></tr>"; $discountQ = "select m.memDesc, sum(d.total)*-1 as discount from\n {$dlog} as d inner join custdata as c on c.cardno = d.card_no\n inner join memtypeID as m on c.memType = m.memTypeID\n where datediff(dd,?,d.tdate) = 0\n and d.upc = 'DISCOUNT' and c.personnum = 1\n group by m.memDesc,d.upc"; if ($FROM_SAVED) { $discountQ = "select sub_type,value from dailyJournal where\n datediff(dd,tdate,?) = 0 and type = 'D'"; } $discountP = $sql->prepare($discountQ); $discountR = $sql->execute($discountP, array($date)); $discMem = 0; $discStaffMem = 0; $discStaffNonMem = 0; while ($w = $sql->fetch_row($discountR)) { if ($w[0] == "Member") { $discMem = $w[1]; } elseif ($w[0] == "Staff Member") { $discStaffMem = $w[1]; } elseif ($w[0] == "Staff NonMem") { $discStaffNonMem = $w[1]; } } $ret .= "<tr><td>Member</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMemDisc type=text size=8 value=\"{$discMem}\" /></td>"; $ret .= "<td name=jDebit id=jDebitDiscMem align=right> </td>"; $ret .= "<td name=jCredit id=jCreditDiscMem align=right> </td><td align=right>66600</td></tr>"; $ret .= "<tr><td>Staff Member</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputStaffMemDisc type=text size=8 value=\"{$discStaffMem}\" /></td>"; $ret .= "<td name=jDebit id=jDebitDiscStaffMem align=right> </td>"; $ret .= "<td name=jCredit id=jCreditDiscStaffMem align=right> </td><td align=right>61170</td></tr>"; $ret .= "<tr><td>Staff NonMem</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputStaffNonMemDisc type=text size=8 value=\"{$discStaffNonMem}\" /></td>"; $ret .= "<td name=jDebit id=jDebitDiscStaffNonMem align=right> </td>"; $ret .= "<td name=jCredit id=jCreditDiscStaffNonMem align=right> </td><td align=right>61170</td></tr>"; $ret .= "<tr><td>MAD Coupon</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMAD type=text value=\"{$MAD}\" size=8 /></td>"; $ret .= "<td name=jDebit id=jDebitMAD align=right> </td>"; $ret .= "<td name=jCredit id=jCreditMAD align=right> </td><td align=right>66600</td></tr>"; $ret .= "<tr><td>TOTAL Discounts</td><td id=totalDisc> </td><td> </td><td> </td><td> </td></tr>"; $taxQ = "select sum(total) as tax_collected from {$dlog} as d\n where datediff(dd,?,tdate) = 0\n and d.upc = 'TAX' group by d.upc"; if ($FROM_SAVED) { $taxQ = "select value from dailyJournal where type = 'X'\n and datediff(dd,tdate,?) = 0"; } $taxP = $sql->prepare($taxQ); $taxR = $sql->execute($taxP, array($date)); $taxW = $sql->fetch_row($taxR); $ret .= "<tr><td>Sales Tax Collected</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputTax type=text size=8 value=\"{$taxW['0']}\" /></td>"; $ret .= "<td name=jDebit id=jDebitTax align=right> </td>"; $ret .= "<td name=jCredit id=jCreditTax align=right> </td><td align=right>21180</td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>Other Income</td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td>IT Corrections</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputITCorrections type=text size=8 value=\"{$ITCorrections}\" /></td>"; $ret .= "<td name=jDebit id=jDebitITCorrections align=right> </td>"; $ret .= "<td name=jCredit id=jCreditITCorrections align=right> </td><td> </td></tr>"; $ret .= "<tr><td>Misc. #1</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMisc1 type=text size=8 value=\"{$misc1}\" /></td>"; $ret .= "<td name=jDebit id=jDebitMisc1 align=right> </td>"; $ret .= "<td name=jCredit id=jCreditMisc1 align=right> </td><td align=right>42231</td></tr>"; $ret .= "<tr><td>Misc. #2</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMisc2 type=text size=8 value=\"{$misc2}\" /></td>"; $ret .= "<td name=jDebit id=jDebitMisc2 align=right> </td>"; $ret .= "<td name=jCredit id=jCreditMisc2 align=right> </td><td align=right>42232</td></tr>"; $ret .= "<tr><td>Supplies (Stamps sold)</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputSupplies type=text size=8 value=\"{$supplies}\" /></td>"; $ret .= "<td name=jDebit id=jDebitSupplies align=right> </td>"; $ret .= "<td name=jCredit id=jCreditSupplies align=right> </td><td align=right>64410</td></tr>"; $ret .= "<tr><td>Class (public not staff)</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputClass type=text size=8 value=\"{$class}\" /></td>"; $ret .= "<td name=jDebit id=jDebitClass align=right> </td>"; $ret .= "<td name=jCredit id=jCreditClass align=right> </td><td align=right>42225</td></tr>"; $ret .= "<tr><td>Found Money</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputFound type=text size=8 value=\"{$foundMoney}\" /></td>"; $ret .= "<td name=jDebit id=jDebitFound align=right> </td>"; $ret .= "<td name=jCredit id=jCreditFound align=right> </td><td align=right>63350</td></tr>"; $ret .= "<tr><td>Totes</td><td>"; $ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputTotes type=text size=8 value=\"{$totes}\" /></td>"; $ret .= "<td name=jDebit id=jDebitTotes align=right> </td>"; $ret .= "<td name=jCredit id=jCreditTotes align=right> </td><td align=right>63320</td></tr>"; $miscCount = 0; if ($FROM_SAVED) { $miscQ = $sql->prepare("select sub_type,value from dailyJournal where\n datediff(dd,?,tdate) = 0 and type='M'"); $miscR = $sql->execute($miscQ, array($date)); while ($row = $sql->fetch_row($miscR)) { $ret .= "<tr><td>MiscReceipt</td>"; $ret .= "<td><input onchange=\"resumMisc();resumTotals();\" type=text size=8 value={$row['1']} id=inputMisc{$miscCount} /></td>"; $ret .= "<td name=jDebit id=jDebitMisc{$miscCount} align=right> </td>"; $ret .= "<td name=jCredit id=jCreditMisc{$miscCount} align=right> </td>"; $ret .= "<td align=right><input type=text size=8 value=\"{$row['0']}\" id=accountMisc{$miscCount} /></td>"; $ret .= "</tr>"; $miscCount++; } } $ret .= "<tr><td><a href=\"\" onclick=\"addMisc(); return false;\">Add MiscReceipt</a></td>"; $ret .= "<td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<input type=hidden id=miscCount value={$miscCount} />"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><td><i>SHEET SUBTOTAL</i></td><td> </td>"; $ret .= "<td id=sheetSubDebit align=right> </td><td id=sheetSubCredit align=right> </td><td> </td></tr>"; $ret .= "<tr><td>Over/Short</td><td id=overshort> </td>"; $ret .= "<td id=debitOvershort align=right> </td><td id=creditOvershort align=right> </td><td align=right>63350</td></tr>"; $ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>"; $ret .= "<tr><th>SHEET TOTAL</th><td> </td>"; $ret .= "<td id=sheetDebit align=right> </td><td id=sheetCredit align=right> </td><td> </td></tr>"; $ret .= "<tr><td><i>SHEET IMBALANCE</i></td><td> </td><td> </td><td id=sheetDiff align=right> </td><td> </td></tr>"; $ret .= "</table>"; return $ret; }
protected function updateSalesCache($week, $num_cached, $dateInfo) { $class_lib = $this->class_lib; $dbc = $class_lib::getDB(); $sales = $class_lib::getCache($dbc); $sales->obfWeekID($week->obfWeekID()); /** Lookup total sales for each category in a given date range */ $salesQ = 'SELECT m.obfCategoryID as id, m.superID, SUM(t.total) AS sales FROM __table__ AS t INNER JOIN ' . $this->config->get('OP_DB') . $dbc->sep() . 'superdepts AS s ON t.department=s.dept_ID INNER JOIN ObfCategorySuperDeptMap AS m ON s.superID=m.superID LEFT JOIN ObfCategories AS c ON m.obfCategoryID=c.obfCategoryID WHERE c.hasSales=1 AND t.tdate BETWEEN ? AND ? AND t.trans_type IN (\'I\', \'D\') GROUP BY m.obfCategoryID, m.superID'; /** Lookup number of transactions in a given date range */ $transQ = 'SELECT YEAR(t.tdate) AS year, MONTH(t.tdate) AS month, DAY(t.tdate) AS day, t.trans_num FROM __table__ AS t INNER JOIN ' . $this->config->get('OP_DB') . $dbc->sep() . 'superdepts AS s ON t.department=s.dept_ID INNER JOIN ObfCategorySuperDeptMap AS m ON s.superID=m.superID WHERE t.tdate BETWEEN ? AND ? AND t.trans_type IN (\'I\', \'D\') AND t.upc <> \'RRR\' GROUP BY YEAR(t.tdate), MONTH(t.tdate), DAY(t.tdate), t.trans_num'; /** Lookup tables for current week and year-over-year comparison */ $dlog1 = DTransactionsModel::selectDlog(date('Y-m-d', $dateInfo['start_ts']), date('Y-m-d', $dateInfo['end_ts'])); $dlog2 = DTransactionsModel::selectDlog(date('Y-m-d', $dateInfo['start_ly']), date('Y-m-d', $dateInfo['end_ly'])); $args = array(date('Y-m-d 00:00:00', $dateInfo['start_ts']), date('Y-m-d 23:59:59', $dateInfo['end_ts'])); $future = $dateInfo['end_ts'] >= strtotime(date('Y-m-d')) ? true : false; /** Lookup number of transactions for the current week and save that information if the week is complete */ $trans1Q = str_replace('__table__', $dlog1, $transQ); $transP = $dbc->prepare($trans1Q); $transR = $dbc->execute($transP, $args); if (!$future && $transR) { $sales->transactions($dbc->num_rows($transR)); } else { $sales->transactions(0); } /** Lookup sales for the current week. Actual sales is zeroed out until the week is complete, but the records are saved as placeholders for later */ $oneQ = str_replace('__table__', $dlog1, $salesQ); $oneP = $dbc->prepare($oneQ); $oneR = $dbc->execute($oneP, $args); while ($row = $dbc->fetch_row($oneR)) { $sales->obfCategoryID($row['id']); $sales->superID($row['superID']); $sales->actualSales($row['sales']); if ($future) { $sales->actualSales(0); } $labor = $class_lib::getLabor($dbc); $labor->obfWeekID($week->obfWeekID()); $labor->obfCategoryID($row['id']); foreach ($labor->find() as $l) { $sales->growthTarget($l->growthTarget()); } $sales->save(); } if (count($num_cached) == 0) { /** Now lookup year-over-year info Since it examines a whole month rather than a single week, we'll take the average and then extend that out to seven days */ $sales->reset(); $sales->obfWeekID($week->obfWeekID()); $args = array(date('Y-m-d 00:00:00', $dateInfo['start_ly']), date('Y-m-d 23:59:59', $dateInfo['end_ly'])); $num_days = (double) date('t', $dateInfo['start_ly']); /** Transactions last year, pro-rated */ $trans2Q = str_replace('__table__', $dlog2, $transQ); $transP = $dbc->prepare($trans2Q); $transR = $dbc->execute($transP, $args); if ($transR) { $month_trans = $dbc->num_rows($transR); $avg_trans = $month_trans / $num_days * 7; $sales->lastYearTransactions($avg_trans); } else { $sales->lastYearTransactions(0); } /** Sales last year, pro-rated */ $twoQ = str_replace('__table__', $dlog2, $salesQ); $twoP = $dbc->prepare($twoQ); $twoR = $dbc->execute($twoP, $args); while ($row = $dbc->fetch_row($twoR)) { $sales->obfCategoryID($row['id']); $sales->superID($row['superID']); $avg_sales = $row['sales'] / $num_days * 7; $sales->lastYearSales($avg_sales); if ($future) { $sales->actualSales(0); $labor = $class_lib::getLabor($dbc); $labor->obfWeekID($week->obfWeekID()); $labor->obfCategoryID($row['id']); foreach ($labor->find() as $l) { $sales->growthTarget($l->growthTarget()); } } $sales->save(); } } }