<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Codedge\Fpdf\Fpdf\Fpdf;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use DB;



class reportesController extends Controller
{
    function comprobante(Request $request, $id){

        $jefe = DB::select('SELECT p.cedula, p.nombre, c.zona, c.nombre AS comunidad, jf.direccion, jf.calle FROM j_familia AS jf INNER JOIN persona AS p ON p.id = jf.id_persona INNER JOIN jefe_com AS jc ON jc.id = jf.id_jefe_com INNER JOIN comunidad AS c ON c.id_jefe_com = jc.id WHERE jf.id = ?', [$id]);

        $pagos = DB::select('SELECT p.id AS idpagos, zt.id, zt.trimestre, zt.tarifa, p.fecha, zt.zona FROM pagos AS p INNER JOIN zona_tabulador AS zt ON zt.id = p.id_pagos WHERE p.id_familia = ? AND p.estatus = ?', [$id, "AC"]);

        $pdf = new Fpdf();

        foreach ($pagos as $key => $value) {

            $pdf->AddPage('P', 'Letter');
            $pdf->SetFont('Arial','B',10);
            $pdf->ln(4);
            $pdf->Cell(0,0,utf8_decode('REPÚBLICA BOLIVARIANA DE VENEZUELA'),0,1,'C');
            $pdf->ln(4);
            $pdf->Cell(0,0,$pdf->Image('assets/img/1.jpg',10,14,45,15),0,1,'');
            $pdf->Cell(0,0,utf8_decode('ESTADO YARACUY'),0,1,'C');
            $pdf->ln(4);
            $pdf->Cell(0,0,utf8_decode('ALCALDÍA MUNICIPIO INDEPENDENCIA'),0,1,'C');
            $pdf->ln(4);
            $pdf->Cell(0,0,utf8_decode('DIRECCIÓN DE LIQUIDACIÓN RENTAS MUNICIPALES'),0,1,'C');
            $pdf->ln(4);
            $pdf->Cell(0,0,utf8_decode('RIF: G-20000634-0'),0,1,'C');
            $pdf->ln(8);
            $pdf->SetFont('Arial','B',8);
            $date=date_create($value->fecha);
            $pdf->Cell(0,0,utf8_decode('FECHA: '.date_format($date,"d/m/Y")),0,1,'R');
            $pdf->SetFont('Arial','B',12);
            $pdf->ln(8);
            $pdf->Cell(0,0,utf8_decode('RECIBO DE PAGO NRO. '.str_pad($value->idpagos, 7, "0", STR_PAD_LEFT)),0,1,'C');
            $pdf->SetFont('Arial','',8);
            $pdf->ln(10);
            $pdf->Cell(0,0,utf8_decode('CÉDULA: '.$jefe[0]->cedula),0,1,'');
            $pdf->SetX(50);
            $pdf->Cell(0,0,utf8_decode('CONTRIBUYENTE: '.$jefe[0]->nombre),0,0,'');
            $pdf->ln(7);
            $pdf->Cell(0,0,utf8_decode('CÓDIGO INGRESOS ASEO DOMICILIARIO: 301-03-54-00'),0,1,'');
            $pdf->ln(7);
            $pdf->Cell(0,0,utf8_decode('TIPO DE PAGO: DEPOSITO BANCO VENEZUELA: 01020743610000655293'),0,1,'');
            $pdf->ln(7);
            $pdf->Cell(0,0,utf8_decode('CONCEPTO: SERVICIO ASEO Y RELLENO SANITARIO // RESIDENCIAL'),0,1,'');
            $pdf->ln(7);
            $pdf->Cell(0,0,utf8_decode('ZONA '.$jefe[0]->zona.' - COMUNIDAD '.$jefe[0]->comunidad),0,1,'');
            $pdf->ln(7);
            $pdf->Cell(0,0,utf8_decode('N° CASA: '.$jefe[0]->calle.', DIRECCIÓN: '.$jefe[0]->direccion),0,1,'');

            $pdf->SetFont('Arial','B',9);
            $pdf->ln(25);
            $pdf->SetX(130);
            $pdf->Cell(0,0,utf8_decode('RELACIÓN DE PAGOS POR TRIMESTRES'),0,0,'');

            $pdf->ln(5);
            $pdf->SetFont('Arial','',8);

            $total = 0;
        
            $pdf->SetX(135);
            $pdf->Cell(35,4,utf8_decode($this->TRIMESTRE($value->trimestre). " TRIMESTRE ZONA ".$value->zona),0,0,'R',false);
            $pdf->Cell(35,4,number_format($value->tarifa, 2, ',','.'),0,0,'R',false);
            $total = $total + $value->tarifa;
            $pdf->Ln();

            $pdf->SetFont('Arial','B',8);
            $pdf->ln(5);
            $pdf->SetX(135);
            $pdf->Cell(35,4,utf8_decode("TOTAL PAGADO"),0,0,'R',false);
            $pdf->Cell(35,4,number_format($total, 2, ',','.'),0,0,'R',false);
        }

        $pdf->Output();
        exit;

        
    }

