sgi/gema/export/xls_alumnocardex.php

<?php

setlocale(LC_TIME, 'es_MX.UTF-8');
require_once("../../include/nocache.php");
require_once("../../include/constantes.php");
require_once("../../include/bd_pdo.php");
require_once("../../include/util.php");
require_once("../../classes/ValidaSesion.php");
require "../../include/phpSpreadsheet/autoload.php";

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Style;
use PhpOffice\PhpSpreadsheet\Shared\Date;


function numberToLetter ($number){
    $temp = ($number-1) % 26;
    return chr($temp + 65);
};


//--- Objeto para validar usuario. El id de usuario lo lee desde sesión

$pag = "../reporte_alumnoscardex.php";

//--- Objeto para validar usuario. El id de usuario lo lee desde sesión
$objSesion = new ValidaSesion($pdo, array(102, 122), GEMA);
if(!$objSesion->tieneAcceso()){
    header("Location: ".$pag."?error=3");
    exit();
}

unset($objValida);

if(!isset($_GET["clave"])){
    header("Location: ".$pag."?error=0");
    exit();
}
$clave = filter_input(INPUT_GET, "clave", FILTER_SANITIZE_NUMBER_INT);//limpia texto

//Obtiene horario del salon

$stmt = $pdo->prepare('Select * from fs_alumno(NULL, :clave)');
$stmt->bindParam(":clave", $clave);
if(!$stmt->execute()){
    header("Location: materias.php?error=2");
    //$errorDesc = "Error al cargar los datos del alumno";
    //print_r($stmt->errorInfo());
    exit();
}
$alumno_rs = $stmt->fetch();
$stmt->closeCursor();

$pag = "../reporte_alumnoscardex_ver.php?id=".$alumno_rs["Usuario_id"];

$stmt = $pdo->prepare('Select * from fs_alumnomaterias_cardex(:clave, :plan)');
$stmt->bindParam(":clave", $clave);
$stmt->bindParam(":plan", $alumno_rs["PlanEstudio_id"]);
if(!$stmt->execute()){
    header("Location: ".$pag."?error=1");
    //$errorDesc = "Ocurrió un error al cargar las materias.";
    //print_r($stmt->errorInfo());
    exit();
}
$materias_rs = $stmt->fetchAll();
$stmt->closeCursor();

//formato al RS de materias
$materiasArr = Array();

$m = 0;
$mat_last = -1;
foreach($materias_rs as $mat){
    if($mat_last != $mat["Materia_id"]){
        if($mat_last != -1){
            $materiasArr[$m]["califArr"]=$califArr;
            $m++;
        }
        $mat_last = $mat["Materia_id"];
        $califArr = array();
    }
    $materiasArr[$m] = array("id" => $mat["Materia_id"], "desc" => $mat["Materia_desc"], "clave"=>$mat["Materia_clave"],
                "sem"=> $mat["Materia_semestre"], "califArr"=>array());
    //if($mat["Calificacion_calif"] != null && $mat["Calificacion_calif"] != ""){
    if($mat["Calificacion_calif"] > 0 || $mat["Calificacion_calif"] === 0 ){//no es null
        $califArr[] = array("calif"=>$mat["Calificacion_calif"], "fecha"=>$mat["Calificacion_fecha"], "calif_tipo"=>$mat["TipoCalificacion_id"], "calif_tipo_desc"=>$mat["TipoCalificacion_desc"], "calif_tipo_corta"=>$mat["TipoCalificacion_desc_corta"],
                "revalidada"=>$mat["TipoCalificacion_esEquivalencia"], "reprobada"=>($mat["Calificacion_calif"] <=5)?true:false, "intersemestral"=>($mat["Periodo_intersemestral"])?true:false);
    }
}
if($mat_last != -1){
    $materiasArr[$m]["califArr"]=$califArr;
}


//--------
//
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Ingeniería La Salle')
    ->setLastModifiedBy('Ingeniería La Salle')
    ->setTitle('Cardex de alumno')
    ->setDescription('CArdex de alumno '.$clave);


$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'],
        ]
    ]
);

$alumnoStyle = new Style();
$alumnoStyle->applyFromArray(
    [
        'font' => [
            'bold'  =>  true,
            'color' => ['argb' => 'FF001d68'],
            'size' => 14,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
        ],
    ]
);

