<?php

namespace Modules\Additional\Specifique\Client0\Modules\Facture\Repository;

//use Helpers\Filter\FilterTable;

class CreationFacture
{
    
    private $pdo;
    
    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function getCsCodeJournal($params)
    {
        $req="SELECT LIB01DICO csNum,
            case when exists
            (select VALEUR from CS_DON with(nolock) where COD_TAB='TYPDOC'
            and COD_ENR=(select TYP_DOC from CA_ENTET with(nolock) where NO_CDE=:num_cde_0)
            and NUM_FIX= (select LIB01DICO  from DILISPEC with(nolock) where TYPEDICO = 'PARAMEL2D' and CODEDICO='NCS_TYPDOC_CODE_JOURNAL'))
                then
                    (select VALEUR from CS_DON with(nolock) where COD_TAB='TYPDOC'
                    and COD_ENR=(select TYP_DOC from CA_ENTET with(nolock) where NO_CDE=:num_cde_1)
                    and NUM_FIX= (select LIB01DICO  from DILISPEC with(nolock) where TYPEDICO = 'PARAMEL2D' and CODEDICO='NCS_TYPDOC_CODE_JOURNAL'))
                else
                    (select VALEUR from CS_DON with(nolock) where COD_TAB='CPTE_COLLECTIF'
                    and COD_ENR=
                        (select VALEUR from CS_DON with(nolock) where COD_TAB='FOURNISS'
                        and COD_ENR= :fournisseur
                        and NUM_FIX= (select LIB01DICO  from DILISPEC with(nolock) where TYPEDICO = 'PARAMEL2D' and CODEDICO='NCS_FAC_ENT_CPTE_COLL_FOURN'))
                    and NUM_FIX= (select LIB01DICO  from DILISPEC with(nolock) where TYPEDICO = 'PARAMEL2D' and CODEDICO='NCS_CPTE_COLL_FOURN_CODE_JOURNAL'))
                end
                as VALEUR
            from DILISPEC with(nolock) where TYPEDICO = 'PARAMEL2D' and CODEDICO='NCS_FAC_ENT_CODE_JOURNAL'";
        
        $arrayReq = [
            ':num_cde_0' => $params['numCde'],
            ':num_cde_1' => $params['numCde'],
            ':fournisseur' => $params['fournisseur'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        return $param->fetch();
    }
    
    public function getCdcSousFamille($params)
    {
        
        $req="SELECT CDC
            FROM CDCSTESF with(nolock)
            WHERE SFAMILL = :sousFamille AND CODE_STE = :codeSte";
        
        $arrayReq = [
            ':sousFamille' => $params['CODE_SFAM'],
            ':codeSte' => $params['societe'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        return $param->fetch()['CDC'];
    }
    
    public function getScaSousFamille($params)
    {
        
        $req="SELECT SCA
            FROM SCASTESF with(nolock) 
            WHERE SFAMILL = :sousFamille AND CODE_STE = :codeSte";
        
        $arrayReq = [
            ':sousFamille' => $params['CODE_SFAM'],
            ':codeSte' => $params['societe'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        return $param->fetch()['SCA'];
    }
        
    public function getCSValueSpecific($params)
    {
        
        $req="SELECT LIB01DICO csNum, 
            (SELECT VALEUR 
                FROM CS_DON with(nolock)
                WHERE COD_TAB='".$params['COD_TAB']."' 
                AND COD_ENR='".$params['CODE_ENR']."' 
                AND NUM_FIX= 
                    (SELECT LIB01DICO  
                    FROM DILISPEC with(nolock) 
                    WHERE TYPEDICO = '".$params['typedico']."' 
                    AND CODEDICO='".$params['codedicoOrigine']."')
            ) valeur
            FROM DILISPEC with(nolock) 
            WHERE TYPEDICO = :typedico AND CODEDICO=:codedicoDestination";
        
        $arrayReq = [
            ':typedico' => $params['typedico'],
            ':codedicoDestination' => $params['codedicoDestination'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        return $param->fetch();
    }
    
    public function getCompteFromTypeTva($params)
    {
        
        $req="SELECT LIB01DICO csNum, 
            (SELECT LIB01DICO  
                FROM DILISPEC with(nolock) 
                WHERE TYPEDICO = '".$params['typedicoTva']."' 
                AND CODEDICO='".$params['typeTva']."'
            ) valeur
            FROM DILISPEC with(nolock) 
            WHERE TYPEDICO = :typedico AND CODEDICO=:codedicoDestination";
        
        $arrayReq = [
            ':typedico' => $params['typedico'],
            ':codedicoDestination' => $params['codedicoDestination'],
        ];

        $param = $this->pdo->prepare($req);
        $param->execute($arrayReq);
        
        return $param->fetch();
    }
}
