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);
}
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);
}
public function searchCountByCapabililty(Surfnet_Search_Parameters $params)
{
if (!$params->getSortByField()) {
$params->setSortByField('num');
$params->setSortDirection('desc');
}
$dao = new Portal_Model_DbTable_GadgetDefinition();
$selectTotal = $dao->select();
$selectTotal->from($dao, array("num" => "COUNT(id)", 'type' => new Zend_Db_Expr("'Total'")));
$selectGroupEnabled = $dao->select();
$selectGroupEnabled->from($dao, array("num" => "COUNT(id)", 'type' => new Zend_Db_Expr("'Group enabled'")))->where("UPPER(supports_groups) = 'T'");
$selectSsoEnabled = $dao->select();
$selectSsoEnabled->from($dao, array('num' => "COUNT(id)", 'type' => new Zend_Db_Expr("'SSO Enabled'")))->where("UPPER(supportssso) = 'T'");
$selectSsoGroupEnabled = $dao->select();
$selectSsoGroupEnabled->from($dao, array("num" => "COUNT(id)", 'type' => new Zend_Db_Expr("'SSO and Group Enabled'")))->where("UPPER(supportssso) = 'T' AND upper(supports_groups) = 'T'");
$select = $dao->select()->union(array($selectTotal, $selectSsoGroupEnabled, $selectSsoEnabled, $selectGroupEnabled));
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, 4);
}
