<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class funcionesController extends Controller
{   
    function cne(Request $request){

        $persona = DB::select('SELECT * FROM persona WHERE cedula = ?', [$request->input('n1').'-'.$request->input('c1')]);

        $zona = !empty($request->input('zo')) ? $request->input('zo') : "";

        if(!empty($request->input('zo'))){
            $tarifa1 = DB::select('SELECT * FROM zona_tabulador WHERE zona = ? AND estatus = ? ORDER BY trimestre ASC', [$request->input('zo'), "SI"]);

            $j['tarifa1'] = $tarifa1;
            $j['lista'] = [];
        }


        if (!empty($persona[0])) {
            //EXISTE

            $persona1 = DB::select('SELECT p.id, jf.id AS jefe_id, p.cedula, p.nombre, p.telefono, p.correo, jf.propia, jf.calle, jf.direccion, c.zona, c.id AS id_com, c.nombre AS comunidad, pt.cedula AS pcedula, pt.nombre AS pnombre, pt.telefono AS ptelefono FROM persona AS p INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN jefe_com AS jc ON jc.id = jf.id_jefe_com INNER JOIN persona AS pc ON pc.id = jc.id_persona INNER JOIN comunidad AS c ON c.id_jefe_com = jc.id LEFT JOIN propietario AS pr ON pr.id_familia = jf.id LEFT JOIN persona AS pt ON pt.id = pr.id_propietario WHERE p.cedula = ?', [$request->input('n1').'-'.$request->input('c1')]);

            if (!empty($persona1[0])){
                $pagos = DB::select('SELECT p.id AS pagos, p.id_pagos, zt.id, zt.trimestre, zt.tarifa, p.estatus FROM pagos AS p INNER JOIN zona_tabulador AS zt ON zt.id = p.id_pagos WHERE id_familia = ?', [$persona1[0]->jefe_id]);

                $j['error'] = 'NO';
                $j['cedula'] = $persona1[0]->cedula;
                $j['nombre'] = $persona1[0]->nombre;
                $j['telefono'] = $persona1[0]->telefono;
                $j['correo'] = $persona1[0]->correo;

                $j['direccion'] = $persona1[0]->direccion;
                $j['calle'] = $persona1[0]->calle;

                $j['comunidad'] = $persona1[0]->comunidad;

                $j['zon'] = $persona1[0]->zona;
                $j['com'] = $persona1[0]->id_com;

                $j['pagos'] = 'SI';
                $j['pro'] = $persona1[0]->propia;
                $j['lista'] = $pagos;

                $j['pcedula'] = $persona1[0]->pcedula;
                $j['pnombre'] = $persona1[0]->pnombre;
                $j['ptelefono'] = $persona1[0]->ptelefono;

                $j['ids'] = $persona[0]->id;

                print json_encode($j);
                return;
            } else {
                $j['error'] = 'NO';
                $j['cedula'] = $persona[0]->cedula;
                $j['nombre'] = $persona[0]->nombre;
                $j['telefono'] = $persona[0]->telefono;
                $j['correo'] = $persona[0]->correo;

                $j['direccion'] = '';
                $j['calle'] = '';

                $j['pagos'] = 'NO';

                $j['ids'] = $persona[0]->id;

                $j['zon'] = '0';
                $j['com'] = 0;

                print json_encode($j);
                return;
            }
        } else {
            //NO EXISTE
            $url = "http://www.cne.gob.ve/web/registro_electoral/ce.php?nacionalidad=".$request->input('n1')."&cedula=".$request->input('c1');

            $ch = curl_init();
            curl_setopt($ch,CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_REFERER,'http://www.cne.gob.ve/');
            curl_setopt($ch,CURLOPT_USERAGENT,'Mozilla/5.0 (X11; Linux i686; rv:32.0) Gecko/20100101 Firefox/32.0');
            curl_setopt($ch,CURLOPT_RETURNTRANSFER,TRUE);
            curl_setopt($ch,CURLOPT_FRESH_CONNECT,TRUE);
            curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,5);
            curl_setopt($ch,CURLOPT_TIMEOUT,10);
            $html=curl_exec($ch);

            if($html==false){
                $m=curl_error(($ch));
                error_log($m);
                curl_close($ch);
                //$j['error'] = 'SI';
                //$j['descripcion'] = $m;

                $j['error'] = 'SI';
                $j['cedula'] = $request->input('n1').'-'.$request->input('c1');
                $j['nombre'] = '';
                $j['telefono'] = '';
                $j['ids'] = '';
                $j['correo'] = '';

                print json_encode($j);
                return;
            } else {
                curl_close($ch);
                if (strpos($html, '<b>DATOS DEL ELECTOR</b>') > 0) {
                    $modo = 1; # Puede Votar
                } else if (strpos($html, '<strong>DATOS PERSONALES</strong>') > 0) {
                    $modo = 2; # No Puede Votar
                } else {
                    $modo = -1;

                    $j['error'] = 'SI';
                    $j['cedula'] = $request->input('n1').'-'.$request->input('c1');
                    $j['nombre'] = '';
                    $j['telefono'] = '';
                    $j['ids'] = '';
                    $j['correo'] = '';


                    return json_encode($j);
                }
                
                $j['error'] = 'NO';
                $j["descripcion"] = "/cne/elector";
                $j['modo'] = $modo;

                $j['zon'] = '0';
                $j['com'] = 0;

                // Datos para un elector que puede votar
                if ($j['modo'] == 1) {
                    #Obtener Cédula
                    $npos = strpos($html, 'align="left">', strpos($html, 'dula:')) + 13;
                    $j['cedula'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Nombre
                    $npos = strpos($html, 'align="left"><b>', strpos($html, 'Nombre:')) + 16;
                    $j['nombre'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Estado
                    $npos = strpos($html, 'align="left">', strpos($html, 'Estado:')) + 13;
                    $j['estado'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Municipio
                    $npos = strpos($html, 'align="left">', strpos($html, 'Municipio:')) + 13;
                    $j['municipio'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Parroquia
                    $npos = strpos($html, 'align="left">', strpos($html, 'Parroquia:')) + 13;
                    $j['parroquia'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));

                    $j['telefono'] = '';
                }
                // Datos para un elector con objeción
                else if ($j['modo'] == 2) {
                    #Obtener Cédula
                    $npos = strpos($html, 'strong> ', strpos($html, 'dula:')) + 8;
                    $j['cedula'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Nombre
                    $npos = strpos($html, 'strong> ', strpos($html, 'Primer Nombre:')) + 8;
                    $j['nombre'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Nombre
                    $npos = strpos($html, 'strong> ', strpos($html, 'Segundo Nombre:')) + 8;
                    $j['nombre'] .= " " . trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Apellido
                    $npos = strpos($html, 'strong> ', strpos($html, 'Primer Apellido:')) + 8;
                    $j['nombre'] .= " " . trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Apellido
                    $npos = strpos($html, 'strong> ', strpos($html, 'Segundo Apellido:')) + 8;
                    $j['nombre'] .= " " . trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));
                    #Obtener Estatus
                    $npos = strpos($html, '<td>', strpos($html, 'ESTATUS')) + 4;
                    $j['estatus'] = trim(substr($html, ($npos), (strpos($html, '<', ($npos)) - ($npos))));

                    $j['telefono'] = '';
                }

                return json_encode($j);
            }
        }
        

    }

    function zona(Request $request, $id){
        $comunidad = DB::select('SELECT * FROM comunidad AS c WHERE c.zona = ?', [$id]);

        $tarifa = DB::select('SELECT * FROM zona_tabulador WHERE zona = ? AND estatus = ?', [$id, "SI"]);

        echo json_encode(array('datos' => $comunidad, 'tt' => $tarifa));
    }

    function j_comunidad(Request $request, $id){
        $jefe = DB::select('SELECT p.cedula, p.nombre, p.telefono FROM comunidad AS c INNER JOIN jefe_com AS j ON j.id = c.id_jefe_com INNER JOIN persona AS p ON p.id = j.id_persona WHERE c.id = ?', [$id]);

        echo json_encode(array('datos' => $jefe[0]));
    }

    function configcomunidad(Request $request, $id){
        $jefe = DB::select('SELECT * FROM comunidad WHERE id = ?', [$id]);

        echo json_encode(array('datos' => $jefe[0]));
    }

    function buscar__(Request $request, $id){
        $jefe = DB::select('SELECT p.id, jf.id AS jefe_id, p.cedula, p.nombre, p.telefono, p.correo, jf.propia, jf.calle, jf.direccion, jf.id_jefe_com, c.zona, c.id AS id_com, c.nombre AS comunidad, pt.cedula AS pcedula, pt.nombre AS pnombre, pt.telefono AS ptelefono FROM persona AS p INNER JOIN j_familia AS jf ON jf.id_persona = p.id INNER JOIN jefe_com AS jc ON jc.id = jf.id_jefe_com INNER JOIN persona AS pc ON pc.id = jc.id_persona INNER JOIN comunidad AS c ON c.id_jefe_com = jc.id LEFT JOIN propietario AS pr ON pr.id_familia = jf.id LEFT JOIN persona AS pt ON pt.id = pr.id_propietario WHERE jf.id = ?', [$id]);

        $comunidad = DB::select('SELECT * FROM comunidad AS c WHERE c.zona = ?', [$jefe[0]->zona]);

        echo json_encode(array('datos' => $jefe[0], 'comunidad' => $comunidad));
    }
}