<?php
function get_user_company(){
	global $wpdb;
	$query='   select distinct dominio from
		 (select distinct(substring(mail1,1,position(\'.\' in mail1)-1) )dominio,user_email,mail1,id from (
		select substring(user_email,position(\'@\'in user_email)+1,(length(user_email)-position(\'@\' in user_email))) 
		mail1,user_email,id  from wp_users) tabla order by dominio) dominios';
	$date='';
	$results=$wpdb->get_results( $wpdb->prepare( $query ,$date));
	//echo $wpdb->last_query;
	return $results;
}


function get_users_by_company ($company){
	global $wpdb;
	$company='%'.$company.'%';
	$query2='select * from wp_users where user_email like %s';
	//echo '//'.$query.'//';

	$results2=$wpdb->get_results( $wpdb->prepare( $query2 ,$company));
	//echo $wpdb->last_query;
	//echo ' resultados users: '.sizeof($results2);
	
	return $results2;
	
}

function get_readed_by_category($date_ini,$date_end,$dominio,$usuario){
	
	$dominio='%'.$dominio.'%';
 	global $wpdb;
	
 	if ($dominio){
		 	$query2=' select t.*, count(grupo.object_id) as cuenta_numero_articulos, count(distinct(grupo.user_id)) as cuenta_usuarios 
			from
			 	(SELECT user_id,object_id  FROM wp_user_relations where object_type =\'browse\' and date between %s and %s) grupo 
			STRAIGHT_JOIN wp_users users on users.id=grupo.user_id and users.user_email like %s 
			STRAIGHT_JOIN wp_posts p on p.ID=grupo.object_id 
			STRAIGHT_JOIN wp_term_relationships tr on tr.object_id=p.ID 
			STRAIGHT_JOIN wp_term_taxonomy tt on tt.term_taxonomy_id=tr.term_taxonomy_id and tt.taxonomy=\'category\' 
			STRAIGHT_JOIN wp_terms t on t.term_id=tt.term_id ';
			if($usuario){
				$query2=$query2.' and grupo.user_id='.$usuario;
			}
			$query2=$query2.' group by t.term_id order by cuenta_numero_articulos desc';
			
			//echo '//'.$query.'//';
		
			$results2=$wpdb->get_results( $wpdb->prepare( $query2 ,$date_ini,$date_end,$dominio));
 	}else{
 		$query2=' select t.*, count(grupo.object_id) as cuenta_numero_articulos, count(distinct(grupo.user_id)) as cuenta_usuarios 
			from
			 	(SELECT user_id,object_id  FROM wp_user_relations where object_type =\'browse\' and date between %s and %s) grupo 
			STRAIGHT_JOIN wp_users users on users.id=grupo.user_id 
			STRAIGHT_JOIN wp_posts p on p.ID=grupo.object_id 
			STRAIGHT_JOIN wp_term_relationships tr on tr.object_id=p.ID 
			STRAIGHT_JOIN wp_term_taxonomy tt on tt.term_taxonomy_id=tr.term_taxonomy_id and tt.taxonomy=\'category\' 
			STRAIGHT_JOIN wp_terms t on t.term_id=tt.term_id ';
			if($usuario){
				$query2=$query2.' and grupo.user_id='.$usuario;
			}
			$query2=$query2.' group by t.term_id order by cuenta_numero_articulos desc limit 20';
			
			//echo '//'.$query.'//';
		
			$results2=$wpdb->get_results( $wpdb->prepare( $query2 ,$date_ini,$date_end));
 	}

	//echo $wpdb->last_query;
	//echo ' resultados users: '.sizeof($results2);
	
	return $results2;

}

function get_readed_by_company($date_ini,$date_end,$dominio,$usuario){
	
	$dominio='%'.$dominio.'%';
 	global $wpdb;
	
 	//if ($dominio){
		 	$query2='  select empresa.dominio, count(grupo.object_id) as cuenta_numero_articulos from (SELECT user_id,object_id  
		 	FROM wp_user_relations where object_type =\'browse\' and date between %s and %s) grupo 
STRAIGHT_JOIN 
(select substring(mail1,1,position(\'.\' in mail1)-1) dominio,user_email,mail1,id from (
select substring(user_email,position(\'@\'in user_email)+1,(length(user_email)-position(\'@\' in user_email))) mail1,user_email,id  from wp_users) tabla) empresa
on empresa.id=grupo.user_id STRAIGHT_JOIN wp_posts p on p.ID=grupo.object_id STRAIGHT_JOIN wp_term_relationships tr on tr.object_id=p.ID STRAIGHT_JOIN wp_term_taxonomy tt
 on tt.term_taxonomy_id=tr.term_taxonomy_id and tt.taxonomy=\'category\' STRAIGHT_JOIN wp_terms t on t.term_id=tt.term_id group by empresa.dominio order by cuenta_numero_articulos desc';
			
			//echo '//'.$query.'//';
		
			$results2=$wpdb->get_results( $wpdb->prepare( $query2 ,$date_ini,$date_end,$dominio));
 	//}else{
 		
 	//}

	//echo $wpdb->last_query;
	//echo ' resultados users: '.sizeof($results2);
	
	return $results2;

}

function get_readed_by_company_category_filter($date_ini,$date_end,$dominio,$categoria){
		
	$dominio='%'.$dominio.'%';
 	global $wpdb;
	
 	//if ($dominio){
		 	$query2='  select empresa.dominio, count(grupo.object_id) as cuenta_numero_articulos from (SELECT user_id,object_id  
		 	FROM wp_user_relations where object_type =\'browse\' and date between %s and %s) grupo 
STRAIGHT_JOIN 
(select substring(mail1,1,position(\'.\' in mail1)-1) dominio,user_email,mail1,id from (
select substring(user_email,position(\'@\'in user_email)+1,(length(user_email)-position(\'@\' in user_email))) mail1,user_email,id  from wp_users) tabla) empresa
on empresa.id=grupo.user_id STRAIGHT_JOIN wp_posts p on p.ID=grupo.object_id STRAIGHT_JOIN wp_term_relationships tr on tr.object_id=p.ID STRAIGHT_JOIN wp_term_taxonomy tt
 on tt.term_taxonomy_id=tr.term_taxonomy_id and tt.taxonomy=\'category\' STRAIGHT_JOIN wp_terms t on t.term_id=tt.term_id';

		 	if($categoria>0){
		 		$query2=$query2.' and t.term_id=%d ';
		 	}
		 	$query2=$query2.' group by empresa.dominio order by cuenta_numero_articulos desc';
			
			//echo '//'.$query.'//';
		
			$results2=$wpdb->get_results( $wpdb->prepare( $query2 ,$date_ini,$date_end,$categoria));
 	//}else{
 		
 	//}

	//echo $wpdb->last_query;
	//echo ' resultados users: '.sizeof($results2);
	
	return $results2;

}

function get_readed_by_user($date_ini,$date_end,$concepto,$dominio){
	global $wpdb;
	 if($dominio){
			$dominio='%'.$dominio.'%';
	 }
	$query='select empresa.display_name,empresa.dominio, count(grupo.object_id) as cuenta_numero_articulos 
	from 
	(SELECT user_id,object_id FROM wp_user_relations where object_type =\'browse\' and 
	date between %s and %s) grupo 
	STRAIGHT_JOIN 
	(select substring(mail1,1,position(\'.\' in mail1)-1) dominio,user_email,mail1,id,display_name from 
		( select substring(user_email,position(\'@\'in user_email)+1,(length(user_email)-position(\'@\' in user_email))) mail1,user_email,id,display_name from wp_users users ';
	if($dominio){
			$query=$query.' where users.user_email like %s';
	}
		 		 
		$query=$query.' ) tabla) empresa 
		on empresa.id=grupo.user_id 
	STRAIGHT_JOIN wp_posts p on p.ID=grupo.object_id 
	STRAIGHT_JOIN wp_term_relationships tr on tr.object_id=p.ID 
	STRAIGHT_JOIN wp_term_taxonomy tt on tt.term_taxonomy_id=tr.term_taxonomy_id and tt.taxonomy=\'category\' 
	STRAIGHT_JOIN wp_terms t on t.term_id=tt.term_id ';
	if($concepto>0){
		$query=$query.' and t.term_id=%d';
	}  
	$query=$query.' group by empresa.dominio order by cuenta_numero_articulos desc';
	
		
	if($dominio){
		$results2=$wpdb->get_results( $wpdb->prepare( $query ,$date_ini,$date_end,$dominio,$concepto));
	} else{
		$results2=$wpdb->get_results( $wpdb->prepare( $query ,$date_ini,$date_end,$concepto));
	}
	
	
	return $results2;
	
}
