<?php

namespace Modules\Facture\Repository;

use Helpers\Filter\FilterTable;
use Helpers\Outil\Roles;
use Helpers\QueryBuilder\AbsRepository;
use Modules\GlobalApplication\Repository\LicenceOxalys;

class FactureEventRepo extends AbsRepository
{
    /**
     * @param array<mixed> $post
     * @return string[][]
     */
    public function listeFactFour($post)
    {
        $arrayReq = [];
        $req =
            "SELECT 
                FAC_ENT.NUM_FACT,
                FAC_ENT.TYPE,
                FAC_ENT.FACT_FOU,
                FAC_ENT.CODE_STE,
                FAC_ENT.CODE_FOUR,
                FOURNISS.RS as 'Fournisseur.RS',
                SOCIETE.RAIS_SOC as 'Societe.RAIS_SOC' 
            FROM FAC_ENT with(nolock)
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = FAC_ENT.CODE_FOUR 
            LEFT JOIN SOCIETE with(nolock) ON SOCIETE.CODE_STE = FAC_ENT.CODE_STE
            WHERE 1=1 "
            . $this->getListeFactFourFilter($post, $arrayReq);
        
        $paginator = new \Helpers\Pagination\PaginatorGestion();
        $paginator->generate(0, 'FAC_ENT.NUM_FACT');
        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * @param array<mixed> $post
     * @param array<mixed> $arrayReq
     */
    private function getListeFactFourFilter(array $post, array &$arrayReq) :string
    {
        $req = Roles::getClausePerimetreSociete('FAC_ENT.CODE_STE', $arrayReq);
        
        // Filtre sur les factures en fonction du niveau de confidentialité des documents
        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .=
                ' AND not exists (
                    SELECT caEntet.NO_CDE
                    FROM CA_ENTET caEntet WITH (NOLOCK)
                    JOIN FAC_LIG facLig3 WITH (NOLOCK) 
                        ON facLig3.NUM_FACT = FAC_ENT.NUM_FACT 
                        AND facLig3.NUM_CDE = caEntet.NO_CDE
                    JOIN TYPDOC typdoc WITH (NOLOCK) 
                        ON typdoc.TYPE = caEntet.TYP_DOC
                        AND typdoc.NIV_CONF > :nivConf
                )';
            $arrayReq[':nivConf'] = $_SESSION['NiveauConfidentialite'];
        }

        
        /***************/
        if (isset($post['filter_egal_SOCIETEdebut-----finRAIS_SOC'])) {
            $post['filter_egal_FAC_ENT.CODE_STEdebut-----finRAIS_SOC'] = $post['filter_egal_SOCIETEdebut-----finRAIS_SOC'];
            unset($post['filter_egal_SOCIETEdebut-----finRAIS_SOC']);
        }

        if (isset($post['filter_CODE_FOURdebut-----finRS'])) {
            $post['filter_FAC_ENT.CODE_FOURdebut-----finRS'] = $post['filter_CODE_FOURdebut-----finRS'];
            unset($post['filter_CODE_FOURdebut-----finRS']);
        }

