<?php

namespace Modules\Additional\Specifique\Client83\Modules\Liste\Service;

use Helpers\Outil\Sql;
use Modules\Ajax\Repository\UO;

class SpecifiqueSI
{
    /**
     * @param \PDO $pdo
     * @param array<mixed> $array
     * @return string
     */
    public static function clauseSI(\PDO $pdo, $array) : string
    {
        $filtreUO = $array['filtreUO']??'';

        if (!$filtreUO) {
            $filtreUO = $_SESSION['Session_UO'];
        }
        if ($filtreUO) {
            $liste_si_str = self::listeSI($pdo, $filtreUO, $array['codeSte']);
        } else {
            $liste_si_str = "'#####'";
        }

        $req = ' and SECTIMPU.CODE_SI in (' .$liste_si_str. ')';

        return $req;
    }

    /**
     * @param \pdo $pdo
     * @param string|null $filtreUO
     * @param string|null $codeSte
     * @return string
     */
    private static function listeSI(\pdo $pdo, $filtreUO = '', $codeSte = ''):string
    {
        $isSISecto  = Sql::paravrac($pdo, 'PARAMSTE', 'SISECTOR') == 'O' ? true : false;

        $testSte = ($isSISecto) ? " and cs.COD_ENR like left(ha.CODE_STE + '       ', 7)+'%' " : '';
        $testSte2 = ($isSISecto) ? ' and pd.ID1 = ha.CODE_STE ' : '';

        $uo = new UO($pdo);

        $liste_uo = [];
        $isUOSecto  = Sql::paravrac($pdo, 'PARAMSTE', 'UOSECTO') == 'O' ? true : false;
        $uo->trouveFilleUO($isUOSecto, $codeSte, $filtreUO, $liste_uo);

        $liste_uo = "'".implode("','", $liste_uo)."'";
        
        $req = "SELECT 
                    distinct SUBSTRING(cs.COD_ENR, 8, 27) as CODE_SI
				from HASTUTIL ha with(nolock), CS_DON cs with(nolock)
				where
				ha.CODE = '{$_SESSION['Session_Login']}'
				and cs.NUM_FIX = (SELECT cast(LIB01DICO as integer) as LIB01DICO from DILISPEC with(nolock) where TYPEDICO = 'PARAMFFF' and CODEDICO = 'NCS_SECTIMPU_CODE_UO')
				and cs.COD_TAB = 'SECTIMPU' {$testSte}
				and cs.VALEUR in ('" . $liste_uo . "')
				union
				SELECT pd.ID2 as CODE_SI
				from HASTUTIL ha with (nolock), PARAMDICO pd with(nolock)
				where ha.CODE = '{$_SESSION['Session_Login']}' and pd.TYPEDICO = 'FFF_SI_UO' {$testSte2} and pd.ID3 in ('" . $liste_uo . "') and LIBELLE = 'O'";

        $requete = $pdo->prepare($req);
        $requete->execute();
        $result = $requete->fetchAll($pdo::FETCH_ASSOC);

        if ($result) {
            $result = "'".implode("'", array_column($result, 'CODE_SI', 'CODE_SI'))."'";

            return $result;
        } else {
            return "'#####'";
        }
    }
}
