<?php
/**
 * Widget - Supervision
 *
 * Ce widget permet d'afficher des compteurs sur le nombre d'emplacements
 * par type d'emplacement et par cimetière.
 *
 * @package opencimetiere
 * @version SVN : $Id$
 */

require_once "../obj/opencimetiere.class.php";
if (!isset($f)) {
    $f = new opencimetiere(null, null, __("Widget - Supervision"));
}

/**
 * Template
 */
// Composition de la ligne
$table_line =
"
<tr class=\"tab-data %s\">
    <td class=\"lib\">
        %s
    </td>
    <td class=\"right\">
        %s
    </td>
</tr>
";

/**
 *
 */
//
echo "<div id=\"widget-supervision\">";


if ($_SESSION['niveau'] == '1') {
    /**
     *
     *
     */
    //
    $f->displaysubtitle(__("Nombre d'emplacements / nature"));
    // Récupération du nombre total d'emplacements
    $sql = "select count(*) as sum FROM ".DB_PREFIXE."emplacement";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $total = $f->db->getone($sql);
    $f->addToLog("app/widget_supervision.php: db->getone(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($total);
    // Récupération du nombre d'emplacements par nature
    $sql = "select nature, count(*) as sum
    from ".DB_PREFIXE."emplacement ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $sql .= "group by nature
    order by nature";
    $res =& $f->db->query($sql);
    $f->addToLog("app/widget_supervision.php: db->query(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($res);
    // Affichage du tableau résultat
    echo "<table class=\"tab-tab\">";
    // Compteur de ligne pour les odd/even du tableau
    $k = 1;
    // Boucle sur chaque ligne résultat
    while ($row =& $res->fetchrow(DB_FETCHMODE_ASSOC)) {
        // Affichage de la ligne
        printf($table_line,
               ($k%2 == true ? "odd": "even"),
               __($row["nature"]),
               $row["sum"]);
        $k++;
    }
    // Affichage d'une ligne total
    printf($table_line,
           "total",
           __("total"),
           $total);
    // Fermeture du tableau
    echo "</table>";
}

/**
 *
 */
//
if ($_SESSION["niveau"] == "2") {
    $sql = "select om_collectivite.libelle as elemlib, emplacement.nature, count(*) as sum
    FROM
        ".DB_PREFIXE."emplacement
        LEFT JOIN ".DB_PREFIXE."om_collectivite
            ON emplacement.om_collectivite=om_collectivite.om_collectivite
    group by emplacement.om_collectivite, om_collectivite.libelle, emplacement.nature
    order by emplacement.om_collectivite, om_collectivite.libelle, emplacement.nature";
    $by_elem = __("collectivité");
} else {
    $sql = "select cimetiere.cimetierelib as elemlib, emplacement.nature, count(*) as sum
    FROM
        ".DB_PREFIXE."emplacement
        LEFT JOIN ".DB_PREFIXE."voie
            ON emplacement.voie=voie.voie
        LEFT JOIN ".DB_PREFIXE."zone
            ON voie.zone=zone.zone
        LEFT JOIN ".DB_PREFIXE."cimetiere
            ON zone.cimetiere=cimetiere.cimetiere
    WHERE
        (emplacement.om_collectivite = '".$_SESSION["collectivite"]."')
    group by cimetiere.cimetierelib, emplacement.nature
    order by cimetiere.cimetierelib, emplacement.nature";
    $by_elem = __("cimetière");
}
$qres = $f->get_all_results_from_db_query(
    $sql,
    array(
        "origin" => "app/widget_supervision.php",
    )
);

//
$f->displaysubtitle(sprintf(__("Nombre d'emplacements / %s / nature"), $by_elem));
if (count($qres["result"]) > 0) {
    //
    echo "<div id=\"accordion\">";
    //
    $elem = "";
    foreach($qres["result"] as $row) {
        //
        if ($elem != $row["elemlib"]) {
            if ($elem != "") {
                // Fermeture du tableau
                echo "</table>";
                echo "</div>";
            }
            //
            $elem = $row["elemlib"];
            //
            echo "<h3>";
            echo "<a href=\"#\">";
            //
            echo $row["elemlib"];
            //
            echo "</a>";
            echo "</h3>";
            //
            echo "<div>";
            // Affichage du tableau résultat
            echo "<table class=\"tab-tab\">";
            // Compteur de ligne pour les odd/even du tableau
            $k = 1;
        }
        // Affichage de la ligne
        printf($table_line,
               ($k%2 == true ? "odd": "even"),
               __($row["nature"]),
               $row["sum"]);
        $k++;
    }
    // Fermeture du tableau
    echo "</table>";
    echo "</div>";
    //
    echo "</div>";
    echo "<br/>";
}

/**
 * Les éléments suivants ne servent pas dans le widget mais font partie de la
 * supervision pour l'administrateur. Il suffit d'appeler l'url :
 * ../app/widget_supervision.php?more pour obtenir des informations
 * supplémentaires
 */
if (isset($_GET["more"])) {

    /**
     *
     *
     */
    
    //
    $sql = "select count(*) as sum FROM ".DB_PREFIXE."dossier LEFT JOIN ".DB_PREFIXE."emplacement ON dossier.emplacement=emplacement.emplacement ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $total = $f->db->getone($sql);
    $f->addToLog("app/widget_supervision.php: db->getone(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($total);
    echo "<h2>DOSSIERS => ".$total."</h2>";
    
    
    //
    $sql = "select dossier.typedossier, count(*) as sum
    FROM
        ".DB_PREFIXE."dossier
        LEFT JOIN ".DB_PREFIXE."emplacement
            ON dossier.emplacement=emplacement.emplacement
        LEFT JOIN ".DB_PREFIXE."voie
            ON emplacement.voie=voie.voie
        LEFT JOIN ".DB_PREFIXE."zone
            ON voie.zone=zone.zone
        LEFT JOIN ".DB_PREFIXE."cimetiere
            ON zone.cimetiere=cimetiere.cimetiere ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $sql .= " group by dossier.typedossier";
    $res =& $f->db->query($sql);
    $f->addToLog("app/widget_supervision.php: db->query(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($res);
    //
    echo "<ul>";
    while ($row =& $res->fetchrow(DB_FETCHMODE_ASSOC)) {
        //
        echo "<li>";
        echo $row["typedossier"];
        echo " : ";
        echo $row["sum"];
        echo "</li>";
    
    }
    echo "</ul>";
    echo "<br/>";
    
    
    /**
     *
     *
     */
    
    //
    $sql = "select count(*) as sum FROM ".DB_PREFIXE."defunt LEFT JOIN ".DB_PREFIXE."emplacement ON defunt.emplacement=emplacement.emplacement ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $total = $f->db->getone($sql);
    $f->addToLog("app/widget_supervision.php: db->getone(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($total);
    echo "<h2>DEFUNTS => ".$total."</h2>";
    
    $f->displaysubtitle(__("Nombre de défunts / cimetière / nature"));
    
    //
    $sql = "select cimetiere.cimetierelib, emplacement.nature, count(*) as sum
    FROM
        ".DB_PREFIXE."defunt
        LEFT JOIN ".DB_PREFIXE."emplacement
            ON defunt.emplacement=emplacement.emplacement
        LEFT JOIN ".DB_PREFIXE."voie
            ON emplacement.voie=voie.voie
        LEFT JOIN ".DB_PREFIXE."zone
            ON voie.zone=zone.zone
        LEFT JOIN ".DB_PREFIXE."cimetiere
            ON zone.cimetiere=cimetiere.cimetiere ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $sql .= " group by cimetiere.cimetierelib, emplacement.nature
    order by cimetiere.cimetierelib, emplacement.nature";
    $res =& $f->db->query($sql);
    $f->addToLog("app/widget_supervision.php: db->query(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($res);
    //
    echo "<ul>";
    while ($row =& $res->fetchrow(DB_FETCHMODE_ASSOC)) {
        
        echo "<li>";
        echo $row["cimetierelib"];
        echo " - ";
        echo $row["nature"];
        echo " : ";
        echo $row["sum"];
        echo "</li>";
    }
    echo "</ul>";
    echo "<br/>";
    
    /**
     *
     */
    
    //
    $sql = "select count(*) as sum FROM ".DB_PREFIXE."autorisation LEFT JOIN ".DB_PREFIXE."emplacement ON autorisation.emplacement=emplacement.emplacement ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $total = $f->db->getone($sql);
    $f->addToLog("app/widget_supervision.php: db->getone(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($total);
    echo "<h2>CONTACTS => ".$total."</h2>";
    
    //
    $f->displaysubtitle(__("Nombre de contacts / cimetière / nature"));
    
    //
    $sql = "select cimetiere.cimetierelib, emplacement.nature, count(*) as sum
    FROM
        ".DB_PREFIXE."autorisation
        LEFT JOIN ".DB_PREFIXE."emplacement
            ON autorisation.emplacement=emplacement.emplacement
        LEFT JOIN ".DB_PREFIXE."voie
            ON emplacement.voie=voie.voie
        LEFT JOIN ".DB_PREFIXE."zone
            ON voie.zone=zone.zone
        LEFT JOIN ".DB_PREFIXE."cimetiere
            ON zone.cimetiere=cimetiere.cimetiere ";
    // Filtre MONO
    if ($_SESSION["niveau"] == "1") {
        $sql .= " WHERE (emplacement.om_collectivite = '".$_SESSION["collectivite"]."') ";
    }
    $sql .= " group by cimetiere.cimetierelib, emplacement.nature
    order by cimetiere.cimetierelib, emplacement.nature";
    $res =& $f->db->query($sql);
    $f->addToLog("app/widget_supervision.php: db->query(\"".$sql."\");", VERBOSE_MODE);
    $f->isDatabaseError($res);
    //
    echo "<ul>";
    while ($row =& $res->fetchrow(DB_FETCHMODE_ASSOC)) {
        
        echo "<li>";
        echo $row["cimetierelib"];
        echo " - ";
        echo $row["nature"];
        echo " : ";
        echo $row["sum"];
        echo "</li>";
    }
    echo "</ul>";

}

/**
 *
 */
//
echo "</div>\n";