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