public function query(\Zend\Db\Sql\Select $select)
{
$dbAdapter = $this->getDbAdapter();
$platform = $dbAdapter->getPlatform();
$sql = $select->getSqlString($platform);
$result = $dbAdapter->query($sql)->execute()->current();
return $result;
}
/**
* @param PlatformInterface $platform
* @return string
*/
public function getSqlString(PlatformInterface $platform = null)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
return parent::getSqlString($platform);
}
/**
* @param PlatformInterface $platform
* @return string
*/
public function getSqlString(PlatformInterface $platform = null)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
unset($this->specifications[self::LIMIT]);
unset($this->specifications[self::OFFSET]);
$this->specifications['LIMITOFFSET'] = null;
return parent::getSqlString($platform);
}
/**
* @param PlatformInterface $platform
* @return string
*/
public function getSqlString(PlatformInterface $platform = null)
{
// localize variables
foreach (get_object_vars($this->select) as $name => $value) {
$this->{$name} = $value;
}
if ($this->limit === null && $this->offset !== null) {
$this->specifications[self::LIMIT] = 'LIMIT 18446744073709551615';
}
return parent::getSqlString($platform);
}
public function searchSerial($serial)
{
$select = new Select($this->tableGateway->getTable());
$select->columns(array());
$select->join("products", "products.id = " . $this->tableGateway->getTable() . ".product", array('id' => 'id'));
$select->join("products_receive_inventory", "products_receive_inventory.details_receive_inventory = " . $this->tableGateway->getTable() . ".id", array('serial' => 'serial'));
$select->where(array("products_receive_inventory.status" => 0));
$select->where->like("products_receive_inventory.serial", "%" . $serial . "%");
error_log($select->getSqlString());
$result = $this->tableGateway->selectWith($select);
return $result;
}
/**
* Handles job get report event by fetching messages from the log table
*
* @param Event $event
* @return \SporkTools\Core\Job\Report
*/
public function report(Event $event)
{
$job = $event->getTarget();
$serviceManager = $job->getServiceManager();
$this->initialize($serviceManager);
$report = new Report();
$runId = $this->fetchLastBatchId($job);
$select = new Select($this->table);
$select->columns(array('message' => $this->columnMap['message'], 'type' => $this->columnMap['priority'], 'datetime' => $this->columnMap['timestamp']))->where->like($this->columnMap['message'], "% [{$runId}]");
$results = $this->db->query($select->getSqlString($this->db->getPlatform()), Adapter::QUERY_MODE_EXECUTE);
foreach ($results as $row) {
$type = isset($this->typeMap[$row->type]) ? $this->typeMap[$row->type] : $row->type;
$report->addMessage(new Message($row->message, $type, $row->datetime));
}
return $report;
}
/**
* @param array $accList
* @param string $date
*
* @return \DDD\Domain\ApartmentGroup\ConciergeView[]|\ArrayObject
*/
public function getArrivalsByDay($accList, $date)
{
$columns = array('id', 'ki_page_hash', 'guest_first_name', 'guest_last_name', 'res_number', 'pax' => 'man_count', 'occupancy', 'date_to', 'arrival_status', 'overbooking_status', 'model', 'check_charged', 'guest_email', 'guest_balance', 'housekeeping_comment' => new Expression("(\n SELECT\n GROUP_CONCAT(\n CONCAT('<blockquote class=\"comment-blockquote\">', '<p>', value, '</p><footer>', users.firstname, ' ', users.lastname, ', ', `timestamp`, ' (Amsterdam Time)', '</footer></blockquote>') SEPARATOR ''\n )\n FROM " . DbTables::TBL_ACTION_LOGS . "\n LEFT JOIN " . DbTables::TBL_BACKOFFICE_USERS . " AS users ON users.id = " . DbTables::TBL_ACTION_LOGS . ".user_id\n WHERE module_id = " . Logger::MODULE_BOOKING . " AND identity_id = " . $this->getTable() . ".`id` AND action_id = " . Logger::ACTION_HOUSEKEEPING_COMMENT . "\n )"), 'ki_page_status' => 'ki_page_status', 'provide_cc_page_status', 'provide_cc_page_hash');
$select = new Select($this->getTable());
$select->join(['charge' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charge.reservation_id AND charge.addons_type = 6 AND charge.status = 0'), ['parking' => 'id'], Select::JOIN_LEFT);
$select->join(['apartment' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartment.id', ['unitNumber' => 'unit_number', 'acc_name' => 'name'], Select::JOIN_LEFT);
$select->join(['cc_token' => DbTables::TBL_TOKEN], new Expression($this->getTable() . '.customer_id = cc_token.customer_id AND cc_token.status in (' . CardServiceUse::CC_STATUS_VALID . ',' . CardServiceUse::CC_STATUS_UNKNOWN . ')'), ['first_digits', 'cc_type' => 'brand', 'salt'], Select::JOIN_LEFT);
$select->where($this->getTable() . ".date_from = '" . $date . "'")->where($this->getTable() . '.status = 1')->where->notEqualTo($this->getTable() . '.overbooking_status', BookingTicket::OVERBOOKING_STATUS_OVERBOOKED);
$select->where->in($this->getTable() . '.apartment_id_assigned', $accList);
$select->columns($columns)->order(['cc_token.is_default DESC', 'cc_token.status DESC', 'arrival_status ASC', 'guest_last_name ASC']);
$sql = $select->getSqlString($this->getAdapter()->getPlatform());
$sql = "SELECT * FROM ({$sql}) AS main GROUP BY res_number ORDER BY arrival_status ASC, guest_last_name ASC";
$statement = $this->adapter->createStatement($sql);
$result = $statement->execute();
$resultSet = clone $this->resultSetPrototype;
$resultSet->initialize($result);
return $resultSet;
}
/**
* Get SQL string for this statement
*
* @param null|PlatformInterface $adapterPlatform Defaults to Sql92 if none provided
* @return string
*/
public function getSqlString(PlatformInterface $adapterPlatform = null)
{
$adapterPlatform = $adapterPlatform ?: new Sql92();
$table = $this->table;
$schema = null;
// create quoted table name to use in insert processing
if ($table instanceof TableIdentifier) {
list($table, $schema) = $table->getTableAndSchema();
}
$table = $adapterPlatform->quoteIdentifier($table);
if ($schema) {
$table = $adapterPlatform->quoteIdentifier($schema) . $adapterPlatform->getIdentifierSeparator() . $table;
}
$columns = array_map(array($adapterPlatform, 'quoteIdentifier'), $this->columns);
$columns = implode(', ', $columns);
if (is_array($this->values)) {
$values = array();
foreach ($this->values as $value) {
if ($value instanceof Expression) {
$exprData = $this->processExpression($value, $adapterPlatform);
$values[] = $exprData->getSql();
} elseif ($value === null) {
$values[] = 'NULL';
} else {
$values[] = $adapterPlatform->quoteValue($value);
}
}
return sprintf($this->specifications[static::SPECIFICATION_INSERT], $table, $columns, implode(', ', $values));
} elseif ($this->values instanceof Select) {
$selectString = $this->values->getSqlString($adapterPlatform);
if ($columns) {
$columns = "({$columns})";
}
return sprintf($this->specifications[static::SPECIFICATION_SELECT], $table, $columns, $selectString);
} else {
throw new Exception\InvalidArgumentException('values or select should be present');
}
}
/**
* @param Select $select
* @param PlatformInterface $platform
*/
public static function dumpSQL($select, $platform = null)
{
die(str_replace('"', '', $select->getSqlString($platform)));
}
/**
* @param \Zend\Db\Sql\Select $select
* @return mixed
*/
protected function getSql($select)
{
return $select->getSqlString($this->tableGateway->getAdapter()->getPlatform());
}
/**
* @param string $expectedSql
*/
protected function assertTableGatewayLastSqlSelect($expectedSql)
{
$actualSql = $this->select->getSqlString($this->mysqlPlatform);
$this->assertSqlEquals($expectedSql, $actualSql);
}
/**
* @testdox unit test: Test getSqlString() will produce expected sql and parameters based on a variety of provided arguments [uses data provider]
* @covers Zend\Db\Sql\Select::getSqlString
* @dataProvider providerData
*/
public function testGetSqlString(Select $select, $unused, $unused2, $expectedSqlString)
{
$this->assertEquals($expectedSqlString, $select->getSqlString(new TrustingSql92Platform()));
}
/**
* @param Select $select
* @return string $sqlString
*/
public function getSqlString($select)
{
$adapterPlatform = $this->getAdapter()->getPlatform();
$sqlString = $select->getSqlString($adapterPlatform);
return $sqlString;
}
/**
* @covers Zend\Db\Sql\Select::getSqlString
*/
public function testGetSqlString()
{
$this->select->from('foo')->columns(array('bee', 'baz'))->join('zac', 'm = n')->where('x = y');
$this->assertEquals('SELECT "bee", "baz", "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n" WHERE x = y', $this->select->getSqlString());
}
/**
* @inheritdoc
*/
public function findAll()
{
$collection = new TrackingUnitCollection();
$dbAdapter = $this->getDbAdapter();
$select = new Select(Utils::getTrackingTableName());
$select->order(array(Utils::getTrackingTableName(), Select::ORDER_ASCENDING));
$sql = $select->getSqlString($dbAdapter->getPlatform());
$results = $dbAdapter->query($sql, DbAdapter::QUERY_MODE_EXECUTE);
foreach ($results as $rowData) {
$trackingUnit = $this->loadTrackingUnit($rowData);
$collection->attach($trackingUnit);
}
//end loop
return $collection;
}
/**
* @testdox unit test: Test getSqlString() will produce expected sql and parameters based on a variety of provided arguments [uses data provider]
* @covers Zend\Db\Sql\Select::getSqlString
* @dataProvider providerData
*/
public function testGetSqlString(Select $select, $unused, $unused2, $expectedSqlString)
{
$this->assertEquals($expectedSqlString, $select->getSqlString());
}
protected function processSubSelect(Select $subselect, PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
{
if ($driver) {
$stmtContainer = new StatementContainer();
// Track subselect prefix and count for parameters
$this->processInfo['subselectCount']++;
$subselect->processInfo['subselectCount'] = $this->processInfo['subselectCount'];
$subselect->processInfo['paramPrefix'] = 'subselect' . $subselect->processInfo['subselectCount'];
// call subselect
$subselect->prepareStatement(new \Zend\Db\Adapter\Adapter($driver, $platform), $stmtContainer);
// copy count
$this->processInfo['subselectCount'] = $subselect->processInfo['subselectCount'];
$parameterContainer->merge($stmtContainer->getParameterContainer()->getNamedArray());
$sql = $stmtContainer->getSql();
} else {
$sql = $subselect->getSqlString($platform);
}
return $sql;
}
/**
* Returns the generated sql
*
* @param Zend\Db\Sql\Select
*/
public function getSqlForSelect(Select $select)
{
return $select->getSqlString($this->tableGateway->getAdapter()->getPlatform());
}