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