PHP Where::notEqualTo方法代码示例

 public function getRowBySerialNumber($serialNumber, $assetId)
 {
     return $this->fetchOne(function (Select $select) use($serialNumber, $assetId) {
         $where = new Where();
         $where->equalTo('serial_number', $serialNumber);
         if (FALSE !== $assetId) {
             $where->notEqualTo('id', $assetId);
         }
         $select->columns(['id'])->where($where);
     });
 }

 public function isUnitUniqueInLot($params)
 {
     $prototype = $this->resultSetPrototype->getArrayObjectPrototype();
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $result = $this->fetchOne(function (Select $select) use($params) {
         $where = new Where();
         if ($params['id']) {
             $where->notEqualTo($this->getTable() . '.id', $params['id']);
         }
         $where->equalTo($this->getTable() . '.unit', $params['unit']);
         $where->equalTo($this->getTable() . '.lot_id', $params['lot_id']);
         $select->columns(['id'])->where($where);
     });
     $this->resultSetPrototype->setArrayObjectPrototype($prototype);
     return $result;
 }

 public function getRowByCategoryLocationIdLocationEntity($categoryId, $locationEntityType, $locationEntityId, $assetId, $getInfo = false)
 {
     return $this->fetchOne(function (Select $select) use($categoryId, $locationEntityType, $locationEntityId, $assetId, $getInfo) {
         $where = new Where();
         $where->equalTo('category_id', $categoryId);
         $where->equalTo('location_entity_type', $locationEntityType);
         $where->equalTo('location_entity_id', $locationEntityId);
         if ($assetId) {
             $where->notEqualTo($this->getTable() . '.id', $assetId);
         }
         if (!$getInfo) {
             $select->columns(['id']);
         }
         $select->where($where);
     });
 }

 /**
  * @param bool $status
  *
  * @return \DDD\Domain\Booking\Statuses[]|\ArrayObject
  */
 public function getAllList($status = false)
 {
     $result = $this->fetchAll(function (Select $select) use($status) {
         $where = new Where();
         $where->equalTo('visible', 1);
         if ($status) {
             // If is canceled can't be changed to booked
             if ($status != 1) {
                 $where->notEqualTo('id', 1);
             }
             // Smart select (active ones + selected one)
             $where->or->equalTo('id', $status);
         }
         $select->where($where);
         $select->columns(array('id', 'name', 'visible'))->order('id');
     });
     return $result->buffer();
 }

 /**
  * @param  array $params
  * @return array|\ArrayObject|null
  */
 public function getSalarySchemesByParams($params = [])
 {
     $result = $this->fetchAll(function (Select $select) use($params) {
         $where = new Where();
         if (isset($params['status']) && $params['status'] > 0) {
             $where->equalTo($this->getTable() . '.status', $params['status']);
         } else {
             $where->notEqualTo($this->getTable() . '.status', \DDD\Service\User\SalaryScheme::SALARY_SCHEME_STATUS_ARCHIVED);
         }
         if (!empty($params['sort'])) {
             foreach ($params['sort'] as $col => $dir) {
                 $select->order($col . ' ' . $dir);
             }
         }
         $where->equalTo('transactionAccount.id', $params['transactionAccountId']);
         $select->join(['externalAccount' => DbTables::TBL_EXTERNAL_ACCOUNT], $this->getTable() . '.external_account_id = externalAccount.id', [])->join(['transactionAccount' => DbTables::TBL_TRANSACTION_ACCOUNTS], 'externalAccount.transaction_account_id = transactionAccount.id', [])->where($where);
     });
     return $result;
 }

 /**
  * @param $usage
  * @param bool $deactivatedIncluded
  * @return \DDD\Domain\ApartmentGroup\ForSelect[]
  * @throws \Exception
  */
 public function getApartmentGroupsByUsage($usage, $deactivatedIncluded = false, $hasNotDevAccess)
 {
     /**
      * @var ApartmentGroupDAO $apartmentGroupDao
      */
     $apartmentGroupDao = $this->getServiceLocator()->get('dao_apartment_group_apartment_group');
     switch ($usage) {
         case self::APARTMENT_GROUP_USAGE_APARTEL:
             $usageField = 'usage_apartel';
             break;
         case self::APARTMENT_GROUP_USAGE_BUILDING:
             $usageField = 'usage_building';
             break;
         case self::APARTMENT_GROUP_USAGE_CONCIERGE:
             $usageField = 'usage_concierge_dashboard';
             break;
         case self::APARTMENT_GROUP_USAGE_COST_CENTER:
             $usageField = 'usage_cost_center';
             break;
         case self::APARTMENT_GROUP_USAGE_PERFORMANCE:
             $usageField = 'usage_performance_group';
             break;
         default:
             throw new \Exception('Wrong apartment group usage passed');
     }
     $where = new Where();
     $where->equalTo($usageField, 1);
     if (!$deactivatedIncluded) {
         $where->equalTo('active', 1);
     }
     if ($hasNotDevAccess) {
         $where->notEqualTo('id', 1);
     }
     $apartmentGroupDao->getResultSetPrototype()->setArrayObjectPrototype(new ForSelect());
     /**
      * @var ForSelect[] $apartmentGroups
      */
     $apartmentGroups = $apartmentGroupDao->fetchAll($where, ['id', 'name', 'usage_apartel']);
     return $apartmentGroups;
 }

 /**
  * Get the list of employees working with the given Employee ID
  * 
  * @param $id Employee ID
  * */
 public function getList($id = false)
 {
     $users_gateway = Users::factory($this->getServiceLocator());
     $employee_id = $this->params()->fromRoute('user_id');
     $table = $users_gateway->getTable();
     // select all users who works during the same time period
     $result = $users_gateway->select(function ($select) use($employee_id, $id, $table) {
         // the shifts other employees work
         $select->join(array('S' => 'shifts'), "{$table}.id = S.employee_id", array(), Select::JOIN_INNER);
         // the shifts the current employee work
         $select->join(array('S2' => 'shifts'), new Expression("(S.start_time >= S2.start_time\n\t\t\t\t\t\t\t\t\t\t\tAND S.start_time < S2.end_time)\n\t\t\t\t\t\t\t\t\t\tOR (S.end_time >= S2.start_time\n\t\t\t\t\t\t\t\t\t\t\tAND S.end_time < S2.end_time)"), array(), Select::JOIN_INNER);
         $where = new Where();
         // we do not want to know about our own shifts
         $where->notEqualTo("S.employee_id", $employee_id);
         // we want to use only our shifts as a comparison
         $where->equalTo('S2.employee_id', $employee_id);
         if ($id !== false) {
             $where->equalTo("{$table}.id", $id);
         }
         $select->where($where);
     });
     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);
     }
 }

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

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

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

 public function getUDListCount($userId, $type)
 {
     $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
     $result = $this->fetchOne(function (Select $select) use($userId, $type) {
         $where = new Where();
         $where->lessThanOrEqualTo($this->getTable() . '.start_date', date('Y-m-d H:i:s', strtotime('+3 day')));
         $where->notIn('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED]);
         $expression = false;
         switch ($type) {
             case 'team':
                 $select->join(['team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = team_staff.team_id
                             AND team_staff.user_id = ' . $userId . '
                             AND team_staff.type IN (' . TeamService::STAFF_MANAGER . ', ' . TeamService::STAFF_OFFICER . ')'), [], Select::JOIN_INNER)->join(['task_responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_responsibles.task_id AND task_responsibles.type = ' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT);
                 $where->notEqualTo('is_hk', TaskService::TASK_IS_HOUSEKEEPING);
                 $expression = 'task_responsibles.id IS NULL';
                 break;
             case 'team_assigned':
                 $select->join(['team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = team_staff.team_id
                             AND team_staff.user_id = ' . $userId . '
                             AND team_staff.type IN (' . TeamService::STAFF_MANAGER . ', ' . TeamService::STAFF_OFFICER . ', ' . TeamService::STAFF_MEMBER . ')'), [], Select::JOIN_INNER)->join(['task_responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_responsibles.task_id AND task_responsibles.type = ' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT);
                 $expression = 'task_responsibles.user_id = ' . User::ANY_TEAM_MEMBER_USER_ID;
                 break;
             case 'created':
                 $select->join(['task_creators' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_creators.task_id AND task_creators.type = ' . TaskService::STAFF_CREATOR), [], Select::JOIN_LEFT);
                 $expression = 'task_creators.user_id = ' . $userId;
                 break;
             case 'verifying':
                 $select->join(['task_verifiers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_verifiers.task_id AND task_verifiers.type = ' . TaskService::STAFF_VERIFIER), [], Select::JOIN_LEFT);
                 $expression = 'task_verifiers.user_id = ' . $userId . ' AND task_status = ' . TaskService::STATUS_DONE;
                 break;
             case 'doing':
                 $select->join(['task_responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_responsibles.task_id AND task_responsibles.type = ' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT)->join(['task_helpers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_helpers.task_id AND task_helpers.type = ' . TaskService::STAFF_HELPER), [], Select::JOIN_LEFT);
                 $expression = '(task_responsibles.user_id = ' . $userId . ' OR task_helpers.user_id = ' . $userId . ') AND task_status <> ' . TaskService::STATUS_DONE;
                 break;
             case 'following':
                 $select->join(['task_followers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_followers.task_id AND task_followers.type = ' . TaskService::STAFF_FOLLOWER), [], Select::JOIN_LEFT);
                 $select->join(['following_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression('task_followers.user_id = ' . $userId . ' OR (' . $this->getTable() . '.following_team_id = following_team_staff.team_id
                     AND following_team_staff.user_id = ' . $userId . '
                     AND following_team_staff.type IN (' . TeamService::STAFF_MANAGER . ', ' . TeamService::STAFF_OFFICER . ', ' . TeamService::STAFF_MEMBER . ')
                     AND is_hk = 0)'), [], Select::JOIN_INNER);
                 $expression = 'task_status != ' . TaskService::STATUS_DONE;
                 break;
         }
         if ($expression) {
             $where->expression($expression, []);
         }
         $select->columns(['count' => new Expression('COUNT(DISTINCT ' . $this->getTable() . '.id' . ')')])->where($where);
     });
     return $result['count'];
 }

 public function teamSave($data, $id, $global, $isDirector)
 {
     $apartmentDao = $this->getServiceLocator()->get('dao_accommodation_accommodations');
     $auth = $this->getServiceLocator()->get('library_backoffice_auth');
     $data = (array) $data;
     $teamData = ['usage_department' => $data['usage_department'], 'usage_notifiable' => $data['usage_notifiable'], 'usage_frontier' => $data['usage_frontier'], 'usage_taskable' => $data['usage_taskable'], 'usage_security' => $data['usage_security'], 'usage_hiring' => $data['usage_hiring'], 'usage_storage' => $data['usage_storage'], 'extra_inspection' => isset($data['extra_inspection']) ? $data['extra_inspection'] : 0, 'timezone' => $data['timezone']];
     if ($global) {
         $teamData['name'] = $data['name'];
         $teamData['description'] = $data['description'];
     }
     if ($id > 0) {
         $teamData['modified_date'] = date('Y-m-d');
         $this->getTeamDao()->save($teamData, ['id' => (int) $id]);
     } else {
         $teamData['created_date'] = date('Y-m-d');
         $teamData['modified_date'] = null;
         $id = $this->getTeamDao()->save($teamData);
         $this->getTeamStaffDao()->insert(['type' => self::STAFF_CREATOR, 'team_id' => $id, 'user_id' => $auth->getIdentity()->id]);
     }
     $diffTimezone = [];
     if ($id > 0) {
         $teamInfo = $this->getTeamDao()->fetchOne(['id' => $id]);
         // Remove duplication if user is set as both manager and member && its not frontier team
         if ($teamInfo && $teamInfo->isFrontier() != self::IS_FRONTIER_TEAM) {
             if (isset($data['members'])) {
                 $managers = $data['managers'];
                 $members = $data['members'];
                 foreach ($managers as $manager) {
                     if (array_search($manager, $members) !== false) {
                         $key = array_search($manager, $members);
                         unset($data['members'][$key]);
                     }
                 }
             }
         }
         // Delete all staff and save again to avoid complicated select, check, insert & delete actions
         $staffDeleteCondition = new Where();
         $staffDeleteCondition->equalTo('team_id', $id);
         // Do not delete creator
         $staffDeleteCondition->notEqualTo('type', self::STAFF_CREATOR);
         // Change (delete + insert) director only if global
         if (!$global) {
             $staffDeleteCondition->notEqualTo('type', self::STAFF_DIRECTOR);
         }
         // Change (delete + insert) managers only if global or director
         if (!$global && !$isDirector) {
             $staffDeleteCondition->notEqualTo('type', self::STAFF_MANAGER);
         }
         $this->getTeamStaffDao()->delete($staffDeleteCondition);
         // Insert director, if global
         if ($global && $data['director']) {
             $this->getTeamStaffDao()->insert(['type' => self::STAFF_DIRECTOR, 'team_id' => $id, 'user_id' => $data['director']]);
         }
         // Insert members passed by form
         if (!empty($data['members'])) {
             foreach ($data['members'] as $member) {
                 $this->getTeamStaffDao()->insert(['type' => self::STAFF_MEMBER, 'team_id' => $id, 'user_id' => $member]);
             }
         }
         // Insert officers passed by form
         if (!empty($data['officers'])) {
             foreach ($data['officers'] as $officer) {
                 $this->_dao_team_staff->insert(['type' => self::STAFF_OFFICER, 'team_id' => $id, 'user_id' => $officer]);
             }
         }
         // Insert managers passed by form
         if (!empty($data['managers'])) {
             foreach ($data['managers'] as $manager) {
                 $this->_dao_team_staff->insert(['type' => self::STAFF_MANAGER, 'team_id' => $id, 'user_id' => $manager]);
             }
         }
         // Delete all apartments and buildings to avoid complexity of selecting existing, comparison, deletion and addition
         if ($global || $isDirector) {
             $apartmentsDeleteCondition = new Where();
             $apartmentsDeleteCondition->equalTo('team_id', $id);
             $this->getTeamFrontierApartmentsDao()->delete($apartmentsDeleteCondition);
             $this->getTeamFrontierBuildingsDao()->delete($apartmentsDeleteCondition);
             // Insert apartments passed by form
             if (!empty($data['frontier_apartments'])) {
                 foreach ($data['frontier_apartments'] as $frontierApartment) {
                     $isDuplicate = $this->getTeamFrontierApartmentsDao()->checkDuplicateApartment($id, $frontierApartment);
                     if (!$isDuplicate) {
                         $this->getTeamFrontierApartmentsDao()->insert(['team_id' => $id, 'apartment_id' => $frontierApartment]);
                         $apartmentTimezone = $apartmentDao->getApartmentTimezone($frontierApartment);
                         if ($data['timezone'] !== $apartmentTimezone['timezone']) {
                             array_push($diffTimezone, $apartmentTimezone['name']);
                         }
                     }
                 }
             }
             // Insert buildings passed by form
             if (!empty($data['frontier_buildings'])) {
                 foreach ($data['frontier_buildings'] as $frontierBuilding) {
                     $this->getTeamFrontierBuildingsDao()->insert(['team_id' => $id, 'building_id' => $frontierBuilding]);
                 }
             }
         }
         return ['id' => $id, 'diffTimezone' => implode(',', $diffTimezone)];
     }
 }