function onInstall()
{
// member homepage
$db = $this->getDb();
$select = new Zend_Db_Select($db);
$select->from('engine4_core_pages')->where('name = ?', 'user_index_home')->limit(1);
$page_id = $select->query()->fetchObject()->page_id;
// Check if it's already been placed
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'widget')->where('name = ?', 'social-connect.call-popup-invite');
$info = $select->query()->fetch();
if (empty($info)) {
// container_id (will always be there)
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'container')->limit(1);
$container_id = $select->query()->fetchObject()->content_id;
// middle_id (will always be there)
$select = new Zend_Db_Select($db);
$select->from('engine4_core_content')->where('parent_content_id = ?', $container_id)->where('type = ?', 'container')->where('name = ?', 'middle')->limit(1);
$middle_id = $select->query()->fetchObject()->content_id;
// tab on profile
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'social-connect.call-popup-invite', 'parent_content_id' => $middle_id, 'params' => '', 'order' => 999));
}
parent::onInstall();
}
/**
* Lists all tests which are available in system
* @return dataset
*/
public function getAll()
{
$obj = new Zend_Db_Select(Zend_Db_Table::getDefaultAdapter());
$select = $obj->from('tests as t', array('id', 'name'))->order('t.name');
$result = $select->query()->fetchAll();
return $result;
}
public function indexAction()
{
// Users
$userTable = Engine_Api::_()->getItemTable('user');
$select = new Zend_Db_Select($userTable->getAdapter());
$select->from($userTable->info('name'), 'COUNT(user_id) as count');
$data = $select->query()->fetch();
$this->view->userCount = (int) $data['count'];
// Reports
$reportTable = Engine_Api::_()->getDbtable('reports', 'core');
$select = new Zend_Db_Select($reportTable->getAdapter());
$select->from($reportTable->info('name'), 'COUNT(report_id) as count')->where('`read` = ?', 0);
$data = $select->query()->fetch();
$this->view->reportCount = (int) $data['count'];
// Plugins
$moduleTable = Engine_Api::_()->getDbtable('modules', 'core');
$select = new Zend_Db_Select($moduleTable->getAdapter());
$select->from($moduleTable->info('name'), 'COUNT(TRUE) as count')->where('type = ?', 'extra');
$data = $select->query()->fetch();
$this->view->pluginCount = (int) $data['count'];
// Notifications
// Hook-based
$event = Engine_Hooks_Dispatcher::_()->callEvent('getAdminNotifications');
$this->view->notifications = $event->getResponses();
// Database-based
$select = Engine_Api::_()->getDbtable('log', 'core')->select()->where('domain = ?', 'admin')->order('timestamp DESC');
$this->view->paginator = $paginator = Zend_Paginator::factory($select);
$paginator->setItemCountPerPage(4);
}
public function init()
{
// Form
$this->setMethod('GET')->setAction(Zend_Controller_Front::getInstance()->getRouter()->assemble(array()))->addAttribs(array('id' => 'filter_form', 'class' => 'global_form_box'));
$this->clearDecorators()->addDecorator('FormElements')->addDecorator('Form')->addDecorator('HtmlTag', array('tag' => 'div', 'class' => 'search'))->addDecorator('HtmlTag2', array('tag' => 'div', 'class' => 'clear'));
// Element: moduleName
$jobTypesTable = Engine_Api::_()->getDbtable('jobTypes', 'core');
$modulesTable = Engine_Api::_()->getDbtable('modules', 'core');
$select = new Zend_Db_Select($jobTypesTable->getAdapter());
$modules = $select->distinct()->from($jobTypesTable->info('name'), 'module')->joinLeft($modulesTable->info('name'), 'module=name', array('title'))->where($modulesTable->info('name') . '.enabled = ?', 1)->order('title')->query()->fetchAll();
$multiOptions = array('' => '');
foreach ($modules as $module) {
if (!empty($module['title'])) {
$multiOptions[$module['module']] = $module['title'];
}
}
$this->addElement('Select', 'moduleName', array('label' => 'Module', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
// Element: jobType
$jobTypes = $jobTypesTable->select()->from($jobTypesTable, array('jobtype_id', 'title', 'type'))->order('title')->query()->fetchAll();
$multiOptions = array('' => '');
foreach ($jobTypes as $jobType) {
$multiOptions[$jobType['jobtype_id']] = $jobType['title'];
}
$this->addElement('Select', 'jobtype_id', array('label' => 'Type', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
// Element: state
$this->addElement('Select', 'state', array('label' => 'State', 'multiOptions' => array('' => '', 'pending' => 'Pending', 'active' => 'Active', 'sleeping' => 'Sleeping', 'failed' => 'Failed', 'cancelled' => 'Cancelled', 'completed' => 'Completed', 'timeout' => 'Timed Out'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
// Element: order
$this->addElement('Select', 'order', array('label' => 'Order', 'multiOptions' => array('job_id' => 'ID', 'jobtype_id' => 'Type', 'state' => 'State', 'progress' => 'Progress', 'creation_date' => 'Queued Date', 'started_date' => 'Started Date', 'completion_date' => 'Completed Date', 'priority' => 'Priority'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
// Element: direction
$this->addElement('Select', 'direction', array('label' => 'Direction', 'multiOptions' => array('ASC' => 'A-Z', 'DESC' => 'Z-A'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
// Element: submit
$this->addElement('Button', 'execute', array('label' => 'Filter', 'ignore' => true, 'type' => 'submit', 'decorators' => array('ViewHelper', array('HtmlTag', array('tag' => 'div', 'class' => 'buttons')), array('HtmlTag2', array('tag' => 'div')))));
}
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();
}
/**
* 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);
}
/**
* 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);
}
}
}
/**
* A select used by subclasses to add fields to the select.
*
* @param string $login_name
* @param int $organization
* @return \Zend_Db_Select
*/
protected function getUserSelect($login_name, $organization)
{
// 'user_group' => 'gsf_id_primary_group', 'user_logout' => 'gsf_logout_on_survey',
$select = new \Zend_Db_Select($this->db);
$select->from('gems__user_logins', array('user_login_id' => 'gul_id_user', 'user_active' => 'gul_can_login'))->join('gems__respondent2org', 'gul_login = gr2o_patient_nr AND gul_id_organization = gr2o_id_organization', array('user_login' => 'gr2o_patient_nr', 'user_base_org_id' => 'gr2o_id_organization'))->join('gems__respondents', 'gr2o_id_user = grs_id_user', array('user_id' => 'grs_id_user', 'user_email' => 'grs_email', 'user_first_name' => 'grs_first_name', 'user_surname_prefix' => 'grs_surname_prefix', 'user_last_name' => 'grs_last_name', 'user_gender' => 'grs_gender', 'user_locale' => 'grs_iso_lang', 'user_birthday' => 'grs_birthday'))->join('gems__organizations', 'gr2o_id_organization = gor_id_organization', array('user_group' => 'gor_respondent_group'))->join('gems__groups', 'gor_respondent_group = ggp_id_group', array('user_role' => 'ggp_role', 'user_allowed_ip_ranges' => 'ggp_allowed_ip_ranges'))->joinLeft('gems__user_passwords', 'gul_id_user = gup_id_user', array('user_password_reset' => 'gup_reset_required', 'user_resetkey_valid' => new \Zend_Db_Expr('CASE WHEN DATE_ADD(gup_reset_requested, INTERVAL ' . $this->hoursResetKeyIsValid . ' HOUR) >= CURRENT_TIMESTAMP THEN 1 ELSE 0 END'), 'user_password_last_changed' => 'gup_last_pwd_change'))->joinLeft('gems__reception_codes', 'gr2o_reception_code = grc_id_reception_code', array())->where('ggp_group_active = 1')->where('grc_success = 1')->where('gul_can_login = 1')->where('gul_login = ?')->where('gul_id_organization = ?')->limit(1);
return $select;
}
/**
* 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 . 'adb_list_m_role'), $db->quoteIdentifier($correlationName . '.contact_id') . ' = ' . $db->quoteIdentifier('addressbook.id'), array());
$_select->where($db->quoteIdentifier($correlationName . '.list_role_id') . ' IN (?)', (array) $this->_value);
}
/**
* 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);
}
/**
* 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());
}
}
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
* @throws Tinebase_Exception_UnexpectedValue
*/
public function appendFilterSql($_select, $_backend)
{
// don't take empty filter into account
if (empty($this->_value) || !is_array($this->_value) || !isset($this->_value['cfId']) || empty($this->_value['cfId']) || !isset($this->_value['value'])) {
return;
} else {
if ($this->_operator == 'in') {
throw new Tinebase_Exception_UnexpectedValue('Operator "in" not supported.');
}
}
// make sure $correlationName is a string
$correlationName = Tinebase_Record_Abstract::generateUID() . $this->_value['cfId'] . 'cf';
if (Tinebase_Core::isLogLevel(Zend_Log::DEBUG)) {
Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Adding custom field filter: ' . print_r($this->_value, true));
}
$db = Tinebase_Core::getDb();
$idProperty = $db->quoteIdentifier($this->_options['idProperty']);
// per left join we add a customfield column named as the customfield and filter this joined column
// NOTE: we name the column we join like the customfield, to be able to join multiple customfield criteria (multiple invocations of this function)
$what = array($correlationName => SQL_TABLE_PREFIX . 'customfield');
$on = $db->quoteIdentifier("{$correlationName}.record_id") . " = {$idProperty} AND " . $db->quoteIdentifier("{$correlationName}.customfield_id") . " = " . $db->quote($this->_value['cfId']);
$_select->joinLeft($what, $on, array());
$valueIdentifier = $db->quoteIdentifier("{$correlationName}.value");
if ($this->_value['value'] === '') {
$where = $db->quoteInto($valueIdentifier . ' IS NULL OR ' . $valueIdentifier . ' = ?', $this->_value['value']);
} else {
$value = $this->_replaceWildcards($this->_value['value']);
$where = $db->quoteInto($valueIdentifier . $this->_opSqlMap[$this->_operator]['sqlop'], $value);
}
$_select->where($where . ' /* add cf filter */');
}
protected function _addContentEventProfile()
{
$db = $this->getDb();
$select = new Zend_Db_Select($db);
// Check if it's already been placed
$select = new Zend_Db_Select($db);
$hasWidget = $select->from('engine4_core_pages', new Zend_Db_Expr('TRUE'))->where('name = ?', 'event_profile_index')->limit(1)->query()->fetchColumn();
// Add it
if (empty($hasWidget)) {
$db->insert('engine4_core_pages', array('name' => 'event_profile_index', 'displayname' => 'Event Profile', 'title' => 'Event Profile', 'description' => 'This is the profile for an event.', 'custom' => 0));
$page_id = $db->lastInsertId('engine4_core_pages');
// containers
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'main', 'parent_content_id' => null, 'order' => 1, 'params' => ''));
$container_id = $db->lastInsertId('engine4_core_content');
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'middle', 'parent_content_id' => $container_id, 'order' => 3, 'params' => ''));
$middle_id = $db->lastInsertId('engine4_core_content');
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'container', 'name' => 'left', 'parent_content_id' => $container_id, 'order' => 1, 'params' => ''));
$left_id = $db->lastInsertId('engine4_core_content');
// middle column
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'core.container-tabs', 'parent_content_id' => $middle_id, 'order' => 2, 'params' => '{"max":"6"}'));
$tab_id = $db->lastInsertId('engine4_core_content');
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-status', 'parent_content_id' => $middle_id, 'order' => 1, 'params' => ''));
// left column
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-photo', 'parent_content_id' => $left_id, 'order' => 1, 'params' => ''));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-options', 'parent_content_id' => $left_id, 'order' => 2, 'params' => ''));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-info', 'parent_content_id' => $left_id, 'order' => 3, 'params' => ''));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-rsvp', 'parent_content_id' => $left_id, 'order' => 4, 'params' => ''));
// tabs
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'activity.feed', 'parent_content_id' => $tab_id, 'order' => 1, 'params' => '{"title":"Updates"}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-members', 'parent_content_id' => $tab_id, 'order' => 2, 'params' => '{"title":"Guests","titleCount":true}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-photos', 'parent_content_id' => $tab_id, 'order' => 3, 'params' => '{"title":"Photos","titleCount":true}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'event.profile-discussions', 'parent_content_id' => $tab_id, 'order' => 4, 'params' => '{"title":"Discussions","titleCount":true}'));
$db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'core.profile-links', 'parent_content_id' => $tab_id, 'order' => 5, 'params' => '{"title":"Links","titleCount":true}'));
}
}
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'];
}
public function joinComments()
{
$select = new Zend_Db_Select($connection = Mage::getSingleton('core/resource')->getConnection('read'));
$select->from(Mage::getSingleton('core/resource')->getTableName('mdlblog/comment'), array('post_id', 'comment_count' => new Zend_Db_Expr('COUNT(IF(status = 2, post_id, NULL))')))->group('post_id');
$this->getSelect()->joinLeft(array('comments_select' => $select), 'main_table.post_id = comments_select.post_id', 'comment_count');
return $this;
}
protected function _addHierarchy()
{
$requestPath = $this->_getWriteAdapter()->getIfNullSql('h.request_url', 'main_table.identifier');
// Add Hierarchy
$this->_select->join(array('h' => $this->getTable('enterprise_cms/hierarchy_node')), 'main_table.page_id=h.page_id', array($requestPath . ' as url'));
return $this;
}
public function onStatistics($event)
{
$table = Engine_Api::_()->getDbTable('photos', 'album');
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), 'COUNT(*) AS count');
$event->addResponse($select->query()->fetchColumn(0), 'photo');
}
public static function _setSelectRules(Zend_Db_Select $select)
{
$select->where('accountStatus != ?', self::STATUS_DELETED);
if (!is_null(self::getMyType())) {
$select->where('type = ?', static::getMyType());
}
}
/**
* This returns the next chunk of database result objects.
*
* @return array|null Set of Omeka_Record instances, or null if none can be
* found.
* @author Eric Rochester <*****@*****.**>
**/
public function next()
{
$this->pageNumber++;
$this->select->limitPage($this->pageNumber, $this->rowCount);
$rows = $this->table->fetchObjects($this->select, $this->params);
return $rows;
}
/**
* 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);
}
/**
* 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);
}
}
}
public function onStatistics($event)
{
$table = Engine_Api::_()->getItemTable('ynfeedback_idea');
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), 'COUNT(*) AS count');
$event->addResponse($select->query()->fetchColumn(0), 'feedback');
}
/**
* 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)
{
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);
}
}
/**
* Filter the select to append only product visible into the catalog or search into the index.
*
* Note : Magento put only enabled products that have the following
* visibility into the catalog_category_product_index table :
* - visible in catalog
* - visible in search
* - visible in catalog & search
*
* So joining on the root category will filter only products that have to be indexed
* and you don't need to put any additional filter on the visibility field.
*
* @param \Zend_Db_Select $select Product select to be filtered.
* @param integer $storeId Store Id
*
* @return \Smile\ElasticsuiteCatalog\Model\ResourceModel\Product\Indexer\Fulltext\Action\Full Self Reference
*/
private function addIsVisibleInStoreFilter($select, $storeId)
{
$rootCategoryId = $this->getRootCategoryId($storeId);
$indexTable = $this->getTable('catalog_category_product_index');
$visibilityJoinCond = $this->getConnection()->quoteInto('visibility.product_id = e.entity_id AND visibility.store_id = ?', $storeId);
$select->useStraightJoin(true)->join(['visibility' => $indexTable], $visibilityJoinCond, ['visibility'])->where('visibility.category_id = ?', (int) $rootCategoryId);
return $this;
}
/**
* appends sql to given select statement
*
* @param Zend_Db_Select $_select
* @param Tinebase_Backend_Sql_Abstract $_backend
*/
public function appendFilterSql($_select, $_backend)
{
$db = Tinebase_Core::getDb();
if (!is_array($this->_foreignIds)) {
$this->_foreignIds = $this->_getController()->search($this->_filterGroup, new Tinebase_Model_Pagination(), FALSE, TRUE);
}
$_select->where($this->_getQuotedFieldName($_backend) . ' IN (?)', empty($this->_foreignIds) ? new Zend_Db_Expr('NULL') : $this->_foreignIds);
}
function getVesion()
{
$select = new Zend_Db_Select($this->db);
$select->from('Version', 'VersionId');
$select->limit(1);
$res = $this->db->fetchOne($select);
return $res;
}
public function getPostIndex()
{
$table = $this->getTable();
$select = new Zend_Db_Select($table->getAdapter());
$select->from($table->info('name'), new Zend_Db_Expr('COUNT(post_id) as count'))->where('topic_id = ?', $this->topic_id)->where('post_id < ?', $this->getIdentity())->order('post_id ASC');
$data = $select->query()->fetch();
return (int) $data['count'];
}
/**
* @see library/System/Controller/Action/Helper/GridFilter/System_Controller_Action_Helper_GridFilter_Abstract#filter($select)
*/
public function filter(Zend_Db_Select $select)
{
if ($this->_getComparison() == '=') {
$select->where('DATE(' . $this->_field . ') ' . $this->_getComparison() . ' DATE(?) ', $this->_getValue());
} else {
parent::filter($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/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;
}
