#!/usr/bin/php -q
<?php

$login_data = parse_ini_file("/etc/aranea/aranea.conf");

mysql_connect( $login_data['storage_host'], $login_data['storage_user'], $login_data['storage_password']) or die ("0");
mysql_select_db( $login_data['storage_db']) or die("0");
$res_stat = mysql_query("SELECT COUNT(*) FROM user") or die("0");
$registered = mysql_result($res_stat, 0, 0);
$res_stat = mysql_query("SELECT COUNT(*) FROM `user` WHERE disabled = true") or die ("0");
$dis_users = mysql_result($res_stat, 0, 0);
$res_stat = mysql_query("SELECT COUNT(DISTINCT user_id) FROM session") or die("0");
$online = mysql_result($res_stat, 0, 0);
$res_stat = mysql_query("SELECT COUNT(*) FROM project") or die("0");
$projects = mysql_result($res_stat, 0, 0);
$res_stat = mysql_query("SELECT COUNT(*) FROM project WHERE closed_source = true");
$closed_source_projects = mysql_result($res_stat, 0,0);

$res_stat = mysql_query("SELECT COUNT(*) FROM `workitem` GROUP BY type_id ORDER BY type_id ASC");
$wi_issue = mysql_result($res_stat, 0, 0);
$wi_release = mysql_result($res_stat, 1, 0);
$wi_commit = mysql_result($res_stat, 2, 0);
$wi_wiki = mysql_result($res_stat, 3, 0);
$wi_blog = mysql_result($res_stat, 4, 0);
$wi_comment = mysql_result($res_stat, 5, 0);
$workitems = $wi_issue + $wi_release + $wi_commit + $wi_wiki + $wi_blog + $wi_comment;

$res_stat = mysql_query("SELECT COUNT(*) FROM `project` WHERE hidden = true");
$hidden_projects = mysql_result($res_stat, 0, 0);

$res_stat = mysql_query("SELECT COUNT(*) FROM user_information WHERE last_login > UNIX_TIMESTAMP()-86400*7");
$user_7_percentile = mysql_result($res_stat, 0, 0);
$res_stat = mysql_query("SELECT COUNT(*) FROM user_information WHERE last_login > UNIX_TIMESTAMP()-86400");
$user_1_percentile = mysql_result($res_stat, 0, 0);
$res_stat = mysql_query("SELECT COUNT(*) FROM user_information WHERE last_login > UNIX_TIMESTAMP()-86400*30");
$user_30_percentile = mysql_result($res_stat, 0, 0);

$res_orphaned_projects = mysql_query("SELECT DISTINCT COUNT(*) FROM `project` LEFT JOIN `user_project_association` ON project.project_id = user_project_association.project_id WHERE user_project_association.project_id IS NULL");
$orphaned_projects = mysql_result($res_orphaned_projects, 0, 0);

$app_mylyn = 0;
$app_eclipse33 = 0;
$app_vista_widget = 0;
$app_osx_widget = 0;
$app_other = 0;

$res_application_logins = mysql_query("SELECT application_id, login_count FROM application");
while ($row = mysql_fetch_assoc($res_application_logins)) {
  switch ($row['application_id']) {
  case 4:
      $app_eclipse33 = $row['login_count'];
      break;
  case 7:
      $app_mylyn = $row['login_count'];
      break;
  case 9:
      $app_osx_widget = $row['login_count'];
      break;
  case 11:
      $app_vista_widget = $row['login_count'];
      break;
  default:
      $app_other += $row['login_count'];
      break;
  }
}

mysql_close();

$nl = "\n";

echo $registered . $nl;
echo $online . $nl;
echo $projects . $nl;
echo $workitems . $nl;
echo $closed_source_projects . $nl;
echo $wi_issue . $nl;
echo $wi_release . $nl;
echo $wi_commit . $nl;
echo $wi_wiki . $nl;
echo $wi_blog . $nl;
echo $wi_comment . $nl;
echo $hidden_projects . $nl;
echo $dis_users . $nl;
echo $user_1_percentile . $nl;
echo $user_7_percentile . $nl;
echo $user_30_percentile . $nl;
echo $orphaned_projects . $nl;
echo $app_eclipse33 . $nl;
echo $app_mylyn . $nl;
echo $app_osx_widget . $nl;
echo $app_vista_widget . $nl;
echo $app_other . $nl;

?>