Liberations/compilation_soldes.inc.php

<?PHP

CONST PX170 = "170px";

// ------------------------------
// Caractéristiques du module
// ------------------------------
unset($module);
$module = array(
    "titre" => "Compilation des soldes",
    "urldest" => $SCRIPT_NAME . "?page=compilation_soldes",
    "autre" => 1,
    "tailleentete" => "1220",
    "tailleliste" => "800px",
    "boutonfermeraction" => "fermerfenetre",
    "colonneaction" => false,
    "lientri" => false,
    "jeton" => $jeton,
    "entete" => array(
        1 => array("Article" => "refarticle", "largeur" => "85px"),
        2 => array("Convention" => "expression", "largeur" => PX170),
        3 => array("Utilisation" => "expression", "largeur" => PX170),
        4 => array("Solde" => "expression", "largeur" => PX170),
    ),
    "colonneaction" => false,
);

if ($_SESSION['gestion_site'] == 'Non') {
    die();
}

// ****************************************************
// Requête SQL principale
// ****************************************************
// Minimum requis : L'employeur
$employeur = filter_input(INPUT_GET, "ListeEmployeurs");
if (!isset($employeur)) {
    print"<center>Veuillez sélectionné l'employeur!";
    print "<br>";
    print "<input type='button' onclick='javascript:window.close();' value='Fermer'></center>";
    die();
}

$connex = setConnectionSql();
// Lecture des paramètres du générateur de rapports.
// Employeur
$noEmployeur = antiInjection(substr(filter_input(INPUT_GET, "ListeEmployeurs"), 0,
                strlen(filter_input(INPUT_GET, "ListeEmployeurs")) - 1));

// Article, si nécessaire
$sqlWhereArticle = "";
$article = filter_input(INPUT_GET, "ListeArticles");
if (isset($article)) {
    $listeArticles = antiInjection(substr(filter_input(INPUT_GET, "ListeArticles"), 0,
                    strlen(filter_input(INPUT_GET, "ListeArticles"))) - 1);
    $sqlWhereArticle = " AND articles.refarticle IN (" . $listeArticles . ")";
}

// Période pour les libérations
// ****************************************************
// Sélection de date
// Toujours déterminer la date de début et de fin de la période courante.
// ****************************************************
$sqlab = "SELECT unix_timestamp(compteurautre.renouvellement) as dateAutre FROM compteurautre";
$result = mysqli_query($connex, "$sqlab") or die(mysqli_error());
$champs = mysqli_fetch_row($result);
$datedebut = date("Y-m-d", mktime(0, 0, 0, date("m", $champs[0]), date("d", $champs[0]), date("Y", $champs[0]) - 1));
$datefin = date("Y-m-d", mktime(0, 0, 0, date("m", $champs[0]), date("d", $champs[0]) - 1, date("Y", $champs[0])));
mysqli_free_result($result);

$sqla = "SELECT articles.refarticle, articles.noarticle, empl.nomemployeur, ifnull(lib.total_duree,0) as "
        . "total_duree, conv.nombre, ";
$sqlb = "(conv.nombre-ifnull(lib.total_duree,0)) as total FROM (articles left join (select articles.noarticle, "
        . "articles.refarticle,";
$sqlc = "sum(if(liberations.typeoperation=1,duree.dureeNombre,0-duree.dureeNombre)) as total_duree, ";
$sqld = sprintf("employeurs.nomemployeur from employeurs, employes, liberations, ligneliberations, articles, duree "
        . "where employeurs.refemployeur=%d and ", $noEmployeur);
$sqle = sprintf("(ligneliberations.dateliberation between \"%s\" And \"%s\") AND articles.refarticle=ligneliberations."
        . "refarticle AND ", $datedebut, $datefin);
$sqlf = "ligneliberations.refliberation = liberations.refliberation AND liberations.refemploye=employes.refemploye "
        . "and employes.refemployeur=employeurs.refemployeur ";
