function ad_report_group_table in Advertisement 5.2

Display table with per-group statistics.

1 call to ad_report_group_table()

ad_report_bargraph in report/ad_report.module

Page to display ad with bargraph.

File

report/ad_report.module, line 530

Provides comprehensive charts and reports about advertising statistics.

Code

function ad_report_group_table($id, $type, $start, $end) {
  $start_date = _ad_report_format_date_db(_ad_report_get_date_from_path($start));
  $end_date = _ad_report_format_date_db(_ad_report_get_date_from_path($end));

  // TODO: Support other types than nodes
  $result = db_query('SELECT DISTINCT(adgroup) FROM {ad_statistics} WHERE aid = %d AND date >= %d AND date <= %d', $id, $start_date, $end_date);

  // extract all groups that this advertisement has been displayed in
  while ($group = db_fetch_object($result)) {
    if ($group->adgroup) {
      $first = substr($group->adgroup, 0, 1);
      if ($first == 't') {
        $tids = $tids = explode(',', substr($group->adgroup, 1, strlen($group->adgroup)));
        foreach ($tids as $tid) {
          if ($tid) {
            $adgroups[$tid][] = $group->adgroup;
          }
        }
      }
      else {

        // handle this type of "group"
        $adgroups['other'][] = $group->adgroup;
      }
    }
    else {
      $adgroups[0][] = $group->adgroup;
    }
  }
  $headers = array(
    t('Group'),
    t('Active dates'),
    t('Views'),
    t('Clicks'),
    t('Click-thru'),
  );

  // get counts for each group
  $groups = ad_groups_list();
  $rows = array();
  $total_views = $total_clicks = 0;
  foreach ($groups as $tid => $group) {
    $views = $clicks = 0;
    if (is_array($adgroups[$tid])) {
      foreach ($adgroups[$tid] as $adgroup) {
        $views += (int) db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND adgroup = '%s' AND action = 'view' AND date >= %d AND date <= %d", $id, $adgroup, $start_date, $end_date));
        $clicks += (int) db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND adgroup = '%s' AND action = 'click' AND date >= %d AND date <= %d", $id, $adgroup, $start_date, $end_date));
      }
    }
    if ($views || $clicks) {
      $begin = (int) db_result(db_query("SELECT MIN(date) FROM {ad_statistics} WHERE (adgroup LIKE '%%t%s' OR adgroup LIKE '%%,%s') AND action = 'view' AND date >= %d AND date <= %d", $tid, $tid, $start_date, $end_date));
      if ($begin) {
        $begin = format_date(_ad_report_get_date_from_path($begin), 'small');
        $finish = (int) db_result(db_query("SELECT MAX(date) FROM {ad_statistics} WHERE (adgroup LIKE '%%t%s' OR adgroup LIKE '%%,%s') AND action = 'view' AND date >= %d AND date <= %d", $tid, $tid, $start_date, $end_date));
        if ($finish) {
          $finish = format_date(_ad_report_get_date_from_path($finish), 'small');
        }
      }
      if ($begin && $finish) {
        $row = array();
        $row[] = $group;
        $row[] = "first view: {$begin}<br />last view: {$finish}";
        $row[] = number_format($views);
        $row[] = number_format($clicks);
        if ($views) {
          $row[] = number_format($clicks / $views, 2) . '%';
        }
        else {
          $row[] = '0%';
        }
        $rows[] = $row;
        $total_views += $views;
        $total_clicks += $clicks;
      }
    }
  }
  if ($total_views || $total_clicks) {
    $row = array();
    $row[] = '<strong>' . t('Total') . '</strong>';
    $row[] = '';
    $row[] = '<strong>' . number_format($total_views) . '</strong>';
    $row[] = '<strong>' . number_format($total_clicks) . '</strong>';
    if ($total_views) {
      $row[] = '<strong>' . number_format($total_clicks / $total_views, 2) . '%' . '</strong>';
    }
    else {
      $row[] = '<strong>' . '0%' . '</strong>';
    }
    $rows[] = $row;
  }
  return theme('table', $headers, $rows);
}