<?php

namespace App\Repository;

use App\Entity\Direction;
use App\Entity\Activity;
use App\Entity\Notation;
use App\Entity\Target;
use App\Entity\Goal;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Direction|null find($id, $lockMode = null, $lockVersion = null)
 * @method Direction|null findOneBy(array $criteria, array $orderBy = null)
 * @method Direction[]    findAll()
 * @method Direction[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class DirectionRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Direction::class);
    }

    public function findDirectionByYear($year, $ministry, $commune) {
        return $this->createQueryBuilder('d')
            ->andWhere('d.years LIKE :year')
            ->andWhere($ministry ? 'd.ministry = :ministry' : '1 = :ministry')
            ->andWhere($commune ? 'd.commune = :commune' : '1 = :commune')
            ->setParameter('year', $year)
            ->setParameter('ministry', $ministry ? $ministry : 1)
            ->setParameter('commune', $commune ? $commune : 1)
            ->orderBy('d.code', 'ASC');
    }
    
    public function getSensitivity($year, Goal $goal, ?Target $target)
    {        
        $whereClause = $target ? 't.id = :target' : 'g.id = :goal';
        $paramClause = $target ? 'target' : 'goal';
        $paramObject = $target ? $target : $goal;

        $qb = $this->createQueryBuilder('d')
            ->select('d.code')
            ->addSelect('d.name')
            ->addSelect('IDENTITY(d.ministry) AS ministry')
            ->addSelect('SUM(n.sensitivity) AS sumSensitivity')
            ->addSelect('COUNT(n.target) AS nbActivity')
            ->addSelect('SUM(n.sensitivity)/COUNT(n.target) AS sensitivity')
            ->addSelect('SUM(CASE WHEN n.duplicated=false THEN y.amount ELSE 0 END) AS amount')

            ->innerJoin(Activity::class, 'y', Expr\Join::WITH, 'd.id = y.direction')
            ->innerJoin(Notation::class, 'n', Expr\Join::WITH, 'y.id = n.activity')
            ->innerJoin(Target::class, 't', Expr\Join::WITH, 't.id = n.target')
            ->innerJoin(Goal::class, 'g', Expr\Join::WITH, 'g.id = t.goal')

            ->andWhere('n.sensitivity != 0')
            ->andWhere('y.year = :year')
            ->andWhere($whereClause)

            ->setParameter('year', $year)
            ->setParameter($paramClause, $paramObject)
            ->groupBy('d.id') 
            ->orderBy('d.id', 'ASC')
            ;

        $rows = $qb->getQuery()->getResult();

        foreach ($rows as $key => $row) {
        }

        return array_reverse($rows);
    }    
}