<?php
include('../dbinfo.php');

$rsWilayat = mysql_query("SELECT * FROM cm_wilayat");
    $wilayat_cnt = mysql_num_rows($rsWilayat);
    while($rwWilayat = mysql_fetch_array($rsWilayat)){
    $wilayat_name = $rwWilayat['wilayat'];
    $wilayat_id = $rwWilayat['id'];
    $wilayatArray[$wilayat_id] = $wilayat_name;
    }

$rsJobTitle = mysql_query("SELECT * FROM joborder WHERE status='Active'");
$ref = 0;
while($rwJobTitle = mysql_fetch_array($rsJobTitle)){
$joborder_id = $rwJobTitle['joborder_id'];
$jobArray[] = $rwJobTitle['title'];


foreach($wilayatArray as $k=>$v){
    $qry19 = mysql_query("SELECT * FROM candidate a, candidate_joborder b WHERE b.joborder_id='$joborder_id' AND b.site_id=1 AND b.date_created NOT LIKE '0000%' AND a.wilayat='$k' AND a.candidate_id=b.candidate_id AND a.site_id=1");
   $res19 = mysql_num_rows($qry19);
   $datArray[$ref][] = $res19;
   $totalArray[$ref] += $res19;
   
   $total_overall += $res19;
   
   $qry20 = mysql_query("SELECT * FROM candidate a, candidate_joborder b WHERE b.joborder_id!='0' AND b.site_id=1 AND b.date_created NOT LIKE '0000%' AND a.wilayat='$k' AND a.candidate_id=b.candidate_id AND a.site_id=1");
   $res20 = mysql_num_rows($qry20);
   $totArray[$k]=$res20;
   }
   $ref++;
}
      
      
require_once "Spreadsheet/Excel/Writer.php"; 

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

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

$countJob=count($jobArray);

for($k=5;$k<=$countJob+5;$k++)

{

$m=$k-5;
 
$sheet->write($k,0,$jobArray[$m],$alphaDataFormat);

}

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

$count=count($datArray);

$headingarray[0]="Wilayat";

$p=1;
foreach($wilayatArray as $key=>$val){
$headingarray[$p]=$val;
$p++;
}

$headcount = count($headingarray);
$s = $headcount +1;
$headingarray[$s]="Total"; 

$countTot=count($totalArray);

for($c=5;$c<=$countTot+5;$c++)

{

$d=$c-5;
 
$sheet->write($c,$s-1,$totalArray[$d],$alphaDataFormat);

}

$sheet->write(11,0,"Total",$alphaDataFormat);
$sheet->write(11,$s-1,$total_overall,$alphaDataFormat);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


//printing the heading of the table

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


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

{

$l=$i-5;

$sheet->writeRow($i,1,$datArray[$l],$alphaDataFormat);

}

/*$count_tot = count($totArray);
for($x=14;$x<=$count_tot+14;$x++)

{

$y=$x-14;

$sheet->write($x,1,$totArray[$y],$alphaDataFormat);

}*/
$f=1;
foreach($totArray as $g=>$h){
$sheet->write(11,$f,$h,$alphaDataFormat);
$f++;
}

/*$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();  
?>