PHP Dao::getSingleResultNative方法代码示例

 private function _getSeries($groupFrame, $from, $to, $statusId = array())
 {
     $select = array();
     foreach ($groupFrame as $index => $time) {
         $select[] = 'sum(if((created >= "' . $time['from'] . '" && created < "' . $time['to'] . '"), 1 , 0)) `' . $index . '`';
     }
     $where = array('active = 1');
     if (count($statusId) > 0) {
         $where[] = 'statusId in (' . implode(', ', $statusId) . ')';
     }
     $sql = "select " . implode(', ', $select) . ' from `order` where ' . implode(' AND ', $where) . ' and created >=? and created < ?';
     $row = Dao::getSingleResultNative($sql, array(trim($from), trim($to)), PDO::FETCH_NUM);
     $return = array();
     foreach ($row as $col) {
         $return[] = intval($col);
     }
     return $return;
 }

    /**
     * Getting the items
     *
     * @param unknown $sender
     * @param unknown $param
     * @throws Exception
     *
     */
    public function getItems($sender, $param)
    {
        $results = $errors = array();
        try {
            $class = trim($this->_focusEntity);
            $pageNo = 1;
            $pageSize = DaoQuery::DEFAUTL_PAGE_SIZE;
            if (isset($param->CallbackParameter->pagination)) {
                $pageNo = $param->CallbackParameter->pagination->pageNo;
                $pageSize = $param->CallbackParameter->pagination->pageSize;
            }
            $where = array('ri.active = :active');
            $params = array('active' => 1);
            if (isset($param->CallbackParameter->searchCriteria)) {
                $criteria = $param->CallbackParameter->searchCriteria;
                if (isset($criteria->invoiceNo) && ($invNo = trim($criteria->invoiceNo)) !== '') {
                    $where[] = 'ri.invoiceNo like :invNo';
                    $params['invNo'] = '%' . $invNo . '%';
                }
                if (isset($criteria->purchaseOrderIds) && count($purchaseOrderIds = array_filter(explode(',', trim($criteria->purchaseOrderIds)))) > 0) {
                    $poWhere = array();
                    foreach ($purchaseOrderIds as $index => $purchaseOrderId) {
                        $key = 'purchaseOrderId' . $index;
                        $poWhere[] = ':' . $key;
                        $params[$key] = $purchaseOrderId;
                    }
                    $where[] = 'ri.purchaseOrderId in(' . implode(', ', $poWhere) . ')';
                }
                if (isset($criteria->supplierIds) && count($supplierIds = array_filter(explode(',', trim($criteria->supplierIds)))) > 0) {
                    $suppWhere = array();
                    foreach ($supplierIds as $index => $supplierId) {
                        $key = 'supplierId' . $index;
                        $suppWhere[] = ':' . $key;
                        $params[$key] = $supplierId;
                    }
                    $where[] = 'po.supplierId in(' . implode(', ', $suppWhere) . ')';
                }
            }
            $sql = 'select sql_calc_found_rows ri.invoiceNo,
						po.supplierId,
						sum(ri.qty) `qty`,
						sum(ri.unitPrice * ri.qty) `price`,
						group_concat(distinct po.id) `poIds`,
						group_concat(distinct ri.id) `itemIds`,
						min(ri.created) `created`
					from receivingitem ri
					inner join purchaseorder po on (po.id = ri.purchaseOrderId)
					where ' . implode(' AND ', $where) . '
					group by po.supplierId,  ri.invoiceNo
					order by ri.id desc
					limit ' . ($pageNo - 1) * $pageSize . ', ' . $pageSize;
            $rows = Dao::getResultsNative($sql, $params);
            $stats = array();
            $statsResult = Dao::getSingleResultNative('select found_rows()', array(), PDO::FETCH_NUM);
            $stats['totalRows'] = intval($statsResult[0]);
            $stats['pageSize'] = $pageSize;
            $stats['pageNumber'] = $pageNo;
            $stats['totalPages'] = intval(ceil($stats['totalRows'] / $stats['pageSize']));
            $results['items'] = array();
            foreach ($rows as $row) {
                $pos = count($poIds = explode(',', $row['poIds'])) === 0 ? array() : PurchaseOrder::getAllByCriteria('id in (' . implode(',', array_fill(0, count($poIds), '?')) . ')', $poIds);
                $results['items'][] = array('invoiceNo' => $row['invoiceNo'], 'supplier' => Supplier::get($row['supplierId'])->getJson(), 'created' => $row['created'], 'totalQty' => $row['qty'], 'totalPrice' => $row['price'], 'purchaseOrders' => array_map(create_function('$a', 'return $a->getJson();'), $pos), 'poIds' => explode(',', $row['poIds']), 'itemIds' => explode(',', $row['itemIds']));
            }
            $results['pageStats'] = $stats;
        } catch (Exception $ex) {
            $errors[] = $ex->getMessage();
        }
        $param->ResponseData = StringUtilsAbstract::getJson($results, $errors);
    }

 /**
  * Getter for totalReceivedCount
  *
  * @return int
  */
 public function getTotalReceivedCount()
 {
     $sql = "select sum(qty) `qty` from receivingitem where active = 1 and purchaseOrderId = ? ";
     $result = Dao::getSingleResultNative($sql, array($this->getId()));
     return intval($result['qty']);
 }

 private function _getSeries($groupFrame, $from, $to, array $productIds, $showPrice = false)
 {
     $select = array();
     foreach ($groupFrame as $index => $time) {
         $select[] = 'sum(if((created >= "' . $time['from'] . '" && created < "' . $time['to'] . '"), ' . ($showPrice === true ? 'totalPrice' : 'qtyOrdered') . ' , 0)) `' . $index . '`';
     }
     $where = array('active = 1');
     if (count($productIds) > 0) {
         $where[] = 'productId in (' . implode(', ', $productIds) . ')';
     }
     $sql = "select " . implode(', ', $select) . ' from `orderitem` where ' . implode(' AND ', $where) . ' and created >=? and created <= ?';
     $row = Dao::getSingleResultNative($sql, array(trim($from), trim($to)), PDO::FETCH_NUM);
     $return = array();
     foreach ($row as $col) {
         $return[] = $showPrice === true ? (double) $col : intval($col);
     }
     return $return;
 }