<?php

namespace App\Service;

use App\Entity\Activity;
use App\Entity\ActivityPTA;
use App\Entity\Ministry;
use App\Entity\StructureYear;
use App\Utils\Settings;
use App\Entity\Indicator;
use App\Entity\Year;
use App\Entity\Commune;
use App\Entity\Department;
use App\Entity\Target;
use App\Entity\RealisationProjet;
use App\Entity\Goal;
use App\Entity\User;
use App\Entity\Zone;
use App\Entity\Statut;
use App\Entity\Indice;
use App\Entity\Desagregation;
use App\Entity\IndiceCommune;
use App\Entity\Modalite;
use App\Entity\Performance;
use App\Entity\ValeurSousIndicateur;
use Craue\ConfigBundle\Util\Config as CraueConfig;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\DependencyInjection\ParameterBag\ParameterBagInterface;
use Symfony\Component\Security\Core\Security;
use Symfony\Component\HttpFoundation\Session\SessionInterface;

class SettingsManager
{
    private $craueConfig;
    private $params;
    private $doctrine;
    private $logger;
    private $session;
    private $security;

    private $settings = [
        'brand_name',
        'administrator_email',
        'phone_number',
        'google_api_key',
        'latlng',
        'ministry_weight',
        'home_intro',
    ];

    private $secretSettings = [
        'google_api_key',
    ];

    private $cache = [];

    public function __construct(
        CraueConfig $craueConfig,
        ManagerRegistry $doctrine,
        ParameterBagInterface $params,
        LoggerInterface $logger,
        SessionInterface $session,
        Security $security) 
    {
        $this->craueConfig = $craueConfig;
        $this->doctrine = $doctrine;
        $this->logger = $logger;
        $this->session = $session;
        $this->security = $security;

        $this->configEntityName = $params->get('craue_entity_name');
    }

    public function getSettings()
    {
        return $this->settings;
    }

    public function isSecret($name)
    {
        return in_array($name, $this->secretSettings);
    }

    public function get($name)
    {
        switch ($name) {
            case 'timezone':
                return ini_get('date.timezone');
        }

        if (isset($this->cache[$name])) {

            return $this->cache[$name];
        }

        try {

            $value = $this->craueConfig->get($name);

            $this->cache[$name] = $value;

            return $value;

        } catch (\RuntimeException $e) {}
    }

    public function getBoolean($name)
    {
        return filter_var($this->get($name), FILTER_VALIDATE_BOOLEAN);
    }

    public function set($name, $value, $section = null)
    {
        $className = $this->configEntityName;

        $params = [
            'name' => $name,
        ];

        if (!empty($section)) {
            $params['section'] = $section;
        }

        $setting = $this->doctrine
            ->getRepository($className)
            ->findOneBy($params);

        if (!$setting) {

            $setting = new $className();
            $setting->setName($name);
            $setting->setSection($section);

            $this->doctrine
                ->getManagerForClass($className)
                ->persist($setting);
        }

        if (isset($this->cache[$name])) {
            unset($this->cache[$name]);
        }

        $setting->setValue($value);
    }

    public function flush()
    {
        $this->doctrine->getManagerForClass($this->configEntityName)->flush();
    }

    public function isFullyConfigured()
    {
        foreach ($this->settings as $name) {
            try {
                $this->craueConfig->get($name);
            } catch (\RuntimeException $e) {
                return false;
            }
        }

        return true;
    }

    public function asEntity()
    {
        $settings = new Settings();

        foreach ($this->settings as $name) {
            try {
                $value = $this->craueConfig->get($name);
                $settings->$name = $value;
            } catch (\RuntimeException $e) {}
        }

        return $settings;
    }

    public function getYear($an){
        $year = $this->doctrine->getRepository(Year::class)->findOneBy(['name' => $an]);
        return $year;
    }

    public function getStructureYear($an, $ministry=null, $commune=null){
        $year = $this->getYear($an);
        $commune=$commune ? $commune : $this->session->get('_commune');
        $ministere=$ministry ? $ministry : $this->session->get('_ministry');
        $stucture=null;
        if($commune && $year){
            $commune = $this->doctrine->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
            $stucture=$this->doctrine->getRepository(StructureYear::class)->findOneBy(['commune' => $commune, 'year'=> $year]);
        }
        if($ministere && $year){
            $ministere = $this->doctrine->getRepository(Ministry::class)->findOneBy(['id' => $ministere->getId()]);
            $stucture=$this->doctrine->getRepository(StructureYear::class)->findOneBy(['ministry' => $ministere, 'year'=> $year]);
        }
        return $stucture;
    }

