<?php
namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\Validation\Validator;
use Cake\ORM\TableRegistry;
use Cake\Datasource\ConnectionManager;

class EstadisticoTable extends Table
{

    public function getPopularVideos()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube, V.imagen, V.likes_no, V.dislike,
			(count(LV.id_video)*100)/(SELECT count(*) FROM log_video) pct, V.youtube_id, CC.descripcion cat,CC.imagen cat, CS.imagen subcat, CS.descripcion subcat, CS.id id_subcat, CS.imagen scat, CS.id id_cat FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			GROUP BY LV.id_video ORDER BY sitio DESC LIMIT 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosBySubcat($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube, V.imagen, V.likes_no, V.dislike,
			(count(LV.id_video)*100)/(SELECT count(*) FROM log_video) pct, CVS.id_subcategoria, V.youtube_id, CC.descripcion cat, CC.imagen cat, CC.imagen subcat, CS.descripcion subcat, 
			CS.imagen subcat, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' group by LV.id_video order by sitio DESC LIMIT 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosByCat($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube, V.imagen, V.likes_no, V.dislike,
			(count(LV.id_video)*100)/(SELECT count(*) from log_video) pct, CS.id_categoria id_cat , V.youtube_id, CC.descripcion cat, CC.imagen cat, CS.descripcion subcat, CS.imagen subcat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' group by id_video order by sitio DESC LIMIT 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosNoLimit()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube,
			(count(LV.id_video)*100)/(SELECT count(*) FROM log_video) pct, V.youtube_id, CC.descripcion cat, CS.descripcion subcat, V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			GROUP BY LV.id_video
			UNION 
			SELECT V.id, V.nombre, 0 sitio, V.views_no youtube, 0 pct, V.youtube_id, CC.descripcion cat, CS.descripcion subcat , V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE V.id NOT IN (SELECT id_video from log_video) 
			 ORDER BY sitio DESC')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosBySubcatNoLimit($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube,
			(count(LV.id_video)*100)/(SELECT count(*) FROM log_video) pct, CVS.id_subcategoria, V.youtube_id, CC.descripcion cat, CS.descripcion subcat , V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' group by LV.id_video
			UNION 
			SELECT V.id, V.nombre, 0 sitio, V.views_no youtube, 0 pct, CVS.id_subcategoria, V.youtube_id, CC.descripcion cat, CS.descripcion subcat , V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE V.id NOT IN (SELECT id_video from log_video) and CVS.id_subcategoria='.$idSubcat.'
			order by sitio DESC')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosByCatNoLimit($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube, 
			(count(LV.id_video)*100)/(SELECT count(*) from log_video) pct, CS.id_categoria , V.youtube_id, CC.descripcion cat, CS.descripcion subcat, V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' group by id_video
			UNION 
			SELECT V.id, V.nombre, 0 sitio, V.views_no youtube, 0 pct, CS.id_categoria, V.youtube_id, CC.descripcion cat, CS.descripcion subcat, V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE V.id NOT IN (SELECT id_video from log_video) and CS.id_categoria='.$idCat.'
			order by sitio DESC')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosDateLimit($inicio,$fin)
    {
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube,
			(count(LV.id_video)*100)/(SELECT count(*) FROM log_video) pct, V.youtube_id, CC.descripcion cat, CS.descripcion subcat, V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE LV.hora BETWEEN "'.$inicio.'" AND "'.$fin.'" 
			GROUP BY LV.id_video
			UNION 
			SELECT V.id, V.nombre, 0 sitio, V.views_no youtube, 0 pct, V.youtube_id, CC.descripcion cat, CS.descripcion subcat , V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE V.id NOT IN (SELECT id_video from log_video) 
			 ORDER BY sitio DESC')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosBySubcatDateLimit($idSubcat,$inicio,$fin)
    {
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube,
			(count(LV.id_video)*100)/(SELECT count(*) FROM log_video) pct, CVS.id_subcategoria, V.youtube_id, CC.descripcion cat, CS.descripcion subcat , V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' AND LV.hora BETWEEN "'.$inicio.'" AND "'.$fin.'" group by LV.id_video
			UNION 
			SELECT V.id, V.nombre, 0 sitio, V.views_no youtube, 0 pct, CVS.id_subcategoria, V.youtube_id, CC.descripcion cat, CS.descripcion subcat , V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE V.id NOT IN (SELECT id_video from log_video) and CVS.id_subcategoria='.$idSubcat.'
			order by sitio DESC')->fetchAll('assoc');
		return $results;
    }
    public function getPopularVideosByCatDateLimit($idCat,$inicio,$fin)
    {
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LV.id_video, V.nombre, count(LV.id_video) sitio, views_no youtube, 
			(count(LV.id_video)*100)/(SELECT count(*) from log_video) pct, CS.id_categoria , V.youtube_id, CC.descripcion cat, CS.descripcion subcat, V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM log_video LV 
			LEFT JOIN video V on V.id=LV.id_video 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' and LV.hora BETWEEN "'.$inicio.'" AND "'.$fin.'" group by id_video
			UNION 
			SELECT V.id, V.nombre, 0 sitio, V.views_no youtube, 0 pct, CS.id_categoria, V.youtube_id, CC.descripcion cat, CS.descripcion subcat, V.imagen, V.likes_no, V.dislike, CC.id id_cat, CS.id id_subcat
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE V.id NOT IN (SELECT id_video from log_video) and CS.id_categoria='.$idCat.'
			order by sitio DESC')->fetchAll('assoc');
		return $results;
    }
    public function getVistasPorCategoria()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LC.id_categoria, count(LC.id_categoria) visitas,(count(LC.id_categoria)*100)/(SELECT count(*) from log_categoria) pct, CC.descripcion, CC.imagen, 
			(SELECT count(V.id) from video V LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria WHERE CS.id_categoria=LC.id_categoria) videos
			FROM log_categoria LC 
			LEFT JOIN cat_categoria CC ON CC.id=LC.id_categoria 
			GROUP BY LC.id_categoria ')->fetchAll('assoc');
		return $results;
    }
    public function getVistasPorSubcategoria()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT LC.id_subcategoria, count(LC.id_subcategoria) visitas,(count(LC.id_subcategoria)*100)/(SELECT count(*) from log_subcategoria) pct, CC.descripcion, CC.imagen
			FROM log_subcategoria LC 
			LEFT JOIN cat_subcategoria CC ON CC.id=LC.id_subcategoria 
			GROUP BY LC.id_subcategoria ')->fetchAll('assoc');
		return $results;
    }

