<?php

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Credentials: true");
header("Access-Control-Max-Age: 1000");
header("Access-Control-Allow-Headers: Content-Type, Authorization, x-Requested-with");
header("Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept , Cache-Control, Pragma, Accept-Encoding");
header("Content-Type: application/json; charset=utf-8");

include "library/config.php";
include "library/config2.php";


use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;


require 'mailer/Exception.php';
require "mailer/PHPMailer.php";
require "mailer/SMTP.php";



// Allow from any origin
if(isset($_SERVER["HTTP_ORIGIN"]))
{
    // You can decide if the origin in $_SERVER['HTTP_ORIGIN'] is something you want to allow, or as we do here, just allow all
    header("Access-Control-Allow-Origin: {$_SERVER['HTTP_ORIGIN']}");
}
else
{
    //No HTTP_ORIGIN set, so we allow any. You can disallow if needed here
    header("Access-Control-Allow-Origin: *");
}

header("Access-Control-Allow-Credentials: true");
header("Access-Control-Max-Age: 600");    // cache for 10 minutes

if($_SERVER["REQUEST_METHOD"] == "OPTIONS")
{
    if (isset($_SERVER["HTTP_ACCESS_CONTROL_REQUEST_METHOD"]))
        header("Access-Control-Allow-Methods: POST, GET, OPTIONS, DELETE, PUT"); //Make sure you remove those you do not want to support

    if (isset($_SERVER["HTTP_ACCESS_CONTROL_REQUEST_HEADERS"]))
        header("Access-Control-Allow-Headers: {$_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS']}");

    //Just exit with 200 OK with the above headers for OPTIONS method
    exit(0);
}
//From here, handle the request as it is ok






$postjson = json_decode(file_get_contents('php://input'),true);



$fecha_hora = date('Y-m-d H:i:s');


//aqui parten las consultas a la bdd.




