=begin # -------------------------------------------------------------------------------------------- # Verifico si hay puertos deshabilitados y existe algun slot que puede ser habilitado # -------------------------------------------------------------------------------------------- sub SlotAvailable { my $dbh = $_[0]; my @jsessionid = $_[1]; my $display = 0; # ------------------------------------------------------- # Busco los Puertos/Slot habilitados de cada Gateway # # Enabled = 1 Esta con sim # Status = 1 Es que esta en uso # Registered = 1 Es que esta registrado # ------------------------------------------------------- my $sql = sprintf( "select sql_no_cache count(id) as ports, gw, port from gateway_ports where enabled=1 group by gw,port;" ); my $resultPorts = $dbh->prepare($sql); if ( !$resultPorts->err ) { if( $resultPorts->execute() ) { while (my @rowPorts = $resultPorts->fetchrow_array()) { my ($ports, $gw, $port ) = @rowPorts; # writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: GW %d, Port %d with %d ports enabled", $gw, $port, $ports ), 0 ); # ---------------------------------------------------------------------------- # Busco cuantos mensajes diarios tiene disponibles cada slot de ese puerto # Empiezo con cual esta registrado. # ---------------------------------------------------------------------------- my $needSlot = 1; my $sql = sprintf( "select sql_no_cache gateway_ports.id, gw, port, slot, date(fecha_ultimo), gateway_ports.plan, maximo_diarios, enabled, gateway_ports.status, registered from gateway_ports left join planes on planes.id=gateway_ports.plan where enabled=1 and gw=%d and port=%d order by registered, status desc;", $gw, $port ); my $resultEnabled = $dbh->prepare($sql); if ( !$resultEnabled->err ) { if( $resultEnabled->execute() ) { while (my @row = $resultEnabled->fetchrow_array()) { my ($id, $gw_port, $portnum, $slot, $ultimo, $plan, $maximo_diarios, $enabled, $port_status, $registered ) = @row; $sql = sprintf( "select sql_no_cache gateway, port, slot, fecha, enviados, plan from port_statistics where gateway=%d and port=%d and slot='%s' and date(fecha)=date(now());", $gw_port, $port, $slot ); my $resultStat = $dbh->prepare($sql); if ( !$resultStat->err ) { if( $resultStat->execute() ) { if ( $resultStat->rows() <= 0 ) { $resultStat->finish(); writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: Insert Statistics GW %d, Ports: %d Port: %d%s", $gw, $ports, $port, $slot ), 0 ); $display = 1; $sql = sprintf( "insert into `port_statistics` (`id`,`fecha`,`gateway`,`port`,`slot`,`enviados`,`plan`,`status`) VALUES (0,now(),%d,%d,'%s',0,%d,1);", int($gw_port), int( $port ), $slot, int($plan) ); $resultStat = $dbh->prepare($sql); if ( !$resultStat->err ) { $resultStat->execute(); } } } } $resultStat->finish(); # ---------------------------------------- # Verifico mensajes disponibles por Slot # ---------------------------------------- # writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: Test GW %d, Port %d%s Status: %d", $gw_port, $port, $slot, $port_status ), 0 ); $sql = sprintf( "select sql_no_cache ip, api_user, api_password, port_statistics.gateway as port_gateway, port_statistics.port as pport, slot, fecha, enviados, status, plan, sn, user, password from port_statistics left join gateways on gateways.id=port_statistics.gateway where port_statistics.gateway=%d and port_statistics.port=%d and port_statistics.slot='%s' and date(fecha)=date(now());", $gw_port, $port, $slot ); my $resultSend = $dbh->prepare($sql); if ( !$resultSend->err ) { if( $resultSend->execute() ) { if ( $resultSend->rows() > 0 ) { if (my @rowSend = $resultSend->fetchrow_array()) { my ($ip, $api_user, $api_password, $gw, $port, $slot, $ultimo, $enviados, $status, $plan, $sn, $user, $gw_password ) = @rowSend; my $auth = sprintf( "%s:%s", $api_user, $api_password ); # ------------------------------------------------------------------ # Si supero el maximo diario y esta activo, inhabilito el status # ------------------------------------------------------------------ # printf( "Maximo: %d/%d Status: $enabled/%d\n", $enviados, $maximo_diarios, $status ); if( $enviados >= $maximo_diarios && $enabled == 1 && $port_status == 1 ) { writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: DISABLE GW %d, Port %d Slot '%s'", $gw, $port, $slot ), 0 ); $display = 1; # -------------- # Disable Slot # -------------- if( !defined($jsessionid[$gw]) ) { my $session = loginGateway( $ip, $user, $gw_password ); $jsessionid[$gw] = $session; if( !defined($jsessionid[$gw]) ) { writeToLog( WARNING, $logfile, sprintf( "Invalid JSESSIONID GW: %d, %d%s", $gw, $port, $slot ), 0 ); } else { disableSlot( $ip, $port, $slot, $auth, $jsessionid[$gw], $sn ); $sql = sprintf( "update gateway_ports set status=0 where gw=%d and port=%d and slot='%s';", $gw, $port, $slot ); my $resultUpdate = $dbh->prepare($sql); if ( !$resultUpdate->err ) { $resultUpdate->execute(); } $resultUpdate->finish(); } } $needSlot = 1; } else { if( $enviados < $maximo_diarios && $enabled == 1 && $port_status == 1 ) { # ----------------------------------------------------------- # Si no esta registrado, lo registro # ----------------------------------------------------------- if( $registered == 0 ) { my $sql = sprintf( "select sql_no_cache gateway_ports.id, gw, port, slot, registered from gateway_ports where registered=1 and gw=%d and port=%d;", $gw, $port ); my $resultDereg = $dbh->prepare($sql); if ( !$resultDereg->err ) { if( $resultDereg->execute() ) { while (my @rowDereg = $resultDereg->fetchrow_array()) { my ($idd, $dgw, $dportnum, $dslot, $dregistered ) = @rowDereg; writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: DEREGISTER GW %d, Port %d:'%s'", $dgw, $dportnum, $dslot ), 0 ); if( !defined($jsessionid[$gw]) ) { my $session = loginGateway( $ip, $user, $gw_password ); $jsessionid[$gw] = $session; if( !defined($jsessionid[$gw]) ) { writeToLog( WARNING, $logfile, sprintf( "Invalid JSESSIONID GW: %d, %d%s", $gw, $port, $slot ), 0 ); } else { # ----------------- # Inhabilitar Slot # ----------------- $display = 1; writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: REGISTER GW %d, Port %d:'%s'", $gw, $port, $slot ), 0 ); $sql = sprintf( "update gateway_ports set status=1, registered=0 where gw=%d and port=%d and slot='%s';", $dgw, $dportnum, $dslot ); my $resultUpdate = $dbh->prepare($sql); if ( !$resultUpdate->err ) { $resultUpdate->execute(); } $resultUpdate->finish(); # -------------- # Dsiable Slot # -------------- disableSlot( $ip, $dportnum, $dslot, $auth, $jsessionid[$gw], $sn ); } } } } } $resultDereg->finish(); # ------------------------ # Desregistro los demas # ------------------------ } # $display = 1; # writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: GW %d, Port %d Slot '%s' NO NEED TO CHANGE", $gw, $port, $slot ), 0 ); $needSlot = 0; } else { # ----------------- # Slot Disponible # ----------------- if( $enviados < $maximo_diarios && $enabled == 1 && $port_status == 0 ) { if( !defined($jsessionid[$gw]) ) { my $session = loginGateway( $ip, $user, $gw_password ); $jsessionid[$gw] = $session; if( !defined($jsessionid[$gw]) ) { writeToLog( WARNING, $logfile, sprintf( "Invalid JSESSIONID GW: %d, %d%s", $gw, $port, $slot ), 0 ); } else { # ----------------- # Habilitar Slot # ----------------- $display = 1; writeToLog( DEBUG, $logfile, sprintf( "SlotAvailable: ENABLE GW %d, Port %d Slot '%s'", $gw, $port, $slot ), 0 ); $sql = sprintf( "update gateway_ports set status=1 where gw=%d and port=%d and slot='%s';", $gw, $port, $slot ); my $resultUpdate = $dbh->prepare($sql); if ( !$resultUpdate->err ) { $resultUpdate->execute(); } $resultUpdate->finish(); # -------------- # Enable Slot # -------------- enableSlot( $ip, $port, $slot, $auth, $jsessionid[$gw], $sn ); $needSlot = 0; } } } } } } } } } $resultSend->finish(); if( $needSlot == 0 ) { last; } } } } $resultEnabled->finish(); } } } $resultPorts->finish(); if( $display == 1 ) { writeToLog( DEBUG, $logfile, "", 1 ); } } =cut