<?php
DisplayHeader(".:".ProgramTitle().":.");

if(!$_REQUEST['servicefunction'])
	$_REQUEST['servicefunction'] = 'duplicate_requests';

$reports = array('duplicate_requests'=>_('Duplicate Requests'),'students_too_many'=>_('Students with too many Requests'),'students_too_few'=>_('Students with too few Requests'),'courses_too_many'=>_('Courses with too many Requests'),'courses_too_few'=>_('Courses with too few Requests'),'courses_unscheduled'=>_('Courses with Unfilled Requests'),'sections_overscheduled'=>_('Overfilled Sections'),'overlapping_singletons'=>_('Overlapping Singletons'),'period_counts'=>_('Period Counts'));
if($_Config['scheduling_multiple_periods'] == true)
{
	unset($reports['students_too_many']);
	unset($reports['students_too_few']);
}

// 'non_applicable_restrictions'=>_('Non Applicable Restrictions'),'overlapping_groups'=>_('Overlapping Groups')
// teachers with open periods
// teachers with double-scheduled periods
// delete the duplicate -- delete all duplicates

foreach($reports as $servicefunction=>$title)
{
	$tabs[] = array('title'=>$title,'link'=>"modloader.php?servicemod=$_REQUEST[servicemod]&servicefunction=".$servicefunction);
}
$_SYSTEM['selected_tab'] = "modloader.php?servicemod=$_REQUEST[servicemod]&servicefunction=$_REQUEST[servicefunction]";

echo '<BR>';
PopTable('header',$tabs);
echo '<BR>';

