Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Trello Time Tracker
<?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>';
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.