<?php

namespace Modules\Commande\Repository;

use Entity\Model\CaLigne;
use Entity\Model\CaPlCt;
use Entity\Model\CaPlPr;
use Entity\Model\Centre;
use Helpers\ChampsSupplementaire\ChampSupp;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Webservice\Wsdl;

class ModificationDetailCommande extends AbsRepository
{
    
    /**
     * retourne les clauses pour une ligne de DA
     * @param integer $noDA
     * @param integer $noLigne
     * @return mixed
     */
    public function detailLigneDA($noDA, $noLigne)
    {
        $req = "select DA_LIGNE.NO_DA, NO_PO NO_PO_DA, ARTFOURN.CODE_ART, DA_LIGNE.DES_SOMM, QTE_DEM QTE_COMM, UNITE, PU, DTELIVSOU DTELIVCON 
                from DA_LIGNE with (nolock) 
                left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
                left join ARTFOURN with (nolock) on ARTFOURN.CODE_ART = DA_LIGNE.CODE_ART and ARTFOURN.FOU = DA_ENTET.FOU
                where DA_LIGNE.NO_DA=:noDA and NO_PO=:noLigne";
        
        $arrayReq = [
            ':noDA' => $noDA,
            ':noLigne' => $noLigne,
        ];
                
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    public function detailLigne($noCde, $noLigne)
    {
        $req = "select NO_PO, NO_DA,NO_PO_DA, CODE_ART,DES_SOMM,UNITE,PU, QTE_COMM, DTELIVCON, NM_DCOMP, TITREAV
                from CA_LIGNE with (nolock) where NO_CDE=:noCde and NO_PO=:noLigne";
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':noLigne' => $noLigne,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch($this->pdo::FETCH_ASSOC);
    }
    
    public function allDetailLigne($noCde, $noLigne)
    {
        $req = "Select CA_LIGNE.*, desigComp.CONTMEMO DESIGNCOMP, 
                titreAvant.CONTMEMO AVLIGNE, desigComp.CONTMEMO DES_COMPL,articles.LIMITPERSTOCK,
                (select count(*) from CA_PL_CT with (nolock) where NO_CA = CA_LIGNE.NO_CDE and NO_PO_CA = CA_LIGNE.NO_PO ) PLANNING
                from 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
                left join ARTICLES articles with (nolock) on articles.CODE_ART = CA_LIGNE.CODE_ART
                WHERE NO_CDE = :noCde and NO_PO=:noLigne";
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':noLigne' => $noLigne,
        ];
        
        $commande = $this->pdo->prepare($req);
        $commande->execute($arrayReq);
        
        return $commande->fetch();
    }
    
