PHP Where::between方法代码示例

 public function findCourses($free, $disability, $child_care, $lat, $lng)
 {
     $sql = new Sql($this->tableGateway->getAdapter());
     $select = $sql->select();
     $select->from(array("co" => "course"))->join(array("cc" => "course_centre"), "co.id = cc.course_id")->join(array("ce" => "centre"), "ce.id = cc.centre_id", array("centre_id" => "id", "centreName" => "name", "location" => new Expression("AsWKT(location)"), "post_code", "address", "buses", "tube", "accebility", "accebility_condition", "other_information"));
     //especificar las columnas que queremos en el resultados de las consultas.
     $select->columns(array("id", "courseName" => "name", "class_type", "levels", "who_join", "how_join", "when_join", "how_long", "cost_free", "cost_condition", "times", "documentation_required", "contact_phone", "contact_email", "contact_person", "child_care", "child_condition", "organization_id", "other_information"));
     $where = new Where();
     //TODO terminar de poner prefijos a las tablas
     if (isset($free)) {
         if ($free == 'Yes') {
             $where->in("co.cost_free", array("y", "c"));
         } else {
             if ($free == 'No') {
                 $where->equalTo("co.cost_free", "n");
             }
         }
     }
     if (isset($disability)) {
         if ($disability == 'Yes') {
             $where->in("ce.accebility", array("y", "c"));
         } else {
             if ($disability == 'No') {
                 $where->equalTo("ce.accebility", "n");
             }
         }
     }
     if (isset($child_care)) {
         if ($child_care == 'Yes') {
             $where->in("co.child_care", array("y", "c"));
         } else {
             if ($child_care == 'No') {
                 $where->equalTo("co.child_care", "n");
             }
         }
     }
     //hacemos busquedas en 3km a la redonda, se puede convertir en un parametro
     $R = 6371;
     //radio de la tierra en km
     if (isset($lat) && isset($lng)) {
         $maxLat = $lat + rad2deg(1.5 / $R);
         $minLat = $lat - rad2deg(1.5 / $R);
         $maxLng = $lng + rad2deg(1.5 / $R / cos(deg2rad($lat)));
         $minLng = $lng - rad2deg(1.5 / $R / cos(deg2rad($lat)));
         $where->between(new Expression("X(ce.location)"), $minLat, $maxLat);
         $where->between(new Expression("y(ce.location)"), $minLng, $maxLng);
     }
     //TODO terminar con el resto de los filtros de busqueda
     $select->where($where);
     $statement = $sql->prepareStatementForSqlObject($select);
     $rowset = $statement->execute();
     return $rowset;
 }

 /**
  * Construct Where object from query parameters
  *
  * @param array $filterParams
  * @param bool $testApartments
  * @return Where
  */
 public function constructWhereFromFilterParams($filterParams, $testApartments = true)
 {
     $auth = $this->getServiceLocator()->get('library_backoffice_auth');
     $hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING);
     $where = new Where();
     $table = DbTables::TBL_APARTMENTS;
     $productStatusGroups = Objects::getProductStatusGroups();
     if (!$testApartments || !$hasDevTestRole) {
         $where->expression($table . '.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
     }
     if (isset($filterParams["status"]) && $filterParams["status"] != '0') {
         $statusGroup = $productStatusGroups[$filterParams["status"]];
         $where->in($table . ".status", $statusGroup);
     }
     if (isset($filterParams["building_id"]) && $filterParams["building_id"] != '0') {
         $where->expression($table . '.id IN (SELECT `apartment_id` FROM ' . DbTables::TBL_APARTMENT_GROUP_ITEMS . ' JOIN ' . DbTables::TBL_APARTMENT_GROUPS . ' ON `apartment_group_id` = ' . DbTables::TBL_APARTMENT_GROUPS . '.id WHERE ' . DbTables::TBL_APARTMENT_GROUPS . '.id = ' . $filterParams['building_id'] . ' ) ', []);
     }
     if (isset($filterParams["address"]) && $filterParams["address"] != '') {
         $addressQuery = $filterParams["address"];
         $nestedWhere = new Where();
         $nestedWhere->like($table . '.name', '%' . $addressQuery . '%')->or->like('det1.name', '%' . $addressQuery . '%')->or->like('det2.name', '%' . $addressQuery . '%')->or->like($table . '.address', '%' . $addressQuery . '%')->or->like($table . '.unit_number', '%' . $addressQuery . '%')->or->like($table . '.postal_code', '%' . $addressQuery . '%');
         $where->addPredicate($nestedWhere);
     }
     if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') {
         $createdDate = explode(' - ', $filterParams['createdDate']);
         $where->between($table . '.create_date', $createdDate['0'], $createdDate['1']);
     }
     return $where;
 }

 public function fetchAllByIdEmpleadoAndPeriodo($id_empleado, $inicio, $fin)
 {
     $sql = new Sql($this->adapter);
     $select = $sql->select();
     $select->from($this->table)->join('cat_asistencia', 'cat_asistencia.id = tab_asistencia.id_cat_asistencia', array('asistencia' => 'nombre'));
     $where1 = new Where();
     $where1->between('tab_asistencia.fecha', $inicio, $fin);
     $select->where($where1);
     $where2 = new Where();
     $where2->equalTo('tab_asistencia.id_empleado', $id_empleado);
     $select->where(array($where2));
     $select->order(array('fecha'));
     //        echo $select->getSqlString();
     $statement = $sql->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     $entities = array();
     foreach ($resultSet as $row) {
         $entity = new Entity\Asistencia(array('id' => $row["id"], 'id_empleado' => $row["id_empleado"], 'id_cat_asistencia' => $row["id_cat_asistencia"], 'fecha' => $row["fecha"], 'asistencia' => $row["asistencia"]));
         $entities[] = $entity->toArray();
     }
     return $entities;
 }

 public function detachBranch($nodeMoveID, $options = null)
 {
     $moveInfo = $this->getNodeInfo(array('id' => $nodeMoveID));
     $moveLeft = $moveInfo->left;
     $moveRight = $moveInfo->right;
     $totalNode = ($moveRight - $moveLeft + 1) / 2;
     // ================================== Node on branch ==================================
     if ($options == null) {
         $data = array('left' => new Expression('`left` - ?', array($moveLeft)), 'right' => new Expression('`right` - ?', array($moveRight)));
         $where = new Where();
         $where->between('left', $moveInfo->left, $moveInfo->right);
         $this->tableGateway->update($data, $where);
     }
     if ($options['task'] == 'remove-node') {
         $where = new Where();
         $where->between('left', $moveInfo->left, $moveInfo->right);
         $this->tableGateway->delete($where);
     }
     // ================================== Node on tree (LEFT) ==================================
     $data = array('left' => new Expression('`left` - ?', array($totalNode * 2)));
     $where = new Where();
     $where->greaterThan('left', $moveRight);
     $this->tableGateway->update($data, $where);
     // ================================== Node on tree (RIGHT) ==================================
     $data = array('right' => new Expression('`right` - ?', array($totalNode * 2)));
     $where = new Where();
     $where->greaterThan('right', $moveInfo->right);
     $this->tableGateway->update($data, $where);
     return $totalNode;
 }

 /**
  * Deletes a row from tree.
  *
  * @param int|array $where
  * @param string $table
  * @return int number of affected rows
  */
 public function delete($where, $table = null)
 {
     if (is_array($where)) {
         $pk = $where[$this->getPrimaryKey()];
     } else {
         $pk = (int) $where;
     }
     $row = $this->getPosition($pk);
     $where = new Where();
     $where->between(self::COLUMN_LEFT, $row->getLft(), $row->getRgt());
     $result = parent::delete($where, $table);
     if ($result) {
         $this->updateTree($row->getRgt(), '-', $row->width());
     }
     return $result;
 }

 /**
  *
  * @param array $filterParams
  * @param bool $testApartments
  * @return Where
  */
 public function constructWhereFromFilterParams($filterParams, $securityLevels = [])
 {
     /* @var $auth \Library\Authentication\BackofficeAuthenticationService */
     $auth = $this->getServiceLocator()->get('library_backoffice_auth');
     $hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING);
     $documentsTable = DbTables::TBL_DOCUMENTS;
     $where = new Where();
     if (!$hasDevTestRole) {
         $where->expression('apartment.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
     }
     if (isset($filterParams["validation-range"]) && $filterParams["validation-range"] != '') {
         $tempDatesArray = explode(' - ', $filterParams['validation-range']);
         $validFrom = $tempDatesArray[0];
         $validTo = $tempDatesArray[1];
         $where->expression('DATE(' . $documentsTable . '.valid_from) >= DATE("' . $validFrom . '") ' . 'AND DATE(' . $documentsTable . '.valid_to) <= DATE("' . $validTo . '") ', []);
     }
     if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') {
         $createdDate = explode(' - ', $filterParams['createdDate']);
         $where->between($documentsTable . '.created_date', $createdDate['0'] . ' 00:00:00', $createdDate['1'] . ' 23:59:59');
     }
     if (!empty($filterParams['supplier_id']) && $filterParams['supplier_id'] != '78') {
         $where->equalTo($documentsTable . '.supplier_id', $filterParams['supplier_id']);
     }
     if (!empty($filterParams['document_type'])) {
         $where->equalTo($documentsTable . '.type_id', $filterParams['document_type']);
     }
     if (isset($filterParams['legal_entity_id']) && $filterParams['legal_entity_id'] != 0) {
         $where->equalTo($documentsTable . '.legal_entity_id', $filterParams['legal_entity_id']);
     }
     if (isset($filterParams['signatory_id']) && $filterParams['signatory_id'] != 0) {
         $where->equalTo($documentsTable . '.signatory_id', $filterParams['signatory_id']);
     }
     if (!empty($filterParams['author_id'])) {
         $where->equalTo($documentsTable . '.created_by', $filterParams['author_id']);
     }
     if (!empty($filterParams['account_number'])) {
         $where->like($documentsTable . '.account_number', '%' . $filterParams['account_number'] . '%');
     }
     if (!empty($filterParams['entity_id'])) {
         $where->equalTo($documentsTable . '.entity_id', $filterParams['entity_id']);
     }
     if (!empty($filterParams['entity_type'])) {
         $where->equalTo($documentsTable . '.entity_type', $filterParams['entity_type']);
     }
     if (!empty($filterParams['account_holder'])) {
         $where->like($documentsTable . '.account_holder', '%' . $filterParams['account_holder'] . '%');
     }
     if (!empty($filterParams['has_attachment'])) {
         switch ($filterParams['has_attachment']) {
             case 1:
                 $where->isNotNull($documentsTable . '.attachment')->notEqualTo($documentsTable . '.attachment', '');
                 break;
             case 2:
                 $where->NEST->isNull($documentsTable . '.attachment')->OR->equalTo($documentsTable . '.attachment', '')->UNNEST;
                 break;
         }
     }
     if (isset($filterParams['has_url']) && !empty($filterParams['has_url'])) {
         switch ($filterParams['has_url']) {
             case 1:
                 $where->notEqualTo($documentsTable . '.url', '');
                 break;
             case 2:
                 $where->equalTo($documentsTable . '.url', '');
                 break;
         }
     }
     $hasSecurityAccess = $auth->hasRole(Roles::ROLE_DOCUMENTS_MANAGEMENT_GLOBAL);
     if (isset($securityLevels[0]) && !$hasSecurityAccess) {
         $where->in($documentsTable . '.security_level', $securityLevels);
     }
     return $where;
 }

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

 /**
  * @param $params
  * @return Where
  * @throws \Exception
  */
 private function constructWhereForItemSearchForDatatable($params)
 {
     $where = new Where();
     $itemTable = DbTables::TBL_EXPENSE_ITEM;
     if (!empty($params['item-search-supplier'])) {
         $where->equalTo("{$itemTable}.account_id", $params['item-search-supplier']);
     }
     if (!empty($params['creator_id'])) {
         $where->equalTo("{$itemTable}.creator_id", $params['creator_id']);
     }
     if (!empty($params['item-search-creation-date'])) {
         list($creationFrom, $creationTo) = explode(' - ', $params['item-search-creation-date']);
         $creationFrom = date(Constants::DATABASE_DATE_FORMAT, strtotime($creationFrom));
         $creationTo = date(Constants::DATABASE_DATE_FORMAT, strtotime($creationTo));
         $where->expression("date({$itemTable}.date_created) BETWEEN ? AND ?", [$creationFrom, $creationTo]);
     }
     if (!empty($params['item-search-reference'])) {
         $where->like("{$itemTable}.account_reference", "%{$params['item-search-reference']}%");
     }
     if ($params['item-search-amount'] !== '') {
         $where->equalTo("{$itemTable}.amount", $params['item-search-amount']);
     }
     if (!empty($params['item-search-period'])) {
         list($periodFrom, $periodTo) = explode(' - ', $params['item-search-period']);
         $periodFrom = date(Constants::DATABASE_DATE_FORMAT, strtotime($periodFrom));
         $periodTo = date(Constants::DATABASE_DATE_FORMAT, strtotime($periodTo));
         $whereP = new Where();
         $whereP->between("{$itemTable}.period_from", $periodFrom, $periodTo);
         $whereP->or;
         $whereP->between("{$itemTable}.period_to", $periodFrom, $periodTo);
         $where->andPredicate($whereP);
     }
     if (!empty($params['item-search-category'])) {
         list($categoryId, $categoryType) = explode('_', $params['item-search-category']);
         // Conventional: 1 - category, 2 - sub category
         if ($categoryType == 1) {
             $where->equalTo("sub_category.category_id", $categoryId);
             $where->equalTo("sub_category.category_id", $categoryId);
         } elseif ($categoryType == 2) {
             $where->equalTo("{$itemTable}.sub_category_id", $categoryId);
         } else {
             throw new \Exception('Invalid category type.');
         }
     }
     if (!empty($params['item-search-cost-center'])) {
         list($costCenterType, $costCenterId) = explode('_', $params['item-search-cost-center']);
         // Conventional: 1 - apartment, 2 - office section
         if (in_array($costCenterType, [1, 2])) {
             $where->equalTo("cost.cost_center_id", $costCenterId);
             $where->equalTo("cost.cost_center_type", $costCenterType);
         } else {
             throw new \Exception('Invalid cost center type.');
         }
     }
     return $where;
 }

 private function prepareTransactionStatement($data)
 {
     $statement = new Where();
     // Basic configuration
     $types = [];
     foreach ($data['transaction_type'] as $type => $typeValue) {
         if ((int) $typeValue == 1) {
             array_push($types, $type);
         }
     }
     if (count($types)) {
         $statement->in('type', $types);
     }
     // Transaction specific
     if (!empty($data['transaction_date'])) {
         list($dateFrom, $dateTo) = explode(' - ', $data['transaction_date']);
         $statement->between(DbTables::TBL_CHARGE_TRANSACTION . '.date', $dateFrom, date('Y-m-d', strtotime('+1 day', strtotime($dateTo))));
     }
     // Reservation specific
     if ($data['status']) {
         if ($data['status'] == 111) {
             // Canceled
             $statement->notEqualTo(DbTables::TBL_BOOKINGS . '.status', Booking::BOOKING_STATUS_BOOKED);
         } else {
             $statement->equalTo(DbTables::TBL_BOOKINGS . '.status', $data['status']);
         }
     }
     if ($data['payment_model']) {
         $statement->equalTo(DbTables::TBL_BOOKINGS . '.model', $data['payment_model']);
     }
     if ($data['partner_id']) {
         $statement->equalTo(DbTables::TBL_BOOKINGS . '.partner_id', $data['partner_id']);
     }
     if ($data['no_collection'] != -1) {
         $statement->equalTo(DbTables::TBL_BOOKINGS . '.no_collection', $data['no_collection']);
     }
     if (!empty($data['booking_date'])) {
         list($dateFrom, $dateTo) = explode(' - ', $data['booking_date']);
         $statement->between(DbTables::TBL_BOOKINGS . '.timestamp', $dateFrom, date('Y-m-d', strtotime('+1 day', strtotime($dateTo))));
     }
     if (!empty($data['arrival_date'])) {
         list($dateFrom, $dateTo) = explode(' - ', $data['arrival_date']);
         $statement->between(DbTables::TBL_BOOKINGS . '.date_from', $dateFrom, $dateTo);
     }
     if (!empty($data['departure_date'])) {
         list($dateFrom, $dateTo) = explode(' - ', $data['departure_date']);
         $statement->between(DbTables::TBL_BOOKINGS . '.date_to', $dateFrom, $dateTo);
     }
     if (!empty($data['product_id'])) {
         $statement->equalTo(DbTables::TBL_BOOKINGS . '.apartment_id_origin', $data['product_id']);
     }
     if (!empty($data['assigned_product_id'])) {
         $statement->equalTo(DbTables::TBL_BOOKINGS . '.apartment_id_assigned', $data['assigned_product_id']);
     }
     if ($data['city']) {
         $statement->equalTo(DbTables::TBL_BOOKINGS . '.acc_city_id', $data['city']);
     }
     if ($data['psp']) {
         $statement->equalTo(DbTables::TBL_CHARGE_TRANSACTION . '.psp_id', $data['psp']);
     }
     // Apartment Group specific
     if ($data['group']) {
         $statement->equalTo(DbTables::TBL_APARTMENT_GROUP_ITEMS . '.apartment_group_id', $data['group']);
     }
     // Transaction Status
     if ($data['transaction_status']) {
         $statement->equalTo(DbTables::TBL_CHARGE_TRANSACTION . '.status', $data['transaction_status']);
     }
     return $statement;
 }

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