private function getThreadCounts()
{
$select = $this->getSelect('thread')->group('forum_id')->columns(['forum_id', 'count_for_forum' => new Expression('COUNT(forum_id)')]);
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('deleted', "0");
$select->where($predicate);
return $select;
}
public function testEqualToCreatesOperatorPredicate()
{
$predicate = new Predicate();
$predicate->equalTo('foo.bar', 'bar');
$parts = $predicate->getExpressionData();
$this->assertEquals(1, count($parts));
$this->assertContains('%s = %s', $parts[0]);
$this->assertContains(array('foo.bar', 'bar'), $parts[0]);
}
public function getSettings($userId)
{
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('user_id', $userId);
$select = new Select();
$select->from($this->tableName);
$select->where($predicate);
$result = $this->select($select);
return $result->toArray();
}
public function getEmailContent($type)
{
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('type', $type);
$select = new Select();
$select->from($this->tableName);
$select->where($predicate);
$result = $this->select($select);
return $result->toArray();
}
public function getProductsById(array $productIds = array())
{
$wheres = array();
foreach ($productIds as $productId) {
$predicate = new Predicate\Predicate();
$wheres[] = $predicate->equalTo('product_id', $productId);
}
$select = $this->getSelect()->where($wheres, Predicate\PredicateSet::OP_OR);
return $this->selectManyModels($select);
}
public function updatePost($postId, $content)
{
$entity = ['content' => $content, 'last_updated' => date('Y/m/d H:i:s')];
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('id', $postId);
try {
$result = parent::update($entity, $predicate, $this->tableName, null);
} catch (Exception $e) {
return false;
}
return true;
}
/**
* Find reservations by ginosik email addresses to show on profile page
*
* @param string $ginosiEmailAddress
* @param string $alternativeEmailAddress
*
* @return \DDD\Domain\Booking\ResId[]|\ArrayObject
*/
public function getGinosikResevations($ginosiEmailAddress, $alternativeEmailAddress)
{
$columns = ['id', 'res_number', 'date_from', 'date_to', 'guest_email'];
return $this->fetchAll(function (Select $select) use($columns, $ginosiEmailAddress, $alternativeEmailAddress) {
$select->columns($columns);
$select->join(['apartments' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartments.id', ['acc_name' => 'name']);
$where = new Where();
$where->equalTo($this->getTable() . '.status', BookingService::BOOKING_STATUS_BOOKED);
$where->expression('YEAR(' . $this->getTable() . '.date_from) = YEAR(CURDATE())', array());
$where->expression($this->getTable() . '.apartment_id_assigned NOT IN (' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
if ($alternativeEmailAddress == '') {
$where->equalTo($this->getTable() . '.guest_email', $ginosiEmailAddress);
} else {
$emailPredicate = new Predicate();
$emailPredicate->equalTo($this->getTable() . '.guest_email', $ginosiEmailAddress)->or->equalTo($this->getTable() . '.guest_email', $alternativeEmailAddress);
$where->addPredicate($emailPredicate);
}
$select->where($where)->order($this->getTable() . '.date_from ASC');
});
}
<?php
require_once 'vendor/autoload.php';
use Zend\Db\Sql\Predicate\Predicate;
use Zend\Db\Sql\Sql;
$adapter = (require_once 'adapter.php');
/** @var \Zend\Db\Adapter\Adapter $adapter ['sqlite'] */
$sql = new Sql($adapter['sqlite']);
$wherePredicate = new Predicate();
$wherePredicate->equalTo('Email', '*****@*****.**')->equalTo('LastName', 'Setter')->isNotNull('FirstName');
$delete = $sql->delete();
$delete->from('Customer')->where($wherePredicate);
$statement = $sql->prepareStatementForSqlObject($delete);
$results = $statement->execute();
echo SqlFormatter::format($statement->getSql());
/**
* Prepare filter parameters
*
* @param array|string $binds A bind variable array
* @param \Zend\Db\Sql\Predicate\PredicateInterface $predicate
* @return Predicate
*/
public function prepareFilterParams($binds = [], $predicate = null)
{
if (null === $predicate) {
$predicate = new Predicate();
}
$fields = $this->getRepository()->fields;
if (is_array($binds)) {
foreach ($binds as $v) {
if (!is_array($v) || empty($v['predicate'])) {
continue;
}
if (isset($v['nesting']) && (Enums\PredicateType::NEST === $v['nesting'] || Enums\PredicateType::UNNEST === $v['nesting'])) {
/* @see \Zend\Db\Sql\Predicate\Predicate::nest
* @see \Zend\Db\Sql\Predicate\Predicate::unnest */
$predicate = $predicate->{$v['nesting']}();
}
if (isset($v['combine']) && (Predicate::OP_OR === $v['combine'] || strtolower(Predicate::OP_OR) === $v['combine'])) {
$predicate->or;
}
switch ($v['predicate']) {
case Enums\PredicateType::BETWEEN:
case Enums\PredicateType::NOT_BETWEEN:
if (empty($v['identifier']) || !isset($v['minValue']) || !isset($v['maxValue']) || !isset($fields[$v['identifier']])) {
continue;
}
/* @see \Zend\Db\Sql\Predicate\Predicate::between
* @see \Zend\Db\Sql\Predicate\Predicate::notBetween */
$predicate->{$v['predicate']}($v['identifier'], "'" . $this->filter($v['minValue'], true) . "'", "'" . $this->filter($v['maxValue'], 86399) . "'");
break;
case Enums\PredicateType::EQUAL_TO:
case Enums\PredicateType::NOT_EQUAL_TO:
case Enums\PredicateType::GREATER_THAN:
case Enums\PredicateType::LESS_THAN:
case Enums\PredicateType::GREATER_THAN_OR_EQUAL_TO:
case Enums\PredicateType::LESS_THAN_OR_EQUAL_TO:
case Enums\PredicateType::EQ:
case Enums\PredicateType::NEQ:
case Enums\PredicateType::GT:
case Enums\PredicateType::LT:
case Enums\PredicateType::GTE:
case Enums\PredicateType::LTE:
if (!isset($v['left']) || !isset($v['right'])) {
continue;
}
if (empty($v['leftType']) || Predicate::TYPE_VALUE !== $v['leftType']) {
$v['leftType'] = Predicate::TYPE_IDENTIFIER;
}
if (empty($v['rightType']) || Predicate::TYPE_IDENTIFIER !== $v['rightType']) {
$v['rightType'] = Predicate::TYPE_VALUE;
}
if ($v['leftType'] == $v['rightType']) {
$v['leftType'] = Predicate::TYPE_IDENTIFIER;
$v['rightType'] = Predicate::TYPE_VALUE;
}
if (Predicate::TYPE_IDENTIFIER !== $v['leftType']) {
$v['left'] = "'" . $this->filter($v['left'], true) . "'";
} elseif (!isset($fields[$v['left']])) {
continue;
}
if (Predicate::TYPE_IDENTIFIER !== $v['rightType']) {
$v['right'] = "'" . $this->filter($v['right'], true) . "'";
} elseif (!isset($fields[$v['right']])) {
continue;
}
/* @see \Zend\Db\Sql\Predicate\Predicate::equalTo
* @see \Zend\Db\Sql\Predicate\Predicate::notEqualTo
* @see \Zend\Db\Sql\Predicate\Predicate::lessThan
* @see \Zend\Db\Sql\Predicate\Predicate::greaterThan
* @see \Zend\Db\Sql\Predicate\Predicate::lessThanOrEqualTo
* @see \Zend\Db\Sql\Predicate\Predicate::greaterThanOrEqualTo */
$predicate->{$v['predicate']}($v['left'], $v['right'], $v['leftType'], $v['rightType']);
break;
case Enums\PredicateType::EXPR:
case Enums\PredicateType::EXPRESSION:
if (empty($v['expression'])) {
continue;
}
if (isset($v['parameters'])) {
if (!is_array($v['parameters'])) {
$v['parameters'] = [$v['parameters']];
}
foreach ($v['parameters'] as $key => &$value) {
if (!is_scalar($value)) {
unset($v['parameters'][$key]);
} elseif (!isset($fields[$value])) {
$value = "'" . str_replace('%', '%%', $this->filter($value)) . "'";
}
}
unset($value);
$v['parameters'] = array_values($v['parameters']);
}
$v['expression'] = str_replace(['<', '>'], ['<', '>'], $this->filter($v['expression']));
$predicate->expression($v['expression'], @$v['parameters']);
break;
case Enums\PredicateType::IN:
case Enums\PredicateType::NIN:
case Enums\PredicateType::NOT_IN:
if (empty($v['identifier']) || empty($v['valueSet']) || !is_array($v['valueSet'])) {
continue;
}
if (is_array($v['identifier'])) {
foreach ($v['identifier'] as $key => $value) {
if (!isset($fields[$value])) {
unset($v['identifier'][$key]);
}
}
if (empty($v['identifier'])) {
continue;
}
$v['identifier'] = array_values($v['identifier']);
} elseif (!isset($fields[$v['identifier']])) {
continue;
}
foreach ($v['valueSet'] as &$value) {
$value = "'" . $this->filter($value) . "'";
}
unset($value);
/* @see \Zend\Db\Sql\Predicate\Predicate::in
* @see \Zend\Db\Sql\Predicate\Predicate::notIn */
$predicate->{$v['predicate']}($v['identifier'], $v['valueSet']);
break;
case Enums\PredicateType::IS_NOT_NULL:
case Enums\PredicateType::IS_NULL:
if (empty($v['identifier']) || !isset($fields[$v['identifier']])) {
continue;
}
/* @see \Zend\Db\Sql\Predicate\Predicate::isNull
* @see \Zend\Db\Sql\Predicate\Predicate::isNotNull */
$predicate->{$v['predicate']}($v['identifier']);
break;
case Enums\PredicateType::LIKE:
case Enums\PredicateType::NOT_LIKE:
if (empty($v['identifier']) || empty($v[$v['predicate']]) || !isset($fields[$v['identifier']]) || !is_string($v[$v['predicate']])) {
continue;
}
$v[$v['predicate']] = "'%" . str_replace('%', '%%', $this->filter($v[$v['predicate']])) . "%'";
/* @see \Zend\Db\Sql\Predicate\Predicate::like
* @see \Zend\Db\Sql\Predicate\Predicate::notLike */
$predicate->{$v['predicate']}($v['identifier'], $v[$v['predicate']]);
break;
case Predicate::TYPE_LITERAL:
if (empty($v['literal']) || !is_string($v['literal'])) {
continue;
}
$predicate->literal(str_replace(['<', '>', ''', '''], ['<', '>', "'", "'"], $this->filter($v['literal'])));
break;
default:
}
}
} elseif (is_string($binds)) {
$predicateSet = new Predicate();
$searchable = $this->getConfig()->get('app.minSearchChars') <= strlen($binds);
$binds = $this->filter($binds);
$equalValue = "'" . $binds . "'";
$likeValue = "'%" . str_replace('%', '%%', $binds) . "%'";
$count = 0;
foreach ($fields as $k => $v) {
if ((Types\Type::STRING_TYPE === $v['type'] || Types\Type::TEXT_TYPE === $v['type']) && ($searchable || ($isChar = isset($v['options']) && isset($v['options']['fixed'])))) {
$predicateSet->or;
isset($isChar) && $isChar ? $predicateSet->equalTo($k, $equalValue) : $predicateSet->like($k, $likeValue);
if (CHAOS_MAX_QUERY <= ++$count) {
break;
}
}
}
if (0 !== count($predicateSet)) {
$predicate->predicate($predicateSet);
}
}
return $predicate;
}
public function constructWhereFromFilterParams($filterParams, $download = false)
{
/* @var $auth \Library\Authentication\BackofficeAuthenticationService */
$auth = $this->getServiceLocator()->get('library_backoffice_auth');
$where = new Where();
$table = DbTables::TBL_BOOKINGS;
if (isset($filterParams["res_number"]) && $filterParams["res_number"] != '') {
$where->like($table . '.res_number', '%' . $filterParams["res_number"] . '%');
}
if (isset($filterParams["rooms_count"])) {
$apartmentTableName = $download ? DbTables::TBL_APARTMENTS : 'apartments';
$roomsCount = $filterParams["rooms_count"] - 1;
if ($roomsCount >= 0) {
if ($roomsCount == 2) {
$where->greaterThanOrEqualTo($apartmentTableName . '.bedroom_count', $roomsCount);
} else {
$where->equalTo($apartmentTableName . '.bedroom_count', $roomsCount);
}
}
}
if (isset($filterParams["status"]) && $filterParams["status"]) {
if ($filterParams["status"] == Constants::NOT_BOOKED_STATUS) {
$where->notEqualTo($table . '.status', Booking::BOOKING_STATUS_BOOKED);
} else {
$where->equalTo($table . '.status', $filterParams["status"]);
}
}
if (isset($filterParams["arrival_status"]) && $filterParams['arrival_status'] != -1) {
$where->equalTo($table . '.arrival_status', $filterParams["arrival_status"]);
}
if (isset($filterParams["overbooking_status"]) && $filterParams["overbooking_status"] != -1) {
$where->equalTo($table . '.overbooking_status', $filterParams["overbooking_status"]);
}
if (isset($filterParams["apartel_id"]) && $filterParams["apartel_id"] != -2) {
$where->equalTo($table . '.apartel_id', $filterParams["apartel_id"]);
}
if (isset($filterParams["product_id"]) && isset($filterParams["product"]) && $filterParams["product_id"] != '' && $filterParams["product"] != '') {
$where->equalTo($table . '.apartment_id_origin', $filterParams["product_id"]);
}
if (isset($filterParams["assigned_product_id"]) && isset($filterParams["assigned_product"]) && $filterParams["assigned_product_id"] != '' && $filterParams["assigned_product"] != '') {
$where->equalTo($table . '.apartment_id_assigned', $filterParams["assigned_product_id"]);
}
if (isset($filterParams["booking_date"]) && $filterParams["booking_date"] != '') {
$dates = explode(' - ', $filterParams["booking_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->expression('DATE(' . $table . '.timestamp) >= \'' . $startDate . '\'', []);
$where->expression('DATE(' . $table . '.timestamp) <= \'' . $endDate . '\'', []);
}
if (isset($filterParams["arrival_date"]) && $filterParams["arrival_date"] != '') {
$dates = explode(' - ', $filterParams["arrival_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->lessThanOrEqualTo($table . ".date_from", $endDate);
$where->greaterThanOrEqualTo($table . ".date_from", $startDate);
}
if (isset($filterParams["departure_date"]) && $filterParams["departure_date"] != '') {
$dates = explode(' - ', $filterParams["departure_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->lessThanOrEqualTo($table . ".date_to", $endDate);
$where->greaterThanOrEqualTo($table . ".date_to", $startDate);
}
if (isset($filterParams["guest_first_name"]) && $filterParams["guest_first_name"] != '') {
$where->like($table . '.guest_first_name', '%' . $filterParams["guest_first_name"] . '%');
}
if (isset($filterParams["guest_last_name"]) && $filterParams["guest_last_name"] != '') {
$where->like($table . '.guest_last_name', '%' . $filterParams["guest_last_name"] . '%');
}
if (isset($filterParams["guest_phone"]) && $filterParams["guest_phone"] != '') {
$nestedWhere = new Predicate();
$nestedWhere->like($table . '.guest_phone', '%' . $filterParams["guest_phone"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.guest_travel_phone', '%' . $filterParams["guest_phone"] . '%');
$where->addPredicate($nestedWhere);
}
if (isset($filterParams["guest_email"]) && $filterParams["guest_email"] != '') {
$where->like($table . '.guest_email', '%' . $filterParams["guest_email"] . '%');
}
if (isset($filterParams["guest_secondary_email"]) && $filterParams["guest_secondary_email"] != '') {
$where->like($table . '.secondary_email', '%' . $filterParams["guest_secondary_email"] . '%');
}
if (isset($filterParams["guest_country_id"]) && $filterParams["guest_country_id"] != '' && $filterParams["guest_country"] != '') {
$where->equalTo($table . '.guest_country_id', $filterParams["guest_country_id"]);
}
if (isset($filterParams["apt_location_id"]) && $filterParams["apt_location_id"] != '') {
$nestedWhere = new Predicate();
$nestedWhere->equalTo($table . '.acc_country_id', $filterParams["apt_location_id"]);
$nestedWhere->OR;
$nestedWhere->equalTo($table . '.acc_city_id', $filterParams["apt_location_id"]);
$where->addPredicate($nestedWhere);
}
if (isset($filterParams["partner_id"]) && $filterParams["partner_id"] != '0') {
$where->equalTo($table . '.partner_id', $filterParams["partner_id"]);
}
if (isset($filterParams["partner_reference"]) && $filterParams["partner_reference"] != '') {
$where->like($table . '.partner_ref', '%' . $filterParams["partner_reference"] . '%');
}
if (isset($filterParams["payment_model"]) && $filterParams["payment_model"] != '0') {
$where->equalTo($table . '.model', $filterParams["payment_model"]);
}
if (!$auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING)) {
$where->expression($table . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
}
if (isset($filterParams["no_collection"]) && $filterParams["no_collection"] > 1) {
$where->equalTo($table . '.no_collection', $filterParams["no_collection"] == 2 ? 1 : 0);
}
if (isset($filterParams["channel_res_id"]) && $filterParams["channel_res_id"]) {
$where->equalTo($table . '.channel_res_id', $filterParams["channel_res_id"]);
}
return $where;
}
<?php
require_once 'vendor/autoload.php';
require_once 'output-results.php';
use Zend\Db\Sql\Predicate\Expression;
use Zend\Db\Sql\Predicate\Predicate;
use Zend\Db\Sql\Sql;
$adapter = (require_once 'adapter.php');
$queryValues = [':invoiceMin' => 1, ':invoiceMax' => 20, ':countryOne' => 'USA', ':countryTwo' => 'Germany'];
/** @var \Zend\Db\Adapter\Driver\StatementInterface $statement */
$sql = new Sql($adapter['sqlite']);
$countryPredicate = new Predicate();
$countryPredicate->equalTo('c.Country', 'USA')->or->equalTo('c.Country', 'Germany');
$invoicePredicate = new Predicate();
$invoicePredicate->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax']);
$predicate = new Predicate();
//$predicate->addPredicate($invoicePredicate)->addPredicate($countryPredicate);
$predicate->addPredicates([$invoicePredicate]);
$predicate->andPredicate($countryPredicate);
$sqliteFullNameExpression = new Expression('c.FirstName || " " || c.LastName');
$whereLastNameExpression = new Expression('LastName IN (?)', [['Stevens', 'Brooks', 'Harris']]);
/** @var Zend\Db\Sql\Select $select */
$select = $sql->select();
$select->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', "Full Name" => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total'])->where($predicate)->where($whereLastNameExpression)->order(['c.Country DESC', 'i.Total DESC', 'c.LastName'])->limit(30)->offset(1);
/** @var \Zend\Db\Adapter\Driver\Pdo\Result $results */
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
renderResults($results, $statement);
/**
* Returns true if user is this team's director or one of team's managers
* @param $teamId
* @param $userId
* @return bool
*/
public function isTeamManagerOrDirector($teamId, $userId)
{
$where = new Where();
$nestedWhere = new Predicate();
$where->equalTo('team_id', $teamId)->equalTo('user_id', $userId);
$nestedWhere->equalTo('type', self::STAFF_MANAGER)->or->equalTo('type', self::STAFF_DIRECTOR);
$where->addPredicate($nestedWhere);
$result = $this->getTeamStaffDao()->fetchOne($where, ['id']);
if (!$result) {
return false;
}
return true;
}
