Symfony Profiler

<?php
namespace App\Repository\Educational;
use App\Entity\Educational\Domain;
use App\Entity\Educational\Lesson\ArtWorkStudy;
use App\Entity\Educational\Lesson\WorkList;
use App\Entity\Educational\Lesson\WorkListLine;
use App\Entity\Educational\Lesson\WorkListNode;
use App\Entity\Educational\SchoolBag\ReviewProgram;
use App\Entity\Educational\SchoolBag\ReviewProgramLesson;
use Bo\MainBundle\Entity\PackBac;
use Bo\MainBundle\Entity\StudentPackBac;
use Bo\MediaBundle\Entity\Video;
use App\Entity\Educational\Chapter;
use App\Entity\Educational\Course;
use App\Entity\Educational\Discipline;
use App\Entity\Educational\Serie;
use App\Entity\Users\User;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
class WorkListRepository extends AbstractRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, WorkList::class);
}
public function scriptADeleteAfterSetup(){
return $this->createQueryBuilder('w')
->andWhere('w.pictureMobileId is not null')
->getQuery()->getResult();
}
public function getWorklistByWn($id,$domainId){
return $this->createQueryBuilder('w')
->select('wn.slug,w.title,w.pictureMobilePath')
->join(WorkListNode::class,'wn',Join::WITH,'wn.worklist =w.id ')
->andWhere('wn.domain =:domainId and wn.id != :id and w.e3c = 0 and w.boost = 0 and w.stage = 0 and w.packBac = 0 and w.packBrevet = 0')
->setParameters(['domainId'=>$domainId,'id'=>$id])
->setMaxResults(10)
->getQuery()->getResult();
}
public function getWorkListDisplay(Course $course,?Discipline $discipline,?Chapter $chapter){
$parameters = ['course'=>$course->getId()];
$result = $this->createQueryBuilder('w')
->select('wn.id as wnodeId,wk.title as wtitle,wk.picture as wpicture,cha.title,cha.slug,d.picture as posterChapter,w.picture as posterW,dis.title as dtitle,dis.slug as dslug,wk.freeAccess,wk.pictureFreeAccess')
->join(WorkListNode::class,'wn',Join::WITH,'wn.worklist = w.id')
->join('wn.worklist','wk')
->join('wn.domain','d')
->join('d.chapter','cha')
->join('d.discipline','dis')
->join('d.serie','s')
->where('d.course =:course');
if(!is_null($discipline)){
$result= $result->andWhere('dis.slug =:disslug');
$parameters = array_merge(['disslug'=>$discipline->getSlug()],$parameters);
}
if(!is_null($chapter)){
$result= $result->andWhere('cha.slug =:chaslug');
$parameters = array_merge(['chaslug'=>$chapter->getSlug()],$parameters);
}
$result = $result->andWhere('s.id = 4 and d.online = 1 and wk.stage = 0 and wk.e3c = 0 and wk.packBac = 0 and wk.packBrevet = 0 and wk.boost = 0')->setParameters($parameters)
->orderBy('dis.order','asc')
->getQuery()->getResult();
$tab = [];
if(!empty($result)){
foreach ($result as $row){
$tab[$row['dtitle']][$row['title']][] = [
'chaSlug'=>$row['slug'],
'chaTitle'=>$row['title'],
'chaPoster'=>$row['posterChapter'],
'picture'=> $row['posterW'],
'dslug'=>$row['dslug'],
'wTitle'=>$row['wtitle'],
'wPicture'=>$row['wpicture'],
'wnodeId'=>$row['wnodeId'],
'freeAccess'=>$row['freeAccess'],
'pictureFreeAccess'=>$row['pictureFreeAccess']
];
}
}
return $tab;
}
public function getWorkListByDomainForPopinProgram($slug,$user,$artwork = [])
{
if(is_null($slug)){
return [];
}
$parameters = [
'slug' => $slug,
];
$qb = $this->_em->createQueryBuilder();
$q = $this
->createQueryBuilder('w')
->select('count(wl.id) as nbr,w.title,wn.slug,rp.percentProgression as percent,w.pictureMobilePath,wlpicture.picture as pictureMobile,w.freeAccess')
->join(WorkListNode::class,'wn',Join::WITH,'wn.worklist=w.id');
if(!empty($artwork)){
$q = $q->join('wn.worklist','wk')
->leftjoin(ArtWorkStudy::class,'aws',Join::WITH,'wk.objectStudy=aws.id')
->andWhere(" (aws.id in (${artwork}) or wk.objectStudy is null )");
}
$q=$q->leftjoin('w.workListPictureMobile','wlpicture')
->leftJoin(ReviewProgramLesson::class,'rp',Join::WITH,$qb->expr()->andX(
$qb->expr()->eq('rp.student',$user->getId()),
$qb->expr()->eq('rp.workListNode','wn.id')))
->leftjoin(WorkListLine::class,'wl',Join::WITH,'wl.worklist = w.id')
->join('wn.domain','d')
->groupBy('w.id')
->andWhere('d.slug = :slug and wn.folderNode = 1 and wn.online = 1')
->setParameters($parameters)
->orderBy('wn.position', 'ASC')
->getQuery()
->getResult();
return $q;
}
/**
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param string $relationName
* @param string $relationAlias
* @param null $alias
*/
public static function leftJoin(QueryBuilder $queryBuilder, $relationName, $relationAlias, $alias = null)
{
$queryBuilder->leftJoin(
sprintf('%s.%s', $alias, $relationName),
$relationAlias,
\Doctrine\ORM\Query\Expr\Join::WITH,
sprintf('%s.%s is null', $relationAlias, 'stage')
);
$queryBuilder->addSelect($relationAlias);
}
public function getWorkListTableStatementLines($id)
{
$q1 = $this->_em->createQueryBuilder()
->select('w')
->from('BoWorkListBundle:WorkListTableStatementLine', 'w')
->join('w.statementLines', 's')
->where('s.id=:id')
->setParameter('id', $id);
return $q1->getQuery()->getResult();
}
public function getWorkListTableQuiz($id)
{
$q1 = $this->_em->createQueryBuilder()
->select('w')
->from('BoWorkListBundle:WorklistTableObjectExercise', 'w')
->join('w.objectExercise', 'o')
->where('o.id=:id')
->setParameter('id', $id);
return $q1->getQuery()->getResult();
}
public function getWorkListTableVideo($id)
{
$q1 = $this->_em->createQueryBuilder()
->select('w')
->from('BoWorkListBundle:WorkListTableVideo', 'w')
->join('w.video', 'v')
->where('v.id=:id')
->setParameter('id', $id);
return $q1->getQuery()->getResult();
}
public function getWorkListsByReviewProgramId($reviewProgramId)
{
return $this
->createQueryBuilder('wl')
->andWhere('wl.studentReviewProgram = :reviewProgramId')
->setParameters(
[
'reviewProgramId' => $reviewProgramId,
]
)
->orderBy('wl.studentReviewProgramPosition', 'ASC')
->getQuery()
->getResult();
}
public function unlinkWorkListBeforeFlush(WorkList $entity, $type)
{
$workListTypes = [
WorkListType::TYPE_ID_EXAM,
WorkListType::TYPE_ID_ETE,
WorkListType::TYPE_ID_RENTRE,
];
if (in_array($type, $workListTypes)) {
$packExamId = null;
foreach ($entity->getWorklistPackExams() as $wp) {
$wp->setWorklist(null);
$this->_em->remove($wp);
}
return true;
}
if ($type == WorkListType::TYPE_ID_STAGE) {
$pt = $entity->getProgramTraining();
$trainingId = $pt[0]->getPlanningTraining()->getTraining()->getId();
foreach ($pt as $v) {
$v->setWorklist(null);
}
return true;
}
return false;
}
/**
* @param $cId
* @param $sId
* @param $packId
*
* @return array
* @deprecated
*/
public function getWorkListPackExamsByPack($cId, $sId, $packId)
{
$worklists = [];
if ($packId == PackBac::ID_PACK_BAC_2017) {
$qb = $this->_em->createQueryBuilder()
->select('wp.id', 'wp.date', 'd.title as title');
} else {
$qb = $this->_em->createQueryBuilder()
->select('wp.id', 'w.id as wId', 'w.title as day', 'd.title as discipline');
}
$qb->from('WorkListPackBac', 'wp')
->join('wp.worklist', 'w')
->join('wp.discipline', 'd')
->where('wp.course=:cId')
->andWhere('wp.serie=:sId')
->andWhere('wp.packExam=:pId')
->setParameter('cId', $cId)
->setParameter('sId', $sId)
->setParameter('pId', $packId);
$result = $qb->getQuery()->getResult();
if (count($result) > 0) {
if ($packId == PackBac::ID_PACK_BAC_2017) {
foreach ($qb->getQuery()->getResult() as $wp) {
$worklists[$wp['date']->format('d-m-Y')][$wp['id']] = $wp['title'];
}
} else {
foreach ($qb->getQuery()->getResult() as $wp) {
$worklists[$wp['discipline']][$wp['id']] = ['wId' => $wp['wId'], 'day' => $wp['day']];
}
}
}
return $worklists;
}
public function getSharedWorkListPackExam()
{
$query = $this->_em->getConnection()->prepare(
'select worklist_id as wId, course_id as cId, serie_id as sId FROM WorkListPackExam where worklist_id in (select worklist_id FROM WorkListPackExam group by worklist_id having count(*) > 1)'
);
$query->execute();
$result = $query->fetchAll();
return $result;
}
public function getWorklistByDomain(Domain $domain, Video $video)
{
foreach ($domain->getWorkListNode() as $wl) {
$w = $wl->getWorkList();
foreach ($w->getWorkListlines() as $wll) {
if ($wll->getAbstractWorkListTable() instanceof WorkListTableVideo && $wll->getAbstractWorkListTable(
)->getVideo()->getId() == $video->getId()) {
return $w;
}
}
}
return ! is_null($domain->getWorkListNode()[0]) ? $domain->getWorkListNode()[0]->getWorkList() : null;
}
public function getWorklistByDomainId($id)
{
$query = $this->_em->createQuery(
'SELECT wkl.id, wkl.title as worklist_title FROM BO\WorkListBundle\Entity\WorkListNode w JOIN w.worklist wkl WHERE w.domain = :id '
);
$query->setParameter('id', $id);
return $query->getArrayResult();
}
public function getWorklistDetailsForProgramByWorklistNodeId($worklist_node)
{
$query = $this->_em->createQuery(
"SELECT CASE WHEN v.id is not null THEN v.title ELSE CASE WHEN obj.id is not null THEN obj.title ELSE 'Exercice sur feuille' end end as title_content,
CASE WHEN v.id is not null THEN 'video' ELSE CASE WHEN obj.id is not null THEN 'exercise' ELSE 'homework' end end as type,
wkl.id as worklist_id, wkl.title as worklist_title , wlwls.id as worklistline_id,wkt.id as worklist_type
FROM Bo\WorkListBundle\Entity\WorkListNode wln
JOIN wln.worklist wkl
JOIN wkl.workListType wkt
JOIN Bo\WorkListBundle\Entity\WorkListLine wlwls WITH wlwls.worklist = wkl.id
LEFT JOIN Bo\WorkListBundle\Entity\WorkListTableVideo wv WITH wlwls.abstractWorkListTable = wv.id
LEFT JOIN wv.video v
LEFT JOIN Bo\WorkListBundle\Entity\WorklistTableObjectExercise woe WITH wlwls.abstractWorkListTable = woe.id
LEFT JOIN woe.objectExercise obj
LEFT JOIN Bo\WorkListBundle\Entity\WorkListTableStatementLine wts WITH wlwls.abstractWorkListTable = wts.id
WHERE wln.id = :worklist_node
ORDER BY wlwls.position ASC"
);
$query->setParameter('worklist_node', $worklist_node);
return $query->getArrayResult();
}
public function getWorklistNodeDisciplineBySerieCourse($serie, $course)
{
if (is_null($serie) || is_null($course)) {
return null;
}
$sql = "select * from
(select w.id as id, ad.slug as domain_slug,ad.id as domain_id,s.id as serie_id, c.id as course_id, wn.id as worklist_node_id,ch.id as chapter_id, ch.title as chapter_title, ch.slug as chapter_slug, dis.title as discipline_title,dis.slug as discipline_slug,w.packBlanc as ispackblanc, w.id as worklist_id, w.title as worklist_title,w.workListType_id as worklist_type
from `WorkListNode` wn
inner join `new_worklist` w on wn.`worklist_id`=w.`id`
inner join admin_domain ad on ad.id = wn.`domain_id`
inner join admin_discipline dis on dis.id = ad.`disciplines_id`
inner join Course c on c.id = ad.course_id
inner join Chapter ch on ch.id = ad.`chapter_id`
inner join admin_serie s on s.id = ad.`serie_id`
where s.id = " . $serie->getId() . " and ad.course_id = " . $course->getId() . " and ad.online = 1 ORDER BY dis.order,ad.position,w.position ASC) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn join admin_domain ad where ad.serie_id = " . $serie->getId(
) . " and ad.course_id = " . $course->getId() . ")
group by w.id)
h on t.id = h.id";
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
return $all;
}
/**
* @param WorkList $worklist
*
* @return mixed
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getWorklisPackDiscipline(WorkList $worklist)
{
$qb = $this->_em->createQueryBuilder();
$qb = $qb->select('d')->from('BoReferencesBundle:Discipline', 'd')
->join(WorkListPackBac::class, 'wp', Join::WITH, "wp.discipline = d.id")
->join(WorkList::class, 'w', Join::WITH, "w.id = wp.worklist")
->where('w = :worklist')
->setParameter('worklist', $worklist);
$discipline = $qb->getQuery()->getSingleResult();
return $discipline;
}
/**
* Recherche des packs
*
* @param StudentPackBac $studentPackBac
* @param \DateTime|null $date
* @param null $limit
*
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
public function getWorklistsByPack(StudentPackBac $studentPackBac, \DateTime $date = null, $limit = null)
{
$list_disciplines = [];
foreach ($studentPackBac->getDisciplines() as $discipline) {
$list_disciplines[] = $discipline->getId();
}
foreach ($studentPackBac->getObjectStudy() as $objectStudy) {
$list_objectStudy[] = $objectStudy->getId();
}
if (! $date) {
$date = new \DateTime("now");
}
$serie = $studentPackBac->getStudent()->getSerie();
$course = $studentPackBac->getStudent()->getCourse();
if ($studentPackBac->getPack()->getType() == PackBac::TYPE_ETE) {
$serie = $studentPackBac->getStudent()->getSerie();
$course = $studentPackBac->getStudent()->getCourse();
$d [] = date_diff($date, $studentPackBac->getStartDate())->days;
$dateNow = new \DateTime("now");
$per2days = $dateNow->add(new \DateInterval('P2D'));
if ($studentPackBac->getPack()->getStart() <= $studentPackBac->getStartDate(
) && $date >= $studentPackBac->getStartDate()) {
$subscriptionDay = date_diff($date, $studentPackBac->getStartDate())->days + 1;
} else {
$subscriptionDay = 0;
}
$dateCondition = "and wp.day_order <= $subscriptionDay ";
} else {
$subscriptionDate = $studentPackBac->getCreated();
//$subscriptionDate->sub(new \DateInterval('P2D'));
//$dateCondition = "and wp.date >= '".$date->format('Y')."' and wp.date <= '" . $date->format('Y-m-d') ."' ";
$dateCondition = "and wp.date <= '" . $date->format(
'Y-m-d 00:00:s'
) . "' or wp.date LIKE '" . $subscriptionDate->format('Y-m-d') . "' ";
//$dateCondition = "and wp.date >= '2020-01-01'";
}
$sql = "select * from
(select
wp.date as worklist_date,
wp.day_order as worklist_day,
spb.start_date as start_date,
w.id as id,
ad.slug as domain_slug,
ad.id as domain_id,
s.id as serie_id,
c.id as course_id,
ch.id as chapter_id,
ch.title as chapter_title,
ch.slug as chapter_slug,
dis.id as discipline_id,
dis.title as discipline_title,
dis.slug as discipline_slug,
w.packBlanc as ispackblanc,
w.id as worklist_id,
w.title as worklist_title,
w.workListType_id as worklist_type,
wn.id as worklist_node_id
from StudentPackBac spb
join WorkListPackBac wp on wp.packBac_id = spb.pack_id
join new_worklist w on w.id = wp.worklist_id
join admin_discipline dis on dis.id = wp.discipline_id
join Course c on c.id = wp.course_id
join admin_serie s on s.id = wp.serie_id
join ReviewProgram rp on rp.id = spb.pack_id
left join admin_domain ad on ad.id = w.domain_id
left join WorkListNode wn on w.id = wn.worklist_id
left join Chapter ch on ch.id = ad.chapter_id
where dis.id IN (" . implode(',', $list_disciplines) . ")
and s.id = " . $serie->getId() . "
and c.id = " . $course->getId() . "
and spb.id = " . $studentPackBac->getId() . "
$dateCondition
ORDER BY wp.day_order ASC, dis.order DESC) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn)
group by w.id)
h on t.id = h.id";
if ($limit) {
$sql .= " limit $limit";
}
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
return $all;
}
/**
* Recherche des packs
*
* @param StudentPackBac $studentPackBac
* @param \DateTime|null $date
* @param null $limit
*
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
public function getWorklistsByPackNew(StudentPackBac $studentPackBac, \DateTime $date = null, $limit = null)
{
$list_disciplines = [];
foreach ($studentPackBac->getDisciplines() as $discipline) {
$list_disciplines[] = $discipline->getId();
}
$serie = $studentPackBac->getStudent()->getSerie();
$course = $studentPackBac->getStudent()->getCourse();
$date = new \DateTime("now");
/*
$q1 = $this->_em->createQueryBuilder()
->select(
'spb.startDate as start_date',
's.id as id'
)
->from('BoStudentBundle:ReviewProgram', 'rp')
->join('rp.student', 's')
->join('rp.worklist', 'w')
->join('w.worklistPackExams', 'wPE')
->join('rp.studentPackBac', 'spb ')
->where('s.id = :studentId')
->setParameter('studentId', $studentPackBac->getStudent()->getId())
;
*/
$dateCondition1 = $date->format('Y-m-d');
$dateCondition2 = "and r.playlistReady <= '" . $date->format('Y-m-d') . "'";
$q1 = $this->_em->createQueryBuilder()
->select(
'r.day as worklist_date',
'pe.day as worklist_day',
'spb.startDate as start_date',
'r.playlistReady as playlist_ready',
'w.id as id',
'ad.slug as domain_slug',
'ad.id as domain_id',
's.id as serie_id',
'c.id as course_id',
'dis.id as discipline_id',
'dis.title as discipline_title',
'dis.slug as discipline_slug',
'ch.id as chapter_id',
'ch.title as chapter_title',
'ch.slug as chapter_slug',
'w.packBlanc as ispackblanc',
'w.id as worklist_id',
'w.title as worklist_title',
'workListType.id as worklist_type',
'wn.id as worklist_node_id'
)
->from('BoMainBundle:StudentPackBac', 'spb')
->join('spb.student', 'student')
->join('spb.reviewProgram', 'r')
->join('r.worklist', 'w')
->join('w.programExam', 'pe')
->join('pe.discipline', 'dis')
->join('pe.planningExam', 'planningExam')
->join('w.workListType', 'workListType')
->innerJoin('planningExam.course', 'c')
->innerJoin('planningExam.serie', 's')
->leftJoin('w.domain', 'ad')
->leftJoin('ad.chapter', 'ch')
->leftJoin('w.workListNode', 'wn')
->where('student.id = :studentId')
->andWhere("r.playlistReady <= '$dateCondition1' ")
->setParameter('studentId', $studentPackBac->getStudent()->getId())
->orderBy('pe.day', 'asc');
$all = $q1->getQuery()->getResult();
/*
$sql = "select * from
(select
r.day as worklist_date,
pe.day as worklist_day,
spb.start_date as start_date,
r.playlistReady as playlist_ready,
w.id as id,
ad.slug as domain_slug,
ad.id as domain_id,
s.id as serie_id,
c.id as course_id,
ch.id as chapter_id,
ch.title as chapter_title,
ch.slug as chapter_slug,
dis.id as discipline_id,
dis.title as discipline_title,
dis.slug as discipline_slug,
w.packBlanc as ispackblanc,
w.id as worklist_id,
w.title as worklist_title,
workListType.id as worklist_type,
wn.id as worklist_node_id
from StudentPackBac spb
join aba_user student on student.id = spb.student_id
join ReviewProgram r on r.studentPackBac_id = spb.id
join new_worklist w on w.id = r.worklist_id
join ProgramExam pe on pe.worklist_id = w.id
join admin_discipline dis on dis.id = pe.discipline_id
join PlanningExam planningExam on planningExam.id = pe.planningExam_id
join WorkListType workListType on workListType.id = w.workListType_id
left join Course c on c.id = planningExam.course_id
left join admin_serie s on s.id = planningExam.serie_id
left join admin_domain ad on ad.id = w.domain_id
left join WorkListNode wn on wn.worklist_id = w.id
left join Chapter ch on ch.id = ad.chapter_id
and spb.student_id = " . $studentPackBac->getStudent()->getId() . "
$dateCondition2
ORDER BY pe.day ASC, dis.order DESC ) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn)
group by w.id)
h on t.id = h.id";
if ($limit) {
$sql .= " limit $limit";
}
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
*/
return $all;
}
/**
* Recherche
*
* @param $wnIds
* @param $serie
* @param $course
*
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
public function getWorklistByIds($serie, $course, $wnIds)
{
$wnList = implode(', ', $wnIds);
$sql = "select * from
(select w.id as id,ad.slug as domain_slug,ad.id as domain_id,s.id as serie_id, c.id as course_id, wn.id as worklist_node_id,ch.id as chapter_id, ch.title as chapter_title, ch.slug as chapter_slug, dis.title as discipline_title,dis.slug as discipline_slug,w.packBlanc as ispackblanc, w.id as worklist_id, w.title as worklist_title,w.workListType_id as worklist_type
from `WorkListNode` wn
left join `new_worklist` w on wn.`worklist_id`=w.`id`
left join admin_domain ad on ad.id = wn.`domain_id`
left join admin_discipline dis on dis.id = ad.`disciplines_id`
left join Course c on c.id = ad.course_id
left join Chapter ch on ch.id = ad.`chapter_id`
left join admin_serie s on s.id = ad.`serie_id`
where s.id = " . $serie->getId() . " and ad.course_id = " . $course->getId() . " and wn.id IN (" . $wnList . ")
and w.stage = 0
and w.e3c = 0
ORDER BY dis.order ASC) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn join admin_domain ad where ad.serie_id = " . $serie->getId(
) . " and ad.course_id = " . $course->getId() . ")
and w.stage = 0
and w.e3c = 0
group by w.id)
h on t.id = h.id";
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
return $all;
}
/**
* @param $page
* @param int $max
*
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
public function getPaginatedWorklists($page, $max = 100)
{
$offset = $max * ($page - 1);
$sql = "select * from
(select w.id as id,ad.slug as domain_slug,ad.id as domain_id,s.id as serie_id, c.id as course_id, wn.id as worklist_node_id,ch.id as chapter_id, ch.title as chapter_title, ch.slug as chapter_slug, dis.title as discipline_title,dis.slug as discipline_slug,w.packBlanc as ispackblanc, w.id as worklist_id, w.title as worklist_title,w.workListType_id as worklist_type
from `WorkListNode` wn
left join `new_worklist` w on wn.`worklist_id`=w.`id`
left join admin_domain ad on ad.id = wn.`domain_id`
left join admin_discipline dis on dis.id = ad.`disciplines_id`
left join Course c on c.id = ad.course_id
left join Chapter ch on ch.id = ad.`chapter_id`
left join admin_serie s on s.id = ad.`serie_id`
ORDER BY dis.order ASC
LIMIT $max OFFSET $offset) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn join admin_domain ad)
group by w.id)
h on t.id = h.id";
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
return $all;
}
/**
* Recherche
*
* @param $serie
* @param $course
* @param $discipline
* @param $search
*
* @return array
* @throws \Doctrine\DBAL\DBALException
*/
public function getWorklistBySearch($serie, $course, $discipline = null, $search)
{
$list_words = explode(" ", $search);
$andWhere = "";
foreach ($list_words as $key => $word) {
if ($key > 0) {
$andWhere .= " and ";
}
$word = addslashes($word);
$andWhere .= " w.title LIKE '%$word%'";
}
if ($discipline !== null) {
$andWhereDiscipline = ' and ad.disciplines_id = ' . $discipline->getId() . ' ';
} else {
$andWhereDiscipline = '';
}
$sql = "select * from
(select w.id as id,ad.slug as domain_slug,ad.id as domain_id,s.id as serie_id, c.id as course_id, wn.id as worklist_node_id,ch.id as chapter_id, ch.title as chapter_title, ch.slug as chapter_slug, dis.title as discipline_title,dis.slug as discipline_slug,w.packBlanc as ispackblanc, w.id as worklist_id, w.title as worklist_title,w.workListType_id as worklist_type
, w.free_access, dis.id as discipline_id
from `WorkListNode` wn
left join `new_worklist` w on wn.`worklist_id`=w.`id`
left join admin_domain ad on ad.id = wn.`domain_id`
left join admin_discipline dis on dis.id = ad.`disciplines_id`
left join Course c on c.id = ad.course_id
left join Chapter ch on ch.id = ad.`chapter_id`
left join admin_serie s on s.id = ad.`serie_id`
where s.id = " . $serie->getId() . " and ad.course_id = " . $course->getId(
) . " " . $andWhereDiscipline . " and " . $andWhere . "
and w.stage = 0
and w.e3c = 0
ORDER BY dis.order ASC
) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn join admin_domain ad where ad.serie_id = " . $serie->getId(
) . " and ad.course_id = " . $course->getId() . " " . $andWhereDiscipline . " and" . $andWhere . ")
and w.stage = 0
and w.e3c = 0
group by w.id)
h on t.id = h.id";
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
return $all;
}
public function getAllWorkListReviewProgram()
{
$q1 = $this->_em->createQueryBuilder()
->select('w')
->from('BoWorkListBundle:Worklist', 'w')
->join('w.studentReviewProgram', 's')
->where('w.studentReviewProgram is not null')
->andWhere('s.created > "2017-09-01 00:00:00"');
return $q1->getQuery()->getResult();
}
public function updatePictureMobileId(WorkList $worklist, $id)
{
$queryBuilder = $this->_em->createQueryBuilder()->update(WorkList::class, 'w')
->set('w.pictureMobileId', ':pictureMobileId')
->where('w.id = :id')
->setParameter('pictureMobileId', $id)
->setParameter('id', $worklist->getId());
$queryBuilder->getQuery()->execute();
}
public function getWorklistNodeDisciplineByChapter($serie, $course, $discipiline, $chapter)
{
if (is_null($serie) || is_null($course) || is_null($discipiline) || is_null($chapter)) {
return null;
}
$sql = "select * from
(select w.id as id, ad.slug as domain_slug,ad.id as domain_id,s.id as serie_id, c.id as course_id, wn.id as worklist_node_id,ch.id as chapter_id, ch.title as chapter_title, ch.slug as chapter_slug, dis.title as discipline_title,dis.slug as discipline_slug,w.packBlanc as ispackblanc, w.id as worklist_id, w.title as worklist_title,w.workListType_id as worklist_type
from `WorkListNode` wn
left join `new_worklist` w on wn.`worklist_id`=w.`id`
left join admin_domain ad on ad.id = wn.`domain_id`
left join admin_discipline dis on dis.id = ad.`disciplines_id`
left join Course c on c.id = ad.course_id
left join Chapter ch on ch.id = ad.`chapter_id`
left join admin_serie s on s.id = ad.`serie_id`
where s.id = " . $serie->getId() . " and ad.course_id = " . $course->getId(
) . " and dis.id = " . $discipiline->getId() . " and ch.id = " . $chapter->getId() . "
and w.stage = 0 and w.e3c = 0
ORDER BY dis.title,ad.position,w.position ASC) t
left join
( select w.id,coalesce(sum(discr = 'ObjectExercise'),0) exercice,coalesce(sum(discr = 'video'), 0) nbrVideo,coalesce(sum(discr = 'statementLines'), 0) statement
from new_worklist w
join WorkListLine wl on wl.`worklist_id`=w.id
join `AbstractWorkListTable` awt on awt.id=wl.`abstractWorkListTable_id` where w.id in (select distinct(wn.`worklist_id`) from `WorkListNode` wn join admin_domain ad where ad.serie_id = " . $serie->getId(
) . " and ad.course_id = " . $course->getId() . ")
and w.stage = 0 and w.e3c = 0
group by w.id)
h on t.id = h.id";
$stmt = $this->_em->getConnection()->query($sql);
$all = $stmt->fetchAll();
return $all;
}
public function getNumberElementSubQuery()
{
return '
(
SELECT COUNT(wl.id)
FROM ' . $this->_em->getClassMetadata('BoWorkListBundle:WorkListLine')->getTableName() . ' wl
INNER JOIN AbstractWorkListTable awlt ON awlt.id = wl.abstractWorkListTable_id
INNER JOIN Video v ON v.id = awlt.video_id
WHERE wl.worklist_id = w.id
AND awlt.video_id IS NOT NULL
) AS number_element_video,
(
SELECT COUNT(wl.id)
FROM ' . $this->_em->getClassMetadata('BoWorkListBundle:WorkListLine')->getTableName() . ' wl
INNER JOIN AbstractWorkListTable awlt ON awlt.id = wl.abstractWorkListTable_id
INNER JOIN ObjectExercise o ON o.id = awlt.objectExercise_id
WHERE wl.worklist_id = w.id
AND awlt.objectExercise_id IS NOT NULL
AND (
SELECT COUNT(ans.id)
FROM qcm_question qq
INNER JOIN Answer ans ON ans.question_id = qq.id
WHERE qq.exercise_id = o.id
) > 1
) AS number_element_qcm
';
}
public function getAllByCourseSerieDisciplineChapterWithTotalLine(
Course $course,
Serie $serie,
Discipline $discipline,
Chapter $chapter
) {
$stmt = $this
->_em
->getConnection()
->prepare(
"
SELECT w.id, w.title, free_access AS hasFreeAccess, ad.id AS domainId, w.picture_mobile_id,
" . $this->getNumberElementSubQuery() . "
FROM " . $this->_em->getClassMetadata('BoWorkListBundle:WorkListNode')->getTableName() . " wln
INNER JOIN " . $this->_em->getClassMetadata('BoDomainBundle:Domain')->getTableName() . " ad ON ad.id = wln.domain_id
INNER JOIN " . $this->_em->getClassMetadata('BoReferencesBundle:Chapter')->getTableName() . " c ON c.id = ad.chapter_id
INNER JOIN " . $this->_em->getClassMetadata('BoWorkListBundle:WorkList')->getTableName() . " w ON w.id = wln.worklist_id
WHERE ad.chapter_id = " . $chapter->getId() . "
AND ad.course_id = " . $course->getId() . "
AND ad.serie_id = " . $serie->getId() . "
AND ad.disciplines_id = " . $discipline->getId() . "
AND ad.`online` = 1
AND (w.packBlanc IS NULL OR w.packBlanc = 0)
AND w.stage = 0
AND w.e3c = 0
ORDER BY wln.position
"
);
$stmt->execute();
return $stmt->fetchAll();
}
public function getAllByUserWithTotalLineEnded(
User $user,
Course $course,
Serie $serie,
Discipline $discipline,
Chapter $chapter
) {
$stmt = $this
->_em
->getConnection()
->prepare(
"
SELECT rp.worklist_id AS id, COUNT(*) as number_element
FROM " . $this->_em->getClassMetadata('BoStudentBundle:ReviewProgram')->getTableName() . " rp
INNER JOIN " . $this->_em->getClassMetadata('BoWorkListBundle:WorkList')->getTableName() . " w ON w.id = rp.worklist_id
INNER JOIN " . $this->_em->getClassMetadata('BoDomainBundle:Domain')->getTableName() . " ad ON ad.id = rp.domain_id
INNER JOIN " . $this->_em->getClassMetadata(
'BoWorkListBundle:AbstractWorkListTableReviewProgramInformations'
)->getTableName() . " awltrpi ON awltrpi.review_program_id = rp.id
INNER JOIN " . $this->_em->getClassMetadata('BoWorkListBundle:AbstractWorkListTable')->getTableName() . " awlt ON awlt.id = awltrpi.abstract_work_list_table_id
WHERE rp.student_id = " . $user->getId() . "
AND awltrpi.ended = 1
AND ad.chapter_id = " . $chapter->getId() . "
AND ad.course_id = " . $course->getId() . "
AND ad.serie_id = " . $serie->getId() . "
AND ad.disciplines_id = " . $discipline->getId() . "
AND ad.`online` = 1
AND (w.packBlanc IS NULL OR w.packBlanc = 0)
AND w.stage = 0
AND w.e3c = 0
AND (
awlt.video_id IS NOT NULL OR
(awlt.objectExercise_id IS NOT NULL
AND (
SELECT COUNT(ans.id)
FROM qcm_question qq
INNER JOIN Answer ans ON ans.question_id = qq.id
WHERE qq.exercise_id = awlt.objectExercise_id
) > 1)
)
GROUP BY rp.worklist_id
"
);
$stmt->execute();
return $stmt->fetchAll();
}
public function getAllWorklistVideo(WorkList $workList, ReviewProgram $reviewProgram)
{
$stmt = $this
->_em
->getConnection()
->prepare(
"
SELECT awlt.id, v.title, v.description, v.picture_mobile_id,
(
SELECT ended
FROM " . $this->_em->getClassMetadata(
'BoWorkListBundle:AbstractWorkListTableReviewProgramInformations'
)->getTableName() . " awltrpi
WHERE awltrpi.review_program_id = " . $reviewProgram->getId() . "
AND awltrpi.abstract_work_list_table_id = awlt.id
) AS isEnded
FROM WorkListLine wl
INNER JOIN AbstractWorkListTable awlt ON awlt.id = wl.abstractWorkListTable_id
INNER JOIN Video v ON v.id = awlt.video_id
WHERE wl.worklist_id = " . $workList->getId() . "
AND awlt.video_id IS NOT NULL
ORDER BY wl.position
"
);
$stmt->execute();
return $stmt->fetchAll();
}
public function getAllWorklistQcm(WorkList $workList, ReviewProgram $reviewProgram)
{
$stmt = $this
->_em
->getConnection()
->prepare(
"
SELECT awlt.id, o.title,
(
SELECT ended
FROM " . $this->_em->getClassMetadata(
'BoWorkListBundle:AbstractWorkListTableReviewProgramInformations'
)->getTableName() . " awltrpi
WHERE awltrpi.review_program_id = " . $reviewProgram->getId() . "
AND awltrpi.abstract_work_list_table_id = awlt.id
) AS isEnded
FROM WorkListLine wl
INNER JOIN AbstractWorkListTable awlt ON awlt.id = wl.abstractWorkListTable_id
INNER JOIN ObjectExercise o ON o.id = awlt.objectExercise_id
WHERE wl.worklist_id = " . $workList->getId() . "
AND o.display_mobile = 1
AND awlt.objectExercise_id IS NOT NULL
AND (
SELECT COUNT(ans.id)
FROM qcm_question qq
INNER JOIN Answer ans ON ans.question_id = qq.id
WHERE qq.exercise_id = o.id
) > 1
ORDER BY wl.position
"
);
$stmt->execute();
return $stmt->fetchAll();
}
public function getWorklistVideoOrFiche(
WorkList $workList,
AbstractWorkListTable $abstractWorkListTable,
$type = 'video'
) {
switch ($type) {
case 'fiche':
$complementSelect = ', v.description, v.title AS videoTitle, v.picture_mobile_id';
break;
default :
$complementSelect = ', v.poster AS picture';
break;
}
$stmt = $this
->_em
->getConnection()
->prepare(
"
SELECT v.id, v.title, v.vimeo_file AS videoUrl " . $complementSelect . "
FROM WorkListLine wl
INNER JOIN AbstractWorkListTable awlt ON awlt.id = wl.abstractWorkListTable_id
INNER JOIN Video v ON v.id = awlt.video_id
WHERE wl.worklist_id = " . $workList->getId() . "
AND awlt.id = " . $abstractWorkListTable->getId() . "
AND awlt.video_id IS NOT NULL
"
);
$stmt->execute();
return $stmt->fetchAll();
}
public function getWorklistQcm(WorkList $workList, $abstractWorkListTable)
{
if ($abstractWorkListTable instanceof AbstractWorkListTable) {
$andWhere = " AND awlt.id = " . $abstractWorkListTable->getId() . " ";
} elseif (is_array($abstractWorkListTable)) {
$andWhere = " AND awlt.id IN (" . implode(', ', $abstractWorkListTable) . ") ";
}
$stmt = $this
->_em
->getConnection()
->prepare(
"
SELECT awlt.id, o.id as qcmId, o.title as qcmTitle, q.statement AS qcmInstruction, qq.id AS questionId, qq.position AS questionPosition, qq.statement AS questionText, a.id AS answerId, a.answer, a.valid
FROM WorkListLine wl
INNER JOIN AbstractWorkListTable awlt ON awlt.id = wl.abstractWorkListTable_id
INNER JOIN " . $this->_em->getClassMetadata('BoExerciseBundle:ObjectExercise')->getTableName() . " o ON o.id = awlt.objectExercise_id
INNER JOIN " . $this->_em->getClassMetadata('BoExerciseBundle:Quiz')->getTableName() . " q ON q.id = awlt.objectExercise_id
INNER JOIN " . $this->_em->getClassMetadata('BoExerciseBundle:QCMQuestion')->getTableName() . " qq ON qq.exercise_id = q.id
INNER JOIN " . $this->_em->getClassMetadata('BoExerciseBundle:Answer')->getTableName() . " a ON a.question_id = qq.id
WHERE wl.worklist_id = " . $workList->getId() . "
" . $andWhere . "
AND awlt.objectExercise_id IS NOT NULL
ORDER BY qq.position
"
);
$stmt->execute();
return $stmt->fetchAll();
}
/**
* @return array
*/
public function searchWorkList()
{
$query = $this->_em->createQueryBuilder();
$query->select('w.id', 'w.picture', 'w.pictureFreeAccess')
->from('BoWorkListBundle:WorkList', 'w');
return $query->getQuery()->getResult();
}
}

src/Repository/Educational/WorkListRepository.php line 766