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">'.$Language->getString('stats_site_utils','all_projects').'</option>
59 <option value="-1">'.$Language->getString('stats_site_utils','sprecial_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 $Language->getString('stats_site_utils','returned_no_category')) ";
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" class="tableheading">' . "\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');
129 $reports_names[]=_('all');
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 ) {
169 // Determine if we are looking at ALL projects,
170 // a trove category, or a specific list
174 if ($trove == '-2') {
175 //do a query of ALL groups
177 } elseif ($trove == '-1') {
178 //do a query of just a specific list of passed in groups
179 $grp_str=" AND g.group_id IN (" . $projects . ") ";
182 $grp_str=" AND EXISTS
184 FROM trove_group_link
185 WHERE trove_cat_id ='$trove'
186 AND g.group_id=trove_group_link.group_id) ";
191 $orderby = "group_name";
194 if ($report == 'last_30') {
196 $sql = "SELECT g.group_id,
198 SUM(s.downloads) AS downloads,
199 SUM(s.site_views) AS site_views,
200 SUM(s.subdomain_views) AS subdomain_views,
201 SUM(s.msg_posted) AS msg_posted,
202 SUM(s.bugs_opened) AS bugs_opened,
203 SUM(s.bugs_closed) AS bugs_closed,
204 SUM(s.support_opened) AS support_opened,
205 SUM(s.support_closed) AS support_closed,
206 SUM(s.patches_opened) AS patches_opened,
207 SUM(s.patches_closed) AS patches_closed,
208 SUM(s.tasks_opened) AS tasks_opened,
209 SUM(s.tasks_closed) AS tasks_closed,
210 SUM(s.cvs_checkouts) AS cvs_checkouts,
211 SUM(s.cvs_commits) AS cvs_commits,
212 SUM(s.cvs_adds) AS cvs_adds
214 stats_project_vw s, groups g
216 s.group_id = g.group_id
218 GROUP BY g.group_id, g.group_name
219 ORDER BY $orderby DESC ";
223 $sql = "SELECT g.group_id,
241 stats_project_all_vw s, groups g
243 s.group_id = g.group_id
245 ORDER BY $orderby DESC ";
248 return db_query( $sql, 30, 0, SYS_DB_STATS);
252 function stats_site_projects( $report, $orderby, $projects, $trove ) {
257 $res=stats_site_project_result( $report, $orderby, $projects, $trove );
258 // if there are any rows, we have valid data (or close enough).
259 if ( db_numrows( $res ) > 1 ) {
262 <table width="100%" cellpadding="0" cellspacing="0" border="0">
264 <tr valign="top" align="right" class="tableheading">
265 <td><strong><?php echo _('Group Name'); ?></strong></td>
266 <td colspan="2"><strong><?php echo _('Page Views'); ?></strong></td>
267 <td><strong><?php echo _('Downloads'); ?></strong></td>
268 <td colspan="2"><strong><?php echo _('Bugs'); ?></strong></td>
269 <td colspan="2"><strong><?php echo _('Support'); ?></strong></td>
270 <td colspan="2"><strong><?php echo _('Patches'); ?></strong></td>
271 <td colspan="2"><strong><?php echo _('All Trkr'); ?></strong></td>
272 <td colspan="2"><strong><?php echo _('Tasks'); ?></strong></td>
273 <td colspan="3"><strong><?php echo _('CVS'); ?></strong></td>
278 // Build the query string to resort results.
279 $uri_string = "projects.php?report=" . $report;
280 if ( $trove_cat > 0 ) {
281 $uri_string .= "&trovecatid=" . $trove_cat;
283 if ( $trove_cat == -1 ) {
284 $uri_string .= "&projects=" . urlencode( implode( " ", $projects) );
286 $uri_string .= "&orderby=";
289 <tr valign="top" align="right" class="tableheading">
291 <td><a href="<?php echo $uri_string; ?>site_views"><?php echo _('Site'); ?></a></td>
292 <td><a href="<?php echo $uri_string; ?>subdomain_views"><?php echo _('Subdomain'); ?></a></td>
293 <td><a href="<?php echo $uri_string; ?>downloads"><?php echo _('Total'); ?></a></td>
294 <td><a href="<?php echo $uri_string; ?>bugs_opened"><?php echo _('Opn'); ?></a></td>
295 <td><a href="<?php echo $uri_string; ?>bugs_closed"><?php echo _('Cls'); ?></a></td>
296 <td><a href="<?php echo $uri_string; ?>support_opened"><?php echo _('Opn'); ?></a></td>
297 <td><a href="<?php echo $uri_string; ?>support_closed"><?php echo _('Cls'); ?></a></td>
298 <td><a href="<?php echo $uri_string; ?>patches_opened"><?php echo _('Opn'); ?></a></td>
299 <td><a href="<?php echo $uri_string; ?>patches_closed"><?php echo _('Cls'); ?></a></td>
300 <td><a href="<?php echo $uri_string; ?>artifacts_opened"><?php echo _('Opn'); ?></a></td>
301 <td><a href="<?php echo $uri_string; ?>artifacts_closed"><?php echo _('Cls'); ?></a></td>
302 <td><a href="<?php echo $uri_string; ?>tasks_opened"><?php echo _('Opn'); ?></a></td>
303 <td><a href="<?php echo $uri_string; ?>tasks_closed"><?php echo _('Cls'); ?></a></td>
304 <td><a href="<?php echo $uri_string; ?>cvs_checkouts"><?php echo _('CO\'s'); ?></a></td>
305 <td><a href="<?php echo $uri_string; ?>cvs_commits"><?php echo _('Comm\'s'); ?></a></td>
306 <td><a href="<?php echo $uri_string; ?>cvs_adds"><?php echo _('Adds'); ?></a></td>
311 while ( $row = db_fetch_array($res) ) {
312 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
313 . '<td>' . ($i + 1) . '. <a href="'.$GLOBALS['sys_urlprefix'].'/project/stats/?group_id=' . $row["group_id"] . '">' . $row["group_name"] . '</a></td>'
314 . '<td> ' . number_format( $row["site_views"],0 ) . '</td>'
315 . '<td> ' . number_format( $row["subdomain_views"],0 ) . '</td>'
316 . '<td> ' . number_format( $row["downloads"],0 ) . '</td>'
317 . '<td> ' . number_format( $row["bugs_opened"],0 ) . '</td>'
318 . '<td> ' . number_format( $row["bugs_closed"],0 ) . '</td>'
319 . '<td> ' . number_format( $row["support_opened"],0 ) . '</td>'
320 . '<td> ' . number_format( $row["support_closed"],0 ) . '</td>'
321 . '<td> ' . number_format( $row["patches_opened"],0 ) . '</td>'
322 . '<td> ' . number_format( $row["patches_closed"],0 ) . '</td>'
323 . '<td> ' . number_format( $row["artifacts_opened"],0 ) . '</td>'
324 . '<td> ' . number_format( $row["artifacts_closed"],0 ) . '</td>'
325 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
326 . '<td> ' . number_format( $row["tasks_opened"],0 ) . '</td>'
327 . '<td> ' . number_format( $row["cvs_checkouts"],0 ) . '</td>'
328 . '<td> ' . number_format( $row["cvs_commits"],0 ) . '</td>'
329 . '<td> ' . number_format( $row["cvs_adds"],0 ) . '</td>'
332 $sum = stats_util_sum_array( $sum, $row );
340 echo _('Query returned no valid data.')."\n";
341 echo "<br /><hr /><br />\n $sql \n<br /><hr /><br />\n\n";
349 function stats_site_projects_daily( $span ) {
353 // We now only have 30 & 7-day views
355 if ( $span != 30 && $span != 7) {
359 $sql="SELECT * FROM stats_site_vw
360 ORDER BY month DESC, day DESC";
363 $res = db_query($sql, 30, 0, SYS_DB_STATS);
365 $res = db_query($sql, 7, 0, SYS_DB_STATS);
370 // if there are any weeks, we have valid data.
371 if ( ($valid_days = db_numrows( $res )) > 1 ) {
374 <p><strong><?php printf(_('Statistics for the past %1$s days'), $valid_days); ?></strong></p>
375 <table width="100%" cellpadding="0" cellspacing="0" border="0">
376 <tr valign="top" align="right">
377 <td><strong><?php echo _('Day'); ?></strong></td>
378 <td><strong><?php echo _('Site Views'); ?></strong></td>
379 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
380 <td><strong><?php echo _('Downloads'); ?></strong></td>
381 <td><strong><?php echo _('Bugs'); ?></strong></td>
382 <td><strong><?php echo _('Support'); ?></strong></td>
383 <td><strong><?php echo _('Patches'); ?></strong></td>
384 <td><strong><?php echo _('Tasks'); ?></strong></td>
385 <td><strong><?php echo _('CVS'); ?></strong></td>
389 while ( $row = db_fetch_array($res) ) {
392 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . ' align="right">'
393 . '<td>' . gmstrftime("%d %b %Y", mktime(0,0,1,substr($row["month"],4,2),$row["day"],substr($row["month"],0,4)) ) . '</td>'
394 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
395 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
396 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
397 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
398 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
399 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
400 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
401 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
414 function stats_site_projects_monthly() {
417 $sql="SELECT * FROM stats_site_months
418 ORDER BY month DESC";
420 $res=db_query($sql, -1, 0, SYS_DB_STATS);
424 // if there are any weeks, we have valid data.
425 if ( ($valid_months = db_numrows( $res )) > 1 ) {
428 <p><strong><?php printf(_('Statistics for the past %1$s months'), $valid_months); ?></strong></p>
430 <table width="100%" cellpadding="0" cellspacing="0" border="0">
431 <tr valign="top" align="right">
432 <td><strong><?php echo _('Month'); ?>Month</strong></td>
433 <td><strong><?php echo _('Site Views'); ?></strong></td>
434 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
435 <td><strong><?php echo _('Downloads'); ?></strong></td>
436 <td><strong><?php echo _('Bugs'); ?></strong></td>
437 <td><strong><?php echo _('Support'); ?></strong></td>
438 <td><strong><?php echo _('Patches'); ?></strong></td>
439 <td><strong><?php echo _('All Trkr'); ?></strong></td>
440 <td><strong><?php echo _('Tasks'); ?></strong></td>
441 <td><strong><?php echo _('CVS'); ?></strong></td>
445 while ( $row = db_fetch_array($res) ) {
448 print '<tr ' . $GLOBALS['HTML']->boxGetAltRowStyle($i) . 'align="right">'
449 . '<td>' . $row['month'] . '</td>'
450 . '<td>' . number_format( $row["site_page_views"],0 ) . '</td>'
451 . '<td>' . number_format( $row["subdomain_views"],0 ) . '</td>'
452 . '<td>' . number_format( $row["downloads"],0 ) . '</td>'
453 . '<td> ' . number_format($row["bugs_opened"],0) . " (" . number_format($row["bugs_closed"],0) . ')</td>'
454 . '<td> ' . number_format($row["support_opened"],0) . " (" . number_format($row["support_closed"],0) . ')</td>'
455 . '<td> ' . number_format($row["patches_opened"],0) . " (" . number_format($row["patches_closed"],0) . ')</td>'
456 . '<td> ' . number_format($row["artifacts_opened"],0) . " (" . number_format($row["artifacts_closed"],0) . ')</td>'
457 . '<td> ' . number_format($row["tasks_opened"],0) . " (" . number_format($row["tasks_closed"],0) . ')</td>'
458 . '<td> ' . number_format($row["cvs_checkouts"],0) . " (" . number_format($row["cvs_commits"],0) . ')</td>'
471 function stats_site_aggregate( ) {
474 $res = db_query("SELECT * FROM stats_site_all_vw", -1, 0, SYS_DB_STATS);
475 $site_totals = db_fetch_array($res);
477 $sql = "SELECT COUNT(*) AS count FROM groups WHERE status='A'";
478 $res = db_query( $sql );
479 $groups = db_fetch_array($res);
481 $sql = "SELECT COUNT(*) AS count FROM users WHERE status='A'";
482 $res = db_query( $sql );
483 $users = db_fetch_array($res);
487 <p><strong><?php echo _('Current Aggregate Statistics for All Time'); ?></strong></p>
489 <table width="100%" cellpadding="0" cellspacing="0" border="0">
491 <td><strong><?php echo _('Site Views'); ?></strong></td>
492 <td><strong><?php echo _('Subdomain Views'); ?></strong></td>
493 <td><strong><?php echo _('Downloads'); ?></strong></td>
494 <td><strong><?php echo _('Developers'); ?></strong></td>
495 <td><strong><?php echo _('Projects'); ?></strong></td>
499 <td><?php echo number_format( $site_totals["site_page_views"],0 ); ?></td>
500 <td><?php echo number_format( $site_totals["subdomain_views"],0 ); ?></td>
501 <td><?php echo number_format( $site_totals["downloads"],0 ); ?></td>
502 <td><?php echo number_format( $users["count"],0 ); ?></td>
503 <td><?php echo number_format( $groups["count"],0 ); ?></td>