4 * SourceForge Sitewide Statistics - stats common module
6 * SourceForge: Breaking Down the Barriers to Open Source Development
7 * Copyright 1999-2001 (c) VA Linux Systems
8 * http://sourceforge.net
12 $allowed_orderby_vals = array('downloads',
29 function week_to_dates( $week, $year = 0 ) {
32 $year = gmstrftime("%Y", time() );
35 // One second into the New Year!
36 $beginning = gmmktime(0,0,0,1,1,$year);
37 while ( gmstrftime("%U", $beginning) < 1 ) {
38 // 86,400 seconds? That's almost exactly one day!
41 $beginning += (86400 * 7 * ($week - 1));
42 $end = $beginning + (86400 * 6);
44 return array( $beginning, $end );
48 function stats_util_sum_array( $sum, $add ) {
49 while( list( $key, $val ) = each( $add ) ) {
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>';
86 function stats_trove_cat_to_name( $trovecatid ) {
88 $res = db_query_params ('
91 WHERE trove_cat_id = $1',
94 if ( $row = db_fetch_array($res) ) {
95 return $row["fullpath"];
97 return sprintf(_(" (no category found with ID %d)"), $trovecatid) ;
102 function stats_generate_trove_grouplist( $trovecatid ) {
106 $res = db_query_params ('
108 FROM trove_group_link
109 WHERE trove_cat_id=$1',
112 print db_error( $res );
115 while ( $row = db_fetch_array($res) ) {
116 $results[$i++] = $row["group_id"];
123 function stats_site_projects_form( $report='last_30', $orderby = 'downloads', $projects = 0, $trovecat = 0 ) {
124 global $allowed_orderby_vals ;
126 print '<form action="projects.php" method="get">' . "\n";
127 print '<table width="100%" cellpadding="0" cellspacing="0" class="tableheading">' . "\n";
129 print '<tr><td><strong>'._('Projects in trove category:').'</strong></td><td>';
130 stats_generate_trove_pulldown( $trovecat );
133 print '<tr><td><strong>'._('OR enter Special Project List:').'</strong></td>';
134 print '<td> <input type="text" width="100" name="projects" value="'. $projects . '" />';
135 print ' ('._('<strong>comma separated</strong> group_id\'s)').'</td></tr>';
137 print '<tr><td><strong>'._('Report:').'</strong></td><td>';
139 $reports_ids=array();
140 $reports_ids[]='last_30';
141 $reports_ids[]='all';
143 $reports_names=array();
144 $reports_names[]=_('last_30');
145 $reports_names[]=_('all');
147 echo html_build_select_box_from_arrays($reports_ids, $reports_names, 'report', $report, false);
151 print '<tr><td><strong>'._('View by:').'</strong></td><td>';
153 print html_build_select_box_from_arrays ( $allowed_orderby_vals, $allowed_orderby_vals, "orderby", $orderby, false );
156 print '<tr><td colspan="2" style="text-align:center"> <input type="submit" value="'._('Generate Report').'" /> </td></tr>';
158 print '</table>' . "\n";
159 print '</form>' . "\n";
164 * New function to separate out the SQL so it may be reused in other
168 function stats_site_project_result( $report, $orderby, $projects, $trove ) {
169 global $allowed_orderby_vals ;
172 $order_clause = 'group_name ASC' ;
174 $order_clause = util_ensure_value_in_set ($orderby,
175 $allowed_orderby_vals) ;
176 $order_clause .= ' DESC, group_name ASC';
179 if ($report == 'last_30') {
180 return db_query_params ('
181 SELECT g.group_id, g.group_name,
182 SUM(s.downloads) AS downloads,
183 SUM(s.site_views) AS site_views,
184 SUM(s.subdomain_views) AS subdomain_views,
185 SUM(s.msg_posted) AS msg_posted,
186 SUM(s.bugs_opened) AS bugs_opened,
187 SUM(s.bugs_closed) AS bugs_closed,
188 SUM(s.support_opened) AS support_opened,
189 SUM(s.support_closed) AS support_closed,
190 SUM(s.patches_opened) AS patches_opened,
191 SUM(s.patches_closed) AS patches_closed,
192 SUM(s.tasks_opened) AS tasks_opened,
193 SUM(s.tasks_closed) AS tasks_closed,
194 SUM(s.cvs_checkouts) AS cvs_checkouts,
195 SUM(s.cvs_commits) AS cvs_commits,
196 SUM(s.cvs_adds) AS cvs_adds
197 FROM stats_project_vw s, groups g
198 WHERE s.group_id = g.group_id
199 GROUP BY g.group_id, g.group_name
200 ORDER BY ' . $order_clause,
203 return db_query_params ('
204 SELECT g.group_id, g.group_name, s.downloads, s.site_views,
205 s.subdomain_views, s.msg_posted, s.bugs_opened, s.bugs_closed,
206 s.support_opened, s.support_closed, s.patches_opened,
207 s.patches_closed, s.tasks_opened, s.tasks_closed,
208 s.cvs_checkouts, s.cvs_commits, s.cvs_adds
209 FROM stats_project_all_vw s, groups g
210 WHERE s.group_id = g.group_id
211 ORDER BY ' . $order_clause,
216 function stats_site_projects( $report, $orderby, $projects, $trove ) {
220 $res=stats_site_project_result( $report, $orderby, $projects, $trove );
221 // if there are any rows, we have valid data (or close enough).
222 if ( db_numrows( $res ) > 1 ) {
225 <table width="100%" cellpadding="0" cellspacing="0" border="0">
227 <tr valign="top" align="right" class="tableheading">
228 <td><strong><?php echo _('Group Name'); ?></strong></td>
229 <td colspan="2"><strong><?php echo _('Page Views'); ?></strong></td>
230 <?php if ($GLOBALS['sys_use_frs']) { ?>
231 <td><strong><?php echo _('Downloads'); ?></strong></td>
233 <?php if ($GLOBALS['sys_use_tracker']) { ?>
234 <td colspan="2"><strong><?php echo _('Bugs'); ?></strong></td>
235 <td colspan="2"><strong><?php echo _('Support'); ?></strong></td>
236 <td colspan="2"><strong><?php echo _('Patches'); ?></strong></td>
237 <td colspan="2"><strong><?php echo _('All Trkr'); ?></strong></td>
239 <?php if ($GLOBALS['sys_use_pm']) { ?>
240 <td colspan="2"><strong><?php echo _('Tasks'); ?></strong></td>
242 <?php if ($GLOBALS['sys_use_scm']) { ?>
243 <td colspan="3"><strong><?php echo _('SCM'); ?></strong></td>
245 <?php plugin_hook('stats_header_table'); ?>
250 // Build the query string to resort results.
251 $uri_string = "projects.php?report=" . $report;
252 if ( $trove_cat > 0 ) {
253 $uri_string .= "&trovecatid=" . $trove_cat;
255 if ( $trove_cat == -1 ) {
256 $uri_string .= "&projects=" . urlencode( implode( " ", $projects) );
258 $uri_string .= "&orderby=";
261 <tr valign="top" align="right" class="tableheading">
263 <td><a href="<?php echo $uri_string; ?>site_views"><?php echo _('Site'); ?></a></td>
264 <td><a href="<?php echo $uri_string; ?>subdomain_views"><?php echo _('Subdomain'); ?></a></td>
265 <?php if ($GLOBALS['sys_use_frs']) { ?>
266 <td><a href="<?php echo $uri_string; ?>downloads"><?php echo _('Total'); ?></a></td>
268 <?php if ($GLOBALS['sys_use_tracker']) { ?>
269 <td><a href="<?php echo $uri_string; ?>bugs_opened"><?php echo _('Opened'); ?></a></td>
270 <td><a href="<?php echo $uri_string; ?>bugs_closed"><?php echo _('Closed'); ?></a></td>
271 <td><a href="<?php echo $uri_string; ?>support_opened"><?php echo _('Opened'); ?></a></td>
272 <td><a href="<?php echo $uri_string; ?>support_closed"><?php echo _('Closed'); ?></a></td>
273 <td><a href="<?php echo $uri_string; ?>patches_opened"><?php echo _('Opened'); ?></a></td>
274 <td><a href="<?php echo $uri_string; ?>patches_closed"><?php echo _('Closed'); ?></a></td>
275 <td><a href="<?php echo $uri_string; ?>artifacts_opened"><?php echo _('Opened'); ?></a></td>
276 <td><a href="<?php echo $uri_string; ?>artifacts_closed"><?php echo _('Closed'); ?></a></td>
278 <?php if ($GLOBALS['sys_use_pm']) { ?>
279 <td><a href="<?php echo $uri_string; ?>tasks_opened"><?php echo _('Opened'); ?></a></td>
280 <td><a href="<?php echo $uri_string; ?>tasks_closed"><?php echo _('Closed'); ?></a></td>
282 <?php if ($GLOBALS['sys_use_scm']) { ?>
283 <td><a href="<?php echo $uri_string; ?>cvs_checkouts"><?php echo _('Checkouts'); ?></a></td>
284 <td><a href="<?php echo $uri_string; ?>cvs_commits"><?php echo _('Commits'); ?></a></td>
285 <td><a href="<?php echo $uri_string; ?>cvs_adds"><?php echo _('Adds'); ?></a></td>
287 <?php plugin_hook('stats_detail_header_table'); ?>
292 while ( $row = db_fetch_array($res) ) {
293 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
294 . '<td>' . ($i + 1)." " . util_make_link ('/project/stats/?group_id='.$row["group_id"], $row["group_name"]) . '</td>'
295 . '<td> ' . number_format( $row["site_views"],0 ) . '</td>'
296 . '<td> ' . number_format( $row["subdomain_views"],0 ) . '</td>';
297 if ($GLOBALS['sys_use_frs']) {
298 print '<td> ' . number_format( $row["downloads"],0 ) . '</td>';
300 if ($GLOBALS['sys_use_tracker']) {
301 print '<td> ' . number_format( $row["bugs_opened"],0 ) . '</td>'
302 . '<td> ' . number_format( $row["bugs_closed"],0 ) . '</td>'
303 . '<td> ' . number_format( $row["support_opened"],0 ) . '</td>'
304 . '<td> ' . number_format( $row["support_closed"],0 ) . '</td>'
305 . '<td> ' . number_format( $row["patches_opened"],0 ) . '</td>'
306 . '<td> ' . number_format( $row["patches_closed"],0 ) . '</td>'
307 . '<td> ' . number_format( $row["artifacts_opened"],0 ) . '</td>'
308 . '<td> ' . number_format( $row["artifacts_closed"],0 ) . '</td>';
310 if ($GLOBALS['sys_use_pm']) {
311 print '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
312 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>';
314 if ($GLOBALS['sys_use_scm']) {
315 print '<td> ' . number_format( $row["cvs_checkouts"],0 ) . '</td>'
316 . '<td> ' . number_format( $row["cvs_commits"],0 ) . '</td>'
317 . '<td> ' . number_format( $row["cvs_adds"],0 ) . '</td>';
319 $hook_params = array();
320 $hook_params['group_id'] = $row["group_id"];
321 plugin_hook('stats_data',$hook_params);
322 print '</tr>' . "\n";
324 $sum = stats_util_sum_array( $sum, $row );
332 echo _('Query returned no valid data.')."\n";
340 function stats_site_projects_daily( $span ) {
343 // We now only have 30 & 7-day views
345 $span = util_ensure_value_in_set ($span,
347 $res = db_query_params ('SELECT * FROM stats_site_vw ORDER BY month DESC, day DESC',
352 // if there are any weeks, we have valid data.
353 if ( ($valid_days = db_numrows( $res )) > 1 ) {
356 <h2><?php printf(_('Statistics for the past %1$s days'), $valid_days); ?></h2>
357 <table width="100%" cellpadding="0" cellspacing="0" border="0">
358 <tr valign="top" align="right">
359 <td><strong><?php echo _('Day'); ?></strong></td>
360 <td><strong><?php echo _('Site Views'); ?></strong></td>
361 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
362 <td><strong><?php echo _('Downloads'); ?></strong></td>
363 <td><strong><?php echo _('Bugs'); ?></strong></td>
364 <td><strong><?php echo _('Support'); ?></strong></td>
365 <td><strong><?php echo _('Patches'); ?></strong></td>
366 <td><strong><?php echo _('Tasks'); ?></strong></td>
367 <td><strong><?php echo _('SCM'); ?></strong></td>
371 while ( $row = db_fetch_array($res) ) {
374 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
375 . '<td>' . gmstrftime("%d %b %Y", mktime(0,0,1,substr($row["month"],4,2),$row["day"],substr($row["month"],0,4)) ) . '</td>'
376 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
377 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
378 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
379 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
380 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
381 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
382 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
383 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
396 function stats_site_projects_monthly() {
399 $res = db_query_params ('SELECT * FROM stats_site_months
400 ORDER BY month DESC',
405 // if there are any weeks, we have valid data.
406 if ( ($valid_months = db_numrows( $res )) > 1 ) {
410 <h2><?php printf(_('Statistics for the past %1$s months'), $valid_months); ?></h2>
411 <table width="100%" cellpadding="0" cellspacing="0" border="0">
412 <tr valign="top" align="right">
413 <td><strong><?php echo _('Month'); ?>Month</strong></td>
414 <td><strong><?php echo _('Site Views'); ?></strong></td>
415 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
416 <td><strong><?php echo _('Downloads'); ?></strong></td>
417 <td><strong><?php echo _('Bugs'); ?></strong></td>
418 <td><strong><?php echo _('Support'); ?></strong></td>
419 <td><strong><?php echo _('Patches'); ?></strong></td>
420 <td><strong><?php echo _('All Trkr'); ?></strong></td>
421 <td><strong><?php echo _('Tasks'); ?></strong></td>
422 <td><strong><?php echo _('SCM'); ?></strong></td>
426 while ( $row = db_fetch_array($res) ) {
429 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . 'align="right">'
430 . '<td>' . $row['month'] . '</td>'
431 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
432 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
433 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
434 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
435 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
436 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
437 . '<td> ' . number_format($row["artifacts_opened"],0) . " (" . number_format($row["artifacts_closed"],0) . ')</td>'
438 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
439 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
452 function stats_site_aggregate( ) {
453 $res = db_query_params ('SELECT * FROM stats_site_all_vw',
455 $site_totals = db_fetch_array($res);
458 $res = db_query_params ('SELECT COUNT(*) AS count FROM groups WHERE status=$1',
460 $groups = db_fetch_array($res);
463 $res = db_query_params ('SELECT COUNT(*) AS count FROM users WHERE status=$1',
465 $users = db_fetch_array($res);
469 <h2><?php echo _('Current Aggregate Statistics for All Time'); ?></h2>
471 <table width="100%" cellpadding="0" cellspacing="0" border="0">
473 <td><strong><?php echo _('Site Views'); ?></strong></td>
474 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
475 <td><strong><?php echo _('Downloads'); ?></strong></td>
476 <td><strong><?php echo _('Developers'); ?></strong></td>
477 <td><strong><?php echo _('Projects'); ?></strong></td>
481 <td><?php echo number_format( $site_totals["site_page_views"],0 ); ?></td>
482 <td><?php echo number_format( $site_totals["subdomain_views"],0 ); ?></td>
483 <td><?php echo number_format( $site_totals["downloads"],0 ); ?></td>
484 <td><?php echo number_format( $users["count"],0 ); ?></td>
485 <td><?php echo number_format( $groups["count"],0 ); ?></td>
494 // c-file-style: "bsd"