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