    function TRIMESTRE($v){
        $t = array(
            '1' => 'I',
            '2' => 'II',
            '3' => 'III',
            '4' => 'IV',
            '5' => 'V',
            '6' => 'VI',
            '7' => 'VII',
            '8' => 'VIII',
            '9' => 'IX',
            '10' => 'X',
        );

        return $t[$v];
    }

    function pagos(Request $request){
        if($request->isMethod('post')){
            $szona = $request->input('szona');
            $scomunidad = $request->input('scomunidad');

            $dd = $request->input('desde');
            $ht = $request->input('hasta');

            $tipo = $request->input('tipo');

            $desde = date("Y-m-d", strtotime(str_replace('/', '-',$dd)));
            $hasta = date("Y-m-d", strtotime(str_replace('/', '-',$ht)));

            $sql = '';
            $ge = '';

            $accion = 0;
            $fila = 0;

            $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');

            if ($szona != "T") {
                $sql .= " AND c.zona = '".$szona."'";
                $ge .= 'ZONA: '.$szona;
                $accion = 0;
                $fila = 1;
            } else {
                $ge .= 'ZONA: TODAS ';
                $accion = 1;
                $fila = 11;
            }

            if ($scomunidad != "T") {
                $sql .= " AND c.id = '".$scomunidad."'";
                $cc = DB::select('SELECT nombre FROM comunidad WHERE id = ?', [$scomunidad]);

                $ge .= ' - COMUNIDAD: '.$cc[0]->nombre;
                $accion = 0;
                $fila = 1;
            } else {
                $ge .= ' - COMUNIDAD: TODAS';
                $accion = 1;
                $fila = 11;
            }

            $styleArray = [
                'borders' => [
                    'outline' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                        'color' => ['argb' => 'FFFF0000'],
                    ],
                ],
            ];

            $spread->setActiveSheetIndex(0)->mergeCells('A1:H1')->setCellValue('A1', 'REPORTE DE PAGO '.$dd.'-'.$ht);
            $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->getStyle('B6:F11')->applyFromArray($styleArray);

            if ($tipo == "C") {
                $spread->getActiveSheet()->getColumnDimension('B')->setWidth(12);
                $spread->getActiveSheet()->getColumnDimension('C')->setWidth(20);
                $spread->getActiveSheet()->getColumnDimension('D')->setWidth(20);
                $spread->getActiveSheet()->getColumnDimension('E')->setWidth(20);
                $spread->getActiveSheet()->getColumnDimension('F')->setWidth(20);
                $spread->getActiveSheet()->getColumnDimension('G')->setWidth(20);

                $pagos = DB::select('SELECT zt.trimestre, c.zona, c.nombre, c.id, SUM(zt.tarifa) AS sumas, count(zt.id) AS total, c.ncasas FROM zona_tabulador AS zt INNER JOIN pagos AS p ON p.id_pagos = zt.id INNER JOIN j_familia AS jf ON jf.id = p.id_familia INNER JOIN comunidad AS c ON c.id_jefe_com = jf.id_jefe_com WHERE p.fecha BETWEEN ? AND ? '.$sql.' AND p.estatus = ? GROUP BY zt.trimestre, c.zona, c.nombre, c.id, c.ncasas ORDER BY c.zona, c.nombre, zt.trimestre ASC', [$desde.' 00:00:00', $hasta.' 23:59:59', "AC"]);
                

                if (!empty($pagos)) {
                    $com = 0;

                    $totales = array();

                    $totalesgc = 0;
                    $corte = 0;

                    $full_c = count($pagos);
                    $full_c = $full_c - 1;

                    $ncasas = 0;

                    foreach ($pagos as $key => $value) {
                        if ($value->id == $com) {
                            //SEGUNDO CICLO
                            $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila, $this->TRIMESTRE($value->trimestre));
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila, $value->ncasas);
                            
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila, $value->total);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila, number_format(($value->total*100)/$value->ncasas, 2, ',','.'));
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, $value->ncasas - $value->total);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, number_format($value->sumas, 2, ',','.'));

                            $t2 = !empty($totales[$value->trimestre]['casa']) ? $totales[$value->trimestre]['casa'] : 0;
                            $t3 = !empty($totales[$value->trimestre]['bs']) ? $totales[$value->trimestre]['bs'] : 0;
                            //$t4 = !empty($totales[$value->trimestre]['totalcasas']) ? $totales[$value->trimestre]['totalcasas'] : 0;

                            $t2 = $t2 + $value->total;
                            $t3 = $t3 + $value->sumas;

                            $totales[$value->trimestre] = array('trimestre' => $value->trimestre, 'casa' => $t2, 'bs' => $t3);

                            $totalesgc = $totalesgc + $value->sumas;

                            $corte = 1;

                            $fila++;

                            if ($full_c == $key) {
                                if($corte == 1){
                                    $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                                    $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getFont()->setBold(true);
        
                                    $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, 'TOTAL');
                                    $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, number_format($totalesgc, 2, ',','.'));
        
                                    $totalesgc = 0;
                                    $corte = 0;
                                }
                            }
                        } else {
                            //PRIMER CICLO
                            if($corte == 1){
                                $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                                $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getFont()->setBold(true);

                                $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, 'TOTAL');
                                $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, number_format($totalesgc, 2, ',','.'));

                                $totalesgc = 0;
                                $corte = 0;
                            }

                            $fila = $fila + 4;

                            $com = $value->id;

                            $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                            $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getFont()->setBold(true);
                            $spread->setActiveSheetIndex(0)->mergeCells('B'.$fila.':G'.$fila)->setCellValue('B'.$fila, 'Zona: '.$value->zona.' - Comunidad: '.$value->nombre);

                            $fila++;

                            $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                            $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getFont()->setBold(true);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila, 'TRIMESTRE');
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila, 'CASAS COMUNIDAD');
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila, 'CASAS PAGADAS');
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila, 'CASAS PAGADAS %');
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, 'CASAS POR PAGAR');
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, 'BS');
                            $fila++;

                            $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila, $this->TRIMESTRE($value->trimestre));
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila, $value->ncasas);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila, $value->total);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila, number_format(($value->total*100)/$value->ncasas, 2, ',','.'));
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, $value->ncasas - $value->total);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, number_format($value->sumas, 2, ',','.'));

                            $t2 = !empty($totales[$value->trimestre]['casa']) ? $totales[$value->trimestre]['casa'] : 0;
                            $t3 = !empty($totales[$value->trimestre]['bs']) ? $totales[$value->trimestre]['bs'] : 0;

                            $t2 = $t2 + $value->total;
                            $t3 = $t3 + $value->sumas;
                            $ncasas = $ncasas + $value->ncasas;

                            $totales[$value->trimestre] = array('trimestre' => $value->trimestre, 'casa' => $t2, 'bs' => $t3);

                            $totalesgc = $totalesgc + $value->sumas;

                            $fila++;
                            $corte = 1;

                            if ($full_c == $key) {
                                if($corte == 1){
                                    $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                                    $spread->getActiveSheet()->getStyle('B'.$fila.':G'.$fila)->getFont()->setBold(true);
        
                                    $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, 'TOTAL');
                                    $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, number_format($totalesgc, 2, ',','.'));
        
                                    $totalesgc = 0;
                                    $corte = 0;
                                }
                            }
                        }
                    }

                    if ($accion == 1) {
                        $fila2 = 1;
                        $fila2 = $fila2 + 4;

                        $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                        $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getFont()->setBold(true);
                        $spread->setActiveSheetIndex(0)->mergeCells('B'.$fila2.':G'.$fila2)->setCellValue('B'.$fila2, 'TOTALES GLOBAL');

                        $fila2++;

                        $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                        $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getFont()->setBold(true);
                        $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, 'TRIMESTRE');
                        $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, 'CASAS COMUNIDAD');
                        $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, 'CASAS PAGADAS');
                        $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, 'CASAS PAGADAS %');
                        $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, 'CASAS POR PAGAR');
                        $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, 'BS');
                        $fila2++;

                        $totalesg = 0;
                        foreach ($totales as $key => $value) {
                            $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $this->TRIMESTRE($value['trimestre']));
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $ncasas);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value['casa']);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, number_format(($value['casa']*100)/$ncasas, 2, ',','.'));
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $ncasas - $value['casa']);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, number_format($value['bs'], 2, ',','.'));

                            $totalesg = $totalesg + $value['bs'];

                            $fila2++;
                        }
                    
                        $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                        $spread->getActiveSheet()->getStyle('B'.$fila2.':G'.$fila2)->getFont()->setBold(true);

                        $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, 'TOTAL');
                        $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, number_format($totalesg, 2, ',','.'));
                    }

                }
            } else if ($tipo == "L") {
                $spread->getActiveSheet()->getColumnDimension('A')->setWidth(8);
                $spread->getActiveSheet()->getColumnDimension('B')->setWidth(12);
                $spread->getActiveSheet()->getColumnDimension('C')->setWidth(45);
                $spread->getActiveSheet()->getColumnDimension('D')->setWidth(15);

                $spread->getActiveSheet()->getColumnDimension('E')->setWidth(14);
                $spread->getActiveSheet()->getColumnDimension('F')->setWidth(12);
                $spread->getActiveSheet()->getColumnDimension('G')->setWidth(14);
                $spread->getActiveSheet()->getColumnDimension('H')->setWidth(12);
                $spread->getActiveSheet()->getColumnDimension('I')->setWidth(14);
                $spread->getActiveSheet()->getColumnDimension('J')->setWidth(12);
                $spread->getActiveSheet()->getColumnDimension('K')->setWidth(14);
                $spread->getActiveSheet()->getColumnDimension('L')->setWidth(12);
                $spread->getActiveSheet()->getColumnDimension('M')->setWidth(14);

                $pagos = DB::select('SELECT jf.id, pe.cedula, pe.nombre AS pnombre, pe.telefono, c.zona, c.nombre, c.ncasas FROM zona_tabulador AS zt INNER JOIN pagos AS p ON p.id_pagos = zt.id INNER JOIN j_familia AS jf ON jf.id = p.id_familia INNER JOIN comunidad AS c ON c.id_jefe_com = jf.id_jefe_com INNER JOIN persona AS pe ON pe.id = jf.id_persona WHERE p.fecha BETWEEN ? AND ? '.$sql.' AND p.estatus = ? GROUP BY jf.id, pe.cedula, pe.nombre, pe.telefono, c.zona, c.nombre, c.ncasas ORDER BY pe.cedula ASC', [$desde.' 00:00:00', $hasta.' 23:59:59', "AC"]);

                $fila = 15;

                $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, 'I TRIMESTRE');
                $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila, 'RECIBO I');
                $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila, 'II TRIMESTRE');
                $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila, 'RECIBO II');
                $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila, 'III TRIMESTRE');
                $spread->setActiveSheetIndex(0)->setCellValue('J'.$fila, 'RECIBO III');
                $spread->setActiveSheetIndex(0)->setCellValue('K'.$fila, 'IV TRIMESTRE');
                $spread->setActiveSheetIndex(0)->setCellValue('L'.$fila, 'RECIBO IV');
                $spread->setActiveSheetIndex(0)->setCellValue('M'.$fila, 'TOTAL');
                

                if (!empty($pagos)){
                    $totalesglobal = array();

                    foreach ($pagos as $key => $value) {
                        $fila++;

                        $spread->getActiveSheet()->getStyle('A'.$fila.':M'.$fila)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                        $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila, $key + 1);
                        $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila, $value->cedula);
                        $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila, $value->pnombre);
                        $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila, $value->telefono);

                        $pagos1 = DB::select('SELECT p.id AS idpagos, zt.tarifa, zt.trimestre FROM pagos AS p INNER JOIN zona_tabulador AS zt ON zt.id = p.id_pagos WHERE p.id_familia = ? AND p.estatus = ? ORDER BY zt.trimestre ASC', [$value->id, "AC"]);

                        $column = array('E', 'F', 'G', 'H', 'I', 'J', 'K', 'L');

                        $secuencia = 0;
                        $totales = 0;

                        foreach ($pagos1 as $key1 => $value2) {
                            
                            $spread->setActiveSheetIndex(0)->setCellValue($column[$secuencia].$fila, number_format($value2->tarifa, 2, ',','.'));

                            $secuencia++;

                            $spread->setActiveSheetIndex(0)->setCellValue($column[$secuencia].$fila, str_pad($value2->idpagos, 7, "0", STR_PAD_LEFT));

                            $totales = $totales + $value2->tarifa;

                            $secuencia++;

                            $t2 = !empty($totalesglobal[$value2->trimestre]['casa']) ? $totalesglobal[$value2->trimestre]['casa'] : 0;
                            $t3 = !empty($totalesglobal[$value2->trimestre]['bs']) ? $totalesglobal[$value2->trimestre]['bs'] : 0;

                            $t2 = $t2 + 1;
                            $t3 = $t3 + $value2->tarifa;

                            $totalesglobal[$value2->trimestre] = array('trimestre' => $value2->trimestre, 'casa' => $t2, 'bs' => $t3, 'ncasas' => $value->ncasas);

                        }

                        $spread->setActiveSheetIndex(0)->setCellValue('M'.$fila, number_format($totales, 2, ',','.'));
                    }

                    $fila2 = 1;
                    $fila2 = $fila2 + 4;

                    $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getFont()->setBold(true);
                    $spread->setActiveSheetIndex(0)->mergeCells('B'.$fila2.':F'.$fila2)->setCellValue('B'.$fila2, 'TOTALES GLOBAL');

                    $fila2++;

                    $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getFont()->setBold(true);
                    $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, 'TRIMESTRE');
                    $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, 'CASAS COMUNIDAD');
                    $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, 'CASAS PAGADAS');
                    $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, 'CASAS POR PAGAR');
                    $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, 'BS');
                    $fila2++;

                    $totalesg = 0;
                    foreach ($totalesglobal as $key => $value) {
                        $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

                        $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $this->TRIMESTRE($value['trimestre']));
                        $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value['ncasas']);
                        $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value['casa']);
                        $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value['ncasas'] - $value['casa']);
                        $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, number_format($value['bs'], 2, ',','.'));

                        $totalesg = $totalesg + $value['bs'];

                        $fila2++;
                    }
                
                    $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
                    $spread->getActiveSheet()->getStyle('B'.$fila2.':F'.$fila2)->getFont()->setBold(true);

                    $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, 'TOTAL');
                    $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, number_format($totalesg, 2, ',','.'));
                    
                }
            }
            
            $fileName="Descarga_excel.xlsx";
            # Crear un "escritor"
            $sheet = $spread->getActiveSheet();
            $sheet->setTitle("Hoja 1");

            $writer = new Xlsx($spread);
            # Le pasamos la ruta de guardado

            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();
        }

        return view('reportes.pagos');
    }

    function participantes(Request $request){
        return view('reportes.participantes');
    }

    function participantes1(Request $request){

        $szona = $request->input('szona');
        $trmestres = $request->input('trimestres');

        $tipo = $request->input('tipo');

        $dd = $request->input('desde');
        $ht = $request->input('hasta');

        $desde = date("Y-m-d", strtotime(str_replace('/', '-',$dd)));
        $hasta = date("Y-m-d", strtotime(str_replace('/', '-',$ht)));

        $sql = "";

        if ($szona == "T") {
            $sql = "";
        } else {
            $sql .= "c.zona = '".$szona."' AND ";
        }

        if ($tipo == "LE") {
            $listado = DB::select('SELECT jf.id, zt.trimestre, pa.id AS numero, SUBSTRING_INDEX(p.cedula, "-", 1) AS iden, SUBSTRING_INDEX(p.cedula, "-", -1) AS cedula, p.nombre, p.telefono, c.zona, c.nombre AS comunidad, jf.calle, jf.direccion FROM persona AS p INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN comunidad AS c ON c.id = jf.id_jefe_com INNER JOIN pagos AS pa ON pa.id_familia = jf.id INNER JOIN zona_tabulador AS zt ON zt.id = pa.id_pagos WHERE p.no_participar = ? AND '.$sql.' pa.fecha BETWEEN ? AND ? AND pa.estatus = ? ORDER BY cedula, id, trimestre ASC', [0, $desde.' 00:00:00', $hasta.' 23:59:59', "AC"]);


            $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 DE PARTICIPANTES');
            $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').' - TRIMESTRE '.$trmestres);
            $spread->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle("A2:H2")->getFont()->setBold(true);

            $fila2 = 5;

            $spread->getActiveSheet()->getStyle('A'.$fila2.':H'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle('A'.$fila2.':H'.$fila2)->getFont()->setBold(true);

            $spread->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('E')->setWidth(15);
            $spread->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);

            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, 'N°');
            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, 'IDENTIFICADOR');
            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, 'CÉDULA');
            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, 'NOMBRE Y APELLIDO');
            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, 'ZONA');
            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, 'COMUNIDAD');
            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, 'DIRECCIÓN');
            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, 'N° CASA');
            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, 'N° RECIBO');

            $cont = 0;

            $trimestre_1 = 0;
            $trimestre_2 = 0;
            $trimestre_3 = 0;
            $trimestre_4 = 0;

            $nrecibo_1 = 0;
            $nrecibo_2 = 0;
            $nrecibo_3 = 0;
            $nrecibo_4 = 0;

            foreach ($listado as $key => $value) {
                
                if ($value->trimestre == 1) {
                    $trimestre_1 = 1;
                    $nrecibo_1 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 2) {
                    $trimestre_2 = 1;
                    $nrecibo_2 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 3) {
                    $trimestre_3 = 1;
                    $nrecibo_3 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 4) {
                    $trimestre_4 = 1;
                    $nrecibo_4 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                switch ($trmestres) {
                    case '1':
                        if($trimestre_1 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_1);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '2':
                        if($trimestre_1 == 1 && $trimestre_2 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_2);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '3':
                        if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_3);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '4':
                        if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1 && $trimestre_4 == 1 ){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_4);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                }

                
            }

            $fileName="Participantes ".date('d-m-Y H-i-s').".xlsx";
            # Crear un "escritor"
            $sheet = $spread->getActiveSheet();
            $sheet->setTitle("Hoja 1");

            $writer = new Xlsx($spread);
            # Le pasamos la ruta de guardado

            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();
        } else if ($tipo == "LEN") {
            $listado = DB::select('SELECT jf.id, zt.trimestre, pa.id AS numero, SUBSTRING_INDEX(p.cedula, "-", 1) AS iden, SUBSTRING_INDEX(p.cedula, "-", -1) AS cedula, p.nombre, p.telefono, c.zona, c.nombre AS comunidad, jf.calle, jf.direccion FROM persona AS p INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN comunidad AS c ON c.id = jf.id_jefe_com INNER JOIN pagos AS pa ON pa.id_familia = jf.id INNER JOIN zona_tabulador AS zt ON zt.id = pa.id_pagos WHERE p.no_participar = ? AND '.$sql.' pa.fecha BETWEEN ? AND ? AND pa.estatus = ? ORDER BY cedula, id, trimestre ASC', [1, $desde.' 00:00:00', $hasta.' 23:59:59', "AC"]);


            $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 DE PARTICIPANTES');
            $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').' - TRIMESTRE '.$trmestres);
            $spread->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle("A2:H2")->getFont()->setBold(true);

            $fila2 = 5;

            $spread->getActiveSheet()->getStyle('A'.$fila2.':H'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle('A'.$fila2.':H'.$fila2)->getFont()->setBold(true);

            $spread->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('E')->setWidth(15);
            $spread->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);

            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, 'N°');
            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, 'IDENTIFICADOR');
            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, 'CÉDULA');
            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, 'NOMBRE Y APELLIDO');
            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, 'ZONA');
            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, 'COMUNIDAD');
            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, 'DIRECCIÓN');
            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, 'N° CASA');
            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, 'N° RECIBO');

            $cont = 0;

            $trimestre_1 = 0;
            $trimestre_2 = 0;
            $trimestre_3 = 0;
            $trimestre_4 = 0;

            $nrecibo_1 = 0;
            $nrecibo_2 = 0;
            $nrecibo_3 = 0;
            $nrecibo_4 = 0;

            foreach ($listado as $key => $value) {
                
                if ($value->trimestre == 1) {
                    $trimestre_1 = 1;
                    $nrecibo_1 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 2) {
                    $trimestre_2 = 1;
                    $nrecibo_2 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 3) {
                    $trimestre_3 = 1;
                    $nrecibo_3 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 4) {
                    $trimestre_4 = 1;
                    $nrecibo_4 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                switch ($trmestres) {
                    case '1':
                        if($trimestre_1 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_1);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '2':
                        if($trimestre_1 == 1 && $trimestre_2 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_2);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '3':
                        if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_3);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '4':
                        if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1 && $trimestre_4 == 1 ){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_4);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                }

                
            }

            $fileName="Participantes ".date('d-m-Y H-i-s').".xlsx";
            # Crear un "escritor"
            $sheet = $spread->getActiveSheet();
            $sheet->setTitle("Hoja 1");

            $writer = new Xlsx($spread);
            # Le pasamos la ruta de guardado

            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();
        } else if($tipo == "LET"){
            $listado = DB::select('SELECT jf.id, zt.trimestre, pa.id AS numero, SUBSTRING_INDEX(p.cedula, "-", 1) AS iden, SUBSTRING_INDEX(p.cedula, "-", -1) AS cedula, p.nombre, p.telefono, c.zona, c.nombre AS comunidad, jf.calle, jf.direccion FROM persona AS p INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN comunidad AS c ON c.id = jf.id_jefe_com INNER JOIN pagos AS pa ON pa.id_familia = jf.id INNER JOIN zona_tabulador AS zt ON zt.id = pa.id_pagos WHERE '.$sql.' pa.fecha BETWEEN ? AND ? AND pa.estatus = ? ORDER BY cedula, id, trimestre ASC', [$desde.' 00:00:00', $hasta.' 23:59:59', "AC"]);


            $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', 'REPORTES TODOS');
            $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').' - TRIMESTRE '.$trmestres);
            $spread->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle("A2:H2")->getFont()->setBold(true);

            $fila2 = 5;

            $spread->getActiveSheet()->getStyle('A'.$fila2.':H'.$fila2)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
            $spread->getActiveSheet()->getStyle('A'.$fila2.':H'.$fila2)->getFont()->setBold(true);

            $spread->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('E')->setWidth(15);
            $spread->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
            $spread->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);

            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, 'N°');
            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, 'IDENTIFICADOR');
            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, 'CÉDULA');
            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, 'NOMBRE Y APELLIDO');
            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, 'ZONA');
            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, 'COMUNIDAD');
            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, 'DIRECCIÓN');
            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, 'N° CASA');
            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, 'N° RECIBO');

            $cont = 0;

            $trimestre_1 = 0;
            $trimestre_2 = 0;
            $trimestre_3 = 0;
            $trimestre_4 = 0;

            $nrecibo_1 = 0;
            $nrecibo_2 = 0;
            $nrecibo_3 = 0;
            $nrecibo_4 = 0;

            foreach ($listado as $key => $value) {
                
                if ($value->trimestre == 1) {
                    $trimestre_1 = 1;
                    $nrecibo_1 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 2) {
                    $trimestre_2 = 1;
                    $nrecibo_2 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 3) {
                    $trimestre_3 = 1;
                    $nrecibo_3 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                if ($value->trimestre == 4) {
                    $trimestre_4 = 1;
                    $nrecibo_4 = str_pad($value->numero, 7, "0", STR_PAD_LEFT);
                }

                switch ($trmestres) {
                    case '1':
                        if($trimestre_1 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_1);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '2':
                        if($trimestre_1 == 1 && $trimestre_2 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_2);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '3':
                        if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_3);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                    case '4':
                        if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1 && $trimestre_4 == 1 ){

                            $fila2++;
                            $cont++;
            
                            $spread->setActiveSheetIndex(0)->setCellValue('A'.$fila2, $cont);
                            $spread->setActiveSheetIndex(0)->setCellValue('B'.$fila2, $value->iden);
                            $spread->setActiveSheetIndex(0)->setCellValue('C'.$fila2, $value->cedula);
                            $spread->setActiveSheetIndex(0)->setCellValue('D'.$fila2, $value->nombre);
                            $spread->setActiveSheetIndex(0)->setCellValue('E'.$fila2, $value->zona);
                            $spread->setActiveSheetIndex(0)->setCellValue('F'.$fila2, $value->comunidad);
                            $spread->setActiveSheetIndex(0)->setCellValue('G'.$fila2, $value->direccion);
                            $spread->setActiveSheetIndex(0)->setCellValue('H'.$fila2, $value->calle);
            
                            $spread->setActiveSheetIndex(0)->setCellValue('I'.$fila2, $nrecibo_4);
            
                            $trimestre_1 = 0;
                            $trimestre_2 = 0;
                            $trimestre_3 = 0;
                            $trimestre_4 = 0;
            
                            $nrecibo_1 = 0;
                            $nrecibo_2 = 0;
                            $nrecibo_3 = 0;
                            $nrecibo_4 = 0;
                        }
                        break;
                }

                
            }

            $fileName="Participantes ".date('d-m-Y H-i-s').".xlsx";
            # Crear un "escritor"
            $sheet = $spread->getActiveSheet();
            $sheet->setTitle("Hoja 1");

            $writer = new Xlsx($spread);
            # Le pasamos la ruta de guardado

            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();
        } else if($tipo == "MC"){
            return redirect()->route('mini_comprobante',$desde.'--'.$hasta.'--'.$szona);
        } 

    }

    function mini_comprobante(Request $request, $id){

        $d = explode('--', $id);

        $sql = "";

        if ($d[2] == "T") {
            $sql = "";
        } else {
            $sql .= "c.zona = '".$d[2]."' AND ";
        }

        $listado = DB::select('SELECT jf.id, zt.trimestre, pa.id AS idpagos, p.cedula, p.nombre, p.telefono, c.zona, c.nombre AS comunidad, jf.calle, jf.direccion, pa.fecha, zt.tarifa FROM persona AS p INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN comunidad AS c ON c.id = jf.id_jefe_com INNER JOIN pagos AS pa ON pa.id_familia = jf.id INNER JOIN zona_tabulador AS zt ON zt.id = pa.id_pagos WHERE '.$sql.' p.no_participar = ? AND pa.fecha BETWEEN ? AND ? AND pa.estatus = ? ORDER BY cedula, id, trimestre ASC', [0,$d[0].' 00:00:00', $d[1].' 23:59:59', "AC"]);

        $pdf = new Fpdf();

        $pdf->AddPage('P', 'Letter');
        $pdf->SetFont('Arial','B',5);

        $fila = 5;
        $count_fila = 0;
        $count_columna = 0;

        $trimestre_1 = 0;
        $trimestre_2 = 0;
        $trimestre_3 = 0;
        $trimestre_4 = 0;

        $nrecibo_1 = 0;
        $nrecibo_2 = 0;
        $nrecibo_3 = 0;
        $nrecibo_4 = 0;
        

        foreach ($listado as $key => $value) {

            if ($value->trimestre == 1) {
                $trimestre_1 = 1;
                $nrecibo_1 = str_pad($value->idpagos, 7, "0", STR_PAD_LEFT);
            }

            if ($value->trimestre == 2) {
                $trimestre_2 = 1;
                $nrecibo_2 = str_pad($value->idpagos, 7, "0", STR_PAD_LEFT);
            }

            if ($value->trimestre == 3) {
                $trimestre_3 = 1;
                $nrecibo_3 = str_pad($value->idpagos, 7, "0", STR_PAD_LEFT);
            }

            if ($value->trimestre == 4) {
                $trimestre_4 = 1;
                $nrecibo_4 = str_pad($value->idpagos, 7, "0", STR_PAD_LEFT);
            }



            if ($count_fila == $fila) {
                $pdf->SetFont('Arial','I',7);
                $pdf->Cell(0,10,utf8_decode('Página ').$pdf->PageNo(),0,0,'C');

                $pdf->AddPage('P', 'Letter');
                $pdf->SetFont('Arial','B',5);

                $count_fila = 0;
            }

            if($trimestre_1 == 1 && $trimestre_2 == 1 && $trimestre_3 == 1 && $trimestre_4 == 1 ){
                if ($count_columna != 1) {
                    $count_columna = 1;

                    $date=date_create($value->fecha);

                    $pdf->SetX(27);
                    $pdf->SetFont('Arial','B',7);
                    $pdf->Cell(0,0,utf8_decode('RECIBO DE PAGO NRO. '.str_pad($value->idpagos, 7, "0", STR_PAD_LEFT)),0,1,'');
                    
                    $pdf->ln(7);
                    $pdf->SetFont('Arial','',6);
                    $pdf->SetX(7);
                    $pdf->Cell(0,0,utf8_decode('CÉDULA: '.$value->cedula),0,1,'');
                    $pdf->SetX(75);
                    $pdf->Cell(0,0,utf8_decode('FECHA: '.date_format($date,"d/m/Y")),0,1,'');
                    
                    $pdf->ln(5);
                    $pdf->SetX(7);
                    $pdf->Cell(0,0,utf8_decode('CONTRIBUYENTE: '.$value->nombre),0,0,'');
                    
                    $pdf->ln(5);
                    $pdf->SetX(7);
                    $pdf->Cell(0,0,utf8_decode('ZONA '.$value->zona.' - COMUNIDAD '.$value->comunidad),0,1,'');
                    $pdf->ln(5);
                    $pdf->SetX(7);
                    $pdf->Cell(0,0,utf8_decode('N° CASA: '.$value->calle.', DIRECCIÓN: '.$value->direccion),0,1,'');

                    $total = 0;

                    $total = $total + $value->tarifa;

                    $pdf->SetFont('Arial','B',7);
                    $pdf->ln(5);
                    $pdf->SetX(37);
                    $pdf->Cell(35,4,utf8_decode("TOTAL PAGADO, ".$this->TRIMESTRE($value->trimestre)." TRIMESTRE ".number_format($total, 2, ',','.')." Bs"),0,0,'R',false);
                    

                } else if ($count_columna == 1){
                    /////////////////////
                    ///SEGUNDA COLUMNA///
                    /////////////////////
                    $pdf->ln(-27);
                    $count_columna = 0;
                    $count_fila++;

                    $date=date_create($value->fecha);

                    $pdf->SetX(127);
                    $pdf->SetFont('Arial','B',7);
                    $pdf->Cell(0,0,utf8_decode('RECIBO DE PAGO NRO. '.str_pad($value->idpagos, 7, "0", STR_PAD_LEFT)),0,1,'');
                    
                    $pdf->ln(7);
                    $pdf->SetFont('Arial','',6);
                    $pdf->SetX(107);
                    $pdf->Cell(0,0,utf8_decode('CÉDULA: '.$value->cedula),0,1,'');
                    $pdf->SetX(175);
                    $pdf->Cell(0,0,utf8_decode('FECHA: '.date_format($date,"d/m/Y")),0,1,'');
                    
                    $pdf->ln(5);
                    $pdf->SetX(107);
                    $pdf->Cell(0,0,utf8_decode('CONTRIBUYENTE: '.$value->nombre),0,0,'');
                    
                    $pdf->ln(5);
                    $pdf->SetX(107);
                    $pdf->Cell(0,0,utf8_decode('ZONA '.$value->zona.' - COMUNIDAD '.$value->comunidad),0,1,'');
                    $pdf->ln(5);
                    $pdf->SetX(107);
                    $pdf->Cell(0,0,utf8_decode('N° CASA: '.$value->calle.', DIRECCIÓN: '.$value->direccion),0,1,'');

                    $total = 0;

                    $total = $total + $value->tarifa;

                    $pdf->SetFont('Arial','B',7);
                    $pdf->ln(5);
                    $pdf->SetX(137);
                    $pdf->Cell(35,4,utf8_decode("TOTAL PAGADO, ".$this->TRIMESTRE($value->trimestre)." TRIMESTRE ".number_format($total, 2, ',','.')." Bs"),0,0,'R',false);


                    $pdf->ln(20);
                }

                $trimestre_1 = 0;
                $trimestre_2 = 0;
                $trimestre_3 = 0;
                $trimestre_4 = 0;

                $nrecibo_1 = 0;
                $nrecibo_2 = 0;
                $nrecibo_3 = 0;
                $nrecibo_4 = 0;
            }
            ///
            
        }

        $pdf->Output();
        exit;

        
    }
}