SAEWEB2.2/ci/application/models/Model_music.php

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');                                             

class Model_music extends CI_Model {
	public function __construct(){
		$this->load->database();
	}

	public function getAlbums($selectedGenre) {
	    if ($selectedGenre == '0') {
	        $query = $this->db->query(
	            "SELECT album.name, album.id, year, artist.name as artistName, genre.name as genreName, jpeg 
	            FROM album 
	            JOIN artist ON album.artistid = artist.id
	            JOIN genre ON genre.id = album.genreid
	            JOIN cover ON cover.id = album.coverid
	            ORDER BY year"
	        );
	    } else {
	        $query = $this->db->query(
	            "SELECT album.name, album.id, year, artist.name as artistName, genre.name as genreName, jpeg 
	            FROM album 
	            JOIN artist ON album.artistid = artist.id
	            JOIN genre ON genre.id = album.genreid
	            JOIN cover ON cover.id = album.coverid
	            WHERE album.genreId = ?
	            ORDER BY year",
	            array($selectedGenre)
	        );
	    }

	    return $query->result();
	}


	public function getArtists(){
		$query = $this->db->query(
			"SELECT artist.id, artist.name
			FROM artist
			Group by artist.id
			"
		);
	return $query->result();
	}

	public function getPlaylist($mail){
		$query = $this->db->query(
			"SELECT playlist.id, playlist.name
			FROM playlist NATURAL JOIN Login
			where playlist.mail = ?
			Group by playlist.id
			",
			array($mail)
		);
	return $query->result();
	}

	public function deletePlaylist($playlist_id, $mail){
		$this->db->query(
		        "DELETE FROM SongPlaylist WHERE id = ?" , array($playlist_id)
		    );

		    // Ensuite, supprimer l'enregistrement dans playlist
		$this->db->query(
		        "DELETE FROM playlist WHERE id = ? AND playlist.mail = ?", array($playlist_id, $mail)
		    );
	}

	public function delete_Song($playlist_id,$Song_name){
		$Song_name = urldecode($Song_name);
		$this->db->query(
			"DELETE FROM SongPlaylist Where id = ? AND name=?",
			array($playlist_id, $Song_name)
		);
	}

	public function createPlaylist($name_playlist, $mail){
		$name_playlist_escaped = $this->db->escape($name_playlist);
		$query = $this->db->query(
			"INSERT INTO playlist (name, mail) VALUES (?, ?);
			",
			array($name_playlist_escaped, $mail)
		);
	}

	public function getSongOfPlaylist($playlist_id){
		$query = $this->db->query(
			"SELECT SongPlaylist.name
			FROM SongPlaylist
			WHERE $playlist_id = SongPlaylist.id;

			"
		);
	return $query->result();
	}

	public function getSongOfAlbum($album_id){
    	$query = $this->db->query(
        "SELECT song.id, song.name, track.duration
        FROM song
        INNER JOIN track ON song.id = track.songId
	

        WHERE track.albumId = ?",
        array($album_id)
    	);
    return $query->result();
	}

	public function getGenre(){
		$query = $this->db->query(
			"SELECT genre.id,genre.name
			FROM genre
			"
		);
	return $query->result();
	}

	public function getAlbumsOfArtist($artist_id){
	    $query = $this->db->query(
	    "SELECT album.name, album.id, album.year, genre.name as genreName, cover.jpeg 
	    FROM album
	    JOIN artist ON album.artistId = artist.id
	    JOIN genre ON album.genreId = genre.id
	    JOIN cover ON album.coverId = cover.id
	    WHERE artist.id = ?",
	    array($artist_id)
	    );
	return $query->result();
	}

