5 * Copyright 1999-2001 (c) VA Linux Systems
6 * Copyright 2010 (c) FusionForge Team
7 * Copyright 2013, Franck Villaume - TrivialDev
8 * http://fusionforge.org
10 * This file is part of FusionForge. FusionForge is free software;
11 * you can redistribute it and/or modify it under the terms of the
12 * GNU General Public License as published by the Free Software
13 * Foundation; either version 2 of the Licence, or (at your option)
16 * FusionForge is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 * GNU General Public License for more details.
21 * You should have received a copy of the GNU General Public License along
22 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
23 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
26 $allowed_orderby_vals = array('downloads',
42 function stats_util_sum_array( $sum, $add ) {
43 if (!is_array($sum)) {
46 while( list( $key, $val ) = each( $add ) ) {
47 if (!isset($sum[$key])) {
56 * generates the trove list in a select box format.
57 * contains the odd choices of "-2" and "-1" which mean "All projects
58 * and "special project list" respectively
60 function stats_generate_trove_pulldown( $selected_id = 0 ) {
61 $res = db_query_params ('
62 SELECT trove_cat_id,fullpath
68 <select name="trovecatid">';
71 <option value="-2">'._('All Projects').'</option>
72 <option value="-1">'._('Special Projects').'</option>';
74 while ( $row = db_fetch_array($res) ) {
76 <option value="' . $row['trove_cat_id'] . '"'
77 . ( $selected_id == $row["trove_cat_id"] ? " selected=\"selected\"" : "" )
78 . ">" . $row["fullpath"] . '</option>';
85 function stats_trove_cat_to_name( $trovecatid ) {
87 $res = db_query_params ('
90 WHERE trove_cat_id = $1',
93 if ( $row = db_fetch_array($res) ) {
94 return $row["fullpath"];
96 return sprintf(_(" (no category found with ID %d)"), $trovecatid) ;
100 function stats_generate_trove_grouplist( $trovecatid ) {
104 $res = db_query_params ('
106 FROM trove_group_link
107 WHERE trove_cat_id=$1',
110 print db_error( $res );
113 while ( $row = db_fetch_array($res) ) {
114 $results[$i++] = $row["group_id"];
120 function stats_site_projects_form( $report='last_30', $orderby = 'downloads', $projects = 0, $trovecat = 0 ) {
121 global $allowed_orderby_vals ;
123 print '<form action="projects.php" method="get">' . "\n";
124 print '<table class="tableheading fullwidth">' . "\n";
126 print '<tr><td><strong>'._('Projects in trove category:').'</strong></td><td>';
127 stats_generate_trove_pulldown( $trovecat );
130 print '<tr><td><strong>'._('OR enter Special Project List:').'</strong></td>';
131 print '<td> <input type="text" width="100" name="projects" value="'. $projects . '" />';
132 print ' ('._('<strong>comma separated</strong> group_id\'s)').'</td></tr>';
134 print '<tr><td><strong>'._('Report:').'</strong></td><td>';
136 $reports_ids=array();
137 $reports_ids[]='last_30';
138 $reports_ids[]='all';
140 $reports_names=array();
141 $reports_names[]=_('last_30');
142 $reports_names[]=_('All');
144 echo html_build_select_box_from_arrays($reports_ids, $reports_names, 'report', $report, false);
148 print '<tr><td><strong>'._('View by:').'</strong></td><td>';
150 print html_build_select_box_from_arrays ( $allowed_orderby_vals, $allowed_orderby_vals, "orderby", $orderby, false );
153 print '<tr><td colspan="2" class="align-center"> <input type="submit" value="'._('Generate Report').'" /> </td></tr>';
155 print '</table>' . "\n";
156 print '</form>' . "\n";
161 * New function to separate out the SQL so it may be reused in other
165 function stats_site_project_result( $report, $orderby, $projects, $trove ) {
166 global $allowed_orderby_vals ;
169 $order_clause = 'group_name ASC' ;
171 $order_clause = util_ensure_value_in_set ($orderby,
172 $allowed_orderby_vals) ;
173 $order_clause .= ' DESC, group_name ASC';
176 if ($report == 'last_30') {
177 return db_query_params ('
178 SELECT g.group_id, g.group_name,
179 SUM(s.downloads) AS downloads,
180 SUM(s.site_views) AS site_views,
181 SUM(s.subdomain_views) AS subdomain_views,
182 SUM(s.msg_posted) AS msg_posted,
183 SUM(s.bugs_opened) AS bugs_opened,
184 SUM(s.bugs_closed) AS bugs_closed,
185 SUM(s.support_opened) AS support_opened,
186 SUM(s.support_closed) AS support_closed,
187 SUM(s.patches_opened) AS patches_opened,
188 SUM(s.patches_closed) AS patches_closed,
189 SUM(s.tasks_opened) AS tasks_opened,
190 SUM(s.tasks_closed) AS tasks_closed,
191 SUM(s.artifacts_opened) AS artifacts_opened,
192 SUM(s.artifacts_closed) AS artifacts_closed,
193 SUM(s.cvs_checkouts) AS cvs_checkouts,
194 SUM(s.cvs_commits) AS cvs_commits,
195 SUM(s.cvs_adds) AS cvs_adds
196 FROM stats_project_vw s, groups g
197 WHERE s.group_id = g.group_id
198 GROUP BY g.group_id, g.group_name
199 ORDER BY ' . $order_clause,
202 return db_query_params ('
203 SELECT g.group_id, g.group_name, s.downloads, s.site_views,
204 s.subdomain_views, s.msg_posted, s.bugs_opened, s.bugs_closed,
205 s.support_opened, s.support_closed, s.patches_opened,
206 s.patches_closed, s.artifacts_opened, artifacts_closed, s.tasks_opened, s.tasks_closed,
207 s.cvs_checkouts, s.cvs_commits, s.cvs_adds
208 FROM stats_project_all_vw s, groups g
209 WHERE s.group_id = g.group_id
210 ORDER BY ' . $order_clause,
215 function stats_site_projects( $report, $orderby, $projects, $trove ) {
219 $res=stats_site_project_result( $report, $orderby, $projects, $trove );
220 // if there are any rows, we have valid data (or close enough).
221 if ( db_numrows( $res ) > 1 ) {
224 <table class="fullwidth">
226 <tr class="align-right top tableheading">
227 <td><strong><?php echo _('Project Name'); ?></strong></td>
228 <td colspan="2"><strong><?php echo _('Page Views'); ?></strong></td>
229 <?php if (forge_get_config('use_frs')) { ?>
230 <td><strong><?php echo _('Downloads'); ?></strong></td>
232 <?php if (forge_get_config('use_tracker')) { ?>
233 <td colspan="2"><strong><?php echo _('Bugs'); ?></strong></td>
234 <td colspan="2"><strong><?php echo _('Support'); ?></strong></td>
235 <td colspan="2"><strong><?php echo _('Patches'); ?></strong></td>
236 <td colspan="2"><strong><?php echo _('All Trkr'); ?></strong></td>
238 <?php if (forge_get_config('use_pm')) { ?>
239 <td colspan="2"><strong><?php echo _('Tasks'); ?></strong></td>
241 <?php if (forge_get_config('use_scm')) { ?>
242 <td colspan="3"><strong><?php echo _('SCM'); ?></strong></td>
244 <?php plugin_hook('stats_header_table'); ?>
249 // Build the query string to resort results.
250 $uri_string = "projects.php?report=" . $report;
251 if ( $trove_cat > 0 ) {
252 $uri_string .= "&trovecatid=" . $trove_cat;
254 if ( $trove_cat == -1 ) {
255 $uri_string .= "&projects=" . urlencode( implode( " ", $projects) );
257 $uri_string .= "&orderby=";
260 <tr class="align-right top tableheading">
262 <td><a href="<?php echo $uri_string; ?>site_views"><?php echo _('Site'); ?></a></td>
263 <td><a href="<?php echo $uri_string; ?>subdomain_views"><?php echo _('Subdomain'); ?></a></td>
264 <?php if (forge_get_config('use_frs')) { ?>
265 <td><a href="<?php echo $uri_string; ?>downloads"><?php echo _('Total'); ?></a></td>
267 <?php if (forge_get_config('use_tracker')) { ?>
268 <td><a href="<?php echo $uri_string; ?>bugs_opened"><?php echo _('Opened'); ?></a></td>
269 <td><a href="<?php echo $uri_string; ?>bugs_closed"><?php echo _('Closed'); ?></a></td>
270 <td><a href="<?php echo $uri_string; ?>support_opened"><?php echo _('Opened'); ?></a></td>
271 <td><a href="<?php echo $uri_string; ?>support_closed"><?php echo _('Closed'); ?></a></td>
272 <td><a href="<?php echo $uri_string; ?>patches_opened"><?php echo _('Opened'); ?></a></td>
273 <td><a href="<?php echo $uri_string; ?>patches_closed"><?php echo _('Closed'); ?></a></td>
274 <td><a href="<?php echo $uri_string; ?>artifacts_opened"><?php echo _('Opened'); ?></a></td>
275 <td><a href="<?php echo $uri_string; ?>artifacts_closed"><?php echo _('Closed'); ?></a></td>
277 <?php if (forge_get_config('use_pm')) { ?>
278 <td><a href="<?php echo $uri_string; ?>tasks_opened"><?php echo _('Opened'); ?></a></td>
279 <td><a href="<?php echo $uri_string; ?>tasks_closed"><?php echo _('Closed'); ?></a></td>
281 <?php if (forge_get_config('use_scm')) { ?>
282 <td><a href="<?php echo $uri_string; ?>cvs_checkouts"><?php echo _('Checkouts'); ?></a></td>
283 <td><a href="<?php echo $uri_string; ?>cvs_commits"><?php echo _('Commits'); ?></a></td>
284 <td><a href="<?php echo $uri_string; ?>cvs_adds"><?php echo _('Adds'); ?></a></td>
286 <?php plugin_hook('stats_detail_header_table'); ?>
291 while ( $row = db_fetch_array($res) ) {
292 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' style="text-align:right">'
293 . '<td>' . ($i + 1)." " . util_make_link ('/project/stats/?group_id='.$row["group_id"], $row["group_name"]) . '</td>'
294 . '<td> ' . number_format( $row["site_views"],0 ) . '</td>'
295 . '<td> ' . number_format( $row["subdomain_views"],0 ) . '</td>';
296 if (forge_get_config('use_frs')) {
297 print '<td> ' . number_format( $row["downloads"],0 ) . '</td>';
299 if (forge_get_config('use_tracker')) {
300 print '<td> ' . number_format( $row["bugs_opened"],0 ) . '</td>'
301 . '<td> ' . number_format( $row["bugs_closed"],0 ) . '</td>'
302 . '<td> ' . number_format( $row["support_opened"],0 ) . '</td>'
303 . '<td> ' . number_format( $row["support_closed"],0 ) . '</td>'
304 . '<td> ' . number_format( $row["patches_opened"],0 ) . '</td>'
305 . '<td> ' . number_format( $row["patches_closed"],0 ) . '</td>'
306 . '<td> ' . number_format( $row["artifacts_opened"],0 ) . '</td>'
307 . '<td> ' . number_format( $row["artifacts_closed"],0 ) . '</td>';
309 if (forge_get_config('use_pm')) {
310 print '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
311 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>';
313 if (forge_get_config('use_scm')) {
314 print '<td> ' . number_format( $row["cvs_checkouts"],0 ) . '</td>'
315 . '<td> ' . number_format( $row["cvs_commits"],0 ) . '</td>'
316 . '<td> ' . number_format( $row["cvs_adds"],0 ) . '</td>';
318 $hook_params = array();
319 $hook_params['group_id'] = $row["group_id"];
320 plugin_hook('stats_data',$hook_params);
321 print '</tr>' . "\n";
330 echo _('Query returned no valid data.')."\n";
338 function stats_site_projects_daily( $span ) {
341 // We now only have 30 & 7-day views
343 $span = util_ensure_value_in_set ($span,
345 $res = db_query_params ('SELECT * FROM stats_site_vw ORDER BY month DESC, day DESC',
350 // if there are any weeks, we have valid data.
351 if ( ($valid_days = db_numrows( $res )) >= 1 ) {
354 <h2><?php printf(_('Statistics for the past %1$s days'), $valid_days); ?></h2>
355 <table class="fullwidth">
356 <tr class="top align-right">
357 <td><strong><?php echo _('Day'); ?></strong></td>
358 <td><strong><?php echo _('Site Views'); ?></strong></td>
359 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
360 <td><strong><?php echo _('Downloads'); ?></strong></td>
361 <td><strong><?php echo _('Bugs'); ?></strong></td>
362 <td><strong><?php echo _('Support'); ?></strong></td>
363 <td><strong><?php echo _('Patches'); ?></strong></td>
364 <td><strong><?php echo _('Tasks'); ?></strong></td>
365 <td><strong><?php echo _('SCM'); ?></strong></td>
369 while ( $row = db_fetch_array($res) ) {
372 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' style="text-align:right">'
373 . '<td>' . gmstrftime("%d %b %Y", mktime(0,0,1,substr($row["month"],4,2),$row["day"],substr($row["month"],0,4)) ) . '</td>'
374 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
375 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
376 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
377 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
378 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
379 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
380 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
381 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
394 function stats_site_projects_monthly() {
397 $res = db_query_params ('SELECT * FROM stats_site_months
398 ORDER BY month DESC',
403 // if there are any weeks, we have valid data.
404 if ( ($valid_months = db_numrows( $res )) >= 1 ) {
408 <h2><?php printf(_('Statistics for the past %1$s months'), $valid_months); ?></h2>
409 <table class="fullwidth">
410 <tr class="top align-right">
411 <td><strong><?php echo _('Month'); ?>Month</strong></td>
412 <td><strong><?php echo _('Site Views'); ?></strong></td>
413 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
414 <td><strong><?php echo _('Downloads'); ?></strong></td>
415 <td><strong><?php echo _('Bugs'); ?></strong></td>
416 <td><strong><?php echo _('Support'); ?></strong></td>
417 <td><strong><?php echo _('Patches'); ?></strong></td>
418 <td><strong><?php echo _('All Trkr'); ?></strong></td>
419 <td><strong><?php echo _('Tasks'); ?></strong></td>
420 <td><strong><?php echo _('SCM'); ?></strong></td>
424 while ( $row = db_fetch_array($res) ) {
427 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . 'style="text-align:right">'
428 . '<td>' . $row['month'] . '</td>'
429 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
430 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
431 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
432 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
433 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
434 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
435 . '<td> ' . number_format($row["artifacts_opened"],0) . " (" . number_format($row["artifacts_closed"],0) . ')</td>'
436 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
437 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
450 function stats_site_aggregate( ) {
451 $res = db_query_params ('SELECT * FROM stats_site_all_vw',
453 $site_totals = db_fetch_array($res);
455 $res = db_query_params ('SELECT COUNT(*) AS count FROM groups WHERE status=$1',
457 $groups = db_fetch_array($res);
459 $res = db_query_params ('SELECT COUNT(*) AS count FROM users WHERE status=$1',
461 $users = db_fetch_array($res);
464 <h2><?php echo _('Current Aggregate Statistics for All Time'); ?></h2>
466 <table class="fullwidth">
468 <td><strong><?php echo _('Site Views'); ?></strong></td>
469 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
470 <td><strong><?php echo _('Downloads'); ?></strong></td>
471 <td><strong><?php echo _('Developers'); ?></strong></td>
472 <td><strong><?php echo _('Projects'); ?></strong></td>
476 <td><?php echo number_format( $site_totals["site_page_views"],0 ); ?></td>
477 <td><?php echo number_format( $site_totals["subdomain_views"],0 ); ?></td>
478 <td><?php echo number_format( $site_totals["downloads"],0 ); ?></td>
479 <td><?php echo number_format( $users["count"],0 ); ?></td>
480 <td><?php echo number_format( $groups["count"],0 ); ?></td>
487 function views_graph($monthly = 0) {
489 require_once $gfcommon.'reporting/Report.class.php';
490 $report = new Report();
492 $res = db_query_params('SELECT month,site_page_views AS site_views,subdomain_views
493 FROM stats_site_months ORDER BY month ASC',
496 $beg_year = date('Y',mktime(0,0,0,(date('m')-1),date('d'),date('Y')));
497 $beg_month = date('m',mktime(0,0,0,(date('m')-1),date('d'),date('Y')));
498 $beg_day = date('d',mktime(0,0,0,(date('m')-1),date('d'),date('Y')));
499 $res = db_query_params ('SELECT month,day,site_page_views AS site_views,subdomain_views
501 ( month = $1 AND day >= $2 ) OR ( month > $3 )
502 ORDER BY month ASC, day ASC',
503 array ("$beg_year$beg_month",
505 "$beg_year$beg_month"));
511 while ( $row = db_fetch_array($res) ) {
512 $xlabel[$i] = $row['month'] . ((isset($row['day'])) ? "/" . $row['day'] : '');
513 $ydata[$i] = $row['site_views'] + $row['subdomain_views'];
517 $monthStartArr = $report->getMonthStartArr();
518 $monthStartArrFormat = $report->getMonthStartArrFormat();
520 $chartid = '_views_graph';
521 echo '<script type="text/javascript">//<![CDATA['."\n";
522 echo 'var '.$chartid.'values = new Array();';
523 echo 'var plot'.$chartid.';';
524 for ($j = 0; $j < count($monthStartArrFormat); $j++) {
525 $key = array_search($monthStartArrFormat[$j], $xlabel);
526 if ($key !== FALSE) {
527 echo 'var '.$chartid.'datevalues = '.$ydata[$key].';';
529 echo 'var '.$chartid.'datevalues = 0;';
531 echo 'var '.$chartid.'date = '.$monthStartArrFormat[$j].';';
532 echo $chartid.'values.push(['.$chartid.'date, '.$chartid.'datevalues]);';
534 echo 'jQuery(document).ready(function(){
535 plot'.$chartid.' = jQuery.jqplot (\'chart'.$chartid.'\', ['.$chartid.'values], {
537 tickRenderer: jQuery.jqplot.CanvasAxisTickRenderer,
546 renderer: jQuery.jqplot.CategoryAxisRenderer,
547 label: \''.('Pages view per month').'\',
564 echo 'jQuery(window).resize(function() {
565 plot'.$chartid.'.replot();
567 echo '//]]></script>';
568 echo '<div id="chart'.$chartid.'"></div>';
570 echo '<p class="information">'._('Page view: no graph to display.').'</p>';
574 function users_graph() {
576 require_once $gfcommon.'reporting/Report.class.php';
577 $report = new Report();
578 $res = db_query_params ('SELECT month,day,new_users,new_projects FROM stats_site ORDER BY month ASC, day ASC',
587 $label[0] = _('New users');
588 $label[1] = _('New projects');
589 $monthStartArr = $report->getMonthStartArr();
590 $monthStartArrFormat = $report->getMonthStartArrFormat();
591 while ( $row = db_fetch_array($res) ) {
592 $xlabel[$i] = $row['month'] . ((isset($row['day'])) ? "/" . $row['day'] : '');
593 $ydata[0][$i] = $row['new_users'];
594 $ydata[1][$i] = $row['new_projects'];
597 if (count($ydata[0])) {
598 $chartid = '_newprojectuser';
599 echo '<script type="text/javascript">//<![CDATA['."\n";
600 echo 'var '.$chartid.'values = new Array();';
601 echo 'var '.$chartid.'labels = new Array();';
602 echo 'var '.$chartid.'series = new Array();';
603 echo 'var plot'.$chartid.';';
604 for ($z = 0; $z < count($ydata); $z++) {
605 echo $chartid.'values['.$z.'] = new Array();';
606 echo $chartid.'labels.push({label:\''.$label[$z].'\'});';
607 for ($j = 0; $j < count($monthStartArrFormat); $j++) {
608 $key = array_search($monthStartArrFormat[$j], $xlabel);
609 if ($key !== FALSE) {
610 echo 'var '.$chartid.'datevalues = '.$ydata[$z][$key].';';
611 if ($ydata[$z][$thekey] > $yMax) {
612 $yMax = $ydata[$z][$thekey];
615 echo 'var '.$chartid.'datevalues = 0;';
617 echo 'var '.$chartid.'date = '.$monthStartArrFormat[$j].';';
618 echo $chartid.'values['.$z.'].push(['.$chartid.'date, '.$chartid.'datevalues]);';
621 for ($z = 0; $z < count($ydata); $z++) {
622 echo $chartid.'series.push('.$chartid.'values['.$z.']);';
624 echo 'jQuery(document).ready(function(){
625 plot'.$chartid.' = jQuery.jqplot (\'chart'.$chartid.'\', '.$chartid.'series, {
627 tickRenderer: jQuery.jqplot.CanvasAxisTickRenderer,
635 show:true, location: \'ne\',
642 renderer: jQuery.jqplot.CategoryAxisRenderer,
643 label: \''.('New Users and new projects per month').'\',
659 echo 'jQuery(window).resize(function() {
660 plot'.$chartid.'.replot();
662 echo '//]]></script>';
663 echo '<div id="chart'.$chartid.'"></div>';
665 echo '<p class="information">'._('New users, new projects: no graph to display.').'</p>';
671 // c-file-style: "bsd"