public function getTotal($type, $start = null, $end = null)
{
$select = new Zend_Db_Select($this->getAdapter());
$select->from($this->info('name'), 'SUM(value) as sum')->where('type = ?', $type);
// Can pass "today" into start
switch ($start) {
case 'day':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n"), gmdate("j") + 1, gmdate("Y"));
break;
case 'week':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1, gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1 + 7, gmdate("Y"));
break;
case 'month':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n") + 1, gmdate("j"), gmdate("Y"));
break;
case 'year':
$start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
$end = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y") + 1);
break;
}
if (null !== $start) {
$select->where('date >= ?', gmdate('Y-m-d', $start));
}
if (null !== $end) {
$select->where('date < ?', gmdate('Y-m-d', $end));
}
$data = $select->query()->fetch();
if (!isset($data['sum'])) {
return 0;
}
return $data['sum'];
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_NotFound
*/
public function appendFilterSql($_select, $_backend)
{
// quote field identifier, set action and replace wildcards
$field = $this->_getQuotedFieldName($_backend);
$action = $this->_opSqlMap[$this->_operator];
$value = $this->_replaceWildcards($this->_value);
// check if group by is operator and return if this is the case
if ($this->_operator == 'group') {
$_select->group($this->_field);
}
if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) {
$value = explode(' ', $value);
}
if (is_array($value) && empty($value)) {
$_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */'));
return;
}
$where = Tinebase_Core::getDb()->quoteInto($field . $action['sqlop'], $value);
if (in_array($this->_operator, array('not', 'notin')) && $value !== '') {
$where = "( {$where} OR {$field} IS NULL)";
}
if (in_array($this->_operator, array('equals', 'contains', 'startswith', 'endswith', 'in')) && $value === '') {
$where = "( {$where} OR {$field} IS NULL)";
}
// finally append query to select object
$_select->where($where);
}
/**
* Run query and returns matches, or null if no matches are found.
*
* @param String $value
* @return Array when matches are found.
*/
protected function _query($value)
{
/**
* Check for an adapter being defined. if not, fetch the default adapter.
*/
if ($this->_adapter === null) {
$this->_adapter = Zend_Db_Table_Abstract::getDefaultAdapter();
}
/**
* Build select object
*/
$select = new Zend_Db_Select($this->_adapter);
$select->from($this->_table, array($this->_field), $this->_schema)->where($this->_adapter->quoteIdentifier($this->_field) . ' = ?', $value);
if ($this->_exclude !== null) {
if (is_array($this->_exclude)) {
$select->where($this->_adapter->quoteIdentifier($this->_exclude['field']) . ' != ?', $this->_exclude['value']);
} else {
$select->where($this->_exclude);
}
}
$select->limit(1);
/**
* Run query
*/
$result = $this->_adapter->fetchRow($select, array(), Zend_Db::FETCH_ASSOC);
return $result;
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
$action = $this->_opSqlMap[$this->_operator];
if (empty($this->_value) && $this->_value != '0') {
// prevent sql error
if ($this->_operator == 'in' || $this->_operator == 'equals') {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Empty value with "in" operator (model: ' . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options') . ')');
}
$_select->where('1=0');
}
} else {
if ($this->_operator == 'equals' && is_array($this->_value)) {
if (Tinebase_Core::isLogLevel(Zend_Log::NOTICE)) {
Tinebase_Core::getLogger()->notice(__METHOD__ . '::' . __LINE__ . ' Unexpected array value with "equals" operator (model: ' . (isset($this->_options['modelName']) ? $this->_options['modelName'] : 'unknown / no modelName defined in filter options') . ')');
}
$_select->where('1=0');
} else {
$type = $this->_getFieldType($_backend);
$this->_enforceValueType($type);
$field = $this->_getQuotedFieldName($_backend);
// finally append query to select object
$_select->where($field . $action['sqlop'], $this->_value, $type);
}
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
// prepare value
if ($this->_operator === 'equals' && empty($this->_value)) {
// @see 0009362: allow to filter for empty datetimes
$operator = 'isnull';
$value = array($this->_value);
} else {
$operator = $this->_operator;
$value = $this->_getDateValues($operator, $this->_value);
if (!is_array($value)) {
// NOTE: (array) null is an empty array
$value = array($value);
}
}
// quote field identifier
$field = $this->_getQuotedFieldName($_backend);
$db = Tinebase_Core::getDb();
$dbCommand = Tinebase_Backend_Sql_Command::factory($db);
// append query to select object
foreach ((array) $this->_opSqlMap[$operator]['sqlop'] as $num => $operator) {
if (isset($value[$num]) || array_key_exists($num, $value)) {
if (get_parent_class($this) === 'Tinebase_Model_Filter_Date' || in_array($operator, array('isnull', 'notnull'))) {
$_select->where($field . $operator, $value[$num]);
} else {
$_select->where($dbCommand->setDate($field) . $operator, new Zend_Db_Expr($dbCommand->setDateValue($value[$num])));
}
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' No filter value found, skipping operator: ' . $operator);
}
}
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
// quote field identifier, set action and replace wildcards
$field = $this->_getQuotedFieldName($_backend);
$action = $this->_opSqlMap[$this->_operator];
$value = $this->_replaceWildcards($this->_value);
if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) {
$value = explode(' ', $this->_value);
}
if (in_array($this->_operator, array('equals', 'greater', 'less', 'in', 'notin'))) {
$value = str_replace(array('%', '\\_'), '', $value);
if (is_array($value) && empty($value)) {
$_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */'));
} elseif ($this->_operator == 'equals' && ($value === '' || $value === NULL || $value === false)) {
$_select->where($field . 'IS NULL');
} else {
// finally append query to select object
$_select->where($field . $action['sqlop'], $value, $this->valueType);
}
} else {
// finally append query to select object
$_select->where($field . $action['sqlop'], $value);
}
if (in_array($this->_operator, array('not', 'notin')) && $value !== '') {
$_select->orWhere($field . ' IS NULL');
}
}
public static function _setSelectRules(Zend_Db_Select $select)
{
$select->where('accountStatus != ?', self::STATUS_DELETED);
if (!is_null(self::getMyType())) {
$select->where('type = ?', static::getMyType());
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
if ($this->_value === self::VALUE_NOTSET) {
return;
}
$action = $this->_opSqlMap[$this->_operator];
$db = $_backend->getAdapter();
// prepare value
$value = $this->_value ? 1 : 0;
if (!empty($this->_options['fields'])) {
foreach ((array) $this->_options['fields'] as $fieldName) {
$quotedField = $db->quoteIdentifier(strpos($fieldName, '.') === false ? $_backend->getTableName() . '.' . $fieldName : $fieldName);
if ($value) {
$_select->where($quotedField . $action['sqlop'], $value);
} else {
$_select->orwhere($quotedField . $action['sqlop'], $value);
}
}
} else {
if (!empty($this->_options['leftOperand'])) {
$_select->where($this->_options['leftOperand'] . $action['sqlop'], $value);
} else {
$_select->where($this->_getQuotedFieldName($_backend) . $action['sqlop'], $value);
}
}
}
function getById($jobid)
{
// do Bacula ACLs
Zend_Loader::loadClass('Job');
$table = new Job();
if (!$table->isJobIdExists($jobid)) {
return FALSE;
}
$select = new Zend_Db_Select($this->db);
switch ($this->db_adapter) {
case 'PDO_SQLITE':
// bug http://framework.zend.com/issues/browse/ZF-884
$select->distinct();
$select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
$select->where("JobId = ?", $jobid);
$select->order(array('LogId', 'LogTime'));
break;
default:
// mysql, postgresql
$select->distinct();
$select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
$select->where("JobId = ?", $jobid);
$select->order(array('LogId', 'LogTime'));
}
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
$stmt = $select->query();
return $stmt->fetchAll();
}
/**
* Returns the select object to be used for validation.
* @return Zend_Db_Select $select
*/
public function getSelect()
{
if (null === $this->_select) {
$db = $this->getAdapter();
// Zend_Debug::dump($this->_excludeId ); // die(0);
$select = new Zend_Db_Select($db);
$select->from($this->_table, array($this->_field), $this->_schema);
if ($db->supportsParameters('named')) {
$select->where($db->quoteIdentifier($this->_field, true) . ' = :value');
// named
} else {
$select->where($db->quoteIdentifier($this->_field, true) . ' = ?');
// positional
}
$select->limit(1);
if (!empty($this->_meetingId)) {
$select->where($db->quoteInto('`meeting_id` = ?', $this->_meetingId));
}
if (!empty($this->_excludeId)) {
$select->where($db->quoteInto('`id` != ?', $this->_excludeId));
}
$this->_select = $select;
}
return $this->_select;
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
if (empty($this->_value)) {
$_select->where('1=1/* empty query */');
return;
}
$db = Tinebase_Core::getDb();
switch ($this->_operator) {
case 'contains':
case 'equals':
case 'startswith':
$queries = explode(' ', $this->_value);
foreach ($queries as $query) {
$whereParts = array();
foreach ($this->_options['fields'] as $qField) {
// if field has . in name, then we already have tablename
if (strpos($qField, '.') !== FALSE) {
$whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)'));
} else {
$whereParts[] = Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent($db->quoteIdentifier($_backend->getTableName() . '.' . $qField))) . ' ' . Tinebase_Backend_Sql_Command::factory($db)->getLike() . Tinebase_Backend_Sql_Command::factory($db)->prepareForILike(Tinebase_Backend_Sql_Command::factory($db)->getUnaccent('(?)'));
}
}
$whereClause = '';
if (!empty($whereParts)) {
$whereClause = implode(' OR ', $whereParts);
}
if (!empty($whereClause)) {
if ($this->_operator == 'equals') {
$_select->where($db->quoteInto($whereClause, trim($query)));
} else {
if ($this->_operator == 'startswith') {
$_select->where($db->quoteInto($whereClause, trim($query) . '%'));
} else {
$_select->where($db->quoteInto($whereClause, '%' . trim($query) . '%'));
}
}
}
}
break;
case 'in':
foreach ($this->_options['fields'] as $qField) {
// if field has . in name, then we allready have tablename
if (strpos($qField, '.') !== FALSE) {
$whereParts[] = $db->quoteInto($db->quoteIdentifier($qField) . ' IN (?)', (array) $this->_value);
} else {
$whereParts[] = $db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.' . $qField) . ' IN (?)', (array) $this->_value);
}
}
if (!empty($whereParts)) {
$whereClause = implode(' OR ', $whereParts);
}
if (!empty($whereClause)) {
$_select->where($whereClause);
}
break;
default:
throw new Tinebase_Exception_InvalidArgument('Operator not defined: ' . $this->_operator);
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_NotFound
*/
public function appendFilterSql($_select, $_backend)
{
$db = $_backend->getAdapter();
$correlationName = Tinebase_Record_Abstract::generateUID() . $this->_value . 'owner';
$_select->joinLeft(array($correlationName => SQL_TABLE_PREFIX . 'container_acl'), $db->quoteIdentifier("{$correlationName}.container_id") . " = container.id", array());
// only personal containers have an owner!
$_select->where("{$db->quoteIdentifier('container.type')} = ?", Tinebase_Model_Container::TYPE_PERSONAL);
// assure admin grant
$_select->where($db->quoteIdentifier("{$correlationName}.account_id") . " = " . $db->quote($this->_value) . ' AND ' . $db->quoteIdentifier("{$correlationName}.account_grant") . " = ?", Tinebase_Model_Grants::GRANT_ADMIN);
}
/**
* Padrão de filtros para os relatórios FEFOP
*
* @access protected
* @param Zend_Db_Select $select
* @return void
*/
protected function _whereDefault(Zend_Db_Select $select)
{
if (!empty($this->_data['fk_id_dec'])) {
$select->where('SysUser.fk_id_dec IN(?)', $this->_data['fk_id_dec']);
}
if (!empty($this->_data['id_fefop_programs'])) {
$select->where('FEFOP_Contract.fk_id_fefop_programs IN(?)', $this->_data['id_fefop_programs']);
}
if (!empty($this->_data['id_fefop_modules'])) {
$select->where('FEFOP_Contract.fk_id_fefop_modules IN(?)', $this->_data['id_fefop_modules']);
}
if (!empty($this->_data['id_adddistrict'])) {
$select->where('AddDistrict.id_adddistrict IN(?)', $this->_data['id_adddistrict']);
}
if (!empty($this->_data['id_scholarity_area'])) {
$select->where('DRH_TrainingPlan.fk_id_scholarity_area = ?', $this->_data['id_scholarity_area']);
}
if (!empty($this->_data['id_profocupationtimor'])) {
$select->where('DRH_TrainingPlan.fk_id_profocupationtimor = ?', $this->_data['id_profocupationtimor']);
}
if (!empty($this->_data['id_fefpeduinstitution'])) {
$select->where('DRH_TrainingPlan.fk_id_fefpeduinstitution = ?', $this->_data['id_fefpeduinstitution']);
}
if (!empty($this->_data['id_fefop_status'])) {
$select->where('s.id_fefop_status IN(?)', $this->_data['id_fefop_status']);
}
if (!empty($this->_data['id_beneficiary'])) {
$select->where('b.id = ?', $this->_data['id_beneficiary']);
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
$correlationName = Tinebase_Record_Abstract::generateUID(30);
$db = $_backend->getAdapter();
$_select->joinLeft(array($correlationName => $db->table_prefix . 'addressbook_list_members'), $db->quoteIdentifier($correlationName . '.contact_id') . ' = ' . $db->quoteIdentifier('addressbook.id'), array());
if (null === $this->_value) {
$_select->where($db->quoteIdentifier($correlationName . '.list_id') . ' IS NULL');
} else {
$_select->where($db->quoteIdentifier($correlationName . '.list_id') . ' IN (?)', (array) $this->_value);
}
}
/**
* Get Canonical Collection ( all different link for that product )
*
* @param string $storeId
* @param string $prodId
*
* @return Zend_Db_Statement_Interface
*/
public function _getCollectionCE($storeId, $prodId, $catId = null)
{
$adapter = $this->_getReadAdapter();
$urlConditions = array('e.product_id=ur.product_id', 'e.category_id=ur.category_id', $adapter->quoteInto('ur.store_id=?', $storeId), 'ur.is_system="1"');
$this->_select = $adapter->select()->from(array('e' => $this->getMainTable()), array('category_id'))->where('e.product_id =?', $prodId)->where('e.store_id =?', $storeId)->where('e.is_parent= "1"');
if ($catId) {
$this->_select->where('e.category_id =?', $catId);
}
$this->_select = $this->_select->join(array('ur' => $this->getTable('core/url_rewrite')), join(' AND ', $urlConditions), array('url' => 'request_path'));
// die((string)($this->_select));
$query = $adapter->query($this->_select);
return $query;
}
public function getAllUsers($resource)
{
$select = new Zend_Db_Select($this->getAdapter());
$select->from($this->info('name'), array('active', 'user_id'));
$select->where('resource_id = ?', $resource->getIdentity());
$select->where('active = ?', '1');
$users = array();
foreach ($select->query()->fetchAll() as $data) {
$users[] = $data['user_id'];
}
$users = array_values(array_unique($users));
return Engine_Api::_()->getItemMulti('user', $users);
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*
* @todo to be removed once we split filter model / backend
*/
public function appendFilterSql($_select, $_backend)
{
$ec = HumanResources_Controller_Employee::getInstance();
$filter = new HumanResources_Model_EmployeeFilter(array());
$filter->addFilter(new Tinebase_Model_Filter_Text(array('field' => 'n_fn', 'operator' => 'contains', 'value' => $this->_value)));
$employees = $ec->search($filter);
$db = Tinebase_Core::getDb();
if ($employees->count()) {
$_select->where($db->quoteInto($db->quoteIdentifier('employee_id') . ' IN (?) ', $employees->id, 'array'));
} else {
$_select->where($db->quoteInto($db->quoteIdentifier('employee_id') . ' = (?) ', 'xxxxxxxxxxxxxxx', 'array'));
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*
* @todo to be removed once we split filter model / backend
*/
public function appendFilterSql($_select, $_backend)
{
$now = new Tinebase_DateTime();
$now->setHour(0)->setMinute(0)->setSecond(0);
$db = Tinebase_Core::getDb();
if ($this->_value == 1) {
$_select->where($db->quoteInto('(' . $db->quoteIdentifier('employment_end') . ' >= ? ', $now, 'datetime') . ' OR ' . $db->quoteIdentifier('employment_end') . ' IS NULL )');
$_select->where($db->quoteInto('( ' . $db->quoteIdentifier('employment_begin') . ' <= ? ', $now, 'datetime') . ' OR ( ' . $db->quoteIdentifier('employment_begin') . ' IS NULL ))');
} else {
$_select->where($db->quoteInto($db->quoteIdentifier('employment_end') . ' < ? ', $now, 'datetime'));
$_select->orWhere($db->quoteInto($db->quoteIdentifier('employment_begin') . ' > ? ', $now, 'datetime'));
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' ' . 'Adding Path filter for: ' . $_backend->getModelName());
}
$this->_resolvePathIds();
$idField = isset($this->_options['idProperty']) || array_key_exists('idProperty', $this->_options) ? $this->_options['idProperty'] : 'id';
$db = $_backend->getAdapter();
$qField = $db->quoteIdentifier($_backend->getTableName() . '.' . $idField);
if (empty($this->_pathRecordIds)) {
$_select->where('1=0');
} else {
$_select->where($db->quoteInto("{$qField} IN (?)", $this->_pathRecordIds));
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
$db = $_backend->getAdapter();
// prepare value
$value = $this->_value ? 1 : 0;
if ($value) {
// nothing to do -> show all contacts!
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Query all account contacts.');
}
} else {
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Only query visible and enabled account contacts.');
}
if (Tinebase_Core::getUser() instanceof Tinebase_Model_FullUser) {
$where = '/* is no user */ ' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'true', 'false') . ' OR /* is user */ (' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'false', 'true') . ' AND ' . $db->quoteInto($db->quoteIdentifier('accounts.status') . ' = ?', 'enabled') . " AND " . '(' . $db->quoteInto($db->quoteIdentifier('accounts.visibility') . ' = ?', 'displayed') . ' OR ' . $db->quoteInto($db->quoteIdentifier('accounts.id') . ' = ?', Tinebase_Core::getUser()->getId()) . ')' . ")";
} else {
$where = '/* is no user */ ' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'true', 'false') . ' OR /* is user */ (' . Tinebase_Backend_Sql_Command::getIfIsNull($db, $db->quoteIdentifier('accounts.id'), 'false', 'true') . ' AND ' . $db->quoteInto($db->quoteIdentifier('accounts.status') . ' = ?', 'enabled') . " AND " . $db->quoteInto($db->quoteIdentifier('accounts.visibility') . ' = ?', 'displayed') . ")";
}
$_select->where($where);
$select = $_select instanceof Zend_Db_Select ? $_select : $_select->getSelect();
$select = Tinebase_Backend_Sql_Abstract::traitGroup($db, $_backend->getTablePrefix(), $select);
$_select instanceof Zend_Db_Select ? $_select = $select : $_select->setSelect($select);
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' contacts query ' . $_select->assemble());
}
}
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Zend_Db_Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Zend_Db_Select) {
return false;
}
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = $this->_categoryResource->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = ['entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType()];
}
$attribute = $this->_attributesCache[$attributeCode];
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(['t1_' . $attributeCode => $attribute['table']], 'e.entity_id = t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id = 0', [])->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(['t2_' . $attributeCode => $attribute['table']], $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), [])->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Adds a full-text search to the query
*
* @param string $filter Filter type to be applyed
* @param string $field Field Name
*
* @return void
*/
public function addFullTextSearch($filter, $field)
{
$full = $field['search'];
if (!isset($full['indexes'])) {
$indexes = $field['field'];
} elseif (is_array($full['indexes'])) {
$indexes = implode(',', array_values($full['indexes']));
} elseif (is_string($full['indexes'])) {
$indexes = $full['indexes'];
}
$extra = isset($full['extra']) ? $full['extra'] : 'boolean';
if (!in_array($extra, array('boolean', 'queryExpansion', false))) {
throw new Bvb_Grid_Exception('Unrecognized value in extra key');
}
if ($extra == 'boolean') {
$extra = 'IN BOOLEAN MODE';
} elseif ($extra == 'queryExpansion') {
$extra = ' WITH QUERY EXPANSION ';
} else {
$extra = '';
}
if ($extra == 'IN BOOLEAN MODE') {
$filter = preg_replace("/\\s+/", " +", $this->_getDb()->quote(' ' . $filter));
} else {
$filter = $this->_getDb()->quote($filter);
}
$this->_select->where(new Zend_Db_Expr("MATCH ({$indexes}) AGAINST ({$filter} {$extra}) "));
return;
}
/**
* Replaces calc_invoice_paid()
*
* @param int $invoice
*/
public function getPaidAmountForInvoice($invoice)
{
$select = new Zend_Db_Select($this->getAdapter());
$select->from($this->_name, array('amount' => new Zend_Db_Expr("COALESCE(SUM(ac_amount), 0)")));
$select->where('ac_inv_id=?', $invoice);
return $this->getAdapter()->fetchOne($select);
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
*
* @return Zend_Db_Select
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = Mage::getSingleton('catalog/category')->getResource()->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal(), 'backend_type' => $attribute->getBackendType());
}
$attribute = $this->_attributesCache[$attributeCode];
if (!$this->_select instanceof Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'e.entity_id=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), array())->where('IFNULL(t2_' . $attributeCode . '.value, t1_' . $attributeCode . '.value)' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return \Zend_Db_Select|bool
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!$this->_select instanceof \Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
$attribute = $this->_getAttribute($attributeCode);
if ($attribute['backend_type'] == 'static') {
$this->_select->where('e.' . $attributeCode . $conditionRule, $value);
} else {
$this->_joinAttribute($storeId, $attributeCode);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return Zend_Db_Select
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!isset($this->_attributesCache[$attributeCode])) {
$this->_loadAttribute($attributeCode);
}
$attribute = $this->_attributesCache[$attributeCode];
if (!$this->_select instanceof Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
if ($attribute['backend_type'] == 'static') {
$this->_select->where('main_table.' . $attributeCode . $conditionRule, $value);
} else {
$this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'main_table.entity_id=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->_select->getAdapter()->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id = ?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
}
}
return $this->_select;
}
/**
* appends custom filters to a given select object
* - add user phone ids to filter
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @return void
*/
public function appendFilterSql($_select, $_backend)
{
$accountIdFilter = $this->_findFilter('account_id');
if ($accountIdFilter !== NULL) {
$db = Tinebase_Core::getDb();
$backend = new Voipmanager_Backend_Snom_Phone();
$_validPhoneIds = $backend->getValidPhoneIds($accountIdFilter->getValue());
if (empty($_validPhoneIds)) {
$_select->where('1=0');
} else {
$_select->where($db->quoteInto($db->quoteIdentifier($_backend->getTableName() . '.id') . ' IN (?)', $_validPhoneIds));
}
// remove filter
$this->_removeFilter('account_id');
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_NotFound
*/
public function appendFilterSql2($_select, $_backend)
{
$this->_options['ignoreAcl'] = TRUE;
$this->_resolve();
$quotedDisplayContainerIdentifier = $_backend->getAdapter()->quoteIdentifier('attendee.displaycontainer_id');
$_select->where($quotedDisplayContainerIdentifier . ' IN (?)', empty($this->_containerIds) ? " " : $this->_containerIds);
}
/**
* Saving information about customer
*
* @param Mage_Log_Model_Visitor $visitor
*
* @return Mage_Log_Model_Resource_Visitor
*/
protected function _saveCustomerInfo($visitor)
{
$adapter = $this->_getWriteAdapter();
if ($visitor->getDoCustomerLogout() && ($logId = $visitor->getCustomerLogId())) {
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Zend_Db_Select($connection);
$select->from($resource->getTableName('log/customer'));
$select->reset(Zend_Db_Select::COLUMNS);
$select->columns('login_at');
$select->where('log_id = ?', $logId);
$loginAt = $connection->fetchOne($select);
if (!$loginAt) {
return parent::_saveCustomerInfo($visitor);
}
$data = new Varien_Object(array('login_at' => $loginAt, 'logout_at' => Mage::getSingleton('core/date')->gmtDate(), 'store_id' => (int) Mage::app()->getStore()->getId()));
$bind = $this->_prepareDataForTable($data, $this->getTable('log/customer'));
$condition = array('log_id = ?' => (int) $logId);
$adapter->update($this->getTable('log/customer'), $bind, $condition);
$visitor->setDoCustomerLogout(false);
$visitor->setCustomerId(null);
$visitor->setCustomerLogId(null);
} else {
return parent::_saveCustomerInfo($visitor);
}
return $this;
}
/**
* Add attribute to filter
*
* @param int $storeId
* @param string $attributeCode
* @param mixed $value
* @param string $type
* @return Zend_Db_Select
*/
protected function _addFilter($storeId, $attributeCode, $value, $type = '=')
{
if (!isset($this->_attributesCache[$attributeCode])) {
$attribute = Mage::getSingleton('catalog/product')->getResource()->getAttribute($attributeCode);
$this->_attributesCache[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal() == Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL, 'backend_type' => $attribute->getBackendType());
}
$attribute = $this->_attributesCache[$attributeCode];
if (!$this->_select instanceof Zend_Db_Select) {
return false;
}
switch ($type) {
case '=':
$conditionRule = '=?';
break;
case 'in':
$conditionRule = ' IN(?)';
break;
default:
return false;
break;
}
$this->_select->join(array('t1_' . $attributeCode => $attribute['table']), 'r.entity_pk_value=t1_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.store_id=0', array())->where('t1_' . $attributeCode . '.attribute_id=?', $attribute['attribute_id']);
if ($attribute['is_global']) {
$this->_select->where('t1_' . $attributeCode . '.value' . $conditionRule, $value);
} else {
$ifCase = $this->getCheckSql('t2_' . $attributeCode . '.value_id > 0', 't2_' . $attributeCode . '.value', 't1_' . $attributeCode . '.value');
$this->_select->joinLeft(array('t2_' . $attributeCode => $attribute['table']), $this->_getWriteAdapter()->quoteInto('t1_' . $attributeCode . '.entity_id = t2_' . $attributeCode . '.entity_id AND t1_' . $attributeCode . '.attribute_id = t2_' . $attributeCode . '.attribute_id AND t2_' . $attributeCode . '.store_id=?', $storeId), array())->where('(' . $ifCase . ')' . $conditionRule, $value);
}
return $this->_select;
}
