public function testStatementConstructWithSelectObject()
{
$select = $this->_db->select()->from('zfproducts');
$stmt = new Zend_Db_Statement_Pdo($this->_db, $select);
$this->assertTrue($stmt instanceof Zend_Db_Statement_Interface);
$stmt->closeCursor();
}
/**
* Get formatted statistics message
*
* @param string $type Type of query
* @param string $sql
* @param array $bind
* @param \Zend_Db_Statement_Pdo|null $result
* @return string
* @throws \Zend_Db_Statement_Exception
*/
public function getStats($type, $sql, $bind = [], $result = null)
{
$message = '## ' . getmypid() . ' ## ';
$nl = "\n";
$time = sprintf('%.4f', microtime(true) - $this->timer);
if (!$this->logAllQueries && $time < $this->logQueryTime) {
return '';
}
switch ($type) {
case self::TYPE_CONNECT:
$message .= 'CONNECT' . $nl;
break;
case self::TYPE_TRANSACTION:
$message .= 'TRANSACTION ' . $sql . $nl;
break;
case self::TYPE_QUERY:
$message .= 'QUERY' . $nl;
$message .= 'SQL: ' . $sql . $nl;
if ($bind) {
$message .= 'BIND: ' . var_export($bind, true) . $nl;
}
if ($result instanceof \Zend_Db_Statement_Pdo) {
$message .= 'AFF: ' . $result->rowCount() . $nl;
}
break;
}
$message .= 'TIME: ' . $time . $nl;
if ($this->logCallStack) {
$message .= 'TRACE: ' . Debug::backtrace(true, false) . $nl;
}
$message .= $nl;
return $message;
}
/**
* Run test fetchItem method
*
* @return void
*/
public function testFetchItem()
{
$adapterMock = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', ['query'], [], '', false);
$this->selectMock->expects($this->once())->method('getConnection')->will($this->returnValue($adapterMock));
$adapterMock->expects($this->once())->method('query')->will($this->returnValue($this->fetchStmtMock));
$this->fetchStmtMock->expects($this->once())->method('fetch')->will($this->returnValue(null));
$this->assertEquals([], $this->query->fetchItem());
}
/**
* Run test fetchItem method
*
* @return void
*/
public function testFetchItem()
{
$adapterMock = $this->getMockForAbstractClass('Zend_Db_Adapter_Abstract', [], '', false, true, true, ['query']);
$this->selectMock->expects($this->once())->method('getAdapter')->will($this->returnValue($adapterMock));
$adapterMock->expects($this->once())->method('query')->will($this->returnValue($this->fetchStmtMock));
$this->fetchStmtMock->expects($this->once())->method('fetch')->will($this->returnValue(null));
$this->assertEquals([], $this->query->fetchItem());
}
/**
* Fetches a row from the result set.
*
* @param int $style OPTIONAL Fetch mode for this fetch operation.
* @param int $cursor OPTIONAL Absolute, relative, or other.
* @param int $offset OPTIONAL Number for absolute or relative cursors.
* @return mixed Array, object, or scalar depending on fetch mode.
* @throws Zend_Db_Statement_Exception
*/
public function fetch($style = null, $cursor = null, $offset = null)
{
$row = parent::fetch($style, $cursor, $offset);
$remove = $this->_adapter->foldCase('zend_db_rownum');
if (is_array($row) && array_key_exists($remove, $row)) {
unset($row[$remove]);
}
return $row;
}
/**
* Fetch statement
*
* @return mixed
*/
public function fetchItem()
{
if (null === $this->fetchStmt) {
$this->fetchStmt = $this->getConnection()->query($this->getSelect(), $this->bindParams);
}
$data = $this->fetchStmt->fetch();
if (!$data) {
$data = [];
}
return $data;
}
public function editAction()
{
//$this->_helper->viewRenderer->setNoRender(true);
$referer = $this->getRequest()->getHeader('Referer');
$id = $this->getParam('id');
$userForm = new Form_Auth_Add();
try {
#using named parameters
/*
$sql = 'SELECT * FROM users WHERE id = :id';
$stmt = new Zend_Db_Statement_Pdo($this->db, $sql);
$stmt->execute(array(':id' => $this->getParam('id')));
*/
#using positional parameters
$sql = 'SELECT * FROM users WHERE id = ?';
$stmt = new Zend_Db_Statement_Pdo($this->db, $sql);
$stmt->execute(array($id));
$userData = $stmt->fetchAll();
if ($this->getRequest()->isPost()) {
print_r($usersData);
exit;
// clearing previous values
$usersData = array();
$userData = $this->getRequest()->getPost();
if ($userForm->isValid($userData)) {
$this->db->update($userData, 'id = ' . (int) $id);
$this->_redirect($referer);
}
}
$userForm->populate($userData);
print_r($userData[0]);
//exit;
//$this->view->users = $usersData; // oneliner // $this->view->users = $this->db->fetchAll($sql);
$this->view->userForm = $userForm;
// oneliner // $this->view->users = $this->db->fetchAll($sql);
} catch (Exception $e) {
$this->_helper->flashMessenger('An error_log');
}
}
/**
* Returns an array containing all of the result set rows.
*
* Behaves like parent, but if limit()
* is used, the final result removes the extra column
* 'zend_db_rownum'
*
* @param int $style OPTIONAL Fetch mode.
* @param int $col OPTIONAL Column number, if fetch mode is by column.
* @return array Collection of rows, each in a format by the fetch mode.
* @throws Zend_Db_Statement_Exception
*/
public function fetchAll($style = null, $col = null)
{
$data = parent::fetchAll($style, $col);
$results = array();
$remove = $this->_adapter->foldCase('ZEND_DB_ROWNUM');
foreach ($data as $row) {
if (is_array($row) && array_key_exists($remove, $row)) {
unset($row[$remove]);
}
$results[] = $row;
}
return $results;
}
public function getFirstOrLastLogForUser($idUser, $first = true)
{
$db = $this->getDbTable()->getAdapter();
$sql = ' SELECT *
FROM pic
WHERE user_id = :idUser
';
if ($first) {
$sql .= ' ORDER BY date_shot DESC LIMIT 1';
} else {
$sql .= ' ORDER BY date_shot ASC LIMIT 1';
}
$stmt = new Zend_Db_Statement_Pdo($db, $sql);
$stmt->bindParam(':idUser', $idUser);
$stmt->execute();
$resultSet = $stmt->fetchAll();
$arrPics = $this->createObjektArr($resultSet);
if (empty($arrPics)) {
return 0;
} else {
return $arrPics[0];
}
}
/**
* Returns a collection item that corresponds to the fetched row
* and moves the internal data pointer ahead
*
* @return Varien_Object|bool
*/
public function fetchItem()
{
if (null === $this->_fetchStmt) {
$this->_fetchStmt = $this->getConnection()->query($this->getSelect());
}
$data = $this->_fetchStmt->fetch();
if (!empty($data) && is_array($data)) {
$item = $this->getNewEmptyItem();
if ($this->getIdFieldName()) {
$item->setIdFieldName($this->getIdFieldName());
}
$item->setData($data);
return $item;
}
return false;
}
function insertAllSessionData($session_date, $records)
{
$db = Zend_Registry::get('db');
$i = 0;
$max_inserts = 100;
for ($rows = 0; $rows < count($records); $rows += $max_inserts) {
$values = array();
$sql = 'INSERT INTO t_exercise_data
(session_date,
time,
distance,
heartrate,
speed,
latitude,
longitude,
altitude,
cadence,
temperature,
power,
gradient,
userid)
VALUES ';
/* Do a multi insert */
for ($i = 0; $i < $max_inserts && $i + $rows < count($records); $i++) {
if ($i != 0) {
$sql .= ', ';
}
$sql .= '(:session_date,
:time' . $i . ',
:distance' . $i . ',
:heartrate' . $i . ',
:speed' . $i . ',
:latitude' . $i . ',
:longitude' . $i . ',
:altitude' . $i . ',
:cadence' . $i . ',
:temperature' . $i . ',
:power' . $i . ',
:gradient' . $i . ',
:userid) ';
$values[':time' . $i] = $records[$i + $rows]->interval;
$values[':distance' . $i] = $records[$i + $rows]->distance;
$values[':heartrate' . $i] = $records[$i + $rows]->heart_rate;
$values[':speed' . $i] = $records[$i + $rows]->speed;
$values[':latitude' . $i] = $records[$i + $rows]->position_lat;
$values[':longitude' . $i] = $records[$i + $rows]->position_long;
$values[':altitude' . $i] = $records[$i + $rows]->altitude;
$values[':cadence' . $i] = $records[$i + $rows]->cadence;
$values[':temperature' . $i] = $records[$i + $rows]->temperature;
$values[':power' . $i] = $records[$i + $rows]->power;
$values[':gradient' . $i] = $records[$i + $rows]->gradient;
}
//$stmt = new Zend_Db_Statement($db, $sql);
$stmt = new Zend_Db_Statement_Pdo($db, $sql);
/* Add the constant values for all rows */
$values[':session_date'] = $session_date;
$values[':userid'] = Core_Common::getCurrentUserLogin();
$stmt->execute($values);
}
}
/**
* Prepares an SQL statement.
*
* @param string $sql The SQL statement with placeholders.
* @param array $bind An array of data to bind to the placeholders.
* @return PDOStatement
*/
public function prepare($sql)
{
$this->_connect();
$stmt = new Zend_Db_Statement_Pdo($this, $sql);
$stmt->setFetchMode($this->_fetchMode);
return $stmt;
}
/**
* Logging debug information
*
* @param int $type
* @param string $sql
* @param array $bind
* @param Zend_Db_Statement_Pdo $result
* @return Varien_Db_Adapter_Pdo_Mysql
*/
protected function _debugStat($type, $sql, $bind = array(), $result = null)
{
if (!$this->_debug) {
return $this;
}
$code = '## ' . getmypid() . ' ## ';
$nl = "\n";
$time = sprintf('%.4f', microtime(true) - $this->_debugTimer);
if (!$this->_logAllQueries && $time < $this->_logQueryTime) {
return $this;
}
switch ($type) {
case self::DEBUG_CONNECT:
$code .= 'CONNECT' . $nl;
break;
case self::DEBUG_TRANSACTION:
$code .= 'TRANSACTION ' . $sql . $nl;
break;
case self::DEBUG_QUERY:
$code .= 'QUERY' . $nl;
$code .= 'SQL: ' . $sql . $nl;
if ($bind) {
$code .= 'BIND: ' . var_export($bind, true) . $nl;
}
if ($result instanceof Zend_Db_Statement_Pdo) {
$code .= 'AFF: ' . $result->rowCount() . $nl;
}
break;
}
$code .= 'TIME: ' . $time . $nl;
if ($this->_logCallStack) {
$code .= 'TRACE: ' . Varien_Debug::backtrace(true, false) . $nl;
}
$code .= $nl;
$this->_debugWriteToFile($code);
return $this;
}
/**
* Executes a prepared statement.
*
* @param array $params OPTIONAL Values to bind to parameter placeholders.
* @return bool
* @throws Zend_Db_Statement_Exception
*/
public function _execute(array $params = null)
{
$this->_adapter->incrementExecCounter($this->caller);
return parent::_execute($params);
}
/**
* Executes a prepared statement.
*
* @param array $params OPTIONAL Values to bind to parameter placeholders.
* @return bool
* @throws Zend_Db_Statement_Exception
*/
public function _execute(array $params = null)
{
$specialExecute = false;
if ($params) {
foreach ($params as $param) {
if ($param instanceof Varien_Db_Statement_Parameter) {
$specialExecute = true;
break;
}
}
}
if ($specialExecute) {
return $this->_executeWithBinding($params);
} else {
return parent::_execute($params);
}
}
/**
* Create default timeslot values based on days/session duration
*
* @param array $post Post request
*/
public function createTimeslots($post)
{
$db = $this->getAdapter();
$dt = new Zend_Date($post['start'], 'dd/MM/YYYY hh:mm');
$sql = 'insert into timeslots (tstart, tend, number, type, conference_id)' . 'values (:tstart, :tend, :number, :type, ' . (int) $post['id'] . ')';
$stmt = new Zend_Db_Statement_Pdo($db, $sql);
$n = 0;
// for every day add slot/break/lunch/slot/break/slot
for ($i = 1; $i <= $post['days']; $i++) {
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n + 1, ':type' => 1));
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('30', 'mm')->get(Zend_Date::ISO_8601), ':number' => 0, ':type' => 2));
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n + 2, ':type' => 1));
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => 0, ':type' => 3));
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n + 3, ':type' => 1));
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('30', 'mm')->get(Zend_Date::ISO_8601), ':number' => 0, ':type' => 2));
$stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n = $n + 4, ':type' => 1));
// reset date and add a day
$dt = new Zend_Date($post['start'], 'dd/MM/YYYY hh:mm');
$dt->add($i, Zend_Date::DAY_SHORT);
}
return true;
}
public function searchFriendsByName($idUser, $username)
{
$db = $this->getDbTable()->getAdapter();
$sql = ' SELECT f.*
FROM user f,
user_friends u
WHERE u.id_user = :idUser
AND f.id = u.id_friend
AND f.username LIKE :name ';
$username = '******' . $username . '%';
$stmt = new Zend_Db_Statement_Pdo($db, $sql);
$stmt->bindParam(':idUser', $idUser);
$stmt->bindParam(':name', $username);
$stmt->execute();
$resultSet = $stmt->fetchAll();
$arrRestaurants = $this->createObjektArr($resultSet);
return $arrRestaurants;
}
