<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2013 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.9, 2013-06-02
 */

/** Error reporting */

$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('Asia/Tokyo');

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

// === Init Vars
$ismonthly=$_REQUEST['ismonthly'];
$month=$_REQUEST['month'];
$period=$_REQUEST['period'];
$idemployee=$_REQUEST['idemployee'];
$idsubunit=$_REQUEST['idsubunit'];
            
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// My data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','Employee Leave Reporting')
            ->setCellValue('A2','Human Capital '.$_SESSION['employeename'])
            ->setCellValue('A3','Date Reporting : '.date("d/m/Y H:i:s"))
            ->setCellValue('A4','Periode : '.$cl->FN_GETMONTHNAME($month)." $period");


if($idsubunit)
{
    $subunitname=$cl->fn_get_fieldvalue("subunitname","select subunitname from ".DB_APORTIL.".tbl_employee_subunit where id='$idsubunit'");
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A5','Sub Unit : '.$subunitname);
}
            

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


// Print Header
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A7','No')
            ->setCellValue('B7','Name')
            ->setCellValue('C7','Nik')
            ->setCellValue('D7','Position')
            ->setCellValue('E7','Status')
            ->setCellValue('F7','Leave Category')
            ->setCellValue('G7','Submit Date')
            ->setCellValue('H7','Start Date')
            ->setCellValue('I7','End Date')
            ->setCellValue('J7','Leave Days')
            ->setCellValue('K7','Approval 1')
            ->setCellValue('L7','Approval 2')
            ->setCellValue('M7','Excess')
            ;
// Worksheet Name
$objPHPExcel->getActiveSheet()->setTitle('LEAVEINFO_'.date("Ym"));


$objPHPExcel->getActiveSheet()->getStyle('A7:M7')->getFont()->getColor()->setRGB('ffffff');
// === Set Background Cell
$objPHPExcel->cellColor("A7:M7","938e8e");

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

// Data
$swhere="";
if($idemployee){$swhere="e.id='$idemployee' and ";}
if($idsubunit){$swhere.="e.idsubunit='$idsubunit' and ";}
if($month){$swhere.="month(d.startdate)='$month' and ";}
$qry="select e.id idemployee,e.nik,e.fullname,e.positionname,d.id idleavedata,d.leavetype,d.startdate,d.enddate,d.hcmdurationdays,
             d.fullnameapproval approval1,d.fullnameapproval2 approval2,d.credit,d.status
      from tbl_employee e inner join tbl_hcm_leavedata d on e.id=d.idemployee
      where $swhere d.isdeleted=0 and d.status>=-1";
$rsrpt=$cl->selectquery($qry);
if($rsrpt)
{
    $i=8;$j=1;
    while($rw=$cl->data_row_array($rsrpt))
    {
        $qry="select description from tbl_hcm_leavestatus where status='".$rw['status']."' and isdeleted=0";
        $statusdescription=$cl->fn_get_fieldvalue("description",$qry);
        
        $qry="select idt dates from tbl_hcm_leavedatahistory where idheader='".$rw['idleavedata']."' and status=0 and isdeleted=0 order by id asc limit 1";
        $sd=$cl->fn_get_fieldvalue("dates",$qry);
        $submitdate="";
        if($sd&&(int)$sd!=0){$submitdate=date("Y-m-d H:i:s",strtotime($sd));}        
        
        $qry="select idt dates from tbl_hcm_leavedatahistory where idheader='".$rw['idleavedata']."' and status=2 and isdeleted=0 order by id asc limit 1";
        $sd=$cl->fn_get_fieldvalue("dates",$qry);
        $appdate1="";
        if($sd&&(int)$sd!=0&&$rw['approval1']){$appdate1=date("Y-m-d H:i:s",strtotime($sd));}        
        
        $qry="select idt dates from tbl_hcm_leavedatahistory where idheader='".$rw['idleavedata']."' and status=5 and isdeleted=0 order by id asc limit 1";
        $sd=$cl->fn_get_fieldvalue("dates",$qry);
        $appdate2="";
        if($sd&&(int)$sd!=0){$appdate2=date("Y-m-d H:i:s",strtotime($sd));}        

        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i,$j)
                                            ->setCellValue('B'.$i,$rw["fullname"])
                                            ->setCellValue('C'.$i,$rw["nik"])
                                            ->setCellValue('D'.$i,$rw["positionname"])
                                            ->setCellValue('E'.$i,$statusdescription)
                                            ->setCellValue('F'.$i,$rw["leavetype"])
                                            ->setCellValue('G'.$i,$submitdate)
                                            ->setCellValue('H'.$i,date("d/m/Y",strtotime($rw['startdate'])))
                                            ->setCellValue('I'.$i,date("d/m/Y",strtotime($rw['enddate'])))
                                            ->setCellValue('J'.$i,$rw['hcmdurationdays'])
                                            ->setCellValue('K'.$i,$rw['approval1']." ".$appdate1)
                                            ->setCellValue('L'.$i,$rw['approval2']." ".$appdate2)
                                            ->setCellValue('M'.$i,$rw['credit'])
                                            ;
        $colors="000000";
        if($rw['credit']<0){$colors="FF0000";}
        $phpColor = new PHPExcel_Style_Color();
        $phpColor->setRGB($colors);

        $objPHPExcel->getActiveSheet()->getStyle('M'.$i)->getFont()->setColor($phpColor);
        $i+=1;$j+=1;
        $j+=1;
    }
    $rowcount=$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="HCM_LEAVEINFO.xls"');
header('Cache-Control: max-age=0');

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