PHP Tinebase_Backend_Sql_Abstract::_getSelect方法代码示例

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     $select->joinLeft($this->_tablePrefix . 'bill_debitor', $this->_db->quoteIdentifier($this->_tablePrefix . 'bill_debitor.id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.debitor_id'), array());
     $select->joinLeft($this->_tablePrefix . 'addressbook', $this->_db->quoteIdentifier($this->_tablePrefix . 'bill_debitor.contact_id') . ' = ' . $this->_db->quoteIdentifier($this->_tablePrefix . 'addressbook.id'), array());
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     // return probableTurnover (turnover * probability)
     if ($_cols == '*' || array_key_exists('probableTurnover', (array) $_cols)) {
         $select->columns(array('probableTurnover' => '(' . $this->_db->quoteIdentifier($this->_tableName . '.turnover') . '*' . $this->_db->quoteIdentifier($this->_tableName . '.probability') . '*0.01)'));
     }
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     $select->joinLeft(array('op' => $this->_tablePrefix . 'bill_order_position'), $this->_db->quoteIdentifier('op.id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.order_position_id'), array());
     $select->joinLeft(array('sa' => $this->_tablePrefix . 'bill_article'), $this->_db->quoteIdentifier('op.article_id') . ' = ' . $this->_db->quoteIdentifier('sa.id'), array());
     $select->joinLeft(array('order' => $this->_tablePrefix . 'bill_order'), $this->_db->quoteIdentifier('order.id') . ' = ' . $this->_db->quoteIdentifier('op.order_id'), array());
     $select->joinLeft(array('receipt' => $this->_tablePrefix . 'bill_receipt'), $this->_db->quoteIdentifier('receipt.id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.receipt_id'), array());
     $select->columns(array('article_id' => 'sa.id', 'unit_id' => 'sa.article_unit_id', 'article_nr' => 'sa.article_nr', 'article_group_id' => 'sa.article_group_id', 'article_series_id' => 'sa.article_series_id', 'article_ext_nr' => 'sa.article_ext_nr', 'name' => 'sa.name', 'description' => 'sa.description', 'price_netto' => 'op.price_netto', 'price_brutto' => 'op.price_brutto', 'min_price_netto' => 'MIN(op.price_netto)', 'min_price_brutto' => 'MIN(op.price_brutto)', 'max_price_netto' => 'MAX(op.price_netto)', 'max_price_brutto' => 'MAX(op.price_brutto)', 'amount' => 'SUM(op.amount)', 'total_netto' => 'SUM(op.total_netto)', 'total_brutto' => 'SUM(op.total_brutto)', 'total1_netto' => 'SUM(op.total1_netto)', 'total1_brutto' => 'SUM(op.total1_brutto)', 'total2_netto' => 'SUM(op.total2_netto)', 'total2_brutto' => 'SUM(op.total2_brutto)', 'date1' => 'MIN(receipt.invoice_date)', 'date2' => 'MAX(receipt.invoice_date)'));
     $select->group(array('sa.id'));
     return $select;
 }

 /**
  * Calendar optimized search function
  * 
  * 1. get all events neglecting grants filter
  * 2. get all related container grants (via resolving)
  * 3. compute effective grants in PHP and only keep events 
  *    user has required grant for
  * 
  * @TODO rethink if an outer container filter could help
  *
  * @param  Tinebase_Model_Filter_FilterGroup    $_filter
  * @param  Tinebase_Model_Pagination            $_pagination
  * @param  boolean                              $_onlyIds
  * @return Tinebase_Record_RecordSet|array
  */
 public function search(Tinebase_Model_Filter_FilterGroup $_filter = NULL, Tinebase_Model_Pagination $_pagination = NULL, $_onlyIds = FALSE)
 {
     if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
         Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Searching events ...');
     }
     if ($_pagination === NULL) {
         $_pagination = new Tinebase_Model_Pagination();
     }
     $getDeleted = is_object($_filter) && $_filter->getFilter('is_deleted');
     $select = parent::_getSelect('*', $getDeleted);
     $select->joinLeft(array('exdate' => $this->_tablePrefix . 'cal_exdate'), $this->_db->quoteIdentifier('exdate.cal_event_id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.id'), array('exdate' => $this->_dbCommand->getAggregate('exdate.exdate')));
     // NOTE: we join here as attendee and role filters need it
     $select->joinLeft(array('attendee' => $this->_tablePrefix . 'cal_attendee'), $this->_db->quoteIdentifier('attendee.cal_event_id') . ' = ' . $this->_db->quoteIdentifier('cal_events.id'), array());
     if (!$getDeleted) {
         $select->joinLeft(array('dispcontainer' => $this->_tablePrefix . 'container'), $this->_db->quoteIdentifier('dispcontainer.id') . ' = ' . $this->_db->quoteIdentifier('attendee.displaycontainer_id'), array());
         $select->where($this->_db->quoteIdentifier('dispcontainer.is_deleted') . ' = 0 OR ' . $this->_db->quoteIdentifier('dispcontainer.is_deleted') . 'IS NULL');
     }
     // remove grantsfilter here as we do grants computation in PHP
     $grantsFilter = $_filter->getFilter('grants');
     if ($grantsFilter) {
         $_filter->removeFilter('grants');
     }
     // clone the filter, as the filter is also used in the json frontend
     // and the calendar filter is used in the UI to
     $clonedFilters = clone $_filter;
     $calendarFilter = null;
     foreach ($clonedFilters as $filter) {
         if ($filter instanceof Calendar_Model_CalendarFilter) {
             $calendarFilter = $filter;
             $clonedFilters->removeFilter($filter);
             break;
         }
     }
     $this->_addFilter($select, $clonedFilters);
     $select->group($this->_tableName . '.' . 'id');
     Tinebase_Backend_Sql_Abstract::traitGroup($select);
     if ($calendarFilter) {
         $select1 = clone $select;
         $select2 = clone $select;
         $calendarFilter->appendFilterSql1($select1, $this);
         $calendarFilter->appendFilterSql2($select2, $this);
         $select = $this->getAdapter()->select()->union(array($select1, $select2));
     }
     $_pagination->appendPaginationSql($select);
     $stmt = $this->_db->query($select);
     $rows = (array) $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
     if (Tinebase_Core::isLogLevel(Zend_Log::TRACE)) {
         Tinebase_Core::getLogger()->trace(__METHOD__ . '::' . __LINE__ . ' Event base rows fetched: ' . count($rows) . ' select: ' . $select);
     }
     $result = $this->_rawDataToRecordSet($rows);
     $this->_checkGrants($result, $grantsFilter);
     return $_onlyIds ? $result->{is_bool($_onlyIds) ? $this->_getRecordIdentifier() : $_onlyIds} : $result;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     $select->joinLeft(array('bk' => $this->_tablePrefix . 'bill_booking'), $this->_db->quoteIdentifier('booking_id') . ' = ' . $this->_db->quoteIdentifier('bk.id'), array());
     $select->columns(array('booking_date' => 'bill_account_booking.booking_date', 'erp_context_id' => 'bk.erp_context_id', 'b_booking_date' => 'bk.booking_date'));
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     $select->joinLeft(array('tree_fileobjects' => $this->_tablePrefix . 'tree_fileobjects'), $this->_db->quoteIdentifier($this->_tableName . '.object_id') . ' = ' . $this->_db->quoteIdentifier('tree_fileobjects.id'), array('type', 'created_by', 'creation_time', 'last_modified_by', 'last_modified_time', 'revision', 'contenttype'))->joinLeft(array('tree_filerevisions' => $this->_tablePrefix . 'tree_filerevisions'), $this->_db->quoteIdentifier('tree_fileobjects.id') . ' = ' . $this->_db->quoteIdentifier('tree_filerevisions.id') . ' AND ' . $this->_db->quoteIdentifier('tree_fileobjects.revision') . ' = ' . $this->_db->quoteIdentifier('tree_filerevisions.revision'), array('hash', 'size'));
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *
  * @param array|string $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     if ($_cols === '*') {
         $select->joinLeft(array('owner' => SQL_TABLE_PREFIX . 'container_acl'), "{$this->_db->quoteIdentifier('owner.container_id')} = {$this->_db->quoteIdentifier('container.id')} AND " . "{$this->_db->quoteIdentifier('container.type')} = {$this->_db->quote(Tinebase_Model_Container::TYPE_PERSONAL)} AND " . "{$this->_db->quoteIdentifier('owner.account_type')} = {$this->_db->quote('user')} AND " . "{$this->_db->quoteIdentifier('owner.account_grant')} = {$this->_db->quote(Tinebase_Model_Grants::GRANT_ADMIN)}", array('owner_id' => 'account_id'));
     }
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     /*$select->joinLeft(array('rp' => $this->_tablePrefix . 'bill_receipt_position'),
         $this->_db->quoteIdentifier($this->_tableName . '.id') . ' = ' . $this->_db->quoteIdentifier('rp.receipt_id'),
       array());        
       
       $select->joinLeft(array('order' => $this->_tablePrefix . 'bill_order'),
         $this->_db->quoteIdentifier('order.id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.order_id'),
       array());
       
       $select->joinLeft(array('debitor_id' => $this->_tablePrefix . 'bill_order'),
         $this->_db->quoteIdentifier('order.id') . ' = ' . $this->_db->quoteIdentifier($this->_tableName . '.order_id'),
       array());
       */
     $select->columns(array());
     //$select->group(array('rp.receipt_id'));
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     $select->joinLeft(array('bact' => $this->_tablePrefix . 'bill_bank_account'), $this->_db->quoteIdentifier('bank_account_id') . ' = ' . $this->_db->quoteIdentifier('bact.id'), array());
     $select->joinLeft(array('ba' => $this->_tablePrefix . 'bill_bank'), $this->_db->quoteIdentifier('bact.bank_id') . ' = ' . $this->_db->quoteIdentifier('ba.id'), array());
     $select->columns(array('bank_id' => 'ba.id', 'iban' => 'bact.iban', 'contact_id' => 'bact.contact_id', 'bic' => 'ba.bic', 'account_name' => 'bact.name', 'bank_code' => 'ba.code', 'bank_name' => 'ba.name', 'bank_country_code' => 'ba.country_code', 'bank_postal_code' => 'ba.postal_code', 'bank_location' => 'ba.location'));
     return $select;
 }

 /**
  * get the basic select object to fetch records from the database
  *  
  * @param array|string|Zend_Db_Expr $_cols columns to get, * per default
  * @param boolean $_getDeleted get deleted records (if modlog is active)
  * @return Zend_Db_Select
  */
 protected function _getSelect($_cols = '*', $_getDeleted = FALSE)
 {
     $select = parent::_getSelect($_cols, $_getDeleted);
     $select->joinLeft(array('rc' => $this->_tablePrefix . 'bill_receipt'), $this->_db->quoteIdentifier($this->_tableName . '.receipt_id') . ' = ' . $this->_db->quoteIdentifier('rc.id'), array());
     /*$select->joinLeft(array('rp' => $this->_tablePrefix . 'bill_receipt_position'),
     		$this->_db->quoteIdentifier($this->_tableName . '.receipt_id') . ' = ' . $this->_db->quoteIdentifier('rp.receipt_id'),
            array());        
            
            $select->joinLeft(array('op' => $this->_tablePrefix . 'bill_order_position'),
              $this->_db->quoteIdentifier('op.id') . ' = ' . $this->_db->quoteIdentifier('rp.order_position_id'),
            array());*/
     $select->joinLeft(array('pay' => $this->_tablePrefix . 'bill_payment'), $this->_db->quoteIdentifier($this->_tableName . '.payment_id') . ' = ' . $this->_db->quoteIdentifier('pay.id'), array());
     $select->columns(array('due_days' => 'DATEDIFF(' . $this->_db->quote($this->getDueDate()->toString('yyyy-MM-dd')) . ',rc.due_date)+0', 'payment_date' => 'pay.payment_date', 'receipt_due_date' => 'rc.due_date', 'total_netto' => 'rc.total_netto', 'total_brutto' => 'rc.total_brutto', 'usage' => 'rc.usage', 'period' => 'rc.period', 'is_member' => 'rc.is_member', 'fee_group_id' => 'rc.fee_group_id'));
     //$select->group(array('rp.receipt_id'));
     return $select;
 }

 protected function _getCountSelect($_cols = '*', $_getDeleted = FALSE, $_filter, $fromDate = null, $untilDate = null)
 {
     $select = $this->_db->select();
     $subselect = parent::_getSelect(array('id', 'type'), FALSE);
     $strAddFrom = '';
     if (!is_null($fromDate)) {
         $fromDate = new Zend_Date($fromDate);
         $strAddFrom = ' AND ab.booking_date >= ' . $fromDate->toString('yyyy-mm-dd') . ' ';
     }
     $strAddUntil = '';
     if (!is_null($untilDate)) {
         $untilDate = new Zend_Date($untilDate);
         $strAddUntil = ' AND ab.booking_date <= ' . $untilDate->toString('yyyy-mm-dd');
     }
     $subselect->joinLeft(array('ab' => $this->_tablePrefix . 'bill_account_booking'), $this->_db->quoteIdentifier($this->_tableName . '.id') . ' = ' . $this->_db->quoteIdentifier('ab.account_system_id') . $strAddFrom . $strAddUntil, array());
     $this->_addFilter($subselect, $_filter);
     $subselect->group(array($this->_tableName . '.id', 'ab.account_system_id'));
     $select->from($subselect, array('count' => 'COUNT(*)'));
     return $select;
 }