<?php
require_once( "db.php" );

 class Operations extends Db {
	 
	 public $fname;
	 public $lname;
	 public $dob;
	 public $doe;
	 public $examcode;
	 public $subject_id;
	 public $class;
	 public $time;
	 public $duration;
	 public $date;
	 public $questions;
	 public $total;
	 public $title;
	 public $status;
	 
	 public $question;
	 public $optionA;
	 public $optionB;
	 public $optionC;
	 public $optionD;
	 public $answer;
	 public $totalPages;
	 
	 public function __construct() {

   }



   public function addSubject($title) {
	$instance = parent::getInstance();	
	$MySQLiconn  = $instance->getConnection();	
	
  try{
  
	$sql = "INSERT INTO subject (title)
			 VALUES (:title)";
				 

	
	 $statement = $MySQLiconn->prepare($sql);
 
	 $statement->bindValue(':title',$title);
	 
	 
	 $statement->execute();
	 
	 
	 parent::DisConnect( $MySQLiconn);
   }

   catch(Exception $e){
	echo "oops! ".$e->getMessage(); 
	
	}   
}


public function updateSubject($id,$title) {
	$instance = parent::getInstance();	
	$MySQLiconn  = $instance->getConnection();	
	
  try{
  
 //   $sql = "INSERT INTO login (staffno,password,role,email,textcode,password2)
		   //  VALUES (:staffno, :password, :role, :email, :textcode, :password2)";
		   
		   $sql = "UPDATE subject SET title = :title WHERE id = :id";
				 

	
	 $statement = $MySQLiconn->prepare($sql);
 
	 $statement->bindValue(':id',$id);
	 
	 $statement->bindValue(":title", $title);
	 
	 
		
	 $statement->execute();
	 
	 
	 parent::DisConnect( $MySQLiconn);
   }

	 catch(Exception $e){
		 echo "oops! ".$e->getMessage(); 
		 
		 }   
 }
 
	 
	
	
	
	public function checkSubject($title){
		
		$instance = parent::getInstance();	
       $MySQLiconn  = $instance->getConnection();
		
        $sql = 'SELECT *
                FROM   subject
                WHERE  title = :title';

        $statement = $MySQLiconn->prepare($sql);
        $statement->bindValue(':title', $title);

        $statement->execute();
		//$row = $statement->fetch(PDO::FETCH_ASSOC);
		
     
	  $num = $statement->rowCount();
	 
 
    return $num;
        
      
		 parent::DisConnect( $MySQLiconn);
		
		
		
		
		}


 
		public function getAllSubject(){
		
			$instance = parent::getInstance();	
		   $MySQLiconn  = $instance->getConnection();
			
			$sql = 'SELECT *
					FROM   subject
					';
	
			$statement = $MySQLiconn->prepare($sql);
			//$statement->bindValue(':title', $title);
	
			$statement->execute();
			//$row = $statement->fetch(PDO::FETCH_ASSOC);
			
		 
		 // $num = $statement->rowCount();
		 
	 
		return $statement;
			
		  
			 parent::DisConnect( $MySQLiconn);
			
			
			
			
			}
		
	
	
			public function deleteSubject($id){
			
				$instance = parent::getInstance();	
		  $MySQLiconn  = $instance->getConnection();
		   
		   $sql = 'DELETE 
				   FROM   subject
				   WHERE  id = :id';
   
		   $statement = $MySQLiconn->prepare($sql);
		   $statement->bindValue(':id', $id);
   
		   $statement->execute();  
			 parent::DisConnect( $MySQLiconn);
				
				}
				
			 
	
	
	 public function findSubjectById($id){
		$instance = parent::getInstance();	
       $MySQLiconn  = $instance->getConnection();
	   
	    $sql = 'SELECT * from subject WHERE id =:id';
	   
	   
	    $statement = $MySQLiconn->prepare($sql);
		
		$statement->bindValue(':id',$id);
		
	    $statement->execute();
	   $row = $statement->fetch(PDO::FETCH_ASSOC);
	   
	   $this->title = $row['title'];
	  // $this->class1=$row['class'];
	   $this->id = $row['id'];
	   
		 parent::DisConnect( $MySQLiconn); 
		
		}
	
	
	 
	  
		 
			 		 
	 
	 public function searchStudentAll($name){
		$instance = parent::getInstance();	
       $MySQLiconn  = $instance->getConnection();
	   


 $sql = "SELECT class,image, lname,fname,admissionno,gender,phone,dateadmitted,dob,doe,email
                FROM  student  where admissionno 
				 LIKE '%" . $name .  "%' OR lname LIKE '%" . $name ."%' OR fname LIKE '%" . $name ."%'  OR class LIKE '%" . $name ."%'";		
			
				

        $statement = $MySQLiconn->prepare($sql);
		
       
        $statement->execute();
		
	    return $statement;
	   
		 parent::DisConnect( $MySQLiconn); 
		
		
		}
				 		 


 
	 // exam shedules

public function addSchedule($examcode,$subject_id,$class,$time,$duration,$date,$questions,$term,$year,$total
,$increment, $date1) {
		$instance = parent::getInstance();	
		$MySQLiconn  = $instance->getConnection();	
		
	  
	  try{

		$sql = "INSERT INTO exams (examcode,subject_id,class,time,duration,date,questions,term,year,total,increment,date1)
		VALUES (:examcode,:subject_id,:class,:time,:duration,:date,:questions,:term,:year,:total, :increment, :date1)";
			


$statement = $MySQLiconn->prepare($sql);

$statement->bindValue(':examcode',$examcode);
$statement->bindValue(':subject_id',$subject_id);
$statement->bindValue(':class',$class);
$statement->bindValue(':duration',$duration);
$statement->bindValue(':time',$time);
$statement->bindValue(':date',$date);
$statement->bindValue(':questions',$questions);
$statement->bindValue(':term',$term);
$statement->bindValue(':year',$year);
$statement->bindValue(':total',$total);
$statement->bindValue(':increment',$increment);
$statement->bindValue(':date1',$date1);

$statement->execute(); 
parent::DisConnect( $MySQLiconn);

	  }

	  catch(Exception $e){
		echo "oops! ".$e->getMessage(); 
		
		}   
		
	   
	}

	 

	public function updateSchedule($examcode,$subject_id,$class,$time,$duration,$date,$questions,$term,$year,$date1) {
		$instance = parent::getInstance();	
		$MySQLiconn  = $instance->getConnection();	
		
	  try{
	  
	 //   $sql = "INSERT INTO login (staffno,password,role,email,textcode,password2)
			   //  VALUES (:staffno, :password, :role, :email, :textcode, :password2)";
			   
			   $sql = "UPDATE exams SET subject_id = :subject_id, class = :class, time = :time,
			   duration = :duration, date = :date, questions = :questions, term = :term,
			   year = :year,  date1 = :date1 WHERE examcode = :examcode";
					 
	
		
		 $statement = $MySQLiconn->prepare($sql);
	 
		 $statement->bindValue(':examcode',$examcode);
		 $statement->bindValue(':subject_id',$subject_id);
		 $statement->bindValue(':class',$class);
		 $statement->bindValue(':duration',$duration);
		 $statement->bindValue(':time',$time);
		 $statement->bindValue(':date',$date);
		 $statement->bindValue(':questions',$questions);
		 $statement->bindValue(':term',$term);
		 $statement->bindValue(':year',$year);
	
		 $statement->bindValue(':date1',$date1);
		 
			
		 $statement->execute();
		 
		 
		 parent::DisConnect( $MySQLiconn);
	   }
	
		 catch(Exception $e){
			 echo "oops! ".$e->getMessage(); 
			 
			 }   
	 }
	 


	 public function getAllSchedule(){
		
		$instance = parent::getInstance();	
	   $MySQLiconn  = $instance->getConnection();
		
		$sql = 'SELECT e.examcode, e.class, e.subject_id,e.duration,e.time,e.questions,e.total,
		e.date1,e.status, s.title,e.year,e.term
				FROM   exams e INNER JOIN subject s ON e.subject_id = s.id order by e.date desc
				';

		$statement = $MySQLiconn->prepare($sql);
		//$statement->bindValue(':title', $title);

		$statement->execute();
		//$row = $statement->fetch(PDO::FETCH_ASSOC);
		
	 
	 // $num = $statement->rowCount();
	 
 
	return $statement;
		
	  
		 parent::DisConnect( $MySQLiconn);
		
		
		
		
		}


		public function findExamsById($examcode){
			$instance = parent::getInstance();	
		   $MySQLiconn  = $instance->getConnection();
		   
			$sql = 'SELECT * from exams WHERE examcode =:examcode';
		   
		   
			$statement = $MySQLiconn->prepare($sql);
			
			$statement->bindValue(':examcode',$examcode);
			
			$statement->execute();
		   $row = $statement->fetch(PDO::FETCH_ASSOC);
		   
		 
		  // $this->class1=$row['class'];
		   $this->examcode = $row['examcode'];
		   $this->subject_id = $row['subject_id'];
		   $this->class = $row['class'];
		   $this->time = $row['time'];
		   $this->date = $row['date'];
		   $this->duration = $row['duration'];
		   $this->questions = $row['questions'];
		   $this->total = $row['total'];
		   $this->date1 = $row['date1'];
		   $this->status = $row['status'];
		   $this->passage = $row['passage'];
		   
		    $this->term = $row['term'];
		   $this->year = $row['year'];
			 parent::DisConnect( $MySQLiconn); 
			
			}


			public function getLastExamID(){
		
				$instance = parent::getInstance();	
			   $MySQLiconn  = $instance->getConnection();
				
				$sql = 'SELECT *
						FROM   exams ORDER BY increment DESC LIMIT 1
						';
		
				$statement = $MySQLiconn->prepare($sql);
				//$statement->bindValue(':title', $title);
		
				$statement->execute();
				$row = $statement->fetch(PDO::FETCH_ASSOC);
				
			 
			 // $num = $statement->rowCount();
			 
		   $increment = $row['increment'];
			return $increment;
				
			  
				 parent::DisConnect( $MySQLiconn);
				
				
				
				
				}



				public function checkExamsCode($examcode){
		
					$instance = parent::getInstance();	
				   $MySQLiconn  = $instance->getConnection();
					
					$sql = 'SELECT *
							FROM   exams
							WHERE  examcode = :examcode';
			
					$statement = $MySQLiconn->prepare($sql);
					$statement->bindValue(':examcode', $examcode);
			
					$statement->execute();
					//$row = $statement->fetch(PDO::FETCH_ASSOC);
					
				 
				  $num = $statement->rowCount();
				 
			 
				return $num;
					
				  
					 parent::DisConnect( $MySQLiconn);
					
					
					
					
					}
			
			
			// questions codes


			public function addQuestions($subject_id,$question,$optionA,$optionB, $optionC, $optionD, $answer,$examcode) {
				$instance = parent::getInstance();	
				$MySQLiconn  = $instance->getConnection();	
				
			  try{
			  
				$sql = "INSERT INTO questions (subject_id,question,optionA, optionB, optionC,optionD, answer,examcode
				)
						 VALUES (:subject_id,:question,:optionA,:optionB,:optionC,:optionD, :answer, :examcode)";
							 
			
				
				 $statement = $MySQLiconn->prepare($sql);
			 
				 $statement->bindValue(':subject_id',$subject_id);
				 $statement->bindValue(':question',$question);
				 $statement->bindValue(':optionA',$optionA);
				 $statement->bindValue(':optionB',$optionB);
				 $statement->bindValue(':optionC',$optionC);
				 $statement->bindValue(':optionD',$optionD);
				 $statement->bindValue(':answer',$answer);
				 $statement->bindValue(':examcode',$examcode);
				 
				 $statement->execute();
				 
				 
				 parent::DisConnect( $MySQLiconn);
			   }
			
			   catch(Exception $e){
				echo "oops! ".$e->getMessage(); 
				
				}   
			}


			public function editQuestions($id,$question,$optionA,$optionB, $optionC, $optionD, $answer) {
				$instance = parent::getInstance();	
				$MySQLiconn  = $instance->getConnection();	
				
			  try{
			  
				$sql = " UPDATE questions set  question = :question, optionA = :optionA,
				optionB = :optionB, optionC = :optionC, optionD = :optionD, answer = :answer where id = :id ";
							 
			
				
				 $statement = $MySQLiconn->prepare($sql);
			 
				 $statement->bindValue(':id',$id);
				 $statement->bindValue(':question',$question);
				 $statement->bindValue(':optionA',$optionA);
				 $statement->bindValue(':optionB',$optionB);
				 $statement->bindValue(':optionC',$optionC);
				 $statement->bindValue(':optionD',$optionD);
				 $statement->bindValue(':answer',$answer);
	
				 
				 $statement->execute();
				 
				 
				 parent::DisConnect( $MySQLiconn);
			   }
			
			   catch(Exception $e){
				echo "oops! ".$e->getMessage(); 
				
				}   
			}
			
 
			public function getQuestionsByExamID($examcode){
		
				$instance = parent::getInstance();	
			   $MySQLiconn  = $instance->getConnection();
				
				$sql = 'SELECT *
						FROM   questions where examcode = :examcode ORDER BY id ASC
						';
		
				$statement = $MySQLiconn->prepare($sql);
				$statement->bindValue(':examcode', $examcode);
		
				$statement->execute();
			//	$row = $statement->fetch(PDO::FETCH_ASSOC);
				
			 
			 // $num = $statement->rowCount();
			 
		  // $increment = $row['increment'];
			return $statement;
				
			  
				 parent::DisConnect( $MySQLiconn);
				
				
				
				
				}
			
 

				public function getQuestionByID($id){
		
					$instance = parent::getInstance();	
				   $MySQLiconn  = $instance->getConnection();
					
					$sql = 'SELECT *
							FROM   questions where id = :id
							';
			
					$statement = $MySQLiconn->prepare($sql);
					$statement->bindValue(':id', $id);
			
					$statement->execute();
					$row = $statement->fetch(PDO::FETCH_ASSOC);
					
					$this->id = $row['id'];
					$this->question = $row['question'];
					$this->optionA = $row['optionA'];
					$this->optionB = $row['optionB'];
					$this->optionC = $row['optionC'];
					$this->optionD = $row['optionD'];
					$this->answer = $row['answer'];
					$this->subject_id = $row['subject_id'];
					$this->examcode = $row['examcode'];
				 // $num = $statement->rowCount();
				 
			  // $increment = $row['increment'];
				
					
				  
					 parent::DisConnect( $MySQLiconn);
					
					
					
					
					}



					public function deleteQuestion($id){
		
						$instance = parent::getInstance();	
					   $MySQLiconn  = $instance->getConnection();
						
						$sql = 'DELETE
								FROM   questions where id = :id
								';
				
						$statement = $MySQLiconn->prepare($sql);
						$statement->bindValue(':id', $id);
				
						$statement->execute();
						
					
						
					  
						 parent::DisConnect( $MySQLiconn);
						
						
						
						
						}


						public function getCountQuestionsByExamID($examcode){
		
							$instance = parent::getInstance();	
						   $MySQLiconn  = $instance->getConnection();
							
							$sql = 'SELECT *
									FROM   questions where examcode = :examcode ORDER BY id DESC
									';
					
							$statement = $MySQLiconn->prepare($sql);
							$statement->bindValue(':examcode', $examcode);
					
							$statement->execute();
						//	$row = $statement->fetch(PDO::FETCH_ASSOC);
							
						 
						  $num = $statement->rowCount();
						 
					  // $increment = $row['increment'];
						return $num;
							
						  
							 parent::DisConnect( $MySQLiconn);
							
							
							
							
							}

							public function updateExamStatus($examcode,$status){
		
								$instance = parent::getInstance();	
							   $MySQLiconn  = $instance->getConnection();
								
								$sql = "UPDATE exams set status = :status where examcode = :examcode";
						
								$statement = $MySQLiconn->prepare($sql);
								$statement->bindValue(':examcode', $examcode);
								$statement->bindValue(':status', $status);
						
								$statement->execute();
								
							
								
							  
								 parent::DisConnect( $MySQLiconn);
								
								
								
								
								}	

				public function getQuestionsByExamIDLIMIT1($examcode,$start,$limit){
		
				$instance = parent::getInstance();	
			   $MySQLiconn  = $instance->getConnection();
				
				$sql = "SELECT *
						FROM   questions where examcode = :examcode ORDER BY id DESC  LIMIT $start ,$limit "
						;
		
				$statement = $MySQLiconn->prepare($sql);
				$statement->bindValue(':examcode', $examcode);
	
				$statement->execute();
			//	$row = $statement->fetch(PDO::FETCH_ASSOC);
				
			 
			 // $num = $statement->rowCount();
			 
		  // $increment = $row['increment'];
			return $statement;
				
			  
				 parent::DisConnect( $MySQLiconn);
				
				
				
				
				}
									

				public function getCountQuestionsByExamIDTest($examcode){
		
					$instance = parent::getInstance();	
				   $MySQLiconn  = $instance->getConnection();
					
					$sql = 'SELECT *
							FROM   questions where examcode = :examcode ORDER BY id DESC
							';
			
					$statement = $MySQLiconn->prepare($sql);
					$statement->bindValue(':examcode', $examcode);
			
					$statement->execute();
					$row = $statement->fetch(PDO::FETCH_ASSOC);
					
				 
				//  $num = $statement->rowCount();

			$num = $row[0];
				 
			  // $increment = $row['increment'];
				return $num;
					
				  
					 parent::DisConnect( $MySQLiconn);
					
					
					
					
					}

					// scores page


					public function addScores($subject_id,$examcode,$lname,$fname,$class,$admissionno,$score,$class2) {
						$instance = parent::getInstance();	
						$MySQLiconn  = $instance->getConnection();	
						
					  try{
					  
						$sql = "INSERT INTO scores (subject_id,examcode,lname,fname,class,admissionno,
						score,class2)
								 VALUES (:subject_id,:examcode,:lname, :fname,:class,:admissionno, :score, :class2)";
									 
					
						
						 $statement = $MySQLiconn->prepare($sql);
					 
						 $statement->bindValue(':subject_id',$subject_id);
						 $statement->bindValue(':examcode',$examcode);
						 $statement->bindValue(':lname',$lname);
						 $statement->bindValue(':fname',$fname);
						 $statement->bindValue(':class',$class);
						 $statement->bindValue(':admissionno',$admissionno);
						 $statement->bindValue(':score',$score);
						 $statement->bindValue(':class2',$class2);
						
						 
						 
						 $statement->execute();
						 
						 
						 parent::DisConnect( $MySQLiconn);
					   }
					
					   catch(Exception $e){
						echo "oops! ".$e->getMessage(); 
						
						}   


					}


					public function updateScores($examcode,$admissionno,$score){
		
						$instance = parent::getInstance();	
					   $MySQLiconn  = $instance->getConnection();
						
						$sql = "UPDATE scores set score = :score where admissionno = :admissionno and
						examcode = :examcode";
				
						$statement = $MySQLiconn->prepare($sql);
						$statement->bindValue(':examcode', $examcode);
						$statement->bindValue(':admissionno', $admissionno);
				        $statement->bindValue(':score', $score);
						$statement->execute();
						
					
						
					  
						 parent::DisConnect( $MySQLiconn);
						
						
						
						
						}
						
						
						public function getScores($examcode){
		
							$instance = parent::getInstance();	
						   $MySQLiconn  = $instance->getConnection();
							
							$sql = 'SELECT *
									FROM   scores where examcode = :examcode ORDER BY score DESC
									';
					
							$statement = $MySQLiconn->prepare($sql);
							$statement->bindValue(':examcode', $examcode);
					
							$statement->execute();
						//	$row = $statement->fetch(PDO::FETCH_ASSOC);
							
						 
						  $num = $statement->rowCount();
						 
					  // $increment = $row['increment'];
						return $num;
							
						  
							 parent::DisConnect( $MySQLiconn);
							
							
							
							
							}

			
							public function checkCountScores($examcode,$admissionno){
		
								$instance = parent::getInstance();	
							   $MySQLiconn  = $instance->getConnection();
								
								$sql = 'SELECT *
										FROM   scores
										WHERE  examcode = :examcode and admissionno = :admissionno';
						
								$statement = $MySQLiconn->prepare($sql);
								$statement->bindValue(':examcode', $examcode);
								$statement->bindValue(':admissionno', $admissionno);
						
								$statement->execute();
								//$row = $statement->fetch(PDO::FETCH_ASSOC);
								
							 
							  $num = $statement->rowCount();
							 
						 
							return $num;
								
							  
								 parent::DisConnect( $MySQLiconn);
								
								
								
								
								}

					
								public function getQuestionsByExamIDByStatus($examcode){
		
									$instance = parent::getInstance();	
								   $MySQLiconn  = $instance->getConnection();
									
									$sql = "SELECT q.question,q.optionA,q.optionB,q.optionC,q.optionD,q.id,e.status
											FROM   questions q INNER JOIN exams e ON q.examcode = e.examcode where q.examcode = :examcode 
											and e.status = 'opened'  ORDER BY q.id ACS";
											
							
									$statement = $MySQLiconn->prepare($sql);
									$statement->bindValue(':examcode', $examcode);
							
									$statement->execute();
								//	$row = $statement->fetch(PDO::FETCH_ASSOC);
									
								 
								 // $num = $statement->rowCount();
								 
							  // $increment = $row['increment'];
								return $statement;
									
								  
									 parent::DisConnect( $MySQLiconn);
									
									
									
									
									}
								
					 
									public function getQuestionsByExamIDRandom($examcode){
		
										$instance = parent::getInstance();	
									   $MySQLiconn  = $instance->getConnection();
										
										$sql = 'SELECT *
												FROM   questions where examcode = :examcode ORDER BY RAND()
												';
								
										$statement = $MySQLiconn->prepare($sql);
										$statement->bindValue(':examcode', $examcode);
								
										$statement->execute();
									//	$row = $statement->fetch(PDO::FETCH_ASSOC);
										
									 
									 // $num = $statement->rowCount();
									 
								  // $increment = $row['increment'];
									return $statement;
										
									  
										 parent::DisConnect( $MySQLiconn);
										
										
										}
                         
										public function getGeneralScoresAndSubjectAndClass($examcode,$class2){
		
											$instance = parent::getInstance();	
										   $MySQLiconn  = $instance->getConnection();
											
											$sql = 'SELECT  s.admissionno, s.fname, s.lname,s.score,s.class, s.class2,
											e.title from scores s INNER JOIN subject e ON s.subject_id = e.subject_id 
											 where s.examcode = :examcode AND s.class2 = :class2 ORDER BY score DESC
													';
									
											$statement = $MySQLiconn->prepare($sql);
											$statement->bindValue(':examcode', $examcode);
											$statement->bindValue(':class2', $class2);
											$statement->execute();
										//	$row = $statement->fetch(PDO::FETCH_ASSOC);
											
										 
										 // $num = $statement->rowCount();
										 return $statement;
										 
									  // $increment = $row['increment'];
									
											
										  
											 parent::DisConnect( $MySQLiconn);
											
											
											
											
											}


											public function getGeneralScoresAndSubject($examcode,$class2){
		
												$instance = parent::getInstance();	
											   $MySQLiconn  = $instance->getConnection();
												
												$sql = 'SELECT  s.admissionno, s.fname, s.lname,s.score,s.class, s.class2,
												e.title from scores s INNER JOIN subject e ON s.subject_id = e.id 
												 where s.examcode = :examcode  AND s.class2 = :class2 ORDER BY score DESC
														';
										
												$statement = $MySQLiconn->prepare($sql);
												$statement->bindValue(':examcode', $examcode);
													$statement->bindValue(':class2', $class2);
										
												$statement->execute();
											//	$row = $statement->fetch(PDO::FETCH_ASSOC);
												
											 
											 // $num = $statement->rowCount();
											 return $statement;
											 
										  // $increment = $row['increment'];
										
												
											  
												 parent::DisConnect( $MySQLiconn);
												
												
												
												
												}
									
											
								
												public function getCountScoresAndSubject($examcode){
		
													$instance = parent::getInstance();	
												   $MySQLiconn  = $instance->getConnection();
													
													$sql = 'SELECT  s.admissionno, s.fname, s.lname,s.score,s.class, s.class2,
													e.title from scores s INNER JOIN subject e ON s.subject_id = e.id 
													 where s.examcode = :examcode ORDER BY score DESC
															';
											
													$statement = $MySQLiconn->prepare($sql);
													$statement->bindValue(':examcode', $examcode);
											
													$statement->execute();
												//	$row = $statement->fetch(PDO::FETCH_ASSOC);
													
												 
												  $num = $statement->rowCount();
												 return $num;
												 
											  // $increment = $row['increment'];
											
													
												  
													 parent::DisConnect( $MySQLiconn);
													
													
													
													
													}
										
													public function getCountScoresAndSubjectAndClass($examcode,$class2){
		
														$instance = parent::getInstance();	
													   $MySQLiconn  = $instance->getConnection();
														
														$sql = 'SELECT  s.admissionno, s.fname, s.lname,s.score,s.class, s.class2,
														e.title from scores s INNER JOIN subject e ON s.subject_id = e.subject_id 
														 where s.examcode = :examcode AND s.class2 = :class2 ORDER BY score DESC
																';
												
														$statement = $MySQLiconn->prepare($sql);
														$statement->bindValue(':examcode', $examcode);
														$statement->bindValue(':class2', $class2);
														$statement->execute();
													//	$row = $statement->fetch(PDO::FETCH_ASSOC);
														
													 
													  $num = $statement->rowCount();
													 return $num;
													 
												  // $increment = $row['increment'];
												
														
													  
														 parent::DisConnect( $MySQLiconn);
														
														
														
														
														}
			
					
														public function addPassage($examcode,$passage) {
															$instance = parent::getInstance();	
															$MySQLiconn  = $instance->getConnection();	
															
														  try{
														  
														 //   $sql = "INSERT INTO login (staffno,password,role,email,textcode,password2)
																   //  VALUES (:staffno, :password, :role, :email, :textcode, :password2)";
																   
																   $sql = "UPDATE exams SET passage = :passage WHERE examcode = :examcode";
																		 
														
															
															 $statement = $MySQLiconn->prepare($sql);
														 
															 $statement->bindValue(':examcode',$examcode);
															 $statement->bindValue(':passage',$passage);
														
															 
															 
																
															 $statement->execute();
															 
															 
															 parent::DisConnect( $MySQLiconn);
														   }
														
															 catch(Exception $e){
																 echo "oops! ".$e->getMessage(); 
																 
																 }   
														 }								
						
														 
								 public function addScheduleStaff($examcode,$subject_id,$class,$time,$duration,$date,$questions,$term,$year
														 ,$increment, $date1,$staffid) {
																 $instance = parent::getInstance();	
																 $MySQLiconn  = $instance->getConnection();	
																 
															   
															   try{
														 
																 $sql = "INSERT INTO exams (examcode,subject_id,class,time,duration,date,questions,term,year,increment,date1,staffid)
																 VALUES (:examcode,:subject_id,:class,:time,:duration,:date,:questions,:term,:year, :increment, :date1, :staffid)";
																	 
														 
														 
														 $statement = $MySQLiconn->prepare($sql);
														 
														 $statement->bindValue(':examcode',$examcode);
														 $statement->bindValue(':subject_id',$subject_id);
														 $statement->bindValue(':class',$class);
														 $statement->bindValue(':duration',$duration);
														 $statement->bindValue(':time',$time);
														 $statement->bindValue(':date',$date);
														 $statement->bindValue(':questions',$questions);
														 $statement->bindValue(':term',$term);
														 $statement->bindValue(':year',$year);
														
														 $statement->bindValue(':increment',$increment);
														 $statement->bindValue(':date1',$date1);
														 $statement->bindValue(':staffid',$staffid);
														 $statement->execute(); 
														 parent::DisConnect( $MySQLiconn);
														 
															   }
														 
															   catch(Exception $e){
																 echo "oops! ".$e->getMessage(); 
																 
																 }   
																 
																
															 }
																									  
															 public function getAllScheduleStaff($staffid){
		
																$instance = parent::getInstance();	
															   $MySQLiconn  = $instance->getConnection();
																
																$sql = 'SELECT e.examcode, e.class, e.subject_id,e.duration,e.time,e.questions,e.total,
																e.date1,e.status, s.title,e.year,e.term
																		FROM   exams e INNER JOIN subject s ON e.subject_id = s.id where e.staffid = :staffid order by e.date desc
																		';
														
																$statement = $MySQLiconn->prepare($sql);
																$statement->bindValue(':staffid', $staffid);
														
																$statement->execute();
																//$row = $statement->fetch(PDO::FETCH_ASSOC);
																
															 
															 // $num = $statement->rowCount();
															 
														 
															return $statement;
																
															  
																 parent::DisConnect( $MySQLiconn);
																
																
																
																
																}

							
																public function getQuestionsByExamIDStaffCount($examcode){
		
																	$instance = parent::getInstance();	
																   $MySQLiconn  = $instance->getConnection();
																	
																	$sql = 'SELECT *
																			FROM   questions where examcode = :examcode  ORDER BY id ASC
																			';
															
																	$statement = $MySQLiconn->prepare($sql);
																	$statement->bindValue(':examcode', $examcode);
																	
															
																	$statement->execute();
																//	$row = $statement->fetch(PDO::FETCH_ASSOC);
																	
																 
																  $num = $statement->rowCount();
																 
															  // $increment = $row['increment'];
																return $num;
																	
																  
																	 parent::DisConnect( $MySQLiconn);
																	
																	
																	
																	
																	}   
																	public function getQuestionsByExamIDStaffLimit($examcode,$page_first_result,$results_per_page){
		
																		$instance = parent::getInstance();	
																	   $MySQLiconn  = $instance->getConnection();
																		
																		$sql = "SELECT *
																				FROM   questions where examcode = :examcode ORDER BY id ASC LIMIT $page_first_result,$results_per_page  
																				";
																
																		$statement = $MySQLiconn->prepare($sql);
																		$statement->bindValue(':examcode', $examcode);
																		
																
																		$statement->execute();
																	//	$row = $statement->fetch(PDO::FETCH_ASSOC);
																		
																	 
																	//  $num = $statement->rowCount();
																	 
																  // $increment = $row['increment'];
															return $statement;
																		
																	  
																		 parent::DisConnect( $MySQLiconn);
																		
																		
																		
																		
																		}    
																		
						public function deleteExamSchedule($examcode){
			
				$instance = parent::getInstance();	
		  $MySQLiconn  = $instance->getConnection();
		   
		   $sql = 'DELETE 
				   FROM   exams
				   WHERE  examcode = :examcode';
   
		   $statement = $MySQLiconn->prepare($sql);
		   $statement->bindValue(':examcode', $examcode);
   
		   $statement->execute();  
			 parent::DisConnect( $MySQLiconn);
				
				}
																	
	
		
		// new codes
					public function addExamAudit($examcode,$admissionno,$question_id,$studanswer,$date) {
						$instance = parent::getInstance();	
						$MySQLiconn  = $instance->getConnection();	
						
					  try{
					  
						$sql = "INSERT INTO exam_audit (examcode,admissionno,
						question_id,studanswer,date)
								 VALUES (:examcode,:admissionno, :question_id, :studanswer,:date)";
									 
					
						
						 $statement = $MySQLiconn->prepare($sql);
					 
						
						 $statement->bindValue(':examcode',$examcode);
						 
						 $statement->bindValue(':admissionno',$admissionno);
						 $statement->bindValue(':question_id',$question_id);
						 $statement->bindValue(':studanswer',$studanswer);
						 $statement->bindValue(':date',$date);
						
						 
						 
						 $statement->execute();
						 
						 
						 parent::DisConnect( $MySQLiconn);
					   }
					
					   catch(Exception $e){
						echo "oops! ".$e->getMessage(); 
						
						}   


					}

					public function getExamsAudit($examcode,$admissionno){
		
						$instance = parent::getInstance();	
					   $MySQLiconn  = $instance->getConnection();
						
						$sql = "SELECT  e.admissionno, e.examcode, e.question_id, e.studanswer,e.date, q.question,q.optionA,q.optionB,
						q.optionC,q.optionD, q.answer  
						from exam_audit e INNER JOIN questions q ON e.question_id = q.id
						 where e.examcode = :examcode and e.admissionno = :admissionno 
								";
				
						$statement = $MySQLiconn->prepare($sql);
						$statement->bindValue(':examcode', $examcode);
						$statement->bindValue(':admissionno', $admissionno);
				
						$statement->execute();
					//	$row = $statement->fetch(PDO::FETCH_ASSOC);
						
					 
					 // $num = $statement->rowCount();
					 return $statement;
					 
				  // $increment = $row['increment'];
				
						
					  
						 parent::DisConnect( $MySQLiconn);
						
						
						
						
						}		
																							 
																										 
    	public function findPassage($examcode){
			
		$instance = parent::getInstance();	
   $MySQLiconn  = $instance->getConnection();
		
		try {
		$sql = "SELECT *  from exams WHERE examcode =:examcode";
		
		$statement = $MySQLiconn->prepare($sql);
		
		$statement->bindValue(':examcode',$examcode);
		 $statement->execute();
	  $row = $statement->fetch(PDO::FETCH_ASSOC);  
	  $this->passage = $row['passage'];
	 
		parent::DisConnect( $MySQLiconn);	

		}
		catch(Exception $e){
			echo "oops! ".$e->getMessage(); 
			
			} 

		}


		//  new codes

		public function addTopic($subject_id,$title,$date,$class) {
			$instance = parent::getInstance();	
			$MySQLiconn  = $instance->getConnection();	
			
		  try{
		  
			$sql = "INSERT INTO topic (subject_id,title,date,class)
					 VALUES (:subject_id,:title,:date,:class)";
						 
		
			
			 $statement = $MySQLiconn->prepare($sql);
		 
			 $statement->bindValue(':title',$title);
			 $statement->bindValue(':subject_id',$subject_id);
			 $statement->bindValue(':date',$date);
			 $statement->bindValue(':class',$class);
			 
			 $statement->execute();
			 
			 
			 parent::DisConnect( $MySQLiconn);
		   }
		
		   catch(Exception $e){
			echo "oops! ".$e->getMessage(); 
			
			}   
		}
		
		
		public function updateTopic($id,$subject_id,$title,$class) {
			$instance = parent::getInstance();	
			$MySQLiconn  = $instance->getConnection();	
			
		  try{
		  
		
				   
				   $sql = "UPDATE topic SET subject_id = :subject_id,  title = :title, class = :class WHERE id = :id";
						 
		
			
			 $statement = $MySQLiconn->prepare($sql);
		 
			 $statement->bindValue(':id',$id);
			 
			 $statement->bindValue(":title", $title);
			 $statement->bindValue(":subject_id", $subject_id);
			 $statement->bindValue(":class", $class);
				
			 $statement->execute();
			 
			 
			 parent::DisConnect( $MySQLiconn);
		   }
		
			 catch(Exception $e){
				 echo "oops! ".$e->getMessage(); 
				 
				 }   
		 }
		 
   

		 public function findTopics($subject_id){
		
			$instance = parent::getInstance();	
		   $MySQLiconn  = $instance->getConnection();
			
			$sql = "SELECT  e.id, e.title, s.title
			from topic e INNER JOIN subject s ON e.subject_id = s.id
			 where e.subject_id = :subject_id 
					";
	
			$statement = $MySQLiconn->prepare($sql);
			$statement->bindValue(':subject_id', $subject_id);
		
	
			$statement->execute();
		
		 return $statement;
		 
	 
	
			
		  
			 parent::DisConnect( $MySQLiconn);
			
			
			
			
			}		
				

      // check if topic has already been entered for class
			public function checkTopic($title,$class){
		
				$instance = parent::getInstance();	
			   $MySQLiconn  = $instance->getConnection();
				
				$sql = 'SELECT *
						FROM   topic
						WHERE  title = :title AND class = :class';
		
				$statement = $MySQLiconn->prepare($sql);
				$statement->bindValue(':title', $title);
		        $statement->bindValue(':class', $class);
				$statement->execute();
				//$row = $statement->fetch(PDO::FETCH_ASSOC);
				
			 
			  $num = $statement->rowCount();
			 
		 
			return $num;
				
			  
				 parent::DisConnect( $MySQLiconn);
				
				
				
				
				}	