        return $this->applyFilter($req, $arrayReq, $post);
    }
    
    /**
     * @param mixed $id
     * @return string[][]
     */
    public function ligneListeEvenements($id)
    {
        $param = [
            ':id' => $id,
        ];
        $req = "SELECT 
            e.NUM_FACT, 
            e.NUM_ORD, 
            e.DATE_CRE, e.HEURE_CRE,
            e.CODE_EVE, 
            e.UTIL, 
            e.SIGNAT, 
            e.GERE_HAP,
            " . $this->selectTrad('SUIFADAT.LIBELLE') . ",
            Utili.NOM as Utili_NOM,
            Signataire.NOM as Signataire_NOM
        FROM FA_EVENT e WITH (NOLOCK)
        LEFT JOIN SUIFADAT WITH (NOLOCK) ON SUIFADAT.CODE_SFAC = e.CODE_EVE
        LEFT JOIN HASTUTIL Utili  WITH (NOLOCK) ON Utili.CODE = e.UTIL
        LEFT JOIN HASTUTIL Signataire  WITH (NOLOCK) ON Signataire.CODE = e.SIGNAT
        " . $this->joinTrad('SUIFADAT.LIBELLE', $param) . "
        WHERE e.NUM_FACT = :id 
        ORDER BY e.DATE_CRE desc, e.HEURE_CRE desc";

        return $this->fetchAssocAll($req, $param);
    }
    
    /**
     * @param array<mixed> $post
     * @return string[][]
     */
    public function listeGlobaleEvenements($post = [])
    {
        $arrayReq = [];
        $req = "SELECT 
            e.NUM_FACT, 
            e.DATE_CRE, 
            e.HEURE_CRE, 
            e.UTIL, 
            Utili.NOM as Utili_NOM,
            e.CODE_EVE, 
            " . $this->selectTrad('SUIFADAT.LIBELLE') . ",
            e.SIGNAT, 
            Signataire.NOM as Signataire_NOM,
            f.CODE_STE, 
            m.CONTMEMO 
        FROM FA_EVENT e WITH (NOLOCK)
        JOIN FAC_ENT f WITH (NOLOCK) ON e.NUM_FACT=f.NUM_FACT
        LEFT JOIN MEMOS m WITH (NOLOCK) ON m.NODEMEMO = e.NM_COMME
        LEFT JOIN SUIFADAT WITH (NOLOCK) ON SUIFADAT.CODE_SFAC = e.CODE_EVE
        LEFT JOIN HASTUTIL Utili WITH (NOLOCK) ON Utili.CODE = e.UTIL
        LEFT JOIN HASTUTIL Signataire WITH (NOLOCK) ON Signataire.CODE = e.SIGNAT
        " . $this->joinTrad('SUIFADAT.LIBELLE', $arrayReq) . "
        LEFT JOIN SOCIETE soc WITH (NOLOCK) ON soc.CODE_STE = f.CODE_STE
        LEFT JOIN HASTUTIL util WITH (NOLOCK) ON util.CODE = e.UTIL
        LEFT JOIN HASTUTIL signat WITH (NOLOCK) ON signat.CODE = e.SIGNAT
        WHERE 1=1";

        
        
        if (!$_SESSION['Session_UsrMultiSoc']) {
            $req .=
            " AND (f.CODE_STE IN (
                SELECT hastutil.CODE_STE 
                FROM HASTUTIL hastutil WITH (NOLOCK) 
                WHERE hastutil.CODE='".$_SESSION['Session_Login']."') OR f.CODE_STE IN (
                    SELECT socUsr.COD_STE FROM SOC_USR socUsr WITH (NOLOCK) WHERE socUsr.COD_USR='".$_SESSION['Session_Login']."'))";
        }
        
        // Filtre sur les factures en fonction du niveau de confidentialité des documents
        if ((new LicenceOxalys($this->pdo))->accordConfActive()) {
            $req .=
            ' AND not exists (
                SELECT caEntet.NO_CDE
                FROM CA_ENTET caEntet WITH (NOLOCK)
                INNER JOIN FAC_LIG facLig3 WITH (NOLOCK) ON facLig3.NUM_FACT=f.NUM_FACT AND facLig3.NUM_CDE=caEntet.NO_CDE
                INNER JOIN TYPDOC typdoc WITH (NOLOCK) ON caEntet.TYP_DOC=typdoc.TYPE AND typdoc.NIV_CONF>'.$_SESSION['NiveauConfidentialite'].')';
        }
        

        /***  Filtres avancés ***/
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $resultFiltreAvance = $filtreAvance->generateSql(1);
        if ($resultFiltreAvance != ' AND ') {
            $req .= $resultFiltreAvance;
        }
        $filtreAvance->pushArray($arrayReq);
        /***************/
                
        /***  Filtres et tri tableau ***/
        $whereArray = FilterTable::splitFilter($post, 0, 0, 1);
        if (is_array($whereArray) && isset($whereArray[0]) && $whereArray[0]!= '') {
            $filtre = (string) $whereArray[0];
            $filtre = str_replace('NUMFACT', 'e.NUM_FACT', $filtre);
            $filtre = str_replace('SOCIETE', 'f.CODE_STE', $filtre);
            $filtre = str_replace('NOMUTILISATEUR', 'util.NOM', $filtre);
            $filtre = str_replace('UTILISATEUR', 'e.UTIL', $filtre);
            $filtre = str_replace('NOMSIGNATAIRE', 'signat.NOM', $filtre);
            $filtre = str_replace('SIGNATAIRE', 'e.SIGNAT', $filtre);
            $req .= $filtre;
            if (is_array($whereArray[1]) && count($whereArray[1]) > 0) {
                $arrayReq=  array_merge($arrayReq, $whereArray[1]);
            }
        }
        $req .= $whereArray['tri']??'';

        $paginatorGestion = new \Helpers\Pagination\PaginatorGestion();
        $paginatorGestion->injecterSqlCount($req, $post, $arrayReq, $this->pdo);

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

    /**
     * @param array<mixed> $post
     */
    public function export(array $post) :\PDOStatement
    {
        $arrayReq = [];
        $query =
            "SELECT 
                FA_EVENT.*,
                ".$this->selectTrad('SUIFADAT.LIBELLE').",
                Util.NOM as 'Util.NOM',
                Signat.NOM as 'Signat.NOM',
                MemoCom.CONTMEMO as COMME,
                FAC_ENT.NUM_FACT,
                FAC_ENT.TYPE,
                FAC_ENT.FACT_FOU,
                FAC_ENT.CODE_STE,
                FAC_ENT.CODE_FOUR,
                FOURNISS.RS as 'Fournisseur.RS',
                SOCIETE.RAIS_SOC as 'Societe.RAIS_SOC'
            FROM FA_EVENT with(nolock)
            LEFT JOIN SUIFADAT with(nolock) ON SUIFADAT.CODE_SFAC = FA_EVENT.CODE_EVE
            " . $this->joinTrad('SUIFADAT.LIBELLE', $arrayReq) . "
            LEFT JOIN MEMOS as MemoCom with(nolock) ON MemoCom.NODEMEMO = FA_EVENT.NM_COMME
            LEFT JOIN HASTUTIL as Util with(nolock) ON Util.CODE = FA_EVENT.UTIL
            LEFT JOIN HASTUTIL as Signat with(nolock) ON Signat.CODE = FA_EVENT.SIGNAT

            JOIN FAC_ENT with(nolock) ON FAC_ENT.NUM_FACT = FA_EVENT.NUM_FACT
            JOIN FOURNISS with(nolock) ON FOURNISS.CODE = FAC_ENT.CODE_FOUR 
            LEFT JOIN SOCIETE with(nolock) ON SOCIETE.CODE_STE = FAC_ENT.CODE_STE
            
            WHERE 1=1 "
            . $this->getListeFactFourFilter($post, $arrayReq);
        

        return $this->createStatement(
            $query,
            $arrayReq
        );
    }
}
