#!/usr/bin/perl
# Author:    john@stilen.com
# Date:      20100820
# Purpose:   Convert data in database from this format =C3=B2=A1 (quoted-printable) to the utf8 enocded glyph.
# Reason:    After converting database from latin-1 to utf-8 some records 
#            look like content-type=quoted-printable, and character type wrong.
# Procedure: 1. Connect to database
#            2. SELECT records with = sign for a colunm where = is not valid (like first name).
#            3. For each record
#               3.1  Use module MIME::QuotedPrint to decode =C3=B2=A1 as quoted-printable content-type
#               3.2  Use module Encode::Detect to guess at the character encoding.
#               3.3  UPDATE record in database
#
#            If $debug=1, pause for user input before updating database.
#
#########################
# Load Modules 
#
use strict;
use DBI;
use Encode;
require Encode::Detect;
use open qw/:std :utf8/;
use utf8;
use MIME::QuotedPrint;
#
# Had to install for Chinese Chars.
#
use Encode::HanExtra;
use Encode::JIS2K;
use Encode::CN;
#
# Didn't end up using these:
#     Encode::DoubleEncodedUTF8 
#     Encoding::FixLatin
#########################
#
# Set debug for entire script
#
my $debug=1;
#########################
#
#  Some systems need this for some reasons.
#
#binmode STDOUT;
########################
#
# Fix one column at a time
#
my $column="first_name";
#my $column="last_name";
#my $column="address1";
#my $column="country";
#my $column="title";
#my $column="company";
#my $column="zip";
#########################
#
# MYSQL Variables
#
my	  $host='localhost';
my	   $dbd='mysql';
my	    $db='my_database';
my     $db_user='my_database_user';
my $db_password='my_database_password';
#########################
#
# Prepare sql to select data
#
my $sql=qq{SELECT id,${column} FROM `users` WHERE ${column} like '%=%'};
my $line;
my $counter=0;
#########################
#
# Turn on mysql debugging 
#
DBI->trace( $debug );
#########################
#
# Create the database connection object
#
my $dbh = DBI->connect("dbi:$dbd:$db:$host","$db_user","$db_password",{mysql_enable_utf8 => '1'})
   || die("Cannot connect: $DBI::errstr");    
$dbh->{RaiseError} = 0;
#########################
#
# Get data from the database
#
my $sth_getdata = $dbh->prepare( $sql);
$sth_getdata->execute() || print("n[Select ID]Error executing SQL statement! $DBI::errstrn");
#########################
#
# Process each Record
#
while  ( my $hash_ref = $sth_getdata->fetchrow_hashref() ){
   $counter=$counter+1;
   print "# $counter-------------------------------------------\n";
   print "Before:\n";
   for my $key (keys %$hash_ref) {   
          print "\t$key\t$$hash_ref{$key}\n";       
   }
   for my $key (keys %$hash_ref) {          
       #-------------------------
       # Convert from quoted-printable ( =??=??=?? )
       #-------------------------
       my $Decode_QP=decode_qp( $$hash_ref{$key} ) ;
       
       #-------------------------
       #  Guess at the encoding
       #-------------------------     
       my $translate=decode( "Detect", $Decode_QP);

       #-------------------------
       # Replace Origninal value
       #-------------------------
       $$hash_ref{$key}=$translate;
   }
   #-------------------------
   # Display the new data
   #-------------------------
   print "After:\n";
   for my $key (keys %$hash_ref) {   
          print "\t$key\t$$hash_ref{$key}\n";       
   }
   if ( $debug == 1 ){
       print "Press enter to update record (or ctrl-c to quit)\n";
       my $name = <STDIN>;
   }
   #-------------------------
   # Run the database update
   #-------------------------
   &update_data( $hash_ref );
     
}
print "Processed $counter records\n";
#########################
#$dbh->disconnect;
exit;
#########################
sub update_data(){
    #--------------------------------------------------
    # get user data in hash reference from caller
    #--------------------------------------------------
    my $href_userdata=shift();
    #--------------------------------------------------
    # Create sql UPDATE statement, and display it
    #--------------------------------------------------
    my $sql_update=qq{ UPDATE `users` SET  $column =  ? WHERE id=? LIMIT 1; } ;    
    print "$sql_update\tARGS: $$href_userdata{$column}, $$href_userdata{id}\n";
    #--------------------------------------------------
    # Prepare and execute statment.  On error, return error to caller.
    #--------------------------------------------------
    my $sth = $dbh->prepare( $sql_update );        
    $sth->execute( $$href_userdata{"$column"}, $$href_userdata{id} ) || return "Update Failed:\t$sth->errstr\n" ;
    #--------------------------------------------------
    # Return to caller
    #--------------------------------------------------
    return;
}