<?php

namespace Modules\Consultation\Repository;

use Helpers\QueryBuilder\AbsRepository;
use Modules\GlobalApplication\Repository\Societe;

class Consultation extends AbsRepository
{

    /**
     * clauseSociete
     *
     * @var string
     */
    private $clauseSociete;

    public function __construct(\PDO $pdo, bool $indicateur = false, string $post_societe = '')
    {
        parent::__construct($pdo);

        // TODO : constructor should never send sql queries, move all of this somewhere else
        if (!$indicateur) {
            $this->clauseSociete = '';
            return;
        }

        $societes = (new Societe($this->pdo))->listRestrictedSocieteForUser();
        if ($societes === null) {
            $this->clauseSociete = '';
            return;
        }

        if ($post_societe) {
            $listeSociete = "'" . $post_societe . "'";
        } else {
            $listeSociete = implode("','", array_column($societes, 'CODE_STE'));
        }

        $this->clauseSociete .= " and ((TYPE_SG = 'S' and AO_ENTET.CODE_STE in ('$listeSociete')) or (TYPE_SG = 'G' and AO_ENTET.CODE_STE in (select DISTINCT GROUPE from SOCIETE with (nolock) where SOCIETE.CODE_STE in ('$listeSociete')))) ";
    }

    /**
     * duplicateFrsAO
     * @param string $idAOOrigine
     * @param string $idNewAo
     * @return void
     */
    public function duplicateFrsAO(string $idAOOrigine, string $idNewAo): void
    {
        $req = "Insert Into AO_FOUR (NO_AO ,CODE_FOU ,NOM_CTCT ,TEL_CTCT ,FAX_CTCT ,POR_CTCT ,EMAILCONT ,ADR ,VILLE ,PAYS, RET_CV ,TYPE_FG ,CPOSTAL ,VILLE1)
                SELECT :idNewAo ,CODE_FOU ,NOM_CTCT ,TEL_CTCT ,FAX_CTCT ,POR_CTCT ,EMAILCONT ,ADR ,VILLE ,PAYS, RET_CV ,TYPE_FG ,CPOSTAL ,VILLE1 FROM AO_FOUR with (nolock) WHERE NO_AO = :idAOOrigine";

        $arrayReq = [':idNewAo' => $idNewAo, ':idAOOrigine' => $idAOOrigine];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }

    /**
     * Liste des utilisateurs pouvait recevoir un AO
     * Ils doivent pouvoir création ou modifier un AO (HA_AO_ADM créer/Modif tout AO ou HA_AO_CMV créer AO)
     * Il doit aussi être sur le même périmetre de l'AO (société ou groupe société)
     *
     * @param string $typeSoc
     * @param string $socAO
     * @return array<mixed>
     */
    public function getListeAcheteurForTransfere(string $typeSoc, string $socAO): array
    {
        if ($typeSoc == 'S') {
            $filtreSoc = "(CODE_STE = :codeSte or CODE in (select COD_USR from SOC_USR with (nolock) where COD_STE = :codeSte2)) ";

            $arrayReq = [':codeSte' => $socAO, ':codeSte2' => $socAO];
        } else {
            $filtreSoc = "(CODE_STE in (select CODE_STE from SOCIETE with (nolock) where GROUPE = :codeGrpSte) or CODE in (select COD_USR from SOC_USR with (nolock) where COD_STE in (select CODE_STE from SOCIETE with (nolock) where GROUPE = :codeGrpSte2)))";
            $arrayReq  = [':codeGrpSte' => $socAO, ':codeGrpSte2' => $socAO];
        }


        $req = "select HASTUTIL.CODE, HASTUTIL.NOM from HASTUTIL with (nolock) 
                where $filtreSoc and (DTE_OUV <= '" . date('Ymd') . "' and (DTE_FERM >= '" . date('Ymd') . "' or DTE_FERM = '00000000'))
                and (select case when exists
                        (
                            select COD_DR from DROITUSR with (nolock) where COD_USR=HASTUTIL.CODE and (COD_DR='HA_AO_ADM' or COD_DR = 'HA_AO_CMV')
                            union
                            select dg.COD_GRP from DROITGRP dg with (nolock)
                            inner join GRPUSR g with (nolock) on g.COD_GRP=dg.COD_GRP and g.COD_USR=HASTUTIL.CODE
                            where dg.COD_DR='HA_AO_ADM'or dg.COD_DR = 'HA_AO_CMV'
                        ) then 1 else 0 end as DROIT
                    ) = 1 order by HASTUTIL.NOM";

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

    /**
     * @return array<mixed>
     */
    public function getBlocNote(string $idAO): array
    {
        $req = "select * from AOBNOTE with(nolock)
                left join MEMOS with(nolock) on MEMOS.NODEMEMO = AOBNOTE.AO_NOTE
                where NO_AO = :noAO";


        $arrayReq = [':noAO' => $idAO];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    public function insertBnote(string $idAO, string $idMemo): void
    {
        $req = "INSERT INTO AOBNOTE(NO_AO,AO_NOTE) VALUES ($idAO, $idMemo)";

        $requete = $this->pdo->prepare($req);
        $requete->execute();
    }

    /**
     * @return array<mixed>
     */
    public function getListeDoc(string $implodeProjet): array
    {
        $req = "
                select DOA.REF, DOC_ATT.* from DOA_RECORD_VAL drv with (nolock)
                left join DOA_DATA dd with (nolock) on dd.ID_OBJET = drv.ID_DOA and dd.ID_DONNEE = drv.ID_DONNEE and dd.ID_DATA = drv.ID_DATA
                left join DOA_STRUCT ds with (nolock) on ds.ID_OBJET = dd.ID_OBJET and ds.ID_DONNEE = dd.ID_DONNEE
                left join DOC_ATT with (nolock) on DOC_ATT.NO_DA = 'DOA' and COD_ENR =  RIGHT ('000000000' + LTRIM(RTRIM(ID_DOA)), 9)  and cast(VALEUR as varchar(100)) = cast(NO_ORDRE as varchar(100))
                left join DOA with (nolock) on DOA.ID = ID_DOA
                where ID_DOA in('$implodeProjet') and TYPE_DATA = 'F' and FIC_GENERE = 0x46 AND MULTIPLE = 0x46 and ISNUMERIC(VALEUR) = 1
                UNION 
                select DOA.REF, DOC_ATT.* from DOA with (nolock)
                left join DOC_ATT with (nolock) on DOC_ATT.NO_DA  = 'DOA' and COD_ENR = RIGHT ('000000000' + LTRIM(RTRIM(DOA.ID)), 9)
                where DOA.ID in('$implodeProjet') AND NO_ORDRE not in ( 
                		
                		select NO_ORDRE from DOA_RECORD_VAL drv with (nolock) 
                		left join DOA_DATA dd with (nolock) on dd.ID_OBJET = drv.ID_DOA and dd.ID_DONNEE = drv.ID_DONNEE and dd.ID_DATA = drv.ID_DATA 
                		left join DOA_STRUCT ds with (nolock) on ds.ID_OBJET = dd.ID_OBJET and ds.ID_DONNEE = dd.ID_DONNEE 
                		left join DOC_ATT with (nolock) on DOC_ATT.NO_DA = 'DOA' and COD_ENR = RIGHT ('000000000' + LTRIM(RTRIM(ID_DOA)), 9) and cast(VALEUR as varchar(100)) = cast(NO_ORDRE as varchar(100)) where ID_DOA in('$implodeProjet') and TYPE_DATA = 'F'
                
                )";

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

    /**
     * @return array<mixed>
     */
    public function getFrsOxalink(string $idAO, ?string $typeFG = null, ?string $codeFou = null): array
    {
        $req = "select * from (
                select AO_FOUR.*, FOURNISS.COD_LNG , GRPFOU.COD_LNG GRPFOU_COD_LNG, FOURNISS.GEREPORTAIL,
                (select count(*) ctcOxalinkActif from CONTFOUR with (nolock) left join OXALINK_USER with (nolock) on OXALINK_USER.USER_ID = CONTFOUR.OXALINK_ID where CODE_FOU = FOURNISS.CODE and STATUT = 'A') ctcOxalinkActif
                
                from AO_FOUR with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = AO_FOUR.CODE_FOU
                left join GRPFOU with (nolock) on GRPFOU.CODE = AO_FOUR.CODE_FOU
                where NO_AO = :noAO) frsConsult where 1=1";

        $arrayReq = [':noAO' => $idAO];

        if ($typeFG && $codeFou) {
            $req .= " and TYPE_FG = :typeFG and CODE_FOU = :codeFou";
            $arrayReq[':typeFG']  = $typeFG;
            $arrayReq[':codeFou'] = $codeFou;
        } else {
            $req .= " and GEREPORTAIL = 0x54 and ctcOxalinkActif != 0";
        }

        if ($typeFG && $codeFou) {
            return $this->fetchAssocOne($req, $arrayReq);
        } else {
            return $this->fetchAssocAll($req, $arrayReq);
        }
    }

    /**
     * Summary of checkIfCtcHasOxalinkAccess
     * @param string $idAO
     * @param string $typeFG
     * @param string $codeFou
     * @param string $email
     * @return array<mixed>
     */
    public function checkIfCtcHasOxalinkAccess(string $idAO, string $typeFG, string $codeFou, string $email): array
    {
        $req = <<<SQL
                with listeContactConsultation  as (
                    select 
                        AO_CONTFOUR.NO_AO, AO_CONTFOUR.CODE_FOUR, AO_CONTFOUR.TYPE_FG, AO_CONTFOUR.NUM_ORDRE,
                        AO_CONTFOUR.NOM_CTCT, AO_CONTFOUR.TEL_CTCT, AO_CONTFOUR.FAX_CTCT, AO_CONTFOUR.POR_CTCT, AO_CONTFOUR.EMAILCONT
                    from AO_CONTFOUR with (nolock) where NO_AO = :noAO1
                    union 
                    select
                        AO_FOUR.NO_AO, AO_FOUR.CODE_FOU CODE_FOUR, AO_FOUR.TYPE_FG, 0 'NUM_ORDRE',
                        AO_FOUR.NOM_CTCT, AO_FOUR.TEL_CTCT, AO_FOUR.FAX_CTCT, AO_FOUR.POR_CTCT, AO_FOUR.EMAILCONT
                    from AO_FOUR with (nolock)  where NO_AO = :noAO2
                )
                select listeContactConsultation.*,FOURNISS.COD_LNG , GRPFOU.COD_LNG GRPFOU_COD_LNG,  FOURNISS.GEREPORTAIL from listeContactConsultation
                left join FOURNISS with (nolock) on FOURNISS.CODE = listeContactConsultation.CODE_FOUR
                left join GRPFOU with (nolock) on GRPFOU.CODE = listeContactConsultation.CODE_FOUR
                where TYPE_FG = :typeFG and CODE_FOUR = :codeFou and EMAILCONT = :email
        SQL;

        $arrayReq = [
            ':noAO1' => $idAO,
            ':noAO2' => $idAO,
            ':typeFG' => $typeFG,
            ':codeFou' => $codeFou,
            ':email' => $email,
        ];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * Summary of doesFournissHasAContactWithOxalinkAccess
     * @param string $fourniss
     * @return string
     */
    public function doesFournissHasAContactWithOxalinkAccess(string $fourniss)
    {
        $req = <<<SQL
            select count(*) TOTAL from CONTFOUR where CODE_FOU = :fourniss and OXALINK_ID != ''
        SQL;

        $arrayReq = [':fourniss' => $fourniss];

        return $this->fetchAssocOne($req, $arrayReq)['TOTAL'] ?? '0';
    }

    /**
     * Summary of getAllCtcWithCheckIfOxalinkAccess
     * @param string $idAO
     * @param string|null $codeFou
     * @return string[][]
     */
    public function getAllCtcWithCheckIfOxalinkAccess(string $idAO, ?string $codeFou = null, ?string $typeFG = null): array
    {
        $req = <<<SQL
                            SELECT  AO_CONTFOUR.NO_AO,
                                        AO_CONTFOUR.CODE_FOUR,
                                        AO_CONTFOUR.TYPE_FG,
                                        AO_CONTFOUR.NUM_ORDRE, 
                                        AO_CONTFOUR.NOM_CTCT,
                                        AO_CONTFOUR.TEL_CTCT,
                                        AO_CONTFOUR.FAX_CTCT,
                                        AO_CONTFOUR.POR_CTCT,
                                        AO_CONTFOUR.EMAILCONT,
                                        FOURNISS.COD_LNG, 
                                        GRPFOU.COD_LNG GRPFOU_COD_LNG, 
                                        FOURNISS.GEREPORTAIL,
                                        (select count(*) ctcOxalinkActif from CONTFOUR with (nolock) 
                                            left join OXALINK_USER with (nolock)    on OXALINK_USER.USER_ID = CONTFOUR.OXALINK_ID 
                                                                                    and OXALINK_USER.EMAIL = AO_CONTFOUR.EMAILCONT 
                                            where CODE_FOU = FOURNISS.CODE and STATUT = 'A'
                                        ) ctcOxalinkActif,
                                        (CASE WHEN EXISTS (
                                            SELECT 1 FROM CONTFOUR 
                                            WHERE CODE_FOU = AO_CONTFOUR.CODE_FOUR and OXALINK_ID != ''
                                        ) THEN 1 ELSE 0 END) as fourHasContactWithOxalinkAccess
                                from AO_CONTFOUR with (nolock)
                                left join FOURNISS with (nolock) on FOURNISS.CODE = AO_CONTFOUR.CODE_FOUR
                                left join GRPFOU with (nolock) on GRPFOU.CODE = AO_CONTFOUR.CODE_FOUR
                                where AO_CONTFOUR.NO_AO = :noAO
                                union all
                                select	AO_FOUR.NO_AO,
                                        AO_FOUR.CODE_FOU as CODE_FOUR,
                                        AO_FOUR.TYPE_FG,
                                        0 as NUM_ORDRE, 
                                        AO_FOUR.NOM_CTCT,
                                        AO_FOUR.TEL_CTCT,
                                        AO_FOUR.FAX_CTCT,
                                        AO_FOUR.POR_CTCT,
                                        AO_FOUR.EMAILCONT,
                                        FOURNISS.COD_LNG, 
                                        GRPFOU.COD_LNG GRPFOU_COD_LNG, 
                                        FOURNISS.GEREPORTAIL,
                                        (select count(*) ctcOxalinkActif from CONTFOUR with (nolock) 
                                            left join OXALINK_USER with (nolock)    on OXALINK_USER.USER_ID = CONTFOUR.OXALINK_ID 
                                                                                    and OXALINK_USER.EMAIL = AO_FOUR.EMAILCONT 
                                            where CODE_FOU = FOURNISS.CODE and STATUT = 'A'
                                        ) ctcOxalinkActif,
                                        CASE WHEN EXISTS (
                                            SELECT 1 FROM CONTFOUR 
                                            WHERE CODE_FOU = AO_FOUR.CODE_FOU and OXALINK_ID != ''
                                        ) THEN 1 ELSE 0 END as fourHasContactWithOxalinkAccess
                                from AO_FOUR
                                left join FOURNISS with (nolock) on FOURNISS.CODE = AO_FOUR.CODE_FOU
                                left join GRPFOU with (nolock) on GRPFOU.CODE = AO_FOUR.CODE_FOU
                                where AO_FOUR.NO_AO = :noAO2
                        SQL;

        $arrayReq = [];

        if ($codeFou) {
            $req .= <<<SQL
                                and AO_FOUR.CODE_FOU = :codeFou and AO_FOUR.TYPE_FG = :typeFG
                            SQL;

            $arrayReq = [
                ':codeFou' => $codeFou,
                ':typeFG' => $typeFG
            ];
        }

        $req .= <<<SQL
                            order by CODE_FOUR
                        SQL;

        $arrayReq = array_merge([
            ':noAO' => $idAO,
            ':noAO2' => $idAO
        ], $arrayReq);

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

    /**
     * @return array<mixed>
     */
    public function getIndicateurconsultPreparation(string $periode)
    {
        return $this->fetchAssocOne(
            "select 
                    COUNT(*) PREP 
                from AO_ENTET with(nolock)
                join PERIODES with(nolock) on PERIODES.CODE_PER = :periode
                where AO_ENTET.DTE_VALID_AO = '00000000' " . $this->clauseSociete . "
                and AO_ENTET.DTE_AO between PERIODES.DATE_DEB AND PERIODES.DATE_FIN",
            [':periode' => $periode]
        );
    }

    /**
     * @return array<mixed>
     */
    public function getIndicateurconsultEnCours(string $periode)
    {
        $req = "select 
                    COUNT(*) ENCO 
                from AO_ENTET with(nolock)
                left join PERIODES with(nolock) on PERIODES.CODE_PER = :periode
                where AO_ENTET.DTE_VALID_AO<>'00000000' and AO_ENTET.VALCHXFOU='00000000' " . $this->clauseSociete . "
                and AO_ENTET.DTE_AO between PERIODES.DATE_DEB AND PERIODES.DATE_FIN";

        $arrayReq = [
            ':periode' => $periode
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function getIndicateurfrsParConsult(string $periode)
    {
        $req = "select 
                    count(AO_ENTET.NO_AO) CONSULT, SUM(test.FOUR) COUNT_FOUR
                from AO_ENTET with(nolock)
                left join PERIODES with(nolock) on PERIODES.CODE_PER = :periode
                outer apply (
                    select 
                        COUNT(AO_FOUR.NO_AO) FOUR
                    from AO_FOUR with(nolock)
                    where AO_FOUR.NO_AO = AO_ENTET.NO_AO 
                ) test
                where ((AO_ENTET.DTE_VALID_AO<>'00000000' and AO_ENTET.VALCHXFOU='00000000') or (AO_ENTET.VALCHXFOU<>'00000000')) " . $this->clauseSociete . "
                and AO_ENTET.DTE_AO between PERIODES.DATE_DEB AND PERIODES.DATE_FIN";

        $arrayReq = [
            ':periode' => $periode
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function getIndicateurPourcReponse(string $periode)
    {
        $req = "select 
                    sum(repFrs.COUNT_FRS)*100/sum(frsInviter.COUNT_FRS) pour_rep
                from AO_ENTET with(nolock)
                left join PERIODES with(nolock) on PERIODES.CODE_PER =:periode
                outer apply (
                    select COUNT(DISTINCT CODE_FOU) COUNT_FRS from AO_DAREP with(nolock) where AO_ENTET.NO_AO = AO_DAREP.NO_AO
                ) repFrs
                outer apply (
                    select COUNT(DISTINCT CODE_FOU) COUNT_FRS from AO_FOUR with(nolock) where AO_ENTET.NO_AO = AO_FOUR.NO_AO
                ) frsInviter
                where AO_ENTET.VALCHXFOU<>'00000000' " . $this->clauseSociete . "
                and AO_ENTET.DTE_AO between PERIODES.DATE_DEB AND PERIODES.DATE_FIN";

        $arrayReq = [
            ':periode' => $periode
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch();
    }

    /**
     * @return array<mixed>
     */
    public function getIndicateurConsultParAcheteur(string $periode)
    {
        $req = "SELECT 
                   TOP 5 CONCAT(NOM, ' (', ACH, ')') libelle, COUNT(NO_AO) value, NOM
                FROM AO_ENTET with(nolock)
                left join PERIODES with(nolock) on PERIODES.CODE_PER = :periode
                LEFT JOIN HASTUTIL with(nolock) on HASTUTIL.CODE = AO_ENTET.ACH
                where AO_ENTET.DTE_VALID_AO<>'00000000' and AO_ENTET.VALCHXFOU='00000000' " . $this->clauseSociete . "
                and AO_ENTET.DTE_AO between PERIODES.DATE_DEB AND PERIODES.DATE_FIN
                GROUP BY ACH, NOM
                order by value desc";

        $arrayReq = [
            ':periode' => $periode
        ];

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


    /**
     * @return array<mixed>
     */
    public function getIndicateurConsultParFamille(string $periode)
    {
        $req = "SELECT 
                     TOP 5 case when AO_ENTET.CODE_FAM = '' then '' else CONCAT(FAMILLE.DESIGNAT, ' (', AO_ENTET.CODE_FAM, ')') end libelle, COUNT(NO_AO) value
                FROM AO_ENTET with(nolock)
                left join PERIODES with(nolock) on PERIODES.CODE_PER = :periode
                LEFT JOIN FAMILLE with (nolock) on FAMILLE.CODE_FAM = AO_ENTET.CODE_FAM
                where AO_ENTET.DTE_VALID_AO<>'00000000' and AO_ENTET.VALCHXFOU='00000000' " . $this->clauseSociete . "
                and AO_ENTET.DTE_AO between PERIODES.DATE_DEB AND PERIODES.DATE_FIN
                GROUP BY AO_ENTET.CODE_FAM, DESIGNAT
                order by value desc";

        $arrayReq = [
            ':periode' => $periode
        ];

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

    /**
     * getListeFrsAo
     *
     * @param  string $noAo
     * @return array<mixed>
     */
    public function getListeFrsAo($noAo)
    {
        $req = "SELECT AO_FOUR.*, FOURNISS.GEREPORTAIL FROM AO_FOUR with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = AO_FOUR.CODE_FOU
                WHERE NO_AO = :noAo";

        $arrayReq = [
            ':noAo' => $noAo
        ];

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

    /**
     * getOxalinkUser
     *
     * @param  string $codeFou
     * @param  string $mail
     * @return array<mixed>
     */
    public function getOxalinkUser($codeFou, $mail = '')
    {
        $req = "select * from CONTFOUR with (nolock) 
                left join OXALINK_USER with (nolock) on CONTFOUR.OXALINK_ID = OXALINK_USER.USER_ID
                where CODE_FOU = :codeFou  ";

        $arrayReq = [
            ':codeFou' => $codeFou,
        ];

        if ($mail) {
            $req .= " and OXALINK_USER.EMAIL = :mail ";
            $arrayReq[':mail'] = $mail;
        } else {
            $req .= " and OXALINK_USER.EMAIL != '' ";
        }

        $req .= " order by IND_FOU ";

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * getContactFrs
     *
     * @param  string $codeFou
     * @param  string $mail
     * @return array<mixed>
     */
    public function getContactFrs($codeFou, $mail = '')
    {
        $req = "select * from CONTFOUR with (nolock) 
                where CODE_FOU = :codeFou  ";

        $arrayReq = [
            ':codeFou' => $codeFou,
        ];

        if ($mail) {
            $req .= " and CONTFOUR.EMAIL = :mail ";
            $arrayReq[':mail'] = $mail;
        } else {
            $req .= " and CONTFOUR.EMAIL != '' ";
        }

        $req .= " order by IND_FOU ";



        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }

    /**
     * @param array<mixed> $article
     */
    public function getPrixRef(string $typeSG, string $societe, bool $modeRef, $article): string
    {
        $req = "select 
                    CA_LIGNE.PU * ( 1 - CA_LIGNE.REMISE / 100 ) PRIX_REF 
                from CA_LIGNE with (nolock)
                left join CA_ENTET with (nolock) on CA_LIGNE.NO_CDE = CA_ENTET.NO_CDE
                left join SOCIETE with (nolock) on SOCIETE.CODE_STE  = CA_LIGNE.CODE_STE
                WHERE CA_ENTET.EVE_IMP <> 0";

        if ($typeSG == 'S') {
            $req .= " AND SOCIETE.CODE_STE = :codeSte ";
        } else {
            $req .= " AND SOCIETE.GROUPE = :codeSte ";
        }

        $arrayReq[':codeSte'] = $societe;

        if ($modeRef) {
            $req .= " AND CA_LIGNE.COD_FAB = :codeFab and CA_LIGNE.REF_FAB = :refFab ";
            $arrayReq[':codeFab'] = $article['COD_FAB'];
            $arrayReq[':refFab']  = $article['REF_FAB'];
        } else {
            $req .= " AND CA_LIGNE.CODE_ART = :codeArt ";
            $arrayReq[':codeArt'] = $article['CODE_ART'];
        }

        $req .= " order by CA_ENTET.DTE_PERIO DESC ";

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        return $requete->fetch($this->pdo::FETCH_ASSOC)['PRIX_REF'] ?? 0;
    }
}
