<?
/* 
   This file is part of the 'Science Fair In A Box' project
   SFIAB Website: http://www.sfiab.ca

   Copyright (C) 2005 Sci-Tech Ontario Inc <info@scitechontario.org>
   Copyright (C) 2005 James Grant <james@lightbox.org>

   This program is free software; you can redistribute it and/or
   modify it under the terms of the GNU General Public
   License as published by the Free Software Foundation, version 2.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; see the file COPYING.  If not, write to
   the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
   Boston, MA 02111-1307, USA.
*/

/* Components:  languages, teams */

$report_judges_fields = array(
	'last_name' =>  array(
		'name' => 'Judge -- Last Name',
		'header' => 'Last Name',
		'width' => 1.0,
		'table' => 'judges.lastname' ),

	'first_name' => array(
		'name' => 'Judge -- First Name',
		'header' => 'First Name',
		'width' => 1.0,
		'table' => 'judges.firstname' ),

	'name' =>  array(
		'name' => 'Judge -- Full Name (last, first)',
		'header' => 'Name',
		'width' => 1.75,
		'table' => "CONCAT(judges.lastname, ', ', judges.firstname)",
		'table_sort'=> 'judges.lastname' ),
		
	'namefl' =>  array(
		'name' => 'Judge -- Full Name (first last)',
		'header' => 'Name',
		'width' => 1.75,
		'table' => "CONCAT(judges.firstname, ' ', judges.lastname)",
		'table_sort'=> 'judges.lastname' ),

	'email' =>  array(
		'name' => 'Judge -- Email',
		'header' => 'Email',
		'width' => 2.0,
		'table' => 'judges.email'),

	'address' =>  array(
		'name' => 'Judge -- Address Street',
		'header' => 'Address',
		'width' => 2.0,
		'table' => "CONCAT(judges.address, ' ', judges.address2)"),

	'city' =>  array(
		'name' => 'Judge -- Address City',
		'header' => 'City',
		'width' => 1.5,
		'table' => 'judges.city' ),

	'province' =>  array(
		'name' => 'Judge -- Address '.$config['provincestate'],
		'header' => $config['provincestate'],
		'width' => 0.75,
		'table' => 'judges.province' ),

	'postal' =>  array(
		'name' => 'Judge -- Address '.$config['postalzip'],
		'header' => $config['postalzip'],
		'width' => 0.75,
		'table' => 'judges.postalcode' ),
	
	'phone_home' => array(
		'name' => 'Judge -- Phone (Home)',
		'header' => 'Phone(Home)',
		'width' => 1,
		'table' => 'judges.phonehome'),

	'phone_work' => array(
		'name' => 'Judge -- Phone (Work)',
		'header' => 'Phone(Work)',
		'width' => 1.25,
		'table' => "CONCAT(judges.phonework, ' ', judges.phoneworkext)"),

	'organization' => array(
		'name' => 'Judge -- Organization',
		'header' => 'Organization',
		'width' => 2,
		'table' => 'judges.organization'),

	'languages' => array(
		'name' => 'Judge -- Languages (REQUIRES MySQL 5.0)',
		'header' => 'Lang',
		'width' => 0.75,
		'table' => "GROUP_CONCAT(judges_languages.languages_lang ORDER BY judges_languages.languages_lang SEPARATOR ' ')",
		'group_by' => array('judges.id'),
		'components' => array('languages')),

	'complete' =>  array(
		'name' => 'Judge -- Registration Complete',
		'header' => 'Cmpl',
		'width' => 0.4,
		'table' => 'judges.complete',
		'value_map' => array ('no' => 'No', 'yes' => 'Yes')),

	'year' =>  array(
		'name' => 'Judge -- Year',
		'header' => 'Year',
		'width' => 0.5,
		'table' => 'judges_years.year'),

	'captain' => array(
		'name' => 'Judge Team -- Captain?',
		'header' => 'Cptn',
		'width' => 0.5,
		'table' => 'judges_teams_link.captain',
		'value_map' => array ('no' => 'No', 'yes' => 'Yes'),
		'components' => array('teams')),

	'team' => array(
		'name' => 'Judge Team -- Name',
		'header' => 'Team Name',
		'width' => 3.0,
		'table' => 'judges_teams.name',
		'components' => array('teams')),

	'teamnum' => array(
		'name' => 'Judge Team -- Team Number',
		'header' => 'Team',
		'width' => 0.5,
		'table' => 'judges_teams.num',
		'components' => array('teams')),

	'project_pn' => array(
		'name' => 'Project -- Number',
		'header' => 'Number',
		'width' => 0.5,
		'table' => 'projects.projectnumber',
		'components' => array('teams', 'projects')),

	'project_title' => array(
		'name' => 'Project -- Title',
		'header' => 'Project',
		'width' => 3,
		'table' => 'projects.title',
		'components' => array('teams', 'projects')),

	'project_summary' => array(
		'name' => 'Project -- Summary',
		'header' => 'Summary',
		'width' => 5,
		'table' => 'projects.summary',
		'components' => array('teams', 'projects')),

	'project_language' => array(
		'name' => 'Project -- Language',
		'header' => 'Lang',
		'width' => 0.4,
		'table' => 'projects.language',
		'components' => array('teams', 'projects')),
		
	'project_students' => array(
		'name' => 'Project -- Student Name(s) (REQUIRES MYSQL 5.0) ',
		'header' => 'Student(s)',
		'width' => 3.0,
		'table' => "GROUP_CONCAT(students.firstname, ' ', students.lastname ORDER BY students.lastname SEPARATOR ', ')",
		'group_by' => array('judges.id','judges_teams_timeslots_projects_link.id'),
		'components' => array('teams', 'projects', 'students')),

	'project_timeslot_start' => array(
		'name' => 'Project -- Timeslot Start Time (HH:MM)',
		'header' => 'Start',
		'width' => 0.75,
		'table' => "TIME_FORMAT(judges_timeslots.starttime,'%H:%i')",
		'components' => array('teams', 'projects')),

	'project_timeslot_end ' => array(
		'name' => 'Project -- Timeslot End Time (HH:MM)',
		'header' => 'End',
		'width' => 0.75,
		'table' => "TIME_FORMAT(judges_timeslots.endtime,'%H:%i')",
		'components' => array('teams', 'projects')),

	'project_timeslot' => array(
		'name' => 'Project -- Timeslot Start - End (HH:MM - HH:MM)',
		'header' => 'Timeslot',
		'width' => 1.5,
		'table' => "CONCAT(TIME_FORMAT(judges_timeslots.starttime,'%H:%i'),'-',TIME_FORMAT(judges_timeslots.endtime,'%H:%i'))",
		'components' => array('teams', 'projects')),

	'project_timeslot_date' => array(
		'name' => 'Project -- Timeslot Date - (YYYY-MM-DD)',
		'header' => 'Timeslot Date',
		'width' => 1,
		'table' => "judges_timeslots.date",
		'components' => array('teams', 'projects')),

	'static_text' =>  array(
		'name' => 'Static Text (useful for labels)',
		'header' => '',
		'width' => 0.1,
		'table' => "CONCAT(' ')"),

);

 function report_judges_fromwhere($report, $components)
 {
 	global $config, $report_judges_fields;
	
	$fields = $report_judges_fields;
	$year = $report['year'];

	/* For now languages is a module, doesn't duplicate rows, but it requries mysql 5 for
	 * a GROUP_CONCAT */
	$languages_from = '';
	if(in_array('languages', $components)) {
		$languages_from = 'LEFT JOIN judges_languages ON judges_languages.judges_id=judges.id';
	}
					
	$teams_from = '';
	$teams_where = '';
	if(in_array('teams', $components)) {
		$teams_from = "LEFT JOIN judges_teams_link ON judges_teams_link.judges_id=judges.id
				LEFT JOIN judges_teams ON judges_teams.id=judges_teams_link.judges_teams_id";
		$teams_where = "AND judges_teams_link.year='$year'
				AND judges_teams.year='$year'";
	}

	$projects_from='';
	$projects_where='';
	if(in_array('projects', $components)) {
		$projects_from = "LEFT JOIN judges_teams_timeslots_projects_link ON
					judges_teams_timeslots_projects_link.judges_teams_id=judges_teams.id
				LEFT JOIN projects ON projects.id=judges_teams_timeslots_projects_link.projects_id
				LEFT JOIN judges_timeslots ON judges_timeslots.id=judges_teams_timeslots_projects_link.judges_timeslots_id";
		$projects_where = "AND judges_teams_timeslots_projects_link.year='$year'
				AND projects.year='$year'";
	}

	$students_from='';
	$students_where='';
	if(in_array('students', $components)) {
		$students_from = "LEFT JOIN students ON students.registrations_id=projects.registrations_id";
		$students_where = "AND students.year='$year'";
	}

	/* Search the report for a filter based on judge year */
	$year_where = "judges_years.year='$year'";
	foreach($report['filter'] as $d) {
		if($d['field'] == 'year') {
			/* Don't interally filter on year, we'll do it
			 * externally */
			$year_where = '1';
		}
	}
										
	$q = "	FROM 	judges
			LEFT JOIN judges_years ON judges_years.judges_id = judges.id
			$languages_from
			$teams_from
			$projects_from
			$students_from
		WHERE
			$year_where
			$teams_where
			$projects_where
			$students_where 
		";

	return $q;
}

?>