github_to_excel.php

<?php

/* 
 * state: string  Indicates the state of the issues to return. Can be either open, closed, or all. Default: open
 */
define("ISSUE_URL", "https://api.github.com/repos/MaisonLogicielLibre/Website/issues?state=all&page=");
define("LABEL_NAME_KEY", "name");
define("OUTPUT_FILE_NAME", "github.csv");
define("DATE_FORMAT", "F j, Y");

date_default_timezone_set("America/Montreal"); /* This is required by PHP */

$csvColumns = array("number", "updated_at", "assignee", "state", "title", "body");

print("Fetching Issues from github\n");
$i = 0;
$jsonIssues = array();
do {
    $json = json_from_url(ISSUE_URL . $i);

    if ($json == false) {
        break;
    }

    $jsonIssues = array_merge($jsonIssues, $json);
    $i = $i + 1;

    // Github: The rate limit allows you to make up to 60 requests per hour.
    // Ref: https://developer.github.com/v3/#rate-limiting
} while(true);

if (count($jsonIssues) == 0) {
    error_log("[Error] No issues found, cannot build CSV file.");
    exit;
}

print("Building CSV file\n");
/*
 * For some reason, the github API does not return all labels when we query
 * MaisonLogicielLibre/Website/labels. So this is how we get labels.
 */
foreach ($jsonIssues as $issue) {
    foreach ($issue["labels"] as $label) {
        $labels[$label[LABEL_NAME_KEY]] = "";
    } 
}

$csvOutput = fopen(OUTPUT_FILE_NAME, "w");
fputcsv($csvOutput, array_merge($csvColumns, array_keys($labels))); // Print Header
foreach ($jsonIssues as $issue) {
    $issue["updated_at"] = date(DATE_FORMAT, strtotime(trim($issue["updated_at"])));
    $issue["assignee"] = $issue["assignee"]["login"];
    $issue["body"] = trim(preg_replace('/\s+/', ' ', $issue["body"]));

    $line = array(); // Clear line for every iteration
    foreach ($csvColumns as $column) {
        $line[] = trim($issue[$column], " \t\n\r");
    }
    $line = array_merge($line, $labels);
    foreach ($issue["labels"] as $label) {
        $line[$label[LABEL_NAME_KEY]] = 1;
    }
    fputcsv($csvOutput, $line);
}
fclose($csvOutput);

/**
 * Get JSON array from URL.
 *
 * @param string $url link to the labels.
 * @return array JSON array with response content.
 */
function json_from_url($url) {
    $curl = curl_init();
    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_USERAGENT,  // Github requires user agent.
        'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)'); 
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); // Don't echo output
    $data = curl_exec($curl);
    $httpcode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
    curl_close($curl);

    if ($httpcode>=200 && $httpcode<300) {
        return json_decode($data, true);
    } else {
        error_log("[Warning] HTTP response: " . $httpcode, 0);
         return false;
    }
}
?>