    public function autorisationSaisie($an){ 
        $year = $this->getYear($an);
        $ministere=$this->session->get('_ministry');
        if(!$year){
            return false;
        }
        else if($year->getRapport()){
            return false;
        }
        else{
            if($this->security->isGranted('ROLE_MANAGER') || $this->security->isGranted('ROLE_INSTAD')){
                return true;
            }else{
                $start = $year->getDebut();
                $end=null;
                $etat=null;
                $structure=$this->getStructureYear($an);
                if($this->security->isGranted('ROLE_FOCAL') && $structure && $structure->getDpp()){
                    return false;
                }
                if($this->security->isGranted('ROLE_DPP')){
                    $end=$year->getFindpp(); //toDateString
                    $etat=$structure ? $structure->getDpp() : false; 
                }else if($this->security->isGranted('ROLE_FOCAL')){
                    $end=$year->getFinfocal();
                    $etat=$structure ? $structure->getFocal() : false; 
                }else{
                    return false;
                } 
                $now = new \DateTime("today");
                $etat=$now<=$end && $now>=$start && (!$etat); 
                if($ministere) {
                    $ministryYear = $this->doctrine->getRepository(Ministry::class)->findOneBy(['id' => $ministere->getId()]);
                    return $etat && in_array($an,$ministryYear->getYears());
                }
                return $etat; 
            }
        }
    }

    public function autorisationSaisiePTA($an){
        $year = $this->getYear($an);
        $ministere=$this->session->get('_ministry');
        if(!$year){
            return false;
        }
        else if($year->getRapport()){
            return false;
        }
        else{
            if($this->security->isGranted('ROLE_MANAGER') || $this->security->isGranted('ROLE_INSTAD')){
                return true;
            }else{
                $start = $year->getDebut();
                $end=null;
                $etat=null;
                $structure=$this->getStructureYear($an);
                if($this->security->isGranted('ROLE_FOCAL') && $structure && $structure->getDpp2()){
                    return false;
                }
                if($this->security->isGranted('ROLE_DPP')){
                    $end=$year->getFindpp();
                    $etat=$structure ? $structure->getDpp2() : false; 
                }else if($this->security->isGranted('ROLE_FOCAL')){
                    $end=$year->getFinfocal();
                    $etat=$structure ? $structure->getFocal2() : false; 
                }else{
                    return false;
                } 
                $now = new \DateTime("today");
                $etat=$now<=$end && $now>=$start && (!$etat); 
                if($ministere) {
                    $ministryYear = $this->doctrine->getRepository(Ministry::class)->findOneBy(['id' => $ministere->getId()]);
                    return $etat && in_array($an,$ministryYear->getYears());
                }
                return $etat; 
            }
        }
    }

    public function autorisationSaisieCommune($an){
        //if($an==2019) return true;
        $now = new \DateTime("today");
        $year = $this->getYear($an); 
        if(!$year || $this->security->isGranted('ROLE_INSTAD')){
            return false;
        }else if($year->getRapport()){
            return false;
        }else{
            if($this->security->isGranted('ROLE_MANAGER') || $this->security->isGranted('ROLE_INSTAD')){
                return true;
            }else{
                $start = $year->getDebut2();
                $end=null;
                $etat=null;
                $etat2=true;
                $structure=$this->getStructureYear($an);
                if($this->security->isGranted('ROLE_FOCAL') && $structure && $structure->getDpp2()){
                    return false;
                }
                if($this->security->isGranted('ROLE_DPP')){
                    
                    $endpf=$year->getFinfocal2();
                    $end=$year->getFindpp2();
                    // Vérifier si le point focal a toujours la main
                    $etat2=($now>$endpf || ($structure && $structure->getFocal2()));
                    $etat=$structure ? $structure->getDpp2() : false; 
                }else if($this->security->isGranted('ROLE_FOCAL')){
                    $end=$year->getFinfocal2();
                    $etat=$structure ? $structure->getFocal2() : false; 
                }else{
                    return false;
                } 
                $etat=$now<=$end && $now>=$start && (!$etat); 
                return $etat && $etat2; 
            }
        }
    }

    public function accessIndicator(?Indicator $indicator){
        if($this->security->isGranted('ROLE_MANAGER') || $this->security->isGranted('ROLE_ADMIN')){
            return true;
        }

        if($this->security->isGranted('ROLE_FOCAL') || $this->security->isGranted('ROLE_DPP')){
            $ministere=$this->session->get('_ministry');
            return $ministere && $indicator->getMinistere() && ($ministere->getId() == $indicator->getMinistere()->getId());
        }

        if($this->security->isGranted('ROLE_INSTAD')){
            return $indicator->getInstad();
        }
        return false;
    }

    public function valeurMsg($desagregation,$modalite){
        //Désagrégations
        $national=1;
        $departement = 2;
        $commune=3;
        $zone=4;
        $sexe=5;

        if(!$desagregation || ($desagregation->getid()!=$national && !$modalite)) return null;
        
        switch ($desagregation->getId()) {
            case $national:
                return "Valeurs nationales";
                break;
            case $departement:
                $departement = $this->doctrine->getRepository(Department::class)->findOneBy(['id' => $modalite]);
                return "Valeurs du département : ".$departement->getName();
                break;
            case $commune:
                $commune = $this->doctrine->getRepository(Commune::class)->findOneBy(['id' => $modalite]);
                return "Valeurs de la commune : ".$commune->getName();
                break;

            case $zone:
                $zone = $this->doctrine->getRepository(Zone::class)->findOneBy(['id' => $modalite]);
                return "Valeurs de la zone sanitaire : ".$zone->getName();
                break;

            case $sexe:
                $msg = $modalite==1 ? "Valeurs pour les hommes" : "Valeurs pour les femmes";
                return $msg;
                break;
            default:
                $modalite = $this->doctrine->getRepository(Modalite::class)->findOneBy(['id' => $modalite]);
                return $desagregation->getName()." : ".$modalite->getName();
        }
    }

