tools/kpi.php

<?php
/********************************************************************************
 * Copyright (c) 2016 The Eclipse Foundation
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0.
 *
 * SPDX-License-Identifier: EPL-2.0
 ********************************************************************************/
require_once ($_SERVER ['DOCUMENT_ROOT'] . "/eclipse.org-common/system/app.class.php");
require_once ($_SERVER ['DOCUMENT_ROOT'] . "/eclipse.org-common/system/nav.class.php");
require_once ($_SERVER ['DOCUMENT_ROOT'] . "/eclipse.org-common/system/menu.class.php");

$App = new App ();
$Nav = new Nav ();
$Menu = new Menu ();
include ($App->getProjectCommon ());

require_once $_SERVER ['DOCUMENT_ROOT'] . '/projects/classes/Project.class.php';
require_once $_SERVER ['DOCUMENT_ROOT'] . '/projects/classes/common.php';
require_once $_SERVER ['DOCUMENT_ROOT'] . '/projects/classes/debug.php';
require_once dirname ( __FILE__ ) . '/kpi.inc';
require_once dirname ( __FILE__ ) . '/charts.inc';

// Let's keep this restricted to Eclipse Foundation employees
// for now.
mustBeFoundationEmployee();

$pageTitle = "Eclipse Project KPIs";
$pageKeywords = "";
$pageAuthor = "Wayne Beaton";

