committers/committertools/adReportRefined.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>";  
		
	
	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 52 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 52 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 51 WEEK)";
	
	mysql_query($impressions, $dbh_RW);
	
	mysql_query($clicks, $dbh_RW);
	$result = mysql_query($merge, $dbh_RW);
	
	
	?>
	<link rel="stylesheet" type="text/css" href="style.css">
	<table>
		<tr>
			<th>Key</th>
			<th>Period</th>
			<th>Impressions</th>
			<th>Clicks</th>
			<th>CTR</th>
		</tr>


	<?php
	$diffcheck == '';
	while($rr = mysql_fetch_array($result)){ 
		if ($diffcheck != $rr['campaignKey']){
			$new = TRUE;
		}	
		else {
			$new = FALSE;
		}
		
		?>
		<tr <?php if ($new) print 'class="new"'?>>
			<td><a href="adReportWeekDetail.php?campaignKey=<?php print $rr['campaignKey']?>&week=<?php print $rr['date']?>"><?php print $rr['campaignKey'];?></a></td>
			<td><?php print $rr['date']?></td>
			<td><?php print $rr['impressions']?></td>
			<td><?php print $rr['clicks']?></td>
			<td><?php printf("%01.2f", ($rr['clicks']/$rr['impressions'])*100) ?>%</td>
		</tr>
	<?php
		$diffcheck = $rr['campaignKey'];
	}
	?>
	</table>