Examen Programmation Web 2012-2013

Soit la base de données "TCO" contenant les tables suivantes:

Partie 1:
Donner le script en PHP qui permet de :
- créer la BDD (1 point)
- créer les tables (1 point)
- Ajouter les enregistrements ci-dessus dans les tables (1 point)

Solution:
<?php
$c=mysql_connect("localhost","root","") or die ("Erreur de connexion");
$req="drop database `tco`";
$res=mysql_query($req);
if ($res) echo "Ancienne BDD supprimée.";
$r="create database `tco`";
$res=mysql_query($r, $c) or die("erreur de requête ou BDD existante...");

$bdd=mysql_select_db("tco", $c);
if(!$bdd) die("Impossible d'accéder à la base de données :".mysql_error());

/// appeloffre
$req="drop table `appeloffre`";
$res=mysql_query($req);
if ($res) echo "Ancienne table appeloffre supprimée.";

$req="create table `appeloffre` ( `num` int(4), `objet` varchar(30), `lieu` varchar(30), `duree` int(2))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `appeloffre` ( `num` , `objet` , `lieu`, `duree` ) values
('1000','350 Logements','Draria','24'),
('2000','2 hopitaux ','Oran ','36')";
$res=mysql_query($req) or die(mysql_error());

///soumissionnaire
$req="drop table `soumissionnaire`";
$res=mysql_query($req);
if ($res) echo "Ancienne table soumissionnaire supprimée.";

$req="create table `soumissionnaire` ( `code` int(3), `rs` varchar(50), `capital` decimal(10,2))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `soumissionnaire` ( `code` , `rs` , `capital` ) values
('100','Sarl Ahmed','400000.00'),
('200','Eurl Wahbi','150000.00'),
('300','SPA Warda ','350000.00')";
$res=mysql_query($req) or die(mysql_error());

///implantation
$req="drop table `implantation`";
$res=mysql_query($req);
if ($res) echo "Ancienne table implantation supprimée.";

$req="create table `implantation` ( `cs` int(3), `codeu` int(2), `lieuu` varchar(30))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `implantation` ( `cs`, `codeu`, `lieuu` ) values
('100','55','Draria'),
('200','56','Oran '),
('300','57','Draria'),
('100','56','Oran')";
$res=mysql_query($req) or die(mysql_error());

/// soumission
$req="drop table `soumission`";
$res=mysql_query($req);
if ($res) echo "Ancienne table soumission supprimée.";

$req="create table `soumission` ( `codes` int(3), `numao` int(4), `montant` decimal(9,2), `delai` int(2), `resultat` varchar(10))";
$res=mysql_query($req) or die(mysql_error());

$req="insert into `soumission` ( `codes` , `numao` , `montant`, `delai`, `resultat` ) values
('100','1000','100000','26',''),
('200','1000','110000','28',''),
('300','2000','250000','34',''),
('100','2000','240000','35',''),
('200','2000','260000','42','')";
$res=mysql_query($req) or die(mysql_error());
///
mysql_close($c);
echo "Base de données Créée avec succés";
?>

Partie 2:
Soit le script suivant:
<?php
$c= mysql_connect ( "localhost","root","") or die ( "erreur 1");
$bdd= mysql_select_db ( "TCO") or die ( "erreur 2");
$req = "Select `Num` as `R1`, `Objet` as `R2`, `RS` as `R3`, `Capital` as `R4`
From `Soumissionnaire`, `Soumission`, `AppelOffre`
Where `Code` = `CodeS` and `Num` = `NumAO` and `Capital` >= '3' * `Montant` ";
$res= mysql_query ( $req ) or die ( "erreur 3");
while ( $i = mysql_fetch_array ( $res ) ) {
    $x= $i [ "R1" ];  $y= $i [ "R2" ];
    $z= $i [ "R3" ];  $w= $i [ "R4" ];
    echo "$x $y $z $w <br>"; }
mysql_close ( $c );
?>
a. Donner l'interprétation (1 point) et les résultats affichés dans le navigateur aprés l'exécution de ce script en justifiant les calculs (1 point).

Solution :
Pour chaque appel d'offre, la liste des soumissionnaires ayant un capital au moins 3 fois plus grand que le montant de leur offre.
Les résultats
1000 350 Logements Sarl Ahmed 400000

b. Même question avec la requête suivante : (2 points)
    $req = "Select `CS` as `R1`, `RS` as `R2`, `Objet` as `R3`, `Capital` as `R4`
                From `Soumissionnaire`, `Soumission`, `AppelOffre`, `Implantation`
               Where `Code` = `CodeS` and `Num` = `NumAO` and `CS` = `CodeS` and `Lieu` =`LieuU` ";

