PHP Where::isNull方法代码示例

 public function fetchFeed($params = null)
 {
     $sql = new Sql($this->adapter);
     $select = $sql->select()->from($this->table);
     $params['orderColumn'] = 'id';
     $params['orderDirection'] = 'DESC';
     $tableSchemaArray = TableSchema::getSchemaArray($this->table);
     $hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
     $params = $this->applyDefaultEntriesSelectParams($params);
     $columns = ['id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data'];
     $select->columns($columns);
     // ->order('id DESC');
     $select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
     $select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
     //die($this->dumpSql($select));
     $rowset = $this->selectWith($select);
     $rowset = $rowset->toArray();
     $rowset = $this->convertDates($rowset, $tableSchemaArray);
     //        @TODO: Returns date in ISO 8601 Ex: 2016-06-06T17:18:20Z
     //        see: https://en.wikipedia.org/wiki/ISO_8601
     //        foreach ($rowset as &$row) {
     //            $row['datetime'] .= ' UTC';
     //        }
     $countTotalWhere = new Where();
     $countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
     $activityTotal = $this->countTotal($countTotalWhere);
     return ['total' => $activityTotal, 'rows' => $rowset];
 }

 public function fetchFeed($params = null)
 {
     $sql = new Sql($this->adapter);
     $select = $sql->select()->from($this->table);
     $params['orderColumn'] = 'id';
     $params['orderDirection'] = 'DESC';
     $tableSchemaArray = TableSchema::getSchemaArray($this->table);
     $hasActiveColumn = $this->schemaHasActiveColumn($tableSchemaArray);
     $params = $this->applyDefaultEntriesSelectParams($params);
     $columns = array('id', 'identifier', 'action', 'table_name', 'row_id', 'user', 'datetime', 'type', 'data');
     $select->columns($columns);
     // ->order('id DESC');
     $select->where->nest->isNull('parent_id')->OR->equalTo('type', 'FILES')->unnest;
     $select = $this->applyParamsToTableEntriesSelect($params, $select, $tableSchemaArray, $hasActiveColumn);
     //die($this->dumpSql($select));
     $rowset = $this->selectWith($select);
     $rowset = $rowset->toArray();
     foreach ($rowset as &$row) {
         $row['datetime'] .= ' UTC';
     }
     $countTotalWhere = new Where();
     $countTotalWhere->isNull('parent_id')->OR->equalTo('type', 'FILES');
     $activityTotal = $this->countTotal($countTotalWhere);
     return array('total' => $activityTotal, 'rows' => $rowset);
 }

 /**
  * @param null $parentId
  * @param array $roles
  * @return array
  */
 public function getMenuList($parentId = null, array $roles)
 {
     $results = $this->select(function (Select $select) use($parentId, $roles) {
         try {
             $where = new Where();
             if (empty($parentId)) {
                 $where->isNull('parentId');
             } else {
                 $where->equalTo('parentId', $parentId);
             }
             if (!empty($roles)) {
                 $where->in('mp.roleId', $roles);
             } else {
                 $where->expression(' 1 = ?', 0);
             }
             $select->join(array('mp' => 'tbl_menu_permission'), 'tbl_menu.menuId = mp.menuId', array('menuId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT)->order(array('priority ASC'));
         } catch (\Exception $ex) {
             throw $ex;
         }
     });
     $menus = array();
     foreach ($results as $menu) {
         $nav = array();
         $nav['id'] = $menu->getMenuId();
         $pages = $this->getMenuList($menu->getMenuId(), $roles);
         $caret = '';
         if (!empty($pages)) {
             $nav['pages'] = $pages;
         }
         $icon = '';
         if ($menu->getIcon()) {
             $icon = '<span class="' . $menu->getIcon() . '"></span>&nbsp;';
         }
         $nav['title'] = $menu->getDescription();
         $nav['label'] = $icon . htmlspecialchars($menu->getTitle()) . $caret;
         $nav['order'] = $menu->getPriority();
         $nav['rel'] = array('divider' => $menu->getHasDivider());
         if ($menu->getUrlType() == 'R') {
             $nav['route'] = $menu->getUrls();
         } else {
             $nav['uri'] = $menu->getUrls();
         }
         $menus[] = $nav;
     }
     return $menus;
 }

 public function getListForSearch($iDisplayStart, $iDisplayLength, $filterParams, $sSearch, $sortCol = 0, $sortDir = 'DESC')
 {
     $where = new Where();
     if ($filterParams["category"] > 0) {
         $where->equalTo($this->getTable() . '.category_id', $filterParams["category"]);
     }
     if (FALSE !== strpos($filterParams["location"], '_')) {
         $locationsArray = explode('_', $filterParams["location"]);
         $locationId = $locationsArray[1];
         $where->equalTo($this->getTable() . '.location_entity_id', $locationId);
     }
     switch ((int) $filterParams["runningOut"]) {
         case AssetService::RUNNING_OUT_YES:
             $where->expression($this->getTable() . '.quantity <= thresholds.threshold', []);
             break;
         case AssetService::RUNNING_OUT_NO:
             $where->expression($this->getTable() . '.quantity > thresholds.threshold', []);
             break;
         case AssetService::RUNNING_OUT_NOT_SET:
             $where->isNull(' thresholds.threshold');
             break;
     }
     if (strlen($sSearch)) {
         $where->nest->like('storages.name', "%" . $sSearch . "%")->or->like('categories.name', "%" . $sSearch . "%")->unnest;
     }
     $sortColumns = ['category_name', 'location_name', 'quantity', 'running_out', 'threshold'];
     $result = $this->fetchAll(function (Select $select) use($filterParams, $sortColumns, $iDisplayStart, $iDisplayLength, $where, $sSearch, $sortCol, $sortDir) {
         $select->join(['categories' => DbTables::TBL_ASSET_CATEGORIES], $this->getTable() . '.category_id = categories.id', ['category_name' => 'name'], Select::JOIN_INNER)->join(['storages' => DbTables::TBL_WM_STORAGE], $this->getTable() . '.location_entity_id = storages.id', ['location_name' => 'name'], Select::JOIN_INNER)->join(['thresholds' => DbTables::TBL_WM_THRESHOLD], $this->getTable() . '.category_id = thresholds.asset_category_id AND ' . $this->getTable() . '.location_entity_id = thresholds.storage_id', ['threshold' => 'threshold'], Select::JOIN_LEFT);
         if ($where !== null) {
             $select->where($where);
         }
         $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
         if ($iDisplayLength !== null && $iDisplayStart !== null) {
             $select->limit((int) $iDisplayLength);
             $select->offset((int) $iDisplayStart);
         }
         $select->order($sortColumns[$sortCol] . ' ' . $sortDir);
     });
     $return['result'] = $result;
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $result2 = $statement->execute();
     $row = $result2->current();
     $return['count'] = $row['total'];
     return $return;
 }

 public function detailAction()
 {
     $storeID = $this->queryData['storeID'];
     $auctionStatus = $this->queryData['auctionStatus'];
     $order = $this->queryData['order'];
     $sort = $this->queryData['sort'];
     $storeCategoryID = $this->queryData['storeCategoryID'];
     $where = array('storeID' => $storeID);
     $storeInfo = $this->storeModel->fetch($where);
     $storeCategories = $this->storeCategoryModel->select(array('storeID' => $storeID))->toArray();
     /*$storeRecommendProducts = $this->productModel->getProducts(array('Product.isStoreRecommend' => 1, 'Product.storeID' => $storeID, 'Product.auctionStatus' => array(1, 2)));
       $storeRecommendProductsData = $storeRecommendProducts['data'];
       foreach($storeRecommendProductsData as $k => $v){
           $storeRecommendProductsData[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
       }*/
     if (!empty($order) && !empty($sort)) {
         $order = 'Product.' . $order . ' ' . $sort;
     }
     $where = new Where();
     $where->equalTo('Product.storeID', $storeID);
     if (!empty($auctionStatus)) {
         $where->equalTo('Product.auctionStatus', $auctionStatus);
     } else {
         $where->in('Product.auctionStatus', array(1, 2));
     }
     $where->isNull('Product.specialID');
     if (!empty($storeCategoryID)) {
         $where->and->nest()->or->equalTo('Product.firstStoreCategoryID', $storeCategoryID)->or->equalTo('Product.secondStoreCategoryID', $storeCategoryID)->or->equalTo('Product.thirdStoreCategoryID', $storeCategoryID);
     }
     $storeProducts = $this->productModel->getProducts($where, $this->pageNum, $this->limit, $order);
     $storeProductsData = $storeProducts['data'];
     foreach ($storeProductsData as $k => $v) {
         $storeProductsData[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
     }
     $this->view->setVariables(array('storeInfo' => $storeInfo, 'storeProducts' => $storeProductsData, 'storeCategories' => $storeCategories, 'pages' => $storeProducts['pages'], 'auctionStatus' => $auctionStatus, 'order' => $this->queryData['order'], 'sort' => $sort, 'storeCategoryID' => $storeCategoryID));
     return $this->view;
 }

 /**
  * Permet d'afficher seulement les informations présentes sur l'écran de recherche Client (qui sont également les critères)
  * @author Ophélie
  * @param  ServiceLocator $sm
  * @param  array $critere 
  * @since  1.0
  * @return array
  */
 public function getClientsFromForms($sm, $codeClient = '', $raisonSociale = null, $limit = 100)
 {
     $sql = new Sql($sm->get('Zend\\Db\\Adapter\\Adapter'));
     $select = $sql->select();
     $select->columns(array('id', 'societe' => new Expression("CONCAT(c.raison_sociale,' (',a.code_postal,' ',a.ville,', ',a.pays,')')")))->from(array('c' => 'client'))->join(array('a' => 'adresse'), 'a.ref_client = c.id', array(), $select::JOIN_LEFT)->order('raison_sociale ASC');
     $where = new Where();
     if ($codeClient !== '') {
         if ($codeClient === null) {
             //var_dump($codeClient);die();
             $where->isNull('c.code_client')->and;
         } else {
             $where->equalTo('c.code_client', $codeClient)->and;
         }
     }
     if ($raisonSociale !== null) {
         $where->equalTo('c.raison_sociale', $raisonSociale)->and;
     }
     $where->and->nest()->equalTo('a.adresse_principale', 1)->or->isNull('a.adresse_principale')->unnest();
     $select->where($where);
     $statement = $sql->prepareStatementForSqlObject($select);
     $results = $statement->execute();
     if ($results->isQueryResult()) {
         $resultSet = new ResultSet();
         $resultSet->initialize($results);
         return $resultSet->toArray();
     }
     return array();
 }

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

 /**
  * @param null $parentId
  * @param string $parentName
  * @param string $type
  * @return array
  */
 public function getChildren($type = '', $parentId = null, $parentName = "")
 {
     $results = $this->select(function (Select $select) use($parentId, $type) {
         if (empty($type)) {
             $select->where(array('parentTypeId' => $parentId));
         } else {
             $where = new Where();
             if (empty($parentId)) {
                 $where->isNull('parentTypeId');
             } else {
                 $where->equalTo('parentTypeId', $parentId);
             }
             $where->in('baseType', array('B', $type));
             $select->where($where);
         }
     });
     $resultList = array();
     foreach ($results as $accountType) {
         $children = $this->getChildren($type, $accountType->getAccountTypeId(), $parentName);
         if (!empty($children)) {
             $accountType->setChildren($children);
         }
         $resultList[] = $accountType;
     }
     return $resultList;
 }

 public function getTaskListForSearch($authID, $iDisplayStart = null, $iDisplayLength = null, $filterParams = array(), $sortCol = 0, $sortDir = 'DESC', $taskManger)
 {
     $where = new Where();
     foreach ($filterParams as $key => $row) {
         if (!is_array($row)) {
             $filterParams[$key] = trim($row);
         }
     }
     if ($filterParams["title"] != '') {
         $where->like($this->getTable() . '.title', '%' . $filterParams["title"] . '%');
     }
     if ($filterParams["status"] > 0) {
         $statusArray = [$filterParams["status"]];
         if ($filterParams["status"] == TaskService::STATUS_ALL_OPEN) {
             $statusArray = [TaskService::STATUS_NEW, TaskService::STATUS_VIEWED, TaskService::STATUS_BLOCKED, TaskService::STATUS_STARTED];
         }
         $where->in($this->getTable() . '.task_status', $statusArray);
     }
     if ($filterParams["priority"] > 0) {
         $where->equalTo($this->getTable() . '.priority', $filterParams["priority"]);
     }
     if ($filterParams["type"] > 0) {
         $where->equalTo($this->getTable() . '.task_type', $filterParams["type"]);
     }
     if ($filterParams["creator_id"] > 0) {
         $where->equalTo('task_creators.user_id', (int) $filterParams["creator_id"]);
     }
     if ($filterParams["responsible_id"] > 0) {
         $where->equalTo('task_responsibles.user_id', (int) $filterParams["responsible_id"]);
     }
     if ($filterParams["responsible_id"] < 0) {
         $where->isNull('task_responsibles.user_id');
     }
     if ($filterParams["verifier_id"] > 0) {
         $where->equalTo('task_verifiers.user_id', (int) $filterParams["verifier_id"]);
     }
     if ($filterParams["helper_id"] > 0) {
         $where->equalTo('task_helpers.user_id', (int) $filterParams["helper_id"]);
     }
     if ($filterParams["follower_id"] > 0) {
         $where->equalTo('task_followers.user_id', (int) $filterParams["follower_id"]);
     }
     if ($filterParams["property_id"] > 0 && $filterParams['property']) {
         $where->equalTo($this->getTable() . '.property_id', $filterParams["property_id"]);
     }
     if ($filterParams["team_id"]) {
         $where->equalTo($this->getTable() . '.team_id', $filterParams["team_id"]);
     }
     if (isset($filterParams['tags']) && !empty($filterParams['tags'])) {
         $where->in('task_tag.tag_id', explode(',', $filterParams['tags']));
     }
     if ($filterParams["end_date"] != '') {
         $dates = explode(' - ', $filterParams["end_date"]);
         $rangeStart = $dates[0];
         $rangeEnd = $dates[1];
         $where->lessThanOrEqualTo($this->getTable() . ".end_date", $rangeEnd);
         $where->greaterThanOrEqualTo($this->getTable() . ".end_date", $rangeStart);
     }
     if ($filterParams["creation_date"] != '') {
         $dates = explode(' - ', $filterParams["creation_date"]);
         $rangeStart = $dates[0] . ' 00:00';
         $rangeEnd = $dates[1] . ' 23:59';
         $where->lessThanOrEqualTo($this->getTable() . ".creation_date", $rangeEnd);
         $where->greaterThanOrEqualTo($this->getTable() . ".creation_date", $rangeStart);
     }
     if ($filterParams["done_date"] != '') {
         $dates = explode(' - ', $filterParams["done_date"]);
         $rangeStart = $dates[0];
         $rangeEnd = $dates[1];
         $where->lessThanOrEqualTo($this->getTable() . ".done_date", $rangeEnd);
         $where->greaterThanOrEqualTo($this->getTable() . ".done_date", $rangeStart);
     }
     $buildingId = false;
     if ($filterParams["building_id"] > 0 && $filterParams['building']) {
         $buildingId = $filterParams["building_id"];
     }
     $sortColumns = array('priority', 'task_status', 'start_date', 'end_date', 'title', 'apartment_name', 'responsible_name', 'verifier_name', 'task_type');
     $result = $this->fetchAll(function (Select $select) use($sortColumns, $iDisplayStart, $iDisplayLength, $where, $sortCol, $sortDir, $authID, $taskManger, $buildingId) {
         $select->join(['task_types' => DbTables::TBL_TASK_TYPE], $this->getTable() . '.task_type = task_types.id', ['task_type_name' => 'name'], Select::JOIN_INNER)->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)->join(['users_creators' => DbTables::TBL_BACKOFFICE_USERS], 'task_creators.user_id = users_creators.id', ['creator_id' => 'id', 'creator_name' => new Expression("CONCAT(users_creators.firstname, ' ', users_creators.lastname)")], Select::JOIN_LEFT)->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(['users_responsibles' => DbTables::TBL_BACKOFFICE_USERS], 'task_responsibles.user_id = users_responsibles.id', ['responsible_id' => 'id', 'responsible_name' => new Expression("CONCAT(users_responsibles.firstname, ' ', users_responsibles.lastname)")], Select::JOIN_LEFT)->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)->join(['users_verifiers' => DbTables::TBL_BACKOFFICE_USERS], 'task_verifiers.user_id = users_verifiers.id', ['verifier_id' => 'id', 'verifier_name' => new Expression("CONCAT(users_verifiers.firstname, ' ', users_verifiers.lastname)")], 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)->join(['users_helpers' => DbTables::TBL_BACKOFFICE_USERS], 'task_helpers.user_id = users_helpers.id', ['helper_id' => 'id', 'helper_name' => new Expression("CONCAT(users_helpers.firstname, ' ', users_helpers.lastname)")], Select::JOIN_LEFT)->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)->join(['task_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = task_team_staff.team_id and task_team_staff.type NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')'), [], Select::JOIN_LEFT)->join(['task_following_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.following_team_id = task_following_team_staff.team_id and task_following_team_staff.type NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')'), [], Select::JOIN_LEFT)->join(['users_followers' => DbTables::TBL_BACKOFFICE_USERS], 'task_followers.user_id = users_followers.id', ['follower_id' => 'id', 'follower_name' => new Expression("CONCAT(users_followers.firstname, ' ', users_followers.lastname)")], Select::JOIN_LEFT)->join(['apartment1' => DbTables::TBL_APARTMENTS], $this->getTable() . '.property_id = apartment1.id', ['apartment_name' => 'name', 'apartment_unit_number' => 'unit_number'], Select::JOIN_LEFT)->join(['subtask' => DbTables::TBL_TASK_SUBTASK], 'subtask.task_id = ' . $this->getTable() . '.id', ['subtask_description' => new Expression('GROUP_CONCAT(subtask.description)')], Select::JOIN_LEFT)->join(['task_tag' => DbTables::TBL_TASK_TAG], new Expression($this->getTable() . '.id = task_tag.task_id'), [], Select::JOIN_LEFT);
         if ($buildingId) {
             $select->join(['apartment' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.property_id = apartment.id AND apartment.building_id = ' . $buildingId), []);
         }
         if (!$taskManger) {
             $where->expression('(users_creators.id = ' . $authID . ' OR users_responsibles.id = ' . $authID . ' OR task_verifiers.user_id = ' . $authID . ' OR users_helpers.id = ' . $authID . ' OR users_followers.id = ' . $authID . ' OR task_team_staff.user_id = ' . $authID . ' OR task_following_team_staff.user_id = ' . $authID . ')', []);
         }
         if ($where !== null) {
             $select->where($where);
         }
         $select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
         if ($iDisplayLength !== null && $iDisplayStart !== null) {
             $select->limit((int) $iDisplayLength);
             $select->offset((int) $iDisplayStart);
         }
         $select->group($this->getTable() . '.id');
         $select->order($sortColumns[$sortCol] . ' ' . $sortDir);
     });
     $statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
     $result2 = $statement->execute();
     $row = $result2->current();
     $total = $row['total'];
//.........这里部分代码省略.........