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