File: /var/www/dvpis2026/dvpis.kaunokolegija.lt/src/Repository/LecturerPlanRepository.php
<?php
namespace App\Repository;
use App\Entity\Lecturer;
use Doctrine\ORM\EntityRepository;
/**
* LecturerPlanRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class LecturerPlanRepository extends EntityRepository
{
public function findAllQueryBuilder($filter = null)
{
$query = $this->createQueryBuilder('lp')
->join('lp.lecturer', 'l')
->join('lp.academicGroupPlans', 'agp')
->join('lp.lecturerPosition', 'lpos')
->join('agp.studiesProgramPlan', 'spp')
->join('spp.studiesProgram', 'sp')
->join('sp.department', 'd')
->join('d.academicUnit', 'au')
->select('lp, agp, spp, l, lpos ')
// ->select('lp')
->addOrderBy('agp.academicGroup', 'ASC')
->addOrderBy('agp.studiesProgramPlan', 'ASC');
if (!empty($filter->get('academicUnit'))) {
$query->andWhere('au.id = :auid');
$query->setParameter(':auid', $filter->get('academicUnit'));
}
if (!empty($filter->get('department'))) {
$query->andWhere('d.id = :did');
$query->setParameter(':did', $filter->get('department'));
}
if (!empty($filter->get('studiesProgram'))) {
$query->andWhere('sp.id = :spid');
$query->setParameter(':spid', $filter->get('studiesProgram'));
}
if (!empty($filter->get('academicGroup'))) {
$query->andWhere('agp.academicGroup = :agid');
$query->setParameter(':agid', $filter->get('academicGroup'));
}
if (!empty($filter->get('semester'))) {
$query->andWhere('spp.semester = :agsem');
$query->setParameter(':agsem', $filter->get('semester'));
}
if (!empty($filter->get('studiesModule'))) {
$query->andWhere('spp.studiesModule = :sppsm');
$query->setParameter(':sppsm', $filter->get('studiesModule'));
}
$results = $query->getQuery()->getResult();
// dump($results);
$return = [];
foreach ($results as $result) {
$return[$result->getId()] = $result;
}
return $return;
}
public function findAllQueryBuilderJSON($filter = null)
{
$query = $this->createQueryBuilder('lp')
->select('lp, agp, spp, l, lpos, d')
->join('lp.lecturer', 'l')
->join('l.department', 'd')
->join('lp.academicGroupPlans', 'agp')
->join('lp.lecturerPosition', 'lpos')
->join('agp.studiesProgramPlan', 'spp')
->join('spp.studiesProgram', 'sp')
->join('d.academicUnit', 'au')
->addOrderBy('agp.academicGroup', 'ASC')
->addOrderBy('agp.studiesProgramPlan', 'ASC');
if (!empty($filter->get('academicUnit'))) {
$query->andWhere('au.id = :auid');
$query->setParameter(':auid', $filter->get('academicUnit'));
}
if (!empty($filter->get('department'))) {
$query->andWhere('d.id = :did');
$query->setParameter(':did', $filter->get('department'));
}
if (!empty($filter->get('studiesProgram'))) {
$query->andWhere('sp.id = :spid');
$query->setParameter(':spid', $filter->get('studiesProgram'));
}
if (!empty($filter->get('academicGroup'))) {
$query->andWhere('agp.academicGroup = :agid');
$query->setParameter(':agid', $filter->get('academicGroup'));
}
if (!empty($filter->get('semester'))) {
$query->andWhere('spp.semester = :agsem');
$query->setParameter(':agsem', $filter->get('semester'));
}
if (!empty($filter->get('studiesModule'))) {
$query->andWhere('spp.studiesModule = :sppsm');
$query->setParameter(':sppsm', $filter->get('studiesModule'));
}
return $query->getQuery()->getResult();
}
// public function findAllSQL($filter = null)
// {
//
// $conn = $this->getEntityManager()
// ->getConnection();
//
// $query = "
// SELECT
// l0_.id AS id_0,
// l0_.student_count AS student_count_1,
// l0_.theory_hours AS theory_hours_2,
// l0_.practic_group AS practic_group_3,
// l0_.practic_group1 AS practic_group1_4,
// l0_.practic_group2 AS practic_group2_5,
// l0_.consultation_hours AS consultation_hours_6,
// l0_.egzam_hours AS egzam_hours_7,
// l0_.score_hours AS score_hours_8,
// l0_.prepare_contact_hours AS prepare_contact_hours_9,
// l0_.additional_consultation_hours AS additional_consultation_hours_10,
// l0_.description AS description_11,
// l0_.is_not_full_time AS is_not_full_time_12,
// l1_.name AS name_14,
// l1_.surname AS surname_15,
// a2_.id AS id_26,
// s3_.id AS id_27,
// s3_.semester AS semester_28,
// s3_.theory_hours AS theory_hours_29,
// s3_.practic_hours AS practic_hours_30,
// s3_.consultation_hours AS consultation_hours_31,
// s3_.homework_hours AS homework_hours_32,
// s3_.credits AS credits_33,
// l0_.lecturer_id AS lecturer_id_34,
// l0_.department_id AS department_id_35,
// l0_.lecturer_position_id AS lecturer_position_id_36,
// l0_.finance_source_id AS finance_source_id_37,
// l1_.lecturer_position_id AS lecturer_position_id_38,
// l1_.department_id AS department_id_39,
// a2_.academic_group_id AS academic_group_id_40,
// a2_.studies_program_plan_id AS studies_program_plan_id_41,
// s3_.studies_program_id AS studies_program_id_42,
// s3_.studies_form_id AS studies_form_id_43,
// s3_.studies_module_id AS studies_module_id_44,
// s3_.studies_module_type_id AS studies_module_type_id_45,
// s3_.teaching_language_id AS teaching_language_id_46
// FROM lecturer_plan l0_
// INNER JOIN academic_group_plan_lecturer_plan a4_
// ON l0_.id = a4_.lecturer_plan_id
// INNER JOIN academic_group_plan a2_
// ON a2_.id = a4_.academic_group_plan_id
// INNER JOIN studies_program_plan s3_
// ON a2_.studies_program_plan_id = s3_.id
// INNER JOIN studies_program s5_ ON s3_.studies_program_id = s5_.id
// INNER JOIN department d6_ ON s5_.department_id = d6_.id
// INNER JOIN academic_unit a7_ ON d6_.academic_unit_id = a7_.id
// INNER JOIN lecturer l1_ ON l0_.lecturer_id = l1_.id
// ORDER BY a2_.studies_program_plan_id ASC;
// ";
// $statement = $conn->prepare($query);
// $statement->execute();
//
// $results = $statement->fetchAll();
//
// $query = $this->createQueryBuilder('lp')
// ->join('lp.lecturer', 'l')
// ->join('lp.academicGroupPlans', 'agp')
// ->join('agp.studiesProgramPlan', 'spp')
// ->join('spp.studiesProgram', 'sp')
// ->join('sp.department', 'd')
// ->join('d.academicUnit', 'au')
// ->select('lp, agp, spp')
// ->addOrderBy('agp.academicGroup', 'ASC')
// ->addOrderBy('agp.studiesProgramPlan', 'ASC');
//
// if (!empty($filter->get('academicUnit'))) {
// $query->andWhere('au.id = :auid');
// $query->setParameter(':auid', $filter->get('academicUnit'));
// }
//
// if (!empty($filter->get('department'))) {
// $query->andWhere('d.id = :did');
// $query->setParameter(':did', $filter->get('department'));
// }
//
// if (!empty($filter->get('studiesProgram'))) {
// $query->andWhere('sp.id = :spid');
// $query->setParameter(':spid', $filter->get('studiesProgram'));
// }
//
// if (!empty($filter->get('academicGroup'))) {
// $query->andWhere('agp.academicGroup = :agid');
// $query->setParameter(':agid', $filter->get('academicGroup'));
// }
//
// if (!empty($filter->get('semester'))) {
// $query->andWhere('spp.semester = :agsem');
// $query->setParameter(':agsem', $filter->get('semester'));
// }
//
// if (!empty($filter->get('studiesModule'))) {
// $query->andWhere('spp.studiesModule = :sppsm');
// $query->setParameter(':sppsm', $filter->get('studiesModule'));
// }
//
// $results = $query->getQuery()->getResult();
//// dump($results);
// $return = [];
// foreach ($results as $result) {
// $return[$result->getId()] = $result;
// }
//
// return $return;
// }
public function getLecturerPlanHoursGroupByLecturer($filter = null)
{
$query = $this->createQueryBuilder('lp')
->join('lp.lecturer', 'l')
->join('lp.academicGroupPlans', 'agp')
->join('agp.studiesProgramPlan', 'spp')
->join('spp.studiesProgram', 'sp')
->join('sp.department', 'd')
->join('d.academicUnit', 'au')
// ->where('l.id = 470')
->groupBy('lp.lecturer')
->select('SUM('
. 'COALESCE(lp.theoryHours,0) + '
. 'COALESCE(lp.practicGroup,0) + '
. 'COALESCE(lp.practicGroup1,0) + '
. 'COALESCE(lp.practicGroup2,0) + '
. 'COALESCE(lp.consultationHours,0) + '
. 'COALESCE(lp.egzamHours,0)'
. ') as sumContact, '
. 'SUM('
. 'COALESCE(lp.scoreHours,0) + '
. 'COALESCE(lp.prepareContactHours,0) + '
. 'COALESCE(lp.additionalConsultationHours,0) +'
. 'COALESCE(lp.studiesModuleDescriptionHours,0) +'
. 'COALESCE(lp.studiesModulePreparationHours,0) +'
. 'COALESCE(lp.studiesModuleRenewHours,0)'
. ') as sumNotContact,'
. 'l.id as lecturerId')
->addOrderBy('agp.academicGroup', 'ASC')
->addOrderBy('agp.studiesProgramPlan', 'ASC');
$results = $query->getQuery()->getResult();
//remove stream, sum all streams, then divide by stream count
$query = $this->createQueryBuilder('lp')
->join('lp.lecturer', 'l')
->join('lp.academicGroupPlans', 'agp')
->join('agp.academicGroup', 'ag')
->groupBy('l, lp')
// ->where('l.id = 470')
->having('COUNT(agp.id) > 1')
->select('SUM('
. 'COALESCE(lp.theoryHours,0) + '
. 'COALESCE(lp.practicGroup,0) + '
. 'COALESCE(lp.practicGroup1,0) + '
. 'COALESCE(lp.practicGroup2,0) + '
. 'COALESCE(lp.consultationHours,0) + '
. 'COALESCE(lp.egzamHours,0)'
. ') as sumContact, '
. 'SUM('
. 'COALESCE(lp.scoreHours,0) + '
. 'COALESCE(lp.prepareContactHours,0) + '
. 'COALESCE(lp.additionalConsultationHours,0) +'
. 'COALESCE(lp.studiesModuleDescriptionHours,0) +'
. 'COALESCE(lp.studiesModulePreparationHours,0) +'
. 'COALESCE(lp.studiesModuleRenewHours,0)'
. ') as sumNotContact,'
. 'l.id as lecturerId,'
. 'COUNT(agp.id) as cnt')
->addOrderBy('agp.academicGroup', 'ASC')
->addOrderBy('agp.studiesProgramPlan', 'ASC');
$resultsStreams = $query->getQuery()->getResult();
$streamLecturer = [];
foreach ($resultsStreams as $resultsStream) {
if (!isset($streamLecturer[$resultsStream['lecturerId']])) {
$streamLecturer[$resultsStream['lecturerId']] = [
'sumContact' => 0,
'sumNotContact' => 0,
];
}
$streamLecturer[$resultsStream['lecturerId']]['sumContact'] += ($resultsStream['sumContact'] / $resultsStream['cnt'] * ($resultsStream['cnt'] - 1));
$streamLecturer[$resultsStream['lecturerId']]['sumNotContact'] += ($resultsStream['sumNotContact'] / $resultsStream['cnt'] * ($resultsStream['cnt'] - 1));
}
$return = [];
foreach ($results as $result) {
//if isset stream then remove stream duplicates of it
if (isset($streamLecturer[$result['lecturerId']])) {
$result['sumContact'] = $result['sumContact'] - $streamLecturer[$result['lecturerId']]['sumContact'];
$result['sumNotContact'] = $result['sumNotContact'] - $streamLecturer[$result['lecturerId']]['sumNotContact'];
}
$return[$result['lecturerId']] = $result;
}
return $return;
}
public function getLecturerPlanActivityPlan(Lecturer $lecturer)
{
$query = $this->createQueryBuilder('lp')
->select('lp, agp, lp2, spp, l, lpos, tl')
->join('lp.lecturer', 'l')
->join('lp.academicGroupPlans', 'agp')
->join('agp.lecturerPlans', 'lp2')
->join('lp.lecturerPosition', 'lpos')
->join('agp.studiesProgramPlan', 'spp')
->join('spp.studiesProgram', 'sp')
->leftJoin('spp.teachingLanguage', 'tl')
->join('sp.department', 'd')
->join('d.academicUnit', 'au')
->where('lp.lecturer = :lecturer')
->setParameter(':lecturer', $lecturer)
->addOrderBy('agp.academicGroup', 'ASC')
->addOrderBy('agp.studiesProgramPlan', 'ASC');
return $query->getQuery()->getResult();
}
public function deleteAll()
{
return $this->getEntityManager()
->createQuery(
"DELETE App:LecturerPlan t"
)
->execute();
}
}