<?php
//Fichier appelé pour afficher l'application

$p = strtolower(gpc("p"));
$titre = ucfirst(trim(gpc("titre", "post")));
$auteur = ucwords(trim(gpc("auteur", "post")));

function build_list($req)
{
  $chansons = array();
  if (!empty($req))
    foreach($req as $ligne)
    {
      if (isset($chansons[$ligne["id"]]))
        $chansons[$ligne["id"]][] = $ligne;
      else
        $chansons[$ligne["id"]] = array($ligne);
    }
  return $chansons;
}

function format_list($chansons, $xml, $xml_root)
{
  if (!empty($chansons))
  {
    foreach($chansons as $chanson)
    {
      $c = $chanson[0];

      if (empty($c["titre"]))
        continue;

      $chan = $xml->createElement("chanson");
      $chan->setAttribute("id", $c["id"]);
      $chan->setAttribute("titre", $c["titre"]);
      $chan->setAttribute("artiste", $c["artiste"]);

      if (!empty($c["id_album"]))
      {
        foreach ($chanson as $cd)
        {
          $albm = $xml->createElement("inalbum");
          $albm->setAttribute("id", $cd["id_album"]);
          $albm->setAttribute("type", $cd["type"]);
          $albm->setAttribute("titre", $cd["nom"]);
          $chan->appendChild($albm);
        }
      }
      $xml_root->appendChild($chan);
    }
  }
}

var_dump($_GET, $_POST);

