<?php
/**
 * Created by PhpStorm.
 * User: remy.bages
 * Date: 24/05/2018
 * Time: 14:39
 */

namespace AppBundle\Entity\Repositories;


use Doctrine\ORM\EntityRepository;

class EcheancesRepository extends EntityRepository
{

    public function getEcheancesFromSiret($siret, $date)
    {
        $sql ="SELECT e.*, da.*, i.siret AS interlocuteurSiret
                FROM `echeance` AS e 
                INNER JOIN dossier_aide AS da ON da.numero=e.dossier_aide_numero
                INNER JOIN interlocuteur AS i ON da.interlocuteur_siret=i.siret
                WHERE i.siret =". $siret. " AND YEAR(e.date_echeance)=".$date;

        $statement = $this->getEntityManager()->getConnection()->prepare($sql);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function getEcheancesFromAnnee($date)
    {
        $sql ="SELECT e.*,da.*, i.siret AS interlocuteurSiret
                FROM `echeance` AS e 
                INNER JOIN dossier_aide AS da ON da.numero=e.dossier_aide_numero
                INNER JOIN interlocuteur AS i ON da.interlocuteur_siret=i.siret
                WHERE YEAR(e.date_echeance)=".$date;

        $statement = $this->getEntityManager()->getConnection()->prepare($sql);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function getEcheancesFromNumeroDossier($dossierID)
    {
        $sql ="SELECT * FROM `echeance` AS e 
                INNER JOIN dossier_aide AS da ON da.numero=e.dossier_aide_numero
                LEFT JOIN modalite_aide AS ma ON ma.dossier_aide_numero=e.dossier_aide_numero AND ma.code=e.code_modalite
                WHERE e.dossier_aide_numero = '". $dossierID . "'";

        $statement = $this->getEntityManager()->getConnection()->prepare($sql);
        $statement->execute();

        return $statement->fetchAll();
    }

}