<?php
$button = new Button();

DisplayHeader(".:".ProgramTitle().":.");
//die('dddddd');
if($_REQUEST['subject_id'])
{
//print_r($_REQUEST['subject_id']);die('dfsdsg');
	if($_REQUEST['course_id'])
	{
		$header2 = "modloader.php?servicemod=$_REQUEST[servicemod]&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&selected_menu=$_REQUEST[selected_menu]&link_menu=$_REQUEST[link_menu]&useontop=".$_REQUEST['useontop'];
		$courses_link = "modloader.php?servicemod=mod_curriculum/ScdCourseProperties.php&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&useontop=".$_REQUEST['useontop'];
		$location = 'courses';
		$location_title = _('Course');
		if(isset($_REQUEST['course_credits']))
		{
        
      
			$header2 .= "&course_credits=$_REQUEST[course_credits]";
			$courses_link .= "&course_credits=$_REQUEST[course_credits]";
			$location = 'course_credits';
			if(SystemPreferences('WEIGHTS')=='Y')
			{
				$location_title = _('Course Weight ');
			}
			if($_REQUEST['course_section_id'])
			{
				$header2 .= "&course_section_id=$_REQUEST[course_section_id]";
				$courses_link .= "&course_section_id=$_REQUEST[course_section_id]";
				$location = 'course_sections';
				$location_title = _('Course Section ');
			}
		}
    
    
   // die('dnfskdnkdn');
		if(!$_REQUEST['CREATEPDF'])
		{
			if($_REQUEST['students'] && !$_REQUEST['unscheduled'])
			{
				if(AllowUse('mod_curriculum/ScdCourseProperties.php'))
					$header .= $button->createText(sprintf(_('View or Edit this %s'),$location_title),$courses_link);
				if($location == 'courses')
				{
					$location_title = _(' Course');
				}
				$header .= $button->createButton(sprintf(_(' Display List of Students in this %s'),$location_title),$header2."&students=$location&servicefunction=$location");
				if(SystemPreferences('USE_REQUESTS')=='Y')
				{
					if($location == 'courses')
					{
						$location_title = _('Course');
					}
					$header .= $button->createText(sprintf(_('Display  List of Unfilled Requests in this %s'),$location_title),$header2."&unscheduled=true&students=$location&servicefunction=$location");
				}
			}
			elseif($_REQUEST['students'] && $_REQUEST['unscheduled'])
			{
				if(AllowUse('mod_curriculum/ScdCourseProperties.php'))
					$header .= $button->createText(sprintf(_('View or Edit this %s'),$location_title),$courses_link);
				$header .= $button->createText(sprintf(_('Display List of Students in this %s'),$location_title),$header2."&students=$location&servicefunction=$location");
				if(SystemPreferences('USE_REQUESTS')=='Y')
					$header .= $button->createButton(sprintf(_('Display List of Unfilled Requests in this %s'),$location_title),$header2."&unscheduled=true&students=$location&servicefunction=$location");
			}
			else
			{
				if(AllowUse('mod_curriculum/ScdCourseProperties.php'))
				{
					if($location == 'courses')
					{
						$location_title = _('Course');
						$header .= $button->createText(sprintf(_('View or Edit this %s'),$location_title),$courses_link);
					}
					elseif($location == 'course_credits')
					{
						$location_title = _('Course Weight');
						$header .= $button->createText(sprintf(_('View or Edit this %s'),$location_title),$courses_link);
						$location_title = _('Course Weight ');
					}
					else
						$header .= $button->createText(sprintf(_('View or Edit this %s'),$location_title),$courses_link);
				}
					if($location == 'courses')
					{
						$location_title = _(' Course');
						$header .= $button->createText(sprintf(_(' Display List of Students in this %s'),$location_title),$header2."&students=$location&servicefunction=$location");
					}
					else
						$header .= $button->createText(sprintf(_(' Display List of Students in this %s'),$location_title),$header2."&students=$location&servicefunction=$location");

					if(SystemPreferences('USE_REQUESTS')=='Y')
					if($location == 'courses')
					{
						$location_title = _('Course');
						$header .= $button->createText(sprintf(_('Display  List of Unfilled Requests in this %s'),$location_title),$header2."&unscheduled=true&students=$location&servicefunction=$location");
					}
					else
						$header .= $button->createText(sprintf(_(' Display List of Unfilled Requests in this %s'),$location_title),$header2."&unscheduled=true&students=$location&servicefunction=$location");
			}
			DisplayHeader(null,TabsHeader($header),'','white');
		}	
	}
}


//die('dfndbjdbj');
$Gridoutput_options = array('save'=>false,'search'=>false,'print'=>false);

echo '<TABLE><TR>';

