Hello, I am migrating an application from ZF 2.4 to ZF3, but I am having the following errors when calling the function getCountTicketsByStatus:
Fatal error: Uncaught exception 'Zend\Db\Sql\Exception\InvalidArgumentException' with message 'Argument type should be in array(identifier,literal,select,value)' in C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractExpression.php:82
Stack trace: 0 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractExpression.php(57): Zend\Db\Sql\AbstractExpression->buildNormalizedArgument(NULL, false)1 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\Expression.php(154): Zend\Db\Sql\AbstractExpression->normalizeArgument(Array, 'value')
2 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractSql.php(130): Zend\Db\Sql\Expression->getExpressionData()
3 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractSql.php(362): Zend\Db\Sql\AbstractSql->processExpression(Object(Zend\Db\Sql\Expression), Object(Zend\Db\Adapter\Platform\Mysql), Object(Zend\Db\Adapter\Driver\Pdo\Pdo), in C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractExpression.php on line 82
Could someone give me a direction I’m a bit rusty in zend …
<?php
namespace Ticket\Model;
use Zend\Db\Adapter\Driver\ConnectionInterface;
use Zend\Db\Adapter\Platform\Mysql as MysqlPlatform;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Expression;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Insert;
use Zend\Db\TableGateway\TableGateway;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;
use Ticket\Model\TicketMessage;
use Ticket\Model\Ticket;
class TicketTable
{
// Tabela
protected $tableGateway;
public function __construct(TableGateway $tableGateway) { $this->tableGateway = $tableGateway; }
public function fetchAll() { $resultSet = $this->tableGateway->select(); return $resultSet; }
// Retorno de dados paginados
public function fetchAllPaginated($id_user = 0, $filter)
{
$subcolumns1 = array(
‘lastdate’ => new Expression(‘MAX(t4.date)’),
);$subselect1 = new Select(array('t4' => 'db_ticket_message')); $subselect1->columns($subcolumns1); $subselect1->where(array('t4.id_ticket' => new Expression('t3.id_ticket'))); $in = null; switch($filter) { case TicketMessage::OPEN: $in = array( TicketMessage::OPEN, ); break; case TicketMessage::UPDATED: $in = array( TicketMessage::UPDATED, ); break; case TicketMessage::WAITING: $in = array( TicketMessage::WAITING, ); break; case TicketMessage::RESOLVED: $in = array( TicketMessage::RESOLVED, ); break; case TicketMessage::CLOSED: $in = array( TicketMessage::CLOSED, ); break; default: $in = array( TicketMessage::OPEN, TicketMessage::UPDATED, TicketMessage::WAITING, TicketMessage::RESOLVED, TicketMessage::CLOSED, ); break; } $on = new Expression( '? = ? AND ? = ? AND ? IN (?)', array( 't1.id', 't3.id_ticket', 't3.date', $subselect1, 't3.status', $this->tableGateway->getAdapter() ->getPlatform() ->quoteValueList($in) ), array( Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE, Expression::TYPE_IDENTIFIER, Expression::TYPE_LITERAL ) ); $columns = array( 'id' => 'id', 'createdate' => 'date', 'subject' => 'subject', ); $columnst2 = array( 'author' => 'id', 'name' => 'name', 'lastname' => 'lastname', );
$columnst3 = array( 'lastupdate' => 'date', 'laststatus' => 'status', ); $columnst5 = array( 'countmessages' => 'count', ); $subcolumns2 = array( 'count' => new Expression('COUNT(*)'), 'id_ticket' => 'id_ticket', );
$subselect2 = new Select('db_ticket_message'); $subselect2->columns($subcolumns2); $subselect2->group('id_ticket'); $select = new Select(); $select->columns($columns); $select->from(array('t1' => 'db_ticket')); $select->join(array('t2' => 'db_user'), 't1.id_user = t2.id', $columnst2); $select->join(array('t3' => 'db_ticket_message'), $on, $columnst3); $select->join(array('t5' => $subselect2), 't5.id_ticket = t1.id', $columnst5); if($id_user != 0) { $select->where(array('t1.id_user' => $id_user)); } $select->group('t1.id'); $resultSetPrototype = new ResultSet(); $paginatorAdapter = new DbSelect( $select, $this->tableGateway->getAdapter(), $resultSetPrototype ); $paginator = new Paginator($paginatorAdapter); return $paginator;
}
//Retorna o Cabeçalho do Ticket
public function getTicket($id)
{
$id = (int) $id;
$rowset = $this->tableGateway->select(array(‘id’ => $id));
$row = $rowset->current();
if (!$row)
{
return false;
}
return $row;
}public function newTicket($user, $subject, $message) { $retorno = false; $dataticket = array( 'id_user' => $user->id, 'subject' => $subject, ); $dbadapter = $this->tableGateway->getAdapter(); $connection = null; try { $connection = $this->tableGateway->getAdapter() ->getDriver() ->getConnection(); $connection->beginTransaction(); $this->tableGateway->insert($dataticket); $id_ticket = $this->tableGateway->lastInsertValue;
$datamessage = array( 'id_user' => $user->id, 'id_ticket' => $id_ticket, 'message' => $message, ); $colunnsmessage = array( 'id_user', 'id_ticket', 'message', ); $insert = new Insert('db_ticket_message'); $insert->columns($colunnsmessage); $insert->values($datamessage, $insert::VALUES_MERGE); $statement = $this->tableGateway->getAdapter()->createStatement(); $insert->prepareStatement($this->tableGateway->getAdapter(), $statement); $statement->execute(); $connection->commit(); $retorno = true; } catch (\Exception $e) { if ($connection instanceof ConnectionInterface) { $connection->rollback(); } } return $retorno; }
// Retorna o numero de Tickets com o Status informado
public function getCountTicketsByStatus($id_user, $status)
{
$columnsempty = array();
$subcolumns1 = array(
‘lastdate’ => new Expression(‘MAX(t4.date)’),
);
$subselect1 = new Select(array(‘t4’ => ‘db_ticket_message’));
$subselect1->columns($subcolumns1);
$subselect1->where(array(‘t4.id_ticket’ => new Expression(‘t3.id_ticket’)));
$in = null;
if(!is_array($status))
{
switch($status)
{
case TicketMessage::OPEN:
$in = array(
TicketMessage::OPEN,
);
break;
case TicketMessage::UPDATED:
$in = array(
TicketMessage::UPDATED,
);
break;
case TicketMessage::WAITING:
$in = array(
TicketMessage::WAITING,
);
break;
case TicketMessage::RESOLVED:
$in = array(
TicketMessage::RESOLVED,
);
break;
case TicketMessage::CLOSED:
$in = array(
TicketMessage::CLOSED,
);
break;
default:
$in = array(
TicketMessage::OPEN,
TicketMessage::UPDATED,
TicketMessage::WAITING,
TicketMessage::RESOLVED,
TicketMessage::CLOSED,
);
break;
}
}
else
{
$in = $status;
}
$on = new Expression(
‘? = ? AND ? = ? AND ? IN (?)’,
array(
‘t1.id’,
‘t3.id_ticket’,
‘t3.date’,
$subselect1,
‘t3.status’,
$this->tableGateway->getAdapter()
->getPlatform()
->quoteValueList($in)
),
array(
Expression::TYPE_IDENTIFIER,
Expression::TYPE_IDENTIFIER,
Expression::TYPE_IDENTIFIER,
Expression::TYPE_VALUE,
Expression::TYPE_IDENTIFIER,
Expression::TYPE_LITERAL
)
);
$select = new Select();
$select->columns(array(‘counttickets’ => new Expression(‘COUNT(*)’)));
$select->from(array(‘t1’ => ‘db_ticket’));
$select->join(array(‘t3’ => ‘db_ticket_message’), $on, $columnsempty);
if($id_user != 0)
{
$select->where(array(‘t1.id_user’ => $id_user));
}
$sql = new Sql($this->tableGateway->getAdapter());
$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();
$result = $resultSet->current();
return $result[‘counttickets’];
}// Retorna o numero de Tickets com o Status informado
public function getCountTicketsPending($id_user)
{
$status = array(
TicketMessage::OPEN,
TicketMessage::UPDATED,
TicketMessage::WAITING,
TicketMessage::RESOLVED,
);
$count = $this->getCountTicketsByStatus($id_user, $status);
return $count;
}}