public function getCompetitionLink()
{
$competition = Statistics::getCompetition(array('competitionId' => $this->competitionId, 'cellName' => $this->competition->cellName, 'cityName' => $this->competition->cityName));
return CHtml::link(ActiveRecord::getModelAttributeValue($competition, 'name'), $competition['url']);
}
public static function getResults($id)
{
$db = Yii::app()->wcaDb;
//个人排名
$ranks = RanksSingle::model()->with(array('average' => array('together' => true), 'person' => array('together' => true), 'event' => array('together' => true)))->findAllByAttributes(array('personId' => $id), array('order' => 'event.rank ASC'));
$personRanks = array();
foreach ($ranks as $rank) {
$personRanks[$rank->eventId] = $rank;
}
//sum of ranks
$sumOfRanks = RanksSum::model()->findAllByAttributes(array('personId' => $id), array('order' => 'type DESC'));
foreach ($sumOfRanks as $key => $sumOfRank) {
$sumOfRank->getRanks();
}
//奖牌数量
$command = $db->createCommand();
$command->select(array('eventId', 'sum(CASE WHEN pos=1 AND roundId IN ("c", "f") AND best>0 THEN 1 ELSE 0 END) AS gold', 'sum(CASE WHEN pos=2 AND roundId IN ("c", "f") AND best>0 THEN 1 ELSE 0 END) AS silver', 'sum(CASE WHEN pos=3 AND roundId IN ("c", "f") AND best>0 THEN 1 ELSE 0 END) AS bronze', 'sum(CASE WHEN value1>0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value2>0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value3>0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value4>0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value5>0 THEN 1 ELSE 0 END)
AS solve', 'sum(CASE WHEN value1>-2 AND value1!=0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value2>-2 AND value2!=0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value3>-2 AND value3!=0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value4>-2 AND value4!=0 THEN 1 ELSE 0 END)
+sum(CASE WHEN value5>-2 AND value5!=0 THEN 1 ELSE 0 END)
AS attempt'))->from('Results')->where('personId=:personId', array(':personId' => $id))->group('eventId');
foreach ($command->queryAll() as $row) {
if (isset($personRanks[$row['eventId']])) {
$personRanks[$row['eventId']]->medals = $row;
}
}
//历史成绩
$competitions = array();
$personResults = array();
$eventId = '';
$best = $average = PHP_INT_MAX;
$results = Results::model()->with(array('competition', 'competition.country', 'round', 'event'))->findAllByAttributes(array('personId' => $id), array('order' => 'event.rank, competition.year, competition.month, competition.day, round.rank'));
foreach ($results as $result) {
if ($eventId != $result->eventId) {
//重置各值
$eventId = $result->eventId;
$best = $average = PHP_INT_MAX;
$personResults[$eventId] = array();
}
if ($result->best > 0 && $result->best <= $best) {
$result->newBest = true;
$best = $result->best;
}
if ($result->average > 0 && $result->average <= $average) {
$result->newAverage = true;
$average = $result->average;
}
$personResults[$eventId][] = $result;
$competitions[$result->competitionId] = $result->competition;
}
//世锦赛获奖记录
$wcPodiums = Results::model()->with(array('competition', 'event'))->findAllByAttributes(array('personId' => $id, 'roundId' => array('c', 'f'), 'pos' => array(1, 2, 3)), array('condition' => 'competitionId LIKE "WC%"', 'order' => 'competition.year DESC, event.rank ASC'));
//WR们
$historyWR = Results::model()->with(array('competition', 'event', 'round'))->findAllByAttributes(array('personId' => $id), array('condition' => 'regionalSingleRecord="WR" OR regionalAverageRecord="WR"', 'order' => 'event.rank ASC, competition.year DESC, competition.month DESC, competition.day DESC, round.rank DESC'));
//CR们
$historyCR = Results::model()->with(array('competition', 'event', 'round'))->findAllByAttributes(array('personId' => $id), array('condition' => 'regionalSingleRecord NOT IN ("WR", "NR", "") OR regionalAverageRecord NOT IN ("WR", "NR", "")', 'order' => 'event.rank ASC, competition.year DESC, competition.month DESC, competition.day DESC, round.rank DESC'));
//NR们
$historyNR = Results::model()->with(array('competition', 'event', 'round'))->findAllByAttributes(array('personId' => $id), array('condition' => 'regionalSingleRecord="NR" OR regionalAverageRecord="NR"', 'order' => 'event.rank ASC, competition.year DESC, competition.month DESC, competition.day DESC, round.rank DESC'));
//
$firstCompetitionResult = Results::model()->with(array('competition'))->findByAttributes(array('personId' => $id), array('order' => 'competition.year ASC, competition.month ASC, competition.day ASC'));
$lastCompetitionResult = Results::model()->with(array('competition'))->findByAttributes(array('personId' => $id), array('order' => 'competition.year DESC, competition.month DESC, competition.day DESC'));
$overAll = array('gold' => array_sum(array_map(function ($result) {
return $result->medals['gold'];
}, $personRanks)), 'silver' => array_sum(array_map(function ($result) {
return $result->medals['silver'];
}, $personRanks)), 'bronze' => array_sum(array_map(function ($result) {
return $result->medals['bronze'];
}, $personRanks)), 'WR' => count(array_filter($historyWR, function ($result) {
return $result->regionalSingleRecord == 'WR';
})) + count(array_filter($historyWR, function ($result) {
return $result->regionalAverageRecord == 'WR';
})), 'CR' => count(array_filter($historyCR, function ($result) {
return !in_array($result->regionalSingleRecord, array('WR', 'NR', ''));
})) + count(array_filter($historyCR, function ($result) {
return !in_array($result->regionalAverageRecord, array('WR', 'NR', ''));
})), 'NR' => count(array_filter($historyNR, function ($result) {
return $result->regionalSingleRecord == 'NR';
})) + count(array_filter($historyNR, function ($result) {
return $result->regionalAverageRecord == 'NR';
})));
usort($competitions, function ($competitionA, $competitionB) {
$temp = $competitionB->year - $competitionA->year;
if ($temp == 0) {
$temp = $competitionB->month - $competitionA->month;
}
if ($temp == 0) {
$temp = $competitionB->day - $competitionA->day;
}
return -$temp;
});
$temp = array('longitude' => 0, 'latitude' => 0);
$mapData = array();
foreach ($competitions as $key => $competition) {
$temp['longitude'] += $competition->longitude / 1000000.0;
$temp['latitude'] += $competition->latitude / 1000000.0;
$data = Statistics::getCompetition(array('competitionId' => $competition->id, 'cellName' => $competition->cellName, 'cityName' => $competition->cityName));
$data['longitude'] = $competition->longitude / 1000000.0;
$data['latitude'] = $competition->latitude / 1000000.0;
$data['url'] = CHtml::normalizeUrl($data['url']);
$data['date'] = $competition->getDate();
$competition->number = $key + 1;
$mapData[] = $data;
}
$mapCenter = array('longitude' => number_format($temp['longitude'] / count($competitions), 6, ',', ''), 'latitude' => number_format($temp['latitude'] / count($competitions), 6, ',', ''));
return array('id' => $id, 'personRanks' => $personRanks, 'sumOfRanks' => $sumOfRanks, 'personResults' => call_user_func_array('array_merge', array_map('array_reverse', $personResults)), 'wcPodiums' => $wcPodiums, 'historyWR' => $historyWR, 'historyCR' => $historyCR, 'historyNR' => $historyNR, 'overAll' => $overAll, 'score' => $overAll['WR'] * 10 + $overAll['CR'] * 5 + $overAll['NR'], 'firstCompetition' => $firstCompetitionResult->competition, 'lastCompetition' => $lastCompetitionResult->competition, 'mapData' => $mapData, 'mapCenter' => $mapCenter, 'competitions' => array_reverse($competitions), 'user' => User::model()->findByAttributes(array('wcaid' => $id, 'status' => User::STATUS_NORMAL)));
}
