<?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
$month1=$_REQUEST['month1'];
$period1=$_REQUEST['period1'];
$month2=$_REQUEST['month2'];
$period2=$_REQUEST['period2'];

$idemployee=$_REQUEST['idemployee'];
$idunit=$_REQUEST['idunit'];
$idsubunit=$_REQUEST['idsubunit'];
            
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

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

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

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


// Print Header
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A7','No')
            ->setCellValue('B7','Nik')
            ->setCellValue('C7','Employee Name')
            ->setCellValue('D7','Position')
            ->setCellValue('E7','Band')
            ->setCellValue('F7','Hari')
            ->setCellValue('G7','Tgl')
            ->setCellValue('H7','Start')
            ->setCellValue('I7','End')
            ->setCellValue('J7','Lama Lembur ( Menit )')
            ->setCellValue('K7','Lama Lembur ( Jam )')
            //->setCellValue('L7','Over ( Jam )')
            ->setCellValue('L7','Hari Lembur')
            ->setCellValue('M7','Deskripsi')
            ->setCellValue('N7','User')
            ;

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

$objPHPExcel->getActiveSheet()->getStyle('A7:N7')->getFont()->getColor()->setRGB('ffffff');
// Worksheet Name
$objPHPExcel->getActiveSheet()->setTitle('OVERTIME_'.date("Ym"));

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

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

$swhere="";
if($month1&&$month2)
{
    $dt1=date("Y-m-d",strtotime($period1."-".$month1."-01"));
    $dt2=date("Y-m-d",strtotime($period2."-".((int)$month2+1)."-01"));
    $swhere="cast(l.overtime_date as date) between cast('$dt1' as date) and cast('$dt2' as date) and ";
}
if($idemployee)
{
    $swhere.="e.id='$idemployee' and ";
}
if($idunit)
{
    $swhere.="e.idunit='$idunit' and ";
}
if($idsubunit)
{
    $swhere.="e.idsubunit='$idsubunit' and ";
}

$qry="select l.id, l.id_employee, l.nik, l.empgroup, l.fullname, l.title, l.date, l.overtime_date, 
             l.end_date, l.start_hour, l.end_hour, l.total_duration, l.task, l.user, l.status_approval, l.description 
      from ".DB_APORTIL.".tbl_employee e inner join ".DB_APORTIL.".tbl_hcm_lembur l on e.id=l.id_employee
      where $swhere l.isdeleted=0 
      order by l.id asc";
      
$rsrpt=$cl->selectquery($qry);
if($rsrpt)
{
    $i=8;$j=1;
    while($rw=$cl->data_row_array($rsrpt))
    {
        $bandposition=$cl->fn_get_fieldvalue("bandposition","select bandposition from ".DB_APORTIL.".tbl_employee where id='".$rw['id_employee']."'");
        $days=date("l",strtotime($rw['overtime_date']));
        $colors="000000";
        if(strtolower($days)!='saturday'&&strtolower($days)!='sunday')
        {
            //if(strtotime($rw->start_hour)>=strtotime("18:00:00"&&strtotime($rw->end_hour)<=strtotime("21:00:00")))
            $dayscategory="Weekdays";
            if(strtotime($rw['end_hour'])>strtotime("21:00:00"))
            {
                $dayscategory="Weekdays Night";
            }
        }
        else
        {
            $colors="FF0000";
            $dayscategory="Weekend";
        }
        $durationmenit=$rw['total_duration'];
        $durationhour=round(($rw['total_duration']/60),2);
        $over=0;
        if($durationhour>5)
        {
            $over=($durationhour%5);
        }
        $phpColor = new PHPExcel_Style_Color();
        $phpColor->setRGB($colors);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i,$j)
                                            ->setCellValue('B'.$i,$rw["nik"])
                                            ->setCellValue('C'.$i,$rw["fullname"])
                                            ->setCellValue('D'.$i,$rw["title"])
                                            ->setCellValue('E'.$i,$bandposition)
                                            ->setCellValue('F'.$i,$days)
                                            ->setCellValue('G'.$i,date("d/m/Y",strtotime($rw['overtime_date'])))
                                            ->setCellValue('H'.$i,date("H:i",strtotime($rw['start_hour'])))
                                            ->setCellValue('I'.$i,date("H:i",strtotime($rw['end_hour'])))
                                            ->setCellValue('J'.$i,$durationmenit)
                                            ->setCellValue('K'.$i,$durationhour)
//                                            ->setCellValue('L'.$i,$over)
                                            ->setCellValue('L'.$i,$dayscategory)
                                            ->setCellValue('M'.$i,$rw['task'])
                                            ->setCellValue('N'.$i,$rw['user'])
                                            ;
        $objPHPExcel->getActiveSheet()->getStyle('L'.$i)->getFont()->setColor($phpColor);
        $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="HCM_OVERTIME.xls"');
header('Cache-Control: max-age=0');

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