<?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> |