[insert_php]

require (“Gestion_LOHAD.php”);

$cas = $_GET[‘cas’];
$id_tournee = $_GET[‘id_tournee’];

$cas = $conn->real_escape_string($_GET[‘cas’]);
$id_tournee = $conn->real_escape_string($_GET[‘id_tournee’]);

//echo “

$id_tournee

“;

//récupérer la tb_tournée
$tour = (“SELECT tb_tournee.id_tournee, tb_tournee.tournee, tb_tournee.image_tour FROM tb_tournee WHERE id_tournee = $id_tournee”);
$res_tour = $conn->query($tour);
$re_tour = mysqli_fetch_array($res_tour);

$tournee = $re_tour[‘tournee’];

//nb de concert
$count_conc = (“SELECT COUNT(*) AS nbr_conc FROM tb_concert, tb_leg WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_count_conc = $conn->query($count_conc);
$rep_count_conc = mysqli_fetch_array($res_count_conc);

$nbr_conc = $rep_count_conc[‘nbr_conc’];

//nb de pays
$count_pays =(“SELECT COUNT( DISTINCT tb_ville.id_pays ) AS nbr_pays
FROM tb_concert, tb_leg, tb_salle, tb_ville
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_salle = tb_salle.id_salle
AND tb_salle.id_ville = tb_ville.id_ville”);
$res_count_pays = $conn->query($count_pays);
$rep_count_pays = mysqli_fetch_array($res_count_pays);

$nbr_pays = $rep_count_pays[‘nbr_pays’];

//nb de salle
$count_salle =(“SELECT COUNT( DISTINCT tb_concert.id_salle ) AS nbr_salle
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_count_salle = $conn->query($count_salle);
$rep_count_salle = mysqli_fetch_array($res_count_salle);

$nbr_salle = $rep_count_salle[‘nbr_salle’];

//nb de morceaux joués
$count_morco = (“SELECT COUNT(*) AS nbr_morco
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert”);
$res_count_morco = $conn->query($count_morco);
$rep_count_morco = mysqli_fetch_array($res_count_morco);

$nbr_morco = $rep_count_morco[‘nbr_morco’];

//nb de chansons différentes
$count_songs = (“SELECT COUNT( DISTINCT tb_unite.id_chanson ) AS nbr_songs
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert”);
$res_count_songs = $conn->query($count_songs);
$rep_count_songs = mysqli_fetch_array($res_count_songs);

$nbr_songs = $rep_count_songs[‘nbr_songs’];

//nb moyen de chanson par concert
$moy_songs = (“SELECT AVG( tb_concert.nbr_chanson ) AS moy_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_moy_songs = $conn->query($moy_songs);
$rep_moy_songs = mysqli_fetch_array($res_moy_songs);

$moy_songs = $rep_moy_songs[‘moy_songs’];
$moy_songs = number_format($moy_songs, 2, ‘,’, ‘ ‘);

$ratio = (($rep_count_songs[‘nbr_songs’] – $rep_moy_songs[‘moy_songs’])/$rep_count_conc[‘nbr_conc’]) ;
$ratio = number_format($ratio, 2, ‘,’, ‘ ‘);

//nb de chanson maxi
$max_songs = (“SELECT MAX( tb_concert.nbr_chanson ) AS max_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_max_songs = $conn->query($max_songs);
$rep_max_songs = mysqli_fetch_array($res_max_songs);

$max_songs = $rep_max_songs[‘max_songs’];

//nb de chanson mini
$min_songs = (“SELECT MIN( tb_concert.nbr_chanson ) AS min_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee”);
$res_min_songs = $conn->query($min_songs);
$rep_min_songs = mysqli_fetch_array($res_min_songs);

$min_songs = $rep_min_songs[‘min_songs’];

//les chansons jouées:
$songs = (“SELECT DISTINCT ( tb_chanson.titre ), tb_chanson.id_chanson, tb_album.image_album
FROM tb_concert, tb_leg, tb_unite, tb_chanson, tb_album
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee
AND tb_leg.numero > 0
AND tb_concert.id_concert = tb_unite.id_concert AND tb_unite.id_chanson = tb_chanson.id_chanson
AND tb_chanson.id_album = tb_album.id_album ORDER BY titre ASC”);

//nb de chansons différentes hors répétitions
$count_songs_rep = (“SELECT COUNT( DISTINCT tb_unite.id_chanson ) AS nbr_songs
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert AND tb_leg.numero>0”);
$res_count_songs_rep = $conn->query($count_songs_rep);
$rep_count_songs_rep = mysqli_fetch_array($res_count_songs_rep);

//nb de morceaux joués hors répértitions
$count_morco_rep = (“SELECT COUNT(*) AS nbr_morco
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee AND tb_concert.id_concert = tb_unite.id_concert AND tb_leg.numero>0”);
$res_count_morco_rep = $conn->query($count_morco_rep);
$rep_count_morco_rep = mysqli_fetch_array($res_count_morco_rep);

//nb de concert hors répétitions
$count_conc_rep = (“SELECT COUNT(*) AS nbr_conc FROM tb_concert, tb_leg WHERE tb_concert.id_leg = tb_leg.id_leg
AND tb_leg.id_tournee = $id_tournee AND tb_leg.numero>0”);
$res_count_conc_rep = $conn->query($count_conc_rep);
$rep_count_conc_rep = mysqli_fetch_array($res_count_conc_rep);

//les ALBUMS

$albums = (“SELECT DISTINCT ( tb_album.album ), tb_album.id_album, tb_album.image_album, tb_album.date_paru
FROM tb_concert, tb_leg, tb_unite, tb_chanson, tb_album
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_tournee = $id_tournee
AND tb_leg.numero > 0
AND tb_concert.id_concert = tb_unite.id_concert AND tb_unite.id_chanson = tb_chanson.id_chanson
AND tb_chanson.id_album = tb_album.id_album ORDER BY tb_album.date_paru ASC”) ;

echo “

$tournee

“;

echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;

echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;

//requête pour chercher les legs de chaque tournée
$reponse_leg = (“SELECT * FROM tb_leg WHERE id_tournee = $id_tournee ORDER BY numero”);
$res_reponse_leg = $conn->query($reponse_leg);

while ($donnees_leg = mysqli_fetch_array($res_reponse_leg) )
{
$idleg = $donnees_leg[‘id_leg’];
$num_leg = $donnees_leg[‘numero’];
$leg = $donnees_leg[‘leg’];

$count_conc_leg = (“SELECT COUNT(*) AS nbr_conc FROM tb_concert, tb_leg WHERE tb_concert.id_leg = $idleg AND tb_concert.id_leg = tb_leg.id_leg”);
$res_count_conc_leg = $conn->query($count_conc_leg);
$rep_count_conc_leg = mysqli_fetch_array($res_count_conc_leg);

$nbr_conc = $rep_count_conc_leg[‘nbr_conc’];

//nb de pays par leg
$count_pays_leg = (“SELECT COUNT( DISTINCT tb_ville.id_pays ) AS nbr_pays
FROM tb_concert, tb_leg, tb_salle, tb_ville
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg AND tb_concert.id_salle = tb_salle.id_salle
AND tb_salle.id_ville = tb_ville.id_ville”);
$res_count_pays_leg = $conn->query($count_pays_leg);
$rep_count_pays_leg = mysqli_fetch_array($res_count_pays_leg);

$nbr_pays = $rep_count_pays_leg[‘nbr_pays’];

//nb de salle par leg
$count_salle_leg = (“SELECT COUNT( DISTINCT tb_concert.id_salle ) AS nbr_salle
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg”);
$res_count_salle_leg = $conn->query($count_salle_leg);
$rep_count_salle_leg = mysqli_fetch_array($res_count_salle_leg);

$nbr_salle = $rep_count_salle_leg[‘nbr_salle’];

//nb de morceaux joués par leg
$count_morco_leg = (“SELECT COUNT(*) AS nbr_morco
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg AND tb_concert.id_concert = tb_unite.id_concert”);
$res_count_morco_leg = $conn->query($count_morco_leg);
$rep_count_morco_leg = mysqli_fetch_array($res_count_morco_leg);

$nbr_morco = $rep_count_morco_leg[‘nbr_morco’];

//nb de chansons différentes par leg
$count_songs_leg = (“SELECT COUNT( DISTINCT tb_unite.id_chanson ) AS nbr_songs
FROM tb_concert, tb_leg, tb_unite
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg AND tb_concert.id_concert = tb_unite.id_concert”);
$res_count_songs_leg = $conn->query($count_songs_leg);
$rep_count_songs_leg = mysqli_fetch_array($res_count_songs_leg);

$nbr_songs = $rep_count_songs_leg[‘nbr_songs’];

//nb moyen de chanson par concert
$moy_songs_leg = (“SELECT AVG( tb_concert.nbr_chanson ) AS moy_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg”);
$res_moy_songs_leg = $conn->query($moy_songs_leg);
$rep_moy_songs_leg = mysqli_fetch_array($res_moy_songs_leg);

$moy_songs = $rep_moy_songs_leg[‘moy_songs’];
$moy_songs = number_format($moy_songs, 2, ‘,’, ‘ ‘);

//nb de chanson maxi par leg
$max_songs_leg = (“SELECT MAX( tb_concert.nbr_chanson ) AS max_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg”);
$res_max_songs_leg = $conn->query($max_songs_leg);
$rep_max_songs_leg = mysqli_fetch_array($res_max_songs_leg);

$max_songs = $rep_max_songs_leg[‘max_songs’];

//nb de chanson mini
$min_songs_leg = (“SELECT MIN( tb_concert.nbr_chanson ) AS min_songs
FROM tb_concert, tb_leg
WHERE tb_concert.id_leg = tb_leg.id_leg AND tb_leg.id_leg = $idleg”);
$res_min_songs_leg = $conn->query($min_songs_leg);
$rep_min_songs_leg = mysqli_fetch_array($res_min_songs_leg);

$min_songs = $rep_min_songs_leg[‘min_songs’];

$ratio1 = ($nbr_songs – $moy_songs) / $nbr_conc;
$ratio1 = number_format($ratio1, 2, ‘,’, ‘ ‘);

echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;
echo “

“;

}

echo “

	Nb de concerts (NC)	Nb de pays	Nb de salles	Nb de morceaux joués	Nb de chansons différentes (N1)	Nb moy de chansons par concert (Nmoy)	Nb maxi de chansons par concert	Nb min de chansons par concert	Ratio (N1-Nmoy)/NC
Total	$nbr_conc	$nbr_pays	$nbr_salle	$nbr_morco	$nbr_songs	$moy_songs	$max_songs	$min_songs	$ratio
$leg	$nbr_conc	$nbr_pays	$nbr_salle	$nbr_morco	$nbr_songs	$moy_songs	$max_songs	$min_songs	$ratio1

“;

mysqli_close($conn);
[/insert_php]