public static function getLevelUpInfoByUserIdOrder($userId, $levelOrder) { $sql = "SELECT u.score,u.lesson,u.credit,u.create_time,l.credit as level_credit,l.score as level_score,l.name,l.login_duration,l.content FROM " . self::tableName() . " ul LEFT JOIN " . Users::tableName() . " u ON ul.user_id = u.id LEFT JOIN " . Level::tableName() . " l ON ul.level_id = l.id WHERE ul.user_id=:user_id AND l.order=:level_order"; $result = Yii::$app->db->createCommand($sql, [':user_id' => $userId, ':level_order' => $levelOrder])->queryOne(); return $result; }
public function getAllInfoById($activityId) { $result = Yii::$app->db->createCommand('SELECT u.username,u.score,uf.birthday,u.level_id,uf.photo FROM ' . self::tableName() . ' au LEFT JOIN ' . UsersInfo::tableName() . ' uf ON au.user_id = uf.id LEFT JOIN ' . Users::tableName() . ' u ON au.user_id = u.id WHERE au.activity_id=:activity_id', [':activity_id' => $activityId])->queryAll(); return $result; }
public function actionIndex() { $keyword = Yii::$app->request->get('keyword', ''); $levelId = Yii::$app->request->get('level_id', ''); $district = Yii::$app->request->get('district', ''); $sex = Yii::$app->request->get('sex', ''); if (empty($keyword) && empty($levelId) && empty($district) && empty($sex)) { throw new ServerErrorHttpException('шп╖ш╛УхЕехЕ│щФохнЧя╝Б'); } $levelList = Level::getAll(); $districtList = Train::$districtList; $userName = ''; $certificateNumber = ''; $credentialsNumber = ''; if (preg_match("/^[- ]+\$/", $keyword)) { $userName = $keyword; } else { if (strlen($keyword) > 10) { $credentialsNumber = $keyword; } else { $certificateNumber = $keyword; } } $order = Yii::$app->request->get('order'); $query = Users::find()->from(Users::tableName() . ' u')->select("u.*,ui.account_location,ui.sex")->leftJoin(UsersLevel::tableName() . ' ul', ' u.id = ul.user_id')->leftJoin(UsersInfo::tableName() . ' ui', ' u.id = ui.user_id'); if (!empty($order)) { $query->orderBy($order); } $query->andFilterWhere(['!=', 'u.level_id', '1']); if (!empty($levelId)) { $query->andFilterWhere(['u.level_id' => $levelId]); } if (!empty($district)) { $query->andFilterWhere(['account_location' => $district]); } if (!empty($sex)) { $query->andFilterWhere(['sex' => $sex]); } if (!empty($userName)) { $query->andFilterWhere(['like', 'name', $userName]); } if (!empty($certificateNumber)) { $query->andFilterWhere(['like', 'certificate_number', $certificateNumber]); } if (!empty($credentialsNumber)) { $query->andFilterWhere(['like', 'credentials_number', $credentialsNumber]); } $pages = new Pagination(['totalCount' => $query->count(), 'pageSize' => 15]); $models = $query->offset($pages->offset)->limit($pages->limit)->all(); $result = []; if (!empty($models)) { foreach ($models as $key => $val) { $result[$key]['name'] = $val['username']; $result[$key]['photo'] = UsersInfo::getPhotoByUserId($val['id']); $result[$key]['birthday'] = UsersInfo::getBirthdayByUserId($val['id']); $result[$key]['level_id'] = $val['level_id']; $result[$key]['user_id'] = $val['id']; } } $data = ['models' => $result, 'pages' => $pages, 'count' => $query->count(), 'levelList' => $levelList, 'districtList' => $districtList, 'keyword' => empty($keyword) ? '' : $keyword, 'levelId' => empty($levelId) ? '' : $levelId, 'district' => empty($district) ? '' : $district, 'sex' => empty($sex) ? '' : $sex]; return $this->render('index', ['data' => $data]); }