Dashboard

Editing: adrotate-admin-statistics.php

<?php
/* ------------------------------------------------------------------------------------
*  COPYRIGHT AND TRADEMARK NOTICE
*  Copyright 2008-2024 Arnan de Gans. All Rights Reserved.
*  ADROTATE is a registered trademark of Arnan de Gans.

*  COPYRIGHT NOTICES AND ALL THE COMMENTS SHOULD REMAIN INTACT.
*  By using this code you agree to indemnify Arnan de Gans from any
*  liability that might arise from its use.
------------------------------------------------------------------------------------ */

/*-------------------------------------------------------------
 Name:      adrotate_draw_graph
 Purpose:   Draw graph using ElyCharts
-------------------------------------------------------------*/
function adrotate_draw_graph($id = 0, $labels = 0, $clicks = 0, $impressions = 0) {

if($id == 0 OR !is_numeric($id) OR strlen($labels) < 1 OR strlen($clicks) < 1 OR strlen($impressions) < 1) {
		echo "Syntax error, graph (ID: ".$id.") can not de drawn!";
	} else {
		echo "<script type=\"text/javascript\">
		jQuery(document).ready(function(){
			jQuery(\"#chart-".$id."\").chart({
			    type: 'line',
			    margins: [5, 45, 45, 45],
		        values: {
		            serie1: [".$clicks."], serie2: [".$impressions."]
		        },
        		labels: [".$labels."],
			    tooltips: function(env, serie, index, value, label) {
			        return '<div class=\"adrotate_label\">' + label + '<br /><span class=\"adrotate_clicks\">Clicks:</span> ' + env.opt.values['serie1'][index] + '<br /><span class=\"adrotate_impressions\">Impressions:</span> ' + env.opt.values['serie2'][index] + '</div>';
			    },
			    defaultSeries: {
					plotProps: { 'stroke-width': 3 }, dot: true, rounded: true, dotProps: { stroke: 'white', size: 5, 'stroke-width': 1, opacity: 0 }, highlight: { scaleSpeed: 0, scaleEasing: '', scale: 1.2, newProps: { opacity: 1 } }, tooltip: { height: 55, width: 120, padding: [0], offset: [-10, -10], frameProps: { opacity: 0.95, stroke: '#000' } }
			    },
			    series: {
			        serie1: {
			            fill: true, fillProps: { opacity: .1 }, color: '#26B',
			        },
			        serie2: {
			            axis: 'r', color: '#F80', plotProps: { 'stroke-width': 2 }, dotProps: { stroke: 'white', size: 3, 'stroke-width': 1 }
			        }

},
			    defaultAxis: {
			        labels: true, labelsProps: { fill: '#777', 'font-size': '10px', }, labelsAnchor: 'start', labelsMargin: 5, labelsDistance: 8, labelsRotate: 65
			    },
 			    axis: {
			        l: { // left axis
			            labels: true, labelsSkip: 1, labelsAnchor: 'end', labelsMargin: 15, labelsDistance: 4, labelsProps: { fill: '#26B', 'font-size': '11px', 'font-weight': 'bold' }
			        },
			        r: { // right axis
			            labels: true, labelsSkip: 1, labelsAnchor: 'start', labelsMargin: 15, labelsDistance: 4, labelsProps: { fill: '#F80', 'font-size': '11px', 'font-weight': 'bold' }
			        }
			    },
			    features: {
			        mousearea: {
			            type: 'axis'
			        },
			        tooltip: {
			            positionHandler: function(env, tooltipConf, mouseAreaData, suggestedX, suggestedY) {
			                return [mouseAreaData.event.pageX, mouseAreaData.event.pageY, true]
			            }
			        },
			        grid: {
			            draw: true, // draw both x and y grids
			            forceBorder: [true, true, true, true], // force grid for external border
			            props: {
			                stroke: '#eee' // color for the grid
			            }
			        }
			    }
			});
		});
		</script>";
	}
}

