<?php
error_reporting(0);
include('../dbinfo.php');
  $rsCandidate = mysql_query("SELECT a.*, 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%' AND a.date_created>='2014-07-20' order by a.candidate_id desc");

$reasonArray = array("EDUCATION / DISCIPLINE","GENDER","YEAR OF PASSING","GPA","NATIONALITY","AGE","CURRENTLY EMPLOYED");
  $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'];
$age = get_age($dob);

$national = $rwCandidate['nationality'];
if($national==1){
$nationality = "Omani";
}else if($national==2){
$nationality = "Expat";
}

$interviewer = $rwCandidate['owner'];
$rsIntv = mysql_query("SELECT * FROM user WHERE user_id='$interviewer'");
$rwIntv = mysql_fetch_array($rsIntv);
$intv_taken = $rwIntv['first_name'] . " ".$rwIntv['last_name'];


$appl_id = $rwCandidate['applicant_id'];
$rsApplied = mysql_query("SELECT * FROM candidate_as_applied WHERE applicant_id='$appl_id'");
$rwApplied = mysql_fetch_array($rsApplied);

$gend = $rwApplied['gender'];
if($gend ==1){
$agender = 'Male';
}else if($gend ==2){
$agender = 'Female';
}
$aregi = $rwApplied['region'];
$awila = $rwApplied['wilayat'];
$adisc = $rwApplied['discipline'];

$auniv = $rwApplied['university'];
if($auniv ==1){
$aunivers = 'Omani University';
}else if($auniv ==2){
$aunivers = 'Foreign University';
}

$aquali = $rwApplied['qualification'];
if($aquali ==1){
$qualif_app = 'Diploma';
}else if($aquali ==2){
$qualif_app = 'Bachelors';
}else if($aqualif ==3){
								$qualif_app = 'Lesser than secondary school';
								}else if($aqualif ==4){
								$qualif_app = 'Secondary school';
								}else if($aqualif ==5){
								$qualif_app = 'Higher Diploma';
								}else if($aqualif ==6){
								$qualif_app = 'Advanced Diploma';
								}else if($aqualif ==7){
								$qualif_app = 'Masters';
								}

$qry1 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='VERBAL COMMUNICATION'");								
$res1 = mysql_fetch_assoc($qry1);

$qry2 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='KNOWLEDGE OF Orpic'");								
$res2 = mysql_fetch_assoc($qry2);

$qry3 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='ATTITUDE TOWARDS WORK & LIFE'");								
$res3 = mysql_fetch_assoc($qry3);

$qry4 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='RELOCATE TO SOHAR'");								
$res4 = mysql_fetch_assoc($qry4);

$qry5 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='F2F SCORE'");								
$res5 = mysql_fetch_assoc($qry5);

$qry6 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='F2F RESULT'");								
$res6 = mysql_fetch_assoc($qry6);

