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