[WTF CVS] wtf/lib/WTF SQL.pm,1.14,1.15

[Gryphon S. <gry...@us...>]

Update of /cvsroot/wtf-tracker/wtf/lib/WTF
In directory sc8-pr-cvs9.sourceforge.net:/tmp/cvs-serv1183/lib/WTF

Modified Files:
	SQL.pm 
Log Message:
Feature add from bug 1656452: Activity Excel download

Index: SQL.pm
===================================================================
RCS file: /cvsroot/wtf-tracker/wtf/lib/WTF/SQL.pm,v
retrieving revision 1.14
retrieving revision 1.15
diff -C2 -d -r1.14 -r1.15
*** SQL.pm	9 Mar 2007 22:34:53 -0000	1.14
--- SQL.pm	21 Mar 2007 17:26:31 -0000	1.15
***************
*** 322,325 ****
--- 322,326 ----
  			IF( DATE_FORMAT( work_day, '%Y-%m' ) = ?, 1, 0 ) AS selected
  		FROM summary
+ 		WHERE vacation = 0 AND sick = 0
  		GROUP BY datestamp
  		ORDER BY datestamp DESC
***************
*** 410,413 ****
--- 411,451 ----
  	},
  
+ 	'activity_xls_activities' => q{
+ 		SELECT id, activity_id AS parent_id, name
+ 		FROM activity
+ 		WHERE id NOT IN (
+ 			SELECT DISTINCT activity_id FROM activity
+ 			WHERE activity_id IS NOT NULL AND active = 1
+ 		) AND active = 1
+ 		ORDER BY ordering
+ 	},
+ 
+ 	'activity_xls_percentages' => q{
+ 		SELECT
+ 			e.id AS employee_id, e.name AS employee_name, e.active,
+ 			a.id AS activity_id, AVG( acts.percentage ) AS avg_percentage
+ 		FROM employee AS e 
+ 		LEFT JOIN summary AS s ON s.employee_id = e.id
+ 		LEFT JOIN activity_summary AS acts ON s.id = acts.summary_id
+ 		LEFT JOIN activity AS a ON acts.activity_id = a.id
+ 		WHERE (
+ 			1 NOT IN ( s.vacation, s.sick ) AND SUBSTRING( s.work_day, 1, 7 ) = ? AND
+ 			a.id NOT IN (
+ 				SELECT DISTINCT activity_id FROM activity
+ 				WHERE activity_id IS NOT NULL AND active = 1
+ 			)
+ 		) OR ( a.id IS NULL AND s.vacation IS NULL AND s.sick IS NULL )
+ 		GROUP BY a.id, e.id
+ 		ORDER BY e.name, a.ordering
+ 	},
+ 
+ 	'activity_xls_vacation_sick' => q{
+ 		SELECT SUM( vacation ) AS vacation, SUM( sick ) AS sick
+ 		FROM summary
+ 		WHERE employee_id = ? AND SUBSTRING( work_day, 1, 7 ) = ?
+ 	},
+ 
+ 	'parent_activity' => 'SELECT id, name FROM activity WHERE id = ?',
+ 
  );