Location: PHPKode > projects > Top Music module > Top Music 3.0.7/html/modules/topMusic/lib/com/sergids/topmusic/model/SongDAO.php
<?php
require_once("SongO.php");
require_once("CustomFieldO.php");
class SongDAO{
	var $db;
	var $prefix;
	function SongDAO($db,$prefix="nuke"){
		$this->db=$db;
		$this->prefix=$prefix;
	} 
	function getSong($idsong,$language){
	  if(is_numeric($idsong)){
		$songO=new SongO();
		$sSql="SELECT 
			  ".$this->prefix."_topmusic_song.idsong,
			  ".$this->prefix."_topmusic_song.idgenre,
			  ".$this->prefix."_topmusic_genre_lang.name genrename,
			  ".$this->prefix."_topmusic_song.idalbum,
			  ".$this->prefix."_topmusic_album.title albumtitle,
			  ".$this->prefix."_topmusic_song.title,
			  ".$this->prefix."_topmusic_song.author,
			  ".$this->prefix."_topmusic_song.lyrics,
			  ".$this->prefix."_topmusic_song.number,
			  ".$this->prefix."_topmusic_song.track,
			  ".$this->prefix."_topmusic_song.nvotes,
			  ".$this->prefix."_topmusic_song.avgvotes,
			  ".$this->prefix."_topmusic_album.idartist,
			  ".$this->prefix."_topmusic_album.frontcover_file albumcover_file,
			  ".$this->prefix."_topmusic_album.frontcover_url albumcover_url,
			  ".$this->prefix."_topmusic_artist.name artistname
			FROM
			  ".$this->prefix."_topmusic_song
			   LEFT OUTER JOIN ".$this->prefix."_topmusic_album ON (".$this->prefix."_topmusic_song.idalbum = ".$this->prefix."_topmusic_album.idalbum)
			  LEFT OUTER JOIN ".$this->prefix."_topmusic_artist ON (".$this->prefix."_topmusic_album.idartist = ".$this->prefix."_topmusic_artist.idartist)
				LEFT OUTER JOIN ".$this->prefix."_topmusic_genre ON (".$this->prefix."_topmusic_song.idgenre = ".$this->prefix."_topmusic_genre.idgenre)
			  LEFT OUTER JOIN ".$this->prefix."_topmusic_genre_lang ON (".$this->prefix."_topmusic_genre.idgenre = ".$this->prefix."_topmusic_genre_lang.idgenre)
			WHERE
			  (".$this->prefix."_topmusic_genre_lang.language = '".$language."' OR ".$this->prefix."_topmusic_genre_lang.language IS NULL) AND
			  ".$this->prefix."_topmusic_song.idsong=".$idsong;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$songO->setId($rs['idsong']);
			$songO->setTitle($rs['title']);
			$songO->setGenreid($rs['idgenre']);
			$songO->setGenrename($rs['genrename']);
			$songO->setIdalbum($rs['idalbum']);
			$songO->setAlbumname($rs['albumtitle']);
			$songO->setLyrics($rs['lyrics']);
			$songO->setAuthor($rs['author']);
			$songO->setIdartist($rs['idartist']);
			$songO->setNumber($rs['number']);
			$songO->setTrack($rs['track']);
			$songO->setAlbumcover_file($rs['albumcover_file']);
			$songO->setAlbumcover_url($rs['albumcover_url']);
			$votes=$this->getSongRate($idsong);
			$songO->setAvgvotes($votes);
			$songO->setArtistname($rs['artistname']);
		}
		$this->db->sql_freeresult($stmt);
	  }
		return $songO;
	}
	function getSongList($idalbum,$language){
		if(is_numeric($idalbum)){
		$sSql="	SELECT 
				  ".$this->prefix."_topmusic_song.idsong,
				  ".$this->prefix."_topmusic_song.idgenre,
				  ".$this->prefix."_topmusic_genre_lang.name genrename,
				  ".$this->prefix."_topmusic_song.idalbum,
				  ".$this->prefix."_topmusic_album.title albumtitle,
				  ".$this->prefix."_topmusic_song.title,
				  ".$this->prefix."_topmusic_song.author,
				  ".$this->prefix."_topmusic_song.lyrics,
				  ".$this->prefix."_topmusic_song.number,
				  ".$this->prefix."_topmusic_song.track,
				  ".$this->prefix."_topmusic_album.idartist,
				  ".$this->prefix."_topmusic_artist.name artistname
				FROM
				  ".$this->prefix."_topmusic_song
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_genre ON (".$this->prefix."_topmusic_song.idgenre = ".$this->prefix."_topmusic_genre.idgenre)
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_genre_lang ON (".$this->prefix."_topmusic_genre.idgenre = ".$this->prefix."_topmusic_genre_lang.idgenre)
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_album ON (".$this->prefix."_topmusic_song.idalbum = ".$this->prefix."_topmusic_album.idalbum)
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_artist ON (".$this->prefix."_topmusic_album.idartist = ".$this->prefix."_topmusic_artist.idartist)
				WHERE
				  (".$this->prefix."_topmusic_genre_lang.language = '".$language."' OR ".$this->prefix."_topmusic_genre_lang.language IS NULL) AND
				  ".$this->prefix."_topmusic_album.idalbum=".$idalbum." 
				ORDER BY ".$this->prefix."_topmusic_song.number,".$this->prefix."_topmusic_song.title";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$songO=new SongO();
			$songO->setId($rs['idsong']);
			$songO->setTitle($rs['title']);
			$songO->setGenreid($rs['idgenre']);
			$songO->setGenrename($rs['genrename']);
			$songO->setIdalbum($rs['idalbum']);
			$songO->setAlbumname($rs['albumtitle']);
			$songO->setLyrics($rs['lyrics']);
			$songO->setAuthor($rs['author']);
			$songO->setIdartist($rs['idartist']);
			$songO->setNumber($rs['number']);
			$songO->setTrack($rs['track']);
			$songO->setArtistname($rs['artistname']);
			$songList[$i]=$songO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		}
		return $songList;
	}
	function searchSong($song,$freetext,$language,$page=1,$numppage=10){
		$sSql="SELECT 
		  ".$this->prefix."_topmusic_song.idsong,
		  ".$this->prefix."_topmusic_song.idgenre,
		  ".$this->prefix."_topmusic_song.idalbum,
		  ".$this->prefix."_topmusic_album.title albumtitle,
		  ".$this->prefix."_topmusic_album.idartist,
		  ".$this->prefix."_topmusic_artist.name artistname,
		  ".$this->prefix."_topmusic_song.title,
	  	  ".$this->prefix."_topmusic_song.number,
		  ".$this->prefix."_topmusic_song.track
		FROM
		  ".$this->prefix."_topmusic_song
		  LEFT OUTER JOIN ".$this->prefix."_topmusic_album ON (".$this->prefix."_topmusic_song.idalbum = ".$this->prefix."_topmusic_album.idalbum)
		  INNER JOIN ".$this->prefix."_topmusic_artist ON (".$this->prefix."_topmusic_album.idartist = ".$this->prefix."_topmusic_artist.idartist)";
		$junc=" AND ";
		if($song->getGenreid()!="")
			$sWhere.=" ".$this->prefix."_topmusic_song.idgenre=".$song->getGenreid();
		if($song->getTitle()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_song.title like '%".$song->getTitle()."%'";
		}
		if($song->getAlbumname()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_album.title like '%".$song->getAlbumname()."%'";
		}
		if($song->getArtistname()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_artist.name like '%".$song->getArtistname()."%'";
		}
		if($freetext!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_song.lyrics like '%$freetext%'";
		}
		if($sWhere!="")
			$sWhere=" WHERE ".$sWhere;
		$sOrder=" ORDER BY ".$this->prefix."_topmusic_song.title";
		$sSql.=$sWhere.$sOrder;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		$numtotal=$this->db->sql_numrows();
		if($numtotal>0 && $numppage>0)
			$pagetotal=ceil($numtotal/$numppage);
		else
			$pagetotal=1;
		if($page>1 && $page<=$pagetotal)
			$this->db->sql_rowseek(($page-1)*$numppage);
		while($i<$numppage+0 && $rs=$this->db->sql_fetchrow($stmt)){
			$songO=new SongO();
			$songO->setId($rs['idsong']);
			$songO->setTitle($rs['title']);
			$songO->setGenreid($rs['idgenre']);
			$songO->setGenrename($rs['genrename']);
			$songO->setIdalbum($rs['idalbum']);
			$songO->setAlbumname($rs['albumtitle']);
			$songO->setLyrics($rs['lyrics']);
			$songO->setAuthor($rs['author']);
			$songO->setIdartist($rs['idartist']);
			$songO->setNumber($rs['number']);
			$songO->setTrack($rs['track']);
			$songO->setArtistname($rs['artistname']);
			$songO->setCount($numtotal);
			$songList[$i]=$songO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $songList;
	}
	function addView($idsong){
	  if(is_numeric($idsong)){
		$sql="UPDATE ".$this->prefix."_topmusic_song SET views=views+1 WHERE idsong=".$idsong;	
		$this->db->sql_query($sql);
	  }
	}
	function getSongTitle($idsong){
	  if(is_numeric($idsong)){
		$sql="SELECT title FROM ".$this->prefix."_topmusic_song WHERE idsong=".$idsong;	
		$stmt=$this->db->sql_query($sql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$name=$rs['title'];
		}
		$this->db->sql_freeresult($stmt);
	  }
		return $name;
	}
	function getMostVisitedSongs($numSongs=10){
		$sSql="	SELECT 
				song.idsong,
			  song.idalbum,
			  song.title,
			  song.views,
			  song.track,
			  album.idartist,
			  album.title albumname,
			  artist.name artistname
			FROM
			  ".$this->prefix."_topmusic_song song
			  LEFT OUTER JOIN ".$this->prefix."_topmusic_album album
			  	ON (song.idalbum = album.idalbum)
			  LEFT OUTER JOIN ".$this->prefix."_topmusic_artist artist
			  	ON (album.idartist = artist.idartist)
			ORDER BY song.views desc, song.avgvotes desc
			LIMIT ".$numSongs;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$songO=new SongO();
			$songO->setId($rs['idsong']);
			$songO->setIdalbum($rs['idalbum']);
			$songO->setIdartist($rs['idartist']);
			$songO->setTitle($rs['title']);
			$songO->setCount($rs['views']);
			$songO->setIdartist($rs['idartist']);
			$songO->setAlbumname($rs['albumname']);
			$songO->setArtistname($rs['artistname']);
			$songO->setTrack($rs['track']);
			$songList[$i]=$songO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $songList;
	}
	/**
	 * @deprecated 3.0PR5 - Apr 12, 2006
	 */
	//	function updateSong($song){
	//		$sSQL="UPDATE ".$this->prefix."_topmusic_song SET
	//			nvotes=".$song->getNvotes().",avgvotes=".$song->getAvgvotes()."
	//			WHERE idsong=".$song->getId();
	//		$this->db->sql_query($sSQL);
	//		$error=$this->db->sql_error();
	//		if($error['message']!="")
	//			return false;
	//		else
	//			return true;
	//	}
	function voteSong($idsong,$rate,$ip){
		$sql="SELECT * FROM ".$this->prefix."_topmusic_song_votes where IP like '".$ip."' AND idsong=".$idsong." LIMIT 1";
		//print $sql."<br>";
		$stmt=$this->db->sql_query($sql);
		if($this->db->sql_fetchrow($stmt)){
			$this->db->sql_freeresult($stmt);
			return false;
		}else{
			$this->db->sql_freeresult($stmt);
			$sql="INSERT INTO ".$this->prefix."_topmusic_song_votes (idsong,value,IP,votedate) VALUES ($idsong,$rate,'$ip',NOW())";
			//print $sql."<br>";
			$this->db->sql_query($sql);
			$error=$this->db->sql_error();
			if($error['message']!="")
				return false;
			else
				return true;
		}
	}
	function getSongRate($idsong){
		$sql="SELECT AVG(value) rate FROM ".$this->prefix."_topmusic_song_votes WHERE idsong=".$idsong;
		//print $sql."<br>";
		$stmt=$this->db->sql_query($sql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$avg=$rs['rate'];
			$this->db->sql_freeresult($stmt);
			return $avg;
		}else
			$this->db->sql_freeresult($stmt);
			return false;
	}
	function getMostVotedSongs($numSongs=10,$idartist=""){
		$sSql="	SELECT 
				son.idsong,
			  	son.title,
				son.idalbum,
				son.track,
				alb.title albumname,
				alb.idartist,
				alb.frontcover_file albumcover_file,
			    alb.frontcover_url albumcover_url,
				art.name artistname,
				AVG(vot.value) avgvotes
			FROM
			  	".$this->prefix."_topmusic_song_votes vot
				INNER JOIN ".$this->prefix."_topmusic_song son ON (vot.idsong = son.idsong)
			  	INNER JOIN ".$this->prefix."_topmusic_album alb ON (son.idalbum = alb.idalbum)
				INNER JOIN ".$this->prefix."_topmusic_artist art ON (art.idartist = alb.idartist) ";
			if($idartist!="")
				$sSql.=" WHERE alb.idartist=".$idartist;
		$sSql.=" GROUP BY son.idsong
			ORDER BY vot.value desc,son.title asc
			LIMIT ".$numSongs;
				
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$songO=new SongO();
			$songO->setId($rs['idsong']);
			$songO->setIdalbum($rs['idalbum']);
			$songO->setIdartist($rs['idartist']);
			$songO->setTitle($rs['title']);
			$songO->setAvgvotes($rs['avgvotes']);
			$songO->setIdartist($rs['idartist']);
			$songO->setAlbumname($rs['albumname']);
			$songO->setArtistname($rs['artistname']);
			$songO->setAlbumcover_file($rs['albumcover_file']);
			$songO->setAlbumcover_url($rs['albumcover_url']);
			$songO->setTrack($rs['track']);
			$songList[$i]=$songO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $songList;
	}
	function getRelated($idsong){
		$sSql="	SELECT 
				song.idsong,
				song.idalbum,
				album.idartist,
			  	song.title,
				song.track
			 FROM
			  ".$this->prefix."_topmusic_song song
			  INNER JOIN ".$this->prefix."_topmusic_song_relation rel ON (song.idsong = rel.idsong_related)
				LEFT JOIN ".$this->prefix."_topmusic_album album ON (song.idalbum = album.idalbum)
			WHERE rel.idsong=$idsong
			ORDER BY song.avgvotes desc";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$songO=new SongO();
			$songO->setId($rs['idsong']);
			$songO->setIdalbum($rs['idalbum']);
			$songO->setIdartist($rs['idartist']);
			$songO->setTitle($rs['title']);
			$songO->setTrack($rs['track']);
			$songList[$i]=$songO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $songList;
	}
	function getCustomFields($idsong){
		$sSql="	SELECT 
			  	camps.name,
				camps.description,
				camps.type,
				valors.value,
				camps.select_values,
				camps.file_path,
				camps.file_icon,
				camps.image_width
			 FROM
			  ".$this->prefix."_topmusic_song_fields camps
			  INNER JOIN ".$this->prefix."_topmusic_song_fields_values valors ON (camps.idsong_field = valors.idsong_field)
			WHERE valors.idsong=$idsong
			ORDER BY camps.name";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$songFieldO=new CustomFieldO();
			$songFieldO->name=$rs['name'];
			$songFieldO->type=$rs['type'];
			$songFieldO->value=$rs['value'];
			$songFieldO->description=$rs['description'];
			$songFieldO->description=$rs['description'];
			$songFieldO->file_path=$rs['file_path'];
			$songFieldO->file_icon=$rs['file_icon'];
			$songFieldO->image_thumb=$rs['image_thumb'];
			$songFieldO->image_width=$rs['image_width'];
			$songFields[$i]=$songFieldO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $songFields;
	} 
}
?>
100% Free PHP Newsletter Script! Download now!
Return current item: Top Music module