foreach($reasonArray AS $val){
$qry7 = mysql_query("SELECT * FROM extra_field WHERE data_item_id='$candidate_id' AND field_name='$val'");
$res7 = mysql_num_rows($qry7);
if($res7!=0){
$reject[] = $val;
}
}

								
 		$datArray[$ref]['applicant']        = $rwCandidate['applicant_id'];
                $datArray[$ref]['datecreated']      = substr($rwCandidate['date_created'],0,10);
                $datArray[$ref]['fname']  = ucfirst($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']        = $age;
                $datArray[$ref]['nationality']        = $nationality;
                $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']   = round($rwCandidate['gpa'],2);
                $datArray[$ref]['interviewer']   = $intv_taken;
                $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]['change_status']   = utf8_encode($rwCandidate['change_status']);
		$datArray[$ref]['activity_remarks_update']   = $rwCandidate['activity_remarks_update'];
		$datArray[$ref]['test_date']   = $rwCandidate['test_date'];
		$datArray[$ref]['test_batch']   = $rwCandidate['test_batch'];
		$datArray[$ref]['test_score']   = $rwCandidate['test_score'];
		$datArray[$ref]['day']   = $rwCandidate['day'];
		$datArray[$ref]['engg_score']   = $rwCandidate['engg_score'];
		$datArray[$ref]['f2f_date']   = $rwCandidate['f2f_date'];
		$datArray[$ref]['f2f_batch']   = $rwCandidate['f2f_batch'];
		$datArray[$ref]['test_attd']   = $rwCandidate['test_attd'];
		$datArray[$ref]['intv_attd']   = $rwCandidate['intv_attd'];
		$datArray[$ref]['f2f_result']   = $rwCandidate['f2f_result'];
		$datArray[$ref]['rating']   = $rwCandidate['rating'];
		$datArray[$ref]['document']   = $rwCandidate['document'];
		
		$datArray[$ref]['afname']  = ucfirst($rwApplied['first_name']);
                $datArray[$ref]['amname'] = $rwApplied['middle_name'];
                $datArray[$ref]['alname']     = $rwApplied['last_name'];
		$datArray[$ref]['agender']   = $agender;
                $datArray[$ref]['adob']        = $rwApplied['dob'];
                $datArray[$ref]['anationalid']      = $rwApplied['national_id'];
                $datArray[$ref]['agsm']  = $rwApplied['gsm'];
                $datArray[$ref]['aemail'] = $rwApplied['email1'];
                $datArray[$ref]['aregion']     = getRegionName($aregi);
                $datArray[$ref]['awilayat']   = getWilayatName($awila);
                $datArray[$ref]['aquali']        = $qualif_app;
                $datArray[$ref]['adisc']      = getDisciplineName($adisc);
                $datArray[$ref]['ayear']  = $rwApplied['year'];
                $datArray[$ref]['aunivers'] = $aunivers;
                $datArray[$ref]['ainstitute']     = $rwApplied['institute'];
                $datArray[$ref]['agpa']   = round($rwApplied['gpa'],2);
                $datArray[$ref]['verbal']     = $res1['value'];
                $datArray[$ref]['knowledge']     = $res2['value'];
                $datArray[$ref]['attitude']     = $res3['value'];
                $datArray[$ref]['relocate']     = $res4['value'];
                $datArray[$ref]['f2fscore']     = $res5['value'];
                $datArray[$ref]['f2fres']     = $res6['value'];
                $datArray[$ref]['reason']     = implode(",",$reject);
                $datArray[$ref]['misc']   = $rwCandidate['notes'];
                //$datArray[$ref]['joborder_id']     = $rwCandidate['candidate_joborder_id'];
                $ref++;
                UNSET($reject);
                }

//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]="Nationality";

$headingarray[9]="National ID";

$headingarray[10]="GSM";

$headingarray[11]="Email";

$headingarray[12]="Region";

$headingarray[13]="Wilayat";

$headingarray[14]="Qualification";

$headingarray[15]="Discipline";

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

$headingarray[17]="University";

$headingarray[18]="Institute";

$headingarray[19]="GPA";

$headingarray[20]="Interviewer";

$headingarray[21]="Position Applying";

$headingarray[22]="Current Status";

$headingarray[23]="Change status to";

$headingarray[24]="Activity Remarks";

$headingarray[25]="Activity Remarks to Update";

$headingarray[26]="Final Comment";

$headingarray[27]="System Comment";

$headingarray[28]="Assessment Date";

$headingarray[29]="Assessment Batch";

$headingarray[30]="Attendance Status";

$headingarray[31]="Test Date";

$headingarray[32]="Test Batch";

$headingarray[33]="Test Score";

$headingarray[34]="Day";

$headingarray[35]="Engg score";

$headingarray[36]="F2F Date";

$headingarray[37]="F2F Batch";

$headingarray[38]="Test Attended";

$headingarray[39]="Interview Attended";

$headingarray[40]="F2F Result";

$headingarray[41]="Rating";

$headingarray[42]="Document";

$headingarray[43]="Fname_Applied";

$headingarray[44]="Mname_Applied";

$headingarray[45]="Lname_Applied";

$headingarray[46]="Gender_Applied";

$headingarray[47]="DOB_Applied";

$headingarray[48]="National ID_Applied";

$headingarray[49]="GSM_Applied";

$headingarray[50]="Email_Applied";

$headingarray[51]="Region_Applied";

$headingarray[52]="Wilayat_Applied";

$headingarray[53]="Qualification_Applied";

$headingarray[54]="Discipline_Applied";

$headingarray[55]="Year of Passing_Applied";

$headingarray[56]="University_Applied";

$headingarray[57]="Institute_Applied";

$headingarray[58]="GPA_Applied";

$headingarray[59]="Verbal Communication";

$headingarray[60]="Knowledge of Orpic";

$headingarray[61]="Attitude towards Work & Life";

$headingarray[62]="Relocate to Sohar";

$headingarray[63]="F2F Score";

$headingarray[64]="F2F Result";

$headingarray[65]="Reason for Reject";

$headingarray[66]="Misc Notes";

$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(27,27,10);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

//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);
 }
?>
