<?php

function matchPrefix($tb_name) {
	
	$pref_to_check = array(
		'kms',
		'core',
		'learning',
		'cms',
		'conference'
	);
	foreach($pref_to_check as $pref) {
		
		if(substr($tb_name, 0, strlen($pref)) == $pref) return true;
	}
	return false;
}

function custom_is_utf8($str) {
	// From http://w3.org/International/questions/qa-forms-utf-8.html
	
	return preg_match('/^(?:
		 [\x09\x0A\x0D\x20-\x7E]            # ASCII
		| [\xC2-\xDF][\x80-\xBF]            # non-overlong 2-byte
		|  \xE0[\xA0-\xBF][\x80-\xBF]        # excluding overlongs
		| [\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}  # straight 3-byte
		|  \xED[\x80-\x9F][\x80-\xBF]        # excluding surrogates
		|  \xF0[\x90-\xBF][\x80-\xBF]{2}    # planes 1-3
		| [\xF1-\xF3][\x80-\xBF]{3}          # planes 4-15
		|  \xF4[\x80-\x8F][\x80-\xBF]{2}    # plane 16
	)*$/xs', $str);
}

function convertToUtf8Db($dbhost, $dbuname, $dbpass, $dbname) {
	$report = '';
	//header("Content-Type: text/html; charset=utf-8");
	
	require(dirname(__FILE__).'/lib.db.php');
	
	$db = new DbConnection($dbhost, $dbuname, $dbpass, $dbname);
	
	//-------------------------------------
	$sorg_tables = $db->getTableList();
	//$sorg_tables = array_slice($sorg_tables, 0, 40);
	$total = 0;
	while(list(, $tb_name) = each($sorg_tables)) {
		
		if(matchPrefix($tb_name)) {
			
			if($tb_name != 'core_lang_language' 
				&& $tb_name != 'core_lang_text'
				&& $tb_name != 'core_lang_text_translation' 
				//&& $tb_name != 'core_lang_translation'
				&& $tb_name != 'core_event_property'
				&& $tb_name != 'learning_trackingeneral'
				&& $tb_name != 'learning_tracksession'
				&& $tb_name != 'cms_banner_raw_stat'
				&& $tb_name != 'conference_chat_msg'
				&& $tb_name != 'core_event'
				&& $tb_name != 'core_feed_cache'
				
				&& strpos($tb_name, '_menu') === false
				&& strpos($tb_name, '_setting') === false) {
				
				
				$tb_field = $db->describe($tb_name);
				
				$update_number = 0;
				$field		= array();
				$field_pri 	= array();
				while(list(, $field_c) = each($tb_field)) {
					
					if(strpos($field_c[1], 'varchar') !== false || strpos($field_c[1], 'text') !== false) {
						
						$field[] = '`'.$field_c[0].'`';
					}
					if($field_c[3] == 'PRI') {
						$field_pri[] = '`'.$field_c[0].'`';
					}
				}
				if(!empty($field) && !empty($field_pri)) {
					
					$sel_query = "SELECT ".implode(',', $field_pri).', '.implode(', ', $field)." 
					FROM `".$tb_name."`";
					$re_sel = $db->query($sel_query);
					while($values = mysql_fetch_row($re_sel)) {
						
						$where = ' 1';
						$set = '';
						$i = 0;
						foreach($field_pri as $pri) {
							$where .= " AND ".$pri." = '".mysql_escape_string($values[$i])."'";
							$i++;
						}
						foreach($field as $f_name) {
							
							$values[$i] = html_entity_decode($values[$i]);
							if($values[$i] != '' && !custom_is_utf8(strip_tags($values[$i]))) {
								
								$set .= "".$f_name." = '".mysql_escape_string(utf8_encode($values[$i]))."', ";
							}
							$i++;
						}
						
						if($set != '') {
							
							$update_number++;
							
							$update = "
							UPDATE `".$tb_name."` 
							SET ".substr($set, 0, -2)." 
							WHERE ".$where." ";
							$db->query($update);
						}
					}
				}
				$report .= 'Table: '.$tb_name.' ('.$update_number.')<br />';
	
			}
		}
	}
	$db->closeConn();
	return $report;
}




?>
