mysql_modif_adresse-abonne.pl

#!/usr/bin/perl -w
#
# mysql_modif_adresse-abonne.pl
#
# Catherine.Balleydier@inpg.fr
#
# maj : 23/09/05
#
########################################################################
# 
#   modifier les adresses mails des abonnes  en changeant le doamine
#
########################################################################
#
use strict;
use warnings;
use File::Copy;
use DBI;
use DBD::mysql ;
use Term::ReadKey;

# donnees pour connexion a la base sympa
#

my $compte = "sympa" ;
my $BASE = "sympa" ;
my $host = "localhost" ;

#
#  demande du password de la base sympa
#
my $passe;
print "Password: ";
ReadMode 2;
    $passe = <STDIN>;
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);

&modification ( "\@ujf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@e.ujf-grenoble.fr", "\@etu.univ-grenoble-alpes.fr" );
&modification ( "\@obs.ujf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@lgge.obs.ujf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@spectro.ujf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@upmf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@iut2.upmf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@sah.upmf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@iae.upmf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@etu.upmf-grenoble.fr", "\@univ-grenoble-alpes.fr" );
&modification ( "\@u-grenoble3.fr", "\@univ-grenoble-alpes.fr" );

$db->disconnect() or warn "Error disconnecting: $DBI::errstr\n";

sub modification
{
  my $domaineanc = $_[0] ;
  my $domainenv = $_[1] ;
  my @result ;
  my $query1 ;
  my $query2 ;
  my $query3 ;
  my $execution ;
  my $result2 ;
#
# recherche d'abonne
#
  $query1 = "SELECT user_subscriber,list_subscriber FROM subscriber_table  where included_subscriber='0' and user_subscriber like '%"."$domaineanc"."'  ";
  $execution = $db->prepare($query1) ;
  $execution ->execute();

#
# Recuperation dans un tableau $Tab les abonnes avec les listes
# auxquelles ils appartiennent
#
  my $ind = 0 ;
  my @Tab ;
  while ( @result = $execution->fetchrow() )
  {
    $Tab[$ind][0] = $result[0];   # abonne
    $Tab[$ind][1] = $result[1];   # liste
    $ind++ ;
  }
  $execution->finish();

#
#  modification des adresses des abonnes
#
  for $ind (0..$#Tab)
  {
#
#  separation dans l'adresse du nom et du domaine
#
    my @tampon = split(/@/, $Tab[$ind][0]) ;
    my $adrnv = "$tampon[0]"."$domainenv" ;

#
# recherche si la nouvelle adresse existe dans la liste
#
    $query2 = "SELECT user_subscriber,list_subscriber FROM subscriber_table  where list_subscriber ='"."$Tab[$ind][1]"."' and user_subscriber = '"."$adrnv"."' ";
    $execution = $db->prepare($query2) ;
    $execution ->execute();
 
    if ( $result2 = $execution->fetchrow() )
    {
      $execution->finish();
      print " res : $result2 \n";
    }
    else
    {
      $execution->finish();
#    
# changement d'adresse
#
      $query3 = "UPDATE subscriber_table SET user_subscriber = '"."$adrnv"."' where user_subscriber = '"."$Tab[$ind][0]"."' and list_subscriber = '"."$Tab[$ind][1]"."'" ;
      $execution = $db->prepare($query3) ;
      $execution ->execute();
      $execution->finish();
    }
  }
}