[mdrop@authbackup5 jojo]$ cat insert_sql_statement.sql

#!/usr/bin/perl -w

#
# Created by: JoJo
# Created Date: 28 July 2009
# Desc: To create the sql statement for Sanriotown Digital
#

use DateTime;
use DBI;


#open log file for writing, append purpose
open(MYLOGFILE, ">> mylog");


######### Step 1: Create DB Connection  ################################
#definition of variables
$db="UserDB";
$host="localhost";
$socket="/var/lib/auth5-1.us4/mysql1/mysql.sock";
$user="root";
$password="pwd";


#connect to MySQL database
my $dbh   = DBI->connect ("DBI:mysql::mysql_socket=$socket;database=$db:host=$host",$user,$password)
                         or die "Can't connect to database: $DBI::errstr"n";



######### Step 2: Get Active User ################################

# Get date
my $sdt = DateTime->now;
# Get active user ( from userdb, usertype != 8)
print MYLOGFILE "Get all active users from cobranddb.sanriotown_com_userdb tables at  $sdt."n";
#!`echo 'select username from cobranddb.sanriotown_com_userdb where usertype  &8 <> 8 '|mysql -uroot -ppwd --socket=/var/lib/auth5-1.us4/mysql/mysql.sock --skip-column-names >  activeuser.txt` || die print "Cannot connect to cobranddb database."n";
# Get date
my $edt = DateTime->now;
print MYLOGFILE "Finish grep all active users from cobranddb.sanriotown_com_userdb tables at  $edt."n";




######### Step 3: Get Active UserID and Field Value, then Create Insert Statement File#############

# Open active user file for reading
open (USERFILE, 'activeuser.txt');
# Get date
my $s1dt = DateTime->now;
# Get active userid and its field value for "country" filedname whose cobrand is "sanriotown.com"
print MYLOGFILE "Get active userid and its field value for 'country' filedname whose cobrand is 'sanriotown.com' from UserDB.UserProfileTbl and UserDB.UserTbl tables at  $s1dt."n";
while (<USERFILE>) {
  chomp;
  #prepare the query
  my $sql = "select  u.userid, p.fieldvalue from UserDB.UserProfileTbl p, UserDB.UserTbl u where u.cobrand='sanriotown.com' and u.username='$_' and u.userid=p.userid and p.fieldname='country' ";
  my $sth = $dbh->prepare( $sql);
  #execute the query
  $sth->execute( );

  ## Retrieve the results of a row of data and print
  my ( $userid,$fieldvalue);
  $sth->bind_columns ( undef,"$userid,"$fieldvalue );
  while ( $sth->fetch( ) )  {      
      #print MYLOGFILE  "Userid is $userid and the fieldvalue is $fieldvalue"n";
      `echo "insert UserDB.UserProfileTbl values($userid,'orignal_country', '$fieldvalue');" >> insert_sql_statement.sql`;
  }

  $sth->finish( );
}
# Get date
my $e1dt = DateTime->now;
print MYLOGFILE "Finish grep active userid and its field value for 'country' filedname whose cobrand is 'sanriotown.com' from UserDB.UserProfileTbl and UserDB.UserTbl tables at  $e1dt."n";


######### Step 4: Close All File and DB Connection#############

# Close all opened file
close (USERFILE);
close (MYLOGFILE);
$dbh->disconnect( );
exit;