#!/usr/bin/perl
 
use DBI;
use strict;
use POSIX;
#use ESL;
use Switch;
use Time::HiRes;
use WWW::Curl::Easy;
use JSON;
use Config::Tiny;
use Data::Dumper;
use Cwd;
use URI::Encode qw(uri_encode uri_decode);
use threads;
use Thread::Semaphore;
use threads::shared;
use POSIX qw(strftime);
use Net::Ping;
use Dinstar;
use Email::Send;   
use Email::Simple::Creator; # or other Email::

use constant {
    CONSOLE => 0,
    ALERT => 1,
    CRIT  => 2,
    ERROR  => 3,
    WARNING  => 4,
    NOTICE  => 5,
    INFO => 6,
    DEBUG  => 7
};

# ----------------------------
# SALDO MOVISTAR SALDO AL 444
# ----------------------------
#Tu saldo actual es de $95,11. Consulta gratis desde la app mov.is/xvNSN. �Sin saldo?, envi� SOS al 4445 o recarg� desde la app. �Mantenete conectado!


#  Te informamos que el IMEI 35044829876678 de este equipo es invalido. Cumpliendo con la resolucion de ENACOM 2459/16, se bloqueara en nuestra red.

# ----------------------------------------
# Need to Install Config-Tiny Perl Module
# ----------------------------------------
my $app_folder = getcwd;
#chdir "/var/www/html/PortalSMS";
chdir $app_folder;
my $filename = sprintf("%s/SmsStatus.ini", $app_folder );
my $config = Config::Tiny->read( $filename, 'utf8' );
my $daemon = 0;
my $dbh;
#my $logfile = "./SmsStatus.log";
my $logfile = sprintf("%s/SmsStatus.log", $app_folder );
my $threadCount = 10;
my $site = 1;
my $debug = 7;
my $firstRun = 1;
my $last_line = 0;
my $Count = 5;

# --------------------------------
# Variables
# --------------------------------
my $sth;
my $forever = 1;
my $dbConnect = 1;
my $i;
my @thr;
my @Used:shared;
my $globalThreadCount:shared = 0;
my $semaphore = Thread::Semaphore->new();
my $coding = 0;

my $ip = "";
my $port = 0;
my $id = 0;
my $auth = "";
my @jsessionid;
my $tac = "";
my $imeiChange = 0;
my $resend_failed_time = 5;
my $time_for_register = 5;
my $failed_sms_count = 3;
my $max_delivery_count = 3;
my $max_send_status = 3;
my $timeout_for_status_test = 15;

use constant {
    CONSOLE => 0,
    ALERT => 1,
    CRIT  => 2,
    ERROR  => 3,
    WARNING  => 4,
    NOTICE  => 5,
    INFO => 6,
    DEBUG  => 7
};


if( $config->{core}{daemon} eq "Yes" ) {
   $daemon = 1;
}

# ---------------------------
# Is program already running
# ---------------------------
writeToLog( DEBUG, $logfile, "", 0 );
my $progname = "SmsStatus.pl";
my $pidx = `ps -C $progname -o pid=`;
#my $pidx = `ps -C $progname -o command=`;
if( $pidx != "" ) {
   if( $pidx != getpid() ) {
      writeToLog( INFO, $logfile, sprintf( "SmsStatus PID: %d is ALREADY RUNNING", $pidx ) ,0 );
      exit( 0 );
   }
   else {
      writeToLog( INFO, $logfile, sprintf( "SmsStatus.pl daemon PID: %d IS NOT RUNNING", $pidx ),0 ); 
   }
}
else {
   writeToLog( INFO, $logfile, sprintf( "SmsStatus.pl daemon PID: %d IS NOT RUNNING", $pidx ),0 ); 
}

if( $daemon == 1 ) {
   fork and exit;
   POSIX::setsid();
   fork and exit;
   umask 0;

   writeToLog( INFO, $logfile, sprintf( "Start 'SmsStatus' daemon PID: %d", getpid() ),0 );
   my $filename = sprintf("%s/SmsStatus.pid", $app_folder );
#  if( open(my $fp, '>', "./SmsStatus.pid") ) {
   if( open(my $fp, '>', $filename) ) {
      printf $fp "%d", getpid();    
      close $fp;
   }

   close STDIN;
   close STDOUT;
   close STDERR;
}
else {
   writeToLog( INFO, $logfile, sprintf( "Start 'SmsStatus' as Foreground" ) ,0 );
}   
writeToLog( INFO, $logfile, "", 1 );


my $shuttingDown = 0;
$SIG{INT} = sub { 
   return if $shuttingDown;
   $shuttingDown = 1;
   $forever = 1;
   AppEnd();
   print("\nControl-C pressed, terminating process. Please wait...\n\n");
   exit;
};

$SIG{TERM} = sub {
   return if $shuttingDown;
   $shuttingDown = 1;
   $forever = 1;
   AppEnd();
   exit;
};

sub AppEnd {
   while( $globalThreadCount > 0 ) {
      Time::HiRes::sleep(1);
   }
   if( $dbh ) {
      $dbh->disconnect();
   }   
   writeToLog( DEBUG, $logfile, "", 1 );
   writeToLog( DEBUG, $logfile, sprintf( "Terminated 'SmsStatus' daemon PID: %d", getpid() ) ,0 );
   writeToLog( DEBUG, $logfile, "", 1 );
   my $filename = sprintf("%s/SmsStatus.pid", $app_folder );
   unlink($filename);
}

=begin
END {
  while( $globalThreadCount > 0 ) {
     Time::HiRes::sleep(1);
  }
  if( $dbh ) {
     $dbh->disconnect();
  }   
  writeToLog( DEBUG, $logfile, "", 1 );
  writeToLog( DEBUG, $logfile, sprintf( "Terminated 'SmsStatus' daemon PID: %d", getpid() ) ,0 );
  writeToLog( DEBUG, $logfile, "", 1 );
  unlink("./SmsStatus.pid");
}
=cut

