public function getTeamListDetails($offset, $limit, $sortCol, $sortDir, $like, $deactivatedIncluded = '1') { $oldEntityPrototype = $this->resultSetPrototype->getArrayObjectPrototype(); $this->resultSetPrototype->setArrayObjectPrototype(new PeopleTeamsTableRow()); $result = $this->fetchAll(function (Select $select) use($offset, $limit, $sortCol, $sortDir, $like, $deactivatedIncluded) { $sortColumns = ['is_disable', 'name', 'description', 'size', 'usage_department', 'usage_notifiable', 'usage_frontier', 'usage_security', 'usage_taskable', 'usage_procurement', 'usage_hiring', 'usage_storage']; $select->columns(['id' => 'id', 'is_active' => 'is_disable', 'name' => 'name', 'description' => 'description', 'usage_department' => 'usage_department', 'usage_notifiable' => 'usage_notifiable', 'usage_frontier' => 'usage_frontier', 'usage_security' => 'usage_security', 'usage_taskable' => 'usage_taskable', 'usage_procurement' => 'usage_procurement', 'usage_hiring' => 'usage_hiring', 'usage_storage' => 'usage_storage'])->join(['creator' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.id = creator.team_id AND creator.type =' . TeamService::STAFF_CREATOR), [])->join(['staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.id = staff.team_id AND staff.type !=' . TeamService::STAFF_CREATOR . ' AND staff.type !=' . TeamService::STAFF_DIRECTOR . ' AND staff.user_id !=' . UserService::ANY_TEAM_MEMBER_USER_ID), ['size' => new Expression('COUNT(distinct(staff.user_id))')], Select::JOIN_LEFT); switch ($deactivatedIncluded) { case '2': $select->where->equalTo($this->getTable() . '.is_disable', 1); // only inactive ones break; case '1': $select->where->equalTo($this->getTable() . '.is_disable', 0); // only active ones break; case '0': // all break; } if ($like !== '') { $nestedWhere = new Predicate(); $nestedWhere->like($this->getTable() . '.description', '%' . $like . '%'); $nestedWhere->or; $nestedWhere->like($this->getTable() . '.name', '%' . $like . '%'); $select->where->andPredicate($nestedWhere); } $select->group($this->getTable() . '.id')->order($sortColumns[$sortCol] . ' ' . $sortDir)->offset((int) $offset)->limit((int) $limit); }); $this->resultSetPrototype->setArrayObjectPrototype($oldEntityPrototype); return $result; }
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; }
/** * Begin nesting predicates * * @return Predicate */ public function nest() { $predicateSet = new Predicate(); $predicateSet->setUnnest($this); $this->addPredicate($predicateSet, $this->nextPredicateCombineOperator ?: $this->defaultCombination); $this->nextPredicateCombineOperator = null; return $predicateSet; }
/** * Get all empty transactions * * @param integer $limit * @return array */ public function getEmptyTransactions($limit) { $predicate = new Predicate(); $select = $this->select(); $select->from(['a' => 'payment_transaction_list'])->columns(['id', 'slug'])->join(['b' => 'payment_transaction_item'], 'a.id = b.transaction_id', [], 'left')->where([$predicate->isNull('b.transaction_id')])->group('a.id')->limit($limit); $statement = $this->prepareStatementForSqlObject($select); $resultSet = new ResultSet(); $resultSet->initialize($statement->execute()); return $resultSet->toArray(); }
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; }
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; }
/** * Delete not active empty connections * * @return boolean|string */ public function deleteNotActiveEmptyConnections() { try { $this->adapter->getDriver()->getConnection()->beginTransaction(); $predicate = new Predicate(); $delete = $this->delete()->from('membership_level_connection')->where(['active' => self::MEMBERSHIP_LEVEL_CONNECTION_NOT_ACTIVE, $predicate->isNull('membership_id')]); $statement = $this->prepareStatementForSqlObject($delete); $statement->execute(); $this->adapter->getDriver()->getConnection()->commit(); } catch (Exception $e) { $this->adapter->getDriver()->getConnection()->rollback(); ApplicationErrorLogger::log($e); return $e->getMessage(); } return true; }
public function getTranslationListForSearch($filterParams = array()) { $where = new Where(); if ((int) $filterParams["id_translation"] > 0) { $where->equalTo($this->getTable() . '.id', $filterParams["id_translation"]); } else { if ($filterParams["srch_txt"] != '') { $pred = new Predicate(); $pred->like($this->getTable() . '.name', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%')->or->like($this->getTable() . '.information_text_html_clean', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%'); $where->addPredicate($pred); } } $columns = ['id' => 'id', 'tx_2' => 'information_text', 'name' => 'name', 'name_en' => 'name']; $sortColumns = ['id', 'information_text', 'name_en']; $result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $where) { $select->columns($columns); $select->join(DbTables::TBL_COUNTRIES, $this->getTable() . '.id = ' . DbTables::TBL_COUNTRIES . '.detail_id', array('country' => 'id'), 'left'); $select->join(DbTables::TBL_PROVINCES, $this->getTable() . '.id = ' . DbTables::TBL_PROVINCES . '.detail_id', array('provinces' => 'id'), 'left'); $select->join(DbTables::TBL_CITIES, $this->getTable() . '.id = ' . DbTables::TBL_CITIES . '.detail_id', array('city' => 'id'), 'left'); $select->join(DbTables::TBL_POI, $this->getTable() . '.id = ' . DbTables::TBL_POI . '.detail_id', array('poi' => 'id'), 'left'); if ($where !== null) { $select->where($where); } $select->group($this->getTable() . '.id'); }); $count = $this->getCount($where); return ['result' => $result, 'count' => $count]; }
<?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'); $update = $sql->update(); $update->table('Customer')->set(['FirstName' => 'Matthew', 'LastName' => 'Setter', 'Email' => '*****@*****.**', 'Country' => 'Australia'])->where($wherePredicate); $statement = $sql->prepareStatementForSqlObject($update); $results = $statement->execute(); echo SqlFormatter::format($statement->getSql());
/** * Delete node * * @param integer $leftKey * @param integer $rightKey * @param array $filter * @param boolean $useTransaction * @return boolean|string */ public function deleteNode($leftKey, $rightKey, array $filter = [], $useTransaction = true) { try { if ($useTransaction) { $this->tableGateway->getAdapter()->getDriver()->getConnection()->beginTransaction(); } $predicate = new Predicate(); $this->tableGateway->delete([$predicate->greaterThanOrEqualTo($this->left, $leftKey), $predicate->lessThanOrEqualTo($this->right, $rightKey)] + $filter); $predicate = new Predicate(); $this->tableGateway->update([$this->left => new Expression('IF(' . $this->left . ' > ?, ' . $this->left . ' - (? - ? + 1), ' . $this->left . ')', [$leftKey, $rightKey, $leftKey]), $this->right => new Expression($this->right . ' - (? - ? + 1)', [$rightKey, $leftKey])], [$predicate->greaterThan($this->right, $rightKey)] + $filter); if ($useTransaction) { $this->tableGateway->getAdapter()->getDriver()->getConnection()->commit(); } } catch (Exception $e) { if ($useTransaction) { $this->tableGateway->getAdapter()->getDriver()->getConnection()->rollback(); } ErrorLogger::log($e); return $e->getMessage(); } return true; }
"SELECT c.FirstName, c.LastName, c.Email, c.Country, i.InvoiceId, i.InvoiceDate, i.Total FROM Customer c INNER JOIN Invoice i ON (i.CustomerId = c.CustomerId) WHERE (i.Total BETWEEN :invoiceMin and :invoiceMax) AND c.Country = :customerCountry LIMIT 20", $queryValues );*/ $sqliteFullNameExpression = new Expression('c.FirstName || " " || c.LastName'); $whereLastNameExpression = new Expression('lower(LastName) IN (?)', ['stevens', 'brooks', 'harris']); $sql = new Sql($adapter['sqlite']); /** @var Zend\Db\Sql\Select $select */ $select = $sql->select(); $select->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', 'Customer Full Name' => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total']); $predicate = new Predicate(); $predicate->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax'])->equalTo('c.Country', $queryValues[':customerCountry']); $predicate2 = new Predicate(); $predicate2->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax'])->equalTo('c.Country', 'Chile'); $select->where($predicate); $select2 = $sql->select(); $select2 = $select2->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', 'Customer Full Name' => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total']); $select2->where($predicate2); $select->limit(20)->order(["Total DESC", "LastName ASC"]); $select2->combine($select); $statement = $sql->prepareStatementForSqlObject($select2); echo SqlFormatter::format($statement->getSql()); exit; /** @var \Zend\Db\Adapter\Driver\Pdo\Result $results */ $results = $statement->execute(); if ($results->count()) { foreach ($results as $result) { $headers = array_keys($result);
public function getReservationForAccOnDate($apartment_id, $start_date, $end_date) { return $this->fetchAll(function (Select $select) use($apartment_id, $start_date, $end_date) { $select->columns(['date_from', 'date_to']); $where = new Where(); $where->equalTo('apartment_id_assigned', $apartment_id)->equalTo('status', 1); $fromPredicate = new Predicate(); $fromPredicate->greaterThanOrEqualTo('date_from', $start_date)->lessThanOrEqualTo('date_from', $end_date); $toPredicate = new Predicate(); $toPredicate->greaterThanOrEqualTo('date_to', $start_date)->lessThanOrEqualTo('date_to', $end_date); $fromToPredicate = new PredicateSet([$fromPredicate, $toPredicate], PredicateSet::COMBINED_BY_OR); $where->addPredicate($fromToPredicate); $select->where($where); }); }
/** * Get SQL string for statement * * @param null|PlatformInterface $platform If null, defaults to Sql92 * @return string */ public function getSqlString(PlatformInterface $platform = null) { $platform = $platform ?: new Sql92(); $parts = parent::getWhereParts(); $wherePart = ''; foreach ($parts as $part) { if (is_string($part)) { $wherePart .= $part; } elseif (is_array($part)) { $values = $part[1]; $types = isset($part[2]) ? $part[2] : array(); foreach ($values as $index => $value) { if (isset($types[$index]) && $types[$index] == self::TYPE_IDENTIFIER) { $values[$index] = $platform->quoteIdentifierInFragment($value); } elseif (isset($types[$index]) && $types[$index] == self::TYPE_VALUE) { $values[$index] = $platform->quoteValue($value); } } $wherePart .= vsprintf($part[0], $values); } } return sprintf($this->specification, $wherePart); }
<?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());
/** * @param int $reservationId * * @return \Traversable|array */ public function getFirstAccTaxCharge($reservationId) { $this->resultSetPrototype->setArrayObjectPrototype(new \DDD\Domain\Booking\ForCancelCharge()); $result = $this->fetchAll(function (Select $select) use($reservationId) { $select->join(['addons' => DbTables::TBL_BOOKING_ADDONS], $this->getTable() . '.addons_type = addons.id', ['addon' => 'name', 'location_tax' => 'location_join', 'cxl_apply' => 'cxl_apply'], Select::JOIN_LEFT); $select->where->equalTo($this->getTable() . '.reservation_id', $reservationId); $select->where->equalTo($this->table . '.status', ChargeService::CHARGE_STATUS_NORMAL); $accOrTaxWhere = new Predicate(); $accOrTaxWhere->in($this->table . '.addons_type', [BookingAddon::ADDON_TYPE_ACC, BookingAddon::ADDON_TYPE_PARKING, BookingAddon::ADDON_TYPE_CLEANING_FEE, BookingAddon::ADDON_TYPE_DISCOUNT, BookingAddon::ADDON_TYPE_COMPENSATION, BookingAddon::ADDON_TYPE_EXTRA_PERSON]); // acc or parking $accOrTaxWhere->OR; $accOrTaxWhere->notEqualTo('addons.location_join', ''); // taxes $select->where->addPredicate($accOrTaxWhere); $select->order('id ASC'); }); return $result; }
public function testCanNestPredicates() { $predicate = new Predicate(); $predicate->isNull('foo.bar') ->nest() ->isNotNull('bar.baz') ->and ->equalTo('baz.bat', 'foo') ->unnest(); $parts = $predicate->getExpressionData(); $this->assertEquals(7, count($parts)); $this->assertContains('%1$s IS NULL', $parts[0]); $this->assertContains(array('foo.bar'), $parts[0]); $this->assertEquals(' AND ', $parts[1]); $this->assertEquals('(', $parts[2]); $this->assertContains('%1$s IS NOT NULL', $parts[3]); $this->assertContains(array('bar.baz'), $parts[3]); $this->assertEquals(' AND ', $parts[4]); $this->assertContains('%s = %s', $parts[5]); $this->assertContains(array('baz.bat', 'foo'), $parts[5]); $this->assertEquals(')', $parts[6]); }
/** * @param array $data * @return array */ public function getPayToPartnerReservations(array $data) { /** * @var \DDD\Dao\Booking\Booking $bookingDao */ $bookingDao = $this->getServiceLocator()->get('dao_booking_booking'); $bookingDao->setEntity(new \DDD\Domain\Booking\BookingTableRow()); $where = new Where(); if (isset($data['res_number'])) { $where->equalTo(DbTables::TBL_BOOKINGS . '.res_number', $data['res_number']); } else { $partnerId = $data['partner']; $dateFrom = $data['date_from']; $dateTo = $data['date_to']; $dist = $data['dist']; $where->equalTo(DbTables::TBL_BOOKINGS . '.partner_id', $partnerId); $where->greaterThanOrEqualTo(DbTables::TBL_BOOKINGS . '.date_to', date('Y-m-d', strtotime($dateFrom))); $where->lessThanOrEqualTo(DbTables::TBL_BOOKINGS . '.date_to', date('Y-m-d', strtotime($dateTo))); $where->greaterThan(DbTables::TBL_BOOKINGS . '.partner_balance', 0); $where->equalTo(DbTables::TBL_BOOKINGS . '.partner_settled', 0); $where->equalTo(DbTables::TBL_BOOKINGS . '.payment_settled', 1); $where->expression(DbTables::TBL_BOOKINGS . '.apartment_id_assigned not in (?, ?)', [Constants::TEST_APARTMENT_1, Constants::TEST_APARTMENT_2]); if (is_array($dist) && count($dist)) { $apartmentIdList = []; $apartelIdList = []; $fiscalList = []; foreach ($dist as $distItem) { list($entityType, $entityId) = explode('_', $distItem); // 1 for apartment, 2 for apartel (as a convention) if ($entityType == Distribution::TYPE_APARTMENT) { array_push($apartmentIdList, $entityId); } elseif ($entityType == Distribution::TYPE_APARTEL) { array_push($apartelIdList, $entityId); } else { array_push($fiscalList, $entityId); } } if (!count($apartmentIdList) && !count($apartelIdList) && !count($fiscalList)) { throw new \RuntimeException('Entity not selected.'); } $wrapperPredicate = new Predicate(); $checkAddOr = false; // apartment if (count($apartmentIdList)) { $predicate = new Predicate(); $predicate->in(DbTables::TBL_BOOKINGS . '.apartment_id_origin', $apartmentIdList)->lessThan(DbTables::TBL_BOOKINGS . '.apartel_id', 1); $wrapperPredicate->addPredicate($predicate); $checkAddOr = true; } // apartel if (count($apartelIdList)) { if ($checkAddOr) { $wrapperPredicate->or; } $checkAddOr = true; $wrapperPredicate->in(DbTables::TBL_BOOKINGS . '.apartel_id', $apartelIdList); } // fiscal if (count($fiscalList)) { if ($checkAddOr) { $wrapperPredicate->or; } $wrapperPredicate->in('fiscal.id', $fiscalList); } $where->addPredicate($wrapperPredicate); } } $reservations = $bookingDao->getPayToPartnerReservationsByFilter($where); $reservationList = []; if ($reservations->count()) { foreach ($reservations as $reservation) { array_push($reservationList, ['id' => $reservation->getReservationId(), 'res_number' => $reservation->getReservationNumber(), 'status' => $reservation->getStatus(), 'apartel_id' => $reservation->getApartelId(), 'booking_date' => $reservation->getBookingDate(), 'departure_date' => $reservation->getDepartureDate(), 'apartment_id' => $reservation->getApartmentId(), 'apartment_name' => $reservation->getApartmentName(), 'partner_id' => $reservation->getPartnerId(), 'partner_name' => $reservation->getPartnerName(), 'guest_balance' => $reservation->getGuestBalance(), 'partner_balance' => $reservation->getPartnerBalance(), 'symbol' => $reservation->getSymbol(), 'currency_id' => $reservation->getCurrencyId()]); } } return $reservationList; }
<?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);
/** * @param array $fieldMap * * @return Predicate */ public function filter($fieldMap = array()) { if (empty($this->filter['filters'])) { return array(); } $where = new Predicate(); $where->addPredicate($this->parseFilters($this->filter, $fieldMap)); return $where; }
/** * @testdox Unit test: Test literal() is chainable, returns proper values, and is backwards compatible with 2.0.* */ public function testLiteral() { $predicate = new Predicate(); // is chainable $this->assertSame($predicate, $predicate->literal('foo = bar')); // with parameter $this->assertEquals(array(array('foo = bar', array(), array())), $predicate->getExpressionData()); // test literal() is backwards-compatible, and works with with parameters $predicate = new Predicate(); $predicate->expression('foo = ?', 'bar'); // with parameter $this->assertEquals(array(array('foo = %s', array('bar'), array(Expression::TYPE_VALUE))), $predicate->getExpressionData()); // test literal() is backwards-compatible, and works with with parameters, even 0 which tests as false $predicate = new Predicate(); $predicate->expression('foo = ?', 0); // with parameter $this->assertEquals(array(array('foo = %s', array(0), array(Expression::TYPE_VALUE))), $predicate->getExpressionData()); }
/** * Create predicate object * * @param string|array $predicate * @param string $combination * * @return Predicate\Predicate */ public function create($predicate, $combination = null) { $combination = $combination ? strtoupper($combination) : null; //$predicates = $this->canonize($predicate); //$result = new Predicate\Predicate($predicates, $combination); $result = new Predicate\Predicate(); $result->addPredicates($predicate, $combination); return $result; }
/** * 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; }
/** * 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; }