/**
* Default constructor
* @author Benjamin BALET <*****@*****.**>
*/
public function __construct()
{
parent::__construct();
setUserContext($this);
$this->load->model('organization_model');
$this->lang->load('organization', $this->language);
}
/**
* Default constructor
* @author Benjamin BALET <*****@*****.**>
*/
public function __construct()
{
parent::__construct();
setUserContext($this);
$this->load->model('entitleddays_model');
$this->lang->load('entitleddays', $this->language);
}
/**
* Default constructor
* @author Benjamin BALET <*****@*****.**>
*/
public function __construct()
{
parent::__construct();
setUserContext($this);
$this->lang->load('reports', $this->language);
$this->lang->load('global', $this->language);
}
/**
* Default constructor
* @author Benjamin BALET <*****@*****.**>
*/
public function __construct()
{
parent::__construct();
setUserContext($this);
$this->load->model('types_model');
$this->lang->load('leavetypes', $this->language);
}
public function __construct()
{
parent::__construct();
setUserContext($this);
$this->load->model('overtime_model');
$this->lang->load('overtime', $this->language);
$this->lang->load('global', $this->language);
}
/**
* Default constructor
* @author Benjamin BALET <*****@*****.**>
*/
public function __construct()
{
parent::__construct();
setUserContext($this);
}
/**
* Action: export the presence details for a given employee
* @param string $source page calling the report (employees, collaborators)
* @param int $id employee id
* @param int $month Month number or 0 for last month (default)
* @param int $year Year number or 0 for current year (default)
* @author Benjamin BALET <*****@*****.**>
*/
public function exportPresence($source, $id, $month = 0, $year = 0)
{
if ($source == 'collaborators') {
$this->auth->checkIfOperationIsAllowed('list_collaborators');
}
if ($source == 'employees') {
$this->auth->checkIfOperationIsAllowed('list_employees');
}
setUserContext($this);
$this->lang->load('calendar', $this->language);
$this->load->model('leaves_model');
$this->load->model('users_model');
$this->load->model('dayoffs_model');
$this->load->model('contracts_model');
$employee = $this->users_model->getUsers($id);
if ($this->user_id != $employee['manager'] && $this->is_hr === FALSE) {
log_message('error', 'User #' . $this->user_id . ' illegally tried to access to hr/presence #' . $id);
$this->session->set_flashdata('msg', sprintf(lang('global_msg_error_forbidden'), 'hr/presence'));
redirect('leaves');
}
$this->load->library('excel');
$data['employee'] = $employee;
$data['month'] = $month;
$data['year'] = $year;
$data['id'] = $id;
$this->load->view('hr/export_presence', $data);
}
/**
* Export the yearly calendar into Excel. The presentation differs a bit according to the limitation of Excel
* We'll get one line for the morning and one line for the afternoon
* @author Benjamin BALET <*****@*****.**>
*/
public function exportYear($employee = 0, $year = 0)
{
setUserContext($this);
$this->lang->load('calendar', $this->language);
$this->auth->checkIfOperationIsAllowed('organization_calendar');
if ($year == 0) {
$year = date("Y");
}
$this->load->model('leaves_model');
$this->load->model('users_model');
$this->load->library('excel');
$data['employee'] = $employee;
$data['year'] = $year;
$this->load->view('calendar/export_year', $data);
}
/**
* Export the yearly calendar into Excel. The presentation differs a bit according to the limitation of Excel
* We'll get one line for the morning and one line for the afternoon
* @param int $id identifier of the employee
* @param int $year Year number
* @author Benjamin BALET <*****@*****.**>
*/
public function exportYear($employee = 0, $year = 0)
{
setUserContext($this);
$this->lang->load('calendar', $this->language);
$this->auth->checkIfOperationIsAllowed('organization_calendar');
$this->load->model('users_model');
$user = $this->users_model->getUsers($employee);
//Either self access, Manager or HR
if ($employee == 0) {
$employee = $this->user_id;
$user = $this->users_model->getUsers($employee);
} else {
if (!$this->is_hr) {
if ($this->user_id != $user['manager']) {
$employee = $this->user_id;
$user = $this->users_model->getUsers($employee);
}
}
}
if ($year == 0) {
$year = date("Y");
}
$this->load->model('leaves_model');
$this->load->library('excel');
$data['employee'] = $employee;
$data['year'] = $year;
$this->load->view('calendar/export_year', $data);
}
/**
* Action: export the presence details for a given employee
* @param string $source page calling the report (employees, collaborators)
* @param int $id employee id
* @param int $month Month number or 0 for last month (default)
* @param int $year Year number or 0 for current year (default)
* @author Benjamin BALET <*****@*****.**>
*/
public function export_presence($source, $id, $month = 0, $year = 0)
{
if ($source == 'collaborators') {
$this->auth->check_is_granted('list_collaborators');
}
if ($source == 'employees') {
$this->auth->check_is_granted('list_employees');
}
setUserContext($this);
expires_now();
$this->lang->load('calendar', $this->language);
$this->load->model('leaves_model');
$this->load->model('users_model');
$this->load->model('dayoffs_model');
$this->load->model('contracts_model');
$this->load->library('excel');
//Details about the employee
$employee = $this->users_model->get_users($id);
if ($this->user_id != $employee['manager'] && $this->is_hr === false) {
log_message('error', 'User #' . $this->user_id . ' illegally tried to access to hr/presence #' . $id);
$this->session->set_flashdata('msg', sprintf(lang('global_msg_error_forbidden'), 'hr/presence'));
redirect('leaves');
}
$employee_name = $employee['firstname'] . ' ' . $employee['lastname'];
$contract = $this->contracts_model->get_contracts($employee['contract']);
if (!empty($contract)) {
$contract_name = $contract['name'];
} else {
$contract_name = '';
}
//Compute facts about dates and the selected month
if ($month == 0) {
$month = date('m', strtotime('last month'));
}
if ($year == 0) {
$year = date('Y', strtotime('last month'));
}
$total_days = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$start = sprintf('%d-%02d-01', $year, $month);
$lastDay = date("t", strtotime($start));
//last day of selected month
$end = sprintf('%d-%02d-%02d', $year, $month, $lastDay);
//Number of non working days during the selected month
$non_working_days = $this->dayoffs_model->sumdayoffs($employee['contract'], $start, $end);
$opened_days = $total_days - $non_working_days;
$month_name = lang(date('F', strtotime($start)));
//tabular view of the leaves
$linear = $this->leaves_model->linear($id, $month, $year, FALSE, FALSE, TRUE, FALSE);
$leave_duration = $this->leaves_model->monthly_leaves_duration($linear);
$work_duration = $opened_days - $leave_duration;
$leaves_detail = $this->leaves_model->monthly_leaves_by_type($linear);
//Leave balance of the employee
$summary = $this->leaves_model->get_user_leaves_summary($id, FALSE, $end);
//Print the header with the facts of the presence report
$sheet = $this->excel->setActiveSheetIndex(0);
$sheet->setTitle(mb_strimwidth(lang('hr_presence_title'), 0, 28, "..."));
//Maximum 31 characters allowed in sheet title.
$sheet->setCellValue('A1', lang('hr_presence_employee'));
$sheet->setCellValue('A2', lang('hr_presence_month'));
$sheet->setCellValue('A3', lang('hr_presence_days'));
$sheet->setCellValue('A4', lang('hr_presence_contract'));
$sheet->setCellValue('A5', lang('hr_presence_working_days'));
$sheet->setCellValue('A6', lang('hr_presence_non_working_days'));
$sheet->setCellValue('A7', lang('hr_presence_work_duration'));
$sheet->setCellValue('A8', lang('hr_presence_leave_duration'));
$sheet->getStyle('A1:A8')->getFont()->setBold(true);
$sheet->setCellValue('B1', $employee_name);
$sheet->setCellValue('B2', $month_name);
$sheet->setCellValue('B3', $total_days);
$sheet->setCellValue('B4', $contract_name);
$sheet->setCellValue('B5', $opened_days);
$sheet->setCellValue('B6', $non_working_days);
$sheet->setCellValue('B7', $work_duration);
$sheet->setCellValue('B8', $leave_duration);
if (count($leaves_detail) > 0) {
$line = 9;
foreach ($leaves_detail as $leaves_type_name => $leaves_type_sum) {
$sheet->setCellValue('A' . $line, $leaves_type_name);
$sheet->setCellValue('B' . $line, $leaves_type_sum);
$sheet->getStyle('A' . $line)->getAlignment()->setIndent(2);
$line++;
}
}
//Print two lines : the short name of all days for the selected month (horizontally aligned)
$start = $year . '-' . $month . '-' . '1';
//first date of selected month
$lastDay = date("t", strtotime($start));
//last day of selected month
for ($ii = 1; $ii <= $lastDay; $ii++) {
$dayNum = date("N", strtotime($year . '-' . $month . '-' . $ii));
$col = $this->excel->column_name(3 + $ii);
//Print day number
$sheet->setCellValue($col . '11', $ii);
//Print short name of the day
switch ($dayNum) {
case 1:
$sheet->setCellValue($col . '10', lang('calendar_monday_short'));
break;
case 2:
$sheet->setCellValue($col . '10', lang('calendar_tuesday_short'));
break;
case 3:
$sheet->setCellValue($col . '10', lang('calendar_wednesday_short'));
break;
case 4:
$sheet->setCellValue($col . '10', lang('calendar_thursday_short'));
break;
case 5:
$sheet->setCellValue($col . '10', lang('calendar_friday_short'));
break;
case 6:
$sheet->setCellValue($col . '10', lang('calendar_saturday_short'));
break;
case 7:
$sheet->setCellValue($col . '10', lang('calendar_sunday_short'));
break;
}
}
//The header is horizontally aligned
$col = $this->excel->column_name(3 + $lastDay);
$sheet->getStyle('C8:' . $col . '9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//Box around the lines for each employee
$styleBox = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$dayBox = array('borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'rgb' => '808080'), 'right' => array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'rgb' => '808080')));
//Background colors for the calendar according to the type of leave
$styleBgPlanned = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DDD')));
$styleBgRequested = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F89406')));
$styleBgAccepted = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '468847')));
$styleBgRejected = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0000')));
$styleBgDayOff = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000')));
$line = 12;
$col = $this->excel->column_name($lastDay + 3);
$sheet->getStyle('D' . $line . ':' . $col . ($line + 1))->applyFromArray($styleBox);
//Iterate on all days of the selected month
$dayNum = 0;
foreach ($linear->days as $day) {
$dayNum++;
$col = $this->excel->column_name(3 + $dayNum);
if (strstr($day->display, ';')) {
//Two statuses in the cell
$statuses = explode(";", $day->status);
$types = explode(";", $day->type);
//0 - Working day _
//1 - All day []
//2 - Morning |\
//3 - Afternoon /|
//4 - All Day Off []
//5 - Morning Day Off |\
//6 - Afternoon Day Off /|
$sheet->getComment($col . $line)->getText()->createTextRun($types[0]);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($types[1]);
switch (intval($statuses[0])) {
case 1:
$sheet->getStyle($col . $line)->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line)->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRejected);
break;
// Rejected
// Rejected
case '5':
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
break;
//Day off
//Day off
case '6':
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
break;
//Day off
}
switch (intval($statuses[1])) {
case 1:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
// Rejected
case '5':
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
break;
//Day off
//Day off
case '6':
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
break;
//Day off
}
//Two statuses in the cell
} else {
//Only one status in the cell
switch ($day->display) {
case '1':
//All day
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
switch ($day->status) {
// 1 : 'Planned';
// 2 : 'Requested';
// 3 : 'Accepted';
// 4 : 'Rejected';
case 1:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '2':
//AM
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
switch ($day->status) {
case 1:
$sheet->getStyle($col . $line)->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line)->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '3':
//PM
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
switch ($day->status) {
case 1:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '4':
//Full day off
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgDayOff);
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
break;
case '5':
//AM off
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
break;
case '6':
//PM off
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
break;
}
}
//Only one status in the cell
}
//day
//Autofit for all column containing the days
for ($ii = 1; $ii <= $lastDay; $ii++) {
$col = $this->excel->column_name($ii + 3);
$sheet->getStyle($col . '10:' . $col . '13')->applyFromArray($dayBox);
$sheet->getColumnDimension($col)->setAutoSize(TRUE);
}
$sheet->getColumnDimension('A')->setAutoSize(TRUE);
$sheet->getColumnDimension('B')->setAutoSize(TRUE);
//Leave Balance
$sheet->setCellValue('C16', lang('hr_summary_thead_type'));
$sheet->setCellValue('J16', lang('hr_summary_thead_available'));
$sheet->setCellValue('P16', lang('hr_summary_thead_taken'));
$sheet->setCellValue('V16', lang('hr_summary_thead_entitled'));
$sheet->setCellValue('AB16', lang('hr_summary_thead_description'));
$sheet->getStyle('C16:AH16')->getFont()->setBold(true);
$sheet->mergeCells('C16:I16');
$sheet->mergeCells('J16:O16');
$sheet->mergeCells('P16:U16');
$sheet->mergeCells('V16:AA16');
$sheet->mergeCells('AB16:AK16');
$line = 17;
foreach ($summary as $key => $value) {
$sheet->setCellValue('C' . $line, $key);
$sheet->setCellValue('J' . $line, (double) $value[1] - (double) $value[0]);
if ($value[2] == '') {
$sheet->setCellValue('P' . $line, (double) $value[0]);
} else {
$sheet->setCellValue('P' . $line, '-');
}
if ($value[2] == '') {
$sheet->setCellValue('V' . $line, (double) $value[1]);
} else {
$sheet->setCellValue('V' . $line, '-');
}
$sheet->setCellValue('AB' . $line, $value[2]);
$sheet->getStyle('C' . $line . ':AK' . $line)->applyFromArray($styleBox);
$sheet->mergeCells('C' . $line . ':I' . $line);
$sheet->mergeCells('J' . $line . ':O' . $line);
$sheet->mergeCells('P' . $line . ':U' . $line);
$sheet->mergeCells('V' . $line . ':AA' . $line);
$sheet->mergeCells('AB' . $line . ':AK' . $line);
$line++;
}
//Set layout to landscape and make the Excel sheet fit to the page
$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$sheet->getPageSetup()->setFitToPage(true);
$sheet->getPageSetup()->setFitToWidth(1);
$sheet->getPageSetup()->setFitToHeight(0);
$filename = 'presence.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
$objWriter->save('php://output');
}
/**
* Ajax endpoint: Select the supervisor of an entity of the organization
* takes parameters by GET
* @author Benjamin BALET <*****@*****.**>
*/
public function setsupervisor()
{
header("Content-Type: application/json");
setUserContext($this);
if ($this->auth->isAllowed('edit_organization') == FALSE) {
$this->output->set_header("HTTP/1.1 403 Forbidden");
} else {
if ($this->input->get('user', TRUE) == "") {
$id = NULL;
} else {
$id = $this->input->get('user', TRUE);
}
$entity = $this->input->get('entity', TRUE);
$this->load->model('organization_model');
echo json_encode($this->organization_model->setSupervisor($id, $entity));
}
}
/**
* Export the yearly calendar into Excel. The presentation differs a bit according to the limitation of Excel
* We'll get one line for the morning and one line for the afternoon
* @author Benjamin BALET <*****@*****.**>
*/
public function year_export($employee = 0, $year = 0)
{
setUserContext($this);
expires_now();
$this->lang->load('calendar', $this->language);
$this->auth->check_is_granted('organization_calendar');
if ($year == 0) {
$year = date("Y");
}
//Either self access, Manager or HR
if ($employee == 0) {
$employee = $this->user_id;
} else {
if (!$this->is_hr) {
if ($this->manager != $this->user_id) {
$employee = $this->user_id;
}
}
}
$this->load->model('users_model');
$employee_name = $this->users_model->get_label($employee);
//Load the leaves for all the months of the selected year
$this->load->model('leaves_model');
$months = array(lang('January') => $this->leaves_model->linear($employee, 1, $year, TRUE, TRUE, TRUE, TRUE), lang('February') => $this->leaves_model->linear($employee, 2, $year, TRUE, TRUE, TRUE, TRUE), lang('March') => $this->leaves_model->linear($employee, 3, $year, TRUE, TRUE, TRUE, TRUE), lang('April') => $this->leaves_model->linear($employee, 4, $year, TRUE, TRUE, TRUE, TRUE), lang('May') => $this->leaves_model->linear($employee, 5, $year, TRUE, TRUE, TRUE, TRUE), lang('June') => $this->leaves_model->linear($employee, 6, $year, TRUE, TRUE, TRUE, TRUE), lang('July') => $this->leaves_model->linear($employee, 7, $year, TRUE, TRUE, TRUE, TRUE), lang('August') => $this->leaves_model->linear($employee, 8, $year, TRUE, TRUE, TRUE, TRUE), lang('September') => $this->leaves_model->linear($employee, 9, $year, TRUE, TRUE, TRUE, TRUE), lang('October') => $this->leaves_model->linear($employee, 10, $year, TRUE, TRUE, TRUE, TRUE), lang('November') => $this->leaves_model->linear($employee, 11, $year, TRUE, TRUE, TRUE, TRUE), lang('December') => $this->leaves_model->linear($employee, 12, $year, TRUE, TRUE, TRUE, TRUE));
$this->load->library('excel');
$sheet = $this->excel->setActiveSheetIndex(0);
//Print the header with the values of the export parameters
$sheet->setTitle(mb_strimwidth(lang('calendar_year_title'), 0, 28, "..."));
//Maximum 31 characters allowed in sheet title.
$sheet->setCellValue('A1', lang('calendar_year_title') . ' ' . $year . ' (' . $employee_name . ') ');
$sheet->getStyle('A1')->getFont()->setBold(true);
$sheet->mergeCells('A1:C1');
//Print a line with all possible day numbers (1 to 31)
for ($ii = 1; $ii <= 31; $ii++) {
$col = $this->excel->column_name(3 + $ii);
$sheet->setCellValue($col . '3', $ii);
}
//Box around the lines for each employee
$styleBox = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
//Box around a day
$dayBox = array('borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'rgb' => '808080'), 'right' => array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'rgb' => '808080')));
//To fill at the left of months having less than 31 days
$styleMonthPad = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '00FFFF')));
//Background colors for the calendar according to the type of leave
$styleBgPlanned = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DDD')));
$styleBgRequested = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F89406')));
$styleBgAccepted = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '468847')));
$styleBgRejected = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0000')));
$styleBgDayOff = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000')));
$line = 4;
//Iterate on all employees of the selected entity
foreach ($months as $month_name => $month) {
//Merge the two line containing the name of the month and apply a border around it
$sheet->setCellValue('C' . $line, $month_name);
$sheet->mergeCells('C' . $line . ':C' . ($line + 1));
$col = $this->excel->column_name(34);
$sheet->getStyle('C' . $line . ':' . $col . ($line + 1))->applyFromArray($styleBox);
//Iterate on all days of the selected month
$dayNum = 0;
foreach ($month->days as $day) {
$dayNum++;
$col = $this->excel->column_name(3 + $dayNum);
$overlapping = FALSE;
if (strstr($day->display, ';')) {
//Two statuses in the cell
$periods = explode(";", $day->display);
$statuses = explode(";", $day->status);
$types = explode(";", $day->type);
//0 - Working day _
//1 - All day []
//2 - Morning |\
//3 - Afternoon /|
//4 - All Day Off []
//5 - Morning Day Off |\
//6 - Afternoon Day Off /|
$sheet->getComment($col . $line)->getText()->createTextRun($types[0]);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($types[1]);
switch (intval($statuses[0])) {
case 1:
$sheet->getStyle($col . $line)->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line)->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRejected);
break;
// Rejected
// Rejected
case '5':
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
break;
//Day off
//Day off
case '6':
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
break;
//Day off
}
switch (intval($statuses[1])) {
case 1:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
// Rejected
case '5':
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
break;
//Day off
//Day off
case '6':
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
break;
//Day off
}
//Two statuses in the cell
} else {
//Only one status in the cell
switch ($day->display) {
case '1':
//All day
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
switch ($day->status) {
// 1 : 'Planned';
// 2 : 'Requested';
// 3 : 'Accepted';
// 4 : 'Rejected';
case 1:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '2':
//AM
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
switch ($day->status) {
case 1:
$sheet->getStyle($col . $line)->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line)->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '3':
//PM
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
switch ($day->status) {
case 1:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '4':
//Full day off
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgDayOff);
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
break;
case '5':
//AM off
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
break;
case '6':
//PM off
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
break;
}
}
//Only one status in the cell
}
//day
if ($dayNum < 31) {
$pad = (int) (35 - (31 - $dayNum));
$colFrom = $this->excel->column_name($pad);
$colTo = $this->excel->column_name(34);
$sheet->mergeCells($colFrom . $line . ':' . $colTo . ($line + 1));
$sheet->getStyle($colFrom . $line . ':' . $colTo . ($line + 1))->applyFromArray($styleMonthPad);
}
$line += 2;
}
//Employee
//Autofit for all column containing the days
for ($ii = 1; $ii <= 31; $ii++) {
$col = $this->excel->column_name($ii + 3);
$sheet->getStyle($col . '3:' . $col . ($line - 1))->applyFromArray($dayBox);
$sheet->getColumnDimension($col)->setAutoSize(TRUE);
}
$sheet->getColumnDimension('A')->setAutoSize(TRUE);
$sheet->getColumnDimension('B')->setAutoSize(TRUE);
$sheet->getColumnDimension('C')->setWidth(40);
//Set layout to landscape and make the Excel sheet fit to the page
$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$sheet->getPageSetup()->setFitToPage(true);
$sheet->getPageSetup()->setFitToWidth(1);
$sheet->getPageSetup()->setFitToHeight(0);
$filename = 'year.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
$objWriter->save('php://output');
}
/**
* Export the tabular calendar into Excel. The presentation differs a bit according to the limitation of Excel
* We'll get one line for the morning and one line for the afternoon
* @author Benjamin BALET <*****@*****.**>
*/
public function tabular_export($id = -1, $month = 0, $year = 0, $children = TRUE)
{
expires_now();
//Load the language file (the loaded language depends if it was called from the public view)
if ($this->config->item('public_calendar') == TRUE && !$this->session->userdata('logged_in')) {
$this->load->library('polyglot');
$language = $this->config->item('language');
//$language_code = $this->polyglot->language2code($language);
} else {
setUserContext($this);
$language = $this->language;
}
$this->lang->load('calendar', $language);
$this->lang->load('global', $language);
$this->load->library('excel');
$sheet = $this->excel->setActiveSheetIndex(0);
//Print the header with the values of the export parameters
$sheet->setTitle(mb_strimwidth(lang('calendar_tabular_export_title'), 0, 28, "..."));
//Maximum 31 characters allowed in sheet title.
$sheet->setCellValue('A1', lang('calendar_tabular_export_param_entity'));
$sheet->setCellValue('A2', lang('calendar_tabular_export_param_month'));
$sheet->setCellValue('A3', lang('calendar_tabular_export_param_year'));
$sheet->setCellValue('A4', lang('calendar_tabular_export_param_children'));
$sheet->getStyle('A1:A4')->getFont()->setBold(true);
$this->load->model('organization_model');
$sheet->setCellValue('B1', $this->organization_model->get_label($id));
$sheet->setCellValue('B2', $month);
$sheet->setCellValue('B3', $year);
if ($children == TRUE) {
$sheet->setCellValue('B4', lang('global_true'));
} else {
$sheet->setCellValue('B4', lang('global_false'));
}
//Print two lines : the short name of all days for the selected month (horizontally aligned)
$start = $year . '-' . $month . '-' . '1';
//first date of selected month
$lastDay = date("t", strtotime($start));
//last day of selected month
for ($ii = 1; $ii <= $lastDay; $ii++) {
$dayNum = date("N", strtotime($year . '-' . $month . '-' . $ii));
$col = $this->excel->column_name(3 + $ii);
//Print day number
$sheet->setCellValue($col . '9', $ii);
//Print short name of the day
switch ($dayNum) {
case 1:
$sheet->setCellValue($col . '8', lang('calendar_monday_short'));
break;
case 2:
$sheet->setCellValue($col . '8', lang('calendar_tuesday_short'));
break;
case 3:
$sheet->setCellValue($col . '8', lang('calendar_wednesday_short'));
break;
case 4:
$sheet->setCellValue($col . '8', lang('calendar_thursday_short'));
break;
case 5:
$sheet->setCellValue($col . '8', lang('calendar_friday_short'));
break;
case 6:
$sheet->setCellValue($col . '8', lang('calendar_saturday_short'));
break;
case 7:
$sheet->setCellValue($col . '8', lang('calendar_sunday_short'));
break;
}
}
//Label for employee name
$sheet->setCellValue('C8', lang('calendar_tabular_export_thead_employee'));
$sheet->mergeCells('C8:C9');
//The header is horizontally aligned
$col = $this->excel->column_name(3 + $lastDay);
$sheet->getStyle('C8:' . $col . '9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//Get the tabular data
$this->load->model('leaves_model');
$tabular = $this->leaves_model->tabular($id, $month, $year, $children);
//Box around the lines for each employee
$styleBox = array('borders' => array('top' => array('style' => PHPExcel_Style_Border::BORDER_THIN), 'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$dayBox = array('borders' => array('left' => array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'rgb' => '808080'), 'right' => array('style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'rgb' => '808080')));
//Background colors for the calendar according to the type of leave
$styleBgPlanned = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'DDD')));
$styleBgRequested = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'F89406')));
$styleBgAccepted = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '468847')));
$styleBgRejected = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => 'FF0000')));
$styleBgDayOff = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '000000')));
$line = 10;
//Iterate on all employees of the selected entity
foreach ($tabular as $employee) {
//Merge the two line containing the name of the employee and apply a border around it
$sheet->setCellValue('C' . $line, $employee->name);
$sheet->mergeCells('C' . $line . ':C' . ($line + 1));
$col = $this->excel->column_name($lastDay + 3);
$sheet->getStyle('C' . $line . ':' . $col . ($line + 1))->applyFromArray($styleBox);
//Iterate on all days of the selected month
$dayNum = 0;
foreach ($employee->days as $day) {
$dayNum++;
$col = $this->excel->column_name(3 + $dayNum);
$overlapping = FALSE;
if (strstr($day->display, ';')) {
//Two statuses in the cell
$periods = explode(";", $day->display);
$statuses = explode(";", $day->status);
$types = explode(";", $day->type);
//0 - Working day _
//1 - All day []
//2 - Morning |\
//3 - Afternoon /|
//4 - All Day Off []
//5 - Morning Day Off |\
//6 - Afternoon Day Off /|
$sheet->getComment($col . $line)->getText()->createTextRun($types[0]);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($types[1]);
switch (intval($statuses[0])) {
case 1:
$sheet->getStyle($col . $line)->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line)->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRejected);
break;
// Rejected
// Rejected
case '5':
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
break;
//Day off
//Day off
case '6':
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
break;
//Day off
}
switch (intval($statuses[1])) {
case 1:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
// Rejected
case '5':
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
break;
//Day off
//Day off
case '6':
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
break;
//Day off
}
//Two statuses in the cell
} else {
//Only one status in the cell
switch ($day->display) {
case '1':
//All day
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
switch ($day->status) {
// 1 : 'Planned';
// 2 : 'Requested';
// 3 : 'Accepted';
// 4 : 'Rejected';
case 1:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '2':
//AM
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
switch ($day->status) {
case 1:
$sheet->getStyle($col . $line)->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . $line)->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . $line)->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '3':
//PM
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
switch ($day->status) {
case 1:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgPlanned);
break;
// Planned
// Planned
case 2:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRequested);
break;
// Requested
// Requested
case 3:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgAccepted);
break;
// Accepted
// Accepted
case 4:
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgRejected);
break;
// Rejected
}
break;
case '4':
//Full day off
$sheet->getStyle($col . $line . ':' . $col . ($line + 1))->applyFromArray($styleBgDayOff);
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
break;
case '5':
//AM off
$sheet->getStyle($col . $line)->applyFromArray($styleBgDayOff);
$sheet->getComment($col . $line)->getText()->createTextRun($day->type);
break;
case '6':
//PM off
$sheet->getStyle($col . ($line + 1))->applyFromArray($styleBgDayOff);
$sheet->getComment($col . ($line + 1))->getText()->createTextRun($day->type);
break;
}
}
//Only one status in the cell
}
//day
$line += 2;
}
//Employee
//Autofit for all column containing the days
for ($ii = 1; $ii <= $lastDay; $ii++) {
$col = $this->excel->column_name($ii + 3);
$sheet->getStyle($col . '8:' . $col . ($line - 1))->applyFromArray($dayBox);
$sheet->getColumnDimension($col)->setAutoSize(TRUE);
}
$sheet->getColumnDimension('A')->setAutoSize(TRUE);
$sheet->getColumnDimension('B')->setAutoSize(TRUE);
$sheet->getColumnDimension('C')->setWidth(40);
//Set layout to landscape and make the Excel sheet fit to the page
$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$sheet->getPageSetup()->setFitToPage(true);
$sheet->getPageSetup()->setFitToWidth(1);
$sheet->getPageSetup()->setFitToHeight(0);
$filename = 'tabular.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
$objWriter->save('php://output');
}
/**
* Default constructor
* @author Benjamin BALET <*****@*****.**>
*/
public function __construct()
{
parent::__construct();
setUserContext($this);
$this->lang->load('calendar', $this->language);
}
/**
* Displays a page that allows you to :
* 1. Select a list of employees (using organization tree).
* 2. Credit entitled days to them.
* @author Benjamin BALET <*****@*****.**>
*/
public function organization()
{
setUserContext($this);
$this->auth->checkIfOperationIsAllowed('entitleddays_user');
$data = getUserContext($this);
$this->lang->load('organization', $this->language);
$this->lang->load('datatable', $this->language);
$this->lang->load('treeview', $this->language);
$data['title'] = lang('organization_index_title');
$data['help'] = '';
$this->load->view('templates/header', $data);
$this->load->view('menu/index', $data);
$this->load->view('entitleddays/organization', $data);
$this->load->view('templates/footer');
}
