<?php
require_once("AlbumO.php");
require_once("CustomFieldO.php");
/**
* Album Data Access Object
* @author sergi
* @copyright Copyright &copy; 2004, sergi
*/
class AlbumDAO{
	var $db;
	var $prefix;
	/**
	* The constructor
	* @param DB db database object
	* @param string prefix phpnuke database tables prefix
	* @return void
	* @author sergi
	*/
	function AlbumDAO($db,$prefix="nuke"){
		$this->db=$db;
		$this->prefix=$prefix;
	} 
	/**
	* Get album object
	* @param int id Album identifier
	* @param string language language for text query
	* @return AlbumO Album object
	* @author sergi
	*/
	function getAlbum($id,$language){
	  if(is_numeric($id)){
		$albumO=new AlbumO();
		$sSql="SELECT 
		  ".$this->prefix."_topmusic_album.idalbum,
		  ".$this->prefix."_topmusic_genre_lang.name genrename,
		  ".$this->prefix."_topmusic_album.idartist,
		  ".$this->prefix."_topmusic_artist.name artistname,
		  ".$this->prefix."_topmusic_album.title,
		  ".$this->prefix."_topmusic_album.backcover_file,
		  ".$this->prefix."_topmusic_album.frontcover_file,
		  ".$this->prefix."_topmusic_album.backcover_url,
		  ".$this->prefix."_topmusic_album.frontcover_url,
		  ".$this->prefix."_topmusic_album.yeardate,
		  ".$this->prefix."_topmusic_album.shopurl
		FROM
		  ".$this->prefix."_topmusic_album
		  LEFT OUTER JOIN ".$this->prefix."_topmusic_genre ON (".$this->prefix."_topmusic_album.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_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=".$id;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$albumO->setId($rs['idalbum']);
			$albumO->setTitle($rs['title']);
			$albumO->setYeardate($rs['yeardate']);
			$albumO->setGenreid($rs['idgenre']);
			$albumO->setGenrename($rs['genrename']);
			$albumO->setIdartist($rs['idartist']);
			$albumO->setArtistname($rs['artistname']);
			$albumO->setComments($rs['comments']);
			$albumO->frontcover_file=$rs['frontcover_file'];
			$albumO->setBackcover_file($rs['backcover_file']);
			$albumO->setFrontcover_url($rs['frontcover_url']);
			$albumO->setBackcover_url($rs['backcover_url']);
			if($albumO->frontcover_file=="" && $albumO->frontcover_url=="")
				$albumO->frontcover_file="no_cover.gif";
			$votes=$this->getAlbumRate($id);
			$albumO->setAvgvotes($votes);
			$albumO->setAmazonid($rs['amazonid']);
			$albumO->setShopurl($rs['shopurl']);
		}
		$this->db->sql_freeresult($stmt);
		$sSql="SELECT comments FROM ".$this->prefix."_topmusic_album_lang WHERE language='".$language."' AND idalbum=".$id;
		$stmt=$this->db->sql_query($sSql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$albumO->setComments($rs['comments']);
		}
		$this->db->sql_freeresult($stmt);
	  }
		return $albumO;
	}
	/**
	* Get list of albums
	* @param int idartist artist identificator
	* @return vector Vector of album objects
	* @author sergi
	*/
	function getAlbumList($idartist,$language,$order="yeardate",$limit=0){
	  if(is_numeric($idartist)){
		$sSql="	SELECT 
		  ".$this->prefix."_topmusic_album.idalbum,
		  ".$this->prefix."_topmusic_genre_lang.name genrename,
		  ".$this->prefix."_topmusic_album.idartist,
		  ".$this->prefix."_topmusic_artist.name artistname,
		  ".$this->prefix."_topmusic_album.title,
		  ".$this->prefix."_topmusic_album.yeardate,
		  ".$this->prefix."_topmusic_album.frontcover_file,
		  ".$this->prefix."_topmusic_album.frontcover_url
		FROM
		  ".$this->prefix."_topmusic_album
		  LEFT OUTER JOIN ".$this->prefix."_topmusic_genre ON (".$this->prefix."_topmusic_album.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)
		  INNER 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.idartist=".$idartist."
		ORDER BY ".$order;
		if($limit>0)
			$sSql.=" LIMIT ".$limit;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$albumO=new AlbumO();
			$albumO->setId($rs['idalbum']);
			$albumO->setTitle($rs['title']);
			$albumO->setYeardate($rs['yeardate']);
			$albumO->setGenreid($rs['idgenre']);
			$albumO->setGenrename($rs['genrename']);
			$albumO->setIdartist($rs['idartist']);
			$albumO->setArtistname($rs['artistname']);
			$albumO->setFrontcover_file($rs['frontcover_file']);
			$albumO->setFrontcover_url($rs['frontcover_url']);
			if($albumO->frontcover_file=="" && $albumO->frontcover_url=="")
				$albumO->frontcover_file="no_cover.gif";			
			$albumList[$i]=$albumO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
	  }
		return $albumList;
	}
	/**
	* Search inside albums
	* @param AlbumO album Album object with search parametres
	* @param string freetext free text to search for in some text fields
	* @param string language Language name for specific album text searching
	* @param int page Current page number (default=1)
	* @param int numpage Number of albums per page (default=10)
	* @return vector vector of album objects
	* @author sergi
	*/
	function searchAlbum($album,$freetext,$language,$page=1,$numppage=10){
		$sSql="	SELECT 
				  ".$this->prefix."_topmusic_album.idalbum,
				  ".$this->prefix."_topmusic_song.title songtitle,
				  ".$this->prefix."_topmusic_album.idgenre,
				  ".$this->prefix."_topmusic_album.idartist,
				  ".$this->prefix."_topmusic_artist.name artistname,
				  ".$this->prefix."_topmusic_album.title,
				  ".$this->prefix."_topmusic_album.yeardate
				FROM
				  ".$this->prefix."_topmusic_album
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_song ON (".$this->prefix."_topmusic_album.idalbum = ".$this->prefix."_topmusic_song.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_album_lang ON (".$this->prefix."_topmusic_album.idalbum = ".$this->prefix."_topmusic_album_lang.idalbum)
				  ";
		$junc=" AND ";
		$sWhere="";
		if($album->getGenreid()!="")
			$sWhere.=" ".$this->prefix."_topmusic_album.idgenre=".$album->getGenreid();
		if($album->getTitle()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_album.title like '%".$album->getTitle()."%'";
		}
		if($album->getArtistname()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_artist.name like '%".$album->getArtistname()."%'";
		}
		if($album->getSongtitle()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_song.title like '%".$album->getSongtitle()."%'";
		}
		if($freetext!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_album_lang.comments like '%".$freetext."%'";
		}
		if($sWhere!="")
			$sWhere=" WHERE (".$this->prefix."_topmusic_album_lang.language='".$language."' OR ".$this->prefix."_topmusic_album_lang.language IS NULL) AND ".$sWhere;
		$sSql.=$sWhere." GROUP BY idalbum";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		$numtotal=$this->db->sql_numrows();
		$pagetotal=ceil($numtotal/$numppage);
		if($page>1 && $page<=$pagetotal)
			$this->db->sql_rowseek(($page-1)*$numppage);
		while($i<$numppage+0 && $rs=$this->db->sql_fetchrow($stmt)){
			$albumO=new AlbumO();
			$albumO->setId($rs['idalbum']);
			$albumO->setTitle($rs['title']);
			$albumO->setYeardate($rs['yeardate']);
			$albumO->setIdartist($rs['idartist']);
			$albumO->setArtistname($rs['artistname']);
			$albumO->setCount($numtotal);
			$albumList[$i]=$albumO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $albumList;
	}
	function addView($idalbum){
	  if(is_numeric($idalbum)){
		$sql="UPDATE ".$this->prefix."_topmusic_album SET views=views+1 WHERE idalbum=".$idalbum;	
		$this->db->sql_query($sql);
	  }
	}
	function getAlbumTitle($idalbum){
	  if(is_numeric($idalbum)){
		$sql="SELECT title FROM ".$this->prefix."_topmusic_album WHERE idalbum=".$idalbum;	
		$stmt=$this->db->sql_query($sql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$name=$rs['title'];
		}
		$this->db->sql_freeresult($stmt);
	  }
		return $name;
	}
	function getMostVisitedAlbums($numAlbums=10){
		$sSql="	SELECT 
			  album.idalbum,
			  album.title,
			  album.views,
			  album.idartist,
			  artist.name artistname,
			  album.frontcover_file,
			  album.frontcover_url,
			  album.backcover_file,
			  album.backcover_url
			FROM
			  ".$this->prefix."_topmusic_album album
			  LEFT OUTER JOIN ".$this->prefix."_topmusic_artist artist
			  	ON (album.idartist=artist.idartist)
			WHERE album.views > 0
			ORDER BY album.views desc
			LIMIT ".$numAlbums;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$albumO=new AlbumO();
			$albumO->setId($rs['idalbum']);
			$albumO->setTitle($rs['title']);
			$albumO->setCount($rs['views']);
			$albumO->setIdartist($rs['idartist']);
			$albumO->setArtistname($rs['artistname']);
			$albumO->setFrontcover_file($rs['frontcover_file']);
			$albumO->setFrontcover_url($rs['frontcover_url']);
			$albumO->setBackcover_file($rs['backcover_file']);
			$albumO->setBackcover_url($rs['backcover_url']);
			if($albumO->frontcover_file=="" && $albumO->frontcover_url=="")
				$albumO->frontcover_file="no_cover.gif";
			$albumList[$i]=$albumO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $albumList;
	}
	/**
	 * deprecated
	 */
	//	function updateAlbum($album){
	//		$sSQL="UPDATE ".$this->prefix."_topmusic_album SET
	//			nvotes=".$album->getNvotes().",avgvotes=".$album->getAvgvotes()."
	//			WHERE idalbum=".$album->getId();
	//		$this->db->sql_query($sSQL);
	//		$error=$this->db->sql_error();
	//		if($error['message']!="")
	//			return false;
	//		else
	//			return true;
	//	}
	function voteAlbum($idalbum,$rate,$ip){
		$sql="SELECT * FROM ".$this->prefix."_topmusic_album_votes where IP like '".$ip."' AND idalbum=".$idalbum." 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_album_votes (idalbum,value,IP,votedate) VALUES ($idalbum,$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 getAlbumRate($idalbum){
		$sql="SELECT AVG(value) rate FROM ".$this->prefix."_topmusic_album_votes WHERE idalbum=".$idalbum;
		//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 getMostVotedAlbums($numAlbums=10,$idartist=""){
		
		$sSql="	SELECT 
				alb.idalbum,
			  	alb.title,
				alb.idartist,
				art.name artistname,
				alb.frontcover_file,
			    alb.frontcover_url,
			    alb.backcover_file,
			    alb.backcover_url,
				AVG(vot.value) avgvotes
			FROM
			  ".$this->prefix."_topmusic_album_votes vot
			  	INNER JOIN ".$this->prefix."_topmusic_album alb ON (vot.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 idalbum
			ORDER BY vot.value desc,alb.title asc
			LIMIT ".$numAlbums;
			  
			
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$albumO=new AlbumO();
			$albumO->setId($rs['idalbum']);
			$albumO->setIdartist($rs['idartist']);
			$albumO->setTitle($rs['title']);
			$albumO->setArtistname($rs['artistname']);
			$albumO->setFrontcover_file($rs['frontcover_file']);
			$albumO->setFrontcover_url($rs['frontcover_url']);
			$albumO->setBackcover_file($rs['backcover_file']);
			$albumO->setBackcover_url($rs['backcover_url']);
			$albumO->setAvgvotes($rs['avgvotes']);
			if($albumO->frontcover_file=="" && $albumO->frontcover_url=="")
				$albumO->frontcover_file="no_cover.gif";
			$albumList[$i]=$albumO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $albumList;
	}
	function getRelated($idalbum){
		$sSql="	SELECT 
				album.idalbum,
			  	title,
				frontcover_file,
				frontcover_url,
				artist.name artistname
			 FROM
			  ".$this->prefix."_topmusic_album album
			  INNER JOIN ".$this->prefix."_topmusic_album_relation rel ON (album.idalbum = rel.idalbum_related)
				LEFT JOIN ".$this->prefix."_topmusic_artist artist ON (album.idartist = artist.idartist)
			WHERE rel.idalbum=$idalbum
			ORDER BY yeardate desc";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$albumO=new AlbumO();
			$albumO->setId($rs['idalbum']);
			$albumO->setTitle($rs['title']);
			$albumO->setFrontcover_file($rs['frontcover_file']);
			$albumO->setFrontcover_url($rs['frontcover_url']);
			if($albumO->frontcover_file=="" && $albumO->frontcover_url=="")
				$albumO->frontcover_file="no_cover.gif";
			$albumO->artistname=$rs['artistname'];
			$albumList[$i]=$albumO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $albumList;
	}
	function getCustomFields($idalbum){
		$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_album_fields camps
			  INNER JOIN ".$this->prefix."_topmusic_album_fields_values valors ON (camps.idalbum_field = valors.idalbum_field)
			WHERE valors.idalbum=$idalbum
			ORDER BY camps.name";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$albumFieldO=new CustomFieldO();
			$albumFieldO->name=$rs['name'];
			$albumFieldO->type=$rs['type'];
			$albumFieldO->value=$rs['value'];
			$albumFieldO->description=$rs['description'];
			$albumFieldO->file_path=$rs['file_path'];
			$albumFieldO->file_icon=$rs['file_icon'];
			$albumFieldO->image_thumb=$rs['image_thumb'];
			$albumFieldO->image_width=$rs['image_width'];
			$albumFields[$i]=$albumFieldO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $albumFields;
	} 
	function getLastAlbumList($numAlbums=10){
		$sSql="	SELECT 
				  ".$this->prefix."_topmusic_album.idalbum,
				  ".$this->prefix."_topmusic_album.title,
				  ".$this->prefix."_topmusic_artist.idartist,
				  ".$this->prefix."_topmusic_artist.name artistname,
				  ".$this->prefix."_topmusic_album.frontcover_file,
				  ".$this->prefix."_topmusic_album.frontcover_url
				FROM
				  ".$this->prefix."_topmusic_album
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_artist ON (".$this->prefix."_topmusic_album.idartist=".$this->prefix."_topmusic_artist.idartist)
				ORDER BY idalbum desc
				LIMIT ".$numAlbums;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$albumO=new AlbumO();
			$albumO->setId($rs['idalbum']);
			$albumO->setTitle($rs['title']);
			$albumO->setIdartist($rs['idartist']);
			$albumO->setArtistname($rs['artistname']);
			$albumO->setFrontcover_url($rs['frontcover_url']);
			$albumO->setFrontcover_file($rs['frontcover_file']);
			if($albumO->frontcover_file=="" && $albumO->frontcover_url=="")
				$albumO->frontcover_file="no_cover.gif";
			$albumList[$i]=$albumO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $albumList;
	}
}
?>