Hace alg\u00fan tiempo que uso Redmine para gestionar proyectos, la herramienta en general es buena pero a veces necesitaba de reportes diarios de tiempo utilizado por los miembros del proyecto.<\/p>\n
En este punto se me ocurri\u00f3 si podr\u00eda lograr una consulta de referencias cruzadas (al estilo MS Excel), MySQL no soporta consultas de ese tipo pero pueden simularse:<\/p>\n
1. Escribir la consulta principal que contenga las filas m\u00e1s importantes: project identifier, task subject y total_hours, todo en un rango de fechas.<\/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>\nResultado:<\/p>\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. Ahora queda averiguar quienes han estado activos en ese mismo rango de tiempo:<\/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. La tarea es insertar ese resultado como columnas del primer resultado, de tal modo que tengamos una consulta de referencias cruzadas. He creado un procedimiento (no explico mucho al respecto) que hace esa tarea recibiendo como par\u00e1metros las fechas:<\/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>\nAl probar con la llamada: 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>\nEs todo! Luego con ese resultado es f\u00e1cil crear un peque\u00f1o reporte diario\/semanal sobre las horas utilizadas por cada usuario.<\/p>\n
Compartelo:<\/h3>