<?php

namespace Modules\Contrat\Repository;

use Entity\Model\Paravrac;
use Helpers\Outil\Sql;
use Helpers\QueryBuilder\AbsRepository;

class Uo extends AbsRepository
{

    public function recherche(array $post)
    {
        
        $req="select * from  UO with (nolock) ";
        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 0, 0, 0);
        
        
        
        if (count($whereArray)>  0) {
            $req .= $whereArray[0].$whereArray['tri'];
            $typeMarche = $this->pdo->prepare($req);
            
            $arrayReq = [];
            
            if (count($whereArray[1]) != 0) {
                $arrayReq=  array_merge($arrayReq, $whereArray[1]);
            }
                
                $typeMarche->execute($arrayReq);
                
                return  $typeMarche->fetchAll();
        }
    }
    
    /**
     * Add an AO to the database
     * @param array $array
     * @return true
     */
    public function ajouterUo(Array $array) : bool
    {
        
        $arrayReq =
        [
            ':CODE_UO' => $array['CODE_UO'],
            ':CODE_ACD' => $array['idacc']
        ];
        if (array_key_exists('CODE_STE', $array)) {
            $arrayReq['CODE_STE'] = $array['CODE_STE'];
            $req = "INSERT INTO LSTACDPARUO (CODE_UO, CODE_STE, CODE_ACD) VALUES ( :CODE_UO, :CODE_STE, :CODE_ACD)";
        } else {
            $req = "INSERT INTO LSTACDPARUO (CODE_UO, CODE_ACD) VALUES ( :CODE_UO, :CODE_ACD)";
        }

        $delais = $this->pdo->prepare($req);
        $delais->execute($arrayReq);
        
        return true;
    }

    /**
     * Delete an UO with his id
     * @param string $code_uo
     * @param string $code_ste
     * @param string $code_acd
     * @return bool
     */
    public function deleteUo(string $code_uo, string $code_ste = '', string $code_acd = '') : bool
    {
        $req="DELETE from LSTACDPARUO
        WHERE CODE_ACD = :CODE_ACD and CODE_UO = :CODE_UO";

        if (Sql::boolSql(Sql::paravrac($this->pdo, 'PARAMSTE', 'UOSECTO')) == 1) {
            $req .= " AND CODE_STE= :CODE_STE ";

            $arrayReq =
            [
                ':CODE_ACD' => $code_acd ,
                ':CODE_STE' => $code_ste,
                ':CODE_UO' => $code_uo
            ];
        } else {
            $arrayReq =
            [
                ':CODE_ACD' => $code_acd ,
                ':CODE_UO' => $code_uo
            ];
        }

        $del = $this->pdo->prepare($req);
        $del->execute($arrayReq);
        
        return true;
    }

    /**
     * Return the list of all UO
     * @param string $code
     * @param array<mixed> $post
     * @return array<array<string>>
     */
    public function listeUo($code, Array $post) : array
    {
        
        // $req="SELECT * from UO INNER JOIN LSTACDPARUO uoAcd on uoAcd.CODE_UO = UO.CODE_UO
        // WHERE uoAcd.CODE_ACD = :value";
          
        $req="SELECT *, (
                SELECT LIB_UO from UO with(nolock) 
                where CODE_STE = ls.CODE_STE and CODE_UO = ls.CODE_UO
            ) LIB_UO 
        from LSTACDPARUO ls with(nolock) 
        WHERE CODE_ACD = :value";
        $arrayReq[':value'] =  $code;
        
   
        if (isset($post['filter_CODE_UO']) && $post['filter_CODE_UO'] != '') {
            $req .=" and UPPER(ls.CODE_UO) like :value0";
            $arrayReq[':value0']=strtoupper('%'.$post['filter_CODE_UO'].'%');
        }
        if (sql::boolSql(Sql::paravrac($this->pdo, 'PARAMSTE', 'UOSECTO')) == 1) {
            if (isset($post['filter_CODE_STE']) && $post['filter_CODE_STE'] != '') {
                $req .=" and UPPER(ls.CODE_STE) like :value1";
                $arrayReq[':value1']=strtoupper('%'.$post['filter_CODE_STE'].'%');
            }
        } else {
            if (isset($post['filter_CODE_STE']) && $post['filter_CODE_STE'] != '') {
                $req .=" and UPPER(ls.CODE_STE) = ''";
            }
        }
        if (isset($post['filter_LIB_UO']) && $post['filter_LIB_UO'] != '') {
            $req .=" and UPPER(ls.LIB_UO) like :value2";
            $arrayReq[':value2']= '%'.$post['filter_LIB_UO'].'%';
        }

        
        $whereArray = \Helpers\Filter\FilterTable::splitFilter($post, 3, 0, 1);
        
        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);
    }
    /**
     * Return list UO where the society and the framework agreement is inside
     * @param array<string> $post
     * @return array<mixed>
     */
    public function existUo(array $post) : ?array
    {
        if (array_key_exists('CODE_STE', $post)) {
            $req="SELECT * from LSTACDPARUO
            WHERE CODE_UO = :code_uo and CODE_STE = :code_ste and CODE_ACD = :code_acd";
    
    
            $arrayReq =
            [
                ':code_uo' => $post['CODE_UO'],
                ':code_acd'  => $post['idacc'],
                ':code_ste' => $post['CODE_STE']
            ];
        } else {
            $req = "SELECT * from LSTACDPARUO
            WHERE CODE_UO = :code_uo and CODE_ACD = :code_acd";
    
    
            $arrayReq =
            [
                ':code_uo' => $post['CODE_UO'],
                ':code_acd'  => $post['idacc'],
            ];
        }
        
        return $this->fetchAssocAll($req, $arrayReq);
    }
    /**
     * listExportUo
     * @param string $code
     * @return array<array<string>>
     */
    public function listExportUo(string $code) : array
    {
        
        $req="SELECT *, (SELECT LIB_UO from UO with(nolock) where CODE_STE = ls.CODE_STE and CODE_UO = ls.CODE_UO) LIB_UO from LSTACDPARUO ls
        WHERE CODE_ACD = :value";
        $arrayReq[':value'] =  $code;
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    /**
     * rqSqlGetUOLieeUtilisateur
     * @param string $CODE_STE
     * @param string $CODE_USER
     * @return array<mixed>
     */
    public function rqSqlGetUOLieeUtilisateur(string $CODE_STE, string $CODE_USER) : ?array
    {
        $sql = "SELECT * from LSTUOPARUSR with (nolock) where CODE_USER = :CODE_USER and CODE_STE = :CODE_STE and CODE_UO != ''";

        $arrayReq =
        [
            ':CODE_STE' => $CODE_STE,
            ':CODE_USER'  => $CODE_USER
        ];
        return $this->fetchAssocAll($sql, $arrayReq);
    }
    
  
    /**
     * sqlUOSteSectoRechercheResponsable
     * @param string $code_user
     * @param ?string $code_ste
     * @return array<mixed>
     */
    public function sqlUOSteSectoRechercheResponsable(string $code_user, ?string $code_ste = null) : ?array
    {
        $arrayReq = [
            'userCode_0' => $code_user,
            'userCode_1' => $code_user,
            'userCode_2' => $code_user,
        ];
        if (! is_null($code_ste)) {
            $req = " SELECT CODE_STE, CODE_UO from HASTUTIL where CODE=:userCode_0 AND CODE_STE = :code_ste_0
                union
                select CODE_STE,CODE_UO from LSTUOPARUSR where CODE_USER=:userCode_1 AND CODE_STE = :code_ste_1
                union
                select CODE_STE,CODE_UO from UO where RESP_HIER=:userCode_2 AND CODE_STE = :code_ste_2";

            $arrayReq += [
                ':code_ste_0' => $code_ste,
                ':code_ste_1' => $code_ste,
                ':code_ste_2' => $code_ste,
            ];
        } else {
            $req = " SELECT CODE_STE, CODE_UO from HASTUTIL where CODE=:userCode_0
                union
                select CODE_STE,CODE_UO from LSTUOPARUSR where CODE_USER=:userCode_1
                union
                select CODE_STE,CODE_UO from UO where RESP_HIER=:userCode_2";
        }
        
        $req .= " ORDER BY CODE_UO";
        return $this->fetchAssocAll($req, $arrayReq);
    }
    
    /**
     * Return all the UO from responsable
     * @param string $cod_rech
     * @param string $lib_rech
     * @param string $user
     * @param bool $exitUser
     * @return array<array<string>>
     */
    public function sqlUOSteRechercheResponsable(string $cod_rech = '', string $lib_rech = '', string $user = '', bool $exitUser = false) : array
    {
        
        $exist = '';
        
        if ($exitUser) {
            $exist .= "and 
				not EXISTS  (
					select CODE_UO from LSTUOPARUSR with (nolock) where CODE_USER = :CODE_USER 
				) ";
            $arrayReq[':CODE_USER'] = 'CSABATIER';
        }

            
            
        $req = "select * from UO with (nolock)
				where CODE_STE = ' ' 
				and 
				(";

        if ($user != '') {
            $req .=" AND UPPER(RESP_HIER) = :value1";
            $arrayReq[':value1']=strtoupper('%'.$user.'%');
        }
        if ($cod_rech != '') {
            $req .=" AND UPPER(CODE_UO) LIKE :value2";
            $arrayReq[':value2']=strtoupper('%'.$cod_rech.'%');
        }
        if ($lib_rech != '') {
            $req .=" AND UPPER(LIB_UO) like :value3";
            $arrayReq[':value3']=strtoupper('%'.$lib_rech.'%');
        }

        $req .= $exist.' order by CODE_UO)';
        
        return $this->fetchAssocAll($req, $arrayReq);
    }

    public function getFilleUo(&$listUoArray, $uoTmp = '', $first = 1, &$isSecto = 0, $listeUoDb = [], $uoEnCours = '')
    {
        $uoRattacher = "";
        $ste = $_SESSION['Session_SocUsr'];

        if ($first) {
            $isSecto = Sql::boolSql(Sql::paravrac($this->pdo, 'PARAMSTE', 'UOSECTO'));
            if (is_array($uoTmp)) {
                $i=0;
                foreach ($uoTmp as $uoTmpA) {
                    if (trim($uoTmpA['CODE_UO'])[0] !="'") {
                        if ($i!=0) {
                            $uoRattacher .= ",'".$uoTmpA['CODE_UO']."'";
                        } else {
                            $uoRattacher .= "'".$uoTmpA['CODE_UO']."'";
                        }
                    } else {
                        if ($i!=0) {
                            $uoRattacher .= ",".$uoTmpA['CODE_UO'];
                        } else {
                            $uoRattacher .= $uoTmpA['CODE_UO'];
                        }
                    }

                    $i++;
                }
            } else {
                $uoRattacher .= $uoTmp;
            }

            $arrayReq = [];

            if ($isSecto != 1) {
                $req = 'SELECT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with (nolock) order by CODE_UO';
            } else {
                $req = 'SELECT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with (nolock) where CODE_STE = :ste order by CODE_UO';
                $arrayReq[':ste']=$ste;
            }
                $uoDb = $this->pdo->prepare($req);
            
                $uoDb->execute($arrayReq);
                
                $listeUoDb = $uoDb->fetchAll(\PDO::FETCH_ASSOC);
           
    

            if ($isSecto != 1) {
                $req = 'SELECT DISTINCT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with (nolock) where CODE_UO in ('.$uoRattacher.') order by CODE_UO';
            } else {
                $req = "SELECT DISTINCT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with (nolock) where CODE_UO in (".$uoRattacher.") and CODE_STE = :ste order by CODE_UO";
            }
            
            $uoR = $this->fetchAssocAll($req, $arrayReq);

            foreach ($uoR as &$uoRt) {
                $conteur = 0;
                foreach ($listUoArray as $tmpp) {
                    if ($tmpp['CODE_UO'] == $uoRt['CODE_UO']) {
                        $conteur++;
                    }
                }
                if ($conteur == 0) {
                    $listUoArray[] = [
                        'CODE_UO' =>  $uoRt['CODE_UO'],
                        'LIB_UO' => $uoRt['LIB_UO'],
                        'CODE_STE' =>  $uoRt['CODE_STE'],
                        'UO_MERE' => $uoRt['UO_MERE'],
                        'NIVEAU' => $uoRt['NIVEAU']
                    ];
                    $this->getFilleUo($listUoArray, $uoTmp, $first = 0, $isSecto, $listeUoDb, $uoRt['CODE_UO']);
                }
            }
        } else {
            foreach ($listeUoDb as $listUOt) {
                if (trim($uoEnCours)!= '') {
                    if ($listUOt['UO_MERE'] == $uoEnCours) {
                        $conteur = 0;
                        foreach ($listUoArray as $tmpp) {
                            if ($tmpp['CODE_UO'] == $listUOt['CODE_UO']) {
                                $conteur++;
                            }
                        }
                        
                        if ($conteur == 0) {
                            $listUoArray[] = [
                                'CODE_UO' =>  $listUOt['CODE_UO'],
                                'LIB_UO' => $listUOt['LIB_UO'],
                                'CODE_STE' =>  $listUOt['CODE_STE'],
                                'UO_MERE' => $listUOt['UO_MERE'],
                                'NIVEAU' => $listUOt['NIVEAU']
                            ];
                            $this->getFilleUo($listUoArray, $uoTmp, $first = 0, $isSecto, $listeUoDb, $listUOt['CODE_UO']);
                        }
                    }
                }
            }
        }
    }

    public function getAllOfUO($uo_list = '')
    {
        $ste = $_SESSION['Session_SocUsr'];
        $isSecto = Sql::boolSql(Sql::paravrac($this->pdo, 'PARAMSTE', 'UOSECTO'));
        if (trim($uo_list) == '' || trim($uo_list) == "''") {
             $req = 'SELECT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with (nolock)';
        } else {
            $uo_parent_list = $this->getAllUOMere();

            if (is_array($uo_parent_list)) {
                $arrayUOParentList = array();
                foreach ($uo_parent_list as $values) {
                    $UO_MERE = (string)$values['UO_MERE'];
                    if (! in_array($UO_MERE, $arrayUOParentList)) {
                        $arrayUOParentList[] = $UO_MERE;
                    }
                }
            }
    
            $all_uo_list = $this->array1DToSqlStr($arrayUOParentList);

            if ($isSecto) {
                $req = 'SELECT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with(nolock) WHERE UO_MERE IN ('. $uo_list .') AND CODE_STE = \''.$ste.'\'';
                ;
            } else {
                $req = 'SELECT CODE_UO, CODE_STE, LIB_UO, UO_MERE, NIVEAU FROM UO with(nolock) WHERE UO_MERE IN ('. $uo_list .')';
            }
            if (trim($all_uo_list) != '') {
                $req .= ' AND UO_MERE IN ('.$all_uo_list.') 
                    OR CODE_UO IN ('.$all_uo_list.')
                    ';
            }
        }

        $req .= " order by CODE_UO";

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

    /**
     * Return all the mommy unique in the UO database
     * @return array<mixed>
     */
    public function getAllUOMere() : ?array
    {
        $req = "SELECT DISTINCT UO_MERE FROM UO with(nolock) WHERE UO_MERE != '' order by CODE_UO";

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

    /**
     * Return all uo where the framework agreement is not null
     * @return array<mixed>
     */
    public function getAllAcdUo() : array
    {
        $req = "SELECT lst.CODE_ACD FROM UO with(nolock) left join LSTACDPARUO lst with(nolock) on lst.CODE_UO = UO.CODE_UO where lst.CODE_ACD IS NOT NULL order by CODE_UO";

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

    public function array1DToSqlStr($array)
    {
        $str = '';

        if (is_array($array)) {
            foreach ($array as $value) {
                if (! is_null($value) && !is_array($value)) {
                    if (strpos(trim($value), "'") ===0) {
                        $str .= ($str == '' ? '' : ','). $value;
                    } else {
                        $str .= ($str == '' ? '' : ',') . '\'' . $value. '\'';
                    }
                }
                if (is_array($value)) {
                    foreach ($value as $v) {
                        if (strpos(trim($v), "'") ===0) {
                            $str .= ($str == '' ? '' : ',').$v;
                        } else {
                            $str .= ($str == '' ? '' : ',') . '\'' . $v. '\'';
                        }
                    }
                }
            }
        }
        return $str;
    }

    public function getUOResponsableStr()
    {
        return implode(',', array_map(function ($s) {
            return "'".$s."'";
        }, $this->getUOResponsable()));
    }

    /**
     * @return array<string>
     */
    public function getUOResponsable()
    {
        $user = $_SESSION['Session_Login'];
        $ste = (new Paravrac($this->pdo))->getFlag('PARAMSTE', 'UOSECTO') ?
            $_SESSION['Session_SocUsr'] : null;
        
        $CODE_STE_tmp = $ste ?? '';

        return array_unique(array_merge(
            array_column(
                $this->sqlUOSteSectoRechercheResponsable($user, $ste),
                'CODE_UO'
            ),
            // Dossier 26636 : on ajoute les UO de LSTUOPARUSR
            //
            // UO auxquelles l'utilisateur est lié.
            //
            array_column(
                $this->rqSqlGetUOLieeUtilisateur($CODE_STE_tmp, $user),
                'CODE_UO'
            )
        ));
    }
}
