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