private function query(Rule $rule, BattleFilterForm $filter) { $query = (new \yii\db\Query())->select(['kill' => '{{battle}}.[[kill]]', 'death' => '{{battle}}.[[death]]', 'count' => 'COUNT(*)', 'win' => 'SUM(CASE WHEN {{battle}}.[[is_win]] = TRUE THEN 1 ELSE 0 END)'])->from('battle')->leftJoin('lobby', '{{battle}}.[[lobby_id]] = {{lobby}}.[[id]]')->andWhere(['or', ['{{battle}}.[[lobby_id]]' => null], ['<>', '{{lobby}}.[[key]]', 'private']])->andWhere('{{battle}}.[[is_win]] IS NOT NULL')->andWhere('{{battle}}.[[kill]] IS NOT NULL')->andWhere('{{battle}}.[[death]] IS NOT NULL')->andWhere(['{{battle}}.[[rule_id]]' => $rule->id, '{{battle}}.[[is_automated]]' => true])->groupBy(['{{battle}}.[[kill]]', '{{battle}}.[[death]]']); if (!$filter->hasErrors()) { if ($filter->map != '') { $query->innerJoin('map', '{{battle}}.[[map_id]] = {{map}}.[[id]]'); $query->andWhere(['{{map}}.[[key]]' => $filter->map]); } if ($filter->weapon != '') { $query->innerJoin('weapon', '{{battle}}.[[weapon_id]] = {{weapon}}.[[id]]'); switch (substr($filter->weapon, 0, 1)) { default: $query->andWhere(['{{weapon}}.[[key]]' => $filter->weapon]); break; case '@': $query->innerJoin('weapon_type', '{{weapon}}.[[type_id]] = {{weapon_type}}.[[id]]'); $query->andWhere(['{{weapon_type}}.[[key]]' => substr($filter->weapon, 1)]); break; case '+': $query->innerJoin('subweapon', '{{weapon}}.[[subweapon_id]] = {{subweapon}}.[[id]]'); $query->andWhere(['{{subweapon}}.[[key]]' => substr($filter->weapon, 1)]); break; case '*': $query->innerJoin('special', '{{weapon}}.[[special_id]] = {{special}}.[[id]]'); $query->andWhere(['{{special}}.[[key]]' => substr($filter->weapon, 1)]); break; } } } return $query->createCommand()->queryAll(); }
private function getData(User $user, BattleFilterForm $filter) { $query = (new \yii\db\Query())->select(['map_key' => 'MAX({{map}}.[[key]])', 'rule_key' => 'MAX({{rule}}.[[key]])', 'result' => '(CASE WHEN {{battle}}.[[is_win]] = TRUE THEN \'win\' ELSE \'lose\' END)', 'count' => 'COUNT(*)'])->from('battle')->innerJoin('map', '{{battle}}.[[map_id]] = {{map}}.[[id]]')->innerJoin('rule', '{{battle}}.[[rule_id]] = {{rule}}.[[id]]')->leftJoin('lobby', '{{battle}}.[[lobby_id]] = {{lobby}}.[[id]]')->leftJoin('game_mode', '{{rule}}.[[mode_id]] = {{game_mode}}.[[id]]')->leftJoin('weapon', '{{battle}}.[[weapon_id]] = {{weapon}}.[[id]]')->leftJoin('weapon_type', '{{weapon}}.[[type_id]] = {{weapon_type}}.[[id]]')->leftJoin('subweapon', '{{weapon}}.[[subweapon_id]] = {{subweapon}}.[[id]]')->leftJoin('special', '{{weapon}}.[[special_id]] = {{special}}.[[id]]')->andWhere(['{{battle}}.[[user_id]]' => $user->id])->andWhere(['in', '{{battle}}.[[is_win]]', [true, false]])->groupBy(['{{battle}}.[[map_id]]', '{{battle}}.[[rule_id]]', '{{battle}}.[[is_win]]']); if ($filter && !$filter->hasErrors()) { $this->filter($query, $filter); } $maps = Map::find()->all(); $rules = Rule::find()->all(); $ret = []; foreach ($maps as $map) { $tmp = []; foreach ($rules as $rule) { $tmp[$rule->key] = ['win' => 0, 'lose' => 0]; } $ret[$map->key] = $tmp; } foreach ($query->createCommand()->queryAll() as $row) { $row = (object) $row; $ret[$row->map_key][$row->rule_key][$row->result] = (int) $row->count; } $maps2 = []; foreach ($maps as $map) { $maps2[$map->key] = Yii::t('app-map', $map->name); } asort($maps2); $rules2 = []; foreach ($rules as $rule) { $rules2[$rule->key] = Yii::t('app-rule', $rule->name); } asort($rules2); return ['data' => $ret, 'mapNames' => $maps2, 'ruleNames' => $rules2]; }
public function getList(User $user, BattleFilterForm $filter) { $query = (new \yii\db\Query())->select(['reason_id' => '{{death_reason}}.[[id]]', 'count' => 'SUM({{battle_death_reason}}.[[count]])'])->from('battle')->innerJoin('battle_death_reason', '{{battle}}.[[id]] = {{battle_death_reason}}.[[battle_id]]')->innerJoin('death_reason', '{{battle_death_reason}}.[[reason_id]] = {{death_reason}}.[[id]]')->leftJoin('rule', '{{battle}}.[[rule_id]] = {{rule}}.[[id]]')->leftJoin('game_mode', '{{rule}}.[[mode_id]] = {{game_mode}}.[[id]]')->leftJoin('lobby', '{{battle}}.[[lobby_id]] = {{lobby}}.[[id]]')->leftJoin('map', '{{battle}}.[[map_id]] = {{map}}.[[id]]')->leftJoin('weapon', '{{battle}}.[[weapon_id]] = {{weapon}}.[[id]]')->leftJoin('weapon_type', '{{weapon}}.[[type_id]] = {{weapon_type}}.[[id]]')->leftJoin('subweapon', '{{weapon}}.[[subweapon_id]] = {{subweapon}}.[[id]]')->leftJoin('special', '{{weapon}}.[[special_id]] = {{special}}.[[id]]')->andWhere(['{{battle}}.[[user_id]]' => $user->id])->groupBy('{{death_reason}}.[[id]]'); if ($filter && !$filter->hasErrors()) { $this->filter($query, $filter); } $list = $query->createCommand()->queryAll(); // 必要な死因名の一覧を作る $deathReasons = []; array_map(function ($o) use(&$deathReasons) { $deathReasons[$o->id] = $o->getTranslatedName(); }, DeathReason::findAll(['id' => array_map(function ($row) { return $row['reason_id']; }, $list)])); $ret = array_map(function ($row) use($deathReasons) { return (object) ['name' => @$deathReasons[$row['reason_id']] ?: '?', 'count' => (int) $row['count']]; }, $list); usort($ret, function ($a, $b) { if ($a->count !== $b->count) { return $b->count - $a->count; } return strcasecmp($a->name, $b->name); }); return $ret; }
public function run() { $request = Yii::$app->getRequest(); $user = User::findOne(['screen_name' => $request->get('screen_name')]); if (!$user) { throw new NotFoundHttpException(Yii::t('app', 'Could not find user')); } // リスト表示モード切替 if ($request->get('v') != '') { $view = $request->get('v'); if ($view === 'simple' || $view === 'standard') { Yii::$app->response->cookies->add(new Cookie(['name' => 'battle-list', 'value' => $view, 'expire' => time() + 86400 * 366])); } $next = $_GET; unset($next['v']); $next[0] = 'show/user'; $this->controller->redirect(Url::to($next)); return; } $battle = Battle::find()->with(['lobby', 'rule', 'rule.mode', 'map', 'weapon', 'weapon.subweapon', 'weapon.special', 'rank', 'rankAfter']); $filter = new BattleFilterForm(); $filter->load($_GET); $filter->screen_name = $user->screen_name; if ($filter->validate()) { $battle->filter($filter); } $summary = $battle->summary; $permLink = Url::to(array_merge(['show/user', 'screen_name' => $user->screen_name], $filter->hasErrors() ? [] : $filter->toPermLink()), true); $isPjax = $request->isPjax; $template = $this->viewMode === 'simple' ? 'user.simple.tpl' : 'user.tpl'; return $this->controller->render($template, array_merge(['user' => $user, 'battleDataProvider' => new ActiveDataProvider(['query' => $battle, 'pagination' => ['pageSize' => 100]]), 'summary' => $summary, 'filter' => $filter, 'permLink' => $permLink], $this->makeFilterFormData($user))); }
private function getData(User $user, BattleFilterForm $filter) { $query = (new \yii\db\Query())->select(['map_key' => 'MAX({{map}}.[[key]])', 'map_name' => 'MAX({{map}}.[[name]])', 'result' => '(CASE WHEN {{battle}}.[[is_win]] = TRUE THEN \'win\' ELSE \'lose\' END)', 'count' => 'COUNT(*)'])->from('battle')->innerJoin('map', '{{battle}}.[[map_id]] = {{map}}.[[id]]')->leftJoin('lobby', '{{battle}}.[[lobby_id]] = {{lobby}}.[[id]]')->leftJoin('rule', '{{battle}}.[[rule_id]] = {{rule}}.[[id]]')->leftJoin('game_mode', '{{rule}}.[[mode_id]] = {{game_mode}}.[[id]]')->leftJoin('weapon', '{{battle}}.[[weapon_id]] = {{weapon}}.[[id]]')->leftJoin('weapon_type', '{{weapon}}.[[type_id]] = {{weapon_type}}.[[id]]')->leftJoin('subweapon', '{{weapon}}.[[subweapon_id]] = {{subweapon}}.[[id]]')->leftJoin('special', '{{weapon}}.[[special_id]] = {{special}}.[[id]]')->andWhere(['{{battle}}.[[user_id]]' => $user->id])->andWhere(['in', '{{battle}}.[[is_win]]', [true, false]])->groupBy(['{{battle}}.[[map_id]]', '{{battle}}.[[is_win]]']); if ($filter && !$filter->hasErrors()) { $this->filter($query, $filter); } $maps = []; foreach ($query->createCommand()->queryAll() as $row) { $row = (object) $row; if (!isset($maps[$row->map_key])) { $maps[$row->map_key] = ['name' => Yii::t('app-map', $row->map_name), 'win' => 0, 'lose' => 0]; } $maps[$row->map_key][$row->result] = (int) $row->count; } return $maps; }
public function run() { $request = Yii::$app->getRequest(); $user = User::findOne(['screen_name' => $request->get('screen_name')]); if (!$user) { throw new NotFoundHttpException(Yii::t('app', 'Could not find user')); } $battle = $user->getBattles()->with(['rule', 'map', 'weapon', 'weapon.subweapon', 'weapon.special']); $filter = new BattleFilterForm(); $filter->load($_GET); $filter->screen_name = $user->screen_name; if ($filter->validate()) { $battle->filter($filter); } $isPjax = $request->isPjax; return $this->controller->render('user.tpl', ['user' => $user, 'battleDataProvider' => new ActiveDataProvider(['query' => $battle, 'pagination' => ['pageSize' => 100]]), 'filter' => $filter, 'rules' => $isPjax ? [] : $this->makeRulesList(), 'maps' => $isPjax ? [] : $this->makeMapsList(), 'weapons' => $isPjax ? [] : $this->makeWeaponsList(), 'results' => ['' => Yii::t('app', 'Won / Lost'), 'win' => Yii::t('app', 'Won'), 'lose' => Yii::t('app', 'Lost')]]); }