<?php

namespace Modules\Contrat\Repository;

use Helpers\QueryBuilder\AbsRepository;

class CentreAutorise extends AbsRepository
{
        
    /**
     * Return all the liste of the transmitter center
     * @param array $post
     * @param string $codeacd
     * @return array<array<string>>
     */
    public function listCentreEmeteur(Array $post, $codeacd)
    {

        $valueacd = trim($codeacd);
        
        $req1 = "select COUNT(*) MARCHE from ACCCADRE with (nolock), MARCHSOC with (nolock) where ACCCADRE.MARCHE = MARCHSOC.CODEMARCH and  CODE_ACD = '".$valueacd."'";
        $typeMarche1 = $this->pdo->prepare($req1);
        $typeMarche1->execute();
        $marchesAcc1 = $typeMarche1->fetch(\PDO::FETCH_ASSOC)['MARCHE'];
                
        $arrayReq = [];
        $req="SELECT cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,SOC.CODE_STE, SOC.RAIS_SOC,CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTR with (nolock) WHERE CODE_ACD = '".$valueacd."' 
            and  ACCTR.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist
        FROM CENTRE cen with (nolock) inner join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE where 1=1  and cen.EMISSION = 0x54";
        
        if ($marchesAcc1) {
            $req .= " and cen.CODE_STE IN (select CODESOC from ACCCADRE with(nolock), MARCHSOC with(nolock) where ACCCADRE.MARCHE = MARCHSOC.CODEMARCH and  CODE_ACD = '".$valueacd."')";
        }
       
        if (isset($post['type']) && $post['type']=='marches') {
            $req .= " AND
            (
                CASE WHEN (SELECT STEMARCHE FROM MARCHES with (nolock)  WHERE CODE='".$post['idType']."')=''
                    THEN
                        CASE WHEN cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC  with (nolock) where CODEMARCH = '".$post['idType']."') or not exists (SELECT CODESOC FROM MARCHSOC with(nolock) where CODEMARCH = '".$post['idType']."')
                            THEN 1	
                            ELSE 0
                            END	
                    WHEN (SELECT STEMARCHE FROM MARCHES  with (nolock) WHERE CODE='".$post['idType']."')<>'' 
                    THEN 
                        CASE WHEN cen.CODE_STE = (SELECT STEMARCHE FROM MARCHES  with (nolock) WHERE CODE='".$post['idType']."') OR cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC with(nolock) where CODEMARCH = '".$post['idType']."')
                        THEN 1
                        ELSE 0
                        END
                    ELSE 0
                END =1
            )";
        }

        if (!$_SESSION['Session_UsrMultiSoc']) {
            $req .= " AND cen.CODE_STE IN (
                SELECT CODE_STE FROM HASTUTIL with (nolock) WHERE CODE= '".$_SESSION['Session_Login']."'
                UNION
                SELECT COD_STE FROM SOC_USR with (nolock) WHERE COD_USR= '".$_SESSION['Session_Login']."'
            )";
        }
        
        if (isset($post['filter_Code_Centre']) && $post['filter_Code_Centre'] != '') {
            $req .=" AND UPPER(cen.CODE_CTR) like  :value1";
            $arrayReq[':value1']=strtoupper('%'.$post['filter_Code_Centre'].'%');
        }
        
        if (isset($post['filter_Centre_Libelle']) && $post['filter_Centre_Libelle'] != '') {
            $req .=" AND UPPER(cen.RAIS_SOC) like :value2";
            $arrayReq[':value2']=strtoupper('%'.$post['filter_Centre_Libelle'].'%');
        }
        
        if (isset($post['filter_CODE_STE']) && $post['filter_CODE_STE'] != '') {
            $req .=" AND UPPER(cen.CODE_STE) like UPPER(:value3)";
            $arrayReq[':value3']= '%'.$post['filter_CODE_STE'].'%';
        }
        
        if (isset($post['filter_RAIS_SOC']) && $post['filter_RAIS_SOC'] != '') {
            $req .=" AND UPPER(cen.RAIS_SOC) LIKE UPPER(:value4) ";
            $arrayReq[':value4']= '%'.$post['filter_RAIS_SOC'].'%';
        }

        if (isset($post['autorise']) && $post['autorise'] != '' && $post['autorise'] == 'Oui') {
            $req .=" AND cen.CODE_CTR in (SELECT CODE_CTR FROM ACCTR with(nolock) WHERE CODE_ACD = '".$valueacd."' and ACCTR.CODE_CTR = cen.CODE_CTR)";
        }
        if (isset($post['autorise']) && $post['autorise'] != '' && $post['autorise'] == 'Non') {
            $req .=" AND cen.CODE_CTR not in (SELECT CODE_CTR FROM ACCTR with(nolock) WHERE CODE_ACD = '".$valueacd."' and ACCTR.CODE_CTR = cen.CODE_CTR)";
        }
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 3, 0, 1);
        
        if (count($whereArray)>  0) {
            $tri = (string) str_replace('Code_Centre', 'cen.CODE_CTR', $whereArray['tri']);
            $tri = (string) str_replace('Centre_Libelle', 'cen.RAIS_SOC', $tri);
            $req .= (string) str_replace(
                'Code_Centre',
                'cen.CODE_CTR',
                (string) str_replace(
                    'CODE_STE',
                    'SOC.CODE_STE',
                    (string) str_replace('RAIS_SOC', 'SOC.RAIS_SOC', $whereArray[0])
                )
            ).$tri;

            if (count($whereArray[1]) != 0) {
                $arrayReq=  array_merge($arrayReq, $whereArray[1]);
            }
        }

        return $this->fetchAssocAll($req, $arrayReq);
    }
    /**
     * Return the compplete liste of the delevery society transmitter
     * @param array $post
     * @param string $codeacd
     * @return array<array<string>>
     */
    public function listSocieteCentreLiv(Array $post, $codeacd) : array
    {
        
        $valueacd = trim($codeacd);
        
        $req1 = "select COUNT(*) MARCHE from ACCCADRE with (nolock), MARCHSOC with (nolock) where ACCCADRE.MARCHE = MARCHSOC.CODEMARCH and  CODE_ACD = '".$valueacd."'";
        $typeMarche = $this->pdo->prepare($req1);
        $typeMarche->execute();
        $marchesAcc = $typeMarche->fetch(\PDO::FETCH_ASSOC)['MARCHE'];
        
        $arrayReq = [];
        $req="SELECT cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,SOC.CODE_STE, SOC.RAIS_SOC,CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTRLIV with (nolock) WHERE CODE_ACD = '".$valueacd."'  and  
            ACCTRLIV.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist
        FROM CENTRE cen with (nolock) inner join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE
        where 1=1  and cen.LIVRAISON = 0x54";

        if ($marchesAcc) {
            $req .= " and cen.CODE_STE IN (select CODESOC from ACCCADRE with(nolock), MARCHSOC with(nolock) where ACCCADRE.MARCHE = MARCHSOC.CODEMARCH and  CODE_ACD = '".$valueacd."')";
        }
        
        if (isset($post['type']) && $post['type']=='marches') {
            $req .= " AND
            (
                CASE WHEN (SELECT STEMARCHE FROM MARCHES  with (nolock) WHERE CODE='".$post['idType']."')=''
                    THEN
                        CASE WHEN cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC  with (nolock) where CODEMARCH = '".$post['idType']."') or not exists (SELECT CODESOC FROM MARCHSOC with(nolock) where CODEMARCH = '".$post['idType']."')
                            THEN 1	
                            ELSE 0
                            END	
                    WHEN (SELECT STEMARCHE FROM MARCHES with(nolock) WHERE CODE='".$post['idType']."')<>'' 
                    THEN 
                        CASE WHEN cen.CODE_STE = (SELECT STEMARCHE FROM MARCHES with (nolock)  WHERE CODE='".$post['idType']."') OR cen.CODE_STE in (SELECT CODESOC FROM MARCHSOC with(nolock) where CODEMARCH = '".$post['idType']."')
                        THEN 1
                        ELSE 0
                        END
                    ELSE 0
                END =1
            )";
        }

        if (!$_SESSION['Session_UsrMultiSoc']) {
            $req .= " AND cen.CODE_STE IN (
                SELECT CODE_STE FROM HASTUTIL with (nolock) WHERE CODE= '".$_SESSION['Session_Login']."'
                UNION
                SELECT COD_STE FROM SOC_USR with (nolock) WHERE COD_USR= '".$_SESSION['Session_Login']."'
            )";
        }
        
        
        if (isset($post['filter_Code_Centre']) && $post['filter_Code_Centre'] != '') {
            $req .=" and UPPER(cen.CODE_CTR) like  :value1";
            $arrayReq[':value1']=strtoupper('%'.$post['filter_Code_Centre'].'%');
        }
        
        if (isset($post['filter_Centre_Libelle']) && $post['filter_Centre_Libelle'] != '') {
            $req .=" and UPPER(cen.RAIS_SOC) like :value2";
            $arrayReq[':value2']=strtoupper('%'.$post['filter_Centre_Libelle'].'%');
        }
        
        if (isset($post['filter_CODE_STE']) && $post['filter_CODE_STE'] != '') {
            $req .=" and UPPER(SOC.CODE_STE) like :value3";
            $arrayReq[':value3']=strtoupper('%'.$post['filter_CODE_STE'].'%');
        }
        
        if (isset($post['filter_RAIS_SOC']) && $post['filter_RAIS_SOC'] != '') {
            $req .=" and UPPER(SOC.RAIS_SOC) LIKE UPPER(:value4) ";
            $arrayReq[':value4']=$post['filter_RAIS_SOC'].'%';
        }
        
        if (isset($post['autorise']) && $post['autorise'] != '' && $post['autorise'] == ' ') {
            $req = '';
        }
        if (isset($post['autorise']) && $post['autorise'] != '' && $post['autorise'] == 'Oui') {
            $req .=" and cen.CODE_CTR in (SELECT CODE_CTR FROM ACCTR with(nolock) WHERE CODE_ACD = '".$valueacd."' and ACCTR.CODE_CTR = cen.CODE_CTR)";
        }
        if (isset($post['autorise']) && $post['autorise'] != '' && $post['autorise'] == 'Non') {
            $req .=" and cen.CODE_CTR not in (SELECT CODE_CTR FROM ACCTR with(nolock) WHERE CODE_ACD = '".$valueacd."' and ACCTR.CODE_CTR = cen.CODE_CTR)";
        }
        
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 3, 0, 1);
        
        if (count($whereArray)>  0) {
            $tri = (string) str_replace('Code_Centre', 'cen.CODE_CTR', $whereArray['tri']);
            $tri = (string) str_replace('Centre_Libelle', 'cen.RAIS_SOC', $tri);
            $req .= (string) str_replace(
                'Code_Centre',
                'cen.CODE_CTR',
                (string) str_replace(
                    'CODE_STE',
                    'SOC.CODE_STE',
                    (string) str_replace('RAIS_SOC', 'SOC.RAIS_SOC', $whereArray[0])
                )
            ).$tri;
                    
            if (count($whereArray[1]) != 0) {
                $arrayReq=  array_merge($arrayReq, $whereArray[1]);
            }
        }

        return $this->fetchAssocAll($req, $arrayReq);
    }
        
    /**
     * For test if an framework agreement and a center are present in the center liste
     * @param string $codeacd
     * @param string $codectr
     * @return array<string>
     */
    public function listByCodeAcd($codeacd, $codectr) : array
    {
        $req=" SELECT cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,*,
            CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTR with (nolock) WHERE CODE_ACD = '".$codeacd."' and  ACCTR.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist FROM CENTRE cen with (nolock) join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE where CODE_CTR = :value";
        
        $array[':value'] = $codectr;
        $typeMarche = $this->pdo->prepare($req);
        
        $typeMarche->execute($array);
        
        
        
        return $typeMarche->fetch(\PDO::FETCH_ASSOC);
    }
    
    /**
     * Return an unique array of an framework agreement code that are present in the center liste
     * @param string $codeacd
     * @param string $codectr
     * @return array<string>
     */
    public function listByCodeAcdCentreLivraison($codeacd, $codectr) : array
    {
        $req=" SELECT cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,*,
            CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTRLIV with (nolock) WHERE CODE_ACD = '".$codeacd."' and  ACCTRLIV.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist FROM CENTRE cen with (nolock) join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE where CODE_CTR = :value";
        
        $array[':value'] = $codectr;
        $typeMarche = $this->pdo->prepare($req);
        
        $typeMarche->execute($array);
        
        
        
        return $typeMarche->fetch(\PDO::FETCH_ASSOC);
    }
    /**
     * Return the detail of en society which is on changing mod
     * @param string $code
     * @param string $code_ste
     * @return array<string>
     */
    public function detailSocieteModification($code, $code_ste) : array
    {
        
        $req="SELECT * FROM SOCIETE soc with (nolock) left join MARCHSOC msoc with (nolock) on soc.CODE_STE= msoc.CODESOC and msoc.CODEMARCH like :value where soc.CODE_STE = :code_ste";
        
        $arrayReq = [':value' => '%'.$code.'%', ':code_ste' => $code_ste];
        $typeMarche = $this->pdo->prepare($req);
        
        $typeMarche->execute($arrayReq);
        
        
        
        return $typeMarche->fetch();
    }

    
    /**
     * Return if the transmitter center exist
     * @param string $codeacd
     * @return array<array<string>>
     */
    public function iCheckedCentreEmetteur($codeacd)
    {
        
        $valueacd = trim($codeacd);
       
        $req=" SELECT
        cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,*,
        CASE WHEN NOT EXISTS (SELECT CODE_CTR FROM ACCTR with (nolock) WHERE CODE_ACD = '".$valueacd."'  and  ACCTR.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist
        FROM CENTRE cen with (nolock) inner join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE";
        
        $arrayReq = [];
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * Return if the delivery center exist
     * @param string $codeacd
     * @return array<array<string>>
     */
    public function iCheckedCentreLivraison($codeacd)
    {
        
        $valueacd = trim($codeacd);
        
        $req=" SELECT
        cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,*,
        CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTRLIV with (nolock) WHERE CODE_ACD = '".$valueacd."'  and  ACCTRLIV.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist
        FROM CENTRE cen with (nolock) inner join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE";
        
        $arrayReq = [];
        return $this->fetchAssocAll($req, $arrayReq);
    }


    /**
     * Return all the center delivery and transmitter by his code id
     * @param string $codeAcd
     * @param string $societeUser
     * @return array<array<string>>
     */
    public function centreEmetteurBySoc($codeAcd, $societeUser)
    {
        $req="SELECT cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,SOC.CODE_STE, SOC.RAIS_SOC,CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTRLIV with (nolock) WHERE CODE_ACD = '".$codeAcd."'  and  
        ACCTRLIV.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist
        FROM CENTRE cen with (nolock) inner join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE
        
        where cen.CODE_STE = :societeUser";

        $arrayReq = [':societeUser' => $societeUser];
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * Return all the delivery society by his id binding by framework agreement
     * @param string $codeAcd
     * @param string $societeUser
     * @return array<array<string>>
     */
    public function centreLivraisonBySoc($codeAcd, $societeUser)
    {
        $req="SELECT cen.CODE_CTR as Code_Centre, cen.RAIS_SOC as Centre_Libelle,SOC.CODE_STE, SOC.RAIS_SOC,CASE WHEN EXISTS (SELECT CODE_CTR FROM ACCTRLIV with (nolock) WHERE CODE_ACD = '".$codeAcd."'  and  
        ACCTRLIV.CODE_CTR = cen.CODE_CTR)
        then 1 else 0 end as exist
        FROM CENTRE cen with (nolock) inner join SOCIETE SOC with (nolock) on SOC.CODE_STE = cen.CODE_STE
        
        where cen.CODE_STE = :societeUser";

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