PHP Where::lessThan方法代码示例

 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);
 }

 public function getAvailableSpotsForApartment($apartmentId, $dateFrom, $dateTo)
 {
     $prototype = $this->resultSetPrototype->getArrayObjectPrototype();
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $result = $this->fetchAll(function (Select $select) use($apartmentId, $dateFrom, $dateTo) {
         $where = new Where();
         $where->lessThan('parking_inventory.date', $dateTo)->greaterThanOrEqualTo('parking_inventory.date', $dateFrom)->equalTo('parking_inventory.availability', 1)->equalTo($this->getTable() . '.apartment_id', $apartmentId);
         $select->columns([])->join(['apartments' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id = apartments.id', [], Select::JOIN_LEFT)->join(['parking_building' => DbTables::TBL_BUILDING_LOTS], 'apartments.building_section_id = parking_building.building_section_id', [], Select::JOIN_LEFT)->join(['spots' => DbTables::TBL_PARKING_SPOTS], 'parking_building.lot_id = spots.lot_id', ['spot_id' => 'id', 'spot_unit' => 'unit', 'price'], Select::JOIN_LEFT)->join(['lots' => DbTables::TBL_PARKING_LOTS], 'parking_building.lot_id = lots.id', ['lot_name' => 'name'], Select::JOIN_LEFT)->join(['parking_inventory' => DbTables::TBL_PARKING_INVENTORY], 'parking_inventory.spot_id = spots.id', [], Select::JOIN_INNER)->where($where)->group('spots.id')->having('count(parking_inventory.id) = (DATEDIFF("' . $dateTo . '", "' . $dateFrom . '"))');
     });
     $this->resultSetPrototype->setArrayObjectPrototype($prototype);
     return $result;
 }

 /**
  * Garbage Collection
  * Only delete sessions that have expired.
  *
  * @param int $maxlifetime
  * @return true
  */
 public function gc($maxlifetime)
 {
     $platform = $this->tableGateway->getAdapter()->getPlatform();
     $where = new Where();
     $where->lessThan($this->options->getModifiedColumn(), new Expression('(' . time() . ' - ' . $platform->quoteIdentifier($this->options->getLifetimeColumn()) . ')'));
     $rows = $this->tableGateway->select($where);
     $ids = [];
     /* @var \UthandoSessionManager\Model\Session $row */
     foreach ($rows as $row) {
         $ids[] = $row->{$this->options->getIdColumn()};
     }
     if (count($ids) > 0) {
         $where = new Where();
         $result = (bool) $this->tableGateway->delete($where->in($this->options->getIdColumn(), $ids));
     } else {
         $result = false;
     }
     return $result;
 }

 /**
  * @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
  */
 public function removeDateExpiredRows()
 {
     $where = new Where();
     $where->lessThan($this->getTable() . '.date', date('Y-m-d'));
     return $this->delete($where);
 }

 /**
  * @param $apartmentGroupId
  * @param $from
  * @param $to
  * @param $roomCount
  * @param $roomType
  * @return ResultSet
  */
 public function getApartmentGroupOverbookingsForDateRange($apartmentGroupId, $from, $to, $roomCount, $roomType)
 {
     $to = date('Y-m-j', strtotime($to . ' +1 days'));
     $prototype = $this->resultSetPrototype->getArrayObjectPrototype();
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $result = $this->fetchAll(function (Select $select) use($apartmentGroupId, $from, $to, $roomCount, $roomType) {
         $where = new Where();
         $where->lessThan($this->getTable() . '.date_from', $to)->greaterThan($this->getTable() . '.date_to', $from)->equalTo($this->getTable() . '.status', BookingService::BOOKING_STATUS_BOOKED)->equalTo($this->getTable() . '.overbooking_status', ReservationTicketService::OVERBOOKING_STATUS_OVERBOOKED);
         if ($roomCount != -1) {
             $where->equalTo('A.bedroom_count', $roomCount);
         }
         $select->columns(['channel_res_id', 'res_number', 'ki_viewed', 'date_from', 'date_to', 'apartel_id', 'apartment_id' => 'apartment_id_assigned', 'occupancy', 'guest_balance', 'is_locked' => 'locked', 'res_length' => new Expression('datediff(date_to, date_from)'), 'draw_length' => new Expression('datediff(least(date_to, "' . $to . '"), greatest(date_from, "' . $from . '"))'), 'draw_start' => new Expression('datediff(greatest(date_from, "' . $from . '"), "' . $from . '")'), 'res_start' => new Expression('datediff(date_from, "' . $from . '")')])->join(['AGI' => DbTables::TBL_APARTMENT_GROUP_ITEMS], new Expression($this->getTable() . '.apartment_id_assigned = AGI.apartment_id AND AGI.apartment_group_id = ' . $apartmentGroupId), [], Select::JOIN_INNER)->join(['A' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = A.id', ['apartment_name' => 'name'], Select::JOIN_INNER);
         if ($roomType > 0) {
             $select->join(['rel_apartment_room_type' => DbTables::TBL_APARTEL_REL_TYPE_APARTMENT], new Expression($this->getTable() . '.apartment_id_assigned = rel_apartment_room_type.apartment_id AND rel_apartment_room_type.apartel_type_id = ' . $roomType), [], Select::JOIN_INNER);
         }
         $select->where($where);
     });
     $this->resultSetPrototype->setArrayObjectPrototype($prototype);
     return $result;
 }

 public function getmanagernames($userRoleId)
 {
     $sql = new Sql($this->getAdapter());
     $select = $sql->select()->from('company_roles')->where(['id' => $userRoleId, 'is_active' => 1, 'is_delete' => 0]);
     $roleDetail = $sql->prepareStatementForSqlObject($select)->execute()->current();
     $where = new Where();
     $sql = new Sql($this->getAdapter());
     $select = $sql->select()->from(['ul' => 'userlist'])->columns(['id', 'username'])->join(['cr' => 'company_roles'], 'cr.id=ul.role_id', 'role_name');
     $where->lessThan('cr.seniority', $roleDetail['seniority']);
     $select->where($where);
     $select->where(['ul.is_active' => 1, 'ul.is_delete' => 0, 'ul.comp_id' => $this->loggedInUserDetails->comp_id, 'cr.is_active' => 1, 'cr.is_delete' => 0])->order('cr.seniority');
     $result = $sql->prepareStatementForSqlObject($select)->execute();
     $dataArray = array();
     if (count($result)) {
         foreach ($result as $managers) {
             $tempArr = [];
             $tempArr['Id'] = $managers['id'];
             $tempArr['Name'] = $managers['username'] . ' (' . $managers['role_name'] . ')';
             $dataArray[] = $tempArr;
         }
     }
     //        echo '<pre>';print_r($dataArray);exit;
     return json_encode($dataArray);
 }

 /**
  * @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 int $rateId
  * @param string $date
  *
  * @return int
  */
 public function deleteAvailabilities($rateId, $date)
 {
     $where = new Where();
     $where->lessThan('date', $date)->and->equalTo('rate_id', $rateId);
     return $this->delete($where);
 }