I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:<\/p>\n
1. Write the main query with rows: project identifier, task subject and total hours in a date range.<\/p>\n
select p.identifier, \r\n\te.issue_id, i.subject, round(sum(e.hours),2) as total_hours\r\n\tfrom redmine_nsp.time_entries e\r\n\tinner join redmine_nsp.issues i on e.issue_id = i.id\r\n\tinner join redmine_nsp.projects p on e.project_id = p.id\r\n\twhere e.spent_on between '2014-07-01' and '2014-07-07'\r\n group by p.identifier,e.issue_id;<\/pre>\n\r\n+------------+----------+----------------------------+-------------+\r\n| identifier | issue_id | subject | total_hours |\r\n+------------+----------+----------------------------+-------------+\r\n| bg02 | 3223 | Gestion de proyecto | 0.25 |\r\n| bg04 | 3256 | 1.1 Preparaci\u00f3n del entor | 0.63 |\r\n| emision | 3251 | Desarrollar la aplicaci\u00f3n | 3.97 |\r\n| nsp00 | 3236 | Preparar propuesta para G | 2.02 |\r\n| nsp02 | 3234 | Subida al servidor de pro | 0.52 |\r\n| nsp02 | 3240 | Agregar funcionalidad de | 0.55 |\r\n| nsp02 | 3241 | Revertir el Documento | 2.80 |\r\n| nsp02 | 3242 | Agregar Filtros en las vi | 0.72 |\r\n| nsp02 | 3243 | Reportes de Cargo de entr | 2.35 |\r\n| nsp02 | 3254 | Control de sesiones | 3.23 |\r\n| nsp05 | 3252 | Mantenimiento del servido | 0.18 |\r\n| nsp05 | 3253 | Mantenimiento a redmine | 0.53 |\r\n| nsp06 | 3203 | Elaborar el visualzador d | 0.23 |\r\n| nsp06 | 3228 | Evitar que se tome mas de | 0.25 |\r\n| nsp06 | 3255 | Actualizar el porcentaje | 1.50 |\r\n| nsp08 | 3239 | Asistencia remota | 1.38 |\r\n+------------+----------+----------------------------+-------------+\r\n<\/pre>\n2. Then list the active users between dates:<\/p>\n
select e.user_id, u.login\r\nfrom redmine_nsp.time_entries e\r\n\tinner join redmine_nsp.users u on e.user_id = u.id \r\nwhere e.spent_on between '2014-07-01' and '2014-07-07'\r\ngroup by e.user_id;<\/pre>\n+---------+----------+\r\n| user_id | login |\r\n+---------+----------+\r\n| 1 | user1 |\r\n| 4 | user2 |\r\n| 5 | user3 |\r\n| 6 | user4 |\r\n+---------+----------+\r\n<\/pre>\n3. Now, create a stored procedure to create a dynamic SQL command with the second query as column names and values with each Redmine issue. The second query is used as cursor.<\/p>\n
\r\nDELIMITER $$\r\n\r\nCREATE PROCEDURE `get_range_summary`(dDate1 date,dDate2 date)\r\nBEGIN\r\n\r\nDECLARE done INT DEFAULT 0;\r\ndeclare p_user_id int;\r\ndeclare p_sql text;\r\ndeclare p_login varchar(255);\r\ndeclare c_users cursor for\r\n\tselect e.user_id, u.login\r\n\tfrom redmine_nsp.time_entries e\r\n\t\tinner join redmine_nsp.users u on e.user_id = u.id \r\n\twhere e.spent_on between dDate1 and dDate2 \r\n\tgroup by e.user_id;\r\nDECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;\r\n\r\nset p_sql = 'select s.codigo,s.issue_id,s.subject ';\r\nopen c_users;\r\n\r\nread_loop: LOOP\r\n\tFETCH c_users INTO p_user_id, p_login;\r\n IF done THEN\r\n LEAVE read_loop;\r\n END IF;\r\n\tset p_sql = concat(p_sql,\r\n ', (select round(sum(t.hours),2) as total\r\n\t\tfrom redmine_nsp.time_entries t\r\n\t\twhere t.spent_on between \\'',dDate1,'\\' and \\'',dDate2,'\\' \r\n and t.issue_id = s.issue_id\r\n\t\tand t.user_id = ',p_user_id,') as `',p_login,'` ');\r\n\r\nEND LOOP;\r\n\r\nset @sql = concat(p_sql,' ,s.total_hours from \r\n\t(select p.identifier, e.issue_id, i.subject, \r\n round(sum(e.hours),2) as total_hours\r\n\tfrom redmine_nsp.time_entries e\r\n\tinner join redmine_nsp.issues i on e.issue_id = i.id\r\n\tinner join redmine_nsp.projects p on e.project_id = p.id\r\n\twhere e.spent_on between \\'',dDate1,'\\' and \\'',dDate2,'\\' group by p.identifier,e.issue_id) as s');\r\n\r\n\r\nclose c_users;\r\n\r\nPREPARE stmt1 FROM @sql;\r\nEXECUTE stmt1;\r\nDEALLOCATE PREPARE stmt1; \r\n\r\nEND\r\n<\/pre>\nThe result of call get_range_summary(‘2014-07-01′,’2014-07-07’)<\/strong>:<\/p>\n\r\n+---------+----------+----------------------------+-------+--------+---------+----------+-------------+\r\n| identif | issue_id | subject | user1 | user2 | user3 | user4 | total_hours |\r\n+---------+----------+----------------------------+-------+--------+---------+----------+-------------+\r\n| bg02 | 23 | Gestion de proyecto | NULL | 0.25 | NULL | NULL | 0.25 |\r\n| bg04 | 56 | 1.1 Preparaci\u00f3n del entor | NULL | 0.63 | NULL | NULL | 0.63 |\r\n| emision | 51 | Desarrollar la aplicaci\u00f3n | NULL | NULL | 3.97 | NULL | 3.97 |\r\n| nsp00 | 36 | Preparar propuesta para G | NULL | 2.02 | NULL | NULL | 2.02 |\r\n| nsp02 | 34 | Subida al servidor de pro | NULL | 0.52 | NULL | NULL | 0.52 |\r\n| nsp02 | 40 | Agregar funcionalidad de | NULL | NULL | 0.55 | NULL | 0.55 |\r\n| nsp02 | 41 | Revertir el Documento | 1.40 | NULL | 1.40 | NULL | 2.80 |\r\n| nsp02 | 42 | Agregar Filtros en las vi | NULL | NULL | 0.72 | NULL | 0.72 |\r\n| nsp02 | 43 | Reportes de Cargo de entr | NULL | NULL | NULL | 2.35 | 2.35 |\r\n| nsp02 | 54 | Control de sesiones | NULL | NULL | NULL | 3.23 | 3.23 |\r\n| nsp05 | 52 | Mantenimiento del servidor | NULL | 0.18 | NULL | NULL | 0.18 |\r\n| nsp05 | 53 | Mantenimiento a redmine | NULL | 0.53 | NULL | NULL | 0.53 |\r\n| nsp06 | 3 | Elaborar el visualzador de | NULL | 0.23 | NULL | NULL | 0.23 |\r\n| nsp06 | 28 | Evitar que se tome mas de | NULL | 0.25 | NULL | NULL | 0.25 |\r\n| nsp06 | 55 | Actualizar el porcentaje d | NULL | 1.50 | NULL | NULL | 1.50 |\r\n| nsp08 | 39 | Asistencia remota | NULL | 1.38 | NULL | NULL | 1.38 |\r\n+---------+----------+----------------------------+-------+--------+---------+----------+-------------+\r\n<\/pre>\nThat’s it. I put the result easily in a php based report and added it to cron task.<\/p>\n
Share this:<\/h3>