<?php

$recipients = 'lquirolafrank@hotmail.com';

$recipients1='';
$recipients2='';
$recipients3='';
$recipients4='';
$name1='';
$name2='';
$name3='';
$name4='';
$subject='';
$message='';
$fechahora='';
$tipo='';


require './phpmailer/PHPMailerAutoload.php';

$cnx = new PDO("mysql:host=localhost:3306;dbname=qpsec_SIGPROI","qpsec_usrSIGPROI", "MabeLuis2602");

//Realizamos el UPDATE de la cantidad de usuarios por cliente en GUSBER
$sql = "update users u
        inner join (select idSupervisor, count(1) as cantidad from users where idSupervisor > 0 group by idSupervisor) b
        on u.id = b.idSupervisor
        set u.cantUsers = b.cantidad - 1";

$cnx->prepare($sql)->execute();    

$res=$cnx->query("SELECT N.id as codigo, N.tipo, U1.name as n1, U1.email as m1, U2.name as n2, U2.email as m2, U3.name as n3, U3.email as m3, U4.name as n4, U4.email as m4, N.Concepto, N.mensaje, N.FechaHora, N.idSupervisor  FROM `notificaciones` N LEFT OUTER JOIN `users` U1 ON U1.id = N.idDestino1 LEFT OUTER JOIN `users` U2 ON U2.id = N.idDestino2 LEFT OUTER JOIN `users` U3 ON U3.id = N.idDestino3 LEFT OUTER JOIN `users` U4 ON U4.id = N.idDestino4 WHERE N.FechaHora > DATE_ADD(NOW(), INTERVAL -1 DAY) AND enviaMail = 1 and enviadoMail < (case when tipo='I' then 1 else 3 end)");

foreach ($res as $row){
    $recipients1=$row['m1'];
    $recipients2=$row['m2'];
    $recipients3=$row['m3'];
    $recipients4=$row['m4'];
    $name1=$row['n1'];
    $name2=$row['n2'];
    $name3=$row['n3'];
    $name4=$row['n4'];
    $subject= utf8_encode($row['Concepto']);
    $message=utf8_encode($row['mensaje']);
    $fechahora=$row['FechaHora'];
    $tipo=$row['tipo'];
    $id=$row['codigo'];
    $idNuevoUser=$row['idSupervisor'];
    
    
    if ($recipients1==''){
        $recipients1 = $recipients2;
    }
    
    if($recipients1 != ''){
    preg_match_all("/([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)/", $recipients1, $addresses, PREG_OFFSET_CAPTURE);
    }

    if($recipients2 != ''){
    preg_match_all("/([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)/", $recipients2, $addresses2, PREG_OFFSET_CAPTURE);
    }

    if($recipients3 != ''){
    preg_match_all("/([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)/", $recipients3, $addresses3, PREG_OFFSET_CAPTURE);
    }

    if($recipients4 != ''){
    preg_match_all("/([\w-]+(?:\.[\w-]+)*)@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)/", $recipients4, $addresses4, PREG_OFFSET_CAPTURE);
    }


    if (!count($addresses[0])) {
        die('MF001');
    }

    if (preg_match('/^(127\.|192\.168\.)/', $_SERVER['REMOTE_ADDR'])) {
        die('MF002');
    }

    if($tipo=='I'){
        if($subject=='CONFIRMAR CORREO :: CUENTA DE USUARIO'){
           $link_confirmacion="www.qps.ec/SIGPROI/bat/confirmaremail.php?id=".$idNuevoUser;
           $template = file_get_contents('rd-ConfirmarMail.tpl');
           $template = str_replace("_linkconfirm_", $link_confirmacion, $template);
        }else{
            $template = file_get_contents('rd-Notification.tpl');
        }
    }
    else
        $template = file_get_contents('rd-AlertaRoja.tpl');


    if (isset($subject)) {
        $template = str_replace("_MENSAJEPRINCIPAL_", $subject, $template);
    }
    if (isset($message)) {
        $template = str_replace("DATA_INFORMACION", $message, $template);
    }

    $mail = new PHPMailer();
    //$mail->From = 'notificaciones@appgusber.qps.ec';
    //$mail->FromName = 'notificaciones@appgusber.qps.ec';

    $mail->From = 'info_sigproi@saicorp.com';
    $mail->FromName = 'SIG-PROI Notificaciones';

    try{
    foreach ($addresses[0] as $key => $value) {
        $mail->addAddress($value[0]);
    }
    }catch (Exception $e) {
        echo 'Excepción capturada: ',  $e->getMessage(), "\n";
    }
    
    try{
        if($recipients2 != ''){
            foreach ($addresses2[0] as $key => $value) {
                $mail->addAddress($value[0]);
            }
        }
    }catch (Exception $e) {
        echo 'Excepción capturada: ',  $e->getMessage(), "\n";
    }

    try{    
        if($recipients3 != ''){
            foreach ($addresses3[0] as $key => $value) {
                $mail->addAddress($value[0]);
            }
        }
    }catch (Exception $e) {
        echo 'Excepción capturada: ',  $e->getMessage(), "\n";
    }

    try{    
        if($recipients4 != ''){
            foreach ($addresses4[0] as $key => $value) {
                $mail->addAddress($value[0]);
            }
        }
    }catch (Exception $e) {
        echo 'Excepción capturada: ',  $e->getMessage(), "\n";
    }

    $mail->CharSet = 'utf-8';
    $mail->Subject = $subject;
    $mail->MsgHTML($template);

    if (isset($_FILES['attachment'])) {
        foreach ($_FILES['attachment']['error'] as $key => $error) {
            if ($error == UPLOAD_ERR_OK) {
                $mail->AddAttachment($_FILES['attachment']['tmp_name'][$key], $_FILES['Attachment']['name'][$key]);
            }
        }
    }

    $sql= "Update notificaciones set enviadoMail=(enviadoMail + 1) where id=$id";
    $cnx->prepare($sql)->execute();    

    $mail->send();

}
?>