<?php defined('SYSPATH') || die('No direct script access.');
class Controller_Handler_FileCreator extends Controller_Handler
{
// Массив указания позиций (имен колонок) для разных ведомостей.
// Параметризация ведется по типу аттестации и по этапу сдачи
private $ColumnsPositions = [
"exam" => [
0 => [
'Index' => 'A',
'Name' => 'B',
'Total' => 'G',
'Semester' => 'H',
'Bonus' => 'I',
'Extra' => 0,
'Exam' => 'J',
'RateString' => 'K',
'RightestColumn' => 'L'
],
2 => [
'Index' => 'A',
'Name' => 'B',
'Total' => 'G',
'Semester' => 'H',
'Bonus' => 'I',
'Extra' => 'J',
'Exam' => 'K',
'RateString' => 'L',
'RightestColumn' => 'M'
],
],
"credit" => [
0 => [
'Index' => 'A',
'Name' => 'B',
'Total' => 'H',
'Semester' => 'I',
'Bonus' => 'J',
'Extra' => 0,
'Exam' => 0,
'RateString' => 'K',
'RightestColumn' => 'L'
],
2 => [
'Index' => 'A',
'Name' => 'B',
'Total' => 'H',
'Semester' => 'I',
'Bonus' => 'J',
'Extra' => 'K',
'Exam' => 0,
'RateString' => 'L',
'RightestColumn' => 'M'
],
],
]; // остальные данные о позициях копируются в функции before()
public function before() {
// копирование настроек полей для других типов аттестации и этапов
$this->ColumnsPositions['exam'][1] = $this->ColumnsPositions['exam'][0];
$this->ColumnsPositions['exam'][3] = $this->ColumnsPositions['exam'][2];
$this->ColumnsPositions['credit'][1] = $this->ColumnsPositions['credit'][0];
$this->ColumnsPositions['credit'][3] = $this->ColumnsPositions['credit'][2];
for ($i = 0; $i < 4; ++$i)
$this->ColumnsPositions['grading_credit'][$i] = $this->ColumnsPositions['credit'][$i];
parent::before();
}
// Таблица баллов (со страницы оценивания) [dev version]
public function action_GenerateExcelRatingTable() {
$this->user->checkAccess(User::RIGHTS_TEACHER | User::RIGHTS_DEAN);
$xls = new PHPExcel();
// Устанавливаем индекс активного листа
$xls->setActiveSheetIndex(0);
// Получаем активный лист
$sheet = $xls->getActiveSheet();
// Шапка таблицы: структура УКД (модули и мероприятия)
$structure = Model_Map::getRoadmap($this->post['disciplineID'], 'rate');
$id = -1;
$count = 0;
$maxRate = 0;
$structureHandled = [];
foreach ($structure as $row) {
if ($row['ModuleID'] != $id) {
$id = $row['ModuleID'];
$module =& $structureHandled[$count++];
$module = [
'MaxRate' => 0,
'SubmodulesCount' => 0,
'ModuleTitle' => $row['ModuleName'],
'ModuleType' => $row['ModuleType'],
];
}
$module['SubmodulesCount']++;
$module[] = [
'SubmoduleID' => $row['SubmoduleID'],
'Title' => $row['SubModuleName'],
'MaxRate' => (int) $row['MaxRate'],
];
$maxRate += $row['MaxRate'];
$module['MaxRate'] += (int) $row['MaxRate'];
}
$structureHandled['ModulesCount'] = $count;
$structureHandled['MaxRate'] = (int) $maxRate;
$sheet->setCellValueByColumnAndRow(0, 1, 'Модуль');
$sheet->setCellValueByColumnAndRow(0, 2, 'Мероприятие');
$sheet->setCellValueByColumnAndRow(0, 3, 'Макс. балл');
$submodulesCount = 0;
// Модули
for ($k = 0, $pointer = 1; $k <= $structureHandled['ModulesCount']; $k++) {
$sheet->mergeCellsByColumnAndRow($pointer, 1, $pointer + $structureHandled[$k]['SubmodulesCount'] - 1, 1);
$sheet->setCellValueByColumnAndRow($pointer, 1, $structureHandled[$k]['ModuleTitle']);
// Мероприятия
for ($l = 0; $l <= $structureHandled[$k]['SubmodulesCount']; $l++) {
$sheet->setCellValueByColumnAndRow($pointer + $l, 2, $structureHandled[$k][$l]['Title']);
$sheet->setCellValueByColumnAndRow($pointer + $l, 3, $structureHandled[$k][$l]['MaxRate']);
$submodulesCount++;
}
$pointer = $structureHandled[$k]['SubmodulesCount'] + 1;
}
$sheet->setCellValueByColumnAndRow($submodulesCount + 1, 1, 'Итог');
$sheet->mergeCellsByColumnAndRow($submodulesCount + 1, 1, $submodulesCount + 1, 3);
// Студенты и их баллы
$students = Model_Rating::GetStudentsForRating($this->post['disciplineID']);
$i = 0;
$curGroup = 0;
$rateSum = 0;
foreach ($students as $row) {
if ($curGroup !== $row['GroupID']) {
$curGroup = $row['GroupID'];
$i++;
$sheet->mergeCellsByColumnAndRow(0, 3 + $i, $submodulesCount + 1, 3 + $i);
$sheet->setCellValueByColumnAndRow(0, 3 + $i, $row['GroupNum'] . ' группа');
}
// Студенты
$i++;
$rateSum += $row['Rate'];
$sheet->setCellValueByColumnAndRow(0, 3 + $i, $row['Last'] . ' ' . $row['First'] . ' ' . $row['Second']);
// Баллы студента
$rate = Model_Rating::getMapForStudent($row['ID'], $this->post['disciplineID']);
$i_r = 0;
foreach ($rate as $r) {
$i_r++;
$sheet->setCellValueByColumnAndRow($i_r, 3 + $i, $r['Rate']);
}
$sheet->setCellValueByColumnAndRow($submodulesCount + 1, 3 + $i, $rateSum);
}
$this->GetHeaders();
// Выводим содержимое файла
$objWriter = new PHPExcel_Writer_Excel5($xls);
$objWriter->save('php://output');
}
// Ведомость
public function action_GenerateFinalForm() {
$this->user->checkAccess(User::RIGHTS_TEACHER | User::RIGHTS_DEAN);
$this->post
->rule('disciplineID', 'not_empty')
->rule('disciplineID', 'digit')
->rule('groupID', 'not_empty')
->rule('groupID', 'digit')
->rule('stage', 'not_empty')
->rule('stage', 'digit', [0, 1, 2, 3]);
if (!$this->post->check())
throw HTTP_Exception::factory(417, "Некорректные параметры запроса!");
// parameters
$disciplineID = $this->post['disciplineID'];
$groupID = $this->post['groupID'];
$stage = $this->post['stage'];
// make form from template
$info = Model_Rating::getFinalFormInfo($disciplineID, $groupID);
$info['DisciplineID'] = $disciplineID;
$this->printDisciplineToExcelFile($objPHPExcel, $disciplineID, $groupID, $info, $stage);
// prepare filename
$grade = $info["GradeNum"];
$group = $info["GroupNum"];
$subjName = $info["SubjectName"];
$subjName = UTF8::substr($subjName, 0, 10);
$subjName = str_replace(' ', '_', $subjName);
$filename = $subjName . "_" . $grade . "_" . $group;
$this->GetHeaders($filename);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
public function printDisciplineToExcelFile(&$objPHPExcel, $disciplineID, $groupID, $headerData, $stage) {
$useNewForms = true;
if ($headerData["FacultyID"] == 2)
$useNewForms = false;
$type = $headerData['ExamType'];
if ($type === 'grading_credit')
$type = 'credit';
$templateFile = DOCROOT . "docs/old template $type.xls";
if ($useNewForms) {
$stageSuffix = $stage < 2 ? "0 1" : "2 3";
$templateFile = DOCROOT . "docs/template $type $stageSuffix.xls";
}
if (!file_exists($templateFile)) {
exit("template wasn't found" . PHP_EOL);
}
$objPHPExcel = PHPExcel_IOFactory::load($templateFile);
$objPHPExcel->setActiveSheetIndex(0);
if ($useNewForms)
$this->printNewForm($objPHPExcel, $disciplineID, $groupID, $headerData, $stage);
else
$this->printOldForm($objPHPExcel, $disciplineID, $groupID, $headerData);
}
/**
* @param PHPExcel $objPHPExcel
* @param int $disciplineID
* @param int $groupID
* @param array
* @param int $stage
*/
protected function printNewForm(&$objPHPExcel, $disciplineID, $groupID, $headerData, $stage) {
// preparation
$type = $headerData['ExamType'];
$rates = Model_Rating::getRatesForStudentsGroupByStage($disciplineID, $groupID, $stage);
// fill header
$this->prepareSheetHeader($objPHPExcel, $type, $headerData);
// fill students rows
$startRow = 12;
$rowNumber = $startRow;
$index = 1;
$sheet = $objPHPExcel->getActiveSheet();
foreach ($rates as $studentRates) {
// проверяем дошел ли студент до этапа $stage или уже сдал
if ($type == 'exam') {
if (($stage >= 2) &&
(((int)$studentRates['PreviousExam'] >= 22) || ((int)$studentRates['AutoPassed'] == 1))
)
continue; // на предыдущем этапе уже сдал
} else {
$prevStageAttempt = (int)$studentRates['PreviousExtra'] + (int)$studentRates['Semester'];
if (($stage >= 2) && ($prevStageAttempt >= 60))
continue; // на предыдущем этапе уже сдал
}
$this->addStudentToSheetNew($sheet, $type, $studentRates, $rowNumber++, $index++, $stage);
}
}
protected function addStudentToSheetNew(PHPExcel_Worksheet &$sheet,
$type, $studentRates, $rowIndex, $studentIndex, $stage) {
$indPosition = $this->ColumnsPositions[$type][$stage]['Index']; // Номер
$namePosition = $this->ColumnsPositions[$type][$stage]['Name']; // ФИО
$totalRatePosition = $this->ColumnsPositions[$type][$stage]['Total']; // Итоговый рейтинг
$semesterRatePosition = $this->ColumnsPositions[$type][$stage]['Semester']; // Сумма баллов семестра
$bonusRatePosition = $this->ColumnsPositions[$type][$stage]['Bonus']; // Бонусные баллы
$extraRatePosition = $this->ColumnsPositions[$type][$stage]['Extra']; // Добор, если есть
$examRatePosition = $this->ColumnsPositions[$type][$stage]['Exam']; // Экзамен, если есть
$stringRatePosition = $this->ColumnsPositions[$type][$stage]['RateString']; // Оценка в виде строки (зачтено/не зачтено или отл/хор/...)
$rightestColumn = $this->ColumnsPositions[$type][$stage]['RightestColumn'];
// $datePosition = 'N'; // Дата
$sheet->getStyle("A" . $rowIndex . ":" . $rightestColumn . $rowIndex)
->getBorders()->getAllBorders()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle($totalRatePosition . $rowIndex . ":" . $rightestColumn . $rowIndex)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$rightestColumnOfName = chr(ord($totalRatePosition) - 1);
$sheet->mergeCells("B" . $rowIndex . ":" . $rightestColumnOfName . $rowIndex);
$lastName = $studentRates['LastName'];
$firstName = $studentRates['FirstName'];
$secondName = $studentRates['SecondName'];
$fullName = $lastName . " " . $firstName . " " . $secondName;
$ratesSet['Semester'] = (int) $studentRates['Semester'];
$ratesSet['Bonus'] = (int) $studentRates['Bonus'];
$ratesSet['Extra'] = (int) $studentRates['Extra'];
$ratesSet['Exam'] = (int) $studentRates['Exam'];
$ratesSet['Option'] = $studentRates['Option'];
list($totalRateStr, $stringRate, $examRateStr) =
$this->formStringsForRates($ratesSet, $type, $stage > 0);
$sheet->setCellValue($indPosition . $rowIndex, $studentIndex)
->setCellValue($namePosition . $rowIndex, $fullName)
->setCellValue($totalRatePosition . $rowIndex, $totalRateStr)
->setCellValue($semesterRatePosition . $rowIndex, $ratesSet['Semester'])
->setCellValue($bonusRatePosition . $rowIndex, $ratesSet['Bonus'])
->setCellValue($stringRatePosition . $rowIndex, $stringRate);
if ($extraRatePosition)
$sheet->setCellValue($extraRatePosition . $rowIndex, $ratesSet['Extra']);
if ($examRatePosition)
$sheet->setCellValue($examRatePosition . $rowIndex, $examRateStr);
}
public function printOldForm(&$objPHPExcel, $disciplineID, $groupID, $headerData) {
// preparation
$type = $headerData['ExamType'];
$result = Model_Rating::getRatesForStudentsGroup($disciplineID, $groupID);
$rates = Model_Rating::getAttestationData($disciplineID, $groupID);
// fill header
$this->prepareSheetHeader($objPHPExcel, $type, $headerData);
// fill students rows
$startRow = 12;
$rowNumber = $startRow;
$index = 1;
$examHold = $this->checkExamIsHold($result);
$i = 0;
foreach ($result as $studentInfo) {
while ($rates[$i]['Type'] == 'extra' && $rates[$i]['StudentID'] == $studentInfo['ID']) {
$orderNum = $rates[$i]['OrderNum'];
$studentInfo['Attempt'][$orderNum]['ExtraRate'] = (int) $rates[$i]['Rate'];
$i++;
}
while ($rates[$i]['Type'] == 'exam' && $rates[$i]['StudentID'] == $studentInfo['ID']) {
$orderNum = $rates[$i]['OrderNum'];
$studentInfo['Attempt'][$orderNum]['ExamRate'] = (int) $rates[$i]['Rate'];
$timestamp = strtotime($rates[$i]['Date']);
if ($timestamp == false)
$date = '';
else
$date = date("d.m.y", $timestamp);
$studentInfo['Attempt'][$orderNum]['ExamDate'] = $date;
$i++;
}
$this->addStudentToSheet($objPHPExcel, $type, $studentInfo, $rowNumber, $index, $examHold);
$rowNumber++;
$index++;
}
}
protected function prepareSheetHeader(PHPExcel &$objPHPExcel, $disciplineType, $data) {
$numOfTeachersInRow = 4;
$sheet = $objPHPExcel->getActiveSheet();
$range = $objPHPExcel->getNamedRange("Discipline")->getRange();
$sheet->setCellValue($range, $data['SubjectName']);
$range = $objPHPExcel->getNamedRange("Group")->getRange();
$sheet->setCellValue($range, $data['GroupNum']);
$range = $objPHPExcel->getNamedRange("Subdivision")->getRange();
$sheet->setCellValue($range, $data['FacultyName']);
$range = $objPHPExcel->getNamedRange("Major")->getRange();
$sheet->setCellValue($range, "Специальность: " . $data['SpecName'] . " " . $data['SpecCode']);
$teachers = Model_Discipline::load($data['DisciplineID'])->getTeachers()->as_array();
$teachersStr = '';
if (count($teachers) > 1) {
$i = 0;
for (; $i < count($teachers) - 1; ++$i) {
$teachersStr .= Text::abbreviateName($teachers[$i]) . ', ';
if ($numOfTeachersInRow * ceil(($i + 1) / $numOfTeachersInRow) == $i + 1) {
$teachersStr .= "\r\n";
}
}
$teachersStr .= Text::abbreviateName($teachers[$i]);
} else
$teachersStr = $data['LastName'] . " " . $data['FirstName'] . " " . $data['SecondName'];
$range = $objPHPExcel->getNamedRange("Teacher")->getRange();
$rowPosition = preg_replace('/[^0-9]/', '', $range);
$rowHeight = ceil((count($teachers) / $numOfTeachersInRow)) * 14;
$sheet->getRowDimension($rowPosition)
->setRowHeight($rowHeight);
$sheet->setCellValue($range, $teachersStr);
$range = $objPHPExcel->getNamedRange("Grade")->getRange();
$degree = $data['Degree'];
$gradeNum = $data['GradeNum'];
$gradeName = $gradeNum;
if ($degree == 'master')
$gradeName = $gradeName . "м";
$sheet->setCellValue($range, $gradeName);
$range = $objPHPExcel->getNamedRange("Semester")->getRange();
$semester = $data['SemesterNum'] % 2 ? "Осенний" : "Весенний";
$sheet->setCellValue($range, $semester);
$range = $objPHPExcel->getNamedRange("Year")->getRange();
$startYear = $data['Year'];
$sheet->setCellValue($range, $startYear . "/" . ($startYear + 1));
$range = $objPHPExcel->getNamedRange("CreationDate")->getRange();
$sheet->setCellValue($range, date("d.m.y"));
$range = $objPHPExcel->getNamedRange("Date")->getRange();
if ($disciplineType == 'exam') {
$controlDate = "Дата экзамена\n__________";
} else {
$controlDate = "Дата зачета\n" . $this->figureOutCreditDate($data['SemesterNum'], $startYear);
}
$sheet->setCellValue("$range", $controlDate);
}
protected function addStudentToSheet(PHPExcel &$objPHPExcel, $disciplineType, $data, $row, $index, $examHold1) {
$sheet = $objPHPExcel->getActiveSheet();
if ($disciplineType == 'exam') {
$this->addStudentInfoForExamToSheet($sheet, $data, $row, $index, $examHold1);
} else {
$this->addStudentInfoForCreditToSheet($sheet, $data, $row, $index);
}
}
protected function addStudentInfoForCreditToSheet(PHPExcel_Worksheet &$sheet, $data, $row, $index) {
$indPosition = 'A'; // Номер
$namePosition = 'B'; // ФИО
$totalRatePosition = 'G'; // Итоговый рейтинг
$semesterRatePosition = 'H'; // Сумма баллов
$bonusRatePosition = 'I'; // Бонусные баллы
$datePosition = ['*', 'N', 'R']; // Добор
$extraRatePosition = ['*', 'L', 'P']; // Добор
$stringRatePosition = ['J', 'M', 'Q']; // Оценка вида зачтено/не зачтено
$sheet->getStyle("A" . $row . ":S" . $row)
->getBorders()->getAllBorders()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle("G" . $row . ":S" . $row)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->mergeCells("B" . $row . ":F" . $row);
$lastName = $data['LastName'];
$firstName = $data['FirstName'];
$secondName = $data['SecondName'];
$semesterRate = (int) $data['intermediate'];
$bonus = (int) $data['bonus'];
$fullName = $lastName . " " . $firstName . " " . $secondName;
$rateForCredit = $semesterRate;
$sheet->setCellValue($indPosition . $row, $index)
->setCellValue($namePosition . $row, $fullName)
->setCellValue($semesterRatePosition . $row, $semesterRate)
->setCellValue($bonusRatePosition . $row, $bonus);
$i = 0;
$extra = 0;
do {
if ($i > 0) {
if (is_null($data['Attempt'][$i]['ExtraRate']))
continue;
$extra = $data['Attempt'][$i]['ExtraRate'];
$sheet->setCellValue($datePosition[$i] . $row, $data['Attempt'][$i]['ExamDate'])
->setCellValue($extraRatePosition[$i] . $row, $extra);
}
$rateForCredit += $extra;
$totalRate = $rateForCredit + $bonus;
if ($totalRate > 100)
$totalRate = 100;
if ($rateForCredit < 60) {
$rateToShow = "";
$tempStr = "не зачтено";
} else {
$rateToShow = $totalRate;
$tempStr = "зачтено";
}
$sheet->setCellValue($stringRatePosition[$i] . $row, $tempStr);
if ($rateToShow)
break;
} while (++$i < 3);
$sheet->setCellValue($totalRatePosition . $row, $rateToShow);
}
protected function addStudentInfoForExamToSheet(PHPExcel_Worksheet &$sheet, $data, $row, $index, $examHold) {
$indPosition = 'A'; // Номер
$namePosition = 'B'; // ФИО
$totalRatePosition = 'H'; // Итоговый рейтинг
$semesterRatePosition = 'I'; // Сумма баллов
$bonusRatePosition = 'J'; // Бонусные баллы
$extraRatePosition = 'N'; // Добор
$examRatePosition = ['K', 'O', 'S']; // Баллы за экзамен
$rateOfFivePosition = ['L', 'P', 'T']; // Оценка за экзамен по пятибальной системе
$datePosition = ['*', 'Q', 'U']; // Дата экзамена
$sheet->getStyle("A" . $row . ":V" . $row)
->getBorders()->getAllBorders()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$sheet->getStyle("H" . $row . ":K" . $row)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle("L" . $row . ":M" . $row)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("N" . $row . ":O" . $row)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle("P" . $row . ":V" . $row)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$sheet->getStyle("S" . $row . ":S" . $row)
->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->mergeCells("B" . $row . ":G" . $row);
$lastName = $data['LastName'];
$firstName = $data['FirstName'];
$secondName = $data['SecondName'];
$semesterRate = (int) $data['intermediate'];
$bonus = (int) $data['bonus'];
$fullName = $lastName . " " . $firstName . " " . $secondName;
$examAdmission = $semesterRate;
$sheet->setCellValue($indPosition . $row, $index)
->setCellValue($namePosition . $row, $fullName)
->setCellValue($semesterRatePosition . $row, $semesterRate)
->setCellValue($bonusRatePosition . $row, $bonus);
$i = 0;
do {
if (!$data['Attempt'][$i + 1]['ExamDate'])
continue;
$examRateValue = $data['Attempt'][$i + 1]['ExamRate'];
if ($i != 0) {
$extra = $data['Attempt'][1]['ExtraRate'];
$sheet->setCellValue($datePosition[$i] . $row, $data['Attempt'][$i + 1]['ExamDate'])
->setCellValue($extraRatePosition . $row, $extra);
$examAdmission += $extra;
}
$total = $examAdmission + $examRateValue + $bonus;
list($totalRateStr, $rateOfFive, $examRateStr) =
$this->formRateOfFive($examAdmission, $examRateValue, $total, $examHold);
$sheet->setCellValue($examRatePosition[$i] . $row, $examRateStr)
->setCellValue($rateOfFivePosition[$i] . $row, $rateOfFive);
if ($totalRateStr)
break;
} while (++$i < 3);
$sheet->setCellValue($totalRatePosition . $row, $totalRateStr);
}
protected function getSheetName(&$disciplineInfo) {
$str = $disciplineInfo['SubjectAbbr'];
if (empty($str)) {
$str = $disciplineInfo['SubjectName'];
}
//$str = preg_replace('/[^A-Za-z0-9\-]/', '', $str);
return substr($str, 0, 10);
}
public function action_GenerateFinalFormsForGroup() {
$this->user->checkAccess(User::RIGHTS_DEAN);
// parameters
//$gradeID = $this->post['gradeID'];
$groupID = $this->post['GroupID'];
// preparation
$listDisciplines = Model_Group::with($groupID)->getDisciplines();
$templateFile = DOCROOT . "docs/template credit.xls";
if (!file_exists($templateFile)) {
exit("template wasn't found" . PHP_EOL); // TODO
}
$objPHPExcel = PHPExcel_IOFactory::load($templateFile);
// Make copy of sheet
$sheetTemplate = $objPHPExcel->getActiveSheet()->copy();
// Make copy of named ranges
$nmRange = $objPHPExcel->getNamedRanges();
$index = 0;
$gradeNum = 0;
$groupNum = 0;
foreach ($listDisciplines as $discipline) {
$info = Model_Rating::getFinalFormInfo($discipline->ID, $groupID);
$type = $info['ExamType'];
if ($type == 'exam') {
continue;
}
if ($index > 0) {
// remove name ranges from active page
foreach ($objPHPExcel->getNamedRanges() as $name => $r) {
$objPHPExcel->removeNamedRange($r->getName(), null);
}
//make clone from page copy and include it in workbook
$C = clone $sheetTemplate;
try {
$C->setTitle($this->getSheetName($info));
} catch (Exception $ex) {
$C->setTitle("Без_имени_" . $index);
}
$objPHPExcel->addSheet($C, $index);
$objPHPExcel->setActiveSheetIndex($index);
// create name ranges at new sheet
foreach ($nmRange as $namedRange) {
$name = $namedRange->getName();
$rg = $namedRange->getRange();
$objPHPExcel->addNamedRange(new PHPExcel_NamedRange($name, $objPHPExcel->getActiveSheet(), $rg));
}
} else {
try {
$objPHPExcel->setActiveSheetIndex($index)->setTitle($this->getSheetName($info));
} catch (Exception $ex) {
$objPHPExcel->setActiveSheetIndex($index)->setTitle("Без_имени_" . $index);
}
$gradeNum = $info['GradeNum'];
$groupNum = $info['GroupNum'];
}
# fixme: $stage param is missing
$this->printDisciplineToExcelFile($objPHPExcel, $discipline->ID, $groupID, $info);
$index++;
}
if ($index === 0) {
throw HTTP_Exception::factory(400);
}
$this->GetHeaders("FinalForm_" . $gradeNum . "_" . $groupNum);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
// Выводим HTTP-заголовки
public function GetHeaders($filename = 'excel') {
$this->response->headers("Last-Modified", gmdate("D,d M YH:i:s") . " GMT");
$this->response->headers("Cache-Control", " no-cache, must-revalidate");
$this->response->headers("Pragma", " no-cache");
$this->response->headers("Content-type", " application/vnd.ms-excel");
$this->response->headers("Content-Disposition", "attachment; filename=" . $filename . ".xls");
}
protected function checkExamIsHold(&$studentsRates) {
foreach ($studentsRates as $studentInfo) {
if ($studentInfo['exam'] > 0)
return 1;
}
return 0;
}
protected function formStringsForRates($ratesSet, $type, $examHold) {
$attestationAdmission = $ratesSet['Semester'] + $ratesSet['Extra'];
$total = $attestationAdmission + $ratesSet['Bonus'] + $ratesSet['Exam'];
switch ($type) {
case 'exam':
return $this->formRateOfFive($attestationAdmission, $ratesSet['Exam'], $total, $examHold, $ratesSet['Option']);
case 'grading_credit': {
// emulate exam to call formRateOfFive
$ratesSet['Exam'] = 22;
$ratesSet['Semester'] -= 22;
$attestationAdmission -= 22;
$res = $this->formRateOfFive($attestationAdmission, $ratesSet['Exam'], $total, $examHold);
$t = $res[1];
switch ($t) {
case 'неуд':
$res[1] = 'не зачтено';
break;
case 'удовл':
$res[1] = 'зачтено (3)';
break;
case 'хор':
$res[1] = 'зачтено (4)';
break;
case 'отл':
$res[1] = 'зачтено (5)';
break;
}
return $res;
}
case 'credit': {
$totalStr = $total;
$totalStr = ($total < 60) ? '' : $totalStr;
$totalStr = ($total > 100) ? '100' : $totalStr;
$attestationStr = 'не зачтено';
if ($attestationAdmission >= 60)
$attestationStr = 'зачтено';
return [$totalStr, $attestationStr, ""];
}
}
}
// Определяет оценку по пятибальной системе для экзамена
protected function formRateOfFive($examAdmission, $examRateValue, $totalRateValue, $examHold, $option = null) {
// особые случаи: неявка и автомат
if ($option == 'absence')
return ['', 'н/я', ''];
if ($option == 'pass') {
if ($examAdmission == 60)
return [$totalRateValue, 'удовл', '0'];
else
return ['', 'автомат не допустим', ''];
}
// стандартная ситуация с оцениванием
if ($totalRateValue > 100)
$totalRateValue = 100;
$totalRateStr = '';
$rateOfFive = '';
$examRateStr = '';
if ($examHold != 0) {
$examRateStr = $examRateValue;
if ($examAdmission < 38) {
$rateOfFive = 'н/д';
} elseif ($examRateValue < 22) {
$rateOfFive = 'неуд';
} else {
$totalRateStr = $totalRateValue;
$rateOfFive = 'удовл';
if (($totalRateValue >= 71) && ($totalRateValue < 85))
$rateOfFive = 'хор';
elseif ($totalRateValue >= 85)
$rateOfFive = 'отл';
}
} else {
if ($examAdmission < 38) { // задолженник
//$totalRateStr = ' ';
$rateOfFive = 'н/д';
$examRateStr = '';
}
}
return [$totalRateStr, $rateOfFive, $examRateStr];
}
protected function figureOutCreditDate($semesterNum, $year) {
$yearInt = (int) $year;
$result = "";
if ($semesterNum == 1)
$result = "29.12.";
else if ($semesterNum == 2) {
$result = "30.05.";
$yearInt++;
}
$result = $result . $yearInt;
return $result;
}
}