export/cqs.csv.php

<?php
/*******************************************************************************
 * Copyright (c) 2017 Eclipse Foundation and others.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *******************************************************************************/

/*
 * Export CQ information from IPZilla in CSV format.
 *
 * e.g. http://www.eclipse.org/projects/export/cqs.csv.php
 *
 * INTERNAL USE ONLY: restricted to callers within the Eclipse Foundation.
 */
require_once(dirname(__FILE__) . "/../../eclipse.org-common/system/app.class.php");
$App = new App();
require_once(dirname(__FILE__) . "/../classes/database.inc");
require_once(dirname(__FILE__) . "/../classes/common.php");
require_once(dirname(__FILE__) . "/../classes/debug.php");

mustBeEclipseFoundationCaller();

$sql = "
    select
      b.bug_id as id,
      c.name as project,
      b.short_desc as description,
      b.cf_type as type,
      date(b.creation_ts) as created,
      b.bug_status as status,
      b.resolution as resolution,
      b.bug_severity as state,
      b.cf_license as license,
      group_concat(kd.name) as keywords,
      l.thetext as comment
    from bugs as b
      join components as c on b.component_id = c.id
      join (
        select
          bug_id, min(comment_id) as comment_id
        from longdescs group by bug_id
      ) as s on b.bug_id=s.bug_id
      join longdescs as l on s.bug_id=l.bug_id and s.comment_id=l.comment_id
      join keywords as k on b.bug_id=k.bug_id
      join keyworddefs as kd on k.keywordid=kd.id
    group by b.bug_id";

$columns = array('id','project','description','type','created','status','resolution','state','licenses','keywords','projecturl','sourceurl');

$fp = fopen('php://output', 'w');
fputcsv($fp, $columns);

query('ipzilla', $sql, array(), function($row) use (&$fp, &$columns) {
 // Grab the 'comment' row and use it to compute some
 // other values and remove it from the array to avoid it being included
 // in the output.
 $comment = $row['comment'];
 unset($row['comment']);

 // The "Project URL" and "Source URL" values are represented in
 // the text of the first comment on a CQ.
 $row['projecturl'] = (preg_match('/^Project URL:\s*(\S+)$/m', $comment, $matches)) ? trim($matches[1]) : '';
 $row['sourceurl'] = (preg_match('/^Source URL:\s*(\S+)$/m', $comment, $matches)) ? trim($matches[1]) : '';

 fputcsv($fp, $row);
});

fclose($fp);

?>