<?php

namespace Modules\DemandeAchat\Repository;

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

class CreationDemandeAchat extends AbsRepository
{
    /**
     * createPurchaseRequestHeading
     *
     * @param  array<mixed> $post
     * @return bool
     */
    public function createPurchaseRequestHeading(array $post): bool
    {
        $query = "INSERT INTO DA_ENTET (
            NO_DA, 
            NO_DEM, 
            SECTIMP, 
            CODECOMP, 
            SSCPTEANA, 
            FOU, 
            DEVISE, 
            DTE_EMISS, 
            CTR_LIV, 
            COND_PAIE, 
            MODE_PAIE, 
            MODE_LIV, 
            CHIF_EXEC, 
            TYP_DOC, 
            CODE_STE, 
            ACD, 
            MOD_TRS, 
            NOCDISDA, 
            ADRFOU, 
            VILFOU, 
            PAYSFOU, 
            CTCT_FOU, 
            TEL_CTCT, 
            FAX_CTCT, 
            POR_CTCT, 
            EMA_CTCT, 
            COD_LNG, 
            CODGES, 
            LIBGES, 
            CPFOU, 
            VILLEFOU, 
            ID_CG, 
            NMCTC1L, 
            FTCTC1L, 
            TLCTC1L, 
            FXCTC1L, 
            PRCTC1L, 
            MLCTC1L, 
            ADRLIV, 
            VILLIV, 
            PAYSLIV, 
            CPLIV, 
            VILLELIV, 
            DAENT_CENTRE_EMETTEUR, 
            UO_DEM
        ) 
        SELECT 
            :NO_DA, 
            :NO_DEM, 
            :SECTIMP, 
            :CODECOMP, 
            :SSCPTEANA, 
            :FOU, 
            :DEVISE, 
            :DTE_EMISS, 
            :CTR_LIV, 
            :COND_PAIE,
            :MODE_PAIE,
            :MODE_LIV,
            :CHIF_EXEC, 
            :TYP_DOC, 
            :CODE_STE, 
            :ACD, 
            :MOD_TRS, 
            :NOCDISDA, 
            ISNULL(FOURNISS.ADR, ''), 
            ISNULL(FOURNISS.VILLE, ''), 
            ISNULL(FOURNISS.CODE_PAYS, ''), 
            ISNULL(CONTFOUR.NOM, ''), 
            ISNULL(CONTFOUR.TEL , ''), 
            ISNULL(CONTFOUR.FAX , ''), 
            ISNULL(CONTFOUR.TELPORT , ''), 
            ISNULL(CONTFOUR.EMAIL , ''), 
            :COD_LNG, 
            :CODE_AFF, 
            :LIB_GES, 
            ISNULL(FOURNISS.CPOSTAL, ''), 
            ISNULL(FOURNISS.VILLE1, ''), 
            :CODE_GES, 
            :CTC_NOM, 
            :CTC_FCT, 
            :CTC_TEL, 
            :CTC_FAX, 
            :CTC_GSM, 
            :CTC_MAIL, 
            CENTRE.ADRESSE, 
            CENTRE.VILLE, 
            CENTRE.PAYS, 
            CENTRE.CPOSTAL, 
            CENTRE.VILLE1, 
            :DAENT_CENTRE_EMETTEUR, 
            :CODE_UO
        FROM CENTRE with(nolock)
			LEFT JOIN CTC_CTR with(nolock) ON CTC_CTR.COD_CTR = CENTRE.CODE_CTR AND CTC_CTR.CTC_IND = 0
			LEFT JOIN ACCCADRE with(nolock) ON ACCCADRE.CODE_ACD = :CODE_ACD
			LEFT JOIN MARCHES with(nolock) ON MARCHES.CODE = ACCCADRE.MARCHE
			LEFT JOIN FOURNISS with(nolock) ON FOURNISS.CODE = :CODE_FOU
			LEFT JOIN CONTFOUR with(nolock) ON CONTFOUR.CODE_FOU = FOURNISS.CODE AND IND_FOU  = 0
			LEFT JOIN HASTUTIL with(nolock) ON HASTUTIL.CODE = :USER
			LEFT JOIN CODGEST with(nolock) ON CODGEST.CODE_AFF = :CODGES2
        WHERE CENTRE.CODE_CTR = :CTR_LIV
        ";