    public function detailContactFrs($contact, $fournisseur, $typeFG)
    {
        
        if ($typeFG == 'F') {
            $req = "select * from CONTFOUR with (nolock) where CODE_FOU = :fournisseur and IND_FOU = :contact";
        } else {
            $req = "select * from CONTGF with (nolock) where CODE_GF = :fournisseur and IND_FOU = :contact";
        }
        
        $arrayReq = [
            ':contact'      => $contact,
            ':fournisseur'  => $fournisseur
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        return $requete->fetch();
    }
    
    public function infoFournisseur($fournisseur)
    {
        $req = "select TEL, FAX, EMAIL, TELEX from FOURNISS with (nolock) where CODE = :fournisseur";
        
        $arrayReq = [
            ':fournisseur'  => $fournisseur
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch();
    }
    
    public function deviseReference()
    {
        $req = "select HADEVSTK from PARAMETR with(nolock) where CLE = '1';";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
        return $requete->fetch()['HADEVSTK'];
    }
    
    /**
     * @param mixed $centre
     * @return array<string>
     */
    public function adresseCentre($centre)
    {
        return (new Centre($this->pdo))->find($centre);
    }
    
    public function contactCentre($centre)
    {
        $req = "select TOP 2 CTC_NOM, CTC_TEL, CTC_FAX,CTC_MAIL, CTC_GSM, CTC_FCT from CTC_CTR with (nolock) where COD_CTR=:centre and CTC_RCA = 0x54 order by CTC_IND";
        
        $arrayReq = [
            ':centre'  => $centre
        ];
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    public function detailContactCentre($centre, $contact)
    {
        $req = "select CTC_NOM, CTC_TEL, CTC_FAX,CTC_MAIL, CTC_GSM, CTC_FCT from CTC_CTR with (nolock) where COD_CTR=:centre and CTC_IND= :contact";
        
        $arrayReq = [
            ':centre'  => $centre,
            ':contact' => $contact,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch();
    }
    
    public function countBRsurCde($numCde)
    {
        $req = "select count(*) NUM_BR from BR_LIGNE with (nolock) where NUM_CDE= :numCde";
        
        $arrayReq = [
            ':numCde'  => $numCde,
        ];
        
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return $requete->fetch()['NUM_BR'];
    }
    
    public function getListeModeTransport()
    {
        $req = "SELECT MOD_TRS, TRAD_MDT.DES_ART 
        FROM MODTRANS WITH (NOLOCK) 
            LEFT JOIN TRAD_MDT WITH (NOLOCK) 
                ON TRAD_MDT.COD_ART = MODTRANS.MOD_TRS 
                    AND TRAD_MDT.COD_LNG = :codeLangue";
        
        $arrayReq = [
            ':codeLangue' => $_SESSION['Session_Langue'],
        ];

        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /*Controle accord-cadre */
    public function lienAccordCadreCentre($centre, $accordCadre, $mode)
    {
        if ($mode == 'EMETTEUR') {
            $filtre = "CENTRE.EMISSION";
        } elseif ($mode == 'LIVRAISON') {
            $filtre = "CENTRE.LIVRAISON";
        }
        // TODO : prepare & quote
        $req = "select 
                /* On recherche la presence de centre pour l'accord-cadre, si = 0 alors l'accord-cadre est disponible pour tout les centres */
                    (select COUNT(*) from ACCTR with (nolock)
                    join CENTRE with (nolock) on ACCTR.CODE_CTR = CENTRE.CODE_CTR and $filtre = 'T'
                    where CODE_ACD='$accordCadre') PRESENSE_CENTRE_POUR_ACD,
                /* Si il est != 0, alors on recherche un lien eventuelle entre l'accord-cadre et le centre */
                    (select COUNT(*) from ACCTR with (nolock)
                    join CENTRE with (nolock) on ACCTR.CODE_CTR = CENTRE.CODE_CTR and $filtre = 'T'
                    where CODE_ACD='$accordCadre' AND CENTRE.CODE_CTR='$centre') LIEN_ACD_CENTRE_PRESENT";
        
        $requete = $this->pdo->prepare($req);
        $requete->execute();
        return $requete->fetch();
    }
    
    /* update index ligne cde */
    public function modificationOrdreCde($noCde, $newPosition, $oldPosition, $moveUp)
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        //deplace la ligne qui a été deplacer a la ligne 999 999 999
        $req = "UPDATE CA_LIGNE SET NO_PO='999999999' WHERE NO_CDE = :noCde and NO_PO = :oldPosition";
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        
        //on ré-index toute les lignes de commande entre la nouvelle et l'ancien ligne
        if ($moveUp) {
            $req = "UPDATE CA_LIGNE SET NO_PO=NO_PO+1 WHERE NO_CDE = :noCde and (NO_PO <= :oldPosition and NO_PO >= :newPosition)";
        } else {
            $req = "UPDATE CA_LIGNE SET NO_PO=NO_PO-1 WHERE NO_CDE = :noCde and (NO_PO >= :oldPosition and NO_PO <= :newPosition)";
        }
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
            ':newPosition' => $newPosition,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);


        //on place la ligne qui a été deplacer a sa nouvelle position
        $req = "UPDATE CA_LIGNE SET NO_PO=:newPosition WHERE NO_CDE = :noCde and NO_PO = '999999999'";
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':newPosition' => $newPosition,
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    /* update index CS ligne cde */
    public function modificationOrdreCdeCS($noCde, $newPosition, $oldPosition, $moveUp)
    {
        $prevId = ChampSupp::autoCompletCodeEnr($noCde, $oldPosition);
        $newIdCS = ChampSupp::autoCompletCodeEnr($noCde, $newPosition);
        
        $this->execute(
            "UPDATE CS_DON SET COD_TAB = 'TMP_CA_LIGNE'
            WHERE COD_TAB = 'CA_LIGNE' and COD_ENR = :code_enr",
            [':code_enr' => $prevId,]
        );

        //on ré-index toute les lignes de commande entre la nouvelle et l'ancien ligne
        if ($moveUp) {
            $req = "UPDATE CS_DON SET COD_ENR = RIGHT('000000000000'+ CONVERT(VARCHAR(50),COD_ENR+1),12)  
                WHERE COD_TAB='CA_LIGNE'
                    and CAST(SUBSTRING(COD_ENR,0,10) as INTEGER) = :noCde 
                    and CAST(SUBSTRING(COD_ENR,10,3) as INTEGER) >= :newPosition 
                    and CAST(SUBSTRING(COD_ENR,10,3) as INTEGER) <= :oldPosition";
        } else {
            $req = "UPDATE CS_DON SET COD_ENR = RIGHT('000000000000'+ CONVERT(VARCHAR(50),COD_ENR-1),12) 
                WHERE COD_TAB='CA_LIGNE'
                    and CAST(SUBSTRING(COD_ENR,0,10) as INTEGER) = :noCde 
                    and CAST(SUBSTRING(COD_ENR,10,3) as INTEGER) >= :oldPosition 
                    and CAST(SUBSTRING(COD_ENR,10,3) as INTEGER) <= :newPosition";
        }
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
            ':newPosition' => $newPosition,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        
        //on place la ligne qui a été deplacer a sa nouvelle position
        $this->execute(
            "UPDATE CS_DON SET COD_TAB = 'CA_LIGNE', COD_ENR = :newIdCS 
            WHERE COD_TAB = 'TMP_CA_LIGNE' and COD_ENR = :prevId",
            [
                'prevId' => $prevId,
                'newIdCS' => $newIdCS,
            ]
        );
    }

        /* update index CC ligne cde */
    public function modificationOrdreCdeCC(string $noCde, string $idPanier, string $newPosition, string $oldPosition, bool $moveUp) : void
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        //deplace la ligne qui a été deplacer a la ligne 999 999 999
        $req = "UPDATE CC_LDOC 
                SET DOC_LINE_NUM='999999999' 
                WHERE DOC_TYPE = CA' and DOC_NUM = :noCde and DOC_LINE_NUM = :oldPosition and ID_USER_BASKET = :idPanier";
            
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
            'idPanier'   => $idPanier,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    
            
        //on ré-index toute les lignes de commande entre la nouvelle et l'ancien ligne
        if ($moveUp) {
            $req = "UPDATE CC_LDOC 
                    SET DOC_LINE_NUM=DOC_LINE_NUM+1 
                    WHERE DOC_TYPE = 'CA' and ID_USER_BASKET = :idPanier and DOC_NUM = :noCde and (DOC_LINE_NUM <= :oldPosition and DOC_LINE_NUM >= :newPosition)";
        } else {
            $req = "UPDATE CC_LDOC 
                    SET DOC_LINE_NUM=DOC_LINE_NUM-1 
                    WHERE DOC_TYPE = 'CA' and ID_USER_BASKET = :idPanier and DOC_NUM = :noCde and (DOC_LINE_NUM >= :oldPosition and DOC_LINE_NUM <= :newPosition)";
        }
            
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
            ':newPosition' => $newPosition,
            'idPanier'   => $idPanier,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    
    
        //on place la ligne qui a été deplacer a sa nouvelle position
        $this->execute(
            "UPDATE CC_LDOC 
            SET DOC_LINE_NUM=:newPosition 
            WHERE DOC_TYPE = 'CA' 
                and DOC_NUM = :noCde 
                and DOC_LINE_NUM = '999999999'
                and ID_USER_BASKET = :idPanier",
            [
                ':noCde' => $noCde,
                ':newPosition' => $newPosition,
                ':idPanier'   => $idPanier,
            ]
        );
    }

    /* update index ligne cde */
    public function modificationOrdrePlanningCde(string $noCde, string $newPosition, string $oldPosition, bool $moveUp) : void
    {
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        //deplace la ligne qui a été deplacer a la ligne 999 999 999
        $req = "UPDATE CA_PL_CT SET NO_PO_CA='999999999' WHERE NO_CA = :noCde and NO_PO_CA = :oldPosition";
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);

        
        //on ré-index toute les lignes de commande entre la nouvelle et l'ancien ligne
        if ($moveUp) {
            $req = "UPDATE CA_PL_CT SET NO_PO_CA=NO_PO_CA+1 WHERE NO_CA = :noCde and (NO_PO_CA <= :oldPosition and NO_PO_CA >= :newPosition)";
        } else {
            $req = "UPDATE CA_PL_CT SET NO_PO_CA=NO_PO_CA-1 WHERE NO_CA = :noCde and (NO_PO_CA >= :oldPosition and NO_PO_CA <= :newPosition)";
        }
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':oldPosition' => $oldPosition,
            ':newPosition' => $newPosition,
        ];
        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);


        //on place la ligne qui a été deplacer a sa nouvelle position
        $req = "UPDATE CA_PL_CT SET NO_PO_CA=:newPosition WHERE NO_CA = :noCde and NO_PO_CA = '999999999'";
        
        $arrayReq = [
            ':noCde' => $noCde,
            ':newPosition' => $newPosition,
        ];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
    }
    
    
    public function getNombreLigneCde(string $noCde) :int
    {
        $req = "SELECT count(*) as NB_LIGNE 
            FROM CA_LIGNE with (nolock)  
            WHERE NO_CDE = :noCde";
        
        $arrayReq = [':noCde' => $noCde];

        $requete = $this->pdo->prepare($req);
        $requete->execute($arrayReq);
        return intval($requete->fetch()['NB_LIGNE']??0);
    }