if($postjson['aksi']=='getdata'){
    $rut= $postjson['rut'];

     $sth2 = $dbh2->prepare("INSERT INTO visita (rut, fecha) VALUES (?, NOW())");
    $sth2->bindParam(1, $rut);
    // Excecute
    $sth2->execute();



  $sth = $dbh->prepare("select 
Trabajador.TrabajadorId as trab_id,
Trabajador.TrabajadorNombre as nombres, 
Trabajador.TrabajadorPaterno as paterno 
from 
gdoc_corma.Trabajador 
where Trabajador.trabajadorRutDv='$rut'");
  $sth->setFetchMode(PDO::FETCH_ASSOC);
  $sth->execute();


  // Mostramos los resultados
  while ($row = $sth->fetch()){
    $trab_id = $row['trab_id'];
    $nombres = $row['nombres'];
    $paterno = $row['paterno'];
  }


  $data = array();
  $sth = $dbh->prepare("
  SELECT
Trabajador.TrabajadorNombre as nombres,
Trabajador.TrabajadorPaterno as paterno,
OECL.OECLFantasia as fantasia,
Cargo_Agr.Cargo_AgrId,
Area.AreaNombre as nombre_area,
Cargo.CargoNombre AS cargo_cert,
DATE_FORMAT(CompLaboral.CompLaboralFchSolicitud,('%d-%m-%Y')) AS fecha,
Credencial.CredencialId AS credencial,
Credencial.CredencialFchEmision as emision,
CompLaboral.CompLaboralSAG as sag,
CompLaboral.CompLaboralCargoId,
CompLaboral.CompLaboralId as LABORAL_ID,
ifnull(CompLaboral.CompLaboralObs, 'sin comentarios') as OBS,
ifnull(CompLaboral.CompLaboralObsDescarte, 'sin comentarios') as OBS_DESCARTE,
Credencial.CredencialTermino as vencimiento,

CompLaboral.EstadoCompId as ESTADO,
CompLaboral.CompLaboralAntiguo AS ANTIGUO,
CompLaboral.OTId as OT_ID,
CompLaboral.CompLaboralFchRevision as FECHA_REVISION,
EstadoComp.EstadoCompNombre as estC_nombre,
EstadoComp.EstadoCompId as estC_id,
(SELECT
EvaComp.EvaCompFchVence
FROM
EvaComp
WHERE
EvaComp.CompLaboralId=CompLaboral.CompLaboralId and
EvaComp.EvaCompTipoEvaId=1
LIMIT 1) AS medica,
(SELECT
EvaComp.EvaCompFchVence
FROM
EvaComp
WHERE
EvaComp.CompLaboralId=CompLaboral.CompLaboralId and
EvaComp.EvaCompTipoEvaId=2
LIMIT 1) AS psicologica,
(SELECT
EvaComp.EvaCompFchVence
FROM
EvaComp
WHERE
EvaComp.CompLaboralId=CompLaboral.CompLaboralId and
EvaComp.EvaCompTipoEvaId=3
LIMIT 1) AS tecnica,
if(Contratista.ContratistaFantasia is null, 'no','si') as TIENECONT
FROM gdoc_corma.CompLaboral
LEFT OUTER JOIN gdoc_corma.OECL ON (CompLaboral.CompLaboralOECLId = OECL.OECLId)
LEFT OUTER JOIN gdoc_corma.Solicitud ON (CompLaboral.CompLaboralSolicitudId = Solicitud.SolicitudId)
LEFT OUTER JOIN gdoc_corma.Contratista ON (Solicitud.ContratistaId = Contratista.ContratistaId)
LEFT OUTER JOIN gdoc_corma.Cargo ON (CompLaboral.CompLaboralCargoId = Cargo.CargoId)
LEFT OUTER JOIN gdoc_corma.Area ON (CompLaboral.CompLaboralAreaId = Area.AreaId)
LEFT OUTER JOIN gdoc_corma.Cargo_Agr ON (CompLaboral.CompLaboralCargoId = Cargo_Agr.Cargo_AgrId)
INNER JOIN gdoc_corma.EstadoComp ON (CompLaboral.EstadoCompId = EstadoComp.EstadoCompId)
LEFT OUTER JOIN gdoc_corma.cor_cargos ON (CompLaboral.CompLaboralCargoId = cor_cargos.COD_CARGO)
LEFT OUTER JOIN gdoc_corma.Credencial ON (CompLaboral.CompLaboralId = Credencial.CredencialCompLaboralId)
INNER JOIN gdoc_corma.Trabajador ON (Trabajador.TrabajadorId = CompLaboral.TrabajadorId)
WHERE
CompLaboral.CompLaboralId IN (SELECT c1.CompLaboralId FROM CompLaboral c1
JOIN (SELECT CompLaboral.CompLaboralCargoId, Max(CompLaboralId) AS MAX_LABORAL_ID
FROM CompLaboral  WHERE  CompLaboral.TrabajadorId = '$trab_id'  AND CompLaboral.EstadoCompId IN ( 6, 7, 8, 9, 12, 13 )
GROUP BY CompLaboral.CompLaboralCargoId) c2 ON c2.CompLaboralCargoId = c1.CompLaboralCargoId AND c2.max_laboral_id = c1.CompLaboralId) order by CompLaboral.CompLaboralId ");
  $sth->setFetchMode(PDO::FETCH_ASSOC);
  $sth->execute();


  // Mostramos los resultados
  while ($row = $sth->fetch()){

  $fech_med="";
  $fech_psi="";
  $fech_tec="";

    //buscamos la fecha medico
    $sthm = $dbh->prepare("
      SELECT 
EvaComp.EvaCompVigencia as VIGENCIA, 
EvaComp.EvaCompReconocida as evacompreconocida,
EvaComp.EvaCompFchVence as venc,
EvaComp.EvaCompFchExamen as FECHA_EXAMEN,
EvaComp.EstadoEvaId as eva_estado 
FROM 
gdoc_corma.EvaComp
WHERE   EvaComp.EvaCompTipoEvaId = 1 AND    EvaComp.CompLaboralId = ?
      ");
    $sthm->bindParam(1, $row['LABORAL_ID']);
    $sthm->setFetchMode(PDO::FETCH_ASSOC);
    $sthm->execute();
    while ($row2 = $sthm->fetch()){
        $fech_med=$row2['venc'];
        //echo $row2['venc'];

    }


    $datetime1 = new DateTime('now');
    $datetime2 = new DateTime($fech_med);
    $dias_vigencia_medica = $datetime1->diff($datetime2);


    //buscamos la fecha psicologico
    $sthm = $dbh->prepare("
      SELECT 
EvaComp.EvaCompVigencia as VIGENCIA, 
EvaComp.EvaCompReconocida as evacompreconocida,
EvaComp.EvaCompFchVence as venc,
EvaComp.EvaCompFchExamen as FECHA_EXAMEN,
EvaComp.EstadoEvaId as eva_estado 
FROM 
gdoc_corma.EvaComp
WHERE   EvaComp.EvaCompTipoEvaId = 2 AND    EvaComp.CompLaboralId = ?
      ");
    $sthm->bindParam(1, $row['LABORAL_ID']);
    $sthm->setFetchMode(PDO::FETCH_ASSOC);
    $sthm->execute();
    while ($row2 = $sthm->fetch()){
        $fech_psi=$row2['venc'];
        //echo $row2['venc'];

    }

    $datetime1 = new DateTime('now');
    $datetime2 = new DateTime($fech_psi);
    $dias_vigencia_psicologica = $datetime1->diff($datetime2);


     //buscamos la fecha tecnico
    $sthm = $dbh->prepare("
    SELECT 
EvaComp.EvaCompVigencia as VIGENCIA, 
EvaComp.EvaCompReconocida as evacompreconocida,
EvaComp.EvaCompFchVence as venc,
EvaComp.EvaCompFchExamen as FECHA_EXAMEN,
EvaComp.EstadoEvaId as eva_estado 
FROM 
gdoc_corma.EvaComp
WHERE   EvaComp.EvaCompTipoEvaId = 2 AND    EvaComp.CompLaboralId = ?
    ");
    $sthm->bindParam(1, $row['LABORAL_ID']);
    $sthm->setFetchMode(PDO::FETCH_ASSOC);
    $sthm->execute();
    while ($row2 = $sthm->fetch()){
        $fech_tec=$row2['venc'];
        //echo $row2['venc'];
    }

    $datetime1 = new DateTime('now');
    $datetime2 = new DateTime($fech_tec);
    $dias_vigencia_tecnica = $datetime1->diff($datetime2);


      if($dias_vigencia_medica->format('%R%a')>=60){
        $medica_v="v";
      }
      if($dias_vigencia_medica->format('%R%a')<60 && $dias_vigencia_medica->format('%R%a')>=30 ){
        $medica_v="n";
      }
      if($dias_vigencia_medica->format('%R%a')<30){
        $medica_v="r";
      }
   
      if($dias_vigencia_psicologica->format('%R%a')>=60){
        $psicologica_v="v";
      }
      if($dias_vigencia_psicologica->format('%R%a')<60 && $dias_vigencia_psicologica->format('%R%a')>=30 ){
        $psicologica_v="n";
      }
      if($dias_vigencia_psicologica->format('%R%a')<30){
        $psicologica_v="r";
      }

 
      if($dias_vigencia_tecnica->format('%R%a')>=60){
        $tecnica_v="v";
      }
      if($dias_vigencia_tecnica->format('%R%a')<60 && $dias_vigencia_tecnica->format('%R%a')>=30 ){
        $tecnica_v="n";
      }
      if($dias_vigencia_tecnica->format('%R%a')<30){
        $tecnica_v="r";
      }

      $m = $fech_med;
      $p = $fech_psi;
      $t = $fech_tec;
    
        if(empty($m)){
            $m="N/A";
        }
        if(empty($p)){
            $p="N/A";
        }
        if(empty($t)){
            $t="N/A";
        }
        
      

      if($m>=$p && !empty($m)){
      	$vencimiento=$p;
      }elseif($p>=$m && !empty($p)){
      	$vencimiento=$m;
      }elseif($t>=$m && !empty($t)){
        $vencimiento=$t;
      }elseif($t>=$p && !empty($t)){
        $vencimiento=$t;  
      }
     
      if($medica_v== "r" || $psicologica_v == "r" || $tecnica_v == "r"){
         

        if($medica_v== "r" || $tecnica_v == "r"){
        }  
        elseif($row['sag']!=""){
           $data[] = array(
          'rut' => $rut,
          'cargo_cert' => $row['cargo_cert'],
          'fantasia' => $row['fantasia'],
          'nombre_area' => $row['nombre_area'],
          'credencial' => $row['credencial'],
          'emision' => $row['emision'],
          'sag' => $row['sag'],
          'medica' => $row['medica'],
          'psicologica' => $row['psicologica'],
          'tecnica' => $row['tecnica'],
          'nombres' => $nombres,
          'paterno' => $paterno,
          'laboral_id' => $row['LABORAL_ID'],
          'medica_v' => $medica_v,
          'psicologica_v' => $psicologica_v,
          'tecnica_v' => $tecnica_v,
          'vencimiento' => $vencimiento,
          );


        }else{
          $data[] = array(
            'rut' => "vacio",
          );
        }
      	

        }else{  
            $sin_utf8 = $row['nombre_area'];
            $con_utf8 = utf8_encode($sin_utf8);
            $row['nombre_area']=$con_utf8;
            
            
            
            
		    $data[] = array(
          'rut' => $rut,
		      'cargo_cert' => $row['cargo_cert'],
		      'fantasia' => $row['fantasia'],
		      'nombre_area' => $row['nombre_area'],
		      'credencial' => $row['credencial'],
		      'emision' => $row['emision'],
		      'sag' => "-",
		      'medica' => $row['medica'],
		      'psicologica' => $row['psicologica'],
		      'tecnica' => $row['tecnica'],
               'nombres' => $nombres,
		      'paterno' => $paterno,
		      'laboral_id' => $row['LABORAL_ID'],
		      'medica_v' => $medica_v,
		      'psicologica_v' => $psicologica_v,
		      'tecnica_v' => $tecnica_v,
		      'vencimiento' => $vencimiento,
		    );
		  }




     /*echo $rut;
     echo "--";
  echo $row['cargo_cert'];
  echo "--";
  echo $row['fantasia'];
  echo "--";
  echo $row['nombre_area'];
  echo "--";
  echo $row['credencial'];
  echo "--";
  echo $row['emision'];

  echo "--";
  echo $row['medica'];
  echo "--";
  echo $row['psicologica'];
  echo "--";
  echo $row['tecnica'];
  echo "--";
  echo $nombres;
   echo "--";
  echo $paterno;
  echo "--";
  echo $row['LABORAL_ID'];
  echo "--";
  echo $medica_v;
  echo "--";
  echo $psicologica_v;
  echo "--";
  echo $tecnica_v;
  echo "--";
  echo $vencimiento;
      */
  }
  
 

  /*
  if(empty($data)){
    $data[] = array(
        'rut' => "vacio",
        );
  }
*/


//    print_r($data);
    
  if($sth) $result = json_encode(array('success'=>true, 'result'=>$data));
  else $result = json_encode(array('success'=>false));
  echo $result;

}elseif($postjson['aksi']=='getnoticias'){

	$data2 = array();
  $sth2 = $dbh2->prepare("select noticias.id_noticia, noticias.titulo, noticias.contenido, noticias.link, noticias.fecha, noticias.imagen from noticias order by noticias.id_noticia DESC");
	$sth2->setFetchMode(PDO::FETCH_ASSOC);
	$sth2->execute();

	// Mostramos los resultados
	while ($row2 = $sth2->fetch()){


		$data2[] = array(
		    'titulo' => $row2['titulo'],
      	'contenido' => $row2['contenido'],
      	'link' => $row2['link'],
        'fecha' => $row2['fecha'],
        'imagen' => $row2['imagen']
		);

	}


	if($sth2) $result2 = json_encode(array('success'=>true, 'result'=>$data2));
	else $result2 = json_encode(array('success'=>false));
	echo $result2;



}elseif($postjson['aksi']=='contacto'){

 
  $rut = $postjson['rut'];
  $pregunta = $postjson['pregunta'];
  $correo = $postjson['correo'];
  $tipo_pregunta = $postjson['tipo_pregunta'];


    $sth2 = $dbh2->prepare("INSERT INTO visita (rut, fecha) VALUES (?, NOW())");
    $sth2->bindParam(1, $rut);
    // Excecute
    $sth2->execute();




  $sth2 = $dbh2->prepare("INSERT INTO contactenos (rut, pregunta, correo, tipo_pregunta, fecha) VALUES (?, ?, ?, ?, NOW())");

  
  $sth2->bindParam(1, $rut);
  $sth2->bindParam(2, $pregunta);
  $sth2->bindParam(3, $correo);
  $sth2->bindParam(4, $tipo_pregunta);
  // Excecute
  $sth2->execute();

  	
echo json_encode(array('ok'));

sleep(3);

$mail = new PHPMailer();
$mail->IsSMTP(); // habilita SMTP
$mail->SMTPDebug = 1; // debugging: 1 = errores y mensajes, 2 = sólo mensajes
$mail->SMTPAuth = true; // auth habilitada
$mail->SMTPSecure = 'ssl'; // transferencia segura REQUERIDA para Gmail
$mail->Host = "mail.seminarioscorma.cl";
$mail->Port = 465; // or 587
$mail->IsHTML(true);
$mail->Username = "app@seminarioscorma.cl";
$mail->Password = "Corma277";
$mail->SetFrom("app@seminarioscorma.cl");
$mail->Subject = "Nuevo Mensaje App Corma";
$mail->Body = "Nuevo mensaje de App corma usuario: " .$rut. " pregunta:" .$pregunta. " Tipo de comentario: ".$tipo_pregunta ;
$mail->AddAddress("app@seminarioscorma.cl");

 if(!$mail->Send()) {
    //echo "Mailer Error: " . $mail->ErrorInfo;
 } else {
    //echo "Message has been sent";
 }


  


}elseif($postjson['aksi']=='getpyr'){
  $rut = $postjson['rut'];
  
  $sth2 = $dbh2->prepare("INSERT INTO visita (rut, fecha) VALUES (?, NOW())");
    $sth2->bindParam(1, $rut);
    // Excecute
    $sth2->execute();
  
  
  $data2 = array();
  $sth2 = $dbh2->prepare("select contactenos.fecha, contactenos.pregunta, contactenos.respuesta from contactenos where contactenos.rut = ?  group by contactenos.fecha desc");
  $sth2->bindParam(1, $rut);
  $sth2->setFetchMode(PDO::FETCH_ASSOC);
  $sth2->execute();
  // Mostramos los resultados
  while ($row2 = $sth2->fetch()){
      
    $data2[] = array(
        'fecha' => $row2['fecha'],
        'pregunta' => $row2['pregunta'],
        'respuesta' => $row2['respuesta']
    );
    
    
  }
  
  if($sth2) $result2 = json_encode(array('success'=>true, 'result'=>$data2));
  else $result2 = json_encode(array('success'=>false));
  echo $result2;

  
}elseif($postjson['aksi']=='getpreguntas_frecuentes'){

  $data2 = array();
  $sth2 = $dbh2->prepare("select preguntas_frecuentes.titulo, preguntas_frecuentes.area, preguntas_frecuentes.respuesta from preguntas_frecuentes");
  $sth2->setFetchMode(PDO::FETCH_ASSOC);
  $sth2->execute();

  // Mostramos los resultados
  while ($row2 = $sth2->fetch()){


    $data2[] = array(
        'titulo' => $row2['titulo'],
        'area' => $row2['area'],
        'respuesta' => $row2['respuesta'] 
    );

  }


  if($sth2) $result2 = json_encode(array('success'=>true, 'result'=>$data2));
  else $result2 = json_encode(array('success'=>false));
  echo $result2;



}elseif($postjson['aksi']=='getultimas_evaluaciones'){
  $rut = $postjson['rut'];

$sth2 = $dbh2->prepare("INSERT INTO visita (rut, fecha) VALUES (?, NOW())");
    $sth2->bindParam(1, $rut);
    // Excecute
    $sth2->execute();


  $data = array();
  $sth = $dbh->prepare("select 
EvaComp.EvaCompTipoEvaId as 'tipo_eva',
max(EvaComp.EvaCompFchExamen) as 'fecha',
Trabajador.trabajadorRutDv as'rut'
from
gdoc_corma.Trabajador,CompLaboral,EvaComp,TipoEvaluacion,Credencial
where
Trabajador.TrabajadorId=CompLaboral.TrabajadorId
and
CompLaboral.CompLaboralId=EvaComp.CompLaboralId
and
Trabajador.trabajadorRutDv=?
and
EvaComp.EvaCompTipoEvaId=TipoEvaluacion.TipoEvaluacionId
and
Credencial.CredencialCompLaboralId=CompLaboral.CompLaboralId
group by EvaComp.EvaCompTipoEvaId");

  $sth->bindParam(1, $rut);
  $sth->setFetchMode(PDO::FETCH_ASSOC);
  $sth->execute();
  $vuelta=0;
  $data3 = array();
  $row2="";
  // Mostramos los resultados
 
  while ($row = $sth->fetch()){
      
      $fecha=$row['fecha'];
      $rut=$row['rut']; 
      //divido el rut para agregar a la consulta y obtener el nombre del archivo final 
      $str = $rut;

      $array = explode("-", $str);
      $rut_ent= $array[0]; 
      $cod_div= $array[1];

      
      $rut_ent_f=str_replace('.', '',$rut_ent);
      
      $tipo_eva=$row['tipo_eva']; 

      if($tipo_eva==1){
        $tipo_evan=15;
      }
      if($tipo_eva==2){
        $tipo_evan=16;
      }
      if($tipo_eva==3){
        $tipo_evan=17;
      }


      $sth2 = $dbh->prepare("select 
      }
EvaComp.EvaCompTipoEvaId as 'tipo',
EvaComp.EvaCompFchExamen as 'fecha',
EvaComp.EvaCompFchVence as 'fecha_vencimiento',
Trabajador.trabajadorRutDv as 'rut',
EvaComp.CompLaboralId as 'laboral' 
from gdoc_corma.Trabajador,CompLaboral,EvaComp
where
Trabajador.TrabajadorId=CompLaboral.TrabajadorId
and
CompLaboral.CompLaboralId=EvaComp.CompLaboralId
and
EvaComp.EvaCompFchExamen=?
and
Trabajador.trabajadorRutDv=?
and
EvaComp.EvaCompTipoEvaId=?
group by EvaComp.EvaCompTipoEvaId,EvaComp.EvaCompFchExamen,EvaComp.EvaCompFchVence,Trabajador.trabajadorRutDv,EvaComp.CompLaboralId order by EvaComp.CompLaboralId  desc limit 1
");

        $sth2=$dbh->prepare("select 
EvaComp.EvaCompTipoEvaId as 'tipo',
EvaComp.EvaCompFchExamen as 'fecha',
EvaComp.EvaCompFchVence as 'fecha_vencimiento',
Trabajador.trabajadorRutDv as 'rut',
Trabajador.trabajadorDv as 'divisor',
EvaComp.CompLaboralId as 'laboral', 
Trabajador.TrabajadorPaterno as 'apellido',
Docto.DoctoFileName as  'nombre_documento',
Docto.DoctoURL as 'url',
Docto.DoctoInstancia as 'instancia',
Docto.DoctoInd as 'doctoind'
from gdoc_corma.Trabajador,CompLaboral,EvaComp,Docto
where
Trabajador.TrabajadorId=CompLaboral.TrabajadorId
and
CompLaboral.CompLaboralId=EvaComp.CompLaboralId
and
Docto.DoctoRut=Trabajador.TrabajadorRut
and
EvaComp.EvaCompFchExamen=?
and
Trabajador.trabajadorRutDv=?
and
EvaComp.EvaCompTipoEvaId=?
and
Docto.TipoDocumentoId=?
group by EvaComp.EvaCompTipoEvaId,EvaComp.EvaCompFchExamen,EvaComp.EvaCompFchVence,Trabajador.trabajadorRutDv,EvaComp.CompLaboralId, Docto.DoctoFileName, Docto.DoctoURL, Docto.DoctoInstancia, Docto.DoctoInd  order by Docto.DoctoInstancia  desc limit 1
");







                            $sth2->bindParam(1, $fecha);
                            $sth2->bindParam(2, $rut);
                            $sth2->bindParam(3, $tipo_eva);
                            $sth2->bindParam(4, $tipo_evan);

                            $sth2->setFetchMode(PDO::FETCH_ASSOC);
                            $sth2->execute();

                             while ($row2 = $sth2->fetch()){
                            	
                            $datetime1 = new DateTime('now');
						    $datetime2 = new DateTime($row2['fecha_vencimiento']);
						    $diferencia = $datetime1->diff($datetime2);
								
							if($diferencia->format('%R%a')>=60){
						        $color="v";
						      }
						      if($diferencia->format('%R%a')<60 && $diferencia->format('%R%a')>=30 ){
						        $color="n";
						      }
						      if($diferencia->format('%R%a')<30){
						        $color="r";
						      }           

                              $data3[$vuelta] = array(
                                    'tipo' => $row2['tipo'],
                                    'fecha' => $row2['fecha'],
                                    'laboral' => $row2['laboral'],
                                    'rut' => $row2['rut'],
                                    'color' => $color,
                                    'fecha_vencimiento' => $row2['fecha_vencimiento'],
                                    'url'=> $row2['url'],
                                    'apellido' => $row2['apellido'],
                                    'rut_sin_puntos' => $rut_ent_f,
                                    'codigo_verificador' => $row2['divisor'],
                                    'nombre_documento' => $row2['nombre_documento'],
                                    'doctoind' => $row2['doctoind'],

                                );           




                             }
                             $vuelta=$vuelta+1;
                       


  }

  if($sth2) $result = json_encode(array('success'=>true, 'result'=>$data3));
  else $result = json_encode(array('success'=>false));
  echo $result;



}elseif($postjson['aksi']=='mostrar_oecl'){

  $data = array();
  $sth2 = $dbh2->prepare("SELECT id_oecl, oecl, direccion, comuna, telefono, celular, contacto1, contacto2, contacto3, correo1, correo2, correo3 FROM oecl");

  $sth2->setFetchMode(PDO::FETCH_ASSOC);
  $sth2->execute();
  // Mostramos los resultados
  while ($row = $sth2->fetch()){             

  

      $data[] = array(
          'oecl' => $row['oecl'],
          'direccion' => $row['direccion'],
          'comuna' => $row['comuna'],
          'telefono' => $row['telefono'],
          'celular' => $row['celular'],
          'contacto1' => $row['contacto1'],
          'contacto2' => $row['contacto2'],
          'contacto3' => $row['contacto3'],
          'correo1' => $row['correo1'],
          'correo2' => $row['correo2'],
          'correo3' => $row['correo3'],
      );
    

   }
  

  if($sth2) $result = json_encode(array('success'=>true, 'result'=>$data));
  else $result = json_encode(array('success'=>false));
  echo $result;

}elseif($postjson['aksi']=='validar_rut'){
  $rut = $postjson['rut'];
  $num_credencial=$postjson['numero_credencial'];
  //$numero_telefono=$postjson['numero'];
  $data = array();
  $sth = $dbh->prepare("
SELECT Trabajador.trabajadorRutDv as 'rut' FROM gdoc_corma.Trabajador,CompLaboral,Credencial 
where 
CompLaboral.TrabajadorId=Trabajador.TrabajadorId
and
CompLaboral.CompLaboralId=Credencial.CredencialCompLaboralId
and
Trabajador.trabajadorRutDv=?
and
Credencial.CredencialId=?");

  $sth->bindParam(1, $rut);
  $sth->bindParam(2, $num_credencial);
  $sth->setFetchMode(PDO::FETCH_ASSOC);
  $sth->execute();
  // Mostramos los resultados
  $rutt="nulo";
  while ($row = $sth->fetch()){
	  $rutt = $row['rut'];
   }

   $data[] = array(
  		'rut' => $rutt,
    );
  

  if($sth) $result = json_encode(array('success'=>true, 'result'=>$data));
  else $result = json_encode(array('success'=>false));
  echo $result;
  
  
  if($rut==$rutt){
  
  $sth2 = $dbh2->prepare("INSERT INTO visita (rut, fecha) VALUES (?, NOW())");
    $sth2->bindParam(1, $rut);
    // Excecute
    $sth2->execute();
  
  
  $sth2 = $dbh2->prepare("INSERT INTO registro_instalacion (rut, credencial, numero_telefono, fecha_instalacion) VALUES (?, ?, ?, NOW())");
  $sth2->bindParam(1, $rut);
  $sth2->bindParam(2, $num_credencial);
  $sth2->bindParam(3, $numero_telefono);
  // Excecute
  $sth2->execute();
  }
  

}elseif($postjson['aksi']=='opinion_noticia'){
  
  $opinion = $postjson['opinion'];
  $rut = $postjson['rut'];
  $titulo_noticia = $postjson['titulo'];



  $data = array();
  $sth2 = $dbh2->prepare("SELECT rut FROM opinion_noticia where titulo_noticia='$titulo_noticia' and rut='$rut'");

  $sth2->setFetchMode(PDO::FETCH_ASSOC);
  $sth2->execute();
  // Mostramos los resultados
  while ($row = $sth2->fetch()){             
 		$rutt = $row['rut'];
   }


   if($rutt==$rut){

   }else{

   	$sth2 = $dbh2->prepare("INSERT INTO opinion_noticia (opinion_noticia, titulo_noticia, rut , fecha) VALUES (?, ?, ?, NOW())");
  	$sth2->bindParam(1, $opinion);
  	$sth2->bindParam(2, $titulo_noticia);
  	$sth2->bindParam(3, $rut);

   }
  
  // Excecute
  $sth2->execute();

  echo json_encode(array('ok'));

}elseif($postjson['aksi']=='ultima_noticia'){
  $data = array();
  $sth2 = $dbh2->prepare("SELECT max(id_noticia) as 'max_id' FROM noticias ");

  $sth2->setFetchMode(PDO::FETCH_ASSOC);
  $sth2->execute();
  // Mostramos los resultados
  while ($row = $sth2->fetch()){
	  $max = $row['max_id'];
   }

   $data[] = array(
  		'max_id' => $max,
    );
  

  if($sth2) $result = json_encode(array('success'=>true, 'result'=>$data));
  else $result = json_encode(array('success'=>false));
  echo $result;

}




















?>
