/** * 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); // add join only if needed and allowed if ($_cols == '*' || is_array($_cols) && isset($_cols['context'])) { $select->joinLeft(array('contexts' => $this->_tablePrefix . 'asterisk_context'), 'context_id = contexts.id', array('context' => 'name')); } 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; }
/** * 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($this->_revisionsTableName => $this->_tablePrefix . $this->_revisionsTableName), $this->_db->quoteIdentifier($this->_tableName . '.id') . ' = ' . $this->_db->quoteIdentifier($this->_revisionsTableName . '.id') . ' AND ' . $this->_db->quoteIdentifier($this->_tableName . '.revision') . ' = ' . $this->_db->quoteIdentifier($this->_revisionsTableName . '.revision'), array('revision', 'hash', 'size')); 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('ba' => $this->_tablePrefix . 'bill_bank'), $this->_db->quoteIdentifier('bank_id') . ' = ' . $this->_db->quoteIdentifier('ba.id'), array()); $select->columns(array('bic' => 'ba.bic', 'bank_name' => 'ba.name', 'bank_code' => 'ba.code')); 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; }