Location: PHPKode > projects > Top Music module > Top Music 3.0.7/html/modules/topMusic/lib/com/sergids/topmusic/model/ArtistDAO.php
<?php
require_once("ArtistO.php");
require_once("CustomFieldO.php");
require_once("modules/topMusic/lib/com/sergids/common/File.php");
class ArtistDAO{
	var $db;
	var $prefix;
	function ArtistDAO($db,$prefix="nuke"){
		$this->db=$db;
		$this->prefix=$prefix;
	} 
	function getArtist($id,$language){
	  if(is_numeric($id)){
		$artistO=new ArtistO();
		$sSql="
		SELECT 
		  ".$this->prefix."_topmusic_artist.idartist,
		  ".$this->prefix."_topmusic_genre_lang.name genrename,
		  ".$this->prefix."_topmusic_artist.name artistname,
		  ".$this->prefix."_topmusic_artist.bornyear,
		  ".$this->prefix."_topmusic_artist.deadyear,
		  ".$this->prefix."_topmusic_artist.registerdate,
		  ".$this->prefix."_topmusic_artist.photo,
		  ".$this->prefix."_topmusic_artist.photo_file,
		  ".$this->prefix."_topmusic_genre_lang.name,
		  ".$this->prefix."_topmusic_artist.webpage,
		  ".$this->prefix."_topmusic_artist.forumURL,
		  ".$this->prefix."_topmusic_artist.members
		FROM
		  ".$this->prefix."_topmusic_artist
		  LEFT OUTER JOIN ".$this->prefix."_topmusic_genre ON (".$this->prefix."_topmusic_artist.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_artist.idartist =".$id." AND
		  (".$this->prefix."_topmusic_genre_lang.language='".$language."' OR ".$this->prefix."_topmusic_genre_lang.language IS NULL)";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$artistO->setId($rs['idartist']);
			$artistO->setName($rs['artistname']);
			$artistO->setBornyear($rs['bornyear']);
			$artistO->setDeadyear($rs['deadyear']);
			$artistO->setGenreid($rs['idgenre']);
			$artistO->setGenrename($rs['genrename']);
			$artistO->setPhoto_file($rs['photo_file']);
			$artistO->setPhoto_url($rs['photo']); //photo url
			if($artistO->getPhotoFile()!="")
				$artistO->setPhoto($artistO->getPhotoFile());
			elseif($artistO->getPhotoUrl()!="")
				$artistO->setPhoto($artistO->getPhotoUrl());
			else{
				$artistO->photo="anonym.jpg";
				$artistO->photo_file="anonym.jpg";
			}
			$artistO->setWeb($rs['webpage']);
			$artistO->setForum($rs['forumURL']);
			$votes=$this->getArtistRate($id);
			$artistO->setAvgvotes($votes);
			$artistO->setMembers($rs['members']);
		}
		$this->db->sql_freeresult($stmt);
		$sSql="SELECT biography FROM ".$this->prefix."_topmusic_artist_lang WHERE idartist=".$id." AND language='".$language."'";
		//die($sSql);
		$stmt=$this->db->sql_query($sSql);
		if($rs=$this->db->sql_fetchrow($stmt))
			$artistO->setBiography($rs['biography']);
		$this->db->sql_freeresult($stmt);
	  }
		return $artistO;
	}
	function getArtistIndex($letter=""){
		$sSql="	SELECT 
		  ".$this->prefix."_topmusic_artist.idartist,
		  ".$this->prefix."_topmusic_artist.name
		FROM
		  ".$this->prefix."_topmusic_artist";
		if($letter!="")
			$sSql.=" WHERE UPPER(".$this->prefix."_topmusic_artist.name) like '".strtoupper($letter)."%'";
		$sSql.=" ORDER BY ".$this->prefix."_topmusic_artist.name";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$artistO=new ArtistO();
			$artistO->setId($rs['idartist']);
			$artistO->setName($rs['name']);
			$artistList[$i]=$artistO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $artistList;
	}
	function searchArtist($artist,$freetext,$language,$page=1,$numppage=10){
		$sSql="	SELECT 
				  ".$this->prefix."_topmusic_artist.idartist,
				  ".$this->prefix."_topmusic_artist.idgenre,
				  ".$this->prefix."_topmusic_artist.name artistname,
				  ".$this->prefix."_topmusic_artist.bornyear,
				  ".$this->prefix."_topmusic_artist.deadyear,
				  ".$this->prefix."_topmusic_artist.photo_file,
				  ".$this->prefix."_topmusic_artist.photo,
				  ".$this->prefix."_topmusic_album.title albumtitle,
				  ".$this->prefix."_topmusic_song.title songtitle
				FROM
				  ".$this->prefix."_topmusic_artist
				  LEFT OUTER JOIN ".$this->prefix."_topmusic_album ON (".$this->prefix."_topmusic_artist.idartist = ".$this->prefix."_topmusic_album.idartist)
				  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_lang ON (".$this->prefix."_topmusic_artist.idartist = ".$this->prefix."_topmusic_artist_lang.idartist)";
		$junc=" AND ";
		$sWhere="";
		if($artist->getGenreid()!="")
			$sWhere.=" ".$this->prefix."_topmusic_artist.idgenre=".$artist->getGenreid();
		if($artist->getName()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_artist.name like '%".$artist->getName()."%'";
		}
		if($artist->getAlbumname()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_album.title like '%".$artist->getAlbumname()."%'";
		}
		if($artist->getSongtitle()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_song.title like '%".$artist->getSongtitle()."%'";
		}
		if($artist->getBornyear()!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" (".$this->prefix."_topmusic_artist.bornyear <= ".$artist->getBornyear()." AND
			".$this->prefix."_topmusic_artist.deadyear >= ".$artist->getBornyear().")
			OR 
			(".$this->prefix."_topmusic_artist.bornyear <= ".$artist->getBornyear()." AND
			".$this->prefix."_topmusic_artist.deadyear IS NULL)
			";
		}
		if($freetext!=""){
			if($sWhere!="")
				$sWhere.=$junc;
			$sWhere.=" ".$this->prefix."_topmusic_artist_lang.biography like '%".$freetext."%'";
		}
		if($sWhere!="")
			$sWhere=" WHERE (".$this->prefix."_topmusic_artist_lang.language='".$language."' OR ".$this->prefix."_topmusic_artist_lang.language IS NULL) AND ".$sWhere;
		$groupBy=" GROUP BY ".$this->prefix."_topmusic_artist.idartist";
		$sSql.=$sWhere.$groupBy;
		//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=0;
		if($page>1 && $page<=$pagetotal)
			$this->db->sql_rowseek(($page-1)*$numppage);
		while(($rs=$this->db->sql_fetchrow($stmt)) && $i<$numppage){
			$artistO=new ArtistO();
			$artistO->setId($rs['idartist']);
			$artistO->setName($rs['artistname']);
			$artistO->setBornyear($rs['bornyear']);
			$artistO->setDeadyear($rs['deadyear']);
			$artistO->setCount($numtotal);
			$artistList[$i]=$artistO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $artistList;
	}
	function addView($idartist){
	  if(is_numeric($idartist)){
		$sql="UPDATE ".$this->prefix."_topmusic_artist SET views=views+1 WHERE idartist=".$idartist;	
		$this->db->sql_query($sql);
		//$error=$this->db->sql_error();
		//echo $error['message'];
	  }
	}
	function getArtistName($idartist){
	  if(is_numeric($idartist)){
		$sql="SELECT name FROM ".$this->prefix."_topmusic_artist WHERE idartist=".$idartist;	
		$stmt=$this->db->sql_query($sql);
		if($rs=$this->db->sql_fetchrow($stmt)){
			$name=$rs['name'];
		}
		$this->db->sql_freeresult($stmt);
	  }
		return $name;
	}
	function getMostVisitedBands($numBands=10){
		$sSql="	SELECT 
			  idartist,
			  name artistname,
			  views,
			  photo,
			  photo_file
			FROM
			  ".$this->prefix."_topmusic_artist
			WHERE views > 0
			ORDER BY views desc
			LIMIT ".$numBands;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$bandO=new ArtistO();
			$bandO->setId($rs['idartist']);
			$bandO->setName($rs['artistname']);
			$bandO->setCount($rs['views']);
			$bandO->setPhoto_file($rs['photo_file']);
			$bandO->setPhoto_url($rs['photo']);
			if($bandO->photo_file=="" && $bandO->photo_url=="")
				$bandO->photo_file="anonym.jpg";
			$bandList[$i]=$bandO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $bandList;
	}
	/** deprecated **/
	//	function updateArtist($artist){
	//		$sSQL="UPDATE ".$this->prefix."_topmusic_artist SET
	//			nvotes=".$artist->getNvotes().",avgvotes=".$artist->getAvgvotes()."
	//			WHERE idartist=".$artist->getId();
	//		$this->db->sql_query($sSQL);
	//		$error=$this->db->sql_error();
	//		if($error['message']!="")
	//			return false;
	//		else
	//			return true;
	//	}
	function voteArtist($idartist,$rate,$ip){
		$sql="SELECT * FROM ".$this->prefix."_topmusic_artist_votes where IP like '".$ip."' AND idartist=".$idartist." 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_artist_votes (idartist,value,IP,votedate) VALUES ($idartist,$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 getArtistRate($idartist){
		$sql="SELECT AVG(value) rate FROM ".$this->prefix."_topmusic_artist_votes WHERE idartist=".$idartist;
		//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 getMostVotedArtists($numArtists=10){
		$sSql="	SELECT 
				art.idartist,
			  	art.name,
				art.photo_file,
				art.photo,
				art.bornyear,
				AVG(vot.value) avgvotes
			FROM
			  ".$this->prefix."_topmusic_artist_votes vot INNER JOIN
			  ".$this->prefix."_topmusic_artist art ON (vot.idartist = art.idartist)
			GROUP BY idartist
			ORDER BY vot.value desc,art.name asc
			LIMIT ".$numArtists;
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$artistO=new ArtistO();
			$artistO->setId($rs['idartist']);
			$artistO->setName($rs['name']);
			$artistO->setAvgvotes($rs['avgvotes']);
			$artistO->setPhoto_file($rs['photo_file']);
			$artistO->setPhoto_url($rs['photo']);
			if($artistO->photo_file=="" && $artistO->photo_url=="")
				$artistO->photo_file="anonym.jpg";
			$artistO->setBornyear($rs['bornyear']);
			$artistList[$i]=$artistO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $artistList;
	}
	function getRelated($idartist){
		$sSql="	SELECT 
				artist.idartist,
			  	name,
				photo_file,
				photo
			 FROM
			  ".$this->prefix."_topmusic_artist artist
			  INNER JOIN ".$this->prefix."_topmusic_artist_relation rel ON (artist.idartist = rel.idartist_related)
			WHERE rel.idartist=$idartist
			ORDER BY name desc";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$artistO=new ArtistO();
			$artistO->setId($rs['idartist']);
			$artistO->setName($rs['name']);
			$artistO->setPhoto($rs['photo']);
			$artistO->setPhoto_file($rs['photo_file']);
			$artistO->setPhoto_url($rs['photo']);
			if($artistO->photo_file=="" && $artistO->photo_url=="")
				$artistO->photo_file="anonym.jpg";
			$artistList[$i]=$artistO;
			$i++;
		}
		$this->db->sql_freeresult($stmt);
		return $artistList;
	}
	function getCustomFields($idartist){
		$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_artist_fields camps
			  INNER JOIN ".$this->prefix."_topmusic_artist_fields_values valors ON (camps.idartist_field = valors.idartist_field)
			WHERE valors.idartist=$idartist
			ORDER BY camps.name";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		while($rs=$this->db->sql_fetchrow($stmt)){
			$artistFieldO=new CustomFieldO();
			$artistFieldO->name=$rs['name'];
			$artistFieldO->type=$rs['type'];
			$artistFieldO->value=$rs['value'];
			$artistFieldO->description=$rs['description'];
			$artistFieldO->file_path=$rs['file_path'];
			$artistFieldO->file_icon=$rs['file_icon'];
			$artistFieldO->image_thumb=$rs['image_thumb'];
			$artistFieldO->image_width=$rs['image_width'];
			$artistFields[]=$artistFieldO;
		}
		$this->db->sql_freeresult($stmt);
		return $artistFields;
	}
	function getArtistIndexLetters(){
		$sSql="	SELECT 
		  ".$this->prefix."_topmusic_artist.name
		FROM
		  ".$this->prefix."_topmusic_artist";
		$sSql.=" ORDER BY ".$this->prefix."_topmusic_artist.name";
		//echo "sql=".$sSql."<br>";
		$stmt=$this->db->sql_query($sSql);
		$i=0;
		while($rs=$this->db->sql_fetchrow($stmt)){
			$name=$rs['name'];
			$first=substr($name,0,1);
			$first=strtoupper($first);
			if(count($letters)>0){
				if(!in_array($first,$letters)){
					$letters[]=$first;
				}
			}else
				$letters[]=$first;
		}
		$this->db->sql_freeresult($stmt);
		return $letters;
	} 
	
}

?>
Return current item: Top Music module