committers/committertools/adReports.php

<?php
	require_once("/home/data/httpd/eclipse-php-classes/system/app.class.php");
	require_once("/home/data/httpd/eclipse-php-classes/people/ldapperson.class.php");
	require_once("/home/data/httpd/eclipse-php-classes/menu/menu.class.php");
 	require_once("/home/data/httpd/eclipse-php-classes/system/dbconnection_rw.class.php");
    
	#*****************************************************************************
	#
	# adReports.php
	#
	# Author: 		Nathan Gervais
	# Date:			2010-12-14
	#
	# Description: Reporting tool for ad campaigns
	#
	# HISTORY:  Jan 2, 2008 Dons first public release
	#
	#****************************************************************************
	$App 	= new App();
	$App->runStdWebAppCacheable();
	$ErrorMessage = "";
	
	$LDAPPerson = new LDAPPerson();
	$LDAPPerson = $LDAPPerson->redirectIfNotLoggedIn();
		
	
####################### CONNECTIONS
	# Connect to databases
	$dbc_RW 	= new DBConnectionRW();
		// echo "ERROR FROM NEW CONNECTION: " . mysql_error() . "<br>"; 
	$dbh_RW 	= $dbc_RW->connect();
		// echo "ERROR FROM CONNECT: " . mysql_error() . "<br>";  
	
	$interval = 10; 					# Weeks
	$interval_plus_one = $interval++;
	
	include("../html/header.php");

	$Menu = new Menu("en");
	include("../modules/menu.php");

	
	
	echo "<h3>Ad Reports</h3>";
	
	
	$impressions = "create temporary table impressions select campaignKey, count(*) as impressions, yearweek(TimeImpressed) as period from CampaignImpressions FORCE INDEX (idx_TimeImpressed) WHERE TimeImpressed > (NOW()-INTERVAL $interval_plus_one WEEK) group by campaignKey, yearweek(TimeImpressed) ";
	$clicks = "create temporary table clicks select campaignKey, count(ClickID) as clicks, yearweek(TimeClicked) as period from CampaignClicks WHERE TimeClicked > (NOW()-INTERVAL $interval_plus_one WEEK) group by campaignkey, yearweek(TimeClicked)";
	$merge = "select *, str_to_date(concat(period, ' Sunday'), '%X%V %W') as date from impressions left join clicks using (campaignKey, period) WHERE period > yearweek(NOW()-INTERVAL $interval WEEK)";
	
	mysql_query($impressions, $dbh_RW);
	
	sleep(60);  #sleep a bit to let db load subside
	
	mysql_query($clicks, $dbh_RW);
	$result = mysql_query($merge, $dbh_RW);
	
	while($rr = mysql_fetch_array($result)){ ?>
		
		<?php print $rr['campaignKey'];?>,<?php print $rr['date']?>,<?php print $rr['impressions']?>,<?php print $rr['clicks']?>,%<?php printf("%01.2f", ($rr['clicks']/$rr['impressions'])*100) ?><br/>
	<?php }
	?>