From 68cee0ac3fe728566e16dc0460416c77022f4b22 Mon Sep 17 00:00:00 2001
From: =?utf8?q?J=C3=A9r=C3=B4me=20Benoit?= <jerome.benoit@piment-noir.org>
Date: Sat, 20 Jan 2018 17:28:00 +0100
Subject: [PATCH] Avoid the booking of full fligths.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Signed-off-by: Jérôme Benoit <jerome.benoit@piment-noir.org>
---
 includes/formsearch.php   | 60 +++++++++++++++++++++++++--------------
 includes/reservations.php |  2 +-
 lib/utils.php             |  2 +-
 3 files changed, 41 insertions(+), 23 deletions(-)

diff --git a/includes/formsearch.php b/includes/formsearch.php
index 3c6e0ea..cd12ee4 100644
--- a/includes/formsearch.php
+++ b/includes/formsearch.php
@@ -99,20 +99,19 @@ if (empty($form_arrival_date)) {
 <?php
 if (!$input_failure) {
      global $connection;
-     $sql_pquery = "select VOLS.NumVol as NumVol, VilleD, DateD, VilleA, DateA, Classe, round(CoutVol*CoeffPrix, 2) as Prix, NumAv
-                    from VOLS, DEFCLASSES
-                    where DEFCLASSES.NumVol = VOLS.NumVol and
-                    DateD >= ? and VilleD = ? and DateA <= ? and VilleA = ?
+     $sql_pquery = "select VOLS.NumVol as NumVol, VilleD, DateD, VilleA, DateA, DEFCLASSES.Classe, round(CoutVol*CoeffPrix, 2) as Prix, CapAv
+                    from VOLS join DEFCLASSES on DEFCLASSES.NumVol = VOLS.NumVol
+                    join AVIONS on AVIONS.NumAv = VOLS.NumAv
+                    where DateD >= ? and VilleD = ? and DateA <= ? and VilleA = ?
                     order by DateD, NumVol, Prix";
      $connection->prepare_query($sql_pquery);
      $connection->prepared_query_bind_param("ssss", array($form_departure_date, $form_departure_city, $form_arrival_date, $form_arrival_city));
      $connection->run_prepared_query();
      $connection->get_pquery_result();
-     $rows = $connection->get_result_array();
+     $fligths = $connection->get_result_array();
      $connection->close_prepared_query();
-     //FIXME: Use NumAv to see if a flight is fully booked.
-     //var_dump($rows);
-     if (empty($rows)) {
+     //var_dump($fligths);
+     if (empty($fligths)) {
          echo "Aucun vol ne correspond aux crite&#768;res de recherche. <br>";
      } else {
          if ($action === "return_flight" && isset($_SESSION['return_flight_nb_place'])) {
@@ -132,28 +131,47 @@ if (!$input_failure) {
          echo "    <th>Prix d'une place</th>\n";
          echo "    <th>Re&#769;server</th>\n";
          echo "  </tr>\n";
-         foreach ($rows as $row) {
+         foreach ($fligths as $fligth) {
+             $sql_pquery = "select sum(NbPlaces) from RESERVATIONS where NumVol = ?";
+             $connection->prepare_query($sql_pquery);
+             $connection->prepared_query_bind_param("s", array($fligth['NumVol']));
+             $connection->run_prepared_query();
+             $connection->get_pquery_result();
+             $rows = $connection->get_result_array();
+             $connection->close_prepared_query();
+             $booked_places = $rows[0][0];
+             if (is_null($booked_places)) {
+                 $booked_places = 0;
+             }
+             if (isset($fligth['CapAv']) && isset($booked_places)) {
+                 $free_places = $fligth['CapAv'] - $booked_places;
+             } else {
+                 $free_places = "Inconnu";
+             }
              echo "  <tr>\n";
-             echo "    <td>" . $row['NumVol'] . "</td>\n";
-             echo "    <td>" . $row['VilleD'] . "</td>\n";
-             echo "    <td>" . $row['DateD'] . "</td>\n";
-             echo "    <td>" . $row['VilleA'] . "</td>\n";
-             echo "    <td>" . $row['DateA'] . "</td>\n";
-             echo "    <td>" . $row['Classe'] . "</td>\n";
-             echo "    <td>" . $row['Prix'] . "&euro;</td>\n";
+             echo "    <td>" . $fligth['NumVol'] . "</td>\n";
+             echo "    <td>" . $fligth['VilleD'] . "</td>\n";
+             echo "    <td>" . $fligth['DateD'] . "</td>\n";
+             echo "    <td>" . $fligth['VilleA'] . "</td>\n";
+             echo "    <td>" . $fligth['DateA'] . "</td>\n";
+             echo "    <td>" . $fligth['Classe'] . "</td>\n";
+             echo "    <td>" . $fligth['Prix'] . "&euro;</td>\n";
              echo "    <td>
+                        Places libres : " . $free_places . "
                         <form action=\"index.php\" id=\"booking\" method=\"post\">
                           <input type=\"hidden\" name=\"form\" value=\"booking\" />
-                          <input type=\"hidden\" name=\"flight_id\" value=\"" . $row['NumVol'] . "\" />
-                          <input type=\"hidden\" name=\"class_name\" value=\"" . $row['Classe'] . "\" />
-                          <input type=\"hidden\" name=\"place_price\" value=\"" . $row['Prix'] . "\" />
+                          <input type=\"hidden\" name=\"flight_id\" value=\"" . $fligth['NumVol'] . "\" />
+                          <input type=\"hidden\" name=\"class_name\" value=\"" . $fligth['Classe'] . "\" />
+                          <input type=\"hidden\" name=\"place_price\" value=\"" . $fligth['Prix'] . "\" />
                           <label> Place(s) : </label>
                           <input type=\"number\" name=\"nb_place\" min=\"1\" max=\"9\" value=\"" . $nb_places . "\" required/>\n";
-             if (!($action === "return_flight")) {
+             if ($action !== "return_flight") {
                  echo "       <label> Vol retour : </label>\n";
                  echo "       <input type=\"checkbox\" name=\"return_flight\" checked/>\n";
              }
-             echo "       <input type=\"submit\" value=\"Re&#769;server\">\n";
+             if ($free_places !== 0 || $free_places === "Inconnu") {
+                 echo "       <input type=\"submit\" value=\"Re&#769;server\">\n";
+             }
              echo "     </form>
                        </td>\n";
              echo "  </tr>\n";
diff --git a/includes/reservations.php b/includes/reservations.php
index 0416bd5..0d32077 100644
--- a/includes/reservations.php
+++ b/includes/reservations.php
@@ -17,7 +17,7 @@ $oDateNow = new Datetime($date_now);
 global $connection;
 $sql_pquery = "select RESERVATIONS.NumVol as NumVol, VilleD, DateD, VilleA, DateA, RESERVATIONS.Classe, NbPlaces, round(CoutVol*CoeffPrix*NbPlaces, 2) as Prix
                from RESERVATIONS join VOLS on RESERVATIONS.NumVol = VOLS.NumVol
-               join DEFCLASSES on DEFCLASSES.NumVol = VOLS.NumVol and RESERVATIONS.Classe = DEFCLASSES.Classe
+               join DEFCLASSES on DEFCLASSES.NumVol = RESERVATIONS.NumVol and DEFCLASSES.NumVol = VOLS.NumVol and DEFCLASSES.Classe = RESERVATIONS.Classe
        		   where NumCl = ? and DateD > ?
                order by DateD, NumVol, Prix";
 $connection->prepare_query($sql_pquery);
diff --git a/lib/utils.php b/lib/utils.php
index 5ab99b5..f886716 100644
--- a/lib/utils.php
+++ b/lib/utils.php
@@ -98,7 +98,7 @@ function get_client_id($email)
 function nb_booked($client_id, $flight_id, $class_name)
 {
     global $connection;
-    $sql_pquery = "select SUM(NbPlaces) from RESERVATIONS where NumCl = ? and NumVol = ? and Classe = ?";
+    $sql_pquery = "select sum(NbPlaces) from RESERVATIONS where NumCl = ? and NumVol = ? and Classe = ?";
     $connection->prepare_query($sql_pquery);
     $connection->prepared_query_bind_param("iss", array($client_id, $flight_id, $class_name));
     $connection->run_prepared_query();
-- 
2.34.1