File: /var/www/dvpis2025/dvpis.kaunokolegija.lt/src/Controller/Report/LecturerPollReportController.php
<?php
namespace App\Controller\Report;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use App\Entity\FinalEgzam;
use App\Entity\FinalProject;
use App\Entity\KtvLecturer;
use App\Entity\MeovLecturer;
use App\Entity\TmmvLecturer;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Security;
use App\Entity\Lecturer;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\StreamedResponse;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\ResponseHeaderBag;
use App\Entity\LecturerPlan;
use App\Entity\Poll\Poll;
use App\Entity\Poll\Summary;
use App\Entity\Poll\PollGroup;
use App\Entity\ProcessDates;
use Symfony\Component\Security\Core\Authorization\AuthorizationCheckerInterface;
use App\Service\YearInformation;
use Doctrine\ORM\EntityManagerInterface;
use App\Service\DbDataFilter;
/**
* Lecturer controller.
*
* @Security("is_granted('ROLE_LECTURER')
or is_granted('ROLE_DEPARTMENT_ADMINISTRATOR')
or is_granted('ROLE_DEPARTMENT_HEAD')
or is_granted('ROLE_ACADEMIC_UNIT_PROHEAD')
or is_granted('ROLE_ACADEMIC_UNIT_HEAD')
or is_granted('ROLE_PERSONAL_DEPARTMENT')
or is_granted('ROLE_FINANCE_DEPARTMENT')
or is_granted('ROLE_DIRECTOR')
")
*/
#[Route(path: 'lecturerpollreport')]
class LecturerPollReportController extends AbstractController
{
public function __construct(
private readonly EntityManagerInterface $em,
private readonly AuthorizationCheckerInterface $authorizationChecker,
private readonly DbDataFilter $dbDataFilter,
) {
if (
$this->authorizationChecker->isGranted('ROLE_DIRECTOR') || $this->authorizationChecker->isGranted('ROLE_FINANCE_DEPARTMENT') || $this->authorizationChecker->isGranted('ROLE_PERSONAL_DEPARTMENT') || $this->authorizationChecker->isGranted('ROLE_ACADEMIC_UNIT_HEAD') || $this->authorizationChecker->isGranted('ROLE_ACADEMIC_UNIT_PROHEAD')
) {
return;
}
if ($this->authorizationChecker->isGranted('ROLE_DEPARTMENT_ADMINISTRATOR') || $this->authorizationChecker->isGranted('ROLE_DEPARTMENT_HEAD')) {
$this->dbDataFilter->enableOnlyDepartmentFilter();
return;
}
if ($this->authorizationChecker->isGranted('ROLE_LECTURER')) {
$this->dbDataFilter->enableOnlyLecturerFilter();
return;
}
throw new AccessDeniedHttpException("Jūsų naudotojas negali pasiekti šio turinio");
}
/**
* Lists all lecturer entities.
*
* @Method("GET")
*/
#[Route(path: '/', name: 'lecturerpollreport_index')]
public function indexAction()
{
$lecturers = $this->em->getRepository(Lecturer::class)->findAllFilter();
$pollDates = $this->em->getRepository(ProcessDates::class)->getActiveLecturerPollDates();
$lecturerContactHours = $this->em->getRepository(LecturerPlan::class)->getLecturerPlanHoursGroupByLecturer();
$lecturerFinalEgzamHours = $this->em->getRepository(FinalEgzam::class)->getLecturerFinalEgzamHoursGroupByLecturer();
$lecturerFinalProjectHours = $this->em->getRepository(FinalProject::class)->getLecturerFinalProjectHoursGroupByLecturer();
$lecturerTmmv = $this->em->getRepository(TmmvLecturer::class)->getLecturerTmmvPlanHoursGroupByLecturer();
$lecturerMeov = $this->em->getRepository(MeovLecturer::class)->getLecturerMeovPlanHoursGroupByLecturer();
$lecturerKtv = $this->em->getRepository(KtvLecturer::class)->getLecturerKtvPlanHoursGroupByLecturer();
return $this->render('reports/lecturerpollreport/index.html.twig', [
'lecturers' => $lecturers,
'pollDates' => $pollDates,
'lecturerStatuses' => $this->em->getRepository(Summary::class)->findAllByLecturer(),
'lecturerContactHours' => $lecturerContactHours,
'lecturerFinalEgzamHours' => $lecturerFinalEgzamHours,
'lecturerFinalProjectHours' => $lecturerFinalProjectHours,
'lecturerTmmv' => $lecturerTmmv,
'lecturerMeov' => $lecturerMeov,
]);
}
#[Route(path: '/excel/{lecturer}', defaults: ['lecturer' => 0], name: 'lecturerpollreport_report')]
public function reportExcelAction(Request $request, YearInformation $yearInformation, int $lecturer)
{
ini_set('max_execution_time', 60 * 5);
ini_set('max_input_time', 60 * 5);
ini_set('memory_limit', '1024M');
$excelFileName = 'dvpis-ataskaita_dest_Savianalizes-' . date("Y_m_d_H_i_s") . '.xls';
$excelTemplateDir = $this->getParameter('excel_templates_directory');
$excelTemplate = $excelTemplateDir . "/Ataskaita_Dest_Savianaliziu.xlsx";
$objExcel = IOFactory::load($excelTemplate);
$answerRows = $this->em->getRepository(Poll::class)->findAllArrayByPoll($lecturer);
$summaries = $this->em->getRepository(Summary::class)->findByLecturer($lecturer);
$pollGroups = $this->em->getRepository(PollGroup::class)->findAll();
$polls = $this->em->getRepository(Poll::class)->findAll();
$colors = [
'E8F9E9',
'F7F9E8',
'E8F9EA',
'F4F9E8',
'F9E8F4',
'F3E8F9',
'E8F9E9',
'F7F9E8',
'F7F9E8',
'E8F9EA',
'F4F9E8',
'F9E8F4',
'F3E8F9',
];
$i = 0;
foreach ($pollGroups as $pollGroup) {
if (!isset($groupColors[$pollGroup->getType()])) {
$groupColors[$pollGroup->getType()] = $colors[$i];
$i++;
}
}
$activeSheetIndex = 0;
if (!empty($lecturer)) {
$year = $yearInformation->getActiveYear();
$lecturer = $this->em->getRepository(Lecturer::class)->find($lecturer);
// "ĮVYKDYMAS" - kur imtų pedagoginę dalį iš fakto, o tvm, meov ir ktv įvykdymo info;
$excelHeader = $year . "-" . ($year + 1) . " m.m. dėstytojo veiklos įvykdymas";
// $excelFileName = 'dvpis-detytojoveiklosivykdymas-' . date("Y_m_d_H_i_s") . '.xls';
$lecturerContactHours = $this->em->getRepository(LecturerPlan::class)->getLecturerPlanActivityPlan($lecturer);
$lecturerFinalProjectHours = $this->em->getRepository(FinalProject::class)->getLecturerFinalProjectHoursActivityPlan($lecturer);
$lecturerFinalEgzamHours = $this->em->getRepository(FinalEgzam::class)->getLecturerFinalEgzamHoursActivityPlan($lecturer);
$lecturerTmmvHours = $this->em->getRepository(TmmvLecturer::class)->getLecturerTmmvPlanFactHoursActivityPlan($lecturer);
$lecturerMeovHours = $this->em->getRepository(MeovLecturer::class)->getLecturerMeovPlanFactHoursActivityPlan($lecturer);
$lecturerKtvHours = $this->em->getRepository(KtvLecturer::class)->getLecturerKtvPlanFactHoursActivityPlan($lecturer);
$worksheet = $objExcel->setActiveSheetIndex(1);
$worksheet->setCellValue('A2', $lecturer->getDepartment());
$worksheet->setCellValue('A3', $excelHeader);
$worksheet->setCellValue('A4', $lecturer->getSurname() . " " . $lecturer->getName() . ", " . strtolower($lecturer->getLecturerPosition()) . " " . $lecturer->getPostSize());
$rowsLecturerContactHoursAutumn = [];
$rowsLecturerContactHoursSpring = [];
foreach ($lecturerContactHours as $lecturerPlan) {
foreach ($lecturerPlan->getAcademicGroupPlans() as $academicGroupPlan) {
$stream = [];
foreach ($lecturerPlan->getAcademicGroupPlans() as $lecturerPlanGroup) {
$stream[] = (string) $lecturerPlanGroup->getAcademicGroup();
}
$spp = $academicGroupPlan->getStudiesprogramPlan();
$ag = $academicGroupPlan->getAcademicGroup();
$row = [
(string) $academicGroupPlan->getAcademicGroup(),
(string) $spp->getStudiesForm(),
$ag->getStudentCount() + $ag->getStudentPartCount() + $ag->getStudentListenerCount(),
(string) $spp->getStudiesModule(),
(string) $spp->getCredits(),
// (string)$spp->getSemester(),
(string) $spp->getTeachingLanguage()->getShortName(),
$lecturerPlan->getTheoryHours(),
$lecturerPlan->getPracticGroup(),
$lecturerPlan->getPracticGroup1(),
$lecturerPlan->getPracticGroup2(),
$lecturerPlan->getConsultationHours(),
$lecturerPlan->getEgzamHours(),
$lecturerPlan->getScoreHours(),
$lecturerPlan->getPrepareContactHours(),
$lecturerPlan->getAdditionalConsultationHours(),
$lecturerPlan->getStudiesModuleDescriptionHours(),
$lecturerPlan->getStudiesModulePreparationHours(),
$lecturerPlan->getStudiesModuleRenewHours(),
count($stream),
// md5(implode("_", $stream)),
$lecturerPlan->getId(),
];
if ($spp->getSemester() % 2 == 0) {
$rowsLecturerContactHoursSpring[] = $row;
} else {
$rowsLecturerContactHoursAutumn[] = $row;
}
}
}
//AUTUMN==================================================================================================
$iterator = 0;
$pedAutumn = 9;
$lecturerContactHoursAutumnCount = count($rowsLecturerContactHoursAutumn);
$streamsCount = [];
if ($lecturerContactHoursAutumnCount > 0) {
if ($lecturerContactHoursAutumnCount > 1) {
$worksheet->insertNewRowBefore($pedAutumn + 1, $lecturerContactHoursAutumnCount - 1);
}
foreach ($rowsLecturerContactHoursAutumn as $lecturerContactHour) {
$rowIndex = $pedAutumn + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerContactHour[0]);
$worksheet->setCellValue('B' . $rowIndex, $lecturerContactHour[1]);
$worksheet->setCellValue('C' . $rowIndex, $lecturerContactHour[2]);
$worksheet->setCellValue('D' . $rowIndex, $lecturerContactHour[3]);
$worksheet->setCellValue('E' . $rowIndex, $lecturerContactHour[4]);
$worksheet->setCellValue('F' . $rowIndex, $lecturerContactHour[5]);
$worksheet->setCellValue('G' . $rowIndex, $lecturerContactHour[6]);
$worksheet->setCellValue('H' . $rowIndex, $lecturerContactHour[7]);
$worksheet->setCellValue('I' . $rowIndex, $lecturerContactHour[8]);
$worksheet->setCellValue('J' . $rowIndex, $lecturerContactHour[9]);
$worksheet->setCellValue('K' . $rowIndex, $lecturerContactHour[10]);
$worksheet->setCellValue('L' . $rowIndex, $lecturerContactHour[11]);
$worksheet->setCellValue('M' . $rowIndex, '=SUM(G' . $rowIndex . ':L' . $rowIndex . ')');
$worksheet->setCellValue('N' . $rowIndex, $lecturerContactHour[12]);
$worksheet->setCellValue('O' . $rowIndex, $lecturerContactHour[13]);
$worksheet->setCellValue('P' . $rowIndex, $lecturerContactHour[14]);
$worksheet->setCellValue('Q' . $rowIndex, $lecturerContactHour[15]);
$worksheet->setCellValue('R' . $rowIndex, $lecturerContactHour[16]);
$worksheet->setCellValue('S' . $rowIndex, $lecturerContactHour[17]);
$worksheet->setCellValue('T' . $rowIndex, '=SUM(G' . $rowIndex . ':L' . $rowIndex . ')+SUM(N' . $rowIndex . ':S' . $rowIndex . ')');
// $worksheet->setCellValue('R' . $rowIndex, '1');//Count or NOT, streams
if ($lecturerContactHour[18] > 1) {
if (isset($streamsCount[$lecturerContactHour[19]])) {
$worksheet->setCellValue('X' . $rowIndex, ' '); //Count or NOT, streams
// $worksheet
// ->getStyle('R' . $rowIndex)
// ->applyFromArray([
// 'font' => [
// 'color' => ['rgb' => 'ffffff'],
// ]
// ]);
} else {
$streamsCount[$lecturerContactHour[19]] = 1;
}
$worksheet
->getStyle('A' . $rowIndex . ':T' . $rowIndex)
->getFill()
->applyFromArray([
'fillType' => Fill::FILL_SOLID,
'startColor' => [
'rgb' => 'efefef'
],
]);
// $worksheet
// ->getStyle('A' . $rowIndex . ':Q' . $rowIndex)
// ->applyFromArray([
// 'font' => [
// 'color' => ['rgb' => 'c9cdd3'],
// ]
// ]);
}
$iterator++;
}
//sums row
$rowIndex = $pedAutumn + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('G' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",G' . $pedAutumn . ':G' . $rowSumIndex . ')');
$worksheet->setCellValue('H' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",H' . $pedAutumn . ':H' . $rowSumIndex . ')');
$worksheet->setCellValue('I' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",I' . $pedAutumn . ':I' . $rowSumIndex . ')');
$worksheet->setCellValue('J' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",J' . $pedAutumn . ':J' . $rowSumIndex . ')');
$worksheet->setCellValue('K' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",K' . $pedAutumn . ':K' . $rowSumIndex . ')');
$worksheet->setCellValue('L' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",L' . $pedAutumn . ':L' . $rowSumIndex . ')');
$worksheet->setCellValue('M' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",M' . $pedAutumn . ':M' . $rowSumIndex . ')');
$worksheet->setCellValue('N' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",N' . $pedAutumn . ':N' . $rowSumIndex . ')');
$worksheet->setCellValue('O' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",O' . $pedAutumn . ':O' . $rowSumIndex . ')');
$worksheet->setCellValue('P' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",P' . $pedAutumn . ':P' . $rowSumIndex . ')');
$worksheet->setCellValue('Q' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",Q' . $pedAutumn . ':Q' . $rowSumIndex . ')');
$worksheet->setCellValue('R' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",R' . $pedAutumn . ':R' . $rowSumIndex . ')');
$worksheet->setCellValue('S' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",S' . $pedAutumn . ':S' . $rowSumIndex . ')');
$worksheet->setCellValue('T' . $rowIndex, '=SUMIF(X' . $pedAutumn . ':X' . $rowSumIndex . ',"",T' . $pedAutumn . ':T' . $rowSumIndex . ')');
}
//SPRING==================================================================================================
$iterator = 0;
$pedSpring = $pedAutumn + $lecturerContactHoursAutumnCount + ($lecturerContactHoursAutumnCount > 0 ? 2 : 3);
$lecturerContactHoursSpringCount = count($rowsLecturerContactHoursSpring);
$streamsCount = [];
if ($lecturerContactHoursSpringCount > 0) {
if ($lecturerContactHoursSpringCount > 1) {
$worksheet->insertNewRowBefore($pedSpring + 1, $lecturerContactHoursSpringCount - 1);
}
foreach ($rowsLecturerContactHoursSpring as $lecturerContactHour) {
$rowIndex = $pedSpring + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerContactHour[0]);
$worksheet->setCellValue('B' . $rowIndex, $lecturerContactHour[1]);
$worksheet->setCellValue('C' . $rowIndex, $lecturerContactHour[2]);
$worksheet->setCellValue('D' . $rowIndex, $lecturerContactHour[3]);
$worksheet->setCellValue('E' . $rowIndex, $lecturerContactHour[4]);
$worksheet->setCellValue('F' . $rowIndex, $lecturerContactHour[5]);
$worksheet->setCellValue('G' . $rowIndex, $lecturerContactHour[6]);
$worksheet->setCellValue('H' . $rowIndex, $lecturerContactHour[7]);
$worksheet->setCellValue('I' . $rowIndex, $lecturerContactHour[8]);
$worksheet->setCellValue('J' . $rowIndex, $lecturerContactHour[9]);
$worksheet->setCellValue('K' . $rowIndex, $lecturerContactHour[10]);
$worksheet->setCellValue('L' . $rowIndex, $lecturerContactHour[11]);
$worksheet->setCellValue('M' . $rowIndex, '=SUM(G' . $rowIndex . ':L' . $rowIndex . ')');
$worksheet->setCellValue('N' . $rowIndex, $lecturerContactHour[12]);
$worksheet->setCellValue('O' . $rowIndex, $lecturerContactHour[13]);
$worksheet->setCellValue('P' . $rowIndex, $lecturerContactHour[14]);
$worksheet->setCellValue('Q' . $rowIndex, $lecturerContactHour[15]);
$worksheet->setCellValue('R' . $rowIndex, $lecturerContactHour[16]);
$worksheet->setCellValue('S' . $rowIndex, $lecturerContactHour[17]);
$worksheet->setCellValue('T' . $rowIndex, '=SUM(G' . $rowIndex . ':L' . $rowIndex . ')+SUM(N' . $rowIndex . ':S' . $rowIndex . ')');
// $worksheet->setCellValue('R' . $rowIndex, '1');//Count or NOT, streams
if ($lecturerContactHour[18] > 1) {
if (isset($streamsCount[$lecturerContactHour[19]])) {
$worksheet->setCellValue('X' . $rowIndex, ' '); //Count or NOT, streams
} else {
$streamsCount[$lecturerContactHour[19]] = 1;
}
$worksheet
->getStyle('A' . $rowIndex . ':T' . $rowIndex)
->getFill()
->applyFromArray([
'fillType' => Fill::FILL_SOLID,
'startColor' => [
'rgb' => 'efefef'
],
]);
}
$iterator++;
}
//sums row
$rowIndex = $pedSpring + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('G' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",G' . $pedSpring . ':G' . $rowSumIndex . ')');
$worksheet->setCellValue('H' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",H' . $pedSpring . ':H' . $rowSumIndex . ')');
$worksheet->setCellValue('I' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",I' . $pedSpring . ':I' . $rowSumIndex . ')');
$worksheet->setCellValue('J' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",J' . $pedSpring . ':J' . $rowSumIndex . ')');
$worksheet->setCellValue('K' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",K' . $pedSpring . ':K' . $rowSumIndex . ')');
$worksheet->setCellValue('L' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",L' . $pedSpring . ':L' . $rowSumIndex . ')');
$worksheet->setCellValue('M' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",M' . $pedSpring . ':M' . $rowSumIndex . ')');
$worksheet->setCellValue('N' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",N' . $pedSpring . ':N' . $rowSumIndex . ')');
$worksheet->setCellValue('O' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",O' . $pedSpring . ':O' . $rowSumIndex . ')');
$worksheet->setCellValue('P' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",P' . $pedSpring . ':P' . $rowSumIndex . ')');
$worksheet->setCellValue('Q' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",Q' . $pedSpring . ':Q' . $rowSumIndex . ')');
$worksheet->setCellValue('R' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",R' . $pedSpring . ':R' . $rowSumIndex . ')');
$worksheet->setCellValue('S' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",S' . $pedSpring . ':S' . $rowSumIndex . ')');
$worksheet->setCellValue('T' . $rowIndex, '=SUMIF(X' . $pedSpring . ':X' . $rowSumIndex . ',"",T' . $pedSpring . ':T' . $rowSumIndex . ')');
} else {
$lecturerContactHoursSpringCount = +2;
}
$projectCoord = $pedSpring + ($lecturerContactHoursSpringCount - 1) + 5;
$iterator = 0;
$lecturerFinalProjectHoursCount = count($lecturerFinalProjectHours);
if ($lecturerFinalProjectHours) {
foreach ($lecturerFinalProjectHours as $lecturerFinalProjectHour) {
$rowIndex = $projectCoord + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerFinalProjectHour['studiesProgram']);
$worksheet->setCellValue('B' . $rowIndex, $lecturerFinalProjectHour['studiesForm']);
$worksheet->setCellValue('C' . $rowIndex, $lecturerFinalProjectHour['semester']);
$worksheet->setCellValue('D' . $rowIndex, $lecturerFinalProjectHour['countWorks']);
$worksheet->setCellValue('E' . $rowIndex, $lecturerFinalProjectHour['hours']);
$worksheet->setCellValue('H' . $rowIndex, $lecturerFinalProjectHour['departmentCommision']);
$worksheet->setCellValue('K' . $rowIndex, $lecturerFinalProjectHour['finalCommisionHead']);
$worksheet->setCellValue('N' . $rowIndex, $lecturerFinalProjectHour['finalCommision']);
$worksheet->setCellValue('Q' . $rowIndex, '=SUM(E' . $rowIndex . ':P' . $rowIndex . ')');
if ($lecturerFinalProjectHoursCount - $iterator - 1 > 0) {
$worksheet->insertNewRowBefore($rowIndex + 1, 1);
$worksheet->mergeCells('E' . ($rowIndex + 1) . ':G' . ($rowIndex + 1));
$worksheet->mergeCells('H' . ($rowIndex + 1) . ':J' . ($rowIndex + 1));
$worksheet->mergeCells('K' . ($rowIndex + 1) . ':M' . ($rowIndex + 1));
$worksheet->mergeCells('N' . ($rowIndex + 1) . ':P' . ($rowIndex + 1));
}
$iterator++;
}
//sums row
$rowIndex = $projectCoord + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('D' . $rowIndex, '=SUM(D' . $projectCoord . ':D' . $rowSumIndex . ')');
$worksheet->setCellValue('E' . $rowIndex, '=SUM(E' . $projectCoord . ':G' . $rowSumIndex . ')');
$worksheet->setCellValue('H' . $rowIndex, '=SUM(H' . $projectCoord . ':J' . $rowSumIndex . ')');
$worksheet->setCellValue('K' . $rowIndex, '=SUM(K' . $projectCoord . ':M' . $rowSumIndex . ')');
$worksheet->setCellValue('N' . $rowIndex, '=SUM(N' . $projectCoord . ':P' . $rowSumIndex . ')');
$worksheet->setCellValue('Q' . $rowIndex, '=SUM(Q' . $projectCoord . ':Q' . $rowSumIndex . ')');
} else {
$projectCoord++;
}
$lecturerFinalProjectHoursCount = count($lecturerFinalProjectHours);
$lecturerFinalEgzamHoursCount = count($lecturerFinalEgzamHours);
$lecturerTmmvHoursCount = count($lecturerTmmvHours);
$lecturerMeovHoursCount = count($lecturerMeovHours);
$lecturerKtvHoursCount = count($lecturerKtvHours);
$egzamCoord = $projectCoord + ($lecturerFinalProjectHoursCount - 1) + 4;
$iterator = 0;
if ($lecturerFinalEgzamHours) {
foreach ($lecturerFinalEgzamHours as $lecturerFinalEgzamHour) {
$rowIndex = $egzamCoord + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerFinalEgzamHour['studiesProgram']);
$worksheet->setCellValue('B' . $rowIndex, $lecturerFinalEgzamHour['studiesForm']);
$worksheet->setCellValue('C' . $rowIndex, $lecturerFinalEgzamHour['semester']);
$worksheet->setCellValue('D' . $rowIndex, $lecturerFinalEgzamHour['prepareTasks']);
$worksheet->setCellValue('E' . $rowIndex, $lecturerFinalEgzamHour['finalCommisionHead']);
$worksheet->setCellValue('K' . $rowIndex, $lecturerFinalEgzamHour['finalCommision']);
$worksheet->setCellValue('Q' . $rowIndex, '=SUM(D' . $rowIndex . ':P' . $rowIndex . ')');
if ($lecturerFinalEgzamHoursCount - $iterator - 1 > 0) {
$worksheet->insertNewRowBefore($rowIndex + 1, 1);
$worksheet->mergeCells('E' . ($rowIndex + 1) . ':J' . ($rowIndex + 1));
$worksheet->mergeCells('K' . ($rowIndex + 1) . ':P' . ($rowIndex + 1));
}
$iterator++;
}
//sums row
$rowIndex = $egzamCoord + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('D' . $rowIndex, '=SUM(D' . $egzamCoord . ':D' . $rowSumIndex . ')');
$worksheet->setCellValue('E' . $rowIndex, '=SUM(E' . $egzamCoord . ':J' . $rowSumIndex . ')');
$worksheet->setCellValue('K' . $rowIndex, '=SUM(K' . $egzamCoord . ':P' . $rowSumIndex . ')');
$worksheet->setCellValue('Q' . $rowIndex, '=SUM(Q' . $egzamCoord . ':Q' . $rowSumIndex . ')');
}
$tmmvCoord = $this->findRowNumberByText($worksheet, 'Taikomieji moksliniai tyrimai, eksperimentinė plėtra ir meno veikla');
$iterator = 2; //first row for names
if ($lecturerTmmvHours) {
foreach ($lecturerTmmvHours as $lecturerTmmvHour) {
$rowIndex = $tmmvCoord + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerTmmvHour['name']);
$worksheet->setCellValue('D' . $rowIndex, $lecturerTmmvHour['description']);
$worksheet->setCellValue('G' . $rowIndex, $lecturerTmmvHour['hours']);
$worksheet->setCellValue('I' . $rowIndex, $lecturerTmmvHour['descriptionFact']);
$worksheet->setCellValue('P' . $rowIndex, $lecturerTmmvHour['hoursFact']);
if ($lecturerTmmvHoursCount - $iterator >= 0) {
$worksheet->insertNewRowBefore($rowIndex + 1, 1);
$worksheet->mergeCells('A' . ($rowIndex + 1) . ':C' . ($rowIndex + 1));
$worksheet->mergeCells('D' . ($rowIndex + 1) . ':F' . ($rowIndex + 1));
$worksheet->mergeCells('G' . ($rowIndex + 1) . ':H' . ($rowIndex + 1));
$worksheet->mergeCells('I' . ($rowIndex + 1) . ':O' . ($rowIndex + 1));
$worksheet->mergeCells('P' . ($rowIndex + 1) . ':Q' . ($rowIndex + 1));
}
$iterator++;
}
//sums row
$rowIndex = $tmmvCoord + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('G' . $rowIndex, '=SUM(G' . ($tmmvCoord + 1) . ':H' . $rowSumIndex . ')');
$worksheet->setCellValue('P' . $rowIndex, '=SUM(P' . ($tmmvCoord + 1) . ':Q' . $rowSumIndex . ')');
}
$meovCoord = $this->findRowNumberByText($worksheet, 'Metodinė, ekspertinė ir organizacinė veikla');
$iterator = 2;
if ($lecturerMeovHours) {
foreach ($lecturerMeovHours as $lecturerMeovHour) {
$rowIndex = $meovCoord + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerMeovHour['name']);
$worksheet->setCellValue('D' . $rowIndex, $lecturerMeovHour['description']);
$worksheet->setCellValue('G' . $rowIndex, $lecturerMeovHour['hours']);
$worksheet->setCellValue('I' . $rowIndex, $lecturerMeovHour['descriptionFact']);
$worksheet->setCellValue('P' . $rowIndex, $lecturerMeovHour['hoursFact']);
if ($lecturerMeovHoursCount - $iterator >= 0) {
$worksheet->insertNewRowBefore($rowIndex + 1, 1);
$worksheet->mergeCells('A' . ($rowIndex + 1) . ':C' . ($rowIndex + 1));
$worksheet->mergeCells('D' . ($rowIndex + 1) . ':F' . ($rowIndex + 1));
$worksheet->mergeCells('G' . ($rowIndex + 1) . ':H' . ($rowIndex + 1));
$worksheet->mergeCells('I' . ($rowIndex + 1) . ':O' . ($rowIndex + 1));
$worksheet->mergeCells('P' . ($rowIndex + 1) . ':Q' . ($rowIndex + 1));
}
$iterator++;
}
//sums row
$rowIndex = $meovCoord + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('G' . $rowIndex, '=SUM(G' . ($meovCoord + 1) . ':H' . $rowSumIndex . ')');
$worksheet->setCellValue('P' . $rowIndex, '=SUM(P' . ($meovCoord + 1) . ':Q' . $rowSumIndex . ')');
}
$ktvCoord = $this->findRowNumberByText($worksheet, 'Kompetencijų tobulinimas');
$iterator = 2;
if ($lecturerKtvHours) {
foreach ($lecturerKtvHours as $lecturerKtvHour) {
$rowIndex = $ktvCoord + $iterator;
$worksheet->setCellValue('A' . $rowIndex, $lecturerKtvHour['name']);
$worksheet->setCellValue('D' . $rowIndex, $lecturerKtvHour['description']);
$worksheet->setCellValue('G' . $rowIndex, $lecturerKtvHour['hours']);
$worksheet->setCellValue('I' . $rowIndex, $lecturerKtvHour['descriptionFact']);
$worksheet->setCellValue('P' . $rowIndex, $lecturerKtvHour['hoursFact']);
if ($lecturerKtvHoursCount - $iterator >= 0) {
$worksheet->insertNewRowBefore($rowIndex + 1, 1);
$worksheet->mergeCells('A' . ($rowIndex + 1) . ':C' . ($rowIndex + 1));
$worksheet->mergeCells('D' . ($rowIndex + 1) . ':F' . ($rowIndex + 1));
$worksheet->mergeCells('G' . ($rowIndex + 1) . ':H' . ($rowIndex + 1));
$worksheet->mergeCells('I' . ($rowIndex + 1) . ':O' . ($rowIndex + 1));
$worksheet->mergeCells('P' . ($rowIndex + 1) . ':Q' . ($rowIndex + 1));
}
$iterator++;
}
//sums row
$rowIndex = $ktvCoord + $iterator;
$rowSumIndex = $rowIndex - 1;
$worksheet->setCellValue('G' . $rowIndex, '=SUM(G' . ($ktvCoord + 1) . ':H' . $rowSumIndex . ')');
$worksheet->setCellValue('P' . $rowIndex, '=SUM(P' . ($ktvCoord + 1) . ':Q' . $rowSumIndex . ')');
}
$activeSheetIndex = 2;
} else {
$objExcel->removeSheetByIndex(
$objExcel->getIndex(
$objExcel->getSheetByName('Veiklos plano įvykdymas')
)
);
$activeSheetIndex = 1;
}
foreach ($polls as $poll) {
//form header
$pollGroup = $poll->getPollGroup();
//form table header
$pollAnswerFields = $poll->getAnswerFields();
$tableHeaderCols = [
'Dėstytojo pavardė, vardas',
'Kadedra/akademija/centras',
'Ademinis padalinys',
];
foreach ($pollAnswerFields as $answerField) {
$tableHeaderCols[] = $answerField;
}
$tableAnswerRows = [];
if (!empty($answerRows[$poll->getId()])) {
foreach ($answerRows[$poll->getId()] as $answerRow) {
$row = [
$answerRow->getLecturer()->getSurname() . " " . $answerRow->getLecturer()->getName(),
$answerRow->getLecturer()->getDepartment()->getShortName(),
$answerRow->getLecturer()->getDepartment()->getAcademicUnit()->getShortName(),
];
$answers = $answerRow->getAnswers();
foreach ($answers as $answer) {
$row[] = $answer->getName();
}
$tableAnswerRows[] = $row;
}
}
$objExcel->createSheet($activeSheetIndex + 1);
$worksheet = $objExcel->setActiveSheetIndex($activeSheetIndex);
foreach ($tableHeaderCols as $key => $tableHeaderCol) {
$worksheet->setCellValue([$key + 1, 6], $tableHeaderCol);
}
$worksheet->fromArray(
$tableAnswerRows,
null,
'A7'
);
$maxColName = $worksheet->getHighestDataColumn();
$worksheet->setAutoFilter('A6:' . $maxColName . '6');
$worksheet->setTitle(substr($poll->getName(), 0, 6));
$worksheet->mergeCells('A1:' . $maxColName . '1');
$worksheet->mergeCells('A2:' . $maxColName . '2');
$worksheet->mergeCells('A3:' . $maxColName . '3');
$worksheet->mergeCells('A4:' . $maxColName . '4');
$worksheet->mergeCells('A5:' . $maxColName . '5');
$worksheet->setCellValue('A1', $pollGroup->getType());
$worksheet->setCellValue('A2', $pollGroup->getName());
$worksheet->setCellValue('A3', $poll->getName());
$worksheet->setCellValue('A4', $poll->getShortName());
$worksheet->setCellValue('A5', $poll->getDescription());
$styleArray = [
'font' => [
// 'bold' => true,
// 'color' => array('rgb' => 'FF0000'),
'size' => 8,
'name' => 'Arial',
]
];
$worksheet->getStyle($worksheet->calculateWorksheetDimension())->applyFromArray($styleArray);
$worksheet->getStyle('A1')->applyFromArray([
'font' => [
'bold' => true,
'size' => 11,
]
]);
$worksheet->getStyle('A2')->applyFromArray([
'font' => [
'size' => 10,
]
]);
$worksheet->getStyle('A3')->applyFromArray([
'font' => [
'size' => 9,
]
]);
$worksheet->getStyle('A4:A5')->applyFromArray([
'font' => [
'size' => 7,
]
]);
$color = $groupColors[$pollGroup->getType()];
$worksheet
->getStyle('A6:' . $maxColName . '6')
->getFill()
->setFillType(Fill::FILL_SOLID)
->getStartColor()
->setRGB($color);
foreach (range('A', $maxColName) as $col) {
$worksheet->getColumnDimension($col)
->setAutoSize(true);
}
$worksheet->calculateColumnWidths();
$worksheet->getTabColor()->setRGB($color);
$activeSheetIndex++;
}
$sheetToRemove = $objExcel->getSheetByName('Worksheet');
if ($sheetToRemove !== null) {
$objExcel->removeSheetByIndex(
$objExcel->getIndex(
$sheetToRemove
)
);
}
$activeSheetIndex = $objExcel->getIndex(
$objExcel->getSheetByName('Apib.')
);
$worksheet = $objExcel->setActiveSheetIndex($activeSheetIndex);
$i = 5;
foreach ($summaries as $summary) {
$worksheet->setCellValue('A' . $i, $summary->getLecturer());
$worksheet->setCellValue('B' . $i, $summary->getLecturer()->getDepartment()->getShortName());
$worksheet->setCellValue('C' . $i, $summary->getLecturer()->getDepartment()->getAcademicUnit()->getShortName());
$worksheet->setCellValue('D' . $i, $summary->getLecturerActivity());
$worksheet->setCellValue('E' . $i, $summary->getResearchActivity());
$worksheet->setCellValue('F' . $i, $summary->getMethodicActivity());
$worksheet->setCellValue('G' . $i, $summary->getCompetencyActivity());
$worksheet->setCellValue('H' . $i, $summary->getLecturerActivityReview());
$worksheet->setCellValue('I' . $i, $summary->getResearchActivityReview());
$worksheet->setCellValue('J' . $i, $summary->getMethodicActivityReview());
$worksheet->setCellValue('K' . $i, $summary->getCompetencyActivityReview());
$i++;
}
$maxColName = $worksheet->getHighestDataColumn();
$worksheet->setAutoFilter('A4:' . $maxColName . '4');
// create the writer
$writer = new Xls($objExcel);
// create the response
$response = new StreamedResponse(function () use ($writer) {
$writer->save('php://output');
});
// adding headers
$dispositionHeader = $response->headers->makeDisposition(
ResponseHeaderBag::DISPOSITION_ATTACHMENT,
$excelFileName
);
$response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
$response->headers->set('Pragma', 'public');
$response->headers->set('Cache-Control', 'maxage=0');
$response->headers->set('Content-Disposition', $dispositionHeader);
$response->send();
}
private function findRowNumberByText($workSheet, $text): int
{
for ($i = 0; $i < 5000; $i++) {
if ($workSheet->getCell('A' . $i) == $text) {
return $i;
}
}
return 0;
}
}