<?php

class gestion_avis {

	
	function get_moyenne_ami_gplace($contact_id, $gplace, $types)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		/****** on récupère la liste d'ami ********/
		//TODO : choper les amis des amis plus tard
		$tab_ami = array();
		
		$sql = "SELECT listeami_ami
			FROM avidami_liste_ami
			WHERE listeami_contact = '".$contact_id."'
			AND listeami_valide = '1'";
		$query_ami = $db->sql_query($sql);
		
		while($result_ami = $db->sql_fetchrow($query_ami))
		{
			$tab_ami[] = $result_ami["listeami_ami"];
		}
		
		$tab_ami[] = $_SESSION["contact_log_id"];
		/************************************************/
		
		/************* on calcul les moyennes ****************/
		$final_tab = array();
		
		$tab_general = array("nb_bleu"=>0, "nb_rouge"=>0, "nb_avis"=>0);
		$tab_avis_id = array();//J'utilise un tableau d'id d'avis pour éviter de faire trop d'appel DB
		$tab_commentaire = array();
		
		$sql = "SELECT avis_commentaire, avis_contact, avis_pouce, contact_nom, contact_prenom, avis_inserted, avis_id, contactmedia_src
			FROM avis_interet
			INNER JOIN contact ON contact_id = avis_contact
			LEFT JOIN contact_media ON contactmedia_contact = contact_id AND contactmedia_principal = '1' 
			WHERE avis_gplace = '".$db->sql_inject($gplace)."'
			AND avis_contact IN ('".implode("','", $tab_ami)."')
			AND avis_type 
			ORDER BY avis_inserted DESC";
		$query_avis = $db->sql_query($sql);
		
		if($db->sql_numrows($query_avis) > 0)
		{
			while($result_avis = $db->sql_fetchrow($query_avis))
			{
				if($result_avis["avis_pouce"] == 1)
				{
					$tab_general["nb_bleu"]++;
				}
				else{
					$tab_general["nb_rouge"]++;
				}
				
				$tab_general["nb_avis"]++;
				
				$tab_avis_id[] = $result_avis["avis_id"];
				
				$tab_commentaire[] = $result_avis;
			}
			
			$final_tab["general"] = $tab_general;
			$final_tab["liste_avis_commentaire"] = $tab_commentaire;
			
			//Je recherche toutes les notes des critères
			
			$tab_crit = $this->get_critere_avis($tab_avis_id, $types);
			
			$final_tab["details"] = $tab_crit;
			$final_tab["success"] = "OK";
		}
		else{
			$final_tab["success"] = "no_avis";
		}
		/***************************************************/
		
