Liberations/obtsolde.php

<?php

//Librairie de fonctions
ini_set('display_errors', 'On');
session_start();
require_once(__DIR__ . '/vendor/autoload.php');
require_once("lib/libconfig.inc.php");
require_once("lib/libsession.inc.php");
require_once("lib/libmysql.inc.php");
require_once("lib/libencode.inc.php");

setConnectionSql();
$valueFromGet = filter_input(INPUT_GET, "prm1", FILTER_VALIDATE_INT);
if ($valueFromGet !== null) {
    // Protection SQL et cie.
    $search2 = antiInjection(filter_input(INPUT_GET, "prm1"));
    $search = intval($search2);
    //Pas une valeur numérique, on arrête.
    if (!is_numeric($search)) {
        print "t1";
    }
    if ($search != "") {
        //Employeur
        $noEmploye = $search;
        $sqlWhereArticle = "";

        // **********************************************************************
        // 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.
        // **********************************************************************

        unset($sqlparam, $resultat, $datelue);
        $sqlparam["table"][] = "compteurautre";
        $sqlparam["champs"][] = "unix_timestamp(compteurautre.renouvellement) as dateAutre";
        $resultat = executerRequeteSql($sqlparam);

        $datelue = $resultat[0]["dateAutre"];
        $datedebut = date("Y-m-d", mktime(0, 0, 0, date("m", $datelue), date("d", $datelue) + 1, date("Y", $datelue) - 1));
        $datefin = date("Y-m-d", mktime(0, 0, 0, date("m", $datelue), date("d", $datelue) - 1, date("Y", $datelue)));

        unset($sqlparam, $resultat);
        $sqlparam["table"][] = "employes";
        $sqlparam["champs"][] = "employes.refemployeur";
        $sqlparam["where"][] = sprintf("refemploye=%d", $noEmploye);

        $resultat = executerRequeteSql($sqlparam);

        // Employé inexistant, on arrête.
        if (!($resultat && is_array($resultat) && count($resultat) > 0)) {
            print "t2";
        } else {
            $noEmployeur = $resultat[0]["refemployeur"];

            unset($sqla, $sqlb, $sqlc, $sqld, $sqle, $sqlf, $sqlg, $sqlh, $sqli, $sqlj, $sqlk, $sqll, $sqlm, $sqln, $sqlo, $sqlp, $sqlq, $sqlr, $sqls);
            unset($sqlSecondaireA);

            //Construction de la requête
            $sqla = "SELECT articles.refarticle, articles.noarticle, employeurs.nomemployeur, employeurs.refemployeur, ";
            $sqlb = "IF(ISNULL(convention2.Nombre),0,convention2.nombre) AS nombre, IF(ISNULL(convention2.refarticle),articles.refarticle,convention2.refarticle) AS refarticle2,";
            $sqlc = "SUM(IF(typeoperation=1,IF(duree<3,0.5,1),IF(duree<3,-0.5,-1))) AS utilisation ";
            $sqld = "FROM (articles LEFT JOIN ((SELECT convention.Nombre, convention.refarticle FROM convention ";
            $sqle = "WHERE (select nombreemployes.nombreemployes AS nombreemployes ";
            $sqlf = "FROM (((employeurs JOIN nombreemployes ON ((employeurs.refemployeur = nombreemployes.refemployeur))) ";
            $sqlg = "JOIN periodes ON ((nombreemployes.refperiode = periodes.refperiode))) JOIN compteurautre ";
            $sqlh = sprintf("ON ((periodes.periode = compteurautre.renouvellement))) WHERE employeurs.refemployeur=%d ", $noEmployeur);
            $sqli = "ORDER BY employeurs.refemployeur,nombreemployes.refperiode) BETWEEN convention.condition1 AND convention.condition2)";
            $sqlj = ") AS convention2 ON articles.refarticle = convention2.refarticle) ";
            $sqlk = "INNER JOIN (((employeurs INNER JOIN employes ON employeurs.refemployeur = employes.refemployeur) ";
            $sqll = "INNER JOIN liberations ON employes.refemploye = liberations.refemploye) ";
            $sqlm = "INNER JOIN (SELECT ligneliberations.refliberation, ligneliberations.refarticle, ligneliberations.dateliberation, ligneliberations.duree ";
            $sqln = "FROM ligneliberations WHERE DATE_FORMAT(ligneliberations.dateliberation,\"%Y-%m-%d\") Between ";
            $sqlo = sprintf("\"%s\" And \"%s\") AS liberationscourantes ON liberations.refliberation = liberationscourantes.refliberation) ", $datedebut, $datefin);
            $sqlp = "ON articles.refarticle = liberationscourantes.refarticle GROUP BY articles.refarticle, articles.noarticle, ";
            $sqlq = "employeurs.nomemployeur, employeurs.refemployeur, convention2.nombre ";
            $sqlr = sprintf("HAVING employeurs.refemployeur=%d AND articles.refarticle<>2 ", $noEmployeur);
            $sqls = "ORDER BY articles.noarticle, employeurs.nomemployeur";

            $sqlSecondaireA = "$sqla$sqlb$sqlc$sqld$sqle$sqlf$sqlg$sqlh$sqli$sqlj$sqlk$sqll$sqlm$sqln$sqlo$sqlp$sqlq$sqlr$sqls";

            if (!$connection) {
                die("t3");
            }

            unset($sqlPrincipaleA, $sqlPrincipaleB, $sqlPrincipaleC, $result);
            $sqlPrincipaleA = "SELECT articles.noarticle, src1.nombre as nombre,src1.utilisation as utilisation FROM articles ";
            $sqlPrincipaleB = "LEFT JOIN ($sqlSecondaireA) AS src1 ON articles.refarticle=src1.refarticle ";
            $sqlPrincipaleC = "ORDER BY length(articles.noarticle), articles.noarticle";

            $result = mysqli_query($connection, "$sqlPrincipaleA$sqlPrincipaleB$sqlPrincipaleC") or die("t4");

            unset($style, $sortieHTML);

            //Encodage des caractères
            header('Content-Type: text/html; charset=ISO-8859-1');
            $style = 'border:1px solid #cfcfcf;';
            unset($sortieHTML);
            $sortieHTML = "<span id='close'><a onclick='fermersolde();' style='cursor:pointer; text-decoration: none;'><strong>Fermer X</strong></a></span><br>";
            $sortieHTML .= "<table style='border-collapse:collapse;'>";
            $sortieHTML .= "<tr style=\"font-weight: 700;\"><th style='width:100px; text-align:center;'>Article</th><th style='width:100px; text-align:center;'>Restant</th></tr>";
            unset($champs);
            while ($champs = mysqli_fetch_array($result)) {
                unset($couleur, $valeur0, $valeur1);
                $couleur = ($champs[1] - $champs[2] < 0) ? "color:#ff0000; font-weight:700;" : "";
                $valeur0 = htmlentities($champs[0], ENT_QUOTES, "UTF-8");
                $valeur1 = sprintf("%0.2f", $champs[1] - $champs[2]);
                $sortieHTML .= "<tr style='background-color:#f2f6f7; $couleur'>\n";
                $sortieHTML .= "<td style='$style'>$valeur0</td>\n";
                $sortieHTML .= "<td style='$style' align=\"right\">$valeur1</td></tr>\n";
            }
            $sortieHTML .= "</table>";
            print $sortieHTML;
        }
    }
}