function ad_statistics in Advertisement 7

Same name and namespace in other branches

1. 5.2 ad.module \ad_statistics()
2. 5 ad.module \ad_statistics()
3. 6.3 ad.pages.inc \ad_statistics()
4. 6 ad.pages.inc \ad_statistics()
5. 6.2 ad.pages.inc \ad_statistics()

Calculate statistics for the given advertisements. TODO: Introduce caching to make this more efficient.

2 calls to ad_statistics()

ad_token_values in ./ad_token.inc

Implementation of hook_token_values().

theme_node_ad in ./ad.pages.inc

@file Advertisement nodes pages and forms.

File

./ad.pages.inc, line 84

Advertisement nodes pages and forms.

Code

function ad_statistics($aid) {

  // Gek global statistics.
  $statistics['global']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view'", array(
    ':aid' => $aid,
  ))
    ->fetchField();
  $statistics['global']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click'", array(
    ':aid' => $aid,
  ))
    ->fetchField();

  // No sense in making further queries if the ad has no global statistics.
  if (!$statistics['global']['views'] && !$statistics['global']['clicks']) {
    return $statistics;
  }

  // Get statistics for this year and last year.
  $this_year = date('Y000000');
  $last_year = date('Y') - 1 . '000000';
  $statistics['last_year']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date >= :mindate AND date <= :maxdate", array(
    ':aid' => $aid,
    ':mindate' => $last_year,
    ':maxdate' => $this_year,
  ))
    ->fetchField();
  $statistics['last_year']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date >= :mindate AND date <= :maxdate", array(
    ':aid' => $aid,
    ':mindate' => $last_year,
    ':maxdate' => $this_year,
  ))
    ->fetchField();
  $statistics['this_year']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date >= :mindate", array(
    ':aid' => $aid,
    ':mindate' => $this_year,
  ))
    ->fetchField();
  $statistics['this_year']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date >= :mindate", array(
    ':aid' => $aid,
    ':mindate' => $this_year,
  ))
    ->fetchField();

  // No sense in making further queries if the ad has no statistics this year.
  if (!$statistics['this_year']['views'] && !$statistics['this_year']['clicks']) {
    return $statistics;
  }

  // Get statistics for this month and last month.
  $this_month = date('Ym0000');
  $last_month = date('m') - 1;
  if ($last_month == 0) {
    $last_month = date('Y') - 1 . '120000';
  }
  else {
    $last_month = date('Y') . ($last_month < 10 ? '0' : '') . $last_month . '0000';
  }
  $statistics['last_month']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date >= :mindate AND date <= :maxdate", array(
    ':aid' => $aid,
    ':mindate' => $last_month,
    ':maxdate' => $this_month,
  ))
    ->fetchField();
  $statistics['last_month']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date >= :mindate AND date <= :maxdate", array(
    ':aid' => $aid,
    ':mindate' => $last_month,
    ':maxdate' => $this_month,
  ))
    ->fetchField();
  $statistics['this_month']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date >= :mindate", array(
    ':aid' => $aid,
    ':mindate' => $this_month,
  ))
    ->fetchField();
  $statistics['this_month']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date >= :mindate", array(
    ':aid' => $aid,
    ':mindate' => $this_month,
  ))
    ->fetchField();

  // No sense in making further queries if the ad has no statistics this month.
  if (!$statistics['this_month']['views'] && !$statistics['this_month']['clicks']) {
    return $statistics;
  }

  // Get statistics for this week.
  $this_week_start = date('Ymd00', time() - 60 * 60 * 24 * 6);
  $statistics['this_week']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date > :mindate", array(
    ':aid' => $aid,
    ':mindate' => $this_week_start,
  ))
    ->fetchField();
  $statistics['this_week']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date > :mindate", array(
    ':aid' => $aid,
    ':mindate' => $this_week_start,
  ))
    ->fetchField();

  // No sense in making further queries if the ad has no statistics this week.
  if (!$statistics['this_week']['views'] && !$statistics['this_week']['clicks']) {
    return $statistics;
  }

  // Get statistics for yesterday and today.
  $yesterday_start = date('Ymd00', time() - 60 * 60 * 24);
  $yesterday_end = date('Ymd24', time() - 60 * 60 * 24);
  $today_start = date('Ymd00', time());
  $statistics['yesterday']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date >= :mindate AND date <= :maxdate", array(
    ':aid' => $aid,
    ':mindate' => $yesterday_start,
    ':maxdate' => $yesterday_end,
  ))
    ->fetchField();
  $statistics['yesterday']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date >= :mindate AND date <= :maxdate", array(
    ':aid' => $aid,
    ':mindate' => $yesterday_start,
    ':maxdate' => $yesterday_end,
  ))
    ->fetchField();
  $statistics['today']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date >= :mindate", array(
    ':aid' => $aid,
    ':mindate' => $today_start,
  ))
    ->fetchField();
  $statistics['today']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date >= :mindate", array(
    ':aid' => $aid,
    ':mindate' => $today_start,
  ))
    ->fetchField();

  // No sense in making further queries if the ad has no statistics today.
  if (!$statistics['today']['views'] && !$statistics['today']['clicks']) {
    return $statistics;
  }

  // Get statistics for this hour and the last hour.
  $last_hour = date('YmdH', time() - 60 * 60);
  $this_hour = date('YmdH', time());
  $statistics['last_hour']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date = :date", array(
    ':aid' => $aid,
    ':date' => $last_hour,
  ))
    ->fetchField();
  $statistics['last_hour']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date = :date", array(
    ':aid' => $aid,
    ':date' => $last_hour,
  ))
    ->fetchField();
  $statistics['this_hour']['views'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'view' AND date = :date", array(
    ':aid' => $aid,
    ':date' => $this_hour,
  ))
    ->fetchField();
  $statistics['this_hour']['clicks'] = (int) db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = :aid AND action = 'click' AND date = :date", array(
    ':aid' => $aid,
    ':date' => $this_hour,
  ))
    ->fetchField();
  return $statistics;
}