File: /var/www/gestor-horarios.bradford/app/controllers/ReportsExcelController.php
<?php
class ReportsExcelController extends CI_Controller
{
function __construct()
{
parent::__construct();
$this->load->helper(array('model', 'form_helper', 'validar', 'utilities'));
$this->load->library('session');
$this->load->model('admin_model');
validate_session($this->session->userdata);
define("STAFF_ID", '' . $this->session->userdata('rt_staff') . '');
$this->session->unset_userdata('employees');
}
function index()
{
$data['title'] = 'Inicio';
$data['main_content'] = 'report/index_view';
$this->load->view('shared/layout', $data);
}
function download_report($name = '', $query_name = 'query', $filtro_evaluar = 0)
{
$sql = $this->query($query_name, $filtro_evaluar);
$result_report = $this->db->query($sql)->result();
$keys = get_object_vars($result_report[0]);
$keys = array_keys($keys);
$alphas = range('A', 'Z');
//pre($keys);
//pre($result_report);
foreach ($result_report as $key => $value) {
if (property_exists($value, 'grupo')) {
$result_report[$key]->grupo = pondera_objetivo($value->grupo)['grupo'];
}
}
//pre_die($result_report);
foreach ($keys as $key => $value) {
$header[$alphas[$key]] = strtoupper($value);
}
$this->export_to_excel($header, $result_report, $name . '_' . date('YmdHis'), true);
}
function query($name = '', $excluir_evaluacion = 0)
{
$querys['employees_evaluation'] = "select CONCAT(T2.nombres, ' ', T2.apellido_p) jefe,
T2.rut rut_jefe,
CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) trabajador,
trabajadores.rut rut_trabajador,
if(evaluaciones.estado = 3, 'Terminado', (IF(evaluaciones.estado =2, 'Feedback', 'Borrador'))) estado,
evaluaciones.nota_final, evaluaciones.nota_final grupo,
centros_costos.nombre centro_costo
from trabajadores
left join evaluaciones on trabajadores.id = evaluaciones.trabajadores_id
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner join centros_costos on centros_costos.id = trabajadores.centros_costos_id
where trabajadores.activo = 1
AND trabajadores.excluir_evaluacion = $excluir_evaluacion
order by T2.id
";
$querys['employees'] = "select trabajadores.rut, CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) nombre, trabajadores.activo, unidades.nombre unidad,
cargos.nombre cargo, CONCAT(T2.nombres, ' ', T2.apellido_p) jefe, T2.rut rut_jefe
from trabajadores
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner JOIN centros_costos on centros_costos.id = trabajadores.centros_costos_id
INNER JOIN unidades on unidades.id = trabajadores.unidades_id
left join cargos on cargos.id = trabajadores.cargos_id
left join sucursales on sucursales.id = trabajadores.sucursales_id
WHERE trabajadores.activo = 1
AND trabajadores.excluir_evaluacion = $excluir_evaluacion
";
$querys['progress'] = "select
CONCAT(T2.nombres, ' ', T2.apellido_p) jefe,
T2.rut rut_jefe,
centros_costos.nombre centro_costo,
(SELECT count(E2.id) from evaluaciones E2
inner join trabajadores T3 on T3.id = E2.trabajadores_id
where E2.estado = 1 and T3.jefe_id = T2.id and T3.centros_costos_id = centros_costos.id
AND T3.excluir_evaluacion = $excluir_evaluacion) borrador,
(SELECT count(E2.id) from evaluaciones E2
inner join trabajadores T3 on T3.id = E2.trabajadores_id
where E2.estado = 2 and T3.jefe_id = T2.id and T3.centros_costos_id = centros_costos.id
AND T3.excluir_evaluacion = $excluir_evaluacion) feedback,
(SELECT count(E2.id) from evaluaciones E2
inner join trabajadores T3 on T3.id = E2.trabajadores_id
where E2.estado = 3 and T3.jefe_id = T2.id and T3.centros_costos_id = centros_costos.id
AND T3.excluir_evaluacion = $excluir_evaluacion) terminada,
(SELECT count(T3.id) from trabajadores T3
where T3.activo = 1 and T3.jefe_id = T2.id and T3.centros_costos_id = centros_costos.id
AND T3.excluir_evaluacion = $excluir_evaluacion) totales
from trabajadores
left join evaluaciones on trabajadores.id = evaluaciones.trabajadores_id
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner join centros_costos on centros_costos.id = trabajadores.centros_costos_id
where trabajadores.activo = 1
-- and trabajadores.jefe_id = 36
group by centros_costos.nombre, trabajadores.jefe_id
order by T2.id";
$querys['evaluation'] = "select
CONCAT(T2.nombres, ' ', T2.apellido_p) jefe,
T2.rut rut_jefe,
CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) trabajador,
trabajadores.rut rut_trabajador,
evaluaciones.nota_objetivos, evaluaciones.nota_competencias, evaluaciones.nota_final, evaluaciones.nota_final grupo,
centros_costos.nombre centro_costo
from trabajadores
left join evaluaciones on trabajadores.id = evaluaciones.trabajadores_id
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner join centros_costos on centros_costos.id = trabajadores.centros_costos_id
-- left join evaluacion_competencias on evaluacion_competencias.trabajadores_id = trabajadores. --
where trabajadores.activo = 1
AND trabajadores.excluir_evaluacion = $excluir_evaluacion ";
$querys['improve'] = "select
CONCAT(T2.nombres, ' ', T2.apellido_p) jefe,
T2.rut rut_jefe,
CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) trabajador,
trabajadores.rut rut_trabajador,
compromisos.detalle compromiso, compromisos.fecha_seguimiento, compromisos.fecha_finalizacion,
CA1.nombre capacitacion1, CA2.nombre capacitacion2,
centros_costos.nombre centro_costo
from trabajadores
left join evaluaciones on trabajadores.id = evaluaciones.trabajadores_id
LEFT join capacitaciones CA1 on CA1.id = evaluaciones.capacitaciones_1
LEFT join capacitaciones CA2 on CA2.id = evaluaciones.capacitaciones_2
LEFT join compromisos on compromisos.evaluaciones_id = evaluaciones.id
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner join centros_costos on centros_costos.id = trabajadores.centros_costos_id
-- left join evaluacion_competencias on evaluacion_competencias.trabajadores_id = trabajadores. --
where trabajadores.activo = 1
AND trabajadores.excluir_evaluacion = $excluir_evaluacion ";
$querys['ranking_mejores'] = "select
CONCAT(T2.nombres, ' ', T2.apellido_p) jefe,
CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) trabajador,
evaluaciones.nota_objetivos, evaluaciones.nota_competencias, evaluaciones.nota_final, evaluaciones.nota_final grupo,
centros_costos.nombre centro_costo
from trabajadores
left join evaluaciones on trabajadores.id = evaluaciones.trabajadores_id
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner join centros_costos on centros_costos.id = trabajadores.centros_costos_id
-- left join evaluacion_competencias on evaluacion_competencias.trabajadores_id = trabajadores. --
where trabajadores.activo = 1
AND trabajadores.excluir_evaluacion = $excluir_evaluacion
order by evaluaciones.nota_final DESC , evaluaciones.nota_competencias, evaluaciones.nota_objetivos
limit 30";
$querys['ranking_peores'] = "select
CONCAT(T2.nombres, ' ', T2.apellido_p) jefe,
CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) trabajador,
evaluaciones.nota_objetivos, evaluaciones.nota_competencias, evaluaciones.nota_final, evaluaciones.nota_final grupo,
centros_costos.nombre centro_costo
from trabajadores
left join evaluaciones on trabajadores.id = evaluaciones.trabajadores_id
inner join trabajadores T2 on T2.id = trabajadores.jefe_id
inner join centros_costos on centros_costos.id = trabajadores.centros_costos_id
-- left join evaluacion_competencias on evaluacion_competencias.trabajadores_id = trabajadores. --
where trabajadores.activo = 1
AND trabajadores.excluir_evaluacion = $excluir_evaluacion
order by evaluaciones.nota_final ASC , evaluaciones.nota_competencias, evaluaciones.nota_objetivos
limit 30";
return $querys[$name];
}
function export_to_excel($header = array(), $rows = array(), $title = "", $direct_download = FALSE)
{
require_once ROOT_PATH_BASE . '/app/libraries/PHPExcel/PHPExcel.php';
require_once ROOT_PATH_BASE . '/app/libraries/PHPExcel/PHPExcel/Writer/Excel2007.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Evaluacion de Desempeno - MDMN");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Evaluacion Report");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Evaluacion Report");
$objPHPExcel->getProperties()->setDescription("");
foreach ($header as $key => $value) {
$objPHPExcel->getActiveSheet()->SetCellValue($key . '1', $value);
}
$styleArray = array(
'font' => array(
'bold' => true,
)
);
$objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->applyFromArray($styleArray);
for ($i = 65; $i <= 90; $i++) // letters Array
{
$letters[] = chr($i);
}
foreach ($rows as $row_key => $row_value) {
$array_row = array_values((array)$row_value);
for ($i = 0; $i < count($header); $i++) {
$objPHPExcel->getActiveSheet()->SetCellValue($letters[$i] . ($row_key + 2), $array_row[$i]);
//pre($array_row[$i]);
}
}
$objPHPExcel->getActiveSheet()->setTitle(substr($title, 0, 29));
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$saved_file = ROOT_PATH_BASE . '/reports/' . $title . '.xlsx';
$objWriter->save($saved_file);
if ($direct_download) {
header("Content-type: application/octet-stream");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=\"$title.xlsx\"\n");
readfile($saved_file);
}
}
function download_report_vision_general($por = "", $grupo = "", $rut = "", $fecha_desde = "", $fecha_hasta = "")
{
$where = "";
$having = "";
if ($por == "100") {
$having .= "having 100 - (round(SUM(TIME_TO_SEC(atraso) / 60) + SUM(TIME_TO_SEC(ausencia ) / 60)) / round(SUM(TIME_TO_SEC(total_jornada) / 60)) * 100) > 90 ";
}
if ($por == "90") {
$having .= "having 100 - (round(SUM(TIME_TO_SEC(atraso) / 60) + SUM(TIME_TO_SEC(ausencia ) / 60)) / round(SUM(TIME_TO_SEC(total_jornada) / 60)) * 100) between 85 and 90 ";
}
if ($por == "85") {
$having .= "having 100 - (round(SUM(TIME_TO_SEC(atraso) / 60) + SUM(TIME_TO_SEC(ausencia ) / 60)) / round(SUM(TIME_TO_SEC(total_jornada) / 60)) * 100) < 85 ";
}
if (!empty($fecha_desde) && $fecha_desde != "fecha_desde") {
$where .= "and fecha >= '$fecha_desde' ";
}
if (!empty($fecha_hasta) && $fecha_hasta != "fecha_hasta") {
$where .= "and fecha <= '$fecha_hasta' ";
}
if (!empty($rut) && $rut != "rut") {
$where .= "and movimientos_reloj.rut = '$rut' ";
}
if ($grupo == "mes") {
$group = "group by DATE_FORMAT(fecha, '%Y-%m')";
$select_group = "DATE_FORMAT(fecha, '%Y-%m') fecha";
$orderby = " order by fecha desc";
}
if (empty($grupo) || $grupo == "rut_mes" || $grupo == "grupo") {
$group = "group by DATE_FORMAT(fecha, '%Y-%m'), movimientos_reloj.rut";
$select_group = "DATE_FORMAT(fecha, '%Y-%m') fecha, movimientos_reloj.rut, CONCAT(trabajadores.nombres, ' ', trabajadores.apellido_p) nombre";
$orderby = " order by fecha desc, movimientos_reloj.rut";
}
$query = "select $select_group,
COUNT(CASE WHEN entrada = '00:00:00' AND salida = '00:00:00' THEN 1 END) AS num_ausencias,
COUNT(CASE WHEN salida < hora_termino THEN 1 END) AS salidas_antes_horario,
round(SUM(TIME_TO_SEC(atraso) / 60) + SUM(TIME_TO_SEC(ausencia ) / 60)) ausencias,
round(SUM(TIME_TO_SEC(atraso) / 60) + SUM(TIME_TO_SEC(ausencia ) / 60)) / round(SUM(TIME_TO_SEC(total_jornada) / 60)) * 100 as '%Atraso y ausencias',
100 - (round(SUM(TIME_TO_SEC(atraso) / 60) + SUM(TIME_TO_SEC(ausencia ) / 60)) / round(SUM(TIME_TO_SEC(total_jornada) / 60)) * 100) as '%Efectivo Trabajo',
round(SUM(TIME_TO_SEC(total_jornada) / 60)) jornada_pactada,
round(SUM(TIME_TO_SEC(total_jornada_efectiva) / 60)) jornada_efectiva
from movimientos_reloj
inner join trabajadores on trabajadores.id = movimientos_reloj.trabajadores_id
$where
$group
$having
$orderby
";
$result_report = $this->db->query($query)->result();
$keys = get_object_vars($result_report[0]);
$keys = array_keys($keys);
$alphas = range('A', 'Z');
foreach ($result_report as $key => $value) {
$horas_ausencias = floor($value->ausencias / 60);
$minutos_ausencias_restantes = $value->ausencias % 60;
$minutos_ausencias_restantes = $minutos_ausencias_restantes < 10 ? '0' . $minutos_ausencias_restantes : $minutos_ausencias_restantes;
$horas_minutos_ausencias = $horas_ausencias . ':' . $minutos_ausencias_restantes . '';
$value->ausencias = $horas_minutos_ausencias;
$horas_jornada_pactada = floor($value->jornada_pactada / 60);
$minutos_jornada_pactada_restantes = $value->jornada_pactada % 60;
$minutos_jornada_pactada_restantes = $minutos_jornada_pactada_restantes < 10 ? '0' . $minutos_jornada_pactada_restantes : $minutos_jornada_pactada_restantes;
$horas_minutos_jornada_pactada = $horas_jornada_pactada . ':' . $minutos_jornada_pactada_restantes . '';
$value->jornada_pactada = $horas_minutos_jornada_pactada;
$horas_jornada_efectiva = floor($value->jornada_efectiva / 60);
$minutos_jornada_efectiva_restantes = $value->jornada_efectiva % 60;
$minutos_jornada_efectiva_restantes = $minutos_jornada_efectiva_restantes < 10 ? '0' . $minutos_jornada_efectiva_restantes : $minutos_jornada_efectiva_restantes;
$horas_minutos_jornada_efectiva = $horas_jornada_efectiva . ':' . $minutos_jornada_efectiva_restantes . '';
$value->jornada_efectiva = $horas_minutos_jornada_efectiva;
if (property_exists($value, 'grupo')) {
$result_report[$key]->grupo = pondera_objetivo($value->grupo)['grupo'];
}
}
foreach ($keys as $key => $value) {
$header[$alphas[$key]] = strtoupper($value);
}
$rows = $result_report;
$title = "vision_general" . '_' . date('YmdHis');
require_once ROOT_PATH_BASE . '/app/libraries/PHPExcel/PHPExcel.php';
require_once ROOT_PATH_BASE . '/app/libraries/PHPExcel/PHPExcel/Writer/Excel2007.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Evaluacion de Desempeno - MDMN");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Evaluacion Report");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Evaluacion Report");
$objPHPExcel->getProperties()->setDescription("");
foreach ($header as $key => $value) {
$objPHPExcel->getActiveSheet()->SetCellValue($key . '1', $value);
}
$styleArray = array(
'font' => array(
'bold' => true,
)
);
$objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->applyFromArray($styleArray);
for ($i = 65; $i <= 90; $i++) // letters Array
{
$letters[] = chr($i);
}
$cantidad_100 = 0;
$cantidad_90 = 0;
$cantidad_85 = 0;
foreach ($rows as $row_key => $row_value) {
$array_row = array_values((array)$row_value);
//pre($array_row);
for ($i = 0; $i < count($header); $i++) {
$objPHPExcel->getActiveSheet()->SetCellValue($letters[$i] . ($row_key + 2), $array_row[$i]);
//pre($array_row[$i]);
}
$por_efectividad = $array_row[7];
if ($grupo = "mes") {
$por_efectividad = $array_row[7];
}
if ($por_efectividad > 90) {
$cantidad_100++;
}
if ($por_efectividad < 90 & $por_efectividad > 85) {
$cantidad_90++;
}
if ($por_efectividad < 85) {
$cantidad_85++;
}
//pre($array_row);
}
//pre($rows);
$index = count($rows) + 3;
$cantidad_registros = count($rows);
$por_100 = $cantidad_100 * 100 / $cantidad_registros;
$por_90 = $cantidad_90 * 100 / $cantidad_registros;
$por_85 = $cantidad_85 * 100 / $cantidad_registros;
$objPHPExcel->getActiveSheet()->setCellValue("B$index", 'Vista General');
if (!empty($mes) || !empty($anno)) {
$objPHPExcel->getActiveSheet()->setCellValue("E$index", 'Mes/Año');
$index_anno = $index + 1;
$objPHPExcel->getActiveSheet()->setCellValue("E$index_anno", $mes_anno);
}
$objPHPExcel->getActiveSheet()->setCellValue("F$index", 'Cant Registros');
$index++;
$objPHPExcel->getActiveSheet()->setCellValue("F$index", "$cantidad_registros");
$objPHPExcel->getActiveSheet()->setCellValue("B$index", 'Cumplimiento Jornada Laboral');
$objPHPExcel->getActiveSheet()->setCellValue("C$index", "% Trabajadores");
$index++;
$objPHPExcel->getActiveSheet()->setCellValue("B$index", 'Rango 100% a 90%');
$objPHPExcel->getActiveSheet()->setCellValue("C$index", "$por_100");
$index++;
$objPHPExcel->getActiveSheet()->setCellValue("B$index", 'Rango 89% a 85%');
$objPHPExcel->getActiveSheet()->setCellValue("C$index", "$por_90");
$index++;
$objPHPExcel->getActiveSheet()->setCellValue("B$index", 'Rango Inferior a 85%');
$objPHPExcel->getActiveSheet()->setCellValue("C$index", "$por_85");
$objPHPExcel->getActiveSheet()->setTitle(substr($title, 0, 29));
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$saved_file = ROOT_PATH_BASE . '/reports/' . $title . '.xlsx';
$objWriter->save($saved_file);
header("Content-type: application/octet-stream");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=\"$title.xlsx\"\n");
readfile($saved_file);
}
}