/*-------------------------------------------------------------
 Name:      adrotate_stats
 Purpose:   Generate latest number of clicks and impressions
-------------------------------------------------------------*/
function adrotate_stats($ad, $when = 0, $until = 0) {
	global $wpdb;

if($when > 0 AND is_numeric($when) AND $until > 0 AND is_numeric($until)) { // date range
		$whenquery = " AND `thetime` >= $when AND `thetime` <= $until GROUP BY `ad` ORDER BY `ad` ASC";
	} else if($when > 0 AND is_numeric($when) AND $until == 0) { // one day
		$until = $when + 86400;
		$whenquery =  " AND `thetime` >= $when AND `thetime` <= $until";
	} else { // everything
		$whenquery = "";
	}

$ad_query = "`ad` = ".$ad;
	if(is_array($ad)) {
		$ad_query = "(";
		foreach($ad as $key => $value) {
			$ad_query .= "`ad` = $value OR ";
		}
		$ad_query = rtrim($ad_query, " OR ");
		$ad_query .= ")";
	}

$stats = $wpdb->get_row("SELECT SUM(`clicks`) as `clicks`, SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}adrotate_stats` WHERE {$ad_query}{$whenquery};", ARRAY_A);

if(empty($stats['clicks'])) $stats['clicks'] = '0';
	if(empty($stats['impressions'])) $stats['impressions'] = '0';

return $stats;
}

/*-------------------------------------------------------------
 Name:      adrotate_stats_nav
 Purpose:   Create browsable links for graph
-------------------------------------------------------------*/
function adrotate_stats_nav($type, $id, $month, $year) {
	global $wpdb;

$lastmonth = $month-1;
	$nextmonth = $month+1;
	$lastyear = $nextyear = $year;
	if($month == 1) {
		$lastmonth = 12;
		$lastyear = $year - 1;
	}
	if($month == 12) {
		$nextmonth = 1;
		$nextyear = $year + 1;
	}
	$months = array(__("January", 'adrotate'), __("February", 'adrotate'), __("March", 'adrotate'), __("April", 'adrotate'), __("May", 'adrotate'), __("June", 'adrotate'), __("July", 'adrotate'), __("August", 'adrotate'), __("September", 'adrotate'), __("October", 'adrotate'), __("November", 'adrotate'), __("December", 'adrotate'));

$page = '';
	if($type == 'ads') $page = 'adrotate-statistics&view=advert&id='.$id;
	if($type == 'groups') $page = 'adrotate-statistics&view=group&id='.$id;
	if($type == 'fullreport') $page = 'adrotate-statistics';
	if($type == 'advertiser') $page = 'adrotate-advertiser&view=report&ad='.$id;
	if($type == 'advertiserfull') $page = 'adrotate-advertiser';

$nav = "<a href=\"admin.php?page=".$page."&month=".$lastmonth."&year=".$lastyear."\">&lt;&lt; ".__("Previous", 'adrotate')."</a> - ";
	$nav .= "<strong>".$months[$month-1]." ".$year."</strong> - ";
	$nav .= "(<a href=\"admin.php?page=".$page."\">".__("This month", 'adrotate')."</a>) - ";
	$nav .= "<a href=\"admin.php?page=".$page."&month=".$nextmonth."&year=".$nextyear."\">". __("Next", 'adrotate')." &gt;&gt;</a>";

return $nav;
}

