Returns the number of records.
public count ( string $q = '*', |
||
$q | string | the COUNT expression. Defaults to '*'. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
$db | the database connection used to generate the SQL statement. If this parameter is not given (or null), the `db` application component will be used. | |
리턴 | integer | string | number of records. The result may be a string depending on the underlying database engine and to support integer values higher than a 32bit PHP integer can handle. |
public function actionIndex() { Yii::$app->response->format = Response::FORMAT_JSON; $params = Yii::$app->getRequest(); $filter = array(); $sort = ""; $page = isset($params->page) ? $params->page : 1; $limit = isset($params->limit) ? $params->limit : 10; $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params->filter)) { $filter = (array) json_decode($params->filter); } if (isset($params->sort)) { $sort = $params->sort; if (isset($params->order)) { if ($params->order == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('restaurants')->orderBy($sort)->select("restaurant_id, name, street, house_nr, flat_nr, zip_code, city"); $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); echo json_encode(array('status' => 1, 'code' => 200, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
public function actionRegion($id = null) { if ($id) { $model = Geo::getOne($id); //получчаем количество розничных точек в регионе $col_apteki = Apteki::find()->where(['region_id' => $id])->count(); //получаем количество Юр.лиц $col_ur_l = RegionUrL::find()->where(['id_reg' => $id])->count(); $db = new Query(); $db->from('region_ur_l'); $db->InnerJoin('ur_l', 'id_ur = ur_l.id'); $db->andWhere(['=', 'id_reg', $id]); $db->andWhere(['=', 'ur_l.regional_id', $model['regional_id']]); $col_ur_l_regpred = $db->count(); // $col_ur_l_regpred = RegionUrL::find()->where(['id_reg' => $id])->andWhere(['$model'=>])->count(); } else { $model = Geo::getAll(); } $region = new Geo(); if ($_POST['Geo']) { $model->attributes = $_POST['Geo']; if ($model->validate() && $model->save()) { return $this->render('/edit/region', ['model' => $model, 'region' => $region, 'ok' => 1, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]); } } if ($id) { return $this->render('/edit/region', ['model' => $model, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]); } else { return $this->render('region', ['model' => $model]); } }
/** * @inheritDoc */ public function actionDown($limit = 'all') { $ret = parent::actionDown('all'); $query = new Query(); $query->from($this->migrationTable); if (1 == $query->count()) { $this->deleteMigrationHistoryTable(); } return $ret; }
public function getOne($mat_id, $num_tour) { $query = new Query(); $query->select('id, mat_id, numero_tour, done')->from('kanban')->where('mat_id = ' . $mat_id . ' AND numero_tour = ' . $num_tour); if ($query->count() > 0) { $data = $query->all(); return $this->arrayToKanbanList($data)[0]; } else { return null; } }
/** * Lists all ExploreRecommend models. * @return mixed */ public function actionIndex() { $query = new Query(); $query->select('id, summary, created_at, title, username, user_id')->from('{{%explore_recommend}}')->where(['category' => 'post'])->orderBy('id DESC'); $pages = new \yii\data\Pagination(['totalCount' => $query->count()]); $posts = $query->offset($pages->offset)->limit($pages->limit)->all(); $forums = new Query(); $forums = $forums->select('f.forum_name, f.forum_url, f.forum_desc, f.forum_icon, e.id')->from('{{%forum}} as f')->join('LEFT JOIN', '{{%explore_recommend}} as e', 'e.table_id=f.id')->where(['e.category' => 'forum'])->orderBy('e.id DESC')->all(); $albums = new Query(); $albums = $albums->select('id, table_id')->from('{{%explore_recommend}}')->where(['category' => 'album'])->orderBy('id DESC')->all(); return $this->render('index', ['posts' => $posts, 'pages' => $pages, 'forums' => $forums, 'albums' => $albums]); }
/** * @inheritDoc */ public function actionDown($limit = 'all') { $ret = parent::actionDown('all'); $query = new Query(); $query->from($this->migrationTable); if (1 == $query->count()) { $tableName = $this->db->schema->getRawTableName($this->migrationTable); $this->stdout("Deleting migration history table \"{$tableName}\"...", Console::FG_YELLOW); $this->db->createCommand()->dropTable($this->migrationTable)->execute(); $this->stdout("Done.\n", Console::FG_GREEN); } return $ret; }
/** * Lists all City models. * @return mixed */ public function actionIndex() { $params = $_REQUEST; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('user')->andFilterWhere(['like', 'id', $filter['id']])->andFilterWhere(['like', 'name', $filter['name']])->andFilterWhere(['like', 'age', $filter['age']])->orderBy($sort)->select("id,name,age,createdAt,updatedAt"); if ($datefilter['from']) { $query->andWhere("createdAt >= '" . $datefilter['from'] . "' "); } if ($datefilter['to']) { $query->andWhere("createdAt <= '" . $datefilter['to'] . "'"); } $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
/** * Lists all Airports models. * @return mixed */ public function actionSearch() { $params = Yii::$app->request->queryParams; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); if (!empty($params)) { $query->offset($offset)->limit($limit)->from('airports')->andFilterWhere(['like', 'id', $filter['id']])->andFilterWhere(['like', 'airport_code', $filter['airport_code']])->andFilterWhere(['like', 'airport_name', $filter['airport_name']])->andFilterWhere(['like', 'country', $filter['country']])->andFilterWhere(['like', 'city', $filter['city']])->orderBy($sort)->select("id, airport_code, airport_name, country, city"); } else { $query->offset($offset)->limit($limit)->from('airports')->orderBy($sort)->select("id, airport_code, airport_name, country, city"); } $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
public static function queryDatas($tableName, $conditions, $offset, $size, $orderby, $isGetTotal = false) { $query = new Query(); $query->select('*')->from($tableName); if (!empty($conditions)) { foreach ($conditions as $name => $value) { if (is_array($value)) { $query->andWhere($value); } else { $query->andWhere([$name => $value]); } } } $total = 0; if ($isGetTotal) { $total = $query->count('*'); } $query->offset($offset)->limit($size)->orderBy($orderby); $list = $query->all(); return ['list' => $list, 'total' => $total]; }
protected function getCountByCommand($tableName, Query $command) { $command->from($tableName); return (int) $command->count(); }
/** * Updates all Entries of a Contest with votecount and avg rating * * @param \app\models\Contest $Contest Contest Object */ private function UpdateContestEntries($Contest) { $Entries = Entry::findAll(['contest_id' => $Contest->id]); foreach ($Entries as $Entry) { $avgQuery = new Query(); $avgQuery->from(Tweet::tableName())->where(['entry_id' => $Entry->id, 'needs_validation' => 0, 'old_vote' => 0]); $avgRating = $avgQuery->average('rating'); $votestQuery = new Query(); $votestQuery->from(Tweet::tableName())->where(['entry_id' => $Entry->id, 'needs_validation' => 0, 'old_vote' => 0]); $votes = $votestQuery->count(); $Entry->votes = $votes; $Entry->avg_rating = round($avgRating, 2); $Entry->save(); } }
public function actionUsers() { $_currUser = Yii::$app->user->isGuest ? null : User::findOne(['id' => Yii::$app->user->id]); if (!$_currUser || 0 === $_currUser->is_admin) { return $this->goHome(); } if ($_currUser && $_currUser->is_admin && Yii::$app->request->get('user_id') && 'markAdmin' === Yii::$app->request->get('action')) { $tarUser = User::findOne(['id' => intval(Yii::$app->request->get('user_id'))]); if ($tarUser) { $tarUser->is_admin = 1; $tarUser->save(); } } $users = []; $currPage = Yii::$app->request->get('page', 0); $filteredAddress = Yii::$app->request->get('address', null); $queryUserCount = new Query(); $queryUserCount->select('*')->from('users u'); $queryUser = new Query(); $queryUser->select('u.id as id, u.first_name as fname, u.last_name as lname, u.is_admin as is_admin, ' . 'u.photo as photo, u.is_complete as is_complete, avg(r.rate) as rate')->from('users u')->leftJoin('rates r', 'r.rated_id = u.id')->groupBy('id, fname, lname, is_admin, photo, is_complete')->orderBy('rate desc')->offset(intval(Yii::$app->params['driversPerPageLimit'] * $currPage))->limit(Yii::$app->params['driversPerPageLimit']); if ($filteredAddress) { $queryUser->leftJoin('rel_user_address rua', 'rua.user_id = u.id'); $queryUser->leftJoin('addresses adr', 'adr.id = rua.address_id'); $queryUser->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]); $queryUserCount->leftJoin('rel_user_address rua', 'rua.user_id = u.id'); $queryUserCount->leftJoin('addresses adr', 'adr.id = rua.address_id'); $queryUserCount->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]); } else { $queryUser->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]); $queryUserCount->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]); } $totalCount = $queryUserCount->count(); $context = ['is_guest' => Yii::$app->user->isGuest, 'curr_user_id' => Yii::$app->user->isGuest ? null : Yii::$app->user->id, 'is_admin' => $_currUser ? 1 === $_currUser->is_admin : false, 'is_drivers' => 0, 'total_pages' => ceil($totalCount / Yii::$app->params['driversPerPageLimit']), 'total_count' => $totalCount, 'current_page' => $currPage, 'filtered_address' => $filteredAddress, 'address_start' => Yii::$app->request->get('address_start', null), 'address_dest' => Yii::$app->request->get('address_dest', null), 'time_start' => Yii::$app->request->get('time_start', null), 'message' => Yii::$app->request->get('message', null)]; $rows = $queryUser->all(); foreach ($rows as $row) { $users[] = ['id' => $row['id'], 'first_name' => $row['fname'], 'last_name' => $row['lname'], 'is_admin' => $row['is_admin'], 'rating' => sprintf("%.2f", $row['rate']), 'photo' => $row['photo'], 'is_complete' => $row['is_complete']]; } return $this->render('drivers', ['drivers' => $users, 'context' => $context]); }
public function actionIndex() { $params = $_REQUEST; //TODO Calling unknown method: yii\web\Request::getParams() why? //$params = \Yii::$app->request->getParams(); var_dump($params); exit; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('user')->orderBy($sort)->select("*"); /*if ($datefilter['from']) { $query->andWhere("createdAt >= '" . $datefilter['from'] . "' "); } if ($datefilter['to']) { $query->andWhere("createdAt <= '" . $datefilter['to'] . "'"); }*/ $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
/** * Dashboard * * @return mixed */ public function actionIndex() { // Unique users today $usersQuery = new Query(); $usersQuery->select(['domain_userid'])->from('{{%event}}')->where('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->distinct(); // Unique users total $totalUsersQuery = new Query(); $totalUsersQuery->select(['users'])->from('{{%stats_performance}}')->where('DATE(day) != CURRENT_DATE'); // Submissions today $submissionsQuery = new Query(); $submissionsQuery->select(['id'])->from('{{%form_submission}}')->andWhere('DATE(FROM_UNIXTIME(created_at)) = CURRENT_DATE'); // Submissions total $totalSubmissionsQuery = new Query(); $totalSubmissionsQuery->select(['id'])->from('{{%form_submission}}'); // If not admin if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) { // Add user filter to queries $formIds = Yii::$app->user->getAssignedFormIds(); $formIds = count($formIds) > 0 ? $formIds : 0; // Important restriction $usersQuery->andFilterWhere(['app_id' => $formIds]); $totalUsersQuery->andFilterWhere(['app_id' => $formIds]); $submissionsQuery->andFilterWhere(['form_id' => $formIds]); $totalSubmissionsQuery->andFilterWhere(['form_id' => $formIds]); } // Execute queries $users = $usersQuery->count(); $totalUsers = $totalUsersQuery->sum('users'); $submissions = $submissionsQuery->count(); $totalSubmissions = $totalSubmissionsQuery->count(); // Add today data to total $totalUsers = $totalUsers + $users; // Users / submissions = Conversion rate $submissionRate = 0; if ($users > 0 && $submissions > 0) { $submissionRate = round($submissions / $users * 100); } $totalSubmissionRate = 0; if ($totalUsers > 0 && $totalSubmissions > 0) { $totalSubmissionRate = round($totalSubmissions / $totalUsers * 100); } // Most viewed forms list by unique users $formsByUsersQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(DISTINCT(e.domain_userid)) AS users'])->from('{{%event}} AS e')->innerJoin('{{%form}} AS f', 'e.app_id = f.id')->where(['event' => 'pv'])->andWhere('DATE(FROM_UNIXTIME(collector_tstamp)) = CURRENT_DATE')->groupBy(['id'])->orderBy('users DESC')->limit(Yii::$app->params['ListGroup.listSize']); // Forms list by submissions $formsBySubmissionsQuery = (new Query())->select(['f.id', 'f.name', 'COUNT(fs.id) AS submissions'])->from('{{%form_submission}} AS fs')->innerJoin('{{%form}} as f', 'fs.form_id = f.id')->where('DATE(FROM_UNIXTIME(fs.created_at)) = CURRENT_DATE')->groupBy(['f.id'])->orderBy('submissions DESC')->limit(Yii::$app->params['ListGroup.listSize']); // Last updated forms list $lastUpdatedFormsQuery = (new Query())->select(['id', 'name', 'updated_at'])->from('{{%form}} AS f')->where('DATE(FROM_UNIXTIME(updated_at)) = CURRENT_DATE')->orderBy('updated_at DESC')->limit(Yii::$app->params['ListGroup.listSize']); // If not admin if (!empty(Yii::$app->user) && !Yii::$app->user->can("admin")) { // Add user filter to queries $formIds = Yii::$app->user->getAssignedFormIds(); $formIds = count($formIds) > 0 ? $formIds : 0; // Important restriction $formsByUsersQuery->andFilterWhere(['f.id' => $formIds]); $formsBySubmissionsQuery->andFilterWhere(['fs.form_id' => $formIds]); $lastUpdatedFormsQuery->andFilterWhere(['f.id' => $formIds]); } $formsByUsers = $formsByUsersQuery->all(); $formsBySubmissions = $formsBySubmissionsQuery->all(); $lastUpdatedForms = $lastUpdatedFormsQuery->all(); return $this->render('index', ['users' => $users, 'submissions' => $submissions, 'submissionRate' => $submissionRate, 'totalUsers' => $totalUsers, 'totalSubmissions' => $totalSubmissions, 'totalSubmissionRate' => $totalSubmissionRate, 'formsByUsers' => $formsByUsers, 'formsBySubmissions' => $formsBySubmissions, 'lastUpdatedForms' => $lastUpdatedForms]); }
protected function resetRank() { $author = YBoardMember::findOne($this->user_id); if ($author !== null) { $query = new Query(); $query->select('id')->from('YBoardRank')->orderBy('min_posts ASC')->where('min_posts>=' . $author->totalReplies)->limit(1); $count = $query->count(); $id = $query->one(); if ($author->rank_id != $id['id'] && $count > 0) { $author->setAttribute('rank_id', $id['id']); $author->save(); } } }
public static function find($module, $params = [], $page = 30) { $relation = ['country' => ['translations'], 'city' => ['country', 'country.translations', 'airports', 'airports.translations', 'translations'], 'direction' => ['iata', 'from', 'from.translations', 'from.country', 'from.country.translations', 'to', 'to.translations', 'to.country', 'to.country.translations'], 'airport' => ['translations', 'country', 'country.translations', 'city', 'city.translations'], 'airline' => []]; if (!empty($params['relation'])) { $relation = array_merge($relation, $params['relation']); unset($params['relation']); } $model = 'common\\models\\Received' . ucfirst($module); if (!class_exists($model)) { throw new ErrorException('no module > ' . $module); } $query = new Query(); $query->select(["{$module}.id"])->from([$module => "received_{$module}"]); if ($module == 'direction') { $query->addSelect(["{$module}.popularity"]); $query->orderBy(['direction.popularity' => SORT_DESC]); $query->groupBy(['direction.city_to_code', 'direction.city_from_code']); } foreach ($params as $key => $value) { $postfix = ''; if ($key == 'from' || $key == 'to') { $postfix = "_{$key}"; $key = 'city'; } if (is_array($value) && $value) { $query->leftJoin([$key . $postfix => "received_{$key}"], "{$module}.{$key}{$postfix}_code = {$key}{$postfix}.code"); foreach ($value as $_key => $_value) { if (is_array($_value) && $_value) { $query->leftJoin([$_key . $postfix => "received_{$_key}"], "{$key}{$postfix}.{$_key}_code = {$_key}{$postfix}.code"); foreach ($_value as $__key => $__value) { $query->andFilterWhere(["{$_key}{$postfix}.{$__key}" => $__value]); } } else { $query->andFilterWhere(["{$key}{$postfix}.{$_key}" => $_value]); } } } else { $query->andFilterWhere(["{$module}.{$key}{$postfix}" => $value]); } } $total = $query->count(); $id = $query->column(); if ($page > 1) { if (!empty($_GET['page'])) { $_page = $_GET['page']; } else { $_page = 0; } $id = array_slice($id, $_page > 1 ? ($_page - 1) * $page : 0, $page); // VarDumper::dump($id); } /** @var $model \yii\db\ActiveRecord */ $query = $model::find()->where(['id' => $id])->asArray(); if ($module == 'direction') { $query->orderBy(['received_direction.popularity' => SORT_DESC]); } $query->with($relation[$module]); if ($page == 1) { return $query->all(); } $pagination = new Pagination(['totalCount' => $total, 'pageSize' => $page, 'pageSizeParam' => false]); if ($page == 1) { $page = 0; } $query->offset(0)->limit($page); return [$query->all(), $pagination]; }
public function isMatiereComplete($num_tour, $item_id) { //on récupère l'id de lamatière concernée $query_matiere = new Query(); $query_matiere->select('mat_id')->from('item')->where('id = ' . $item_id); $mat_id = $query_matiere->all()[0]['mat_id']; //on récupère la liste desitems non terminés pour cette matièe et ce tour $query_complete = new Query(); $query_complete->select('done')->from('tour')->where('mat_id = ' . $mat_id . ' AND numero_tour = ' . $num_tour . ' AND done = 0'); $count = $query_complete->count(); //si il n'y a aucun résultat, alors la matière est Complète $bool = $count == 0 ? "y" : "n"; return $mat_id . "-" . $bool; }
public function actionTextsearch() { if (strlen($_POST['text']) > 2) { $query = new Query(); $query->select(['id', 'id_papka', 'title', 'text'])->from('pages')->where(['status' => '1'])->andWhere(['like', 'text', $_POST['text']])->limit(10); $all = $query->count('*'); $name = '<h1 class="page-count">Найдено: ' . $all . ' стр.</h1>'; foreach ($query->each() as $rowname) { $pattern = '/(' . $_POST['text'] . ')/'; $replacement = '<strong class="search">$1</strong>'; $finder = preg_replace($pattern, $replacement, $rowname['text']); $name .= '<h3 class="page-item">' . $rowname['title'] . '<a class="page-link" href="/' . $rowname['id_papka'] . '?i=' . $rowname['id'] . '"> Читать...</a></h3><div class="finder">' . $finder . '</div>'; } return $this->render('text-search', ['model' => $name]); } else { return $this->goHome(); } }
public static function All_excel($select, $where, $groupby, $count) { $db = new Query(); $db->from('apteki'); $select[] = 'ur_l.inn as inn2'; $select[] = 'ur_l.id as id2'; $select[] = 'region.id as rid'; $db->select($select); $db->LeftJoin('ur_l', 'apteki.ur_l_id = ur_l.id'); //$db->Where('=', 'region.id',$where[region]); if ($where['region']) { $db->andWhere(['=', 'region.id', $where['region']]); } if ($where['regional']) { $db->andWhere(['=', 'users.id', $where['regional']]); } if ($groupby['ur_l.id']) { $db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id'); $db->InnerJoin('region', 'region_ur_l.id_reg = region.id'); $db->InnerJoin('ur_l as u2', 'ur_l.id=region_ur_l.id_ur'); $db->GroupBy('ur_l.id'); } if (!$groupby['ur_l.id']) { $db->LeftJoin('region', 'apteki.region_id = region.id'); } //регионы Юр.лиц //$db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id'); //$db->InnerJoin('region', 'region_ur_l.id_reg = region.id'); $db->LeftJoin('users', 'apteki.regional_id = users.id'); $db->LeftJoin('view_pi', 'apteki.pi_id = view_pi.id'); $db->LeftJoin('sb_site_users', 'sb_site_users.ur_l_id = ur_l.id'); // $db->GroupBy('apteki.id'); // $db->orderBy('region.name','ur_l.name','apteki.name'); $db->orderBy(['region.name' => SORT_ASC, 'ur_l.name' => SORT_ASC]); $data = $db->all(); $countAll = $db->count(); for ($i = 0; $i < count($data); $i++) { /* $count = (new Query()) ->from('apteki') ->where(['ur_l_id' => $data[$i]['id2']]) ->count(); */ if ($data[$i]['ur_l__farmopeka']) { $data[$i]['ur_l__farmopeka'] = "Да"; } else { $data[$i]['ur_l__farmopeka'] = "Нет"; } if ($data[$i]['apteki__farmopeka']) { $data[$i]['apteki__farmopeka'] = "Да"; } else { $data[$i]['apteki__farmopeka'] = "Нет"; } if ($data[$i]['ur_l__plat']) { $data[$i]['ur_l__plat'] = "Да"; } else { $data[$i]['ur_l__plat'] = "Нет"; } if ($count['apteki']) { $count2 = new Query(); $count2->from('apteki'); $count2->where(['ur_l_id' => $data[$i]['id2']]); $count2->andWhere(['apteki.region_id' => $data[$i]['rid']]); if (!isset($temp)) { $temp = ""; } if ($temp != $data[$i]['rid'] . $data[$i]['id2']) { $counts = $count2->count(); $data[$i]['apteki'] = $counts; $no = 1; } else { $data[$i]['apteki'] = ""; $data[$i]['ur_l'] = ""; $no = 2; } $temp = $data[$i]['rid'] . $data[$i]['id2']; } if ($no == 1) { if ($count['ur_l']) { $count2 = new Query(); $count2->from('ur_l'); $count2->where(['inn' => $data[$i]['inn2']]); $counts = $count2->count(); $data[$i]['ur_l'] = $counts; } $data[$i]['count'] = $countAll; } } return $data; }
/** * start de dialog om een contract aan te bieden **/ public function actionDialogContr() { $id = ArrayHelper::getValue($_POST, 'id'); $calenderevent = CalendarEvents::findOne(['id' => $id]); $start = substr($calenderevent->start, 0, 10); //Zie of er op die dag all contracten die zijn geaccepteerd. // $query = new Query(); $query->from('calendar_events'); $query->where(['start' => substr($calenderevent->start, 0, 10), 'user_id' => $calenderevent->user_id]); $query->andWhere(['like', 'code_title', 'Accep']); if ($query->count() > 0) { return $this->renderAjax('_dialogContrbezet', ['calenderevent' => $calenderevent]); } $usercv = UserCv::findOne(['user_id' => $calenderevent->user_id]); $user = User::findOne(['id' => $calenderevent->user_id]); $contractdeal = new ContractDeals(); $company = Company::findOne(['id' => $user->company_id]); $usercontactperson = Gebruikers::findOne(['id' => $calenderevent->user_id]); return $this->renderAjax('_dialogContr', ['calenderevent' => $calenderevent, 'company' => $company, 'usercv' => $usercv, 'contractdeal' => $contractdeal, 'user' => $user]); }
/** * Updates all Entry of the Tweet with votecount and avg rating * * @param \app\models\Tweet $Tweet Tweet Object */ private function UpdateEntry($Tweet) { $Entry = Entry::findOne($Tweet->entry_id); $avgQuery = new Query(); $avgQuery->from(Tweet::tableName())->where(['entry_id' => $Entry->id, 'needs_validation' => 0, 'old_vote' => 0]); $avgRating = $avgQuery->average('rating'); $votestQuery = new Query(); $votestQuery->from(Tweet::tableName())->where(['entry_id' => $Entry->id, 'needs_validation' => 0, 'old_vote' => 0]); $votes = $votestQuery->count(); $Entry->votes = $votes; $Entry->avg_rating = round($avgRating, 2); $Entry->save(); }
public function actionIndex() { $role = UserRoleDetector::getUserRole(); Yii::$app->response->format = Response::FORMAT_JSON; if (isset($role)) { echo json_encode(array('status' => 0, 'error_code' => Codes::$UNAUTHORIZED, 'errors' => StatusCodeMessage::$UNAUTHORIZED), JSON_PRETTY_PRINT); } else { $params = Yii::$app->getRequest(); $filter = array(); $sort = ""; $page = isset($params->page) ? $params->page : 1; $limit = isset($params->limit) ? $params->limit : 10; $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params->filter)) { $filter = (array) json_decode($params->filter); } if (isset($params->sort)) { $sort = $params->sort; if (isset($params->order)) { if ($params->order == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('users')->orderBy($sort)->select("user_id, login, email, name, surname, street, house_nr, flat_nr, zipcode, city"); $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); echo json_encode(array('status' => 1, 'code' => 200, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); } }
/** * @return int|string */ public function getCount() { $query = new Query(); $query->select(['id'])->from('{{%teacher_student}}')->where(['student_id' => $this->id]); return $query->count(); }
public function actionGenerateDb() { $session = Yii::$app->session; $flash = 'Database generated. 10 000 teachers and 100 000 students.'; $break = false; $query = new Query(); $query->select(['id'])->from('{{%teacher}}'); $teachers_count = $query->count(); $query = new Query(); $query->select(['id'])->from('{{%student}}'); $students_count = $query->count(); for ($i = $teachers_count; $i < 10000; $i++) { $command = Yii::$app->db->createCommand(' INSERT INTO {{%teacher}} (name, phone, gender) VALUES(\'' . self::generateString(rand(6, 10)) . '\', \'' . self::generatePhone() . '\', \'' . self::generateGender() . '\') '); $command->execute(); if ($i - $teachers_count == 5000) { $break = true; break; } $break = true; } if (!$break) { for ($i = $students_count; $i < 100000; $i++) { $command = Yii::$app->db->createCommand(' INSERT INTO {{%student}} (name, email, birth_dt, level) VALUES(\'' . self::generateString(rand(6, 10)) . '\', \'' . self::generateEmail() . '\', \'' . self::generateDate() . '\', \'' . self::generateLevel() . '\') '); $command->execute(); if ($i - $students_count == 30000) { $break = true; break; } } } if (!$break) { $query = new Query(); $query->select(['teacher_id'])->from('{{%teacher_student}}'); $teacher_max = $query->max('teacher_id') + 1; for ($i = $teacher_max; $i <= 10000; $i++) { for ($j = 0; $j < rand(2, 5); $j++) { $command = Yii::$app->db->createCommand(' INSERT INTO {{%teacher_student}} (teacher_id, student_id) VALUES(\'' . $i . '\', \'' . rand(1, 100000) . '\') '); try { $command->execute(); } catch (\yii\db\Exception $e) { null; } } if ($i - $teacher_max == 5000) { $break = true; break; } } } if ($break) { $query->select(['id'])->from('{{%teacher}}'); $teachers = $query->count(); $query->select(['id'])->from('{{%student}}'); $students = $query->count(); $flash = 'Teachers = ' . $teachers . '. Students = ' . $students . '. Please, press "Generate teachers and students" again!'; } $session->setFlash('DbGenerated', $flash); $this->redirect('/'); }