switch($_REQUEST['servicefunction'])
{
	case 'duplicate_requests':
		if($_REQUEST['delete_duplicate_requests']=='true')
		{
			if(Prompt(_('Delete Confirmation'),_('Do you want to delete all the duplicate requests?')))
			{
				$count_array = db_request(db_execute("select course_id,student_id,count(*) AS COUNT,STUDENT_ID AS FULL_NAME,COURSE_ID AS COURSE,COURSE_ID AS SHORT_NAME from schedule_requests WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."' group by course_id,student_id having count(*) > 1"),$functions);
				foreach($count_array as $request)
				{
					$requests_array = db_request(db_execute("SELECT REQUEST_ID FROM STUDENT_SCHEDULE_REQUESTS WHERE SCHOOL_YEAR='".get_school_year()."' AND STUDENT_ID='".$request['STUDENT_ID']."' AND COURSE_ID='".$request['COURSE_ID']."'"));
					for($i=2;$i<=count($requests_array);$i++)
						db_execute("DELETE FROM STUDENT_SCHEDULE_REQUESTS WHERE REQUEST_ID='".$requests_array[$i]['REQUEST_ID']."'");
				}
				unset($_REQUEST['delete_duplicate_requests']);
			}
		}

		if(!$_REQUEST['delete_duplicate_requests'])
		{
			$courses_array = db_request(db_execute("SELECT COURSE_ID,TITLE,SHORT_NAME FROM COURSES WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('COURSE_ID'));
	
			if(Preferences('NAME_FORMAT')=='WCM')
				$full_name = "s.LAST_NAME".CCAT."', '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
			else
//				$full_name = "s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')".CCAT."' '".CCAT."s.LAST_NAME";
				$full_name = "s.LAST_NAME".CCAT."' '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
			$addition = array('SELECT_ONLY'=>'sr.COURSE_ID,sr.STUDENT_ID,count(*),'.$full_name.' AS FULL_NAME,sr.COURSE_ID AS SHORT_NAME,sr.COURSE_ID AS COURSE',
							'FROM'=>',STUDENT_SCHEDULE_REQUESTS sr',
							'WHERE'=>'AND sr.SCHOOL_YEAR=ssm.SCHOOL_YEAR AND sr.SCHOOL_ID=ssm.SCHOOL_ID AND sr.STUDENT_ID=ssm.STUDENT_ID ',
							'GROUP'=>'sr.COURSE_ID,sr.STUDENT_ID,s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME HAVING COUNT(*)>1',
							'functions'=>array('COURSE'=>'_makeCourse','SHORT_NAME'=>'_makeCourse')
			);
			$count_array = get_student_list($addition);

			if(count($count_array))
			{
				$header_extra = ' '._('These should likely be deleted.').'<BR><div style="text-align:right"><INPUT type=button value="'._('Delete All the Duplicate Requests').'" onclick="document.location.href=\''.SetRequestOptions().'&delete_duplicate_requests=true\'"></div>';
			}
			DisplayHeader('<div style="width:800">'._('Duplicate requests are requests for courses that were made by a student more than once.').$header_extra.'</div>');
	
			$columns = array('FULL_NAME'=>_('Student Name'),'STUDENT_ID'=>_('ID number'),'COURSE'=>_('Course'),'SHORT_NAME'=>_('Course Number'),'COUNT'=>_('Number of Requests'));
			$link['FULL_NAME'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdStudReqProperties.php&useontop=false','variables'=>array('student_id'=>'STUDENT_ID'),'js'=>true);
			Gridoutput($count_array,$columns,_('Duplicate Request'),_('Duplicate Requests'),$link);
		}
	break;

	case 'students_too_many':
		// semester-long classes
		DisplayHeader('<div style="width:800">'._('Students with too many requests are students who have requested more classes than there are periods in the day.  Requests for semester-long courses are counted as half a request.  This excludes requests which are alternates for other requests.  You can exclude periods from being part of the normal school day by unchecking them below.  Click the students name to delete or modify his or her requests.').'</div>');
		echo '<CENTER><TABLE bgcolor="#'.Preferences('COLOR').'"><TR><TD>'._('Periods in the School Day:').'</TD>';
		$periods_array = db_request(db_execute("SELECT SHORT_NAME,SCHOOL_PERIOD_ID FROM SCHOOL_PERIODS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."' ORDER BY SORT_ORDER"));
		$custom = false;
		foreach($periods_array as $period)
		{
			if($_REQUEST['period_'.$period['SCHOOL_PERIOD_ID']])
			{
				$custom = true;
				$custom_periods['period_'.$period['SCHOOL_PERIOD_ID']] = 'Y';
			}
		}

		if(!$custom)
		{
			if(Preferences('MULTI_CHECKBOX_PERIODS_IN_DAY','Schedule'))
			{
				$_REQUEST += unserialize(Preferences('MULTI_CHECKBOX_PERIODS_IN_DAY','Schedule'));
			}
			else
			{
				foreach($periods_array as $period)
					$_REQUEST['period_'.$period['SCHOOL_PERIOD_ID']] = 'Y';
			}
		}
		else
			SetPreference('MULTI_CHECKBOX_PERIODS_IN_DAY','Schedule',serialize($custom_periods),get_school_year(),get_school_id());

		if(count($periods_array))
		{
			foreach($periods_array as $period)
			{
				echo '<TD>'.CheckBoxOnclick('period_'.$period['SCHOOL_PERIOD_ID'],$period['SHORT_NAME']).'</TD>';
				if($_REQUEST['period_'.$period['SCHOOL_PERIOD_ID']]=='Y')
					$periods_extra .= ",'".$period['SCHOOL_PERIOD_ID']."'";
	
			}
			if($periods_extra)
				$periods_extra = ' AND SCHOOL_PERIOD_ID IN ('.substr($periods_extra,1).') ';
		}
		echo '</TR></TABLE></CENTER>';

		$periods_count_array = db_request(db_execute("SELECT COUNT(*) FROM SCHOOL_PERIODS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'".$periods_extra));
		$periods_count_array = $periods_count_array[1]['COUNT'];
		if(is_null($periods_count_array)) 
			$periods_count_array = 0;
		//$count_array = db_request(db_execute("SELECT sr.STUDENT_ID,sr.STUDENT_ID AS FULL_NAME,SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).") AS COUNT FROM STUDENT_SCHEDULE_REQUESTS sr,COURSES c WHERE c.COURSE_ID=sr.COURSE_ID AND sr.SCHOOL_YEAR='".get_school_year()."' AND sr.SCHOOL_ID='".get_school_id()."' AND sr.PRIORITY IS NULL GROUP BY sr.STUDENT_ID HAVING SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).")>'".$periods_count_array."'"),$functions);

		if(Preferences('NAME_FORMAT')=='WCM')
			$full_name = "s.LAST_NAME".CCAT."', '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
		else
//			$full_name = "s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')".CCAT."' '".CCAT."s.LAST_NAME";
			$full_name = "s.LAST_NAME".CCAT."' '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
		$addition = array('SELECT_ONLY'=>'sr.STUDENT_ID,count(*),'.$full_name." AS FULL_NAME,SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).") AS COUNT",
						'FROM'=>',STUDENT_SCHEDULE_REQUESTS sr,COURSES c',
						'WHERE'=>"AND c.COURSE_ID=sr.COURSE_ID AND sr.SCHOOL_YEAR=ssm.SCHOOL_YEAR AND sr.SCHOOL_ID=ssm.SCHOOL_ID AND sr.STUDENT_ID=ssm.STUDENT_ID AND (sr.PRIORITY IS NULL OR sr.PRIORITY='1')",
						'GROUP'=>"sr.STUDENT_ID,s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME HAVING SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).")>'".$periods_count_array."'",
						'ORDER_BY'=>'s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME'
		);
		$count_array = get_student_list($addition);		
		
		$columns = array('FULL_NAME'=>_('Student Name'),'STUDENT_ID'=>_('ID number'),'COUNT'=>_('Number of Requests'));
		$link['FULL_NAME'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdStudReqProperties.php&useontop=false','variables'=>array('student_id'=>'STUDENT_ID'),'js'=>true);
		Gridoutput($count_array,$columns,_('Student with too many Requests'),_('Students with too many Requests'),$link);
	break;
	
	case 'students_too_few':
		// semester-long classes
		DisplayHeader('<div style="width:800">'._('Students with too few requests are students who have requested fewer classes than there are periods in the day. Requests for semester-long courses are counted as half a request. This report excludes requests which are alternates for other requests. You can exclude periods from being part of the normal school day by unchecking them below. Click the students name to add to his or her requests.').'</div>');
		echo '<CENTER><TABLE bgcolor="#'.Preferences('COLOR').'"><TR><TD>'._('Periods in the School Day:').'</TD>';
		$periods_array = db_request(db_execute("SELECT SHORT_NAME,SCHOOL_PERIOD_ID FROM SCHOOL_PERIODS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."' ORDER BY SORT_ORDER"));
		$custom = false;
		foreach($periods_array as $period)
		{
			if($_REQUEST['period_'.$period['SCHOOL_PERIOD_ID']])
			{
				$custom = true;
				$custom_periods['period_'.$period['SCHOOL_PERIOD_ID']] = 'Y';
			}
		}

		if(!$custom)
		{
			if(Preferences('MULTI_CHECKBOX_PERIODS_IN_DAY','Schedule'))
			{
				$_REQUEST += unserialize(Preferences('MULTI_CHECKBOX_PERIODS_IN_DAY','Schedule'));
			}
			else
			{
				foreach($periods_array as $period)
					$_REQUEST['period_'.$period['SCHOOL_PERIOD_ID']] = 'Y';
			}
		}
		else
			SetPreference('MULTI_CHECKBOX_PERIODS_IN_DAY','Schedule',serialize($custom_periods),get_school_year(),get_school_id());


		if(count($periods_array))
		{
			foreach($periods_array as $period)
			{
				echo '<TD>'.CheckBoxOnclick('period_'.$period['SCHOOL_PERIOD_ID'],$period['SHORT_NAME']).'</TD>';
				if($_REQUEST['period_'.$period['SCHOOL_PERIOD_ID']]=='Y')
					$periods_extra .= ",'".$period['SCHOOL_PERIOD_ID']."'";
	
			}
			$periods_extra = ' AND SCHOOL_PERIOD_ID IN ('.substr($periods_extra,1).') ';
		}
		echo '</TR></TABLE></CENTER>';

		$periods_count_array = db_request(db_execute("SELECT COUNT(*) FROM SCHOOL_PERIODS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'".$periods_extra));
		$periods_count_array = $periods_count_array[1]['COUNT'];
		if(is_null($periods_count_array)) 
			$periods_count_array = 0;
		//$count_array = db_request(db_execute("SELECT sr.STUDENT_ID,sr.STUDENT_ID AS FULL_NAME,SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).") AS COUNT FROM STUDENT_SCHEDULE_REQUESTS sr,COURSES c WHERE c.COURSE_ID=sr.COURSE_ID AND sr.SCHOOL_YEAR='".get_school_year()."' AND sr.SCHOOL_ID='".get_school_id()."' AND sr.PRIORITY IS NULL GROUP BY sr.STUDENT_ID HAVING SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).")<'".$periods_count_array."'"),$functions);
		if(Preferences('NAME_FORMAT')=='WCM')
			$full_name = "s.LAST_NAME".CCAT."', '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
		else
//			$full_name = "s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')".CCAT."' '".CCAT."s.LAST_NAME";
			$full_name = "s.LAST_NAME".CCAT."' '".CCAT."s.FIRST_NAME".CCAT."' '".CCAT."COALESCE(s.MIDDLE_NAME,' ')";
		$extdra = array('SELECT_ONLY'=>'sr.STUDENT_ID,count(*),'.$full_name." AS FULL_NAME,SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).") AS COUNT",
						'FROM'=>',STUDENT_SCHEDULE_REQUESTS sr,COURSES c',
						'WHERE'=>"AND c.COURSE_ID=sr.COURSE_ID AND sr.SCHOOL_YEAR=ssm.SCHOOL_YEAR AND sr.SCHOOL_ID=ssm.SCHOOL_ID AND sr.STUDENT_ID=ssm.STUDENT_ID AND (sr.PRIORITY IS NULL OR sr.PRIORITY='1')",
						'GROUP'=>"sr.STUDENT_ID,s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME HAVING SUM(".db_case(array('c.MP',"'FY'","1","'SEM'",".5","'QTR'",".25","1")).")<'".$periods_count_array."'",
						'ORDER_BY'=>'s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME'
		);
		$count_array = get_student_list($addition);		
		
		$columns = array('FULL_NAME'=>_('Student Name'),'STUDENT_ID'=>_('ID number'),'COUNT'=>_('Number of Requests'));
		$link['FULL_NAME'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdStudReqProperties.php&useontop=false','variables'=>array('student_id'=>'STUDENT_ID'),'js'=>true);
		Gridoutput($count_array,$columns,_('Student with too few Requests'),_('Students with too few Requests'),$link);
	break;	

	case 'courses_too_many':
		DisplayHeader('<div style="width:800">'._('Courses with too many requests are courses with fewer available seats than requests. If possible, increase the available seats in the existing sections or add another section entirely by clicking the name of the course.').'</div>');
		$subjects_array = db_request(db_execute("SELECT SUBJECT_ID,TITLE FROM COURSE_SUBJECTS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('SUBJECT_ID'));
		$courses_array = db_request(db_execute("SELECT COURSE_ID,TITLE,SHORT_NAME FROM COURSES WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('COURSE_ID'));
		$functions = array('COURSE'=>'_makeCourse','SHORT_NAME'=>'_makeCourse','SUBJECT'=>'_makeSubject');
		$count_array = db_request(db_execute("SELECT c.SUBJECT_ID,c.SUBJECT_ID AS SUBJECT,c.COURSE_ID,c.COURSE_ID AS COURSE,c.COURSE_ID AS SHORT_NAME,(SELECT COUNT(*) FROM COURSE_SECTIONS cp2 WHERE c.COURSE_ID=cp2.COURSE_ID AND cp2.COURSE_SECTION_ID=cp2.PARENT_ID) AS SECTIONS,sum(cp.TOTAL_SEATS) AS TOTAL_SEATS,(SELECT count(*) FROM STUDENT_SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=c.COURSE_ID AND (sr.PRIORITY IS NULL OR sr.PRIORITY='1')) AS REQUESTED_SEATS FROM COURSES c LEFT OUTER JOIN COURSE_SECTIONS cp ON (c.COURSE_ID=cp.COURSE_ID AND cp.COURSE_SECTION_ID=cp.PARENT_ID) WHERE c.SCHOOL_YEAR='".get_school_year()."' AND c.SCHOOL_ID='".get_school_id()."' GROUP BY c.SUBJECT_ID,c.COURSE_ID HAVING (COALESCE(sum(cp.TOTAL_SEATS),0)<(SELECT count(*) FROM STUDENT_SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=c.COURSE_ID))"),$functions);

		$columns = array('COURSE'=>_('Course'),'SHORT_NAME'=>_('Course Number'),'SUBJECT'=>_('Subject'),'SECTIONS'=>_('Sections'),'TOTAL_SEATS'=>_('Available Seats'),'REQUESTED_SEATS'=>_('Requested Seats'));
		$link['COURSE'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdCourseProperties.php&useontop=false','variables'=>array('subject_id'=>'SUBJECT_ID','course_id'=>'COURSE_ID'),'js'=>true);
		Gridoutput($count_array,$columns,_('Course with too many Requests'),_('Courses with too many Requests'),$link);
	break;

	case 'courses_too_few':
		DisplayHeader('<div style="width:800">'._('Courses with too few requests are courses with less than 80% of seats filled. It may be possible to delete a section or move a section to a smaller room.').'</div>');
		$subjects_array = db_request(db_execute("SELECT SUBJECT_ID,TITLE FROM COURSE_SUBJECTS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('SUBJECT_ID'));
		$courses_array = db_request(db_execute("SELECT COURSE_ID,TITLE,SHORT_NAME FROM COURSES WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('COURSE_ID'));
		$functions = array('COURSE'=>'_makeCourse','SHORT_NAME'=>'_makeCourse','SUBJECT'=>'_makeSubject');
		$count_array = db_request(db_execute("SELECT c.SUBJECT_ID,c.SUBJECT_ID AS SUBJECT,c.COURSE_ID,c.COURSE_ID AS COURSE,c.COURSE_ID AS SHORT_NAME,(SELECT COUNT(*) FROM COURSE_SECTIONS cp2 WHERE c.COURSE_ID=cp2.COURSE_ID AND cp2.COURSE_SECTION_ID=cp2.PARENT_ID) AS SECTIONS,sum(cp.TOTAL_SEATS) AS TOTAL_SEATS,(SELECT count(*) FROM STUDENT_SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=c.COURSE_ID AND (sr.PRIORITY IS NULL OR sr.PRIORITY='1')) AS REQUESTED_SEATS FROM COURSES c LEFT OUTER JOIN COURSE_SECTIONS cp ON (c.COURSE_ID=cp.COURSE_ID AND cp.COURSE_SECTION_ID=cp.PARENT_ID) WHERE c.SCHOOL_YEAR='".get_school_year()."' AND c.SCHOOL_ID='".get_school_id()."' GROUP BY c.SUBJECT_ID,c.COURSE_ID HAVING (COALESCE(sum(cp.TOTAL_SEATS),0)>((SELECT count(*) FROM STUDENT_SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=c.COURSE_ID)*.8))"),$functions);

		$columns = array('COURSE'=>_('Course'),'SHORT_NAME'=>_('Course Number'),'SUBJECT'=>_('Subject'),'SECTIONS'=>_('Sections'),'TOTAL_SEATS'=>_('Available Seats'),'REQUESTED_SEATS'=>_('Requested Seats'));
		$link['COURSE'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdCourseProperties.php&useontop=false','variables'=>array('subject_id'=>'SUBJECT_ID','course_id'=>'COURSE_ID'),'js'=>true);
		Gridoutput($count_array,$columns,_('Course with too few Requests'),_('Courses with too few Requests'),$link);
	break;

	case 'courses_unscheduled':
		$subjects_array = db_request(db_execute("SELECT SUBJECT_ID,TITLE FROM COURSE_SUBJECTS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('SUBJECT_ID'));
		$courses_array = db_request(db_execute("SELECT COURSE_ID,TITLE,SHORT_NAME FROM COURSES WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('COURSE_ID'));
		$functions = array('COURSE'=>'_makeCourse','SHORT_NAME'=>'_makeCourse','SUBJECT'=>'_makeSubject','UNSTUDENT_SCHEDULED_REQUESTS'=>'_makeUnscheduled','REQUESTED_SEATS'=>'_makeRequestedSeats');
		$addition = array(
			'SELECT_ONLY'=>'COUNT(*) AS COUNT,sr.COURSE_ID,sr.COURSE_CREDIT_TITLE',
			'FROM'=>',STUDENT_SCHEDULE_REQUESTS sr',
			'WHERE'=>' AND sr.STUDENT_ID=ssm.STUDENT_ID AND sr.SCHOOL_YEAR=ssm.SCHOOL_YEAR AND (sr.PRIORITY IS NULL OR sr.PRIORITY=\'1\') AND NOT EXISTS (SELECT \'\' FROM STUDENT_SCHEDULE s3 WHERE s3.STUDENT_ID=ssm.STUDENT_ID AND s3.COURSE_ID=sr.COURSE_ID AND s3.COURSE_CREDIT_TITLE=sr.COURSE_CREDIT_TITLE)',
			'GROUP'=>' sr.COURSE_ID,sr.COURSE_CREDIT_TITLE',
			'group'=>array('COURSE_ID','COURSE_CREDIT_TITLE')
		);
		$unscheduled_array = get_student_list($addition);
		$addition = array(
			'SELECT_ONLY'=>'COUNT(*) AS COUNT,sr.COURSE_ID,sr.COURSE_CREDIT_TITLE',
			'FROM'=>',STUDENT_SCHEDULE_REQUESTS sr',
			'WHERE'=>' AND sr.STUDENT_ID=ssm.STUDENT_ID AND sr.SCHOOL_YEAR=ssm.SCHOOL_YEAR AND (sr.PRIORITY IS NULL OR sr.PRIORITY=\'1\') ',
			'GROUP'=>' sr.COURSE_ID,sr.COURSE_CREDIT_TITLE',
			'group'=>array('COURSE_ID','COURSE_CREDIT_TITLE')
		);
		$requested_array = get_student_list($addition);

//		(SELECT COUNT(*) FROM STUDENT_SCHEDULE_REQUESTS sr2 WHERE c.COURSE_ID=sr2.COURSE_ID AND NOT EXISTS (SELECT '' FROM STUDENT_SCHEDULE s3 WHERE s3.STUDENT_ID=sr2.STUDENT_ID AND s3.COURSE_ID=sr2.COURSE_ID AND s3.COURSE_CREDIT_TITLE=sr2.COURSE_CREDIT_TITLE))
//		(SELECT count(*) FROM STUDENT_SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=c.COURSE_ID AND sr.PRIORITY IS NULL)
		
		$count_array = db_request(db_execute("SELECT c.SUBJECT_ID,c.SUBJECT_ID AS SUBJECT,c.COURSE_ID,c.COURSE_ID AS COURSE,c.COURSE_ID AS SHORT_NAME,(SELECT COUNT(*) FROM COURSE_SECTIONS cp2 WHERE c.COURSE_ID=cp2.COURSE_ID AND cp2.COURSE_SECTION_ID=cp2.PARENT_ID) AS SECTIONS,NULL AS UNSCHEDULED_REQUESTS,sum(cp.TOTAL_SEATS) AS TOTAL_SEATS,NULL AS REQUESTED_SEATS,cw.COURSE_CREDIT_TITLE 
			FROM COURSES c LEFT OUTER JOIN COURSE_SECTIONS cp ON (c.COURSE_ID=cp.COURSE_ID AND cp.COURSE_SECTION_ID=cp.PARENT_ID),COURSE_CREDITS cw WHERE cw.COURSE_ID=c.COURSE_ID AND c.SCHOOL_YEAR='".get_school_year()."' AND c.SCHOOL_ID='".get_school_id()."' GROUP BY c.SUBJECT_ID,c.COURSE_ID,cw.COURSE_CREDIT_TITLE"),$functions);
		foreach($count_array as $i=>$course)
		{
			if(!$course['UNSCHEDULED_REQUESTS'])
				unset($count_array[$i]);
		}
		$count_array = ReIndexResults($count_array);

		if(!$_REQUEST['GridSortingBy'])
		{
			$_REQUEST['GridSortingBy'] = 'UNSCHEDULED_REQUESTS';
			$_REQUEST['GridOrder'] = '-1';
		}
		$columns = array('COURSE'=>_('Course'),'SHORT_NAME'=>_('Course Number'),'COURSE_CREDIT_TITLE'=>_('Course Weight'),'SUBJECT'=>_('Subject'),'UNSCHEDULED_REQUESTS'=>_('Number of Unfilled Requests'),'SECTIONS'=>_('Sections'),'TOTAL_SEATS'=>_('Available Seats'),'REQUESTED_SEATS'=>_('Requested Seats'));
		if(SystemPreferences('WEIGHTS')!='Y')
			unset($columns['COURSE_CREDIT_TITLE']);
		$link['COURSE'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdCourseProperties.php&useontop=false','variables'=>array('subject_id'=>'SUBJECT_ID','course_id'=>'COURSE_ID'),'js'=>true);
		Gridoutput($count_array,$columns,_('Course with Unfilled Requests'),_('Courses with Unfilled Requests'),$link);
	break;

	case 'sections_overscheduled':
		$subjects_array = db_request(db_execute("SELECT SUBJECT_ID,TITLE FROM COURSE_SUBJECTS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."'"),array(),array('SUBJECT_ID'));
		$functions = array('SUBJECT'=>'_makeSubject','SCHEDULED'=>'_makeScheduled','OVERFILL'=>'_makeOverfill');
		$addition = array(
			'SELECT_ONLY'=>'COUNT(*) AS COUNT,s2.COURSE_SECTION_ID',
			'FROM'=>',STUDENT_SCHEDULE s2',
			'WHERE'=>" AND s2.STUDENT_ID=ssm.STUDENT_ID AND s2.SCHOOL_YEAR=ssm.SCHOOL_YEAR AND (s2.END_DATE IS NULL OR s2.END_DATE<'".get_db_date('postgres')."')",
			'GROUP'=>' s2.COURSE_SECTION_ID',
			'group'=>array('COURSE_SECTION_ID')
		);
		$scheduled_array = get_student_list($addition);
		
		$count_array = db_request(db_execute("SELECT c.SUBJECT_ID,c.SUBJECT_ID AS SUBJECT,c.COURSE_ID,c.TITLE AS COURSE,c.SHORT_NAME,NULL AS SCHEDULED,cp.TOTAL_SEATS,cp.COURSE_CREDIT_TITLE,cp.TITLE AS CP_TITLE,NULL AS OVERFILL,cp.COURSE_SECTION_ID FROM COURSES c,COURSE_SECTIONS cp,COURSE_CREDITS cw WHERE c.COURSE_ID=cp.COURSE_ID AND c.COURSE_ID=cw.COURSE_ID AND cw.COURSE_CREDIT_TITLE=cp.COURSE_CREDIT_TITLE AND c.SCHOOL_YEAR='".get_school_year()."' AND c.SCHOOL_ID='".get_school_id()."'"),$functions);
		foreach($count_array as $i=>$section)
		{
			if(!$section['SCHEDULED'] || $section['SCHEDULED']<=$section['TOTAL_SEATS'])
				unset($count_array[$i]);
		}
		$count_array = ReIndexResults($count_array);

		if(!$_REQUEST['GridSortingBy'])
		{
			$_REQUEST['GridSortingBy'] = 'OVERFILL';
			$_REQUEST['GridOrder'] = '-1';
		}
		$columns = array('SUBJECT'=>_('Subject'),'COURSE'=>_('Course'),'SHORT_NAME'=>_('Course Number'),'COURSE_CREDIT_TITLE'=>_('Course Weight'),'CP_TITLE'=>_('Section'),'SCHEDULED'=>_('Filled Seats'),'TOTAL_SEATS'=>_('Available Seats'),'OVERFILL'=>_('Overfilled By'));
		if(SystemPreferences('WEIGHTS')!='Y')
			unset($columns['COURSE_CREDIT_TITLE']);
		$link['COURSE'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdCourseProperties.php&useontop=false','variables'=>array('subject_id'=>'SUBJECT_ID','course_id'=>'COURSE_ID'),'js'=>true);
		$link['CP_TITLE'] = array('link'=>'modloader.php?servicemod=mod_curriculum/ScdCourseProperties.php&useontop=false','variables'=>array('subject_id'=>'SUBJECT_ID','course_id'=>'COURSE_ID','course_credits'=>'COURSE_CREDIT_TITLE','course_section_id'=>'COURSE_SECTION_ID'),'js'=>true);
		Gridoutput($count_array,$columns,_('Overfilled Section'),_('Overfilled Sections'),$link);
	break;

	case 'overlapping_singletons':
		DisplayHeader('<div style="width:800">'._('Overlapping Singletons are two courses with only one section that both meet on the same period.  Students who request both courses can be scheduled into only one of them.  Click the course to add a section or change the period on which it meets.').'</div>');
		$count_array = db_request(db_execute("SELECT cp.COURSE_ID,cp.COURSE_CREDIT_TITLE,c.SUBJECT_ID,c.TITLE AS COURSE_TITLE,cp.SCHOOL_PERIOD_ID FROM COURSE_SECTIONS cp,COURSES c WHERE cp.SCHOOL_YEAR='".get_school_year()."' AND cp.SCHOOL_ID='".get_school_id()."' AND c.COURSE_ID=cp.COURSE_ID AND (SELECT COUNT(*) FROM COURSE_SECTIONS cp2 WHERE cp.COURSE_ID=cp2.COURSE_ID)=1"),null,array('SCHOOL_PERIOD_ID'));
		// COURSE_CREDIT_TITLE
		$i = 0;
		$array = array();
		foreach($count_array as $school_period_id=>$sections)
		{
			if(count($sections)>1)
			{
				$i++;
				$array[$i]['SCHOOL_PERIOD_ID'] = '<!-- '.get_period($school_period_id,null,'SORT_ORDER').' -->'.get_period($school_period_id);
				foreach($sections as $section)
					$array[$i]['COURSES'] .= ', '.ProgramLink('mod_curriculum/ScdCourseProperties.php',$section['COURSE_TITLE'],'&subject_id='.$section['SUBJECT_ID'].'&course_id='.$section['COURSE_ID'].'&course_credits='.$section['COURSE_CREDIT_TITLE'].'&course_section_id'.$section['COURSE_SECTION_ID'],true);
				$array[$i]['COURSES'] = substr($array[$i]['COURSES'],1);
			}
		}
		$columns = array('COURSES'=>'Singletons','SCHOOL_PERIOD_ID'=>_('Period'));
		$options = array('wrap'=>true);
		$_REQUEST['GridSortingBy'] = 'SCHOOL_PERIOD_ID';
		Gridoutput($array,$columns,_('Overlapping Singleton'),_('Overlapping Singletons'),null,null,$options);
	break;
	
	case 'overlapping_groups':
	break;
	
	case 'period_counts':
		DisplayHeader('<div style="width:800">'._('This report lists the number of seats, courses, and sections in each period.  These numbers should be fairly balanced among each of the periods.  If you have to add or move a section, it might be best to add or move it to a period with few sections.').'</div>');
		$periods_array = db_request(db_execute("SELECT SCHOOL_PERIOD_ID,TITLE,SHORT_NAME FROM SCHOOL_PERIODS WHERE SCHOOL_YEAR='".get_school_year()."' AND SCHOOL_ID='".get_school_id()."' ORDER BY SORT_ORDER,TITLE"));
		
		$seats_array = db_request(db_execute("SELECT cp.SCHOOL_PERIOD_ID,SUM(cp.TOTAL_SEATS) AS TOTAL FROM COURSE_SECTIONS cp GROUP BY cp.SCHOOL_PERIOD_ID"),array(),array('SCHOOL_PERIOD_ID'));
		$courses_array = db_request(db_execute("SELECT cp.SCHOOL_PERIOD_ID,COUNT(DISTINCT cp.COURSE_ID) AS TOTAL FROM COURSE_SECTIONS cp GROUP BY cp.SCHOOL_PERIOD_ID"),array(),array('SCHOOL_PERIOD_ID'));
		$sections_array = db_request(db_execute("SELECT cp.SCHOOL_PERIOD_ID,COUNT(*) AS TOTAL FROM COURSE_SECTIONS cp GROUP BY cp.SCHOOL_PERIOD_ID"),array(),array('SCHOOL_PERIOD_ID'));

		foreach($periods_array as $i=>$period)
		{
			$periods_array[$i]['SEATS'] = $seats_array[$period['SCHOOL_PERIOD_ID']][1]['TOTAL'];
			$periods_array[$i]['COURSES'] = $courses_array[$period['SCHOOL_PERIOD_ID']][1]['TOTAL'];
			$periods_array[$i]['SECTIONS'] = $sections_array[$period['SCHOOL_PERIOD_ID']][1]['TOTAL'];
		}
		
		$columns = array('TITLE'=>_('Period'),'SEATS'=>_('Available Seats'),'COURSES'=>_('Courses Offered'),'SECTIONS'=>_('Number of Sections'));
		Gridoutput($periods_array,$columns,'Period','Periods');
	break;
		
	case 'non_applicable_restrictions':
	break;
}

echo '<BR><BR>';
PopTable('footer');

function _makeStudent($student_id,$column)
{	global $students_array;
	
	return $students_array[$student_id][1]['FULL_NAME'];
}

function _makeCourse($course_id,$column)
{	global $courses_array;

	if($column=='SHORT_NAME')
		return $courses_array[$course_id][1]['SHORT_NAME'];
	else
		return $courses_array[$course_id][1]['TITLE'];
}

function _makeSubject($subject_id,$column)
{	global $subjects_array;

	return $subjects_array[$subject_id][1]['TITLE'];
}

function _makeUnscheduled($value,$column)
{	global $THIS_array,$unscheduled_array;

	return $unscheduled_array[$THIS_array['COURSE_ID']][$THIS_array['COURSE_CREDIT_TITLE']][1]['COUNT'];
}

function _makeRequestedSeats($value,$column)
{	global $THIS_array,$requested_array;

	return $requested_array[$THIS_array['COURSE_ID']][$THIS_array['COURSE_CREDIT_TITLE']][1]['COUNT'];
}

function _makeScheduled($value,$column)
{	global $THIS_array,$scheduled_array;

	return $scheduled_array[$THIS_array['COURSE_SECTION_ID']][1]['COUNT'];
}

function _makeOverfill($value,$column)
{	global $THIS_array,$scheduled_array;

	return $scheduled_array[$THIS_array['COURSE_SECTION_ID']][1]['COUNT'] - $THIS_array['TOTAL_SEATS'];
}

?>