<?php
include('../dbinfo.php');
  $rsCandidate = mysql_query("SELECT a.applicant_id, a.candidate_id, a.date_created, a.first_name, a.middle_name, a.last_name, a.gender, a.dob, a.age,a.national_id, a.gsm, a.region, a.wilayat, a.email1, a.qualification, a.discipline, a.year, a.university, a.institute, a.gpa, b.joborder_id, b.status, b.final_comment, b.final_comment2 FROM candidate a, candidate_joborder b WHERE a.candidate_id=b.candidate_id AND a.site_id=1 AND a.site_id=b.site_id AND b.joborder_id!=0 AND a.date_created NOT LIKE '0000%' order by a.candidate_id desc");
  $ref = 0;
  while($rwCandidate = mysql_fetch_array($rsCandidate )){
  $candidate_id = $rwCandidate['candidate_id'];
  $joborder_id = $rwCandidate['joborder_id'];
  
$quali = $rwCandidate['qualification'];
if($quali ==1){
$qualif = 'Diploma';
}else if($quali ==2){
$qualif = 'Bachelors';
}
																
$gen = $rwCandidate['gender'];
if($gen ==1){
$gender = 'Male';
}else if($gen ==2){
$gender = 'Female';
}
								
$univ = $rwCandidate['university'];
if($univ ==1){
$univers = 'Omani University';
}else if($univ ==2){
$univers = 'Foreign University';
}	
								
$disc = $rwCandidate['discipline'];
$regi = $rwCandidate['region'];
$wila = $rwCandidate['wilayat'];

/*$rsRemark = mysql_query("SELECT * FROM activity WHERE joborder_id='$joborder_id' AND data_item_id='$candidate_id' AND site_id=1");
$rwRemark = mysql_fetch_assoc($rsRemark);
$can_id = $rwRemark['data_item_id'];
$remark = $rwRemark['notes'];*/
/*echo "<pre>";print_r($remark);die;
$remarks = implode(",",$remark);*/

$cand_status = $rwCandidate['status'];
$rsStatus = mysql_query("SELECT * FROM candidate_joborder_status WHERE candidate_joborder_status_id='$cand_status' AND is_enabled=1");
$rwStatus = mysql_fetch_assoc($rsStatus );
$status = $rwStatus['short_description'];

$rsAttendance = mysql_query("SELECT * FROM assessment_attendance WHERE candidate_id='$candidate_id'");
$rwAttendance = mysql_fetch_array($rsAttendance);

$dob = $rwCandidate['dob'];

 		$datArray[$ref]['applicant']        = $rwCandidate['applicant_id'];
                $datArray[$ref]['datecreated']      = substr($rwCandidate['date_created'],0,10);
                $datArray[$ref]['fname']  = $rwCandidate['first_name'];
                $datArray[$ref]['mname'] = $rwCandidate['middle_name'];
                $datArray[$ref]['lname']     = $rwCandidate['last_name'];
                $datArray[$ref]['gender']   = $gender;
                $datArray[$ref]['dob']        = $rwCandidate['dob'];
                $datArray[$ref]['age']        = $rwCandidate['age'];
                $datArray[$ref]['nationalid']      = $rwCandidate['national_id'];
                $datArray[$ref]['gsm']  = $rwCandidate['gsm'];
                $datArray[$ref]['email'] = $rwCandidate['email1'];
                $datArray[$ref]['region']     = getRegionName($regi);
                $datArray[$ref]['wilayat']   = getWilayatName($wila);
                $datArray[$ref]['quali']        = $qualif;
                $datArray[$ref]['disc']      = getDisciplineName($disc);
                $datArray[$ref]['year']  = $rwCandidate['year'];
                $datArray[$ref]['univers'] = $univers;
                $datArray[$ref]['institute']     = $rwCandidate['institute'];
                $datArray[$ref]['gpa']   = $rwCandidate['gpa'];
                $datArray[$ref]['position'] = getJobName($joborder_id);
                $datArray[$ref]['status']     = $status;
                $datArray[$ref]['remark']   = getActivity($joborder_id,$candidate_id);
	$datArray[$ref]['finalcomment']   = $rwCandidate['final_comment'];
		$datArray[$ref]['finalcomment2']   = $rwCandidate['final_comment2'];
		$datArray[$ref]['ass_date']   = $rwAttendance['assessmentdate'];
		$datArray[$ref]['ass_batch']   = $rwAttendance['assessmentbatch'];
		$datArray[$ref]['att_status']   = $rwAttendance['attendanceconfirmed'];
                //$datArray[$ref]['joborder_id']     = $rwCandidate['candidate_joborder_id'];
                $ref++;
                }