    public function entityModalite($desagregation,$modalite){
        //Désagrégations
        $national=1;
        $departement = 2;
        $commune=3;
        $zone=4;
        $sexe=5;

        if(!$desagregation || !$modalite) return null;
        
        switch ($desagregation->getId()) {
            case $national:
                return null;
                break;
            case $departement:
                $departement = $this->doctrine->getRepository(Department::class)->findOneBy(['id' => $modalite]);
                return $departement;
                break;
            case $commune:
                $commune = $this->doctrine->getRepository(Commune::class)->findOneBy(['id' => $modalite]);
                return $commune;
                break;

            case $zone:
                $zone = $this->doctrine->getRepository(Zone::class)->findOneBy(['id' => $modalite]);
                return $zone;
                break;

            case $sexe:
                return $modalite;
                break;
            default:
                $modalite = $this->doctrine->getRepository(Modalite::class)->findOneBy(['id' => $modalite]);
                return $modalite;
        }
    }

    public function getCiblesSpacialisees($commune){
        $cibles = [];
        if($commune && $commune->getCibles()){
            foreach ($commune->getCibles() as $cible) {
                $cibles [] = $this->doctrine->getRepository(Target::class)->findOneBy(['id' => $cible]);
            }
        }
        return $cibles;
    }

    public function getIndicateursRenseignables($commune){
        $cibles = $this->getCiblesSpacialisees($commune);
        $indicateurs = [];
        foreach ($cibles as $cible) {
            foreach ($cible->getIndicators() as $indicateur) {
                if($indicateur->isCommunal()) $indicateurs [] = $indicateur;
            }
        }
        return $indicateurs;
    }

    public function getIndicateursRenseignablesByCible($cible){
        $indic = $cible->getIndicators();
        $indicateurs = [];
        foreach ($indic as $i) {
                if($i->isCommunal()) $indicateurs [] = $i;
        }
        return $indicateurs;
    }

    public function getRealisationsProjets($norme, $type){
        return $this->doctrine->getRepository(RealisationProjet::class)->findBy(['activitenorme' => $norme, 'type' => $type]);
    }
    public function compter($agenda,$type,$commune=false){
        $odd=1; $cible=2; $indicateur=3;
        if($type==$odd){
            $odds = $this->doctrine->getRepository(Goal::class)->findBy(['agenda' => $agenda]);
            return count($odds);
        }else if ($type==$cible){
            $targets = $this->doctrine->getRepository(TArget::class)->findByAgenda($agenda);
            return count($targets);
        }else if($type==$indicateur){
            $indicators = $commune ? $this->doctrine->getRepository(Indicator::class)->findBy(['communal' => true]) : $this->doctrine->getRepository(Indicator::class)->findByAgenda($agenda);
            return count($indicators);
        }
        return 0;
    }
    public function compterUser($role){
        if($role=='all'){
            $users = $this->doctrine->getRepository(User::class)->findAll();
            return count($users);
        }else{
            $queryBuilder = $this->doctrine->getManager()->createQueryBuilder();
            $queryBuilder->select('u')
                ->from(User::class, 'u')
                ->where('u.roles LIKE :roles')
                ->setParameter('roles', '%"'.$role.'"%');

            $users = $queryBuilder->getQuery()->getResult();
            return count($users);
        }
    }

    public function showRole(){
        if($this->security->isGranted('ROLE_ADMIN')){
            return 'Administrateur';
        }else if($this->security->isGranted('ROLE_MANAGER')){
            return 'Manager';
        }else if($this->security->isGranted('ROLE_INSTAD')){
            return 'INStaD';
        }else{
            return $this->security->getUser()->getOffice();
        }
        return '';
        /*if($this->security->isGranted('ROLE_DPP')){
            $structure = null;
            $ministere=$this->session->get('_ministry');
            $commune=$this->session->get('_commune');
            if($commune) {
                $structure = $this->doctrine->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
                return 'SE - '. $structure->getName();
            }else if($ministere) {
                $structure = $this->doctrine->getRepository(Ministry::class)->findOneBy(['id' => $ministere->getId()]);
                return 'DPAF - '. $structure->getCode();
            }
            return '';
        }if($this->security->isGranted('ROLE_FOCAL')){
            $structure = null;
            $ministere=$this->session->get('_ministry');
            $commune=$this->session->get('_commune');
            if($commune) {
                $structure = $this->doctrine->getRepository(Commune::class)->findOneBy(['id' => $commune->getId()]);
                return 'Point Focal - '. $structure->getName();
            }else if($ministere) {
                $structure = $this->doctrine->getRepository(Ministry::class)->findOneBy(['id' => $ministere->getId()]);
                return 'Point Focal - '. $structure->getName();
            }
        }*/
        
    }