        $params = [
            ':NO_DA' => $post[':NO_DA'],
        ];

        $requete = $this->pdo->prepare($query);
        return $requete->execute($params);
    }

    public function getMaxPoPurchaseRequest(string $prId): string
    {
        return $this->fetchAssocOne(
            'SELECT (ISNULL(MAX(NO_PO), 0) + 1) FREE_NO_PO
            FROM DA_LIGNE with(nolock)
            WHERE NO_DA = :NO_DA',
            [
                ':NO_DA' => $prId
            ]
        )['FREE_NO_PO'];
    }

    /**
     * blockEnteteDA
     * @param string $id
     * @return array<string>
     */
    public function blockEnteteDA(string $id): ?array
    {
        $req = "SELECT DA_ENTET.CODE_STE, DA_ENTET.CHIF_EXEC, DA_ENTET.TYP_DOC, DA_ENTET.TOTAL_DA, DA_ENTET.TOTAL_DA_TTC, DA_ENTET.DEVISE, SOCIETE.RAIS_SOC libelleSociete, TYPDOC.LIBELLE libelleTypeDoc
        FROM DA_ENTET with (nolock)
        LEFT JOIN SOCIETE with (nolock) on SOCIETE.CODE_STE=DA_ENTET.CODE_STE
        LEFT JOIN TYPDOC with (nolock) on TYPDOC.TYPE=DA_ENTET.TYP_DOC
        WHERE DA_ENTET.NO_DA = :numDA";

        $arrayReq = [
            ':numDA' => $id,
        ];

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

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

    /* permet de voir si un Section d'imputation a le code gestion activé */
    /**
     * codeGestionActif
     * @param string $codeSECTIMPU
     * @param string $codeSte
     * @return array<string>
     */
    public function codeGestionActif(string $codeSECTIMPU, string $codeSte): ?array
    {
        $req =
            "SELECT SICODG 
        FROM SECTIMPU with(nolock)
        WHERE CODE_SI = :codeSi 
        AND CODE_STE=:codeSte";

        $arrayReq = [':codeSte' => $codeSte, ':codeSi' => $codeSECTIMPU];

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

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

    /* retourne la liste des conditions de paiement avec traduction */
    /**
     * getAllCondPaieTrad
     * @return array<mixed>|bool
     */
    public function getAllCondPaieTrad()
    {
        $req =
            "SELECT CONDPAIE.CODE_CDP, isnull(TRAD_CDP.LIBELLE,CONDPAIE.LIBELLE) CODE_CDP_TRAD 
        FROM CONDPAIE with(nolock)
        LEFT JOIN TRAD_CDP with(nolock) on TRAD_CDP.CODE_CDP=CONDPAIE.CODE_CDP AND TRAD_CDP.COD_LNG='" . $_SESSION['Session_Langue'] . "'";

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

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

    /* retourne la liste des modes de paiement avec traduction */
    /**
     * getAllModePaieTrad
     * @return bool|array<mixed>
     */
    public function getAllModePaieTrad()
    {
        $req =
            "SELECT MODEPAIE.CODE_MDP, isnull(TRAD_MDP.LIBELLE,MODEPAIE.LIBELLE) CODE_MDP_TRAD 
        FROM MODEPAIE with(nolock) 
        LEFT JOIN TRAD_MDP with(nolock) on TRAD_MDP.CODE_MDP=MODEPAIE.CODE_MDP AND TRAD_MDP.COD_LNG='" . $_SESSION['Session_Langue'] . "'";

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

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

    /* retourne la liste des modes de livraison avec traduction */
    /**
     * getAllModeLivTrad
     * @return bool|array<mixed>
     */
    public function getAllModeLivTrad()
    {
        $req =
            "SELECT MODELIVR.CODE_MDL, isnull(TRAD_MDL.LIBELLE,MODELIVR.LIBELLE) CODE_MDL_TRAD 
        FROM MODELIVR with(nolock) 
        LEFT JOIN TRAD_MDL with(nolock) on TRAD_MDL.CODE_MDL=MODELIVR.CODE_MDL AND TRAD_MDL.COD_LNG='" . $_SESSION['Session_Langue'] . "'";

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

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

    /**
     * getControleQualite
     * @param string $codeArt
     * @param string $codeFou
     * @param string $codeGrpFour
     * @return array<string>
     */
    public function getControleQualite(string $codeArt, string $codeFou = '', string $codeGrpFour = ''): ?array
    {
        $req = "SELECT isnull(ARTFOURN.CTRL_QUA, isnull(ARTGF.CTRL_QUA, isnull(ARTICLES.CTRL_QUA, ''))) CTRL_QUA
        FROM ARTICLES with(nolock)
        LEFT JOIN ARTFOURN with(nolock) on ARTFOURN.CODE_ART=ARTICLES.CODE_ART AND ARTFOURN.FOU = :codeFou
        LEFT JOIN ARTGF with(nolock) on ARTGF.CODE_ART=ARTICLES.CODE_ART AND ARTGF.CODE_GF = :codeGrpFour
        WHERE ARTICLES.CODE_ART = :codeArt
        ";

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

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

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

    /**
     * getReferenceFournisseur
     * @param string $codeArt
     * @param string $codeFou
     * @param string $codeGrpFour
     * @return array<string>
     */
    public function getReferenceFournisseur(string $codeArt, string $codeFou = '', string $codeGrpFour = ''): ?array
    {
        $req = "SELECT isnull(ARTFOURN.REFARTFOU, isnull(ARTGF.REFARTFOU, '')) REF_FOUR
        FROM ARTICLES with(nolock)
        LEFT JOIN ARTFOURN with(nolock) on ARTFOURN.CODE_ART=ARTICLES.CODE_ART AND ARTFOURN.FOU = :codeFou
        LEFT JOIN ARTGF with(nolock) on ARTGF.CODE_ART=ARTICLES.CODE_ART AND ARTGF.CODE_GF = :codeGrpFour
        WHERE ARTICLES.CODE_ART = :codeArt
        ";

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

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

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

    /**
     * getQteCondFou
     * @param string $codeArt
     * @param string $codeFou
     * @param string $codeGrpFour
     * @return array<string>
     */
    public function getQteCondFou(string $codeArt, string $codeFou = '', string $codeGrpFour = ''): ?array
    {
        $req = "SELECT isnull(ARTFOURN.QTE_COND,isnull(ARTGF.QTE_COND, 0)) QTE_COND
        FROM ARTICLES with(nolock)
        LEFT JOIN ARTFOURN with(nolock) on ARTFOURN.CODE_ART=ARTICLES.CODE_ART AND ARTFOURN.FOU = :codeFou
        LEFT JOIN ARTGF with(nolock) on ARTGF.CODE_ART=ARTICLES.CODE_ART AND ARTGF.CODE_GF = :codeGrpFour
        WHERE ARTICLES.CODE_ART = :codeArt
        ";

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

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

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

    /**
     * getSiteStock
     * @param array<string> $post
     * @return array<string>
     */
    public function getSiteStock(array $post): ?array
    {
        $req = "SELECT SITE
        FROM SSTOCKLO with (nolock)
        INNER JOIN SARTICLE with (nolock) on SARTICLE.CODE_ART=SSTOCKLO.CODE_ART
        WHERE SSTOCKLO.CODE_ART = :codeArt 
        AND SSTOCKLO.CODE_STE = :codeSte
        AND SSTOCKLO.SITE = :site
        AND (SSTOCKLO.DTE_FERM >= :date_emiss_0 or SSTOCKLO.DTE_FERM = '00000000') 
        AND (SARTICLE.DTE_FERM >= :date_emiss_1 or SARTICLE.DTE_FERM = '00000000')
        ";

        Date::dateToSql($post['DTE_EMISS']);
        $arrayReq = [
            ':codeArt' => $post['CODE_ART'],
            ':codeSte' => $post['CODE_STE'],
            ':site' => $post['SITE'],
            ':date_emiss_0' => $post['DTE_EMISS'],
            ':date_emiss_1' => $post['DTE_EMISS'],
        ];

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

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