<?php

namespace App\Http\Controllers;

use App\Model\AlumnoSeccion;
use App\Model\CicloMalla;
use App\Model\Curso;
use App\Model\Especialidad;
use App\Model\Grupo;
use App\Model\CIclo;
use App\Model\Notas;
use App\Model\User;
use Illuminate\Http\Request;
use Illuminate\View\View;
use Maatwebsite\Excel\Classes\PHPExcel;
use Maatwebsite\Excel\Facades\Excel;
use Yajra\Datatables\Datatables;
use Illuminate\Support\Facades\DB;

class ActaConsolidacionController extends Controller
{

    public function getActaConsolidacion($especialidadID, $periodoID, $grupoID){
        return  Excel::create("acta_consolidacion", function ($excel) use($especialidadID, $periodoID, $grupoID){
            $excel->sheet('Excel', function ($sheet) use($especialidadID, $periodoID, $grupoID) {
                $sheet->mergeCells('B2:C2');
                $sheet->setBorder('B2:W8', 'thin');
                $sheet->setCellValue('B2', "NOMBRE DE LA INSTITUCIÓN");
                $sheet->getStyle('B2')->getAlignment()->setWrapText(true);
                $sheet->setHeight(8, 40);
                $sheet->setHeight(6, 60);
                $sheet->mergeCells('D2:H2');
                $sheet->mergeCells('B3:C3');
                $sheet->mergeCells('B4:C4');
                $sheet->mergeCells('B5:C5');
                $sheet->mergeCells('B6:C6');
                $sheet->mergeCells('B7:C7');
                $sheet->mergeCells('E3:F3');
                $sheet->mergeCells('E4:F4');
                $sheet->mergeCells('E5:F5');
                $sheet->mergeCells('E6:F6');
                $sheet->mergeCells('G3:H3');
                $sheet->mergeCells('G4:H4');
                $sheet->mergeCells('G5:H5');
                $sheet->mergeCells('G6:H6');
                $sheet->setCellValue('D2', 'INSTITUTO SUPERIOR DE EDUCACIÓN PUBLICA "SAN FRANCISCO DE ASIS"');
                $sheet->getStyle('D2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->getStyle('D2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->setCellValue('B3', 'GESTIÓN');
                $sheet->setCellValue('D3', 'PUBLICO');
                $sheet->getStyle('D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->setCellValue('E3', 'CÓDIGO MODULAR');
                $sheet->getStyle('G3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->setCellValue('B4', 'REGIÓN');
                $sheet->setCellValue('D4', 'ICA');
                $sheet->getStyle('D4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->setCellValue('B5', 'PROVINCIA');
                $sheet->getStyle('B5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('B5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('D5', 'CHINCHA ALTA');
                $sheet->getStyle('D5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('D5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('B6', 'DISTRITO');
                $sheet->getStyle('B6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('B6')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('D6', 'CHINCHA');
                $sheet->getStyle('D6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('D6')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('E4', 'SEMESTRE');
                $sheet->setCellValue('E5', 'SECCIÓN');
                $sheet->getStyle('E5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('E5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('E6', 'HORARIO');
                $sheet->getStyle('E6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('E6')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('B7', 'DIRECCIÓN');
                $sheet->getStyle('B7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('B7')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('G4', 'PRIMERO');
                $sheet->getStyle('G4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


                $sheet->getStyle('G6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('G6')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->mergeCells('D7:H7');
                $sheet->setCellValue('D7', 'AV. AMERICA N 209');
                $sheet->getStyle('D7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('D7')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setWidth(array(
                    'B' => 6,
                    'C' => 21,
                    'D' => 15,
                    'E' => 8,
                    'F' => 10,
                    'G' => 30,
                    'H' => 5,
                    'I' => 6,
                    'J' => 6,
                    'K' => 6,
                    'L' => 6,
                    'M' => 6,
                    'N' => 6,
                    'O' => 6,
                    'P' => 6,
                    'Q' => 6,
                    'R' => 6,
                    'S' => 6,
                    'T' => 6,
                    'U' => 6,
                    'V' => 6,
                    'W' => 3
                ));
                $sheet->setCellValue('B8', "N \nOrd.");
                $sheet->getStyle('B8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('B8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('C8', "CÓDIGO \nMATRIC");
                $sheet->getStyle('B8')->getAlignment()->setWrapText(true);
                $sheet->getStyle('C8')->getAlignment()->setWrapText(true);
                $sheet->getStyle('C8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->getStyle('C8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->mergeCells('E8:G8');
                $sheet->setCellValue('E8', 'APELLIDOS Y NOMBRES');
                $sheet->getStyle('E8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('E8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('H8', 'SEXO');
                $sheet->getStyle('H8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->setCellValue('D8', 'DNI');
                $sheet->getStyle('D8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('D8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

                $sheet->cells('B2:C8', function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });

                $sheet->cells('B8:V8', function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });

                $sheet->cells('I2:I5', function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });

                $sheet->cells('E3:E6', function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });

                $sheet->mergeCells('I2:M2');
                $sheet->mergeCells('W5:W8');
                $sheet->mergeCells('N2:W2');

                $sheet->mergeCells('I3:M3');
                $sheet->mergeCells('N3:W3');

                $sheet->mergeCells('I4:M4');
                $sheet->mergeCells('N4:W4');


                $sheet->mergeCells('X8:AC8');
                $sheet->cells('X8', function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });

                $sheet->getStyle('X8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->getStyle('X8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->setBorder('X8', 'thin');
                $sheet->setCellValue('X8', 'Observaciones');


                $sheet->mergeCells('I5:V5');

                $sheet->setCellValue('I5', "UNIDADES DIDÁCTICAS DE LOS MÓDULOS TÉCNICO PROFESIONALES Y MÓDULOS\n TRANSVERSALES EN EL ORDEN DEL PLAN CURRICULAR");
                $sheet->setSize('I5', 5, 30);
                $sheet->getStyle('I5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->getStyle('I5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('I5')->getAlignment()->setWrapText(true);


    //                $sheet->mergeCells('J2:K2');
    //                $sheet->mergeCells('J3:K3');
    //                $sheet->mergeCells('J4:K4');
    //                $sheet->mergeCells('I5:J5');
//

                $especialidad = Especialidad::find($especialidadID);

                $sheet->setCellValue('I2', 'CARRERA PROFESIONAL:');
                $sheet->setCellValue('N2', $especialidad->nombre);

                $sheet->setCellValue('I3', 'AUTORIZACIÓN DE LA CARRERA:');

                $sheet->setCellValue('I4', 'REVALIDACIÓN DE LA CARRERA:');
                $sheet->setCellValue('N4', '');

                $sheet->setCellValue('W5', 'PUNTAJE PROMEDIO PONDERADO');
                $sheet->cell('W5', function($cell) {
                    $cell->setFontSize(7);
                    $cell->setBackground('#f2f2f2');
                });
                $sheet->getStyle('W5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->getStyle('W5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle("W5")->getAlignment()->setTextRotation(90);

                $arr = "IJKLMNOPQRSTUV";
                $ind = 0;
                foreach (range('A', 'N') as $key=>$value) {
                    $sheet->setCellValue($arr[$ind].'8', $value);
                    $sheet->getStyle($arr[$ind].'8')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $sheet->getStyle($arr[$ind].'8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $ind++;
                }

                $grupo = Grupo::where('grupo.id','=',$grupoID)
                    ->first();

                $ciclo = Ciclo::where('ciclo.id','=',$grupo->id)
                    ->first();


                $sheet->setCellValue('G5',$ciclo->nombre);
                $sheet->getStyle('G5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $sheet->getStyle('G5')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

                $ciclom = CicloMalla::join('ciclo','ciclo.id','ciclo_malla.id_ciclo')
                    ->join('curso','curso.id','ciclo_malla.id_curso')
                    ->select(
                        'ciclo.id as id',
                        'ciclo.nombre as ciclo_nombre',
                        DB::raw('COUNT(curso.id) as cantidad')
                    )
                    ->where('ciclo.id','=',$grupo->id_ciclo)
                    ->groupBy('id','ciclo_nombre')
                    ->first();

                $ind = 0;

                $aux = $arr[$ciclom["cantidad"]];
                $sheet->mergeCells('I6:'.$aux.'6');
                $sheet->setCellValue('I6',$ciclom["ciclo_nombre"]);
                $sheet->getStyle('I6')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $sheet->getStyle('I6')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                $cursos = Curso::join('ciclo_malla','ciclo_malla.id_curso','curso.id')
                    ->select(
                        'curso.nombre as nombre'
                    )
                    ->where('ciclo_malla.id_ciclo','=',$ciclom['id'])
                    ->get();


                foreach($cursos as $key => $value){
                    $sheet->setCellValue($arr[$key].'7', $value["nombre"]);
                    $sheet->getStyle($arr[$key].'7')->getAlignment()->setTextRotation(90);
                    $sheet->getStyle($arr[$key].'7')->getAlignment()->setWrapText(true);
                    $sheet->getStyle($arr[$key].'7')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $sheet->getStyle($arr[$key].'7')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                }


                $ciclo = CicloMalla::join('ciclo','ciclo.id','ciclo_malla.id_ciclo')
                    ->join('curso','curso.id','ciclo_malla.id_curso')
                    ->join('malla','malla.id','ciclo_malla.id_malla')
                    ->join('especialidad_periodo','especialidad_periodo.id_especialidad','malla.id_especialidad_periodo')
                    ->select(
                        'curso.id as id',
                        'curso.nombre as nombre'
                    )
                    ->where('ciclo.id','=',$grupo->id_ciclo)
                    ->where('especialidad_periodo.id_especialidad','=',$grupo->id_especialidad)
                    ->where('especialidad_periodo.id_periodo','=',$periodoID)
                    ->orderBy('id', 'ASC')
                    ->get();

                $sheet->setHeight(7, 100);

                $alumno = User::join('alumno','alumno.id_usuario','usuario.id')
                    ->select(
                        'alumno.id as id',
                        'alumno.codigo as codigo',
                        'usuario.dni as dni',
                        DB::raw('CONCAT(UPPER(usuario.apellido_paterno)," ",UPPER(usuario.apellido_materno),", ",usuario.nombres) as nombres'),
                        'usuario.genero as genero'
                    )
                    ->where('alumno.id_especialidad','=',$grupo->id_especialidad)
                    ->orderBy('nombres')
                    ->get();


                $cursost = [];

                foreach($alumno as $key => $value){
                    $aux = 9+$key;
                    $sheet->setCellValue('B'.$aux,$key+1);
                    $sheet->setCellValue('C'.$aux,$value["codigo"]);
                    $sheet->setCellValue('D'.$aux,$value["dni"]);
                    $sheet->setCellValue('E'.$aux,$value["nombres"]);
                    $sheet->mergeCells('E'.$aux.':G'.$aux);
                    $sheet->setCellValue('H'.$aux,$value["genero"][0]);
                    //agregar filtro por seccion
                    $notas = AlumnoSeccion::join('seccion_curso','seccion_curso.id','alumno_seccion.id_seccion_curso')
                        ->join('curso','curso.id','seccion_curso.id_curso')
                        ->select(
                            'alumno_seccion.nota_final as nota',
                            'curso.creditos as peso'
                        )
                        ->where('alumno_seccion.id_alumno','=',$value['id'])
                        ->where('seccion_curso.id_periodo','=',$periodoID)
                        ->orderBy('seccion_curso.id_curso','ASC')
                        ->get();

                    $notaP = 0.0;
                    $peso = 0.0;

                    foreach($notas as $key => $value){
                        $sheet->setCellValue($arr[$key].$aux,$value["nota"]);
                        $notaP = $notaP + $value["nota"]*$value["peso"];
                        if ($value["nota"]!=null) {
                            $peso = $peso + $value["peso"];
                        }


                    }

                    if($peso!=0.0){
                        $notaP = $notaP/$peso;
                    }

                    $sheet->setCellValue('W'.$aux,$notaP);
                    $sheet->mergeCells('X'.$aux.':AC'.$aux);
                }

                $aux = $aux+2;





                $sheet->mergeCells('B'.($aux).':H'.$aux);
                $sheet->cells('B'.($aux), function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });
                $sheet->setBorder('B'.($aux).':H'.$aux, 'thin');
                $sheet->setCellValue('B'.($aux),'APROBADOS');


                $sheet->mergeCells('B'.($aux+1).':H'.($aux+1));
                $sheet->cells('B'.($aux+1), function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });
                $sheet->setBorder('B'.($aux+1).':H'.($aux+1), 'thin');
                $sheet->setCellValue('B'.($aux+1),'DESAPROBADOS');

                $sheet->mergeCells('B'.($aux+2).':H'.($aux+2));
                $sheet->cells('B'.($aux+2), function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });
                $sheet->setBorder('B'.($aux+2).':H'.($aux+2), 'thin');
                $sheet->setCellValue('B'.($aux+2),'DESAPROBADOS POR INASISTENCIA');

                $sheet->mergeCells('B'.($aux+3).':H'.($aux+3));
                $sheet->cells('B'.($aux+3), function ($cells) {
                    $cells->setBackground('#f2f2f2');
                });
                $sheet->setBorder('B'.($aux+3).':H'.($aux+3), 'thin');
                $sheet->setCellValue('B'.($aux+3),'LICENCIA');

                $sheet->cells('B9:B'.($aux-1), function($cells) {
                    $cells->setAlignment('center');
                });
                $sheet->cells('C9:C'.$aux, function($cells) {
                    $cells->setAlignment('center');
                });
                $sheet->cells('D9:D'.$aux, function($cells) {
                    $cells->setAlignment('center');
                });
                $sheet->cells('H9:H'.$aux, function($cells) {
                    $cells->setAlignment('center');
                });

            });
        })->download('xlsx');
    }
}