    function getIndicatorValue($year, $indicator, $flag, $desagragation=1, $modalite=null) {
        $indice = $this->doctrine->getRepository(Indice::class)->getRecIndice($year, $indicator, $desagragation, $modalite);
        if ($indice) {
            switch ($flag) {
                case 'maxValue':
                    return $indice->getMaxValue();
                    break;
                case 'minValue':
                    return $indice->getMinValue();
                    break;
                case 'refYear':
                    return $indice->getRefYear();
                    break;
                case 'refValue':
                    return $indice->getRefValue();
                    break;
                case 'recYear':
                    return $indice->getYear();
                    break;
                case 'recValue':
                    return $indice->getValue();
                    break;
                default:
                    return null;
            }
        }
    }

    function getValeurIndicateurRenseignable($year, $indicator, $commune){
        $indice = $this->doctrine->getRepository(IndiceCommune::class)->findOneBy(['year' => $year, 'indicator' => $indicator,'commune' => $commune ]);
        return $indice ? round($indice->getValue(), 3) : null;
    }
    

    function getValeurIndicateur($year, $indicator, $desagregation=1, $modalite=null){
        $desagregation = $this->doctrine->getRepository(Desagregation::class)->findOneBy(['id' => $desagregation]);
        $modalite = $this->entityModalite($desagregation, $modalite);
        $indice = $this->doctrine->getRepository(Indice::class)->findOneBy(['year' => $year, 'indicator' => $indicator,'desagregation' => $desagregation,'modalite' => $modalite ]);
        return $indice ? round($indice->getValue(), 3) : null;
    }

    function getIndicatorIndice($year, $indicator, $flag='rec', $desagregation=1, $modalite=null) {
        $result = null;
        $indice = $this->doctrine->getRepository(Indice::class)
                       ->getRecIndice($year, $indicator, $desagregation, $modalite);
        if ($indice and ($indice->getMaxValue() - $indice->getMinValue())) {
            if ($flag == 'rec' ) {
                $result = ($indice->getValue() - $indice->getMinValue()) / ($indice->getMaxValue() - $indice->getMinValue()) * 100;
            } else if($indicator->getRefValue()) {
                $result = ($indicator->getRefValue() - $indice->getMinValue()) / ($indice->getMaxValue() - $indice->getMinValue()) * 100;
            }
        }else if($flag != 'rec' and $indicator->getRefValue() and ($indicator->getMaxValue() - $indicator->getMinValue())){
            $result = ($indicator->getRefValue() - $indicator->getMinValue()) / ($indicator->getMaxValue() - $indicator->getMinValue()) * 100;
        }

        /*if($flag == 'rec' ){
            if ($indice and ($indice->getMaxValue() - $indice->getMinValue())) {
                $result = ($indice->getValue() - $indice->getMinValue()) / ($indice->getMaxValue() - $indice->getMinValue()) * 100;
            }
        }
        else{
            if ($indicator->getMaxValue() - $indicator->getMinValue()) {
                $result = ($indicator->getRefValue() - $indicator->getMinValue()) / ($indicator->getMaxValue() - $indicator->getMinValue()) * 100;
            }
        } */   

        return $result > 100 ? 100 : $result;
    }

    function getIndicatorTrend($year, $indicator, $desagregation=1, $modalite=null) {
        $indice = $this->doctrine->getRepository(Indice::class)->getRecIndice($year, $indicator, $desagregation, $modalite);
        $result = null;
        if ($indice and $indice->getRefYear() and ($indice->getYear() != $indice->getRefYear())) {
            $actualTrend = pow($indice->getValue() / $indice->getRefValue(), 1 / ($indice->getYear() - $indice->getRefYear()))-1;
            $normalTrend = pow($indice->getMaxValue() / $indice->getRefValue(), 1 / (2030 - $indice->getRefYear()))-1;
            $val = $normalTrend ? ($actualTrend / $normalTrend * 100) : 9999;
            
            //Normalisation de la valeur
            if ($val <= -200) { $result = 0; }
            if (($val > -200) && ($val <=0)) { $result = ((($val + 200) / (0 + 200)) * (1 - 0)) + 0; }
            if (($val > 0) && ($val <=50)) { $result = ((($val - 0) / (50 - 0)) * (2 - 1)) + 1; }
            if (($val > 50) && ($val <=100)) { $result = ((($val - 50) / (100 - 50)) * (3 - 2)) + 2; }
            if (($val > 100) && ($val <=200)) { $result = ((($val - 100) / (200 - 100)) * (4 - 3)) + 3; }
            if ($val > 200) { $result = 4; }

            //$result = $val;
        }

        return $result;
    }

