<?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"));
}

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

/**
 *
 *
 */
//
$f->displaysubtitle(__("Nombre d'emplacements / nature"));
// Récupération du nombre total d'emplacements
$sql = "select count(*) as sum FROM ".DB_PREFIXE."emplacement";
$qres_total = $f->get_one_result_from_db_query($sql, array(
    'origin' => 'app/widget_supervision.php'
));
$total = $qres_total['result'];
// Récupération du nombre d'emplacements par nature
$sql = "select nature, count(*) as sum
from ".DB_PREFIXE."emplacement
group by nature
order by nature";
$qres = $f->get_all_results_from_db_query($sql, array(
    'origin' => 'app/widget_supervision.php'
));
// Affichage du tableau résultat
echo "<table class=\"tab-tab\">";
// Composition de la ligne
$table_line =
"
<tr class=\"tab-data %s\">
    <td class=\"lib\">
        %s
    </td>
    <td class=\"right\">
        %s
    </td>
</tr>
";
// Compteur de ligne pour les odd/even du tableau
$k = 1;
// Boucle sur chaque ligne résultat
foreach ($qres['result'] as $row) {
    // 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>";

/**
 *
 */
//
$sql = "select cimetiere.cimetierelib, 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
group by cimetiere.cimetierelib, nature
order by cimetiere.cimetierelib, nature";
$qres2 = $f->get_all_results_from_db_query($sql, array(
    'origin' => 'app/widget_supervision.php'
));

//
if ($qres2['row_count'] != 0) {
    //
    $f->displaysubtitle(__("Nombre d'emplacements / cimetière / nature"));
    //
    echo "<div id=\"accordion\">";
    //
    $cimetiere = "";
    foreach ($qres2['result'] as $row) {
        //
        if ($cimetiere != $row["cimetierelib"]) {
            if ($cimetiere != "") {
                // Fermeture du tableau
                echo "</table>";
                echo "</div>";
            }
            //
            $cimetiere = $row["cimetierelib"];
            //
            echo "<h3>";
            echo "<a href=\"#\">";
            //
            echo $row["cimetierelib"];
            //
            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";
    $qres_dossier = $f->get_one_result_from_db_query($sql, array(
        'origin' => 'app/widget_supervision.php'
    ));
    $total = $qres_dossier['result'];
    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
    group by dossier.typedossier";
    $qres3 = $f->get_all_results_from_db_query($sql, array(
        'origin' => 'app/widget_supervision.php'
    ));
    //
    echo "<ul>";
    foreach ($qres3['result'] as $row) {
        //
        echo "<li>";
        echo $row["typedossier"];
        echo " : ";
        echo $row["sum"];
        echo "</li>";

    }
    echo "</ul>";
    echo "<br/>";


    /**
     *
     *
     */

    //
    $sql = "select count(*) as sum FROM ".DB_PREFIXE."defunt";
    $qres_defunt = $f->get_one_result_from_db_query($sql, array(
        'origin' => 'app/widget_supervision.php'
    ));
    $total = $qres_defunt['result'];
    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
    group by cimetiere.cimetierelib, emplacement.nature
    order by cimetiere.cimetierelib, emplacement.nature";
    $qres4 = $f->get_all_results_from_db_query($sql, array(
        'origin' => 'app/widget_supervision.php'
    ));
    //
    echo "<ul>";
    foreach ($qres4['result'] as $row) {

        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";
    $qres_autorisation = $f->get_one_result_from_db_query($sql, array(
        'origin' => 'app/widget_supervision.php'
    ));
    $total = $qres_autorisation['result'];
    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
    group by cimetiere.cimetierelib, emplacement.nature
    order by cimetiere.cimetierelib, emplacement.nature";
    $qres5 = $f->get_all_results_from_db_query($sql, array(
        'origin' => 'app/widget_supervision.php'
    ));
    //
    echo "<ul>";
    foreach ($qres5['result'] as $row) {

        echo "<li>";
        echo $row["cimetierelib"];
        echo " - ";
        echo $row["nature"];
        echo " : ";
        echo $row["sum"];
        echo "</li>";
    }
    echo "</ul>";

}

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