PHP Where::addPredicate方法代码示例

 /**
  * Create where clause
  *
  * @param  Where|\Closure|string|array $predicate
  * @param  string $combination One of the OP_* constants from Predicate\PredicateSet
  * @return Select
  */
 public function where($predicate, $combination = Predicate\PredicateSet::OP_AND)
 {
     if ($predicate instanceof Where) {
         $this->where = $predicate;
     } elseif ($predicate instanceof \Closure) {
         $predicate($this->where);
     } else {
         if (is_string($predicate)) {
             $predicate = new Predicate\Expression($predicate);
             $this->where->addPredicate($predicate, $combination);
         } elseif (is_array($predicate)) {
             foreach ($predicate as $pkey => $pvalue) {
                 if (is_string($pkey) && strpos($pkey, '?') !== false) {
                     $predicate = new Predicate\Expression($pkey, $pvalue);
                 } elseif (is_string($pkey)) {
                     $predicate = new Predicate\Operator($pkey, Predicate\Operator::OP_EQ, $pvalue);
                 } else {
                     $predicate = new Predicate\Expression($pvalue);
                 }
                 $this->where->addPredicate($predicate, $combination);
             }
         }
     }
     return $this;
 }

 /**
  * Create where clause
  *
  * @param  Where|\Closure|string|array $predicate
  * @param  string $combination One of the OP_* constants from Predicate\PredicateSet
  * @return Select
  */
 public function where($predicate, $combination = Predicate\PredicateSet::OP_AND)
 {
     if (is_null($predicate)) {
         throw new \Zend\Db\Sql\Exception\InvalidArgumentException('Predicate cannot be null');
     }
     if ($predicate instanceof Where) {
         $this->where = $predicate;
     } elseif ($predicate instanceof \Closure) {
         $predicate($this->where);
     } else {
         if (is_string($predicate)) {
             $predicate = new Predicate\Expression($predicate);
             $this->where->addPredicate($predicate, $combination);
         } elseif (is_array($predicate)) {
             foreach ($predicate as $pkey => $pvalue) {
                 if (is_string($pkey) && strpos($pkey, '?') !== false) {
                     $predicate = new Predicate\Expression($pkey, $pvalue);
                 } elseif (is_string($pkey)) {
                     $predicate = new Predicate\Operator($pkey, Predicate\Operator::OP_EQ, $pvalue);
                 } else {
                     $predicate = new Predicate\Expression($pvalue);
                 }
                 $this->where->addPredicate($predicate, $combination);
             }
         }
     }
     return $this;
 }

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

 /**
  * Create where clause
  *
  * @param  Where|\Closure|string|array|Predicate\PredicateInterface $predicate
  * @param  string $combination One of the OP_* constants from Predicate\PredicateSet
  * @throws Exception\InvalidArgumentException
  * @return Select
  */
 public function where($predicate, $combination = Predicate\PredicateSet::OP_AND)
 {
     if ($predicate instanceof Where) {
         $this->where = $predicate;
     } elseif ($predicate instanceof Predicate\PredicateInterface) {
         $this->where->addPredicate($predicate, $combination);
     } elseif ($predicate instanceof \Closure) {
         $predicate($this->where);
     } else {
         if (is_string($predicate)) {
             // String $predicate should be passed as an expression
             $predicate = strpos($predicate, Expression::PLACEHOLDER) !== false ? new Predicate\Expression($predicate) : new Predicate\Literal($predicate);
             $this->where->addPredicate($predicate, $combination);
         } elseif (is_array($predicate)) {
             foreach ($predicate as $pkey => $pvalue) {
                 // loop through predicates
                 if (is_string($pkey) && strpos($pkey, '?') !== false) {
                     // First, process strings that the abstraction replacement character ?
                     // as an Expression predicate
                     $predicate = new Predicate\Expression($pkey, $pvalue);
                 } elseif (is_string($pkey)) {
                     // Otherwise, if still a string, do something intelligent with the PHP type provided
                     if ($pvalue === null) {
                         // map PHP null to SQL IS NULL expression
                         $predicate = new Predicate\IsNull($pkey, $pvalue);
                     } elseif (is_array($pvalue)) {
                         // if the value is an array, assume IN() is desired
                         $predicate = new Predicate\In($pkey, $pvalue);
                     } elseif ($pvalue instanceof Predicate\PredicateInterface) {
                         //
                         throw new Exception\InvalidArgumentException('Using Predicate must not use string keys');
                     } else {
                         // otherwise assume that array('foo' => 'bar') means "foo" = 'bar'
                         $predicate = new Predicate\Operator($pkey, Predicate\Operator::OP_EQ, $pvalue);
                     }
                 } elseif ($pvalue instanceof Predicate\PredicateInterface) {
                     // Predicate type is ok
                     $predicate = $pvalue;
                 } else {
                     // must be an array of expressions (with int-indexed array)
                     $predicate = strpos($pvalue, Expression::PLACEHOLDER) !== false ? new Predicate\Expression($pvalue) : new Predicate\Literal($pvalue);
                 }
                 $this->where->addPredicate($predicate, $combination);
             }
         }
     }
     return $this;
 }

 /**
  * Get all the columns information stored on Directus Columns table
  *
  * @param $tableName
  * @param $params
  *
  * @return array
  */
 protected function getDirectusColumnsInfo($tableName, $params = null)
 {
     $acl = Bootstrap::get('acl');
     $blacklist = $readFieldBlacklist = $acl->getTablePrivilegeList($tableName, $acl::FIELD_READ_BLACKLIST);
     $columnName = isset($params['column_name']) ? $params['column_name'] : -1;
     $select = new Select();
     $select->columns(['id' => 'column_name', 'column_name', 'type' => new Expression('upper(data_type)'), 'char_length' => new Expression('NULL'), 'is_nullable' => new Expression('"NO"'), 'default_value' => new Expression('NULL'), 'comment', 'sort', 'column_type' => new Expression('NULL'), 'ui', 'hidden_list', 'hidden_input', 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(required, 0)')]);
     $select->from('directus_columns');
     $where = new Where();
     $where->equalTo('TABLE_NAME', $tableName)->addPredicate(new In('data_type', ['alias', 'MANYTOMANY', 'ONETOMANY']));
     // ->nest()
     // ->addPredicate(new \Zend\Db\Sql\Predicate\Expression("'$columnName' = '-1'"))
     // ->OR
     // ->equalTo('column_name', $columnName)
     // ->unnest()
     // ->addPredicate(new IsNotNull('data_type'));
     if ($columnName != -1) {
         $where->equalTo('column_name', $columnName);
     }
     if (count($blacklist)) {
         $where->addPredicate(new NotIn('COLUMN_NAME', $blacklist));
     }
     $select->where($where);
     $select->order('sort');
     $sql = new Sql($this->adapter);
     $statement = $sql->prepareStatementForSqlObject($select);
     // $query = $sql->getSqlStringForSqlObject($select, $this->adapter->getPlatform());
     // echo $query;
     $result = $statement->execute();
     $columns = iterator_to_array($result);
     return $columns;
 }

 /**
  * @param string $query
  * @param stdClass $user
  * @param int $limit
  * @return \DDD\Domain\Apartment\FrontierCard[]
  */
 public function getFrontierCardList($query, $user, $limit)
 {
     $this->resultSetPrototype->setArrayObjectPrototype(new \DDD\Domain\Apartment\FrontierCard());
     // Execute for search purposes only
     if (!$query) {
         return false;
     }
     return $this->select(function (Select $select) use($query, $user, $limit) {
         $columns = ['id', 'name', 'unit_number'];
         $where = new Where();
         $nestedWhere = new Where();
         $nestedWhere->like($this->getTable() . '.name', '%' . $query . '%')->or->like($this->getTable() . '.address', '%' . $query . '%')->or->equalTo($this->getTable() . '.unit_number', $query);
         $where->addPredicate($nestedWhere)->in($this->table . '.status', [Objects::PRODUCT_STATUS_LIVEANDSELLIG, Objects::PRODUCT_STATUS_SELLINGNOTSEARCHABLE]);
         $select->columns($columns)->join(['group_item' => DbTables::TBL_APARTMENT_GROUP_ITEMS], $this->getTable() . '.id = group_item.apartment_id', [])->join(['cda' => DbTables::TBL_CONCIERGE_DASHBOARD_ACCESS], new Expression('group_item.apartment_group_id = cda.apartment_group_id AND cda.user_id = ' . $user->id), [])->join(['buildings' => DbTables::TBL_APARTMENT_GROUPS], $this->getTable() . '.building_id = buildings.id', ['building' => 'name'], Select::JOIN_LEFT)->where($where);
         if ($limit) {
             $select->limit($limit);
         }
     });
 }

 public function getAllColumns()
 {
     $selectOne = new Select();
     $selectOne->columns(['column_name' => 'COLUMN_NAME', 'sort' => new Expression('IFNULL(sort, ORDINAL_POSITION)'), 'type' => new Expression('UCASE(C.DATA_TYPE)'), 'char_length' => 'CHARACTER_MAXIMUM_LENGTH', 'is_nullable' => 'IS_NULLABLE', 'default_value' => 'COLUMN_DEFAULT', 'comment' => new Expression('IFNULL(comment, COLUMN_COMMENT)'), 'column_type' => 'COLUMN_TYPE', 'column_key' => 'COLUMN_KEY']);
     $selectOne->from(['C' => new TableIdentifier('COLUMNS', 'INFORMATION_SCHEMA')]);
     $selectOne->join(['D' => 'directus_columns'], 'C.COLUMN_NAME = D.column_name AND C.TABLE_NAME = D.table_name', ['ui', 'hidden_list' => new Expression('IFNULL(hidden_list, 0)'), 'hidden_input' => new Expression('IFNULL(hidden_input, 0)'), 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(D.required, 0)')], $selectOne::JOIN_LEFT);
     $selectOne->join(['T' => new TableIdentifier('TABLES', 'INFORMATION_SCHEMA')], 'C.TABLE_NAME = T.TABLE_NAME', ['table_name' => 'TABLE_NAME'], $selectOne::JOIN_LEFT);
     $selectOne->where(['C.TABLE_SCHEMA' => $this->adapter->getCurrentSchema(), 'T.TABLE_SCHEMA' => $this->adapter->getCurrentSchema(), 'T.TABLE_TYPE' => 'BASE TABLE']);
     $selectTwo = new Select();
     $selectTwo->columns(['column_name', 'sort', 'type' => new Expression('UCASE(data_type)'), 'char_length' => new Expression('NULL'), 'is_nullable' => new Expression('"NO"'), 'default_value' => new Expression('NULL'), 'comment', 'column_type' => new Expression('NULL'), 'column_key' => new Expression('NULL'), 'ui', 'hidden_list', 'hidden_input', 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(required, 0)'), 'table_name']);
     $selectTwo->from('directus_columns');
     $where = new Where();
     $where->addPredicate(new In('data_type', ['alias', 'MANYTOMANY', 'ONETOMANY']));
     $selectTwo->where($where);
     $selectOne->combine($selectTwo, $selectOne::COMBINE_UNION, 'ALL');
     $selectOne->order('table_name');
     $sql = new Sql($this->adapter);
     $statement = $sql->prepareStatementForSqlObject($selectOne);
     return iterator_to_array($statement->execute());
 }

 /**
  * Fetch all or a subset of resources
  *
  * @param  array $params
  * @return ApiProblem|mixed
  */
 public function fetchAll($params = array())
 {
     $where = new Where();
     if (isset($params['nome']) && $params['nome'] != '') {
         $likeSpec = new Where();
         $likeSpec->like('nome', '%' . $params['nome'] . '%');
         $where->addPredicate($likeSpec);
         $likeSpec = new Where();
         $likeSpec->like('sobrenome', '%' . $params['nome'] . '%');
         $where->orPredicate($likeSpec);
     }
     $sort = null;
     if (in_array($params['sort'], array_keys((new ClienteEntity())->getArrayCopy()))) {
         $sort = $params['sort'];
     }
     $dbTableGatewayAdapter = new DbTableGateway($this->tableGateway, $where, $sort);
     return new ClienteCollection($dbTableGatewayAdapter);
 }

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

 public function constructWhereFromFilterParams($filterParams)
 {
     $where = new Where();
     $table = DbTables::TBL_BACKOFFICE_USERS;
     if (isset($filterParams["group"]) && $filterParams["group"] != '0') {
         $where->expression($filterParams["group"] . ' IN (SELECT `group_id` FROM ' . DbTables::TBL_BACKOFFICE_USER_GROUPS . ' WHERE `user_id` = ' . $table . '.id ) ', []);
     }
     if (isset($filterParams["ud"]) && $filterParams["ud"] != '0') {
         $where->expression($filterParams["ud"] . ' IN (SELECT `dashboard_id` FROM ' . DbTables::TBL_BACKOFFICE_USER_DASHBOARDS . ' WHERE `user_id` = ' . $table . '.id ) ', []);
     }
     if (isset($filterParams["team"]) && $filterParams["team"] != '0') {
         $where->expression($filterParams["team"] . ' IN (SELECT `team_id` FROM ' . DbTables::TBL_TEAM_STAFF . ' WHERE `user_id` = ' . $table . '.id AND `type` NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')) ', []);
     }
     if (isset($filterParams['city']) && $filterParams['city'] > 0) {
         $where->EqualTo('city_id', $filterParams['city']);
     }
     if (isset($filterParams["system-user-status"])) {
         if ($filterParams["system-user-status"] == 1) {
             $where->and->EqualTo('system', 0);
         } elseif ($filterParams["system-user-status"] == 2) {
             $where->and->EqualTo('system', 1);
         }
     }
     if (isset($filterParams["external-user-status"])) {
         if ($filterParams["external-user-status"] == 1) {
             $where->and->EqualTo('external', 0);
         } elseif ($filterParams["external-user-status"] == 2) {
             $where->and->EqualTo('external', 1);
         }
     }
     if (isset($filterParams["active-user-status"])) {
         if ($filterParams["active-user-status"] == 1) {
             $where->and->EqualTo('disabled', 0);
         } elseif ($filterParams["active-user-status"] == 2) {
             $where->and->EqualTo('disabled', 1);
         }
     }
     if (isset($filterParams["sSearch"]) && $filterParams["sSearch"] != '') {
         $nestedWhere = new \Zend\Db\Sql\Predicate\Predicate();
         $nestedWhere->like($table . '.firstname', '%' . $filterParams["sSearch"] . '%');
         $nestedWhere->OR;
         $nestedWhere->like($table . '.lastname', '%' . $filterParams["sSearch"] . '%');
         $nestedWhere->OR;
         $nestedWhere->like($table . '.position', '%' . $filterParams["sSearch"] . '%');
         $nestedWhere->OR;
         $nestedWhere->like('details' . '.name', '%' . $filterParams["sSearch"] . '%');
         $nestedWhere->OR;
         $nestedWhere->like('teams' . '.name', '%' . $filterParams["sSearch"] . '%');
         $where->addPredicate($nestedWhere);
     }
     return $where;
 }

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

 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"]);
//.........这里部分代码省略.........

 /**
  * Returns the total number of rows in the result set.
  *
  * @return int
  */
 public function getCount($factory)
 {
     $sqlObject = $this->getSqlObject(TRUE);
     if ($factory[self::FROM] === NULL) {
         $factory[self::FROM] = array($factory[self::ALLIAS_TABLE_NAME] => $factory[self::ORIGINAL_TABLE_NAME]);
     }
     switch ($factory[self::TYPE_OF_REQUEST]) {
         case 'select':
             $sqlObject = $sqlObject->select()->from($factory[self::FROM])->columns(array('all' => new Expression('COUNT(1)')));
             break;
     }
     $w = new Where();
     $factory[self::WHERE] = array_reverse($factory[self::WHERE]);
     foreach ($factory[self::WHERE] as $where) {
         $w->addPredicate($where[0], $where[1]);
     }
     $sqlObject->where($w);
     if (!empty($factory[self::JOIN])) {
         foreach ($factory[self::JOIN] as $join) {
             $sqlObject->join($join['tables'], $join['on'], $join['columns'], $join['type']);
         }
     }
     if (!empty($factory[self::GROUP_BY])) {
         $sqlObject->group($factory[self::GROUP_BY]);
     }
     $statement = $this->getAdapter()->createStatement();
     $statement->prepare($this->getSqlObject(TRUE)->getSqlStringForSqlObject($sqlObject));
     $statement->getResource()->setFetchMode(PDO::FETCH_OBJ);
     $statement->execute($factory[self::VALUES]);
     $results = $statement->getResource()->fetchAll();
     $total = 0;
     if (!empty($factory[self::GROUP_BY])) {
         $total = count($results);
     } else {
         foreach ($results as $result) {
             $total += $result->all;
         }
     }
     return $total;
 }

 /**
  * @param int $resId
  * @param int $start
  * @param int $length
  * @param array $order
  * @param array $search
  * @param int $status
  * @return \DDD\Domain\Task\Task[]
  */
 public function getTasksOnReservationForDatatable($resId, $start, $length, $order, $search, $status)
 {
     $result = $this->fetchAll(function (Select $select) use($resId, $start, $length, $order, $search, $status) {
         $like = $search['value'];
         $where = new Where();
         if ($status == 1) {
             $where->notIn('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED, TaskService::STATUS_DONE]);
         } else {
             if ($status == 2) {
                 $where->in('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED, TaskService::STATUS_DONE]);
             }
         }
         $where->equalTo('reservations.id', $resId);
         $columns = ['priority', 'title', 'task_status', 'start_date', 'end_date', 'id', 'task_type'];
         $orderColumns = ['priority', 'task_status', 'start_date', 'end_date', 'title', 'task_type', 'creator_name', 'responsible_name'];
         $nestedWhere = new Where();
         $nestedWhere->like('title', '%' . $like . '%')->or->like($this->getTable() . '.start_date', '%' . $like . '%')->or->like($this->getTable() . '.end_date', '%' . $like . '%')->or->like('creator_users.firstname', '%' . $like . '%')->or->like('creator_users.lastname', '%' . $like . '%');
         $where->addPredicate($nestedWhere);
         $orderList = [];
         foreach ($order as $entity) {
             $orderList[] = $orderColumns[$entity['column']] . ' ' . $entity['dir'];
         }
         $select->columns($columns)->join(['reservations' => DbTables::TBL_BOOKINGS], $this->getTable() . '.res_id = reservations.id', [], Select::JOIN_INNER)->join(['task_types' => DbTables::TBL_TASK_TYPE], $this->getTable() . '.task_type = task_types.id', ['task_type_name' => 'name'], Select::JOIN_INNER)->join(['creators' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = creators.task_id AND creators.type = ' . TaskService::STAFF_CREATOR), [], Select::JOIN_INNER)->join(['creator_users' => DbTables::TBL_BACKOFFICE_USERS], 'creators.user_id = creator_users.id', ['creator_id' => 'id', 'creator_name' => new Expression('CONCAT(creator_users.firstname, " ", creator_users.lastname)')], Select::JOIN_INNER)->join(['responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = responsibles.task_id AND responsibles.type = ' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT)->join(['responsible_users' => DbTables::TBL_BACKOFFICE_USERS], 'responsibles.user_id = responsible_users.id', ['responsible_id' => 'id', 'responsible_name' => new Expression('CONCAT(responsible_users.firstname, " ", responsible_users.lastname)')], Select::JOIN_LEFT)->where($where)->order($orderList)->group($this->getTable() . '.id')->offset((int) $start)->limit((int) $length)->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $result2 = $statement->execute();
     $row = $result2->current();
     $total = $row['total'];
     return ['result' => $result, 'total' => $total];
 }

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