Skip to content

A Repository to save Trello cards into a mysql database

Notifications You must be signed in to change notification settings

asabi/saveTrelloToMySQLdb

Repository files navigation

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

About

A Repository to save Trello cards into a mysql database

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published