Ejemplo n.º 1
0
 public function testTransactionArchiving()
 {
     $config = FannieConfig::factory();
     $task = new TransArchiveTask();
     $GLOBALS['FANNIE_ARCHIVE_METHOD'] = 'tables';
     /**
       Put a record in dtransactions that should trigger
       a new monthly table & view
     */
     $dtrans = new DTransactionsModel(FannieDB::get($config->get('TRANS_DB')));
     $dtrans->datetime('1901-01-01 00:00:00');
     $dtrans->save();
     $task->run();
     /**
       Verify the task created new monthly table & view
     */
     $archive_db = FannieDB::get($config->get('ARCHIVE_DB'));
     $archive_table_exists = $archive_db->tableExists('transArchive190101');
     $archive_dlog_exists = $archive_db->tableExists('dlog190101');
     $this->assertEquals(true, $archive_table_exists, 'Monthly archive table not created');
     $this->assertEquals(true, $archive_dlog_exists, 'Monthly dlog view not created');
     /**
       Verify dtransactions was cleared
     */
     $trans_db = FannieDB::get($config->get('TRANS_DB'));
     $records = $trans_db->query('SELECT * FROM dtransactions');
     $this->assertEquals(0, $trans_db->num_rows($records), 'dtransactions not cleared');
 }
Ejemplo n.º 2
0
 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;
 }
Ejemplo n.º 3
0
 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;
 }
Ejemplo n.º 4
0
 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;
 }
Ejemplo n.º 5
0
 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;
 }
Ejemplo n.º 6
0
 public function __construct($con)
 {
     $this->columns['store_row_id']['increment'] = false;
     $this->columns['store_row_id']['primary_key'] = false;
     $this->columns['store_row_id']['index'] = false;
     $this->columns['pos_row_id']['index'] = false;
     parent::__construct($con);
 }
Ejemplo n.º 7
0
 /**
   Override BasicModel::create to ensure
   date settings are present
 */
 public function create()
 {
     if ($this->name == '__needs_initialization') {
         return false;
     } else {
         return parent::create();
     }
 }
Ejemplo n.º 8
0
 /**
   Override BasicModel::create to add an 
   initial partition when the table is first
   created
 */
 public function create()
 {
     $exists = $this->connection->tableExists($this->name);
     $created = parent::create();
     if ($created && !$exists) {
         $this->initPartitions(date('Y'), date('n'));
     }
     return $created;
 }
Ejemplo n.º 9
0
 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;
 }
Ejemplo n.º 10
0
 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;
 }
Ejemplo n.º 11
0
 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;
 }
Ejemplo n.º 12
0
 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;
 }
Ejemplo n.º 13
0
 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;
 }
Ejemplo n.º 14
0
 public function __construct($con)
 {
     unset($this->columns['datetime']);
     $tdate = array('tdate' => array('type' => 'datetime', 'index' => True));
     $trans_num = array('trans_num' => array('type' => 'VARCHAR(25)'));
     $this->columns = $tdate + $this->columns + $trans_num;
     $this->columns['store_row_id']['increment'] = false;
     $this->columns['store_row_id']['primary_key'] = false;
     $this->columns['pos_row_id']['index'] = false;
     parent::__construct($con);
 }
Ejemplo n.º 15
0
 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;
 }
Ejemplo n.º 16
0
 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;
 }
Ejemplo n.º 17
0
 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']));
         }
     }
 }
Ejemplo n.º 18
0
 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;
 }
