A Repository to save Trello cards into a mysql database
PHP Shell
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
.idea
alfredWorkflow
vendor
.gitignore
README.md
composer.json
composer.lock
config.php.template
execute.sh
trello.sql
trelloToMySQL.php

README.md

Store Trello card information in a MySQL database

The purpose of this project is to allow running different statistical queries on Trello cards (read only access to trello cards).

##Requirements:

##Installation:

  • Download the code
  • Run "composer install"
  • Go to Trello.com API page, and get a key and a token for your trello account (https://developers.trello.com/get-started)
  • Create a new MySQL database (I named mine "trello"), and load the trello.sql file into it.
  • cp config.php.template config.php
  • Update config.php with the information to your database and the information to your trello account.
  • run it once to test: php trelloToMySQL.php (your mysql database should be populated with your trello cards information)
  • Create yourself a cron job to run it every X minutes / hours ... whatever works for you. -- Example: 0 * * * * /usr/bin/php /path/to/trelloToMySQL.php will run it every hour

#Some useful queries:

Cards by list (excluding archived):

SELECT COUNT(card.id)as cards, list.name AS listName FROM `card` INNER join `board` on `board`.id = `card`.idBoard INNER join list on list.idBoard = board.id And card.idList = list.id Where card.`closed`=0 and board.`name`='Life' GROUP BY list.id Order by FIELD(list.name,'Project','Later','waiting on','next week','this week','today','done')

Summary information:

  • Note: Some of those assume that completed cards are moved to a "Done" list before they get archived.
SELECT 'Avg Days To Complete:' as Description, AVG(DATEDIFF(card.`dateLastActivity`,card.timeCreated)) AS Stat FROM card WHERE card.`closed` = 1 UNION SELECT 'Avg Days Opened:', AVG(DATEDIFF(card.`dateLastActivity`,card.timeCreated)) AS avgDaysToClose FROM card WHERE card.`closed` = 0 UNION Select 'Completed Cards:',count(card.id) as openedCards from card INNER JOIN list ON list.id = card.idList WHERE list.name = 'Done' UNION Select 'Available Cards:',count(card.id) as openedCards from card WHERE card.`closed` = 0 UNION SELECT 'AVG Completed/Weekday:',AVG(completedCards) FROM ( SELECT DATE_FORMAT(`dateLastActivity`,'%Y-%m-%d'), count(card.id) as completedCards from card INNER JOIN list ON list.id = card.idList WHERE list.name = 'Done' AND WEEKDAY(`dateLastActivity`) NOT IN (5,6) GROUP BY DATE_FORMAT(`dateLastActivity`,'%Y-%m-%d')) AS dailyAvgWeekday UNION SELECT 'AVG Completed/day:',AVG(completedCards) FROM ( Select DATE_FORMAT(`dateLastActivity`,'%Y-%m-%d'), count(card.id) as completedCards from card INNER JOIN list ON list.id = card.idList WHERE list.name = 'Done' GROUP BY DATE_FORMAT(`dateLastActivity`,'%Y-%m-%d')) AS dailyAvg UNION SELECT 'AVG Added/Day:',AVG(addedCards) FROM ( Select DATE_FORMAT(`timeCreated`,'%Y-%m-%d'), count(card.id) as addedCards from card GROUP BY DATE_FORMAT(`timeCreated`,'%Y-%m-%d') ) AS dailyAddedAvg UNION SELECT 'Deferred Tickets:', count(card.id) FROM card INNER JOIN list ON list.id = card.idList WHERE card.close =1 AND list.name != 'Done'

##AVG Cards added by day of the week:

SELECT DATE_FORMAT(weekDay,'%W'), ROUND(AVG(addedCards),0) AS avgCompletedCards FROM ( SELECT DATE_FORMAT(card.`timeCreated`,'%Y-%m-%d') AS weekDay , count(card.id) as addedCards from card GROUP BY DATE_FORMAT(`timeCreated`,'%Y-%m-%d') ) AS cards GROUP BY DATE_FORMAT(weekDay,'%W') ORDER BY FIELD(DATE_FORMAT(weekDay,'%W'),'Saturday','Sunday','Monday','Tuesday','Wednesday','Thursday','Friday')

##AVG cards completed / day of the week (most efficient / less efficient):

  • Note: Assumes that completed cards are moved to a "Done" list before they get archived.
SELECT DATE_FORMAT(weekDay,'%W'), ROUND(AVG(completedCards),0) AS avgCompletedCards FROM ( Select DATE_FORMAT(`dateLastActivity`,'%Y-%m-%d') AS weekDay , count(card.id) as completedCards from card INNER JOIN list ON list.id = card.idList WHERE list.name = 'Done' GROUP BY DATE_FORMAT(`dateLastActivity`,'%Y-%m-%d') ) AS cards GROUP BY DATE_FORMAT(weekDay,'%W') ORDER BY FIELD(DATE_FORMAT(weekDay,'%W'),'Saturday','Sunday','Monday','Tuesday','Wednesday','Thursday','Friday')

##AVG days to close cards by year/month:

SELECT DATE_FORMAT(card.timeCreated,'%Y %M') AS monthYear, AVG(DATEDIFF(card.`dateLastActivity`,card.timeCreated)) AS avgDaysToClose FROM card INNER JOIN list ON list.id = card.`idList` WHERE list.name = 'Done' GROUP BY DATE_FORMAT(card.timeCreated,'%Y %M')

##AVG days to close cards:

SELECT AVG(DATEDIFF(card.`dateLastActivity`,card.timeCreated)) AS avgDaysToClose FROM card INNER JOIN list ON list.id = card.`idList` WHERE list.name = 'Done'

#Assumptions for queries that involve projects:

  • Every project has a label that starts with P- or CP- -- P- Stands for project -- CP- Stands for Completed project

List of cards per project

SELECT card.name,label.`name` FROM `card` INNER JOIN label ON card.labels LIKE CONCAT('%',label.name,'%') WHERE label.name LIKE 'P-%' OR label.`name` LIKE 'CP-%' ORDER BY label.name;

##List of projects by last activity compared to when they were created:

SELECT DATEDIFF(max(card.`dateLastActivity`), min(card.timeCreated)) AS lastDay, card.name, label.`name` FROM `card` INNER JOIN label ON card.labels LIKE CONCAT('%',label.name,'%') WHERE label.name LIKE 'P-%' OR label.`name` LIKE 'CP-%' GROUP BY label.`name` ORDER BY DATEDIFF(max(card.`dateLastActivity`), min(card.timeCreated)) DESC;

##Projects by inactive days and days opened and number of open tickets:

SELECT count(card.id) - 1 AS tickets, label.`name`, DATEDIFF(NOW(),max(card.`dateLastActivity`)) AS inactiveDays, DATEDIFF(NOW(),min(card.`timeCreated`)) AS daysOpened FROM card INNER JOIN `cardLabel` ON `cardLabel`.`idCard` = card.`id` INNER JOIN label ON label.id = `cardLabel`.idLabel WHERE label.`name` LIKE 'CP-%' OR label.`name` LIKE 'P-%' GROUP BY `cardLabel`.idLabel ORDER BY DATEDIFF(NOW(),max(card.`dateLastActivity`)) DESC