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

13 九月 2013

Perl SugarCRM converting database – meetings table

#!/usr/bin/perl

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 user here’,
‘dbpasswd’ => ‘db password here’,
‘table’=> “$ARGV[ 0 ]” );

# Old CRM information
my %crm = ( ‘database’ => ‘sugarcrm’,
‘host’ => ‘old sugarcrm hostname 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(6.2.x) database information string
my $crmdb = “DBI:mysql:database=$crm{ ‘database’ };host=$crm{ ‘host’ };port=$crm{ ‘port’ }”;

# New SugarCRM(6.5.x)’s object handler
my $newcrmdbh = DBI->connect( “$newcrmdb”, $newcrm{ ‘dbuser’ }, $newcrm{ ‘dbpasswd’ }, { RaiseError => 1, mysql_enable_utf8 => 1} );
# Old SugarCRM(6.2.x)’s 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 crm 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 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 SQL SELECT
# id part.
##################################################
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/,$//;
#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 == 25 ) # the last field count, add ),(
{
# $newcrm_sql_stmt .= “‘-1′,’0’,'”.$_.”‘”.”,’0′,”,’1’,”,”,”,”,”),(“;
$newcrm_sql_stmt .= “‘-1′,’0’,'”.$_.”‘”.”,’0′,NULL,’1’,NULL,NULL,NULL,NULL,NULL),(“;
}
else
{
$_ =~ s/’//g;
$newcrm_sql_stmt .= $_ ? “‘”.$_.”‘”.’,’:””,”;
}
$count++;
}

}
$newcrm_sql_stmt =~ s/..$//;
#print $newcrm_sql_stmt;

#exit;
my $newcrmstmt = $newcrmdbh->prepare( “$newcrm_sql_stmt” );

$newcrmstmt->execute();