PHP Where::greaterThan方法代码示例

 public function getUsersAndAbove(bool $paginated, $name = '', $roles = [])
 {
     $select = new Select('account');
     $where = new Where();
     if ($roles) {
         $sub = $where->nest();
         for ($i = 0; $i < count($roles); $i++) {
             $sub->equalTo('role', $roles[$i]);
             if ($i < count($roles) - 1) {
                 $sub->or;
             }
         }
         $sub->unnest();
     } else {
         $where->greaterThan('role', '0');
         $where->lessThan('role', '32');
     }
     if ($name) {
         $where->like('name', '%' . $name . '%');
     }
     $select->where($where)->order('name ASC');
     if ($paginated) {
         $resultSetPrototype = new ResultSet();
         $resultSetPrototype->setArrayObjectPrototype(new Account());
         $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
         return new Paginator($paginatorAdapter);
     }
     return $this->tableGateway->select($select);
 }

 /**
  * Gets all events within the specified datetime interval.
  *
  * Events are ordered by start date and time.
  *
  * @param \DateTime $dateTimeStart
  * @param \DateTime $dateTimeEnd
  * @param int $limit
  * @param int $offset
  * @param boolean $loadMeta
  * @return array
  */
 public function getInRange(\DateTime $dateTimeStart, \DateTime $dateTimeEnd, $limit = null, $offset = null, $loadMeta = true)
 {
     $where = new Where();
     $where->greaterThan('datetime_end', $dateTimeStart->format('Y-m-d H:i:s'));
     $where->and;
     $where->lessThan('datetime_start', $dateTimeEnd->format('Y-m-d H:i:s'));
     return $this->getBy($where, 'datetime_start ASC', $limit, $offset, $loadMeta);
 }

 /**
  * @return \DDD\Domain\Warehouse\Assets\Consumable []
  */
 public function getAssetsAwaitingApproval()
 {
     $result = $this->fetchAll(function (Select $select) {
         $where = new Where();
         $where->greaterThan($this->getTable() . '.quantity_change', 0);
         $select->columns(['id', 'category_id', 'location_entity_id', 'location_entity_type', 'location_name' => new Expression(" (CASE " . $this->getTable() . ".location_entity_type " . "WHEN " . AssetService::ENTITY_TYPE_APARTMENT . " THEN apartments.name " . "WHEN " . AssetService::ENTITY_TYPE_OFFICE . " THEN offices.name " . "WHEN " . AssetService::ENTITY_TYPE_BUILDING . " THEN apartment_groups.name " . "WHEN " . AssetService::ENTITY_TYPE_STORAGE . " THEN storages.name " . " END)"), 'quantity_change', 'shipment_status'])->join(['cons' => DbTables::TBL_ASSETS_CONSUMABLE], $this->getTable() . '.category_id = cons.category_id', ['asset_id' => 'id'], Select::JOIN_INNER)->join(['categories' => DbTables::TBL_ASSET_CATEGORIES], $this->getTable() . '.category_id = categories.id', ['category_name' => 'name'], Select::JOIN_INNER)->join(['users' => DbTables::TBL_BACKOFFICE_USERS], 'cons.last_updated_by_id = users.id', ['firstname_last_updated' => 'firstname', 'lastname_last_updated' => 'lastname'], Select::JOIN_LEFT)->join(['apartments' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.location_entity_id = apartments.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_APARTMENT), [], Select::JOIN_LEFT)->join(['offices' => DbTables::TBL_OFFICES], new Expression($this->getTable() . '.location_entity_id = offices.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_OFFICE), [], Select::JOIN_LEFT)->join(['apartment_groups' => DbTables::TBL_APARTMENT_GROUPS], new Expression($this->getTable() . '.location_entity_id = apartment_groups.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_BUILDING), [], Select::JOIN_LEFT)->join(['storages' => DbTables::TBL_WM_STORAGE], new Expression($this->getTable() . '.location_entity_id = storages.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_STORAGE), [], Select::JOIN_LEFT)->where($where);
     });
     return $result;
 }

 public function moveAfter($nodeMoveID, $nodeSelectionID)
 {
     // ========================= Detach branch =========================
     $totalNode = $this->detachBranch($nodeMoveID);
     $nodeSelectionInfo = $this->getNodeInfo(array('id' => $nodeSelectionID));
     $nodeMoveInfo = $this->getNodeInfo(array('id' => $nodeMoveID));
     // ========================= Node on tree (LEFT) ========================= data + where -
     $data = array('left' => new Expression('`left` + ?', array($totalNode * 2)));
     $where = new Where();
     $where->greaterThan('left', $nodeSelectionInfo->right);
     $where->greaterThan('right', 0);
     $this->tableGateway->update($data, $where);
     // ========================= Node on tree (RIGHT) ========================= data + where -
     $data = array('right' => new Expression('`right` + ?', array($totalNode * 2)));
     $where = new Where();
     $where->greaterThan('right', $nodeSelectionInfo->right);
     $this->tableGateway->update($data, $where);
     // ========================= Node on branch (LEVEL) ========================= data - where +
     $where = new Where();
     $where->lessThanOrEqualTo('right', 0);
     $data = array('level' => new Expression('`level` + ?', array($nodeSelectionInfo->level - $nodeMoveInfo->level)));
     $this->tableGateway->update($data, $where);
     // ========================= Node on branch (LEFT) ========================= data - where +
     $data = array('left' => new Expression('`left` + ?', array($nodeSelectionInfo->right + 1)));
     $this->tableGateway->update($data, $where);
     // ========================= Node on branch (RIGHT) ========================= data - where +
     $data = array('right' => new Expression('`right` + ?', array($nodeSelectionInfo->right + $totalNode * 2)));
     $this->tableGateway->update($data, $where);
     // ========================= Node move (PARENT) ========================= data + where +
     $data = array('parent' => $nodeSelectionInfo->parent);
     $this->tableGateway->update($data, array('id' => $nodeMoveInfo->id));
 }

 /**
  * Updates left and right values of tree
  *
  * @param $lft_rgt
  * @param string $operator
  * @param int $offset
  * @return array
  * @internal param int $left_rgt
  */
 protected function updateTree($lft_rgt, $operator, $offset)
 {
     $lft = new Where();
     $rgt = new Where();
     $lftUpdate = $this->update([self::COLUMN_LEFT => new Expression(self::COLUMN_LEFT . $operator . $offset)], $lft->greaterThan(self::COLUMN_LEFT, $lft_rgt));
     $rgtUpdate = $this->update([self::COLUMN_RIGHT => new Expression(self::COLUMN_RIGHT . $operator . $offset)], $rgt->greaterThan(self::COLUMN_RIGHT, $lft_rgt));
     return [$lftUpdate, $rgtUpdate];
 }

 /**
  * @param  DatagridFilter $filter
  * @throws \Exception
  */
 public function applyFilter(DatagridFilter $filter)
 {
     $select = $this->getSelect();
     $adapter = $this->getSql()->getAdapter();
     $qi = function ($name) use($adapter) {
         return $adapter->getPlatform()->quoteIdentifier($name);
     };
     $col = $filter->getColumn();
     if (!$col instanceof Column\Select) {
         throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId());
     }
     $colString = $col->getSelectPart1();
     if ($col->getSelectPart2() != '') {
         $colString .= '.' . $col->getSelectPart2();
     }
     if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) {
         $colString = sprintf($col->getFilterSelectExpression(), $colString);
     }
     $values = $filter->getValues();
     $wheres = [];
     foreach ($values as $value) {
         $where = new Where();
         switch ($filter->getOperator()) {
             case DatagridFilter::LIKE:
                 $wheres[] = $where->like($colString, '%' . $value . '%');
                 break;
             case DatagridFilter::LIKE_LEFT:
                 $wheres[] = $where->like($colString, '%' . $value);
                 break;
             case DatagridFilter::LIKE_RIGHT:
                 $wheres[] = $where->like($colString, $value . '%');
                 break;
             case DatagridFilter::NOT_LIKE:
                 $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value . '%']);
                 break;
             case DatagridFilter::NOT_LIKE_LEFT:
                 $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]);
                 break;
             case DatagridFilter::NOT_LIKE_RIGHT:
                 $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$value . '%']);
                 break;
             case DatagridFilter::EQUAL:
                 $wheres[] = $where->equalTo($colString, $value);
                 break;
             case DatagridFilter::NOT_EQUAL:
                 $wheres[] = $where->notEqualTo($colString, $value);
                 break;
             case DatagridFilter::GREATER_EQUAL:
                 $wheres[] = $where->greaterThanOrEqualTo($colString, $value);
                 break;
             case DatagridFilter::GREATER:
                 $wheres[] = $where->greaterThan($colString, $value);
                 break;
             case DatagridFilter::LESS_EQUAL:
                 $wheres[] = $where->lessThanOrEqualTo($colString, $value);
                 break;
             case DatagridFilter::LESS:
                 $wheres[] = $where->lessThan($colString, $value);
                 break;
             case DatagridFilter::BETWEEN:
                 $wheres[] = $where->between($colString, $values[0], $values[1]);
                 break 2;
             default:
                 throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
                 break;
         }
     }
     if (!empty($wheres)) {
         $set = new PredicateSet($wheres, PredicateSet::OP_OR);
         $select->where->andPredicate($set);
     }
 }

 /**
  * @return int
  *
  * @author Tigran Petrosyan
  */
 public function getNotChargedApartelReservationsCount()
 {
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $result = $this->fetchOne(function (Select $select) {
         $select->join(['apartment_groups' => DbTables::TBL_APARTMENT_GROUPS], $this->getTable() . '.apartel_id = apartment_groups.id', []);
         $select->join(['charges' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charges.reservation_id AND charges.status=0'), [], Select::JOIN_LEFT);
         $where = new Where();
         $where->greaterThan($this->getTable() . '.apartel_id', 0);
         $where->equalTo($this->getTable() . '.status', \DDD\Service\Booking::BOOKING_STATUS_BOOKED);
         $where->isNull('charges.id');
         $where->notEqualTo($this->getTable() . '.check_charged', 1);
         $where->expression($this->getTable() . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
         $where->greaterThanOrEqualTo($this->getTable() . '.date_from', date('Y-m-d'));
         $where->expression('(' . $this->getTable() . '.is_refundable = 2 or ' . $this->getTable() . '.refundable_before_hours >= TIMESTAMPDIFF(HOUR,NOW(),date_from))', []);
         $select->columns(['count' => new Expression('COUNT(*)')]);
         $select->where($where);
     });
     return $result['count'];
 }

 /**
  * @param  DatagridFilter            $filter
  * @throws \InvalidArgumentException
  */
 public function applyFilter(DatagridFilter $filter)
 {
     $select = $this->getSelect();
     $adapter = $this->getSql()->getAdapter();
     $qi = function ($name) use($adapter) {
         return $adapter->getPlatform()->quoteIdentifier($name);
     };
     $column = $filter->getColumn();
     $colString = $column->getSelectPart1();
     if ($column->getSelectPart2() != '') {
         $colString .= '.' . $column->getSelectPart2();
     }
     if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
         $colString = sprintf($column->getFilterSelectExpression(), $colString);
     }
     $values = $filter->getValues();
     $filterSelectOptions = $column->getFilterSelectOptions();
     $wheres = array();
     if ($filter->getColumn()->getType() instanceof Column\Type\DateTime && $filter->getColumn()->getType()->isDaterangePickerEnabled() === true) {
         $where = new Where();
         $wheres[] = $where->between($colString, $values[0], $values[1]);
         if (count($wheres) > 0) {
             $set = new PredicateSet($wheres, PredicateSet::OP_AND);
             $select->where->andPredicate($set);
         }
     } else {
         foreach ($values as $value) {
             $where = new Where();
             switch ($filter->getOperator()) {
                 case DatagridFilter::LIKE:
                     $wheres[] = $where->like($colString, '%' . $value . '%');
                     break;
                 case DatagridFilter::LIKE_LEFT:
                     $wheres[] = $where->like($colString, '%' . $value);
                     break;
                 case DatagridFilter::LIKE_RIGHT:
                     $wheres[] = $where->like($colString, $value . '%');
                     break;
                 case DatagridFilter::NOT_LIKE:
                     $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%'));
                     break;
                 case DatagridFilter::NOT_LIKE_LEFT:
                     $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value));
                     break;
                 case DatagridFilter::NOT_LIKE_RIGHT:
                     $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%'));
                     break;
                 case DatagridFilter::EQUAL:
                     $wheres[] = $where->equalTo($colString, $value);
                     break;
                 case DatagridFilter::NOT_EQUAL:
                     $wheres[] = $where->notEqualTo($colString, $value);
                     break;
                 case DatagridFilter::GREATER_EQUAL:
                     $wheres[] = $where->greaterThanOrEqualTo($colString, $value);
                     break;
                 case DatagridFilter::GREATER:
                     $wheres[] = $where->greaterThan($colString, $value);
                     break;
                 case DatagridFilter::LESS_EQUAL:
                     $wheres[] = $where->lessThanOrEqualTo($colString, $value);
                     break;
                 case DatagridFilter::LESS:
                     $wheres[] = $where->lessThan($colString, $value);
                     break;
                 case DatagridFilter::BETWEEN:
                     $wheres[] = $where->between($colString, $values[0], $values[1]);
                     break;
                 case DatagridFilter::IN:
                     $wheres[] = $where->in($colString, (array) $value);
                     break;
                 case DatagridFilter::NOT_IN:
                     $wheres[] = $where->notin($colString, (array) $value);
                     break;
                 default:
                     throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
                     break;
             }
         }
         if (count($wheres) > 0) {
             $set = new PredicateSet($wheres, PredicateSet::OP_OR);
             $select->where->andPredicate($set);
         }
     }
 }

 /**
  * @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;
 }