// дисциплины
echo '<TD valign=top>';
//$QI = db_execute("SELECT s.SUBJECT_ID,s.TITLE FROM COURSE_SUBJECTS s WHERE s.SCHOOL_YEAR='".get_school_year()."' AND s.SCHOOL_ID='".get_school_id()."' ORDER BY s.TITLE");
//echo ($QI);die;


//$array = db_request($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));
/*query edit on 15-02-2021*/

$QI = db_execute("SELECT SUBJECT_ID,school_id, TITLE,CATEGORY_VISIBLE FROM COURSE_SUBJECTS WHERE (SCHOOL_ID='".get_school_id()."' AND SCHOOL_YEAR='".get_school_year()."') OR
     (CATEGORY_VISIBLE='Y')  ORDER BY TITLE");
$array = db_request($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));

//print_r($sql);
	//$QI = db_execute($sql);
	//$subjects_array = db_request($QI);

/*
echo "<pre>";
print_r($array);die('sdvsdgsfdg');
*/

//die('dmnbfjdsbgjksdbgj');
if(count($array) && $_REQUEST['subject_id'])
{
	foreach($array as $key=>$value)
	{
		if($value['SUBJECT_ID']==$_REQUEST['subject_id'])
			$array[$key]['row_color'] = Preferences('HIGHLIGHT');
	}
}
$link['TITLE']['link'] = "modloader.php?servicemod=$_REQUEST[servicemod]&servicefunction=courses&selected_menu=$_REQUEST[selected_menu]&link_menu=$_REQUEST[link_menu]&useontop=".$_REQUEST['useontop'];
$link['TITLE']['variables'] = array('subject_id'=>'SUBJECT_ID');
Gridoutput($array,array('TITLE'=>_('Subject Name')),_('Subjects'),_('Amount of Subjects'),$link,array(),$Gridoutput_options);
//echo"<pre>";
//print_r($array);die('sdfsgsags');
echo '</TD>';	
//die('dgdfsgtrr');
if(SystemPreferences('WEIGHTS')!=='Y' && $_REQUEST['servicefunction']=='course_credits')
{
	$weight_array = db_request(db_execute("SELECT COURSE_CREDIT_TITLE FROM COURSE_CREDITS WHERE COURSE_ID='".$_REQUEST['course_id']."'"));


	$_REQUEST['course_credits'] = $weight_array[1]['COURSE_CREDIT_TITLE'];
	$_REQUEST['servicefunction'] = 'course_sections';
}
//die;
//print_r($weight_array);die('dfgsyr');
// предметы
if($_REQUEST['servicefunction']=='courses' || ($_REQUEST['subject_id']) || $_REQUEST['students']=='courses')
{
	
//print_r($_REQUEST['subject_id']);die;
echo '<TD valign=top>';
/*query edit on 15-02-2021*/
 $QI = db_execute("SELECT c.COURSE_ID,c.TITLE,sum(cp.TOTAL_SEATS) as TOTAL_SEATS,sum(cp.FILLED_SEATS) as FILLED_SEATS,NULL AS OPEN_SEATS,(SELECT count(*) FROM STUDENT_SCHEDULE_REQUESTS
   
   sr WHERE sr.COURSE_ID=c.COURSE_ID AND sr.PRIORITY IS NULL) AS COUNT_REQUESTS FROM COURSES c LEFT OUTER JOIN COURSE_SECTIONS cp ON (c.COURSE_ID=cp.COURSE_ID) 
   
   WHERE c.SUBJECT_ID='$_REQUEST[subject_id]' AND c.SCHOOL_YEAR='".get_school_year()."'  GROUP BY c.COURSE_ID,c.TITLE  ORDER BY c.TITLE");

//$QI=db_execute("SELECT * FROM STUDENT_SCHEDULE where course_id='14646'");

$array = db_request($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));

