<?php

namespace Modules\Suivi\Repository;

use Entity\Model\Hastutil;
use Entity\Model\Paravrac;
use Helpers\Additional\Specifique\Specifique;
use Helpers\Log\LogSysteme;
use Helpers\Outil\Roles;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;
use Helpers\Trad\ITrad;
use Modules\Suivi\MenuContextuel\SuiviCdeContextuel;
use Modules\Suivi\Service\RuptureService;
use Skeleton\Router;

class DuplicateRepo extends AbsRepository
{
    /**
     * @return array<mixed>
     */
    public function getListeLigneCdeForDuplicate(string $noCde)
    {
        $req = "select 
                CA_LIGNE.*, 
                CA_LIGNE.ACCORCA COD_ACD,
                CA_ENTET.CODE_FOU FOU_SAVE,
                CA_ENTET.CODE_FOU, 
                CA_ENTET.CTR_LIV,
                CA_LIGNE.CALI_RECEPAUTO_JOUR RECEPAUTO_JOUR,
                FOURNISS.ARTFRPORT, FOURNISS.RS,
                ARTICLES.ARTFRAISPORT,
                SSFAMILL.CODE_FAM,SSFAMILL.DESIGNAT LIB_SSFAM,
                (   select 
                        min(CA_PL_CT.DTE_LIV) 
                    from CA_PL_CT with(nolock) 
                    where  CA_PL_CT.NO_CA = CA_LIGNE.NO_CDE and CA_PL_CT.NO_PO_CA = CA_LIGNE.NO_PO
                ) DATE_LIVR,
                MEMOS.CONTMEMO DESCOMP
            from CA_LIGNE with (nolock)
            left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = CA_LIGNE.NO_CDE
            left join FOURNISS with (nolock) on FOURNISS.CODE =  CA_ENTET.CODE_FOU
            left join ARTICLES with (nolock) on ARTICLES.CODE_ART = CA_LIGNE.CODE_ART
            left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = CA_LIGNE.SSFAM
            left joiN MEMOS with (nolock) on MEMOS.NODEMEMO = CA_LIGNE.NM_DCOMP
            where CA_LIGNE.NO_CDE = :noCde";

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

    /**
     * @return array<mixed>
     */
    public function getListeLigneDaForDuplicate(string $noDa)
    {
        $req = "select 
                DA_LIGNE.*, 
                DA_LIGNE.ACD COD_ACD,
                DA_LIGNE.CODE_FOU FOU_SAVE,
                DA_LIGNE.CODE_FOU, DA_LIGNE.CTRLIVR,
                DA_LIGNE.DALI_RECEPAUTO_JOUR RECEPAUTO_JOUR,
                FOURNISS.ARTFRPORT, FOURNISS.RS,
                ARTICLES.ARTFRAISPORT,
                SSFAMILL.CODE_FAM, SSFAMILL.DESIGNAT LIB_SSFAM,
                (
                    SELECT 
                        MIN(DTE_LIV) 
                    FROM DA_PLANN WITH(NOLOCK) 
                    WHERE DA_PLANN.NO_DA = DA_LIGNE.NO_DA AND DA_PLANN.NO_PO = DA_LIGNE.NO_PO
                ) DATE_LIVR,
                MEMOS.CONTMEMO DESCOMP
            from DA_LIGNE with (nolock)
            left join FOURNISS with (nolock) on FOURNISS.CODE =  DA_LIGNE.CODE_FOU
            left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = DA_LIGNE.NO_DA
            left join ARTICLES with (nolock) on ARTICLES.CODE_ART = DA_LIGNE.CODE_ART
            left join SSFAMILL with (nolock) on SSFAMILL.CODE_SFA = DA_LIGNE.SSFAM
            left joiN MEMOS with (nolock) on MEMOS.NODEMEMO = DA_LIGNE.NM_DCOMP
            where DA_LIGNE.NO_DA = :noDa";

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

    /**
     * @param array<mixed> $array
     * @return array<mixed>
     */
    public function controleAcd($array)
    {
        $req = "SELECT * from ACCCADRE acc WITH (nolock) 
                where 1=1
                and CODE_ACD = :acd
                and 
                (
                    (
                        acc.TYPE_FG='F' 
                        and acc.CODE_FOUR = :codeFou1
                    )
                    or
                    (
                        acc.TYPE_FG='G' 
                        and acc.CODE_FOUR=(SELECT four.FOURPRIN FROM FOURNISS four WHERE four.CODE=:codeFou2) 
                        and exists(SELECT null FROM FS_ACD fs WHERE fs.CODE_ACD=acc.CODE_ACD AND fs.CODE_FOU=:codeFou3)
                    )
                )
                and acc.DATE_DEB <= :today_0
                and (acc.DATE_FIN >= :today_1 or acc.DATE_FIN = '00000000')
                and 
                (
                    (
                        exists (SELECT lien.CODE_ACD from ACCTR lien where lien.CODE_ACD=acc.CODE_ACD and lien.CODE_CTR=:ctr)
                        or
                        exists (SELECT lien.CODE_ACD from ACCTRLIV lien where lien.CODE_ACD=acc.CODE_ACD and lien.CODE_CTR=:ctrLivr)
                        or 
                        (
                            not exists (SELECT emet.CODE_ACD from ACCTR emet where emet.CODE_ACD=acc.CODE_ACD) 
                            and 
                            not exists (SELECT liv.CODE_ACD from ACCTRLIV liv where liv.CODE_ACD=acc.CODE_ACD)
                        )
                    )       
                )
                and 
                (
                    acc.MARCHE='' 
                    or 
                    (
                    (exists (select march.CODEMARCH from MARCHSOC march where acc.MARCHE=march.CODEMARCH and march.CODESOC=:societe)
                        or not exists (select march.CODEMARCH from MARCHSOC march where acc.MARCHE=march.CODEMARCH)
                    )
                    and
                        (
                            not exists (select * from MARCHUSR where CODE_MAR=acc.MARCHE)
                            or
                            exists (select * from MARCHUSR where CODE_MAR=acc.MARCHE and CODE_USR=:user)
                        )
                    )
                )
                and 
                (
                    not exists (SELECT * FROM ACART artacd WHERE artacd.CODE_ACD=acc.CODE_ACD AND artacd.CODE_ART=:codeArt1)
                    or
                    exists (SELECT * FROM ACART artacd2 WHERE artacd2.CODE_ACD=acc.CODE_ACD AND artacd2.CODE_ART=:codeArt2 AND artacd2.DDEBVAL<= '".date('Ymd')."' AND (artacd2.DFINVAL='00000000' OR artacd2.DFINVAL>='".date('Ymd')."'))
                )";
        $today = date('Ymd');
        $arrayReq = [
            ':acd' => $array['ACD'],

            ':codeFou1' => $array['CODE_FOU'],
            ':codeFou2' => $array['CODE_FOU'],
            ':codeFou3' => $array['CODE_FOU'],

            ':ctr'      => $array['EMETTEUR'],
            ':ctrLivr'  => $array['LIVRAISON'],

            ':societe' => $array['CODE_STE'],

            ':user'     => $array['USER'],

            ':codeArt1' => $array['CODE_ART'],
            ':codeArt2' => $array['CODE_ART'],

            ':today_0' => $today,
            ':today_1' => $today,
        ];
        
        return $this->fetchAssocOne($req, $arrayReq);
    }

    /**
     * @return array<mixed>
     */
    public function getInfoFrsCde(string $noCde)
    {
        $req = "select 
                    distinct COD_FOURN, CRITERE_REGROUP, 
                    MEMOS.CONTMEMO INSTRUCTION_FRS,
                    oxa_users_panier_ligne.CodeCtrLivraison,
                    CA_ENTET.COD_TAC,
                    CA_ENTET.MODE_PAIE,
                    CA_ENTET.COND_PAIE,
                    CA_ENTET.MODE_LIV,
                    CA_ENTET.COMM_LIBR MSG_STD,
                    FOURNISS.RECEP_AUTO,
                    FOURNISS.PER_REC_AUTO,
                    CTC_CTR.*
                from oxa_users_panier_ligne with (nolock)
                left join CA_ENTET with (nolock) on CA_ENTET.NO_CDE = :noCde and CA_ENTET.CODE_FOU = oxa_users_panier_ligne.COD_FOURN
                left join FOURNISS with (nolock) on FOURNISS.CODE = CA_ENTET.CODE_FOU
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = CA_ENTET.NM_TITRE
                left join CTC_CTR with (nolock) on CTC_CTR.COD_CTR = CodeCtrLivraison and CTC_IND = '0'
                where id_panier = :idPanier and id_user = :idUser";

        $arrayReq[':noCde'] = $noCde;
        $arrayReq[':idPanier'] = 0;
        $arrayReq[':idUser'] = $_SESSION['id_user'];

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

    /**
     * @return array<mixed>
     */
    public function getInfoFrsDa(string $noDa)
    {
        $req = "select 
                distinct COD_FOURN, 
                CRITERE_REGROUP, 
                MEMOS.CONTMEMO INSTRUCTION_FRS,
                oxa_users_panier_ligne.CodeCtrLivraison,
                FOUDACDE.COD_TAC,
                DA_ENTET.MODE_PAIE,
                DA_ENTET.COND_PAIE,
                DA_ENTET.MODE_LIV,
                DA_ENTET.CODE_MESS MSG_STD,
                FOURNISS.RECEP_AUTO,
                FOURNISS.PER_REC_AUTO,
                DOSACHCOMPLET DOSSIER_ACHAT_COMP,
                dossier.CONTMEMO COMMENT_SUIVI_ACHAT,
                CTC_CTR.*
            from oxa_users_panier_ligne with (nolock)
            left join DA_ENTET with (nolock) on DA_ENTET.NO_DA = :noDA
            left join FOUDACDE with (nolock) on FOUDACDE.NO_DA =  DA_ENTET.NO_DA and DA_ENTET.FOU = oxa_users_panier_ligne.COD_FOURN
            left join FOURNISS with (nolock) on FOURNISS.CODE = DA_ENTET.FOU
            left join MEMOS with (nolock) on MEMOS.NODEMEMO = DA_ENTET.NM_TITRE
            left join MEMOS dossier with (nolock) on dossier.NODEMEMO = FOUDACDE.NM_COMSUIVIACH
            left join CTC_CTR with (nolock) on CTC_CTR.COD_CTR = CodeCtrLivraison and CTC_IND = '0'
            where id_panier = :idPanier and id_user = :idUser";

        $arrayReq[':noDA'] = $noDa;
        $arrayReq[':idPanier'] = 0;
        $arrayReq[':idUser'] = $_SESSION['id_user'];

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

    /**
     * @param array<mixed> $listeNoOrdre
     * @return array<mixed>
     */
    public function getClauseDA(string $idDoc, $listeNoOrdre)
    {
        $arrayReq = [];
        $req = "select * from DA_CLAUS with (nolock)
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = DA_CLAUS.NM_PARAG
                where NO_DA = :noDA and ".$this->makeWhereIn('NO_ORDRE', $listeNoOrdre, $arrayReq);

        $arrayReq[':noDA'] = $idDoc;
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $listeNoOrdre
     * @return array<mixed>
     */
    public function getClauseCDE(string $idDoc, $listeNoOrdre)
    {
        $arrayReq = [];
        $req = "select * from CA_CLAUS with (nolock)
                left join MEMOS with (nolock) on MEMOS.NODEMEMO = CA_CLAUS.NM_PARAG
                where NO_CA = :noCA and ".$this->makeWhereIn('NO_ORDRE', $listeNoOrdre, $arrayReq);

        $arrayReq[':noCA'] = $idDoc;
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $listeNoOrdre
     * @return array<mixed>
     */
    public function getPJDA(string $idDoc, $listeNoOrdre)
    {
        $arrayReq = [];
        $req = "    select * from DA_PIJO with (nolock)
            where NO_DA = :noDA and ".$this->makeWhereIn('NO_ORDRE', $listeNoOrdre, $arrayReq);

        $arrayReq[':noDA'] = $idDoc;
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * @param array<mixed> $listeNoOrdre
     * @return array<mixed>
     */
    public function getPJCDE(string $idDoc, $listeNoOrdre)
    {
        $arrayReq = [];
        $req = "    select * from CA_PIJO with (nolock)
            where NO_CA = :noCA and ".$this->makeWhereIn('NO_ORDRE', $listeNoOrdre, $arrayReq);

        $arrayReq[':noCA'] = $idDoc;
        return $this->fetchAssocAll($req, $arrayReq);
    }
}
