web-api/commit-kpis.php

<?php

error_reporting(E_ALL ^ E_NOTICE);

require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/app.class.php");
$App = new App();

include_once($_SERVER['DOCUMENT_ROOT'] . "/projects/fragments/parse-projects-file.php");

header("Content-type: text/plain");
require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection_foundation_ro.class.php";
$all = new AllInfo();
$all->load();

  $_dbcf = new DBConnectionFoundation();
  $_dbhf = $_dbcf->connect();
	/*
	 * Number of committers
	 */
    $foundation_query = "SELECT COUNT(DISTINCT PeopleProjects.PersonID) from PeopleProjects, People WHERE PeopleProjects.PersonID = People.PersonID and Relation = 'CM' and InactiveDate is null and IsUnixAcctCreated <> 0;";
    echo( "# " . $foundation_query . "\n" );
    $result = mysql_query($foundation_query, $_dbhf);
    if( !$result ) {
        echo("MySQL Error: ". mysql_error());
        echo( $foundation_query . "\n" );
        exit( "Stopped because error\n" );
    }
    while( $row = mysql_fetch_array($result) ) {
    	print $row[0] . "\n";
    }
	/*
	 * Number of projects
	 */
    $foundation_query = "SELECT COUNT(*) FROM Projects WHERE IsActive = TRUE and ProjectID <> 'root' and LEFT(ProjectID,19) <> 'foundation-internal';";
    echo( "# " . $foundation_query . " [plus approved pending proposals]\n" );
    $result = mysql_query($foundation_query, $_dbhf);
    if( !$result ) {
        echo("MySQL Error: ". mysql_error());
        echo( $foundation_query . "\n" );
        exit( "Stopped because error\n" );
    }
    $number_of_projects = 0;
    while( $row = mysql_fetch_array($result) ) {
    	$number_of_projects = $row[0];
    }
    foreach( $all->_proposals as $proposal) {
		if( $proposal->status() == "P5" 
		 || $proposal->status() == "PW"
		 || $proposal->status() == "PP" ) {
			$number_of_projects++;
		}
    }
    print $number_of_projects . "\n";
	/*
	 * Number of top-level projects
	 */
    $foundation_query = "SELECT COUNT(*) FROM Projects WHERE IsActive = TRUE and ProjectID <> 'root' and LEFT(ProjectID,19) <> 'foundation-internal' and Level = 1;";
    echo( "# " . $foundation_query . " [plus approved pending proposals]\n" );
    $result = mysql_query($foundation_query, $_dbhf);
    if( !$result ) {
        echo("MySQL Error: ". mysql_error());
        echo( $foundation_query . "\n" );
        exit( "Stopped because error\n" );
    }
    $number_of_projects = 0;
    while( $row = mysql_fetch_array($result) ) {
    	$number_of_projects = $row[0];
    }
    foreach( $all->_proposals as $proposal) {
		if( ($proposal->status() == "P5" 
		  || $proposal->status() == "PW"
		  || $proposal->status() == "PP")
		 && $proposal->get("Project Top-Level") == "" ) {
			$number_of_projects++;
		}
    }
    print $number_of_projects . "\n";
	/*
	 * Number of proposals
	 */
    $foundation_query = "[Proposals]";
    echo( "# " . $foundation_query . "\n" );
    $number_of_proposals = 0;
    foreach( $all->_proposals as $proposal) {
		if( $proposal->status() == "P1"
		 || $proposal->status() == "P2"
		 || $proposal->status() == "P3"
		 || $proposal->status() == "P4"
		 || $proposal->status() == "P6" ) {
			$number_of_proposals++;
		}
    }
    print $number_of_proposals . "\n";
	/*
	 * Number of companies
	 * This computation is a little weird: it's the number of unique email
	 * domains, filtered to remove IBM sub-domains, for those people who
	 * are Employee Members (EM). So this does not cover the people who
	 * are working for their company but the company is not a member.
	 */
    $foundation_query = "SELECT Email from People where Type = 'EM' and IsUnixAcctCreated <> 0;";
    echo( "# " . $foundation_query . " [and then filtered]\n" );
    $result = mysql_query($foundation_query, $_dbhf);
    if( !$result ) {
        echo("MySQL Error: ". mysql_error());
        echo( $foundation_query . "\n" );
        exit( "Stopped because error\n" );
    }
    $domains = array();
    while( $row = mysql_fetch_array($result) ) {
    	$email = $row[0];
    	$email = strrchr( $email, "@" );
    	if( strlen($email) > 7 && substr($email,-7,7) == "ibm.com" ) { $email = "@ibm.com"; };
    	$domains[$email] = 1;
    }
    $number_of_companies = count( array_keys( $domains ) );
    print $number_of_companies. "\n";
	/*
	 * Number of reviews and number of attendees
	 */
	$reviews = array();
	$number_of_reviews = 0;
	$number_of_attendees = 0;
	$count_of_attendees = 0;
	foreach( $all->_proposals as $proposal) {
	  if( !$proposal->review_is_more_than_one_month_old() ) {
	   	array_push( $reviews, $proposal );
	   }
	}
	foreach( $all->_reviews as $review) {
	  if( !$review->review_is_more_than_one_month_old() ) {
	   	array_push( $reviews, $review );
	   }
	}
	foreach( $reviews as $review ) {
		if( is_a($review, 'ReviewInfo') ) {
			if( $review->status() == "JP" 
			 || $review->status() == "J5"
			 || $review->status() == "J6" ) {
				print "## " . $review->name() . "\n";
			 	$number_of_reviews++;
				 $x = $review->get( "Review Attendees" );
				 if( $x != "" ) {
					 $number_of_attendees += $x;
					 $count_of_attendees++;
				 }
			}
		}
		if( is_a($review, 'ProposalInfo') ) {
			if( $review->status() == "P5" 
			 || $review->status() == "PW"
			 || $review->status() == "PP"
			 || $review->status() == "P6" ) {
				print "## " . $review->name() . "\n";
				$number_of_reviews++;
				 $x = $review->get( "Review Attendees" );
				 $x = substr( strrchr( $x, "," ), 1 );
				 if( $x != "" ) {
					 $number_of_attendees += $x;
					 $count_of_attendees++;
				 }
			}
		}
	}
	$number_of_attendees /= $count_of_attendees;
	print $number_of_reviews . "\t" . $number_of_attendees . "\n";

	// Add some stats from Dash
	$end = date("Ym", strtotime("-1 month"));
	$start = date("Ym", strtotime("-3 months"));
	
	runDashCountQuery("Active committers", "select COUNT(DISTINCT LOGIN) AS COUNT FROM commits WHERE YEARMONTH between $start and $end");
	runDashCountQuery("Active projects", "select count(distinct project) as COUNT from commits where YEARMONTH between $start and $end");
	
	function runDashCountQuery($title, $query) {
		global $App;
		echo( "# " . $title . "\n" );
		echo( "# " . $query . "\n" );
		$result = $App->dashboard_sql($query);
		if (!$result) {
			echo("MySQL Error: ".mysql_error());
		} else {
			while($row = mysql_fetch_assoc($result)){
				echo $row['COUNT'] . "\n";
			}
		}
	}
	
