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
14 function week_to_dates( $week, $year = 0 ) {
17 $year = gmstrftime("%Y", time() );
20 // One second into the New Year!
21 $beginning = gmmktime(0,0,0,1,1,$year);
22 while ( gmstrftime("%U", $beginning) < 1 ) {
23 // 86,400 seconds? That's almost exactly one day!
26 $beginning += (86400 * 7 * ($week - 1));
27 $end = $beginning + (86400 * 6);
29 return array( $beginning, $end );
33 function stats_util_sum_array( $sum, $add ) {
34 while( list( $key, $val ) = each( $add ) ) {
41 * generates the trove list in a select box format.
42 * contains the odd choices of "-2" and "-1" which mean "All projects
43 * and "special project list" respectively
45 function stats_generate_trove_pulldown( $selected_id = 0 ) {
47 SELECT trove_cat_id,fullpath
52 <select name="trovecatid">';
55 <option value="-2">'._('All Projects').'</option>
56 <option value="-1">'._('Special Projects').'</option>';
58 while ( $row = db_fetch_array($res) ) {
60 <option value="' . $row['trove_cat_id'] . '"'
61 . ( $selected_id == $row["trove_cat_id"] ? " selected=\"selected\"" : "" )
62 . ">" . $row["fullpath"] . '</option>';
70 function stats_trove_cat_to_name( $trovecatid ) {
75 WHERE trove_cat_id = '$trovecatid'");
77 if ( $row = db_fetch_array($res) ) {
78 return $row["fullpath"];
80 return sprintf(_(" (no category found with ID %d)"), $trovecatid) ;
85 function stats_generate_trove_grouplist( $trovecatid ) {
92 WHERE trove_cat_id='$trovecatid'");
94 print db_error( $res );
97 while ( $row = db_fetch_array($res) ) {
98 $results[$i++] = $row["group_id"];
105 function stats_site_projects_form( $report='last_30', $orderby = 'downloads', $projects = 0, $trovecat = 0 ) {
106 print '<form action="projects.php" method="get">' . "\n";
107 print '<table width="100%" cellpadding="0" cellspacing="0" class="tableheading">' . "\n";
109 print '<tr><td><strong>'._('Projects in trove category:').'</strong></td><td>';
110 stats_generate_trove_pulldown( $trovecat );
113 print '<tr><td><strong>'._('OR enter Special Project List:').'</strong></td>';
114 print '<td> <input type="text" width="100" name="projects" value="'. $projects . '" />';
115 print ' ('._('<strong>comma separated</strong> group_id\'s)').'</td></tr>';
117 print '<tr><td><strong>'._('Report:').'</strong></td><td>';
119 $reports_ids=array();
120 $reports_ids[]='last_30';
121 $reports_ids[]='all';
123 $reports_names=array();
124 $reports_names[]=_('last_30');
125 $reports_names[]=_('all');
127 echo html_build_select_box_from_arrays($reports_ids, $reports_names, 'report', $report, false);
131 print '<tr><td><strong>'._('View by:').'</strong></td><td>';
132 $orderby_vals = array("downloads",
148 print html_build_select_box_from_arrays ( $orderby_vals, $orderby_vals, "orderby", $orderby, false );
151 print '<tr><td colspan="2" style="text-align:center"> <input type="submit" value="'._('Generate Report').'" /> </td></tr>';
153 print '</table>' . "\n";
154 print '</form>' . "\n";
159 * New function to separate out the SQL so it may be reused in other
163 function stats_site_project_result( $report, $orderby, $projects, $trove ) {
165 // Determine if we are looking at ALL projects,
166 // a trove category, or a specific list
170 if ($trove == '-2') {
171 //do a query of ALL groups
173 } elseif ($trove == '-1') {
174 //do a query of just a specific list of passed in groups
175 $grp_str=" AND g.group_id IN (" . $projects . ") ";
178 $grp_str=" AND EXISTS
180 FROM trove_group_link
181 WHERE trove_cat_id ='$trove'
182 AND g.group_id=trove_group_link.group_id) ";
187 $orderby = "group_name";
190 if ($report == 'last_30') {
192 $sql = "SELECT g.group_id,
194 SUM(s.downloads) AS downloads,
195 SUM(s.site_views) AS site_views,
196 SUM(s.subdomain_views) AS subdomain_views,
197 SUM(s.msg_posted) AS msg_posted,
198 SUM(s.bugs_opened) AS bugs_opened,
199 SUM(s.bugs_closed) AS bugs_closed,
200 SUM(s.support_opened) AS support_opened,
201 SUM(s.support_closed) AS support_closed,
202 SUM(s.patches_opened) AS patches_opened,
203 SUM(s.patches_closed) AS patches_closed,
204 SUM(s.tasks_opened) AS tasks_opened,
205 SUM(s.tasks_closed) AS tasks_closed,
206 SUM(s.cvs_checkouts) AS cvs_checkouts,
207 SUM(s.cvs_commits) AS cvs_commits,
208 SUM(s.cvs_adds) AS cvs_adds
210 stats_project_vw s, groups g
212 s.group_id = g.group_id
214 GROUP BY g.group_id, g.group_name
215 ORDER BY $orderby DESC ";
219 $sql = "SELECT g.group_id,
237 stats_project_all_vw s, groups g
239 s.group_id = g.group_id
241 ORDER BY $orderby DESC ";
244 return db_query( $sql, 30, 0, SYS_DB_STATS);
248 function stats_site_projects( $report, $orderby, $projects, $trove ) {
252 $res=stats_site_project_result( $report, $orderby, $projects, $trove );
253 // if there are any rows, we have valid data (or close enough).
254 if ( db_numrows( $res ) > 1 ) {
257 <table width="100%" cellpadding="0" cellspacing="0" border="0">
259 <tr valign="top" align="right" class="tableheading">
260 <td><strong><?php echo _('Group Name'); ?></strong></td>
261 <td colspan="2"><strong><?php echo _('Page Views'); ?></strong></td>
262 <?php if ($GLOBALS['sys_use_frs']) { ?>
263 <td><strong><?php echo _('Downloads'); ?></strong></td>
265 <?php if ($GLOBALS['sys_use_tracker']) { ?>
266 <td colspan="2"><strong><?php echo _('Bugs'); ?></strong></td>
267 <td colspan="2"><strong><?php echo _('Support'); ?></strong></td>
268 <td colspan="2"><strong><?php echo _('Patches'); ?></strong></td>
269 <td colspan="2"><strong><?php echo _('All Trkr'); ?></strong></td>
271 <?php if ($GLOBALS['sys_use_pm']) { ?>
272 <td colspan="2"><strong><?php echo _('Tasks'); ?></strong></td>
274 <?php if ($GLOBALS['sys_use_cvs']) { ?>
275 <td colspan="3"><strong><?php echo _('CVS'); ?></strong></td>
281 // Build the query string to resort results.
282 $uri_string = "projects.php?report=" . $report;
283 if ( $trove_cat > 0 ) {
284 $uri_string .= "&trovecatid=" . $trove_cat;
286 if ( $trove_cat == -1 ) {
287 $uri_string .= "&projects=" . urlencode( implode( " ", $projects) );
289 $uri_string .= "&orderby=";
292 <tr valign="top" align="right" class="tableheading">
294 <td><a href="<?php echo $uri_string; ?>site_views"><?php echo _('Site'); ?></a></td>
295 <td><a href="<?php echo $uri_string; ?>subdomain_views"><?php echo _('Subdomain'); ?></a></td>
296 <?php if ($GLOBALS['sys_use_frs']) { ?>
297 <td><a href="<?php echo $uri_string; ?>downloads"><?php echo _('Total'); ?></a></td>
299 <?php if ($GLOBALS['sys_use_tracker']) { ?>
300 <td><a href="<?php echo $uri_string; ?>bugs_opened"><?php echo _('Opened'); ?></a></td>
301 <td><a href="<?php echo $uri_string; ?>bugs_closed"><?php echo _('Closed'); ?></a></td>
302 <td><a href="<?php echo $uri_string; ?>support_opened"><?php echo _('Opened'); ?></a></td>
303 <td><a href="<?php echo $uri_string; ?>support_closed"><?php echo _('Closed'); ?></a></td>
304 <td><a href="<?php echo $uri_string; ?>patches_opened"><?php echo _('Opened'); ?></a></td>
305 <td><a href="<?php echo $uri_string; ?>patches_closed"><?php echo _('Closed'); ?></a></td>
306 <td><a href="<?php echo $uri_string; ?>artifacts_opened"><?php echo _('Opened'); ?></a></td>
307 <td><a href="<?php echo $uri_string; ?>artifacts_closed"><?php echo _('Closed'); ?></a></td>
309 <?php if ($GLOBALS['sys_use_pm']) { ?>
310 <td><a href="<?php echo $uri_string; ?>tasks_opened"><?php echo _('Opened'); ?></a></td>
311 <td><a href="<?php echo $uri_string; ?>tasks_closed"><?php echo _('Closed'); ?></a></td>
313 <?php if ($GLOBALS['sys_use_scm']) { ?>
314 <td><a href="<?php echo $uri_string; ?>cvs_checkouts"><?php echo _('Checkouts'); ?></a></td>
315 <td><a href="<?php echo $uri_string; ?>cvs_commits"><?php echo _('Commits'); ?></a></td>
316 <td><a href="<?php echo $uri_string; ?>cvs_adds"><?php echo _('Adds'); ?></a></td>
322 while ( $row = db_fetch_array($res) ) {
323 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
324 . '<td>' . ($i + 1) . util_make_link ('/project/stats/?group_id='.$row["group_id"], $row["group_name"]) . '</td>'
325 . '<td> ' . number_format( $row["site_views"],0 ) . '</td>'
326 . '<td> ' . number_format( $row["subdomain_views"],0 ) . '</td>';
327 if ($GLOBALS['sys_use_frs']) {
328 print '<td> ' . number_format( $row["downloads"],0 ) . '</td>';
330 if ($GLOBALS['sys_use_tracker']) {
331 print '<td> ' . number_format( $row["bugs_opened"],0 ) . '</td>'
332 . '<td> ' . number_format( $row["bugs_closed"],0 ) . '</td>'
333 . '<td> ' . number_format( $row["support_opened"],0 ) . '</td>'
334 . '<td> ' . number_format( $row["support_closed"],0 ) . '</td>'
335 . '<td> ' . number_format( $row["patches_opened"],0 ) . '</td>'
336 . '<td> ' . number_format( $row["patches_closed"],0 ) . '</td>'
337 . '<td> ' . number_format( $row["artifacts_opened"],0 ) . '</td>'
338 . '<td> ' . number_format( $row["artifacts_closed"],0 ) . '</td>';
340 if ($GLOBALS['sys_use_pm']) {
341 print '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
342 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>';
344 if ($GLOBALS['sys_use_scm']) {
345 print '<td> ' . number_format( $row["cvs_checkouts"],0 ) . '</td>'
346 . '<td> ' . number_format( $row["cvs_commits"],0 ) . '</td>'
347 . '<td> ' . number_format( $row["cvs_adds"],0 ) . '</td>';
349 print '</tr>' . "\n";
351 $sum = stats_util_sum_array( $sum, $row );
359 echo _('Query returned no valid data.')."\n";
367 function stats_site_projects_daily( $span ) {
370 // We now only have 30 & 7-day views
372 if ( $span != 30 && $span != 7) {
376 $sql="SELECT * FROM stats_site_vw
377 ORDER BY month DESC, day DESC";
380 $res = db_query($sql, 30, 0, SYS_DB_STATS);
382 $res = db_query($sql, 7, 0, SYS_DB_STATS);
387 // if there are any weeks, we have valid data.
388 if ( ($valid_days = db_numrows( $res )) > 1 ) {
391 <p><strong><?php printf(_('Statistics for the past %1$s days'), $valid_days); ?></strong></p>
392 <table width="100%" cellpadding="0" cellspacing="0" border="0">
393 <tr valign="top" align="right">
394 <td><strong><?php echo _('Day'); ?></strong></td>
395 <td><strong><?php echo _('Site Views'); ?></strong></td>
396 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
397 <td><strong><?php echo _('Downloads'); ?></strong></td>
398 <td><strong><?php echo _('Bugs'); ?></strong></td>
399 <td><strong><?php echo _('Support'); ?></strong></td>
400 <td><strong><?php echo _('Patches'); ?></strong></td>
401 <td><strong><?php echo _('Tasks'); ?></strong></td>
402 <td><strong><?php echo _('CVS'); ?></strong></td>
406 while ( $row = db_fetch_array($res) ) {
409 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
410 . '<td>' . gmstrftime("%d %b %Y", mktime(0,0,1,substr($row["month"],4,2),$row["day"],substr($row["month"],0,4)) ) . '</td>'
411 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
412 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
413 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
414 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
415 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
416 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
417 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
418 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
431 function stats_site_projects_monthly() {
433 $sql="SELECT * FROM stats_site_months
434 ORDER BY month DESC";
436 $res=db_query($sql, -1, 0, SYS_DB_STATS);
440 // if there are any weeks, we have valid data.
441 if ( ($valid_months = db_numrows( $res )) > 1 ) {
444 <p><strong><?php printf(_('Statistics for the past %1$s months'), $valid_months); ?></strong></p>
446 <table width="100%" cellpadding="0" cellspacing="0" border="0">
447 <tr valign="top" align="right">
448 <td><strong><?php echo _('Month'); ?>Month</strong></td>
449 <td><strong><?php echo _('Site Views'); ?></strong></td>
450 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
451 <td><strong><?php echo _('Downloads'); ?></strong></td>
452 <td><strong><?php echo _('Bugs'); ?></strong></td>
453 <td><strong><?php echo _('Support'); ?></strong></td>
454 <td><strong><?php echo _('Patches'); ?></strong></td>
455 <td><strong><?php echo _('All Trkr'); ?></strong></td>
456 <td><strong><?php echo _('Tasks'); ?></strong></td>
457 <td><strong><?php echo _('CVS'); ?></strong></td>
461 while ( $row = db_fetch_array($res) ) {
464 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . 'align="right">'
465 . '<td>' . $row['month'] . '</td>'
466 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
467 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
468 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
469 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
470 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
471 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
472 . '<td> ' . number_format($row["artifacts_opened"],0) . " (" . number_format($row["artifacts_closed"],0) . ')</td>'
473 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
474 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
487 function stats_site_aggregate( ) {
488 $res = db_query("SELECT * FROM stats_site_all_vw", -1, 0, SYS_DB_STATS);
489 $site_totals = db_fetch_array($res);
491 $sql = "SELECT COUNT(*) AS count FROM groups WHERE status='A'";
492 $res = db_query( $sql );
493 $groups = db_fetch_array($res);
495 $sql = "SELECT COUNT(*) AS count FROM users WHERE status='A'";
496 $res = db_query( $sql );
497 $users = db_fetch_array($res);
501 <p><strong><?php echo _('Current Aggregate Statistics for All Time'); ?></strong></p>
503 <table width="100%" cellpadding="0" cellspacing="0" border="0">
505 <td><strong><?php echo _('Site Views'); ?></strong></td>
506 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
507 <td><strong><?php echo _('Downloads'); ?></strong></td>
508 <td><strong><?php echo _('Developers'); ?></strong></td>
509 <td><strong><?php echo _('Projects'); ?></strong></td>
513 <td><?php echo number_format( $site_totals["site_page_views"],0 ); ?></td>
514 <td><?php echo number_format( $site_totals["subdomain_views"],0 ); ?></td>
515 <td><?php echo number_format( $site_totals["downloads"],0 ); ?></td>
516 <td><?php echo number_format( $users["count"],0 ); ?></td>
517 <td><?php echo number_format( $groups["count"],0 ); ?></td>
526 // c-file-style: "bsd"