<?php
/**
* @version	$Id: utf.php 15012 2012-01-06 20:38:49Z alex $
* @package	In-Portal
* @copyright	Copyright (C) 1997 - 2009 Intechnic. All rights reserved.
* @license      GNU/GPL
* In-Portal is Open Source software.
* This means that this software may have been modified pursuant
* the GNU General Public License, and as distributed it includes
* or is derivative of works licensed under the GNU General Public License
* or other free or open source software licenses.
* See http://www.in-portal.org/license for copyright notices and details.
*/

// comment out this line to run the script
die(' Should be enabled by the administrator! ');

$start = microtime(true);

define('FULL_PATH', realpath(dirname(__FILE__)).'/../../');
include_once(FULL_PATH.'/core/kernel/startup.php');

$application =& kApplication::Instance();
$application->Init();

header('Content-type: text/html; charset: utf-8');

if (!defined('DEBUG_MODE') || !DEBUG_MODE) exit;

$target_charset = 'utf8';
$target_collation = 'utf8_general_ci';

//$target_charset = 'latin1';
//$target_collation = 'latin1_swedish_ci';

ini_set('max_execution_time', 0);

// Convert language translation
$langs = $application->Conn->Query('SELECT * FROM '.TABLE_PREFIX.'Languages', 'LanguageId');
foreach ($langs as $lang_id => $a_lang) {
	switch ( strtoupper($a_lang['Charset']) ) {
		case 'WINDOWS-1251':
			$langs[$lang_id]['mysql_charset'] = 'cp1251';
			break;

		case 'WINDOWS-1257':
			$langs[$lang_id]['mysql_charset'] = 'cp1257';
			break;

		case 'UTF-8':
			// when column have latin1 collation, but it's data is encoded to utf8 (from web browser)
			$langs[$lang_id]['mysql_charset'] = 'utf8';
			break;

		default:
			$langs[$lang_id]['mysql_charset'] = 'latin1';
	}
}

kUtil::print_r($langs);

//die();

$query = 'ALTER DATABASE `'.SQL_DB.'` DEFAULT CHARACTER SET '.$target_charset.' COLLATE '.$target_collation;
$application->Conn->Query($query);

$query = 'SHOW TABLE STATUS';
$tables = $application->Conn->Query($query);

foreach ($tables as $table_status)
{
	$table = $table_status['Name'];
	if (!preg_match('/^'.preg_quote(TABLE_PREFIX, '/').'/', $table)) {
		continue;
	}

	if ($table_status['Collation'] != $target_collation) {
		$sql = 'ALTER TABLE '.$table.' COLLATE '.$application->Conn->qstr($target_collation);
		echo "$sql<br>\n";
		$application->Conn->Query($sql);
	}

	echo "scanning $table<br>";

	$columns = $application->Conn->Query('SHOW FULL COLUMNS FROM '.$table);
	foreach ($columns as $a_column) {
		if ($a_column['Collation'] && $a_column['Collation'] != 'NULL') {
			if ($a_column['Collation'] == $target_collation) {
				echo "skipping ".$a_column['Field'].'<br>';
				continue;
			}

			echo 'processing column '.$a_column['Field'].' ('.$a_column['Collation'].')<br>';

//			ALTER TABLE `inp_Addresses` ADD `utf_To` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `To` ;

			// adding temporary UTF column
			$new_def = $a_column['Type'].' CHARACTER SET '.$target_charset.' COLLATE '.$target_collation.' '.($a_column['Null'] == 'YES' ? 'NULL':'NOT NULL');
			$q =  'ALTER TABLE `'.$table.'` ADD `utf_'.$a_column['Field'].'` '.$new_def.' AFTER `'.$a_column['Field'].'`;';
			echo "$q<br>";
			$application->Conn->Query($q);

			// copying value to utf
			if ($table == TABLE_PREFIX.'LanguageLabels' && $a_column['Field'] == 'Translation') {
				foreach ($langs as $lang_id => $a_lang) {
					$cast = $a_lang['mysql_charset'];
					if (!$cast) $cast = 'latin1';
					$copy_def = 'CONVERT( CAST(BINARY('.$a_column['Field'].') AS CHAR CHARACTER SET '.$cast.') USING '.$target_charset.')';
					$q = 'UPDATE `'.$table.'` SET `utf_'.$a_column['Field'].'` = '.$copy_def.' WHERE LanguageId = '.$lang_id;
					echo "$q<br>";
					$application->Conn->Query($q);
				}
			}
			elseif (preg_match('/l([0-9]+)_.*/', $a_column['Field'], $matches)) {
				$cast = $langs[$matches[1]]['mysql_charset'];
				if (!$cast) $cast = 'latin1';
				$copy_def = 'CONVERT( CAST(BINARY('.$a_column['Field'].') AS CHAR CHARACTER SET '.$cast.') USING '.$target_charset.')';
				$q = 'UPDATE `'.$table.'` SET `utf_'.$a_column['Field'].'` = '.$copy_def;
				echo "$q<br>";
				$application->Conn->Query($q);
			}
			else {
				$copy_def = 'BINARY(`'.$a_column['Field'].'`);';
				$q = 'UPDATE `'.$table.'` SET `utf_'.$a_column['Field'].'` = '.$copy_def;
				echo "$q<br>";
				$application->Conn->Query($q);
			}

			// altering orignal field to utf
			$q =  'ALTER TABLE `'.$table.'` CHANGE `'.$a_column['Field'].'` `'.$a_column['Field'].'` '.$new_def.';';
			echo "$q<br>";
			$application->Conn->Query($q);

			// copying utf value back
			$q = 'UPDATE `'.$table.'` SET `'.$a_column['Field'].'` = `utf_'.$a_column['Field'].'`;';
			echo "$q<br>";
			$application->Conn->Query($q);

			// removing temporary UTF column
			$q = 'ALTER TABLE `'.$table.'` DROP `utf_'.$a_column['Field'].'`;';
			echo "$q<br>";
			$application->Conn->Query($q);

		}
	}
}

$application->Conn->Query('UPDATE '.TABLE_PREFIX.'Languages SET Charset = \''.$target_charset.'\'');
