<?php

namespace Modules\Commande\Repository;

use Helpers\QueryBuilder\AbsRepository;
use Helpers\Trad\ITrad;

class ListeCommande extends AbsRepository
{
    public function listeLigneCommande($post)
    {
        $req = "SELECT * FROM ( 
            SELECT 
                CA_LIGNE.NO_PO,
                CASE WHEN CA_LIGNE.SOLD_PHYS = 0x46 AND CA_LIGNE.ET_CDELIG='E' AND CA_LIGNE.QTE_COMM != CA_LIGNE.RAL 
                    THEN 1
                    ELSE 0
                END LIVR_PART,
                CA_LIGNE.CODE_ART, 
                CA_LIGNE.DES_SOMM, 
                CA_LIGNE.QTE_COMM, 
                CA_LIGNE.UNITE, 
                CA_LIGNE.DTELIVCON, 
                CA_LIGNE.NM_DCOMP, 
                CA_LIGNE.PU, 
                CA_LIGNE.REMISE, 
                CA_LIGNE.TVA,
                CA_LIGNE.SITE_STK, 
                CA_LIGNE.NO_DA, 
                CA_LIGNE.NO_PO_DA, 
                CA_LIGNE.RECEP_AUTO RECEP_AUTO_L, 
                CA_LIGNE.SSFAM, 
                CA_LIGNE.SECTIMP SECTIMP_L, 
                CA_LIGNE.CODE_COMP CODE_COMP_L, 
                CA_LIGNE.SSCPTEANA SSCPTEANA_L,
                CA_LIGNE.ACCORCA, CA_LIGNE.RAL, 
                
                CA_ENTET.NO_CDE,
                CA_ENTET.CODE_FOU, 
                CA_ENTET.ACH, 
                CA_ENTET.CODE_STE, 
                CA_ENTET.SECTIMP, 
                CA_ENTET.CODECOMP, 
                CA_ENTET.SSCPTEANA, 
                CA_ENTET.TYP_DOC, 
                CA_ENTET.DTE_CREAT, 
                CA_ENTET.DTE_VALID,
                CA_ENTET.DTE_PERIO, 
                CA_ENTET.DTE_AR, 
                CA_ENTET.CTR_EMETT, 
                CA_ENTET.CTR_LIV, 
                CA_ENTET.TOT_CDE, 
                CA_ENTET.TOT_TTC, 
                CA_ENTET.ETAT_CDE, 
                CA_ENTET.SIGNEE, 
                CA_ENTET.REFUSEE,
                Acheteur.NOM as Acheteur_NOM, 
                Demandeur.NOM as Demandeur_NOM, 
                Societe.RAIS_SOC as Societe_RAIS_SOC, 
                CentreEmetteur.RAIS_SOC as CentreEmetteur_RAIS_SOC, 
                CentreLivraison.RAIS_SOC as CentreLivraison_RAIS_SOC, 
                FOURNISS.RS
            FROM CA_LIGNE  with (nolock)
            LEFT JOIN CA_ENTET with (nolock) ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
            LEFT JOIN HASTUTIL as Acheteur with (nolock) ON Acheteur.CODE = CA_ENTET.ACH
            
            LEFT JOIN HASTUTIL as Demandeur with (nolock) ON Demandeur.CODE = CA_ENTET.DEMANDEUR
            LEFT JOIN SOCIETE as Societe with (nolock) ON Societe.CODE_STE = CA_ENTET.CODE_STE
            LEFT JOIN CENTRE as CentreEmetteur with (nolock) ON CentreEmetteur.CODE_CTR = CA_ENTET.CTR_EMETT
            LEFT JOIN CENTRE as CentreLivraison with (nolock) ON CentreLivraison.CODE_CTR = CA_ENTET.CTR_LIV
            
            LEFT JOIN FOURNISS with (nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
        ) CDE where 1=1";


        $arrayReq = [];
        $this->_listeLigneCommandeClause($req, $arrayReq, $post);
        

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

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

    /**
     * @param mixed $post
     * @return array<mixed>
     */
    public function listeLigneCommandeExport($post, ITrad $trad)
    {
        /*$listeEtatTab = [
            'E' => $trad->t('En cours'),
            'A' => $trad->t('En Attente'),
            'V' => $trad->t('Validée'),
            'S' => $trad->t('Soldées physiquement'),
            'Z' => $trad->t('Annulées'),
        ];*/

        $req = "SELECT 
            NO_CDE as Numero,
            NO_PO as Ligne, 
            NOM as '".$trad->t('Nom')."', 
            RS as '".$trad->t('Raison sociale')."',
            LIVR_PART as '".$trad->t('Livraison partielle')."', 
            CODE_ART as Article,
            DES_SOMM as Désignation,
            QTE_COMM as 'Qté comm.',
            UNITE as 'Unité',
            DTELIVCON as Délais,
            CASE WHEN ETAT_CDE = 'E' AND LIVR_PART!=0 THEN '".$trad->t('Livraison partielle')."'
            WHEN ETAT_CDE = 'V' and SIGNEE=0x54 and REFUSEE=0x46 THEN '".$trad->t('A envoyer')."'
            WHEN ETAT_CDE = 'V' and SIGNEE=0x46 and REFUSEE=0x46 THEN '".$trad->t('En signature')."'
            WHEN ETAT_CDE = 'V' and SIGNEE=0x46 and REFUSEE=0x54 THEN '".$trad->t('Refusées')."'
            WHEN ETAT_CDE = 'E' THEN '".$trad->t('En cours')."'
            WHEN ETAT_CDE = 'A' THEN '".$trad->t('En Attente')."'
            WHEN ETAT_CDE = 'v' THEN '".$trad->t('Validée')."'
            WHEN ETAT_CDE = 'S' THEN '".$trad->t('Soldées physiquement')."'
            WHEN ETAT_CDE = 'Z' THEN '".$trad->t('Annulées')."'
            end as '".$trad->t('Etat physique')."',
            CDE.PU as '".$trad->t('Prix unitaire')."',
            REMISE as '".$trad->t('Remise')."',
            TVA as '".$trad->t('TVA')."',
            SITE_STK as '".$trad->t('Site de stock')."',
            NO_DA as '".$trad->t('Numéro de DA')."',
            NO_PO_DA as '".$trad->t('Numéro position DA')."',
            CASE WHEN RECEP_AUTO_L=0x54 THEN '".$trad->t('oui')."' WHEN RECEP_AUTO_L=0x46 THEN '".$trad->t('non')."' end as '".$trad->t('Reception automatique')."',
            SSFAM as '".$trad->t('Sous Famille')."',
            SECTIMP_L as '".$trad->t("Section d''imputation")."',
            CODE_COMP_L as '".$trad->t('Code complementaire')."',
            SSCPTEANA as '".$trad->t('SCA')."',
            ACCORCA as '".$trad->t('Accord-cadre')."',
            RAL as '".$trad->t('Reste à livrer')."',
            CODE_FOU as '".$trad->t('Code fournisseur')."',
            ACH as '".$trad->t('Acheteur')."',
            CODE_STE as '".$trad->t('Code societe')."',
            TYP_DOC as '".$trad->t('Type du document')."',
            DTE_CREAT as '".$trad->t('Date de création')."',
            DTE_VALID as '".$trad->t('Date de validité')."',
            DTE_PERIO as '".$trad->t('Date de la période')."',
            DTE_AR as '".$trad->t('Date accusé de réception')."',
            CTR_EMETT as '".$trad->t('Centre émetteur')."',
            CTR_LIV as '".$trad->t('Centre de livraison')."',
            TOT_CDE as '".$trad->t('Total commande')."',
            ETAT_CDE as '".$trad->t('Etat commande')."',
            ETAT_FIN as '".$trad->t('Etat final')."',
            CASE WHEN SIGNEE=0x54 THEN '".$trad->t('oui')."' WHEN SIGNEE=0x46 THEN '".$trad->t('non')."' end as '".$trad->t('Signée')."',
            CASE WHEN REFUSEE=0x54 THEN '".$trad->t('oui')."' WHEN REFUSEE=0x46 THEN '".$trad->t('non')."' end as '".$trad->t('Refusée')."'
        FROM ( 
            SELECT 
                CA_LIGNE.NO_PO,
                CASE WHEN CA_LIGNE.SOLD_PHYS = 0x46 AND CA_LIGNE.ET_CDELIG='E' AND CA_LIGNE.QTE_COMM != CA_LIGNE.RAL 
                    THEN 1
                    ELSE 0
                END LIVR_PART,
                CA_LIGNE.CODE_ART, 
                CA_LIGNE.DES_SOMM, 
                CA_LIGNE.QTE_COMM, 
                CA_LIGNE.UNITE, 
                CA_LIGNE.DTELIVCON, 
                CA_LIGNE.NM_DCOMP, 
                CA_LIGNE.PU, 
                CA_LIGNE.REMISE, 
                CA_LIGNE.TVA,
                CA_LIGNE.SITE_STK, 
                CA_LIGNE.NO_DA, 
                CA_LIGNE.NO_PO_DA, 
                CA_LIGNE.RECEP_AUTO RECEP_AUTO_L, 
                CA_LIGNE.SSFAM, 
                CA_LIGNE.SECTIMP SECTIMP_L, 
                CA_LIGNE.CODE_COMP CODE_COMP_L, 
                CA_LIGNE.SSCPTEANA SSCPTEANA_L,
                CA_LIGNE.ACCORCA, 
                CA_LIGNE.RAL, 
                
                CA_ENTET.NO_CDE,
                CA_ENTET.CODE_FOU, 
                CA_ENTET.ACH, 
                CA_ENTET.CODE_STE, 
                CA_ENTET.SECTIMP, 
                CA_ENTET.CODECOMP, 
                CA_ENTET.SSCPTEANA, 
                CA_ENTET.TYP_DOC, 
                CA_ENTET.DTE_CREAT, 
                CA_ENTET.DTE_VALID,
                CA_ENTET.DTE_PERIO, 
                CA_ENTET.DTE_AR, 
                CA_ENTET.CTR_EMETT, 
                CA_ENTET.CTR_LIV, 
                CA_ENTET.TOT_CDE, 
                CA_ENTET.TOT_TTC, 
                CA_ENTET.ETAT_CDE, 
                CA_ENTET.ETAT_FIN, 
                CA_ENTET.SIGNEE, 
                CA_ENTET.REFUSEE,

                Acheteur.NOM, 
                FOURNISS.RS
            FROM CA_LIGNE  with (nolock)
            LEFT JOIN CA_ENTET with (nolock) ON CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
            LEFT JOIN HASTUTIL as Acheteur with (nolock) ON Acheteur.CODE = CA_ENTET.ACH
            LEFT JOIN FOURNISS with (nolock) ON FOURNISS.CODE = CA_ENTET.CODE_FOU
        ) CDE where 1=1";

        $arrayReq = [];
        $this->_listeLigneCommandeClause($req, $arrayReq, $post);
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $arrayReq
     * @param array<mixed> $post
     */
    private function _listeLigneCommandeClause(
        string &$req,
        array &$arrayReq,
        array $post
    ) :void {

        if (isset($post['filter_ETAT_FIN'])) {
            if ($post['filter_ETAT_FIN']=='T') {
                $post['filter_ETAT_FIN']='';
                $req .= ' AND ETAT_FIN != \'F\'';
            }
        }
        
        if (isset($post['filter_ETAT_CDE'])) {
            $oldEtat='';
            if ($post['filter_ETAT_CDE']=='V1' || $post['filter_ETAT_CDE']=='V2' || $post['filter_ETAT_CDE']=='V3') {
                $oldEtat = $post['filter_ETAT_CDE'];
                $post['filter_ETAT_CDE'] = 'V';
            }
            
            if ($post['filter_ETAT_CDE']=='L') {
                $post['filter_ETAT_CDE'] = '';
                $req .=" and EXISTS ( select * from CA_LIGNE  with (nolock) WHERE CA_LIGNE.NO_CDE = NO_CDE AND CA_LIGNE.SOLD_PHYS = 0x46 AND CA_LIGNE.ET_CDELIG='E' AND CA_LIGNE.QTE_COMM != CA_LIGNE.RAL)";
            }
            
            if ($oldEtat=='V1') {
                $post['filter_SIGNEE']='T';
                $post['filter_REFUSEE']='F';
            } elseif ($oldEtat=='V2') {
                $post['filter_SIGNEE']='F';
                $post['filter_REFUSEE']='F';
            } elseif ($oldEtat=='V3') {
                $post['filter_SIGNEE']='F';
                $post['filter_REFUSEE']='T';
            }
        }

        $arrayReq = [];

        $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]);
            }
        }
    }
    
    public function listeEventCde($post)
    {
        $arrayReq = [];
        
        $req = "SELECT * 
                FROM (
                    SELECT 
                        NO_CDE, AVNT, DTE,HEURE, CA_EVENT.CODE, 
                        (CASE 
                            WHEN EXISTS (SELECT HASTUTIL.NOM 
                                            FROM HASTUTIL with(nolock)
                                            WHERE HASTUTIL.CODE = CA_EVENT.UTIL) 
                            THEN HASTUTIL.NOM
                            ELSE OXALINK_USER.NOM
                        END) AS NOM, 
                        UTIL, 
                        SIGNAT, 
                        CONTMEMO, NUM_ORD,
                        Signataire.NOM as Signataire_NOM,
                        " . $this->selectTrad('SUICADAT.LIBELLE') . "
                    FROM CA_EVENT WITH(NOLOCK)
                    LEFT JOIN HASTUTIL WITH(NOLOCK) ON HASTUTIL.CODE = CA_EVENT.UTIL
                    LEFT JOIN HASTUTIL as Signataire WITH(NOLOCK) ON Signataire.CODE = CA_EVENT.SIGNAT
                    LEFT JOIN OXALINK_USER with(nolock) ON OXALINK_USER.USER_ID = CA_EVENT.ID_USER_OXALINK
                    LEFT JOIN SUICADAT WITH(NOLOCK) ON SUICADAT.CODE_SCD = CA_EVENT.CODE
                    " . $this->joinTrad('SUICADAT.LIBELLE', $arrayReq) . "
                    LEFT JOIN MEMOS WITH(NOLOCK) ON MEMOS.NODEMEMO = CA_EVENT.NM_COMME
                ) req";
                
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        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);
    }
    
    public function listeEnteteNonValide($post)
    {
        $arrayReq=[];
        
        $req = "SELECT * from (select NO_CDE, CODE_FOU, ACH, DTE_CREAT,TOT_CDEHT,TOT_TTC, NOM, RS
                from CA_ENTET with (nolock)
                left join FOURNISS with(nolock) on FOURNISS.CODE=CA_ENTET.CODE_FOU
                LEFT join HASTUTIL with(nolock) on HASTUTIL.CODE=CA_ENTET.ACH
                where SIGNEE = 0x46 and REFUSEE = 0x46 and ETAT_CDE !='V') CDE";

        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        
        return $commande->fetchAll($this->pdo::FETCH_ASSOC);
    }
    
    public function listeEnteteHistorisees($post)
    {
        $arrayReq=[];
        
        $req = "SELECT * from (select NO_CDE, CODE_FOU, ACH, DTE_CREAT,TOTAL_HT AS TOT_CDEHT,TOTAL_TTC AS TOT_TTC, NOM, RS, AVNT AVENANT, CA_ENTET.DEVISE
                from HCA_ENTE as CA_ENTET with (nolock)
                left join FOURNISS with(nolock) on FOURNISS.CODE=CA_ENTET.CODE_FOU
                LEFT join HASTUTIL with(nolock) on HASTUTIL.CODE=CA_ENTET.ACH) CDE";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        
        return $commande->fetchAll($this->pdo::FETCH_ASSOC);
    }
        
    /**
     * enteteHisto
     *
     * @param  string $code
     * @param  string $avenant
     * @return array<mixed>|false
     */
    public function enteteHisto(string $code, string $avenant)
    {
        $req = "Select * from HCA_ENTE with (nolock)
                WHERE NO_CDE = :value";


        $req .= " and LTRIM(RTRIM(HCA_ENTE.AVNT)) = :avenant";
        $arrayReq[':avenant'] = $avenant==='0'? '':$avenant;
        $arrayReq[':value']=$code;
                
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        
        return $commande->fetch();
    }
        
    /**
     * detailLigneHisto
     *
     * @param  string $code
     * @param  string $avenant
     * @return array<mixed>|false
     */
    public function detailLigneHisto(string $code, string $avenant)
    {
        $req = "Select CA_LIGNE.*, desigComp.CONTMEMO DESIGNCOMP, titreAvant.CONTMEMO AVLIGNE, CA_LIGNE.NUMCDE as NO_CDE from HCA_LIGN as CA_LIGNE with (nolock) 
                left join MEMOS desigComp with (nolock) on desigComp.NODEMEMO = CA_LIGNE.NM_DCOMP  
                left join MEMOS titreAvant with (nolock)  on titreAvant.NODEMEMO = CA_LIGNE.TITREAV  
                WHERE NUMCDE = :value";
        
        $req .= " and LTRIM(RTRIM(CA_LIGNE.AVENANT)) = :avenant";
        $arrayReq[':avenant'] = $avenant==='0'? '':$avenant;
        $arrayReq[':value']=$code;
                        
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function listeLigneHistorisees($post)
    {
        $req = "SELECT 
                HCA_LIGN.*, 
                HCA_ENTE.*, 
                Acheteur.NOM as Acheteur_NOM,
                Societe.RAIS_SOC as Societe_RAIS_SOC,
                CentreEmet.RAIS_SOC as CentreEmet_RAIS_SOC,
                CentreLivr.RAIS_SOC as CentreLivr_RAIS_SOC
            FROM HCA_LIGN with (nolock)
            LEFT JOIN HCA_ENTE with (nolock) ON HCA_LIGN.NUMCDE = HCA_ENTE.NO_CDE
            LEFT JOIN HASTUTIL Acheteur with (nolock) ON Acheteur.CODE = HCA_ENTE.ACH
            LEFT JOIN SOCIETE Societe with (nolock) ON Societe.CODE_STE = HCA_ENTE.CODE_STE
            LEFT JOIN CENTRE CentreEmet with (nolock) ON CentreEmet.CODE_CTR = HCA_ENTE.CODE_EMET
            LEFT JOIN CENTRE CentreLivr with (nolock) ON CentreLivr.CODE_CTR = HCA_ENTE.CODE_LIV
        ";
        
        $arrayReq=[];
        $this->_listeLigneHistoriseesClause($req, $arrayReq, $post);
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $post
     * @return array<mixed>
     */
    public function listeLigneHistoriseesExport(array $post) :array
    {
        $req = "SELECT * 
            FROM HCA_LIGN with (nolock)
            LEFT JOIN HCA_ENTE with (nolock) ON HCA_LIGN.NUMCDE = HCA_ENTE.NO_CDE";
        $arrayReq = [];

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

    /**
     * @param array<mixed> $arrayReq
     * @param array<mixed> $post
     */
    private function _listeLigneHistoriseesClause(
        string &$req,
        array &$arrayReq,
        array $post
    ) :void {
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq = array_merge($arrayReq, $whereArray[1]);
            }
        }
    }
}
