<?php
global $wpdb;
global $post;


$user_id = get_current_user_id();
$user_categories = get_user_categories_2($user_id);

function get_user_categories_2($user_id){

		//echo 'user-products';
		if(!$user_id)$user_id = get_current_user_id();
		 $conceptos = ainia_get_user_terms($user_id, 'category',array(fields=>'ids'));
	
		return $conceptos;
	}

add_shortcode('top_entradas_usuario','top_entradas_usuario_shortcode');
function top_entradas_usuario_shortcode(){
?>
	
		<?php
		
 									$categories = get_categories();
 									$myhashmap = array();
									foreach ( $categories as $category ) {
										
										$args = array(
											'category_name'    => $category->name,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-7 days'))) 
										);
										
										$post_by_cat = get_posts($args);	
										$counter = 0;
										foreach($post_by_cat as $p)
										{
											$counter++;
										}
										
										if ($counter>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap[$texto] = $counter;
										}								
									}
									arsort($myhashmap);
									$temp_array=array_keys($myhashmap);
									$Key=$temp_array[0];
									
									return $Key.' <h2 style="display:inline">'.$myhashmap[$Key].'</h2>';

	?>	<?php
}

add_shortcode('estadistica_creciente_usuario','estadistica_creciente_usuario_shortcode');

function estadistica_creciente_usuario_shortcode(){
	$user_id = get_current_user_id();							

	$user_categories = ainia_get_user_terms($user_id, 'category',array(fields=>'ids'));
	$resultado=menu_periodos_estadisticas_usuario();
	$resultado=$resultado.'<div style="position:relative"><div id="est_semana"><h3>Tendencias</h3>Durante la última semana la tendencia más destacada es... <br>'.estadistica_creciente_usuario($user_categories,7);
	$resultado=$resultado.'<i><br>(*) Variación del Número de entradas respecto a la semana anterior </i>'.'</div>';
	$resultado=$resultado.'<div style="visibility:hidden;position:absolute;top:0;margin-bottom:5px;" id="est_mes"><h3>Tendencias</h3>Durante el último mes la tendencia más detacada es...<br>'.estadistica_creciente_usuario($user_categories,30);
	$resultado=$resultado.'<i><br>(*) Variación del Número de entradas respecto al mes anterior </i>'.'</div>';
	$resultado=$resultado.'<div style="visibility:hidden;position:absolute;top:0;margin-bottom:5px;" id="est_trimestre"><h3>Tendencias</h3>Durante el último año la tendencia más detacada es...<br>'.estadistica_creciente_usuario($user_categories,90);
	$resultado=$resultado.'<i><br>(*) Variación del Número de entradas respecto al año anterior </i>'.'</div></div>';
//	$resultado=$resultado.'<div style="position:relative;height:25px;border:solid black 0px;"><div style="margin-right:5px;position:absolute;right:0px;top:0px;"><a href="'.get_site_url().'"><i>Ver más...</i></a></div></div>';
	return $resultado;

}
function estadistica_creciente_usuario($user_categories,$periodo){
	//echo 'semana';
					
									//echo implode(",",$user_categories);
 									$results=query_tendencia ($periodo,$user_categories);
									$producto_max=$results[0]->name_actual;
									if(!$producto_max){
										$producto_max=$results[0]->name_anterior;
									}
									if($results[0]->cuenta_anterior > 0){
										$dif_porcentaje_max=round (($results[0]->diferencia/$results[0]->cuenta_anterior) * 100,1);
									}else{
										$dif_porcentaje_max=100;
									}
									
									$index_min=sizeof($results)-1;
									//echo sizeof($results); 
									$producto_min=$results[$index_min]->name_actual;
									if(!$producto_min){
										$producto_min=$results[$index_min]->name_anterior;
									}
									if($results[$index_min]->cuenta_anterior > 0){
										$dif_porcentaje_min=round (($results[$index_min]->diferencia/$results[$index_min]->cuenta_anterior) * 100,1);
									}else{
										$dif_porcentaje_min=-100;
									}
									if($results[0]->diferencia>0){
										$_string_max='<div style="margin-right:5px;display:inline;"><i class="fa fa-sort-asc" style="font-size:32px;color:#98DF58;padding:3px;"></i>'.$producto_max.' <h2 style="display:inline">+'.round($results[0]->diferencia,0).'</h2>('.$dif_porcentaje_max.'% )</div>';										
									}else {
										$_string_max='';
									}
										
									$_string_min='<i class="fa fa fa-sort-down" style="font-size:32px;color:#EC6464;padding:3px;margin-left:10px;"></i> '.$producto_min.' <h2 style="display:inline">'.round($results[$index_min]->diferencia,1).'</h2>('.$dif_porcentaje_min.'% ) ';
									
								//	$string_tendencia_semana='<h3>Tendencias</h3>(últimos 7 d�as)';
									$string_tendencia_semana=$_string_max;
									if($results[$index_min]->diferencia<0){
										$string_tendencia_semana=$_string_max.' | '.$_string_min;
									}
									return $string_tendencia_semana;

}

function menu_periodos_estadisticas_usuario(){
	$html='<div id="menu_periodos_estadisticas" style="margin-top:5px;"><a <a style="margin-left:5px;margin-right:5px;" id="myLink_week" href="#" onclick="showLastSevenDays();">últimos 7 días</a>';
	$html=$html.'|<a style="margin-left:5px;margin-right:5px;" id="myLink_month" href="#" onclick="showLastThirtyDays();">último mes</a>';
	$html=$html.'|<a style="margin-left:5px;margin-right:5px;" id="myLink_tri" href="#" onclick="showLastNinetyDays();">último trimestre</a></div>';
	
	return $html;
	
}
	

/****************DETALLE ESTADISTICAS*******************/
add_shortcode('estadistica_creciente_usuario_detalle','estadistica_creciente_usuario_detalle_shortcode');