if ($p == "liste")
{
	$bdd = new BDD();
	$req = $bdd->query("SELECT T.*, A.id AS id_album, A.type, A.titre AS nom FROM gsm_titres T RIGHT OUTER JOIN gsm_in I ON T.id = I.id_titre RIGHT OUTER JOIN gsm_albums A ON A.id = I.id_album;");
	$req["nombre"] = $bdd->num_rows;
	$reqCDs = $bdd->query("SELECT * FROM gsm_albums;");
	$bdd->deconnexion();

        format_list(build_list($req), $xml, $xml_root);

	if (!empty($reqCDs))
        {
          foreach($reqCDs as $cd)
          {
            if (empty($cd["id"]))
              continue;

            $chan = $xml->createElement("album");
            $chan->setAttribute("type", $cd["type"]);
            $chan->setAttribute("id", $cd["id"]);
            $chan->setAttribute("titre", $cd["titre"]);
            $chan->setAttribute("couleur", $cd["color"]);
            $xml_root->appendChild($chan);
          }
        }
}
elseif ($p == "add" && ($type = intval(gpc("type", "post"))) && ($title = gpc("title", "post")))
{
	$color = hexdec(gpc("color", "post"));

	$bdd = new BDD();
	$bdd->escape($title);
	$bdd->query("INSERT INTO `gsm_albums` (type, titre, color) VALUES ($type, '$title', $color);");
	$id = $bdd->insert_id();
	if ($id == 0)
	{
	  $res = $bdd->unique_query("SELECT id FROM `gsm_albums` WHERE type = $type AND titre = '$title';");
	  $id = $res["id"];
	}
	$req = $bdd->unique_query("SELECT COUNT(*) AS nombre FROM gsm_in");
	$bdd->deconnexion();

	if ($id != 0)
	  $xml_root->appendChild($xml->createElement("id", $id));
}
elseif ($p == "del" && $id = intval(gpc("id", "post")))
{
	$alb = intval(gpc("alb", "post"));

	$bdd = new BDD();
	if (empty($alb))
	  $bdd->query("DELETE FROM `gsm_in` WHERE id_titre = $id;");
	else
	  $bdd->query("DELETE FROM `gsm_in` WHERE id_titre = $id AND id_album = $alb;");

	$res = $bdd->unique_query("SELECT COUNT(*) AS c FROM `gsm_in` WHERE id_titre = $id;");
	if ($res["c"] <= 0)
	  $bdd->query("DELETE FROM `gsm_titres` WHERE id = $id;");

	$req = $bdd->unique_query("SELECT COUNT(*) AS nombre FROM gsm_in");
	$bdd->deconnexion();
}
elseif ($p == "color" && ($type = intval(gpc("type", "get"))) && ($idAlbum = intval(gpc("id", "get"))))
{
	$bdd = new BDD();
	$res = $bdd->unique_query("SELECT color FROM `gsm_albums` WHERE id = $idAlbum;");
	$req = $bdd->unique_query("SELECT COUNT(*) AS nombre FROM gsm_in;");
	$bdd->deconnexion();

	$xml_root->appendChild($xml->createElement("color", sprintf("#%06X", $res["color"])));

	$color2R = ($res["color"] >> 16) & 255;
	if ($color2R >= 25)
		$color2R -= 25;
	$color2G = ($res["color"] >> 8) & 255;
	if ($color2G >= 25)
		$color2G -= 25;
	$color2B = ($res["color"]) & 255;
	if ($color2B >= 25)
		$color2B -= 25;
	$xml_root->appendChild($xml->createElement("color", sprintf("#%02X%02X%02X", $color2R, $color2G, $color2B)));
}
elseif ($p == "rech")
{
        $where = "TRUE";
        $type = array();

	$bdd = new BDD();
        foreach($_POST as $key => $val)
        {
          $v = gpc($key, "post");
          $bdd->escape($v);

          if ($key == "titre")
            $where .= " AND T.titre LIKE = '%".$v."%'";
          else if ($key == "artiste")
            $where .= " AND T.artiste LIKE = '%".$v."%'";
          else if ($key == "album")
            $where .= " AND A.titre LIKE = '%".$v."%'";
          else if (preg_match($key, "#^type([0-9]+)$#", $out) && gpc($key, "post") == "1")
            $type[] = intval($out[1]);
        }

        if (count($type) > 0)
        {
          $where .= " AND (FALSE";
          foreach($type as $t)
            $where .= " OR A.type = ".$t;
          $where .= ")";
        }

	$rech = $bdd->query("SELECT T.*, A.id AS id_album, A.type, A.titre AS nom FROM gsm_titres T RIGHT OUTER JOIN gsm_in I ON T.id = I.id_titre RIGHT OUTER JOIN gsm_albums A ON A.id = I.id_album WHERE ".$where);
        $req = $bdd->unique_query("SELECT COUNT(*) AS nombre FROM gsm_in");
	$bdd->deconnexion();

        format_list(build_list($rech), $xml, $xml_root);

	$xml_cds = $xml->createElement("album");
	if (!empty($rech))
	{
		foreach($rech as $ligne)
		{
			$chan = $xml->createElement("chanson");
			$chan->setAttribute("id", $ligne["id"]);
			$chan->setAttribute("titre", $ligne["titre"]);
			$chan->setAttribute("chanteur", $ligne["chanteur"]);
			$chan->setAttribute("CD_decenie", $ligne["CDDeca"]);
			$chan->setAttribute("CD_annee", $ligne["CDAnnee"]);
			$chan->setAttribute("CD_interprete", $ligne["CDChanteur"]);
			$xml_cds->appendChild($chan);
		}
	}
	$xml_root->appendChild($xml_cds);
}
elseif ($p == "stats")
{
	$cds = array();

	$bdd = new BDD();
	$cds["annees"] = $bdd->query("SELECT A.id, A.titre AS nom, COUNT(A.id) AS nombre FROM gsm_albums A RIGHT OUTER JOIN gsm_in I ON A.id = I.id_album WHERE A.type = 1 GROUP BY A.id ORDER BY nom DESC;");
	$cds["interpretes"] = $bdd->query("SELECT A.id, A.titre AS nom, COUNT(A.id) AS nombre FROM gsm_albums A RIGHT OUTER JOIN gsm_in I ON A.id = I.id_album WHERE A.type = 3 GROUP BY A.id ORDER BY nom ASC;");
	$cds["decenies"] = $bdd->query("SELECT A.id, A.titre AS nom, COUNT(A.id) AS nombre FROM gsm_albums A RIGHT OUTER JOIN gsm_in I ON A.id = I.id_album WHERE A.type = 2 GROUP BY A.id ORDER BY nom ASC;");
	$req = $bdd->unique_query("SELECT COUNT(id) AS nombre FROM gsm_in");
	$bdd->deconnexion();

	if (!empty($cds["annees"]))
	{
		foreach($cds["annees"] as $ligne)
		{
			$cd = $xml->createElement("annee");
			$cd->setAttribute("id", $ligne["id"]);
			$cd->setAttribute("nbTitles", $ligne["nombre"]);
			$xml_root->appendChild($cd);
		}
	}

	if (!empty($cds["interpretes"]))
	{
		foreach($cds["interpretes"] as $ligne)
		{
			$cd = $xml->createElement("interprete");
			$cd->setAttribute("id", $ligne["id"]);
			$cd->setAttribute("nbTitles", $ligne["nombre"]);
			$xml_root->appendChild($cd);
		}
	}

	if (!empty($cds["decenies"]))
	{
		foreach($cds["decenies"] as $ligne)
		{
			$cd = $xml->createElement("decenie");
			$cd->setAttribute("id", $ligne["id"]);
			$cd->setAttribute("nbTitles", $ligne["nombre"]);
			$xml_root->appendChild($cd);
		}
	}
}
elseif ($p == "cds")
{
	$bdd = new BDD();
	$cds["decenies"] = $bdd->query("SELECT A.*, COUNT(T.id) AS nombre FROM gsm_cddece A LEFT OUTER JOIN gsm T ON A.id = T.CDDeca GROUP BY A.nom;");
	$cds["interpretes"] = $bdd->query("SELECT A.*, COUNT(T.id) AS nombre FROM gsm_cdchant A LEFT OUTER JOIN gsm T ON A.id = T.CDChanteur GROUP BY A.nom;");
	$cds["annees"] = $bdd->query("SELECT A.*, COUNT(T.id) AS nombre FROM gsm_cdannee A LEFT OUTER JOIN gsm T ON A.id = T.CDAnnee GROUP BY A.nom;");
	$req = $bdd->unique_query("SELECT COUNT(id) AS nombre FROM gsm_in");
	$bdd->deconnexion();

	$xml_cds = $xml->createElement("decenies");
	if (!empty($cds["decenies"]))
	{
		foreach($cds["decenies"] as $ligne)
		{
			$cd = $xml->createElement("cd");
			$cd->setAttribute("id", $ligne["id"]);
			$cd->setAttribute("nom", $ligne["nom"]);
			$cd->setAttribute("nombre_titres", $ligne["nombre"]);
			$xml_cds->appendChild($cd);
		}
	}
	$xml_root->appendChild($xml_cds);

	$xml_cds = $xml->createElement("interpretes");
	if (!empty($cds["interpretes"]))
	{
		foreach($cds["interpretes"] as $ligne)
		{
			$cd = $xml->createElement("cd");
			$cd->setAttribute("id", $ligne["id"]);
			$cd->setAttribute("nom", $ligne["nom"]);
			$cd->setAttribute("nombre_titres", $ligne["nombre"]);
			$xml_cds->appendChild($cd);
		}
	}
	$xml_root->appendChild($xml_cds);

	$xml_cds = $xml->createElement("annees");
	if (!empty($cds["annees"]))
	{
		foreach($cds["annees"] as $ligne)
		{
			$cd = $xml->createElement("cd");
			$cd->setAttribute("id", $ligne["id"]);
			$cd->setAttribute("nom", $ligne["nom"]);
			$cd->setAttribute("nombre_titres", $ligne["nombre"]);
			$xml_cds->appendChild($cd);
		}
	}
	$xml_root->appendChild($xml_cds);
}
elseif (!empty($titre) && !empty($auteur))
{
	$alb = intval(gpc("alb", "post"));
	$type = intval(gpc("type", "post"));
	$id = intval(gpc("id"));

	$bdd = new BDD();
	$bdd->escape($titre);
	$bdd->escape($auteur);
	if ($id)
	  $bdd->query("UPDATE gsm_titres SET titre = '$titre', artiste = '$auteur' WHERE id = $id;");
	else
	{
	  $bdd->query("INSERT INTO gsm_titres (titre, artiste) VALUES ('$titre', '$auteur');");
	  $res = $bdd->insert_id();
	  if (empty($res))
	  {
	    $res = $bdd->unique_query("SELECT id FROM gsm_titres WHERE titre = '$titre' AND artiste = '$auteur';");
	    $res = $res["id"];
	  }

	  if (!empty($alb))
	    $bdd->query("INSERT INTO gsm_in (id_titre, id_album) VALUES ($res, $alb);");
	}
	$req = $bdd->unique_query("SELECT COUNT(id) AS nombre FROM gsm_titres");
	$bdd->deconnexion();

	$xml_root->appendChild($xml->createElement("id", $res));
}
else
{
	$bdd = new BDD();
	$req = $bdd->unique_query("SELECT COUNT(id) AS nombre FROM gsm_titres");
	$bdd->deconnexion();
}

$xml_root->setAttribute("nombre", $req["nombre"]);