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