tieneAcceso()){ header("Location: ".$pag."?error=3"); exit(); } unset($objValida); if(!isset($_POST["fecha_inicial"]) || !isset($_POST["fecha_final"]) || !isset($_POST["area"]) || !isset($_POST["desc"]) /*|| !isset($_POST["periodo"])*/){ //header("Location: ".$pag."?error=0"); echo "

Error, no se recibieron los datos

"; exit(); } //$filter_periodo = filter_input(INPUT_POST, "periodo", FILTER_SANITIZE_NUMBER_INT);//limpia texto $filter_periodo = $_SESSION["periodo_id"]; //Periodo $stmt = $pdo->prepare('Select * from fs_periodo(:id, NULL, NULL, NULL)'); $stmt->bindParam(":id", $filter_periodo); if(!$stmt->execute()){ $errorDesc = "Ocurrió un error al cargar el periodo"; }else{ $periodo_rs = $stmt->fetch(); } $stmt->closeCursor(); //Obtiene profesores if(isset($_POST["fecha_inicial"]) && $_POST["fecha_inicial"]!=""){ $filter_fini = trim(filter_input(INPUT_POST, "fecha_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto } if(isset($_POST["fecha_final"]) && $_POST["fecha_final"]!=""){ $filter_ffin = trim(filter_input(INPUT_POST, "fecha_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto } if(isset($_POST["hora_inicial"]) && $_POST["hora_inicial"]!=""){ $filter_hini = trim(filter_input(INPUT_POST, "hora_inicial", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto }else{ $filter_hini = '00:00'; } if(isset($_POST["hora_final"]) && $_POST["hora_final"]!=""){ $filter_hfin = trim(filter_input(INPUT_POST, "hora_final", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto }else{ $filter_hfin = '23:59'; } $query = ""; if(isset($_POST["desc"]) && $_POST["desc"] != ""){ $query .= ":desc,"; $filter_desc = trim(filter_input(INPUT_POST, "desc", FILTER_SANITIZE_STRING,array('flags' => FILTER_FLAG_STRIP_LOW)));//limpia texto }else{ $query .= "NULL,"; } if(isset($_POST["clave"]) && $_POST["clave"] != ""){ $query .= ":clave,"; $filter_clave = filter_input(INPUT_POST, "clave", FILTER_SANITIZE_NUMBER_INT);//limpia texto }else{ $query .= "NULL,"; } if(isset($_POST["area"]) && is_numeric($_POST["area"]) && trim($_POST["area"]) != ""){ $query .= ":area,"; $filter_area = filter_input(INPUT_POST, "area", FILTER_SANITIZE_NUMBER_INT);//limpia texto }else{ $query .= "NULL,"; } $hoy_fecha = date("Y-m-d"); if(isset($filter_fini)) $fecha_ini = fechaGuion($filter_fini); else $fecha_ini = $periodo_rs["Periodo_fecha_inicial"]; if(isset($filter_ffin)){ $fecha_fin = fechaGuion($filter_ffin); }else{ if($periodo_rs["Periodo_fecha_final"] < $hoy_fecha){ $fecha_fin = $periodo_rs["Periodo_fecha_final"]; }else{ $fecha_fin = $hoy_fecha; } } $stmt = $pdo->prepare('Select * from fs_asistenciaprofesores_all(:per, :fini, :ffin, :hini, :hfin, '.$query.' 0, NULL)');//sólo activos $stmt->bindParam(":per", $filter_periodo); $stmt->bindParam(":fini", $fecha_ini); $stmt->bindParam(":ffin", $fecha_fin); $stmt->bindParam(":hini", $filter_hini); $stmt->bindParam(":hfin", $filter_hfin); if(isset($filter_desc)) $stmt->bindParam(":desc", $filter_desc); if(isset($filter_clave)) $stmt->bindParam(":clave", $filter_clave); if(isset($filter_area)) $stmt->bindParam(":area", $filter_area); if(!$stmt->execute()){ $errorDesc = "Ocurrió un error al cargar los profesores"; print_r($stmt->errorInfo()); }else{ $profesores_rs = $stmt->fetchAll(); } $stmt->closeCursor(); //-------- // // Create new Spreadsheet object $spreadsheet = new Spreadsheet(); // Set document properties $spreadsheet->getProperties()->setCreator('Ingeniería La Salle') ->setLastModifiedBy('Ingeniería La Salle') ->setTitle('Asistencias profesores') ->setDescription('Reporte de asistencia de profesores.'); // Rename worksheet $spreadsheet->getActiveSheet()->setTitle('Asistencias'); $headerStyle = new Style(); $headerStyle->applyFromArray( [ 'fill' => [ 'fillType' => Fill::FILL_SOLID, 'color' => ['argb' => 'FF001d68'], ], 'borders' => [ 'bottom' => ['borderStyle' => Border::BORDER_THIN], 'right' => ['borderStyle' => Border::BORDER_MEDIUM], ], 'font' => [ 'bold' => true, 'color' => ['argb' => 'FFFFFFFF'], ] ] ); $colorRojo = 'ffce0e2d'; // Add some data $spreadsheet->setActiveSheetIndex(0) ->setCellValue('A1', 'Profesor') ->setCellValue('B1', 'Clases total') ->setCellValue('C1', 'Asistencias') ->setCellValue('D1', 'Reposiciones') ->setCellValue('E1', 'Faltas') ->setCellValue('F1', 'Retardos') ->setCellValue('G1', '% Asistencia') ->setCellValue('H1', '% Retardos'); $spreadsheet->getActiveSheet()->duplicateStyle($headerStyle, 'A1:H1'); $highestRow = count($profesores_rs)+1; //Centrar números $spreadsheet->getActiveSheet()->getStyle('B2:F'.$highestRow)->getAlignment()->setHorizontal('center'); $spreadsheet->getActiveSheet()->getStyle('E2:E'.$highestRow)->getFont()->getColor()->setARGB($colorRojo); $spreadsheet->getActiveSheet()->getStyle('G2:H'.($highestRow)) ->getNumberFormat() ->setFormatCode( \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00 ); $row = 2; foreach($profesores_rs as $profesor){ $col = 1; $faltas = $profesor["Faltas"]; $porcentaje_asistencia = 0; $porcentaje_retardo = 0; if( ($profesor["Asistencias"] == "" && $profesor["Reposiciones"] == "") || ($profesor["Asistencias"] + $profesor["Reposiciones"]) == 0){ $faltas = $profesor["Clases_total"]; }else if($profesor["Clases_total"] > 0){ $porcentaje_asistencia = (($profesor["Asistencias"] + $profesor["Reposiciones"])/$profesor["Clases_total"]); $porcentaje_retardo = ($profesor["Retardos"]/($profesor["Asistencias"] + $profesor["Reposiciones"])); } $spreadsheet->setActiveSheetIndex(0) ->setCellValueByColumnAndRow($col++, $row, $profesor["Usuario_apellidos"]." ".$profesor["Usuario_nombre"]) ->setCellValueByColumnAndRow($col++, $row, $profesor["Clases_total"]) ->setCellValueByColumnAndRow($col++, $row, $profesor["Asistencias"]) ->setCellValueByColumnAndRow($col++, $row, $profesor["Reposiciones"]) ->setCellValueByColumnAndRow($col++, $row, $faltas) ->setCellValueByColumnAndRow($col++, $row, $profesor["Retardos"]) ->setCellValueByColumnAndRow($col++, $row, $porcentaje_asistencia) ->setCellValueByColumnAndRow($col++, $row, $porcentaje_retardo); if($porcentaje_asistencia == 0){ $spreadsheet->getActiveSheet()->getStyle('G'.$row) ->getFont()->getColor()->setARGB($colorRojo); } $row++; } //$spreadsheet->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true); $spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension()); //Auto ajustar anchos $spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('D')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('E')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('F')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('G')->setAutoSize(true); $spreadsheet->getActiveSheet()->getColumnDimension('H')->setAutoSize(true); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $spreadsheet->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="asistenciasTotal_'.date("y-m-d").'.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); exit;