// find topic by subject ID and class

public function findTopicBySubjectIDandClass($subject_id,$class){
		
	$instance = parent::getInstance();	
   $MySQLiconn  = $instance->getConnection();
	
	$sql = 'SELECT *
			FROM   topic
			WHERE  subject_id = :subject_id AND class = :class';

	$statement = $MySQLiconn->prepare($sql);
	$statement->bindValue(':subject_id', $subject_id);
	$statement->bindValue(':class', $class);
	$statement->execute();
	//$row = $statement->fetch(PDO::FETCH_ASSOC);
	
 
  //$num = $statement->rowCount();
 

return $statement;
	
  
	 parent::DisConnect( $MySQLiconn);
	
	
	
	
	}	



		// get all subject

		public function findAllTopics(){
		
			$instance = parent::getInstance();	
		   $MySQLiconn  = $instance->getConnection();
			
			$sql = "SELECT t.id, t.subject_id,t.title, t.class, s.title AS subject_title from topic t INNER JOIN 
			subject s on t.subject_id = s.id  ";
					
	
			$statement = $MySQLiconn->prepare($sql);
		
		
	
			$statement->execute();
		
		 return $statement;
		 
	 
	
			
		  
			 parent::DisConnect( $MySQLiconn);
			
			
			
			
			}		


			public function deleteTopic($id){
			
				$instance = parent::getInstance();	
		  $MySQLiconn  = $instance->getConnection();
		   
		   $sql = 'DELETE 
				   FROM   topic
				   WHERE  id = :id';
   
		   $statement = $MySQLiconn->prepare($sql);
		   $statement->bindValue(':id', $id);
   
		   $statement->execute();  
			 parent::DisConnect( $MySQLiconn);
				
				}
						
				
	
				public function findTopicByID($id){
			
					$instance = parent::getInstance();	
			   $MySQLiconn  = $instance->getConnection();
					
					try {
					$sql = "SELECT *  from topic WHERE id =:id";
					
					$statement = $MySQLiconn->prepare($sql);
					
					$statement->bindValue(':id',$id);
					 $statement->execute();
				  $row = $statement->fetch(PDO::FETCH_ASSOC);  
				  $this->title = $row['title'];
				  $this->class = $row['class'];
				  $this->subject_id = $row['subject_id'];
					parent::DisConnect( $MySQLiconn);	
			
					}
					catch(Exception $e){
						echo "oops! ".$e->getMessage(); 
						
						} 
			
					}			

         // question bank


		 public function addQuestionBank($subject_id,$topic_id, $question,$optionA,$optionB
		 ,$optionC, $optionD, $answer, $date) {
			$instance = parent::getInstance();	
			$MySQLiconn  = $instance->getConnection();	
			
		  try{
		  
			$sql = "INSERT INTO question_bank (subject_id,topic_id,question,optionA,optionB,optionC,optionD,answer,date)
					 VALUES (:subject_id,:topic_id,:question,:optionA,:optionB, :optionC, :optionD, :answer, :date)";
						 
		
			
			 $statement = $MySQLiconn->prepare($sql);
		 
			
			 $statement->bindValue(':subject_id',$subject_id);
			 $statement->bindValue(':topic_id',$topic_id);
			 $statement->bindValue(':question',$question);
			 $statement->bindValue(':optionA',$optionA);
			 $statement->bindValue(':optionB',$optionB);
			 $statement->bindValue(':optionC',$optionC);
			 $statement->bindValue(':optionD',$optionD);
			 $statement->bindValue(':answer',$answer);
			 $statement->bindValue(':date',$date);
			
			 
			 $statement->execute();
			 
			 
			 parent::DisConnect( $MySQLiconn);
		   }
		
		   catch(Exception $e){
			echo "oops! ".$e->getMessage(); 
			
			}   
		}
		
