public static function build($statistic, $page = 1)
{
if ($statistic['type'] === 'all') {
$bestPodiums = array();
$eventIds = array_keys(Events::getNormalEvents());
$temp = $statistic;
$temp['type'] = 'single';
foreach ($eventIds as $eventId) {
$temp['eventId'] = $eventId;
$bestPodiums[$eventId] = self::build($temp);
}
return self::makeStatisticsData($statistic, array('statistic' => $bestPodiums, 'select' => Events::getNormalEvents(), 'selectHandler' => 'Yii::t("event", "$name")', 'selectKey' => 'event'));
}
$eventId = $statistic['eventId'];
$type = self::getType($eventId);
$command = Yii::app()->wcaDb->createCommand();
$command->select(array('r.competitionId', 'r.eventId', 'r.roundId', self::getSelectSum($eventId, $type), 'c.cellName', 'c.cityName', 'c.year', 'c.month', 'c.day'))->from('Results r')->leftJoin('Competitions c', 'r.competitionId=c.id')->where('r.eventId=:eventId', array(':eventId' => $eventId))->andWhere('r.roundId IN ("c", "f")')->andWhere('r.pos IN (1,2,3)')->andWhere('c.countryId="China"')->andWhere("r.{$type} > 0");
$cmd = clone $command;
$command->group('r.competitionId')->order('sum ASC')->having('count(DISTINCT pos)<=3 AND count(pos)>=3')->limit(self::$limit)->offset(($page - 1) * self::$limit);
$columns = array(array('header' => 'Yii::t("common", "Competition")', 'value' => 'CHtml::link(ActiveRecord::getModelAttributeValue($data, "name"), $data["url"])', 'type' => 'raw'), array('header' => 'Yii::t("Competition", "Date")', 'value' => '$data["date"]', 'type' => 'raw'), array('header' => 'Yii::t("statistics", "Sum")', 'value' => 'CHtml::tag("b", array(), $data["formatedSum"])', 'type' => 'raw'), array('header' => 'Yii::t("common", "Average")', 'value' => '$data["formatedAverage"]', 'type' => 'raw'), array('header' => 'Yii::t("statistics", "First")', 'value' => self::makePosValue('first'), 'type' => 'raw'), array('header' => '', 'value' => self::makePosResultValue('first')), array('header' => 'Yii::t("statistics", "Second")', 'value' => self::makePosValue('second'), 'type' => 'raw'), array('header' => '', 'value' => self::makePosResultValue('second')), array('header' => 'Yii::t("statistics", "Third")', 'value' => self::makePosValue('third'), 'type' => 'raw'), array('header' => '', 'value' => self::makePosResultValue('third')));
$rows = array();
foreach ($command->queryAll() as $row) {
$row = self::getCompetition($row);
self::setPodiumsResults($row, $type);
$row['formatedSum'] = self::formatSum($row);
$row['formatedAverage'] = self::formatAverage($row);
$row['date'] = sprintf("%d-%02d-%02d", $row['year'], $row['month'], $row['day']);
$rows[] = $row;
}
$statistic['count'] = $cmd->select('count(DISTINCT r.competitionId) AS count')->queryScalar();
$statistic['rank'] = ($page - 1) * self::$limit;
$statistic['rankKey'] = 'sum';
return self::makeStatisticsData($statistic, $columns, $rows);
}
public static function build($statistic, $page = 1)
{
$gender = isset($statistic['gender']) ? $statistic['gender'] : 'all';
$ranks = self::getRanks($statistic['type'], $gender);
$eventIds = !empty($statistic['eventIds']) ? $statistic['eventIds'] : array_keys(Events::getNormalEvents());
$columns = array(array('header' => 'Yii::t("common", "Region")', 'value' => 'Region::getIconName($data["countryId"], $data["iso2"])', 'type' => 'raw'), array('header' => 'Yii::t("statistics", "Sum")', 'value' => 'CHtml::tag("b", array(), $data["sum"])', 'type' => 'raw'));
//计算未参赛的项目应该排第几
$penalty = self::getPenalties($statistic['type']);
$allPenalties = 0;
foreach ($eventIds as $key => $eventId) {
if (!isset($ranks[$eventId])) {
unset($eventIds[$key]);
continue;
}
$allPenalties += $penalty[$eventId];
}
//计算每个人的排名
$rankSum = array();
foreach ($eventIds as $eventId) {
foreach ($ranks[$eventId] as $countryId => $row) {
if (!isset($rankSum[$countryId])) {
$rankSum[$countryId] = $row;
$rankSum[$countryId]['sum'] = $allPenalties;
}
$rankSum[$countryId]['sum'] += $row['worldRank'] - $penalty[$eventId];
}
$columns[] = array('header' => "CHtml::tag('span', array(\r\n\t\t\t\t\t'class'=>'event-icon event-icon-white event-icon-{$eventId}'\r\n\t\t\t\t), ' ')", 'name' => $eventId, 'type' => 'raw');
}
uasort($rankSum, function ($rankA, $rankB) {
return $rankA['sum'] - $rankB['sum'];
});
$count = count($rankSum);
if ($page > ceil($count / self::$limit)) {
$page = ceil($count / self::$limit);
}
$rows = array();
foreach (array_slice($rankSum, ($page - 1) * self::$limit, self::$limit) as $countryId => $row) {
foreach ($eventIds as $eventId) {
$row[$eventId] = isset($ranks[$eventId][$countryId]) ? $ranks[$eventId][$countryId]['worldRank'] : $penalty[$eventId];
if (isset($ranks[$eventId][$countryId]) && $ranks[$eventId][$countryId]['worldRank'] <= 10) {
$row[$eventId] = CHtml::tag('span', array('class' => 'top10'), $row[$eventId]);
} elseif (!isset($ranks[$eventId][$countryId])) {
$row[$eventId] = CHtml::tag('span', array('class' => 'penalty'), $row[$eventId]);
}
}
$rows[] = $row;
}
$statistic['count'] = $count;
$statistic['rank'] = isset($rows[0]) ? count(array_filter($rankSum, function ($row) use($rows) {
return $row['sum'] < $rows[0]['sum'];
})) : 0;
$statistic['rankKey'] = 'sum';
return self::makeStatisticsData($statistic, $columns, $rows);
}
public static function getPenlties($type, $countryId)
{
if (Region::isContinent($countryId)) {
$countryId = '_' . $countryId;
}
$ranksPenalties = self::model()->findAllByAttributes(array('type' => $type, 'countryId' => $countryId));
$penalties = array();
foreach ($ranksPenalties as $ranksPenalty) {
$penalties[$ranksPenalty->eventId] = $ranksPenalty->penalty;
}
//some countries dont' have penalty for some events
//because no person attend the events
foreach (Events::getNormalEvents() as $eventId => $eventName) {
if (!isset($penalties[$eventId])) {
$penalties[$eventId] = 1;
}
}
return $penalties;
}
public static function build($statistic, $page = 1, $recursive = true)
{
$command = Yii::app()->wcaDb->createCommand();
$command->select(array('personId', 'personName', 'sum(CASE WHEN pos=1 THEN 1 ELSE 0 END) AS gold', 'sum(CASE WHEN pos=2 THEN 1 ELSE 0 END) AS silver', 'sum(CASE WHEN pos=3 THEN 1 ELSE 0 END) AS bronze'))->from('Results')->where('personCountryId="China" AND roundId IN ("c", "f") AND best>0');
if (!empty($statistic['eventIds'])) {
$command->andWhere(array('in', 'eventId', $statistic['eventIds']));
}
$cmd = clone $command;
$command->group('personId')->order('gold DESC, silver DESC, bronze DESC, personName ASC')->having('gold + silver + bronze > 0')->limit(self::$limit)->offset(($page - 1) * self::$limit);
$columns = array(array('header' => 'Yii::t("statistics", "Person")', 'value' => 'Persons::getLinkByNameNId($data["personName"], $data["personId"])', 'type' => 'raw'), array('header' => 'Yii::t("statistics", "Gold")', 'name' => 'gold'), array('header' => 'Yii::t("statistics", "Silver")', 'name' => 'silver'), array('header' => 'Yii::t("statistics", "Bronze")', 'name' => 'bronze'), array('header' => 'Yii::t("statistics", "Sum")', 'value' => 'CHtml::tag("b", array(), $data["gold"] + $data["silver"] + $data["bronze"])', 'type' => 'raw'));
if ($statistic['type'] === 'all') {
$rows = array();
foreach ($command->queryAll() as $row) {
$row['rank'] = sprintf('%d_%d_%d', $row['gold'], $row['silver'], $row['bronze']);
$rows[] = $row;
}
$statistic['count'] = $cmd->select('count(DISTINCT personId) AS count')->andWhere('pos IN (1,2,3)')->queryScalar();
$statistic['rank'] = ($page - 1) * self::$limit;
$statistic['rankKey'] = 'rank';
if ($page > 1 && $rows !== array() && $recursive) {
$stat = self::build($statistic, $page - 1, false);
foreach (array_reverse($stat['rows']) as $row) {
if ($row['rank'] === $rows[0]['rank']) {
$statistic['rank']--;
} else {
break;
}
}
}
return self::makeStatisticsData($statistic, $columns, $rows);
} else {
$medals = array();
$eventIds = array_keys(Events::getNormalEvents());
foreach ($eventIds as $eventId) {
$cmd = clone $command;
$rows = $cmd->andWhere("eventId='{$eventId}'")->queryAll();
$medals[$eventId] = self::makeStatisticsData($statistic, $columns, $rows);
}
return self::makeStatisticsData($statistic, array('statistic' => $medals, 'select' => Events::getNormalEvents(), 'selectHandler' => 'Yii::t("event", "$name")'));
}
}
private function getCompetitionData($model)
{
$wcaDelegates = array();
foreach (User::getDelegates(User::IDENTITY_WCA_DELEGATE) as $delegate) {
$wcaDelegates[$delegate->id] = $delegate->name_zh ?: $delegate->name;
}
$ccaDelegates = array();
foreach (User::getDelegates(User::IDENTITY_CCA_DELEGATE) as $delegate) {
$ccaDelegates[$delegate->id] = $delegate->name_zh ?: $delegate->name;
}
$organizers = User::getOrganizers();
$types = Competition::getTypes();
$checkPersons = Competition::getCheckPersons();
$normalEvents = Events::getNormalEvents();
$otherEvents = Events::getOtherEvents();
$cities = Region::getAllCities();
return array('model' => $model, 'normalEvents' => $normalEvents, 'otherEvents' => $otherEvents, 'cities' => $cities, 'wcaDelegates' => $wcaDelegates, 'ccaDelegates' => $ccaDelegates, 'organizers' => $organizers, 'types' => $types, 'checkPersons' => $checkPersons);
}
public static function build($statistic)
{
$db = Yii::app()->wcaDb;
$command = $db->createCommand()->from('Results rs')->leftJoin('Countries country', 'rs.personCountryId=country.Id')->leftJoin('Competitions c', 'rs.competitionId=c.id')->leftJoin('Persons p', 'rs.personId=p.id AND p.subid=1')->where('eventId=:eventId', array(':eventId' => $statistic['event']))->order('value ASC')->limit(200);
ActiveRecord::applyRegionCondition($command, $statistic['region']);
if (isset($statistic['gender'])) {
switch ($statistic['gender']) {
case 'female':
$command->andWhere('p.gender="f"');
break;
case 'male':
$command->andWhere('p.gender="m"');
break;
}
}
switch ($statistic['type']) {
case 'single':
$temp = array();
for ($i = 1; $i <= 5; $i++) {
$cmd = clone $command;
$temp[] = $cmd->select(array("value{$i} AS value", 'personId', 'personName', 'personCountryId', 'country.name AS countryName', 'iso2', 'competitionId', 'cellName', 'cityName', 'eventId'))->andWhere("value{$i}>0")->queryAll();
}
$top200 = call_user_func_array('array_merge', $temp);
usort($top200, function ($resultA, $resultB) {
$temp = $resultA['value'] - $resultB['value'];
if ($temp == 0) {
$temp = strcmp($resultA['personName'], $resultB['personName']);
}
return $temp;
});
break;
case 'average':
$top200 = $command->select(array('personId', 'personName', 'personCountryId', 'country.name AS countryName', 'iso2', 'competitionId', 'cellName', 'cityName', 'eventId', 'average AS value', 'value1', 'value2', 'value3', 'value4', 'value5'))->andWhere('average>0')->queryAll();
break;
}
$top100 = array();
$lastValue = 0;
$number = $pos = 0;
foreach ($top200 as $result) {
$number++;
if ($lastValue != $result['value']) {
$lastValue = $result['value'];
$pos = $number;
}
if ($pos > 100) {
break;
}
if (isset($statistic['count'])) {
if (!isset($top100[$result['personId']])) {
$top100[$result['personId']] = $result;
$top100[$result['personId']]['count'] = 0;
}
$top100[$result['personId']]['count']++;
} else {
$top100[] = $result;
}
}
if (isset($statistic['count'])) {
usort($top100, function ($rowA, $rowB) {
return $rowB['count'] - $rowA['count'];
});
$columns = array(array('header' => 'Yii::t("statistics", "Person")', 'value' => 'Persons::getLinkByNameNId($data["personName"], $data["personId"])', 'type' => 'raw'), array('header' => 'Yii::t("statistics", "Appearances")', 'value' => 'CHtml::tag("b", array(), $data["count"])', 'type' => 'raw'));
self::$top100s[$statistic['type']][$statistic['event']] = self::makeStatisticsData($statistic, $columns, array_slice($top100, 0, self::$limit));
$events = Events::getNormalEvents();
$eventIds = array_keys($events);
foreach ($eventIds as $eventId) {
if (isset(self::$top100s[$statistic['type']][$eventId])) {
continue;
}
$temp = $statistic;
$temp['event'] = $eventId;
self::build($temp);
}
if ($statistic['type'] === 'average') {
unset($events['444bf'], $events['555bf'], $events['333mbf']);
}
return self::makeStatisticsData($statistic, array('statistic' => self::$top100s[$statistic['type']], 'select' => $events, 'selectHandler' => 'Yii::t("event", "$name")', 'selectKey' => 'event'));
} else {
$top100 = array_map(function ($row) {
return self::getCompetition($row);
}, $top100);
$columns = array(array('header' => 'Yii::t("statistics", "Person")', 'value' => 'Persons::getLinkByNameNId($data["personName"], $data["personId"])', 'type' => 'raw'), array('header' => 'Yii::t("common", "Result")', 'value' => 'Results::formatTime($data["value"], $data["eventId"])', 'type' => 'raw'), array('header' => 'Yii::t("common", "Competition")', 'value' => 'CHtml::link(ActiveRecord::getModelAttributeValue($data, "name"), $data["url"])', 'type' => 'raw'));
if ($statistic['type'] === 'average') {
$columns[] = array('header' => "Yii::t('common', 'Detail')", 'value' => 'Results::getDisplayDetail($data)', 'type' => 'raw');
}
$statistic['count'] = count($top100);
$statistic['rankKey'] = 'value';
return self::makeStatisticsData($statistic, $columns, $top100);
}
}
/**
* Retrieves a list of models based on the current search/filter conditions.
*
* Typical usecase:
* - Initialize the model fields with values from filter form.
* - Execute this method to get CActiveDataProvider instance which will filter
* models according to data in model fields.
* - Pass data provider to CGridView, CListView or any similar widget.
*
* @return CActiveDataProvider the data provider that can return the models
* based on the search/filter conditions.
*/
public function search()
{
// @todo Please modify the following code to remove attributes that should not be searched.
$criteria = new CDbCriteria();
$criteria->with = 'country';
$criteria->compare('t.id', $this->id, true);
// $criteria->compare('t.name',$this->name,true);
$criteria->compare('t.cityName', $this->cityName, true);
$criteria->compare('t.countryId', $this->countryId, true);
$criteria->compare('t.information', $this->information, true);
// $criteria->compare('t.year',$this->year);
// $criteria->compare('t.month',$this->month);
// $criteria->compare('t.day',$this->day);
$criteria->compare('t.endMonth', $this->endMonth);
$criteria->compare('t.endDay', $this->endDay);
$criteria->compare('t.eventSpecs', $this->eventSpecs, true);
$criteria->compare('t.wcaDelegate', $this->wcaDelegate, true);
$criteria->compare('t.organiser', $this->organiser, true);
$criteria->compare('t.venue', $this->venue, true);
$criteria->compare('t.venueAddress', $this->venueAddress, true);
$criteria->compare('t.venueDetails', $this->venueDetails, true);
$criteria->compare('t.website', $this->website, true);
$criteria->compare('t.cellName', $this->cellName, true);
$criteria->compare('t.latitude', $this->latitude);
$criteria->compare('t.longitude', $this->longitude);
$pageSize = 100;
if (in_array($this->year, self::getYears())) {
$criteria->compare('year', $this->year);
} elseif ($this->year === 'current') {
$time = time() - 86400 * 90;
$criteria->addCondition('UNIX_TIMESTAMP(CONCAT(year, "-", month, "-", day))>=' . $time);
$pageSize = 10000;
}
switch ($this->region) {
case 'World':
break;
case 'Africa':
case 'Asia':
case 'Oceania':
case 'Europe':
case 'North America':
case 'South America':
$criteria->compare('country.continentId', '_' . $this->region);
break;
default:
$criteria->compare('t.countryId', $this->region);
break;
}
if ($this->event && in_array($this->event, array_keys(Events::getNormalEvents()))) {
$criteria->addCondition("eventSpecs REGEXP '[[:<:]]{$this->event}[[:>:]]'");
}
if ($this->name) {
$names = explode(' ', $this->name);
foreach ($names as $key => $value) {
if (trim($value) === '') {
continue;
}
$paramKey = ':name' . $key;
$criteria->addCondition("t.cellName LIKE {$paramKey} or t.cityName LIKE {$paramKey} or t.venue LIKE {$paramKey}");
$criteria->params[$paramKey] = '%' . $value . '%';
}
}
return new CActiveDataProvider($this, array('criteria' => $criteria, 'pagination' => array('pageSize' => $pageSize), 'sort' => array('defaultOrder' => 't.year DESC, t.month DESC, t.day DESC, t.endMonth DESC, t.endDay DESC')));
}
private function statMedalCollection()
{
$page = $this->iGet('page', 1);
$eventIds = $this->aGet('event');
if (array_intersect($eventIds, array_keys(Events::getNormalEvents())) === array()) {
$eventIds = array();
}
$statistic = array('class' => 'MedalCollection', 'type' => 'all', 'eventIds' => $eventIds);
if ($page < 1) {
$page = 1;
}
$this->title = Yii::t('statistics', 'Medal Collection');
$this->pageTitle = array('Fun Statistics', $this->title);
$this->breadcrumbs = array('Results' => array('/results/index'), 'Statistics' => array('/results/statistics'), $this->title);
$data = Statistics::buildRankings($statistic, $page);
extract($data);
if ($page > ceil($statistic['count'] / Statistics::$limit)) {
$page = ceil($statistic['count'] / Statistics::$limit);
}
$this->render('stat/medalCollection', array('statistic' => $statistic, 'time' => $time, 'page' => $page, 'eventIds' => $eventIds));
}