class OrganizationAffiliationQuery {
    var $committerMap;
    
    function __construct() {
        $this->committerMap = self::loadCommitterMap();
    }
    
    static function loadCommitterMap() {
        global $App;
        
        $sql = "SELECT distinct p.PersonId, o.Name1 as Company from OrganizationContacts as oc join Organizations as o on (oc.OrganizationId=o.OrganizationId) join People as p on (oc.PersonId = p.PersonId and oc.Relation='EMPLY') join PeopleProjects as pp on (p.PersonId = pp.PersonId and pp.Relation='CM')";
        $result = $App->foundation_sql($sql);
        $committers = array();
        while ($row = mysql_fetch_assoc($result)) {
            $committers[$row['PersonId']] = $row['Company'];
        }
        return $committers;
    }

    function countActiveCompanies($date) {
        global $App;
        
        $end = date("Ym", $date);
        $start = date("Ym", strtotime("-2 months", $date));
   
        $sql = "select distinct login FROM commits WHERE YEARMONTH between $start and $end";
        $result = $App->dashboard_sql($sql);
        
        $companies = array();
        while ($row = mysql_fetch_assoc($result)) {
            $id = $row['login'];
            if (isset($this->committerMap[$id])) {
                $company = $this->committerMap[$id];
                $companies[$company] = $company;
            }
        }
        
        return count($companies);
    }
    
    function countActiveCompaniesStarting($date, $count) {
        $counts = array();
        while ($count--) {
            $period = date("Ym", $date);
            $counts[$period] = $this->countActiveCompanies($date);
            $date = strtotime("-1 month", $date);
        }
        return $counts;
    }
}

echo "# Companies\n";
$query = new OrganizationAffiliationQuery();
foreach($query->countActiveCompaniesStarting(strtotime('now'), 100) as $period => $count) {
    echo "$period > $count\n";
}
?>