<?php
namespace Modules\Additional\Specifique\Client131\Modules\Suivi\Repository;

use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Outil\Date;

class SuiviSpecifiqueRepository extends AbsRepository
{

    /**
     * @var array<mixed> $paramCS
     */
    private array $paramCS;

    public function __construct(\PDO $pdo)
    {
        parent::__construct($pdo);

        $CSprojetManager = trim(Sql::dilispec($this->pdo, 'PARNCS0131', 'NCS_SECTIMPU_PROJET_MANAGER'));
        $CSHeadOperation = trim(Sql::dilispec($this->pdo, 'PARNCS0131', 'NCS_SECTIMPU_HEAD_OPERATION'));
        $CSresponsableFamille = trim(Sql::dilispec($this->pdo, 'PARNCS0131', 'NCS_DIRECTIO_LOB_MANAGER'));

        $this->paramCS = [
            'CSprojetManager'       => $CSprojetManager?: '1',
            'CSHeadOperation'       => $CSHeadOperation?:'4',
            'CSresponsableFamille'  => $CSresponsableFamille?:'2',
        ];
    }

    /**
     * @param string $noCde
     * @param string $user
     * @return array<mixed>
     */
    public function getListeLigneCde(string $noCde, string $user, string $CS)
    {
        $req = "
            with mainRequest as (
                select CA_LIGNE.*, CA_ENTET.DEVISE from CA_LIGNE with (nolock)
                left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
                left join SECTIMPU with (nolock) on CA_LIGNE.SECTIMP = SECTIMPU.CODE_SI AND CA_LIGNE.CODE_STE = SECTIMPU.CODE_STE
                left join CS_DON projetManager with (nolock) 
                        on projetManager.COD_TAB = 'SECTIMPU' 
                        and projetManager.NUM_FIX = {$this->paramCS['CSprojetManager']}
                        and projetManager.COD_ENR = CONCAT(CA_LIGNE.CODE_STE + replicate(' ', 7 - len(CA_LIGNE.CODE_STE)),CA_LIGNE.SECTIMP)
                left join CS_DON HeadOperation with (nolock) 
                        on HeadOperation.COD_TAB = 'SECTIMPU' 
                        and HeadOperation.NUM_FIX = {$this->paramCS['CSHeadOperation']}
                        and HeadOperation.COD_ENR = CONCAT(CA_LIGNE.CODE_STE + replicate(' ', 7 - len(CA_LIGNE.CODE_STE)),CA_LIGNE.SECTIMP)
                left join CS_DON responsableFamille with (nolock) 
                        on responsableFamille.COD_TAB = 'DIRECTIO' 
                        and responsableFamille.NUM_FIX = {$this->paramCS['CSresponsableFamille']} 
                        and responsableFamille.COD_ENR = CONCAT(CA_LIGNE.CODE_STE + replicate(' ', 7 - len(CA_LIGNE.CODE_STE)),SECTIMPU.CODE_DIR)
                where (ISNULL($CS.VALEUR, '') = :user1)
                and CA_LIGNE.NO_CDE = :noCde
            ), requestCalcul as (
                SELECT SUM(mainRequest.QTE_COMM*mainRequest.PU*(1 - mainRequest.REMISE/100)) totalHT, SUM((mainRequest.QTE_COMM*mainRequest.PU*(1 - mainRequest.REMISE/100)*(1+mainRequest.TVA/100) )) totalTTC FROM mainRequest
            )
            select mainRequest.NO_PO,mainRequest.DEVISE,  requestCalcul.* from mainRequest, requestCalcul";

        $arrayReq = [
            ':noCde' => $noCde,
            ':user1' => $user,
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $noDA
     * @param string $user
     * @return array<mixed>
     */
    public function getListeLigneDA(string $noDA, string $user, string $CS)
    {
        $req = "
            with mainRequest as (
                select DA_LIGNE.*, DA_ENTET.DEVISE from DA_LIGNE with (nolock)
                left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                left join SECTIMPU with (nolock) on DA_LIGNE.SECTIMP = SECTIMPU.CODE_SI AND DA_LIGNE.CODE_STE = SECTIMPU.CODE_STE
                left join CS_DON projetManager with (nolock) 
                        on projetManager.COD_TAB = 'SECTIMPU' 
                        and projetManager.NUM_FIX = {$this->paramCS['CSprojetManager']}  
                        and projetManager.COD_ENR = CONCAT(DA_LIGNE.CODE_STE + replicate(' ', 7 - len(DA_LIGNE.CODE_STE)),DA_LIGNE.SECTIMP)
                left join CS_DON HeadOperation with (nolock) 
                        on HeadOperation.COD_TAB = 'SECTIMPU' 
                        and HeadOperation.NUM_FIX = {$this->paramCS['CSHeadOperation']}  
                        and HeadOperation.COD_ENR = CONCAT(DA_LIGNE.CODE_STE + replicate(' ', 7 - len(DA_LIGNE.CODE_STE)),DA_LIGNE.SECTIMP)
                left join CS_DON responsableFamille with (nolock) 
                        on responsableFamille.COD_TAB = 'DIRECTIO' 
                        and responsableFamille.NUM_FIX = {$this->paramCS['CSresponsableFamille']} 
                        and responsableFamille.COD_ENR = CONCAT(DA_LIGNE.CODE_STE + replicate(' ', 7 - len(DA_LIGNE.CODE_STE)),SECTIMPU.CODE_DIR)
                where (ISNULL($CS.VALEUR, '') = :user1)
                and DA_LIGNE.NO_DA = :noDA
            ), requestCalcul as (
                SELECT SUM(mainRequest.QTE_DEM*mainRequest.PU*(1 - mainRequest.TX_REMISE/100)) totalHT, SUM((mainRequest.QTE_DEM*mainRequest.PU*(1 - mainRequest.TX_REMISE/100)*(1+mainRequest.TVA/100) )) totalTTC FROM mainRequest
            )
            select mainRequest.NO_PO,mainRequest.DEVISE,  requestCalcul.* from mainRequest, requestCalcul
        ";

        $arrayReq = [
            ':noDA'  => $noDA,
            ':user1' => $user,
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $noFac
     * @param string $user
     * @return array<mixed>
     */
    public function getListeLigneFac(string $noFac, string $user, string $CS)
    {
        $req = "
            with mainRequest as (
                select FAC_LIG.*, FAC_ENT.DEVISE from FAC_LIG with (nolock)
                left join FAC_ENT with (nolock) on FAC_ENT.NUM_FACT = FAC_LIG.NUM_FACT
                left join SECTIMPU with (nolock) on FAC_LIG.CODE_SI = SECTIMPU.CODE_SI AND FAC_LIG.CODE_STE = SECTIMPU.CODE_STE
                left join CS_DON projetManager with (nolock) 
                        on projetManager.COD_TAB = 'SECTIMPU' 
                        and projetManager.NUM_FIX = {$this->paramCS['CSprojetManager']} 
                        and projetManager.COD_ENR = CONCAT(FAC_LIG.CODE_STE + replicate(' ', 7 - len(FAC_LIG.CODE_STE)),FAC_LIG.CODE_SI)
                left join CS_DON HeadOperation with (nolock) 
                        on HeadOperation.COD_TAB = 'SECTIMPU' 
                        and HeadOperation.NUM_FIX = {$this->paramCS['CSHeadOperation']} 
                        and HeadOperation.COD_ENR = CONCAT(FAC_LIG.CODE_STE + replicate(' ', 7 - len(FAC_LIG.CODE_STE)),FAC_LIG.CODE_SI)
                left join CS_DON responsableFamille with (nolock) 
                        on responsableFamille.COD_TAB = 'DIRECTIO' 
                        and responsableFamille.NUM_FIX = {$this->paramCS['CSresponsableFamille']} 
                        and responsableFamille.COD_ENR = CONCAT(FAC_LIG.CODE_STE + replicate(' ', 7 - len(FAC_LIG.CODE_STE)),SECTIMPU.CODE_DIR)
                where (ISNULL($CS.VALEUR, '') = :user1)
                and FAC_LIG.NUM_FACT = :noFac
            ), requestCalcul as (
                SELECT SUM(mainRequest.QUANTITE*mainRequest.PU_REMISE) totalHT, SUM((mainRequest.QUANTITE*mainRequest.PU_REMISE*(1+mainRequest.TAUX_TVA/100) )) totalTTC FROM mainRequest
            )
            select mainRequest.NUM_LIG NO_PO,mainRequest.DEVISE,  requestCalcul.* from mainRequest, requestCalcul
        ";

        $arrayReq = [
            ':noFac' => $noFac,
            ':user1' => $user,
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
}
