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