/*-------------------------------------------------------------
 Name:      adrotate_stats_graph
 Purpose:   Generate graph
-------------------------------------------------------------*/
function adrotate_stats_graph($type, $id, $chartid, $start, $end) {
	global $wpdb;

$table = 'adrotate_stats';
	if($type == 'ads' OR $type == 'advertiser') {
		$stats = $wpdb->get_results($wpdb->prepare("SELECT `thetime`, SUM(`clicks`) as `clicks`, SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}{$table}` WHERE `ad` = %d AND `thetime` >= %d AND `thetime` <= %d GROUP BY `thetime` ORDER BY `thetime` ASC;", $id, $start, $end), ARRAY_A);
	}

if($type == 'groups') {
		$stats = $wpdb->get_results($wpdb->prepare("SELECT `thetime`, SUM(`clicks`) as `clicks`, SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}{$table}` WHERE `group` = %d AND `thetime` >= %d AND `thetime` <= %d GROUP BY `thetime` ORDER BY `thetime` ASC;", $id, $start, $end), ARRAY_A);
	}

if($type == 'fullreport') {
		$stats = $wpdb->get_results($wpdb->prepare("SELECT `thetime`, SUM(`clicks`) as `clicks`, SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}{$table}` WHERE `thetime` >= %d AND `thetime` <= %d GROUP BY `thetime` ORDER BY `thetime` ASC;", $start, $end), ARRAY_A);
	}

if($type == 'advertiserfull') {
		$stats = $wpdb->get_results($wpdb->prepare("SELECT `thetime`, SUM(`clicks`) as `clicks`, SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}{$table}`, `{$wpdb->prefix}adrotate_linkmeta` WHERE `{$wpdb->prefix}adrotate_stats`.`ad` = `{$wpdb->prefix}adrotate_linkmeta`.`ad` AND `{$wpdb->prefix}adrotate_linkmeta`.`user` = %d AND `{$wpdb->prefix}adrotate_stats`.`thetime` >= %d AND `{$wpdb->prefix}adrotate_stats`.`thetime` <= %d GROUP BY `thetime` ORDER BY `thetime` ASC;", $id, $start, $end), ARRAY_A);
	}

if($stats) {
		$dates = $clicks = $impressions = '';

foreach($stats as $result) {
			if(empty($result['clicks'])) $result['clicks'] = '0';
			if(empty($result['impressions'])) $result['impressions'] = '0';

$dates .= ',"'.date_i18n("d M", $result['thetime']).'"';
			$clicks .= ','.$result['clicks'];
			$impressions .= ','.$result['impressions'];
		}

$dates = trim($dates, ',');
		$clicks = trim($clicks, ',');
		$impressions = trim($impressions, ',');

$output = "";
		$output .= "<div id=\"chart-".$chartid."\" style=\"height:300px; width:100%;\"></div>";
		$output .= adrotate_draw_graph($chartid, $dates, $clicks, $impressions);
		unset($stats, $graph, $dates, $clicks, $impressions);
	} else {
		$output = __("No data to show!", 'adrotate');
	}

return $output;
}

/*-------------------------------------------------------------
 Name:      adrotate_ctr
 Purpose:   Calculate Click-Through-Rate
-------------------------------------------------------------*/
function adrotate_ctr($clicks = 0, $impressions = 0, $round = 2) {

if($impressions > 0 AND $clicks > 0) {
		$ctr = round($clicks/$impressions*100, $round);
	} else {
		$ctr = 0;
	}

return $ctr;
}

