[sfiab-commits] r1092 - trunk/admin

[<in...@sf...>]

Author: dave
Date: 2007-12-24 16:18:08 -0500 (Mon, 24 Dec 2007)
New Revision: 1092

Modified:
   trunk/admin/reports_awards.inc.php
Log:
- Clean up the award query.
- Fix a bug in the award query tha twas incorrectly linking judges teams with judges
- Add 'Judge Name' to the query so non mysql5 users can create an award report
  that lists the judge names for each award.



Modified: trunk/admin/reports_awards.inc.php
===================================================================
--- trunk/admin/reports_awards.inc.php	2007-12-24 19:38:06 UTC (rev 1091)
+++ trunk/admin/reports_awards.inc.php	2007-12-24 21:18:08 UTC (rev 1092)
@@ -202,13 +202,21 @@
 		'width' => 0.5,
 		'table' => 'judges_teams.num'),
 
+	'judgeteammembers_name' => array(
+		'components' => array('judgingteam', 'judgingteammembers'),
+		'name' => 'Judging Team -- Judge Name',
+		'header' => 'Judge Name',
+		'width' => 1.5,
+		'table' => "CONCAT(judges.firstname, ' ', judges.lastname)"),
+
 	'judgeteammembers' => array(
-		'components' => array('judgingteam'),
+		'components' => array('judgingteam', 'judgingteammembers'),
 		'name' => 'Judging Team -- Members (REQUIRES MySQL 5.0)',
 		'header' => 'Team Members',
 		'width' => 3.0,
-		'table' => "GROUP_CONCAT(judges.firstname)",
-		'group_by' => array('judges_teams.num') ),
+		'table' => "GROUP_CONCAT(judges.firstname, ' ', judges.lastname ORDER BY judges.lastname SEPARATOR ', ')",
+		'group_by' => array('award_awards.id', 'judges_teams.num') ),
+
 		
 
 );
@@ -220,34 +228,41 @@
 	$fields = $report_awards_fields;
 	$year = $report['year'];
 
-
 	$judges_join = '';
 	$judges_where = '';
-
 	if(in_array('judgingteam', $components)) {
-	   	$judges_join = ', judges_teams_awards_link, judges_teams,
-					judges_teams_link, judges';
-		$judges_where = 'AND judges_teams_awards_link.award_awards_id=award_awards.id
-				AND judges_teams.id=judges_teams_awards_link.judges_teams_id
-				AND judges_teams_link.id=judges_teams.id
-				AND judges.id=judges_teams_link.judges_id';
+	   	$judges_join = 'LEFT JOIN judges_teams_awards_link ON judges_teams_awards_link.award_awards_id=award_awards.id
+				LEFT JOIN judges_teams ON judges_teams.id=judges_teams_awards_link.judges_teams_id';
+		$judges_where = "AND judges_teams_awards_link.year='$year'
+				AND judges_teams.year='$year'";
 	}
 
-	$q = "	FROM 
-			award_awards LEFT JOIN award_sponsors ON (
+	$judges_members_join = '';
+	$judges_members_where = '';
+	if(in_array('judgingteammembers', $components)) {
+		$judges_members_join = 'LEFT JOIN judges_teams_link ON judges_teams_link.judges_teams_id=judges_teams.id
+				LEFT JOIN judges ON judges.id=judges_teams_link.judges_id';
+
+		$judges_members_where = "AND judges_teams_link.year='$year'";
+	}
+
+
+	$q = "	FROM  award_awards 
+			LEFT JOIN award_sponsors ON (
 				award_sponsors.id=award_awards.award_sponsors_id
 				AND award_sponsors.year=award_awards.year)
+			LEFT JOIN award_types ON award_types.id=award_types_id
 			LEFT JOIN award_contacts AS PRIMARYCONTACT ON (
 					PRIMARYCONTACT.award_sponsors_id=award_sponsors.id
 					AND PRIMARYCONTACT.year=award_awards.year
-					AND PRIMARYCONTACT.`primary`='yes'),
-			award_types
+					AND PRIMARYCONTACT.`primary`='yes')
 			$judges_join
+			$judges_members_join
 		WHERE
 			award_awards.year='$year'
-			AND award_types.id=award_types_id
 			AND award_types.year='$year'
 			$judges_where 
+			$judges_members_where
 		";
 
 	return $q;