Solution :
Liste des soumissionnaires dont le lieu d'implantation est similaire à celui de l'appel d'offre relatif à leur offre.
Les résultats
100 Sarl Ahmed 350 Logements 400000
100 Sarl Ahmed 350 Logements 400000
200 Eurl Wahbi 2 Hopitaux 150000

c. Même question avec la requête suivante : (2 points)
    $req = "Select `CodeS` as `R1`, `RS` as `R2`, `Capital` as `R3`, sum(`Montant`) * '0.5' as `R4`
                From `Soumissionnaire`, `Soumission`, `AppelOffre`
               Where `Code` = `CodeS` and `Num` = `NumAO`
               Group by `Code`
               Having sum(`Montant`) > (`Capital` * '0.5') and count(`Num`) >= 2"; (2 points)

Solution :
Pour chaque soumissionnaire ayant au moins 2 offres et dont la somme des montants soumis est plus grande que la moitié de son capital, afficher la moitié de la somme des montants soumis.
Les résultats
100 Sarl Ahmed 400000 17000
200 Eurl Wahbi 150000 185000

d. Même question avec la requête suivante : (2 points)
    $req = "Select `Num` as `R1`, `Objet` as `R2`, `Lieu` as `R3`, `RS` as `R4`
                From `Soumissionnaire`, `Soumission`, `AppelOffre`, `Implantation`
               Where `Code` = `CodeS` and `Num` = `NumAO` and `CS` = `CodeS` and `Lieu` =`LieuU`
               Group by `Num`
               Having sum(`Delai`) <= (sum(`Duree`) * '2') and sum(`Montant`) < '0.5' * avg(`Capital`)"; (2 points)

Solution :
Pour chaque appel d'offre dont la somme des délais proposés est inférieure à 2 fois la durée et dont la somme des montants soumis est plus petite que la moitié de la moyenne des capitaux associés, afficher la liste des raisons sociales/adresses des soumissionnaires correspondants.
Les résultats
1000 350 Logements Draria Sarl Ahmed

Partie 3 :
Pour le script ci-dessus, donnez la requête $req :
a. Pour chaque appel d'offre, la moyenne des montants proposés par les soumissionnaires par ordre décroissant. ( 2 points)

Solution :
$req= "select `Num`, `Objet`, avg(`Montant`) as `moy`
from `Appeloffre`, `Soumission`
where `Num` = `NumAO`
group by `Num`
order by avg(`Montant`) Desc

b. Pour chaque soumissionnaire, la liste des appels d'offre où le soumissionnaire a proposé un montant inférieur à la moyenne des montants. ( 2 points)

Solution :
1- Rechercher d'abord la moyenne des montants avec la $req1 et placer la valeur obtenue dans $moy_mont
    $req1="select avg(`Montant`) as `moy_mont` from `Soumission`
2- $req= "select `Code`, `RS`, `NumAO`, `Montant`
from `Soumissionaire`, `Soumission`
where `Code` = `CodeS` and `Montant` < '$moy_mont' ";

Partie 4 : (5 points)
Donner un nouveau script, qui permet:
- d'afficher pour chaque appel d'offre le meilleur soumissionnaire ayant proposé le plus petit montant qui est inférieur à la moyenne des montants proposés par tous les soumissionnaires pour le même appel d'offre, en respectant le lieu d'implantation et la condition Montant < 1/3 du capital.
- de mettre à jour le champ Resultat dans la table soumission qui prend la valeur "Retenu" dans le cas où le soumissionnaire correspondant est le meilleur soumissionnaire ou la valeur "" (chaine vide) dans le cas contraire.

Solution possible :
<?php
$c= mysql_connect ( "localhost","root","") or die ( "erreur 1");
$bdd= mysql_select_db ( "TCO") or die ( "erreur 2");

//Recherche du montant minimum pour chaque appel d'offre
$req = "Select distinct `NumAO` , min (`Montant`) as `Mont_Min`
From `Soumission`
Group by `NumAO`";
$res= mysql_query ( $req ) or die ( "erreur 3");

while ( $i = mysql_fetch_array ( $res ) ) {
    $NAO= $i [ "NumAO" ];  
    $Mont_Min= $i [ "Mont_Min" ];

    //recherche des soumissionnaires ayant proposé le montant min
    $req="Select `Code` , `RS`
     From `Soumissionnaire`, `Soumission`, `AppelOffre`, `Implantation`
     Where `Code` = `CodeS` and `Num` = `NumAO` and `CS` = `CodeS` and `Lieu` =`LieuU`
               and `Montant` = '$Mont_Min' and `NumAO` = '$NAO' and `Montant` < `Capital` / '3' ";
     $res2= mysql_query ( $req ) or die ( mysql_error());

    while ( $g = mysql_fetch_array ( $res2 ) ) {
           $x= $g [ "Code" ];  
           $y= $g [ "RS" ];
           echo "$x $y<br>"; }
}
mysql_close ( $c );
?>