喬遷啟示:因應實體教室租約到期,富捷培訓於八月起將在長安新址,以線上直播教學為您服務,懇請舊雨新知繼續支持與指教。 地址:台北市長安東路二段201巷19號之1一樓,電話: 02-27116373

13 九月 2013

SugarCRM Perl 程式 – 轉移6.2.x contacts table 到 6.5.x

SugarCRM Perl 程式轉移contacts表單實例

原始碼如下:

#!/usr/bin/perl -w
#####################################################
# SugarCRM Perl script converts contacts table from
# 6.2.x to 6.5.x
######################################################
if( @ARGV != 1 )
{
print “\nUsage:$0 <table name>.\n\n”;
exit 1;
}

use strict;
use DBI;
use Crypt::PasswdMD5;
use utf8;
#use Encode;
use Data::Dumper;

binmode( STDOUT, “:encoding(utf-8)” );
binmode( STDIN, “:encoding(utf-8)” );
binmode( STDERR, “:encoding(utf-8)” );

# New SugarCRM(6.5.x)information
my %newcrm = ( ‘database’ => ‘sugarcrm’,
‘host’ => ‘localhost’,
‘port’ => ‘3306’,
‘dbuser’ => ‘db username here’,
‘dbpasswd’ => ‘db password here’,
‘table’=> “$ARGV[ 0 ]” );

# Old SugarCRM(6.2.x) information
my %crm = ( ‘database’ => ‘sugarcrm’,
‘host’ => ‘old sugarcrm host here’,
‘port’ => ‘3306’,
‘dbuser’ => ‘db username here’,
‘dbpasswd’ => ‘db password here’ ,
‘table’=> “$ARGV[ 0 ]” );

# Field names string to represent New SugarCRM(6.5.x) table fields
my $sql_select_field = ”;
my %crm_table_fields = ();
my @crm_table_fields = ();

# New SugarCRM(6.5.x) database information string
my $newcrmdb = “DBI:mysql:database=$newcrm{ ‘database’ };host=$newcrm{ ‘host’ };port=$newcrm{ ‘port’ }”;
# OLD SugarCRM database information string
my $crmdb = “DBI:mysql:database=$crm{ ‘database’ };host=$crm{ ‘host’ };port=$crm{ ‘port’ }”;

# New SugarCRM(6.5.x) object handler
my $newcrmdbh = DBI->connect( “$newcrmdb”, $newcrm{ ‘dbuser’ }, $newcrm{ ‘dbpasswd’ }, { RaiseError => 1, mysql_enable_utf8 => 1} );
# Old SugarCRM object handler
my $crmdbh = DBI->connect( “$crmdb”, $crm{ ‘dbuser’ }, $crm{ ‘dbpasswd’ }, { RaiseError => 1, mysql_enable_utf8 => 1} );

# prepare New SugarCRM(6.5.x) sql statement
my $newcrmsth = $newcrmdbh->prepare( “SELECT COLUMN_NAME from information_schema.columns where table_schema=’sugarcrm’ and table_name=\’$newcrm{ table }\’ “);
# prepare old SugarCRM sql statement
my $crmsth = $crmdbh->prepare( “SELECT COLUMN_NAME from information_schema.columns where table_schema=’sugarcrm’ and table_name=\’$crm{ table }\’ “);

#executing newcrm SQL
$newcrmsth->execute();
#executing crm SQL
$crmsth->execute();

##################################################
# Getting old crm’s field names into %crm_table_fields
##################################################
while( my $crmsthref = $crmsth->fetchrow_hashref() )
{
foreach ( keys %$crmsthref )
{
$crm_table_fields{ $crmsthref->{ $_ } } = 1;
}
}

##################################################
# Compare newcrm and crm’s fields, choose those fields
# that only exist in newcrm, then form the id part of
#SQL SELECT statement.
##################################################
while( my $ref = $newcrmsth->fetchrow_hashref() )
{
foreach ( keys %$ref )
{
if( exists( $crm_table_fields{ $ref->{ $_ } } ) )
{
$sql_select_field .= $ref->{ $_ }.’,’;
}
}
}

$crmsth->finish;
#$newcrmsth->finish;
$sql_select_field =~ s/,$//;
###### debugging statement ########
#print “$sql_select_field\n”;
#exit;
###################################

my $query_crm_stmt = “SELECT $sql_select_field FROM $crm{ ‘table’ } “;

my $crmstmt = $crmdbh->prepare( $query_crm_stmt );
$crmstmt->execute();

##############################################
# Insert data into newcrm SQL statement – $newcrm_sql_stmt
##############################################

my $newcrm_sql_stmt = “INSERT INTO $newcrm{ ‘table’ } VALUES (“;

while( my $ref = $crmstmt->fetchrow_arrayref() )
{
my $count = 0;
foreach ( @$ref )
{
if( $count == 34 )
{
$newcrm_sql_stmt .= “‘”.$_.”‘”.’),(‘;
}
else
{
$newcrm_sql_stmt .= $_ ? “‘”.$_.”‘”.’,’:””,”;
}
$count++;
}

}

$newcrm_sql_stmt =~ s/..$//;
###### debugging statement ##########
#print $newcrm_sql_stmt;
#exit;
#####################################
my $newcrmstmt = $newcrmdbh->prepare( “$newcrm_sql_stmt” );

$newcrmstmt->execute();