pierre@0: <?php
pierre@1: // $Id: ad.pages.inc,v 1.1.2.6.2.2 2009/02/28 23:39:47 jeremy Exp $
pierre@0: 
pierre@0: /**
pierre@0:  * @file
pierre@0:  * Advertisement nodes pages and forms.
pierre@0:  *
pierre@0:  * Copyright (c) 2005-2009.
pierre@0:  *   Jeremy Andrews <jeremy@tag1consulting.com>
pierre@0:  */
pierre@0: 
pierre@0: function theme_node_ad($node, $yield_form = TRUE) {
pierre@0:   $output = '';
pierre@1:   if (ad_permission($node, 'access statistics')) {
pierre@0:     $output = theme('ad_status_display', $node);
pierre@0:     $output .= theme('ad_statistics_display', ad_statistics($node->nid));
pierre@0:   }
pierre@1:   if (ad_permission($node, 'access click history')) {
pierre@0:     $header = array(
pierre@0:       array('data' => t('Time'), 'field' => 'timestamp', 'sort' => 'desc'),
pierre@0:       array('data' => t('User'), 'field' => 'uid'),
pierre@0:       array('data' => t('URL where clicked'), 'field' => 'url'),
pierre@0:     );
pierre@0:     if (function_exists('click_filter_status_text') && user_access('view filtered clicks')) {
pierre@0:       $header[] = array('data' => t('Status'), 'field' => 'status');
pierre@0:     }
pierre@0:     $header[] = '';
pierre@0: 
pierre@1:     if (isset($node->nid) && $node->nid > 0) {
pierre@1:       $sql = "SELECT cid, timestamp, uid, status, hostname, url FROM {ad_clicks} WHERE aid = %d";
pierre@0:       $sql .= tablesort_sql($header);
pierre@0:       $result = pager_query($sql, 25, 0, NULL, $node->nid);
pierre@0: 
pierre@0:       while ($ad = db_fetch_object($result)) {
pierre@0:         if (module_exists('click_filter') && $ad->status != CLICK_VALID) {
pierre@0:           // Only show filtered clicks to users with permission to view them.
pierre@0:           if (!user_access('view filtered clicks')) {
pierre@0:             continue;
pierre@0:           }
pierre@0:         }
pierre@0:         if (strlen($ad->url) > 40) {
pierre@0:           $url = substr($ad->url, 0, 37) .'...';
pierre@0:         }
pierre@0:         else {
pierre@0:           $url = $ad->url;
pierre@0:         }
pierre@0:         $row = array();
pierre@0:         $click_user = user_load(array('uid' => $ad->uid));
pierre@0:         $row[] = format_date($ad->timestamp, 'custom', 'M j H:i');
pierre@0:         $row[] = theme('username', $click_user);
pierre@0:         $row[] = l($url, $ad->url);
pierre@0:         if (function_exists('click_filter_status_text') && user_access('view filtered clicks')) {
pierre@0:           $row[] = click_filter_status_text($ad->status);
pierre@0:         }
pierre@0:         $row[] = '['. l(t('details'), 'node/'. $node->nid .'/details/'. $ad->cid) .']';
pierre@0:         $rows[] = $row;
pierre@0:       }
pierre@0: 
pierre@0:       if (empty($rows)) {
pierre@0:         $click_history = '<p>'. t('There are no clicks yet.') .'</p>';
pierre@0:       }
pierre@0:       else {
pierre@0:         $click_history = theme('table', $header, $rows);
pierre@0:       }
pierre@0:       $click_history .= theme('pager', NULL, 25, 0);
pierre@0:       $output .= theme('box', t('Click history'), $click_history);
pierre@0:     }
pierre@0:   }
pierre@0:   return $output;
pierre@0: }
pierre@0: 
pierre@0: /**
pierre@0:  * Calculate statistics for the given advertisements.
pierre@0:  * TODO: Introduce caching to make this more efficient.
pierre@0:  */
pierre@0: function ad_statistics($aid) {
pierre@0:   // Get global statistics.
pierre@0:   $statistics['global']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view'", $aid));
pierre@0:   $statistics['global']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click'", $aid));
pierre@0: 
pierre@0:   // No sense in making further queries if the ad has no global statistics.
pierre@0:   if (!$statistics['global']['views'] && !$statistics['global']['clicks']) {
pierre@0:     return $statistics;
pierre@0:   }
pierre@0: 
pierre@0:   // Get statistics for this year and last year.
pierre@0:   $this_year = date('Y000000');
pierre@0:   $last_year = date('Y') - 1 .'000000';
pierre@0:   $statistics['last_year']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $aid, $last_year, $this_year));
pierre@0:   $statistics['last_year']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d AND date <= %d", $aid, $last_year, $this_year));
pierre@0:   $statistics['this_year']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d", $aid, $this_year));
pierre@0:   $statistics['this_year']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d", $aid, $this_year));
pierre@0: 
pierre@0:   // No sense in making further queries if the ad has no statistics this year.
pierre@0:   if (!$statistics['this_year']['views'] && !$statistics['this_year']['clicks']) {
pierre@0:     return $statistics;
pierre@0:   }
pierre@0: 
pierre@0:   // Get statistics for this month and last month.
pierre@0:   $this_month = date('Ym0000');
pierre@0:   $last_month = date('m') - 1;
pierre@0:   if ($last_month == 0) {
pierre@0:     $last_month = date('Y') - 1 .'120000';
pierre@0:   }
pierre@0:   else {
pierre@0:     $last_month = date('Y') . ($last_month < 10 ? '0' : '') . $last_month .'0000';
pierre@0:   }
pierre@0:   $statistics['last_month']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $aid, $last_month, $this_month));
pierre@0:   $statistics['last_month']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d AND date <= %d", $aid, $last_month, $this_month));
pierre@0:   $statistics['this_month']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d", $aid, $this_month));
pierre@0:   $statistics['this_month']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d", $aid, $this_month));
pierre@0: 
pierre@0:   // No sense in making further queries if the ad has no statistics this month.
pierre@0:   if (!$statistics['this_month']['views'] && !$statistics['this_month']['clicks']) {
pierre@0:     return $statistics;
pierre@0:   }
pierre@0: 
pierre@0:   // Get statistics for this week.
pierre@0:   $this_week_start = date('Ymd00', time() - 60*60*24*6);
pierre@0:   $statistics['this_week']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date > %d", $aid, $this_week_start));
pierre@0:   $statistics['this_week']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date > %d", $aid, $this_week_start));
pierre@0: 
pierre@0:   // No sense in making further queries if the ad has no statistics this week.
pierre@0:   if (!$statistics['this_week']['views'] && !$statistics['this_week']['clicks']) {
pierre@0:     return $statistics;
pierre@0:   }
pierre@0: 
pierre@0:   // Get statistics for yesterday and today.
pierre@0:   $yesterday_start = date('Ymd00', time() - 60*60*24);
pierre@0:   $yesterday_end = date('Ymd24', time() - 60*60*24);
pierre@0:   $today_start = date('Ymd00', time());
pierre@0:   $statistics['yesterday']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $aid, $yesterday_start, $yesterday_end));
pierre@0:   $statistics['yesterday']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d AND date <= %d", $aid, $yesterday_start, $yesterday_end));
pierre@0:   $statistics['today']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d", $aid, $today_start));
pierre@0:   $statistics['today']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d", $aid, $today_start));
pierre@0: 
pierre@0:   // No sense in making further queries if the ad has no statistics today.
pierre@0:   if (!$statistics['today']['views'] && !$statistics['today']['clicks']) {
pierre@0:     return $statistics;
pierre@0:   }
pierre@0: 
pierre@0:   // Get statistics for this hour and the last hour.
pierre@0:   $last_hour = date('YmdH', time() - 60*60);
pierre@0:   $this_hour = date('YmdH', time());
pierre@0:   $statistics['last_hour']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date = %d", $aid, $last_hour));
pierre@0:   $statistics['last_hour']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date = %d", $aid, $last_hour));
pierre@0:   $statistics['this_hour']['views'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date = %d", $aid, $this_hour));
pierre@0:   $statistics['this_hour']['clicks'] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date = %d", $aid, $this_hour));
pierre@0: 
pierre@0:   return $statistics;
pierre@0: }
pierre@0: 
pierre@0: function theme_ad_statistics_display($statistics) {
pierre@0:   $header = array('', t('Impressions'), t('Clicks'), t('Click-thru'));
pierre@0:   $rows = array();
pierre@0: 
pierre@0:   $data = array(
pierre@0:    'this_hour' => t('This hour'),
pierre@0:    'last_hour' => t('Last hour'),
pierre@0:    'today' => t('Today'),
pierre@0:    'yesterday' => t('Yesterday'),
pierre@0:    'this_week' => t('Last seven days'),
pierre@0:    'this_month' => t('This month'),
pierre@0:    'last_month' => t('Last month'),
pierre@0:    'this_year' => t('This year'),
pierre@0:    'last_year' => t('Last year'),
pierre@0:    'global' => t('All time')
pierre@0:   );
pierre@0: 
pierre@0:   foreach ($data as $key => $value) {
pierre@0:     if (isset($statistics[$key]) && (isset($statistics[$key]['views']) || isset($statistics[$key]['clicks']) || $key == 'global')) {
pierre@0:       $rows[] = array(
pierre@0:         array('data' => $value),
pierre@0:         array('data' => (int)$statistics[$key]['views']),
pierre@0:         array('data' => (int)$statistics[$key]['clicks']),
pierre@0:         array('data' => $statistics[$key]['views'] ? sprintf('%1.2f', ((int)$statistics[$key]['clicks'] / (int)$statistics[$key]['views']) * 100) .'%' : '0.00%'),
pierre@0:       );
pierre@0:     }
pierre@0:   }
pierre@0:   if (empty($rows) || (!$statistics['global']['views'] && !$statistics['global']['clicks'])) {
pierre@1:     $statistics = '<p>'. t('There are currently no statistics for this advertisement.') .'</p>';
pierre@0:   }
pierre@0:   else {
pierre@0:     $statistics = theme('table', $header, $rows);
pierre@0:   }
pierre@0: 
pierre@0:   return theme('box', t('Statistics'), $statistics);
pierre@0: }
pierre@0: 
pierre@1: /**
pierre@1:  * Display details about a specific click.
pierre@1:  */
pierre@0: function ad_click_details($node, $cid) {
pierre@0:   drupal_set_breadcrumb(array(l(t('Home'), NULL), l(check_plain($node->title), 'node/'. $node->nid)));
pierre@0:   if ($click = db_fetch_object(db_query('SELECT * FROM {ad_clicks} WHERE cid = %d', $cid))) {
pierre@0:     $ad = node_load($click->aid);
pierre@0:     $account = user_load(array('uid' => $click->uid));
pierre@0:     $rows = array(
pierre@0:       array(
pierre@0:         array('data' => t('Time'), 'header' => TRUE),
pierre@0:         format_date($click->timestamp, 'custom', 'D F j, Y h:i a'),
pierre@0:       ),
pierre@0:       array(
pierre@0:         array('data' => t('User'), 'header' => TRUE),
pierre@0:         theme('username', $account),
pierre@0:       ),
pierre@0:       array(
pierre@0:         array('data' => t('IP Address'), 'header' => TRUE),
pierre@0:         $click->hostname,
pierre@0:       ),
pierre@0:       array(
pierre@0:         array('data' => t('User Agent'), 'header' => TRUE),
pierre@0:         check_plain($click->user_agent),
pierre@0:       ),
pierre@0:       array(
pierre@0:         array('data' => t('URL'), 'header' => TRUE),
pierre@0:         l($click->url, $click->url),
pierre@0:       ),
pierre@0:       array(
pierre@0:         array('data' => t('Advertisement'), 'header' => TRUE),
pierre@0:         $ad->ad,
pierre@0:       )
pierre@0:     );
pierre@0:     if (function_exists('click_filter_status_text') && user_access('view filtered clicks')) {
pierre@0:       switch ($click->status) {
pierre@0:         case 0:
pierre@0:         default:
pierre@0:           $status = t('Not valid: this click has not been counted for unknown reasons.  This is an unexpected error.');
pierre@0:           break;
pierre@0:         case 1:
pierre@0:           $status = t('Valid: this is a valid click.');
pierre@0:           break;
pierre@0:         case 2:
pierre@0:           $status = t('Not valid: this click has not been counted because another click by the same IP address was already counted.');
pierre@0:           break;
pierre@0:         case 3:
pierre@0:           $status = t('Not valid: this click has not been counted because it was generated by an owner of the advertisement.');
pierre@0:           break;
pierre@0:         case 4:
pierre@0:           $status = t('Not valid: this click has not been counted because it was generated by a user in a filtered role.');
pierre@0:           break;
pierre@0:         case 5:
pierre@0:           $status = t('Not valid: this click has not been counted because it was generated by an automated "bot".');
pierre@0:           break;
pierre@0:       }
pierre@0:       $rows[] = array(array('data' => t('Status'), 'header' => TRUE), $status);
pierre@0:     }
pierre@0:     $output = theme('table', array(), $rows);
pierre@0:   }
pierre@0:   return $output;
pierre@0: }
pierre@0: 
pierre@0: