240 lines
9.8 KiB
PHP
240 lines
9.8 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
|
|
$month=$_REQUEST['month'];
|
|
$period=$_REQUEST['period'];
|
|
$totdays=cal_days_in_month(CAL_GREGORIAN,$month,$period);
|
|
|
|
$idemployee=$_REQUEST['idemployee'];
|
|
$idsubunit=$_REQUEST['idsubunit'];
|
|
|
|
// Create new PHPExcel object
|
|
$objPHPExcel = new PHPExcel();
|
|
|
|
// My data
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
|
->setCellValue('A1','Employee Absence 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','')
|
|
// ->setCellValue('B7','')
|
|
// ->setCellValue('C7','')
|
|
// ->setCellValue('D7','');
|
|
|
|
$style = array(
|
|
'alignment' => array(
|
|
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
|
|
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
|
|
)
|
|
);
|
|
// === Dates title
|
|
$objPHPExcel->getActiveSheet()->mergeCells('A7:D7');
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A7','Dates');
|
|
$objPHPExcel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A7:D7')->applyFromArray($style);
|
|
$objPHPExcel->cellColor("A7:D7","938e8e");
|
|
$objPHPExcel->getActiveSheet()->getStyle('A7:D7')->getFont()->getColor()->setRGB('ffffff');
|
|
|
|
$startcol=69;$endcol=70;$prefix="";$prefixinc=65;
|
|
for($i=1;$i<=$totdays;$i++)
|
|
{
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($startcol).'7')->getFont()->setBold(true);
|
|
$objPHPExcel->getActiveSheet()->mergeCells($prefix.chr($startcol).'7:'.$prefix.chr($endcol).'7');
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($startcol).'7:'.$prefix.chr($endcol).'7')->applyFromArray($style);
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($prefix.chr($startcol).'7',$i." ".$cl->FN_GETMONTHNAME($month));
|
|
$objPHPExcel->cellColor($prefix.chr($startcol)."7:".$prefix.chr($endcol)."7","938e8e");
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($startcol)."7:".$prefix.chr($endcol)."7")->getFont()->getColor()->setRGB('ffffff');
|
|
|
|
$startcol+=2;$endcol+=2;
|
|
if($startcol>90||$endcol>90){
|
|
$startcol=65;$endcol=66;
|
|
$prefix=chr($prefixinc);
|
|
$prefixinc+=1;
|
|
}
|
|
}
|
|
|
|
$objPHPExcel->setActiveSheetIndex(0)
|
|
->setCellValue('A8','No.')
|
|
->setCellValue('B8','Name')
|
|
->setCellValue('C8','Nik')
|
|
->setCellValue('D8','Position');
|
|
$prefix="";$prefixinc=65;$j=69;$k=70;$qrydt="";$qrysum="";$lastcol="";
|
|
for($i=1;$i<=$totdays;$i++)
|
|
{
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($prefix.chr($j).'8','In');
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($j).'8:'.$prefix.chr($j).'8')->applyFromArray($style);
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($prefix.chr($k).'8','Out');
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($k).'8:'.$prefix.chr($k).'8')->applyFromArray($style);
|
|
$lastcol=$prefix.chr($k).'8';
|
|
$j+=2;$k+=2;
|
|
if($j>90||$k>90){
|
|
$prefix=chr($prefixinc);
|
|
$prefixinc+=1;$j=65;$k=66;
|
|
}
|
|
$qrydt.="case when cast(date_format(a.date,'%d') as integer)='".$i."' and a.clockin<>'' then cast(time_format(a.clockin,'%H%i') as double) else 0 end in_$i,";
|
|
$qrydt.="case when cast(date_format(a.date,'%d') as integer)='".$i."' and a.clockout<>'' then cast(time_format(a.clockout,'%H%i') as double) else 0 end out_$i,";
|
|
$qrysum.="sum(x.in_$i) in_$i,sum(x.out_$i) out_$i,";
|
|
|
|
}
|
|
$qrydt=substr($qrydt,0,strlen($qrydt)-1);
|
|
$qrysum=substr($qrysum,0,strlen($qrysum)-1);
|
|
|
|
//$j-=1;
|
|
|
|
// === Merge Cells
|
|
//$objPHPExcel->getActiveSheet()->mergeCells('G5:H5');
|
|
//$objPHPExcel->getActiveSheet()->mergeCells('I5:J5');
|
|
|
|
//$objPHPExcel->getActiveSheet()->getStyle('E9:'.chr($j).'9')->getFont()->getColor()->setRGB('ffffff');
|
|
// Worksheet Name
|
|
$objPHPExcel->getActiveSheet()->setTitle('Absence_'.date("Ym"));
|
|
|
|
// === Set Background Cell
|
|
//$objPHPExcel->cellColor("A7:N7","938e8e");
|
|
|
|
// === set Column Width A
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(6);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
|
|
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
|
|
// === Set auto COlumn width
|
|
//$prefix="";$prefixinc=65;$j=69;
|
|
//foreach(range('B',$lastcol) as $columnID)
|
|
//{
|
|
// $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
|
|
//}
|
|
|
|
// === Data
|
|
$swhere="";
|
|
if($idemployee){$swhere="a.idemployee='$idemployee' and ";}
|
|
if($idsubunit){$swhere.="e.idsubunit='$idsubunit' and ";}
|
|
|
|
$qry="select x.nik,x.fullname,x.positionname,$qrysum
|
|
from
|
|
(
|
|
select a.nik,e.fullname,e.positionname,a.islate,a.date,$qrydt
|
|
from ".DB_APORTIL.".tbl_employee e inner join ".DB_APORTIL.".tbl_hcm_absence a on e.id=a.idemployee
|
|
where $swhere a.months='$month' and a.years='$period' and a.isdeleted=0
|
|
group by a.nik,e.fullname,e.positionname,a.date
|
|
) x group by x.nik,x.fullname,x.positionname";
|
|
$rsdba=$cl->selectquery($qry);
|
|
if($rsdba){
|
|
$i=9;$x=1;
|
|
while($rw=$cl->data_row_array($rsdba))
|
|
{
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$i,$x)
|
|
->setCellValue('B'.$i,$rw["fullname"])
|
|
->setCellValue('C'.$i,$rw["nik"])
|
|
->setCellValue('D'.$i,$rw["positionname"]);
|
|
$prefix="";$prefixinc=65;$m=69;$n=70;
|
|
for($j=1;$j<=$totdays;$j++)
|
|
{
|
|
$valin='-';$colors="000000";$dot="";
|
|
if($rw["in_$j"]!='0')
|
|
{
|
|
$dtin=substr("00000".$rw["in_$j"],strlen("00000".$rw["in_$j"])-4,strlen("00000".$rw["in_$j"]));
|
|
$timein=substr($dtin,0,2).":".substr($dtin,strlen($dtin)-2,strlen($dtin));
|
|
//$valin=date("H",strtotime($rw["in_$j"]))."<sup>".date("i",strtotime($rw["in_$j"]));
|
|
$valin=date("H",strtotime($timein)).":".date("i",strtotime($timein));
|
|
//if(date("Hi",strtotime($rw["in_$j"]))>900){$color="red";}
|
|
if($rw["in_$j"]>900){$colors="FF0000";}
|
|
$dot=".";
|
|
}
|
|
$valout='-';
|
|
if($rw["out_$j"]!='0'){$valout=date("H",strtotime($rw["out_$j"])).":".date("i",strtotime($rw["out_$j"]));}
|
|
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($prefix.chr($m).$i,$valin);
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($m).$i.':'.$prefix.chr($m).$i)->applyFromArray($style);
|
|
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($prefix.chr($n).$i,$valout);
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($n).$i.':'.$prefix.chr($n).$i)->applyFromArray($style);
|
|
|
|
$phpColor = new PHPExcel_Style_Color();
|
|
$phpColor->setRGB($colors);
|
|
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($m).$i)->getFont()->setColor($phpColor);
|
|
$objPHPExcel->getActiveSheet()->getStyle($prefix.chr($n).$i)->getFont()->setColor($phpColor);
|
|
|
|
$m+=2;$n+=2;
|
|
if($m>90||$n>90){
|
|
$prefix=chr($prefixinc);
|
|
$prefixinc+=1;$m=65;$n=66;
|
|
}
|
|
}
|
|
$x+=1;$i+=1;
|
|
}
|
|
}
|
|
|
|
|
|
// 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_ABSENCE.xls"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
|
|
$objWriter->save('php://output');
|
|
exit;
|
|
?>
|