public static function getAllUsersByActivityId($activityId)
{
$result = Yii::$app->db->createCommand('SELECT au.activity_id,u.name,u.photo,u.sex,au.user_id,au.score_appraise FROM ' . self::tableName() . ' tu LEFT JOIN ' . UsersInfo::tableName() . ' u ON au.user_id = u.id WHERE au.activity_id=:activity_id', [':activity_id' => $activityId])->queryAll();
return $result;
}
public static function getCountUserByLevelId($levelId, $count)
{
$sql = "SELECT u.level_id,ui.photo,ui.name,ui.birthday,u.id FROM " . self::tableName() . " u LEFT JOIN " . UsersInfo::tableName() . " ui ON u.id = ui.user_id WHERE u.level_id='" . $levelId . "' LIMIT 0," . $count;
$result = Yii::$app->db->createCommand($sql)->queryAll();
return $result;
}
public static function getUserLevelAndScoreByUserIdLevelId($userId, $levelId)
{
$sql = "SELECT ul.*,tu.practice_score,tu.theory_score,tu.rule_score,tu.score_appraise,ui.photo,ui.account_location,ui.contact_address,ui.contact_postcode,ul.photo as user_level_photo FROM " . self::tableName() . " ul LEFT JOIN " . TrainUsers::tableName() . " tu ON ul.train_id = tu.train_id AND ul.user_id = tu.user_id LEFT JOIN " . UsersInfo::tableName() . " ui ON ui.user_id = ul.user_id WHERE ul.user_id=:user_id AND ul.level_id = :level_id AND ul.status in (" . UsersLevel::REG . "," . UsersLevel::PAY . "," . UsersLevel::SEND_CARD . "," . UsersLevel::LEVEL_UP . ") AND tu.status = " . TrainUsers::PASS;
$result = Yii::$app->db->createCommand($sql, [':user_id' => $userId, ':level_id' => $levelId])->queryOne();
return $result;
}
/**
* @param $trainId.
*/
public static function getAllUsersByTrainId($trainId)
{
$result = Yii::$app->db->createCommand('SELECT tu.train_id,u.name,u.photo,u.sex,tu.user_id,tu.score_appraise FROM ' . self::tableName() . ' tu LEFT JOIN ' . UsersInfo::tableName() . ' u ON tu.user_id = u.user_id WHERE tu.train_id=:train_id', [':train_id' => $trainId])->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]);
}