function estadistica_creciente_usuario_detalle_shortcode(){
	//$resultado=menu_periodos_estadisticas_detalle();
	$hashmapSemana=estadistica_creciente_usuario_semana_detalle_shortcode();
	$div_semana='graficos_evolucion_3_semana';
	$subtitle_semana='(últimos 7 días)';
	
	$hashmapMes=estadistica_creciente_usuario_mes_detalle_shortcode();
	$div_mes='graficos_evolucion_3_mes';
	$subtitle_mes='(últimos 30 días)';
	
	$hashmapTrimestre=estadistica_creciente_usuario_trimestre_detalle_shortcode();
	$div_trimestre='graficos_evolucion_3_trimestre';
	$subtitle_trimestre='(último trimestre)';
	?>
	

	<div style="position:relative;width:50%;float:left;border-bottom:1px dotted grey;border-left:1px dotted grey;padding-left: 10px;  "><div id="est_semana_detalle">	<?php grafico_evolucion_conceptos_usuario_trimestre($hashmapSemana,$div_semana,$subtitle_semana) ?></div>
	<div style="visibility:hidden;position:absolute;top:0; width:100%;   border: 0px solid red;" id="est_mes_detalle"><?php grafico_evolucion_conceptos_usuario_trimestre($hashmapMes,$div_mes,$subtitle_mes) ?></div>
	<div style="visibility:hidden;position:absolute;top:0; width:100%;   border: 0px solid blue;" id="est_trimestre_detalle"><?php grafico_evolucion_conceptos_usuario_trimestre($hashmapTrimestre,$div_trimestre,$subtitle_trimestre) ?></div></div>
	
	<?php
	

}
function estadistica_creciente_usuario_semana_detalle_shortcode(){

 									$categories = get_categories();
 									$myhashmap_actual = array();
 									$myhashmap_anterior = array();
 									$myhashmap_diferencia = array();
 									$myhashmap_diferencia_ABS = array();
 									
 									$myhashmap_anterior_filtrado = array();
 									$myhashmap_actual_filtrado = array();
 									$hmTotal = array();
 									
									foreach ( $categories as $category ) {
										
										$args_semana_actual = array(
											'category_name'    => $category->name,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-7 days'))) 
										);
										$args_semana_anterior = array(
												'category_name'    => $category->name,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
												'posts_per_page'=>-1,
												'date_query'	   => array('before' => date('Y-m-d', strtotime('-4 days')),'after' => date('Y-m-d', strtotime('-20 days')))
										);
										
										$post_by_cat_semana_actual = get_posts($args_semana_actual);	
										$post_by_cat_semana_anterior = get_posts($args_semana_anterior);
										$counter_actual = 0;
										$counter_anterior = 0;
										foreach($post_by_cat_semana_actual as $p)
										{
											$counter_actual++;
										}
										foreach($post_by_cat_semana_anterior as $p)
										{	
											//echo $p->title;
											$counter_anterior++;
										}
										if ($counter_actual>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_actual[$texto] = $counter_actual;
											//echo 'ACTUAL: '.$texto.'-'.$counter_actual;
										}
										if ($counter_anterior>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_anterior[$texto] = $counter_anterior;
											//echo 'ANTERIOR: '.$texto.'-'.$counter_anterior;
										}
									
									}
									arsort($myhashmap_actual);	
									$max_results=0;
									foreach (array_keys($myhashmap_actual) as $key){
										//if($max_results>9){
										//	break;
										//}
										$diferencia=0;
										$valor_actual=0;
										$valor_anterior=0;
										$valor_actual=$myhashmap_actual[$key];
										$valor_anterior=$myhashmap_anterior[$key];
										if($myhashmap_anterior[$key]>0){
										
											//echo '----------'.$key.'----------';
											//echo 'VALOR_ACTUAL: '.$valor_actual;
										    //echo '---VALOR_ANTERIOR: '.$valor_anterior;
											$diferencia=($valor_actual-$valor_anterior);
											if ($diferencia >= 0){
												$myhashmap_diferencia_ABS[$key]=$diferencia;											
												$diferencia=$diferencia/$valor_anterior;
												//echo 'diferencia2:'.$diferencia;
												$myhashmap_diferencia[$key]=$diferencia*100;
											}
										
										}else{
											$myhashmap_diferencia[$key]=100;
											$myhashmap_diferencia_ABS[$key]=$valor_actual;
										}
						
									}
									arsort($myhashmap_diferencia_ABS);
									$_string_min='';
									$max_results=0;
								
									foreach (array_keys($myhashmap_diferencia_ABS) as $key){
										//echo '----------'.$key.'----------';
										if($max_results>9){
											break;
										}
										$myhashmap_anterior_filtrado[$key]=$myhashmap_anterior[$key];
										$myhashmap_actual_filtrado[$key]=$myhashmap_actual[$key];
										$max_results=$max_results+1;
										if($myhashmap_diferencia[$key]<0){
									
											$_string_min=$_string_min.'<i class="fa fa fa-sort-down" style="font-size:30px;color:#EC6464;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
												
										}else {
										
											$_string_min=$_string_min.'<i class="fa fa-sort-asc" style="font-size:30px;color:#98DF58;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
												
										}
									}
									
									arsort($myhashmap_actual_filtrado);
								
									$hmTotal['anterior']=$myhashmap_anterior_filtrado;
									$hmTotal['actual']=$myhashmap_actual_filtrado;
									$hmTotal['diferencia']=$myhashmap_diferencia_ABS;
									return $hmTotal;
										
									//return $_string_min;
									//return $myhashmap_diferencia_ABS;

	?><?php
}

