PHP FormLib::standardItemFromWhere方法代码示例

 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $query_parts = FormLib::standardItemFromWhere();
     $query = '
         SELECT t.upc,
             t.description,
             t.department,
             d.dept_name,
             ' . DTrans::sumQuantity('t') . ' AS qty,
             SUM(t.total) AS total ' . $query_parts['query'] . ' AND t.memType=?
         GROUP BY t.upc,
             t.description,
             t.department,
             d.dept_name
         ORDER BY t.upc';
     $args = $query_parts['args'];
     $args[] = FormLib::get('memtype');
     $data = array();
     $prep = $dbc->prepare($query);
     $res = $dbc->execute($prep, $args);
     while ($w = $dbc->fetchRow($res)) {
         $data[] = array($w['upc'], $w['description'], $w['department'] . ' ' . $w['dept_name'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['total']));
     }
     return $data;
 }

 public function fetch_report_data()
 {
     $dbc = $this->connection;
     $dbc->selectDB($this->config->get('OP_DB'));
     $query = '';
     $from_where = FormLib::standardItemFromWhere();
     switch ($this->mode) {
         case 'PLU':
             $query = "\n                    SELECT t.upc,\n                        CASE WHEN p.description IS NULL THEN t.description ELSE p.description END as description, \n                        SUM(CASE WHEN trans_status IN('','0') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings," . DTrans::sumQuantity('t') . " as qty,\n                        SUM(t.total) AS total,\n                        t.department,\n                        d.dept_name,\n                        m.super_name,\n                        COALESCE(v.vendorName,x.distributor) AS distributor\n                    " . $from_where['query'] . "\n                    GROUP BY t.upc,\n                        CASE WHEN p.description IS NULL THEN t.description ELSE p.description END,\n                        CASE WHEN t.trans_status='R' THEN 'Refund' ELSE 'Sale' END,\n                        t.department,\n                        d.dept_name,\n                        m.super_name,\n                        COALESCE(v.vendorName,x.distributor)\n                    ORDER BY SUM(t.total) DESC";
             break;
         case 'Department':
             $query = "\n                    SELECT t.department,\n                        d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n                        SUM(total) AS total \n                    " . $from_where['query'] . "\n                    GROUP BY t.department,\n                        d.dept_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(total) AS total \n                    " . $from_where['query'] . "\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 'Weekday':
             $cols = $dbc->dayofweek("tdate") . " AS dayNumber,CASE \n                    WHEN " . $dbc->dayofweek("tdate") . "=1 THEN 'Sun'\n                    WHEN " . $dbc->dayofweek("tdate") . "=2 THEN 'Mon'\n                    WHEN " . $dbc->dayofweek("tdate") . "=3 THEN 'Tue'\n                    WHEN " . $dbc->dayofweek("tdate") . "=4 THEN 'Wed'\n                    WHEN " . $dbc->dayofweek("tdate") . "=5 THEN 'Thu'\n                    WHEN " . $dbc->dayofweek("tdate") . "=6 THEN 'Fri'\n                    WHEN " . $dbc->dayofweek("tdate") . "=7 THEN 'Sat'\n                    ELSE 'Err' END";
             $query = "\n                    SELECT " . $cols . " AS dayName, " . DTrans::sumQuantity('t') . " as qty,\n                        SUM(total) as total \n                    " . $from_where['query'] . "\n                    GROUP BY " . str_replace(' AS dayNumber', '', $cols) . "\n                    ORDER BY " . $dbc->dayofweek('t.tdate');
             break;
     }
     $prep = $dbc->prepare($query);
     $result = $dbc->execute($prep, $from_where['args']);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         switch ($this->mode) {
             case 'PLU':
                 $data[] = array($row['upc'], $row['description'], $row['rings'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']), $row['department'], $row['dept_name'], $row['super_name'], $row['distributor']);
                 break;
             case 'Department':
                 $data[] = array($row['department'], $row['dept_name'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
                 break;
             case 'Date':
                 $tstamp = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);
                 $data[] = array(date('m/d/Y', $tstamp), date('l', $tstamp), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
                 break;
             case 'Weekday':
                 $data[] = array($row['dayNumber'], $row['dayName'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
                 break;
         }
     }
     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;
     $dlog = DTransactionsModel::selectDlog($date1, $date2);
     $from_where = FormLib::standardItemFromWhere();
     $select_cols = '
         t.upc AS prodID, 
         CASE WHEN p.brand IS NULL THEN \'\' ELSE p.brand END AS brand, 
         CASE WHEN p.description IS NULL THEN t.description ELSE p.description END AS description';
     $group_cols = '
         t.upc, 
         p.brand, 
         CASE WHEN p.description IS NULL THEN t.description ELSE p.description END';
     if (FormLib::get('lookup-type') == 'likecode') {
         $select_cols = '
             u.likeCode AS prodID,
             \'\' AS brand,
             u.likeCodeDesc AS description';
         $group_cols = '
             u.likeCode,
             u.likeCodeDesc';
     }
     $query = "\n            SELECT \n                YEAR(t.tdate) AS year,\n                MONTH(t.tdate) AS month,\n                DAY(t.tdate) AS day,\n                {$select_cols}, " . DTrans::sumQuantity('t') . " AS total\n            " . $from_where['query'] . "\n                AND trans_status <> 'M'\n                AND trans_type = 'I'\n            GROUP BY YEAR(t.tdate),\n                MONTH(t.tdate),\n                DAY(t.tdate),\n                {$group_cols}\n            ORDER BY prodID,\n                YEAR(t.tdate),\n                MONTH(t.tdate),\n                DAY(t.tdate)";
     $prep = $dbc->prepare_statement($query);
     $result = $dbc->exec_statement($prep, $from_where['args']);
     // variable columns. one per dates
     $dates = array();
     while ($date1 != $date2) {
         $dates[] = $date1;
         $parts = explode("-", $date1);
         if (count($parts) != 3) {
             break;
         }
         $date1 = date("Y-m-d", mktime(0, 0, 0, $parts[1], $parts[2] + 1, $parts[0]));
     }
     $dates[] = $date2;
     $this->report_headers = array('UPC', 'Brand', 'Description');
     foreach ($dates as $i) {
         $this->report_headers[] = $i;
     }
     $this->report_headers[] = 'Total';
     $current = array('upc' => '', 'brand' => '', 'description' => '');
     $data = array();
     // track upc while going through the rows, storing
     // all data about a given upc before printing
     while ($row = $dbc->fetch_array($result)) {
         if ($current['upc'] != $row['prodID']) {
             if ($current['upc'] != "") {
                 $record = array($current['upc'], $current['brand'], $current['description']);
                 $sum = 0.0;
                 foreach ($dates as $i) {
                     if (isset($current[$i])) {
                         $record[] = sprintf('%.2f', $current[$i]);
                         $sum += $current[$i];
                     } else {
                         $record[] = 0.0;
                     }
                 }
                 $record[] = sprintf('%.2f', $sum);
                 $data[] = $record;
             }
             // update 'current' values and clear data
             // brand may be missing in the case of like codes
             $current = array('upc' => $row['prodID'], 'brand' => $row['brand'], 'description' => $row['description']);
         }
         // get a yyyy-mm-dd format date from sql results
         $year = $row['year'];
         $month = str_pad($row['month'], 2, '0', STR_PAD_LEFT);
         $day = str_pad($row['day'], 2, '0', STR_PAD_LEFT);
         $datestr = $year . "-" . $month . "-" . $day;
         // index result into data based on date string
         // this is to properly place data in the output table
         // even when there are 'missing' days for a given upc
         $current[$datestr] = $row['total'];
     }
     // add the last data set
     $record = array($current['upc'], $current['brand'], $current['description']);
     $sum = 0.0;
     foreach ($dates as $i) {
         if (isset($current[$i])) {
             $record[] = sprintf('%.2f', $current[$i]);
             $sum += $current[$i];
         } else {
             $record[] = 0.0;
         }
     }
     $record[] = sprintf('%.2f', $sum);
     $data[] = $record;
     return $data;
 }