<?php

namespace App\Repository;

use App\Entity\Mainstay;
use App\Entity\Accelerator;
use App\Entity\Costing;
use App\Entity\ActivityPTA;
use App\Entity\ActivityOSC;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\Query\Expr;
use Doctrine\Persistence\ManagerRegistry;

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

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

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

    public function getFinancialPTA($year)
    { 
        $qb = $this->createQueryBuilder('m')
            ->select('m.code')
            ->addSelect('m.name')
            ->addSelect('m.color')
            ->addSelect('SUM(y.expectedCost) AS sumExpectedCost')
            ->addSelect('SUM(y.realizedCost) AS sumRealizedCost')
            ->addSelect('COUNT(y.id) AS nbActivity')

            ->innerJoin(Accelerator::class, 'a', Expr\Join::WITH, 'm.id = a.mainstay')
            ->innerJoin(Costing::class, 'c', Expr\Join::WITH, 'a.id = c.accelerator')
            ->innerJoin(ActivityPTA::class, 'y', Expr\Join::WITH, 'c.id = y.costing')

            //->andWhere('y.executed = 1')
            ->andWhere('y.year = :year')
            ->setParameter('year', $year)
            ->groupBy('m.id') 
            ->orderBy('m.id', 'ASC')
            ;

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

        foreach ($rows as $key => $row) {
            $rows[$key]['rate'] = $rows[$key]['sumRealizedCost'] / $rows[$key]['sumExpectedCost'] * 100;
            /*$appreciation = $this->getEntityManager()
                ->getRepository(Appreciation::class)
                ->findOneByValue($rows[$key]['sensitivity']);
            $rows[$key]['appreciation'] = $appreciation->getName();
            $rows[$key]['flag'] = $appreciation->getFlag();*/
        }

        return array_reverse($rows);
    }  

    public function getFinancialOSC($year)
    { 
        $qb = $this->createQueryBuilder('m')
            ->select('m.code')
            ->addSelect('m.name')
            ->addSelect('m.color')
            ->addSelect('SUM(y.expectedCost) AS sumExpectedCost')
            ->addSelect('SUM(y.realizedCost) AS sumRealizedCost')
            ->addSelect('COUNT(y.id) AS nbActivity')

            //->innerJoin(Accelerator::class, 'a', Expr\Join::WITH, 'm.id = a.mainstay')
            ->innerJoin(ActivityOSC::class, 'y', Expr\Join::WITH, 'm.id = y.mainstay')

            //->andWhere('y.executed = 1')
            ->andWhere('y.year = :year')
            ->setParameter('year', $year)
            ->groupBy('m.id') 
            ->orderBy('m.id', 'ASC')
            ;

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

        foreach ($rows as $key => $row) {
            $rows[$key]['rate'] = $rows[$key]['sumRealizedCost'] / $rows[$key]['sumExpectedCost'] * 100;
            /*$appreciation = $this->getEntityManager()
                ->getRepository(Appreciation::class)
                ->findOneByValue($rows[$key]['sensitivity']);
            $rows[$key]['appreciation'] = $appreciation->getName();
            $rows[$key]['flag'] = $appreciation->getFlag();*/
        }

        return array_reverse($rows);
    }  
}