<?php
//--Class StudentMaster--------------------------

include_once "clsbase_StudentMaster.php";

class StudentMaster extends base_StudentMaster{

function RSStudentMasterByPgIdGroupAdY($xKey){
	$this->SetQuery("select admitAcadYear, max(studentYear) as studentYear from StudentMaster 
						where programId='$xKey' and genStatus='Y' group by admitAcadYear");
}

function RSStudentMasterByPgIdAndAdY($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear='$yKey' order by studentCode");
}

function RSStudentMasterByPgIdAndAdYAndStudying($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear='$yKey' and genStatus='Y' 
						and studentStatus='1' and finishDate=0000-00-00 and graduateYear=0 
						order by studentCode");
}

function RSStudentMasterByPgIdAndBetweenAdYOrderStCode($xKey, $yKey, $zKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear between '$yKey' and '$zKey' order by studentCode");
}

function RSStudentMasterByBetweenAdYOrderStCode($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster 
						where admitAcadYear between '$xKey' and '$yKey' order by studentCode");
}

function RSStudentMasterByPgIdAndStYAndAdY($xKey, $yKey, $zKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and studentYear='$yKey' and admitAcadYear='$zKey' order by studentCode");
}

function RSStudentMasterByPgIdAndAdYLimit($xKey, $yKey, $start, $pageSize){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and admitAcadYear='$yKey' 
						order by studentCode, studentName, studentSurname limit $start, $pageSize");
}

function GetNextCodeByPgIdAndAdY($xKey, $yKey){
	$this->SetQuery("select max(studentCode) as num from StudentMaster where programId='$xKey' and admitAcadYear='$yKey'");
	if ($result=$this->GetResult()) {
		return $result['num']+1;
	}
}

function RSStudentMasterByPgIdAndAdYGS($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and admitAcadYear='$yKey' and genStatus='Y'");
}

function RSStudentMasterByGSGroupPgIdAndAdY($xKey){
	$this->SetQuery("select StudentMaster.programId, admitAcadYear from StudentMaster, Program 
						where genStatus='$xKey' and StudentMaster.programId=Program.programId and programStatus='Y' 
						group by StudentMaster.programId, admitAcadYear");
}

function RSStudentMasterByGSGroupPgIdAndAdYLimit($xKey, $start, $pageSize){
	$this->SetQuery("select StudentMaster.programId, admitAcadYear from StudentMaster, Program 
						where genStatus='$xKey' and StudentMaster.programId=Program.programId and programStatus='Y' 
						group by StudentMaster.programId, admitAcadYear limit $start, $pageSize");
}

function RSStudentMasterByPgIdAndAdYOrderStNm($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and admitAcadYear='$yKey' order by studentName, studentSurname");
}

function RSStudentMasterByPgIdAndGeId($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster where programId= '$xKey' and generationId= '$yKey' order by studentId");
}

function RSStudentMasterByPgIdAndAdYAndExpectGD($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear='$yKey' 
						and studentStatus='3' and finishDate=0000-00-00 and graduateYear=0 
						order by studentCode");
}

function RSStudentMasterByPgIdAndAdYAndExpectGDAndGD($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear='$yKey' 
						and ((studentStatus='3'and finishDate=0000-00-00 and graduateYear=0) or studentStatus='4')");
}

function RSStudentMasterByPgIdAndAdYAndGD($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear='$yKey' 
						and studentStatus='4' and finishDate<>0000-00-00 and graduateYear<>0");
}

function RSStudentMasterByPgIdAndAdYAndStYAndStudying($xKey, $yKey, $zKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$xKey' and admitAcadYear='$yKey' and studentYear='$zKey' 
						and studentStatus='1' and finishDate=0000-00-00 and graduateYear=0 
						order by studentCode");
}

function RSStudentMasterByPgIdAndAdYOrderGPATtPt($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and admitAcadYear='$yKey' order by GPA desc, totalPoint desc");
}

function RSStudentMasterGroupGdY(){
	$this->SetQuery("select graduateYear from StudentMaster group by graduateYear");
}

function RSStudentMasterByPgIdAndFinishDateAndGdY($wKey, $xKey, $yKey, $zKey){
	$this->SetQuery("select * from StudentMaster 
						where programId='$wKey' and finishDate>='$xKey' and finishDate<='$yKey' 
						and graduateYear='$zKey'");
}

function GetCountStIdByPgIdAndFinishDateAndGdYAndStSex($wKey, $xKey, $yKey, $zKey, $sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$wKey' and finishDate>='$xKey' and finishDate<='$yKey' 
						and graduateYear='$zKey' and studentSex='$sex'");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function RSStudentMasterByGSAndAcYGroupPgIdAndAdYAndGenNo($xKey){
	$this->SetQuery("select s.programId, admitAcadYear, s.genNo, count(studentId) as studentId 
						from StudentMaster s, Generation g, Program p 
						where genStatus='Y' and s.genNo=genId and acadYear<='$xKey' and s.programId=p.programId 
						and ('$xKey'-admitAcadYear+1) between 1 and studyYear 
						group by s.programId, admitAcadYear, s.genNo");
}

function RSStudentMasterByGSAndAcYAndLvIdGroupPgIdAndAdYAndGenNo($acY, $sLvId, $eLvId){
	$this->SetQuery("select s.programId, admitAcadYear, s.genNo, count(studentId) as studentId 
						from StudentMaster s, Generation g, Program p 
						where genStatus='Y' and s.genNo=genId and acadYear<='$acY' and s.programId=p.programId 
						and p.levelId between '$sLvId' and '$eLvId' and ('$acY'-admitAcadYear+1) between 1 and studyYear 
						group by s.programId, admitAcadYear, s.genNo");
}

function RSStudentMasterByPgId($xKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' order by studentCode");
}

function RSStudentMasterByPgIdOrderByStdNameAndStdStatus($xKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and studentStatus!=6 order by studentName");
}

function RSStudentMasterByPgIdOrderByStdName($xKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' order by studentName");
}

function RSStudentMasterByStCdAndNmAndSm($xKey, $yKey, $zKey){
	$this->SetQuery("select * from StudentMaster 
						where studentCode like '%$xKey%' and studentName like '%$yKey%' and studentSurname like '%$zKey%' 
						order by studentCode");
}

function RSStudentMasterByOfId1AndStudying($xKey){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where officerId1='$xKey' and studentStatus='1' order by studentId");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function RSStudentMasterByOfId1AndStudyingLimit($xKey, $start, $pageSize){
	$this->SetQuery("select * from StudentMaster 
						where officerId1='$xKey' and studentStatus='1' order by studentId limit $start, $pageSize");
}

function RSStudentMasterCountStudByPgIdStudYearNowDate($aKey, $bKey){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where studentCode!='' and programId='$aKey' and studentYear='$bKey' and studentStatus='1' 
						order by studentId");
	$result=$this->GetResult();
	return $result['num'];
}

function RSStudentMasterByPgIdAndGdY($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and graduateYear='$yKey' and studentStatus='4'");
}

function SearchByKeyAndStudying($xKey){
	if ($this->SetQuery("select * from StudentMaster where studentId='$xKey' and studentStatus='1' and genStatus='Y'")){
		return 1;
	}else {
		return 0;
	}
}

function SearchByStCodeAndStudying($xKey){
	if ($this->SetQuery("select * from StudentMaster where studentCode='$xKey' and studentStatus='1' and genStatus='Y'")){
		return 1;
	}else {
		return 0;
	}
}

function SearchByStCodeAndStudyingAndTransferSt($xKey){
	if ($this->SetQuery("select * from StudentMaster where studentCode='$xKey' and studentStatus='1' and entryTypeId2='2'")){
		return 1;
	}else {
		return 0;
	}
}

function SearchByStCode($xKey){
	if ($this->SetQuery("select * from StudentMaster where studentCode='$xKey' and genStatus='Y'")){
		return 1;
	}else {
		return 0;
	}
}

function RSStudentMasterGroupAdY(){
	$this->SetQuery("select admitAcadYear from StudentMaster group by admitAcadYear");
}

function RSStudentMasterByPgIdAndAdYGroupGenNo($xKey, $yKey){
	$this->SetQuery("select genNo from StudentMaster where programId='$xKey' and admitAcadYear='$yKey' group by genNo");
}

function CheckCredit($stId, &$msg){
	include_once "clsStudentMaster.php";
	include_once "clsProgramStructure.php";
	include_once "clsEnrollItem.php";
	
	$obj1 = new StudentMaster();
	$obj2 = new ProgramStructure();
	$obj3 = new ProgramStructure();
	$obj4 = new EnrollItem();
	
	$obj1->SearchByKey($stId);
	$obj1->GetRecord();
	
	$flag = 1;
	// Find conditionId1
	$obj2->RSProgramStructureByPgIdAndCdId2IsH($obj1->programId);
	while($obj2->GetRecord()) {
		// Find conditionId2
		$obj3->RSProgramStructureByPgIdAndCd1($obj1->programId, $obj2->conditionId1);
		while($obj3->GetRecord()) {
			// Check credit of conditionId2
			$sumCrAtCdId2 = $obj4->GetSumCrAtByStIdAndCdIdAndCdId2($stId, $obj2->conditionId1, $obj3->conditionId2);
			if($sumCrAtCdId2 >= $obj3->creditTotal)
				$msg[$obj3->conditionId2] = intval($sumCrAtCdId2).':PASS';
			else {
				$msg[$obj3->conditionId2] = intval($sumCrAtCdId2).':FAIL';
				$flag = 0;
			}
		}
		
		// Check credit of conditionId1
		$sumCrAtCdId = $obj4->GetSumCrAtByStIdAndCdId($stId, $obj2->conditionId1);
		if($sumCrAtCdId >= $obj2->creditTotal)
			$msg[$obj2->conditionId1] = intval($sumCrAtCdId).':PASS';
		else {
			$msg[$obj2->conditionId1] = intval($sumCrAtCdId).':FAIL';
			$flag = 0;
		}
	}
	
	return $flag;
}

function SearchByStCodeAndStudyingReStatus($StCode){
	$this->SetQuery("select * from StudentMaster 
						where studentCode='$StCode' and (studentStatus='2' or studentStatus='7' or studentStatus='8' or studentStatus='9')");	
}

function SearchByStCodeStNStSn($StC,$StN,$StSn){
	$this->SetQuery("select * from StudentMaster where studentCode='$StC' or studentName='$StN' or studentSurname='$StSn' ");
}

function RSStudentMasterByPgIdAndStY2($xKey, $yKey){
	$this->SetQuery("select * from StudentMaster where programId='$xKey' and studentYear='$yKey' order by studentCode");
}

// ris201
function GetCountStIdByPgIdAndStYAndEtIdAndStSexAndStudying($pgId, $stY, $etId, $sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$pgId' and studentYear='$stY' and entryTypeId='$etId' and studentSex='$sex' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByPgIdAndEtIdAndStSexAndStudying($pgId, $etId, $sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$pgId' and entryTypeId='$etId' and studentSex='$sex' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByPgIdAndEtIdAndStudying($pgId, $etId){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$pgId' and entryTypeId='$etId' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByStYAndEtIdAndStSexAndStudying($stY, $etId, $sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where studentYear='$stY' and entryTypeId='$etId' and studentSex='$sex' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByEtIdAndStSexAndStudying($etId, $sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where entryTypeId='$etId' and studentSex='$sex' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}
//-----------------------------------------------

// ris204
function RSStudentMasterByStudyingGroupPgIdAndGenNoAndStY(){
	$this->SetQuery("select s.programId, s.genNo, s.studentYear, count(s.studentId) as studentId 
						from StudentMaster s, Generation g 
						where s.genStatus='Y' and s.finishDate=0000-00-00 and s.graduateYear=0 and s.genNo=g.genId 
						group by s.programId, s.genNo, s.studentYear order by s.programId, g.genNo, s.studentYear");
}

function GetCountStIdByStudying(){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByPgIdAndStYAndGenNoAndStSexAndStudying($pgId, $stY, $genNo, $sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$pgId' and studentYear='$stY' and genNo='$genNo' and studentSex='$sex' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByStSexAndStudying($sex){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where studentSex='$sex' and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByPgIdAndStYAndGenNoAndEtIdAndStudying($pgId, $stY, $genNo, $etId){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$pgId' and studentYear='$stY' and genNo='$genNo' and entryTypeId='$etId' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByEtIdAndStudying($etId){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where entryTypeId='$etId' and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByPgIdAndStYAndGenNoAndPpIdAndStudying($pgId, $stY, $genNo, $ppId){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where programId='$pgId' and studentYear='$stY' and genNo='$genNo' and preAdmitPositionId='$ppId' 
						and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByPpIdAndStudying($ppId){
	$this->SetQuery("select count(studentId) as num from StudentMaster 
						where preAdmitPositionId='$ppId' and genStatus='Y' and finishDate=0000-00-00 and graduateYear=0");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function GetCountStIdByAcYAndStudying($acY){
	$this->SetQuery("select count(studentId) as num from (
						select studentId from StudentMaster 
							where admitAcadYear<='$acY' and genStatus='Y' and studentStatus=1 
						union 
						select studentId from StudentStatus 
							where acadYear='$acY' and studentStatus=1 
							group by studentId
					) aa");
	if ($result=$this->GetResult()) {
		return $result['num'];
	}
}

function RSStudentMasterByAcYAndStudying($acY){
	$this->SetQuery("select studentId from (
						select m.studentId, m.studentCode from StudentMaster m 
							where m.admitAcadYear<='$acY' and m.genStatus='Y' and m.studentStatus=1 
						union 
						select m.studentId, m.studentCode from StudentStatus s, StudentMaster m 
							where s.acadYear='$acY' and s.studentStatus=1 and s.studentId=m.studentId 
							group by s.studentId
					) aa 
					order by studentCode");
}
//-----------------------------------------------

// ***** Start MIS *****
function RSStudentMasterByStStatusAndAcYGroupPgIdAndAdYAndStYAndEtIdAndSex($stStatus, $acY){
	$this->SetQuery("select m.programId, m.admitAcadYear, m.studentYear, m.entryTypeId, m.studentSex, count(m.studentId) as studentId 
						from StudentMaster m, StudentStatus s 
						where m.studentStatus in ($stStatus) and m.genStatus='Y' 
						and m.studentId=s.studentId and s.studentStatus in ($stStatus) and s.acadYear='$acY' 
						group by m.programId, m.admitAcadYear, m.studentYear, m.entryTypeId, m.studentSex");
}

function RSStudentMasterByNotInStStatusAndAcYAndRepeatClassGroupPgIdAndAdYAndStYAndEtIdAndSex($stStatus, $acY){
	$this->SetQuery("select m.programId, m.admitAcadYear, m.studentYear, m.entryTypeId, m.studentSex, count(m.studentId) as studentId 
						from StudentMaster m, StudentStatus s 
						where m.studentStatus not in ($stStatus) and m.genStatus='Y' 
						and m.studentId=s.studentId and s.acadYear='$acY' and s.passStatus=4 
						group by m.programId, m.admitAcadYear, m.studentYear, m.entryTypeId, m.studentSex");
}

function RSStudentMasterByNotInStStatusGroupPgIdAndAdYAndStYAndEtIdAndSex($stStatus){
	$this->SetQuery("select programId, admitAcadYear, studentYear, entryTypeId, studentSex, count(studentId) as studentId 
						from StudentMaster 
						where studentStatus not in ($stStatus) and genStatus='Y' 
						group by programId, admitAcadYear, studentYear, entryTypeId, studentSex");
}

function RSStudentMasterByNotInStStatusGroupPgIdAndAdYAndEtIdAndSex($stStatus){
	$this->SetQuery("select programId, admitAcadYear, entryTypeId, studentSex, count(studentId) as studentId 
						from StudentMaster 
						where studentStatus not in ($stStatus) and genStatus='Y' 
						group by programId, admitAcadYear, entryTypeId, studentSex");
}

function RSStudentMasterByAdYAndNotInStStatusGroupPgIdAndAdYAndEtIdAndSex($adY, $stStatus){
	$this->SetQuery("select programId, admitAcadYear, entryTypeId, studentSex, count(studentId) as studentId 
						from StudentMaster 
						where admitAcadYear='$adY' and studentStatus not in ($stStatus) and genStatus='Y' 
						group by programId, admitAcadYear, entryTypeId, studentSex");
}

function RSStudentMasterByGdYAndGDGroupPgIdAndAdYAndEtIdAndSex($gdY){
	$this->SetQuery("select programId, admitAcadYear, entryTypeId, studentSex, count(studentId) as studentId 
						from StudentMaster 
						where graduateYear='$gdY' and studentStatus='4' and finishDate<>0000-00-00 
						group by programId, admitAcadYear, entryTypeId, studentSex");
}
// ***** End MIS *****

} //--End class StudentMaster--
?>