<?php


$keyword=$_REQUEST['keyword'];
$cl=new MY_HTML();


error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

if (PHP_SAPI == 'cli')
	die('This example should only be run from a Web Browser');

/** Include PHPExcel */

require_once 'Classes/PHPExcel.php';
if(preg_match("/^windows/i",strtolower(php_uname()))){PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);}

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// My data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','TELKOMCEL')
            ->setCellValue('A2','Employee List')
            ->setCellValue('A3','Reported By : '.$_SESSION['nik']);

//  set format cell
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);


// Print Header
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A6','No')
            ->setCellValue('B6','NIK GROUP')
            ->setCellValue('C6','NIK ORIGINAL')
            ->setCellValue('D6','NAMA LENGKAP')
            ->setCellValue('E6','EMPLOYEE GROUP')
            ->setCellValue('F6','EMPLOYEE SUB GROUP')
            ->setCellValue('G6','PERSONAL AREA')
            ->setCellValue('H6','PERSONAL SUB AREA')
            ->setCellValue('I6','OBJIDPOSISI')
            ->setCellValue('J6','NAMA POSISI')
            ->setCellValue('K6','ABBR POSISI')
            ->setCellValue('L6','OBJIDUNIT')
            ->setCellValue('M6','ABBR UNIT')
            ->setCellValue('N6','NAMA UNIT')
            ->setCellValue('O6','LONG UNIT')
            ->setCellValue('P6','PERUSAHAAN')
            ->setCellValue('Q6','TANGGAL LAHIR')
            ->setCellValue('R6','JNS KELAMIN')
            ->setCellValue('S6','KOTA KELAHIRAN')
            ->setCellValue('T6','KEBANGSAAN')
            ->setCellValue('U6','STATUS PERNIKAHAN')
            ->setCellValue('V6','TANGGAL NIKAH')
            ->setCellValue('W6','AGAMA')
            ->setCellValue('X6','ALAMAT TETAP KARYAWAN')
            ->setCellValue('Y6','KOTA TETAP')
            ->setCellValue('Z6','KODEPOS TETAP')
            ->setCellValue('AA6','NEGARA TETAP')
            ->setCellValue('AB6','ALAMAT SMTR KARYAWAN')
            ->setCellValue('AC6','KOTA SEMENTARA')
            ->setCellValue('AD6','KODEPOS SEMENTARA')
            ->setCellValue('AE6','NEGARA SEMENTARA')
            ->setCellValue('AF6','BAND POSISI')
            ->setCellValue('AG6','HANDPHONE')
            ->setCellValue('AH6','EMAIL')
            ->setCellValue('AI6','KODE PA')
            ->setCellValue('AJ6','OBJIDPOSISI PARENT')
            ;

// === Merge Cells
//$objPHPExcel->getActiveSheet()->mergeCells('G5:H5');
//$objPHPExcel->getActiveSheet()->mergeCells('I5:J5');

$objPHPExcel->getActiveSheet()->getStyle('A6:AJ6')->getFont()->getColor()->setRGB('ffffff');
// Worksheet Name
$objPHPExcel->getActiveSheet()->setTitle('EmployeeInfo_'.date("Ym"));

// === Set Background Cell
$objPHPExcel->cellColor("A6:AJ6","938e8e");

// === set Column Width A
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
// === Set auto COlumn width
foreach(range('B','AJ') as $columnID)
{
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

$qry="select id,nikgroup,nik,fullname,empgroup,empsubgroup,personalarea,personalsubarea,objidposition,
             positionname,abbrposition,idsubunit,objidunit,abbrunit,unitname,subunitname,companyname,birthday,gender,
             birthofplace,nation,married,marrieddate,religion,address,town,postalcode,regiondefault,addresstemp,
             towntemp,postalcodetemp,regiontemp,bandposition,hpnumber,email,codepa,objidpositionparent
      from ".DB_APORTIL.".tbl_employee
      where (nikgroup like '%$keyword%' or nik like '%$keyword%' or fullname like '%$keyword%' or positionname like '%$keyword%' 
             or empgroup like '%$keyword%' or unitname like '%$keyword%') 
            and isdeleted=0
      order by id asc";      
$rsrpt=$cl->selectquery($qry);
if($rsrpt)
{
    $i=7;$j=1;
    while($rw=$cl->data_row_array($rsrpt))
    {
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i,$j)
                                            ->setCellValue('B'.$i,$rw["nikgroup"])
                                            ->setCellValue('C'.$i,$rw["nik"])
                                            ->setCellValue('D'.$i,$rw["fullname"])
                                            ->setCellValue('E'.$i,$rw["empgroup"])
                                            ->setCellValue('F'.$i,$rw["empsubgroup"])
                                            ->setCellValue('G'.$i,$rw["personalarea"])
                                            ->setCellValue('H'.$i,$rw["personalsubarea"])
                                            ->setCellValue('I'.$i,$rw["objidposition"])
                                            ->setCellValue('J'.$i,$rw["positionname"])
                                            ->setCellValue('K'.$i,$rw["abbrposition"])
                                            ->setCellValue('L'.$i,$rw["objidunit"])
                                            ->setCellValue('M'.$i,$rw["abbrunit"])
                                            ->setCellValue('N'.$i,$rw["unitname"])
                                            ->setCellValue('O'.$i,$rw["subunitname"])
                                            ->setCellValue('P'.$i,$rw["companyname"])
                                            ->setCellValue('Q'.$i,$rw["birthday"])
                                            ->setCellValue('R'.$i,$rw["gender"])
                                            ->setCellValue('S'.$i,$rw["birthofplace"])
                                            ->setCellValue('T'.$i,$rw["nation"])
                                            ->setCellValue('U'.$i,$rw["married"])
                                            ->setCellValue('V'.$i,$rw["marrieddate"])
                                            ->setCellValue('W'.$i,$rw["religion"])
                                            ->setCellValue('X'.$i,$rw["address"])
                                            ->setCellValue('Y'.$i,$rw["town"])
                                            ->setCellValue('Z'.$i,$rw["postalcode"])
                                            ->setCellValue('AA'.$i,$rw["regiondefault"])
                                            ->setCellValue('AB'.$i,$rw["addresstemp"])
                                            ->setCellValue('AC'.$i,$rw["towntemp"])
                                            ->setCellValue('AD'.$i,$rw["postalcodetemp"])
                                            ->setCellValue('AE'.$i,$rw["regiontemp"])
                                            ->setCellValue('AF'.$i,$rw["bandposition"])
                                            ->setCellValue('AG'.$i,$rw["hpnumber"])
                                            ->setCellValue('AH'.$i,$rw["email"])
                                            ->setCellValue('AI'.$i,$rw["codepa"])
                                            ->setCellValue('AJ'.$i,$rw["objidpositionparent"])
                                            ;
        $i+=1;$j+=1;
    }
}
$cl->clearrecordset($rsrpt);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a web browser (Excel2007)
//header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="RPT_EMPLOYEE'.date("Ymd").'.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
?>