<?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 agences  */
/* et fichiers rattachés                          */
/**************************************************/

// Catégories

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

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

    return $nbexist;
}

/**
 * Lecture d'un enregistrement catégorie agence
 *
 * @param $code
 * @param bool $open
 * @return array|null
 */
function lit_enr_acat($code, $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBACAT . "` WHERE acat_code = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

// Agences

/**
 * Vérification du nombre d'interventions sur une agence
 *
 * @param $agence
 * @param bool $open
 * @return null
 */
function nb_int($agence, $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    if ($agence <> "")
        $rexist = "SELECT COUNT(*) FROM `" . TBINT . "` WHERE `int_codeage` = '" . $db->escape($agence) . "'";
    else
        $rexist = "SELECT COUNT(*) FROM `" . TBINT . "`";
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Vérification du type de facturation sur une agence (par le CDG ou par l'agence)
 *
 * @param $code
 * @param bool $open
 * @return int
 */
function mod_fact_age($code, $open = TRUE)
{
    global
    $db;
    $age_fact = 0;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT age_fact FROM `" . TBAGE . "` WHERE age_code = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($row2) {
        $row2 = encode_str($row2);
        extract($row2);
    }
    if ($open == TRUE)
        $db->disconnect();
    return $age_fact;
}

/**
 * Vérification si une agence peut louer
 *
 * @param $code
 * @param bool $open
 * @return int
 */
function age_loc_ok($code, $open = TRUE)
{
    global
    $db;
    $age_loc = 0;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT age_loc FROM `" . TBAGE . "` WHERE age_code = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($row2)
        extract($row2);
    if ($open == TRUE)
        $db->disconnect();
    return $age_loc;
}

/**
 * Vérification du nombre d'agences actives
 *
 * @param bool $open
 * @return null
 */
function nb_age_actives($open = TRUE)
{
    global
    $db;

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

    return $nbexist;
}

/**
 * Vérifie si un client (ou un  de ses sites secondaires) est bien géré par une agence (dans le département couvert
 * et pas déjà géré par une autre agence)
 *
 * @param $client
 * @param $agence
 * @param bool $open
 * @return bool
 */
function client_age_ok($client, $agence, $open = TRUE)
{
    global
    $db;
    $result = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    // On lit le client
    $rech = "SELECT cli_codepays,cli_dep,cli_prop FROM `" . TBCLI . "` WHERE `cli_code` = '" . $db->escape($client) . "'";
    $row = $db->get_row($rech, ARRAY_A);
    if ($row) {
        $row = encode_str($row);
        extract($row);
        if (($cli_prop <> NULL) AND (trim($cli_prop) <> "")) // Ce client est affecté à une agence
        {
            // Ce client n'est pas affecté à cette agence, on vérifie si elle gère un site secondaire de ce client
            if ($cli_prop <> $agence) {
                // Il y a des sites secondaires gérés par cette agence?
                $rech2 = "SELECT COUNT(*) FROM `" . TBSCLI . "` WHERE `scli_codecli` = '" . $db->escape($client) . "' AND `scli_prop` = '" . $db->escape($agence) . "'";
                $nb_rs2 = $db->get_var($rech2);
                if ($nb_rs2 > 0)
                    return TRUE;
                else // Elle n'en gère pas, on vérifie qu'elle ne couvre pas un site noté comme "agences couvrant ce département"
                {
                    $rech3 = "SELECT scli_codepays,scli_dep FROM `" . TBSCLI . "` WHERE `scli_codecli` = '" . $db->escape($client) . "' AND `scli_prop` = '-1'";
                    $rows3 = $db->get_results($rech3, ARRAY_A);
                    if (($rows3) AND (count($rows3) > 0)) {
                        foreach ($rows3 as $row3) {
                            extract($row3);
                            $rech4 = "SELECT COUNT(*) FROM `" . TBZCC . "` WHERE `zcc_code` = '" . $db->escape($agence) . "' AND `zcc_codepays` = '" . $db->escape($scli_codepays) . "' AND `zcc_codedep` = '" . $db->escape($scli_dep) . "'";
                            $nbres4 = $db->get_var($rech4);
                            if ($nbres4 > 0) {
                                $result = TRUE;
                                break;
                            }
                        }
                    }
                }
            } else
                $result = TRUE;
        } // On lit l'agence pour vérifier si les départements sont ok
        else {
            $rech3 = "SELECT COUNT(*) FROM `" . TBZCC . "` WHERE `zcc_code` = '" . $db->escape($agence) . "' AND `zcc_codepays` = '" . $db->escape($cli_codepays) . "' AND `zcc_codedep` = '" . $db->escape($cli_dep) . "'";
            $nb_rs3 = $db->get_var($rech3);
            if ($nb_rs3 > 0)
                $result = TRUE;
        }
    }
    if ($open == TRUE)
        $db->disconnect();
    return $result;
}

/**
 * Donne le niveau de gestion du client
 * -1 : Rien autorisé
 * 0 : lecture seule (site principal)
 * 9 : tout autorisé
 *
 * @param $client
 * @param $agence
 * @param bool $open
 * @return int
 */
function nivges_cli($client, $agence, $open = TRUE)
{
    global
    $db,
    $_SESSION;
    $nivges = -1;

    switch ($_SESSION["niv_log"]) {
        case 3 :
        case 4 :
            if ($open == TRUE)
                $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
            // On lit le client
            $rech = "SELECT cli_prop,cli_codepays,cli_dep FROM `" . TBCLI . "` WHERE `cli_code` = '" . $db->escape($client) . "' AND (`cli_prop` = '" . $db->escape($agence) . "' OR `cli_prop`= '' OR `cli_prop` IS NULL)";
            $row = $db->get_row($rech, ARRAY_A);
            if ($row) {
                extract($row);
                // Ce client n'est pas affecté à cette agence (mode "Client géré par aucune agence en particulier"),
                // on vérifie si elle gère un site secondaire
                if ($cli_prop <> $agence) {
                    // On teste si le client est dans la zone de chalandise de l'agence
                    $rech2 = "SELECT COUNT(*) FROM `" . TBZCC . "` WHERE `zcc_code` = '" . $db->escape($agence) . "' AND `zcc_codepays` = '" . $db->escape($cli_codepays) . "' AND `zcc_codedep` = '" . $db->escape($cli_dep) . "'";
                    $nb_rs2 = $db->get_var($rech2);
                    if ($nb_rs2 > 0)
                        $nivges = 9;
                    else // Pas dans la zone de chalandise...on vérifie les sites secondaires
                    {
                        // On teste s'il y a des sites gérés par l'agence gérant le site principal
                        // (code propriétaire exact ou agence idem gestionnaire site principal ou agence couvrant le département)
                        $rech3 = "SELECT COUNT(*) FROM `" . TBSCLI . "` WHERE `scli_codecli` = '" . $db->escape($client) . "' AND (`scli_prop` = '" . $db->escape($agence) . "' OR `scli_prop` = '0' OR `scli_prop` = '-1')";
                        $nb_rs3 = $db->get_var($rech3);
                        if ($nb_rs3 > 0) // Oui : on ne peut que lire (le site principal n'est pas gérable par cette agence)
                            $nivges = 0;
                    }
                } else // Le client est affecté à cette agence : on peut tout faire
                    $nivges = 9;
            }
            if ($open == TRUE)
                $db->disconnect();
            break;
        case 5 :
        case 9 :
            $nivges = 9;
            break;
    }

    return $nivges;
}

/**
 * Retourne la liste des agences pour un client
 * $codecli : si vide, on prend toutes les agences ouvertes
 * $except : sauf une agence (si vide, on en tient pas compte)
 *
 * @param $codecli
 * @param string $except
 * @param bool $open
 *
 * @return array
 */
function give_tab_age_cli($codecli, $except = '', $open = TRUE)
{
    global
    $db;

    $tab_res = [];

    // Création de la liste des agences ouvertes et couvrant le client
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    // On récupére les données du client
    $rech = '';
    if (trim($codecli <> ''))
    {
        $rech1 = "SELECT cli_codepays, cli_dep FROM `" . TBCLI . "` WHERE `cli_code` = '" . $db->escape($codecli) . "'";
        $row = $db->get_row($rech1, ARRAY_A);
        if ($row)
        {
            extract($row);
            // On récupère la liste des agences actives couvrant le pays du client
            $rech2 = "SELECT zcc_code FROM `" . TBZCC . "` WHERE `zcc_codepays`='" . $db->escape($cli_codepays) . "' AND `zcc_codedep`='" . $db->escape($cli_dep) . "'";
            $rows2 = $db->get_results($rech2, ARRAY_A);
            if ($rows2)
            {
                if (count($rows2) >0)
                {
                    $rech = "SELECT age_code,age_nom FROM `" . TBAGE . "` WHERE (";
                    foreach ($rows2 as $row2)
                    {
                        $rech .= " age_code = '" . $db->escape($row2["zcc_code"]) . "' OR";
                    }
                    // Suppression du dernier 'OR'
                    $rech = substr($rech, 0, strlen($rech) - 3);
                    $rech.= ") AND`age_ouvert` = '1'";
                }
            }
        }
    }
    else
        // On récupère la liste de toutes les agences actives
        $rech = "SELECT age_code,age_nom FROM `" . TBAGE . "` WHERE `age_ouvert` = '1'";

    if ($rech <>'')
    {
        if ($except <> '')
            $rech.= " AND `age_code` <> '".$except."'";
        $rech.= " ORDER BY age_nom";
        $rows = $db->get_results($rech, ARRAY_A);
        if ($rows) {
            foreach ($rows as $row) {
                $row = encode_str($row);
                extract($row);
                if (trim($codecli <> ''))
                {
                    if (dep_in_zcc_age_open($cli_dep, $cli_codepays, $age_code, FALSE))
                        $tab_res[$age_code] = $age_nom . " (" . $age_code . ")";
                }
                else
                    $tab_res[$age_code] = $age_nom . " (" . $age_code . ")";
            }
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_res;
}

/**
 * Donne le nombre d'agences
 *
 * @param bool $open
 * @return int
 */
function nb_age($open = TRUE)
{
    global
    $db;

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

    return $nbexist;
}

/**
 * Lecture d'un enregistrement agence
 *
 * @param $code
 * @param bool $open
 * @return array|null
 */
function lit_enr_age($code, $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBAGE . "` WHERE age_code = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

/**
 * Vérification de la présence d'une agence dans un contrat non clôt
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_cocl_ouv($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBCOCL . "` WHERE `cocl_codeage` = '" . $db->escape($code) . "' AND `cocl_signe` IS NOT NULL AND `cocl_datefin` = '0000-00-00'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans une fiche intervention
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_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_codeage` = '" . $db->escape($code) . "'";
    $nb_rs2 = $db->get_var($rexist);
    if ($nb_rs2 <> 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans une fiche intervention non close
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_int_ouv($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBINT . "` WHERE `int_codeage` = '" . $db->escape($code) . "' AND `int_datefin` = '0000-00-00'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();;

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans une fiche technicien non licencié
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_tec_present($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBTEC . "` WHERE `tec_codeage` = '" . $db->escape($code) . "' AND `tec_out` = '0'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();;

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans une fiche technicien
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_tec($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBTEC . "` WHERE `tec_codeage` = '" . $db->escape($code) . "'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();;

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans un dépôt de location
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_dloc($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBDLOC . "` WHERE `dloc_codeage` = '" . $db->escape($code) . "'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();;

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans un contrat de location
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_coloc($code,$open=TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBCOLOC . "`";
    $rexist .= " WHERE coloc_codeage = '" . $db->escape($code) . "'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();;

    return $exist;
}

/**
 * Vérification de la présence d'une agence dans un contrat de location ouvert
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_in_coloc_ouv($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBCOLOC . "`";
    $rexist .= " WHERE coloc_codeage = '" . $db->escape($code) . "'";
    $rexist .= " AND coloc_datefin = '0000-00-00'";
    $row = $db->get_row($rexist, ARRAY_A);
    if ($row)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();;

    return $exist;
}


// Contacts Agences

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

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

    return $nbexist;
}

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

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT * FROM `" . TBCAGE . "` WHERE `cage_id` = '$id' AND `cage_codeage` = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rexist, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

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

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

/**
 * Retourne la liste des contacts agence
 *
 * @param $code
 * @param bool $open
 * @return array
 */
function give_tab_cage($code, $open = TRUE)
{
    global
    $db;

    $tab_cage = [];

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

    // Récupération du contact principal
    $rech = "SELECT age_resp_nom,age_resp_prenom, age_resp_telf, age_resp_telp FROM `" . TBAGE . "` WHERE `age_code` = '" . $db->escape($code) . "'";
    $row = $db->get_row($rech, ARRAY_A);
    if ($row) {
        $row = encode_str($row);
        extract($row);

        // Contact principal
        $txt_cage = $age_resp_nom . " " . $age_resp_prenom . " (";
        $txt_cage .= _("Contact principal");
        $txt_cage .= " - " . $age_resp_telf;
        if ($age_resp_telp <> "")
            $txt_cage .= " / " . $age_resp_telp;
        $txt_cage .= ")";
        $tab_cage[-1] = $txt_cage;
        // $tab_cage["-1"] = $txt_cage;
    }
    // Contacts de l'agence
    $rech = "SELECT * FROM `" . TBCAGE . "` WHERE `cage_codeage` = '" . $db->escape($code) . "'";
    $rech .= " ORDER BY cage_nom";
    $rows = $db->get_results($rech, ARRAY_A);
    if ($rows) {
        foreach ($rows as $row) {
            $row = encode_str($row);
            extract($row);

            $txt_cage = $cage_nom . " " . $cage_prenom;
            $row2 = lit_enr_fonc($cage_codefonc, FALSE);
            if (is_array($row2)) {
                $row2 = encode_str($row2);
                extract($row2);
                $txt_fonc = $fonc_desc;
            } else
                $txt_fonc = $cage_codefonc;
            $txt_cage .= " (";
            $txt_cage .= $txt_fonc;
            $txt_cage .= " - " . $cage_telf;
            if ($cage_telp <> "")
                $txt_cage .= " / " . $cage_telp;
            $txt_cage .= ")";
            $tab_cage[$cage_id] = $txt_cage;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_cage;
}


// Prix de revient

/**
 * Vérification du nombre de prix de revient enregistrés pour une agence
 *
 * @param $age
 * @param bool $open
 * @return int
 */
function nb_agepr($age, $open = TRUE)
{
    global
    $db;


    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    if ($age <> "")
        $rexist = "SELECT COUNT(*) FROM `" . TBAGEPR . "` WHERE `agepr_codeage` = '" . $db->escape($age) . "'";
    else
        $rexist = "SELECT COUNT(*) FROM `" . TBAGEPR . "`";
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Vérification s'il y a un problème de prix de revient pour les responsables des agences ouvertes
 *
 * @param bool $open
 * @return int
 */
function pbm_agespr($open = TRUE)
{
    global
    $db;
    $pbm = 0;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT age_code FROM `" . TBAGE . "` WHERE age_ouvert='1'";
    $rows2 = $db->get_results($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();
    if ($rows2) {
        foreach ($rows2 as $row2) {
            extract($row2);
            if (nb_agepr($age_code, $open) == 0) {
                $pbm = 1;
                return $pbm;
            }
        }
    }

    return $pbm;
}

/**
 * Lecture d'un enregistrement prix de revient agence
 *
 * @param $id
 * @param bool $open
 * @return array|null
 */
function lit_enr_agepr($id, $open = TRUE)
{
    global
    $db;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBAGEPR . "` WHERE `agepr_id` = '$id'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();

    return $row2;
}

/**
 * Retourne le dernier enregistrement d'un prix de revient agence
 *
 * @param $code
 * @param bool $open
 * @return int
 */
function last_id_agepr($code, $open = TRUE)
{
    global
    $db;
    $agepr_id = 0;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT agepr_id FROM `" . TBAGEPR . "` WHERE `agepr_codeage` = '" . $db->escape($code) . "' ORDER BY agepr_id DESC";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($row2)
        extract($row2);
    if ($open == TRUE)
        $db->disconnect();

    return $agepr_id;
}

/**
 * Retourne la dernière date de modif d'un prix de revient agence
 *
 * @param $id
 * @param bool $open
 * @return bool|string
 */
function last_date_agepr($id, $open = TRUE)
{
    global
    $db,
    $datea;
    $agepr_date = $datea;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT agepr_date FROM `" . TBAGEPR . "` WHERE `agepr_id` = '$id'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($row2)
        extract($row2);
    if ($open == TRUE)
        $db->disconnect();

    return $agepr_date;
}

/**
 * Vérification du nombre de prix de revient enregistrés pour un contact agence
 *
 * @param $idcage
 * @param bool $open
 * @return int
 */
function nb_cagepr($idcage, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    if ($agence <> "")
        $rexist = "SELECT COUNT(*) FROM `" . TBCAGEPR . "` WHERE `cagepr_idcage` = '" . $idcage . "'";
    else
        $rexist = "SELECT COUNT(*) FROM `" . TBCAGEPR . "`";
    $nbexist = $db->get_var($rexist);
    if ($open == TRUE)
        $db->disconnect();

    return $nbexist;
}

/**
 * Vérification s'il y a un problème de prix de revient pour les contacts d'une agence
 *
 * @param $agence
 * @param bool $open
 * @return int
 */
function pbm_cagespr($agence, $open = TRUE)
{
    global
    $db;
    $pbm = 0;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    if ($agence <> "")
        $rech2 = "SELECT cage_id FROM `" . TBCAGE . "` WHERE `cage_codeage` = '" . $db->escape($agence) . "'";
    else
        $rech2 = "SELECT cage_id FROM `" . TBCAGE . "`";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($row2) {
        extract($row2);
        $rech3 = "SELECT * FROM `" . TBCAGEPR . "` WHERE `cagepr_idcage` = '" . $cage_id . "'";
        $nb_rs3 = $db->get_var($rech3);
        if ($nb_rs3 == 0) {
            $pbm = 1;
            return $pbm;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $pbm;
}

/**
 * Lecture d'un enregistrement prix de revient pour un contact agence
 *
 * @param $id
 * @param bool $open
 * @return array|null
 */
function lit_enr_cagepr($id, $open = TRUE)
{
    global
    $db;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT * FROM `" . TBCAGEPR . "` WHERE `cagepr_id` = '$id'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($open == TRUE)
        $db->disconnect();
    return $row2;
}

/**
 * Retourne le dernier enregistrement d'un prix de revient contact agence
 *
 * @param $id
 * @param bool $open
 * @return int
 */
function last_id_cagepr($id, $open = TRUE)
{
    global
    $db;
    $cagepr_id = 0;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT cagepr_id FROM `" . TBCAGEPR . "` WHERE `cagepr_idcage` = '$id' ORDER BY cagepr_id DESC";
    $row2 = $db->get_row($rech2, ARRAY_A);;
    if ($row2)
        extract($row2);
    if ($open == TRUE)
        $db->disconnect();

    return $cagepr_id;
}

/**
 * Retourne la dernière date de modif d'un prix de revient contact agence
 *
 * @param $id
 * @param bool $open
 * @return bool|string
 */
function last_date_cagepr($id, $open = TRUE)
{
    global
    $db,
    $datea;
    $cagepr_date = $datea;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT cagepr_date FROM `" . TBCAGEPR . "` WHERE `cagepr_id` = '$id'";
    $row2 = $db->get_row($rech2, ARRAY_A);;
    if ($row2)
        extract($row2);
    if ($open == TRUE)
        $db->disconnect();

    return $cagepr_date;
}
