<?php
/**
* @version	$Id: language_import_helper.php 15359 2012-05-27 20:04:02Z 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.
*/

/**
 * Language pack format version description
 *
 * v1
 * ==========
 * All language properties are separate nodes inside <LANGUAGE> node. There are
 * two more nodes PHRASES and EVENTS for phrase and email event translations.
 *
 * v2
 * ==========
 * All data, that will end up in Language table is now attributes of LANGUAGE node
 * and is name exactly as field name, that will be used to store that data.
 *
 * v4
 * ==========
 * Hint & Column translation added to each phrase translation
 *
 * v5
 * ==========
 * Use separate xml nodes for subject, headers, html & plain translations
 *
 * v6
 * ==========
 * Added e-mail design templates
 *
 */

	defined('FULL_PATH') or die('restricted access!');

	define('LANG_OVERWRITE_EXISTING', 1);
	define('LANG_SKIP_EXISTING', 2);

	class LanguageImportHelper extends kHelper {

		/**
		 * Current Language in import
		 *
		 * @var LanguagesItem
		 */
		var $lang_object = null;

		/**
		 * Current user's IP address
		 *
		 * @var string
		 */
		var $ip_address = '';

		/**
		 * Event type + name mapping to id (from system)
		 *
		 * @var Array
		 */
		var $events_hash = Array ();

		/**
		 * Language pack import mode
		 *
		 * @var int
		 */
		var $import_mode = LANG_SKIP_EXISTING;

		/**
		 * Language IDs, that were imported
		 *
		 * @var Array
		 */
		var $_languages = Array ();

		/**
		 * Temporary table names to perform import on
		 *
		 * @var Array
		 */
		var $_tables = Array ();

		/**
		 * Phrase types allowed for import/export operations
		 *
		 * @var Array
		 */
		var $phrase_types_allowed = Array ();

		/**
		 * Encoding, used for language pack exporting
		 *
		 * @var string
		 */
		var $_exportEncoding = 'base64';

		/**
		 * Exported data limits (all or only specified ones)
		 *
		 * @var Array
		 */
		var $_exportLimits = Array (
			'phrases' => false,
			'emailevents' => false,
			'country-state' => false,
		);

		/**
		 * Debug language pack import process
		 *
		 * @var bool
		 */
		var $_debugMode = false;

		/**
		 * Latest version of language pack format. Versions are not backwards compatible!
		 *
		 * @var int
		 */
		var $_latestVersion = 6;

		/**
		 * Prefix-based serial numbers, that should be changed after import is finished
		 *
		 * @var Array
		 */
		var $changedPrefixes = Array ();

		public function __construct()
		{
			parent::__construct();

			// "core/install/english.lang", phrase count: 3318, xml parse time on windows: 10s, insert time: 0.058s
			set_time_limit(0);
			ini_set('memory_limit', -1);

			$this->lang_object = $this->Application->recallObject('lang.import', null, Array ('skip_autoload' => true));

			if (!(defined('IS_INSTALL') && IS_INSTALL)) {
				// perform only, when not in installation mode
				$this->_updateEventsCache();
			}

			$this->ip_address = getenv('HTTP_X_FORWARDED_FOR') ? getenv('HTTP_X_FORWARDED_FOR') : getenv('REMOTE_ADDR');

//			$this->_debugMode = $this->Application->isDebugMode();
		}

		/**
		 * Performs import of given language pack (former Parse method)
		 *
		 * @param string $filename
		 * @param string $phrase_types
		 * @param Array $module_ids
		 * @param int $import_mode
		 * @return bool
		 */
		function performImport($filename, $phrase_types, $module_ids, $import_mode = LANG_SKIP_EXISTING)
		{
			// define the XML parsing routines/functions to call based on the handler path
			if (!file_exists($filename) || !$phrase_types /*|| !$module_ids*/) {
				return false;
			}

			if ($this->_debugMode) {
				$start_time = microtime(true);
				$this->Application->Debugger->appendHTML(__CLASS__ . '::' . __FUNCTION__ . '("' . $filename . '")');
			}

			if (defined('IS_INSTALL') && IS_INSTALL) {
				// new events could be added during module upgrade
				$this->_updateEventsCache();
			}

			$phrase_types = explode('|', substr($phrase_types, 1, -1) );
//			$module_ids = explode('|', substr($module_ids, 1, -1) );

			$this->phrase_types_allowed = array_flip($phrase_types);
			$this->import_mode = $import_mode;

			$this->_parseXML($filename);

			// copy data from temp tables to live
			foreach ($this->_languages as $language_id) {
				$this->_performUpgrade($language_id, 'phrases', 'PhraseKey', Array ('l%s_Translation', 'l%s_HintTranslation', 'l%s_ColumnTranslation', 'PhraseType'));
				$this->_performUpgrade($language_id, 'emailevents', 'EventId', Array ('l%s_Subject', 'Headers', 'l%s_HtmlBody', 'l%s_PlainTextBody'));
				$this->_performUpgrade($language_id, 'country-state', 'CountryStateId', Array ('l%s_Name'));
			}

			$this->_initImportTables(true);
			$this->changedPrefixes = array_unique($this->changedPrefixes);

			foreach ($this->changedPrefixes as $prefix) {
				$this->Application->incrementCacheSerial($prefix);
			}

			if ($this->_debugMode) {
				$this->Application->Debugger->appendHTML(__CLASS__ . '::' . __FUNCTION__ . '("' . $filename . '"): ' . (microtime(true) - $start_time));
			}

			return true;
		}

		/**
		 * Creates XML file with exported language data (former Create method)
		 *
		 * @param string $filename filename to export into
		 * @param Array $phrase_types phrases types to export from modules passed in $module_ids
		 * @param Array $language_ids IDs of languages to export
		 * @param Array $module_ids IDs of modules to export phrases from
		 */
		function performExport($filename, $phrase_types, $language_ids, $module_ids)
		{
			$fp = fopen($filename,'w');
			if (!$fp || !$phrase_types || !$module_ids || !$language_ids) {
				return false;
			}

			$phrase_types = explode('|', substr($phrase_types, 1, -1) );
			$module_ids = explode('|', substr($module_ids, 1, -1) );

			$ret = '<?xml version="1.0" encoding="utf-8"?>' . "\n";
			$ret .= '<LANGUAGES Version="' . $this->_latestVersion . '">' . "\n";

			$export_fields = $this->_getExportFields();

			// get languages
			$sql = 'SELECT *
					FROM ' . $this->Application->getUnitOption('lang','TableName') . '
					WHERE LanguageId IN (' . implode(',', $language_ids) . ')';
			$languages = $this->Conn->Query($sql, 'LanguageId');

			// get phrases
			$phrase_modules = $module_ids;
			array_push($phrase_modules, ''); // for old language packs without module

			$phrase_modules = $this->Conn->qstrArray($phrase_modules);

			// apply phrase selection limit
			if ($this->_exportLimits['phrases']) {
				$escaped_phrases = $this->Conn->qstrArray($this->_exportLimits['phrases']);
				$limit_where = 'Phrase IN (' . implode(',', $escaped_phrases) . ')';
			}
			else {
				$limit_where = 'TRUE';
			}

			$sql = 'SELECT *
					FROM ' . $this->Application->getUnitOption('phrases','TableName') . '
					WHERE PhraseType IN (' . implode(',', $phrase_types) . ') AND Module IN (' . implode(',', $phrase_modules) . ') AND ' . $limit_where . '
					ORDER BY Phrase';
			$phrases = $this->Conn->Query($sql, 'PhraseId');

			// email events
			$module_sql = preg_replace('/(.*),/U', 'INSTR(Module,\'\\1\') OR ', implode(',', $module_ids) . ',');

			// apply event selection limit
			if ($this->_exportLimits['emailevents']) {
				$escaped_email_events = $this->Conn->qstrArray($this->_exportLimits['emailevents']);
				$limit_where = '`Event` IN (' . implode(',', $escaped_email_events) . ')';
			}
			else {
				$limit_where = 'TRUE';
			}

			$sql = 'SELECT *
					FROM ' . $this->Application->getUnitOption('emailevents', 'TableName') . '
					WHERE `Type` IN (' . implode(',', $phrase_types) . ') AND (' . substr($module_sql, 0, -4) . ') AND ' . $limit_where . '
					ORDER BY `Event`, `Type`';
			$events = $this->Conn->Query($sql, 'EventId');

			if ( in_array('Core', $module_ids) ) {
				if ($this->_exportLimits['country-state']) {
					$escaped_countries = $this->Conn->qstrArray($this->_exportLimits['country-state']);
					$limit_where = '`IsoCode` IN (' . implode(',', $escaped_countries) . ')';
				}
				else {
					$limit_where = 'TRUE';
				}

				$country_table = $this->Application->getUnitOption('country-state', 'TableName');

				// countries
				$sql = 'SELECT *
						FROM ' . $country_table . '
						WHERE Type = ' . DESTINATION_TYPE_COUNTRY . ' AND ' . $limit_where . '
						ORDER BY `IsoCode`';
				$countries = $this->Conn->Query($sql, 'CountryStateId');

				// states
				$sql = 'SELECT state.*
						FROM ' . $country_table . ' state
						JOIN ' . $country_table . ' country ON country.CountryStateId = state.StateCountryId
						WHERE state.Type = ' . DESTINATION_TYPE_STATE . ' AND ' . str_replace('`IsoCode`', 'country.`IsoCode`', $limit_where) . '
						ORDER BY state.`IsoCode`';
				$states = $this->Conn->Query($sql, 'CountryStateId');

				foreach ($states as $state_id => $state_data) {
					$country_id = $state_data['StateCountryId'];

					if ( !array_key_exists('States', $countries[$country_id]) ) {
						$countries[$country_id]['States'] = Array ();
					}

					$countries[$country_id]['States'][] = $state_id;
				}
			}

			foreach ($languages as $language_id => $language_info) {
				// language
				$ret .= "\t" . '<LANGUAGE Encoding="' . $this->_exportEncoding . '"';

				foreach ($export_fields	as $export_field) {
					$ret .= ' ' . $export_field . '="' . htmlspecialchars($language_info[$export_field], NULL, 'UTF-8') . '"';
				}

				$ret .= '>' . "\n";

				// filename replacements
				$replacements = $language_info['FilenameReplacements'];

				if ( $replacements ) {
					$ret .= "\t\t" . '<REPLACEMENTS>' . $this->_exportConvert($replacements) . '</REPLACEMENTS>' . "\n";
				}

				// e-mail design templates
				if ( $language_info['HtmlEmailTemplate'] || $language_info['TextEmailTemplate'] ) {
					$ret .= "\t\t" . '<EMAILDESIGNS>' . "\n";

					if ( $language_info['HtmlEmailTemplate'] ) {
						$ret .= "\t\t\t" . '<HTML>' . $this->_exportConvert($language_info['HtmlEmailTemplate']) . '</HTML>' . "\n";
					}

					if ( $language_info['TextEmailTemplate'] ) {
						$ret .= "\t\t\t" . '<TEXT>' . $this->_exportConvert($language_info['TextEmailTemplate']) . '</TEXT>' . "\n";
					}

					$ret .= "\t\t" . '</EMAILDESIGNS>' . "\n";
				}

				// phrases
				if ($phrases) {
					$ret .= "\t\t" . '<PHRASES>' . "\n";
					foreach ($phrases as $phrase_id => $phrase) {
						$translation = $phrase['l' . $language_id . '_Translation'];
						$hint_translation = $phrase['l' . $language_id . '_HintTranslation'];
						$column_translation = $phrase['l' . $language_id . '_ColumnTranslation'];

						if (!$translation) {
							// phrase is not translated on given language
							continue;
						}

						if ( $this->_exportEncoding == 'base64' ) {
							$hint_translation = base64_encode($hint_translation);
							$column_translation = base64_encode($column_translation);
						}
						else {
							$hint_translation = htmlspecialchars($hint_translation, NULL, 'UTF-8');
							$column_translation = htmlspecialchars($column_translation, NULL, 'UTF-8');
						}

						$attributes = Array (
							'Label="' . $phrase['Phrase'] . '"',
							'Module="' . $phrase['Module'] . '"',
							'Type="' . $phrase['PhraseType'] . '"'
						);

						if ( $phrase['l' . $language_id . '_HintTranslation'] ) {
							$attributes[] = 'Hint="' . $hint_translation . '"';
						}

						if ( $phrase['l' . $language_id . '_ColumnTranslation'] ) {
							$attributes[] = 'Column="' . $column_translation . '"';
						}

						$ret .= "\t\t\t" . '<PHRASE ' . implode(' ', $attributes) . '>' . $this->_exportConvert($translation) . '</PHRASE>' . "\n";
					}

					$ret .= "\t\t" . '</PHRASES>' . "\n";
				}

				// email events
				if ($events) {
					$ret .= "\t\t" . '<EVENTS>' . "\n";

					foreach ($events as $event_data) {
						$fields_hash = Array (
							'HEADERS' => $event_data['Headers'],
							'SUBJECT' => $event_data['l' . $language_id . '_Subject'],
							'HTMLBODY' => $event_data['l' . $language_id . '_HtmlBody'],
							'PLAINTEXTBODY' => $event_data['l' . $language_id . '_PlainTextBody'],
						);

						$data = '';

						foreach ($fields_hash as $xml_node => $xml_content) {
							if ( $xml_content ) {
								$data .= "\t\t\t\t" . '<' . $xml_node . '>' . $this->_exportConvert($xml_content) . '</' . $xml_node . '>' . "\n";
							}
						}

						if ( $data ) {
							$ret .= "\t\t\t" . '<EVENT Event="' . $event_data['Event'] . '" Type="' . $event_data['Type'] . '">' . "\n" . $data . "\t\t\t" . '</EVENT>' . "\n";
						}
					}

					$ret .= "\t\t" . '</EVENTS>' . "\n";
				}

				if (in_array('Core', $module_ids) && $countries) {
					$ret .= "\t\t" . '<COUNTRIES>' . "\n";
					foreach ($countries as $country_data) {
						$translation = $country_data['l' . $language_id . '_Name'];

						if (!$translation) {
							// country is not translated on given language
							continue;
						}

						$data = $this->_exportEncoding == 'base64' ? base64_encode($translation) : $translation;

						if (array_key_exists('States', $country_data)) {
							$ret .= "\t\t\t" . '<COUNTRY Iso="' . $country_data['IsoCode'] . '" Translation="' . $data . '">' . "\n";

							foreach ($country_data['States'] as $state_id) {
								$translation = $states[$state_id]['l' . $language_id . '_Name'];

								if (!$translation) {
									// state is not translated on given language
									continue;
								}

								$data = $this->_exportEncoding == 'base64' ? base64_encode($translation) : $translation;
								$ret .= "\t\t\t\t" . '<STATE Iso="' . $states[$state_id]['IsoCode'] . '" Translation="' . $data . '"/>' . "\n";
							}

							$ret  .= "\t\t\t" . '</COUNTRY>' . "\n";
						}
						else {
							$ret .= "\t\t\t" . '<COUNTRY Iso="' . $country_data['IsoCode'] . '" Translation="' . $data . '"/>' . "\n";
						}
					}

					$ret .= "\t\t" . '</COUNTRIES>' . "\n";
				}

				$ret .= "\t" . '</LANGUAGE>' . "\n";
			}

			$ret .= '</LANGUAGES>';
			fwrite($fp, $ret);
			fclose($fp);

			return true;
		}

		/**
		 * Converts string before placing into export file
		 *
		 * @param string $string
		 * @return string
		 * @access protected
		 */
		protected function _exportConvert($string)
		{
			return $this->_exportEncoding == 'base64' ? base64_encode($string) : '<![CDATA[' . $string . ']]>';
		}

		/**
		 * Sets language pack encoding (not charset) used during export
		 *
		 * @param string $encoding
		 */
		function setExportEncoding($encoding)
		{
			$this->_exportEncoding = $encoding;
		}

		/**
		 * Sets language pack data limit for export
		 *
		 * @param string $prefix
		 * @param string $data
		 */
		function setExportLimit($prefix, $data = null)
		{
			if ( !isset($data) ) {
				$key_field = $prefix == 'phrases' ? 'Phrase' : 'Event';
				$ids = $this->getExportIDs($prefix);

				$sql = 'SELECT ' . $key_field . '
						FROM ' . $this->Application->getUnitOption($prefix, 'TableName') . '
						WHERE ' . $this->Application->getUnitOption($prefix, 'IDField') . ' IN (' . $ids . ')';
				$rows = $this->Conn->GetIterator($sql);

				if ( count($rows) ) {
					$data = '';

					foreach ($rows as $row) {
						$data .= ',' . $row[$key_field];
					}

					$data = substr($data, 1);
				}
			}

			if ( !is_array($data) ) {
				$data = str_replace(',', "\n", $data);
				$data = preg_replace("/\n+/", "\n", str_replace("\r", '', trim($data)));
				$data = $data ? array_map('trim', explode("\n", $data)) : Array ();
			}

			$this->_exportLimits[$prefix] = $data;
		}

		/**
		 * Performs upgrade of given language pack part
		 *
		 * @param int $language_id
		 * @param string $prefix
		 * @param string $unique_field
		 * @param Array $data_fields
		 */
		function _performUpgrade($language_id, $prefix, $unique_field, $data_fields)
		{
			$live_records = $this->_getTableData($language_id, $prefix, $unique_field, $data_fields[0], false);
			$temp_records = $this->_getTableData($language_id, $prefix, $unique_field, $data_fields[0], true);

			if (!$temp_records) {
				// no data for given language
				return ;
			}

			// perform insert for records, that are missing in live table
			$to_insert = array_diff($temp_records, $live_records);

			if ($to_insert) {
				$to_insert = $this->Conn->qstrArray($to_insert);

				$sql = 'INSERT INTO ' . $this->Application->getUnitOption($prefix, 'TableName') . '
						SELECT *
						FROM ' . $this->_tables[$prefix] . '
						WHERE ' . $unique_field . ' IN (' . implode(',', $to_insert) . ')';
				$this->Conn->Query($sql);

				// new records were added
				$this->changedPrefixes[] = $prefix;
			}

			// perform update for records, that are present in live table
			$to_update = array_diff($temp_records, $to_insert);

			if ($to_update) {
				$to_update = $this->Conn->qstrArray($to_update);

				$sql = 'UPDATE ' . $this->Application->getUnitOption($prefix, 'TableName') . ' live
						SET ';

				foreach ($data_fields as $index => $data_field) {
					$data_field = sprintf($data_field, $language_id);

					$sql .= '	live.' . $data_field . ' = (
									SELECT temp' . $index . '.' . $data_field . '
									FROM ' . $this->_tables[$prefix] . ' temp' . $index . '
									WHERE temp' . $index . '.' . $unique_field . ' = live.' . $unique_field . '
								),';
				}

				$sql = substr($sql, 0, -1); // cut last comma

				$where_clause = Array (
					// this won't make any difference, but just in case
					$unique_field . ' IN (' . implode(',', $to_update) . ')',
				);

				if ($this->import_mode == LANG_SKIP_EXISTING) {
					// empty OR not set
					$data_field = sprintf($data_fields[0], $language_id);
					$where_clause[] = '(' . $data_field . ' = "") OR (' . $data_field . ' IS NULL)';
				}

				if ($where_clause) {
					$sql .= "\n" . 'WHERE (' . implode(') AND (', $where_clause) . ')';
				}

				$this->Conn->Query($sql);

				if ($this->Conn->getAffectedRows() > 0) {
					// existing records were updated
					$this->changedPrefixes[] = $prefix;
				}
			}
		}

		/**
		 * Returns data from given table used for language pack upgrade
		 *
		 * @param int $language_id
		 * @param string $prefix
		 * @param string $unique_field
		 * @param string $data_field
		 * @param bool $temp_mode
		 * @return Array
		 */
		function _getTableData($language_id, $prefix, $unique_field, $data_field, $temp_mode = false)
		{
			$data_field = sprintf($data_field, $language_id);
			$table_name = $this->Application->getUnitOption($prefix, 'TableName');

			if ($temp_mode) {
				// for temp table get only records, that have contents on given language (not empty and isset)
				$sql = 'SELECT ' . $unique_field . '
						FROM ' . $this->Application->GetTempName($table_name, 'prefix:' . $prefix) . '
						WHERE (' . $data_field . ' <> "") AND (' . $data_field . ' IS NOT NULL)';
			}
			else {
				// for live table get all records, no matter on what language
				$sql = 'SELECT ' . $unique_field . '
						FROM ' . $table_name;
			}

			return $this->Conn->GetCol($sql);
		}

		function _parseXML($filename)
		{
			if ( $this->_debugMode ) {
				$start_time = microtime(true);
				$this->Application->Debugger->appendHTML(__CLASS__ . '::' . __FUNCTION__ . '("' . $filename . '")');
			}

			$languages = simplexml_load_file($filename);

			if ( $languages === false) {
				// invalid language pack contents
				return false;
			}

			// PHP 5.3 version would be: $languages->count()
			if ( count($languages->children()) ) {
				$this->_processLanguages($languages);
				$this->_processLanguageData($languages);
			}

			if ( $this->_debugMode ) {
				$this->Application->Debugger->appendHTML(__CLASS__ . '::' . __FUNCTION__ . '("' . $filename . '"): ' . (microtime(true) - $start_time));
			}

			return true;
		}

		/**
		 * Creates temporary tables, used during language import
		 *
		 * @param bool $drop_only
		 */
		function _initImportTables($drop_only = false)
		{
			$this->_tables['phrases'] = $this->_prepareTempTable('phrases', $drop_only);
			$this->_tables['emailevents'] = $this->_prepareTempTable('emailevents', $drop_only);
			$this->_tables['country-state'] = $this->_prepareTempTable('country-state', $drop_only);
		}

		/**
		 * Create temp table for prefix, if table already exists, then delete it and create again
		 *
		 * @param string $prefix
		 * @param bool $drop_only
		 * @return string Name of created temp table
		 * @access protected
		 */
		protected function _prepareTempTable($prefix, $drop_only = false)
		{
			$id_field = $this->Application->getUnitOption($prefix, 'IDField');
			$table = $this->Application->getUnitOption($prefix,'TableName');
			$temp_table = $this->Application->GetTempName($table);

			$sql = 'DROP TABLE IF EXISTS %s';
			$this->Conn->Query( sprintf($sql, $temp_table) );

			if (!$drop_only) {
				$sql = 'CREATE TABLE ' . $temp_table . ' SELECT * FROM ' . $table . ' WHERE 0';
				$this->Conn->Query($sql);

				$sql = 'ALTER TABLE %1$s CHANGE %2$s %2$s INT(11) NOT NULL DEFAULT "0"';
				$this->Conn->Query( sprintf($sql, $temp_table, $id_field) );

				switch ($prefix) {
					case 'phrases':
						$unique_field = 'PhraseKey';
						break;

					case 'emailevents':
						$unique_field = 'EventId';
						break;

					case 'country-state':
						$unique_field = 'CountryStateId';
						break;

					default:
						throw new Exception('Unknown prefix "<strong>' . $prefix . '</strong>" during language pack import');
						break;
				}

				$sql = 'ALTER TABLE ' . $temp_table . ' ADD UNIQUE (' . $unique_field . ')';
				$this->Conn->Query($sql);
			}

			return $temp_table;
		}

		/**
		 * Prepares mapping between event name+type and their ids in database
		 *
		 */
		function _updateEventsCache()
		{
			$sql = 'SELECT EventId, CONCAT(Event,"_",Type) AS EventMix
					FROM ' . TABLE_PREFIX . 'EmailEvents';
			$this->events_hash = $this->Conn->GetCol($sql, 'EventMix');
		}

		/**
		 * Returns language fields to be exported
		 *
		 * @return Array
		 */
		function _getExportFields()
		{
			return Array (
				'PackName', 'LocalName', 'DateFormat', 'TimeFormat', 'InputDateFormat', 'InputTimeFormat',
				'DecimalPoint', 'ThousandSep', 'Charset', 'UnitSystem', 'Locale', 'UserDocsUrl'
			);
		}

		/**
		 * Processes parsed XML
		 *
		 * @param SimpleXMLElement $languages
		 */
		function _processLanguages($languages)
		{
			$version = (int)$languages['Version'];

			if ( !$version ) {
				// version missing -> guess it
				if ( $languages->DATEFORMAT->getName() ) {
					$version = 1;
				}
				elseif ( (string)$languages->LANGUAGE['Charset'] != '' ) {
					$version = 2;
				}
			}

			if ( $version == 1 ) {
				$field_mapping = Array (
					'DATEFORMAT' => 'DateFormat',
					'TIMEFORMAT' => 'TimeFormat',
					'INPUTDATEFORMAT' => 'InputDateFormat',
					'INPUTTIMEFORMAT' => 'InputTimeFormat',
					'DECIMAL' => 'DecimalPoint',
					'THOUSANDS' => 'ThousandSep',
					'CHARSET' => 'Charset',
					'UNITSYSTEM' => 'UnitSystem',
					'DOCS_URL' => 'UserDocsUrl',
				);
			}
			else {
				$export_fields = $this->_getExportFields();
			}

			foreach ($languages as $language_node) {
				$fields_hash = Array (
					'PackName' => (string)$language_node['PackName'],
					'LocalName' => (string)$language_node['PackName'],
					'Encoding' => (string)$language_node['Encoding'],
					'Charset' => 'utf-8',
					'SynchronizationModes' => Language::SYNCHRONIZE_DEFAULT,
				);

				if ( $version > 1 ) {
					foreach ($export_fields as $export_field) {
						if ( (string)$language_node[$export_field] ) {
							$fields_hash[$export_field] = (string)$language_node[$export_field];
						}
					}
				}

				$container_nodes = Array ('PHRASES', 'EVENTS', 'COUNTRIES');

				foreach ($language_node as $sub_node) {
					/* @var $sub_node SimpleXMLElement */

					if ( in_array($sub_node->getName(), $container_nodes) ) {
							continue;
					}

					switch ($sub_node->getName()) {
						case 'REPLACEMENTS':
							// added since v2
							$replacements = (string)$sub_node;

							if ( $fields_hash['Encoding'] != 'plain' ) {
								$replacements = base64_decode($replacements);
							}

							$fields_hash['FilenameReplacements'] = $replacements;
							break;

						case 'EMAILDESIGNS':
							// added since v6
							$this->_decodeEmailDesignTemplate($fields_hash, 'HtmlEmailTemplate', (string)$sub_node->HTML);
							$this->_decodeEmailDesignTemplate($fields_hash, 'TextEmailTemplate', (string)$sub_node->TEXT);
							break;

						default:
							if ( $version == 1 ) {
								$fields_hash[$field_mapping[$sub_node->Name]] = (string)$sub_node;
							}
							break;
					}
				}

				$this->_processLanguage($fields_hash);
			}

			if ( !defined('IS_INSTALL') || !IS_INSTALL ) {
				$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');
				/* @var $ml_helper kMultiLanguageHelper */

				// create ML columns for new languages
				$ml_helper->resetState();
				$ml_helper->massCreateFields();
			}

			// create temp tables after new language columns were added
			$this->_initImportTables();
		}

		/**
		 * Processes parsed XML
		 *
		 * @param SimpleXMLElement $languages
		 */
		function _processLanguageData($languages)
		{
			foreach ($languages as $language_node) {
				$encoding = (string)$language_node['Encoding'];
				$language_id = $this->_languages[kUtil::crc32((string)$language_node['PackName'])];

				$container_nodes = Array ('PHRASES', 'EVENTS', 'COUNTRIES');

				foreach ($language_node as $sub_node) {
					/* @var $sub_node SimpleXMLElement */

					if ( !in_array($sub_node->getName(), $container_nodes) || !count($sub_node->children()) ) {
						// PHP 5.3 version would be: !$sub_node->count()
						continue;
					}

					switch ($sub_node->getName()) {
						case 'PHRASES':
							$this->_processPhrases($sub_node, $language_id, $encoding);
							break;

						case 'EVENTS':
							$this->_processEvents($sub_node, $language_id, $encoding);
							break;

						case 'COUNTRIES':
							$this->_processCountries($sub_node, $language_id, $encoding);
							break;
					}
				}
			}
		}

		/**
		 * Decodes e-mail template design from language pack
		 *
		 * @param Array $fields_hash
		 * @param string $field
		 * @param string $design_template
		 */
		protected function _decodeEmailDesignTemplate(&$fields_hash, $field, $design_template)
		{
			if ( $fields_hash['Encoding'] != 'plain' ) {
				$design_template = base64_decode($design_template);
			}

			if ( $design_template ) {
				$fields_hash[$field] = $design_template;
			}
		}

		/**
		 * Performs phases import
		 *
		 * @param SimpleXMLElement $phrases
		 * @param int $language_id
		 * @param string $language_encoding
		 */
		function _processPhrases($phrases, $language_id, $language_encoding)
		{
			static $other_translations = Array ();

			if ( $this->Application->isDebugMode() ) {
				$this->Application->Debugger->profileStart('L[' . $language_id . ']P', 'Language: ' . $language_id . '; Phrases Import');
			}

			foreach ($phrases as $phrase_node) {
				/* @var $phrase_node SimpleXMLElement */

				$phrase_key = mb_strtoupper($phrase_node['Label']);

				$fields_hash = Array (
					'Phrase' => (string)$phrase_node['Label'],
					'PhraseKey' => $phrase_key,
					'PhraseType' => (int)$phrase_node['Type'],
					'Module' => (string)$phrase_node['Module'] ? (string)$phrase_node['Module'] : 'Core',
					'LastChanged' => TIMENOW,
					'LastChangeIP' => $this->ip_address,
				);

				$translation = (string)$phrase_node;
				$hint_translation = (string)$phrase_node['Hint'];
				$column_translation = (string)$phrase_node['Column'];

				if ( array_key_exists($fields_hash['PhraseType'], $this->phrase_types_allowed) ) {
					if ( $language_encoding != 'plain' ) {
						$translation = base64_decode($translation);
						$hint_translation = base64_decode($hint_translation);
						$column_translation = base64_decode($column_translation);
					}

					if ( !array_key_exists($phrase_key, $other_translations) ) {
						// ensure translation in every language to make same column count in every insert
						$other_translations[$phrase_key] = Array ();

						foreach ($this->_languages as $other_language_id) {
							$other_translations[$phrase_key]['l' . $other_language_id . '_Translation'] = '';
							$other_translations[$phrase_key]['l' . $other_language_id . '_HintTranslation'] = '';
							$other_translations[$phrase_key]['l' . $other_language_id . '_ColumnTranslation'] = '';
						}
					}

					$other_translations[$phrase_key]['l' . $language_id . '_Translation'] = $translation;
					$other_translations[$phrase_key]['l' . $language_id . '_HintTranslation'] = $hint_translation;
					$other_translations[$phrase_key]['l' . $language_id . '_ColumnTranslation'] = $column_translation;

					$fields_hash = array_merge($fields_hash, $other_translations[$phrase_key]);
					$this->Conn->doInsert($fields_hash, $this->_tables['phrases'], 'REPLACE', false);
				}
			}

			if ( $this->Application->isDebugMode() ) {
				$this->Application->Debugger->profileFinish('L[' . $language_id . ']P', 'Language: ' . $language_id . '; Phrases Import');
			}

			$this->Conn->doInsert($fields_hash, $this->_tables['phrases'], 'REPLACE');
		}

		/**
		 * Performs email event import
		 *
		 * @param SimpleXMLElement $events
		 * @param int $language_id
		 * @param string $language_encoding
		 */
		function _processEvents($events, $language_id, $language_encoding)
		{
			static $other_translations = Array ();

			if ( $this->Application->isDebugMode() ) {
				$this->Application->Debugger->profileStart('L[' . $language_id . ']E', 'Language: ' . $language_id . '; Events Import');
			}

			$email_message_helper = $this->Application->recallObject('kEmailMessageHelper');
			/* @var $email_message_helper kEmailMessageHelper */

			foreach ($events as $event_node) {
				/* @var $event_node SimpleXMLElement */

				$message_type = (string)$event_node['MessageType'];
				$event_id = $this->_getEventId((string)$event_node['Event'], (int)$event_node['Type']);

				if ( !$event_id ) {
					continue;
				}

				$fields_hash = Array (
					'EventId' => $event_id,
					'Event' => (string)$event_node['Event'],
					'Type' => (int)$event_node['Type'],
				);

				if ( $message_type == '' ) {
					$parsed = $email_message_helper->parseTemplate($event_node, '');
					$parsed = array_map($language_encoding == 'plain' ? 'rtrim' : 'base64_decode', $parsed);

				}
				else {
					$template = $language_encoding == 'plain' ? rtrim($event_node) : base64_decode($event_node);
					$parsed = $email_message_helper->parseTemplate($template, $message_type);
				}

				if ( !array_key_exists($event_id, $other_translations) ) {
					// ensure translation in every language to make same column count in every insert
					$other_translations[$event_id] = Array ();

					foreach ($this->_languages as $other_language_id) {
						$other_translations[$event_id]['l' . $other_language_id . '_Subject'] = '';
						$other_translations[$event_id]['l' . $other_language_id . '_HtmlBody'] = '';
						$other_translations[$event_id]['l' . $other_language_id . '_PlainTextBody'] = '';
					}
				}

				$other_translations[$event_id]['l' . $language_id . '_Subject'] = $parsed['Subject'];
				$other_translations[$event_id]['l' . $language_id . '_HtmlBody'] = $parsed['HtmlBody'];
				$other_translations[$event_id]['l' . $language_id . '_PlainTextBody'] = $parsed['PlainTextBody'];

				if ( $parsed['Headers'] ) {
					$other_translations[$event_id]['Headers'] = $parsed['Headers'];
				}
				elseif ( !$parsed['Headers'] && !array_key_exists('Headers', $other_translations[$event_id]) ) {
					$other_translations[$event_id]['Headers'] = $parsed['Headers'];
				}

				$fields_hash = array_merge($fields_hash, $other_translations[$event_id]);
				$this->Conn->doInsert($fields_hash, $this->_tables['emailevents'], 'REPLACE', false);
			}

			if ( $this->Application->isDebugMode() ) {
				$this->Application->Debugger->profileFinish('L[' . $language_id . ']E', 'Language: ' . $language_id . '; Events Import');
			}

			if ( isset($fields_hash) ) {
				// at least one email event in language pack was found in database
				$this->Conn->doInsert($fields_hash, $this->_tables['emailevents'], 'REPLACE');
			}
		}

		/**
		 * Performs country_state translation import
		 *
		 * @param SimpleXMLElement $country_states
		 * @param int $language_id
		 * @param string $language_encoding
		 * @param bool $process_states
		 * @return void
		 */
		function _processCountries($country_states, $language_id, $language_encoding, $process_states = false)
		{
			static $other_translations = Array ();

			foreach ($country_states as $country_state_node) {
				/* @var $country_state_node SimpleXMLElement */

				if ( $process_states ) {
					$country_state_id = $this->_getStateId((string)$country_states['Iso'], (string)$country_state_node['Iso']);
				}
				else {
					$country_state_id = $this->_getCountryId((string)$country_state_node['Iso']);
				}

				if ( !$country_state_id ) {
					continue;
				}

				if ( $language_encoding == 'plain' ) {
					$translation = rtrim($country_state_node['Translation']);
				}
				else {
					$translation = base64_decode($country_state_node['Translation']);
				}

				$fields_hash = Array ('CountryStateId' => $country_state_id);


				if ( !array_key_exists($country_state_id, $other_translations) ) {
					// ensure translation in every language to make same column count in every insert
					$other_translations[$country_state_id] = Array ();

					foreach ($this->_languages as $other_language_id) {
						$other_translations[$country_state_id]['l' . $other_language_id . '_Name'] = '';
					}
				}

				$other_translations[$country_state_id]['l' . $language_id . '_Name'] = $translation;

				$fields_hash = array_merge($fields_hash, $other_translations[$country_state_id]);
				$this->Conn->doInsert($fields_hash, $this->_tables['country-state'], 'REPLACE', false);

				// PHP 5.3 version would be: $country_state_node->count()
				if ( !$process_states && count($country_state_node->children()) ) {
					$this->_processCountries($country_state_node, $language_id, $language_encoding, true);
				}
			}

			$this->Conn->doInsert($fields_hash, $this->_tables['country-state'], 'REPLACE');
		}

		/**
		 * Creates/updates language based on given fields and returns it's id
		 *
		 * @param Array $fields_hash
		 * @return int
		 */
		function _processLanguage($fields_hash)
		{
			// 1. get language from database
			$sql = 'SELECT ' . $this->lang_object->IDField . '
					FROM ' . $this->lang_object->TableName . '
					WHERE PackName = ' . $this->Conn->qstr($fields_hash['PackName']);
			$language_id = $this->Conn->GetOne($sql);

			if ($language_id) {
				// 2. language found -> update, when allowed
				$this->lang_object->Load($language_id);

				if ($this->import_mode == LANG_OVERWRITE_EXISTING) {
					// update live language record based on data from xml
					$this->lang_object->SetFieldsFromHash($fields_hash);
					$this->lang_object->Update();
				}
			}
			else {
				// 3. language not found -> create
				$this->lang_object->SetFieldsFromHash($fields_hash);
				$this->lang_object->SetDBField('Enabled', STATUS_ACTIVE);

				if ($this->lang_object->Create()) {
					$language_id = $this->lang_object->GetID();

					if (defined('IS_INSTALL') && IS_INSTALL) {
						// language created during install becomes admin interface language
						$this->lang_object->setPrimary(true, true);
					}
				}
			}

			// 4. collect ID of every processed language
			if (!in_array($language_id, $this->_languages)) {
				$this->_languages[kUtil::crc32($fields_hash['PackName'])] = $language_id;
			}

			return $language_id;
		}

		/**
		 * Returns event id based on it's name and type
		 *
		 * @param string $event_name
		 * @param string $event_type
		 * @return int
		 */
		function _getEventId($event_name, $event_type)
		{
			$cache_key = $event_name . '_' . $event_type;

			return array_key_exists($cache_key, $this->events_hash) ? $this->events_hash[$cache_key] : 0;
		}

		/**
		 * Returns country id based on it's 3letter ISO code
		 *
		 * @param string $iso
		 * @return int
		 */
		function _getCountryId($iso)
		{
			static $cache = null;

			if (!isset($cache)) {
				$sql = 'SELECT CountryStateId, IsoCode
						FROM ' . TABLE_PREFIX . 'CountryStates
						WHERE Type = ' . DESTINATION_TYPE_COUNTRY;
				$cache = $this->Conn->GetCol($sql, 'IsoCode');
			}

			return array_key_exists($iso, $cache) ? $cache[$iso] : false;
		}

		/**
		 * Returns state id based on 3letter country ISO code and 2letter state ISO code
		 *
		 * @param string $country_iso
		 * @param string $state_iso
		 * @return int
		 */
		function _getStateId($country_iso, $state_iso)
		{
			static $cache = null;

			if (!isset($cache)) {
				$sql = 'SELECT CountryStateId, CONCAT(StateCountryId, "-", IsoCode) AS IsoCode
						FROM ' . TABLE_PREFIX . 'CountryStates
						WHERE Type = ' . DESTINATION_TYPE_STATE;
				$cache = $this->Conn->GetCol($sql, 'IsoCode');
			}

			$country_id = $this->_getCountryId($country_iso);

			return array_key_exists($country_id . '-' . $state_iso, $cache) ? $cache[$country_id . '-' . $state_iso] : false;
		}

		/**
		 * Returns comma-separated list of IDs, that will be exported
		 *
		 * @param string $prefix
		 * @return string
		 * @access public
		 */
		public function getExportIDs($prefix)
		{
			$ids = $this->Application->RecallVar($prefix . '_selected_ids');

			if ( $ids ) {
				// some records were selected in grid
				return $ids;
			}

			$tag_params = Array (
				'grid' => $prefix == 'phrases' ? 'Phrases' : 'Emails',
				'skip_counting' => 1,
				'per_page' => -1
			);

			$list = $this->Application->recallObject($prefix, $prefix . '_List', $tag_params);
			/* @var $list kDBList */

			$sql = $list->getCountSQL($list->GetSelectSQL());
			$sql = str_replace('COUNT(*) AS count', $list->TableName . '.' . $list->IDField, $sql);

			$ids = '';
			$rows = $this->Conn->GetIterator($sql);

			if ( count($rows) ) {
				foreach ($rows as $row) {
					$ids .= ',' . $row[$list->IDField];
				}

				$ids = substr($ids, 1);
			}

			return $ids;
		}
	}