<?php
	//
	require('../PHPMailer/src/PHPMailer.php');
	require('../PHPMailer/src/SMTP.php');
	require('../PHPMailer/src/Exception.php');

	$mail = new PHPMailer\PHPMailer\PHPMailer();

	function writeToFile($text,$mode='a'){
		$filename = 'test.txt';
		$handle = fopen($filename, $mode);
		//
		fwrite($handle, $text."\n");
		fclose($handle);
	}
	//
    function utf8_string_array_encode(&$array){
        $func = function(&$value,&$key){
            if(is_string($value)){
                $value = utf8_encode($value);
            }
            if(is_string($key)){
                $key = utf8_encode($key);
            }
            if(is_array($value)){
                utf8_string_array_encode($value);
            }
        };
        array_walk($array,$func);
        return $array;
    }
    //
	function recordSetToJson($mysql_result,$jsonencode=false,$marker="") {
		$return_arr = array();
		while($datarow = $mysql_result->fetch(PDO::FETCH_ASSOC)){
			$return_arr[] = $datarow;
		}
		//
		$return_arr = utf8_string_array_encode($return_arr);
		//   		
		if (!$jsonencode)	
			return $return_arr;
		else{
			if ($marker != "")
				return json_encode(array($marker =>$return_arr));			
			else
				return json_encode($return_arr);
		}
	}
	//
	function SendRegisterMail($RegNumber,$PIN,$Names,$Email) {
	    //
	    $subject = 'SIMS Access Information';
		// Body
		$mailmsg = "<strong><u>LOGIN INFORMATION</u></strong><BR>";
		$mailmsg .= "<p>Dear $Names,</p>Please find below the access information to FPE SIMS.<br>";
		$mailmsg .= "<br>UserID: $RegNumber";
		$mailmsg .= "<br>Password: $PIN";
		$mailmsg .= "<br><br>Thanks you.";
		
        if (filter_var($Email, FILTER_VALIDATE_EMAIL))
	        $reply = SMTPSendMail($Email,$subject,$mailmsg);
        else 
            $reply = "$Email is not a valid email address";
		//
	    return $reply; 
	}
	//
	function SMTPSendMail($email,$subject,$mailmsg, $cc=''){	 
		//Create a new PHPMailer instance
		global $mail;
		//Tell PHPMailer to use SMTP
		$mail->isSMTP();
		$mail->Host = 'mail.federalpolyede.edu.ng';
		//Set the SMTP port number - likely to be 25, 465 or 587
		$mail->Port = 25;
		//Whether to use SMTP authentication
		$mail->SMTPAuth = true;
		//Do not  use SSL
		$mail->SMTPAutoTLS = false;
		// Set email format to HTML
		$mail->isHTML(true);
		//Username to use for SMTP authentication
		$mail->Username = '[email protected]';
		//Password to use for SMTP authentication
		$mail->Password = '5yJn0c^19';
		//Set who the message is to be sent from
		$mail->setFrom('[email protected]', 'Federal Polytechnic Ede ICT Support');
		//Set who the message is to be sent to
		$mail->addAddress($email, 'Notification of Result');
		//
		//Set the subject line
		$mail->Subject = $subject;
		//Read an HTML message body from an external file, convert referenced images to embedded,
		//convert HTML into a basic plain-text alternative body
		$mail->msgHTML($mailmsg);
		//Replace the plain text body with one created manually
		$retvalue = "";
		if (!$mail->send())
			$retvalue = 'Mailer Error: ' . $mail->ErrorInfo;
		else   
			$retvalue = "OK";
		//
		return $retvalue;
	}
	//
	require('../connect.php');
	$pdo = db_connect();
	//
    $opCode = $_REQUEST['opCode'];
	$matNo = $_REQUEST['RegNumber'];
	//
	$formNo = $_REQUEST['FormNo'];
	$transID = $_REQUEST['TransID'];
	//
	$session = $_REQUEST['Session'];
	$semester = $_REQUEST['Semester']." Semester";
    //
    $item = explode("/",$session);
    $curYear = $item[1];
	$formlike = "FPE/ADM/$curYear/%";
    //
	$isApproved= "True";
	$paymentType = "School Fees";
	$OtherPaymentType = "'School Fees', 'Certificate Levy','New Transcript Request','Reprint Transcript','Re-Notification of Result','Transcript Request','Transcript (Student Copy)'";
	$Query = "";
	//
	if ($opCode == "SEND_EMAIL"){
		//
		$names = $_REQUEST['Names'];
		$pin = $_REQUEST['PIN'];
		$email = $_REQUEST['Email'];
		//
		echo SendRegisterMail($matNo,$pin,$names,$email);
			
	}else if ($opCode == "GET_ORCHIDS_BIODATA"){
		 $Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.mode, a.gsm,a.state,a.lga FROM realdata a WHERE graduated<>'Yes' and a.matNo = ?";
		$stmt = $pdo->prepare($Query);
		$stmt->execute([$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, 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, 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.state,a.lga, 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 = ? ";

        //$Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.state,a.lga,a.mode, a.gsm, a.email,a.usernamea,a.passworda FROM realdata a , coursereg b WHERE a.matNo=b.matNo and a.matNo=? and formNo like ? group by a.matNo; ";
		
        $Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.state,a.lga,a.mode, a.gsm, a.email,a.usernamea,a.passworda FROM realdata a , coursereg b WHERE a.matNo=b.matNo and a.matNo=? group by a.matNo; ";
		
		$stmt = $pdo->prepare($Query);
		//$stmt->execute([$isApproved,$paymentType,$session,$matNo]);
		//$stmt->execute([$matNo,$formlike]);	
		$stmt->execute([$matNo]);		
		if ($stmt->rowCount() == 0)
			echo " $Query NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Biodata");
		//
	
	}else if ($opCode == "GET_ALL_BIODATA"){
		//exit;
        //$Query = "SELECT remitaRRR,a.matNo, a.names, a.sex, a.course, a.levelID, a.state,a.lga,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= ?";
		
        //$Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.state,a.lga,a.mode, a.gsm, a.email,a.usernamea,a.passworda FROM realdata a , coursereg b WHERE a.matNo=b.matNo and formNo like ? group by a.matNo; ";

			$Query = "SELECT a.matNo, a.names, a.sex, a.course, a.levelID, a.state,a.lga,a.mode, a.gsm, a.email,a.usernamea,a.passworda FROM realdata a , coursereg b WHERE a.matNo=b.matNo group by a.matNo; ";

		$stmt = $pdo->prepare($Query);
		//$stmt->execute([$isApproved,$paymentType,$session]);
		$stmt->execute([$formlike]);
		$stmt->execute();

		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.state,a.lga, 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 == "GET_ALL_REGISTRATION_GROUP"){
        $Query = "SELECT matNo,group_concat(courseCode)as Subject FROM coursereg WHERE asession= ? and semester= ?  group by matNo 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 == "GET_ALL_REGISTRATIONX"){
        $Query = "SELECT matNo,group_concat(courseCode) as courses FROM coursereg WHERE asession= ? and semester= ? group by matNo 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 == "UTME_FORMS"){
//        $Query="SELECT a.formNo,a.surname,a.password, a.othernames,b.dOption,a.formType,a.gsm FROM entrancetable a, dept b WHERE a.formNo=?  and a.course=b.sn and a.formNo in (select regNo from remitaorder where transApproved='True' and paymentType='eQuestion')";
         $Query = "SELECT a.formNo,a.surname,a.password, a.othernames,b.dOption,a.formType,a.gsm FROM entrancetable a, dept b WHERE a.formNo=?  and a.course=b.sn and a.formNo in (select regNo from remitaorder where transApproved='True' and paymentType='eQuestion') ";
        $stmt = $pdo->prepare($Query);
        $stmt->execute([$formNo]);
        if ($stmt->rowCount() == 0)
            echo "NOT_FOUND";

        else
            echo recordSetToJson($stmt,true,"Registration");
    }else if ($opCode == "UTME_FORMS_ALL"){
        $Query="SELECT a.formNo,a.password,a.surname, a.othernames,b.dOption,a.formType,a.gsm FROM entrancetable a, dept b WHERE a.course=b.sn and a.formNo in (select regNo from remitaorder where transApproved='True' and paymentType='eQuestion')";
//        echo $Query = "SELECT a.formNo,md5(a.password),a.surname, a.othernames,b.course,a.levels,a.mode  FROM entrancetable a, dept b WHERE a.formNo=? and password=? and a.course=b.sn and a.formNo in (select regNo from remitaorder_2 where transApproved='True' and paymentType='eQuestion') ";
        $stmt = $pdo->prepare($Query);
        $stmt->execute();
        if ($stmt->rowCount() == 0)
            echo "NOT_FOUND";

        else
            echo recordSetToJson($stmt,true,"Registration");

	}else if ($opCode == "GET_A_REPARATION" || $opCode == "GET_ALL_REPARATION"){
		$Query = "SELECT regNo, remitaRRR FROM remitaorder WHERE regNo= ? and upper(paymentType)='REPARATION FEE' and transApproved='True'";
		if ($opCode == "GET_ALL_REPARATION"){ 
			$Query = "SELECT regNo, remitaRRR FROM remitaorder WHERE upper(paymentType)='REPARATION FEE' and transApproved='True'";
			$stmt = $pdo->prepare($Query);
			$stmt->execute();			
		}else{
			$stmt = $pdo->prepare($Query);			
			$stmt->execute([$matNo]);
		}
		//
		if ($stmt->rowCount() == 0)
			echo "NOT_FOUND";
		else
			echo recordSetToJson($stmt,true,"Biodata");
		
    }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 courserega where matNo='$matNo' and asession='$session' and semester='$semester'");
			//
			$Query = "INSERT INTO courserega 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]);
			}
		}

	}else if($opCode == "UPLOAD_PHOTO") {	
	    //
	    $path = "mobat_passports/".$matNo.".jpg";
	    $upfile = $_FILES['file']['tmp_name'];
        move_uploaded_file($upfile, $path);
		//
	}else if($opCode == "DELETE_PHOTO") {	
	    //
	    $path = "mobat_passports/".$matNo.".jpg";
		if (file_exists($path)){
        	unlink($path);
		}
		//
		echo "Registered Face Biometric has been reset!"; 
	}
    //
	$pdo=null;
?>