FileCreator.php

<?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;
    }

}