    /**
     * @param array<mixed> $arrayChaps
     * @param array<string|int> $listeNoPO
     */
    public function updateMasse(string $idCde, $arrayChaps, array $listeNoPO) : void
    {
        $param = [];
        $count = 1;
        $listSetField = [];
        foreach ($arrayChaps as $field => $value) {
            if (str_starts_with($field, 'MASQ_')) {
                $value = Sql::setBoolSql($value);
            }

            $key = ':val_' . $count;
            $listSetField[ $key ] = $field;
            $param[ $key ] = $value;
            $count++;
        }

        $req = "UPDATE CA_LIGNE SET "
            . implode(',', array_map(function ($field, $placeholder) {
                return Sql::quoteColumn($field) . ' = ' . $placeholder;
            }, $listSetField, array_keys($listSetField)))
            ." WHERE NO_CDE = :noCde "
            . (!empty($listeNoPO) ?
                " AND " . $this->makeWhereIn('NO_PO', $listeNoPO, $param) : ''
            );
        
        $this->execute($req, $param + ['noCde' => $idCde]);

        // Delete planning
        if (isset($arrayChaps['DTELIVCON'])) {
            // TODO : supporte "where in" for QueryBuilder and merge queries
            foreach ($listeNoPO as $id) {
                (new CaPlCt($this->pdo))->delete([
                    'NO_CA' => $idCde,
                    'NO_PO_CA' => $id,
                ]);
            }

            $mapLigne = array_column(
                (new CaLigne($this->pdo))->findAll(['NO_CDE' => $idCde]),
                null,
                'NO_PO'
            );

            $this->multiInsert('CA_PL_CT', array_map(function ($id) use ($idCde, $mapLigne) {
                $daLigne = $mapLigne[ $id ];
                return [
                    'NO_CA' => $idCde,
                    'NO_PO_CA' => $id,
                    'DTE_LIV' => $daLigne['DTELIVCON'],
                    'QTE_COMM' => $daLigne['QTE_COMM'],
                    'CODE_STE' => $daLigne['CODE_STE']
                ];
            }, $listeNoPO));
        }
    }

    public function procDecumul(string $commandeId) :void
    {
        $this->procCdeCumulCumul($commandeId, false);
    }
    
    public function procCumul(string $commandeId) :void
    {
        $this->procCdeCumulCumul($commandeId, true);
    }

    private function procCdeCumulCumul(string $commandeId, bool $sens) :void
    {
        $param = [
            'lang' => $_SESSION['Session_Langue'],
            'user' => $_SESSION['Session_Login'],
            'commandeId' => $commandeId,
            'sens' => $sens ? 1 /* CUMUL */ : -1 /* DECUMUL */,
        ];
        Wsdl::XmlEntities($param);

        $xml = '
        <CdeCumul_In LngUtilisation="'.$param['lang'].'">
            <Utilisateur>'.$param['user'].'</Utilisateur>
            <NumCde>'.$param['commandeId'].'</NumCde>
            <Sens>'.$param['sens'].'</Sens>
        </CdeCumul_In>
        ';
        $Wsdl = (new Wsdl(
            '',
            'OXAPRC_CDE_CUMULDECUMUL',
            $xml,
            false
        ));

        $response = new \SimpleXMLElement($Wsdl->responseData);
        if ($response->Statut != 'O') {
            throw new \Exception($response->MsgErreur);
        }
    }
}
