pierre@0: <?php
pierre@1: // $Id: ad_report.module,v 1.1.2.3.2.7.2.6.2.7 2009/03/27 19:31:36 jeremy Exp $
pierre@0: 
pierre@0: /**
pierre@0:  * @file
pierre@0:  * Provides comprehensive charts and reports about advertising statistics.
pierre@0:  *
pierre@1:  * Copyright (c) 2007-2009.
pierre@0:  *   Jeremy Andrews <jeremy@tag1consulting.com>.
pierre@0:  */
pierre@0: 
pierre@0: /**
pierre@0:  * Implementation of hook_menu().
pierre@0:  */
pierre@0: function ad_report_menu() {
pierre@0:   $items = array();
pierre@1:   $items['admin/content/ad/report'] = array(
pierre@1:     'title' => t('Reports'),
pierre@1:     'page callback' => 'drupal_get_form',
pierre@1:     'page arguments' => array('ad_report_admin'),
pierre@1:     'access arguments' => array('generate administrative reports'),
pierre@1:     'type' => MENU_LOCAL_TASK,
pierre@1:     'weight' => 1
pierre@1:   );
pierre@1:   $items['admin/content/ad/report/display'] = array(
pierre@1:     'page callback' => 'ad_report_admin_display',
pierre@1:     'access arguments' => array('generate administrative reports'),
pierre@1:     'type' => MENU_CALLBACK
pierre@1:   );
pierre@1:   $items['admin/content/ad/report/csv'] = array(
pierre@1:     'page callback' => 'ad_report_admin_ad_table',
pierre@1:     'page arguments' => array('0', '0', array(), TRUE),
pierre@1:     'access arguments' => array('generate administrative reports'),
pierre@1:     'type' => MENU_CALLBACK
pierre@1:   );
pierre@0:   $items['node/%node/report'] = array(
pierre@1:     'title' => t('Reports'),
pierre@1:     'page callback' => 'ad_report_bargraph_handler',
pierre@0:     'page arguments' => array(1),
pierre@0:     'type' => MENU_LOCAL_TASK,
pierre@1:     'access callback' => 'ad_report_bargraph_access',
pierre@0:     'access arguments' => array(1),
pierre@0:   );
pierre@1:   $items['ad_report/%node/bargraph/node/%/%'] = array(
pierre@1:     'title' => 'Bar graph',
pierre@1:     'page callback' => 'ad_report_generate_bargraph',
pierre@1:     'page arguments' => array(1, 'node', 4, 5),
pierre@1:     'type' => MENU_CALLBACK,
pierre@1:     'access callback' => 'ad_report_bargraph_access',
pierre@0:     'access arguments' => array(1),
pierre@0:   );
pierre@1:   return $items;
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Drupal hook_perm implementation.
pierre@1:  */
pierre@1: function ad_report_perm() {
pierre@1:   return array(t('generate administrative reports'));
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Menu system callback, determine if current user can generate reports.
pierre@1:  */
pierre@1: function ad_report_bargraph_access($node) {
pierre@1:   if (isset($node->adtype)) {
pierre@1:     return ad_permission($node->nid, 'generate reports');
pierre@1:   }
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  *
pierre@1:  */
pierre@1: function ad_report_bargraph_handler($node) {
pierre@1:   return ad_report_bargraph($node, "node/$node->nid/report", 'node', arg(3), arg(4));
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Ad module hook_adapi.
pierre@1:  */
pierre@1: function ad_report_adapi($op, $node = NULL) {
pierre@1:   switch ($op) {
pierre@1:     case 'permissions':
pierre@1:       return array(
pierre@1:         'generate reports' => TRUE,
pierre@1:       );
pierre@1:   }
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  *
pierre@1:  */
pierre@1: function ad_report_admin() {
pierre@1:   $form = array();
pierre@1: 
pierre@1:   $start = isset($_SESSION['ad_report_start']) ? strtotime($_SESSION['ad_report_start']) : _ad_report_first_day_of_month();
pierre@1:   $end = isset($_SESSION['ad_report_end']) ? strtotime($_SESSION['ad_report_end']) : _ad_report_last_day_of_month();
pierre@1:   $group = isset($_SESSION['ad_report_group']) ? $_SESSION['ad_report_group'] : array('all');
pierre@1: 
pierre@1:   $form['dates'] = array(
pierre@1:     '#type' => 'fieldset',
pierre@1:     '#title' => t('Report dates'),
pierre@1:     '#prefix' => '<div class="container-inline">',
pierre@1:     '#suffix' => '</div>',
pierre@0:   );
pierre@1:   $form['dates']['start'] = array(
pierre@1:     '#type' => 'textfield',
pierre@1:     '#title' => t('Start'),
pierre@1:     '#size' => 24,
pierre@1:     '#maxlength' => 64,
pierre@1:     '#default_value' => _ad_report_format_date_human($start),
pierre@1:     // display pop up calendar if jstools jscalendar module enabled
pierre@1:     '#attributes' => array('class' => 'jscalendar'),
pierre@1:     '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
pierre@1:     '#jscalendar_timeFormat' => '24',
pierre@0:   );
pierre@1:   $form['dates']['space1'] = array(
pierre@1:     '#value' => '&nbsp;&nbsp;',
pierre@1:   );
pierre@1:   $form['dates']['end'] = array(
pierre@1:     '#type' => 'textfield',
pierre@1:     '#title' => t('End'),
pierre@1:     '#size' => 24,
pierre@1:     '#maxlength' => 64,
pierre@1:     '#default_value' => _ad_report_format_date_human($end),
pierre@1:     // display pop up calendar if jstools jscalendar module enabled
pierre@1:     '#attributes' => array('class' => 'jscalendar'),
pierre@1:     '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
pierre@1:   );
pierre@1:   $form['dates']['space2'] = array(
pierre@1:     '#value' => '&nbsp;&nbsp;&nbsp;',
pierre@0:   );
pierre@0: 
pierre@1:   // groups
pierre@1:   $groups = ad_groups_list();
pierre@1:   $form['groups'] = array(
pierre@1:     '#type' => 'fieldset',
pierre@1:     '#title' => t('Groups'),
pierre@1:   );
pierre@1:   $options = array();
pierre@1:   $options['all'] = t('- All -');
pierre@1:   $options = $options + $groups;
pierre@1:   $form['groups']['group'] = array(
pierre@1:     '#type' => 'select',
pierre@1:     '#title' => t('Ad groups'),
pierre@1:     '#options' => $options,
pierre@1:     '#multiple' => TRUE,
pierre@1:     '#required' => TRUE,
pierre@1:     '#default_value' => $group,
pierre@1:   );
pierre@1: 
pierre@1:   // submit
pierre@1:   $form['submit'] = array(
pierre@1:     '#type' => 'submit',
pierre@1:     '#value' => t('Generate report'),
pierre@1:     '#weight' => 10,
pierre@1:   );
pierre@1:   $form['reset'] = array(
pierre@1:     '#type' => 'submit',
pierre@1:     '#value' => t('Reset report'),
pierre@1:     '#weight' => 10,
pierre@1:   );
pierre@1: 
pierre@1:   return $form;
pierre@0: }
pierre@1: 
pierre@0: /**
pierre@1:  * Sanity check the date range.
pierre@1:  */
pierre@1: function ad_report_admin_validate($form, $form_state) {
pierre@1:   if ($form_state['clicked_button']['#value'] == t('Reset report')) {
pierre@1:     unset($_SESSION['ad_report_start']);
pierre@1:     unset($_SESSION['ad_report_end']);
pierre@1:     unset($_SESSION['ad_report_group']);
pierre@1:   }
pierre@1:   else {
pierre@1:     $start = isset($form_state['values']['start']) ? strtotime($form_state['values']['start']) : 0;
pierre@1:     $end = isset($form_state['values']['start']) ? strtotime($form_state['values']['end']) : 0;
pierre@1:     if (!$start) {
pierre@1:       form_set_error('start', t('You must enter a valid start date.'));
pierre@1:     }
pierre@1:     else if ($start >= (time() - 3600)) {
pierre@1:       form_set_error('start', t('The report must start at least one hour before the current time.'));
pierre@1:     }
pierre@1:     else if ($start >= $end) {
pierre@1:       form_set_error('start', t('The report must start before it ends.'));
pierre@1:     }
pierre@1:     if (!$end) {
pierre@1:       form_set_error('end', t('You must enter a valid end date.'));
pierre@1:     }
pierre@1:   }
pierre@0: }
pierre@1: 
pierre@1: /**
pierre@1:  * Redirect to a path to generate the requested report.
pierre@1:  */
pierre@1: function ad_report_admin_submit($form, $form_state) {
pierre@1:   if ($form_state['clicked_button']['#value'] == t('Generate report')) {
pierre@1:     $start = date('YmdHi', strtotime($form_state['values']['start']));
pierre@1:     $end = date('YmdHi', strtotime($form_state['values']['end']));
pierre@1:     $group = $form_state['values']['group'];
pierre@1:     $_SESSION['ad_report_start'] = $start;
pierre@1:     $_SESSION['ad_report_end'] = $end;
pierre@1:     $_SESSION['ad_report_group'] = $group;
pierre@1: 
pierre@1:     drupal_goto('admin/content/ad/report/display');
pierre@1:   }
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Display the administrative report.
pierre@1:  */
pierre@1: function ad_report_admin_display() {
pierre@1:   $start = isset($_SESSION['ad_report_start']) ? $_SESSION['ad_report_start'] : 0;
pierre@1:   $end = isset($_SESSION['ad_report_end']) ? $_SESSION['ad_report_end'] : 0;
pierre@1:   $group = isset($_SESSION['ad_report_group']) ? $_SESSION['ad_report_group'] : array();
pierre@1:   if (!$start && !$end) {
pierre@1:     drupal_goto('admin/content/ad/report');
pierre@1:   }
pierre@1:   $output  = '<div class="image"><img src="'. url("ad_report/0/bargraph/admin/$start/$end") .'" /></div>';
pierre@1:   $output .= ad_report_admin_ad_table(strtotime($start), strtotime($end), $group);
pierre@1:   $output .= '<div>'. l(t('Modify report'), 'admin/content/ad/report') .'</div>';
pierre@1:   return $output;
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  *
pierre@1:  */
pierre@1: function ad_report_admin_ad_table($start = 0, $end = 0, $group = array(), $csv = FALSE) {
pierre@1:   if (!$start) {
pierre@1:     $start = isset($_SESSION['ad_report_start']) ? strtotime($_SESSION['ad_report_start']) : 0;
pierre@1:   }
pierre@1:   if (!$end) {
pierre@1:     $end = isset($_SESSION['ad_report_end']) ? strtotime($_SESSION['ad_report_end']) : 0;
pierre@1:   }
pierre@1:   if (empty($group)) {
pierre@1:     $group = isset($_SESSION['ad_report_group']) ? $_SESSION['ad_report_group'] : array();
pierre@1:   }
pierre@1:   // prepare dates
pierre@1:   $start = _ad_report_format_date_db($start);
pierre@1:   $end = _ad_report_format_date_db($end);
pierre@1: 
pierre@1:   // prepare groups
pierre@1:   $groups = ad_groups_list();
pierre@1:   $all = FALSE;
pierre@1:   $none = FALSE;
pierre@1:   if (is_array($group)) {
pierre@1:     if (in_array('all', $group)) {
pierre@1:       $all = TRUE;
pierre@1:     }
pierre@1:     if (!$all) {
pierre@1:       if (sizeof($group) == sizeof($groups)) {
pierre@1:         $all = TRUE;
pierre@1:       }
pierre@1:     }
pierre@1:     if (in_array('0', $group)) {
pierre@1:       unset($group[0]);
pierre@1:       $none = TRUE;
pierre@1:     }
pierre@1:   }
pierre@1: 
pierre@1:   $select = 'SELECT DISTINCT(aid) as nid FROM {ad_statistics} a';
pierre@1:   if ($all) {
pierre@1:     $where = array(
pierre@1:       "a.action = 'view'",
pierre@1:       'a.date >= %d',
pierre@1:       'a.date <= %d',
pierre@1:       'a.aid > 0',
pierre@1:     );
pierre@1:     $join = array();
pierre@1:     $args = array($start, $end);
pierre@1:   }
pierre@1:   else if ($none) {
pierre@1:     if (sizeof($group)) {
pierre@1:       $where = array(
pierre@1:         '(t.tid IN (%s) OR ISNULL(t.tid))',
pierre@1:         "a.action = 'view'",
pierre@1:         'a.date >= %d',
pierre@1:         'a.date <= %d',
pierre@1:       );
pierre@1:       $join = array(
pierre@1:         'LEFT JOIN {term_node} t ON a.aid = t.tid',
pierre@1:       );
pierre@1:       $args = array(implode(',', $group), $start, $end);
pierre@1:     }
pierre@1:     else {
pierre@1:       $where = array(
pierre@1:         'ISNULL(t.tid)',
pierre@1:         "a.action = 'view'",
pierre@1:         'a.date >= %d',
pierre@1:         'a.date <= %d',
pierre@1:       );
pierre@1:       $join = array(
pierre@1:         'LEFT JOIN {term_node} t ON a.aid = t.tid',
pierre@1:       );
pierre@1:       $args = array($start, $end);
pierre@1:     }
pierre@1:   }
pierre@1:   else {
pierre@1:     $where = array(
pierre@1:       't.tid IN (%s)',
pierre@1:       "a.action = 'view'",
pierre@1:       'a.date >= %d',
pierre@1:       'a.date <= %d',
pierre@1:     );
pierre@1:     $join = array(
pierre@1:       'LEFT JOIN {term_node} t ON a.aid = t.tid',
pierre@1:     );
pierre@1:     $args = array(implode(',', $group), $start, $end);
pierre@1:   }
pierre@1: 
pierre@1:   $return = module_invoke_all('adreport', $join, $where, $args, $select);
pierre@1:   foreach ($return as $type => $value) {
pierre@1:     switch ($type) {
pierre@1:       case 'join':
pierre@1:         if (is_array($value)) {
pierre@1:           foreach ($value as $option) {
pierre@1:             $join[] = $option;
pierre@1:           }
pierre@1:         }
pierre@1:         break;
pierre@1:       case 'where':
pierre@1:         if (is_array($value)) {
pierre@1:           foreach ($value as $option) {
pierre@1:             $where[] = $option;
pierre@1:           }
pierre@1:         }
pierre@1:         break;
pierre@1:       case 'args':
pierre@1:         if (is_array($value)) {
pierre@1:           foreach ($value as $option) {
pierre@1:             $args[] = $option;
pierre@1:           }
pierre@1:         }
pierre@1:         break;
pierre@1:     }
pierre@1:   }
pierre@1: 
pierre@1:   // Build the query.
pierre@1:   $query = $select .' '. implode(' ', $join) .' WHERE '. implode(' AND ', $where);
pierre@1:   $ads = array();
pierre@1:   $result = db_query($query, $args);
pierre@1:   while ($ad = db_fetch_object($result)) {
pierre@1:     if ($ad->nid) {
pierre@1:       $ads[$ad->nid] = $ad->nid;
pierre@1:     }
pierre@1:   }
pierre@1: 
pierre@1:   if ($csv) {
pierre@1:     header('Content-type: application/octet-stream');
pierre@1:     header("Content-Disposition: attachment; filename=report-$start-$end.csv");
pierre@1:     echo "ad id, title, first view, last view, clicks, views, click-thru\n";
pierre@1:   }
pierre@1:   else {
pierre@1:     $output = '<div class="describe">' . t('There !count matching your parameters.', array('!count' => format_plural(sizeof($ads), 'was 1 active ad', 'were @count active ads'))) . '</div>';
pierre@1: 
pierre@1:     $headers = array(t('Advertisement'), t('Active dates'), t('Views'), t('Clicks'), t('Click-thru'));
pierre@1:     // get counts for each ad
pierre@1:     $rows = array();
pierre@1:   }
pierre@1:   $total_views = $total_clicks = 0;
pierre@1:   foreach ($ads as $nid) {
pierre@1:     $ad = node_load($nid);
pierre@1:     if ($ad->nid) {
pierre@1:       $views = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $nid, $start, $end));
pierre@1:       $first = _ad_report_get_date_from_path((int)db_result(db_query("SELECT MIN(date) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $nid, $start, $end)));
pierre@1:       $first = format_date($first, 'small');
pierre@1:       $last = _ad_report_get_date_from_path((int)db_result(db_query("SELECT MAX(date) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $nid, $start, $end)));
pierre@1:       $last = format_date($last, 'small');
pierre@1:       $clicks = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d AND date <= %d", $nid, $start, $end));
pierre@1:       if ($views) {
pierre@1:         $clickthru = number_format($clicks / $views, 2) .'%';
pierre@1:       }
pierre@1:       else {
pierre@1:         $clickthru = '0%';
pierre@1:       }
pierre@1:       if ($views || $clicks) {
pierre@1:         if ($csv) {
pierre@1:           echo "$ad->nid, $ad->title, $first, $last, $views, $clicks, $clickthru\n";
pierre@1:         }
pierre@1:         else {
pierre@1:           $row = array();
pierre@1:           $row[] = l($ad->title, "node/$ad->nid");
pierre@1:           $row[] = "first view: $first<br />last view: $last";
pierre@1:           $row[] = number_format($views);
pierre@1:           $row[] = number_format($clicks);
pierre@1:           $row[] = $clickthru;
pierre@1:           $rows[] = $row;
pierre@1:           $total_views += $views;
pierre@1:           $total_clicks += $clicks;
pierre@1:         }
pierre@1:       }
pierre@1:     }
pierre@1:   }
pierre@1:   if ($csv) {
pierre@1:     return (0);
pierre@1:   }
pierre@1:   if ($total_views || $total_clicks) {
pierre@1:     $row = array();
pierre@1:     $row[] = '<strong>'. t('Total') .'</strong>';
pierre@1:     $row[] = '';
pierre@1:     $row[] = '<strong>'. number_format($total_views) .'</strong>';
pierre@1:     $row[] = '<strong>'. number_format($total_clicks) .'</strong>';
pierre@1:     if ($total_views) {
pierre@1:       $row[] = '<strong>'. number_format($total_clicks / $total_views, 2) .'%' .'</strong>';
pierre@1:     }
pierre@1:     else {
pierre@1:       $row[] = '<strong>'. '0%' .'</strong>';
pierre@1:     }
pierre@1:     $rows[] = $row;
pierre@1:   }
pierre@1:   $output  .= theme('table', $headers, $rows);
pierre@1:   $output .= l(t('Download CSV'), 'admin/content/ad/report/csv');
pierre@1:   return $output;
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Returns a timestamp for the first hour of the first day of the month.
pierre@1:  */
pierre@1: function _ad_report_first_day_of_month($time = NULL) {
pierre@1:   if ($time === NULL) {
pierre@1:     $time = time();
pierre@1:   }
pierre@1:   return strtotime(date('Ym010000', $time));
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Returns a timestamp for the last hour of the last day of the month.
pierre@1:  */
pierre@1: function _ad_report_last_day_of_month($time = NULL) {
pierre@1:   if ($time === NULL) {
pierre@1:     $time = time();
pierre@1:   }
pierre@1:   $month = date('m', $time);
pierre@1:   $year = date('Y', $time);
pierre@1:   $day = date('d', mktime(0, 0, 0, ($month + 1), 0, $year));
pierre@1:   return strtotime("{$year}{$month}{$day}2359");
pierre@1: }
pierre@1: 
pierre@0: /**
pierre@0:  * Page to display ad with bargraph.
pierre@0:  */
pierre@1: function ad_report_bargraph($data, $url, $type = 'node', $start = 0, $end = 0) {
pierre@1:   if ($type == 'node') {
pierre@1:     drupal_set_title($data->title);
pierre@0:   }
pierre@1:   $start_date = _ad_report_get_date_from_path($start);
pierre@1:   $end_date = _ad_report_get_date_from_path($end);
pierre@1:   $output = drupal_get_form('ad_report_range_form', $type, $url, $start_date, $end_date);
pierre@1:   if ($start && $end) {
pierre@1:     switch ($type) {
pierre@1:       case 'node':
pierre@1:         $ad = db_fetch_object(db_query('SELECT aid, redirect, adtype FROM {ads} WHERE aid = %d', $data->nid));
pierre@1:         if ($ad->aid) {
pierre@1:           $output .= '<img src="'. url("ad_report/$data->nid/bargraph/node/$start/$end") .'" />';
pierre@1:           $output .= theme('box', '', module_invoke("ad_$data->adtype", 'display_ad', $ad));
pierre@1:           $output .= ad_report_group_table($data->nid, $type, $start, $end);
pierre@1:         }
pierre@1:         $output .= module_invoke('ad', 'click_history', $data->nid);
pierre@1:         break;
pierre@1:       default:
pierre@1:         $output = '<img src="'. url("ad_report/$data->uid/bargraph/$granularity/$type") .'" />';
pierre@1:         break;
pierre@1:     }
pierre@0:   }
pierre@0:   return $output;
pierre@0: }
pierre@0: 
pierre@0: /**
pierre@1:  * Return a form for selecting a date range for generating a report.
pierre@1:  */
pierre@1: function ad_report_range_form($form_state, $type, $url = NULL, $start = NULL, $end = NULL) {
pierre@1:   $form = array();
pierre@1: 
pierre@1:   $start = $start ? $start : _ad_report_first_day_of_month();
pierre@1:   $end = $end ? $end : _ad_report_last_day_of_month();
pierre@1: 
pierre@1:   $form['report'] = array(
pierre@1:     '#type' => 'fieldset',
pierre@1:     '#title' => t('Report dates'),
pierre@1:     '#prefix' => '<div class="container-inline">',
pierre@1:     '#suffix' => '</div>',
pierre@1:   );
pierre@1:   $form['report']['type'] = array(
pierre@1:     '#value' => $type,
pierre@1:     '#type' => 'hidden',
pierre@1:   );
pierre@1:   $form['report']['url'] = array(
pierre@1:     '#value' => $url,
pierre@1:     '#type' => 'hidden',
pierre@1:   );
pierre@1:   $form['report']['start'] = array(
pierre@1:     '#type' => 'textfield',
pierre@1:     '#title' => t('Start'),
pierre@1:     '#size' => 24,
pierre@1:     '#maxlength' => 64,
pierre@1:     '#default_value' => _ad_report_format_date_human($start),
pierre@1:     // display pop up calendar if jstools jscalendar module enabled
pierre@1:     '#attributes' => array('class' => 'jscalendar'),
pierre@1:     '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
pierre@1:     '#jscalendar_timeFormat' => '24',
pierre@1:   );
pierre@1:   $form['report']['space1'] = array(
pierre@1:     '#value' => '&nbsp;&nbsp;',
pierre@1:   );
pierre@1:   $form['report']['end'] = array(
pierre@1:     '#type' => 'textfield',
pierre@1:     '#title' => t('End'),
pierre@1:     '#size' => 24,
pierre@1:     '#maxlength' => 64,
pierre@1:     '#default_value' => _ad_report_format_date_human($end),
pierre@1:     // display pop up calendar if jstools jscalendar module enabled
pierre@1:     '#attributes' => array('class' => 'jscalendar'),
pierre@1:     '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
pierre@1:   );
pierre@1:   $form['report']['space2'] = array(
pierre@1:     '#value' => '&nbsp;&nbsp;&nbsp;',
pierre@1:   );
pierre@1:   $form['report']['generate'] = array(
pierre@1:     '#type' => 'submit',
pierre@1:     '#value' => t('Generate report'),
pierre@1:   );
pierre@1: 
pierre@1:   return $form;
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Validate the form range.
pierre@1:  */
pierre@1: function ad_report_range_form_validate($form, $form_state) {
pierre@1:   $start = isset($form_state['values']['start']) ? strtotime($form_state['values']['start']) : 0;
pierre@1:   $end = isset($form_state['values']['start']) ? strtotime($form_state['values']['end']) : 0;
pierre@1:   if (!$start) {
pierre@1:     form_set_error('start', t('You must enter a valid start date.'));
pierre@1:   }
pierre@1:   else if ($start >= (time() - 3600)) {
pierre@1:     form_set_error('start', t('The report must start at least one hour before the current time.'));
pierre@1:   }
pierre@1:   else if ($start >= $end) {
pierre@1:     form_set_error('start', t('The report must start before it ends.'));
pierre@1:   }
pierre@1:   if (!$end) {
pierre@1:     form_set_error('end', t('You must enter a valid end date.'));
pierre@1:   }
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Redirect to URL for displaying report.
pierre@1:  */
pierre@1: function ad_report_range_form_submit($form, $form_state) {
pierre@1:   $start = date('YmdHi', strtotime($form_state['values']['start']));
pierre@1:   $end = date('YmdHi', strtotime($form_state['values']['end']));
pierre@1:   drupal_goto($form_state['values']['url'] ."/$start/$end");
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Helper function to extract date from URL.
pierre@1:  */
pierre@1: function _ad_report_get_date_from_path($path) {
pierre@1:   if (isset($path) && $path) {
pierre@1:     $year = substr($path, 0, 4);
pierre@1:     $month = substr($path, 4, 2);
pierre@1:     $day = substr($path, 6, 2);
pierre@1:     $hour = substr($path, 8, 2);
pierre@1:     if (strlen($path) == 12) {
pierre@1:       $minute = substr($path, 10, 2);
pierre@1:     }
pierre@1:     else {
pierre@1:       $minute = 0;
pierre@1:     }
pierre@1:     $date =  strtotime("$month/$day/$year $hour:$minute");
pierre@1:     if ($date > 0) {
pierre@1:       return $date;
pierre@1:     }
pierre@1:     drupal_set_message(t('Invalid date specified in range.'), 'error');
pierre@1:   }
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Helper function to format date.
pierre@1:  */
pierre@1: function _ad_report_format_date_human($date) {
pierre@1:   return date('Y-m-d H:i', $date);
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Helper function to format date.
pierre@1:  */
pierre@1: function _ad_report_format_date_db($date) {
pierre@1:   return date('YmdH', $date);
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Display table with per-group statistics.
pierre@1:  */
pierre@1: function ad_report_group_table($id, $type, $start, $end) {
pierre@1:   $start_date = _ad_report_format_date_db(_ad_report_get_date_from_path($start));
pierre@1:   $end_date = _ad_report_format_date_db(_ad_report_get_date_from_path($end));
pierre@1:   // TODO: Support other types than nodes
pierre@1:   $result = db_query('SELECT DISTINCT(adgroup) FROM {ad_statistics} WHERE aid = %d AND date >= %d AND date <= %d', $id, $start_date, $end_date);
pierre@1:   // extract all groups that this advertisement has been displayed in
pierre@1:   while ($group = db_fetch_object($result)) {
pierre@1:     if ($group->adgroup) {
pierre@1:       $first = substr($group->adgroup, 0, 1);
pierre@1:       if ($first == 't') {
pierre@1:         $tids = $tids = explode(',', substr($group->adgroup, 1, strlen($group->adgroup)));
pierre@1:         foreach ($tids as $tid) {
pierre@1:           if ($tid) {
pierre@1:             $adgroups[$tid][] = $group->adgroup;
pierre@1:           }
pierre@1:         }
pierre@1:       }
pierre@1:       else {
pierre@1:         // handle this type of "group"
pierre@1:         $adgroups['other'][] = $group->adgroup;
pierre@1:       }
pierre@1:     }
pierre@1:     else {
pierre@1:       $adgroups[0][] = $group->adgroup;
pierre@1:     }
pierre@1:   }
pierre@1:   $headers = array(t('Group'), t('Active dates'), t('Views'), t('Clicks'), t('Click-thru'));
pierre@1:   // get counts for each group
pierre@1:   $groups = ad_groups_list();
pierre@1:   $rows = array();
pierre@1:   $total_views = $total_clicks = 0;
pierre@1:   foreach ($groups as $tid => $group) {
pierre@1:     $views = $clicks = 0;
pierre@1:     if (isset($adgroups[$tid]) && is_array($adgroups[$tid])) {
pierre@1:       foreach ($adgroups[$tid] as $adgroup) {
pierre@1:         $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));
pierre@1:         $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));
pierre@1:       }
pierre@1:     }
pierre@1:     if ($views || $clicks) {
pierre@1:       $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));
pierre@1:       if ($begin) {
pierre@1:         $begin = format_date(_ad_report_get_date_from_path($begin), 'small');
pierre@1:         $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));
pierre@1:         if ($finish) {
pierre@1:           $finish = format_date(_ad_report_get_date_from_path($finish), 'small');
pierre@1:         }
pierre@1:       }
pierre@1:       if ($begin && $finish) {
pierre@1:         $row = array();
pierre@1:         $row[] = $group;
pierre@1:         $row[] = "first view: $begin<br />last view: $finish";
pierre@1:         $row[] = number_format($views);
pierre@1:         $row[] = number_format($clicks);
pierre@1:         if ($views) {
pierre@1:           $row[] = number_format($clicks / $views, 2) .'%';
pierre@1:         }
pierre@1:         else {
pierre@1:           $row[] = '0%';
pierre@1:         }
pierre@1:         $rows[] = $row;
pierre@1:         $total_views += $views;
pierre@1:         $total_clicks += $clicks;
pierre@1:       }
pierre@1:     }
pierre@1:   }
pierre@1:   if ($total_views || $total_clicks) {
pierre@1:     $row = array();
pierre@1:     $row[] = '<strong>'. t('Total') .'</strong>';
pierre@1:     $row[] = '';
pierre@1:     $row[] = '<strong>'. number_format($total_views) .'</strong>';
pierre@1:     $row[] = '<strong>'. number_format($total_clicks) .'</strong>';
pierre@1:     if ($total_views) {
pierre@1:       $row[] = '<strong>'. number_format($total_clicks / $total_views, 2) .'%' .'</strong>';
pierre@1:     }
pierre@1:     else {
pierre@1:       $row[] = '<strong>'. '0%' .'</strong>';
pierre@1:     }
pierre@1:     $rows[] = $row;
pierre@1:   }
pierre@1: 
pierre@1:   return theme('table', $headers, $rows);
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@0:  * Page that utilizes gd to generate a bargraph.
pierre@0:  */
pierre@1: function ad_report_generate_bargraph($id, $type, $start, $end) {
pierre@0:   header("Content-type: image/png");
pierre@0: 
pierre@1:   if ($type == 'node' && is_object($id)) {
pierre@1:     $id = $id->nid;
pierre@1:   }
pierre@1:   $start = _ad_report_get_date_from_path($start);
pierre@1:   $end = _ad_report_get_date_from_path($end);
pierre@1: 
pierre@1:   // be sure we've been passed in valid parameters
pierre@1:   $elapse = $end - $start;
pierre@1:   if ($elapse <= 0 || $start <= 0 || $end <= 0) {
pierre@1:     return NULL;
pierre@1:   }
pierre@1:   $increments = (int)($elapse / 3600);
pierre@1: 
pierre@1:   // image size
pierre@1:   $image_width = 700;
pierre@1:   $image_height = 360;
pierre@1: 
pierre@1:   // graph size
pierre@1:   $graph_width = 600;
pierre@1:   $graph_height = 250;
pierre@1:   $graph_x_offset = 8;
pierre@1:   $graph_y_offset = 8;
pierre@1:   $graph_y = 8;
pierre@1: 
pierre@1:   // calculate slices to extract from database
pierre@1:   $width = $graph_width / $increments;
pierre@1:   $number = $increments;
pierre@1:   $factor = 1;
pierre@1:   if ($width < 1) {
pierre@1:     $factor = 1 / $width;
pierre@1:   }
pierre@1:   $number = $number / $factor;
pierre@1:   $width = $width * $factor;
pierre@1:   $slice = $elapse / $number;
pierre@1: 
pierre@1:   // retrieve views and clicks from the database
pierre@0:   $views = array();
pierre@1:   $clicks = array();
pierre@0:   $max_views = 0;
pierre@0:   $max_clicks = 0;
pierre@1:   $key = 0;
pierre@1:   for ($i = $start; $i < $end; $i += $slice) {
pierre@1:     $start_date = _ad_report_format_date_db($i);
pierre@1:     $end_date = _ad_report_format_date_db($i + $slice);
pierre@1:     switch ($type) {
pierre@1:       case 'node':
pierre@1:         $views[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'view' AND date >= %d AND date <= %d", $id, $start_date, $end_date));
pierre@1:         $clicks[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE aid = %d AND action = 'click' AND date >= %d AND date <= %d", $id, $start_date, $end_date));
pierre@1:         break;
pierre@1:       case 'user':
pierre@1:         $views[] = (int)db_result(db_query("SELECT SUM(a.count) FROM {ad_statistics} a LEFT JOIN {node} n ON a.aid = n.nid WHERE n.uid = %d AND n.type = 'ad' AND a.action = 'view' AND a.date >= %d AND a.date <= %d", $id, $start_date, $end_date));
pierre@1:         $clicks[] = (int)db_result(db_query("SELECT SUM(a.count) FROM {ad_statistics} a LEFT JOIN {node} n ON a.aid = n.nid WHERE n.uid = %d AND n.type = 'ad' AND a.action = 'click' AND a.date >= %d AND a.date <= %d", $id, $start_date, $end_date));
pierre@1:         break;
pierre@1:       case 'admin':
pierre@1:         $group = $_SESSION['ad_report_group'];
pierre@1:         $all = FALSE;
pierre@1:         $none = FALSE;
pierre@1:         if (is_array($group)) {
pierre@1:           if (in_array('all', $group)) {
pierre@1:             $all = TRUE;
pierre@1:           }
pierre@1:           if (!$all) {
pierre@1:             $groups = ad_groups_list();
pierre@1:             if (sizeof($group) == sizeof($groups)) {
pierre@1:               $all = TRUE;
pierre@1:             }
pierre@1:           }
pierre@1:           if (in_array('0', $group)) {
pierre@1:             unset($group[0]);
pierre@1:             $none = TRUE;
pierre@1:           }
pierre@1:         }
pierre@1:         if ($all) {
pierre@1:           $views[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE action = 'view' AND date >= %d AND date <= %d", $start_date, $end_date));
pierre@1:           $clicks[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} WHERE action = 'click' AND date >= %d AND date <= %d", $start_date, $end_date));
pierre@1:         }
pierre@1:         else if ($none) {
pierre@1:           if (sizeof($group)) {
pierre@1:             $views[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} a LEFT JOIN {term_node} t ON a.aid = t.tid WHERE (t.tid IN (%s) OR ISNULL(t.tid)) AND action = 'view' AND date >= %d AND date <= %d", implode(',', $group), $start_date, $end_date));
pierre@1:             $clicks[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} a LEFT JOIN {term_node} t ON a.aid = t.tid WHERE (t.tid IN (%s) OR ISNULL(t.tid)) AND action = 'click' AND date >= %d AND date <= %d", implode(',', $group), $start_date, $end_date));
pierre@1:           }
pierre@1:           else {
pierre@1:             $views[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} a LEFT JOIN {term_node} t ON a.aid = t.tid WHERE ISNULL(t.tid) AND action = 'view' AND date >= %d AND date <= %d", $start_date, $end_date));
pierre@1:             $clicks[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} a LEFT JOIN {term_node} t ON a.aid = t.tid WHERE ISNULL(t.tid) AND action = 'click' AND date >= %d AND date <= %d", $start_date, $end_date));
pierre@1:           }
pierre@1:         }
pierre@1:         else {
pierre@1:           $views[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} a LEFT JOIN {term_node} t ON a.aid = t.tid WHERE tid IN (%s) AND action = 'view' AND date >= %d AND date <= %d", implode(',', $group), $start_date, $end_date));
pierre@1:           $clicks[] = (int)db_result(db_query("SELECT SUM(count) FROM {ad_statistics} a LEFT JOIN {term_node} t ON a.aid = t.tid WHERE t.tid IN (%s) AND action = 'click' AND date >= %d AND date <= %d", implode(',', $group), $start_date, $end_date));
pierre@1:         }
pierre@1:         break;
pierre@1:       default:
pierre@1:         $function = "ad_report_views_$type";
pierre@1:         if (function_exists("$function")) {
pierre@1:           $views[] = $function($id, $day_start, $day_end);
pierre@1:         }
pierre@1:         $function = "ad_report_clicks_$type";
pierre@1:         if (function_exists("$function")) {
pierre@1:           $clicks[] = $function($id, $day_start, $day_end);
pierre@1:         }
pierre@1:         break;
pierre@1:     }
pierre@1:     $max_views =  $views[$key] > $max_views ? $views[$key] : $max_views;
pierre@1:     $max_clicks =  $clicks[$key] > $max_clicks ? $clicks[$key] : $max_clicks;
pierre@1:     $key++;
pierre@0:   }
pierre@0: 
pierre@1:   // create graph
pierre@0:   $graph = imagecreate($image_width, $image_height);
pierre@0: 
pierre@1:   // configure colors to use in chart
pierre@0:   $color = array(
pierre@0:     'white' => imagecolorallocate($graph, 255, 255, 255),
pierre@0:     'black' => imagecolorallocate($graph, 0, 0, 0),
pierre@0:     'grey' => imagecolorallocate($graph, 192, 192, 192),
pierre@0:     'blue' => imagecolorallocate($graph, 0, 0, 255),
pierre@0:     'orange' => imagecolorallocate($graph, 220, 210, 60),
pierre@1:     'red' => imagecolorallocate($graph, 255, 0, 0),
pierre@0:   );
pierre@0: 
pierre@1:   // determine how big the spacers should be
pierre@1:   $max = $max_views > $max_clicks ? $max_views : $max_clicks;
pierre@1:   $y_map = ceil($max / $graph_y / $graph_y) * $graph_y;
pierre@1:   $y_total = $y_map * $graph_y;
pierre@0: 
pierre@1:   if ($y_total) {
pierre@1:     // plot views and clicks on graph
pierre@1:     foreach ($views as $key => $value) {
pierre@1:       $view_height = $graph_height / $y_total * $value;
pierre@1:       if ($view_height) {
pierre@1:         imagefilledrectangle($graph, $graph_x_offset + $key * $width, $graph_y_offset + $graph_height - $view_height, $graph_x_offset + ($key + 1) * $width - 1, $graph_y_offset + $graph_height - 1, $color['blue']);
pierre@1:       }
pierre@1:       $click_height = $graph_height / $y_total * $clicks[$key];
pierre@1:       if ($click_height) {
pierre@1:         imagefilledrectangle($graph, $graph_x_offset + $key * $width, $graph_y_offset + $graph_height - $click_height, $graph_x_offset + ($key + 1) * $width - 1, $graph_y_offset + $graph_height - 1, $color['red']);
pierre@1:       }
pierre@0:     }
pierre@0:   }
pierre@0: 
pierre@1:   // add scale to y
pierre@1:   if ($y_map) {
pierre@1:     $graph_y_width = $graph_height / $graph_y;
pierre@1:     for ($i = 1; $i <= $graph_y; $i++) {
pierre@1:       $text = number_format($i * $y_map);
pierre@1:       $len = strlen($text);
pierre@1:       $x_offset = $graph_width + 14;
pierre@1:       $y_pos = $graph_height - $i * $graph_y_width;
pierre@1:       //imagestring($graph, 1, $x_offset, $graph_y_offset + $y_pos - 3, $text, $color['black']);
pierre@1:       imagestring($graph, 2, $x_offset, $graph_y_offset + $y_pos - 7, $text, $color['black']);
pierre@0:     }
pierre@1:   }
pierre@1:   else {
pierre@1:     $graph_y_width = 0;
pierre@0:   }
pierre@0: 
pierre@1:   // add scale to x
pierre@1:   $graph_x = _ad_report_select_x($number, 8, 0);
pierre@1:   $offset = $elapse / $graph_x;
pierre@1:   $graph_x_width = $graph_width / $graph_x;
pierre@1:   $x_offset = $graph_x_width / 2;
pierre@1:   for ($i = 1; $i <= $graph_x; $i++) {
pierre@1:     $text = date('M d, Y H', $start + ($offset * $i) - $offset / 2);
pierre@1:     $len = strlen($text);
pierre@1:     $x_pos = $graph_x_offset - $x_offset + $i * $graph_x_width - 7;
pierre@1:     $y_pos = $graph_height + $graph_y_offset + ($len * 6) + 3;
pierre@1:     imagestringup($graph, 2, $x_pos, $y_pos, $text, $color['black']);
pierre@1:     //$x_pos = $graph_x_offset - $x_offset + $i * $graph_x_width - 4;
pierre@1:     //$y_pos = $graph_height + $graph_y_offset + ($len * 5) + 3;
pierre@1:     //imagestringup($graph, 1, $x_pos, $y_pos, $text, $color['black']);
pierre@1:   }
pierre@1: 
pierre@1:   // draw a grid
pierre@1:   $style = array($color['grey'], IMG_COLOR_TRANSPARENT, IMG_COLOR_TRANSPARENT);
pierre@1:   imagesetstyle($graph, $style);
pierre@1:   for ($i = 1; $i <= $graph_x; $i++) {
pierre@1:     imageline($graph, $graph_x_offset + $i * $graph_x_width - $graph_x_width / 2, $graph_y_offset, $graph_x_offset + $i * $graph_x_width - $graph_x_width / 2, $graph_y_offset + $graph_height - 1, IMG_COLOR_STYLED);
pierre@1:   }
pierre@1:   for ($i = 1; $i < $graph_y; $i++) {
pierre@1:     imageline($graph, $graph_x_offset, $graph_y_offset + $i * $graph_y_width, $graph_x_offset + $graph_width, $graph_y_offset + $i * $graph_y_width, IMG_COLOR_STYLED);
pierre@1:   }
pierre@1:   // left, right, top, and bottom borders, respectively
pierre@1:   imageline($graph, $graph_x_offset, $graph_y_offset, $graph_x_offset, $graph_y_offset + $graph_height, $color['grey']);
pierre@1:   imageline($graph, $graph_x_offset + $graph_width - 1, $graph_y_offset, $graph_x_offset + $graph_width - 1, $graph_y_offset + $graph_height, $color['grey']);
pierre@1:   imageline($graph, $graph_x_offset, $graph_y_offset, $graph_x_offset + $graph_width - 1, $graph_y_offset, $color['grey']);
pierre@1:   imageline($graph, $graph_x_offset, $graph_y_offset + $graph_height, $graph_x_offset + $graph_width - 1, $graph_y_offset + $graph_height, $color['grey']);
pierre@1: 
pierre@1:   // display the graph
pierre@1:   imagepng($graph);
pierre@1:   imagedestroy($graph);
pierre@1: }
pierre@1: 
pierre@1: /**
pierre@1:  * Figure out how many x columns to display.
pierre@1:  * TODO: Find a better algorithm than this slop.
pierre@1:  */
pierre@1: function _ad_report_select_x($number, $divisor, $diff) {
pierre@1:   if ($divisor < 2) {
pierre@1:     return $number;
pierre@1:   }
pierre@1:   $divisor = $divisor + $diff;
pierre@1:   if ($divisor == 0) {
pierre@1:     $divisor = $divisor + $diff;
pierre@1:   }
pierre@1:   $result = (int)($number / $divisor);
pierre@1:   if ($result < 8) {
pierre@1:     $diff -= 1;
pierre@1:     if ($diff) {
pierre@1:       return _ad_report_select_x($number, $divisor, $diff);
pierre@0:     }
pierre@0:   }
pierre@1:   else if ($result > 12) {
pierre@1:     $diff += 1;
pierre@1:     if ($diff) {
pierre@1:       return _ad_report_select_x($number, $divisor, $diff);
pierre@1:     }
pierre@1:   }
pierre@1:   return $result;
pierre@0: }