From 0ccfee8cc9419f097ea382d1bbb7d93926677984 Mon Sep 17 00:00:00 2001 From: ozhozh Date: Thu, 16 Aug 2012 21:32:34 +0000 Subject: [PATCH] Stat pages now use the Google Visualization API. Fixes issue 556. Much smarter DB queries. Fixes issue 376. Maybe :) git-svn-id: http://yourls.googlecode.com/svn/trunk@732 12232710-3e20-11de-b438-597f59cd7555 --- includes/functions-html.php | 20 ++- includes/functions-infos.php | 231 +++++++++++++++++--------------- js/infos.js | 1 - yourls-infos.php | 251 ++++++++++++++++------------------- 4 files changed, 250 insertions(+), 253 deletions(-) diff --git a/includes/functions-html.php b/includes/functions-html.php index ab0c7b5..b99437b 100644 --- a/includes/functions-html.php +++ b/includes/functions-html.php @@ -18,12 +18,12 @@ function yourls_html_head( $context = 'index', $title = '' ) { yourls_do_action( 'pre_html_head', $context, $title ); // All components to false, except when specified true - $share = $insert = $tablesorter = $tabs = $cal = false; + $share = $insert = $tablesorter = $tabs = $cal = $charts = false; // Load components as needed switch ( $context ) { case 'infos': - $share = $tabs = true; + $share = $tabs = $charts = true; break; case 'bookmark': @@ -83,26 +83,32 @@ function yourls_html_head( $context = 'index', $title = '' ) { - + - + - + - + - + + + + + - -
-MAP; + $options = array( + 'backgroundColor' => "white", + 'colorAxis' => "{colors:['A8D0ED','99C4E4','8AB8DB','7BACD2','6BA1C9','5C95C0','4D89B7','3E7DAE','2E72A5','1F669C']}", + 'width' => "550", + 'height' => "340", + 'theme' => 'maximized' + ); + $options = yourls_apply_filter( 'stats_countries_map_options', $options ); + + $map = yourls_google_viz_code( 'GeoChart', $data, $options, $id ); - echo yourls_apply_filter( 'stats_countries_dynamic', $dynamic, $countries ); + echo yourls_apply_filter( 'stats_countries_map', $map, $countries, $options, $id ); } // Echoes an image tag of Google Charts pie from sorted array of 'data' => 'value' (sort by DESC). Optional $limit = (integer) limit list of X first countries, sorted by most visits -function yourls_stats_pie( $data, $limit = 10, $size = '340x220', $colors = 'C7E7FF,1F669C' ) { - yourls_do_action( 'stats_pie' ); +function yourls_stats_pie( $data, $limit = 10, $size = '340x220', $id = null ) { + + yourls_do_action( 'pre_stats_pie' ); + + // if $id is null then assign a random string + if( $id === null ) + $id = uniqid ( 'yourls_stats_pie_' ); // Trim array: $limit first item + the sum of all others if ( count( $data ) > $limit ) { @@ -85,20 +53,25 @@ function yourls_stats_pie( $data, $limit = 10, $size = '340x220', $colors = 'C7E // Scale items $_data = yourls_scale_data( $data ); - // Hmmm, pie - $pie = array( - 'cht' => 'p', - 'chs' => $size, - 'chd' => 't:'.( join(',' , $_data ) ), - 'chco'=> $colors, - 'chl' => join('|' , array_keys( $data ) ) - ); - $pie_src = 'http://chart.apis.google.com/chart?' . http_build_query( $pie ); - //$pie_src = yourls_match_current_protocol( $pie_src, 'http://chart.apis.', 'https://www.' ); - list( $size_x, $size_y ) = explode( 'x', $size ); + list($width, $height) = explode('x', $size); - $pie = ""; - echo yourls_apply_filter( 'stats_pie', $pie, $data, $limit, $size, $colors ); + $options = array( + 'theme' => 'maximized', + 'width' => $width, + 'height' => $height, + 'colors' => "['A8D0ED','99C4E4','8AB8DB','7BACD2','6BA1C9','5C95C0','4D89B7','3E7DAE','2E72A5','1F669C']", + 'legend' => 'none', + 'chartArea' => '{top: "5%", height: "90%"}', + 'pieSliceText' => 'label', + ); + $options = yourls_apply_filter( 'stats_pie_options', $options ); + + $script_data = array_merge( array( 'Country' => 'Value' ), $_data); + $script_data = yourls_google_array_to_data_table( $script_data ); + + $pie = yourls_google_viz_code( 'PieChart', $script_data, $options, $id ); + + echo yourls_apply_filter( 'stats_pie', $pie, $data, $limit, $size, $options, $id ); } // Build a list of all daily values between d1/m1/y1 to d2/m2/y2. @@ -110,7 +83,7 @@ function yourls_build_list_of_days( $dates ) { 29 => 15, 30 => 5, ), - '09' => array ( + '09' => array ( '02' => 3, '03' => 5, '04' => 2, @@ -153,7 +126,9 @@ function yourls_build_list_of_days( $dates ) { $current_last_day = ( $year == $last_year && $month == $last_month ? $last_day : yourls_days_in_month($month, $year) ); for ( $day = $current_first_day; $day <= $current_last_day; $day++ ) { $day = sprintf('%02d', $day); - $list_of_days["$_year-$_month-$day"] = isset( $dates[$_year][$_month][$day] ) ? $dates[$_year][$_month][$day] : 0; + //$key = "$_year-$_month-$day"; + $key = date( 'M d, Y', mktime( 0, 0, 0, $_month, $day, $_year ) ); + $list_of_days[ $key ] = isset( $dates[$_year][$_month][$day] ) ? $dates[$_year][$_month][$day] : 0; } } } @@ -165,49 +140,42 @@ function yourls_build_list_of_days( $dates ) { ); } -// Echoes an image tag of Google Charts line graph from array of values (eg 'number of clicks'). $legend1_list & legend2_list are values used for the 2 x-axis labels -function yourls_stats_line( $values, $legend1_list, $legend2_list ) { - yourls_do_action( 'stats_line' ); +// Echoes an image tag of Google Charts line graph from array of values (eg 'number of clicks'). +// $legend1_list & legend2_list are values used for the 2 x-axis labels. $id is an HTML/JS id +function yourls_stats_line( $values, $id = null ) { + yourls_do_action( 'pre_stats_line' ); + + // if $id is null then assign a random string + if( $id === null ) + $id = uniqid ( 'yourls_stats_line_' ); + // If we have only 1 day of data, prepend a fake day with 0 hits for a prettier graph if ( count( $values ) == 1 ) array_unshift( $values, 0 ); + // Keep only a subset of values to keep graph smooth $values = yourls_array_granularity( $values, 30 ); - // If x-axis labels have only 1 value, double it for a nicer graph - if( count( $legend1_list ) == 1 ) - $legend1_list[] = current( $legend1_list ); - if( count( $legend2_list ) == 1 ) - $legend2_list[] = current( $legend2_list ); - - // Make the chart - $legend1 = join('|', $legend1_list ); - $legend2 = join('|', $legend2_list ); - $max = max( $values ); - if ( $max >= 4 ) { - $label_clicks = '0|'.intval( $max / 4 ).'|'.intval( $max / 2 ).'|'.intval( $max / 1.5 ).'|'.$max; - } else { - $label_clicks = array(); - for ($i = 0; $i <= $max; $i++) { - $label_clicks[] = $i; - } - $label_clicks = join( '|', $label_clicks ); - } - $line = array( - 'cht' => 'lc', - 'chs' => '440x220', - 'chxt'=> 'x,x,y', - 'chd' => 't:'.( join(',' , $values ) ), - 'chds' => '0,'.$max, - 'chm' => 'B,E3F3FF,0,0,0|o,2a85b3,0,-1,6|o,FFFFFF,0,-1,4', - 'chco' => '2a85b3', - 'chxl'=> '0:|'. $legend1 .'|1:|'. $legend2 .'|2:|'. $label_clicks + $data = array_merge( array( 'Time' => 'Hits' ), $values ); + $data = yourls_google_array_to_data_table( $data ); + + $options = array( + "legend" => "none", + "pointSize" => "3", + "theme" => "maximized", + "curveType" => "function", + "width" => 430, + "height" => 220, + "hAxis" => "{minTextSpacing: 80, maxTextLines: 1, maxAlternation: 1}", + "vAxis" => "{minValue: -0.5, format: '#'}", + "colors" => "['#2a85b3']", ); - $line_src = 'http://chart.apis.google.com/chart?' . http_build_query( $line ); - //$line_src = yourls_match_current_protocol( $line_src, 'http://chart.apis.', 'https://www.' ); + $options = yourls_apply_filter( 'stats_line_options', $options ); + + $lineChart = yourls_google_viz_code( 'LineChart', $data, $options, $id ); - echo yourls_apply_filter( 'stats_line', "", $values, $legend1_list, $legend2_list ); + echo yourls_apply_filter( 'stats_line', $lineChart, $values, $options, $id ); } // Return the number of days in a month. From php.net, used if PHP built without calendar functions @@ -280,3 +248,52 @@ function yourls_array_granularity( $array, $grain = 100, $preserve_max = true ) } return $array; } + +// Transform data array to data table for Google API +function yourls_google_array_to_data_table( $data ){ + $str = "var data = google.visualization.arrayToDataTable([\n"; + foreach( $data as $label => $values ){ + if( !is_array( $values ) ) { + $values = array( $values ); + } + $str .= "\t['$label',"; + foreach( $values as $value ){ + if( !is_numeric( $value ) && strpos( $value, '[' ) !== 0 && strpos( $value, '{' ) !== 0 ) { + $value = "'$value'"; + } + $str .= "$value"; + } + $str .= "],\n"; + } + $str = substr( $str, 0, -2 ) . "\n"; // remove the trailing comma/return, reappend the return + $str .= "]);\n"; // wrap it up + return $str; +} + +// Return javascript code that will display the Google Chart +function yourls_google_viz_code( $graph_type, $data, $options, $id ) { + $function_name = 'yourls_graph' . $id; + $code = "\n\n"; + $code .= "
\n"; + + return $code; +} + diff --git a/js/infos.js b/js/infos.js index 28e2d27..d8ca104 100644 --- a/js/infos.js +++ b/js/infos.js @@ -10,7 +10,6 @@ $(document).ready(function(){ $('div.'+divs).css('display', 'none'); $('div#'+target).css('display', 'block'); $('ul.stat_'+divs+' li a').removeClass('selected'); - //$(this).addClass('selected').css('outline', 'none').blur(); $('ul.stat_'+divs+' li a[href="#'+target+'"]').addClass('selected').css('outline', 'none').blur(); return false; }); diff --git a/yourls-infos.php b/yourls-infos.php index ea8e54d..71af0ea 100644 --- a/yourls-infos.php +++ b/yourls-infos.php @@ -36,25 +36,10 @@ yourls_do_action( 'pre_yourls_infos', $keyword ); + if( yourls_do_log_redirect() ) { - // Duplicate keywords, if applicable - $keyword_list = yourls_get_duplicate_keywords( $longurl ); - - // Fetch all information from the table log $table = YOURLS_DB_TABLE_LOG; - - if( $aggregate ) { - // Fetch information for all keywords pointing to $longurl - $keywords = join( "', '", $keyword_list ); - $query = "SELECT `shorturl`, `click_time`, `referrer`, `user_agent`, `country_code` FROM `$table` WHERE `shorturl` IN ( '$keywords' );"; - } else { - // Fetch information for current keyword only - $query = "SELECT `click_time`, `referrer`, `user_agent`, `country_code` FROM `$table` WHERE `shorturl` = '$keyword'"; - } - - $hits = $ydb->get_results( yourls_apply_filter( 'stat_query_all', $query ) ); - $referrers = array(); $direct = $notdirect = 0; $countries = array(); @@ -63,60 +48,88 @@ $list_of_months = array(); $list_of_years = array(); $last_24h = array(); - + + // Define keyword query range : either a single keyword or a list of keywords + if( $aggregate ) { + $keyword_list = yourls_get_duplicate_keywords( $longurl ); + $keyword_range = "IN ( '" . join( "', '", $keyword_list ) . "' )"; // IN ( 'blah', 'bleh', 'bloh' ) + } else { + $keyword_range = "= '$keyword'"; + } + + + // *** Referrers *** + $query = "SELECT `referrer`, COUNT(*) AS `count` FROM `$table` WHERE `shorturl` $keyword_range GROUP BY `referrer`;"; + $rows = $ydb->get_results( yourls_apply_filter( 'stat_query_referrer', $query ) ); + // Loop through all results and build list of referrers, countries and hits per day - foreach( (array)$hits as $hit ) { - extract( (array)$hit ); - - if ( isset( $country_code ) && $country_code ) { - if( !array_key_exists( $country_code, $countries ) ) - $countries[$country_code] = 0; - $countries[$country_code]++; - } - - if( isset( $referrer ) ) { - if ( $referrer == 'direct' ) { - $direct++; - } else { - $notdirect++; - $host = yourls_get_domain( $referrer ); - if( !array_key_exists( $host, $referrers ) ) - $referrers[$host] = array( ); - if( !array_key_exists( $referrer, $referrers[$host] ) ) - $referrers[$host][$referrer] = 0; - $referrers[$host][$referrer]++; - } + foreach( (array)$rows as $row ) { + if ( $row->referrer == 'direct' ) { + $direct = $row->count; + continue; } - if( isset( $click_time ) ) { - $now = intval( date('U') ); - - preg_match('/(\d+)-(\d+)-(\d+)\s(\d+):(\d+):(\d+)/', $click_time, $matches); - list( $temp, $year, $month, $day, $hour, $min, $sec ) = $matches; - unset( $matches ); - - // Build array of $dates[$year][$month][$day] = number of clicks - if( !array_key_exists( $year, $dates ) ) - $dates[$year] = array(); - if( !array_key_exists( $month, $dates[$year] ) ) - $dates[$year][$month] = array(); - if( !array_key_exists( $day, $dates[$year][$month] ) ) - $dates[$year][$month][$day] = 0; - $dates[$year][$month][$day]++; - - // Build array of last 24 hours $last_24h[$hour] = number of click - $then = strtotime( $click_time); - if( ( $now >= $then ) && ( ( $now - $then ) < ( 24 * 60 * 60 ) ) ) { - $year = sprintf( "%02d", substr($year, -2) ); // 2009 -> 09 - $diff = $now - strtotime( $click_time); - if( !array_key_exists( "$year-$month-$day-$hour", $last_24h ) ) - $last_24h["$year-$month-$day-$hour"] = 0; - $last_24h["$year-$month-$day-$hour"]++; - } + $host = yourls_get_domain( $row->referrer ); + if( !array_key_exists( $host, $referrers ) ) + $referrers[$host] = array( ); + if( !array_key_exists( $row->referrer, $referrers[$host] ) ) { + $referrers[$host][$row->referrer] = $row->count; + $notdirect += $row->count; + } else { + $referrers[$host][$row->referrer] += $row->count; + $notdirect += $row->count; } } + + // Sort referrers. $referrer_sort is a array of most frequent domains + arsort( $referrers ); + $referrer_sort = array(); + $number_of_sites = count( array_keys( $referrers ) ); + foreach( $referrers as $site => $urls ) { + if( count($urls) > 1 || $number_of_sites == 1 ) + $referrer_sort[$site] = array_sum( $urls ); + } + arsort($referrer_sort); + + // *** Countries *** + $query = "SELECT `country_code`, COUNT(*) AS `count` FROM `$table` WHERE `shorturl` $keyword_range GROUP BY `country_code`;"; + $rows = $ydb->get_results( yourls_apply_filter( 'stat_query_country', $query ) ); + + // Loop through all results and build list of countries and hits + foreach( (array)$rows as $row ) { + if ("$row->country_code") + $countries["$row->country_code"] = $row->count; + } + + // Sort countries, most frequent first + if ( $countries ) + arsort( $countries ); + + // *** Dates : array of $dates[$year][$month][$day] = number of clicks *** + $query = "SELECT + DATE_FORMAT(`click_time`, '%Y') AS `year`, + DATE_FORMAT(`click_time`, '%m') AS `month`, + DATE_FORMAT(`click_time`, '%d') AS `day`, + COUNT(*) AS `count` + FROM `$table` + WHERE `shorturl` $keyword_range + GROUP BY `year`, `month`, `day`;"; + $rows = $ydb->get_results( yourls_apply_filter( 'stat_query_dates', $query ) ); + + // Loop through all results and fill blanks + foreach( (array)$rows as $row ) { + if( !array_key_exists($row->year, $dates ) ) + $dates[$row->year] = array(); + if( !array_key_exists( $row->month, $dates[$row->year] ) ) + $dates[$row->year][$row->month] = array(); + if( !array_key_exists( $row->day, $dates[$row->year][$row->month] ) ) + $dates[$row->year][$row->month][$row->day] = $row->count; + else + $dates[$row->year][$row->month][$row->day] += $row->count; + } + // Sort dates, chronologically from [2007][12][24] to [2009][02][19] ksort( $dates ); foreach( $dates as $year=>$months ) { @@ -125,37 +138,38 @@ ksort( $dates[$year][$month] ); } } - + // Get $list_of_days, $list_of_months, $list_of_years - reset($dates); - $first_year = key( $dates ); - $last_year = end( array_keys($dates) ); + reset( $dates ); if( $dates ) { extract( yourls_build_list_of_days( $dates ) ); - - // If the $last_24h doesn't have all the hours, insert missing hours with value 0 - for ($i = 23; $i >= 0; $i--) { - $h = date('y-m-d-H', $now - ($i * 60 * 60) ); - if( !array_key_exists( $h, $last_24h ) ) { - $last_24h[$h] = 0; - } - } - ksort( $last_24h ); } - // Sort countries, most frequent first - if ( $countries ) - arsort( $countries ); - - // Sort referrers. $referrer_sort is a array of most frequent domains - arsort( $referrers ); - $referrer_sort = array(); - $number_of_sites = count( array_keys( $referrers ) ); - foreach( $referrers as $site => $urls ) { - if( count($urls) > 1 || $number_of_sites == 1 ) - $referrer_sort[$site] = array_sum( $urls ); + + // *** Last 24 hours : array of $last_24h[ $hour ] = number of click *** + $query = "SELECT + DATE_FORMAT(`click_time`, '%H %p') AS `time`, + COUNT(*) AS `count` + FROM `$table` + WHERE `shorturl` $keyword_range AND `click_time` > (CURRENT_TIMESTAMP - INTERVAL 1 DAY) + GROUP BY `time`;"; + $rows = $ydb->get_results( yourls_apply_filter( 'stat_query_last24h', $query ) ); + + $_last_24h = array(); + foreach( (array)$rows as $row ) { + if ( $row->time ) + $_last_24h[ "$row->time" ] = $row->count; } - arsort($referrer_sort); + + $now = intval( date('U') ); + for ($i = 23; $i >= 0; $i--) { + $h = date('H A', $now - ($i * 60 * 60) ); + // If the $last_24h doesn't have all the hours, insert missing hours with value 0 + $last_24h[ $h ] = array_key_exists( $h, $_last_24h ) ? $_last_24h[ $h ] : 0 ; + } + unset( $_last_24h ); + + // *** Queries all done, phew *** // Filter all this junk if applicable. Be warned, some are possibly huge datasets. $referrers = yourls_apply_filter( 'pre_yourls_info_referrers', $referrers ); @@ -169,6 +183,7 @@ $last_24h = yourls_apply_filter( 'pre_yourls_info_last_24h', $last_24h ); $countries = yourls_apply_filter( 'pre_yourls_info_countries', $countries ); + // I can haz debug data /** echo "
";
 	echo "referrers: "; print_r( $referrers );
