vcftogcontact.pl

一个页面界面的邮件程序

#!/usr/bin/perl -w
# an script to import a vcf export from Palm Desktop to GContact

use DBI;

# Undo is a text file which should allow you to back-step all your actions if something went wrong with the import
# just execute vcfToGcontact.pl undo to undo last import.
my $undo = "./undo.sql";

# add your database details here
my $db_user = "gcontact";
my $db_pass = "GC0NT4CT";
my $db_name = "gcontact";

# Some words used for your contact details - you may want to personalise this
my %wordfor;
$wordfor{phone_mobile} = "Mobile";
$wordfor{email} = "Email";
$wordfor{phone_home} = "Home";
$wordfor{phone_work} = "Work";
$wordfor{address_home} = "Address1";
$wordfor{organisation} = "Organisation";

# Initialising some global variables
my $dbh;
my $sth;

# If someone puts undo as first argument, then undo last import (if undo file exists)
if( $ARGV[0] eq 'undo' ){
    $dbh = DBI->connect( "DBI:mysql:host=localhost;database=$db_name", $db_user, $db_pass, {PrintError => 0, RaiseError => 1 } ) or die "Could not connect to database: $!";
    open( UNDO, "<$undo" ) or die( "Could not read undo file: $!" );
    while( $line = readline( UNDO ) ){
        chomp( $line );
        &execQuery( $line );
    }
    print "All lines undon\n";
    exit;
}

my $source = $ARGV[0];
my $iduser = $ARGV[1];
my $groupname = $ARGV[2];
my $defaultRole = 2;
my $noUpdate = undef;

die "No groupname specified" unless( $groupname );
die "No user id specified" unless( $iduser );
open( SOURCE, "<$source" ) or die "Could not open source: $!";
open( UNDO, ">$undo" ) or die "Could not open undo fiel: $!";

$dbh = DBI->connect( "DBI:mysql:host=localhost;database=$db_name", $db_user, $db_pass, {PrintError => 0, RaiseError => 1 } ) or die "Could not connect to database: $!";

# check here that the userid given exists, and that it owns a group with the group name given
&execQuery( "SELECT id FROM $db_name.user WHERE id='$iduser';" );
die "A user with ID $iduser does not exist in this database" unless( ( $sth->fetchrow() )[0] );
# check here if that user has a group with that name
&execQuery( "SELECT id FROM $db_name.group WHERE iduser='$iduser' AND name='$groupname';" );
my $idgroup;
die "The user $iduser does not have a group with the name $groupname in this database" unless( $idgroup = ( $sth->fetchrow() )[0] );

# I've fudged the creation of new persons and how to deal with entries which don't have a first name... this helps keep track.
my $gotFirstName = undef;

