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

/**
 * Vérification du type de facturation sur un donneur d'ordre (par le CDG ou par le sous-traitant)
 *
 * @param $code
 * @param bool $open
 * @return int
 */
function mod_fact_st($code, $open = TRUE)
{
    global
    $db;
    $fact_st = 0;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT st_fact FROM `" . TBST . "` WHERE st_code = '" . $db->escape($code) . "'";
    $row2 = $db->get_row($rech2, ARRAY_A);
    if ($row2)
        $fact_st = $row2["st_fact"];
    if ($open == TRUE)
        $db->disconnect();

    return $fact_st;
}

/**
 * Vérification du nombre de sous-traitants actifs
 *
 * @param bool $open
 * @return null
 */
function nb_st_actifs($open = TRUE)
{
    global
    $db;

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

    return $nbexist;
}

/**
 * Donne le nombre de sous-traitants
 *
 * @param bool $open
 * @return null
 */
function nb_st($open = TRUE)
{
    global
    $db;

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

    return $nbexist;
}

/**
 * Lecture d'un enregistrement sous-traitant
 *
 * @param $code
 * @param bool $open
 * @return array|null
 */
function lit_enr_st($code, $open = TRUE)
{
    global
    $db;

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

    return $row2;
}

/**
 * Lecture d'un enregistrement sous-traitant à partir d'un code agence
 *
 * @param $code
 * @param bool $open
 * @return array|null
 */
function lit_enr_stage($code, $open = TRUE)
{
    global
    $db;

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

    return $row2;
}

/**
 * Vérifie si une agence est aussi sous-traitant
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_age_st($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech2 = "SELECT COUNT(*) FROM `" . TBST . "` WHERE st_codeage = '" . $db->escape($code) . "'";
    $nbres = $db->get_var($rech2);
    if ($nbres > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification du nombre d'ordres sur un sous-traitant
 *
 * @param $st
 * @param bool $open
 * @return null
 */
function nb_ot_st($st, $open = TRUE)
{
    global
    $db;

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

    return $nbexist;
}

/**
 * Vérification de la présence d'un donneur d'ordres dans un bon de travail
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_st_in_ot($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBOT . "` WHERE `ot_codest` = '" . $db->escape($code) . "'";
    $nbres = $db->get_var($rexist);
    if ($nbres > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

/**
 * Vérification de la présence d'un donneur d'ordres dans un bon de travail non clôt
 *
 * @param $code
 * @param bool $open
 * @return bool
 */
function exist_st_in_ot_ouv($code, $open = TRUE)
{
    global
    $db;
    $exist = FALSE;
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rexist = "SELECT COUNT(*) FROM `" . TBOT . "` WHERE `ot_codest` = '" . $db->escape($code) . "' AND `ot_datefin` = '0000-00-00'";
    $nbres = $db->get_var($rexist);
    if ($nbres > 0)
        $exist = TRUE;
    if ($open == TRUE)
        $db->disconnect();

    return $exist;
}

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

    return $exist;
}

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

    return $exist;
}

/**
 * Retourne un tableau des sous-traitants
 *
 * @param string $except : sauf un sous-traitant (si vide, on en tient pas compte)
 * @param bool $open
 *
 * @return array
 */

function give_tab_st($except = '', $open = TRUE)
{
    global
    $db;

    $tab_res = [];

    // Création de la liste des sous-traitants ouverts
    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);

    $rech = "SELECT st_code,st_nom FROM `" . TBST . "` WHERE `st_ouvert` = '1'";

    if ($except <> '')
        $rech.= " AND `st_code` <> '".$except."'";
    $rech.= " ORDER BY st_nom";

    $rows = $db->get_results($rech, ARRAY_A);
    if ($rows) {
        foreach ($rows as $row) {
            $row = encode_str($row);
            extract($row);
            $tab_res[$st_code] = $st_nom . " (" . $st_code . ")";
        }
    }

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

    return $tab_res;
}

// Contacts Sous-traitants

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

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

    return $nbexist;
}

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

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

    return $row2;
}

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

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

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

    $tab_cst = [];

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

    // Récupération du contact principal
    $rech = "SELECT st_resp_nom,st_resp_prenom, st_resp_telf, st_resp_telp FROM `" . TBST . "` WHERE `st_code` = '" . $db->escape($code) . "'";
    $row = $db->get_row($rech, ARRAY_A);
    if ($row) {
        $row = encode_str($row);
        extract($row);

        // Contact principal
        $txt_cst = $st_resp_nom . " " . $st_resp_prenom . " (";
        $txt_cst .= _("Contact principal");
        $txt_cst .= " - " . $st_resp_telf;
        if ($st_resp_telp <> "")
            $txt_cst .= " / " . $st_resp_telp;
        $txt_cst .= ")";
        $tab_cst[-1] = $txt_cst;
        // $tab_cst["-1"] = $txt_cst;
    }
    // Contacts du sous-traitant
    $rech = "SELECT * FROM `" . TBCST . "` WHERE `cst_codest` = '" . $db->escape($code) . "'";
    $rech .= " ORDER BY cst_nom";
    $rows = $db->get_results($rech, ARRAY_A);
    if ($rows) {
        foreach ($rows as $row) {
            $row = encode_str($row);
            extract($row);

            $txt_cst = $cst_nom . " " . $cst_prenom;
            $row2 = lit_enr_fonc($cst_codefonc, FALSE);
            if (is_array($row2)) {
                $row2 = encode_str($row2);
                extract($row2);
                $txt_fonc = $fonc_desc;
            } else
                $txt_fonc = $cst_codefonc;
            $txt_cst .= " (";
            $txt_cst .= $txt_fonc;
            $txt_cst .= " - " . $cst_telf;
            if ($cst_telp <> "")
                $txt_cst .= " / " . $cst_telp;
            $txt_cst .= ")";
            $tab_cst[$cst_id] = $txt_cst;
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_cst;
}

// Spécialités

/**
 * Retourne un tableau des spécialités
 *
 * @param $codest
 * @param bool $open
 * @return array
 */
function give_tab_sp_st($codest, $open = TRUE)
{
    global
        $db;

    $tab_sp = [];

    if ($open == TRUE)
        $db = open_database(DBUSER, DBPASS, DBNAME, DBHOST);
    $rech = "SELECT * FROM `" . TBSTSP . "` WHERE `stsp_codest` = '$codest'";;
    $rows = $db->get_results($rech, ARRAY_A);
    if ($rows) {
        foreach ($rows as $row) {
            $row = encode_str($row);
            extract($row);
            $rech2 = "SELECT * FROM `" . TBSP . "` WHERE `sp_code` = '" . $stsp_codesp . "'";;
            $row2 = $db->get_row($rech2, ARRAY_A);
            if ($row2) {
                $row2 = encode_str($row2);
                extract($row2);
                $tab_sp[$sp_code]["des"] = $sp_desc;
                $tab_sp[$sp_code]["niv"] = $stsp_niveau;
            }
        }
    }
    if ($open == TRUE)
        $db->disconnect();

    return $tab_sp;
}
