<?php
include('../dbinfo.php');
 $rsAttendance = mysql_query("SELECT * FROM assessment_attendance");
  $ref = 0;
  while($rwAttendance = mysql_fetch_array($rsAttendance)){
  $name = $rwAttendance['fname'] . " " . $rwAttendance['mame'] . " " . $rwAttendance['lname'];
  
 		$datArray[$ref]['applicant']        = $rwAttendance['applicant_id'];
                $datArray[$ref]['fname']      	    = $rwAttendance['fname'];
                $datArray[$ref]['mname']      	    = $rwAttendance['mname'];
                $datArray[$ref]['lname']            = $rwAttendance['lname'];
                $datArray[$ref]['nationalid']       = $rwAttendance['nationalid'];
                $datArray[$ref]['email']            = $rwAttendance['email'];
                $datArray[$ref]['assessmenbatch']   = $rwAttendance['assessmentbatch'];
                $datArray[$ref]['assessmentdate']   = $rwAttendance['assessmentdate'];
                $datArray[$ref]['assessmentvenue']  = $rwAttendance['assessmentvenue'];
                $datArray[$ref]['assessmenttime']   = $rwAttendance['assessmenttime'];
                $datArray[$ref]['attendanceconfirmed']  = $rwAttendance['attendanceconfirmed'];

                $ref++;
                }


      
require_once "Spreadsheet/Excel/Writer.php"; 

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

$xls->send("Attendance_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('Attendance 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,'Attendance Report',$titleFormat);

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

$count=count($datArray);

$headingarray[0]="Applicant ID";

$headingarray[1]="First name";

$headingarray[2]="Middle name";

$headingarray[3]="Last name";

$headingarray[4]="National ID";

$headingarray[5]="Email";

$headingarray[6]="Assessment Batch";

$headingarray[7]="Assessment Date";

$headingarray[8]="Assessment Venue";

$headingarray[9]="Assessment Time";

$headingarray[10]="Attendance Confirmed";


$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);


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