<?php

namespace Modules\Panier\Repository;

use Helpers\QueryBuilder\AbsRepository;

class PanierFrs extends AbsRepository
{

    /**
     * @param array<mixed>|string $listeFrs
     * @return array<mixed>
     */
    public function getInfoFrsAndMainContact($listeFrs)
    {
        $arrayReq = [];
        $req = "
            select 
                FOURNISS.CODE,
                FOURNISS.MODE_PAIE,
                FOURNISS.COND_PAIE,
                FOURNISS.LIV, 
                FOURNISS.VILLE1,
                FOURNISS.ADR,
                FOURNISS.CPOSTAL,
                FOURNISS.CODE_PAYS,
                FOURNISS.RECEP_AUTO,
                FOURNISS.PER_REC_AUTO,
                t.NOM CTC_NOM, 
                t.FONCTION CTC_FCT, 
                t.TEL CTC_TEL, 
                t.FAX CTC_FAX, 
                t.TELPORT CTC_GSM, 
                t.EMAIL CTC_MAIL 
            from FOURNISS with(nolock) 
            OUTER APPLY (
                select 
                    top 1 * 
                from CONTFOUR with (nolock) 
                where CONTFOUR.CODE_FOU = FOURNISS.CODE and VIBOXAP=0x54 order by IND_FOU
            ) t 
            where ";
            
        if (is_array($listeFrs)) {
            $req .= $this->makeWhereIn('CODE', $listeFrs, $arrayReq);

            return $this->fetchAssocAll($req, $arrayReq);
        } else {
            $req .= " CODE = :codeFrs";
            $arrayReq[':codeFrs'] = $listeFrs;

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

    /**
     * Summary of getInfoCtcAdresse
     * @param string $codeFrs
     * @param string $codAdr
     * @return array<mixed>
     */
    public function getInfoCtcAdresse(string $codeFrs, string $codAdr)
    {
        $req = "select 
                    CASE COD_ADR 
                        WHEN '' THEN 1
                        ELSE 0
                    END prioAdr,
                    NOM CTC_NOM, 
                    FONCTION CTC_FCT, 
                    TEL CTC_TEL, 
                    FAX CTC_FAX, 
                    TELPORT CTC_GSM, 
                    EMAIL CTC_MAIL         
                from CONTFOUR with (nolock) 
                where CODE_FOU = :codeFrs and (COD_ADR = '' or COD_ADR = :codAdr)
                order by prioAdr, IND_FOU";

        $arrayReq[':codeFrs'] = $codeFrs;
        $arrayReq[':codAdr'] = ($codAdr=='ADR_FOU')? '':$codAdr;
        
        return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * Summary of getInfoCtcAdresse
     * @param string $codeFrs
     * @param string $codAdr
     * @return array<mixed>
     */
    public function getListeCtcByCodAdr(string $codeFrs, ?string $codAdr = null)
    {
        $req = "select 
                    CASE COD_ADR 
                        WHEN '' THEN 1
                        ELSE 0
                    END prioAdr,
                    IND_FOU,
                    NOM, 
                    FONCTION, 
                    TEL, 
                    FAX, 
                    TELPORT, 
                    EMAIL         
                from CONTFOUR with (nolock) 
                where CODE_FOU = :codeFrs  ".(is_null($codAdr)? '':" and (COD_ADR = '' or COD_ADR = :codAdr) ")."
                order by prioAdr, IND_FOU";

        $arrayReq[':codeFrs'] = $codeFrs;
        
        if (!is_null($codAdr)) {
            $arrayReq[':codAdr'] = ($codAdr=='ADR_FOU')? '':$codAdr;
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param string $critere
     * @return array<mixed>
     */
    public function getLibellesFrs($critere)
    {
        $req = "SELECT FOURNISS.RS, FOURNISS.CODE from oxa_users_panier_four with (nolock)
                left join FOURNISS with(nolock) on FOURNISS.CODE = oxa_users_panier_four.COD_FOURN
                where CRITERE_REGROUP = :critere";

        $arrayReq = [
            ':critere' => $critere
        ];
                
        return $this->fetchAssocOne($req, $arrayReq);
    }

    public function deleteGroupWithNoLine() :void
    {
        $req = "delete 
                from oxa_users_panier_four
                where id_panier = :id_panier
                and	id_user= :id_user
                and oxa_users_panier_four.CRITERE_REGROUP not in (
                    select distinct(CRITERE_REGROUP) from oxa_users_panier_ligne
                    where id_panier = oxa_users_panier_four.id_panier
                    and	id_user = oxa_users_panier_four.id_user
                )";
    
        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];

        $this->execute($req, $arrayReq);

        $req2 = "delete 
                from OXA_USERS_PANIER_FOUR_CONTACT
                where ID_PANIER = :id_panier
                and	ID_USER = :id_user
                and OXA_USERS_PANIER_FOUR_CONTACT.CRITERE_REGROUP not in (
                    select distinct(CRITERE_REGROUP) from oxa_users_panier_ligne
                    where id_panier = OXA_USERS_PANIER_FOUR_CONTACT.ID_PANIER
                    and	id_user = OXA_USERS_PANIER_FOUR_CONTACT.ID_USER
                )";

        $arrayReq2 = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];
        
        $this->execute($req2, $arrayReq2);
    }

    /**
     * @return array<mixed>
     */
    public function getListeDocCentreToDelete()
    {
        $req = "SELECT * from oxa_users_panier_four_pj with(nolock)
                where id_user = :id_user 
                and id_panier = :id_panier 
                and TABLE_DOCATT = 'CENTRE' 
                and exists (
                    select * 
                    from oxa_users_panier_four with (nolock) 
                    where id_user = oxa_users_panier_four_pj.id_user 
                    and id_panier = oxa_users_panier_four_pj.id_panier
                    and CRITERE_REGROUP = oxa_users_panier_four_pj.CRITERE_REGROUP
                    and oxa_users_panier_four.flg_add_pj = 0x54
                )";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];

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

    public function deleteDocCentre() : void
    {
        $req = "delete from oxa_users_panier_four_pj 
                where id_user = :id_user 
                and id_panier = :id_panier 
                and TABLE_DOCATT = 'CENTRE' 
                and exists 
                (
                    select * 
                    from oxa_users_panier_four with (nolock) 
                    where id_user = oxa_users_panier_four_pj.id_user 
                    and id_panier = oxa_users_panier_four_pj.id_panier
                    and CRITERE_REGROUP = oxa_users_panier_four_pj.CRITERE_REGROUP
                    and oxa_users_panier_four.flg_add_pj = 0x54
                )";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];

        $this->execute($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getAdresseFrsByBloc()
    {
        $req = "select 
                    COD_FOURN,
                    oxa_users_panier_four.CRITERE_REGROUP,
                    FOURNISS.VILLE1,
                    FOURNISS.ADR,
                    FOURNISS.CPOSTAL,
                    FOURNISS.CODE_PAYS
                from oxa_users_panier_four with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = COD_FOURN
                where id_user = :id_user and id_panier = :id_panier";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];

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

    public function frsProspectInPanier():string
    {
        $req = "select count(*) COUNT_FRS from oxa_users_panier_four with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = COD_FOURN
                where id_user = :id_user and id_panier = :id_panier and PROSPECT = 0x54";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];

        return $this->fetchAssocOne($req, $arrayReq)['COUNT_FRS']??'0';
    }

    /**
     * @return array<mixed>
     */
    public function getBlocFrsWithFraisPort(?string $critere = null, ?string $codeFou = null)
    {
        $req = "select 
                    FOURNISS.AUTOCC, CODE, RFRACT, FRPOBLIG, MNTMINFRA, ARTFRPORT,  
                    oxa_users_panier_four.CRITERE_REGROUP, oxa_users_panier_four.COD_FOURN,
                    fraisPort.qte, fraisPort.type_de_ligne, fraisPort.COD_ART artPresent, fraisPort.id_lig,
					ligneBloc.NB_LIGNE, ligneBloc.TOTAL    
                from oxa_users_panier_four with (nolock)
                left join FOURNISS with (nolock) on FOURNISS.CODE = COD_FOURN
                OUTER APPLY (
                    select top 1 qte, type_de_ligne,COD_ART, id_lig  from oxa_users_panier_ligne with (nolock)
                    where id_user = oxa_users_panier_four.id_user and id_panier = oxa_users_panier_four.id_panier and oxa_users_panier_four.CRITERE_REGROUP = oxa_users_panier_ligne.CRITERE_REGROUP
                    and COD_ART = FOURNISS.ARTFRPORT
                ) fraisPort
				OUTER APPLY (
					select 
					count (*) NB_LIGNE,
					sum(qte*PX_ART*((case when CONDITNT <> 0 and CONDITNT <> 1 and rtrim(ltrim(TXTCOND)) <>'' then CONDITNT else 1 end))) as TOTAL 
					from oxa_users_panier_ligne with (nolock) 
					where oxa_users_panier_four.CRITERE_REGROUP = oxa_users_panier_ligne.CRITERE_REGROUP and type_de_ligne != '3'
					and oxa_users_panier_ligne.id_user = oxa_users_panier_four.id_user and oxa_users_panier_ligne.id_panier = oxa_users_panier_four.id_panier
				) ligneBloc
                where oxa_users_panier_four.id_user = :id_user and oxa_users_panier_four.id_panier = :id_panier";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
        ];

        if ($critere) {
            $req .= " and oxa_users_panier_four.CRITERE_REGROUP = :critere";
            $arrayReq[':critere'] = $critere;
        }

        if ($codeFou) {
            $req .= " and oxa_users_panier_four.COD_FOURN = :codeFou";
            $arrayReq[':codeFou'] = $codeFou;
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function listeDocInit(string $societe = '', string $centreLvr = '', string $centreEmett = '', string $fournisseur = '')
    {

        $arrayReq = [];
        
        $req = "SELECT * from DOC_ATT with (nolock) 
                WHERE PJ_CDE = 0x54 
                AND (DT_PARUT = '00000000' OR DT_PARUT <= '".date('Ymd')."') 
                AND (DT_VALID = '00000000' OR DT_VALID >= '".date('Ymd')."') 
                AND (
                    ".($societe? "(NO_DA = 'SOCIETE' and COD_ENR = :societe ) or" : "")."
                    ".($centreLvr? "(NO_DA = 'CENTRE' and COD_ENR = :centrLiv ) or " : "")."
                    ".($centreEmett? "(NO_DA = 'CENTRE' and COD_ENR = :centrEmetteur ) or" : "")."
                    ".($fournisseur? "(NO_DA = 'FOURNISS' and COD_ENR = :fournisseur ) or" : "")."
                    1=0
                )";

        if ($societe) {
            $arrayReq[':societe'] = $societe;
        }

        if ($centreLvr) {
            $arrayReq[':centrLiv'] = $centreLvr;
        }

        if ($centreEmett) {
            $arrayReq[':centrEmetteur'] = $centreEmett;
        }

        if ($fournisseur) {
            $arrayReq[':fournisseur'] = $fournisseur;
        }

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

    public function deleteClauseTypeAchat(string $critere):void
    {
        $req = "delete from oxa_users_panier_four_clause 
                where id_user = :id_user and id_panier = :id_panier and CRITERE_REGROUP = :critere and TYP_ACH != ''";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
            ':critere' => $critere
        ];

        $this->execute($req, $arrayReq);
    }
    public function deletePjTypeAchat(string $critere):void
    {
        $req = "delete from oxa_users_panier_four_pj 
                where id_user = :id_user and id_panier = :id_panier and CRITERE_REGROUP = :critere and TYP_ACH != ''";

        $arrayReq = [
            ':id_panier' => $_SESSION['id_panier'],
            ':id_user' => $_SESSION['id_user'],
            ':critere' => $critere
        ];

        $this->execute($req, $arrayReq);
    }
}