//echo "<pre>";print_r($datArray);die;
      
require_once "Spreadsheet/Excel/Writer.php"; 

$xls =& new Spreadsheet_Excel_Writer(); // Create an instance 

$xls->send("Candidate_Master_Report.xls"); // Send HTTP headers to tell the browser what's coming 

$titleFormat =& $xls->addFormat(); // Create a format object 

$titleFormat->setBold();// Set the text to bold 

$titleFormat->setAlign('center'); 	// Set the alignment to the special merge value 

$titleFormat->setSize('14');// Set the text size 

$titleFormat->setFgcolor('silver'); 

$alphaTitleFormat =& $xls->addFormat(array('bottom_color' => 'blue')); 

$alphaTitleFormat->setBold(); 

$alphaTitleFormat->setFgcolor('silver');

$alphaTitleFormat->setTextWrap();

$alphaDataFormat = & $xls->addFormat(array('text_h_align' => 2));

$alphaDataFormat->setAlign('left');

$alphaDataFormat->setBorder(0);

$alphaDataFormat->setTextWrap();

$aggFormat =& $xls->addFormat(); // Create a format object 

$aggFormat->setAlign('left');

$aggFormat->setFgcolor('silver');

$aggFormat->setTextWrap();

$aggFormat->_text_v_align = 1;

$aggheadFormat =& $xls->addFormat(); // Create a format object 

$aggheadFormat->setAlign('left');

$aggheadFormat->setBold();// Set the text to bold

$aggheadFormat->setFgcolor('silver');

$aggheadFormat->setTextWrap();

$aggheadFormat->_text_v_align = 1;


$sheet =& $xls->addWorksheet('Candidate Master Report'); // Add a worksheet to the file, returning an object to add data to

//prints title

$sheet->_orientation = 0;//landscape

$sheet->_margin_left     = 0.5;

$sheet->_margin_right    = 0.5;

$sheet->_margin_top      = 0.5;

$sheet->_margin_bottom   = 0.5;



$sheet->write(0,0,'Candidate Master Report',$titleFormat);

$sheet->mergeCells(0,0,0,10);

$count=count($datArray);

$headingarray[0]="Applicant ID";

$headingarray[1]="Registered On";

$headingarray[2]="Fname";

$headingarray[3]="Mname";

$headingarray[4]="Lname";

$headingarray[5]="Gender";

$headingarray[6]="DOB";

$headingarray[7]="Age";

$headingarray[8]="National ID";

$headingarray[9]="GSM";

$headingarray[10]="Email";

$headingarray[11]="Region";

$headingarray[12]="Wilayat";

$headingarray[13]="Qualification";

$headingarray[14]="Discipline";

$headingarray[15]="Year of Passing";

$headingarray[16]="University";

$headingarray[17]="Institute";

$headingarray[18]="GPA";

$headingarray[19]="Position Applying";

$headingarray[20]="Current Status";

$headingarray[21]="Activity Remarks";

$headingarray[22]="Final Comment";

$headingarray[23]="System Comment";

$headingarray[24]="Assessment Date";

$headingarray[25]="Assessment Batch";

$headingarray[26]="Attendance Status";

//$headingarray[22]="Joborder Id";

