<?php

namespace Modules\GlobalApplication\Repository;

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

class DocumentsRattaches extends AbsRepository
{

    
    /**
     * detail
     * @param string $code
     * @param array<mixed> $valeur
     * @return array<array<string>>
     */
    public function detail(string $code, array $valeur) : ?array
    {
        $req="SELECT *,MEMOS.CONTMEMO COMMENTAIRE FROM DOC_ATT d with (nolock)
        LEFT JOIN CENTRE with (nolock) ON CENTRE.CODE_CTR = d.CENTRE
        LEFT JOIN HASTUTIL with (nolock) ON HASTUTIL.CODE = d.AUTEUR 
        LEFT join PJTYPES with (nolock) on PJTYPES.PJ_TYPE = d.TYPE_PJ
        LEFT join MEMOS with (nolock) on MEMOS.NODEMEMO = d.NM_COMM
        WHERE d.NO_DA=:noDa ";
        
        $arrayReq = [
            ':noDa' => $valeur['NO_DA'],
            ':codEnr' => $code
        ];

        if ($valeur['NO_DA']=='CA_ENTET') {
            $req .= " and (REPLACE(d.COD_ENR,' ','')=:codEnr or REPLACE(d.COD_ENR,' ','')=:codEnrWithZero)";
            
            $numCde=$code;
            while (strlen($numCde)<9) {
                $numCde = '0'.$numCde;
            }

            $arrayReq[':codEnrWithZero'] = $numCde;
        } else {
            $req .= " and d.COD_ENR = :codEnr";
        }
        
        if (isset($valeur["filter_CODE_CAT"]) && $valeur["filter_CODE_CAT"]) {
            $req .= " and (d.CODE_CAT=:value1 or p.ALPHA1=:value10)";
            $arrayReq[':value1']="";
            $arrayReq[':value10']="";
            if ($valeur["filter_CODE_CAT"]!="Autre") {
                $arrayReq[':value1']=$valeur["filter_CODE_CAT"];
            }
                $arrayReq[':value10']=$valeur["filter_CODE_CAT"];
        }
        
        if (isset($valeur["filter_TITRE"]) && $valeur["filter_TITRE"]) {
            $req .= " and (UPPER(d.TITRE) LIKE :value2)";
            $arrayReq[':value2']='%'.strtoupper($valeur["filter_TITRE"]).'%';
        }
        
        if (isset($valeur["filter_CENTRE"]) && $valeur["filter_CENTRE"]) {
            $req .= " and CODE_CTR=:value3";
            $arrayReq[':value3']=$valeur["filter_CENTRE"];
        }
        
        if (isset($valeur["filter_AUTEUR"]) && $valeur["filter_AUTEUR"]) {
            $req .= " and HASTUTIL.CODE=:value4";
            $arrayReq[':value4']=$valeur["filter_AUTEUR"];
        }
        
        if (isset($valeur["filter_DT_VALID"]) && $valeur["filter_DT_VALID"]) {
            Date::dateToSql($valeur["filter_DT_VALID"]);
            
            $req .= " and d.DT_VALID>:value5 AND d.DT_PARUT<:value6";
            $arrayReq[':value5']=$valeur["filter_DT_VALID"];
            $arrayReq[':value6']=$valeur["filter_DT_VALID"];
        }
        
        
        if (isset($valeur["filter_etat"]) && $valeur["filter_etat"]) {
            $date=date("Ymd");
            switch ($valeur["filter_etat"]) {
                case 1: // a jours
                    $req .= " and d.NOM_FIC != '' and ('$date'<d.DT_VALID OR (d.DT_VALID='00000000' and d.DT_PARUT!='00000000') OR (d.DT_VALID='00000000' and d.DT_PARUT='00000000'))";
                    break;
                case 2: // périmé
                    $req .= " and d.NOM_FIC != '' and '$date'>d.DT_VALID and d.DT_VALID!='00000000'";
                    break;
                case 3:// sans objet
                    $req .= " and d.NOM_FIC=''";
                    break;
                case 4: // absent
                    $req .= " and d.DOC_INUTILE IS NULL";
                    break;
            }
        }
        $whereArray = FilterTable::splitFilter($valeur);
        
        if ($whereArray) {
            $req .= $whereArray['tri'];
        } else {
            $req .= " ORDER BY TYPE_PJ ASC , TITRE ASC";
        }

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * docAttAvecMemos
     * @param string $code
     * @param string $ordre
     * @param string $noDa
     * @return array<string>
     */
    public function docAttAvecMemos(string $code, string $ordre, string $noDa) : ?array
    {
        $req=
            "SELECT * 
            FROM DOC_ATT with (nolock) LEFT JOIN MEMOS with (nolock) on MEMOS.NODEMEMO = DOC_ATT.NM_COMM 
            WHERE NO_DA=:noDa and COD_ENR=:value and NO_ORDRE=:value1";
            
        $arrayReq = [
            ':noDa' => $noDa,
            ':value' => $code,
            ':value1' => $ordre
        ];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        return $docAtt->fetch();
    }
    
    /**
     * libelleChampSupp
     * @param string $id
     * @param string $noDa
     * @return array<string>
     */
    public function libelleChampSupp(string $id, string $noDa) : ?array
    {
        $req="SELECT * FROM CS_CHPS with (nolock) WHERE COD_TAB=:noDa AND NUM_VAR=:value";
        $arrayReq = [
            ':noDa' => $noDa,
            ':value' => $id
        ];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        return $docAtt->fetch();
    }
    
    // A GARDER ???
    /**
     * titreExist
     * @param string $titre
     * @return array<string>
     */
    public function titreExist(string $titre) : ?array
    {
        $req="SELECT * FROM PARAMDICO with (nolock) WHERE TYPEDICO = 'DOCATTFNR_LSTDOCS' AND LIBELLE=:value";
        $arrayReq = [':value' => $titre];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        return $docAtt->fetch();
    }
    
    // A GARDER ???
    /**
     * idExistPARAMDICO
     * @param string $id
     * @return array<string>
     */
    public function idExistPARAMDICO(string $id) : ?array
    {
        $req="SELECT * FROM PARAMDICO with (nolock) WHERE  TYPEDICO = 'DOCATTFNR_LSTDOCS' AND LTRIM(RTRIM(ALPHA2)) = :value  ";
        $arrayReq = [':value' => $id];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        return $docAtt->fetch();
    }
    
    /**
     * idExistdocAtt
     * @param string $code
     * @param string $id
     * @param string $noDa
     * @return array<string>
     */
    public function idExistdocAtt(string $code, string $id, string $noDa) : ?array
    {
        $req = "SELECT * FROM DOC_ATT with (nolock) WHERE NO_DA=:noDa AND NO_ORDRE=:value1 AND COD_ENR=:value2";
        $arrayReq = [
            ':noDa' => $noDa,
            ':value1' => $id,
            ':value2' => $code
        ];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        
        return $docAtt->fetch();
    }
    
    /**
     * getDocRattaches
     * @param string $noDa
     * @param string $id
     * @return array<string>
     */
    public function getDocRattaches(string $noDa, string $id) : ?array
    {
        $req = "SELECT * FROM DOC_ATT d with (nolock) WHERE d.NO_DA=:noDa AND REPLACE(d.COD_ENR,' ','')=:value1 ";
        $arrayReq = [
            ':noDa' => $noDa,
            ':value1' => $id,
        ];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        return $docAtt->fetch();
    }

    /**
     * select all the PJ from database
     * @param string $FIC_PJ_NO_MIN
     * @param string $FIC_PJ_NO_MAX
     * @param string $COD_ENR
     * @return array<mixed>
     */
    public function selectPJDocAtt(string $FIC_PJ_NO_MIN, string $FIC_PJ_NO_MAX, string $COD_ENR) : ?array
    {
        $req = "select * from DOC_ATT with (nolock) where NO_DA='MAIL_SUIVI' and NO_ORDRE >= :FIC_PJ_NO_MIN and NO_ORDRE <= :FIC_PJ_NO_MAX and COD_ENR = :COD_ENR";
        $arrayReq = [   ':FIC_PJ_NO_MIN' => $FIC_PJ_NO_MIN,
                        ':FIC_PJ_NO_MAX' => $FIC_PJ_NO_MAX,
                        ':COD_ENR' => $COD_ENR
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * getMaxNoOrdre
     * @param string $noDa
     * @param string $codeEreng
     * @return int
     */
    public function getMaxNoOrdre(string $noDa, string $codeEreng) : int
    {
        $req = "select max(NO_ORDRE) NO_ORDRE from DOC_ATT where NO_DA=:noDa and COD_ENR=:codeEreng";
        
        $arrayReq = [
            ':noDa' => $noDa,
            ':codeEreng' => $codeEreng,
        ];
        
        $docAtt = $this->pdo->prepare($req);
        $docAtt->execute($arrayReq);
        $returnRequete =  $docAtt->fetch();
        
        return ((int) $returnRequete['NO_ORDRE']) + 1;
    }

    /**
     * @param string $data
     * @param array<mixed> $params
     * @return void
     */
    public function createDocumentRattachesFromMobility($data, $params = [])
    {
        if ($data !== '') {
            $stmtAddDocAtt = "
                INSERT INTO
                    DOC_ATT (NO_DA, NO_ORDRE, NOM_FIC, NOM_ORI, COD_ENR, TYPE_PJ, TITRE, DT_PARUT, AUTEUR, COPIE)
                VALUES {$data}
            ";

            $this->fetchAssocAll($stmtAddDocAtt, $params);
        }
    }

    /**
     * @param string $values
     * @param array<mixed> $params
     * @return mixed[]|\string[][]
     */
    public function getDocumentRattaches($values, $params = [])
    {
        $stmtGetDocAtt = "
            SELECT
                DOC.NOM_FIC,
                DOC.NO_ORDRE,
                DOC.COD_ENR,
                DOC.NO_DA,
                DOC.NM_COMM
            FROM
                DOC_ATT DOC WITH(nolock)
            WHERE
                DOC.NO_DA = :codeTable
                AND cast(DOC.COD_ENR as int) = :numDoc
                AND DOC.NO_ORDRE IN ({$values})
        ";

        return $this->fetchAssocAll($stmtGetDocAtt, $params);
    }

    /**
     * @param string $memos
     * @param string $doc
     * @param array<mixed> $paramsDeleteDocAtt
     * @return void
     */
    public function deleteMemosAndDoc($memos, $doc, $paramsDeleteDocAtt)
    {
        if ($memos !== '') {
            $stmtDeleteMemo = "
                DELETE FROM MEMOS
                WHERE NODEMEMO IN ({$memos})
            ";

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

        if ($doc !== '') {
            $stmtDeleteDocAtt = "
                DELETE FROM DOC_ATT
                WHERE {$doc}
            ";

            $this->fetchAssocAll($stmtDeleteDocAtt, $paramsDeleteDocAtt);
        }
    }
}