    public function getMaxLikes()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.likes_no, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			ORDER BY likes_no DESC LIMIT 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getMaxLikesBySubcat($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.likes_no, V.youtube_id, CC.descripcion cat, CS.descripcion subcat
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' order by likes_no DESC limit 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getMaxLikesByCat($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.likes_no, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' ORDER BY likes_no DESC limit 3 ')->fetchAll('assoc');
		return $results;
    }

    public function getMaxDislikes()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.dislike, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			ORDER BY dislike DESC LIMIT 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getMaxDislikesBySubcat($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.dislike, V.youtube_id, CC.descripcion cat, CS.descripcion subcat
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' order by dislike DESC limit 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getMaxDislikesByCat($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.dislike, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' ORDER BY dislike DESC limit 3 ')->fetchAll('assoc');
		return $results;
    }
    public function getVisitasByCategory($inicio,$fin,$catArr)
    {
    	$cats="";
    	foreach ($catArr as $key => $value) {
    		$cats.=$value.",";
    	}
    	$cats.="0";
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT id_categoria, hora, count(*) cuantos FROM log_categoria 
			WHERE hora BETWEEN "'.$inicio.'" AND "'.$fin.'" and id_categoria IN ('.$cats.')
			GROUP BY EXTRACT(year from hora), EXTRACT(month from hora), EXTRACT(day from hora)')->fetchAll('assoc');
		return $results;
    }


