<?php

namespace Modules\Signature\Repository;

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

class CdeSignRepository extends AbsRepository
{
    /*private int $nivConf;

    private function getNivConf() : int
    {
        if ($_SESSION["Session_Login"] == 'OXAUSER') {
            $this->nivConf = 99;
        } else {
            if (!isset($this->nivConf)) {
                $hastutil = new Hastutil($this->pdo);
                $infoUser = $hastutil->find($_SESSION["Session_Login"]);
                $this->nivConf = (int)$infoUser['NIV_CONF'];
            }
        }
        return $this->nivConf;
    }*/

    /**
     * @param array<mixed> $post
     * @param string $signataire
     * @return array<array<mixed>>
     */
    public function listTobeSigned(array $post = [], $signataire = '')
    {
        $siSecto = Roles::droit($this->pdo, 'SISECTOR', 'PARAMSTE');

        $select = "
            SECTIMPU.LIBELLE LIB_SI,
            CA_ENTET.*,
            SOCIETE.RAIS_SOC as Societe_RAIS_SOC,
            HASTUTIL.NOM,
            FOURNISS.RS,
            SIG_CDE.SIGNAT,
            MEMOS.CONTMEMO BLOCNOTE,
            ( select count(*) from BR_RECEP with(nolock) where BR_RECEP.NUM_CDE=CA_ENTET.NO_CDE) nbBrCde,
            ( select COUNT(*) from BR_LIGNE with (nolock) where NUM_CDE=CA_ENTET.NO_CDE) LIVR_PART, 
            ( select count(*) from DOC_ATT  with (nolock) where NO_DA='CA_ENTET' AND COD_ENR = right('000000000' + cast(CA_ENTET.NO_CDE as varchar), 9) ) DOC_ATT,
            ( select count(*) from CA_PIJO  with (nolock) where CA_PIJO.NO_CA = CA_ENTET.NO_CDE ) PJ,
            ( select count(*) from FOURNINC with (nolock) where CA_ENTET.NO_CDE = FOURNINC.NO_CDE ) INCIDENT,
            ( select count(*) from CDE_NOT with (nolock) where NOTMANU = 0x54 and DTEDEMAND <= '".date('Ymd')."' and (DTEVALID >= '".date('Ymd')."' or DTEVALID = '00000000') and CDE_NOT.NO_CDE = CA_ENTET.NO_CDE ) NOTATION,
            refus.NOM_REFUS,
            refus.MOTIF_REFUS,
            refus.DTE_REFUS,
            PARAVRAC.LIB01PARV BLOC_MODIF_CDE_LIV
        ";
        
        $req = "SELECT $select from CA_ENTET  with (nolock)
                JOIN SOCIETE with (nolock) ON SOCIETE.CODE_STE = CA_ENTET.CODE_STE
                LEFT JOIN SECTIMPU with (nolock) on SECTIMPU.CODE_SI = CA_ENTET.SECTIMP and SECTIMPU.CODE_STE = ".($siSecto? 'CA_ENTET.CODE_STE':"''")." 
                LEFT JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = CA_ENTET.ACH
                LEFT JOIN SIG_CDE with (nolock)  on SIG_CDE.NO_CDE = CA_ENTET.NO_CDE
                LEFT JOIN FOURNISS with (nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
                LEFT JOIN CABNOTE with (nolock) on CABNOTE.NO_CA = CA_ENTET.NO_CDE
                LEFT JOIN MEMOS with (nolock) on MEMOS.NODEMEMO = CABNOTE.CA_NOTE
                left join TYPEACHAT with (nolock) on TYPEACHAT.TYP_ACH = CA_ENTET.COD_TAC
                left join TYPDOC with (nolock) on CA_ENTET.TYP_DOC = TYPDOC.TYPE
                left join PARAVRAC with (nolock) on PARAVRAC.TYPEPARV='PARCDESOC' and PARAVRAC.CODEPARV = CA_ENTET.CODE_STE
                outer apply (
                    select TOP 1 NOM NOM_REFUS, CONTMEMO MOTIF_REFUS, DTE DTE_REFUS 
                    from CA_EVENT with (nolock) 
                    left join MEMOS with(nolock) on MEMOS.NODEMEMO = CA_EVENT.NM_COMME
                    left join HASTUTIL with (nolock) on HASTUTIL.CODE = CA_EVENT.SIGNAT
                    where CA_EVENT.CODE = 'REF' AND CA_EVENT.NO_CDE = CA_ENTET.NO_CDE
                    order by DTE_REFUS desc
                ) refus 
                WHERE 1=1 ".Roles::getClauseSocieteOuvert('SOCIETE');
        $arrayReq=[];

        //TYPDOC.NIV_CONF <= :nivConf
        // $arrayReq[':nivConf'] = $this->getNivConf();

        $req .= " and CA_ENTET.SIGNEE = 0x46
        and SIG_CDE.SIGNABLE= 0x54
        and SIG_CDE.BON_SIGNE = 0x54
        and SIG_CDE.INFORME	 = 0x46
        and SIG_CDE.SIGNAT=:signataire ";
        
        $arrayReq[':signataire'] = $signataire? $signataire:$_SESSION['Session_Login'];

        $req .= Roles::getClausePerimetreSociete('CA_ENTET.CODE_STE', $arrayReq);

        if ($post['filter_NO_CDE']??'') {
            $post['filter_CA_ENTET.NO_CDE'] = $post['filter_NO_CDE'];
            unset($post['filter_NO_CDE']);
        }

        if ($post['filter_CODE']??'') {
            $post['filter_FOURNISS.CODE'] = $post['filter_CODE'];
            unset($post['filter_CODE']);
        }

        if ($post['filter_egal_CODE_STEdebut-----finRAIS_SOC']??'') {
            $post['filter_egal_CA_ENTET.CODE_STEdebut-----finSOCIETE.RAIS_SOC'] = $post['filter_egal_CODE_STEdebut-----finRAIS_SOC'];
            unset($post['filter_egal_CODE_STEdebut-----finRAIS_SOC']);
        }

        // Col ambigu (TRI)
        // WARNING : this is NOT compatible with PaginatorGestion
        $post['tri_CA_ENTET.NO_CDE'] = $post['tri_NO_CDE'] ?? '';
        unset($post['tri_NO_CDE']);
        $post['tri_FOURNISS.CODE'] = $post['tri_CODE'] ?? '';
        unset($post['tri_CODE']);
        $post['tri_CA_ENTET.CODE_STE'] = $post['tri_CODE_STE'] ?? '';
        unset($post['tri_CODE_STE']);
        // TODO : do others ?
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 1);
        $filtreAvance = new \Helpers\Filter\FiltreAvance\Generator($post);
        $req .= $filtreAvance->generateSql(1);
        $filtreAvance->pushArray($arrayReq);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0]. ' ' . $whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $code
     * @return array<mixed> $array
     */
    public function listeSignat($code)
    {
        $req = "SELECT * FROM SIG_CDE  WITH (NOLOCK) 
                LEFT JOIN HASTUTIL  WITH (NOLOCK) ON HASTUTIL.CODE = SIG_CDE.SIGNAT
                WHERE NO_CDE = :value
                ORDER BY ORDRE";
        
        $arrayReq[':value']=$code;
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    

    /**
     * @param  array<mixed> $array
     * @return void
     */
    public function insertNewSignat($array)
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        
        $req = "INSERT INTO SIG_CDE (SIGNAT, BON_SIGNE, NO_CDE, DTE_MISE, ORDRE, SIGNABLE, CAROLES, INFORME)
                VALUES(:value1, 0x54, :value2, :value3, ".$array['ORDRE'].", ".sql::setBoolSql($array['SIGNABLE']).", :value6, ".sql::setBoolSql($array['INFORME']).")";
        
        $arrayReq= [
            ':value1' => $array['SIGNAT'],
            ':value2' => $array['NO_CDE'],
            ':value3' => $array['DTE_MISE'],
            ':value6' => $array['CAROLES'],
        ];
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
    }

    /**
     * signataireAttendu
     *
     * @param  int $numCde
     * @return array<mixed>
     */
    public function signataireAttendu(int $numCde)
    {
        $req = "select * from SIG_CDE with (nolock)
                left join HASTUTIL with (nolock) on HASTUTIL.CODE = SIG_CDE.SIGNAT
                where NO_CDE = :numCde";

        $arrayReq[':numCde'] = $numCde;

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

    /**
     * @param  int $numCde
     * @return array<mixed>
     */
    public function getCommentaireSignataire(int $numCde)
    {
        $req = "select HASTUTIL.NOM, HASTUTIL.CODE, CONTMEMO 
                from CA_EVENT with (nolock)
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = CA_EVENT.NM_COMME
                left join HASTUTIL with (nolock) on HASTUTIL.CODE = CA_EVENT.UTIL
                where CA_EVENT.CODE = 'SIG' AND NO_CDE = :numCde and CONTMEMO != ''
                order by NUM_ORD";

        $arrayReq[':numCde'] = $numCde;

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