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