    function getTargetTrend($year, $target, $desagregation=1, $modalite=null) {
        $nbre = 0;
        $total = 0;
        foreach ($target->getIndicators() as $indicator) {
            $indicatorTrend = $this->getIndicatorTrend($year, $indicator, $desagregation, $modalite);
            if ($indicatorTrend) {
                $total += $indicatorTrend;
                $nbre += 1;
            }
        }
        return $nbre ? ($total / $nbre) : null;
    }

    function getGoalTrend($year, $goal, $desagregation=1, $modalite=null) {
        $nbre = 0;
        $total = 0;
        foreach ($goal->getTargets() as $target) {
            $targetTrend = $this->getTargetTrend($year, $target, $desagregation, $modalite);
            if ($targetTrend) {
                $total += $targetTrend;
                $nbre += 1;
            }
        }
        return $nbre ? ($total / $nbre) : null;
    }

    function getGlobalTrend($year, $goals, $desagregation, $modalite) {
        $nbre = 0;
        $total = 0;
        foreach ($goals as $goal) {
            $goalTrend = $this->getGoalTrend($year, $goal, $desagregation, $modalite);
            if ($goalTrend) {
                $total += $goalTrend;
                $nbre += 1;
            }
        }
        return $nbre ? ($total / $nbre) : null;
    }

    function getTargetIndice($year, $target, $flag='rec', $desagregation=1, $modalite=null, $moyenne=null) {
        $moyenne = $moyenne ? $moyenne : $this->getYear($year)->getMoyenne();
        if($moyenne=='GEOMETRIQUE'){
            $nbre = 0;
            $total = 1;
            foreach ($target->getIndicators() as $indicator) {
                $indicatorIndice = $this->getIndicatorIndice($year, $indicator, $flag, $desagregation, $modalite);
                if ($indicatorIndice) {
                    $total *= $indicatorIndice;
                    $nbre += 1;
                }
            }
            if($nbre){
                $retour = pow($total, 1 / $nbre);
                return $retour > 100 ? 100 : $retour;
            }else{
                return null;
            }
        }else if($moyenne=='ARITHMETIQUE'){  
            $nbre = 0;
            $total = 0;
            foreach ($target->getIndicators() as $indicator) {
                $indicatorIndice = $this->getIndicatorIndice($year, $indicator, $flag, $desagregation, $modalite);
                if ($indicatorIndice) {
                    $total += $indicatorIndice;
                    $nbre += 1;
                }
            }
            if($nbre){
                $retour = $total/ $nbre;
                return $retour > 100 ? 100 : $retour;
            }else{
                return null;
            }
        }else{
            return null;
        }  
    }

    function getGoalIndice($year, $goal, $flag='rec', $desagregation=1, $modalite=null, $moyenne=null) {
       $moyenne = $moyenne ? $moyenne : $this->getYear($year)->getMoyenne();
       if($moyenne=='GEOMETRIQUE'){
            $nbre = 0;
            $total = 1;
            foreach ($goal->getTargets() as $target) {
                $targetIndice = $this->getTargetIndice($year, $target, $flag, $desagregation, $modalite);
                if ($targetIndice) {
                    $total *= $targetIndice;
                    $nbre += 1;
                }
            }
            if($nbre){
                $retour = pow($total, 1 / $nbre);
                return $retour > 100 ? 100 : $retour;
            }else{
                return null;
            }
       }else if($moyenne=='ARITHMETIQUE'){
            $nbre = 0;
            $total = 0;
            foreach ($goal->getTargets() as $target) {
                $targetIndice = $this->getTargetIndice($year, $target, $flag, $desagregation, $modalite);
                if ($targetIndice) {
                    $total += $targetIndice;
                    $nbre += 1;
                }
            }
            if($nbre){
                $retour = $total / $nbre;
                return $retour > 100 ? 100 : $retour;
            }else{
                return null;
            }
       }else{
            return null;
       }
    }

    function getGlobalIndice($year, $goals, $flag='rec', $desagregation, $modalite, $moyenne=null) {
        set_time_limit(0);
        $moyenne = $moyenne ? $moyenne : $this->getYear($year)->getMoyenne();
        if($moyenne=='GEOMETRIQUE'){
            $nbre = 0;
            $total = 1;
            foreach ($goals as $goal) {
                $goalIndice = $this->getGoalIndice($year, $goal, $flag, $desagregation, $modalite);
                if ($goalIndice) {
                    $total *= $goalIndice;
                    $nbre += 1;
                }
            }
            if($nbre){
                $retour = pow($total, 1 / $nbre);
                return $retour > 100 ? 100 : $retour;
            }else{
                return null;
            }
        }else if($moyenne=='ARITHMETIQUE'){
            $nbre = 0;
            $total = 0;
            foreach ($goals as $goal) {
                $goalIndice = $this->getGoalIndice($year, $goal, $flag, $desagregation, $modalite);
                if ($goalIndice) {
                    $total += $goalIndice;
                    $nbre += 1;
                }
            }
            if($nbre){
                $retour =$total/$nbre;
                return $retour > 100 ? 100 : $retour;
            }else{
                return null;
            }
        }else{
            return null;
        }
    }