    public function getVisitasBySubategory($inicio,$fin,$subArr)
    {
    	$subcats="";
    	foreach ($subArr as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT id_subcategoria, hora, count(*) cuantos FROM log_subcategoria 
			WHERE hora BETWEEN "'.$inicio.'" AND "'.$fin.'" and id_subcategoria IN ('.$subcats.') 
			GROUP BY EXTRACT(year from hora), EXTRACT(month from hora), EXTRACT(day from hora)')->fetchAll('assoc');
		return $results;
    }

    public function getVisitasByVideo($inicio,$fin,$videoArr)
    {
    	$videos="";
    	foreach ($videoArr as $key => $value) {
    		$videos.=$value.",";
    	}
    	$videos.="0";
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT id_video, hora, count(*) cuantos FROM log_video 
			WHERE hora BETWEEN "'.$inicio.'" AND "'.$fin.'" and id_video IN ('.$videos.') 
			GROUP BY EXTRACT(year from hora), EXTRACT(month from hora), EXTRACT(day from hora)')->fetchAll('assoc');
		/*echo $fin;
		print_r($results);*/
		return $results;
    }
    public function getVisitasMain($inicio,$fin)
    {
    	
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT hora, count(*) cuantos FROM log_main 
			WHERE hora BETWEEN "'.$inicio.'" AND "'.$fin.'"
			GROUP BY EXTRACT(year from hora), EXTRACT(month from hora), EXTRACT(day from hora)')->fetchAll('assoc');
		return $results;
    }
    public function getMaxLikesNoLimit()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.likes_no, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			ORDER BY likes_no DESC')->fetchAll('assoc');
		return $results;
    }
    public function getMaxLikesBySubcatNoLimit($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.likes_no, V.youtube_id, CC.descripcion cat, CS.descripcion subcat
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' order by likes_no DESC')->fetchAll('assoc');
		return $results;
    }
    public function getMaxLikesByCatNoLimit($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.likes_no, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' ORDER BY likes_no DESC')->fetchAll('assoc');
		return $results;
    }

    public function getMaxDislikesNoLimit()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.dislike, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			ORDER BY dislike DESC')->fetchAll('assoc');
		return $results;
    }
    public function getMaxDislikesBySubcatNoLimit($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.dislike, V.youtube_id, CC.descripcion cat, CS.descripcion subcat
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CVS.id_subcategoria='.$idSubcat.' order by dislike DESC')->fetchAll('assoc');
		return $results;
    }
    public function getMaxDislikesByCatNoLimit($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id, V.nombre, V.dislike, V.youtube_id, CC.descripcion cat, CS.descripcion subcat 
			FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria
			WHERE CS.id_categoria='.$idCat.' ORDER BY dislike DESC')->fetchAll('assoc');
		return $results;
    }
    public function TotalDislikesSubcategoria($catArray)
    {
    	$subcats="";
    	foreach ($catArray as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id,V.nombre videoNombre,CS.descripcion subcat,CC.descripcion cat, V.youtube_id,V.dislike dislikes FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria 
			WHERE CVS.id_subcategoria IN ('.$subcats.') 
			order by dislikes DESC ')->fetchAll('assoc');
		return $results;
    }

    public function TotalLikesSubcategoria($catArray)
    {
    	$subcats="";
    	foreach ($catArray as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT V.id,V.nombre videoNombre,CS.descripcion subcat,CC.descripcion cat, V.youtube_id,V.likes_no likes FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria 
			WHERE CVS.id_subcategoria IN ('.$subcats.') 
			order by likes DESC ')->fetchAll('assoc');
		return $results;
    }
    public function TotalDislikesCategoria($catArray)
    {
    	$subcats="";
    	foreach ($catArray as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CS.id,CS.descripcion subcat, sum(V.dislike) dislikes FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria 
			WHERE CVS.id_subcategoria IN ('.$subcats.') 
			GROUP BY CVS.id_subcategoria 
			order by dislikes DESC ')->fetchAll('assoc');
		return $results;
    }

    public function TotalLikesCategoria($catArray)
    {
    	$subcats="";
    	foreach ($catArray as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CS.id,CS.descripcion subcat, sum(V.likes_no) likes FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria 
			WHERE CVS.id_subcategoria IN ('.$subcats.') 
			GROUP BY CVS.id_subcategoria 
			order by likes DESC ')->fetchAll('assoc');
		return $results;
    }
    public function TotalDislikes($catArray)
    {
    	$subcats="";
    	foreach ($catArray as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CC.id,CC.descripcion cat, sum(V.dislike) dislikes FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria 
			WHERE CC.id IN ('.$subcats.') 
			GROUP BY CC.id 
			order by dislikes DESC ')->fetchAll('assoc');
		return $results;
    }

    public function TotalLikes($catArray)
    {
    	$subcats="";
    	foreach ($catArray as $key => $value) {
    		$subcats.=$value.",";
    	}
    	$subcats.="0";
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CC.id,CC.descripcion cat, sum(V.likes_no) likes FROM video V 
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_video=V.id 
			LEFT JOIN cat_subcategoria CS ON CS.id=CVS.id_subcategoria 
			LEFT JOIN cat_categoria CC ON CC.id=CS.id_categoria 
			WHERE CC.id IN ('.$subcats.') 
			GROUP BY CC.id 
			order by likes DESC ')->fetchAll('assoc');
		return $results;
    }
    public function getAllVideos()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT id from video')->fetchAll('assoc');
		return $results;
    }
    public function getAllVideosFromCategoria($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT DISTINCT CVS.id_video from cat_categoria CC
			LEFT JOIN cat_subcategoria CS ON CS.id_categoria=CC.id
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_subcategoria=CS.id
			WHERE CC.id='.$idCat)->fetchAll('assoc');
		return $results;
    }
    public function getAllVideosFromSubcategoria($idSubcat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT DISTINCT CVS.id_video FROM cat_subcategoria CS
			LEFT JOIN cross_video_subcategoria CVS ON CVS.id_subcategoria=CS.id
			WHERE CS.id='.$idSubcat)->fetchAll('assoc');
		return $results;
    }
    public function getAllSubcategorias()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CS.id from cat_subcategoria CS')->fetchAll('assoc');
		return $results;
    }
    public function getAllSubcategoriasFromCategoria($idCat)
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CS.id from cat_categoria CC
			LEFT JOIN cat_subcategoria CS ON CS.id_categoria=CC.id
			WHERE CC.id='.$idCat." and CS.estatus=1")->fetchAll('assoc');
		return $results;
    }
    public function getAllCategorias()
    {
    	$connection = ConnectionManager::get('default');
		$results = $connection->execute('SELECT CC.id from cat_categoria CC WHERE CC.estatus=1')->fetchAll('assoc');
		return $results;
    }
}

?>