<?php

/**
 * 
 * @author edp
 *
 */
class SqlCentre
{

    public static function rqListeZoneGeoCentre($centre)
    {
        $sql = "select * from DGEOCTRE with (nolock) WHERE CODE_CTR = '$centre'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    public static function rqPopupGetAllCentreEmetteur($array_filters, $type_centre, $modeData, $objectSupport = NULL)
    {
        $array_filters['GRPSOC'] = ReplaceSqlPointeur($array_filters['GRPSOC']);
        
        if (! is_null($objectSupport)) {
            // Sans normalisation
            if (! is_null($objectSupport->getProperties('GROUPE')) && '' != $objectSupport->getProperties('GROUPE')) {
                $array_filters['GRPSOC'] = 'grp';
                $array_filters['GROUPE'] = $objectSupport->getProperties('GRPSOC');
            } else if (! is_null($objectSupport->getProperties('CODE_STE')) && '' != $objectSupport->getProperties('CODE_STE')) {
                $array_filters['GRPSOC'] = 'soc';
                $array_filters['CODE_STE'] = $objectSupport->getProperties('CODE_STE');
            }
            
            // Avec normalisation
            if (! is_null($objectSupport->getProperties(ChampRecherche::Groupe)) && '' != $objectSupport->getProperties(ChampRecherche::Groupe)) {
                $array_filters['GRPSOC'] = 'grp';
                $array_filters['GROUPE'] = $objectSupport->getProperties(ChampRecherche::Groupe);
            } else if (! is_null($objectSupport->getProperties(ChampRecherche::Societe)) && '' != $objectSupport->getProperties(ChampRecherche::Societe)) {
                $array_filters['GRPSOC'] = 'soc';
                $array_filters['CODE_STE'] = $objectSupport->getProperties(ChampRecherche::Societe);
            }
        }
        
        $sql = ' select ' . ($modeData ? "CODE_CTR, RAIS_SOC, ADRESSE, CPOSTAL, VILLE1 VILLE, (select count(*) from DOC_ATT with(nolock) where DOC_ATT.NO_DA='CENTRE' and DOC_ATT.COD_ENR = CODE_CTR) NB_DOC_ATT, CODE_STE, " . BoolDbOxaproc('' == $array_filters['CODE_STE']) . " as AFF_CODE_STE " : "count(*) CPTRESTBL ") . ' from CENTRE with (nolock)' . ' where 1=1 ';
        
        if ('CENTRE_E' == $type_centre)
            $sql .= ' and EMISSION = 0x54';
        elseif ('CENTRE_R' == $type_centre)
            $sql .= ' and LIVRAISON = 0x54';
        
        /**
         * Prise en compte de la possibilité d'avoir une liste d'utilisateurs
         * et dans ce cas l'objectif est d'avoir la liste des codes sociétés commune a cet ensemble de code utilisateurs
         */
        if (array_key_exists('DOA_USER', $array_filters)) {
            if (trim($array_filters['DOA_USER']) != '') {
                $sqlSte = '';
                $arraySteCommune = null;
                $index = 0;
                foreach (explode('|', $array_filters['DOA_USER']) as $codeUti)
                    if ($codeUti != '') {
                        $liste_societes = SqlUsers::rqListSocieteForUser($codeUti)->TrouveTous();
                        
                        if ($index == 0) {
                            foreach ($liste_societes as & $value)
                                if (is_null($arraySteCommune) || ! array_key_exists($value['Code'], $arraySteCommune))
                                    $arraySteCommune[$value['Code']] = $value['Code'];
                        } else if (! is_null($arraySteCommune))
                            if (is_null($liste_societes))
                                $arraySteCommune = null;
                            else {
                                $arraySoc = null;
                                foreach ($liste_societes as & $value)
                                    if (is_null($arraySoc) || ! array_key_exists($value['Code'], $arraySoc))
                                        $arraySoc[$value['Code']] = $value['Code'];
                                
                                if (is_null($arraySoc))
                                    $arraySteCommune = null;
                                else {
                                    $arrayRet = array_intersect($arraySoc, $arraySteCommune);
                                    $arraySteCommune = $arrayRet;
                                }
                            }
                        
                        $index ++;
                    }
                
                $sqlSte = '';
                // Aucun point commun
                if (is_null($arraySteCommune)) {
                    $sql .= ' and CODE_STE = "_____" ';
                } else {
                    foreach ($arraySteCommune as & $value) {
                        if ('' !== $sqlSte)
                            $sqlSte .= ',';
                        
                        $sqlSte .= '\'' . $value . '\'';
                    }
                    $sql .= ' and CODE_STE in (' . $sqlSte . ') ';
                }
            }
        } else {
            if ('' == $array_filters['GRPSOC'] || 'soc' == $array_filters['GRPSOC']) {
                // Filtre sur les sociétés
                if ('' != $array_filters['CODE_STE'])
                    $sql .= " and CODE_STE = '".ReplaceSqlPointeur($array_filters['CODE_STE'])."' ";
                elseif ($_SESSION['Session_GestionUsrMultiSoc']) {
                    // utilisateur multi-société : rien à faire
                } elseif ($_SESSION['Session_UsrPluriSoc']) {
                    // utilisateur pluri-société
                    $liste_societes = SqlUsers::rqListSocieteForUser($_SESSION['Session_Login'])->TrouveTous();
                    $sqlSte = '';
                    foreach ($liste_societes as & $value) {
                        if ('' !== $sqlSte)
                            $sqlSte .= ',';
                        
                        $sqlSte .= '\'' . $value['Code'] . '\'';
                    }
                    $sql .= ' and CODE_STE in (' . $sqlSte . ') ';
                } else {
                    $sql .= ' and CODE_STE = \'' . $_SESSION['Session_SocUsrProfil'] . '\' ';
                }
            } elseif ('grp' == $array_filters['GRPSOC']) {
                // Filtre sur les groupes de sociétés
                if ('' != $array_filters['GROUPE'])
                    $sql .= ' and CODE_STE in (select CODE_STE from SOCIETE where GROUPE = \'' . ReplaceSqlPointeur($array_filters['GROUPE']) . '\') ';
            }
        }
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and CODE_CTR = '{$array_filters['DATA_STRICT']}'";
        }
        $clause = '';
        $is_clause = false;
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    if ('' != trim($array_filters['CODE_CTR'])) {
                        $liste_ctr = explode(' ', $array_filters['CODE_CTR']);
                        foreach ($liste_ctr as $ctr)
                            if ('' !== trim($ctr))
                                $clause .= " upper(CODE_CTR) like upper('%".ReplaceSqlPointeur($ctr)."%') {$array_filters['relevance']}";
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        if ('' != $clause) {
                            $clause = " ({$clause})";
                            $is_clause = true;
                        }
                    }
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        $liste_rs = explode(' ', $array_filters['RAIS_SOC']);
                        if ($is_clause)
                            $clause .= ' AND ';
                        foreach ($liste_rs as $rs)
                            if ('' !== trim($rs))
                                $clause .= " upper(RAIS_SOC) like upper('%".ReplaceSqlPointeur($rs)."%') {$array_filters['relevance']}";
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    if ('' != trim($array_filters['CPOSTAL'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $liste_cp = explode(' ', $array_filters['CPOSTAL']);
                        foreach ($liste_cp as $cp)
                            if ('' !== trim($cp))
                                $clause .= " upper(CPOSTAL) like upper('%".ReplaceSqlPointeur($cp)."%') {$array_filters['relevance']}";
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    if ('' != trim($array_filters['VILLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $liste_ville = explode(' ', ReplaceSqlPointeur($array_filters['VILLE']));
                        foreach ($liste_ville as $ville) {
                            if ('' !== trim($ville)) {
                                $clause .= " upper(VILLE1) like upper('%".$ville."%') {$array_filters['relevance']}";
                            }
                        }
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    break;
                case 'strict':
                    if ('' != trim($array_filters['CODE_CTR'])) {
                        $clause .= "(upper(CODE_CTR) like upper('".ReplaceSqlPointeur($array_filters['CODE_CTR'])."%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(RAIS_SOC) like upper('".ReplaceSqlPointeur($array_filters['RAIS_SOC'])."%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['CPOSTAL'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        $clause .= "(upper(CPOSTAL) like upper('".ReplaceSqlPointeur($array_filters['CPOSTAL'])."%'))";
                        $is_clause = true;
                    }
                    if ('' != trim($array_filters['VILLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(VILLE1) like upper('".ReplaceSqlPointeur($array_filters['VILLE'])."%'))";
                        $is_clause = true;
                    }
                    break;
            }
            if ($is_clause)
                $sql .= ' AND ' . $clause;
            if ($modeData && array_key_exists('orderByField', $array_filters) && '' != $array_filters['orderByValue'])
                $sql .= " order by {$array_filters['orderByField']} {$array_filters['orderByValue']}";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
    
    //4474
    public static function getAcheteurCentre($centre)
    {
        $sql = "select ACHETEUR from CENTRE where CODE_CTR='$centre'";
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }

    public static function rqPopupGetAllCentreLivraison($array_filters, $modeData, $codeSi = null, $objectSupport = null)
    {
        $array_filters['GRPSOC'] = ReplaceSqlPointeur($array_filters['GRPSOC']);
        
        if (! is_null($objectSupport)) {
            // Sans normalisation
            if (! is_null($objectSupport->getProperties('GROUPE')) && '' != $objectSupport->getProperties('GROUPE')) {
                $array_filters['GRPSOC'] = 'grp';
                $array_filters['GROUPE'] = $objectSupport->getProperties('GROUPE');
            } else if (! is_null($objectSupport->getProperties('CODE_STE')) && '' != $objectSupport->getProperties('CODE_STE')) {
                $array_filters['GRPSOC'] = 'soc';
                $array_filters['CODE_STE'] = $objectSupport->getProperties('CODE_STE');
            }
            // Avec normalisation
            if (! is_null($objectSupport->getProperties(ChampRecherche::Groupe)) && '' != $objectSupport->getProperties(ChampRecherche::Groupe)) {
                $array_filters['GRPSOC'] = 'grp';
                $array_filters['GROUPE'] = $objectSupport->getProperties(ChampRecherche::Groupe);
            } else if (! is_null($objectSupport->getProperties(ChampRecherche::Societe)) && '' != $objectSupport->getProperties(ChampRecherche::Societe)) {
                $array_filters['GRPSOC'] = 'soc';
                $array_filters['CODE_STE'] = $objectSupport->getProperties(ChampRecherche::Societe);
            }
        }
        
        // ($modeData ? "CODE_CTR, RAIS_SOC, ADRESSE, CPOSTAL, VILLE1 VILLE, (select count(*) from DOC_ATT with(nolock) where DOC_ATT.NO_DA='CENTRE' and DOC_ATT.COD_ENR = CODE_CTR) NB_DOC_ATT, CODE_STE, ".BoolDbOxaproc('' == $array_filters['CODE_STE'])." as AFF_CODE_STE " : "count(*) CPTRESTBL ") .
        
        if (Param::isClientBYCN() && Param::isSteSAPactivated($array_filters['CODE_STE'])) {
            if ('' == $array_filters['SI'])
                $sql = ' select ' . ($modeData ? "CODE_CTR, RAIS_SOC, ADRESSE, CPOSTAL, VILLE1 VILLE, COD_ENR SI, (select count(*) from DOC_ATT with(nolock) where DOC_ATT.NO_DA='CENTRE' and DOC_ATT.COD_ENR = CODE_CTR) NB_DOC_ATT, CODE_STE, " . BoolDbOxaproc('' == $array_filters['CODE_STE']) . " as AFF_CODE_STE " : "count(*) CPTRESTBL ") . "from  CENTRE with (nolock) left outer join CS_DON with(nolock) on CS_DON.VALEUR = CENTRE.CODE_CTR 
									      and 	CS_DON.COD_TAB 	  = 'SECTIMPU'
									      and 	CS_DON.NUM_FIX 	  = (select LIB01DICO from DILISPEC with(nolock) where TYPEDICO='PARAMSAP' and CODEDICO='NCS_SECTIMPU_CENTRE_LIV')   
									      and   COD_ENR like ('" . strtoupper(ReplaceSqlPointeur(str_pad($array_filters['CODE_STE'], 7, ' ', STR_PAD_RIGHT))) . "%')	
										  where CENTRE.LIVRAISON  = 0x54";
            else
                $sql = ' select ' . ($modeData ? "CODE_CTR, RAIS_SOC, ADRESSE, CPOSTAL, VILLE1 VILLE, COD_ENR SI, CODE_STE, " . BoolDbOxaproc('' == $array_filters['CODE_STE']) . " as AFF_CODE_STE " : "count(*) CPTRESTBL ") . "from CENTRE with (nolock), CS_DON with(nolock) " . "where LIVRAISON = 0x54 
										  and COD_TAB 	  = 'SECTIMPU' 
										  and NUM_FIX 	  = (select LIB01DICO from DILISPEC with(nolock) where TYPEDICO='PARAMSAP' and CODEDICO='NCS_SECTIMPU_CENTRE_LIV') 
										  and VALEUR  	  = CENTRE.CODE_CTR	
										  and COD_ENR like ('" . strtoupper(ReplaceSqlPointeur(str_pad($array_filters['CODE_STE'], 7, ' ', STR_PAD_RIGHT))) . "'+upper('%" . ReplaceSqlPointeur($array_filters['SI']) . "%'))
										";
        } else {
            $sql = ' select ' . ($modeData ? "CODE_CTR, RAIS_SOC, ADRESSE, CPOSTAL, VILLE1 VILLE, CODE_STE, " . BoolDbOxaproc('' == $array_filters['CODE_STE']) . " as AFF_CODE_STE " : "count(*) CPTRESTBL ") . " from CENTRE with (nolock)" . " where ( LIVRAISON = 0x54";
            
            // SPEC 3440 Restriction supplementaire sur la notion emetteur
            if (CLT_SPAC == $_SESSION['Session_NumClient'] && ! is_null($codeSi))
                $sql .= " and (EMISSION = 0x54 OR CODE_CTR = '".ReplaceSqlPointeur($codeSi)."') ";
            
            $sql .= ' ) ';
        }
        
        // SPEC 4629
        // if($_SESSION['Session_NumClient'] == 61 && !is_null($objectSupport) && ($objectSupport->getProperties('CODE_SI')??'')!=''){
        //     $array_filters['CODE_SI'] = $objectSupport->getProperties('CODE_SI');
        //     $sql .= " and CODE_CTR in (select COD_ENR from CS_DON where COD_TAB ='CENTRE' and 
        //     ((NUM_FIX =(select LIB01DICO from DILISPEC where TYPEDICO ='PARNCS0061' and CODEDICO = 'NCS_CENTRE_CODE_CI') and VALEUR = '".$array_filters['CODE_SI']."')
        //     or (NUM_FIX =(select LIB01DICO from DILISPEC where TYPEDICO ='PARNCS0061' and CODEDICO = 'NCS_CENTRE_ADR_COMM') and VALEUR = 'O')
        //     ))";
        // }
        
        if ('' != $array_filters['CODE_STE'])
            $sql .= " and CODE_STE = '{$array_filters['CODE_STE']}' ";
        elseif ($_SESSION['Session_GestionUsrMultiSoc']) {
            // utilisateur multi-société : rien à faire
        } elseif ($_SESSION['Session_UsrPluriSoc']) {
            // utilisateur pluri-société
            $liste_societes = & SqlUsers::rqListSocieteForUser($_SESSION['Session_Login'])->TrouveTous();
            $sqlSte = '';
            foreach ($liste_societes as & $value) {
                if ('' !== $sqlSte) {
                    $sqlSte .= ',';
                }
                $sqlSte .= '\'' . $value['Code'] . '\'';
            }
            $sql .= ' and CODE_STE in (' . $sqlSte . ') ';
        } else {
            $sql .= ' and CODE_STE = \'' . $_SESSION['Session_SocUsrProfil'] . '\' ';
        }
        
        if ('grp' == $array_filters['GRPSOC'] && '' != trim($array_filters['GROUPE'])) {
            $groupe = trim($array_filters['GROUPE']);
            $sql .= ' and CENTRE.CODE_STE  in  (select CODE_STE from SOCIETE where GROUPE=\'' . ReplaceSqlPointeur($groupe) . '\')';
        } elseif ('soc' == $array_filters['GRPSOC'] && '' != trim($array_filters['CODE_STE'])) {
            $societe = trim($array_filters['CODE_STE']);
            $sql .= ' and CENTRE.CODE_STE  = \'' . ReplaceSqlPointeur($societe) . '\'';
        }
        
        if (array_key_exists('DATA_STRICT', $array_filters)) {
            $array_filters['DATA_STRICT'] = ReplaceSqlPointeur($array_filters['DATA_STRICT']);
            $sql .= " and CODE_CTR = '{$array_filters['DATA_STRICT']}'";
        }
        
        $clause = '';
        $is_clause = false;
        if (array_key_exists('relevance', $array_filters)) {
            switch ($array_filters['relevance']) {
                case 'OR':
                case 'AND':
                    
                    if ('' != trim($array_filters['CODE_CTR'])) {
                        
                        $liste_ctr = explode(' ', ReplaceSqlPointeur($array_filters['CODE_CTR']));
                        
                        foreach ($liste_ctr as $ctr)
                            if ('' !== trim($ctr))
                                $clause .= " upper(CODE_CTR) like upper('%{$ctr}%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != $clause) {
                            $clause = " ({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        
                        $liste_rs = explode(' ', ReplaceSqlPointeur($array_filters['RAIS_SOC']));
                        
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        foreach ($liste_rs as $rs)
                            if ('' !== trim($rs))
                                $clause .= " upper(RAIS_SOC) like upper('%{$rs}%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    if ('' != trim($array_filters['CPOSTAL'])) {
                        
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $liste_cp = explode(' ', ReplaceSqlPointeur($array_filters['CPOSTAL']));
                        
                        foreach ($liste_cp as $cp)
                            if ('' !== trim($cp))
                                $clause .= " upper(CPOSTAL) like upper('%{$cp}%') {$array_filters['relevance']}";
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    if ('' != trim($array_filters['VILLE'])) {
                        
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $liste_ville = explode(' ', ReplaceSqlPointeur($array_filters['VILLE']));
                        
                        foreach ($liste_ville as $ville)
                            if ('' !== trim($ville)) {
                                $clause .= " upper(VILLE1) like upper('%{$ville}%') {$array_filters['relevance']}";
                            }
                        
                        $clause = mb_substr($clause, 0, mb_strlen($clause) - mb_strlen($array_filters['relevance']));
                        
                        if ('' != trim($clause)) {
                            $clause = "({$clause})";
                            $is_clause = true;
                        }
                    }
                    
                    break;
                
                case 'strict':
                    if ('' != trim($array_filters['CODE_CTR'])) {
                        $clause .= "(upper(CODE_CTR) like upper('".ReplaceSqlPointeur($array_filters['CODE_CTR'])."%'))";
                        $is_clause = true;
                    }
                    
                    if ('' != trim($array_filters['RAIS_SOC'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(RAIS_SOC) like upper('".ReplaceSqlPointeur($array_filters['RAIS_SOC'])."%'))";
                        $is_clause = true;
                    }
                    
                    if ('' != trim($array_filters['CPOSTAL'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(CPOSTAL) like upper('".ReplaceSqlPointeur($array_filters['CPOSTAL'])."%'))";
                        $is_clause = true;
                    }
                    
                    if ('' != trim($array_filters['VILLE'])) {
                        if ($is_clause)
                            $clause .= ' AND ';
                        
                        $clause .= "(upper(VILLE1) like upper('".ReplaceSqlPointeur($array_filters['VILLE'])."%'))";
                        $is_clause = true;
                    }
                    
                    break;
            }
            if ($is_clause)
                $sql .= ' AND ' . $clause;
        }
        
        if (! is_null($objectSupport)) {
            if ($objectSupport instanceof PopupBrowse)
                $classContext = $objectSupport->getClassContexte();
            elseif ($objectSupport instanceof ContexteWhereDynamic)
                $classContext = $objectSupport;
            else
                $classContext = null;
            
            // Spec 3807
            if ($classContext instanceof ContexteWhereDynamic) {
                // On va pouvoir travailler sur la class
                $classContext->setAliasTable('CENTRE_R', 'CENTRE');
                $sqlAdd = $classContext->getQueryWhere('CENTRE_R');
                if (trim($sqlAdd) != '')
                    $sql .= $sqlAdd;
            }
        }
        
        if ($modeData && array_key_exists('orderByField', $array_filters) && '' != $array_filters['orderByValue']) {
            if ("SI" == $array_filters['orderByField'])
                $array_filters['orderByField'] = 'COD_ENR';
            
            $sql .= " order by {$array_filters['orderByField']} {$array_filters['orderByValue']}";
        }
        
        return new RequeteSQL($sql, DB_OXASQLDB);
    }
}