public function getAbsentByEmployeeSummary($data)
{
$year_start = substr($data['date_from'], 6, 4);
$month_start = substr($data['date_from'], 3, 2);
$day_start = substr($data['date_from'], 0, 2);
$year_end = substr($data['date_to'], 6, 4);
$month_end = substr($data['date_to'], 3, 2);
$day_end = substr($data['date_to'], 0, 2);
$total = getRangeDate($day_start, $month_start, $year_start, $day_end, $month_end, $year_end);
$sql = " SELECT employeeid,";
$sql .= " COUNT(CASE WHEN a.job_id=11 THEN 1 END) as cuti_tahunan, ";
$sql .= " COUNT(CASE WHEN a.job_id=500 THEN 1 END) as cuti_bersama, ";
$sql .= " COUNT(CASE WHEN a.job_id=12 THEN 1 END) as cuti_tanggungan, ";
$sql .= " COUNT(CASE WHEN a.job_id=10 THEN 1 END) as cuti_khusus, ";
$sql .= " COUNT(CASE WHEN a.job_id=2 THEN 1 END) as sakit, ";
$sql .= " COUNT(CASE WHEN a.job_id>=4 AND a.job_id<=9 THEN 1 END) AS izin, ";
$sql .= " COUNT(CASE WHEN a.job_id=3 THEN 1 END) AS haid, ";
$sql .= " employeefirstname, employeemiddlename, employeelastname ";
$sql .= " from timesheet a\n inner join job b on a.job_id = b.job_id\n inner join employee c on a.employee_id = c.employee_id\n WHERE a.timesheet_approval=2\n and a.timesheetdate >= STR_TO_DATE('" . $data['date_from'] . "', '%d/%m/%Y')\n and a.timesheetdate <= STR_TO_DATE('" . $data['date_to'] . "', '%d/%m/%Y')\n and b.jobtype='HRD'\n GROUP BY employeeid \n ORDER BY employeefirstname, employeemiddlename, employeelastname;";
return $this->rst2Array($sql);
}
function reportEmployeeAbsentExcel()
{
$this->load->library('PHPExcel');
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel, "Excel2007");
$objPHPExcel->getProperties()->setTitle("Mantap")->setDescription("description");
$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$objWorksheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objWorksheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A5);
$objWorksheet->getPageSetup()->setScale(93);
$border = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$fill = array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'rotation' => 0, 'startcolor' => array('rgb' => 'CCCCCC'), 'endcolor' => array('argb' => 'CCCCCC'));
$objPHPExcel->getDefaultStyle()->getFont()->setName('Trebuchet MS')->setSize(8);
$objWorksheet = $objPHPExcel->setActiveSheetIndex();
// We'll be outputting an excel file
$data['date_from'] = $this->session->userdata('date_from');
$data['date_to'] = $this->session->userdata('date_to');
$users = $this->reportModel->getAbsentByEmployee($data);
$col = 0;
$row = 1;
$objWorksheet->setCellValueByColumnAndRow($col, $row, 'Name');
$objWorksheet->getColumnDimensionByColumn($col)->setWidth(30);
$objWorksheet->getStyleByColumnAndRow($col, $row)->applyFromArray($border);
$objWorksheet->getStyleByColumnAndRow($col, $row)->getFill()->applyFromArray($fill);
$objWorksheet->mergeCellsByColumnAndRow($col, $row, $col, $row + 1);
$objWorksheet->getStyleByColumnAndRow($col, $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objWorksheet->setCellValueByColumnAndRow($col + 1, $row, 'NIK');
$objWorksheet->getColumnDimensionByColumn($col + 1)->setWidth(10);
$objWorksheet->getStyleByColumnAndRow($col + 1, $row)->applyFromArray($border);
$objWorksheet->getStyleByColumnAndRow($col + 1, $row)->getFill()->applyFromArray($fill);
$objWorksheet->mergeCellsByColumnAndRow($col + 1, $row, $col + 1, $row + 1);
$objWorksheet->getStyleByColumnAndRow($col + 1, $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$row++;
$objWorksheet->getStyleByColumnAndRow($col, $row)->applyFromArray($border);
$objWorksheet->getStyleByColumnAndRow($col + 1, $row)->applyFromArray($border);
$year_start = substr($data['date_from'], 6, 4);
$month_start = substr($data['date_from'], 3, 2);
$day_start = substr($data['date_from'], 0, 2);
$year_end = substr($data['date_to'], 6, 4);
$month_end = substr($data['date_to'], 3, 2);
$day_end = substr($data['date_to'], 0, 2);
$total = getRangeDate($day_start, $month_start, $year_start, $day_end, $month_end, $year_end);
$x = 1;
$y = 1;
for ($i = 0; $i <= $total; $i++) {
$date = $year_start . '-' . $month_start . '-' . $day_start;
$ndate = date("d-M", strtotime("{$date} +{$i} day"));
$objWorksheet->setCellValueByColumnAndRow($col + 1 + $x, $row - 1, $ndate);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $x, $row - 1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $x, $row - 1)->getFill()->applyFromArray($fill);
$objWorksheet->mergeCellsByColumnAndRow($col + 1 + $x, $row - 1, $col + 1 + ($x + 1), $row - 1);
$objWorksheet->setCellValueByColumnAndRow($col + 1 + $y, $row, "Sakit");
$objWorksheet->getColumnDimensionByColumn($col + 1 + $y)->setWidth(5);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->applyFromArray($border);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->getFill()->applyFromArray($fill);
$y++;
$objWorksheet->setCellValueByColumnAndRow($col + 1 + $y, $row, "Izin");
$objWorksheet->getColumnDimensionByColumn($col + 1 + $y)->setWidth(4);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->applyFromArray($border);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->getFill()->applyFromArray($fill);
$x = $x + 2;
$y++;
}
$col = 0;
$row++;
foreach ($users as $user => $u) {
$objWorksheet->setCellValueByColumnAndRow($col + 0, $row, $u['employeefirstname'] . ' ' . $u['employeemiddlename'] . ' ' . $u['employeelastname']);
$objWorksheet->getStyleByColumnAndRow($col + 0, $row)->applyFromArray($border);
$objWorksheet->setCellValueByColumnAndRow($col + 1, $row, $u['employeeid']);
$objWorksheet->getStyleByColumnAndRow($col + 1, $row)->applyFromArray($border);
// $x=1;
$y = 1;
for ($i = 0; $i <= $total; $i++) {
$objWorksheet->setCellValueByColumnAndRow($col + 1 + $y, $row, $u['S' . $i]);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->applyFromArray($border);
$y++;
$objWorksheet->setCellValueByColumnAndRow($col + 1 + $y, $row, $u['I' . $i]);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objWorksheet->getStyleByColumnAndRow($col + 1 + $y, $row)->applyFromArray($border);
$y++;
}
$row++;
}
$objWriter->save("./media/Absent.xlsx");
// force_download("Transport",$file);
redirect('../media/Absent.xlsx');
}