$ordinarioStyle =
    [
        'font' => [
            'color' => ['argb' => 'FF001d68'],
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
$extraStyle =
    [
        'font' => [
            'color' => ['argb' => 'FFd21034'],
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];

$equivalenciaStyle = 
    [
        'font' => [
            'color' => ['argb' => 'FF339933'],
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
$intersemestralStyle = 
    [
        'font' => [
            'color' => ['argb' => 'FFAD60BF'],
            'size' => 10,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
$semestreStyle = 
    [
        'font' => [
            'color' => ['argb' => 'FFFFFFFF'],
            'size' => 14,
            'bold' => true,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
        'fill' =>[
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => [
                'argb' => 'FF001d68',
            ],
        ]
    ];


//--------
$sheet = 0;
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Cardex '.$clave);

$row = 1;
$col = 1;
$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(1, 1, $alumno_rs["Usuario_apellidos"]." ".$alumno_rs["Usuario_nombre"])
                ->setCellValueByColumnAndRow(1, 2, "Clave: ".$clave)
                ->setCellValueByColumnAndRow(1, 3, $alumno_rs["Carrera_desc"]." ".$alumno_rs["PlanEstudio_desc"]);
$colMax = $col;
$colLetter = numberToLetter($colMax-1);

$spreadsheet->setActiveSheetIndex($sheet)
                ->setCellValueByColumnAndRow(7, 1, "Ordinario")
                ->setCellValueByColumnAndRow(7, 2, "Extraordinario")
                ->setCellValueByColumnAndRow(7, 3, "Equivalencia")
                ->setCellValueByColumnAndRow(7, 4, "Intersemestral");

$spreadsheet->getActiveSheet()->getStyle('G1')->applyFromArray($ordinarioStyle);
$spreadsheet->getActiveSheet()->getStyle('G2')->applyFromArray($extraStyle);
$spreadsheet->getActiveSheet()->getStyle('G3')->applyFromArray($equivalenciaStyle);
$spreadsheet->getActiveSheet()->getStyle('G4')->applyFromArray($intersemestralStyle);


$spreadsheet->getActiveSheet()->mergeCells('A1:E1');
$spreadsheet->getActiveSheet()->mergeCells('A2:E2');
$spreadsheet->getActiveSheet()->mergeCells('A3:E3');
$spreadsheet->getActiveSheet()->duplicateStyle($alumnoStyle, 'A1:C3');

//--------
$row = 6;
$maxCol = 5;
$maxCol = 5;
$sem = -1;
if($alumno_rs["Nivel_id"] == 1)
    $nivel_desc = "Semestre";
else
    $nivel_desc = "Cuatrimestre";

foreach($materiasArr as $mat){
    if($sem != intval($mat["sem"])){
        $sem = intval($mat["sem"]);
        if($sem != -1){
            $spreadsheet->setActiveSheetIndex($sheet)->setCellValueByColumnAndRow(1, $row, $nivel_desc ." ". $sem);
            $spreadsheet->getActiveSheet()->getStyle("A".$row)->applyFromArray($semestreStyle);
            $spreadsheet->getActiveSheet()->mergeCells('A'.$row.':F'.$row);
            $row++;
        }
    }

    $spreadsheet->setActiveSheetIndex($sheet)->setCellValueByColumnAndRow(1, $row, $mat["desc"]);
    
    for($c=0; $c < $maxCol; $c++){
        if($c < count($mat["califArr"])){
            if($mat["califArr"][$c]["calif"] >0)
                $spreadsheet->setActiveSheetIndex($sheet)->setCellValueByColumnAndRow(2+$c, $row, $mat["califArr"][$c]["calif"]);
            else{
                if($mat["califArr"][$c]["calif_tipo"] == 2)//ordinario
                    $spreadsheet->setActiveSheetIndex($sheet)->setCellValueByColumnAndRow(2+$c, $row, "SD");
                else
                    $spreadsheet->setActiveSheetIndex($sheet)->setCellValueByColumnAndRow(2+$c, $row, "NP");
            }

            switch($mat["califArr"][$c]["calif_tipo"]){
                case 1:
                    $spreadsheet->getActiveSheet()->getStyle(numberToLetter(2+$c).$row)->applyFromArray($equivalenciaStyle);
                break;
                case 2:
                    if($mat["califArr"][$c]["intersemestral"])
                        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(2+$c).$row)->applyFromArray($intersemestralStyle);
                    else
                        $spreadsheet->getActiveSheet()->getStyle(numberToLetter(2+$c).$row)->applyFromArray($ordinarioStyle);
                break;
                default:
                    $spreadsheet->getActiveSheet()->getStyle(numberToLetter(2+$c).$row)->applyFromArray($extraStyle);
                break;
            }
        }
    }
    $row++;
}



$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(1))->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter(7))->setAutoSize(true);
//Auto ajustar anchos
for($col = 2; $col < $maxCol+2; $col++)
    $spreadsheet->getActiveSheet()->getColumnDimension(numberToLetter($col))->setWidth(3.5);


//$spreadsheet->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
//$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension());

//---------
// 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="alumno_'.$clave.'.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;