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('/');
}