public function export($competition, $exportFormsts, $all = false, $xlsx = false, $extra = false, $order = 'date') { $registrations = $this->getRegistrations($competition, $all, $order); $template = PHPExcel_IOFactory::load(Yii::getPathOfAlias('application.data.results') . '.xls'); $export = new PHPExcel(); $export->getProperties()->setCreator(Yii::app()->params->author)->setLastModifiedBy(Yii::app()->params->author)->setTitle($competition->wca_competition_id ?: $competition->name)->setSubject($competition->name); $export->removeSheetByIndex(0); //注册页 $sheet = $template->getSheet(0); $sheet->setCellValue('A1', $competition->wca_competition_id ?: $competition->name); $events = $competition->getRegistrationEvents(); $col = 'J'; $cubecompsEvents = array('333' => '3x3', '444' => '4x4', '555' => '5x5', '666' => '6x6', '777' => '7x7', '222' => '2x2', '333bf' => '333bld', '333fm' => 'fmc', 'minx' => 'mega', 'pyram' => 'pyra', '444bf' => '444bld', '555bf' => '555bld', '333mbf' => '333mlt'); foreach ($events as $event => $data) { $sheet->setCellValue($col . 2, "=SUM({$col}4:{$col}" . (count($registrations) + 4) . ')'); $sheet->setCellValue($col . 3, isset($cubecompsEvents[$event]) ? $cubecompsEvents[$event] : $event); $sheet->getColumnDimension($col)->setWidth(5.5); $col++; } foreach ($registrations as $key => $registration) { $user = $registration->user; $row = $key + 4; $sheet->setCellValue('A' . $row, $registration->number)->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid)->setCellValue('E' . $row, $user->getWcaGender())->setCellValue('F' . $row, PHPExcel_Shared_Date::FormattedPHPToExcel(date('Y', $user->birthday), date('m', $user->birthday), date('d', $user->birthday))); $col = 'J'; foreach ($events as $event => $data) { if (in_array("{$event}", $registration->events)) { $sheet->setCellValue($col . $row, 1); } $col++; } if ($extra) { $col++; $fee = $registration->getTotalFee(); if ($registration->isPaid()) { $fee .= Yii::t('common', ' (paid)'); } $sheet->setCellValue($col . $row, $fee); $col++; $sheet->setCellValue($col . $row, $user->mobile); $col++; $sheet->setCellValue($col . $row, $user->email); $col++; $sheet->setCellValue($col . $row, $registration->comments); } if (!$registration->isAccepted()) { $sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')))); } } $export->addExternalSheet($sheet); //各个项目 foreach ($exportFormsts as $event => $rounds) { $count = count($rounds); foreach ($rounds as $round => $format) { if ($round == $count - 1) { $round = 'f'; } else { $round++; } $sheet = $template->getSheetByName($format); if ($sheet === null) { continue; } $sheet = clone $sheet; $sheet->setTitle("{$event}-{$round}"); $template->addSheet($sheet); $sheet->setCellValue('A1', Events::getFullEventName($event) . ' - ' . Rounds::getFullRoundName($round)); if ($round == 1 || $count == 1) { $row = 5; foreach ($registrations as $registration) { if (!in_array("{$event}", $registration->events)) { continue; } $user = $registration->user; $sheet->setCellValue('B' . $row, $user->getCompetitionName())->setCellValue('C' . $row, $user->country->name)->setCellValue('D' . $row, $user->wcaid); if ($row > 5) { $formula = $sheet->getCell('A' . ($row - 1))->getValue(); $formula = strtr($formula, array('-4' => '_temp_', $row - 1 => $row, $row - 2 => $row - 1, $row => $row + 1)); $formula = str_replace('_temp_', '-4', $formula); $sheet->setCellValue('A' . $row, $formula); } if (!$registration->isAccepted()) { $sheet->getStyle("A{$row}:D{$row}")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFFFFF00')))); } $row++; } } $export->addExternalSheet($sheet); } } $this->exportToExcel($export, 'php://output', $competition->name, $xlsx); }
public function getListableSchedules() { $listableSchedules = array(); $schedules = $this->schedule; usort($schedules, array($this, 'sortSchedules')); $hasGroup = false; $hasNumber = false; $specialEvents = array('333fm' => array(), '333mbf' => array()); foreach ($schedules as $key => $schedule) { if (trim($schedule->group) != '') { $hasGroup = true; } if ($schedule->number > 0) { $hasNumber = true; } else { $schedule->number = ''; } if (isset($specialEvents[$schedule->event])) { $specialEvents[$schedule->event][$schedule->round][] = $key; } } $scheduleEvents = Events::getOnlyScheduleEvents(); foreach ($schedules as $key => $schedule) { if (isset($scheduleEvents[$schedule->event])) { $schedule->round = $schedule->group = $schedule->format = ''; $schedule->cut_off = $schedule->time_limit = 0; } $event = Yii::t('event', Events::getFullEventName($schedule->event)); if (isset($specialEvents[$schedule->event][$schedule->round]) && count($specialEvents[$schedule->event][$schedule->round]) > 1) { $times = array_search($key, $specialEvents[$schedule->event][$schedule->round]); switch ($times + 1) { case 1: $event .= Yii::t('common', '(1st attempt)'); break; case 2: $event .= Yii::t('common', '(2nd attempt)'); break; case 3: $event .= Yii::t('common', '(3rd attempt)'); break; default: $event .= Yii::t('common', '({times}th attempt)', array('{times}' => $times)); break; } } $temp = array('Start Time' => date('H:i', $schedule->start_time), 'End Time' => date('H:i', $schedule->end_time), 'Event' => $event, 'Group' => $schedule->group, 'Round' => Yii::t('Rounds', Rounds::getFullRoundName($schedule->round)), 'Format' => Yii::t('common', Formats::getFullFormatName($schedule->format)), 'Cut Off' => self::formatTime($schedule->cut_off), 'Time Limit' => self::formatTime($schedule->time_limit), 'Competitors' => $schedule->number, 'id' => $schedule->id, 'event' => $schedule->event, 'round' => $schedule->round); if ($hasGroup === false) { unset($temp['Group']); } if ($hasNumber === false) { unset($temp['Competitors']); } $listableSchedules[$schedule->day][$schedule->stage][] = $temp; } return $listableSchedules; }