<?php

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

use Entity\Model\Sectimpu;
use Helpers\Outil\Sql;
use Modules\GlobalApplication\Repository\UO;

class SpecifiqueSCA
{
    /**
     * @param \PDO $pdo
     * @param array<mixed> $param
     * @return string
     */
    public static function clauseSCA(\PDO $pdo, $param) : string
    {
        if (!in_array($param['contexte'], ['lignePanier', 'ligneDA', 'ligneCDE', 'ligneFAC'])) {
            return '';
        }

        $req = '';
        
        $infoSI = (new Sectimpu($pdo))->find($param['SI'], ['CODE_DIR']);
        $cdcSecto = str_pad($param['codeSte'], 7, ' ', STR_PAD_RIGHT).$param['CDC'];
        
        $csCodeCompAcrs = sql::dilispec($pdo, 'PARNCS0166', 'NCS_CODECOMP_ACRS');
        
        $csRubriqueAcrs = sql::dilispec($pdo, 'PARNCS0166', 'NCS_RUBRIQUE_ACRS');
        $csRubriqueNature = sql::dilispec($pdo, 'PARNCS0166', 'NCS_RUBRIQUE_NATURE');
        $csRubriqueMetier = sql::dilispec($pdo, 'PARNCS0166', 'NCS_RUBRIQUE_METIER');

        $csCoupleRubrique = sql::dilispec($pdo, 'PARNCS0166', 'NCS_COUPLES_ACRS_ACRS_RUBRIQUE');
        $csCoupleSection = sql::dilispec($pdo, 'PARNCS0166', 'NCS_COUPLES_ACRS_ACRS_SECTION');

        $req = "
            and exists (

                SELECT 
                distinct metier.VALEUR SCA
                FROM CS_DON metier
                --DIRECTION
                inner join CS_DON DIRECTION on DIRECTION.COD_TAB='RUBRIQUE' and DIRECTION.NUM_FIX = $csRubriqueNature and DIRECTION.COD_ENR = metier.COD_ENR and DIRECTION.VALEUR = '".$infoSI['CODE_DIR']."' -- DIRECTION 
                --CDC
                inner join CS_DON ACRS on ACRS.COD_TAB='RUBRIQUE' and ACRS.NUM_FIX = $csRubriqueAcrs and ACRS.COD_ENR = DIRECTION.COD_ENR 
                inner join CS_DON COUPLES_RUB on COUPLES_RUB.COD_TAB = 'COUPLES_ACRS' and COUPLES_RUB.NUM_FIX = $csCoupleRubrique and COUPLES_RUB.VALEUR = ACRS.VALEUR
                inner join CS_DON COUPLE_ACRS on COUPLE_ACRS.COD_TAB = 'COUPLES_ACRS' and COUPLE_ACRS.NUM_FIX = $csCoupleSection and COUPLES_RUB.COD_ENR = COUPLE_ACRS.COD_ENR
                inner join CS_DON ACRS_CDC on ACRS_CDC.COD_TAB = 'CODECOMP' and ACRS_CDC.NUM_FIX = $csCodeCompAcrs and COUPLE_ACRS.VALEUR = ACRS_CDC.VALEUR and  ACRS_CDC.COD_ENR = '".$cdcSecto."' -- CODE CDC
                inner join TS_DON on CONCAT(LEFT(CONCAT(COD_STE,'       '),7),CODEDICO) = COUPLE_ACRS.COD_ENR and ((TS_DON.DATE_MIN = '00000000' or TS_DON.DATE_MIN < '".date('Ymd')."') and (TS_DON.DATE_MAX = '00000000' or TS_DON.DATE_MAX > '".date('Ymd')."'))
                where metier.COD_TAB = 'RUBRIQUE' and metier.NUM_FIX= $csRubriqueMetier and metier.VALEUR = SSCPTANA.CODE_SCA

            )
        ";
        
        return $req;
    }
}
