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
16 function week_to_dates( $week, $year = 0 ) {
19 $year = gmstrftime("%Y", time() );
22 // One second into the New Year!
23 $beginning = gmmktime(0,0,0,1,1,$year);
24 while ( gmstrftime("%U", $beginning) < 1 ) {
25 // 86,400 seconds? That's almost exactly one day!
28 $beginning += (86400 * 7 * ($week - 1));
29 $end = $beginning + (86400 * 6);
31 return array( $beginning, $end );
35 function stats_util_sum_array( $sum, $add ) {
36 while( list( $key, $val ) = each( $add ) ) {
43 * generates the trove list in a select box format.
44 * contains the odd choices of "-2" and "-1" which mean "All projects
45 * and "special project list" respectively
47 function stats_generate_trove_pulldown( $selected_id = 0 ) {
50 SELECT trove_cat_id,fullpath
55 <select name="trovecatid">';
58 <option value="-2">All Projects</option>
59 <option value="-1">Special Project List</option>';
61 while ( $row = db_fetch_array($res) ) {
63 <option value="' . $row['trove_cat_id'] . '"'
64 . ( $selected_id == $row["trove_cat_id"] ? " selected=\"selected\"" : "" )
65 . ">" . $row["fullpath"] . '</option>';
73 function stats_trove_cat_to_name( $trovecatid ) {
78 WHERE trove_cat_id = '$trovecatid'");
80 if ( $row = db_fetch_array($res) ) {
81 return $row["fullpath"];
83 return " ( $trovecatid returned no category name ) ";
88 function stats_generate_trove_grouplist( $trovecatid ) {
95 WHERE trove_cat_id='$trovecatid'");
97 print db_error( $res );
100 while ( $row = db_fetch_array($res) ) {
101 $results[$i++] = $row["group_id"];
108 function stats_site_projects_form( $report='last_30', $orderby = 'downloads', $projects = 0, $trovecat = 0 ) {
110 print '<form action="projects.php" method="get">' . "\n";
111 print '<table width="100%" cellpadding="0" cellspacing="0" style="background-color:#eeeeee">' . "\n";
113 print '<tr><td><strong>Projects in trove category: </strong></td><td>';
114 stats_generate_trove_pulldown( $trovecat );
117 print '<tr><td><strong>OR enter Special Project List: </strong></td>';
118 print '<td> <input type="text" width="100" name="projects" value="'. $projects . '" />';
119 print ' (<strong>comma separated</strong> group_id\'s) </td></tr>';
121 print '<tr><td><strong>Report: </strong></td><td>';
123 $reports_ids=array();
124 $reports_ids[]='last_30';
125 $reports_ids[]='all';
127 $reports_names=array();
128 $reports_names[]='Last 30 Days';
129 $reports_names[]='All Time';
131 echo html_build_select_box_from_arrays($reports_ids, $reports_names, 'report', $report, false);
135 print '<tr><td><strong>View By: </strong></td><td>';
136 $orderby_vals = array("downloads",
152 print html_build_select_box_from_arrays ( $orderby_vals, $orderby_vals, "orderby", $orderby, false );
155 print '<tr><td colspan="2" align="center"> <input type="submit" value="Generate Report" /> </td></tr>';
157 print '</table>' . "\n";
158 print '</form>' . "\n";
163 * New function to separate out the SQL so it may be reused in other
167 function stats_site_project_result( $report, $orderby, $projects, $trove ) {
170 // Determine if we are looking at ALL projects,
171 // a trove category, or a specific list
173 if ($trove == '-2') {
174 //do a query of ALL groups
176 } elseif ($trove == '-1') {
177 //do a query of just a specific list of passed in groups
178 $grp_str=" AND g.group_id IN (" . $projects . ") ";
181 $grp_str=" AND EXISTS
183 FROM trove_group_link
184 WHERE trove_cat_id ='$trove'
185 AND g.group_id=trove_group_link.group_id) ";
188 if ($report == 'last_30') {
190 $sql = "SELECT g.group_id,
192 SUM(s.downloads) AS downloads,
193 SUM(s.site_views) AS site_views,
194 SUM(s.subdomain_views) AS subdomain_views,
195 SUM(s.msg_posted) AS msg_posted,
196 SUM(s.bugs_opened) AS bugs_opened,
197 SUM(s.bugs_closed) AS bugs_closed,
198 SUM(s.support_opened) AS support_opened,
199 SUM(s.support_closed) AS support_closed,
200 SUM(s.patches_opened) AS patches_opened,
201 SUM(s.patches_closed) AS patches_closed,
202 SUM(s.tasks_opened) AS tasks_opened,
203 SUM(s.tasks_closed) AS tasks_closed,
204 SUM(s.cvs_checkouts) AS cvs_checkouts,
205 SUM(s.cvs_commits) AS cvs_commits,
206 SUM(s.cvs_adds) AS cvs_adds
208 stats_project_vw s, groups g
210 s.group_id = g.group_id
212 GROUP BY g.group_id, g.group_name
213 ORDER BY $orderby DESC ";
217 $sql = "SELECT g.group_id,
235 stats_project_all_vw s, groups g
237 s.group_id = g.group_id
239 ORDER BY $orderby DESC ";
243 return db_query( $sql, 30, 0, SYS_DB_STATS);
247 function stats_site_projects( $report, $orderby, $projects, $trove ) {
249 $res=stats_site_project_result( $report, $orderby, $projects, $trove );
251 // if there are any rows, we have valid data (or close enough).
252 if ( db_numrows( $res ) > 1 ) {
255 <p><table width="100%" cellpadding="0" cellspacing="0" border="0">
257 <tr valign="top" align="right" style="bgcolor:#eeeeee">
258 <td><strong>Group Name</strong></td>
259 <td colspan="2"><strong>Page Views</strong></td>
260 <td><strong>Downloads</strong></td>
261 <td colspan="2"><strong>Bugs</strong></td>
262 <td colspan="2"><strong>Support</strong></td>
263 <td colspan="2"><strong>Patches</strong></td>
264 <td colspan="2"><strong>All Trkr</strong></td>
265 <td colspan="2"><strong>Tasks</strong></td>
266 <td colspan="3"><strong>CVS</strong></td>
271 // Build the query string to resort results.
272 $uri_string = "projects.php?report=" . $report;
273 if ( $trove_cat > 0 ) {
274 $uri_string .= "&trovecatid=" . $trove_cat;
276 if ( $trove_cat == -1 ) {
277 $uri_string .= "&projects=" . urlencode( implode( " ", $projects) );
279 $uri_string .= "&orderby=";
282 <tr valign="top" align="right" style="bgcolor:#eeeeee">
284 <td><a href="<?php echo $uri_string; ?>site_views">Site</a></td>
285 <td><a href="<?php echo $uri_string; ?>subdomain_views">Subdomain</a></td>
286 <td><a href="<?php echo $uri_string; ?>downloads">Total</a></td>
287 <td><a href="<?php echo $uri_string; ?>bugs_opened">Opn</a></td>
288 <td><a href="<?php echo $uri_string; ?>bugs_closed">Cls</a></td>
289 <td><a href="<?php echo $uri_string; ?>support_opened">Opn</a></td>
290 <td><a href="<?php echo $uri_string; ?>support_closed">Cls</a></td>
291 <td><a href="<?php echo $uri_string; ?>patches_opened">Opn</a></td>
292 <td><a href="<?php echo $uri_string; ?>patches_closed">Cls</a></td>
293 <td><a href="<?php echo $uri_string; ?>artifacts_opened">Opn</a></td>
294 <td><a href="<?php echo $uri_string; ?>artifacts_closed">Cls</a></td>
295 <td><a href="<?php echo $uri_string; ?>tasks_opened">Opn</a></td>
296 <td><a href="<?php echo $uri_string; ?>tasks_closed">Cls</a></td>
297 <td><a href="<?php echo $uri_string; ?>cvs_checkouts">CO's</a></td>
298 <td><a href="<?php echo $uri_string; ?>cvs_commits">Comm's</a></td>
299 <td><a href="<?php echo $uri_string; ?>cvs_adds">Adds</a></td>
304 while ( $row = db_fetch_array($res) ) {
305 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
306 . '<td>' . ($i + 1) . '. <a href="/project/stats/?group_id=' . $row["group_id"] . '">' . $row["group_name"] . '</a></td>'
307 . '<td> ' . number_format( $row["site_views"],0 ) . '</td>'
308 . '<td> ' . number_format( $row["subdomain_views"],0 ) . '</td>'
309 . '<td> ' . number_format( $row["downloads"],0 ) . '</td>'
310 . '<td> ' . number_format( $row["bugs_opened"],0 ) . '</td>'
311 . '<td> ' . number_format( $row["bugs_closed"],0 ) . '</td>'
312 . '<td> ' . number_format( $row["support_opened"],0 ) . '</td>'
313 . '<td> ' . number_format( $row["support_closed"],0 ) . '</td>'
314 . '<td> ' . number_format( $row["patches_opened"],0 ) . '</td>'
315 . '<td> ' . number_format( $row["patches_closed"],0 ) . '</td>'
316 . '<td> ' . number_format( $row["artifacts_opened"],0 ) . '</td>'
317 . '<td> ' . number_format( $row["artifacts_closed"],0 ) . '</td>'
318 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
319 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
320 . '<td> ' . number_format( $row["cvs_checkouts"],0 ) . '</td>'
321 . '<td> ' . number_format( $row["cvs_commits"],0 ) . '</td>'
322 . '<td> ' . number_format( $row["cvs_adds"],0 ) . '</td>'
325 $sum = stats_util_sum_array( $sum, $row );
333 echo "Query returned no valid data.\n";
334 echo "<br /><hr /><br />\n $sql \n<br /><hr /><br />\n\n";
342 function stats_site_projects_daily( $span ) {
345 // We now only have 30 & 7-day views
347 if ( $span != 30 && $span != 7) {
351 $sql="SELECT * FROM stats_site_vw
352 ORDER BY month DESC, day DESC";
355 $res = db_query($sql, 30, 0, SYS_DB_STATS);
357 $res = db_query($sql, 7, 0, SYS_DB_STATS);
362 // if there are any weeks, we have valid data.
363 if ( ($valid_days = db_numrows( $res )) > 1 ) {
366 <p><strong>Statistics for the past <?php echo $valid_days; ?> days.</strong></p>
369 <table width="100%" cellpadding="0" cellspacing="0" border="0">
370 <tr valign="top" align="right">
371 <td><strong>Day</strong></td>
372 <td><strong>Site Views</strong></td>
373 <td><strong>Subdomain Views</strong></td>
374 <td><strong>Downloads</strong></td>
375 <td><strong>Bugs</strong></td>
376 <td><strong>Support</strong></td>
377 <td><strong>Patches</strong></td>
378 <td><strong>Tasks</strong></td>
379 <td><strong>CVS</strong></td>
383 while ( $row = db_fetch_array($res) ) {
386 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
387 . '<td>' . gmstrftime("%d %b %Y", mktime(0,0,1,substr($row["month"],4,2),$row["day"],substr($row["month"],0,4)) ) . '</td>'
388 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
389 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
390 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
391 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
392 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
393 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
394 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
395 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
408 function stats_site_projects_monthly() {
410 $sql="SELECT * FROM stats_site_months
411 ORDER BY month DESC";
413 $res=db_query($sql, -1, 0, SYS_DB_STATS);
417 // if there are any weeks, we have valid data.
418 if ( ($valid_months = db_numrows( $res )) > 1 ) {
421 <p><strong>Statistics for the past <?php echo $valid_months; ?> months.</strong></p>
424 <table width="100%" cellpadding="0" cellspacing="0" border="0">
425 <tr valign="top" align="right">
426 <td><strong>Month</strong></td>
427 <td><strong>Site Views</strong></td>
428 <td><strong>Subdomain Views</strong></td>
429 <td><strong>Downloads</strong></td>
430 <td><strong>Bugs</strong></td>
431 <td><strong>Support</strong></td>
432 <td><strong>Patches</strong></td>
433 <td><strong>All Trkr</strong></td>
434 <td><strong>Tasks</strong></td>
435 <td><strong>CVS</strong></td>
439 while ( $row = db_fetch_array($res) ) {
442 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . 'align="right">'
443 . '<td>' . $row['month'] . '</td>'
444 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
445 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
446 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
447 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
448 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
449 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
450 . '<td> ' . number_format($row["artifacts_opened"],0) . " (" . number_format($row["artifacts_closed"],0) . ')</td>'
451 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
452 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
465 function stats_site_aggregate( ) {
467 $res = db_query("SELECT * FROM stats_site_all_vw", -1, 0, SYS_DB_STATS);
468 $site_totals = db_fetch_array($res);
470 $sql = "SELECT COUNT(*) AS count FROM groups WHERE status='A'";
471 $res = db_query( $sql );
472 $groups = db_fetch_array($res);
474 $sql = "SELECT COUNT(*) AS count FROM users WHERE status='A'";
475 $res = db_query( $sql );
476 $users = db_fetch_array($res);
480 <p><strong>Current Aggregate Statistics for All Time</strong></p>
482 <p><table width="100%" cellpadding="0" cellspacing="0" border="0">
484 <td><strong>Site Views</strong></td>
485 <td><strong>Subdomain Views</strong></td>
486 <td><strong>Downloads</strong></td>
487 <td><strong>Developers</strong></td>
488 <td><strong>Projects</strong></td>
492 <td><?php echo number_format( $site_totals["site_page_views"],0 ); ?></td>
493 <td><?php echo number_format( $site_totals["subdomain_views"],0 ); ?></td>
494 <td><?php echo number_format( $site_totals["downloads"],0 ); ?></td>
495 <td><?php echo number_format( $users["count"],0 ); ?></td>
496 <td><?php echo number_format( $groups["count"],0 ); ?></td>