	public function getLogin($login,$password){
		$query = $this->db->query(
			"SELECT *
			 FROM Login
			  WHERE mail = '$login' AND MotDePasse = '$password'"
		);
		return $query->result();
	}
	public function SignUp($pseudo,$login,$password){
		$query = $this->db->query(
			"INSERT INTO `Login`(`pseudo`, `mail`, `MotDePasse`) 
			VALUES ('$pseudo','$login','$password')"
			);
	}
	public function searchAlbums($search,$selectedGenre){
		if($search == '' AND $selectedGenre == '0'){
			$query = $this->db->query(
			"SELECT album.name,album.id,year,artist.name as artistName, genre.name as genreName,jpeg 
			FROM album 
			JOIN artist ON album.artistid = artist.id
			JOIN genre ON genre.id = album.genreid
			JOIN cover ON cover.id = album.coverid
			ORDER BY year
			",
			);
		}elseif($search == ''){
			$query = $this->db->query(
			"SELECT album.name,album.id,year,artist.name as artistName, genre.name as genreName,jpeg 
			FROM album 
			JOIN artist ON album.artistid = artist.id
			JOIN genre ON genre.id = album.genreid
			JOIN cover ON cover.id = album.coverid
			WHERE album.genreId = $selectedGenre
			ORDER BY year
			",
			);
		}elseif($selectedGenre == '0' AND $search !=''){
			$query = $this->db->query(
			"SELECT album.name,album.id,year,artist.name as artistName, genre.name as genreName,jpeg 
			FROM album 
			JOIN artist ON album.artistid = artist.id
			JOIN genre ON genre.id = album.genreid
			JOIN cover ON cover.id = album.coverid
			WHERE album.name LIKE '%$search%'
			ORDER BY year
			",
			);
		}
		else{
		$query = $this->db->query(
			"SELECT album.name,album.id,year,artist.name as artistName, genre.name as genreName,jpeg 
			FROM album 
			JOIN artist ON album.artistid = artist.id
			JOIN genre ON genre.id = album.genreid
			JOIN cover ON cover.id = album.coverid
			WHERE album.name LIKE '%$search%' AND album.genreId = $selectedGenre
			ORDER BY year
			",
		);
	}
	return $query->result();
	}

	public function addSongToPlaylist($songName, $playlistId){
        $query = $this->db->query(
            "INSERT INTO SongPlaylist (id, name) VALUES (?, ?)",
            array($playlistId, $songName)
        );
    }

    public function addAllSongsOfArtistInPlaylist($artistId, $playlistId){
	    // Récupérer toutes les chansons de l'artiste spécifié
	    $query = $this->db->query(
			"SELECT song.name 
			FROM song, track, album
			WHERE song.id = track.songId
			AND track.albumId = album.id
			AND album.artistId = ?",
	        array($artistId)
	    );
	    
	    $songs = $query->result();

	    // Ajouter chaque chanson dans la playlist spécifiée
	    foreach ($songs as $song) {
	        $this->db->query(
	            "INSERT INTO SongPlaylist (id, name) VALUES (?, ?)",
	            array($playlistId, $song->name)
	        );
	    }
	}

	public function duplicatePlaylist($playlist_id, $mail){
	    // Récupérer les informations de la playlist à dupliquer
	    $playlist = $this->db->get_where('playlist', array('id' => $playlist_id))->row();
	    
	    // Créer une nouvelle entrée pour la playlist avec les mêmes informations
	    $data = array(
	        'name' => $playlist->name . ' (Copy)',
	        'mail' => $mail
	    );
	     
	    $this->db->insert('playlist', $data);
	    
	    // Récupérer l'ID de la nouvelle playlist
	    $new_playlist_id = $this->db->insert_id();
	    
	    // Dupliquer les chansons de la playlist originale dans la nouvelle playlist
	    $this->db->query(
	        "INSERT INTO SongPlaylist (id, name)
	        SELECT ?, name
	        FROM SongPlaylist
	        WHERE id = ?",
	        array($new_playlist_id, $playlist_id));
	}

	public function createPlaylistRandom($name_playlist, $num_songs, $mail) {
        $data = array(
            'name' => $name_playlist,
            'mail' => $mail
        );
        $this->db->insert('playlist', $data);
        $new_playlist_id = $this->db->insert_id();

        // Sélectionner le nombre de chansons aléatoires spécifié
        $query = $this->db->query("SELECT id, name FROM song ORDER BY RAND() LIMIT ?", array($num_songs));
        $songs = $query->result();

        foreach ($songs as $song) {
            $this->db->query("INSERT INTO SongPlaylist (id, name) VALUES (?, ?)", array($new_playlist_id, $song->name));
        }
	}
}