$App->AddExtraHtmlHeader("
<style>
img.company-logo {margin: 1em; width: 64px}
</style>");

ob_start ();

$width = 800;
$height = 600;

$age = 3;
$start = date ( 'M Y', strtotime ( "- $age years" ) );
$end = date ( 'M Y', strtotime ( "- 1 month" ) );

function getProjectNickName($id) {
 return ($project = Project::getProject($id)) ? $project->getNickName() : $id;
}

?>
<div id="maincontent">
<div id="midcolumn">
<h1><?=$pageTitle?></h1>

<h2 id="project">Basic Project KPIs</h2>

<?php

ChartBuilder::named('project_committer')
  ->title("Committer Activity ($start to $end)")
  ->description(
    "Overall committer activity by month based on commits made against
     project repositories.
     This excludes the current month.")
  ->query('dashboard', "
    select
     period,
     committers,
     participatingCommitters as participating,
     activeCommitters as active
    from MonthlySummary
    where period >= :start and period <= :end")
  ->substitute(':start', date ( 'Ym', strtotime ( "- $age years" ) ))
  ->substitute(':end', date ( 'Ym', strtotime ( "- 1 month" ) ))
  ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
  ->column('Committers', 'committers', 'number')
  ->column('Participating', 'participating', 'number')
  ->column('Active', 'active', 'number')
  ->render();
?>

<?php

ChartBuilder::named('project_contributor')
  ->title("Contributor Activity ($start to $end)")
  ->description(
    "Overall non-committer contributor activity by month based on commits made against
     project repositories.
     This excludes the current month.")
  ->query('dashboard', "
		select
			period,
			activeAuthors - monthlyCommitters as contributors
		from MonthlySummary
		where period >= :start and period <= :end")
  ->substitute(':start', date ( 'Ym', strtotime ( "- $age years" ) ))
  ->substitute(':end', date ( 'Ym', strtotime ( "- 1 month" ) ))
  ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
  ->column('Contributors', 'contributors', 'number')
  ->render();
?>

<?php

ChartBuilder::named('project_activity')
  ->title("Project Metrics ($start to $end)")
  ->description(
    "Overall project activity by month based on commits made against
     project repositories.
     This excludes the current month.")
  ->query('dashboard', "
		select
			period,
			projects,
      activeProjects as active,
			activeCompanies as companies
		from MonthlySummary
		where period >= :start and period <= :end")
	->substitute(':start', date ( 'Ym', strtotime ( "- $age years" ) ))
	->substitute(':end', date ( 'Ym', strtotime ( "- 1 month" ) ))
  ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
  ->column('Projects', 'projects', 'number')
  ->column('Active Projects', 'active', 'number')
  ->column('Active Companies', 'companies', 'number')
  ->render();
?>

<?php
ChartBuilder::named('project_commits')
 ->title("Commit Activity ($start to $end)")
 ->description("Absolute number of commits.")
 ->query('dashboard', "
	select
			period, commits as count
		from MonthlySummary
 		where period >= :start and period <= :end")
 ->substitute(':start', date ( 'Ym', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Ym', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('Commits', 'count', 'number')
 ->render();
?>

<h2 id='ip'>Intellectual Property KPIs</h2>

<?php
ChartBuilder::named('cq_activity_breakdown')
 ->title("Breakdown of Open CQs Over Time ($start to $end)")
 ->query('ipzilla', "
    select d.period, count(distinct bug_id) as count
    from (select distinct date_format(creation_ts, '%Y-%m') as period from bugs where date_format(creation_ts, '%Y-%m') between ':start' and ':end') as d
      join
        (select
            p.name as tlp,
            c.name as project,
            b.bug_id,
            if(b.short_desc regexp '\((PB( Orbit)?)\s*\d*\)', 'piggyback', kd.name) as type,
            date(b.creation_ts) as created,
            date_format(if (min(date(take.bug_when)) is null, date(b.creation_ts), min(date(take.bug_when))), '%Y-%m') as start,
            if (max(date(approved.bug_when)) is null, null, date_format(max(date(approved.bug_when)), '%Y-%m')) as end
         from bugs as b
           join products as p on b.product_id = p.id
           join components as c on b.component_id = c.id
           join keywords as k on b.bug_id=k.bug_id
           join keyworddefs as kd on k.keywordid=kd.id and kd.name in ('thirdparty', 'projectcode')
           left join bugs_activity as take on b.bug_id=take.bug_id and take.fieldid=13 and take.added in ('triage', 'awaiting_analyis', 'awaiting_ipteam')
           left join bugs_activity as approved on b.bug_id=approved.bug_id and approved.fieldid=9 and approved.added in ('RESOLVED', 'CLOSED')
           where p.name not in ('foundation-internal', 'IP', 'dsdp')
         group by p.id, b.bug_id) as b
       on d.period between b.start and b.end
       group by d.period
")
 ->substitute(':start', date ( 'Y-m', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m', strtotime ( "- 1 months" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('CQs', 'count', 'number')
 ->render();
?>

<?php
ChartBuilder::named('cq_activity')
 ->title("Open CQs Over Time ($start to $end)")
 ->query('ipzilla', "
     SELECT Month, bug_count
      FROM _ipzilla_historical
      WHERE Month >= \":start\"
      UNION
      SELECT DAT.Month, count(*) as bug_count
      FROM (SELECT DISTINCT date_format(creation_ts, \"%Y-%m\") AS Month FROM bugs) AS
      DAT
          INNER JOIN bugs as BUG ON date_format(BUG.creation_ts, \"%Y-%m\") <= Month
          INNER JOIN products AS PRD ON PRD.id = BUG.product_id
          LEFT JOIN bugs_activity AS ACT on ACT.bug_id = BUG.bug_id
              AND ACT.added = \"RESOLVED\"
      WHERE
              BUG.resolution != \"INVALID\"
          AND PRD.name != \"IP\"
          AND BUG.keywords not like '%historical%'
          AND (ACT.bug_when IS NULL OR date_format(ACT.bug_when,\"%Y-%m\")  > Month)
          AND DAT.Month >= \":start\"
          And DAT.Month < \":end\"
      GROUP BY Month")
 ->substitute(':start', date ( 'Y-m', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m', strtotime ( "- 1 months" ) ))
 ->column('Month', 'Month', 'string', function($value) {return asYearMonth($value);})
 ->column('CQs', 'bug_count', 'number')
 ->render();
?>


<?php
ChartBuilder::named('ip_velocity')
 ->title("Average IP Team Velocity Over Time ($start to $end)")
 ->description(
   "This is the average number of non-trival CQs (e.g. not piggybacks)
    resolved by the IP Team, per day in any given month.")
 ->query('ipzilla', "
    select
     date_format(ba.bug_when, '%Y-%m') as period,
     (count(distinct ba.bug_id) / count(distinct date(ba.bug_when))) as velocity
    from bugs_activity as ba
     join bugs as b on ba.bug_id=b.bug_id
     join components as c on b.component_id=c.id
    where
     added='RESOLVED'
     and ba.bug_when between date(':start') and date(':end')
     and b.short_desc not regexp '\((PB( Orbit)?)\s*\d*\)'
     and b.short_desc not regexp '\(ATO\s*\d*\)'
     and b.cf_type not in ('Type_A')
     and c.name not in ('IP_Discussion')
    group by date_format(ba.bug_when, '%Y-%m')
    ")
 ->substitute(':start', date ( 'Y-m-01', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m-t', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('CQs Resolved per Day', 'velocity', 'number')
 ->render();
?>

<?php
ChartBuilder::named('cq_dd_type')
 ->title("CQ Creation by Due Diligence Type ($start to $end)")
 ->description(
   "The Eclipse IP Policy introduces two different types of CQs for third party content:
    <em>Type A</em> for license check only; or <em>Type B</em>e for license, provenance,
    and anomaly scan. Note that the Third Party CQ Type chart excludes piggybacks.")
 ->query('ipzilla', "
     select
       date_format(creation_ts, '%Y-%m') as period,
       sum(if(cf_type='Type_A',1,0)) as typeA,
       sum(if(cf_type='Type_B',1,0)) as typeB
     from bugs as b
       join keywords as kw on b.bug_id=kw.bug_id
       join keyworddefs as kd on kw.keywordid=kd.id
     where kd.name='thirdparty'
       and not (b.short_desc regexp '\\(PB( ORBIT)? CQ[[:digit:]]+\\)')
       and not (b.short_desc regexp '\\(ATO CQ[[:digit:]]+\\)')
       and date_format(creation_ts, '%Y-%m') between ':start' and ':end'
     group by date_format(creation_ts, '%Y%m')")
 ->substitute(':start', date ( 'Y-m', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('Type A', 'typeA', 'number')
 ->column('Type B', 'typeB', 'number')
 ->render();
?>

<?php

/*
 * This query may be more complex than it needs to be, but I don't
 * know SQL well enough to make a more clever single query. The
 * primary query only returns a list of periods (year/month); and
 * for each row that's returned, a subsequent query is executed to
 * determine how many projects have at least one CQ created in that
 * period or any period before it.
 */

ChartBuilder::named('cq_typea_projects')
 ->title("Projects using Type A Due Diligence ($start to $end)")
 ->description(
   "Number of projects that have at least one \"Type A\" CQ.")
 ->query('ipzilla', "
     select distinct
       date_format(creation_ts, '%Y-%m') as period,
       date_format(creation_ts, '%Y%m') as marker
     from bugs as b
     where date_format(creation_ts, '%Y%m') between ':start' and ':end'")
 ->substitute(':start', date ( 'Ym', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Ym', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('Count', 'marker', 'number', function(&$value) {
    $sql = "
       select
         count(distinct b.component_id) as count
       from bugs as b
         join keywords as kw on b.bug_id=kw.bug_id
         join keyworddefs as kd on kw.keywordid=kd.id and kd.name='thirdparty'
       where b.cf_type='Type_A'
        and b.bug_severity='license_certified'
        and date_format(creation_ts, '%Y%m') <= ':value'";
    $parameters = array(':value' => $value);
    query('ipzilla', $sql, $parameters,function($row) use (&$value) {
     $value = $row['count'];
    });
    return $value;
 })
 ->render();
?>

<?php
ChartBuilder::named('cq_typea_automatic')
 ->title("Type A by Automatic/Manual Certification ($start to $end)")
 ->description(
   "Type A CQs by how they were resolved (automatic resolution by Genie, or manual
    resolution by the Eclipse IP Team.")
 ->query('ipzilla', "
     select
       date_format(creation_ts, '%Y-%m') as period,
       count(distinct b.bug_id) as total,
       sum(if(isnull(ba.who),0,1)) as automatic,
       sum(if(isnull(ba.who),1,0)) as manual
     from bugs as b
       join keywords as kw on b.bug_id=kw.bug_id
       join keyworddefs as kd on kw.keywordid=kd.id and kd.name='thirdparty'
       left join bugs_activity as ba
        on b.bug_id=ba.bug_id
         and ba.who=(select userid from profiles where login_name='genie@eclipse.org')
         and ba.fieldid=(select id from fielddefs where name='bug_severity')
         and ba.added='license_certified'
     where b.cf_type='Type_A'
       and date_format(creation_ts, '%Y-%m') between ':start' and ':end'
     group by date_format(creation_ts, '%Y%m')")
 ->substitute(':start', date ( 'Y-m', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('Automatic', 'automatic', 'number')
 ->column('Manual', 'manual', 'number')
 ->render();
?>

<?php
ChartBuilder::named('cq_type_created')
 ->title("CQs by Type Created ($start to $end)")
 ->description(
   "The CQs created in any given month organized by kind
    (e.g. project code, different forms of third party content, and IP Logs).")
 ->query('ipzilla', "
     select
         date as period,
         sum(if(type='projectcode',1,0)) as projectcode,
         sum(if(type='piggyback',1,0)) as piggyback,
         sum(if(type='thirdparty',1,0)) as thirdparty,
         sum(if(type='orbit',1,0)) as orbit,
         sum(if(type='iplog',1,0)) as iplog
     from
         (select
             date_format(b.creation_ts, '%Y-%m') as date,
             b.bug_id,
             if(b.short_desc regexp '\\(PB( ORBIT)? CQ[[:digit:]]+\\)', 'piggyback',
             if(b.short_desc regexp '\\(ATO CQ[[:digit:]]+\\)', 'orbit',
             kwd.name)) as type,
             b.short_desc
         from bugs as b
             join keywords as kw on b.bug_id=kw.bug_id
             join keyworddefs as kwd on kw.keywordid=kwd.id
              and kwd.name in ('iplog', 'thirdparty', 'projectcode')
         ) as cqs
     where date between ':start' and ':end'
     group by date")
 ->substitute(':start', date ( 'Y-m', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('Project code', 'projectcode', 'number')
 ->column('Piggyback', 'piggyback', 'number')
 ->column('Third party', 'thirdparty', 'number')
 ->column('Orbit', 'orbit', 'number')
 ->column('IP Log Review', 'iplog', 'number')
 ->render();
?>

<?php
ChartBuilder::named('cq_type_resolved')
 ->title("Resolved CQs by Type ($start to $end)")
 ->description(
   "The CQs resolved in any given month organized by kind
    (e.g. project code, different forms of third party content, and IP Logs).")
 ->query('ipzilla', "
    select
        date as period,
        sum(if(type='projectcode',1,0)) as projectcode,
        sum(if(type='piggyback',1,0)) as piggyback,
        sum(if(type='thirdparty',1,0)) as thirdparty,
        sum(if(type='orbit',1,0)) as orbit,
        sum(if(type='iplog',1,0)) as iplog
    from
        (select
            date_format(ba.bug_when, \"%Y-%m\") as date,
            b.bug_id,
            if(b.short_desc regexp '\\(PB( ORBIT)? CQ[[:digit:]]+\\)', 'piggyback',
                if(b.short_desc regexp '\\(ATO CQ[[:digit:]]+\\)', 'orbit',
                    kwd.name)) as type,
            b.short_desc
        from bugs_activity as ba
            join bugs as b on ba.bug_id=b.bug_id
            join keywords as kw on b.bug_id=kw.bug_id
            join keyworddefs as kwd on kw.keywordid=kwd.id
                and kwd.name in ('iplog', 'thirdparty', 'projectcode')
        where ba.added='RESOLVED') as cqs
    where date between ':start' and ':end'
    group by date")
 ->substitute(':start', date ( 'Y-m', strtotime ( "- $age years" ) ))
 ->substitute(':end', date ( 'Y-m', strtotime ( "- 1 month" ) ))
 ->column('Month', 'period', 'string', function($value) {return asYearMonth($value);})
 ->column('Project code', 'projectcode', 'number')
 ->column('Piggyback', 'piggyback', 'number')
 ->column('Third party', 'thirdparty', 'number')
 ->column('Orbit', 'orbit', 'number')
 ->column('IP Log Review', 'iplog', 'number')
 ->render();
?>

<?php
ChartBuilder::named('cq_tlp_month')
 ->title("CQ by Top Level Project ($end)")
 ->description("A breakdown by top-level project of the open CQs in the current month.")
 ->query('ipzilla', "
    SELECT
        PRD.name as project,
        count(BUG.bug_id) as count
    FROM (SELECT DISTINCT date_format(creation_ts, \"%Y-%m\") AS Month FROM bugs) AS DAT
        INNER JOIN bugs as BUG ON date_format(BUG.creation_ts, \"%Y-%m\") <= Month
        INNER JOIN products AS PRD ON PRD.id = BUG.product_id
        INNER JOIN components AS CMP ON CMP.id = BUG.component_id
        LEFT JOIN bugs_activity AS ACT on ACT.bug_id = BUG.bug_id
            AND ACT.added = \"RESOLVED\"
    WHERE BUG.resolution != \"INVALID\"
        AND PRD.name != \"IP\"
        AND BUG.keywords not like '%historical%'
        AND CMP.name != \"tools.orbit\"
        AND (ACT.bug_when IS NULL OR date_format(ACT.bug_when,\"%Y-%m\")  > Month)
        AND DAT.Month >= \":last\"
        AND DAT.Month < \":now\"
    GROUP BY PRD.name
    ORDER BY count desc")
 ->substitute(':last', date ( 'Y-m', strtotime ( "-1 months" ) ))
 ->substitute(':now', date ( 'Y-m' ))
 ->pieChart()
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<?php

$year = date ( 'Y', strtotime ( '-1 year' ) );
$date = new DateTime("$year-04-01");
$last = new DateTime("$year-04-01");
$last->add(new DateInterval("P1Y"))->sub(new DateInterval("P1D"));

ChartBuilder::named('cq_tlp_year')
 ->title("CQs by Top Level Project (:startT to :endT)")
 ->description(
   "A breakdown by top-level project of CQs created in the previous year.
    We refer to this as a \"yearly\" chart, but it really tracks from
    April 1 of the previous year to March 31 of the current year.")
 ->query('ipzilla', "
    select
     p.name as project,
     count(distinct b.bug_id) as count
    from bugs as b
     join products as p on b.product_id=p.id
     join keywords as kw on kw.bug_id=b.bug_id
     join keyworddefs as kwd on kw.keywordid=kwd.id and kwd.name in ('projectcode', 'thirdparty')
    where date(b.creation_ts) between date(':start') and date(':end')
    group by p.name
    order by count(distinct b.bug_id) desc")
 ->substitute(':start', $date->format('Y-m-d'))
 ->substitute(':end', $last->format('Y-m-d'))
 ->substitute(':startT', $date->format('M Y'))
 ->substitute(':endT', $last->format('M Y'))
 ->pieChart()
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<?php
ChartBuilder::named('cq_project_month')
 ->title("CQs by Project ($end)")
 ->description("A breakdown by project of the open CQs in the current month.")
 ->query('ipzilla', "
   SELECT
       CMP.name as project,
       count(BUG.bug_id) as count
   FROM (SELECT DISTINCT date_format(creation_ts, \"%Y-%m\") AS Month FROM bugs) AS DAT
       INNER JOIN bugs as BUG ON date_format(BUG.creation_ts, \"%Y-%m\") <= Month
       INNER JOIN products AS PRD ON PRD.id = BUG.product_id
       INNER JOIN components AS CMP ON CMP.id = BUG.component_id
       LEFT JOIN bugs_activity AS ACT on ACT.bug_id = BUG.bug_id
           AND ACT.added = \"RESOLVED\"
   WHERE BUG.resolution != \"INVALID\"
       AND PRD.name != \"IP\"
       AND BUG.keywords not like '%historical%'
       AND CMP.name != \"tools.orbit\"
       AND (ACT.bug_when IS NULL OR date_format(ACT.bug_when,\"%Y-%m\")  > Month)
       AND DAT.Month = \":period\"
   GROUP BY CMP.name
   ORDER BY count desc")
 ->substitute(':period', date ( 'Y-m', strtotime ( "-1 months" ) ))
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<?php
$year = date ( 'Y', strtotime ( '-1 year' ) );
$date = new DateTime("$year-04-01");
$last = new DateTime("$year-04-01");
$last->add(new DateInterval("P1Y"))->sub(new DateInterval("P1D"));

ChartBuilder::named('cq_project_year')
 ->title("Third Party CQs by Project (:startT to :endT)")
 ->description(
   "A breakdown by project of CQs created in the previous year.
    We refer to this as a \"yearly\" chart, but it really tracks from
    April 1 of the previous year to March 31 of the current year.")
 ->query('ipzilla', "
   select
    p.name as project, count(distinct b.bug_id) as count
   from bugs as b
    join components as p on b.component_id=p.id
    join keywords as kw on kw.bug_id=b.bug_id
    join keyworddefs as kwd on kw.keywordid=kwd.id and kwd.name in ('projectcode', 'thirdparty')
   where date(b.creation_ts) between date(':start') and date(':end')
   group by p.name
   order by count(distinct b.bug_id) desc")
 ->substitute(':start', $date->format('Y-m-d'))
 ->substitute(':end', $last->format('Y-m-d'))
 ->substitute(':startT', $date->format('M Y'))
 ->substitute(':endT', $last->format('M Y'))
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<?php
$year = date ( 'Y', strtotime ( '-1 year' ) );
$date = new DateTime("$year-04-01");
$last = new DateTime("$year-04-01");
$last->add(new DateInterval("P1Y"))->sub(new DateInterval("P1D"));

ChartBuilder::named('cq_typeA_project_year')
 ->title("Type A Third Party CQs by Project (:startT to :endT)")
 ->description(
   "A breakdown by project of <b>Type A</b> (license certification) Third Party
    CQs created in the previous year.
    We refer to this as a \"yearly\" chart, but it really tracks from
    April 1 of the previous year to March 31 of the current year.")
 ->query('ipzilla', "
   select
    p.name as project, count(distinct b.bug_id) as count
   from bugs as b
    join components as p on b.component_id=p.id
    join keywords as kw on kw.bug_id=b.bug_id
    join keyworddefs as kwd on kw.keywordid=kwd.id and kwd.name in ('projectcode', 'thirdparty')
   where date(b.creation_ts) between date(':start') and date(':end')
    and cf_type='Type_A'
   group by p.name
   order by count(distinct b.bug_id) desc")
 ->substitute(':start', $date->format('Y-m-d'))
 ->substitute(':end', $last->format('Y-m-d'))
 ->substitute(':startT', $date->format('M Y'))
 ->substitute(':endT', $last->format('M Y'))
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<?php

ChartBuilder::named('top_projects_commits')
 ->title("Top Projects by Commits ($end)")
 ->description(
  "A breakdown by project of the open CQs in the current month.")
 ->query('dashboard', "
    select project, count
    from SubprojectCommitActivity
 		where period=':period'
 		order by count desc")
 ->substitute(':period', date ( 'Ym', strtotime ( "-1 months" ) ))
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<h2>Quarterly Charts</h2>

<?php

list($quarter, $first, $last) = getLastQuarter ( time () );

ChartBuilder::named('top_projects_commits_quarter')
 ->title("Top Projects by Commits ($quarter)")
 ->description("A breakdown by project of the commit activity in the last quarter.")
 ->query('dashboard', "
   select project, count
   from SubprojectCommitActivity
   where period between :start and :end
   group by project
   order by count desc")
 ->substitute(':start', $first)
 ->substitute(':end', $last)
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Project', 'project', 'string', 'getProjectNickName')
 ->column('Count', 'count', 'number')
 ->render();
?>

<h3>Top 10(-ish) Projects by Contributors</h3>
<p>Based on number of (non-committer) contributors.</p>

<?php

renderQuarterlyTop10ContributorsChart ();
?>

<h3>Projects By License</h3>

<?php
renderLicensesInUseChart();
?>

<?php
list($quarter, $first, $last) = getLastQuarter(time());

ChartBuilder::named('company_committers')
 ->title("Companies by Committers in :quarter")
 ->description('Top ten companies contributing active committers to Eclipse Projects.')
 ->query('dashboard', "
   select
    ca.orgName as company,
    count(distinct cm.id) as committers,
    count(distinct gc.ref) as commits,
    count(distinct gr.project) as projects
   from Committer as cm
    join CommitterAffiliation as ca on cm.id=ca.id
    join Organization as o on ca.orgId=o.id and o.isMember=1
    join CommitterEmail as ce on cm.id=ce.id
    join GitCommitAuthor as gca on ce.email=gca.email
    join GitCommit as gc on gca.ref=gc.ref
     and date_format(gc.date, '%Y%m') between ':start' and ':end'
    join GitRepo as gr on gc.path=gr.path group by ca.orgName
   order by count(distinct cm.id) desc;
")
 ->substitute(':start', $first)
 ->substitute(':end', $last)
 ->substitute(':quarter', $quarter)
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Member', 'company', 'string')
 ->column('Count', 'committers', 'number')
 ->render();
?>

<?php
list($quarter, $first, $last) = getLastQuarter(time());

ChartBuilder::named('company_commits')
 ->title("Companies by Commits in :quarter")
 ->description('Top ten companies contributing commits to Eclipse Projects.')
 ->query('dashboard', "
   select
    ca.orgName as company,
    count(distinct cm.id) as committers,
    count(distinct gc.ref) as commits,
    count(distinct gr.project) as projects
   from Committer as cm
    join CommitterAffiliation as ca on cm.id=ca.id
    join Organization as o on ca.orgId=o.id and o.isMember=1
    join CommitterEmail as ce on cm.id=ce.id
    join GitCommitAuthor as gca on ce.email=gca.email
    join GitCommit as gc on gca.ref=gc.ref
     and date_format(gc.date, '%Y%m') between ':start' and ':end'
    join GitRepo as gr on gc.path=gr.path group by ca.orgName
   order by count(distinct gc.ref) desc;
")
 ->substitute(':start', $first)
 ->substitute(':end', $last)
 ->substitute(':quarter', $quarter)
 ->pieChart()
 ->option('sliceVisibilityThreshold', .01)
 ->column('Member', 'company', 'string')
 ->column('Count', 'commits', 'number')
 ->render();
?>

<h3>Active Member Companies</h3>
<p>Eclipse Foundation Member companies that have been active in the last
three months. To be listed here, a member company must have at least one
committer make at least one commit in the last three months.</p>

<div>

<?php
$sql = "
 select
  distinct orgId as id
 from ProjectCompanyActivity
 where orgId is not null
 order by rand()";
query('dashboard', $sql, array(), function($row) {
 $id = $row['id'];
 echo "<a href=\"http://eclipse.org/membership/showMember.php?member_id={$id}\"><img class=\"company-logo\" src=\"https://eclipse.org/membership/scripts/get_image.php?id={$id}&amp;size=small\"></a>";
});
?>
</div>

</div>
</div>

<?php
$html = ob_get_contents ();
ob_end_clean ();
$App->generatePage ( $theme, $Menu, $Nav, $pageAuthor, $pageKeywords, $pageTitle, $html );
?>