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 <td><strong><?php echo _('Downloads'); ?></strong></td>
263 <td colspan="2"><strong><?php echo _('Bugs'); ?></strong></td>
264 <td colspan="2"><strong><?php echo _('Support'); ?></strong></td>
265 <td colspan="2"><strong><?php echo _('Patches'); ?></strong></td>
266 <td colspan="2"><strong><?php echo _('All Trkr'); ?></strong></td>
267 <td colspan="2"><strong><?php echo _('Tasks'); ?></strong></td>
268 <td colspan="3"><strong><?php echo _('CVS'); ?></strong></td>
273 // Build the query string to resort results.
274 $uri_string = "projects.php?report=" . $report;
275 if ( $trove_cat > 0 ) {
276 $uri_string .= "&trovecatid=" . $trove_cat;
278 if ( $trove_cat == -1 ) {
279 $uri_string .= "&projects=" . urlencode( implode( " ", $projects) );
281 $uri_string .= "&orderby=";
284 <tr valign="top" align="right" class="tableheading">
286 <td><a href="<?php echo $uri_string; ?>site_views"><?php echo _('Site'); ?></a></td>
287 <td><a href="<?php echo $uri_string; ?>subdomain_views"><?php echo _('Subdomain'); ?></a></td>
288 <td><a href="<?php echo $uri_string; ?>downloads"><?php echo _('Total'); ?></a></td>
289 <td><a href="<?php echo $uri_string; ?>bugs_opened"><?php echo _('Opn'); ?></a></td>
290 <td><a href="<?php echo $uri_string; ?>bugs_closed"><?php echo _('Cls'); ?></a></td>
291 <td><a href="<?php echo $uri_string; ?>support_opened"><?php echo _('Opn'); ?></a></td>
292 <td><a href="<?php echo $uri_string; ?>support_closed"><?php echo _('Cls'); ?></a></td>
293 <td><a href="<?php echo $uri_string; ?>patches_opened"><?php echo _('Opn'); ?></a></td>
294 <td><a href="<?php echo $uri_string; ?>patches_closed"><?php echo _('Cls'); ?></a></td>
295 <td><a href="<?php echo $uri_string; ?>artifacts_opened"><?php echo _('Opn'); ?></a></td>
296 <td><a href="<?php echo $uri_string; ?>artifacts_closed"><?php echo _('Cls'); ?></a></td>
297 <td><a href="<?php echo $uri_string; ?>tasks_opened"><?php echo _('Opn'); ?></a></td>
298 <td><a href="<?php echo $uri_string; ?>tasks_closed"><?php echo _('Cls'); ?></a></td>
299 <td><a href="<?php echo $uri_string; ?>cvs_checkouts"><?php echo _('CO\'s'); ?></a></td>
300 <td><a href="<?php echo $uri_string; ?>cvs_commits"><?php echo _('Comm\'s'); ?></a></td>
301 <td><a href="<?php echo $uri_string; ?>cvs_adds"><?php echo _('Adds'); ?></a></td>
306 while ( $row = db_fetch_array($res) ) {
307 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
308 . '<td>' . ($i + 1) . util_make_link ('/project/stats/?group_id='.$row["group_id"], $row["group_name"]) . '</td>'
309 . '<td> ' . number_format( $row["site_views"],0 ) . '</td>'
310 . '<td> ' . number_format( $row["subdomain_views"],0 ) . '</td>'
311 . '<td> ' . number_format( $row["downloads"],0 ) . '</td>'
312 . '<td> ' . number_format( $row["bugs_opened"],0 ) . '</td>'
313 . '<td> ' . number_format( $row["bugs_closed"],0 ) . '</td>'
314 . '<td> ' . number_format( $row["support_opened"],0 ) . '</td>'
315 . '<td> ' . number_format( $row["support_closed"],0 ) . '</td>'
316 . '<td> ' . number_format( $row["patches_opened"],0 ) . '</td>'
317 . '<td> ' . number_format( $row["patches_closed"],0 ) . '</td>'
318 . '<td> ' . number_format( $row["artifacts_opened"],0 ) . '</td>'
319 . '<td> ' . number_format( $row["artifacts_closed"],0 ) . '</td>'
320 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
321 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
322 . '<td> ' . number_format( $row["cvs_checkouts"],0 ) . '</td>'
323 . '<td> ' . number_format( $row["cvs_commits"],0 ) . '</td>'
324 . '<td> ' . number_format( $row["cvs_adds"],0 ) . '</td>'
327 $sum = stats_util_sum_array( $sum, $row );
335 echo _('Query returned no valid data.')."\n";
343 function stats_site_projects_daily( $span ) {
346 // We now only have 30 & 7-day views
348 if ( $span != 30 && $span != 7) {
352 $sql="SELECT * FROM stats_site_vw
353 ORDER BY month DESC, day DESC";
356 $res = db_query($sql, 30, 0, SYS_DB_STATS);
358 $res = db_query($sql, 7, 0, SYS_DB_STATS);
363 // if there are any weeks, we have valid data.
364 if ( ($valid_days = db_numrows( $res )) > 1 ) {
367 <p><strong><?php printf(_('Statistics for the past %1$s days'), $valid_days); ?></strong></p>
368 <table width="100%" cellpadding="0" cellspacing="0" border="0">
369 <tr valign="top" align="right">
370 <td><strong><?php echo _('Day'); ?></strong></td>
371 <td><strong><?php echo _('Site Views'); ?></strong></td>
372 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
373 <td><strong><?php echo _('Downloads'); ?></strong></td>
374 <td><strong><?php echo _('Bugs'); ?></strong></td>
375 <td><strong><?php echo _('Support'); ?></strong></td>
376 <td><strong><?php echo _('Patches'); ?></strong></td>
377 <td><strong><?php echo _('Tasks'); ?></strong></td>
378 <td><strong><?php echo _('CVS'); ?></strong></td>
382 while ( $row = db_fetch_array($res) ) {
385 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
386 . '<td>' . gmstrftime("%d %b %Y", mktime(0,0,1,substr($row["month"],4,2),$row["day"],substr($row["month"],0,4)) ) . '</td>'
387 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
388 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
389 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
390 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
391 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
392 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
393 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
394 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
407 function stats_site_projects_monthly() {
409 $sql="SELECT * FROM stats_site_months
410 ORDER BY month DESC";
412 $res=db_query($sql, -1, 0, SYS_DB_STATS);
416 // if there are any weeks, we have valid data.
417 if ( ($valid_months = db_numrows( $res )) > 1 ) {
420 <p><strong><?php printf(_('Statistics for the past %1$s months'), $valid_months); ?></strong></p>
422 <table width="100%" cellpadding="0" cellspacing="0" border="0">
423 <tr valign="top" align="right">
424 <td><strong><?php echo _('Month'); ?>Month</strong></td>
425 <td><strong><?php echo _('Site Views'); ?></strong></td>
426 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
427 <td><strong><?php echo _('Downloads'); ?></strong></td>
428 <td><strong><?php echo _('Bugs'); ?></strong></td>
429 <td><strong><?php echo _('Support'); ?></strong></td>
430 <td><strong><?php echo _('Patches'); ?></strong></td>
431 <td><strong><?php echo _('All Trkr'); ?></strong></td>
432 <td><strong><?php echo _('Tasks'); ?></strong></td>
433 <td><strong><?php echo _('CVS'); ?></strong></td>
437 while ( $row = db_fetch_array($res) ) {
440 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . 'align="right">'
441 . '<td>' . $row['month'] . '</td>'
442 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
443 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
444 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
445 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
446 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
447 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
448 . '<td> ' . number_format($row["artifacts_opened"],0) . " (" . number_format($row["artifacts_closed"],0) . ')</td>'
449 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
450 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
463 function stats_site_aggregate( ) {
464 $res = db_query("SELECT * FROM stats_site_all_vw", -1, 0, SYS_DB_STATS);
465 $site_totals = db_fetch_array($res);
467 $sql = "SELECT COUNT(*) AS count FROM groups WHERE status='A'";
468 $res = db_query( $sql );
469 $groups = db_fetch_array($res);
471 $sql = "SELECT COUNT(*) AS count FROM users WHERE status='A'";
472 $res = db_query( $sql );
473 $users = db_fetch_array($res);
477 <p><strong><?php echo _('Current Aggregate Statistics for All Time'); ?></strong></p>
479 <table width="100%" cellpadding="0" cellspacing="0" border="0">
481 <td><strong><?php echo _('Site Views'); ?></strong></td>
482 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
483 <td><strong><?php echo _('Downloads'); ?></strong></td>
484 <td><strong><?php echo _('Developers'); ?></strong></td>
485 <td><strong><?php echo _('Projects'); ?></strong></td>
489 <td><?php echo number_format( $site_totals["site_page_views"],0 ); ?></td>
490 <td><?php echo number_format( $site_totals["subdomain_views"],0 ); ?></td>
491 <td><?php echo number_format( $site_totals["downloads"],0 ); ?></td>
492 <td><?php echo number_format( $users["count"],0 ); ?></td>
493 <td><?php echo number_format( $groups["count"],0 ); ?></td>
502 // c-file-style: "bsd"