public function searchTeams(Surfnet_Search_Parameters $params) { if (!$params->getSortByField()) { $params->setSortByField('num'); $params->setSortDirection('desc'); } $dao = new Portal_Model_DbTable_Tab(); /** * $qry = "SELECT COUNT(id) AS num, 'Total Tabs' as type FROM `coin_portal`.`tab` * UNION * SELECT COUNT(id) AS num, 'Shared Team Tabs' as type FROM `coin_portal`.`tab` where team IS NOT NULL * UNION * SELECT COUNT(id) AS num, 'Not Shared' as type FROM `coin_portal`.`tab` where team IS NULL"; */ $selectTotal = $dao->select()->from($dao, array("num" => "COUNT(id)", 'type' => new Zend_Db_Expr("'Total'"))); $selectShared = $dao->select()->from($dao, array("num" => "COUNT(id)", 'type' => new Zend_Db_Expr("'Shared'")))->where('team IS NOT NULL'); $selectNotShared = $dao->select(); $selectNotShared->from($dao, array("num" => "COUNT(id)", 'type' => new Zend_Db_Expr("'Not shared'")))->where('team IS NULL'); $searchParams = $params->getSearchParams(); if ($params->searchByDate()) { $dateWhere = $this->_getTeamTabsDateWhere($searchParams['year'], $searchParams['month']); $selectTotal->where($dateWhere); $selectShared->where($dateWhere); $selectNotShared->where($dateWhere); } $select = $dao->select()->union(array($selectTotal, $selectShared, $selectNotShared)); if ($params->getLimit()) { $select->limit($params->getLimit(), $params->getOffset()); } $select->order($params->getSortByField() . ' ' . $params->getSortDirection()); $rows = $dao->fetchAll($select)->toArray(); return new Surfnet_Search_Results($params, $rows, 3); }
protected function _searchType($type, Surfnet_Search_Parameters $params) { $searchParams = $params->getSearchParams(); $dao = new ServiceRegistry_Model_DbTable_JanusEntity(); $rev_fields = array('eid' => 'eid', 'maxrev' => 'max(revisionid)'); $rev_select = $dao->select()->from(array('janus__entity'), $rev_fields)->group('eid'); $fields = array('entityid' => 'entityid', 'state' => 'state', 'metadataurl' => 'metadataurl', 'created' => 'created', 'user' => 'user', 'display_name' => "IFNULL(\n (SELECT `value` FROM `janus__metadata` `jm` WHERE `key`='name:en' AND jm.eid = ent.eid AND jm.revisionid = maxrev),\n ent.entityid\n )"); $entityType = $type === 'saml20-sp' ? 'sp' : 'idp'; $entityType = 'idp'; if ($type === 'saml20-sp') { $entityType = 'sp'; } $select = $dao->select()->setIntegrityCheck(false)->from(array('ent' => 'janus__entity'))->columns($fields)->joinInner(array('entgrp' => $rev_select), 'ent.eid = entgrp.eid and ent.revisionid = entgrp.maxrev')->join(array('ju' => 'janus__user'), '(ent.user=ju.uid)', array('userid' => 'userid'))->where('ent.type= ?', $type); if ($params->searchByDate()) { $select->where($this->_getCountTypesWhereForDate('ent', $searchParams['year'], $searchParams['month'])); } if ($params->getLimit()) { $select->limit($params->getLimit(), $params->getOffset()); } if ($params->getSortByField() != '') { $select->order($params->getSortByField() . ' ' . $params->getSortDirection()); } $rows = $dao->fetchAll($select)->toArray(); $totalCount = $dao->fetchRow($select->reset(Zend_Db_Select::LIMIT_COUNT)->reset(Zend_Db_Select::LIMIT_OFFSET)->columns(array('count' => 'COUNT(*)')))->offsetGet('count'); return new Surfnet_Search_Results($params, $rows, $totalCount); }
protected function _searchWhere(Surfnet_Search_Parameters $params) { $searchParams = $params->getSearchParams(); if (!isset($searchParams['vo_id'])) { throw new EngineBlock_Exception("Invalid VO id!"); } // select VO Attribute record(s) $statement = $this->dbConnection->prepare("SELECT voa.* FROM virtual_organisation_attribute voa WHERE voa.vo_id = ?"); $statement->execute(array($searchParams['vo_id'])); $rows = $statement->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as &$row) { $row = $this->jsonDecodeColumn($row, 'attribute_value'); } return new Surfnet_Search_Results($params, $rows, count($rows)); }
public function search(Surfnet_Search_Parameters $params) { $dao = new EngineBlock_Model_DbTable_EmailConfiguration(); $query = $dao->select()->from($dao)->columns(); $searchParams = $params->getSearchParams(); foreach ($searchParams as $key => $value) { if (!$value) { continue; } $query->where($key . ' LIKE ' . $dao->getAdapter()->quote('%' . $value . '%')); } if ($params->getLimit()) { $query->limit($params->getLimit(), $params->getOffset()); } if ($params->getSortByField()) { $query->order($params->getSortByField() . ' ' . $params->getSortDirection()); } $results = $dao->fetchAll($query)->toArray(); $totalCount = $dao->fetchRow($query->reset(Zend_Db_Select::LIMIT_COUNT)->reset(Zend_Db_Select::LIMIT_OFFSET)->columns(array('count' => 'COUNT(*)')))->offsetGet('count'); return new Surfnet_Search_Results($params, $results, $totalCount); }
/** * Search for gadgets with a custom gadgetdefinition. * * @param Surfnet_Search_Parameters $params * @return Surfnet_Search_Result */ public function search(Surfnet_Search_Parameters $params) { $dao = new Portal_Model_DbTable_Gadget(); $query = $dao->select()->setIntegrityCheck(false)->from($dao, array('id' => 'gadget.id'))->join('gadgetdefinition', 'gadget.definition=gadgetdefinition.id')->join('tab', 'gadget.tab_id=tab.id')->where('object_type <> "ClonedTab"')->columns(); $searchParams = $params->getSearchParams(); foreach ($searchParams as $key => $value) { if (!$value) { continue; } $query->where($key . ' LIKE ' . $dao->getAdapter()->quote('%' . $value . '%')); } if ($params->getLimit()) { $query->limit($params->getLimit(), $params->getOffset()); } if ($params->getSortByField()) { $query->order($params->getSortByField() . ' ' . $params->getSortDirection()); } $results = $dao->fetchAll($query)->toArray(); $totalCount = $dao->fetchRow($query->reset(Zend_Db_Select::LIMIT_COUNT)->reset(Zend_Db_Select::LIMIT_OFFSET)->columns(array('count' => 'COUNT(*)')))->offsetGet('count'); return new Surfnet_Search_Results($params, $results, $totalCount); }
/** * Get logins grouped by type (IDP or SP) * * @param String $groupByField * @param Surfnet_Search_Parameters $params * @return Surfnet_Search_Results */ protected function _searchCountGrouped($groupByField, Surfnet_Search_Parameters $params) { $dao = new EngineBlock_Model_DbTable_LogLogin(); $select = $dao->select()->from($dao, array('num' => 'COUNT(*)', 'grouped' => $groupByField))->group($groupByField); if ($params->getLimit()) { $select->limit($params->getLimit(), $params->getOffset()); } if ($params->getSortByField() != '') { $select->order($params->getSortByField() . ' ' . $params->getSortDirection()); } $searchParams = $params->getSearchParams(); if ($params->searchByDate()) { $select->where($this->_getLoginDateWhere($searchParams['year'], $searchParams['month'])); } if (isset($searchParams['entity_field']) && !empty($searchParams['entity_field'])) { $select->where($this->_getEntityWhere($searchParams['entity_field'], $searchParams['entity_id'])); } $rows = $dao->fetchAll($select)->toArray(); $select->reset(Zend_Db_Select::LIMIT_COUNT)->reset(Zend_Db_Select::LIMIT_OFFSET); $countSelect = $dao->getAdapter()->select()->from($select)->columns(array('count' => 'COUNT(*)')); $totalCount = $countSelect->query()->fetchObject()->count; return new Surfnet_Search_Results($params, $rows, $totalCount); }