Trello Time Tracker

TrelloTimeTracker.php

<?php

// Trello Time Tracker
// (C) 2017 Anthony Eden
// http://mediarealm.com.au/

// This script takes an JSON export from a Trello board (saved as data.json) and shows you a timesheet
// It uses Custom Fields in Trello to read the hours spent working on each card, then displays it as a table
// Simple, yet very handy.

// Set this field to the exact name of your Custom Field in Trello
$CONFIG_TimeFieldName = "Development Time (Hrs)";

// Set this to the number of hours you've already billed
$CONFIG_TimeBilled = 5.00;

// Set this to the number of dollars per hour you charge.
$CONFIG_TimeRate = 120;

// Set this to the number of hours you wish to achieve this billing cycle (since $CONFIG_TimeBilled was last set)
$CONFIG_TimeTarget = 9;

/* ************************** STOP EDITING HERE ************************** */



// data.json must be an export directly from Trello
$dataRaw = file_get_contents("data.json");
$data = json_decode($dataRaw, true);

// We work out the Plugin ID and FieldID and store them here
$CustomField_PluginId = false;
$CustomField_TimeFieldName = false;

// Find the key for the custom field
foreach($data['pluginData'] as $pluginKey => $pluginData) {
    if(isset($pluginData['value'])) {
        $pluginDataValues = json_decode($pluginData['value'], true);

        if(isset($pluginDataValues['fields'])) {
            // Loop over every custom field to try and find the correct one:
            foreach($pluginDataValues['fields'] as $fieldKey => $fieldData) {
                // Does this Custom Field match the name specified in $CONFIG_TimeFieldName ?
                if($fieldData['n'] == $CONFIG_TimeFieldName) {
                    $CustomField_PluginId = $pluginData['idPlugin'];
                    $CustomField_TimeFieldName = $fieldData['id'];
                }
            }
        }
    }
}

// Error - custom field not found
if($CustomField_TimeFieldName == false) {
    die("ERROR: Could not find field name " . $CONFIG_TimeFieldName);
}


// Find and store all the list names/ids
$lists = array();
foreach($data['lists'] as $key => $val)
    $lists[$val['id']] = $val['name'];



$jobs = array();
foreach($data['cards'] as $cardKey => $cardData) {

    $job = array(
        "name" => $cardData['name'],
        "lastActivity" => strtotime($cardData['dateLastActivity']),
        "time" => 0,
        "status" => $lists[$cardData['idList']],
    );

    // Loop over all the active plugins, trying to find the Custom Fields plugin
    foreach($cardData['pluginData'] as $plugin) {

        if($plugin['idPlugin'] == $CustomField_PluginId
        && isset($plugin['value'])) {

            // JSON Decode the custom field values
            $pluginDataValues = json_decode($plugin['value'] , true);

            foreach($pluginDataValues as $dataKey => $dataValue) {
                if(isset($dataValue[$CustomField_TimeFieldName])) {
                    // We've found the plugin and field - add the value to this job
                    $job['time'] += $dataValue[$CustomField_TimeFieldName];
                }
            }
        }

    }

    // Add this job to the big list of jobs
    $jobs[] = $job;

}

// Calculate the total time used on this board:
$totalTime = 0;
foreach($jobs as $job) {
    $totalTime += $job['time'];
}

// Create Output:
echo '<table width="100%">';
echo '<tr>';
echo '<th>Job Name</th>';
echo '<th>Status</th>';
echo '<th>Last Activity</th>';
echo '<th>Hours</th>';
echo '</tr>';

// Sort the job list by last activity
usort($jobs, function ($a, $b) {
    return $b['lastActivity'] - $a['lastActivity'];
});

foreach($jobs as $job) {
    echo '<tr>';
    echo '<td>'.$job['name'].'</td>';
    echo '<td>'.$job['status'].'</td>';
    echo '<td>'.date("Y-m-d", $job['lastActivity']).'</td>';
    echo '<td>'.$job['time'].'</td>';
    echo '</tr>';
}

echo '<tr>';
echo '<th style="text-align: right;" colspan="3">Total Time Spent:</th>';
echo '<th style="text-align: left;">' . $totalTime . 'hrs</th>';
echo '</tr>';

echo '<tr>';
echo '<th style="text-align: right;" colspan="3">Total Time Unbilled:</th>';
echo '<th style="text-align: left;">' . ($totalTime - $CONFIG_TimeBilled) . 'hrs</th>';
echo '</tr>';

echo '<tr>';
echo '<th style="text-align: right;" colspan="3">Value of Time Unbilled:</th>';
echo '<th style="text-align: left;">$' . (($totalTime - $CONFIG_TimeBilled) * $CONFIG_TimeRate) . ' @ $'.$CONFIG_TimeRate.'/hr</th>';
echo '</tr>';

$remainingTime = $CONFIG_TimeTarget  - ($totalTime - $CONFIG_TimeBilled);
$remainingHours = floor($remainingTime);
$remainingMins = $remainingTime - ($remainingHours / 60) * 60;

echo '<tr>';
echo '<th style="text-align: right;" colspan="3">Billing Cycle Time Target:</th>';
echo '<th style="text-align: left;">' . $remainingHours . 'hrs, ' . $remainingMins . 'mins</th>';
echo '</tr>';

echo '</table>';

?>