# Now read the VCF file and start the real work!
VCARD:
while( $line = readline( SOURCE ) ){
  # look for the first vcard in the file
  next unless( $line =~ m/^BEGIN:VCARD/ );
  # Ok - we've obviously found the start of a vcard
  # First, we'd better create a new user_person link, and a person
  &execQuery( "INSERT INTO $db_name.person ( firstname, idgroup ) VALUES( 'new_person', '$idgroup' );" );
  # now get the ID of this new contact
  &execQuery( "SELECT id FROM $db_name.person WHERE firstname='new_person';" );
  $idperson = ( $sth->fetchrow() )[0];
  print UNDO  "DELETE FROM $db_name.person WHERE id='$idperson' LIMIT 1;\n";

  &execQuery( "INSERT INTO $db_name.user_person ( idperson, iduser, role ) VALUES( '$idperson', '$iduser', '$defaultRole' );" );
  print UNDO  "DELETE FROM $db_name.user_person WHERE idperson='$idperson' LIMIT 1;\n";

  $gotFirstName = undef;
  # Now let's try and parse the rest of the details for this person
  my @queries;
 LINE:
  while( $line = readline( SOURCE ) ){
    last LINE if( $line =~ m/END:VCARD/ );
    $line =~ s/\n//;
    $line =~ s/\r//;
    $line =~ s/'/\\'/g;

    # the info for person may be distributed over several lines, so we need to collect that first before writing
    next LINE if( $line =~ m/^FN:/ );  # Skip "Full name" lines
    if( $line =~ m/^N:(.*)$/ ){
      ( $lastname, $firstname ) = split( /;/, $1 );
      push( @queries, "UPDATE $db_name.person SET firstname='" . ( $firstname ? $firstname : "" ) . "', lastname='$lastname' WHERE id='$idperson' LIMIT 1;" );
      $gotFirstName = 1 if( $firstname );
    }elsif( $line =~ m/^BDAY:(.*)$/ ){
      ( $year, $mon, $day ) = unpack( "a4a2a2", $1 );
      push( @queries, "UPDATE $db_name.person SET birthdate='$year-$mon-$day' WHERE id='$idperson' LIMIT 1;" );
    }elsif( $line =~ m/^TEL;CELL:(.*)$/ ){
      push( @queries, "INSERT INTO $db_name.contact ( idperson, type, value ) VALUES( $idperson, '$wordfor{phone_mobile}', '$1' );" );
      print UNDO  "DELETE FROM $db_name.contact WHERE idperson='$idperson' AND type='$wordfor{phone_mobile}' AND value='$1' LIMIT 1;\n";
    }elsif( $line =~ m/^TEL;HOME:(.*)$/ ){
      push( @queries, "INSERT INTO $db_name.contact ( idperson, type, value ) VALUES( $idperson, '$wordfor{phone_home}', '$1' );" );
      print UNDO  "DELETE FROM $db_name.contact WHERE idperson='$idperson' AND type='$wordfor{phone_home}' AND value='$1' LIMIT 1;\n";
    }elsif( $line =~ m/^TEL;WORK:(.*)$/ ){
      push( @queries, "INSERT INTO $db_name.contact ( idperson, type, value ) VALUES( $idperson, '$wordfor{phone_work}', '$1' );" );
      print UNDO  "DELETE FROM $db_name.contact WHERE idperson='$idperson' AND type='$wordfor{phone_work}' AND value='$1' LIMIT 1;\n";
    }elsif( $line =~ m/^EMAIL:(.*)$/ ){
      push( @queries, "INSERT INTO $db_name.contact ( idperson, type, value ) VALUES( $idperson, '$wordfor{email}', '$1' );" );
      print UNDO  "DELETE FROM $db_name.contact WHERE idperson='$idperson' AND type='$wordfor{email}' AND value='$1' LIMIT 1;\n";
    }elsif( $line =~ m/^ORG:(.*)$/ ){
        # As ORG; (organisation) tags always come after Name line, if the name has not been found yet here, I'll insert it as a first name, else give it a tag of organisation.
        if( $gotFirstName ){
            push( @queries, "INSERT INTO $db_name.contact ( idperson, type, value ) VALUES( $idperson, '$wordfor{organisation}', '$1' );" );
            print UNDO  "DELETE FROM $db_name.contact WHERE idperson='$idperson' AND type='$wordfor{organisation}' AND value='$1' LIMIT 1;\n";
        }else{
            push( @queries, "UPDATE $db_name.person SET firstname='$1' WHERE id='$idperson' LIMIT 1;" );
            print "UPDATE $db_name.person SET firstname='$1' WHERE id='$idperson' LIMIT 1;";
            $gotFirstName = 1 if( $1 );
        }
    }elsif( $line =~ m/^NOTE;ENCODING=QUOTED-PRINTABLE:(.*)$/ ){
      $note = $1;
      $note =~ s/=0D=0A/\n/g;
      push( @queries, "UPDATE $db_name.person SET note='$note' WHERE id='$idperson' LIMIT 1;" );
    }elsif( $line =~ m/^NOTE:(.*)$/ ){
      push( @queries, "UPDATE $db_name.person SET note='$1' WHERE id='$idperson' LIMIT 1;" );
    }elsif( $line =~ m/^ADR;WORK:(.*)$/ ){
      @addFields = split( /;/, $1 );
      push( @queries, "INSERT INTO address ( idperson, title, street, town, zipcode, country ) VALUES( '$idperson', '$wordfor{address_home}', '$addFields[2]', " .
        "'$addFields[3]', '$addFields[5]', '$addFields[6]' );" );
      print UNDO  "DELETE FROM $db_name.address WHERE idperson='$idperson' AND title='$wordfor{address_home}' AND street='$addFields[2]' AND town='$addFields[3]' " .
          "AND zipcode='$addFields[5]' AND country='$addFields[6]' LIMIT 1;\n";
    }elsif( $line =~ m/^ADR;WORK;ENCODING=QUOTED-PRINTABLE:(.*)$/ ){
      @addFields = split( /;/, $1 );
      $addFields[2] =~ s/=0D=0A/\n/g;
      push( @queries, "INSERT INTO address ( idperson, title, note, town, zipcode, country ) VALUES( '$idperson', '$wordfor{address_home}','$addFields[2]', " .
        "'$addFields[3]', '$addFields[5]', '$addFields[6]' );" );
      print UNDO  "DELETE FROM $db_name.address WHERE idperson='$idperson' AND title='$wordfor{address_home}' AND town='$addFields[3]' " .
          "AND zipcode='$addFields[5]' AND country='$addFields[6]' LIMIT 1;\n";
    }
  }
  unless( $gotFirstName ){
      push( @queries, "UPDATE $db_name.person SET firstname='' WHERE id='$idperson' LIMIT 1;" );
  }
  foreach( @queries ){
    &execQuery( $_ );
  }
}
close SOURCE;
close UNDO;
exit;

# Yup - just a subroutine to execute a query.
sub execQuery{
  my $queryString = shift;
  unless( $noUpdate && ( $queryString =~ m/^INSERT/ || $queryString =~ m/^UPDATE/ )){
    $sth = $dbh->prepare($queryString) or return "Error preparing: $!";
    $sth->execute() or die( "Error executing query: $queryString\n\n$!" );
  }
}