# --------------------------------
# MariaDB
# --------------------------------
my $driver = "mysql"; 
my $database = "TestSMS-2";
my $dsn = "DBI:$driver:database=smpp:127.0.0.1";
my $userid = "root";
my $password = "dialer19";
my $dbhost = "10.0.66.119";
my $datatable = "outbound_request";
my $error = "";


# --------------------------------
# MariaDB Configuration
# --------------------------------
if( $config->{database}{name} ne "" ) {
   $database = $config->{database}{name};
}
if( $config->{database}{host} ne "" ) {
   $dbhost = $config->{database}{host};
}
if( $config->{database}{host} ne "" ) {
   $userid = $config->{database}{user};
}
if( $config->{database}{password} ne "" ) {
   $password = $config->{database}{password};
}
if( $config->{database}{datatable} ne "" ) {
   $datatable = $config->{database}{datatable};
}

$dsn = "DBI:$driver:database=$database:$dbhost";

if( $config->{core}{debug} ne "" ) {
   $debug = $config->{core}{debug}; 
}

if( $config->{core}{threads} ne "" ) {
   $threadCount = $config->{core}{threads}; 
}

if( $config->{core}{site} ne "" ) {
   $site = $config->{core}{site}; 
}

if( $config->{core}{TAC} ne "" ) {
   $tac = $config->{core}{TAC}; 
}

if( $config->{core}{codigg} ne "" ) {
   $coding = $config->{core}{coding}; 
}

if( $config->{core}{IMEI} ne "" ) {
   $imeiChange = $config->{core}{IMEI}; 
}

if( $config->{core}{resend_failed_time} ne "" ) {
   $resend_failed_time = $config->{core}{resend_failed_time}; 
}

if( $config->{core}{wait_time_for_register} ne "" ) {
   $time_for_register = $config->{core}{wait_time_for_register}; 
}

if( $config->{core}{failed_sms_count} ne "" ) {
   $failed_sms_count = $config->{core}{failed_sms_count}; 
}

if( $config->{core}{max_delivery_number_count} ne "" ) {
   $max_delivery_count = $config->{core}{max_delivery_number_count}; 
}
if( $config->{core}{max_sent_ok_status} ne "" ) {
   $max_send_status = $config->{core}{max_sent_ok_status} * 4; 
}

if( $config->{core}{timeout_for_status_test} ne "" ) {
   $timeout_for_status_test = $config->{core}{timeout_for_status_test}; 
}

   use constant {
      PENDING  => 1,
      DELIVERED => 100,
      WAITING_STATUS => 101,
      RESEND => 102,
      PENDING  => 1,
      PROCESSING => 103,
      MAYBE_SENT => 104,
      FAILED => 200,
      SENDING_PROCESSED => 202,
      NO_STATUS => 400,
      CANCELLED => 401,
      NO_PORTS => 550,
   };


writeToLog( DEBUG, $logfile, sprintf( "DataTable: %s", $datatable) );
writeToLog( DEBUG, $logfile, sprintf( "Debug Level: %d", $debug ) );
writeToLog( DEBUG, $logfile, sprintf( "Max Threads: %d", $threadCount ) );


# --------------------------------
# Connect to MariaDB
# --------------------------------
while( $dbConnect ) {

   if( ( $dbh = DBI->connect($dsn, $userid, $password ) ) == 0 ) {
      writeToLog( DEBUG, $logfile, sprintf( "Cannot connect to MariaDB: $DBI::errstr", 0 ) );
      Time::HiRes::sleep(10);
   }
   else {
      $dbConnect = 0;
      writeToLog( DEBUG, $logfile, sprintf( "Connected to MariaDB @%s", $dbhost ) );
      last;
   }
}

# --------------------------
# Setup Used Threads Flags
# --------------------------
for( $i= 0; $i < $threadCount; $i++ ) {
   $Used[$i] = 0;
}
writeToLog( DEBUG, $logfile, "", 1 );

# --------------------------------------------------------------------------------------------------------------
# TIMEOUT OLD MESSAGES
# Los mensajes que estan en pending 101 y 103 por mas de 60 minutos, le pongo pending 100 con error 'TIMEOUT' 
# --------------------------------------------------------------------------------------------------------------
my $sql = sprintf( "update %s set error_description = 'TIMEOUT', pending=100 where error_description = '' and DATE_SUB(now(),INTERVAL 60 MINUTE) > forwarded and (pending=101 or pending=103);", $datatable );
my $resultSms = $dbh->prepare($sql);
if ( !$resultSms->err ) {
   $resultSms->execute();
}  
$resultSms->finish();