$sqlf1 = "and duree.refduree =ligneliberations.duree group by articles.refarticle) as lib on ";
$sqlg = "lib.refarticle=articles.refarticle) left join ";
$sqlh = "(SELECT convention.refarticle, convention.nombre, employeurs.nomemployeur ";
$sqli = "FROM compteurautre, periodes, employeurs, nombreemployes ";
$sqlj = "left JOIN convention ON (convention.condition1 <= nombreemployes.nombreemployes ";
$sqlk = "AND convention.condition2 >= nombreemployes.nombreemployes) ";
$sqll = "WHERE periodes.periode = compteurautre.renouvellement ";
$sqlm = "AND nombreemployes.refperiode = periodes.refperiode ";
$sqln = "and nombreemployes.refemployeur = employeurs.refemployeur ";
$sqlo = sprintf("AND employeurs.refemployeur =%d) as conv on conv.refarticle=articles.refarticle, (select nomemployeur "
        . "from employeurs where refemployeur= %d) as empl ", $noEmployeur, $noEmployeur);
$sqlp = "where articles.refarticle<>2 $sqlWhereArticle ";
$sqlq = "group by articles.noarticle ";
$sqlr = "order by LENGTH (articles.noarticle), articles.noarticle";
$sqlprinc = "$sqla$sqlb$sqlc$sqld$sqle$sqlf$sqlf1$sqlg$sqlh$sqli$sqlj$sqlk$sqll$sqlm$sqln$sqlo$sqlp$sqlq$sqlr";

$result2 = mysqli_query($connex, "$sqlprinc") or die("La requête (3) a échouée.");

// ****************************************************
// Vérification de la présence de détails dans la liste
// ****************************************************
if (mysqli_num_rows($result2) == 0) {
    print"<center>Aucune libération trouvée!";
    print "<br>";
    print "<input type='button' onclick='javascript:window.close();' value='Fermer'></center>";
    die();
}
$_SESSION['requêteSQL'] = $sqlprinc;
$module["compte"] = 0;

print preparerModule($module);

$i = 1;
unset($style);

$style = "border:1px solid #cfcfcf; font-family:arial, helvetica, sans-serif; font-size:9pt; "
        . "margin-left:5px; margin-right:5px;";
while ($champs = mysqli_fetch_row($result2)) {
    if ($i == 1) {
        print "<tr style='background-color:#f2f6f7;'>\n";
        print "<td style='border:1px solid #cfcfcf; font-family:arial, helvetica, sans-serif; "
                . "font-size:12pt;' colspan='5'>";
        print htmlentities($champs[2], ENT_SUBSTITUTE, "UTF-8") . "</td>";
        print "</tr>\n";
    }

    if ($champs[4] != 0) {
        $valeur1 = sprintf("%10.1f", $champs[4]);
        $valeur3 = sprintf("%10.1f", $champs[5]);
    } else {
        //Todo vérifier pour les valeurs vides des colonnes Convention et Solde
        $valeur1 = "&nbsp;";
        $valeur3 = "&nbsp;";
    }

    $valeur2 = sprintf("%10.1f", $champs[3]);
    $couleur = ($champs[5] >= 0) ? "positif" : "negatif";

    print "<tr style='background-color:#f2f6f7;'>\n";
    print "<td style='$style'>$i</td>\n";
    print "<td style='$style'>$champs[1]</td>\n";
    print "<td style='$style' align='center'>$valeur1</td>\n";
    print "<td style='$style' align='center'>$valeur2</td>";
    print "<td style='$style' align='center' "
            . "class='$couleur'>$valeur3</td>";
    print "</tr>\n";
    $i++;
}
print "</table>";
mysqli_free_result($result2);
print "<div style='width:" . $module["tailleliste"] . "; margin:auto;auto;'>\n";
print "<a href=\"rapport_soldes.php\" class=\"rapport\"><span class=\"pdf\">Afficher le rapport des soldes</span></a>";
print "</div>\n";