function estadistica_creciente_usuario_mes_detalle_shortcode(){
	?>

		<?php
 									$categories = get_categories();
 									$myhashmap_actual = array();
 									$myhashmap_previo = array();
 									$myhashmap_diferencia = array();
 									$myhashmap_diferencia_ABS = array();
 									
 									$myhashmap_anterior_filtrado = array();
 									$myhashmap_actual_filtrado = array();
 									$hmTotal = array();
 									
									foreach ( $categories as $category ) {
										
										$args_semana_actual = array(
											'category_name'    => $category->name,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-30 days'))) 
										);
										$args_semana_anterior = array(
												'category_name'    => $category->name,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
												'posts_per_page'=>-1,
												'date_query'	   => array('before' => date('Y-m-d', strtotime('-30 days')),'after' => date('Y-m-d', strtotime('-60 days')))
										);
										
										$post_by_cat_semana_actual = get_posts($args_semana_actual);
										$post_by_cat_semana_anterior = get_posts($args_semana_anterior);
										$counter_actual = 0;
										$counter_anterior = 0;
										foreach($post_by_cat_semana_actual as $p)
										{
											$counter_actual++;
										}
										foreach($post_by_cat_semana_anterior as $p)
										{
											//echo $p->title;
											$counter_anterior++;
										}
										if ($counter_actual>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_actual[$texto] = $counter_actual;
											//echo 'ACTUAL: '.$texto.'-'.$counter_actual;
										}
										if ($counter_anterior>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_anterior[$texto] = $counter_anterior;
											//echo 'ANTERIOR: '.$texto.'-'.$counter_anterior;
										}
											
										}
										arsort($myhashmap_actual);
										$max_results=0;
										foreach (array_keys($myhashmap_actual) as $key){
											//if($max_results>9){
											//	break;
											//}
											$diferencia=0;
											$valor_actual=0;
											$valor_anterior=0;
											$valor_actual=$myhashmap_actual[$key];
											$valor_anterior=$myhashmap_anterior[$key];
											if($myhashmap_anterior[$key]>0){
										
												//echo '----------'.$key.'----------';
												//echo 'VALOR_ACTUAL: '.$valor_actual;
												//echo '---VALOR_ANTERIOR: '.$valor_anterior;
												$diferencia=($valor_actual-$valor_anterior);
												if ($diferencia >= 0){
													$myhashmap_diferencia_ABS[$key]=$diferencia;
													$diferencia=$diferencia/$valor_anterior;
													//echo 'diferencia2:'.$diferencia;
													$myhashmap_diferencia[$key]=$diferencia*100;
												}
										
											}else{
												$myhashmap_diferencia[$key]=100;
												$myhashmap_diferencia_ABS[$key]=$valor_actual;
											}
											
										}
										arsort($myhashmap_diferencia_ABS);
										$_string_min='';
											
										$max_results=0;
										foreach (array_keys($myhashmap_diferencia_ABS) as $key){
											//echo '----------'.$key.'----------';
											if($max_results>9){
												break;
											}
											$myhashmap_anterior_filtrado[$key]=$myhashmap_anterior[$key];
											$myhashmap_actual_filtrado[$key]=$myhashmap_actual[$key];
											$max_results=$max_results+1;
											if($myhashmap_diferencia[$key]<0){
													
												$_string_min=$_string_min.'<i class="fa fa fa-sort-down" style="font-size:30px;color:#EC6464;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}else {
										
												$_string_min=$_string_min.'<i class="fa fa-sort-asc" style="font-size:30px;color:#98DF58;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}
										}
											
										arsort($myhashmap_actual_filtrado);
										
										$hmTotal['anterior']=$myhashmap_anterior_filtrado;
										$hmTotal['actual']=$myhashmap_actual_filtrado;
										$hmTotal['diferencia']=$myhashmap_diferencia_ABS;
										return $hmTotal;

	?><?php
}
function estadistica_creciente_usuario_trimestre_detalle_shortcode(){
	?>
	
		<?php
 									$categories = get_categories();
 									$myhashmap_actual = array();
 									$myhashmap_previo = array();
 									$myhashmap_diferencia = array();
 									$myhashmap_diferencia_ABS = array();
 									
 									$myhashmap_anterior_filtrado = array();
 									$myhashmap_actual_filtrado = array();
 									$hmTotal = array();
 									
									foreach ( $categories as $category ) {
										
										$args_semana_actual = array(
											'category_name'    => $category->name,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-90 days'))) 
										);
										$args_semana_anterior = array(
												'category_name'    => $category->name,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
												'posts_per_page'=>-1,
												'date_query'	   => array('before' => date('Y-m-d', strtotime('-90 days')),'after' => date('Y-m-d', strtotime('-180 days')))
										);
										
										$post_by_cat_semana_actual = get_posts($args_semana_actual);
										$post_by_cat_semana_anterior = get_posts($args_semana_anterior);
										$counter_actual = 0;
										$counter_anterior = 0;
										foreach($post_by_cat_semana_actual as $p)
										{
											$counter_actual++;
										}
										foreach($post_by_cat_semana_anterior as $p)
										{
											//echo $p->title;
											$counter_anterior++;
										}
										if ($counter_actual>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_actual[$texto] = $counter_actual;
											//echo 'ACTUAL: '.$texto.'-'.$counter_actual;
										}
										if ($counter_anterior>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_anterior[$texto] = $counter_anterior;
											//echo 'ANTERIOR: '.$texto.'-'.$counter_anterior;
										}
											
										}
										arsort($myhashmap_actual);
										$max_results=0;
										foreach (array_keys($myhashmap_actual) as $key){
											//if($max_results>9){
											//	break;
											//}
											$diferencia=0;
											$valor_actual=0;
											$valor_anterior=0;
											$valor_actual=$myhashmap_actual[$key];
											$valor_anterior=$myhashmap_anterior[$key];
											if($myhashmap_anterior[$key]>0){
										
											
												$diferencia=($valor_actual-$valor_anterior);
												if ($diferencia >= 0){
													//echo '----------'.$key.'----------';
													//echo 'VALOR_ACTUAL: '.$valor_actual;
													//echo '---VALOR_ANTERIOR: '.$valor_anterior;
													$myhashmap_diferencia_ABS[$key]=$diferencia;
													$diferencia=$diferencia/$valor_anterior;
													//echo 'diferencia2:'.$diferencia;
													$myhashmap_diferencia[$key]=$diferencia*100;
												}
										
											}else{
												$myhashmap_diferencia[$key]=100;
												$myhashmap_diferencia_ABS[$key]=$valor_actual;
											}
											
										}
										arsort($myhashmap_diferencia_ABS);
										$_string_min='';
										$max_results=0;
										
										foreach (array_keys($myhashmap_diferencia_ABS) as $key){
											//echo '----------'.$key.'----------';
											if($max_results>9){
												break;
											}
											//echo '**** '.$key.'----------';
											//echo '******** VALOR_ACTUAL: '.$valor_actual;
											//echo '***VALOR_ANTERIOR: '.$valor_anterior;
											$myhashmap_anterior_filtrado[$key]=$myhashmap_anterior[$key];
											$myhashmap_actual_filtrado[$key]=$myhashmap_actual[$key];
											$max_results=$max_results+1;
											if($myhashmap_diferencia[$key]<0){
													
												$_string_min=$_string_min.'<i class="fa fa fa-sort-down" style="font-size:30px;color:#EC6464;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}else {
										
												$_string_min=$_string_min.'<i class="fa fa-sort-asc" style="font-size:30px;color:#98DF58;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}
										}
											
										arsort($myhashmap_actual_filtrado);
										
										$hmTotal['anterior']=$myhashmap_anterior_filtrado;
										$hmTotal['actual']=$myhashmap_actual_filtrado;
										$hmTotal['diferencia']=$myhashmap_diferencia_ABS;
										return $hmTotal;

	?><?php
}

function menu_periodos_estadisticas_usuario_detalle(){
	$html='<div id="menu_periodos_estadisticas"><a <a style="margin-right:5px;" id="myLink_week" href="#" onclick="showLastSevenDaysDetail();">últimos 7 días</a>';
	$html=$html.'|<a style="margin-left:5px;margin-right:5px;" id="myLink_month" href="#" onclick="showLastThirtyDaysDetail();">último mes</a>';
	$html=$html.'|<a style="margin-left:5px;margin-right:5px;" id="myLink_tri" href="#" onclick="showLastNinetyDaysDetail();">último trimestre</a></div>';
	
	echo $html;
	
}



add_shortcode('grafico_evolucion_anual_conceptos_usuario','grafico_evolucion_anual_conceptos_usuario_shortcode');
function grafico_evolucion_anual_conceptos_usuario_shortcode(){
	?>
	<div >
		<br>
		<h3>Evolución anual conceptos</h3>
		<br>
		<i>Se muestran las entradas por concepto de búsqueda por mes para los 10 conceptos más frecuentes, en los últimos 12 meses.</i>
	
		<script>
			jQuery(document).ready(function run_module_graphics($)
			{
		var grafica_entradas_x_tags = function run_module_graphics_4(){	
					<?php
						$categories = get_categories();
						//$colors=array('#000000','#661400','#991f00','#cc2900','#ff471a','#ff704d','#ff8566','#ff9980','#ffc2b3','#ffd6cc',' #ffebe6',' #f8dbd3');
						$colors= get_color_sequence();
						$color_index=0;
					?>
					
					var chart3 = new Highcharts.Chart({
						   legend: {
						        align: 'left',
						        verticalAlign: 'top',
						        layout: 'vertical',
						        x: 0,
						        y: 62
						    },
						chart: {
							renderTo: 'graficos_modulos_anyo',
							type: 'line',
							options3d: {
								enabled: true,
								alpha: 45
							}
						},
						exporting: { enabled: false },
						title: {
							text: null
						},  xAxis: {
					        categories: <?php echo genera_array_ultimos_doce_meses();?>
					    },
						 yAxis: {
				            min: 0,
				            title: {
				                text: 'Número de entradas',
				   
				            },
				            labels: {
				                overflow: 'justify'
				            }
				        },
						series: [
				
							<?php
										$first=0;
										$myhashmap = array();
						
										foreach ( $categories as $category ) {
									
											$args = array(
												'category_name'    => $category->name,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
													'posts_per_page'=>-1,
												'date_query'	   => array('after' => date('Y-m-d', strtotime('-365 days'))) 
											);
											
											$post_by_cat = get_posts($args);	
											$counter = 0;
											foreach($post_by_cat as $p)
											{
												$counter++;
											}
											
											if ($counter>0){
	
											$texto=str_replace("'","\'",$category->name);
											$myhashmap[$texto] = $counter;
	
											}
									
										}
										arsort($myhashmap);
										$max_results=0;
										foreach (array_keys($myhashmap) as $key){
											if($max_results>9){
												break;
											}
											if($max_results>0){
												echo ',';
											}
								
											echo '{name:\''.$key.'\',data:'. get_evolution_serie_category_usuario($key).',color: \''.$colors[$color_index].'\'}';
											if($color_index<sizeof($colors)-1){
												$color_index=$color_index+1;
											}else{
												$color_index=0;
											}
											$max_results=$max_results+1;
											
										}
										
										?>
							]
					});						
				}
			grafica_entradas_x_tags();
	});
		</script>
	
		
		<div id="grafico_evolucion_tags">
		
		<div id="graficos_modulos_anyo">
		
		</div>
	</div>
		</div>
	
	
	<?php
}
function get_evolution_serie_category_usuario($category){
	//echo date('Y-m-d', strtotime("last day of 0 month"));
	$previo=-1;
	$entradas_mes=array('','','','','','','','','','','','');
	$strEntradasMes='[';
	foreach ($entradas_mes as &$num_entradas) {
		if($previo<-1){
			//echo ' previo -1)';
			$strEntradasMes=$strEntradasMes.',';
			$args = array(
					'category_name'    => $category,
					'post_type'        => 'post',
					'post_status'      => 'publish',
					'suppress_filters' => true,
					'posts_per_page'=>-1,
					'date_query'	   => array('after' => date('Y-m-d', strtotime("last day of".($previo-1). "month")),
							'before' => date('Y-m-d', strtotime("first day of".($previo+1). "month")))
			);
		}else{
			//echo 'ELSE)';
			$args = array(
					'category_name'    => $category,
					'post_type'        => 'post',
					'post_status'      => 'publish',
					'suppress_filters' => true,
					'posts_per_page'=>-1,
					'date_query'	   => array('after' => date('Y-m-d', strtotime("last day of -1 month")))
			);
		}
		//echo  strtotime("last day of".($previo));
		//echo  strtotime("first day of".($previo+1));
	
		/*echo('------------');
		echo('date_ini: '.date('Y-m-d', strtotime("last day of".($previo). "month")));
		echo('date_end: '.date('Y-m-d', strtotime("first day of".($previo+1). "month")));*/
		$post_by_cat = get_posts($args);
		//echo $post_by_cat->request;
		$counter = 0;
		foreach($post_by_cat as $p)
		{
			$counter++;
		}
		$num_entradas=$counter;
		$strEntradasMes=$strEntradasMes.$counter;
	//	echo '--'.$num_entradas.'---';
		$previo=$previo-1;
	}
	$strEntradasMes=$strEntradasMes.']';
	return $strEntradasMes;
}
 
 
 
 /***************************************/
function grafico_evolucion_conceptos_usuario_trimestre($myhashmap,$div,$subtitlePeriod){
	$myhashmap_anterior = $myhashmap['anterior'];
	$myhashmap_actual= $myhashmap['actual'];

	?>
	<br>
	<h3>Evolución Entradas por concepto</h3><?php echo $subtitlePeriod ?>
	<br>
	<i>Se muestran los incrementos en el número de impactos m�s relevantes.</i>

	<script>
		jQuery(document).ready(function run_module_graphics($)
		{
	var grafica_entradas_x_tags = function run_module_graphics_4(){	
				<?php
					//$categories = get_categories();
					$colors=  get_color_sequence();
				//	$colors_negative=  get_negative_color_sequence();
					$color_index=0;
					$data_actual='';
					$data_anterior='';
				?>
				//$('#graficos_modulos_3').css('border','1px solid #999');
				//$('#graficos_modulos_3').css('box-shadow','2px 2px 10px #999');
				//$('#graficos_modulos_3').css('width','1003px');
				
				var chart3 = new Highcharts.Chart({
				
					chart: {
						renderTo: '<?php echo $div ?>',
						type: 'column',
						options3d: {
							enabled: true,
							alpha: 45
						}
					},
					exporting: { enabled: false },
					title: {
						text: null
					},
					 yAxis: {
			            min: 0,
			            title: {
			                text: 'Número de entradas'
			          
			            },
			            labels: {
			                overflow: 'justify'
			            }
			        },
			        xAxis:{
				        	categories: [<?php 
					        	$max_results=0;
					        	foreach (array_keys($myhashmap_actual) as $key){
					        		if($max_results>9){
					        			break;
					        		}
					        		if ($first>0){
					        			echo ',';
					        			$data_actual=$data_actual.',';
					        			$data_anterior = $data_anterior.',';
					        		}
					        		
					        		if (array_key_exists (  $key ,$myhashmap_actual)){
					        			$data_actual = $data_actual.$myhashmap_actual[$key];
					        		} else {
					        			$data_actual = $data_actual.'0';
					        		}
					        		
					        		if ($myhashmap_anterior[$key]<>''){
					        			$data_anterior = $data_anterior.$myhashmap_anterior[$key];
					        		} else {
					        			$data_anterior = $data_anterior.'0';
					        		}
					        	
					        
					        		echo '\''.$key.'\'';
					        	
					        		$first=$first+1;
					        		$max_results=$max_results+1;
					        	}
					        	?>


					        	]},
				            plotOptions: {
				                column: {
				                    grouping: false,
				                    shadow: false,
				                    borderWidth: 0,
				                    dataLabels: {
				                        enabled: true,
				                        color: 'gray'
				                    }
				                }
				                
				            },    tooltip: {
				                shared: true
				            },
					series: [{
						name: 'Periodo actual',
						color: <?php echo '\''.$colors[4].'\'';?>,
						data: [	<?php echo $data_actual;?>],
						pointPadding:0.2,
						pointPlacement:-0},
						{
							name: 'Periodo anterior',
							color: <?php echo '\''.$colors[7].'\''; ?>,
							data: 	[<?php echo $data_anterior;?>],
							pointPadding:0.3,
							pointPlacement:0}
				
						]
				});						
			}
		grafica_entradas_x_tags();
});
	</script>

	
	<div id="grafico_evolucion_tags">
	
	<div id="<?php echo $div ?>">
	
	</div>
</div>
<?php
}

function get_top_term_by_taxonomy($date,$taxonomy,$user_categories,$top){
	global $wpdb;
	
$query='select term.name,term.term_id,count(distinct posts.ID) as cuenta 
from wp_posts posts 	LEFT JOIN wp_term_relationships rel_cat_usu
											ON (posts.ID = rel_cat_usu.object_id)
											LEFT JOIN wp_term_taxonomy tax_cat_usu ON (rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id)
											INNER JOIN wp_terms cat_usu ON (tax_cat_usu.term_id=cat_usu.term_id) ';
		if (sizeof($user_categories)>0){
			$query=$query.'and cat_usu.term_id in('.implode(",",$user_categories).')';
		}else{
			$top=0;
		}
		
                                            
		$query=$query.'left JOIN wp_term_relationships rel
											ON (posts.ID = rel.object_id)
											left JOIN wp_term_taxonomy tax ON (rel.term_taxonomy_id=tax.term_taxonomy_id)
											left JOIN wp_terms term ON (tax.term_id=term.term_id)
											WHERE
											( posts.post_date > %s)
											AND posts.post_type = \'post\'
											AND ((posts.post_status = \'publish\'))
											AND tax.taxonomy= %s';
			if (strcmp($taxonomy,'category')==0){
				$query=$query.'AND  term.term_id in('.implode(",",$user_categories).') ';
			}

$query=$query.'GROUP BY term.name
				ORDER BY cuenta DESC
				LIMIT %d';

try {
	$results=$wpdb->get_results( $wpdb->prepare( $query,$date,$taxonomy,$top));
} catch (Exception $e) {
	
}

	//	echo $wpdb->last_query;
	return $results;
}
function evolucion_conc_seguimiento_semana_detalle_shortcode(){

 									$user_id = get_current_user_id();								
									$categories = ainia_get_user_terms($user_id, 'concepto_seguimiento',array(fields=>'all'));
 									//echo sizeof ($categories);
 									$myhashmap_actual = array();
 									$myhashmap_anterior = array();
 									$myhashmap_diferencia = array();
 									$myhashmap_diferencia_ABS = array();
 									
 									$myhashmap_anterior_filtrado = array();
 									$myhashmap_actual_filtrado = array();
 									$hmTotal = array();
 									
									foreach ( $categories as $category ) {
									//	echo $category->slug;
										$args_semana_actual = array(
											'concepto_seguimiento'    => $category->slug,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-7 days'))) 
										);
										$args_semana_anterior = array(
													'concepto_seguimiento'    => $category->slug,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
												'posts_per_page'=>-1,
												'date_query'	   => array('before' => date('Y-m-d', strtotime('-4 days')),'after' => date('Y-m-d', strtotime('-20 days')))
										);
										
										$post_by_cat_semana_actual = get_posts($args_semana_actual);	
										$post_by_cat_semana_anterior = get_posts($args_semana_anterior);
										$counter_actual = 0;
										$counter_anterior = 0;
										foreach($post_by_cat_semana_actual as $p)
										{
											$counter_actual++;
										}
										foreach($post_by_cat_semana_anterior as $p)
										{	
											//echo $p->title;
											$counter_anterior++;
										}
										if ($counter_actual>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_actual[$texto] = $counter_actual;
											//echo 'ACTUAL: '.$texto.'-'.$counter_actual;
										}
										if ($counter_anterior>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_anterior[$texto] = $counter_anterior;
											//echo 'ANTERIOR: '.$texto.'-'.$counter_anterior;
										}
									
									}
									arsort($myhashmap_actual);	
									$max_results=0;
									foreach (array_keys($myhashmap_actual) as $key){
										//if($max_results>9){
										//	break;
										//}
										$diferencia=0;
										$valor_actual=0;
										$valor_anterior=0;
										$valor_actual=$myhashmap_actual[$key];
										$valor_anterior=$myhashmap_anterior[$key];
										if($myhashmap_anterior[$key]>0){
										
											//echo '----------'.$key.'----------';
											//echo 'VALOR_ACTUAL: '.$valor_actual;
										    //echo '---VALOR_ANTERIOR: '.$valor_anterior;
											$diferencia=($valor_actual-$valor_anterior);
											if ($diferencia >= 0){
												$myhashmap_diferencia_ABS[$key]=$diferencia;											
												$diferencia=$diferencia/$valor_anterior;
												//echo 'diferencia2:'.$diferencia;
												$myhashmap_diferencia[$key]=$diferencia*100;
											}
										
										}else{
											$myhashmap_diferencia[$key]=100;
											$myhashmap_diferencia_ABS[$key]=$valor_actual;
										}
						
									}
									arsort($myhashmap_diferencia_ABS);
									$_string_min='';
									$max_results=0;
								
									foreach (array_keys($myhashmap_diferencia_ABS) as $key){
										//echo '----------'.$key.'----------';
										if($max_results>9){
											break;
										}
										$myhashmap_anterior_filtrado[$key]=$myhashmap_anterior[$key];
										$myhashmap_actual_filtrado[$key]=$myhashmap_actual[$key];
										$max_results=$max_results+1;
										if($myhashmap_diferencia[$key]<0){
									
											$_string_min=$_string_min.'<i class="fa fa fa-sort-down" style="font-size:30px;color:#EC6464;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
												
										}else {
										
											$_string_min=$_string_min.'<i class="fa fa-sort-asc" style="font-size:30px;color:#98DF58;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
												
										}
									}
									
									arsort($myhashmap_actual_filtrado);
								
									$hmTotal['anterior']=$myhashmap_anterior_filtrado;
									$hmTotal['actual']=$myhashmap_actual_filtrado;
									$hmTotal['diferencia']=$myhashmap_diferencia_ABS;
									return $hmTotal;
										
									//return $_string_min;
									//return $myhashmap_diferencia_ABS;

	?><?php
}

function evolucion_conc_seguimiento_mes_detalle_shortcode(){
	?>

		<?php
		$user_id = get_current_user_id();
 									
 									$categories = ainia_get_user_terms($user_id, 'concepto_seguimiento',array(fields=>'all'));
 								
 									$myhashmap_actual = array();
 									$myhashmap_previo = array();
 									$myhashmap_diferencia = array();
 									$myhashmap_diferencia_ABS = array();
 									
 									$myhashmap_anterior_filtrado = array();
 									$myhashmap_actual_filtrado = array();
 									$hmTotal = array();
 									
									foreach ( $categories as $category ) {
										
										$args_semana_actual = array(
												'concepto_seguimiento'    => $category->slug,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-30 days'))) 
										);
										$args_semana_anterior = array(
													'concepto_seguimiento'    => $category->slug,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
												'posts_per_page'=>-1,
												'date_query'	   => array('before' => date('Y-m-d', strtotime('-30 days')),'after' => date('Y-m-d', strtotime('-60 days')))
										);
										
										$post_by_cat_semana_actual = get_posts($args_semana_actual);
										$post_by_cat_semana_anterior = get_posts($args_semana_anterior);
										$counter_actual = 0;
										$counter_anterior = 0;
										foreach($post_by_cat_semana_actual as $p)
										{
											$counter_actual++;
										}
										foreach($post_by_cat_semana_anterior as $p)
										{
											//echo $p->title;
											$counter_anterior++;
										}
										if ($counter_actual>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_actual[$texto] = $counter_actual;
											//echo 'ACTUAL: '.$texto.'-'.$counter_actual;
										}
										if ($counter_anterior>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_anterior[$texto] = $counter_anterior;
											//echo 'ANTERIOR: '.$texto.'-'.$counter_anterior;
										}
											
										}
										arsort($myhashmap_actual);
										$max_results=0;
										foreach (array_keys($myhashmap_actual) as $key){
											//if($max_results>9){
											//	break;
											//}
											$diferencia=0;
											$valor_actual=0;
											$valor_anterior=0;
											$valor_actual=$myhashmap_actual[$key];
											$valor_anterior=$myhashmap_anterior[$key];
											if($myhashmap_anterior[$key]>0){
										
												//echo '----------'.$key.'----------';
												//echo 'VALOR_ACTUAL: '.$valor_actual;
												//echo '---VALOR_ANTERIOR: '.$valor_anterior;
												$diferencia=($valor_actual-$valor_anterior);
												if ($diferencia >= 0){
													$myhashmap_diferencia_ABS[$key]=$diferencia;
													$diferencia=$diferencia/$valor_anterior;
													//echo 'diferencia2:'.$diferencia;
													$myhashmap_diferencia[$key]=$diferencia*100;
												}
										
											}else{
												$myhashmap_diferencia[$key]=100;
												$myhashmap_diferencia_ABS[$key]=$valor_actual;
											}
											
										}
										arsort($myhashmap_diferencia_ABS);
										$_string_min='';
											
										$max_results=0;
										foreach (array_keys($myhashmap_diferencia_ABS) as $key){
											//echo '----------'.$key.'----------';
											if($max_results>9){
												break;
											}
											$myhashmap_anterior_filtrado[$key]=$myhashmap_anterior[$key];
											$myhashmap_actual_filtrado[$key]=$myhashmap_actual[$key];
											$max_results=$max_results+1;
											if($myhashmap_diferencia[$key]<0){
													
												$_string_min=$_string_min.'<i class="fa fa fa-sort-down" style="font-size:30px;color:#EC6464;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}else {
										
												$_string_min=$_string_min.'<i class="fa fa-sort-asc" style="font-size:30px;color:#98DF58;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}
										}
											
										arsort($myhashmap_actual_filtrado);
										
										$hmTotal['anterior']=$myhashmap_anterior_filtrado;
										$hmTotal['actual']=$myhashmap_actual_filtrado;
										$hmTotal['diferencia']=$myhashmap_diferencia_ABS;
										return $hmTotal;

	?><?php
}
function evolucion_conc_seguimiento_trimestre_detalle_shortcode(){
	?>
	
		<?php
 		$user_id = get_current_user_id();							
		$categories = ainia_get_user_terms($user_id, 'concepto_seguimiento',array(fields=>'all'));
		
 								
 									$myhashmap_actual = array();
 									$myhashmap_previo = array();
 									$myhashmap_diferencia = array();
 									$myhashmap_diferencia_ABS = array();
 									
 									$myhashmap_anterior_filtrado = array();
 									$myhashmap_actual_filtrado = array();
 									$hmTotal = array();
 									
									foreach ( $categories as $category ) {
										
										$args_semana_actual = array(
												'concepto_seguimiento'    => $category->slug,
											'post_type'        => 'post',
											'post_status'      => 'publish',
											'suppress_filters' => true,
												'posts_per_page'=>-1,
											'date_query'	   => array('after' => date('Y-m-d', strtotime('-90 days'))) 
										);
										$args_semana_anterior = array(
												'concepto_seguimiento'    => $category->slug,
												'post_type'        => 'post',
												'post_status'      => 'publish',
												'suppress_filters' => true,
												'posts_per_page'=>-1,
												'date_query'	   => array('before' => date('Y-m-d', strtotime('-90 days')),'after' => date('Y-m-d', strtotime('-180 days')))
										);
										
										$post_by_cat_semana_actual = get_posts($args_semana_actual);
										$post_by_cat_semana_anterior = get_posts($args_semana_anterior);
										$counter_actual = 0;
										$counter_anterior = 0;
										foreach($post_by_cat_semana_actual as $p)
										{
											$counter_actual++;
										}
										foreach($post_by_cat_semana_anterior as $p)
										{
											//echo $p->title;
											$counter_anterior++;
										}
										if ($counter_actual>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_actual[$texto] = $counter_actual;
											//echo 'ACTUAL: '.$texto.'-'.$counter_actual;
										}
										if ($counter_anterior>0){
											$texto=str_replace("'","\'",$category->name);
											$myhashmap_anterior[$texto] = $counter_anterior;
											//echo 'ANTERIOR: '.$texto.'-'.$counter_anterior;
										}
											
										}
										arsort($myhashmap_actual);
										$max_results=0;
										foreach (array_keys($myhashmap_actual) as $key){
											//if($max_results>9){
											//	break;
											//}
											$diferencia=0;
											$valor_actual=0;
											$valor_anterior=0;
											$valor_actual=$myhashmap_actual[$key];
											$valor_anterior=$myhashmap_anterior[$key];
											if($myhashmap_anterior[$key]>0){
										
											
												$diferencia=($valor_actual-$valor_anterior);
												if ($diferencia >= 0){
													//echo '----------'.$key.'----------';
													//echo 'VALOR_ACTUAL: '.$valor_actual;
													//echo '---VALOR_ANTERIOR: '.$valor_anterior;
													$myhashmap_diferencia_ABS[$key]=$diferencia;
													$diferencia=$diferencia/$valor_anterior;
													//echo 'diferencia2:'.$diferencia;
													$myhashmap_diferencia[$key]=$diferencia*100;
												}
										
											}else{
												$myhashmap_diferencia[$key]=100;
												$myhashmap_diferencia_ABS[$key]=$valor_actual;
											}
											
										}
										arsort($myhashmap_diferencia_ABS);
										$_string_min='';
										$max_results=0;
										
										foreach (array_keys($myhashmap_diferencia_ABS) as $key){
											//echo '----------'.$key.'----------';
											if($max_results>9){
												break;
											}
											//echo '**** '.$key.'----------';
											//echo '******** VALOR_ACTUAL: '.$valor_actual;
											//echo '***VALOR_ANTERIOR: '.$valor_anterior;
											$myhashmap_anterior_filtrado[$key]=$myhashmap_anterior[$key];
											$myhashmap_actual_filtrado[$key]=$myhashmap_actual[$key];
											$max_results=$max_results+1;
											if($myhashmap_diferencia[$key]<0){
													
												$_string_min=$_string_min.'<i class="fa fa fa-sort-down" style="font-size:30px;color:#EC6464;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}else {
										
												$_string_min=$_string_min.'<i class="fa fa-sort-asc" style="font-size:30px;color:#98DF58;padding:15px;margin-left:10px;"></i> '.$key.' <h2 style="display:inline;margin-left:10px;margin-right:10px;">'.$myhashmap_diferencia_ABS[$key].'</h2>('.$myhashmap_diferencia[$key].'% )<br> ';
										
											}
										}
											
										arsort($myhashmap_actual_filtrado);
										
										$hmTotal['anterior']=$myhashmap_anterior_filtrado;
										$hmTotal['actual']=$myhashmap_actual_filtrado;
										$hmTotal['diferencia']=$myhashmap_diferencia_ABS;
										return $hmTotal;

	?><?php
}

/**
 * Función que compara el número de entradas por concepto en dos periodos consecutivos.
 * @param entero $periodo (7,30,90.. para comparar semana, mes o trimestre)
 * @return Array result term_id, term_name and number of entries per term for current period and previous and difference>
 */
function query_tendencia ($periodo,$user_categories){
	//echo 'query_tendencia';
	global $wpdb;
	$periodo2 = $periodo*2;
	$fecha_inicio= date('Y-m-d', strtotime('-'.$periodo.' days'));
	$fecha_fin= date('Y-m-d', strtotime('-'.$periodo2.' days'));
;
	
	$query='select * from 
 (select actual.term_id id_actual,anterior.term_id id_anterior,actual.name name_actual,anterior.name name_anterior,COALESCE(actual.cuenta,0) cuenta_actual,COALESCE(anterior.cuenta,0) cuenta_anterior
, (coalesce(actual.cuenta,0)-coalesce(anterior.cuenta,0))as diferencia 
from
 (select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta 
	from ( SELECT * from wp_posts p WHERE p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') GROUP BY cat_usu.name) 
actual 
LEFT JOIN 
(select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta 
	from ( SELECT * from wp_posts p WHERE p.post_date < %s and p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') GROUP BY cat_usu.name) 
anterior on anterior.term_id=actual.term_id) TABLA1
UNION
 (select actual.term_id id_actual,anterior.term_id id_anterior,actual.name name_actual,anterior.name name_anterior,COALESCE(actual.cuenta,0) cuenta_actual,COALESCE(anterior.cuenta,0) cuenta_anterior
, (coalesce(actual.cuenta,0)-coalesce(anterior.cuenta,0))as diferencia 
from
 (select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta 
	from ( SELECT * from wp_posts p WHERE p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') GROUP BY cat_usu.name) 
actual 
RIGHT JOIN 
(select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta 
	from ( SELECT * from wp_posts p WHERE p.post_date < %s and p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') GROUP BY cat_usu.name) 
anterior on anterior.term_id=actual.term_id) 

 order by diferencia DESC';
	
	
	
	//echo $query;
	try {
		$results=$wpdb->get_results( $wpdb->prepare( $query,$fecha_inicio,$fecha_inicio, $fecha_fin,$fecha_inicio,$fecha_inicio, $fecha_fin));
		if($periodo<10){
			//echo '///'.$periodo.'---'.$wpdb->last_query.'///';
		}
	} catch (Exception $e) {
		echo $e;
	}

	return $results;
}


/**
 * Función que compara el número de entradas por concepto en dos periodos consecutivos.
 * @param entero $periodo (7,30,90.. para comparar semana, mes o trimestre)
 * @return Array result term_id, term_name and number of entries per term for current period and previous and difference>
 */
function query_tendencia_con_limites_alertas ($periodo,$user_categories,$tipo_alerta){
	//echo 'query_tendencia';
	global $wpdb;
	$periodo2 = $periodo*2;
	$fecha_inicio= date('Y-m-d', strtotime('-'.$periodo.' days'));
	$fecha_fin= date('Y-m-d', strtotime('-'.$periodo2.' days'));

	$query='select * from 
 (select actual.term_id id_actual,anterior.term_id id_anterior,actual.name name_actual,anterior.name name_anterior,COALESCE(actual.cuenta,0) cuenta_actual,COALESCE(anterior.cuenta,0) cuenta_anterior
, (coalesce(actual.cuenta,0)-coalesce(anterior.cuenta,0))as diferencia, anterior.umbral_ascenso, anterior.umbral_descenso 
from
 (select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta,meta_limite1.meta_value as umbral_ascenso,meta_limite2.meta_value as umbral_descenso
	from ( SELECT * from wp_posts p WHERE p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') 
STRAIGHT_JOIN wp_posts alertas on alertas.post_status = \'publish\' and alertas.post_type=\''.$tipo_alerta.'\' 
STRAIGHT_JOIN wp_term_relationships rel_cat_alerta ON alertas.ID = rel_cat_alerta.object_id and rel_cat_alerta.term_taxonomy_id=rel_cat_usu.term_taxonomy_id
STRAIGHT_JOIN wp_postmeta meta on meta.post_id=alertas.ID and meta.meta_key=\'activa\'  and meta.meta_value>0
STRAIGHT_JOIN wp_postmeta meta_limite1 on meta_limite1.post_id=alertas.ID and meta_limite1.meta_key=\'umbral_ascenso\' 
STRAIGHT_JOIN wp_postmeta meta_limite2 on meta_limite2.post_id=alertas.ID and meta_limite2.meta_key=\'umbral_descenso\'  GROUP BY cat_usu.name) 
actual 
LEFT JOIN 
(select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta,meta_limite1.meta_value as umbral_ascenso,meta_limite2.meta_value as umbral_descenso 
	from ( SELECT * from wp_posts p WHERE p.post_date < %s and p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') 
STRAIGHT_JOIN wp_posts alertas on alertas.post_status = \'publish\' and alertas.post_type=\''.$tipo_alerta.'\' 
STRAIGHT_JOIN wp_term_relationships rel_cat_alerta ON alertas.ID = rel_cat_alerta.object_id and rel_cat_alerta.term_taxonomy_id=rel_cat_usu.term_taxonomy_id
STRAIGHT_JOIN wp_postmeta meta on meta.post_id=alertas.ID and meta.meta_key=\'activa\'  and meta.meta_value>0
STRAIGHT_JOIN wp_postmeta meta_limite1 on meta_limite1.post_id=alertas.ID and meta_limite1.meta_key=\'umbral_ascenso\' 
STRAIGHT_JOIN wp_postmeta meta_limite2 on meta_limite2.post_id=alertas.ID and meta_limite2.meta_key=\'umbral_descenso\'  GROUP BY cat_usu.name) 
anterior on anterior.term_id=actual.term_id) TABLA1
UNION
 (select actual.term_id id_actual,anterior.term_id id_anterior,actual.name name_actual,anterior.name name_anterior,COALESCE(actual.cuenta,0) cuenta_actual,COALESCE(anterior.cuenta,0) cuenta_anterior
, (coalesce(actual.cuenta,0)-coalesce(anterior.cuenta,0))as diferencia, anterior.umbral_ascenso, anterior.umbral_descenso 
from
 (select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta,meta_limite1.meta_value as umbral_ascenso,meta_limite2.meta_value as umbral_descenso 
	from ( SELECT * from wp_posts p WHERE p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') 
STRAIGHT_JOIN wp_posts alertas on alertas.post_status = \'publish\' and alertas.post_type=\''.$tipo_alerta.'\' 
STRAIGHT_JOIN wp_term_relationships rel_cat_alerta ON alertas.ID = rel_cat_alerta.object_id and rel_cat_alerta.term_taxonomy_id=rel_cat_usu.term_taxonomy_id
STRAIGHT_JOIN wp_postmeta meta on meta.post_id=alertas.ID and meta.meta_key=\'activa\'  and meta.meta_value>0
STRAIGHT_JOIN wp_postmeta meta_limite1 on meta_limite1.post_id=alertas.ID and meta_limite1.meta_key=\'umbral_ascenso\' 
STRAIGHT_JOIN wp_postmeta meta_limite2 on meta_limite2.post_id=alertas.ID and meta_limite2.meta_key=\'umbral_descenso\'  GROUP BY cat_usu.name) 
actual 
RIGHT JOIN 
(select cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as cuenta,meta_limite1.meta_value as umbral_ascenso,meta_limite2.meta_value as umbral_descenso 
	from ( SELECT * from wp_posts p WHERE p.post_date < %s and p.post_date > %s AND p.post_type = \'post\' AND p.post_status = \'publish\' ) posts 
STRAIGHT_JOIN wp_term_relationships rel_cat_usu ON posts.ID = rel_cat_usu.object_id 
STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id 
STRAIGHT_JOIN wp_terms cat_usu ON tax_cat_usu.term_id=cat_usu.term_id and cat_usu.term_id in('.implode(",",$user_categories).') 
STRAIGHT_JOIN wp_posts alertas on alertas.post_status = \'publish\' and alertas.post_type=\''.$tipo_alerta.'\' 
STRAIGHT_JOIN wp_term_relationships rel_cat_alerta ON alertas.ID = rel_cat_alerta.object_id and rel_cat_alerta.term_taxonomy_id=rel_cat_usu.term_taxonomy_id
STRAIGHT_JOIN wp_postmeta meta on meta.post_id=alertas.ID and meta.meta_key=\'activa\'  and meta.meta_value>0
STRAIGHT_JOIN wp_postmeta meta_limite1 on meta_limite1.post_id=alertas.ID and meta_limite1.meta_key=\'umbral_ascenso\' 
STRAIGHT_JOIN wp_postmeta meta_limite2 on meta_limite2.post_id=alertas.ID and meta_limite2.meta_key=\'umbral_descenso\'  GROUP BY cat_usu.name) 
anterior on anterior.term_id=actual.term_id) 

 order by diferencia DESC';
	
	
	
	//echo $query;
	try {
		$results=$wpdb->get_results( $wpdb->prepare( $query,$fecha_inicio,$fecha_inicio, $fecha_fin,$fecha_inicio,$fecha_inicio, $fecha_fin));
		if($periodo<10){
			//echo '///'.$periodo.'---'.$wpdb->last_query.'///';
		}
	} catch (Exception $e) {
		echo $e;
	}

	return $results;
}

function genera_query_tendencia_anual(){
		global $wpdb;
	
	$doce_meses=array('mes1','mes2','mes3','mes4','mes5','mes6','mes7','mes8','mes9','mes10','mes11','mes12');
	$fecha_inicio_anyo=date('Y-m-d', strtotime("first day of -12 month"));
	$fecha_fin_anyo=date('Y-m-d', strtotime("last day of -1 month"));
	
	$select= 'select anyo.*,coalesce('.$doce_meses[0].',0) as '.$doce_meses[0].',coalesce('. $doce_meses[1].',0) as '.$doce_meses[1].',coalesce('. $doce_meses[2].',0) as '.$doce_meses[2].',coalesce('.$doce_meses[3].',0) as '.$doce_meses[3].
	',coalesce('. $doce_meses[4].',0) as '.$doce_meses[4].',coalesce('.$doce_meses[5].',0) as '.$doce_meses[5].',coalesce('.
	$doce_meses[6].',0) as '.$doce_meses[6].',coalesce('.$doce_meses[7].',0) as '.$doce_meses[7].',coalesce('. $doce_meses[8].',0) as '.$doce_meses[8].',coalesce('.
	$doce_meses[9].',0) as '.$doce_meses[9].',coalesce('. $doce_meses[10].',0) as '.$doce_meses[10].',coalesce('. $doce_meses[11].',0) as '.$doce_meses[11].' from ';
	 
	 $query_final = $query_final.$select;
	 $query_periodo='(select cat_usu.slug,cat_usu.name,cat_usu.term_id,count(distinct posts.ID) as #PERIODO#
from (
	SELECT 
		*
	from wp_posts p
	WHERE 	p.post_date between \'#FECHA_INI#\' and \'#FECHA_FIN#\'
			AND p.post_type = \'post\'
			AND p.post_status = \'publish\') posts
	STRAIGHT_JOIN wp_term_relationships rel_cat_usu
											ON (posts.ID = rel_cat_usu.object_id)
											STRAIGHT_JOIN wp_term_taxonomy tax_cat_usu ON (rel_cat_usu.term_taxonomy_id=tax_cat_usu.term_taxonomy_id)
											STRAIGHT_JOIN wp_terms cat_usu ON (tax_cat_usu.term_id=cat_usu.term_id) and cat_usu.term_id in(1,494,526,528,535,536,538,554,556,558,559)
GROUP BY cat_usu.name) #PERIODO# ';
	 
	 $query_final=$query_final.str_replace('#PERIODO#','anyo',$query_periodo);
	 $query_final=str_replace('#FECHA_INI#',$fecha_inicio_anyo,$query_final);
	 $query_final=str_replace('#FECHA_FIN#',$fecha_fin_anyo,$query_final);
	 $mes_inicial=-12;
	 $inicio=0;
	 foreach ($doce_meses as $mes){
	 	// str_replace("world","Peter","Hello world!");
	 	
	 	$query_final = $query_final." left join ";
	 	
	 	$fecha_inicio=date('Y-m-d', strtotime("first day of ".$mes_inicial." month"));
		$fecha_fin=date('Y-m-d', strtotime("last day of  ".$mes_inicial." month"));
		
	 	$query_periodo_temp=str_replace('#PERIODO#',$mes,$query_periodo);
	 	$query_periodo_temp=str_replace('#FECHA_INI#',$fecha_inicio,$query_periodo_temp);
	 	$query_periodo_temp=str_replace('#FECHA_FIN#',$fecha_fin,$query_periodo_temp);
	 	 $query_final = $query_final.$query_periodo_temp." on anyo.term_id=".$mes.".term_id  ";
	 	
	 	$mes_inicial=$mes_inicial+1;
	 	
	 }
	 $query_final = $query_final." ORDER BY anyo DESC";
	// echo 'query'.$query_final;
	 
	 	$results=$wpdb->get_results( $wpdb->prepare( $query_final,$fecha_fin_anyo));
	 	
	 	//echo $wpdb->last_query;
	 	return $results;
		
}




