<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Support\Facades\Crypt;
use DB;

class lentes_reportesController extends Controller
{
    function index_report(Request $reques)
    {
        $comunidad = DB::select('SELECT * FROM comunidad');
        $ocupacion = DB::select('SELECT * FROM ocupacion');

        $com = array();
        $ocu = array();

        $com[] = ['id' => 'T', 'nombre' => 'TODOS'];
        $ocu[] = ['id' => 'T', 'nombre' => 'TODOS'];

        foreach ($comunidad as $key => $value) {
            $com[] = ['id' => $value->id, 'nombre' => $value->nombre];
        }

        foreach ($ocupacion as $key => $value) {
            $ocu[] = ['id' => $value->id, 'nombre' => $value->nombre];
        }

        $datos = [
            'comunidad' => Collection::make($com)->pluck('nombre','id'),
            'ocupacion' => Collection::make($ocu)->pluck('nombre','id')
        ];

        return view('reportes.lentes.r1', $datos);
    }

    function r1(Request $request){

        if($request->isMethod('post')){
            $desde = $request->input('desde');
            $hasta = $request->input('hasta');
            $comunidad = $request->input('comunidad');
            $estatus = $request->input('estatus');

            $ocupacion = $request->input('ocupacion');
            $sexo = $request->input('sexo');
            $desdee = $request->input('desdee');
            $hastae = $request->input('hastae');

            $sql = '';

            if ($comunidad != 'T') {
                $sql .= ' AND c.id = '.$comunidad;
            }

            if ($estatus == '4') {
                $sql .= ' AND ls.app = "SI"';
            } else if ($estatus == '1' || $estatus == '2' || $estatus == '3') {
                $sql .= ' AND ls.estatus2 = '.$estatus;
            }

            if ($ocupacion != 'T') {
                $sql .= ' AND p.ocupacion = '.$ocupacion;
            }

            if ($desdee != 0 && $hastae != 0) {
                $sql .= ' AND if(ls.menor = "SI", if(TIMESTAMPDIFF(YEAR,mp.fn,CURDATE()) >= '.$desdee.' AND TIMESTAMPDIFF(YEAR,mp.fn,CURDATE()) <= '.$hastae.', TIMESTAMPDIFF(YEAR,mp.fn,CURDATE()),""), if(TIMESTAMPDIFF(YEAR,p.fn,CURDATE()) >= '.$desdee.' AND TIMESTAMPDIFF(YEAR,p.fn,CURDATE()) <= '.$hastae.', TIMESTAMPDIFF(YEAR,p.fn,CURDATE()),"")) ';
            }

            $comunidad = DB::select('SELECT p.*, mp.cedula AS cmenor, mp.nombre AS mmenor, ls.*, jf.*, c.nombre AS comunidad, o.nombre AS ocupacionn, TIMESTAMPDIFF(YEAR,p.fn,CURDATE()) AS edad, TIMESTAMPDIFF(YEAR,mp.fn,CURDATE()) AS edadmenor, p.sexo AS generoa, mp.sexo AS generom FROM usuario AS u INNER JOIN persona AS p ON p.cedula = u.usuario INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN lentes_solicitud AS ls ON ls.id_solicitante = jf.id INNER JOIN comunidad As c ON c.id = jf.id_jefe_com INNER JOIN ocupacion AS o ON o.id = p.ocupacion LEFT JOIN persona AS mp ON mp.id = ls.id_menor WHERE ls.fecha_registro BETWEEN ? AND ? '.$sql.' ORDER BY ls.fecha_registro ASC', [$desde.' 00:00:00', $hasta.' 23:59:59']);

            $spread = new Spreadsheet();
            $spread
            ->getProperties()
            ->setCreator("Nestor Tapia")
            ->setLastModifiedBy('BaulPHP')
            ->setTitle('Excel creado con PhpSpreadSheet')
            ->setSubject('Excel de prueba')
            ->setDescription('Excel generado como demostración')
            ->setKeywords('PHPSpreadsheet')
            ->setCategory('Categoría Excel');

            $spread->setActiveSheetIndex(0)->mergeCells('A1:H1')->setCellValue('A1', 'REPORTE '.$desde.'-'.$hasta);

            $spread->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $spread->getActiveSheet()->getStyle("A1:H1")->getFont()->setBold(true);

            //$spread->setActiveSheetIndex(0)->mergeCells('A2:H2')->setCellValue('A2', 'FECHA '.date('d/m/Y').' - '.$ge);

            $spread->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

            $spread->getActiveSheet()->getStyle("A2:H2")->getFont()->setBold(true);

            $spread->getActiveSheet()->getColumnDimension('A')->setWidth(6);
            $spread->getActiveSheet()->getColumnDimension('B')->setWidth(15);
            $spread->getActiveSheet()->getColumnDimension('C')->setWidth(60);
            $spread->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $spread->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $spread->getActiveSheet()->getColumnDimension('F')->setWidth(15);
            $spread->getActiveSheet()->getColumnDimension('G')->setWidth(60);
            $spread->getActiveSheet()->getColumnDimension('H')->setWidth(8);
            $spread->getActiveSheet()->getColumnDimension('I')->setWidth(8);
            $spread->getActiveSheet()->getColumnDimension('J')->setWidth(30);
            $spread->getActiveSheet()->getColumnDimension('K')->setWidth(80);
            $spread->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $spread->getActiveSheet()->getColumnDimension('M')->setWidth(100);
            $spread->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $spread->getActiveSheet()->getColumnDimension('O')->setWidth(10);
            $spread->getActiveSheet()->getColumnDimension('P')->setWidth(15);

            $spread->getActiveSheet()->getColumnDimension('Q')->setWidth(20);

            $fila = 4;

            $spread->getActiveSheet()->getStyle('A'.$fila.':M'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle('A'.$fila.':M'.$fila)->getFont()->setBold(true);

            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila, 'N°');
            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila, 'CÉDULA');
            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila, 'NOMBRE');
            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila, 'TELÉFONO');

            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila, 'REPRESENTA MENOR');
            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, 'CÉDULA');
            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, 'NOMBRE');

            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila, 'SEXO');
            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila, 'EDAD');
            $spread->setActiveSheetIndex(0)->setCellValue('J'.$fila, 'OCUPACIÓN');

            $spread->setActiveSheetIndex(0)->setCellValue('K'.$fila, 'DIRECCIÓN');
            $spread->setActiveSheetIndex(0)->setCellValue('L'.$fila, 'COMUNIDAD');
            $spread->setActiveSheetIndex(0)->setCellValue('M'.$fila, 'SOLICITUD');

            $spread->setActiveSheetIndex(0)->setCellValue('N'.$fila, 'FECHA REGISTRO');
            $spread->setActiveSheetIndex(0)->setCellValue('O'.$fila, 'VENAPP');
            $spread->setActiveSheetIndex(0)->setCellValue('P'.$fila, 'N° REPORTE');
            $spread->setActiveSheetIndex(0)->setCellValue('Q'.$fila, 'ESTATUS');

            foreach ($comunidad as $key => $value) {
                $fila++;
                $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila, $key+1);
                $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila, $value->cedula);
                $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila, $value->nombre);
                $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila, $value->telefono);
                $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila, $value->menor);
                $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, $value->cmenor);
                $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, $value->mmenor);

                if($value->menor == 'SI'){
                    $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila, $value->generom);
                    $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila, $value->edadmenor);
                } else {
                    $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila, $value->generoa);
                    $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila, $value->edad);
                }
                
                $spread->setActiveSheetIndex(0)->setCellValue('J'.$fila, $value->ocupacionn);

                $spread->setActiveSheetIndex(0)->setCellValue('K'.$fila, $value->direccion);
                $spread->setActiveSheetIndex(0)->setCellValue('L'.$fila, $value->comunidad);

                $spread->setActiveSheetIndex(0)->setCellValue('M'.$fila, $value->descripcion);
                $spread->setActiveSheetIndex(0)->setCellValue('N'.$fila, $value->fecha_registro);
                
                if ($value->app == '0') {
                    $spread->setActiveSheetIndex(0)->setCellValue('O'.$fila, '');
                } else {
                    $spread->setActiveSheetIndex(0)->setCellValue('O'.$fila, $value->app);
                }

                $spread->setActiveSheetIndex(0)->setCellValue('P'.$fila, $value->reporte);
                $spread->setActiveSheetIndex(0)->setCellValue('Q'.$fila, $value->estatus);
            }

            $fileName="Listas de Solicitudes ".date('Y-m-d').".xlsx";
            $sheet = $spread->getActiveSheet();
            $sheet->setTitle("Hoja 1");

            $writer = new Xlsx($spread);

            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$fileName.'"');
            header('Cache-Control: max-age=0');
            ob_end_clean();
            $writer->save('php://output');
            exit();
        }
        
    }

    function key(Request $request){
        //return Crypt::encrypt('15107214');
        return Crypt::decrypt('eyJpdiI6IlwvNDBpaEMrS3VNQnIxK3R3YWdEUTdRPT0iLCJ2YWx1ZSI6InU4QUp1UW50SkFOR2hKaFRzMXlzQnUwamZ4Mzl0RFVKcnJlS2JRR3JOa2M9IiwibWFjIjoiYWUxY2ZlYzJmODdiMmI3YzU4NWFkYTZjODA3YWJlOWNhNzUzNTE0MWM0ZjExMDM2MTE0MWY4MDMwZmU1MWM0YSJ9');
        //return view('errors.503');
    }
}