    function normalizerStatut($value) {
        //Normalisation de la valeur    
        if (($value > 0) && ($value <=40)) return ((($value - 0) / (40 - 0)) * (1 - 0)) + 0;
        if (($value > 40) && ($value <=60)) return ((($value - 40) / (60 - 40)) * (1.5 - 1)) + 1;
        if (($value > 60) && ($value <=80)) return ((($value - 60) / (80 - 60)) * (2 - 1.5)) + 1.5;
        if (($value > 80) && ($value <=100)) return ((($value - 80) / (100 - 60)) * (3 - 2)) + 2;
    }

    function adjustStatut($statuts) {
        $result = null;
        if (count($statuts) == 1) {
            $result = $statuts[0];
        } elseif (count($statuts) > 1) {
            sort($statuts);
            $result = ($statuts[0] + $statuts[1]) / 2;
            //Condition particulière
            if (($statuts[0] > 2) and ($statuts[1] > 2)) {
                $result = 3;
            } elseif (($statuts[0] > 1.5) or ($statuts[1] > 1.5)) {
                $result = 2;
            }
    
            if (($statuts[0] <= 1) and ($statuts[1] <= 1)) {
                $result = 1;
            } elseif (($statuts[0] <= 1.5) or ($statuts[1] <= 1.5)) {
                $result = 1.5;
            }
        }

        return $result;
    }

    function getIndicatorStatut($year, $indicator, $desagregation=1, $modalite=null) {
        $result = null;
        if ($this->getIndicatorIndice($year, $indicator, 'rec', $desagregation, $modalite)) {
            $result = $this->normalizerStatut($this->getIndicatorIndice($year, $indicator, 'rec', $desagregation, $modalite));
        }
        return $result;
    }

    function getTargetStatut($year, $target, $desagregation=1, $modalite=null) {
        $result = null;
        $arrayStatut = [];
        foreach ($target->getIndicators() as $indicator) {
            $indicatorStatut = $this->getIndicatorStatut($year, $indicator, $desagregation, $modalite);
            if ($indicatorStatut) {
                $arrayStatut[] = $indicatorStatut;
            }
        }
        $result = $this->adjustStatut($arrayStatut);

        return $result;
    }

    function getGoalStatut($year, $goal, $desagregation=1, $modalite=null) {
        $result = null;
        $arrayStatut = [];
        foreach ($goal->getTargets() as $target) {
            $targetStatut = $this->getTargetStatut($year, $target, $desagregation, $modalite);
            if ($targetStatut) {
                $arrayStatut[] = $targetStatut;
            }
        }
        $result = $this->adjustStatut($arrayStatut);

        return $result;
    }

    function getGlobalStatut($year, $goals, $desagregation, $modalite) {
        $result = null;
        $arrayStatut = [];
        foreach ($goals as $goal) {
            $goalStatut = $this->getGoalStatut($year, $goal, $desagregation, $modalite);
            if ($goalStatut) {
                $arrayStatut[] = $goalStatut;
            }
        }
        $result = $this->adjustStatut($arrayStatut);

        return $result;
    }

    public function performancePilier($pilier, $year, $desagregation=1, $modalite=null){
        $desagregation = $this->doctrine->getRepository(Desagregation::class)->findOneBy(['id' => $desagregation]);
        $modalite=$this->entityModalite($desagregation,$modalite);
        $performance = $this->doctrine->getRepository(Performance::class)->findOneBy(['year' => $year,'mainstay' => $pilier, 'desagregation' => $desagregation, 'modalite'=> $modalite]);
        return $performance;
    }

    public function performanceOdd($odd, $year, $desagregation=1, $modalite=null){
        $desagregation = $this->doctrine->getRepository(Desagregation::class)->findOneBy(['id' => $desagregation]);
        $modalite=$this->entityModalite($desagregation,$modalite);
        $performance = $this->doctrine->getRepository(Performance::class)->findOneBy(['year' => $year,'goal' => $odd, 'desagregation' => $desagregation, 'modalite'=> $modalite]);
        return $performance;
    }

    function numberToRoman($num)  
    { 
        // Be sure to convert the given parameter into an integer
        $n = intval($num);
        $result = ''; 
    
        // Declare a lookup array that we will use to traverse the number: 
        $lookup = array(
            'M' => 1000, 'CM' => 900, 'D' => 500, 'CD' => 400, 
            'C' => 100, 'XC' => 90, 'L' => 50, 'XL' => 40, 
            'X' => 10, 'IX' => 9, 'V' => 5, 'IV' => 4, 'I' => 1
        ); 
    
        foreach ($lookup as $roman => $value)  
        {
            // Look for number of matches
            $matches = intval($n / $value); 
    
            // Concatenate characters
            $result .= str_repeat($roman, $matches); 
    
            // Substract that from the number 
            $n = $n % $value; 
        } 

        return $result; 
    } 

    function getCommunes(){
        return $this->doctrine->getRepository(Commune::class)->findAll();
    }

    function getCommunesByDepartement($departement){
        return $this->doctrine->getRepository(Commune::class)->findBy(['department' => $departement]);
    }