Ejemplo n.º 19
0
 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $FANNIE_TRANS_DB = $this->config->get('TRANS_DB');
     $dtrans = $FANNIE_TRANS_DB . $dbc->sep() . 'transarchive';
     $union = true;
     $args = array();
     try {
         $date1 = $this->form->date1;
         $date2 = $this->form->date2;
         $dtrans = DTransactionsModel::selectDTrans($date1, $date2);
         $union = false;
         $args[] = $date1 . ' 00:00:00';
         $args[] = $date2 . ' 23:59:59';
     } catch (Exception $ex) {
         $date1 = '';
         $date2 = '';
     }
     /**
       I'm using {{placeholders}}
       to build the basic query, then replacing those
       pieces depending on date range options
     */
     $query = "SELECT\n                    YEAR(datetime) AS year,\n                    MONTH(datetime) AS month,\n                    DAY(datetime) AS day,\n                    d.upc,\n                    d.description,\n                    d.department,\n                    e.dept_name,\n                    SUM(d.quantity) AS quantity,\n                    SUM(d.total) AS total,\n                    s.description AS shrinkReason,\n                    m.super_name,\n                    e.salesCode,\n                    d.charflag\n                  FROM {{table}} AS d\n                    LEFT JOIN departments AS e ON d.department=e.dept_no\n                    LEFT JOIN ShrinkReasons AS s ON d.numflag=s.shrinkReasonID\n                    LEFT JOIN MasterSuperDepts AS m ON d.department=m.dept_ID\n                  WHERE trans_status = 'Z'\n                    AND trans_type IN ('D', 'I')\n                    AND trans_subtype IN ('','0')\n                    AND emp_no <> 9999\n                    AND register_no <> 99\n                    AND upc <> '0'\n                    {{date_clause}}\n                  GROUP BY\n                    YEAR(datetime),\n                    MONTH(datetime),\n                    DAY(datetime),\n                    d.upc,\n                    d.description,\n                    d.department,\n                    e.dept_name,\n                    s.description";
     $fullQuery = '';
     if (!$union) {
         // user selected date range
         $fullQuery = str_replace('{{table}}', $dtrans, $query);
         $fullQuery = str_replace('{{date_clause}}', 'AND datetime BETWEEN ? AND ?', $fullQuery);
     } else {
         // union of today (dtransaction)
         // plus last quarter (transarchive)
         $today_table = $FANNIE_TRANS_DB . $dbc->sep() . 'dtransactions';
         $today_clause = ' AND ' . $dbc->datediff($dbc->now(), 'datetime') . ' = 0';
         $query1 = str_replace('{{table}}', $today_table, $query);
         $query1 = str_replace('{{date_clause}}', $today_clause, $query1);
         $query2 = str_replace('{{table}}', $dtrans, $query);
         $query2 = str_replace('{{date_clause}}', '', $query2);
         $fullQuery = $query1 . ' UNION ALL ' . $query2;
     }
     $data = array();
     $prep = $dbc->prepare($fullQuery);
     $result = $dbc->execute($prep, $args);
     while ($row = $dbc->fetch_row($result)) {
         $record = array(date('Y-m-d', mktime(0, 0, 0, $row['month'], $row['day'], $row['year'])), $row['upc'], $row['description'], $row['department'], $row['dept_name'], $row['salesCode'], $row['super_name'], sprintf('%.2f', $row['quantity']), sprintf('%.2f', $row['total']), empty($row['shrinkReason']) ? 'n/a' : $row['shrinkReason'], $row['charflag'] == 'C' ? 'No' : 'Yes');
         $data[] = $record;
     }
     return $data;
 }
Ejemplo n.º 20
0
 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);
 }
Ejemplo n.º 21
0
 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;
 }
Ejemplo n.º 22
0
 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;
 }
Ejemplo n.º 23
0
 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;
 }
Ejemplo n.º 24
0
 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();
     }
 }
Ejemplo n.º 25
0
 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;
 }
Ejemplo n.º 26
0
 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;
 }
Ejemplo n.º 27
0
 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();
 }
Ejemplo n.º 28
0
 public function get_date1_date2_dept_view()
 {
     $start = FormLib::get('date1');
     $end = FormLib::get('date2');
     $dept_limit = FormLib::get('dept');
     $method = FormLib::get('sql-method');
     $dlog = false;
     $dbc = FannieDB::get($this->config->get('OP_DB'));
     $timing_point_1 = microtime(true);
     switch ($method) {
         case 'Large Temporary Table':
             $where = array('connection' => $dbc, 'clauses' => array(array('sql' => ' trans_type IN (\'I\',\'D\') ', 'params' => array()), array('sql' => ' department BETWEEN 0 AND ? ', 'params' => array($dept_limit))));
             $dlog = DTransactionsModel::selectDTrans($start, $end, $where);
             break;
         case 'Aggregate Temporary Table':
             $where = new stdclass();
             $where->sql = array("trans_type IN ('I', 'D')", "department BETWEEN 0 AND ?");
             $where->params = array($dept_limit);
             $groupby = array('department');
             $dlog = DTransactionsModel::aggregateDtrans($dbc, $start, $end, $where, $groupby);
             break;
         case 'Single Query':
         default:
             $dlog = DTransactionsModel::selectDTrans($start, $end);
             break;
     }
     $timing_point_2 = microtime(true);
     $ret = '<p>Using archive table(s): <em>' . $dlog . '</em></p>';
     $query = '
         SELECT d.dept_name,
             SUM(t.total) AS ttl
         FROM ' . $dlog . ' AS t
             LEFT JOIN departments AS d ON t.department=d.dept_no
         WHERE t.datetime BETWEEN ? AND ?
             AND trans_type IN (\'I\', \'D\')
             AND t.department BETWEEN 0 AND ?
         GROUP BY d.dept_name';
     $prep = $dbc->prepare($query);
     $args = array($start . ' 00:00:00', $end . ' 23:59:59', $dept_limit);
     $result = $dbc->execute($prep, $args);
     $timing_point_3 = microtime(true);
     $ret .= '<p>Query used:<pre>' . $query . '</pre></p>';
     $ret .= '<p>Query succeeded: ' . ($result ? 'Yes' : 'No') . '</p>';
     $ret .= '<p>Elapsed time: ' . ($timing_point_3 - $timing_point_1) . '</p>';
     return $ret;
 }
Ejemplo n.º 29
0
 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;
 }
Ejemplo n.º 30
0
 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;
 }