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 _searchWhere(Surfnet_Search_Parameters $params, $where)
{
$dao = new Portal_Model_DbTable_GadgetDefinition();
$query = $dao->select()->from($dao)->where($where);
if ($params->getLimit()) {
$query->limit($params->getLimit(), $params->getOffset());
}
if ($params->getSortByField()) {
$query->order('gadgetdefinition.' . $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);
}
/**
*
*
* SELECT COUNT(id) AS num, status
* FROM portal.invite
* GROUP BY status
*
* @param Surfnet_Search_Parameters $params
* @return Surfnet_Search_Results
*/
public function searchCountByStatus(Surfnet_Search_Parameters $params)
{
$dao = new Portal_Model_DbTable_Invite();
$select = $dao->select()->from($dao)->group('status');
$select = $select->columns(array("num" => "COUNT(id)", "status" => "status"));
if ($params->getLimit()) {
$select->limit($params->getLimit(), $params->getOffset());
}
if ($params->getSortByField() != '') {
$select->order($params->getSortByField() . ' ' . $params->getSortDirection());
}
$results = $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, $results, $totalCount);
}
public function searchUsage(Surfnet_Search_Parameters $params)
{
if (!$params->getSortByField()) {
$params->setSortByField('num');
$params->setSortDirection('desc');
}
$dao = new Portal_Model_DbTable_Gadget();
$query = $dao->select()->setIntegrityCheck(false)->from($dao, array('definition'))->join(array('gd' => 'gadgetdefinition'), 'gadget.definition = gd.id', array('num' => 'count(gd.id)', 'title' => 'gd.title', 'author' => 'gd.author'))->join(array('t' => 'tab'), 'gadget.tab_id = t.id')->group('gadget.definition');
if ($params->getLimit()) {
$query->limit($params->getLimit(), $params->getOffset());
}
$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);
}
protected function _searchWhere(Surfnet_Search_Parameters $params, $where = '')
{
// select VO record(s)
$dao = new EngineBlock_Model_DbTable_VirtualOrganisationGroup();
$query = $dao->select()->from($dao);
if (strlen(trim($where)) > 0) {
$query->where($where);
}
if ($params->getLimit()) {
$query->limit($params->getLimit(), $params->getOffset());
}
if ($params->getSortByField()) {
$query->order('virtual_organisation_group.' . $params->getSortByField() . ' ' . $params->getSortDirection());
}
$groupRecords = $dao->fetchAll($query);
$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, $groupRecords, $totalCount);
}
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);
}
/**
* 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 _searchWhere(Surfnet_Search_Parameters $params, $where = '')
{
// Select Group Provider Records
$dao = new EngineBlock_Model_DbTable_GroupProvider();
$query = $dao->select()->from($dao);
if (strlen(trim($where)) > 0) {
$query->where($where);
}
if ($params->getLimit()) {
$query->limit($params->getLimit(), $params->getOffset());
}
if ($params->getSortByField()) {
$query->order('group_provider.' . $params->getSortByField() . ' ' . $params->getSortDirection());
}
$gpRecords = $dao->fetchAll($query);
$gpRecords = $gpRecords->toArray();
// get full classnames
foreach ($gpRecords as &$record) {
$record['fullClassname'] = $record['classname'];
$record['classname'] = EngineBlock_Model_GroupProvider::getClassnameDisplayValue($record['classname']);
}
$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, $gpRecords, $totalCount);
}
protected function _searchWhere(Surfnet_Search_Parameters $params, $where = '')
{
// select VO record(s)
$dao = new EngineBlock_Model_DbTable_VirtualOrganisation();
$query = $dao->select()->from($dao);
if (strlen(trim($where)) > 0) {
$query->where($where);
}
if ($params->getLimit()) {
$query->limit($params->getLimit(), $params->getOffset());
}
if ($params->getSortByField()) {
$query->order('virtual_organisation.' . $params->getSortByField() . ' ' . $params->getSortDirection());
}
$voRecords = $dao->fetchAll($query);
// get corresponding groups
$groupRecords = array();
foreach ($voRecords as $row) {
/* @var $row Zend_Db_Table_Row */
$groupRecords[$row->vo_id] = $row->findDependentRowset('EngineBlock_Model_DbTable_VirtualOrganisationGroup')->toArray();
// remove FK's
foreach ($groupRecords[$row['vo_id']] as &$groupRow) {
unset($groupRow['vo_id']);
}
}
unset($row);
// get corresponding idps
$idpRecords = array();
foreach ($voRecords as $row) {
/* @var $row Zend_Db_Table_Row */
$idpRecords[$row->vo_id] = $row->findDependentRowset('EngineBlock_Model_DbTable_VirtualOrganisationIdp')->toArray();
// remove FK's
foreach ($idpRecords[$row['vo_id']] as &$idpRow) {
unset($idpRow['vo_id']);
}
}
unset($row);
// get corresponding stem
$stemRecords = array();
foreach ($voRecords as $row) {
if ($row->vo_type === 'STEM') {
/* @var $config Zend_Config */
$config = Zend_Registry::get('config');
$stemPrefix = $config->engineBlock->vo->stemPrefix;
$stemRecords[$row['vo_id']] = $stemPrefix . $row->vo_id;
}
}
unset($row);
// merge groups into VOs
$voRecords = $voRecords->toArray();
foreach ($voRecords as &$row) {
$row['groups'] = $groupRecords[$row['vo_id']];
}
unset($row);
// merge idps into VOs
foreach ($voRecords as &$row) {
$row['idps'] = $idpRecords[$row['vo_id']];
}
unset($row);
// merge stems into VOs
foreach ($voRecords as &$row) {
if ($row['vo_type'] === 'STEM') {
$row['stem'] = $stemRecords[$row['vo_id']];
}
}
unset($row);
$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, $voRecords, $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);
}