    function getCible($id){
        return $this->doctrine->getRepository(Target::class)->find($id);
    }

    function getLastYear(){
        $ministeres = $this->doctrine->getRepository(Ministry::class)->findAll();
        $years = [];
        foreach ($ministeres as $ministere) {
            $years[] = $ministere->getYears() ? max($ministere->getYears()) : null;
        }
        return max($years);
    }
    function getLastYearRapport(){
        $queryBuilder = $this->doctrine->getManager()->createQueryBuilder();
            $queryBuilder->select('max(y.name)')
                ->from(Year::class, 'y')
                ->where('y.rapport = 1');

        return $queryBuilder->getQuery()->getSingleScalarResult() ? $queryBuilder->getQuery()->getSingleScalarResult() : null;
    }

    function getLastYearRapportPTA(){
        $queryBuilder = $this->doctrine->getManager()->createQueryBuilder();
            $queryBuilder->select('max(y.name)')
                ->from(Year::class, 'y')
                ->where('y.publierpta = 1');

        return $queryBuilder->getQuery()->getSingleScalarResult() ? $queryBuilder->getQuery()->getSingleScalarResult() : null;
    }

    function getIndicateurStatutColor($year, $indicator, $desagregation, $modalite){

        $statut = $this->doctrine->getRepository(Statut::class)
                                   ->findOneByValue($this->getIndicatorStatut($year, $indicator, $desagregation, $modalite));
        return $statut ? $statut->getFlag() : '#cccccc';
    }

    function getTargetStatutColor($year, $target, $desagregation, $modalite){
        $statut = $this->doctrine->getRepository(Statut::class)
                                   ->findOneByValue($this->getTargetStatut($year, $target, $desagregation, $modalite));
        return $statut ? $statut->getFlag() : '#cccccc';
    }
    function getGoalStatutColor($year, $goal, $desagregation, $modalite){

        $statut = $this->doctrine->getRepository(Statut::class)
                                   ->findOneByValue($this->getGoalStatut($year, $goal, $desagregation, $modalite));
        return $statut ? $statut->getFlag() : '#cccccc';
    }

    function getIndicateur($id){
        $indicateur = $this->doctrine->getRepository(Indicator::class)
                    ->findOneBy(['id' => $id]);
        return $indicateur;
    }

    function getODD($id){
        $indicateur = $this->doctrine->getRepository(Goal::class)
                    ->findOneBy(['id' => $id]);
        return $indicateur;
    }

    function getIndiceCommune($commune, $indicator, $year){
        
        $suivi = $this->doctrine->getRepository(IndiceCommune::class)
                    ->findOneBy(['indicator' => $indicator, 'year' => $year, 'commune' => $commune]);
        return $suivi ? round($suivi->getValue(),3) : null;
    }

    function getSousIndicateurCommune($commune, $indicator, $year){
        $suivi = $this->doctrine->getRepository(ValeurSousIndicateur::class)
                    ->findOneBy(['sousindicateur' => $indicator, 'year' => $year, 'commune' => $commune]);
        return $suivi ? $suivi->getValeur() : null;
    }

    function getIndiceCommuneObservation($commune, $indicator, $year){
        $suivi = $this->doctrine->getRepository(IndiceCommune::class)
                    ->findOneBy(['indicator' => $indicator, 'year' => $year, 'commune' => $commune]);
        return $suivi ? $suivi->getObservation() : null;
    }

    function round($val){
        return round($val,2);
    }

    function getCibleCommuneByOdd($commune, $odd){
        $cibles = [];
        foreach ($commune->getCibles() as $c) {
            $target = $this->doctrine->getRepository(Target::class)->findOneBy(['id' => $c, 'goal' => $odd]);
            if($target) $cibles [] = $target;
        }
        return $cibles;
    }

    function getIndicateurDesagregeCommuneByCible($cible){
        $indicateurs = [];
        $commune=3;
        foreach ($cible->getIndicators() as $indicateur) {
            if($indicateur->isCommunal()==false && $indicateur->getDesagregations() && in_array($commune,$indicateur->getDesagregations())) $indicateurs [] = $indicateur;
        }
        return $indicateurs;
    }

    function getIndicateursInstad(){
        $indicators = $this->doctrine->getRepository(Indicator::class)->findBy(['instad' => true]);
        return $indicators;
    }

    function getActicitiesByMinistry($year, $ministry){
        $activities = $this->doctrine->getRepository(ActivityPTA::class)
                ->findByMinistry($year->getName(), $ministry);
        return $activities;
    }

    function getNoIndicateursByMinistry($year, $ministry){
        $retour=[];
        if(!$ministry) return $retour;
        foreach ($ministry->getIndicators() as $indicateur) {
            $val = $this->getValeurIndicateur($year, $indicateur, 1, null);
            if(! is_numeric($val) ){
                $retour[]=$indicateur;
            }
        }
        return $retour;
    }

