<?php
/****************************************************************************************
 * SMI (Services Maintenance Interventions) est une gestion libre de maintenance et de SAV
 *
 * Version 1.0.2
 *
 * Copyright (C) 2006-2015  Sylvain FATOME
 * This program is free software; you can redistribute it and/or modify it under the terms
 * of the GNU General Public License as published by the Free Software Foundation;
 * either version 2 of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with this program
 * (in the directory docs);
 * if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
 * Boston, MA  02110-1301, USA.
 *
 * Contact : galawa@free.fr
 *
 * Web : http://smi.no-ip.org
 ****************************************************************************************/


// Vérification des tentatives de hack
$pos = strpos($_SERVER['PHP_SELF'], "inc.php");
if ($pos === FALSE)
    $ok_inc = TRUE;
else
    $ok_inc = FALSE;
if ($ok_inc == FALSE) header("Location: ../index.php");

/*************************************************/
/* Fonctions pour les accès aux fichiers clients */
/* et fichiers rattachés                         */
/*************************************************/

// Clients

/**
 * Vérification du nombre de catégories de clients
 *
 * @param bool $open
 *
 * @return null
 */
function nb_ccat($open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBCCAT . "`";
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Vérification du nombre de clients, dans l'ensemble ou pour une agence donnée
 *
 * @param      $agence
 * @param bool $open
 *
 * @return int|null
 */
function nb_cli($agence, $open = TRUE)
{
    global
    $db;

    $nbexist = 0;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    if ($agence <> "") {
        $rexist2 = "SELECT age_code,age_codepays FROM `" . TBAGE . "` WHERE `age_code` = '" . $db->escape($agence) . "'";
        $row2 = $db->get_row($rexist2, ARRAY_A);
        if ($row2) {
            extract($row2);

            $rexist3 = "SELECT cli_prop,cli_dep,cli_codepays FROM `" . TBCLI . "` WHERE `cli_codepays`='" . $db->escape($age_codepays) . "' OR `cli_prop`='" . $db->escape($age_code) . "'";
            $rows3 = $db->get_results($rexist3, ARRAY_A);
            if (($rows3) AND (count($rows3) <> 0)) {
                foreach ($rows3 as $row3) {
                    extract($row3);

                    if ($cli_prop <> $age_code) {
                        if (dep_in_zcc_pays_open($cli_dep, $cli_codepays, FALSE) == TRUE)
                            $nbexist++;
                    } else
                        $nbexist++;
                }
            }
        }
    } else {
        $rexist2 = "SELECT COUNT(*) FROM `" . TBCLI . "`";
        $nbexist = $db->get_var($rexist2);
    }
    $db->disconnect();

    return $nbexist;
}

/**
 * Vérification du nombre d'interventions sur un client
 * Filtre sur niveau et sur code agence/donneur d'ordre
 *
 * @param      $client
 * @param      $level
 * @param      $codead
 * @param bool $open
 *
 * @return null
 */
function nb_int_cli($client, $level, $codead, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE `int_codecli` = '" . $db->escape($client) . "'";
    if (trim($codead) <> "") {
        if (($level == 3) OR ($level == 4))
            $rexist .= " AND `int_codeage` = '" . $db->escape($codead) . "'";
        if (($level == 6) OR ($level == 7))
            $rexist .= " AND `int_codedo` = '" . $db->escape($codead) . "'";
    }
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Vérification du nombre de dossiers sur un client
 * Filtre sur niveau et sur code agence/donneur d'ordre
 *
 * @param      $client
 * @param      $level
 * @param      $codead
 * @param bool $open
 *
 * @return null
 */
function nb_dos_cli($client, $level, $codead, $open = TRUE)
{
    global
    $db;


    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBDOS . "` WHERE `dos_codecli` = '" . $db->escape($client) . "'";
    if (trim($codead) <> "") {
        if (($level == 3) OR ($level == 4))
            $rexist .= " AND `dos_codeage` = '" . $db->escape($codead) . "'";
    }
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Lecture d'un enregistrement client
 *
 * @param        $code
 * @param bool $open
 * @param string $typ
 * @return array|null
 */
function lit_enr_cli($code, $open = TRUE, $typ = "")
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBCLI . "` WHERE `cli_code` = '" . $db->escape($code) . "'";
    switch ($typ) {
        case "num" :
            $row2 = $db->get_row($rech2, ARRAY_N);
            break;
        default :
            $row2 = $db->get_row($rech2, ARRAY_A);
            break;
    }
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

/**
 * Vérification de la présence d'un client dans un contrat client signé
 *
 * @param      $code
 * @param bool $open
 *
 * @return bool
 */
function exist_cli_in_cocl_signe($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBCOCL . "` WHERE `cocl_codecli` = '" . $db->escape($code) . "' AND `cocl_signe` IS NOT NULL";
    $nbexist = $db->get_var($rexist);
    if ($nbexist > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un client dans une fiche intervention
 *
 * @param      $code
 * @param bool $open
 *
 * @return bool
 */
function exist_cli_in_int($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE `int_codecli` = '" . $db->escape($code) . "'";
    $nbexist = $db->get_var($rexist);
    if ($nbexist > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un client dans une fiche intervention ouverte
 *
 * @param      $code
 * @param bool $open
 *
 * @return bool
 */
function exist_cli_in_int_act($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE `int_codecli` = '" . $db->escape($code) . "' AND `int_datefin` = '0000-00-00'";
    $nbexist = $db->get_var($rexist);
    if ($nbexist > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un client dans un Relevé de parc
 *
 * @param      $code
 * @param bool $open
 *
 * @return bool
 */
function exist_cli_in_rel($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBREL . "` WHERE `rel_codecli` = '" . $db->escape($code) . "'";
    $nbexist = $db->get_var($rexist);
    if ($nbexist > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

// Contacts Clients

/**
 * Vérification du nombre de contacts clients dans l'ensemble ou pour un client donné
 *
 * @param      $client
 * @param bool $open
 *
 * @return null
 */
function nb_ccli($client, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    if ($client <> "")
        $rexist2 = "SELECT COUNT(*) FROM `" . TBCCLI . "` WHERE `ccli_codecli` = '" . $db->escape($client) . "'";
    else
        $rexist2 = "SELECT COUNT(*) FROM `" . TBCLI . "`";
    $nbexist = $db->get_var($rexist2);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Vérification du nombre de contacts dans une fiche client
 *
 * @param      $code
 * @param bool $open
 *
 * @return null
 */
function nb_ccli_in_cli($code, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBCCLI . "` WHERE `ccli_codecli` = '" . $db->escape($code) . "'";
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Lecture d'un contact client (code contact)
 *
 * @param      $code
 * @param bool $open
 * @return array|null
 */
function lit_enr_ccli($code, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech = "SELECT * FROM `" . TBCCLI . "` WHERE `ccli_code` = '$code'";
    $row = $db->get_row($rech, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row;
}

/**
 * Lecture d'un contact client (ID + code client)
 *
 * @param      $id
 * @param      $code
 * @param bool $open
 * @return array|null
 */
function lit_enr_ccli_id_in_cli($id, $code, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBCCLI . "` WHERE `ccli_id` = '$id' AND `ccli_codecli` = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    // Le contact existe dans ce code client
    if ($row2)
        $result = $row2;
    else // Il n'existe pas : on vérifie s'il n'est pas dans un groupement "maître" de ce client
    {
        // On récupère le code "maître"
        $rech3 = "SELECT rcli_maitre FROM `" . TBRCLI . "` WHERE rcli_esclave = '" . $db->escape($code) . "'";
        $rows3 = $db->get_results($rech3, ARRAY_A);
        if (($rows3) AND (count($rows3) > 0)) {
            foreach ($rows3 as $row3) {
                $rech4 = "SELECT * FROM `" . TBCCLI . "` WHERE `ccli_id` = '$id' AND `ccli_codecli` = '" . $row3["rcli_maitre"] . "'";
                $row4 = $db->get_row($rech4, ARRAY_A);
                if ($row4)
                    $result = $row4;
            }
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $result;
}

/**
 * Suppression des contacts dans une fiche client
 *
 * @param      $code
 * @param bool $open
 */
function del_ccli_in_cli($code, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "DELETE FROM `" . TBCCLI . "` WHERE `ccli_codecli` = '" . $db->escape($code) . "'";
    $db->query($rexist);
    if ($open == TRUE)
        $db->disconnect();
}

/**
 * Retourne la liste des contacts clients
 *
 * @param       $code
 * @param int   $id_site
 * @param array $tab_maitre
 * @param bool  $open
 *
 * @return array
 */
function give_tab_ccli($code, $id_site = 0, $tab_maitre = [], $open = TRUE)
{
    global
    $db;

    $tab_ccli = [];

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    // Récupération du contact principal
    $rech = "SELECT cli_ste,cli_prenom, cli_nom,cli_telf,cli_telp FROM `" . TBCLI . "` WHERE `cli_code` = '" . $db->escape($code) . "'";
    $row = $db->get_row($rech, ARRAY_A);
    if ($row) {
        $row = encode_str($row);
        extract($row);
        // Contacts uniquement si société
        if ($cli_ste <> '') {
            // Contact principal
            $txt_ccli = $cli_nom . " " . $cli_prenom . " (";
            $txt_ccli .= $cli_ste . ' - ';
            $txt_ccli .= _("Contact principal");
            $txt_ccli .= " - " . $cli_telf;
            if ($cli_telp <> "")
                $txt_ccli .= " / " . $cli_telp;
            $txt_ccli .= ")";
            $tab_ccli[-1] = $txt_ccli;
            // $tab_ccli["-1"] = $txt_ccli;

            if ($id_site > 0) {
                // Contact principal du site
                $rech = "SELECT * FROM `" . TBSCLI . "` WHERE `scli_id` = '$int_idsite'";
                $row = $db->get_row($rech, ARRAY_A);
                if ($row) {
                    $row = encode_str($row);
                    extract($row);
                    $txt_ccli = $scli_rnom . " " . $scli_rprenom . " (";
                    $txt_ccli .= $cli_ste . ' - ';
                    $txt_ccli .= _("Contact principal site");
                    $txt_ccli .= " - " . $scli_telf;
                    if ($scli_telp <> "")
                        $txt_ccli .= " / " . $scli_telp;
                    $txt_ccli .= ")";
                    $tab_ccli[0] = $txt_ccli;
                    // $tab_ccli["0"] = $txt_ccli;
                }
            }

            // Contacts du client et de ses sous-comptes
            $rech = "SELECT * FROM `" . TBCCLI . "` WHERE `ccli_codecli` = '" . $code . "'";
            if (count($tab_maitre) > 0) {
                foreach ($tab_maitre as $value) {
                    $rech .= " OR `ccli_codecli` = '" . $value . "'";
                }
            }
            $rech .= " ORDER BY ccli_nom";
            $rows = $db->get_results($rech, ARRAY_A);
            if ($rows) {
                foreach ($rows as $row) {
                    $row = encode_str($row);
                    extract($row);

                    $txt_ccli = $ccli_nom . " " . $ccli_prenom;
                    $row2 = lit_enr_fonc($ccli_codefonc, FALSE);
                    if (is_array($row2)) {
                        $row2 = encode_str($row2);
                        extract($row2);
                        $txt_fonc = $fonc_desc;
                    } else
                        $txt_fonc = $ccli_codefonc;
                    $txt_ccli .= " (";
                    if (count($tab_maitre) > 0) {
                        $nomcli = "";
                        $rech3 = "SELECT cli_ste FROM `" . TBCLI . "` WHERE `cli_code` = '$ccli_codecli'";
                        $row3 = $db->get_row($rech3, ARRAY_A);
                        if ($row3) {
                            $row3 = encode_str($row3);
                            $nomcli = $row3["cli_ste"];
                        }
                        if ($nomcli <> "")
                            $txt_ccli .= $nomcli . " - ";
                    }
                    $txt_ccli .= $txt_fonc;
                    $txt_ccli .= " - " . $ccli_telf;
                    if ($ccli_telp <> "")
                        $txt_ccli .= " / " . $ccli_telp;
                    $txt_ccli .= ")";
                    $tab_ccli[$ccli_id] = $txt_ccli;
                }
            }
        } else
            $tab_ccli[0] = _("Pas de contact");
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_ccli;
}

/*******************/
/** Sites Clients **/
/*******************/

/**
 * Lecture d'un site client par son ID
 *
 * @param      $idscli
 * @param bool $open
 * @return array|null
 */
function lit_enr_scli_id($idscli, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBSCLI . "` WHERE `scli_id` = ".$idscli;
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

/**
 * Lecture d'un site client par son code client
 *
 * @param      $codecli
 * @param bool $open
 * @return array|null
 */
function lit_enr_scli_cli($codecli, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBSCLI . "` WHERE `scli_codecli` = '" . $db->escape($codecli) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

/**
 * Donne le nom complet d'un site client
 *
 * @param      $idscli
 * @param bool $sdl
 * @param bool $contact
 * @param bool $tel
 * @param bool $open
 *
 * @return string
 */
function nom_scli($idscli, $sdl = FALSE, $contact = TRUE, $tel = TRUE, $open = TRUE)
{
    global
        $db;

    $nom_site = "";
    switch ($idscli) {
        case -2 :
            $nom_site .= _("Merci de choisir un site");
            break;
        case -1 :
            $nom_site .= _("Tous les sites");
            break;
        case 0 :
            $nom_site .= _("Site principal");
            break;
        default :
            if ($open == TRUE)
                $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
            $rech2 = "SELECT * FROM `" . TBSCLI . "` WHERE `scli_id` = '$idscli'";
            $row2 = $db->get_row($rech2, ARRAY_A);
            if ($row2) {
                $row2 = encode_str($row2);
                extract($row2);
                $nom_site .= $scli_ste;
                if ($sdl == TRUE)
                    $nom_site .= "<br />";
                else
                    $nom_site .= ", ";
                if ($contact == TRUE) {
                    $nom_site .= $scli_rcivil . " " . $scli_rprenom . " " . $scli_rnom;
                    if ($sdl == TRUE)
                        $nom_site .= "<br />";
                    else
                        $nom_site .= ", ";
                }
                $nom_site .= $scli_adr1;
                if ($sdl == TRUE)
                    $nom_site .= "<br />";
                else
                    $nom_site .= ", ";
                if ($scli_adr2 <> "") {
                    $nom_site .= $scli_adr2;
                    if ($sdl == TRUE)
                        $nom_site .= "<br />";
                    else
                        $nom_site .= ", ";
                }
                $nom_site .= Retournecp_Id($scli_ville, FALSE) . " " . Retourneville_Id($scli_ville, FALSE);
                if ($tel == TRUE) {
                    if ($sdl == TRUE)
                        $nom_site .= "<br />";
                    else
                        $nom_site .= ", ";
                    $nom_site .= _("Tel").' : ' . $scli_telf;
                    if ($scli_telp <> '')
                        $nom_site .= ' / ' . $scli_telp;
                    if ($sdl == TRUE)
                        $nom_site .= "<br />";
                }
            }
            if ($open == TRUE)
                $db->disconnect();
    }

    return $nom_site;
}

/**
 * Nombre de sites pour un client donné (ou tout les clients si $codecli vide)
 * pour un "propriétaire" de site donné (ou tous si $codeprop vide)
 *
 * @param      $codecli
 * @param      $codeprop
 * @param bool $open
 *
 * @return null
 */
function nb_scli($codecli, $codeprop, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    $rexist = "SELECT COUNT(*) FROM `" . TBSCLI . "`";
    if (trim($codecli) <> "")
        $rexist .= " WHERE scli_codecli = '" . $db->escape($codecli) . "'";
    if (trim($codeprop) <> "") {
        if (!stristr($rech, "WHERE "))
            $rexist .= " WHERE scli_prop = '" . $db->escape($codeprop) . "'";
        else
            $rexist .= " AND scli_prop = '" . $db->escape($codeprop) . "'";
    }
    $nb = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nb;
}

/**
 * Nombre de sites secondaires actifs pour un client donné
 *
 * @param $codecli
 * @param $codeprop
 * @param $open
 *
 * @return null
 */
function nb_scli_actifs($codecli, $codeprop, $open)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBSCLI . "` WHERE scli_codecli = '" . $db->escape($codecli) . "' AND scli_actif=1";
    if (trim($codeprop) <> "")
        $rexist .= " AND scli_prop = '" . $db->escape($codeprop) . "'";
    $nb = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nb;
}

/**
 * Tableau des sites secondaires accessibles par une agence
 *
 * @param      $codecli
 * @param      $codeage1
 * @param      $codeage2
 * @param bool $open
 *
 * @return array
 */
function give_tab_scli_age($codecli, $codeage1, $codeage2, $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    // On récupère les ID sites secondaires actifs
    $tab_scli = [];
    $rech = "SELECT scli_id,scli_dep,scli_codepays FROM `" . TBSCLI . "` WHERE scli_codecli = '" . $db->escape($codecli) . "' AND scli_actif=1";
    $rech .= " AND (scli_prop = '" . $db->escape($codeage1) . "'";
    if ($codeage1 <> $codeage2)
        $rech .= " OR scli_prop = '" . $db->escape($codeage2) . "'";
    $rech .= " OR scli_prop = '-1' OR scli_prop = '0')";
    $rows = $db->get_results($rech, ARRAY_A);
    if ($rows) {
        foreach ($rows as $row) {
            extract($row);
            if (dep_in_zcc_age_open($scli_dep, $scli_codepays, $codeage1, FALSE) == TRUE)
                $tab_scli[] = $scli_id;
            else
                if (dep_in_zcc_age_open($scli_dep, $scli_codepays, $codeage2, FALSE) == TRUE)
                    $tab_scli[] = $scli_id;
        }
    }

    return $tab_scli;
}

/**
 * Tableau des sites secondaires non encore affectés pour un client donné sur un contrat donné
 * $sauf = code de l'annexe à ne pas prendre en compte
 *
 * @param        $codecli
 * @param        $codecocl
 * @param string $sauf
 * @param bool   $open
 *
 * @return array
 */
function give_tab_scli_non_affectes($codecli, $codecocl, $sauf = "", $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    // On récupère les ID sites secondaires actifs
    $tab_scli = [];
    $rexist = "SELECT scli_id FROM `" . TBSCLI . "` WHERE scli_codecli = '" . $db->escape($codecli) . "' AND scli_actif=1";
    $rows2 = $db->get_results($rexist, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $tab_scli[] = $scli_id;
        }
    }

    // On vérifie quel site est libre
    $tab_scli_used = [];

// Vérification sur les sites concernés par le contrat
    $rexist = "SELECT sclic_idscli FROM `" . TBSCLIC . "` WHERE sclic_codecocl = '" . $db->escape($codecocl) . "'";
    $rows2 = $db->get_results($rexist, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $tab_scli_used[] = $sclic_idscli;
        }
    }

    // On récupère l'ID
    $cocla_id = 0;
    if (trim($sauf) <> "") {
        $rech2 = "SELECT cocla_id FROM `" . TBCOCLA . "` WHERE `cocla_code` = '" . $db->escape($sauf) . "' AND `cocla_codecocl` = '" . $db->escape($codecocl) . "'";
        $row2 = $db->get_row($rech2, ARRAY_A);
        if ($row2)
            extract($row2);
    }

    // Vérification sur les sites concernés par les annexes au contrat
    $rexist = "SELECT sclia_idscli FROM `" . TBSCLIA . "` WHERE sclia_codecocl = '" . $db->escape($codecocl) . "'";
    if ($cocla_id <> 0)
        $rexist .= " AND sclia_idcocla <> '" . $cocla_id . "'";
    if (count($tab_scli_used) > 0) {
        $tab_scli_temp = $tab_scli_used;
        $rexist .= " AND (";
        foreach ($tab_scli_temp as $value) {
            $rexist .= " sclia_idscli <> '" . $value . "' AND";
        }
        $rexist = substr($rexist, 0, strlen($rexist) - 3);
        $rexist .= ")";
        $rows2 = $db->get_results($rexist, ARRAY_A);
        if (($rows2) AND (count($rows2) > 0)) {
            foreach ($rows2 as $row2) {
                extract($row2);
                $tab_scli_used[] = $sclia_idscli;
            }
        }
    }

    $tab_scli_free = array_diff($tab_scli, $tab_scli_used);

    if ($open == TRUE)
        $db->disconnect();

    return $tab_scli_free;
}

/**
 * Nombre de sites secondaires non encore affectés pour un client donné sur un contrat donné
 * $sauf = code de l'annexe à ne pas prendre en compte
 *
 * @param        $codecli
 * @param        $codecocl
 * @param string $sauf
 * @param bool   $open
 *
 * @return int
 */
function nb_scli_non_affectes($codecli, $codecocl, $sauf = "", $open = TRUE)
{
    $nb = count(give_tab_scli_non_affectes($codecli, $codecocl, $sauf, $open));
    return $nb;
}

/**
 * Tableau des sites secondaires non encore affectés pour un client donné sur un contrat donné
 * $sauf = code de l'annexe à ne pas prendre en compte
 *
 * @param        $codecli
 * @param        $codecocl
 * @param string $sauf
 * @param bool   $open
 *
 * @return array
 */
function give_tab_scli_affectes_annexes($codecli, $codecocl, $sauf = "", $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    // On récupère les ID sites secondaires actifs
    $tab_scli = [];
    $rexist = "SELECT scli_id FROM `" . TBSCLI . "` WHERE scli_codecli = '" . $db->escape($codecli) . "' AND scli_actif=1";
    $rows2 = $db->get_results($rexist, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $tab_scli[] = $scli_id;
        }
    }

    // On construit la liste des sites utilisés dans les annexes du contrat
    $tab_scli_used = [];

    // On récupère l'ID
    $cocla_id = 0;
    if (trim($sauf) <> "") {
        $rech2 = "SELECT cocla_id FROM `" . TBCOCLA . "` WHERE `cocla_code` = '" . $db->escape($sauf) . "' AND `cocla_codecocl` = '" . $db->escape($codecocl) . "'";
        $row2 = $db->get_row($rech2, ARRAY_A);
        if ($row2)
            extract($row2);
    }

    // Vérification sur les sites concernés par les annexes au contrat
    $rexist = "SELECT sclia_idscli FROM `" . TBSCLIA . "` WHERE sclia_codecocl = '" . $codecocl . "'";
    if ($cocla_id <> 0)
        $rexist .= " AND sclia_idcocla <> '" . $cocla_id . "'";
    if (count($tab_scli_used) > 0) {
        $tab_scli_temp = $tab_scli_used;
        $rexist .= " AND (";
        foreach ($tab_scli_temp as $value) {
            $rexist .= " sclia_idscli <> '" . $value . "' AND";
        }
        $rexist = substr($rexist, 0, strlen($rexist) - 3);
        $rexist .= ")";
        $rows2 = $db->get_results($rexist, ARRAY_A);
        if (($rows2) AND (count($rows2) > 0)) {
            foreach ($rows2 as $row2) {
                extract($row2);
                $tab_scli_used[] = $sclia_idscli;
            }
        }
    }

    if ($open == TRUE)
        $db->disconnect();

    return $tab_scli_used;
}

/**
 * Suppression des sites dans une fiche client
 *
 * @param      $code
 * @param bool $open
 */
function del_scli_in_cli($code, $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "DELETE FROM `" . TBSCLI . "` WHERE `scli_codecli` = '" . $db->escape($code) . "'";
    $db->query($rexist);
    if ($open == TRUE)
        $db->disconnect();
}

/**
 * Vérification de la présence d'un site client dans un contrat
 * (Recherche sur le code du client)
 *
 * @param      $codecli
 * @param bool $open
 *
 * @return bool
 */
function exist_scli_in_cocl_cc($codecli, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist2 = "SELECT * FROM `" . TBSCLI . "` WHERE scli_codecli = '" . $db->escape($codecli) . "'";
    $rows2 = $db->get_results($rexist2, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $rexist3 = "SELECT COUNT(*) FROM `" . TBSCLIC . "` WHERE sclic_idscli = '" . $scli_id . "'";
            $nbexist = $db->get_var($rexist3);
            if ($nbexist <> 0)
                $exist = TRUE;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un site client dans un contrat client signé
 *
 * @param      $idscli
 * @param bool $open
 *
 * @return bool
 */
function exist_scli_in_cocl_signe($idscli, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBSCLIC . "`, `" . TBCOCL . "` WHERE " . TBSCLIC . ".sclic_idscli = '" . $idscli . "'";
    $rexist .= " AND " . TBCOCL . ".cocl_signe IS NOT NULL AND " . TBSCLIC . ".sclic_codecocl=" . TBCOCL . ".cocl_code";
    $nbexist = $db->get_var($rexist);
    if ($nbexist <> 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un site client dans une fiche intervention
 * (Recherche sur l'id du site)
 *
 * @param      $idscli
 * @param bool $open
 *
 * @return bool
 */
function exist_scli_in_int($idscli, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE int_idsite = '" . $idscli . "'";
    $nbexist = $db->get_var($rexist);
    if ($nbexist <> 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un site client dans une fiche intervention
 * (Recherche sur le code du client)
 *
 * @param      $codecli
 * @param bool $open
 *
 * @return bool
 */
function exist_scli_in_int_cc($codecli, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist2 = "SELECT * FROM `" . TBSCLI . "` WHERE scli_codecli = '" . $db->escape($codecli) . "'";
    $rows2 = $db->get_results($rexist2, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $rexist3 = "SELECT * FROM `" . TBINT . "` WHERE int_idsite = '" . $scli_id . "'";
            $nbexist = $db->get_var($rexist3);
            if ($nbexist <> 0)
                $exist = TRUE;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un site client dans une fiche intervention non close
 *
 * @param      $idscli
 * @param bool $open
 *
 * @return bool
 */
function exist_scli_in_int_ouv($idscli, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE int_idsite = '" . $idscli . "'";
    $rexist .= " AND int_datefin='0000-00-00'";
    $nbexist = $db->get_var($rexist);
    if ($nbexist <> 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérifie s'il existe un Relevé pour un code client
 *
 * @param      $codecli
 * @param bool $open
 *
 * @return bool
 */
function exist_rel_cli($codecli, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBREL . "` WHERE `rel_codecli` = '" . $db->escape($codecli) . "'";
    $nbexist = $db->get_var($rech2);
    if ($nbexist <> 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Mise à jour niveau du client
 * Le code "propriétaire" n'est pas touché
 *
 * @param      $codecli
 * @param bool $open
 */
function upd_nivcli($codecli, $open = TRUE)
{
    global
    $db,
    $datea,
    $_SESSION;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    $niveau = 0; // Niveau "Prospect" par défaut

    /*
    // Il y a des dossiers ?
    // Dans cas, niveau "Prospect"
    $rech2 = "SELECT COUNT(*) FROM `" .TBDOS. "` WHERE `dos_codecli` = '$codecli'";
    $nbexist = $db->get_var($rech2);
    if ($nbexist > 0)
        $niveau=0;
    */

    // Il y a une intervention ?
    // Dans cas, niveau "Demande d'intervention"
    $rech2 = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE `int_codecli` = '" . $db->escape($codecli) . "'";
    $nbexist = $db->get_var($rech2);
    if ($nbexist > 0)
        $niveau = 1;

    // Il y a une intervention close ?
    // Dans cas, niveau "déjà client"
    $rech2 = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE `int_codecli` = '" . $db->escape($codecli) . "' AND `int_datefin` <> '0000-00-00'";
    $nbexist = $db->get_var($rech2);
    if ($nbexist > 0)
        $niveau = 2;

    // Il y a un contrat ?
    // Dans cas, niveau "déjà client"
    if ($niveau < 2) {
        $rech2 = "SELECT COUNT(*) FROM `" . TBCOCL . "` WHERE `cocl_codecli` = '" . $db->escape($codecli) . "'";
        $nbexist = $db->get_var($rech2);
        if ($nbexist > 0)
            $niveau = 2;
    }

    // Il y a un contrat de location ?
    // Dans cas, niveau "déjà client"
    if ($niveau < 2) {
        $rech2 = "SELECT COUNT(*) FROM `" . TBCOLOC . "` WHERE `coloc_codecli` = '" . $db->escape($codecli) . "'";
        $nbexist = $db->get_var($rech2);
        if ($nbexist > 0)
            $niveau = 2;
    }

    // Il y a un Relevé ?
    // Dans cas, niveau "déjà client"
    if ($niveau < 2) {
        $rech2 = "SELECT COUNT(*) FROM `" . TBREL . "` WHERE `rel_codecli` = '" . $db->escape($codecli) . "'";
        $nbexist = $db->get_var($rech2);
        if ($nbexist > 0)
            $niveau = 2;
    }

    // Il y a un patrimoine ?
    // Dans cas, niveau "déjà client"
    if ($niveau < 2) {
        $rech2 = "SELECT COUNT(*) FROM `" . TBRPAT . "` WHERE `rpat_codecli` = '" . $db->escape($codecli) . "'";
        $nbexist = $db->get_var($rech2);
        if ($nbexist > 0)
            $niveau = 2;
    }

    // On met à jour
    $rech3 = "UPDATE `" . TBCLI . "` SET `cli_datemod`='" . $datea . "', `cli_codemod`='" . $_SESSION["code_log"] . "',";
    $rech3 .= " `cli_type`='" . $niveau . "'  WHERE `cli_code` = '" . $db->escape($codecli) . "'";
    $db->query($rech3);

    if ($open == TRUE)
        $db->disconnect();
}

/****************************/
/* Regroupements de clients */
/****************************/

/**
 * Retourne un tableau contenant les codes des clients gérés
 *
 * @param      $code
 * @param bool $open
 *
 * @return array
 */
function give_tab_rcli($code, $open = TRUE)
{
    global
    $db;
    $tab_rcli = [];
    $tab_rcli[] = $code;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBRCLI . "` WHERE rcli_maitre = '" . $db->escape($code) . "'";
    $rows2 = $db->get_results($rexist, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            $row2 = encode_str($row2);
            extract($row2);
            $tab_rcli[] = $rcli_esclave;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_rcli;
}

/**
 * Retourne le code "maître" d'un compte client
 *
 * @param      $code
 * @param bool $open
 *
 * @return array
 */
function give_tab_maitre_cli($code, $open = TRUE)
{
    global
    $db;
    $tab_maitre = [];

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT rcli_maitre FROM `" . TBRCLI . "` WHERE rcli_esclave = '" . $db->escape($code) . "'";
    $rows2 = $db->get_results($rexist, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $tab_maitre[] = $rcli_maitre;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_maitre;
}

/**
 * Retourne un tableau avec les agences pouvant gérer un site donné, sur une intervention donnée
 *
 * @param      $codeint
 * @param      $codecli
 * @param      $idsite
 * @param bool $open
 *
 * @return array
 */
function give_tab_age_scli($codeint, $codecli, $idsite, $open = TRUE)
{
    global
    $db,
    $_SESSION;

    $tab_codeages = [];

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    // On vérifie qui est l'agence gestionnaire du compte
    $gest_compte = '';
    if ($codecli <> '') {
        $rech2 = "SELECT cli_prop FROM `" . TBCLI . "` WHERE `cli_code` = '" . $db->escape($codecli) . "'";
        $row2 = $db->get_row($rech2, ARRAY_A);
        if ($row2) {
            $row2 = encode_str($row2);
            extract($row2);
            $gest_compte = $cli_prop;
        }
    }

    // On récupère le code technicien responsable
    // Et le code agence gestionnaire du site d'intervention
    $code_tecs = '';
    $code_ages = '';

    // On est en édition ou suppression
    if ($codeint <> '') {
        $rech2 = "SELECT int_idsite, int_codetec,int_codeages FROM `" . TBINT . "` WHERE `int_code` = '" . $db->escape($codeint) . "'";
        $row2 = $db->get_row($rech2, ARRAY_A);
        if ($row2) {
            $row2 = encode_str($row2);
            extract($row2);
            $code_tecs = $int_codetec;
            $code_ages = $int_codeages;
        }
        else // On est en ajout
        {
            switch (@$_SESSION["niv_log"]) {
                case 2 :
                    break;
                case 3 :
                    $code_ages = @$_SESSION["code_age"];
                    $code_tecs = @$_SESSION["code_log"];
                    break;
                case 4 :
                    $code_ages = @$_SESSION["code_age"];
                    break;
                default :
                    break;
            }
        }
    }

    // On crée le tableau des agences pouvant gérer un site secondaire pour ce client
    $tab_ages_possibles = [];
    $filtre_agence = '';
    switch (@$_SESSION["niv_log"]) {
        case 3 :
            if ($code_tecs == $_SESSION["code_log"]) {
                if (trim($gest_compte) <> '') // Une agence est affectée à ce client
                {
                    // Ce n'est pas l'agence gestionnaire du compte client qui saisi : on ne peut donc pas changer d'agence gestionnaire de site
                    if ($gest_compte <> @$_SESSION["code_age"]) {
                        $filtre_agence = @$_SESSION["code_age"];
                    }
                }
            }
            break;
        case 4 :
            if (trim($gest_compte) <> '') // Une agence est affectée à ce client
            {
                // Ce n'est pas l'agence gestionnaire du compte client qui saisi : on ne peut donc pas changer d'agence gestionnaire de site
                if (trim($gest_compte) <> @$_SESSION["code_age"]) {
                    $filtre_agence = @$_SESSION["code_age"];
                }
            }
            break;
        default :
            // Le gestionnaire de compte est gestionnaire du site principal
            if (trim($gest_compte) <> '') // Une agence est affectée à ce client
                $tab_ages_possibles[] = $gest_compte;
            break;
    }

    $rech2 = "SELECT DISTINCT scli_prop FROM `" . TBSCLI . "` WHERE `scli_codecli` = '$codecli'";
    if ($filtre_agence <> '')
        $rech2 .= " AND `scli_prop` ='" . $filtre_agence . "'";
    $rows2 = $db->get_results($rech2, ARRAY_A);
    if (($rows2) AND (count($rows2) > 0)) {
        foreach ($rows2 as $row2) {
            extract($row2);
            $tab_ages_possibles[] = $scli_prop;
        }
    } else // Aucun résultat : ce ne peut être que l'agence gestionnaire du compte
        $tab_codeages["-1"] = _("Gestionnaire du compte");

    // Un site est sélectionné : On vérifie quel est le gestionnaire de ce site
    $filtre_dep = '';
    $filtre_pays = '';
    if ($idsite > 0) {
        $rech2 = "SELECT scli_prop, scli_codepays, scli_dep FROM `" . TBSCLI . "` WHERE `scli_id` = '$idsite'";
        $row2 = $db->get_row($rech2, ARRAY_A);
        if ($row2) {
            $row2 = encode_str($row2);
            extract($row2);
            $filtre_dep = $scli_dep;
            $filtre_pays = $scli_codepays;
        }
    } else {
        $rech2 = "SELECT cli_prop, cli_codepays, cli_dep FROM `" . TBCLI . "` WHERE `cli_code` = '$codecli'";
        $row2 = $db->get_row($rech2, ARRAY_A);
        if ($row2) {
            $row2 = encode_str($row2);
            extract($row2);
            $filtre_dep = $cli_dep;
            $filtre_pays = $cli_codepays;
        }
    }

    // Il y a une agence gestionnaire du site dans l'intervention
    if ($code_ages <> '') {
        // Cette agence n'est pas dans le tableau des agences possible (cas où "-1" : toutes les agences couvrant le départempent)
        if (in_array($code_ages, $tab_ages_possibles) == FALSE)
            $tab_ages_possibles[] = $code_ages;
    }

    // Il y a des agences trouvées : on continue
    if (count($tab_ages_possibles) > 0) {
        foreach ($tab_ages_possibles as $value) {
            // On retrouve la liste des agences
            switch ($value) {
                // Toutes les agences couvrant le département de ce compte client
                case "-1" :
                    $rech2 = "SELECT zcc_code FROM `" . TBZCC . "` WHERE `zcc_codepays`='" . $filtre_pays . "' AND `zcc_codedep`='" . $filtre_dep . "'";
                    // $rech2.= " AND `zcc_code`='" . $value . "'";
                    $rows2 = $db->get_results($rech2, ARRAY_A);
                    if ($rows2 AND (count($rows2) > 0)) {
                        foreach ($rows2 as $row2) {
                            extract($row2);
                            $rech3 = "SELECT age_code, age_nom FROM `" . TBAGE . "` WHERE `age_code` = '" . $zcc_code . "'";
                            $row3 = $db->get_row($rech3, ARRAY_A);
                            if ($row3) {
                                $row3 = encode_str($row3);
                                extract($row3);
                                $tab_codeages[$age_code] = $age_nom . " (" . $age_code . ")";
                            }
                        }
                    }
                    break;
                // Uniquement agence gestionnaire du compte
                case "0" :
                    if (in_array($gest_compte, $tab_ages_possibles)) {
                        $rech2 = "SELECT age_code, age_nom FROM `" . TBAGE . "` WHERE `age_code` = '" . $gest_compte . "'";
                        if ($filtre_agence <> '')
                            $rech2 .= " AND `age_code` ='" . $filtre_agence . "'";
                        $row2 = $db->get_row($rech2, ARRAY_A);
                        if ($row2) {
                            $row2 = encode_str($row2);
                            extract($row2);
                            $tab_codeages[$age_code] = $age_nom . " (" . $age_code . ")";
                        }
                    }
                    break;
                // Uniquement une agence en particulier
                default :
                    $rech2 = "SELECT age_code, age_nom FROM `" . TBAGE . "` WHERE `age_code` = '" . $value . "'";
                    $row2 = $db->get_row($rech2, ARRAY_A);
                    if ($row2) {
                        $row2 = encode_str($row2);
                        extract($row2);
                        $tab_codeages[$age_code] = $age_nom . " (" . $age_code . ")";
                    }
                    break;
            }
        }
    }

    if (count($tab_codeages) == 0) // On n'a pas encore créé le tableau
    {
        foreach ($tab_ages_possibles as $value) {
            switch ($value) {
                // Toutes les agences couvrant le département de ce site
                case "-1" :
                    $tab_codeages["-1"] = _("Gestionnaire du compte");
                    break;
                // Uniquement agence gestionnaire du compte
                case "0" :
                    break;
                // Uniquement une agence en particulier
                default :
                    break;
            }
        }

    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_codeages;
}
