/**
* 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;
}