# --------------------------------------------------------------------------------------------------------------
# Main Loop
# --------------------------------------------------------------------------------------------------------------
while( $forever ) {

   my $foundThread = 0;

   # -----------------------------------------------------------------
   # Verifico conexion al MySQL y reintento conexion de ser necesario
   # -----------------------------------------------------------------
   if ( not $dbh->ping ) {
      if( ( $dbh = DBI->connect($dsn, $userid, $password ) ) == 0 ) {
         writeToLog( DEBUG, $logfile, sprintf( "Cannot connect to MariaDB $DBI::errstr", 0 ) );
         Time::HiRes::sleep(10);
         next;
      }
   }

    my $auth = "";
    my $resultUpdate;
    my $resultDelivery;
    my $result;
    my $sn = "";

    # --------------------------------------------------------------------------------------
    # Veo que puertos estan registrados (cada 15")
    #
    # Si no estaba registrado y ahora si, verifico el IMEI y lo cambio o Genero uno nuevo 
    # --------------------------------------------------------------------------------------
    if( ++$Count > 10 ) {
      $Count = 0;
      QueryPorts_Registration( $dbh );          # OK
    }

  
   # -----------------------------------------------------------
   # Busco mensajes que se enviaron para obtener el resultado
   # Status:     1 PENDIENTE
   #           100 DELIVERED O SENT_OK
   #                Mensajes SIN STATUS  por mas de 60 minutos 
   #                lo pongo en timeout, no puedo saber si el 
   #                mensaje fue entregado
   #           101 SIGO LEYENDO EL STATUS
   #           102 ENVIO NUEVAMENTE
   #           103 EN PROCESO
   #           104 PODRIA SER MENSAJE ENVIADO
   #           200 FAILED
   #           202 ENVIO PROCESADO
   #          >400 ENVIO CON ERROR DEFINITIVO
   #           550 NO PORTS
   #
   #          -- NO SE USAN
   #           201 NO SE OBTUVO STATUS, SIGO INTENTANDO 
   #           202
   #
   #           400 NO SE OBTUVO STATUS, SIGO INTENTANDO
   #           401 CANCELADO, NO SE OBTUVO STATUS
   # -----------------------------------------------------------
   my $found = 0;
   my $index = 0;
   my $fecha = "";
   my $gateway = "";
   my $forward = "";
   my $timeout = 0;

   # ------------------------------------------------------------------------
   # Me fijo si hay mensajes para comprobar el status (pending 101 o 202)
   # ------------------------------------------------------------------------
    
   my $status_period = 30;
   $sql = sprintf( "select sql_no_cache date_sub(received, INTERVAL 1 SECOND), sent_count, error_description, gw, forwarded from %s where sent_count <= %d and status_read_count <= %d and process < 5 and DATE_ADD(forwarded,INTERVAL %d MINUTE) > now() and (pending = 101 or pending = 202 ) and DATE_ADD(last_status_read,INTERVAL %d SECOND) > now() order by forwarded limit 1;", $datatable, $failed_sms_count, $max_send_status, $timeout_for_status_test, $status_period );
   my $resultSms = $dbh->prepare($sql);
   if ( $resultSms->err ) {
      sleep( 5 );
      next;
    }    
    if( $resultSms->execute() ) {
        if(my @rowSms = $resultSms->fetchrow_array()) {
            my ($received, $send_count, $error_description, $gw, $forwarded) = @rowSms;
            $fecha = $forwarded;
            $gateway = $gw;
            $forward = $forwarded;
        }
        else {
            $resultSms->finish();

            # ----------------------------------------------------------------------------------------------------------
            # Mensajes SIN STATUS  por mas de 60 minutos lo pongo en timeout, no puedo saber si el mensaje fue entregado
            # ----------------------------------------------------------------------------------------------------------
            if( ++$timeout > 10 ) {
                $sql = sprintf( "update %s set error_description = 'TIMEOUT', pending=%d, last_status_read=now() where error_description = '' and DATE_SUB(now(),INTERVAL 60 MINUTE) > forwarded and (pending=101 or pending=202);", $datatable, DELIVERED );
                my $resultSms = $dbh->prepare($sql);
                if ( !$resultSms->err ) {
                    $resultSms->execute();
                }  
                $resultSms->finish();
            }
            sleep( 5 );
            next;
        }
    }
    $resultSms->finish();

    # -------------------------------------------------------------
    # Traigo info del gateway del mensaje que se esta procesando
    # -------------------------------------------------------------
    $sql = sprintf( "select sql_no_cache id, ip, api_user, api_password, user, password, descripcion, tipo_gw, sn, canales from gateways where id=%d;", $gateway );
    my $resultSms = $dbh->prepare($sql);
    if ( $resultSms->err ) {
        sleep( 5 );
        next;
    }

    if( $resultSms->execute() ) {

        while (my @rowSms = $resultSms->fetchrow_array()) {
            my ($gw, $ip, $api_user, $api_password, $gw_user, $gw_password, $descripcion, $tipo_gw, $sn, $channels) = @rowSms;

            $auth = $api_user . ":" . $api_password;

            # ----------------------------------------------------------------------------
            # Me logueo en el GW para tener el jsessionid (solo en DINSTAR es necesario)
            # ----------------------------------------------------------------------------
            if( !defined($jsessionid[$id]) ) {
                my $session = gw_LoginGateway( $ip, $gw_user, $gw_password, $tipo_gw ); 
                $jsessionid[$id] = $session;
            }

            # -------------------------
            # Busco un thread libre
            # -------------------------
            $semaphore->down();
            $foundThread = 0;
            for( $i = 0; $i < $threadCount; $i++ ) {
                if( $Used[$i] == 0 ) {
                    $Used[$i] = 1;
                    $foundThread = 1;
                    $index = $i;
                    last;
                }
            }

            # -----------------------------
            # Actualizo Thread en Uso
            # -----------------------------
            UsedThreads();
            $semaphore->up();

            if( $foundThread == 1 ) {

                # -----------------------------------------------------
                # Ejecuto el thread de Resultado del mensaje enviado
                # -----------------------------------------------------
                $thr[$i] = threads->create(\&queryResultThread, $ip, $auth, $database, $dbhost, $userid, $password, $sn, $jsessionid[$gw], $datatable, $channels, $index, $fecha, $gw, $forward, $tipo_gw );
            }

            while( $globalThreadCount >= $threadCount ) {
                UsedThreads();
                Time::HiRes::sleep(1);
            }
        }
    }
    $resultSms->finish();
    $firstRun = 0;
    sleep( 5 );
}
$dbh->disconnect();
writeToLog( DEBUG, $logfile, sprintf( "End 'SmsStatus' daemon PID: %d", getpid() ),0 );
print ( "-------------------------------------------------------------------------------------------------------------------------------------------------------------------\n" );


    # ------------------------------------
    # THREAD de Resultado
    # ------------------------------------
    sub queryResultThread
    {
        my $LocalCopy;
        my $dbh;
        my $resultUpdate;
        my $resultDelivery;
        my $sql;
  
        my $ip = $_[0];
        my $auth = $_[1];
        my $database = $_[2];
        my $dbhost = $_[3];
        my $userid = $_[4];
        my $password = $_[5];
        my $sn = $_[6];
        my $jsessionid = $_[7];
        my $datatable = $_[8];
        my $channels = $_[9];
        my $index = $_[10];
        my $fecha = $_[11];
        my $gw = $_[12]; 
        my $forwarded = $_[13]; 
        my $tipo_gw = $_[14];  
        my $recipient = "N/D";
  
        threads->detach();
        my $dsn = "DBI:$driver:database=$database:$dbhost";
        if( ( $dbh = DBI->connect($dsn, $userid, $password ) ) == 0 ) {
           $Used[$index] = 0;
           $semaphore->up();
           return;
        }
  
        $LocalCopy = $globalThreadCount; 
        $Used[$index] = 1;
        $LocalCopy++;
        $globalThreadCount = $LocalCopy; 
        $semaphore->up();
  
        # -------------------------------------------
        # Query resultado de envio de Sms del puerto
        # -------------------------------------------
        my @ports = (0..$channels-1);
        my $rd = gw_QuerySmsResults( $ip, \@ports, $auth, $id, $fecha, $recipient, $channels, $tipo_gw );
        my $rc = decode_json($rd);
#     print( Dumper( $rd ) );
#     print( Dumper( $rc ) );

        if( int($rc->{error_code}) == 200 ) {
           my @resultArray = $rc->{result};
           my $len = scalar @resultArray;
     
           if( $len < 1 ) {
     
              # ---------------------------------------------
              # Sin STATUS 5 veces, cancelo con codigo 401,
              # Si no llegue al maximo de 5, codigo 400
              # y sigo esperando respuesta
              # ---------------------------------------------
            }
            else {
                my $errCode;
                my $user_id;
                my $sms_id;
     
                # ------------------------------------  
                # JSON Perl Array (resultado del API)
                # ------------------------------------  
                my @resultArray = @{$rc->{result}};
                my $items = @resultArray;
                $errCode = $rc->{result}[0]->{status}; 
  
                for (my $i = 0; $i < $items; $i++) {

                    my $imsi = $rc->{result}[$i]->{imsi}; 
                    my $port = $rc->{result}[$i]->{port}; 
                    my $number = $rc->{result}[$i]->{number}; 
                    my $fecha = $rc->{result}[$i]->{time}; 
                    my $status = $rc->{result}[$i]->{status}; 
                    my $slot = $rc->{result}[$i]->{slot}; 
                    my $ref_id = $rc->{result}[$i]->{ref_id}; 
                    my $user_id = $rc->{result}[$i]->{user_id};     
                    my $sms_id = $rc->{result}[$i]->{user_id};     
                    my $count = $rc->{result}[$i]->{count}; 
                    my $succ_count = $rc->{result}[$i]->{succ_count}; 
                    my $strSlot = convertSlot( $slot );

                    if( $user_id <= 1 ) {
                        next;
                    }      

                    writeToLog( DEBUG, $logfile, sprintf( "IMSI: %s", $imsi ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Number: %s", $number ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Time: %s", $fecha ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Port: %s", $port ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Slot: %s", $slot ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Status: %s", $status ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "User ID: %s", $user_id ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Ref ID: %s", $ref_id ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Count: %s", $count ), 0 );
                    writeToLog( DEBUG, $logfile, sprintf( "Succ Count: %s", $succ_count ), 0 );
                    writeToLog( DEBUG, $logfile, "", 1 );
     
                    my $errCode = $status;
                    my $recipient = $number;
                    my $my_message = "";
                    my $my_display = 0;

                    # --------------------
                    # Busco el mensaje
                    # --------------------
                    $sql = sprintf( "select sql_no_cache id, pending, error_description, sent_count, status_read_count, message, display from %s where id=%d;", $datatable, $sms_id );
                    $resultUpdate = $dbh->prepare($sql);
                    if ( !$resultUpdate->err ) {
                        $resultUpdate->execute();
                        if( $resultUpdate->rows() > 0 ) {
                            if (my @rowUpdate = $resultUpdate->fetchrow_array()) {
                                my ($id, $pending, $errorCode, $sent_count, $status_read_count, $message, $display ) = @rowUpdate;
                                $my_message = $message;
                                $my_display = $display;
								if( $pending == 102 ) {
				                   $resultUpdate->finish();
									next;
								}

   					    		if( $status_read_count == 0 || $errCode != "SENDING" ) {
				                   # ---------------------------
                                   # Hay STATUS de respuesta
                                   # ---------------------------
                                   my $text = substr( $message, 0, 36 );
                                   my $status = sprintf( "%s: ID: %s, Port: %d%s, Status: %s, Message: %s", $recipient, $sms_id, $port, $strSlot, $errCode, $text );
                                   writeToLog( DEBUG, $logfile, $status, 0 );
                                   writeToLog( DEBUG, $logfile, "", 1 );
                				}
							}
						}
					}
                    $resultUpdate->finish();
   
                    # -------------------------------------------
                    # Codigo Actualizado, verificar por SENT_OK
                    # -------------------------------------------
                    switch( $errCode ) {

                        case /^(SENT_OK|SENDING)$/ {
                            # -----------------
                            # Marco su Status
                            # -----------------
                            $sql = sprintf( "update %s set error_description='%s' where id=%d;", $datatable, $errCode, $sms_id );
                            $resultUpdate = $dbh->prepare($sql);
                            if ( !$resultUpdate->err ) {
                                $resultUpdate->execute();
                            }
                            $resultUpdate->finish();

                            # --------------------------------------------------------
                            # Verifico cuantas veces lei para obtener el status final
                            # --------------------------------------------------------
                            my $status_read_count = 0;
                            $sql = sprintf( "select sql_no_cache status_read_count from %s where id=%d;", $datatable, $sms_id );
                            $resultUpdate = $dbh->prepare($sql);
                            if ( !$resultUpdate->err ) {
                                $resultUpdate->execute();
                                if( $resultUpdate->rows() > 0 ) {
                                    if(my @row = $resultUpdate->fetchrow_array()) {
                                      ($status_read_count) = @row;
                                    }
                                }
                                else {
                                    # ----------------------------------------------
                                    # No encontre el mensaje, lo termino con exito
                                    # ----------------------------------------------
                                    $resultUpdate->finish();
                                    $sql = sprintf( "update %s set pending=%d, display=1 where id=%d;", $datatable, DELIVERED, $user_id );
                                    $resultUpdate = $dbh->prepare($sql);
                                    if ( !$resultUpdate->err ) {
                                        $resultUpdate->execute();
                                    }   
                                }
                                $resultUpdate->finish();
                            }

                            # -------------------------------------------------------
                            # Supere la cantidad de veces el pedido de status final?
                            # -------------------------------------------------------
#                           printf( "$status_read_count == $max_send_status\n" );
                            if( $status_read_count == $max_send_status ) {
                                $sql = sprintf( "update %s set error_description='%s', pending=%d, status_read_count=%d, display=1 where id=%d;", $datatable, $errCode, DELIVERED, $max_send_status, $user_id );
                                $resultUpdate = $dbh->prepare($sql);
                                if ( !$resultUpdate->err ) {
                                    $resultUpdate->execute();
                                }
                                $resultUpdate->finish();

                                if( $my_display == 0 ) {
                                    my $text = substr( $my_message, 0, 36 );
                                    my $status = sprintf( "%s: ID: %s, Port: %d%s, Status: %s, Message: %s", $recipient, $user_id, $port, $strSlot, $errCode, $text );
                                    writeToLog( DEBUG, $logfile, $status, 0 );
                                    writeToLog( DEBUG, $logfile, "", 1 );
                                }
                            }
                            else {
                                # --------------------------------------------
                                # Marco para leer nuevamente el status final
                                # --------------------------------------------
                                $sql = sprintf( "update %s set status_read_count=status_read_count+1, error_description='%s', pending=%d where id=%d;", $datatable, $errCode, WAITING_STATUS, $user_id );  
                                $resultUpdate = $dbh->prepare($sql);
                                if ( !$resultUpdate->err ) {
                                    $resultUpdate->execute();
                                }
                                $resultUpdate->finish();
                            }
                        }

                    case 'FAILED' {
   
                        # -------------------------------------
                        # Cuantas veces se envio este mensaje?   
                        # -------------------------------------   
                        my $send_count = 0;
                        $sql = sprintf( "select sql_no_cache sent_count from %s where id=%d;", $datatable, $user_id );
                        $resultUpdate = $dbh->prepare($sql);
                        if ( $resultUpdate->err ) {
                            $resultUpdate->finish();
                            next;
                        }
                        else {
                            $resultUpdate->execute();
                            if( $resultUpdate->rows() > 0 ) {
                                if(my @row = $resultUpdate->fetchrow_array()) {
                                    ($send_count) = @row;
                                }
                            }
                            $resultUpdate->finish();
                        }

                        # ------------------------------
                        # Actualizo delivery del numero 
                        # ------------------------------
                        $sql = sprintf( "select sql_no_cache errorCount from delivery where gw=%d and port=%d and slot='%s' and recipient='%s';", $gw, $port, $strSlot, $recipient );
                        $resultDelivery = $dbh->prepare($sql);
                        if ( !$resultDelivery->err ) {
      
                            $resultDelivery->execute();
                            if( $resultDelivery->rows() == 0 ) {
   
                                # ----------------------------------
                                # Primer error, agrego registro
                                # ----------------------------------
                                $resultDelivery->finish();
                                $sql = sprintf( "insert into `delivery` (`id`,`fecha`,`gw`,`port`,`slot`,`errorCount`,`recipient`) VALUES (0,now(),%d,%d,'%s',1,'%s');", int($gw), int( $port ), $strSlot, $recipient );
                                $resultDelivery = $dbh->prepare($sql);
                                if ( $resultDelivery->err ) {
                                    $resultDelivery->finish();
                                    next;
                                }
                                $resultDelivery->execute();
                            }
                            else {

                                $resultDelivery->finish();
                                $sql = sprintf( "update `delivery` set errorCount=errorCount+1 where gw=%d and port=%d and slot='%s' and recipient='%s';", $gw, $port, $strSlot, $recipient );
                                $resultDelivery = $dbh->prepare($sql);
                                if ( $resultDelivery->err ) {
                                    $resultDelivery->finish();
                                    next;
                                }
                                $resultDelivery->execute();
                            }
                        }
                        $resultDelivery->finish();

                        # -----------------------------------------------------------------------------
                        # Verificar que se haya enviado n veces hasta que se tome como numero MAL
                        # -----------------------------------------------------------------------------
                        if( $send_count >= $failed_sms_count ) {
   
                           $sql = sprintf( "update gateway_ports set enabled=0, status=0, intento_registro='0000-00-00 00:00:00' where gw=%d and port=%d;", $gw, $port );
                           $resultDelivery = $dbh->prepare($sql);
                           if ( !$resultDelivery->err ) {
                               $resultDelivery->execute();
                           }
                           $resultDelivery->finish();

                           $sql = sprintf( "delete from delivery where gw=%d and port=%d and slot='%s';", $gw, $port, $slot );
                           $resultDelivery = $dbh->prepare($sql);
                           if ( !$resultDelivery->err ) {
                               $resultDelivery->execute();
                           }
                           $resultDelivery->finish();

                           # ------------------------------------------------
                           # Actualizo contador de veces enviado este mensaje
                           # Marco como finalizado con error
                           # ------------------------------------------------
                           $sql = sprintf( "update %s set pending=%d, error_description='%s', sent_count=sent_count+1, display=1 where id=%d;", $datatable, FAILED, $errCode, $user_id );
                           $resultUpdate = $dbh->prepare($sql);
                           if ( !$resultUpdate->err ) {
                               $resultUpdate->execute();
                           }
                           $resultUpdate->finish();

                           # ------------
                           # FAILED
                           # ------------
                           Dlr( $id, $errCode, $fecha, $fecha, "", $dbh );
                        }
                        else {
                            # -------------------------------
                            # Marco para usar este puerto 
                            # -------------------------------
#                            $sql = sprintf( "update gateway_ports set fecha_ultimo=date_add(now(),interval %d MINUTE) where gw=%d and port=%d and slot='%s';", $resend_failed_time, $gw, $port, $strSlot );
                            $sql = sprintf( "update gateway_ports set fecha_ultimo=date_add(now(),interval %d MINUTE) where gw=%d and port=%d;", $resend_failed_time, $gw, $port );
                            $resultDelivery = $dbh->prepare($sql);
                            $resultUpdate = $dbh->prepare($sql);
                            if ( !$resultUpdate->err ) {
                                $resultUpdate->execute();
                            }
                            $resultUpdate->finish();

                            # ------------------------------
                            # Marco para enviar nuevamente?
                            # ------------------------------
                            $sql = sprintf( "update %s set pending=%d, error_description='%s', sent_count=sent_count+1 where id=%d;", $datatable, RESEND, $errCode, $sms_id );  
                            $resultUpdate = $dbh->prepare($sql);
                            if ( !$resultUpdate->err ) {
                                $resultUpdate->execute();
                            }
                            $resultUpdate->finish();

                            # ------------------------------------
                            # Genero un nuevo request para envio                           
                            # ------------------------------------
                            CopyTableRow( $datatable, $dbh, $sms_id );
                        }
                    }

                    case 'DELIVERED' {
                        # -------------------------------------
                        # Marco como terminado con su Status
                        # -------------------------------------
                        $sql = sprintf( "update %s set pending=%d, error_description='%s' where id=%d;", $datatable, DELIVERED, $errCode, $user_id );
                        $resultUpdate = $dbh->prepare($sql);
                        if ( !$resultUpdate->err ) {
                            $resultUpdate->execute();
                        }
                        $resultUpdate->finish();
   
                        $sql = sprintf( "delete from delivery where gw=%d and port=%d and slot='%s';", $gw, $port, $strSlot );
                        $resultDelivery = $dbh->prepare($sql);
                        if ( !$resultDelivery->err ) {
                            $resultDelivery->execute();
                        }
                        $resultDelivery->finish();

                        Dlr( $user_id, $errCode, $fecha, $fecha, "", $dbh );
                    }
                    else {

                        # -------------------------------------
                        # Marco como terminado con su Status
                        # -------------------------------------
                        $sql = sprintf( "update %s set pending=%d, error_description='%s' where id=%d;", $datatable, NO_STATUS, $errCode, $user_id );
                        $resultUpdate = $dbh->prepare($sql);
                        if ( !$resultUpdate->err ) {
                            $resultUpdate->execute();
                        }
                        $resultUpdate->finish();
                        Dlr( $user_id, $errCode, $fecha, $fecha, "", $dbh );
                    }
                }
            }            
         } 
      } #rc 

      $semaphore->down();
      $LocalCopy = $globalThreadCount; 
      $LocalCopy--;
      $globalThreadCount = $LocalCopy; 
      $Used[$index] = 0;
      if( defined($resultDelivery) ) {
         $resultDelivery->finish();
      }                  
      if( defined($resultUpdate) ) {
         $resultUpdate->finish();
      }         
      $dbh->disconnect();
      $semaphore->up();
      threads->exit();
   }   

   # -----------------------------------------------------
   # Veo que puertos estan con estado REGISTERED
   # -----------------------------------------------------
   sub QueryPorts_Registration {
      my $conn = $_[0];
      my $resultUpdate;
      my $gw = 0;
      my $gw_id = 0;
      my $id = 0;
      my $ip = "";
      my $api_user = "";
      my $api_password = "";
      my $tipo_gw = "";
      my $sn = "";
      my $descripcion = "";
      my $code_operador = "";
      my $force_imei = 0;
      my $unregister_count = 0;
      my $display_flag = 0;
      my $count = 0;
      my $data = "";

#      my ($package, $filename, $line, $subroutine) = caller(0);
#      print "Caller: $filename, linea: $line, en el sub: $subroutine\n";

      writeToLog( DEBUG, $logfile, sprintf( "QueryPorts_Registration" ), 0 );

      # -----------------------------------------------------
      # Busco todos los ports habilitados de todos los GW
      # -----------------------------------------------------
      my $sql = sprintf( "select id, ip, api_user, api_password, tipo_gw, sn, descripcion from gateways where active=1;" );
      my $result = $dbh->prepare($sql);
      if ( $result->err ) {
         $result->finish();
         return "";
      }
      if( $result->execute() ) {

         while (my @row = $result->fetchrow_array()) {

            ($gw_id, $ip, $api_user, $api_password, $tipo_gw, $sn, $descripcion ) = @row;
            $auth = $api_user . ":" . $api_password;

            writeToLog( DEBUG, $logfile, "GATEWAY $gw ($ip) TEST", 0 );

            # ------------------------------------------------
            # Verifico que gateway este accesible 
            # ------------------------------------------------
            if( PingTest( $ip, 1 ) == 0 ) {
               writeToLog( WARNING, $logfile, "GATEWAY $gw ($descripcion: $ip) IS NOT AVAILABLE", 0 );
               writeToLog( WARNING, $logfile, "", 1 );
               next;
            }

            # ------------------------------------------------------------------------------------------------------------
            # Verifico todos los puertos de los GW si estan registrados o no y actualizo la base en gateway_ports
            # ------------------------------------------------------------------------------------------------------------
            my $data = gw_QueryPortInfo( $ip, -1, $auth, $tipo_gw, $sn );
            if( $data ne "" ) {

               my $decoded = decode_json($data); 
               if( $decoded->{error_code} == 200 ) {
              
                  my $number = 1;
                  my $valor;
                  my $info = $decoded->{info};
                  my $imsi;
                  my $imei;
                  my $iccid;
                  my @port_status;
                  my $reg = 1;
                  my $len = scalar @$info;
                  my $slotStr = "";
                  my $errors = 0;
                  my $slot = 0;
                  my $signal = "";
                  my $registered = 0;
                  my $id = 0;
                  my $enabled = 0;
                  my $prev_port = 0;
                  my $actual_port = 0;

                  for( $i = 0; $i < $len; $i++ ) {

                     $display_flag = 0;
                     $reg = 1;
                     $imsi  = $decoded->{info}[$i]{imsi};                     
                     $imei  = $decoded->{info}[$i]{imei};                     
                     $iccid = $decoded->{info}[$i]{iccid};                     
                     $slot  = $decoded->{info}[$i]{slot};
                     $actual_port = int($decoded->{info}[$i]{port});
                     $signal = $decoded->{info}[$i]{signal};

                     if( $signal eq "-1" ) {
                        $signal = "---";
                     }

                     # -----------------------------------------
                     # Slots son A, B, C, D o X (single slot)
                     # Si esta en NO_SIM, tratarlo distinto
                     # -----------------------------------------
                     $enabled = 0;
                     $registered = 0;
                     $force_imei = 0;
                     $id = 0;

                     $port_status[$decoded->{info}[$i]{port}] = $decoded->{info}[$i]{callstate};
                     if( $slot ne "" ) {
                        $slotStr = getSlotName( int($decoded->{info}[$i]{slot}) );
                     }
                     else {
                        $slotStr = "X";
                     }

                    # -----------------------------------------------
                    # Si NO ESTA REGISTRADO lo indico en el archivo
                    # -----------------------------------------------
                    if( $decoded->{info}[$i]{reg} ne "REGISTER_OK" ) {

                        writeToLog( WARNING, $logfile, sprintf( "%2d%s %-8s %-15s                     %-23s",$decoded->{info}[$i]{port}, $slotStr, $decoded->{info}[$i]{type}, $decoded->{info}[$i]{imei}, "NOT REGISTERED" ), 0 );
                        $sql = sprintf( "update gateway_ports set registered=0, unregister_count=0, intento_registro='0000-00-00 00:00:00' where gw=%d and port=%d;", $gw_id, $decoded->{info}[$i]{port} );
                        $resultUpdate = $dbh->prepare($sql);
                        if ( !$resultUpdate->err ) {
                           $resultUpdate->execute();
                        }
                        $resultUpdate->finish();
                        ++$count;

#                        if( $firstRun || $decoded->{info}[$i]{reg} ne "NO_SIM" ) {
#                        if( $firstRun ) {
#                            writeToLog( INFO, $logfile, sprintf( "%2d  %-8s %-15s                     %-23s",$decoded->{info}[$i]{port}, $decoded->{info}[$i]{type}, $decoded->{info}[$i]{imei}, $decoded->{info}[$i]{reg} ), 0 );
                            ++$count;
#                        }
                    }
                    else {

                        # --------------------------------------------------------------------------------------------
                        #   Si no estaba registrado anteriormente, lo marco como registrado, verifico el IMEI y el ANI
                        # --------------------------------------------------------------------------------------------
                        my $port = int($decoded->{info}[$i]{port});
                        $sql = sprintf( "update gateway_ports set registered=1, unregister_count=0, intento_registro='0000-00-00 00:00:00', force_imei_change=0 where gw=%d and port=%d;", $gw_id, $decoded->{info}[$i]{port} );
                        $resultUpdate = $dbh->prepare($sql);
                        if ( !$resultUpdate->err ) {
                            $resultUpdate->execute();
                        }
                        $resultUpdate->finish();
                        $display_flag = 1;
                    }
                    if( $display_flag ) {
                       writeToLog( WARNING, $logfile, sprintf( "%2d%s %-8s %-15s %3s(dBm) %-10s %-23s", int($decoded->{info}[$i]{port}), $slotStr, $decoded->{info}[$i]{type}, $decoded->{info}[$i]{imei}, $signal, $decoded->{info}[$i]{callstate}, $decoded->{info}[$i]{reg} ), 0 );
                       ++$count;
                    }
                     $number++;
                  } # for

                  if( $count > 0 ) {
                     writeToLog( WARNING, $logfile, "", 1 );
                  }
               } # error_code == 200
            }
            else {
               writeToLog( WARNING, $logfile, "GATEWAY $gw ($ip) NOT AVAILABLE", 0 );
               writeToLog( WARNING, $logfile, "", 1 );
            }               
         } # while
      }
      $result->finish();
      $firstRun = 0;
   }


   # --------------------------------------
   # writeToLog
   # --------------------------------------

   sub writeToLog 
   {  
      my $level = $_[0];
      my $filename = $_[1];
      my $text = $_[2];
      my $mode = $_[3];
   
      if( $debug == DEBUG || $level <= $debug ) {
         if( open(FH, '>>', $filename) ) {
            if( $mode == 1 ) {
               if( $last_line == 1 ) {
                  close(FH);
                  return;
               }
               $text = "+--------------------------------------------------------------------------------------------------+";
               $last_line = 1;
            }
            my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
            my $hoy = sprintf( "%02d.%02d.%04d %02d:%02d:%02d", $mday, $mon + 1, $year + 1900, $hour, $min, $sec );
            printf( FH sprintf( "%s: %s\n", $hoy, $text ) );
            printf( sprintf( "%s: %s\n", $hoy, $text ) );
            close(FH);
            if( $mode == 0 ) {
               $last_line = 0;
            }
         }      
      }
   }

   # ---------------------------
   # Cuantos Thread hay en uso
   # ---------------------------

   sub UsedThreads
   {
      my $str = "";   
      for( my $x = 0; $x < $threadCount; $x++ ) {
         if( $x == 0 ) {
            if( $Used[$x] == 0 ) {
               $str .= "0";
            }
            else {
               $str .= "1";
            }
         }
         else {
            if( $Used[$x] == 0 ) {
               $str .= ", 0";
            }
            else {
               $str .= ", 1";
            }
         }

      }
#      writeToLog( DEBUG, $logfile, sprintf( "Threads %d: (%s)", $globalThreadCount, $str ), 0 );
#      writeToLog( DEBUG, $logfile, "", 1 );
   }


    # ----------------------------------------------------------------
    # Disable All Slot Except
    # ----------------------------------------------------------------

    sub SlotAvailable_SendEmail {
        my $body = $_[0];

        my $to = 'ag@interactivos.net.ar';
        my $from = 'soporte@interactivos.net.ar';
        my $subject = 'Sms Gateway Notification';
        my $message = $body;
  
  
        my $mailer = Email::Send->new( {
            mailer => 'SMTP::TLS',
            mailer_args => [
                Host => 'box.interactivos.net.ar',
                Port => 587,
                User => 'soporte@interactivos.net.ar',
                Password => 'Dialer1959',
                Hello => 'interactivos.net.ar',
            ]
        } );

        my $email = Email::Simple->create(
            header => [
                From    => $from,
                To      => $to,
                Subject => $subject,
            ],
            body => $message,
        );
        eval { $mailer->send($email) };
    }


    # -------------------
    # db_Error
    # -------------------
    sub db_Error {
        my $level = $_[0];
        my $err = $_[1];
        my $errStr = $_[2];

        writeToLog( $level, $logfile, sprintf( "DBI Error: $err : $errStr" ), 0 );
    }

    # ---------------
    # Test for ping
    # ---------------

    sub PingTest {
        my $my_addr = "10.0.66.119";
        my $host = $_[0];
        my $rc = 0;

#      my ($package, $filename, $line, $subroutine) = caller(0);
#      print "Caller: $filename, linea: $line, en el sub: $subroutine\n";
   
        my $p = Net::Ping->new("icmp");
        $p->hires();
        $p->bind($my_addr); # Specify source interface of pings
        $rc = $p->ping($host,500);
        $p->close();
        return $rc;
    }

    # -------------------
    # Dlr
    # -------------------
    sub Dlr {
        my $messageId = $_[0];
        my $statusDelivery = $_[1];
        my $sendDate = $_[2];
        my $doneDate = $_[3];
        my $carrier = $_[4];
        my $dbh = $_[5];
  
        my $sql = sprintf( "insert into dlrStatus (`id`,`dlrDate`,`ip`,`messageId`,`statusDelivery`,`sendDate`,`doneDate`,`carrier`) values(0,now(),'%s','%s','%s','%s','%s','%s')", $ip, $messageId, $statusDelivery, $sendDate, $doneDate, $carrier );
        my $dlr = $dbh->prepare($sql);
        if ( !$dlr->err ) {
           $dlr->execute();
        }
        else {
           db_Error( DEBUG, $dlr->err, $dlr->errstr );
        }
        $dlr->finish();
  
  	    my $url = sprintf( "http://65.108.69.39" ); 
        my $dlr_text = sprintf("messageId=%s&statusDelivery=%s&sendDate=%s&doneDate=%s&carrier=%s", $id, $statusDelivery, $sendDate, $doneDate, $carrier );

=begin
      my $curl = WWW::Curl::Easy->new;
      $curl->setopt( CURLOPT_HEADER,0);
      $curl->setopt( CURLOPT_URL, $url);
      $curl->setopt( CURLOPT_MAXREDIRS, 10 );                                                
      $curl->setopt( CURLOPT_POSTFIELDS, $dlr_text );
      $curl->setopt( CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
      $curl->setopt( CURLOPT_HTTPAUTH, CURLAUTH_ANY ) ;
      $curl->setopt( CURLOPT_POST, 1);
      $curl->setopt( CURLOPT_TIMEOUT, 0);
      $curl->setopt( CURLOPT_FOLLOWLOCATION, 1);
      $curl->setopt( CURLINFO_HEADER_OUT, 1 );
      
      my $response_body;
      $curl->setopt(CURLOPT_WRITEDATA,\$response_body);
      my $retcode = $curl->perform;
	   return $retcode;
=cut
    }

   # ------------------------------------
   # CopyTableRow
   # ------------------------------------
   sub CopyTableRow
   {
      my $datatable = $_[0];
      my $dbh = $_[1];
      my $id = $_[2];
      my @cols;
      my $first = 1;

      my $sqlQuery = sprintf( "select sql_no_cache id from %s where id=%d;", $datatable, $id );
      my $result = $dbh->prepare($sqlQuery);

      if ( $result->err ) {
         $result->finish();
         return( 0 );
      }
      else {
         $result->execute();

         # -------------------
         # Si el id no existe 
         # -------------------
         if( $result->rows() == 0 ) {
            $result->finish();
            return( 0 );
         }
      }
      $result->finish();

      my $sql = sprintf( "show columns from %s;", $datatable );
      $result = $dbh->prepare($sql);

      if ( !$result->err ) {
         $result->execute();
         if( $result->rows() > 0 ) {
            $sql = sprintf( "insert into outbound_request (" );
            while(my @row = $result->fetchrow_array()) {
               (@cols) = @row; 
#               printf( "%s\n", $cols[0] );
               if( $first == 0 ) {
                  $sql .= ","
               }
               $sql .= "`" . $cols[0]. "`";
               $first = 0;
            }
            $sql .= ") values( ";
            $result->finish();

            $first = 1;
            my $sqlQuery = sprintf( "select sql_no_cache * from %s where id=%d;", $datatable, $id );
            $result = $dbh->prepare($sqlQuery);

            if ( !$result->err ) {
               $result->execute();
               if( $result->rows() > 0 ) {
                  if(my @row = $result->fetchrow_array()) {
                     (@cols) = @row; 
                     for my $i (0 .. $#cols) {
                        if( $first == 0 ) {
                           $sql .= ",";
                        }

                        switch( $i ) {
                           
                           case 0 {
                              $sql .= "'0'";
                           }

                           case 18 {
                              $sql .= "1";
                           }

                           case 19 {
                              $sql .= "''";
                           }

                           case /^(31|32)$/ {
                              $sql .= "0";
                           }

                           else {
                              $sql .= "'" . $cols[$i] . "'";
                           }
                        }
                        $first = 0;
                     }
                  }
                  $sql .= ");";
               }
            }

            printf( "%s\n", $sql );
            $result->finish();
            $result = $dbh->prepare($sql);
            if ( !$result->err ) {
               $result->execute();
            }
            $result->finish();
         }
      }
      $result->finish();
   } 


# --------------------------------------
# writeToLog
# --------------------------------------

sub writeToDebugLog 
{  
   my $level = $_[0];
   my $filename = $_[1];
   my $text = $_[2];
   my $mode = $_[3];

   if( $debug == DEBUG || $level <= $debug ) {
      if( open(FH, '>>', $filename) ) {
         if( $mode == 1 ) {
            if( $last_line == 1 ) {
               close(FH);
               return;
            }
            $text = "+--------------------------------------------------------------------------------------------------+";
            $last_line = 1;
         }
         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
         my $hoy = sprintf( "%02d.%02d.%04d %02d:%02d:%02d", $mday, $mon + 1, $year + 1900, $hour, $min, $sec );
         printf( FH sprintf( "%s: %s\n", $hoy, $text ) );
         printf( sprintf( "%s: %s\n", $hoy, $text ) );
         close(FH);
         if( $mode == 0 ) {
            $last_line = 0;
         }
      }      
   }
}