<?php
ini_set('max_execution_time', 600);

//require("../database.php");
$con = mysqli_connect('localhost','niqsorgn_portal','@Xchange69!') or die("Could not Connect My Sql");
mysqli_select_db($con,"niqsorgn_exams")  or die("Could connect to Database");

$con2 = mysqli_connect('localhost','niqsorgn_portal','@Xchange69!') or die("Could not Connect My Sql");
mysqli_select_db($con2,"niqsorgn_pota1969")  or die("Could 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=Probation Full.xls");

   
if(isset($_GET["t"]))
{   
    if($_GET["t"]=="1"){ $stat = "Submitted"; }else{ $stat = "Pending"; }	//status to fecth
    
    //required table
    $regtbl = $_GET["exam"]."_application";					//reg table 
    $sponsortbl = $_GET["exam"]."_sponsor";					//sponsor 
    //$employertbl = $_GET["exam"]."_employer";					//employer
    $educationtbl = $_GET["exam"]."_education";					//education
    $bodytbl = $_GET["exam"]."_body";
    echo "<td>  </td>";						//logbook
    //$logbooktbl = $_GET["exam"]."_logbook";					//logbook
    //$gdetbl = $_GET["exam"]."_gde";						//gde
    $experiencetbl = $_GET["exam"]."_experience";				//experience
    $exemptiontbl = $_GET["exam"]."_exemption";					//exemption
    
    
    
    $myd = $_GET["myd"];

    //$q = "Select * from $regtbl WHERE regSet='$myd' and status='$stat'";
    $q = "Select * from $regtbl WHERE regSet='$myd'";
    
    $qry=mysqli_query($con2, $q);
    
	
     echo '<table width="100%" border="1" cellspacing="0" cellpadding="5">';
     echo '<tr><td colspan="7"> GDE Full - '.$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><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>Education</b> </td> <td><b>Professional Body</b> </td> <td><b>Logbook</b> </td> <td> <b> Experience </b></td> <td> <b> Exemption </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>$memberEmail </td><td> $memberPhone1 <br>  $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 getLogbook($id, $logbooktbl, $con2);
      	//echo getGde($id, $logbooktbl, $con2);
      	echo getExperience($id, $experiencetbl, $con2);
      	echo getExemption($id, $exemptiontbl, $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 .= strtoupper($degree)." ".strtoupper($course)." - ".strtoupper($institution).": (".$startYear." - ".$endYear.") <br><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 .= strtoupper($currentGrade)." - ".strtoupper($institution).": (".$regRoute." - ".$regYear.") <br><br>";
       
    }
    $details .= " </td>";
    return $details;

}






//gde
function getGde($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> ".$gdeNo."</td><td> ".$gdeCentre." </td><td> ".$gdeMonth." ".$gdeYear."</td>";
       return $details;
    }
}


//professional experience
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 .= strtoupper($position).": ".strtoupper($employer)." - ".strtoupper($nature)." : (".$startDate." to ".$endDate.") <br><br>";
       
    }
    $details .= " </td>";
    return $details;

}



function getExemption($id, $tbl, $con)
{
		
		$q2 = "select * from gdef_exemption where applicant_id=$id";
		$r2 = mysqli_query($con, $q2);
		
		//extract($row2);
		//echo print_r($row2);
		
		 $details = "<td>  ";
		while($row2=mysqli_fetch_assoc($r2))
		{
			extract($row2);
			
			for($sali=6; $sali<=10; $sali++)
			{
			  $mycell = "course".$sali;
			  if($row2[$mycell]=="1")
			  { 
			    //get course
			    $q = "select * from exemptioncourse where exam='GDE' and id=$sali";
			   
			    $r = mysqli_query($con, $q);
			    $row = mysqli_fetch_assoc($r);
			    extract($row);
			    $details .= strtoupper($course).'<br><br>'; 
			  }
			}
		}	
	        $details .= " </td>";
   		return $details;
		
}



?>
