<?php


namespace AppBundle\Entity\Repositories;

use Doctrine\ORM\EntityRepository;

class DossierAideRepository extends EntityRepository
{
    public function findByFilter($interlocuteurSiret,$searchedValue){
        if($interlocuteurSiret != 'tous'){
            $sqlInterlocuteurSelected = "INNER JOIN interlocuteur AS i ON i.siret=d.interlocuteur_siret
            WHERE 
            d.interlocuteur_siret = '" . $interlocuteurSiret ."' AND (
            i.raison_sociale LIKE '%" . $searchedValue . "%' OR 
            i.commune LIKE '%" . $searchedValue . "%' OR ";
            $sqlParenthese = ')';
        }else{
            $sqlInterlocuteurSelected = " WHERE ";
            $sqlParenthese = '';
        }

        $sql = "
            SELECT * FROM `dossier_aide` AS d 
            " . $sqlInterlocuteurSelected . "
            d.ligne_programme LIKE '%" . $searchedValue . "%' OR 
            d.numero LIKE '%" . $searchedValue . "%' OR  
            d.objet LIKE '%" . $searchedValue . "%' OR 
            d.localisation LIKE '%" . $searchedValue . "%' OR 
            d.etat LIKE '%" . $searchedValue . "%' OR 
            d.montant_participation LIKE '%" . $searchedValue . "%' OR 
            d.date_notification LIKE '%" . $searchedValue . "%' " . $sqlParenthese . "
            ORDER BY d.date_notification DESC
        ";

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

        return $statement->fetchAll();
    }
}