Mercurial > defr > drupal > ad

comparison report/ad_report.module @ 1:948362c2a207 ad

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
update advertisement
author pierre
date Thu, 02 Apr 2009 15:28:21 +0000
parents d8a3998dac8e
children 6aeff3329e01
comparison
equal deleted inserted replaced
0:d8a3998dac8e 1:948362c2a207
1 <?php 1 <?php
2 // $Id: ad_report.module,v 1.1.2.3.2.7.2.6 2009/02/16 23:12:29 jeremy Exp $ 2 // $Id: ad_report.module,v 1.1.2.3.2.7.2.6.2.7 2009/03/27 19:31:36 jeremy Exp $
3 3
4 /** 4 /**
5 * @file 5 * @file
6 * Provides comprehensive charts and reports about advertising statistics. 6 * Provides comprehensive charts and reports about advertising statistics.
7 * 7 *
8 * Copyright (c) 2005-2009. 8 * Copyright (c) 2007-2009.
9 * Jeremy Andrews <jeremy@tag1consulting.com>. 9 * Jeremy Andrews <jeremy@tag1consulting.com>.
10 */ 10 */
11 11
12 /** 12 /**
13 * Implementation of hook_menu(). 13 * Implementation of hook_menu().
14 */ 14 */
15 function ad_report_menu() { 15 function ad_report_menu() {
16 $items = array(); 16 $items = array();
17 17 $items['admin/content/ad/report'] = array(
18 'title' => t('Reports'),
19 'page callback' => 'drupal_get_form',
20 'page arguments' => array('ad_report_admin'),
21 'access arguments' => array('generate administrative reports'),
22 'type' => MENU_LOCAL_TASK,
23 'weight' => 1
24 );
25 $items['admin/content/ad/report/display'] = array(
26 'page callback' => 'ad_report_admin_display',
27 'access arguments' => array('generate administrative reports'),
28 'type' => MENU_CALLBACK
29 );
30 $items['admin/content/ad/report/csv'] = array(
31 'page callback' => 'ad_report_admin_ad_table',
32 'page arguments' => array('0', '0', array(), TRUE),
33 'access arguments' => array('generate administrative reports'),
34 'type' => MENU_CALLBACK
35 );
18 $items['node/%node/report'] = array( 36 $items['node/%node/report'] = array(
19 'title' => 'Reports', 37 'title' => t('Reports'),
20 'page callback' => 'ad_report_bargraph', 38 'page callback' => 'ad_report_bargraph_handler',
21 'page arguments' => array(1), 39 'page arguments' => array(1),
22 'type' => MENU_LOCAL_TASK, 40 'type' => MENU_LOCAL_TASK,
23 'access callback' => 'ad_report_access', 41 'access callback' => 'ad_report_bargraph_access',
24 'access arguments' => array(1), 42 'access arguments' => array(1),
25 ); 43 );
26 $items['node/%node/report/monthly'] = array( 44 $items['ad_report/%node/bargraph/node/%/%'] = array(
27 'title' => 'Monthly Reports',
28 'page callback' => 'ad_report_bargraph',
29 'page arguments' => array(1,3),
30 'type' => MENU_LOCAL_TASK,
31 'access callback' => 'ad_report_access',
32 'access arguments' => array(1),
33 );
34 $items['node/%node/report/weekly'] = array(
35 'title' => 'Weekly Reports',
36 'page callback' => 'ad_report_bargraph',
37 'page arguments' => array(1,3),
38 'type' => MENU_LOCAL_TASK,
39 'access callback' => 'ad_report_access',
40 'access arguments' => array(1),
41 );
42 $items['node/%node/report/daily'] = array(
43 'title' => 'Daily Reports',
44 'page callback' => 'ad_report_bargraph',
45 'page arguments' => array(1,3),
46 'type' => MENU_DEFAULT_LOCAL_TASK,
47 'access callback' => 'ad_report_access',
48 'access arguments' => array(1),
49 );
50 $items['node/%node/report/hourly'] = array(
51 'title' => 'Hourly Reports',
52 'page callback' => 'ad_report_bargraph',
53 'page arguments' => array(1,3),
54 'type' => MENU_LOCAL_TASK,
55 'access callback' => 'ad_report_access',
56 'access arguments' => array(1),
57 );
58 $items['ad_report/%node/bargraph'] = array(
59 'title' => 'Bar graph', 45 'title' => 'Bar graph',
60 'page callback' => 'ad_report_generate_bargraph', 46 'page callback' => 'ad_report_generate_bargraph',
61 'page arguments' => array(1), 47 'page arguments' => array(1, 'node', 4, 5),
62 'type' => MENU_CALLBACK, 48 'type' => MENU_CALLBACK,
63 'access callback' => 'ad_report_access', 49 'access callback' => 'ad_report_bargraph_access',
64 'access arguments' => array(1), 50 'access arguments' => array(1),
65 ); 51 );
66
67 return $items; 52 return $items;
68 } 53 }
69 /** 54
70 * Implementation of access callback. 55 /**
71 * 56 * Drupal hook_perm implementation.
72 * @param mixed $node 57 */
73 * Ad object. 58 function ad_report_perm() {
74 */ 59 return array(t('generate administrative reports'));
75 function ad_report_access($node){ 60 }
76 return ($node->type == 'ad') && ad_adaccess($node, 'access statistics'); 61
77 } 62 /**
63 * Menu system callback, determine if current user can generate reports.
64 */
65 function ad_report_bargraph_access($node) {
66 if (isset($node->adtype)) {
67 return ad_permission($node->nid, 'generate reports');
68 }
69 }
70
71 /**
72 *
73 */
74 function ad_report_bargraph_handler($node) {
75 return ad_report_bargraph($node, "node/$node->nid/report", 'node', arg(3), arg(4));
76 }
77
78 /**
79 * Ad module hook_adapi.
80 */
81 function ad_report_adapi($op, $node = NULL) {
82 switch ($op) {
83 case 'permissions':
84 return array(
85 'generate reports' => TRUE,
86 );
87 }
88 }
89
90 /**
91 *
92 */
93 function ad_report_admin() {
94 $form = array();
95
96 $start = isset($_SESSION['ad_report_start']) ? strtotime($_SESSION['ad_report_start']) : _ad_report_first_day_of_month();
97 $end = isset($_SESSION['ad_report_end']) ? strtotime($_SESSION['ad_report_end']) : _ad_report_last_day_of_month();
98 $group = isset($_SESSION['ad_report_group']) ? $_SESSION['ad_report_group'] : array('all');
99
100 $form['dates'] = array(
101 '#type' => 'fieldset',
102 '#title' => t('Report dates'),
103 '#prefix' => '<div class="container-inline">',
104 '#suffix' => '</div>',
105 );
106 $form['dates']['start'] = array(
107 '#type' => 'textfield',
108 '#title' => t('Start'),
109 '#size' => 24,
110 '#maxlength' => 64,
111 '#default_value' => _ad_report_format_date_human($start),
112 // display pop up calendar if jstools jscalendar module enabled
113 '#attributes' => array('class' => 'jscalendar'),
114 '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
115 '#jscalendar_timeFormat' => '24',
116 );
117 $form['dates']['space1'] = array(
118 '#value' => '&nbsp;&nbsp;',
119 );
120 $form['dates']['end'] = array(
121 '#type' => 'textfield',
122 '#title' => t('End'),
123 '#size' => 24,
124 '#maxlength' => 64,
125 '#default_value' => _ad_report_format_date_human($end),
126 // display pop up calendar if jstools jscalendar module enabled
127 '#attributes' => array('class' => 'jscalendar'),
128 '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
129 );
130 $form['dates']['space2'] = array(
131 '#value' => '&nbsp;&nbsp;&nbsp;',
132 );
133
134 // groups
135 $groups = ad_groups_list();
136 $form['groups'] = array(
137 '#type' => 'fieldset',
138 '#title' => t('Groups'),
139 );
140 $options = array();
141 $options['all'] = t('- All -');
142 $options = $options + $groups;
143 $form['groups']['group'] = array(
144 '#type' => 'select',
145 '#title' => t('Ad groups'),
146 '#options' => $options,
147 '#multiple' => TRUE,
148 '#required' => TRUE,
149 '#default_value' => $group,
150 );
151
152 // submit
153 $form['submit'] = array(
154 '#type' => 'submit',
155 '#value' => t('Generate report'),
156 '#weight' => 10,
157 );
158 $form['reset'] = array(
159 '#type' => 'submit',
160 '#value' => t('Reset report'),
161 '#weight' => 10,
162 );
163
164 return $form;
165 }
166
167 /**
168 * Sanity check the date range.
169 */
170 function ad_report_admin_validate($form, $form_state) {
171 if ($form_state['clicked_button']['#value'] == t('Reset report')) {
172 unset($_SESSION['ad_report_start']);
173 unset($_SESSION['ad_report_end']);
174 unset($_SESSION['ad_report_group']);
175 }
176 else {
177 $start = isset($form_state['values']['start']) ? strtotime($form_state['values']['start']) : 0;
178 $end = isset($form_state['values']['start']) ? strtotime($form_state['values']['end']) : 0;
179 if (!$start) {
180 form_set_error('start', t('You must enter a valid start date.'));
181 }
182 else if ($start >= (time() - 3600)) {
183 form_set_error('start', t('The report must start at least one hour before the current time.'));
184 }
185 else if ($start >= $end) {
186 form_set_error('start', t('The report must start before it ends.'));
187 }
188 if (!$end) {
189 form_set_error('end', t('You must enter a valid end date.'));
190 }
191 }
192 }
193
194 /**
195 * Redirect to a path to generate the requested report.
196 */
197 function ad_report_admin_submit($form, $form_state) {
198 if ($form_state['clicked_button']['#value'] == t('Generate report')) {
199 $start = date('YmdHi', strtotime($form_state['values']['start']));
200 $end = date('YmdHi', strtotime($form_state['values']['end']));
201 $group = $form_state['values']['group'];
202 $_SESSION['ad_report_start'] = $start;
203 $_SESSION['ad_report_end'] = $end;
204 $_SESSION['ad_report_group'] = $group;
205
206 drupal_goto('admin/content/ad/report/display');
207 }
208 }
209
210 /**
211 * Display the administrative report.
212 */
213 function ad_report_admin_display() {
214 $start = isset($_SESSION['ad_report_start']) ? $_SESSION['ad_report_start'] : 0;
215 $end = isset($_SESSION['ad_report_end']) ? $_SESSION['ad_report_end'] : 0;
216 $group = isset($_SESSION['ad_report_group']) ? $_SESSION['ad_report_group'] : array();
217 if (!$start && !$end) {
218 drupal_goto('admin/content/ad/report');
219 }
220 $output = '<div class="image"><img src="'. url("ad_report/0/bargraph/admin/$start/$end") .'" /></div>';
221 $output .= ad_report_admin_ad_table(strtotime($start), strtotime($end), $group);
222 $output .= '<div>'. l(t('Modify report'), 'admin/content/ad/report') .'</div>';
223 return $output;
224 }
225
226 /**
227 *
228 */
229 function ad_report_admin_ad_table($start = 0, $end = 0, $group = array(), $csv = FALSE) {
230 if (!$start) {
231 $start = isset($_SESSION['ad_report_start']) ? strtotime($_SESSION['ad_report_start']) : 0;
232 }
233 if (!$end) {
234 $end = isset($_SESSION['ad_report_end']) ? strtotime($_SESSION['ad_report_end']) : 0;
235 }
236 if (empty($group)) {
237 $group = isset($_SESSION['ad_report_group']) ? $_SESSION['ad_report_group'] : array();
238 }
239 // prepare dates
240 $start = _ad_report_format_date_db($start);
241 $end = _ad_report_format_date_db($end);
242
243 // prepare groups
244 $groups = ad_groups_list();
245 $all = FALSE;
246 $none = FALSE;
247 if (is_array($group)) {
248 if (in_array('all', $group)) {
249 $all = TRUE;
250 }
251 if (!$all) {
252 if (sizeof($group) == sizeof($groups)) {
253 $all = TRUE;
254 }
255 }
256 if (in_array('0', $group)) {
257 unset($group[0]);
258 $none = TRUE;
259 }
260 }
261
262 $select = 'SELECT DISTINCT(aid) as nid FROM {ad_statistics} a';
263 if ($all) {
264 $where = array(
265 "a.action = 'view'",
266 'a.date >= %d',
267 'a.date <= %d',
268 'a.aid > 0',
269 );
270 $join = array();
271 $args = array($start, $end);
272 }
273 else if ($none) {
274 if (sizeof($group)) {
275 $where = array(
276 '(t.tid IN (%s) OR ISNULL(t.tid))',
277 "a.action = 'view'",
278 'a.date >= %d',
279 'a.date <= %d',
280 );
281 $join = array(
282 'LEFT JOIN {term_node} t ON a.aid = t.tid',
283 );
284 $args = array(implode(',', $group), $start, $end);
285 }
286 else {
287 $where = array(
288 'ISNULL(t.tid)',
289 "a.action = 'view'",
290 'a.date >= %d',
291 'a.date <= %d',
292 );
293 $join = array(
294 'LEFT JOIN {term_node} t ON a.aid = t.tid',
295 );
296 $args = array($start, $end);
297 }
298 }
299 else {
300 $where = array(
301 't.tid IN (%s)',
302 "a.action = 'view'",
303 'a.date >= %d',
304 'a.date <= %d',
305 );
306 $join = array(
307 'LEFT JOIN {term_node} t ON a.aid = t.tid',
308 );
309 $args = array(implode(',', $group), $start, $end);
310 }
311
312 $return = module_invoke_all('adreport', $join, $where, $args, $select);
313 foreach ($return as $type => $value) {
314 switch ($type) {
315 case 'join':
316 if (is_array($value)) {
317 foreach ($value as $option) {
318 $join[] = $option;
319 }
320 }
321 break;
322 case 'where':
323 if (is_array($value)) {
324 foreach ($value as $option) {
325 $where[] = $option;
326 }
327 }
328 break;
329 case 'args':
330 if (is_array($value)) {
331 foreach ($value as $option) {
332 $args[] = $option;
333 }
334 }
335 break;
336 }
337 }
338
339 // Build the query.
340 $query = $select .' '. implode(' ', $join) .' WHERE '. implode(' AND ', $where);
341 $ads = array();
342 $result = db_query($query, $args);
343 while ($ad = db_fetch_object($result)) {
344 if ($ad->nid) {
345 $ads[$ad->nid] = $ad->nid;
346 }
347 }
348
349 if ($csv) {
350 header('Content-type: application/octet-stream');
351 header("Content-Disposition: attachment; filename=report-$start-$end.csv");
352 echo "ad id, title, first view, last view, clicks, views, click-thru\n";
353 }
354 else {
355 $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>';
356
357 $headers = array(t('Advertisement'), t('Active dates'), t('Views'), t('Clicks'), t('Click-thru'));
358 // get counts for each ad
359 $rows = array();
360 }
361 $total_views = $total_clicks = 0;
362 foreach ($ads as $nid) {
363 $ad = node_load($nid);
364 if ($ad->nid) {
365 $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));
366 $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)));
367 $first = format_date($first, 'small');
368 $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)));
369 $last = format_date($last, 'small');
370 $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));
371 if ($views) {
372 $clickthru = number_format($clicks / $views, 2) .'%';
373 }
374 else {
375 $clickthru = '0%';
376 }
377 if ($views || $clicks) {
378 if ($csv) {
379 echo "$ad->nid, $ad->title, $first, $last, $views, $clicks, $clickthru\n";
380 }
381 else {
382 $row = array();
383 $row[] = l($ad->title, "node/$ad->nid");
384 $row[] = "first view: $first<br />last view: $last";
385 $row[] = number_format($views);
386 $row[] = number_format($clicks);
387 $row[] = $clickthru;
388 $rows[] = $row;
389 $total_views += $views;
390 $total_clicks += $clicks;
391 }
392 }
393 }
394 }
395 if ($csv) {
396 return (0);
397 }
398 if ($total_views || $total_clicks) {
399 $row = array();
400 $row[] = '<strong>'. t('Total') .'</strong>';
401 $row[] = '';
402 $row[] = '<strong>'. number_format($total_views) .'</strong>';
403 $row[] = '<strong>'. number_format($total_clicks) .'</strong>';
404 if ($total_views) {
405 $row[] = '<strong>'. number_format($total_clicks / $total_views, 2) .'%' .'</strong>';
406 }
407 else {
408 $row[] = '<strong>'. '0%' .'</strong>';
409 }
410 $rows[] = $row;
411 }
412 $output .= theme('table', $headers, $rows);
413 $output .= l(t('Download CSV'), 'admin/content/ad/report/csv');
414 return $output;
415 }
416
417 /**
418 * Returns a timestamp for the first hour of the first day of the month.
419 */
420 function _ad_report_first_day_of_month($time = NULL) {
421 if ($time === NULL) {
422 $time = time();
423 }
424 return strtotime(date('Ym010000', $time));
425 }
426
427 /**
428 * Returns a timestamp for the last hour of the last day of the month.
429 */
430 function _ad_report_last_day_of_month($time = NULL) {
431 if ($time === NULL) {
432 $time = time();
433 }
434 $month = date('m', $time);
435 $year = date('Y', $time);
436 $day = date('d', mktime(0, 0, 0, ($month + 1), 0, $year));
437 return strtotime("{$year}{$month}{$day}2359");
438 }
439
78 /** 440 /**
79 * Page to display ad with bargraph. 441 * Page to display ad with bargraph.
80 */ 442 */
81 function ad_report_bargraph($node, $granularity = 'daily', $type = 'node') { 443 function ad_report_bargraph($data, $url, $type = 'node', $start = 0, $end = 0) {
82 switch ($granularity) { 444 if ($type == 'node') {
83 case 'hourly': 445 drupal_set_title($data->title);
84 drupal_set_title(t('Past twelve hours')); 446 }
85 break; 447 $start_date = _ad_report_get_date_from_path($start);
86 case 'daily': 448 $end_date = _ad_report_get_date_from_path($end);
87 drupal_set_title(t('Past twelve days')); 449 $output = drupal_get_form('ad_report_range_form', $type, $url, $start_date, $end_date);
88 break; 450 if ($start && $end) {
89 case 'weekly': 451 switch ($type) {
90 drupal_set_title(t('Past twelve weeks')); 452 case 'node':
91 break; 453 $ad = db_fetch_object(db_query('SELECT aid, redirect, adtype FROM {ads} WHERE aid = %d', $data->nid));
92 case 'monthly': 454 if ($ad->aid) {
93 drupal_set_title(t('Past twelve months')); 455 $output .= '<img src="'. url("ad_report/$data->nid/bargraph/node/$start/$end") .'" />';
94 break; 456 $output .= theme('box', '', module_invoke("ad_$data->adtype", 'display_ad', $ad));
95 } 457 $output .= ad_report_group_table($data->nid, $type, $start, $end);
96 458 }
97 switch ($type) { 459 $output .= module_invoke('ad', 'click_history', $data->nid);
98 case 'node': 460 break;
99 if ($node->aid) { 461 default:
100 $output = '<img src="'. url("ad_report/$node->nid/bargraph/$granularity/node") .'" />'; 462 $output = '<img src="'. url("ad_report/$data->uid/bargraph/$granularity/$type") .'" />';
101 $ad_link = module_invoke('ad_' . $node->adtype, 'display_ad', $node); 463 break;
102 $output .= theme('box', $node->title, $ad_link); 464 }
103 }
104 break;
105 default:
106 $output = '<img src="'. url("ad_report/$node->uid/bargraph/$granularity/$type") .'" />';
107 break;
108 } 465 }
109 return $output; 466 return $output;
110 } 467 }
111 468
112 /** 469 /**
470 * Return a form for selecting a date range for generating a report.
471 */
472 function ad_report_range_form($form_state, $type, $url = NULL, $start = NULL, $end = NULL) {
473 $form = array();
474
475 $start = $start ? $start : _ad_report_first_day_of_month();
476 $end = $end ? $end : _ad_report_last_day_of_month();
477
478 $form['report'] = array(
479 '#type' => 'fieldset',
480 '#title' => t('Report dates'),
481 '#prefix' => '<div class="container-inline">',
482 '#suffix' => '</div>',
483 );
484 $form['report']['type'] = array(
485 '#value' => $type,
486 '#type' => 'hidden',
487 );
488 $form['report']['url'] = array(
489 '#value' => $url,
490 '#type' => 'hidden',
491 );
492 $form['report']['start'] = array(
493 '#type' => 'textfield',
494 '#title' => t('Start'),
495 '#size' => 24,
496 '#maxlength' => 64,
497 '#default_value' => _ad_report_format_date_human($start),
498 // display pop up calendar if jstools jscalendar module enabled
499 '#attributes' => array('class' => 'jscalendar'),
500 '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
501 '#jscalendar_timeFormat' => '24',
502 );
503 $form['report']['space1'] = array(
504 '#value' => '&nbsp;&nbsp;',
505 );
506 $form['report']['end'] = array(
507 '#type' => 'textfield',
508 '#title' => t('End'),
509 '#size' => 24,
510 '#maxlength' => 64,
511 '#default_value' => _ad_report_format_date_human($end),
512 // display pop up calendar if jstools jscalendar module enabled
513 '#attributes' => array('class' => 'jscalendar'),
514 '#jscalendar_ifFormat' => '%Y-%m-%d %H:%M',
515 );
516 $form['report']['space2'] = array(
517 '#value' => '&nbsp;&nbsp;&nbsp;',
518 );
519 $form['report']['generate'] = array(
520 '#type' => 'submit',
521 '#value' => t('Generate report'),
522 );
523
524 return $form;
525 }
526
527 /**
528 * Validate the form range.
529 */
530 function ad_report_range_form_validate($form, $form_state) {
531 $start = isset($form_state['values']['start']) ? strtotime($form_state['values']['start']) : 0;
532 $end = isset($form_state['values']['start']) ? strtotime($form_state['values']['end']) : 0;
533 if (!$start) {
534 form_set_error('start', t('You must enter a valid start date.'));
535 }
536 else if ($start >= (time() - 3600)) {
537 form_set_error('start', t('The report must start at least one hour before the current time.'));
538 }
539 else if ($start >= $end) {
540 form_set_error('start', t('The report must start before it ends.'));
541 }
542 if (!$end) {
543 form_set_error('end', t('You must enter a valid end date.'));
544 }
545 }
546
547 /**
548 * Redirect to URL for displaying report.
549 */
550 function ad_report_range_form_submit($form, $form_state) {
551 $start = date('YmdHi', strtotime($form_state['values']['start']));
552 $end = date('YmdHi', strtotime($form_state['values']['end']));
553 drupal_goto($form_state['values']['url'] ."/$start/$end");
554 }
555
556 /**
557 * Helper function to extract date from URL.
558 */
559 function _ad_report_get_date_from_path($path) {
560 if (isset($path) && $path) {
561 $year = substr($path, 0, 4);
562 $month = substr($path, 4, 2);
563 $day = substr($path, 6, 2);
564 $hour = substr($path, 8, 2);
565 if (strlen($path) == 12) {
566 $minute = substr($path, 10, 2);
567 }
568 else {
569 $minute = 0;
570 }
571 $date = strtotime("$month/$day/$year $hour:$minute");
572 if ($date > 0) {
573 return $date;
574 }
575 drupal_set_message(t('Invalid date specified in range.'), 'error');
576 }
577 }
578
579 /**
580 * Helper function to format date.
581 */
582 function _ad_report_format_date_human($date) {
583 return date('Y-m-d H:i', $date);
584 }
585
586 /**
587 * Helper function to format date.
588 */
589 function _ad_report_format_date_db($date) {
590 return date('YmdH', $date);
591 }
592
593 /**
594 * Display table with per-group statistics.
595 */
596 function ad_report_group_table($id, $type, $start, $end) {
597 $start_date = _ad_report_format_date_db(_ad_report_get_date_from_path($start));
598 $end_date = _ad_report_format_date_db(_ad_report_get_date_from_path($end));
599 // TODO: Support other types than nodes
600 $result = db_query('SELECT DISTINCT(adgroup) FROM {ad_statistics} WHERE aid = %d AND date >= %d AND date <= %d', $id, $start_date, $end_date);
601 // extract all groups that this advertisement has been displayed in
602 while ($group = db_fetch_object($result)) {
603 if ($group->adgroup) {
604 $first = substr($group->adgroup, 0, 1);
605 if ($first == 't') {
606 $tids = $tids = explode(',', substr($group->adgroup, 1, strlen($group->adgroup)));
607 foreach ($tids as $tid) {
608 if ($tid) {
609 $adgroups[$tid][] = $group->adgroup;
610 }
611 }
612 }
613 else {
614 // handle this type of "group"
615 $adgroups['other'][] = $group->adgroup;
616 }
617 }
618 else {
619 $adgroups[0][] = $group->adgroup;
620 }
621 }
622 $headers = array(t('Group'), t('Active dates'), t('Views'), t('Clicks'), t('Click-thru'));
623 // get counts for each group
624 $groups = ad_groups_list();
625 $rows = array();
626 $total_views = $total_clicks = 0;
627 foreach ($groups as $tid => $group) {
628 $views = $clicks = 0;
629 if (isset($adgroups[$tid]) && is_array($adgroups[$tid])) {
630 foreach ($adgroups[$tid] as $adgroup) {
631 $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));
632 $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));
633 }
634 }
635 if ($views || $clicks) {
636 $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));
637 if ($begin) {
638 $begin = format_date(_ad_report_get_date_from_path($begin), 'small');
639 $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));
640 if ($finish) {
641 $finish = format_date(_ad_report_get_date_from_path($finish), 'small');
642 }
643 }
644 if ($begin && $finish) {
645 $row = array();
646 $row[] = $group;
647 $row[] = "first view: $begin<br />last view: $finish";
648 $row[] = number_format($views);
649 $row[] = number_format($clicks);
650 if ($views) {
651 $row[] = number_format($clicks / $views, 2) .'%';
652 }
653 else {
654 $row[] = '0%';
655 }
656 $rows[] = $row;
657 $total_views += $views;
658 $total_clicks += $clicks;
659 }
660 }
661 }
662 if ($total_views || $total_clicks) {
663 $row = array();
664 $row[] = '<strong>'. t('Total') .'</strong>';
665 $row[] = '';
666 $row[] = '<strong>'. number_format($total_views) .'</strong>';
667 $row[] = '<strong>'. number_format($total_clicks) .'</strong>';
668 if ($total_views) {
669 $row[] = '<strong>'. number_format($total_clicks / $total_views, 2) .'%' .'</strong>';
670 }
671 else {
672 $row[] = '<strong>'. '0%' .'</strong>';
673 }
674 $rows[] = $row;
675 }
676
677 return theme('table', $headers, $rows);
678 }
679
680 /**
113 * Page that utilizes gd to generate a bargraph. 681 * Page that utilizes gd to generate a bargraph.
114 * 682 */
115 * TODO: Make this more dynamic, allowing to move through time, etc. 683 function ad_report_generate_bargraph($id, $type, $start, $end) {
116 */
117 function ad_report_generate_bargraph($node, $granularity = 'daily', $type = 'node') {
118 $id = $node->nid;
119 header("Content-type: image/png"); 684 header("Content-type: image/png");
120 685
121 // Preperation. 686 if ($type == 'node' && is_object($id)) {
687 $id = $id->nid;
688 }
689 $start = _ad_report_get_date_from_path($start);
690 $end = _ad_report_get_date_from_path($end);
691
692 // be sure we've been passed in valid parameters
693 $elapse = $end - $start;
694 if ($elapse <= 0 || $start <= 0 || $end <= 0) {
695 return NULL;
696 }
697 $increments = (int)($elapse / 3600);
698
699 // image size
700 $image_width = 700;
701 $image_height = 360;
702
703 // graph size
704 $graph_width = 600;
705 $graph_height = 250;
706 $graph_x_offset = 8;
707 $graph_y_offset = 8;
708 $graph_y = 8;
709
710 // calculate slices to extract from database
711 $width = $graph_width / $increments;
712 $number = $increments;
713 $factor = 1;
714 if ($width < 1) {
715 $factor = 1 / $width;
716 }
717 $number = $number / $factor;
718 $width = $width * $factor;
719 $slice = $elapse / $number;
720
721 // retrieve views and clicks from the database
122 $views = array(); 722 $views = array();
723 $clicks = array();
123 $max_views = 0; 724 $max_views = 0;
124 $statistics = array();
125 $clicks = array();
126 $max_clicks = 0; 725 $max_clicks = 0;
127 $time = time(); 726 $key = 0;
128 727 for ($i = $start; $i < $end; $i += $slice) {
129 $increments = 12; 728 $start_date = _ad_report_format_date_db($i);
130 $end_add = 0; 729 $end_date = _ad_report_format_date_db($i + $slice);
131 switch ($granularity) { 730 switch ($type) {
132 case 'hourly': 731 case 'node':
133 $start_time = (60 * 60 * 11); 732 $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));
134 // Increment hourly. 733 $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));
135 $increment_time = (60 * 60); 734 break;
136 735 case 'user':
137 $format_start = 'YmdH'; 736 $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));
138 $format_end = 'YmdH'; 737 $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));
139 $format_end_append = ''; 738 break;
140 $format_upper = 'M d'; 739 case 'admin':
141 $format_lower = 'ga'; 740 $group = $_SESSION['ad_report_group'];
142 $graph_height = 250; 741 $all = FALSE;
143 break; 742 $none = FALSE;
144 case 'daily': 743 if (is_array($group)) {
145 default: 744 if (in_array('all', $group)) {
146 $start_time = (60 * 60 * 24 * 11); 745 $all = TRUE;
147 // Increment daily. 746 }
148 $increment_time = (60 * 60 * 24); 747 if (!$all) {
149 748 $groups = ad_groups_list();
150 $format_start = 'Ymd00'; 749 if (sizeof($group) == sizeof($groups)) {
151 $format_end = 'Ymd'; 750 $all = TRUE;
152 $format_end_append = '24'; 751 }
153 $format_upper = 'D'; 752 }
154 $format_lower = 'M d'; 753 if (in_array('0', $group)) {
155 break; 754 unset($group[0]);
156 case 'weekly': 755 $none = TRUE;
157 $start_time = (60 * 60 * 24 * 7 * 11); 756 }
158 // Increment weekly. 757 }
159 $increment_time = (60 * 60 * 24 * 7); 758 if ($all) {
160 759 $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));
161 $format_start = 'Ymd00'; 760 $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));
162 $format_end = 'Ymd'; 761 }
163 $format_end_append = '24'; 762 else if ($none) {
164 $end_add = (60 * 60 * 24 * 6); 763 if (sizeof($group)) {
165 //$end_add = 600; 764 $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));
166 $format_upper = 'M d -'; 765 $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));
167 $format_lower = ''; 766 }
168 break; 767 else {
169 case 'monthly': 768 $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));
170 $start_time = ((60 * 60 * 24 * 2) + (60 * 60 * 24 * 7 * 4)) * 11; 769 $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));
171 // Increment monthly (every 30 days). 770 }
172 $increment_time = (60 * 60 * 24 * 2) + (60 * 60 * 24 * 7 * 4); 771 }
173 772 else {
174 $format_start = 'Ymd00'; 773 $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));
175 $format_end = 'Ymd'; 774 $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));
176 $format_end_append = '24'; 775 }
177 $end_add = (60 * 60 * 24 * 29); 776 break;
178 $format_upper = 'M d -'; 777 default:
179 $format_lower = ''; 778 $function = "ad_report_views_$type";
180 break; 779 if (function_exists("$function")) {
181 } 780 $views[] = $function($id, $day_start, $day_end);
182 781 }
183 // Retrive data from database. 782 $function = "ad_report_clicks_$type";
184 for ($i = $time - $start_time; $i <= $time; $i = $i + $increment_time) { 783 if (function_exists("$function")) {
185 $day_start = date($format_start, $i); 784 $clicks[] = $function($id, $day_start, $day_end);
186 $day_end = date($format_end, $i + $end_add). $format_end_append; 785 }
187 if ($type == 'node') { 786 break;
188 $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)); 787 }
189 $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)); 788 $max_views = $views[$key] > $max_views ? $views[$key] : $max_views;
190 } 789 $max_clicks = $clicks[$key] > $max_clicks ? $clicks[$key] : $max_clicks;
191 else if ($type == 'user') { 790 $key++;
192 $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)); 791 }
193 $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)); 792
194 } 793 // create graph
195 else {
196 $function = "ad_report_views_$type";
197 if (function_exists("$function")) {
198 $view = $function($id, $day_start, $day_end);
199 }
200 $function = "ad_report_clicks_$type";
201 if (function_exists("$function")) {
202 $click = $function($id, $day_start, $day_end);
203 }
204 }
205 if ($view > $max_views) {
206 $max_views = $view;
207 }
208 $statistics[] = array(
209 'upper' => date($format_upper, $i),
210 'lower' => date($format_lower, $i),
211 'views' => $view,
212 'clicks' => $click
213 );
214 }
215
216 // Build graph image.
217 $image_width = 50 * $increments + 1;
218 $image_height = 300;
219 $graph_width = 50 * $increments;
220 $graph_height = 250;
221
222 $graph = imagecreate($image_width, $image_height); 794 $graph = imagecreate($image_width, $image_height);
223 795
224 // Configure colors to use in chart. 796 // configure colors to use in chart
225 $color = array( 797 $color = array(
226 'white' => imagecolorallocate($graph, 255, 255, 255), 798 'white' => imagecolorallocate($graph, 255, 255, 255),
227 'black' => imagecolorallocate($graph, 0, 0, 0), 799 'black' => imagecolorallocate($graph, 0, 0, 0),
228 'grey' => imagecolorallocate($graph, 192, 192, 192), 800 'grey' => imagecolorallocate($graph, 192, 192, 192),
229 'blue' => imagecolorallocate($graph, 0, 0, 255), 801 'blue' => imagecolorallocate($graph, 0, 0, 255),
230 'orange' => imagecolorallocate($graph, 220, 210, 60), 802 'orange' => imagecolorallocate($graph, 220, 210, 60),
231 ); 803 'red' => imagecolorallocate($graph, 255, 0, 0),
232 804 );
233 // Draw the outside edges of the graph. 805
234 imageline($graph, 0, 0, 0, $graph_height, $color['grey']); 806 // determine how big the spacers should be
235 imageline($graph, 0, 0, $graph_width, 0, $color['grey']); 807 $max = $max_views > $max_clicks ? $max_views : $max_clicks;
236 imageline($graph, $graph_width - 1, 0, $graph_width - 1, $graph_height, $color['grey']); 808 $y_map = ceil($max / $graph_y / $graph_y) * $graph_y;
237 imageline($graph, 0, $graph_height - 1, $graph_width - 1, $graph_height - 1, $color['grey']); 809 $y_total = $y_map * $graph_y;
238 810
239 // Draw a grid. 811 if ($y_total) {
240 for ($i = 0; $i < ($increments + 1); $i++) { 812 // plot views and clicks on graph
241 imageline($graph, $i*50, 0, $i*50, $graph_height, $color['grey']); 813 foreach ($views as $key => $value) {
242 } 814 $view_height = $graph_height / $y_total * $value;
243 for ($i = 0; $i < 11; $i++) { 815 if ($view_height) {
244 imageline($graph, 0, $i*25, $graph_width, $i*25, $color['grey']); 816 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']);
245 } 817 }
246 818 $click_height = $graph_height / $y_total * $clicks[$key];
247 $multiply = 0; 819 if ($click_height) {
248 if ($max_views > $graph_height) { 820 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']);
249 if (!$multiply) { 821 }
250 $multiply = .9; 822 }
251 } 823 }
252 while (($max_views * $multiply) >= $graph_height) { 824
253 $multiply *= .9; 825 // add scale to y
254 } 826 if ($y_map) {
255 } 827 $graph_y_width = $graph_height / $graph_y;
256 else if ($max_views) { 828 for ($i = 1; $i <= $graph_y; $i++) {
257 while (($max_views * ($multiply + 1)) <= $graph_height) { 829 $text = number_format($i * $y_map);
258 $multiply++; 830 $len = strlen($text);
259 } 831 $x_offset = $graph_width + 14;
260 } 832 $y_pos = $graph_height - $i * $graph_y_width;
261 833 //imagestring($graph, 1, $x_offset, $graph_y_offset + $y_pos - 3, $text, $color['black']);
262 // Display impressions. 834 imagestring($graph, 2, $x_offset, $graph_y_offset + $y_pos - 7, $text, $color['black']);
263 for ($i = 0; $i < $increments ; $i++) { 835 }
264 $view = $multiply ? $statistics[$i]['views'] * $multiply : $statistics[$i]['views']; 836 }
265 if ($view) { 837 else {
266 imagefilledrectangle($graph, $i*50 + 4, $graph_height-$view, ($i+1)*50, $graph_height, $color['grey']); 838 $graph_y_width = 0;
267 $string_height = $view < 10 ? $graph_height - 10 : $graph_height - $view; 839 }
268 imagestring($graph, 2, $i*50 + 15, $string_height, $statistics[$i]['views'], $color['black']); 840
269 } 841 // add scale to x
270 // Display timestamp 842 $graph_x = _ad_report_select_x($number, 8, 0);
271 imagestring($graph, 2, $i*50 + 2, 255, $statistics[$i]['upper'], $color['black']); 843 $offset = $elapse / $graph_x;
272 imagestring($graph, 2, $i*50 + 3, 265, $statistics[$i]['lower'], $color['black']); 844 $graph_x_width = $graph_width / $graph_x;
273 } 845 $x_offset = $graph_x_width / 2;
274 846 for ($i = 1; $i <= $graph_x; $i++) {
275 // Display clicks. 847 $text = date('M d, Y H', $start + ($offset * $i) - $offset / 2);
276 for ($i = 0; $i < $increments; $i++) { 848 $len = strlen($text);
277 $click = $multiply ? $statistics[$i]['clicks'] * $multiply : $statistics[$i]['clicks']; 849 $x_pos = $graph_x_offset - $x_offset + $i * $graph_x_width - 7;
278 if ($click) { 850 $y_pos = $graph_height + $graph_y_offset + ($len * 6) + 3;
279 imagefilledrectangle($graph, $i*50 + 10, $graph_height-$click, ($i+1)*50, $graph_height, $color['blue']); 851 imagestringup($graph, 2, $x_pos, $y_pos, $text, $color['black']);
280 $string_height = $click < 10 ? $graph_height - 10 : $graph_height - $click; 852 //$x_pos = $graph_x_offset - $x_offset + $i * $graph_x_width - 4;
281 imagestring($graph, 2, $i*50 + 20, $string_height, $statistics[$i]['clicks'], $color['white']); 853 //$y_pos = $graph_height + $graph_y_offset + ($len * 5) + 3;
282 } 854 //imagestringup($graph, 1, $x_pos, $y_pos, $text, $color['black']);
283 } 855 }
284 856
857 // draw a grid
858 $style = array($color['grey'], IMG_COLOR_TRANSPARENT, IMG_COLOR_TRANSPARENT);
859 imagesetstyle($graph, $style);
860 for ($i = 1; $i <= $graph_x; $i++) {
861 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);
862 }
863 for ($i = 1; $i < $graph_y; $i++) {
864 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);
865 }
866 // left, right, top, and bottom borders, respectively
867 imageline($graph, $graph_x_offset, $graph_y_offset, $graph_x_offset, $graph_y_offset + $graph_height, $color['grey']);
868 imageline($graph, $graph_x_offset + $graph_width - 1, $graph_y_offset, $graph_x_offset + $graph_width - 1, $graph_y_offset + $graph_height, $color['grey']);
869 imageline($graph, $graph_x_offset, $graph_y_offset, $graph_x_offset + $graph_width - 1, $graph_y_offset, $color['grey']);
870 imageline($graph, $graph_x_offset, $graph_y_offset + $graph_height, $graph_x_offset + $graph_width - 1, $graph_y_offset + $graph_height, $color['grey']);
871
872 // display the graph
285 imagepng($graph); 873 imagepng($graph);
286 imagedestroy($graph); 874 imagedestroy($graph);
287 875 }
288 } 876
289 877 /**
878 * Figure out how many x columns to display.
879 * TODO: Find a better algorithm than this slop.
880 */
881 function _ad_report_select_x($number, $divisor, $diff) {
882 if ($divisor < 2) {
883 return $number;
884 }
885 $divisor = $divisor + $diff;
886 if ($divisor == 0) {
887 $divisor = $divisor + $diff;
888 }
889 $result = (int)($number / $divisor);
890 if ($result < 8) {
891 $diff -= 1;
892 if ($diff) {
893 return _ad_report_select_x($number, $divisor, $diff);
894 }
895 }
896 else if ($result > 12) {
897 $diff += 1;
898 if ($diff) {
899 return _ad_report_select_x($number, $divisor, $diff);
900 }
901 }
902 return $result;
903 }