		return $final_tab;
	}
	
	
	function get_critere_avis($tab_avis_id, $types)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		$tab_crit = array();
		
		$sql = "";
		
		if($types==null)
		{
			$sql = "SELECT pointinterettype_id, pointinterettype_label, pointinterettype_labelgoogle
				FROM point_interet_type
				GROUP BY pointinterettype_id";
		}
		else{
			$sql = "SELECT pointinterettype_id, pointinterettype_label, pointinterettype_labelgoogle
				FROM point_interet_type
				WHERE pointinterettype_id IN ('".implode("','", $types)."')
				GROUP BY pointinterettype_id";
		}
		
		$query_type = $db->sql_query($sql);
		$i = 0;
		
		while($result_type = $db->sql_fetchrow($query_type))
		{
			$tab_crit[$i]["type"] = $result_type;
			
			/*$sql = "SELECT aviscritere_critere, AVG(`aviscritere_note`) as `aviscritere_moyenne`, critere_id, criterelang_label, critere_order
				FROM avis_interet_critere
				INNER JOIN notation_critere ON critere_id = aviscritere_critere
				INNER JOIN notation_critere_lang ON criterelang_critere = critere_id
				INNER JOIN avis_interet ON avis_id = aviscritere_avis
				WHERE aviscritere_avis IN ('".implode("','", $tab_avis_id)."')
				AND FIND_IN_SET('".$result_type["pointinterettype_id"]."', critere_type)
				GROUP BY aviscritere_critere
				ORDER BY critere_order ASC";*/
			
			$sql = "SELECT aviscritere_critere, AVG(`aviscritere_note`) as `aviscritere_moyenne`, critere_id, criterelang_label, critere_order
				FROM avis_interet_critere
				INNER JOIN notation_critere ON critere_id = aviscritere_critere
				INNER JOIN notation_critere_lang ON criterelang_critere = critere_id
				INNER JOIN avis_interet ON avis_id = aviscritere_avis
				WHERE avis_id IN ('".implode("','", $tab_avis_id)."')
				AND FIND_IN_SET('".$result_type["pointinterettype_id"]."', avis_type)
				GROUP BY aviscritere_critere
				ORDER BY critere_order ASC";
			
			$query_avis_crit = $db->sql_query($sql);
			
			while($result_avis_crit = $db->sql_fetchrow($query_avis_crit))
			{
				$result_avis_crit["aviscritere_moyenne"] = round($result_avis_crit["aviscritere_moyenne"]);
				
				$tab_crit[$i]["critere"][] = $result_avis_crit;
			}
			
			$i++;
		}
		
		return $tab_crit;
	}
	
	
	
	function get_avis_general_ami($contact_id, $gplace, $types)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		/****** on récupère la liste d'ami ********/
		//TODO : choper les amis des amis plus tard
		$tab_ami = array();
		
		$sql = "SELECT listeami_ami
			FROM avidami_liste_ami
			WHERE listeami_contact = '".$contact_id."'
			AND listeami_valide = '1'";
		$query_ami = $db->sql_query($sql);
		
		while($result_ami = $db->sql_fetchrow($query_ami))
		{
			$tab_ami[] = $result_ami["listeami_ami"];
		}
		
		$tab_ami[] = $_SESSION["contact_log_id"];
		/************************************************/
		
		/******************* on calcul les moyennes ****************/
		$tab_avis = array("nb_bleu_ami"=>0, "nb_rouge_ami"=>0, "nb_avis_ami"=>0);
		
		$sql = "SELECT avis_id, avis_pouce, avis_type
			FROM avis_interet
			WHERE avis_gplace = '".$db->sql_inject($gplace)."'
			AND avis_contact IN ('".implode("','", $tab_ami)."')
			AND avis_type IN ('".implode("','", $types)."')";
		$query_avis = $db->sql_query($sql);
		
		while($result_avis = $db->sql_fetchrow($query_avis))
		{
			if($result_avis["avis_pouce"] == 1){
				$tab_avis["nb_bleu_ami"]++;
			}
			else{
				$tab_avis["nb_rouge_ami"]++;
			}
			
			$tab_avis["nb_avis_ami"]++;
		}
		/***********************************************************/
		
		return $tab_avis;
	}
	
	
	function get_avis_general($gplace, $types)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		/******************* on calcul les moyennes ****************/
		$tab_avis = array("nb_bleu_total"=>0, "nb_rouge_total"=>0, "nb_avis_total"=>0);
		
		$sql = "SELECT avis_id, avis_pouce, avis_type
			FROM avis_interet
			WHERE avis_gplace = '".$db->sql_inject($gplace)."'
			AND avis_type IN ('".implode("','", $types)."')";
		$query_avis = $db->sql_query($sql);
		
		while($result_avis = $db->sql_fetchrow($query_avis))
		{
			if($result_avis["avis_pouce"] == 1){
				$tab_avis["nb_bleu_total"]++;
			}
			else{
				$tab_avis["nb_rouge_total"]++;
			}
			
			$tab_avis["nb_avis_total"]++;
		}
		/***********************************************************/
		
		return $tab_avis;
	}
	
	
	function get_liste_avis_commentaire_ami($contact_id, $gplace, $types)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		/****** on récupère la liste d'ami ********/
		//TODO : choper les amis des amis plus tard
		$tab_ami = array();
		
		$sql = "SELECT listeami_ami
			FROM avidami_liste_ami
			WHERE listeami_contact = '".$contact_id."'
			AND listeami_valide = '1'";
		$query_ami = $db->sql_query($sql);
		
		while($result_ami = $db->sql_fetchrow($query_ami))
		{
			$tab_ami[] = $result_ami["listeami_ami"];
		}
		
		$tab_ami[] = $_SESSION["contact_log_id"];
		/************************************************/
		
		/******************* on calcul les moyennes ****************/
		$tab_commentaire = array();
		
		$sql = "SELECT avis_commentaire, avis_contact, avis_pouce, contact_nom, contact_prenom, avis_inserted, contactmedia_src
			FROM avis_interet
			INNER JOIN contact ON contact_id = avis_contact
			LEFT JOIN contact_media ON contactmedia_contact = contact_id AND contactmedia_principal = '1' 
			WHERE avis_gplace = '".$db->sql_inject($gplace)."'
			AND avis_contact IN ('".implode("','", $tab_ami)."')
			AND avis_type IN ('".implode("','", $types)."')
			ORDER BY avis_inserted DESC";
		$query_avis = $db->sql_query($sql);
		
		while($result_avis = $db->sql_fetchrow($query_avis))
		{
			$tab_commentaire[] = $result_avis;
		}
		/***********************************************************/
		
		return $tab_commentaire;
	}
	
	
	function delete_avis($avis_id)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		if($avis_id!=0 && $avis_id!="" && $avis_id!= null)
		{
			$sql = "DELETE FROM avis_interet
				WHERE avis_id = '".$db->sql_inject($avis_id)."'";
			$db->sql_query($sql);
			
			$sql = "DELETE FROM avis_interet_critere
				WHERE aviscritere_avis = '".$db->sql_inject($avis_id)."'";
			$db->sql_query($sql);
			
			return true;
		}
		
		return false;
	}
	
	
	function get_commentaires_sur_avis($avis_id)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		$tab_commentaire = array();
		
		$sql = "SELECT *
			FROM avis_commentaire
			INNER JOIN contact ON contact_id = aviscommentaire_contact
			LEFT JOIN contact_media ON contactmedia_contact = contact_id AND contactmedia_principal = '1' 
			WHERE aviscommentaire_avis = '".$avis_id."'
			ORDER BY aviscommentaire_inserted ASC";
		$query = $db->sql_query($sql);
		
		while($result = $db->sql_fetchrow($query))
		{
			$tab_commentaire[] = $result;
		}
		
		return $tab_commentaire;
	}
	
	
	function get_last_avis_contact($contact_id)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		$sql = "SELECT *
			FROM avis_interet";
	}
	
	function get_moyenne_ami_contenu($contact_id, $pointinteret_id, $types)
	{
		global $requete,$db,$template,$tpl,$convert,$widget;
		
		/****** on récupère la liste d'ami ********/
		//TODO : choper les amis des amis plus tard
		$tab_ami = array();
		
		$sql = "SELECT listeami_ami
			FROM avidami_liste_ami
			WHERE listeami_contact = '".$contact_id."'
			AND listeami_valide = '1'";
		$query_ami = $db->sql_query($sql);
		
		while($result_ami = $db->sql_fetchrow($query_ami))
		{
			$tab_ami[] = $result_ami["listeami_ami"];
		}
		
		$tab_ami[] = $_SESSION["contact_log_id"];
		/************************************************/
		
		/************* on calcul les moyennes ****************/
		$final_tab = array();
		
		$tab_general = array("nb_bleu"=>0, "nb_rouge"=>0, "nb_avis"=>0);
		$tab_avis_id = array();//J'utilise un tableau d'id d'avis pour éviter de faire trop d'appel DB
		$tab_commentaire = array();
		
		$sql = "SELECT avis_commentaire, avis_contact, avis_pouce, contact_nom, contact_prenom, avis_inserted, avis_id, contactmedia_src
			FROM avis_interet
			INNER JOIN contact ON contact_id = avis_contact
			LEFT JOIN contact_media ON contactmedia_contact = contact_id AND contactmedia_principal = '1' 
			WHERE avis_pointinteret = '".$db->sql_inject($pointinteret_id)."'
			AND avis_contact IN ('".implode("','", $tab_ami)."')
			AND avis_type 
			ORDER BY avis_inserted DESC";
		$query_avis = $db->sql_query($sql);
		
		if($db->sql_numrows($query_avis) > 0)
		{
			while($result_avis = $db->sql_fetchrow($query_avis))
			{
				if($result_avis["avis_pouce"] == 1)
				{
					$tab_general["nb_bleu"]++;
				}
				else{
					$tab_general["nb_rouge"]++;
				}
				
				$tab_general["nb_avis"]++;
				
				$tab_avis_id[] = $result_avis["avis_id"];
				
				$tab_commentaire[] = $result_avis;
			}
			
			$final_tab["general"] = $tab_general;
			$final_tab["liste_avis_commentaire"] = $tab_commentaire;
			
			//Je recherche toutes les notes des critères
			
			$tab_crit = $this->get_critere_avis($tab_avis_id, $types);
			
			$final_tab["details"] = $tab_crit;
			$final_tab["success"] = "OK";
		}
		else{
			$final_tab["success"] = "no_avis";
		}
		/***************************************************/
		
		return $final_tab;
	}
	
	
	
}



?>