echo "<pre>";
//print_r($array);die('fsdgfsdh');

	if(count($array) && $_REQUEST['course_id'])
	{
		foreach($array as $key=>$value)
		{
			if($value['COURSE_ID']==$_REQUEST['course_id'])
				$array[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$columns = array('TITLE'=>_('Course Name'),
			      'COUNT_REQUESTS'=>_('Course Requests'),
			      'OPEN_SEATS'=>_('Amount of Vacant Seats'),
			      'TOTAL_SEATS'=>_('Total Amount of Seats'));
	if(SystemPreferences('USE_REQUESTS')!='Y')
	unset($columns['COUNT_REQUESTS']);
	$link['TITLE']['link'] = "modloader.php?servicemod=$_REQUEST[servicemod]&servicefunction=course_credits&subject_id=$_REQUEST[subject_id]&selected_menu=$_REQUEST[selected_menu]&link_menu=$_REQUEST[link_menu]&useontop=".$_REQUEST['useontop'];
	$link['TITLE']['variables'] = array('course_id'=>'COURSE_ID');
	Gridoutput($array,$columns,_('Courses'),_('Amount of Courses'),$link,array(),$Gridoutput_options);


//print_r($array);die;
	echo '</TD>';
}

// кредиты 
if(($_REQUEST['servicefunction']=='course_credits' || $_REQUEST['course_id']) && SystemPreferences('WEIGHTS')=='Y')
{
	echo '<TD valign=top>';

/*query edit on 15-02-2021*/
	$QI = db_execute("SELECT cp.COURSE_ID,cp.COURSE_CREDIT_TITLE,sum(cp.TOTAL_SEATS) as TOTAL_SEATS,sum(cp.FILLED_SEATS) as FILLED_SEATS,NULL AS OPEN_SEATS,(SELECT count(*)
    FROM STUDENT_SCHEDULE_REQUESTS sr WHERE sr.COURSE_ID=cp.COURSE_ID AND sr.COURSE_CREDIT_TITLE=cp.COURSE_CREDIT_TITLE AND sr.PRIORITY IS NULL)
    AS COUNT_REQUESTS FROM COURSE_SECTIONS 
    cp WHERE cp.COURSE_ID='".$_REQUEST['course_id']."' AND cp.SCHOOL_YEAR='".get_school_year()."'
    GROUP BY cp.COURSE_ID,cp.COURSE_CREDIT_TITLE ORDER BY cp.COURSE_CREDIT_TITLE");
	$array = db_request($QI,array('OPEN_SEATS'=>'_calcOpenSeats'));

//echo "<pre>";
//print_r($array);die('tyrtyrt');




	if(count($array) && isset($_REQUEST['course_credits']))
	{
		foreach($array as $key=>$value)
		{
			if($value['COURSE_CREDIT_TITLE']==$_REQUEST['course_credits'])
				$array[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$link = array();

	$columns = array('COURSE_CREDIT_TITLE'=>_('Course Weight'),
			      'COUNT_REQUESTS'=>_('Course Requests'),
			      'OPEN_SEATS'=>_('Amount of Vacant Seats'),
			      'TOTAL_SEATS'=>_('Total Amount of Seats'));
                  
	if(SystemPreferences('USE_REQUESTS')!='Y')
		unset($columns['COUNT_REQUESTS']);
			      
	$link['COURSE_CREDIT_TITLE']['link'] = "modloader.php?servicemod=$_REQUEST[servicemod]&servicefunction=course_sections&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&selected_menu=$_REQUEST[selected_menu]&link_menu=$_REQUEST[link_menu]&useontop=".$_REQUEST['useontop'];
	$link['COURSE_CREDIT_TITLE']['variables'] = array('course_credits'=>'COURSE_CREDIT_TITLE');
	Gridoutput($array,$columns,_('Course Weights'),_('Amount of Course Weights'),$link,array(),$Gridoutput_options);
	echo '</TD>';
}

// секции предмета
if($_REQUEST['servicefunction']=='course_sections' || $_REQUEST['students']=='course_sections' || isset($_REQUEST['course_credits']))
{
/*query edit on 15-02-2021*/
	echo '<TD valign=top>';
	$QI = db_execute("SELECT cp.COURSE_ID,cp.COURSE_CREDIT_TITLE,cp.COURSE_SECTION_ID,cp.WEEK,cp.TITLE,sum(cp.TOTAL_SEATS) as TOTAL_SEATS,sum(cp.FILLED_SEATS) as 
    FILLED_SEATS,NULL AS OPEN_SEATS,cp.ROOM FROM COURSE_SECTIONS cp WHERE cp.COURSE_ID='".$_REQUEST['course_id']."' AND cp.COURSE_CREDIT_TITLE='".$_REQUEST['course_credits']."'
    AND cp.SCHOOL_YEAR='".get_school_year()."'  GROUP BY cp.COURSE_ID,cp.COURSE_CREDIT_TITLE,cp.COURSE_SECTION_ID,cp.TITLE,cp.ROOM,cp.WEEK 
    ORDER BY cp.TITLE");
	$array = db_request($QI,array('OPEN_SEATS'=>'_calcOpenSeats','WEEK'=>'get_week','TITLE'=>'get_localized_days'));

	if(count($array) && $_REQUEST['course_section_id'])
	{
		foreach($array as $key=>$value)
		{
			if($value['COURSE_SECTION_ID']==$_REQUEST['course_section_id'])
				$array[$key]['row_color'] = Preferences('HIGHLIGHT');
		}
	}
	$link = array();
	$link['TITLE']['link'] = "modloader.php?servicemod=$_REQUEST[servicemod]&servicefunction=students&students=course_sections&subject_id=$_REQUEST[subject_id]&course_id=$_REQUEST[course_id]&course_credits=$_REQUEST[course_credits]&selected_menu=$_REQUEST[selected_menu]&link_menu=$_REQUEST[link_menu]&useontop=".$_REQUEST['useontop'];
	$link['TITLE']['variables'] = array('course_section_id'=>'COURSE_SECTION_ID');
	$columns = array('TITLE'=>_('School Period - Days - Course Name - Teacher Name'),
			      'WEEK'=>_('Week'),
			      'ROOM'=>_('Class-room'),
			      'OPEN_SEATS'=>_('Vacant Seats'),
			      'TOTAL_SEATS'=>_('Total Amount of Seats'));
	if(SystemPreferences('ODD_EVEN_WEEKS','system') != 'Y')
		unset($columns['WEEK']);
		
	Gridoutput($array,$columns,_('Course Sections'),_('Amount of Course Sections'),$link,array(),$Gridoutput_options);
	echo '</TD>';
}

// список учащихся
if($_REQUEST['students'])
{
	echo '<TD valign=top>';
	if($_REQUEST['unscheduled']=='true')
	{
		$sql = "SELECT s.LAST_NAME".CCAT."', '".CCAT."s.FIRST_NAME AS FULL_NAME,s.STUDENT_ID,s.CFIELD_50004,ssm.CLASSLEVEL_ID 
				FROM STUDENT_SCHEDULE_REQUESTS sr,STUDENTS s,STUDENT_REGISTRATION ssm
				WHERE (('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)) AND sr.PRIORITY IS NULL AND s.STUDENT_ID=sr.STUDENT_ID AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SCHOOL_YEAR='".get_school_year()."' AND ssm.SCHOOL_ID='".get_school_id()."' ";
		if(isset($_REQUEST['course_credits']) && $_REQUEST['course_id'])
			$sql .= "AND sr.COURSE_ID='$_REQUEST[course_id]' AND sr.COURSE_CREDIT_TITLE='$_REQUEST[course_credits]' ";
		elseif($_REQUEST['course_id'])
			$sql .= "AND sr.COURSE_ID='$_REQUEST[course_id]' ";
		$sql .= "AND NOT EXISTS (SELECT '' FROM STUDENT_SCHEDULE ss WHERE ss.COURSE_ID=sr.COURSE_ID AND ss.COURSE_CREDIT_TITLE=sr.COURSE_CREDIT_TITLE AND ss.STUDENT_ID=sr.STUDENT_ID AND ('".get_db_date()."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL))";
	}
	else
	{
		$sql = "SELECT s.LAST_NAME".CCAT."', '".CCAT."s.FIRST_NAME AS FULL_NAME,s.STUDENT_ID,s.CFIELD_50004,ssm.CLASSLEVEL_ID
				FROM STUDENT_SCHEDULE ss,STUDENTS s,STUDENT_REGISTRATION ssm 
				WHERE ('".get_db_date()."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL) AND (('".get_db_date()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)) AND s.STUDENT_ID=ss.STUDENT_ID AND s.STUDENT_ID=ssm.STUDENT_ID AND ssm.SCHOOL_YEAR='".get_school_year()."' AND ssm.SCHOOL_ID='".get_school_id()."' ";
		if($_REQUEST['course_section_id'])
			$sql .= "AND ss.COURSE_SECTION_ID='$_REQUEST[course_section_id]'";
		elseif(isset($_REQUEST['course_credits']) && $_REQUEST['course_id'])
			$sql .= "AND ss.COURSE_ID='$_REQUEST[course_id]' AND ss.COURSE_CREDIT_TITLE='$_REQUEST[course_credits]'";
		elseif($_REQUEST['course_id'])
			$sql .= "AND ss.COURSE_ID='$_REQUEST[course_id]'";
	}
	$sql .= ' ORDER BY s.LAST_NAME,s.FIRST_NAME';
	$array = db_request(db_execute($sql),array('CFIELD_50004'=>'ProperDate','CLASSLEVEL_ID'=>'get_classlevel'));
	
	$link = array();
	$link['FULL_NAME']['link'] = "modloader.php?servicemod=mod_curriculum/Curriculum.php&selected_menu=$_REQUEST[selected_menu]&link_menu=$_REQUEST[link_menu]&useontop=false";
	$link['FULL_NAME']['variables'] = array('student_id'=>'STUDENT_ID');
	$link['FULL_NAME']['js'] = true;
	Gridoutput($array,array('FULL_NAME'=>_('Student Name'),
			      'CLASSLEVEL_ID'=>_('Class Level'),
			      'CFIELD_50004'=>_('Date of Birth')),_('Students'),_('Amount of Students'),$link,array(),$Gridoutput_options);
	echo '</TD>';
}

echo '</TR></TABLE>';

function _calcOpenSeats($null)
{	global $THIS_array;
	
	return $THIS_array['TOTAL_SEATS'] - $THIS_array['FILLED_SEATS'];
}
?>