<?php

namespace App\Repository;

use App\Entity\ActivityPTA;
use App\Entity\Commune;
use App\Entity\Ministry;
use App\Entity\Mainstay;
use App\Entity\Goal;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr;
use Doctrine\Persistence\ManagerRegistry;

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

    // /**
    //  * @return ActivityPTA[] Returns an array of ActivityPTA objects
    //  */
    /*
    public function findByExampleField($value)
    {
        return $this->createQueryBuilder('a')
            ->andWhere('a.exampleField = :val')
            ->setParameter('val', $value)
            ->orderBy('a.id', 'ASC')
            ->setMaxResults(10)
            ->getQuery()
            ->getResult()
        ;
    }
    */

    /*
    public function findOneBySomeField($value): ?ActivityPTA
    {
        return $this->createQueryBuilder('a')
            ->andWhere('a.exampleField = :val')
            ->setParameter('val', $value)
            ->getQuery()
            ->getOneOrNullResult()
        ;
    }
    */

    public function findByMinistryByEtat($year, Ministry $ministry) //, $validated
    {
        $qb = $this->createQueryBuilder('y')
            ->innerJoin(Ministry::class, 'm', Expr\Join::WITH, 'm.id = y.ministry')

            //->andWhere('a.validated = :validated')
            ->andWhere('y.year = :year')
            ->andWhere('m.id = :ministry')
            ->andWhere('y.etat <> true')

            //->setParameter('validated', $validated)
            ->setParameter('year', $year)
            ->setParameter('ministry', $ministry)
            ;

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

        foreach ($rows as $key => $row) {
            //$rows[$key]['activityId'] = $this->getDoctrine()->getRepository(Activity::class)->findOneBy(['id' => $rows[$key]['activityId']]);
        }

        return $rows;
    }


    public function findByCommuneByEtat($year, Commune $commune) //, $validated
    {
        $qb = $this->createQueryBuilder('y')
            ->innerJoin(Commune::class, 'm', Expr\Join::WITH, 'm.id = y.commune')

            //->andWhere('a.validated = :validated')
            ->andWhere('y.year = :year')
            ->andWhere('m.id = :commune')
            ->andWhere('y.etat <> true')

            //->setParameter('validated', $validated)
            ->setParameter('year', $year)
            ->setParameter('commune', $commune)
            ;

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

        foreach ($rows as $key => $row) {
            //$rows[$key]['activityId'] = $this->getDoctrine()->getRepository(Activity::class)->findOneBy(['id' => $rows[$key]['activityId']]);
        }

        return $rows;
    }

    public function findByMinistry($year, Ministry $ministry) //, $validated
    {
        $qb = $this->createQueryBuilder('y')
            ->innerJoin(Ministry::class, 'm', Expr\Join::WITH, 'm.id = y.ministry')

            //->andWhere('a.validated = :validated')
            ->andWhere('y.year = :year')
            ->andWhere('m.id = :ministry')

            //->setParameter('validated', $validated)
            ->setParameter('year', $year)
            ->setParameter('ministry', $ministry)
            ;

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

        foreach ($rows as $key => $row) {
            //$rows[$key]['activityId'] = $this->getDoctrine()->getRepository(Activity::class)->findOneBy(['id' => $rows[$key]['activityId']]);
        }

        return $rows;
    }


    public function findByCommune($year, Commune $commune) //, $validated
    {
        $qb = $this->createQueryBuilder('y')
            ->innerJoin(Commune::class, 'm', Expr\Join::WITH, 'm.id = y.commune')

            //->andWhere('a.validated = :validated')
            ->andWhere('y.year = :year')
            ->andWhere('m.id = :commune')

            //->setParameter('validated', $validated)
            ->setParameter('year', $year)
            ->setParameter('ministry', $commune)
            ;

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

        foreach ($rows as $key => $row) {
            //$rows[$key]['activityId'] = $this->getDoctrine()->getRepository(Activity::class)->findOneBy(['id' => $rows[$key]['activityId']]);
        }

        return $rows;
    }

    
    public function getFinancial($year, Mainstay $mainstay, ?Goal $goal)
    {        
        $whereClause = $goal ? 'a.id = :goal' : 'm.id = :mainstay';
        $paramClause = $goal ? 'goal' : 'mainstay';
        $paramObject = $goal ? $goal : $mainstay;

        $qb = $this->createQueryBuilder('y')
            //->innerJoin(Action::class, 'a', Expr\Join::WITH, 'a.id = y.action')
            //->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')
            //->innerJoin(Ministry::class, 'm', Expr\Join::WITH, 'm.id = p.ministry')

            //->andWhere('a.validated = :validated')
            ->andWhere('y.year = :year')
            //->andWhere($whereClause)

            //->setParameter('validated', $validated)
            ->setParameter('year', $year)
            //->setParameter($paramClause, $paramObject)
            ;

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

        foreach ($rows as $key => $row) {
            //$rows[$key]['activityId'] = $this->getDoctrine()->getRepository(Activity::class)->findOneBy(['id' => $rows[$key]['activityId']]);
        }

        return $rows;
    } 
    
    public function getTotal($year, $pilier=null, $odd=null, $ministry=null,$commune=null)
    { 
        return $this->createQueryBuilder('a')
            ->select('COUNT(a.id)')

            ->innerJoin(Action::class, 'a', Expr\Join::WITH, 'a.id = y.action')
            ->innerJoin(Programm::class, 'p', Expr\Join::WITH, 'p.id = a.programm')

            ->andWhere('a.year = :year')
            ->andWhere('y.validated = :validated')
            //->andWhere('y.treated = :treated')
            ->andWhere($treated ? 'y.treated = :treated' : '1 = :treated')
            ->andWhere($ministry ? 'p.ministry = :ministry' : '1 = :ministry')
            ->andWhere($commune ? 'p.commune = :commune' : '1 = :commune')
            ->setParameter('year', $year)
            ->setParameter('validated', true)
            //->setParameter('treated', true)
            ->setParameter('treated', $treated ? true : 1)
            ->setParameter('ministry', $ministry ? $ministry : 1)
            ->setParameter('commune', $commune ? $commune : 1)
            ->getQuery()
            ->getSingleScalarResult();
    }
}