    function getRIndicateursByMinistry($year, $ministry){
        $n=0;
        foreach ($ministry->getIndicators() as $indicateur) {
            $val = $this->getValeurIndicateur($year, $indicateur, 1, null);
            if(is_numeric($val)) $n++;
        }
        return $n;
    }

    function getNoIndicateursINSTAD($year){
        $retour=[];
        $indicators = $this->doctrine->getRepository(Indicator::class)->findBy(['instad' => true]);
        foreach ($indicators as $indicateur) {
            $val = $this->getValeurIndicateur($year, $indicateur, 1, null);
            if(! is_numeric($val)){
                $retour[]=$indicateur;
            }
        } 
        return $retour;
    }

    function getRIndicateursINSTAD($year){
        $n=0;
        $indicators = $this->doctrine->getRepository(Indicator::class)->findBy(['instad' => true]);
        foreach ($indicators as $indicateur) {
            $val = $this->getValeurIndicateur($year, $indicateur, 1, null);
            if(is_numeric($val)) {
                $n++;
            }
        }
        return $n;
    }


    function getNoIndicateursByCommune($year, $commune){
        $retour=[];
        $indicators = $this->doctrine->getRepository(Indicator::class)->findBy(['communal' => true]);
        foreach ($indicators as $indicateur) {
            $val = $this->getValeurIndicateurRenseignable($year, $indicateur, $commune);
            if(!is_numeric($val)){
                $retour[]=$indicateur;
            }
        }
        return $retour;
    }

    function getRIndicateursByCommune($year, $commune){
        $n=0;
        $indicators = $this->doctrine->getRepository(Indicator::class)->findBy(['communal' => true]);
        foreach ($indicators as $indicateur) {
            $val = $this->getValeurIndicateurRenseignable($year, $indicateur,$commune); 
            if(is_numeric($val)) $n++;
        }
        return $n;
    }


    public function ministriesByODD($odd, $year){
            $queryBuilder = $this->doctrine->getManager()->createQueryBuilder();
            return $queryBuilder->select('m.id')
                ->from(ActivityPTA::class, 'a')   
                ->join('a.ministry', 'm')
                ->andWhere('a.year = :year')
                ->andWhere('a.goal = :odd')
                ->setParameter('year', $year)
                ->setParameter('odd', $odd)
                ->distinct()
                ->orderBy('m.id', 'ASC')
                ->getQuery()
                ->getResult();
    }

    public function montantByMinistryByODD($odd, $year){
        $queryBuilder = $this->doctrine->getManager()->createQueryBuilder();
        return $queryBuilder->select('g.id, sum(a.expectedCost) as programme, sum(a.realizedCost) as realise')
            ->from(ActivityPTA::class, 'a')
            ->join('a.goal', 'g')   
            ->andWhere('a.year = :year')
            ->andWhere('a.goal = :odd')
            ->setParameter('year', $year)
            ->setParameter('odd', $odd)
            ->orderBy('g.id', 'ASC')
            ->getQuery()
            ->getResult();
    }

    public function montantByPilier($pilier, $year){
        $queryBuilder = $this->doctrine->getManager()->createQueryBuilder();
        return $queryBuilder->select('p.id, sum(a.expectedCost) as programme, sum(a.realizedCost) as realise')
            ->from(ActivityPTA::class, 'a')
            ->join('a.goal', 'g') 
            ->join('g.mainstay', 'p')  
            ->andWhere('a.year = :year')
            ->andWhere('g.mainstay = :pilier')
            ->setParameter('year', $year)
            ->setParameter('pilier', $pilier)
            ->orderBy('g.id', 'ASC')
            ->getQuery()
            ->getOneOrNullResult();
    }

    function encryptid($id){
        if ($id){
            $key="@SIGOdd@2030$";
            $ivlen = openssl_cipher_iv_length($cipher="AES-128-CBC");
            $iv = openssl_random_pseudo_bytes($ivlen);
            $ciphertext_raw = openssl_encrypt($id, $cipher, $key, $options=OPENSSL_RAW_DATA, $iv);
            $hmac = hash_hmac('sha256', $ciphertext_raw, $key, $as_binary=true);
            $ciphertext = base64_encode( $iv.$hmac.$ciphertext_raw );
            return $ciphertext;
        }else{
            return null;
        } 
    }

    function decryptid($id){
        if ($id){
            $key="@SIGOdd@2030$";
            $c = base64_decode($id);
            $ivlen = openssl_cipher_iv_length($cipher="AES-128-CBC");
            $iv = substr($c, 0, $ivlen);
            $hmac = substr($c, $ivlen, $sha2len=32);
            $ciphertext_raw = substr($c, $ivlen+$sha2len);
            $original_plaintext = openssl_decrypt($ciphertext_raw, $cipher, $key, $options=OPENSSL_RAW_DATA, $iv);
            $calcmac = hash_hmac('sha256', $ciphertext_raw, $key, $as_binary=true);
            if (hash_equals($hmac, $calcmac))// timing attack safe comparison
            {
                return $original_plaintext;
            }else{
                return null;
            }
        }else{
            return null;
        } 
    }
    
}