<?php

//ini_set('max_execution_time', 600);

//require("../database.php");
$con = mysqli_connect('localhost','niqsorgn_portal','@Xchange69!') or die("Technical Error - 1001");
$db = mysqli_select_db($con,"niqsorgn_exams")  or die("Could not connect to Database");

$con2 = mysqli_connect('localhost','niqsorgn_portal','@Xchange69!') or die("Technical Error - 1001");
$db = mysqli_select_db($con2,"niqsorgn_pota1969")  or die("Could not connect to Database");


//header("Content-type: application/vnd-ms-excel");
 
// Defines the name of the export file "codelution-export.xls"
//header("Content-Disposition: attachment; filename=Mature_Register.xls");


    //print_r($_GET);

//die();   
if(isset($_GET["t"]))
{   
    if($_GET["t"]=="1"){ $stat = "Submitted"; }else{ $stat = "Pending"; }	//status to fecth
    
    //required table

    $regtbl = "phd_application";					//reg table 
    $sponsortbl = "phd_sponsor";					//sponsor 
    $employertbl = "phd_employer";					//employer
    $educationtbl = "phd_education";					//education
    //$oleveltbl = "phd_olevel";					//olevel
    $bodytbl = "phd_body";						//tpc
    $experiencetbl = "phd_experience";					//experience

  
    
    $myd = $_GET["myd"];

    $q = "Select * from $regtbl WHERE regSet='$myd'";
    echo $q;
    
    //$q = "Select * from $regtbl WHERE regSet='$myd' and status='$stat'";
    
    $qry=mysqli_query($con2, $q);
    
	
     echo '<table width="100%" border="1" cellspacing="0" cellpadding="5">';
     echo '<tr><td colspan="7"> '.$myd.'</td></tr>';
    
      	echo "<tr><td align='center'> <b>SN</b> </td><td> <b> Member No. </b> </td><td> <b>Name </b> </td><td> <b>Email </b> </td><td> <b>Phone </b> </td><td align='center'> <b>Sex </b> </td><td> <b> Grade </b> </td><td> <b> Chapter</b> </td><td> <b> Diet </b>  </td><td> <b>Centre</b> </td><td align='center'> <b>PIN</b> </td><td align='center'> <b>Reg. No.</b> </td> <td><b>Division</b> </td><td><b>Status</b> </td><td><b>Sponsor No</b> </td> <td><b>Sponsor Name</b> <td><b>Sponsor Grade</b> </td> </td> <td><b>Sponsor Email</b> </td> <td><b>Sponsor Reg. Date</b> </td> <td><b>Employer</b> </td> <td><b>Employer Business</b> </td> <td><b>Employer Address</b> </td> <td><b>Position</b> </td> <td><b>Date Employed</b> </td> <td><b>Education</b> </td> <td><b>Professional Body</b> </td>  </tr>";
      
     $sn=1;
     while($row=mysqli_fetch_assoc($qry))
     {
     	extract($row);
     	
     	print_r($row);
     	//die();
	 
      	echo "<tr><td align='center'> $sn </td><td> $memberNo </td><td>$memberSurname  $memberOthernames </td><td align='center'> $memberEmail </td><td align='center'> $memberPhone1, $memberPhone2 </td><td align='center'> $memberGender </td><td> ".getGrade($memberGrade)." </td><td> $memberChapter </td><td> $regSet </td><td> $examCentre </td><td align='center'> $password  </td><td align='center'> $uniqueId</td><td> $division </td><td> ".strtoupper($status)." </td>";
      	
      	echo getSponsor($id, $sponsortbl, $con2);
      	echo getEmployer($id, $employertbl, $con2);
      	echo getEducation($id, $educationtbl, $con2);
      	echo getBody($id, $bodytbl, $con2);
      	
      	
      	echo "</tr>";
      	
      	$sn++;
	}


}


//echo '<script>window.close();</script>';


//functions
//employer
function getEmployer($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
        
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       $details = "<td> ".$employer." </td><td> ".$nature." </td><td> ".$address." </td><td> ".$position." </td><td> ".$employmentDate." </td>";
       return $details;
    }
}


//sponsor
function getSponsor($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
        
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       $details = "<td> ".$memberNo." </td><td> ".$memberSurname." ".$memberOthernames."</td> <td> ".$memberGrade." </td><td> ".$memberEmail." </td><td> ".$memberRegDate." </td>";
       return $details;
    }
}

//grade
function getGrade($memberGrade)
{
  switch($memberGrade)
	{
		case 1: $memberGrade="FELLOW";
		break;
		
		case 2: $memberGrade="MEMBER";
		break;
		
		case 3: $memberGrade="PROBATIONER";
		break;
		
		case 4: $memberGrade="TECHNICIAN";
		break;
		
		case 5: $memberGrade="STUDENT";
		break;
		
		case 6: $memberGrade="PRACTICING FIRM";
		break;
		
		case 7: $memberGrade="GRADUATE";
		break;
		
		case 8: $memberGrade="STATE CHAPTER";
		break;
		
		case 9: $memberGrade="PAST PRESIDENT";
		break;
		
		default: $memberGrade = "";
		
	}
	
	return $memberGrade;
}


//education
function getEducation($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
        
    $details = "<td> ";
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       
       $details .= $degree." ".$course." - ".strtoupper($institution).": (".$dateCompleted.") <br>";
       
    }
    $details .= " </td>";
    return $details;

}


//professional body
function getBody($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
        
    $details = "<td> ";
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       $details .= $position." - ".$employer.": (".$startDate." - ".$endDate.") <br> $duties <br><br>";
       
    }
    $details .= " </td>";
    return $details;
}



//logbook
function getLogbook($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
        
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       $details = "<td> ".$logbookMonth." ".$logbookYear." </td>";
       return $details;
    }
}



//gde
function getTpc($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
    
        
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       $details = "<td> ".$tpcNo."</td><td> ".$tpcCentre." </td><td> ".$tpcMonth." ".$tpcYear."</td>";
       return $details;
       
    }
}


//education
function getExperience($id, $tbl, $con)
{
    $qf = "Select * from $tbl WHERE applicant_id=$id";
    $qryf = mysqli_query($con, $qf);
        
    $details = "<td> ";
    while($rowf = mysqli_fetch_array($qryf))
    {
       extract($rowf);
       
       $details .= $degree." ".$course." - ".strtoupper($institution).": (".$startYear." - ".$endYear.") <br>";
       
    }
    $details .= " </td>";
    return $details;

}


function getExemption($id, $tbl, $con, $etype)
{
		$q = "select * from exemption where exam='$etype'";
		$r = mysqli_query($con, $q);
		
		if($etype == "TPC"){ $cx = 1; }else{ $cx = 6; }
		
		 
		//check old option
		$q2 = "select * from tpcf_exemption where applicant_id=$applicant_id";
		$r2 = mysqli_query($con, $q2);
		$row2 = mysqli_fetch_array($r2);
		
		//extract($row2);
		//echo print_r($row2);
		
			echo "<table width='100%' border='1' cellspacing='0'>";
		while($row=mysqli_fetch_assoc($r))
		{
			$gname = "ex".$cx;
			extract($row);
			$mycell = "course".$cx;

			if($row2[$mycell]=="1"){ echo '<tr><td> &nbsp; '.strtoupper($course).'</td></tr>'; }
			$cx++;
		}	echo "</table>";
		
}

?>
