File: D:/HostingSpaces/SBogers10/komma.pro/wwwroot/kes/admin/statistieken_user_views.php
<?php
$email_id = $_GET['email_id'];
$nieuwsbrief_id = $_GET['nieuwsbrief_id'];
$kleur_nieuwsbrieven = false;
$query_user = "SELECT kes_emailadressen.voornaam, kes_emailadressen.tussenvoegsel, kes_emailadressen.achternaam, kes_emailadressen.geslacht, kes_emailadressen.emailadres, kes_emailadressen.bedrijfsnaam, kes_emailadressen.telefoonnummer, kes_emailadressen.geboortedatum, kes_emailadressen.geactiveerd, kes_emailadressen.status, kes_emailadressen.taal, kes_emailadressen.categorie_a, kes_emailadressen.categorie_b, kes_emailadressen.datum_in, kes_emailadressen.tijd_in, kes_emailadressen.verwijderd, kes_emailadressen.datum_uit, kes_emailadressen.verwijderd_door FROM kes_emailadressen WHERE kes_emailadressen.email_id='$email_id'";
$res_user = mysql_query($query_user);
$rec_user= mysql_fetch_array($res_user);
?>
<h1>Statistieken
<?php
echo(" "); echo($rec_user['voornaam']); echo(" ");
if(!empty($rec_user['tussenvoegsel'])){ echo($rec_user['tussenvoegsel']); echo(" "); }
echo($rec_user['achternaam']);
?>
</h1>
<p>Hieronder vindt u alle informatie van de geselecteerde persoon.</p><br />
<?php
$query_nieuwsbrief = "SELECT kes_nieuwsbrieven.naam, kes_nieuwsbrieven.onderwerp, kes_nieuwsbrieven.locatie, kes_nieuwsbrieven.taal FROM kes_nieuwsbrieven WHERE kes_nieuwsbrieven.nieuwsbrief_id='$nieuwsbrief_id'";
$res_nieuwsbrief = mysql_query($query_nieuwsbrief);
$rec_nieuwsbrief = mysql_fetch_array($res_nieuwsbrief);
$query_verzonden = "SELECT kes_verzendingen.verzend_datum FROM kes_verzendingen WHERE kes_verzendingen.nieuwsbrief_id = '$nieuwsbrief_id' LIMIT 1";
$res_verzonden = mysql_query($query_verzonden);
$rec_verzonden = mysql_fetch_array($res_verzonden);
$verzend_datum = $rec_verzonden[0];
$query_aantalkeer = "SELECT COUNT(kes_track_email.email_id) FROM kes_track_email WHERE kes_track_email.nieuwsbrief_id='$nieuwsbrief_id' AND kes_track_email.email_id='$email_id'";
$res_aantalkeer = mysql_query($query_aantalkeer);
$rec_aantalkeer = mysql_fetch_array($res_aantalkeer);
$query_aantalkeer2 = "SELECT COUNT(kes_track_online.email_id) FROM kes_track_online WHERE kes_track_online.nieuwsbrief_id='$nieuwsbrief_id' AND kes_track_online.email_id='$email_id'";
$res_aantalkeer2 = mysql_query($query_aantalkeer2);
$rec_aantalkeer2 = mysql_fetch_array($res_aantalkeer2);
$totaal_bekeken = $rec_aantalkeer[0] + $rec_aantalkeer2[0];
$query_eerste_kijk = "SELECT kes_track_email.datum FROM kes_track_email WHERE kes_track_email.nieuwsbrief_id='$nieuwsbrief_id' AND kes_track_email.email_id='$email_id' UNION SELECT kes_track_online.datum FROM kes_track_online WHERE kes_track_online.nieuwsbrief_id='$nieuwsbrief_id' AND kes_track_online.email_id='$email_id' GROUP BY datum ORDER BY datum asc";
$res_eerste_kijk = mysql_query($query_eerste_kijk);
$rec_eerste_kijk = mysql_fetch_array($res_eerste_kijk);
$begin = strtotime($verzend_datum);
$eind = strtotime($rec_eerste_kijk[0]);
$verschil = $eind - $begin;
$aantal_dagen = $verschil / (60*60*24);
$dagen_totaal = $dagen_totaal + $aantal_dagen;
$query_links = "SELECT kes_track_link.link_id, kes_track_link.url FROM kes_track_link WHERE kes_track_link.nieuwsbrief_id='$nieuwsbrief_id'
AND kes_track_link.email_id = '$email_id' GROUP BY kes_track_link.link_id ORDER BY kes_track_link.link_id asc";
$res_links = mysql_query($query_links);
$query_links_views = "SELECT kes_track_link.link_id, kes_track_link.url, kes_track_link.datum, kes_track_link.tijd FROM kes_track_link WHERE kes_track_link.nieuwsbrief_id='$nieuwsbrief_id'
AND kes_track_link.email_id = '$email_id' ORDER BY kes_track_link.datum asc, kes_track_link.tijd asc";
$res_links_views = mysql_query($query_links_views );
$query_email = "SELECT kes_track_email.datum, kes_track_email.tijd FROM kes_track_email WHERE kes_track_email.nieuwsbrief_id='$nieuwsbrief_id' AND kes_track_email.email_id='$email_id' ORDER BY kes_track_email.datum asc, kes_track_email.tijd asc";
$res_email = mysql_query($query_email);
$query_online = "SELECT kes_track_online.datum, kes_track_online.tijd FROM kes_track_online WHERE kes_track_online.nieuwsbrief_id='$nieuwsbrief_id' AND kes_track_online.email_id='$email_id' ORDER BY kes_track_online.datum asc, kes_track_online.tijd asc";
$res_online = mysql_query($query_online);
?>
<div class="user_overzicht"
<?php if($kleur_nieuwsbrieven){ echo("style=\"background-color:#e6e8eb; \""); $kleur_nieuwsbrieven = false; }else{ $kleur_nieuwsbrieven = true; }?> >
<h2>Nieuwsbrief gegevens</h2>
<div class="left">
<p><strong>Nieuwsbrief:</strong> <?php echo($nieuwsbrief_id); ?> | <?php echo($rec_nieuwsbrief['naam']);?><br />
<strong>Onderwerp:</strong> <?php echo($rec_nieuwsbrief['onderwerp']);?><br />
<strong>Spreektaal:</strong> <?php echo(ucfirst($rec_nieuwsbrief['taal']));?><br /><br />
<strong>Verzonden op:</strong> <?php echo(date_dutch($verzend_datum));?></p>
<a href="/kes/nieuwsbrieven/<?php echo($rec_nieuwsbrief['locatie']);?>/index.html" target="_blank">Klik hier om de nieuwsbrief te bekijken ></a>
</div>
<div class="left">
<strong>Aantal keer bekeken in e-mail:</strong> <?php echo($rec_aantalkeer[0]); ?><br />
<strong>Aantal keer bekeken online:</strong> <?php echo($rec_aantalkeer2[0]); ?><br />
<strong>Totaal aantal keer bekeken:</strong> <?php echo($totaal_bekeken); ?><br /><br />
<strong>Eerste keer bekeken:</strong> <?php echo(date_dutch($rec_eerste_kijk[0])); ?><br />
Bekeken <strong><?php echo(round($dagen_totaal)); ?></strong> dagen na verzending
</div>
<div class="clear"></div>
<?php
$links_first = true;
$kleur_link = true;
while($rec_links = mysql_fetch_array($res_links)){
if($links_first){
$links_first = false;
?>
<br /><br />
<table cellpadding="5" cellspacing="0" border="0">
<tr>
<th>Link ID</th>
<th>URL</th>
<th>Aantal x bekeken</th>
</tr>
<?php }
$query_links_stats = "SELECT COUNT(kes_track_link.email_id) FROM kes_track_link WHERE kes_track_link.nieuwsbrief_id='$nieuwsbrief_id'
AND kes_track_link.link_id='$rec_links[0]' AND kes_track_link.email_id = '$email_id'";
$res_links_stats = mysql_query($query_links_stats);
$rec_links_stats = mysql_fetch_array($res_links_stats);
?>
<tr <?php if($kleur_link){ echo("style=\"background-color:#fff; \""); $kleur_link = false; }else{ echo("style=\"background-color:#e6e8eb; \""); $kleur_link = true; }?> >
<td><?php echo( $rec_links['link_id'] ); ?></td>
<td><a href="<?php echo( $rec_links['url']); ?>" target="_blank"><?php echo( $rec_links['url']); ?></a></td>
<td><?php echo( $rec_links_stats[0]); ?></td>
</tr>
<?php } ?>
</table>
<br /><br />
</div>
<div class="user_overzicht">
<h2>Views links</h2>
<?php
$views_links_first = true;
$kleur_views_links = false;
while($rec_links_views = mysql_fetch_array($res_links_views)){
if($views_links_first){
$views_links_first = false;
?>
<table cellpadding="5" cellspacing="0" border="0">
<tr>
<th>Link ID</th>
<th>URL</th>
<th>Datum</th>
<th>Tijd</th>
</tr>
<?php } ?>
<tr <?php if($kleur_views_links){ echo("style=\"background-color:#fff; \""); $kleur_views_links = false; }else{
echo("style=\"background-color:#e6e8eb; \""); $kleur_views_links = true; }?> >
<td><?php echo( $rec_links_views['link_id'] ); ?></td>
<td><a href="<?php echo( $rec_links_views['url']); ?>" target="_blank"><?php echo( $rec_links_views['url']); ?></a></td>
<td><?php echo( date_dutch($rec_links_views['datum'])); ?></td>
<td><?php echo( $rec_links_views['tijd']); ?></td>
</tr>
<?php } ?>
</table>
<br /><br />
</div>
<div class="user_overzicht">
<div class="left">
<h2>Views e-mail</h2>
<?php
$views_first = true;
$kleur_views = false;
$counter_views = 0;
while($rec_email = mysql_fetch_array($res_email)){
$counter_views++;
if($views_first){
$views_first = false;
?>
<table cellpadding="5" cellspacing="0" border="0">
<tr>
<th>Counter</th>
<th>Datum</th>
<th>Tijd</th>
</tr>
<?php } ?>
<tr <?php if($kleur_views){ echo("style=\"background-color:#fff; \""); $kleur_views = false; }else{ echo("style=\"background-color:#e6e8eb; \""); $kleur_views = true; }?> >
<td><?php echo( $counter_views); ?></td>
<td><?php echo( date_dutch($rec_email['datum'])); ?></td>
<td><?php echo( $rec_email['tijd']); ?></td>
</tr>
<?php } ?>
</table>
</div>
<div class="left" style="padding-left:150px;">
<h2>Views online</h2>
<?php
$views_first = true;
$kleur_views = false;
$counter_views = 0;
while($rec_online = mysql_fetch_array($res_online)){
$counter_views++;
if($views_first){
$views_first = false;
?>
<table cellpadding="5" cellspacing="0" border="0">
<tr>
<th>Counter</th>
<th>Datum</th>
<th>Tijd</th>
</tr>
<?php } ?>
<tr <?php if($kleur_views){ echo("style=\"background-color:#fff; \""); $kleur_views = false; }else{ echo("style=\"background-color:#e6e8eb; \""); $kleur_views = true; }?> >
<td><?php echo( $counter_views); ?></td>
<td><?php echo( date_dutch($rec_online['datum'])); ?></td>
<td><?php echo( $rec_online['tijd']); ?></td>
</tr>
<?php } ?>
</table>
</div>
<div class="clear"></div>
<br /><br />
</div>