@@ -208,7 +223,7 @@
 	}
 } else {
 	yourls_html_link( yourls_link($keyword) );
-	if( count( $keyword_list ) > 1 )
+	if( isset( $keyword_list ) && count( $keyword_list ) > 1 )
 		echo ' ';
 } ?>
 
Long URL:  

@@ -291,61 +306,21 @@
 					if( ${'do_'.$graph} == true ) {
 						$display = ( ${'display_'.$graph} === true ? 'display:block' : 'display:none' );
 						echo "
";
-						$labels_1 = $labels_2 = array();
+						echo "
Number of hits : $graphtitle
";
 						switch( $graph ) {
 							case '24':
-								// each key of $last_24h is of type "yy-mm-dd-hh"
-								$first_key = current( array_keys( $last_24h ) );
-								$last_key = end( array_keys( $last_24h ) );
-								// Get "dd/mm" of first and last key
-								$first_label = preg_replace( '/\d\d-(\d\d)-(\d\d)-\d\d/', '$2/$1', $first_key );
-								$last_label = preg_replace( '/\d\d-(\d\d)-(\d\d)-\d\d/', '$2/$1', $last_key );
-								$labels_2 = array( $first_label, $last_label);
-								// Get hh of each key
-								foreach( $last_24h as $k=>$v ) {
-									$labels_1[] = end( explode( '-', $k ) ); // 'hh'
-								}
-								
-								echo "
Number of hits : $graphtitle
";
-								yourls_stats_line( $last_24h, $labels_1, $labels_2 );
+								yourls_stats_line( $last_24h, "stat_line_$graph" );
 								break;
 
 							case '7':
 							case '30':
-								// each key of $list_of_days is of type "yyyy-mm-dd"
 								$slice = array_slice( $list_of_days, intval( $graph ) * -1 );
-								foreach( $slice as $k=>$v ) {
-									// get "dd"
-									$labels_1[] = preg_replace( '/\d\d\d\d-\d\d-(\d\d)/', '$1', $k );
-								}
-								$first_key = current( array_keys( $slice ) );
-								$last_key = end( array_keys( $slice ) );
-								// Get "dd/mm" of first and last key
-								$first_label = preg_replace( '/\d\d\d\d-(\d\d)-(\d\d)/', '$2/$1', $first_key );
-								$last_label = preg_replace( '/\d\d\d\d-(\d\d)-(\d\d)/', '$2/$1', $last_key );
-								$labels_2 = array( $first_label, $last_label);
-								
-								echo "
Number of hits : $graphtitle
";
-								yourls_stats_line( $slice, $labels_1, $labels_2 );
+								yourls_stats_line( $slice, "stat_line_$graph" );
 								unset( $slice );
 								break;
 
 							case 'all':
-								// get "yy-mm"
-								foreach( $list_of_days as $k=>$v ) {
-									$labels_1[] = preg_replace( '/\d\d(\d\d)-(\d\d)-\d\d/', '$1-$2', $k );
-								}
-								// take out duplicates
-								$labels_1 = array_unique( $labels_1 );
-								// now get "mm" only so we have all different month
-								foreach( $labels_1 as $k=>$v ) {
-									$labels_1[$k] = preg_replace( '/\d\d-(\d\d)/', '$1', $v );
-								}
-								
-								echo "
Number of hits : $graphtitle
";
-								$labels_1 = yourls_array_granularity( $labels_1, 30, false );
-								$labels_2 = yourls_array_granularity( $list_of_years, 30, false );
-								yourls_stats_line( $list_of_days, $labels_1, $labels_2 );
+								yourls_stats_line( $list_of_days, "stat_line_$graph" );
 								break;
 						}
 						echo "
\n";
@@ -363,7 +338,7 @@
 					$daysago = '(about '.$ago .' '.yourls_plural( ' day', $ago ).' ago)';
 				}
 				?>
-				
Short URL created on  

+				
Short URL created on  

 				

 					
    
 					
 				
 					
    Top 5 countries
    
-					
+					
 					
    Click for more details