mysql_erreur-abonne.pl

#!/usr/bin/perl -w
#
# mysql_erreur-abonne.pl
#
# Catherine.Balleydier@inpg.fr
#
# maj : 21/09/16
#
########################################################################
# 
#   recherche des abonnes en erreur
#
########################################################################
#
use strict;
use warnings;
use DBI;
use DBD::mysql ;
use Term::ReadKey;
 
 
my $compte = "sympa" ;
my $BASE = "sympa" ;
my $host = "localhost" ;
 
my $REP = "/usr/local/SCRIPTS/sympa/RESUL" ;
 
my $coderr ;
 
#
#  demande du password de la base sympa
#
my $passe;
print "Password: ";
ReadMode 2;
    $passe = <>;
ReadMode 0;
print "\n";
chomp($passe);
 
#
# Connexion a la base sympa
#
my $data_source = "DBI:mysql:database=$BASE;host=$host";
my $db;
$db = DBI->connect( $data_source , $compte, $passe);
#$db->selectdb($BASE);
 
#
# Requete sql : recherche des adresses en erreur 
#
my $fichier = "$REP"."/adresse-erreur" ;
my $table = "subscriber_table" ;
open (FICH,">$fichier") ||die "probleme fichier";
my $query1 = "SELECT user_subscriber,list_subscriber,bounce_subscriber FROM $table where  bounce_subscriber != 'NULL' ";
my $execution = $db->prepare($query1) ;
$execution ->execute();
#
# Recuperation dans un tableau les abonnes avec la liste 
#
my $ind = 0 ;
while ( my @result = $execution->fetchrow() )
{
  $coderr = "NULL" ;
  if ( $result[2] =~ /4\.0\.0/ ) { $coderr = "4.0.0 Other undefined Status"; goto IMPR; }
  if ( $result[2] =~ /4\.1\.1/ ) { $coderr = "4.1.1 Bad destination mailbox address"; goto IMPR; }
  if ( $result[2] =~ /4\.2\.0/ ) { $coderr = "4.2.0 Other or undefined mailbox status"; goto IMPR; }
  if ( $result[2] =~ /4\.2\.2/ ) { $coderr = "4.2.2 Mailbox full"; goto IMPR; }
  if ( $result[2] =~ /4\.3\.0/ ) { $coderr = "4.3.0 Other or undefined mail system status"; goto IMPR; }
  if ( $result[2] =~ /4\.4\.1/ ) { $coderr = "4.4.1 No answer from host "; goto IMPR; }
  if ( $result[2] =~ /4\.4\.2/ ) { $coderr = "4.4.2 Bad connection"; goto IMPR; }
  if ( $result[2] =~ /4\.4\.3/ ) { $coderr = "4.4.3 Directory server failure"; goto IMPR; }
  if ( $result[2] =~ /4\.4\.6/ ) { $coderr = "4.4.6 Routing loop detected"; goto IMPR; }
  if ( $result[2] =~ /4\.4\.7/ ) { $coderr = "4.4.7 Delivery time expired "; goto IMPR; }
  if ( $result[2] =~ /4\.7\.0/ ) { $coderr = "4.7.0 Temporary server error"; goto IMPR; }
  if ( $result[2] =~ /4\.7\.1/ ) { $coderr = "4.7.1 Cannot find hostname"; goto IMPR; }
 
  if ( $result[2] =~ /5\.0\.0/ ) { $coderr = "5.0.0 Host or domain name not found"; goto IMPR; }
  if ( $result[2] =~ /5\.0\.350/ ) { $coderr = "5.0.350 Undefined error"; goto IMPR; }
  if ( $result[2] =~ /5\.1\.0/ ) { $coderr = "5.1.0 Unknown address "; goto IMPR; }
  if ( $result[2] =~ /5\.1\.10/ ) { $coderr = "5.1.10 Recipient address has null MX"; goto IMPR; }
  if ( $result[2] =~ /5\.1\.1/ ) { $coderr = "5.1.1 Bad destination mailbox address"; goto IMPR; }
  if ( $result[2] =~ /5\.1\.3/ ) { $coderr = "5.1.3 Bad destination mailbox address syntax"; goto IMPR; }
  if ( $result[2] =~ /5\.1\.6/ ) { $coderr = "5.1.6 Destination mailbox has moved, No forwarding address "; goto IMPR; }
  if ( $result[2] =~ /5\.2\.0/ ) { $coderr = "5.2.0 Other or undefined mailbox status"; goto IMPR; }
  if ( $result[2] =~ /5\.2\.1/ ) { $coderr = "5.2.1 Mailbox disabled, not accepting messages"; goto IMPR; }
  if ( $result[2] =~ /5\.2\.2/ ) { $coderr = "5.2.2 Mailbox full "; goto IMPR; }
  if ( $result[2] =~ /5\.2\.3/ ) { $coderr = "5.2.3 Message length exceeds administrative limit "; goto IMPR; }
  if ( $result[2] =~ /5\.3\.0/ ) { $coderr = "5.3.0 Other or undefined mail system status"; goto IMPR; }
  if ( $result[2] =~ /5\.3\.4/ ) { $coderr = "5.3.4 Message too big for system"; goto IMPR; }
  if ( $result[2] =~ /5\.3\.5/ ) { $coderr = "5.3.5 Problem with DNS MX records"; goto IMPR; }
  if ( $result[2] =~ /5\.4\.0/ ) { $coderr = "5.4.0 Other or undefined network or routing status"; goto IMPR; }
  if ( $result[2] =~ /5\.4\.14/ ) { $coderr = "5.4.14 Hop count exceeded "; goto IMPR; }
  if ( $result[2] =~ /5\.4\.1/ ) { $coderr = "5.4.1 Unable to route"; goto IMPR; }
  if ( $result[2] =~ /5\.4\.2/ ) { $coderr = "5.4.2 Bad Connection"; goto IMPR; }
  if ( $result[2] =~ /5\.4\.316/ ) { $coderr = "5.4.316 Message expired, connection refused"; goto IMPR; }
  if ( $result[2] =~ /5\.4\.4/ ) { $coderr = "5.4.4 Unable to route"; goto IMPR; }
  if ( $result[2] =~ /5\.4\.6/ ) { $coderr = "5.4.6 Routing loop detected"; goto IMPR; }
  if ( $result[2] =~ /5\.5\.0/ ) { $coderr = "5.5.0 Other or undefined protocol status"; goto IMPR; }
  if ( $result[2] =~ /5\.6\.1/ ) { $coderr = "5.6.1 Body type not supported by Remote Host"; goto IMPR; }
  if ( $result[2] =~ /5\.7\.0/ ) { $coderr = "5.7.0 Other or undefined security status"; goto IMPR; }
  if ( $result[2] =~ /5\.7\.1/ ) { $coderr = "5.7.1 Delivery not authorized, message refused"; goto IMPR; }
  if ( $result[2] =~ /5\.7\.367/ ) { $coderr = "5.7.367 Sender is not authorised for Relay"; goto IMPR; }
  if ( $result[2] =~ /5\.7\.64/ ) { $coderr = "5.7.64 Relay Access Denied"; goto IMPR; }
  if ( $result[2] =~ /5\.7\.9/ ) { $coderr = "5.7.9 Message content is not acceptable here"; goto IMPR; }
 
IMPR : print FICH "$result[0]"." \; "."$result[1]"." \; "."$result[2]"." \; "."$coderr"." \n";
print " $result[2] : $coderr \n" ;
 
  $result[0] = "";
  $result[1] = "";
  $result[2] = "";
 
  $ind++ ;
}
 
close (FICH) ;
$execution->finish();
$db->disconnect() or warn "Error disconnecting: $DBI::errstr\n";