// update question bank

public function updateQuestionBank($id,$question,$optionA,$optionB
		 ,$optionC, $optionD, $answer, $date) {
			$instance = parent::getInstance();	
			$MySQLiconn  = $instance->getConnection();	
			
		  try{
		  
			$sql = " UPDATE question_bank SET   
			question = :question, optionA = :optionA, optionB = :optionB, optionC = :optionC,
			optionD = :optionD, answer = :answer where id = :id ";
						 
		
			
			 $statement = $MySQLiconn->prepare($sql);
		 
			
			
			 $statement->bindValue(':question',$question);
			 $statement->bindValue(':optionA',$optionA);
			 $statement->bindValue(':optionB',$optionB);
			 $statement->bindValue(':optionC',$optionC);
			 $statement->bindValue(':optionD',$optionD);
			 $statement->bindValue(':answer',$answer);
			 $statement->bindValue(':id',$id);
			
			 
			 $statement->execute();
			 
			 
			 parent::DisConnect( $MySQLiconn);
		   }
		
		   catch(Exception $e){
			echo "oops! ".$e->getMessage(); 
			
			}   
		}


		public function findQuestionBankByID($id){
			
			$instance = parent::getInstance();	
	   $MySQLiconn  = $instance->getConnection();
			
			try {
			$sql = "SELECT *  from question_bank WHERE id =:id";
			
			$statement = $MySQLiconn->prepare($sql);
			
			$statement->bindValue(':id',$id);
			 $statement->execute();
		  $row = $statement->fetch(PDO::FETCH_ASSOC);  
		  $this->subject_id = $row['subject_id'];
		  $this->topic_id = $row['topic_id'];
		  $this->question = $row['question'];
		  $this->optionA = $row['optionA'];
		  $this->optionB = $row['optionB'];
		  $this->optionC = $row['optionC'];
		  $this->optionD = $row['optionD'];
		  $this->answer = $row['answer'];
		  $this->date = $row['date'];
			parent::DisConnect( $MySQLiconn);	
	
			}
			catch(Exception $e){
				echo "oops! ".$e->getMessage(); 
				
				} 
	
			}
			
			public function findQuestionsByTopic($topic_id){
		
				$instance = parent::getInstance();	
			   $MySQLiconn  = $instance->getConnection();
				
				$sql = "SELECT * from question_bank WHERE topic_id =:topic_id  ";
						
		
				$statement = $MySQLiconn->prepare($sql);
				$statement->bindValue(':topic_id',$topic_id);
			
		
				$statement->execute();
			
			 return $statement;
			  
				 parent::DisConnect( $MySQLiconn);
				
				
				
				
				}	

		
				public function deleteQuestionFromBank($id){
			
					$instance = parent::getInstance();	
			  $MySQLiconn  = $instance->getConnection();
			   
			   $sql = 'DELETE 
					   FROM   question_bank
					   WHERE  id = :id';
	   
			   $statement = $MySQLiconn->prepare($sql);
			   $statement->bindValue(':id', $id);
	   
			   $statement->execute();  
				 parent::DisConnect( $MySQLiconn);
					
					}
						
		
		
					public function getCountQuestionsByExamIDAndBankID($examcode,$question_bank_id){
		
						$instance = parent::getInstance();	
					   $MySQLiconn  = $instance->getConnection();
						
						$sql = 'SELECT *
								FROM   questions where examcode = :examcode and question_bank_id = :question_bank_id
								';
				
						$statement = $MySQLiconn->prepare($sql);
						$statement->bindValue(':examcode', $examcode);
						$statement->bindValue(':question_bank_id', $question_bank_id);
				
						$statement->execute();
					//	$row = $statement->fetch(PDO::FETCH_ASSOC);
						
					 
					  $num = $statement->rowCount();
					 
				  // $increment = $row['increment'];
					return $num;
						
					  
						 parent::DisConnect( $MySQLiconn);
					
						
						}	

						
				
						public function addQuestionsFromBank($subject_id,$question,$optionA,$optionB, $optionC, $optionD, $answer,$examcode,$question_bank_id) {
							$instance = parent::getInstance();	
							$MySQLiconn  = $instance->getConnection();	
							
						  try{
						  
							$sql = "INSERT INTO questions (subject_id,question,optionA, optionB, optionC,optionD, answer,examcode, question_bank_id
							)
									 VALUES (:subject_id,:question,:optionA,:optionB,:optionC,:optionD, :answer, :examcode, :question_bank_id)";
										 
						
							
							 $statement = $MySQLiconn->prepare($sql);
						 
							 $statement->bindValue(':subject_id',$subject_id);
							 $statement->bindValue(':question',$question);
							 $statement->bindValue(':optionA',$optionA);
							 $statement->bindValue(':optionB',$optionB);
							 $statement->bindValue(':optionC',$optionC);
							 $statement->bindValue(':optionD',$optionD);
							 $statement->bindValue(':answer',$answer);
							 $statement->bindValue(':examcode',$examcode);
							 $statement->bindValue(':question_bank_id',$question_bank_id);
							 $statement->execute();
							 
							 
							 parent::DisConnect( $MySQLiconn);
						   }
						
						   catch(Exception $e){
							echo "oops! ".$e->getMessage(); 
							
							}   
						}
						
						
						
					// delete question

						public function deleteQuestionsByExamcode($examcode){
		
						$instance = parent::getInstance();	
					   $MySQLiconn  = $instance->getConnection();
						
						$sql = 'DELETE
								FROM   questions where examcode = :examcode
								';
				
						$statement = $MySQLiconn->prepare($sql);
						$statement->bindValue(':examcode', $examcode);
				
						$statement->execute();
						
					
						
					  
						 parent::DisConnect( $MySQLiconn);
						
						
						
						
						}


						//delete questions
						
						
						
				
				// add exam session
						public function addExamSessions($examcode, $admissionno, $class, $lname, $fname, $token, $question_list, $start_time, $expires_at) {
    $instance = parent::getInstance();	
    $MySQLiconn  = $instance->getConnection();	

    try {
        $sql = "INSERT INTO exam_sessions 
                (examcode, admissionno, class, lname, fname, token, question_list, start_time, expires_at)
                VALUES 
                (:examcode, :admissionno, :class, :lname, :fname, :token, :question_list, :start_time, :expires_at)";

        $statement = $MySQLiconn->prepare($sql);

        $statement->bindValue(':examcode', $examcode);
        $statement->bindValue(':admissionno', $admissionno);
        $statement->bindValue(':class', $class);
        $statement->bindValue(':lname', $lname);
        $statement->bindValue(':fname', $fname);
        $statement->bindValue(':token', $token);
        $statement->bindValue(':question_list', $question_list); // already json_encode() outside
        $statement->bindValue(':start_time', $start_time);
        $statement->bindValue(':expires_at', $expires_at);

        $statement->execute();

        parent::DisConnect($MySQLiconn);
    } catch (Exception $e) {
        echo "oops! ".$e->getMessage();
    }
}


						//add exam session


						// find exam session

						public function findExamSession($examcode, $admissionno, $token) {
    $instance   = parent::getInstance();	
    $MySQLiconn = $instance->getConnection();

    try {
        $stmt = $MySQLiconn->prepare("
            SELECT * FROM exam_sessions 
            WHERE examcode = :examcode 
              AND admissionno = :admissionno 
              AND token = :token
            LIMIT 1
        ");

        $stmt->bindValue(':examcode', $examcode);
        $stmt->bindValue(':admissionno', $admissionno);
        $stmt->bindValue(':token', $token);

        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        parent::DisConnect($MySQLiconn); // ✅ close before returning
        return $row;
    } catch (Exception $e) {
        echo "oops! " . $e->getMessage();
        return false;
    }
}



						//find exam sesssion


						//delete exam session

		public function deleteExamSession($examcode, $admissionno, $token) {
    $instance   = parent::getInstance();	
    $MySQLiconn = $instance->getConnection();

    try {
        $stmt = $MySQLiconn->prepare("
            DELETE FROM exam_sessions 
            WHERE examcode = :examcode 
              AND admissionno = :admissionno 
              AND token = :token
        ");

        $stmt->bindValue(':examcode', $examcode);
        $stmt->bindValue(':admissionno', $admissionno);
        $stmt->bindValue(':token', $token);

        $stmt->execute();

        parent::DisConnect($MySQLiconn); // ✅ disconnect before returning
        return true;
    } catch (Exception $e) {
        echo "oops! " . $e->getMessage();
        return false;
    }
}
	

						//delete exam session
						
						//delete exam scores
						
						
							public function resetScores($examcode,$admissionno){
			
					$instance = parent::getInstance();	
			  $MySQLiconn  = $instance->getConnection();
			   
			   $sql = 'DELETE 
					   FROM   scores
					   WHERE  examcode = :examcode  and admissionno = :admissionno';
	   
			   $statement = $MySQLiconn->prepare($sql);
			   $statement->bindValue(':examcode', $examcode);
			    $statement->bindValue(':admissionno', $admissionno);

	   
			   $statement->execute();  
				 parent::DisConnect( $MySQLiconn);
					
					}
					
						
						
						//delete scores
						
						
						

	 }


 ?>