query.php

<?php

class Artistas {

	private $id;

	public function __construct($id) {
		$this->id = $id;
	}

	public function __get($attr){
		return $this->$attr;
	}

	public static function GetArtistaByID($id) {
	
		$sql = db::getInstance();

		$result = $sql->query("SELECT DISTINCT A.name_artist AS Nome, A.artist_image AS Imagem, 
								A.gravadora AS Gravadora, A.site AS Site, A.facebook AS Face, 
								A.instagram AS Insta, B.currency_1 AS Moeda
								FROM artist AS A INNER JOIN currency AS B ON A.currency=B.id_currency 
								WHERE A.id_artist=".$id."; ");
		
		while( $row = $result->fetch_assoc() ){ 
			$lista[] = $row;
		}
		if (isset($lista[0])) { return $lista; } else {return null;}
	}

	public static function GetSpotsArtista($id) {		

		$sql = db::getInstance();
		$result = $sql->query("SELECT a.id_item, SUM(a.SPOTS) 'TOTAL SPOTS',
 			SUM(a.`GET+`) 'TOTAL GET+', (SELECT COUNT(id_user) FROM follow_artist 
 			where follow_artist.id_artist = 19) 'FOLLOWERS' FROM  (SELECT a.id_item,
 			COUNT(a.id_item) 'SPOTS', SUM(a.force_spot) 'GET+', a.city_name 'CIDADE',
 			a.province_name 'ESTADO', a.country_name 'PAIS', avg(a.price) 'PREÇO MÉDIO'
			FROM ( SELECT spots.id_user, spots.id_item, spots.price, spots.force_spot,
   			city.city_name, province.province_name, country.country_name FROM spots,
   			userinfo, city, province, country WHERE spots.id_user = userinfo.user_id
  			AND userinfo.id_city = city.id_city AND city.id_province = province.id_province
  			AND province.id_country = country.id_country AND (spots.es_end_date >= (now())
   			OR (spots.es_start_date = '0002-12-31' AND spots.es_start_date = '0001-12-31'))
   			AND spots.id_item = $id GROUP BY spots.id_user) a GROUP BY a.id_item,
  			a.city_name, a.province_name, a.country_name) a; ");
  					
		if ( $row = $result->fetch_assoc() ){ 
			$lista = $row['TOTAL SPOTS'];
		}
		if (isset($lista)) { return $lista; } else {return null;}
	}

	public static function GetEventsArtista($id) {		

		$sql = db::getInstance();
		$result = $sql->query("SELECT COUNT(a.artist) 'EVENTS' FROM event AS a WHERE a.artist=".$id."; ");		
		if ( $row = $result->fetch_assoc() ){ 
			$lista = $row['EVENTS'];
		}
		if (isset($lista)) { return $lista; } else {return null;}
	}

	public static function ListaSpotsArtista($idArtista) {

		$sql = db::getInstance();
		$result = $sql->query("SELECT COUNT(a.id_item) 'SPOTS', SUM(a.force_spot) 'GET+',
				 a.city_name 'CIDADE', a.province_name 'ESTADO', a.country_name 'PAIS',
				 avg(a.price) 'PREÇO MÉDIO' FROM ( SELECT
				   spots.id_user, spots.id_item, spots.price, spots.force_spot,
				   city.city_name, province.province_name, country.country_name
				  FROM spots, userinfo, city, province, country
				  WHERE spots.id_user = userinfo.user_id
				  AND userinfo.id_city = city.id_city
				  AND city.id_province = province.id_province
				  AND province.id_country = country.id_country
				  AND (spots.es_end_date >= (now())
				   OR (spots.es_start_date = '0002-12-31'
				    AND spots.es_start_date = '0001-12-31')
				  ) AND spots.id_item = $idArtista 
				  GROUP BY spots.id_user) a GROUP BY
				 a.id_item, a.city_name, a.province_name, a.country_name");

		while( $row = $result->fetch_assoc() ){ 
			$lista[] = $row;
		}
		if (isset($lista[0])) { return $lista; } else {return null;}
	}

	public static function ListaArtistasRelacionados($idArtista) {

		$sql = db::getInstance();
		$result = $sql->query("SELECT ( SELECT e.name_artist FROM artist e WHERE
   					e.id_artist = d.id_item) 'ARTISTA', (SELECT g.gravadora FROM
   					artist g WHERE g.id_artist = d.id_item) 'GRAVADORA',(SELECT
   					f.artist_image FROM artist f WHERE f.id_artist = d.id_item) 
					'IMAGEM', (  ( COUNT(d.id_user) / ( SELECT 
					COUNT(DISTINCT id_item, id_user) FROM spots WHERE id_item = $idArtista
					AND spots.es_end_date >= now() ) ) * 100) '% MATCH' FROM
					( SELECT DISTINCT a.id_user, a.id_item FROM spots a, (
					SELECT DISTINCT b.id_user, b.id_item FROM spots b WHERE b.id_item = $idArtista) c
					WHERE a.id_user = c.id_user AND a.es_end_date >= now()
					ORDER BY a.id_item) d WHERE d.id_item <> $idArtista
					GROUP BY d.id_item ORDER BY 4 DESC");

		while( $row = $result->fetch_assoc() ){ 
			if ($row['ARTISTA']!='') $lista[] = $row;
		}
		if (isset($lista[0])) { return $lista; } else {return null;}
	}

	public static function ListaArtistasRelacionadosCidades($idArtista) {

		$sql = db::getInstance();
		$result = $sql->query("SELECT (SELECT e.name_artist FROM artist e 
  			WHERE e.id_artist = d.id_item ) 'ARTISTA', (SELECT g.gravadora
  			FROM artist g WHERE g.id_artist = d.id_item ) 'GRAVADORA', (
  			SELECT f.artist_image FROM artist f WHERE f.id_artist = d.id_item
 			) 'IMAGEM', (( COUNT(d.id_user) / (SELECT COUNT(DISTINCT id_item, id_user)
    		FROM spots WHERE id_item = $idArtista AND spots.es_end_date >= now())) * 100
 			) '% MATCH', ( SELECT city.city_name FROM city WHERE city.id_city = d.id_city
 			) 'CIDADE', (  SELECT province.province_name FROM city, province
  			WHERE city.id_province = province.id_province AND city.id_city = d.id_city
 			) 'ESTADO', (SELECT country.country_name FROM city, province, country
  			WHERE city.id_province = province.id_province AND 
  			province.id_country = country.id_country AND city.id_city = d.id_city
 			) 'PAIS' FROM ( SELECT DISTINCT a.id_user, a.id_item, c.id_city
  			FROM spots a, ( SELECT DISTINCT b.id_user, b.id_item, b2.id_city
    		FROM spots b, userinfo b1, city b2 WHERE b1.user_id = b.id_user
    		AND b1.id_city = b2.id_city AND b.id_item = $idArtista ) c
  			WHERE a.id_user = c.id_user AND a.es_end_date >= now() 
  			ORDER BY a.id_item ) d WHERE d.id_item <> $idArtista 
			GROUP BY d.id_item, d.id_city ORDER BY 5 DESC;");

		while( $row = $result->fetch_assoc() ){ 
			$lista[] = $row;
		}
		if (isset($lista[0])) { return $lista; } else {return null;}
	}


}