public function testCallingBufferAfterIterationThrowsException()
{
$this->resultSet->initialize($this->getMock('Zend\\Db\\Adapter\\Driver\\ResultInterface'));
$this->resultSet->current();
$this->setExpectedException('Zend\\Db\\ResultSet\\Exception\\RuntimeException', 'Buffering must be enabled before iteration is started');
$this->resultSet->buffer();
}
protected function query(\Zend\Db\Sql\Select $obj)
{
return $this->generateCode(function () use($obj) {
$adapter = $this->getTableGateway()->adapter;
$sql = new Sql($adapter);
$statement = $sql->prepareStatementForSqlObject($obj);
$results = $statement->execute();
$resultSet = new ResultSet();
$resultSet->initialize($results);
$resultSet->buffer();
return $resultSet;
});
}
public function getUserRoleData($userId)
{
$userId = (int) $userId;
$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from('user_role');
$where = new Where();
$where->equalTo('user_id', $userId);
$select->where($where);
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
$resultSet = new ResultSet();
$resultSet->initialize($results);
$resultSet->buffer();
$rolesArray = array();
foreach ($resultSet as $menu) {
if (!in_array($menu["role_id"], $rolesArray)) {
array_push($rolesArray, $menu["role_id"]);
}
}
return $rolesArray;
}
public function top5Values($request, $limit, $date_from, $date_to)
{
if ($request == 'Media Values') {
$select = "SUM(`news_header`.`media_value`)";
} elseif ($request == 'Hits') {
$select = "COUNT(`news_header_id`)";
}
$sql = " SELECT\n `company_id` as companies_id, `company_name`, {$select} AS ctr\n FROM\n `news_header`\n LEFT JOIN\n `companies`\n ON\n `companies`.`companies_id` = `news_header`.`company_id`\n WHERE\n `article_date` BETWEEN '{$date_from}' AND '{$date_to}' AND `news_header`.`status`!=2 AND companies_id!= -1 AND `companies`.`status`!=0\n GROUP BY\n `company_name`\n ORDER BY\n `ctr` DESC ";
$sql = " SELECT\n companies_id, `company_name`\n FROM\n `companies`\n WHERE\n companies_id!= -1 AND `companies`.`status`!=0\n GROUP BY\n `company_name`\n ORDER BY\n `company_name` ASC ";
$dbAdapter = $this->getAdapter();
$statement = $dbAdapter->query($sql);
$resultFetch = $statement->execute();
$resultSet = new ResultSet();
// Zend\Db\ResultSet
$resultSet->initialize($resultFetch);
$resultSet->buffer();
return $results = iterator_to_array($resultSet);
}
public function getMediaProfiles(AdapterInterface $adapter, $media_profile_ids)
{
$this->adapter = $adapter;
$dbAdapter = $this->adapter;
$statement = $dbAdapter->query("SELECT CONCAT(first_name,' ',middle_initial,' ',last_name) AS full_name, `media_profiles`.`media_profiles_id` as media_pid\n FROM\n `media_profiles`\n WHERE\n `media_profiles_id` IN (" . $media_profile_ids . ")\n ORDER BY\n first_name ASC");
$resultFetch = $statement->execute();
$resultSet = new ResultSet();
// Zend\Db\ResultSet
$resultSet->initialize($resultFetch);
$resultSet->buffer();
return $resultSet;
}
/**
* This method allows the results to be iterable multiple times
* for database drivers that does not support rewind() method.
* PDO_Mysql for example does not provide backward scrolling resultset,
* They are forward only. MySQLi provides backward scrolling so this method
* should not be used.
*
* @throws Zend\Db\ResultSet\Exception\RuntimeException Buffering must be enabled before iteration is started
*
* @return ResultSet
*/
public function buffer()
{
$this->dataSource->buffer();
return $this;
}
/**
*
* @return AbstractResultSet
*/
public function buffer()
{
$this->zfResultSet->buffer();
return $this;
}
public function studentsAction()
{
$auth = new AuthenticationService();
$container = new Container('username');
if ($auth->hasIdentity() && $container->type == 1) {
$sm = $this->getServiceLocator();
$dba = $sm->get($container->adapter);
$username = $container->id;
$sql = "SELECT section FROM teacher_section WHERE computer_teacher=" . $username . " OR wk_teacher=" . $username . " OR english_teacher=" . $username . " OR math_teacher=" . $username . " OR arabic_teacher=" . $username;
$statement = $dba->query($sql, array(5));
$statement2 = $dba->query($sql, array(5));
$resultSet = new ResultSet();
$resultSet->initialize($statement);
$resultSet2 = new ResultSet();
$resultSet2->initialize($statement2);
$sql = "SELECT students.* FROM students, teacher_section\n WHERE students.Student_Section=teacher_section.section\n AND (teacher_section.computer_teacher=" . $username . " \n OR teacher_section.wk_teacher=" . $username . " \n OR teacher_section.english_teacher=" . $username . "\n OR teacher_section.math_teacher=" . $username . " \n OR teacher_section.arabic_teacher=" . $username . ") Group by students.Student_Name";
$statement3 = $dba->query($sql, array(5));
$resultSet3 = new ResultSet();
$resultSet3->initialize($statement3);
$resultSet3->buffer();
return new ViewModel(array('sections' => $resultSet, 'sections2' => $resultSet2, 'students' => $resultSet3));
} else {
return $this->redirect()->toRoute('login', array('controller' => 'index', 'action' => 'login'));
}
}
/**
* To easily get a paginator instance for a select object
* @author Anderson
* @param $select \Zend\Db\Sql\Select The select object to paginate
* @param $page int The current page number
* @param $limit int Number of items per page
* @return \Zend\Paginator\Paginator
*/
protected function getPaginatorForSelect($select, $page, $limit = 20, $pageRange = 10)
{
//-- Utilizar Buffer para Paginator
$resulset = new \Zend\Db\ResultSet\ResultSet();
$resulset->buffer();
$paginatorAdapter = new DbSelect($select, $this->getAdapter(), $resulset);
$paginator = new Paginator($paginatorAdapter);
$paginator->setItemCountPerPage($limit);
$paginator->setPageRange($pageRange);
$paginator->setCurrentPageNumber($page);
return $paginator;
}
/**
* @author VanCK
* @param Zend\Db\Sql\Select $select
* @param array $paging
* @param Object $objectPrototype
* @return \Zend\Paginator\Paginator
*/
protected function preparePaginator($select, $paging, $objectPrototype = null)
{
$resultSetPrototype = null;
if ($objectPrototype) {
$resultSetPrototype = new ResultSet();
$resultSetPrototype->buffer();
$resultSetPrototype->setArrayObjectPrototype($objectPrototype);
}
$paginatorAdapter = new DbSelect($select, $this->getDbAdapter(), $resultSetPrototype);
$paginator = new PaginatorBase($paginatorAdapter);
if (isset($paging['icpp'])) {
$paginator->setItemCountPerPage($paging['icpp']);
}
if (isset($paging['page'])) {
$paginator->setCurrentPageNumber($paging['page']);
}
return $paginator;
}
public function eventList()
{
//Set Default Values
$date_from = isset($_POST['date_from']) ? $_POST['date_from'] : '';
$date_to = isset($_POST['date_to']) ? $_POST['date_to'] : '';
if ($date_from != '' && $date_to != '') {
//Set Default Values
$date_from = $_POST['date_from'];
$date_to = $_POST['date_to'];
$dbAdapter = $this->adapter;
$sql = "\n SELECT * FROM\n (SELECT\n event_id, SUM(invited) AS invited, SUM(attended) AS attended,SUM(confirmed) AS confirmed, created_at\n FROM `events_media_profiles`\n GROUP BY event_id)emp\n LEFT JOIN `events` AS ev\n ON ev.events_id = emp.event_id\n WHERE emp.created_at BETWEEN '{$date_from}' AND '{$date_to}'\n ";
$statement = $dbAdapter->createStatement($sql);
$resultFetch = $statement->execute();
$resultSet = new ResultSet();
// Zend\Db\ResultSet
$resultSet->initialize($resultFetch);
$resultSet->buffer();
return $resultSet;
} else {
$dbAdapter = $this->adapter;
$sql = "\n SELECT * FROM\n (SELECT\n event_id, SUM(invited) AS invited, SUM(attended) AS attended,SUM(confirmed) AS confirmed, created_at\n FROM `events_media_profiles`\n GROUP BY event_id)emp\n LEFT JOIN `events` AS ev\n ON ev.events_id = emp.event_id\n ";
$statement = $dbAdapter->createStatement($sql);
$resultFetch = $statement->execute();
$resultSet = new ResultSet();
// Zend\Db\ResultSet
$resultSet->initialize($resultFetch);
$resultSet->buffer();
return $resultSet;
}
}
public function studentsAction()
{
$auth = new AuthenticationService();
$container = new Container('username');
if ($auth->hasIdentity() && $container->type == 0) {
$sm = $this->getServiceLocator();
$dba = $sm->get($container->adapter);
$sql = "SELECT * FROM section";
$statement = $dba->query($sql, array(5));
$statement2 = $dba->query($sql, array(5));
$resultSet = new ResultSet();
$resultSet->initialize($statement);
$resultSet2 = new ResultSet();
$resultSet2->initialize($statement2);
$sql2 = "SELECT students.* FROM students Order by Student_Name";
$statement3 = $dba->query($sql2, array(5));
$resultSet3 = new ResultSet();
$resultSet3->initialize($statement3);
$resultSet3->buffer();
/* insert query for all students */
$sqldays = "select students.*,abs.St_Id ,count(*) as Days from students, \n (SELECT St_Id,Abs_day,count(Att_id) FROM `attendance` \n WHERE Abs_value=3 and counted=1\n Group by St_Id,Abs_day\n having count(Att_id) >=3 ) abs\n WHERE students.sid= abs.St_Id \n Group by abs.St_Id";
$statement4 = $dba->query($sqldays, array(5));
$resultSet4 = new ResultSet();
$resultSet4->initialize($statement4);
$resultSet4->buffer();
return new ViewModel(array('sections' => $resultSet, 'sections2' => $resultSet2, 'students' => $resultSet3, 'studentsAbs' => $resultSet4));
} else {
return $this->redirect()->toRoute('login', array('controller' => 'index', 'action' => 'login'));
}
}
