<?php
	function recordSetToJson($mysql_result,$jsonencode=false,$marker="") {
		$return_arr = array();
		while($datarow = $mysql_result->fetch(PDO::FETCH_ASSOC)){
			$return_arr[] = $datarow;
		}   		
		if (!$jsonencode)	
			return $return_arr;
		else{
			if ($marker != "")
				return json_encode(array($marker =>$return_arr));			
			else
				return json_encode($return_arr);
		}
	}
	//
	require('../connect.php');
	$pdo = db_connect();
	//
    $opCode = $_REQUEST['opCode'];
	$matNo = $_REQUEST['RegNumber'];
    $pin = $_REQUEST['PIN'];
	$session = $_REQUEST['Session'];
	$semester = $_REQUEST['Semester']." Semester";
	//$session = "2017/2018";
	//$semester = "First Semester";

    //
	$isApproved= "True";
	$paymentType = "School Fees";
	$Query = "";
	//
	if ($opCode == "GET_ORCHIDS_BIODATA"){
		$Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.mode, a.gsm FROM realdata a, remitaorder b WHERE 				a.matNo=b.regNo AND b.transApproved = ? AND b.paymentType = ? and b.pay_session= ? and a.matNo = ? ";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$isApproved,$paymentType,$session,$matNo]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Biodata");
		//
	}else if ($opCode == "GET_A_BIODATA"){
		$Query = "SELECT remitaRRR,a.matNo, a.names, a.sex, a.course, a.levelID, a.mode, a.gsm, a.email,a.usernamea,a.passworda 		FROM realdata a, remitaorder b WHERE a.matNo =b.regNo AND b.transApproved = ? AND b.paymentType = ? and b.pay_session= 			? and a.matNo = ? and a.passworda= ? ";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$isApproved,$paymentType,$session,$matNo,$pin]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Biodata");
		//
	
	}else if ($opCode == "GET_ALL_BIODATA"){
		$Query = "SELECT remitaRRR,a.matNo, a.names, a.sex, a.course, a.levelID, a.mode, a.gsm, a.email,a.usernamea,a.passworda 		FROM realdata a, remitaorder b WHERE a.matNo =b.regNo AND b.transApproved= ? AND b.paymentType= ? and b.pay_session= ? ";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$isApproved,$paymentType,$session]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Biodata");
    }else if ($opCode == "GET_ALL_BIODATAS"){
        $Query = "SELECT a.matNo as remitaRRR, a.matNo, a.names, a.sex, a.course, a.levelID, a.mode, a.gsm, a.email,a.usernamea,a.passworda 		FROM realdata a where matNo in (select regNo from remitaorder  WHERE transApproved= ? AND paymentType= ? and pay_session= ? )";
        $stmt = $pdo->prepare($Query);
        $stmt->execute([$isApproved,$paymentType,$session]);
        if ($stmt->rowCount() == 0)
            echo "NOT_FOUND";
        else
            echo recordSetToJson($stmt,true,"Biodata");
		//
	}else if ($opCode == "GET_A_REGISTRATION"){
		$Query = "SELECT matNo,courseCode FROM coursereg WHERE matNo= ? and asession= ? and semester= ? ";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$matNo,$session,$semester]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Registration");
		//
	}else if ($opCode == "GET_ALL_REGISTRATION"){
		$Query = "SELECT matNo,courseCode FROM coursereg WHERE asession= ? and semester= ?  order by matNo";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$session,$semester]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Registration");
		//
	}else if ($opCode == "SUBMIT_REGISTRATION"){
		$SelSubjects = $_REQUEST['Subjects'];
		$Query = "SELECT matNo,course,levelID FROM realdata WHERE matNo= ? ";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$matNo]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else{
			// fetch fields from realdata
			$rows = $stmt->fetch();
			$dept = $rows['course'];
			$levelID = $rows['levelID'];
			//
		$Query = "SELECT * FROM coursepool WHERE dept=? and levelID<=? and semester=? and instr('$SelSubjects',courseCode)>0";
			$stmt = $pdo->prepare($Query);
			$stmt->execute([$dept,$levelID,$semester]);
			//
			$hdl = $pdo->query("Delete from coursereg where matNo='$matNo' and asession='$session' and semester='$semester'");
			//
			$Query = "INSERT INTO coursereg VALUES (NULL,?,?,?,?,?,?,?)";
			$hdl = $pdo->prepare($Query);
			while ($rows = $stmt->fetch(PDO::FETCH_ASSOC)){
				$subcode= $rows['courseCode'];
				$subname = $rows['courseTitle'];
				$subunit = $rows['courseUnit'];
				$hdl->execute([$matNo,$subcode,$subname,$subunit,$semester,$session,$levelID]);
			}
		}
	}
	$pdo=null;
?>