/*-------------------------------------------------------------
 Name:      adrotate_prepare_fullreport
 Purpose:   Generate live stats for admins
-------------------------------------------------------------*/
function adrotate_prepare_fullreport() {
	global $wpdb;

$today = adrotate_date_start('day');

$stats['banners'] = $wpdb->get_var("SELECT COUNT(*) FROM `{$wpdb->prefix}adrotate` WHERE (`type` = 'active' OR `type` = 'expired' OR `type` = 'error');");
	$stats['tracker'] = $wpdb->get_var("SELECT COUNT(*) FROM `{$wpdb->prefix}adrotate` WHERE `tracker` = 'Y' AND (`type` = 'active' OR `type` = 'expired' OR `type` = 'error');");
	$stats['overall_clicks'] = $wpdb->get_var("SELECT SUM(`clicks`) as `clicks` FROM `{$wpdb->prefix}adrotate_stats`;");
	$stats['overall_impressions'] = $wpdb->get_var("SELECT SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}adrotate_stats`;");

$active_ids = $wpdb->get_results("SELECT `id` FROM `{$wpdb->prefix}adrotate` WHERE (`type` = 'active' OR `type` = 'expired' OR `type` = 'error');");
	if(count($active_ids) > 0) {
		foreach($active_ids as $id) {
			$sorted[] = $id->id;
		}
		$active_ids = " AND (`ad` = ".implode(' OR `ad` = ', $sorted).")";
		unset($sorted);
	} else {
		$active_ids = "";
	}

$this_month_start = mktime(0, 0, 0, date('m'), 1, date('Y'));
	$this_month_end = mktime(0, 0, 0, date('m'), date('t'), date('Y'));
	$stats['this_month_clicks'] = $wpdb->get_var("SELECT SUM(`clicks`) as `clicks` FROM `{$wpdb->prefix}adrotate_stats` WHERE `thetime` >= {$this_month_start} AND `thetime` <= {$this_month_end}{$active_ids};");
	$stats['this_month_impressions'] = $wpdb->get_var("SELECT SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}adrotate_stats` WHERE `thetime` >= {$this_month_start} AND `thetime` <= {$this_month_end}{$active_ids};");

$last_month_start = mktime(0, 0, 0, date('m')-1, 1, date('Y'));
	$last_month_end = mktime(0, 0, 0, date('m'), 0, date('Y'));
	$stats['last_month_clicks'] = $wpdb->get_var("SELECT SUM(`clicks`) as `clicks` FROM `{$wpdb->prefix}adrotate_stats` WHERE `thetime` >= {$last_month_start} AND `thetime` <= {$last_month_end}{$active_ids};");
	$stats['last_month_impressions'] = $wpdb->get_var("SELECT SUM(`impressions`) as `impressions` FROM `{$wpdb->prefix}adrotate_stats` WHERE `thetime` >= {$last_month_start} AND `thetime` <= {$last_month_end}{$active_ids};");

if(!$stats['banners']) $stats['banners'] = 0;
	if(!$stats['tracker']) $stats['tracker'] = 0;
	if(!$stats['overall_clicks']) $stats['overall_clicks'] = 0;
	if(!$stats['overall_impressions']) $stats['overall_impressions'] = 0;
	if(!$stats['last_month_clicks']) $stats['last_month_clicks'] = 0;
	if(!$stats['last_month_impressions']) $stats['last_month_impressions'] = 0;
	if(!$stats['this_month_clicks']) $stats['this_month_clicks'] = 0;
	if(!$stats['this_month_impressions']) $stats['this_month_impressions'] = 0;

return $stats;
}

/*-------------------------------------------------------------
 Name:      adrotate_archive_stats
 Purpose:   Move stats into a secondary table when adverst are archived
-------------------------------------------------------------*/
function adrotate_archive_stats($id) {
	global $wpdb;

$advert_tracking = $wpdb->get_var("SELECT `tracker` FROM `{$wpdb->prefix}adrotate` WHERE `ad` = {$id} ORDER BY `id` ASC LIMIT 1;");
	if($advert_tracking == "Y") {
		$insert = $delete = false;
		$stats = $wpdb->get_results("SELECT * FROM `{$wpdb->prefix}adrotate_stats` WHERE `ad` = {$id} ORDER BY `id` ASC;");
	
		foreach($stats as $stat) {
			if($stat->id > 0) {
				$insert[] = "(".$stat->ad.", ".$stat->group.", ".$stat->thetime.", ".$stat->clicks.", ".$stat->impressions.")";
				$delete[] = $stat->id;
			}
		}
		
		// Insert each chunk to the archive table
		if(is_array($insert)) {
			// Split the data into chunks
			$insert = array_chunk($insert, 30);

foreach($insert as $chunk) {
				$wpdb->query("INSERT INTO `{$wpdb->prefix}adrotate_stats_archive` (`ad`, `group`, `thetime`, `clicks`, `impressions`) VALUES ".implode(",", $chunk).";");
				unset($chunk);
			}
	
			// Delete old stats
			$wpdb->query("DELETE FROM `{$wpdb->prefix}adrotate_stats` WHERE `id` IN (".implode(',', $delete).");");
		}
	
		unset($stats, $insert, $delete);
	}
}
?>

Cancel

Dashboard

Powered by WordPress