<?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'];
	$transID = $_REQUEST['TransID'];
	//
    $pin = $_REQUEST['PIN'];
	$session = $_REQUEST['Session'];
	$semester = $_REQUEST['Semester']." Semester";
	//$session = "2017/2018";
	//$semester = "First Semester";

    //
	$isApproved= "True";
	$paymentType = "School Fees";
	$OtherPaymentType = "'School Fees', 'Certificate Levy','New Transcript Request','Reprint Transcript','Re-Notification of Result'";
	$Query = "";
	//
	if ($opCode == "GET_ORCHIDS_BIODATA"){
		$Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.mode, a.gsm FROM ace_realdata a, ace_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");
		//
	}elseif ($opCode == "GET_A_PAYMENT_TRANSACTION"){
		$Query = "SELECT b.orderID,b.remitaRRR,a.idNo, a.names, a.phone, a.email,b.paymentType, b.transDate FROM otherpayment a, ace_remitaorder b WHERE a.idNo=b.regNo AND b.transApproved = ? AND b.paymentType IN ($OtherPaymentType) and b.remitaRRR = ? ";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$isApproved,$transID]);
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Biodata");
		//
	}else if ($opCode == "GET_ALL_PAYMENT_TRANSACTIONS"){
		$Query = "SELECT b.orderID,b.remitaRRR,a.idNo, a.names, a.phone, a.email,b.paymentType,b.transDate FROM otherpayment a, ace_remitaorder b WHERE a.idNo=b.regNo AND b.transApproved = ? AND b.paymentType IN ($OtherPaymentType)";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$isApproved]);
		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 ace_realdata a, ace_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 ace_realdata a, ace_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 ace_realdata a where matNo in (select regNo from ace_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 ace_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 ace_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 ace_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 ace_coursereg where matNo='$matNo' and asession='$session' and semester='$semester'");
			//
			$Query = "INSERT INTO ace_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;
?>