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