213 lines
8.0 KiB
PHP
213 lines
8.0 KiB
PHP
<?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 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($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."-".$month2."-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;
|
|
?>
|