$sheet->setColumn(0,0,15);

$sheet->setColumn(1,1,15);

$sheet->setColumn(2,2,15);

$sheet->setColumn(3,3,15);

$sheet->setColumn(4,4,15);

$sheet->setColumn(5,5,15);

$sheet->setColumn(6,6,15);

$sheet->setColumn(7,7,15);

$sheet->setColumn(8,8,15);

$sheet->setColumn(9,9,25);

$sheet->setColumn(10,10,25);

$sheet->setColumn(11,11,15);

$sheet->setColumn(12,12,15);

$sheet->setColumn(13,13,25);

$sheet->setColumn(14,14,15);

$sheet->setColumn(15,15,20);

$sheet->setColumn(16,16,30);

$sheet->setColumn(17,17,15);

$sheet->setColumn(18,18,30);

$sheet->setColumn(19,19,15);

$sheet->setColumn(20,20,15);

$sheet->setColumn(21,21,30);

$sheet->setColumn(22,22,30);

$sheet->setColumn(23,23,30);

$sheet->setColumn(24,24,15);

$sheet->setColumn(25,25,15);

$sheet->setColumn(26,26,10);

//$sheet->setColumn(22,22,10);

//printing the heading of the table

$sheet->writeRow(2,0,$headingarray,$alphaTitleFormat);



for($i=4;$i<=$count+4;$i++)

{

$l=$i-4;
 
$sheet->writeRow($i,0,$datArray[$l],$alphaDataFormat);

}


$sheet->writeCol(2,0,$xaggarray[0],$aggheadFormat);

$sheet->writeCol(2,1,$xaggarray[1],$aggFormat);

$sheet->writeCol(2,2,$xaggarray[2],$aggheadFormat);

$sheet->writeCol(2,3,$xaggarray[3],$aggFormat);

$sheet->writeCol(2,4,$xaggarray[4],$aggheadFormat);

$sheet->writeCol(2,5,$xaggarray[5],$aggFormat);

/*$sheet->writeCol(3,0,$xaggarray[6],$aggheadFormat);

$sheet->writeCol(3,1,$xaggarray[7],$aggFormat);

$sheet->writeCol(3,2,$xaggarray[8],$aggheadFormat);

$sheet->writeCol(3,3,$xaggarray[9],$aggFormat);*/

$freeze = array(4,0,4,0); 

$sheet->freezePanes($freeze);

$xls->close();  

function getRegionName($id){
$rsReg = mysql_query("SELECT * FROM cm_region WHERE id='$id'");
$rwReg = mysql_fetch_array($rsReg);
$region = $rwReg['region'];
return $region;
}

function getDisciplineName($id){
$rsDisc = mysql_query("SELECT * FROM cm_discipline WHERE id='$id'");
$rwDisc = mysql_fetch_assoc($rsDisc);
$discipline = $rwDisc['discipline'];
return $discipline;
}

function getWilayatName($id){
$rsWila = mysql_query("SELECT * FROM cm_wilayat WHERE id='$id'");
$rwWila = mysql_fetch_assoc($rsWila);
$wilayat = $rwWila['wilayat'];
return $wilayat;
}

function getJobName($id){
$rsJobOrderName = mysql_query("SELECT * FROM joborder WHERE joborder_id='$id'");
$rwJobOrderName = mysql_fetch_assoc($rsJobOrderName);
$jobordername = $rwJobOrderName['title'];
return $jobordername;
}

function getActivity($joborder_id,$candidate_id){
$rsRemark = mysql_query("SELECT * FROM activity WHERE joborder_id='$joborder_id' AND data_item_id='$candidate_id' AND site_id=1");
while($rwRemark = mysql_fetch_array($rsRemark)){
$remark[] = $rwRemark['notes'];
}
$remarks = @implode("\n",$remark);
return $remarks;
}

function get_age($birth_date){
 return floor((time() - strtotime($birth_date))/31556926);
 }
?>
