<?php

namespace App\Repository;

use App\Entity\Ministry;
use App\Entity\Commune;
use App\Entity\Programm;
use App\Entity\Action;
use App\Entity\Department;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr;
use Doctrine\Persistence\ManagerRegistry;

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

    public function getTotal($year, $type, ?Ministry $ministry, ?Commune $commune , ?Department $department)
    { 
        return $this->createQueryBuilder('a')
            ->select('COUNT(a.id)')

            ->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')
            ->leftJoin('p.commune', 'c') 

            ->andWhere('a.year = :year')
            ->andWhere('a.validated = :validated')
            ->andWhere($ministry ? 'p.ministry = :ministry' : '1 = :ministry')
            ->andWhere($commune ? 'p.commune = :commune' : '1 = :commune')
            ->andWhere($department ? 'c.department = :department' : '1 = :department')
            ->andWhere(($type === 'ministere' || $ministry) ? 'p.ministry IS NOT NULL' : '1 = 1')
            ->andWhere(($type === 'commune' || $commune) ? 'p.commune IS NOT NULL' : '1 = 1')
            ->setParameter('year', $year)
            ->setParameter('validated', true)
            ->setParameter('ministry', $ministry ? $ministry : 1)
            ->setParameter('commune', $commune ? $commune : 1)
            ->setParameter('department', $department ? $department : 1)
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function getTotalTreated($year, $type, ?Ministry $ministry, ?Commune $commune, ?Department $department)
    { 
        return $this->createQueryBuilder('a')
            ->select('COUNT(a.id)')

            ->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')
            ->leftJoin('p.commune', 'c') 

            ->andWhere('a.year = :year')
            ->andWhere('a.validated = :validated')
            ->andWhere('a.treated = :treated')
            ->andWhere($ministry ? 'p.ministry = :ministry' : '1 = :ministry')
            ->andWhere($commune ? 'p.commune = :commune' : '1 = :commune')
            ->andWhere($department ? 'c.department = :department' : '1 = :department')
            ->andWhere(($type === 'ministere' || $ministry) ? 'p.ministry IS NOT NULL' : '1 = 1')
            ->andWhere(($type === 'commune' || $commune) ? 'p.commune IS NOT NULL' : '1 = 1')
            ->setParameter('year', $year)
            ->setParameter('validated', true)
            ->setParameter('treated', true)
            ->setParameter('ministry', $ministry ? $ministry : 1)
            ->setParameter('commune', $commune ? $commune : 1)
            ->setParameter('department', $department ? $department : 1)
            ->getQuery()
            ->getSingleScalarResult();
    }
}