<?php
/**
* @version	$Id: upgrades.php 16797 2024-08-16 08:27:01Z 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.
*/

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

	$upgrade_class = 'CoreUpgrades';

	/**
	 * Class, that holds all upgrade scripts for "Core" module
	 *
	 */
	class CoreUpgrades extends kUpgradeHelper {

		/**
		 * Tables, that were renamed during 5.2.0 version upgrade
		 *
		 * @var Array
		 * @access private
		 */
		private $renamedTables = Array (
			'ban-rule' => Array ('from' => 'BanRules', 'to' => 'UserBanRules'),
			'conf' => Array ('from' => 'ConfigurationValues', 'to' => 'SystemSettings'),
			'c' => Array ('from' => 'Category', 'to' => 'Categories'),
			'cf' => Array ('from' => 'CustomField', 'to' => 'CustomFields'),
			'draft' => Array ('from' => 'Drafts', 'to' => 'FormSubmissionReplyDrafts'),
			'email-template' => Array ('from' => 'Events', 'to' => 'EmailEvents'),
			'fav' => Array ('from' => 'Favorites', 'to' => 'UserFavorites'),
			'img' => Array ('from' => 'Images', 'to' => 'CatalogImages'),
			'#file' => Array ('from' => 'ItemFiles', 'to' => 'CatalogFiles'),
			'rev' => Array ('from' => 'ItemReview', 'to' => 'CatalogReviews'),
			'lang' => Array ('from' => 'Language', 'to' => 'Languages'),
			'permission-type' => Array ('from' => 'PermissionConfig', 'to' => 'CategoryPermissionsConfig'),
			'phrases' => Array ('from' => 'Phrase', 'to' => 'LanguageLabels'),
			'g' => Array ('from' => 'PortalGroup', 'to' => 'UserGroups'),
			'user-profile' => Array ('from' => 'PersistantSessionData', 'to' => 'UserPersistentSessionData'),
			'u' => Array ('from' => 'PortalUser', 'to' => 'Users'),
			'u-cdata' => Array ('from' => 'PortalUserCustomData', 'to' => 'UserCustomData'),
			'search' => Array ('from' => 'RelatedSearches', 'to' => 'CategoryRelatedSearches'),
			'rel' => Array ('from' => 'Relationship', 'to' => 'CatalogRelationships'),
			'search-log' => Array ('from' => 'SearchLog', 'to' => 'SearchLogs'),
			'skin' => Array ('from' => 'Skins', 'to' => 'AdminSkins'),
			'submission-log' => Array ('from' => 'SubmissionLog', 'to' => 'FormSubmissionReplies'),
			'theme' => Array ('from' => 'Theme', 'to' => 'Themes'),
			'ug' => Array ('from' => 'UserGroup', 'to' => 'UserGroupRelations'),
			'visits' => Array ('from' => 'Visits', 'to' => 'UserVisits'),
			'session-log' => Array ('from' => 'SessionLogs', 'to' => 'UserSessionLogs'),
		);

		/**
		 * Changes table structure, where multilingual fields of TEXT type are present
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_4_1_0($mode)
		{
			if ($mode == 'before') {
				// don't user after, because In-Portal calls this method too
				$this->_toolkit->SaveConfig();
			}

			if ($mode == 'after') {
				/** @var kMultiLanguageHelper $ml_helper */
				$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

				$this->Application->UnitConfigReader->iterateConfigs(Array (&$this, 'updateTextFields'), $ml_helper->getLanguages());
			}
		}

		/**
		 * Moves ReplacementTags functionality from EmailMessage to Events table
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_4_1_1($mode)
		{
			if ($mode == 'after') {
				$sql = 'SELECT ReplacementTags, EventId
						FROM '.TABLE_PREFIX.'EmailMessage
						WHERE (ReplacementTags IS NOT NULL) AND (ReplacementTags <> "") AND (LanguageId = 1)';
				$replacement_tags = $this->Conn->GetCol($sql, 'EventId');

				foreach ($replacement_tags as $event_id => $replacement_tag) {
					$sql = 'UPDATE '.TABLE_PREFIX.'Events
							SET ReplacementTags = '.$this->Conn->qstr($replacement_tag).'
							WHERE EventId = '.$event_id;
					$this->Conn->Query($sql);
				}

				// drop moved field from source table
				$sql = 'ALTER TABLE '.TABLE_PREFIX.'EmailMessage
						DROP `ReplacementTags`';
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Callback function, that makes all ml fields of text type null with same default value
		 *
		 * @param string $prefix
		 * @param Array $config_data
		 * @param Array $languages
		 * @return bool
		 */
		function updateTextFields($prefix, &$config_data, $languages)
		{
			if (!isset($config_data['TableName']) || !isset($config_data['Fields'])) {
				// invalid config found or prefix not found
				return false;
			}

			$table_name = $config_data['TableName'];
			$table_structure = $this->Conn->Query('DESCRIBE '.$table_name, 'Field');
			if (!$table_structure) {
				// table not found
				return false;
			}

			$sqls = Array ();
			foreach ($config_data['Fields'] as $field => $options) {
				if (isset($options['formatter']) && $options['formatter'] == 'kMultiLanguage' && !isset($options['master_field'])) {
					// update all l<lang_id>_<field_name> fields (new format)

					foreach ($languages as $language_id) {
						$ml_field = 'l'.$language_id.'_'.$field;

						if ($table_structure[$ml_field]['Type'] == 'text') {
							$sqls[] = 'CHANGE '.$ml_field.' '.$ml_field.' TEXT NULL DEFAULT NULL';
						}
					}

					// update <field_name> if found (old format)
					if (isset($table_structure[$field]) && $table_structure[$field]['Type'] == 'text') {
						$sqls[] = 'CHANGE '.$field.' '.$field.' TEXT NULL DEFAULT NULL';
					}
				}
			}

			if ($sqls) {
				$sql = 'ALTER TABLE '.$table_name.' '.implode(', ', $sqls);
				$this->Conn->Query($sql);
			}

			return true;
		}

		/**
		 * Replaces In-Portal tags in Forgot Password related email events to K4 ones
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_4_2_0($mode)
		{
			if ($mode == 'after') {
				// 1. get event ids based on their name and type combination
				$event_names = Array (
					'USER.PSWD_' . EmailTemplate::TEMPLATE_TYPE_ADMIN,
					'USER.PSWD_' . EmailTemplate::TEMPLATE_TYPE_FRONTEND,
					'USER.PSWDC_' . EmailTemplate::TEMPLATE_TYPE_FRONTEND,
				);

				$event_sql = Array ();
				foreach ($event_names as $mixed_event) {
					list ($event_name, $event_type) = explode('_', $mixed_event, 2);
					$event_sql[] = 'Event = "'.$event_name.'" AND Type = '.$event_type;
				}

				$sql = 'SELECT EventId
						FROM '.TABLE_PREFIX.'Events
						WHERE ('.implode(') OR (', $event_sql).')';
				$event_ids = implode(',', $this->Conn->GetCol($sql));

				// 2. replace In-Portal tags to K4 tags
				$replacements = Array (
					'<inp:touser _Field="Password" />' => '<inp2:u_ForgottenPassword />',
					'<inp:m_confirm_password_link />' => '<inp2:u_ConfirmPasswordLink no_amp="1"/>',
				);

				foreach ($replacements as $old_tag => $new_tag) {
					$sql = 'UPDATE '.TABLE_PREFIX.'EmailMessage
							SET Template = REPLACE(Template, '.$this->Conn->qstr($old_tag).', '.$this->Conn->qstr($new_tag).')
							WHERE EventId IN ('.$event_ids.')';
					$this->Conn->Query($sql);
				}

			}
		}

		/**
		 * Makes admin primary language same as front-end - not needed, done in SQL
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_4_2_1($mode)
		{

		}

		function Upgrade_4_2_2($mode)
		{
			if ( $mode == 'before' ) {
				$sql = 'SELECT LanguageId
						FROM ' . TABLE_PREFIX . 'Language
						WHERE PrimaryLang = 1';

				if ( $this->Conn->GetOne($sql) ) {
					return;
				}

				$this->Conn->Query('UPDATE ' . TABLE_PREFIX . 'Language SET PrimaryLang = 1 ORDER BY LanguageId LIMIT 1');
			}
		}

		/**
		 * Adds index to "dob" field in "PortalUser" table when it's missing
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_4_3_1($mode)
		{
			if ($mode == 'after') {
				$sql = 'DESCRIBE ' . TABLE_PREFIX . 'PortalUser';
				$structure = $this->Conn->Query($sql);

				foreach ($structure as $field_info) {
					if ($field_info['Field'] == 'dob') {
						if (!$field_info['Key']) {
							$sql = 'ALTER TABLE ' . TABLE_PREFIX . 'PortalUser
									ADD INDEX (dob)';
							$this->Conn->Query($sql);
						}
						break;
					}
				}
			}
		}

		/**
		 * Removes duplicate phrases, update file paths in database
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_4_3_9($mode)
		{
			// 1. find In-Portal old <inp: tags
			$sql = 'SELECT EmailMessageId
					FROM '.TABLE_PREFIX.'EmailMessage
					WHERE Template LIKE \'%<inp:%\'';
			$event_ids = implode(',', $this->Conn->GetCol($sql));

			// 2. replace In-Portal old <inp: tags to K4 tags
			$replacements = Array (
				'<inp:m_category_field _Field="Name" _StripHTML="1"' => '<inp2:c_Field name="Name"',
				'<inp:touser _Field="password"' => '<inp2:u_Field name="Password_plain"',
				'<inp:touser _Field="UserName"' => '<inp2:u_Field name="Login"',
				'<inp:touser _Field="' => '<inp2:u_Field name="',
				'<inp:m_page_title' => '<inp2:m_BaseUrl',
				'<inp:m_theme_url _page="current"' => '<inp2:m_BaseUrl',
				'<inp:topic _field="text"' => '<inp2:bb-post_Field name="PostingText"',
				'<inp:topic _field="link" _Template="inbulletin/post_list"' => '<inp2:bb_TopicLink template="__default__"',
			);

			if ($event_ids) {
				foreach ($replacements as $old_tag => $new_tag) {
					$sql = 'UPDATE '.TABLE_PREFIX.'EmailMessage
							SET Template = REPLACE(Template, '.$this->Conn->qstr($old_tag).', '.$this->Conn->qstr($new_tag).')
							WHERE EventId IN ('.$event_ids.')';
					$this->Conn->Query($sql);
				}
			}

			if ($mode == 'after') {
				$this->_insertInPortalData();
				$this->_moveDatabaseFolders();

				// in case, when In-Portal module is enabled -> turn AdvancedUserManagement on too
				if ($this->Application->findModule('Name', 'In-Portal')) {
					$sql = 'UPDATE ' . TABLE_PREFIX . 'ConfigurationValues
							SET VariableValue = 1
							WHERE VariableName = "AdvancedUserManagement"';
					$this->Conn->Query($sql);
				}
			}

			if ($mode == 'languagepack') {
				$this->_removeDuplicatePhrases();
			}
		}

		function _insertInPortalData()
		{
			$data = Array (
				'ConfigurationAdmin' => Array (
					'UniqueField' => 'VariableName',
					'Records' => Array (
						'AllowDeleteRootCats' => "('AllowDeleteRootCats', 'la_Text_General', 'la_AllowDeleteRootCats', 'checkbox', NULL , NULL , 10.09, 0, 0)",
						'Catalog_PreselectModuleTab' => "('Catalog_PreselectModuleTab', 'la_Text_General', 'la_config_CatalogPreselectModuleTab', 'checkbox', NULL, NULL, 10.10, 0, 1)",
						'RecycleBinFolder' => "('RecycleBinFolder', 'la_Text_General', 'la_config_RecycleBinFolder', 'text', NULL , NULL , 10.11, 0, 0)",
						'AdvancedUserManagement' => "('AdvancedUserManagement', 'la_Text_General', 'la_prompt_AdvancedUserManagement', 'checkbox', NULL, NULL, '10.011', 0, 1)",
					),
				),

				'ConfigurationValues' => Array (
					'UniqueField' => 'VariableName',
					'Records' => Array (
						'AdvancedUserManagement' => "(DEFAULT, 'AdvancedUserManagement', 0, 'In-Portal:Users', 'in-portal:configure_users')",
					),
				),

				'ItemTypes' => Array (
					'UniqueField' => 'ItemType',
					'Records' => Array (
						'1' => "(1, 'In-Portal', 'c', 'Category', 'Name', 'CreatedById', NULL, NULL, 'la_ItemTab_Categories', 1, 'admin/category/addcategory.php', 'clsCategory', 'Category')",
						'6' => "(6, 'In-Portal', 'u', 'PortalUser', 'Login', 'PortalUserId', NULL, NULL, '', 0, '', 'clsPortalUser', 'User')",
					),
				),

				'PermissionConfig' => Array (
					'UniqueField' => 'PermissionName',
					'Records' => Array (
						'CATEGORY.ADD' => "(DEFAULT, 'CATEGORY.ADD', 'lu_PermName_Category.Add_desc', 'lu_PermName_Category.Add_error', 'In-Portal')",
						'CATEGORY.DELETE' => "(DEFAULT, 'CATEGORY.DELETE', 'lu_PermName_Category.Delete_desc', 'lu_PermName_Category.Delete_error', 'In-Portal')",
						'CATEGORY.ADD.PENDING' => "(DEFAULT, 'CATEGORY.ADD.PENDING', 'lu_PermName_Category.AddPending_desc', 'lu_PermName_Category.AddPending_error', 'In-Portal')",
						'CATEGORY.MODIFY' => "(DEFAULT, 'CATEGORY.MODIFY', 'lu_PermName_Category.Modify_desc', 'lu_PermName_Category.Modify_error', 'In-Portal')",
						'ADMIN' => "(DEFAULT, 'ADMIN', 'lu_PermName_Admin_desc', 'lu_PermName_Admin_error', 'Admin')",
						'LOGIN' => "(DEFAULT, 'LOGIN', 'lu_PermName_Login_desc', 'lu_PermName_Admin_error', 'Front')",
						'DEBUG.ITEM' => "(DEFAULT, 'DEBUG.ITEM', 'lu_PermName_Debug.Item_desc', '', 'Admin')",
						'DEBUG.LIST' => "(DEFAULT, 'DEBUG.LIST', 'lu_PermName_Debug.List_desc', '', 'Admin')",
						'DEBUG.INFO' => "(DEFAULT, 'DEBUG.INFO', 'lu_PermName_Debug.Info_desc', '', 'Admin')",
						'PROFILE.MODIFY' => "(DEFAULT, 'PROFILE.MODIFY', 'lu_PermName_Profile.Modify_desc', '', 'Admin')",
						'SHOWLANG' => "(DEFAULT, 'SHOWLANG', 'lu_PermName_ShowLang_desc', '', 'Admin')",
						'FAVORITES' => "(DEFAULT, 'FAVORITES', 'lu_PermName_favorites_desc', 'lu_PermName_favorites_error', 'In-Portal')",
						'SYSTEM_ACCESS.READONLY' => "(DEFAULT, 'SYSTEM_ACCESS.READONLY', 'la_PermName_SystemAccess.ReadOnly_desc', 'la_PermName_SystemAccess.ReadOnly_error', 'Admin')",
					),
				),

				'Permissions' => Array (
					'UniqueField' => 'Permission;GroupId;Type;CatId',
					'Records' => Array (
						'LOGIN;12;1;0' => "(DEFAULT, 'LOGIN', 12, 1, 1, 0)",
						'in-portal:site.view;11;1;0' => "(DEFAULT, 'in-portal:site.view', 11, 1, 1, 0)",
						'in-portal:browse.view;11;1;0' => "(DEFAULT, 'in-portal:browse.view', 11, 1, 1, 0)",
						'in-portal:advanced_view.view;11;1;0' => "(DEFAULT, 'in-portal:advanced_view.view', 11, 1, 1, 0)",
						'in-portal:reviews.view;11;1;0' => "(DEFAULT, 'in-portal:reviews.view', 11, 1, 1, 0)",
						'in-portal:configure_categories.view;11;1;0' => "(DEFAULT, 'in-portal:configure_categories.view', 11, 1, 1, 0)",
						'in-portal:configure_categories.edit;11;1;0' => "(DEFAULT, 'in-portal:configure_categories.edit', 11, 1, 1, 0)",
						'in-portal:configuration_search.view;11;1;0' => "(DEFAULT, 'in-portal:configuration_search.view', 11, 1, 1, 0)",
						'in-portal:configuration_search.edit;11;1;0' => "(DEFAULT, 'in-portal:configuration_search.edit', 11, 1, 1, 0)",
						'in-portal:configuration_email.view;11;1;0' => "(DEFAULT, 'in-portal:configuration_email.view', 11, 1, 1, 0)",
						'in-portal:configuration_email.edit;11;1;0' => "(DEFAULT, 'in-portal:configuration_email.edit', 11, 1, 1, 0)",
						'in-portal:configuration_custom.view;11;1;0' => "(DEFAULT, 'in-portal:configuration_custom.view', 11, 1, 1, 0)",
						'in-portal:configuration_custom.add;11;1;0' => "(DEFAULT, 'in-portal:configuration_custom.add', 11, 1, 1, 0)",
						'in-portal:configuration_custom.edit;11;1;0' => "(DEFAULT, 'in-portal:configuration_custom.edit', 11, 1, 1, 0)",
						'in-portal:configuration_custom.delete;11;1;0' => "(DEFAULT, 'in-portal:configuration_custom.delete', 11, 1, 1, 0)",
						'in-portal:users.view;11;1;0' => "(DEFAULT, 'in-portal:users.view', 11, 1, 1, 0)",
						'in-portal:user_list.advanced:ban;11;1;0' => "(DEFAULT, 'in-portal:user_list.advanced:ban', 11, 1, 1, 0)",
						'in-portal:user_list.advanced:send_email;11;1;0' => "(DEFAULT, 'in-portal:user_list.advanced:send_email', 11, 1, 1, 0)",
						'in-portal:user_groups.view;11;1;0' => "(DEFAULT, 'in-portal:user_groups.view', 11, 1, 1, 0)",
						'in-portal:user_groups.add;11;1;0' => "(DEFAULT, 'in-portal:user_groups.add', 11, 1, 1, 0)",
						'in-portal:user_groups.edit;11;1;0' => "(DEFAULT, 'in-portal:user_groups.edit', 11, 1, 1, 0)",
						'in-portal:user_groups.delete;11;1;0' => "(DEFAULT, 'in-portal:user_groups.delete', 11, 1, 1, 0)",
						'in-portal:user_groups.advanced:send_email;11;1;0' => "(DEFAULT, 'in-portal:user_groups.advanced:send_email', 11, 1, 1, 0)",
						'in-portal:user_groups.advanced:manage_permissions;11;1;0' => "(DEFAULT, 'in-portal:user_groups.advanced:manage_permissions', 11, 1, 1, 0)",
						'in-portal:configure_users.view;11;1;0' => "(DEFAULT, 'in-portal:configure_users.view', 11, 1, 1, 0)",
						'in-portal:configure_users.edit;11;1;0' => "(DEFAULT, 'in-portal:configure_users.edit', 11, 1, 1, 0)",
						'in-portal:user_email.view;11;1;0' => "(DEFAULT, 'in-portal:user_email.view', 11, 1, 1, 0)",
						'in-portal:user_email.edit;11;1;0' => "(DEFAULT, 'in-portal:user_email.edit', 11, 1, 1, 0)",
						'in-portal:user_custom.view;11;1;0' => "(DEFAULT, 'in-portal:user_custom.view', 11, 1, 1, 0)",
						'in-portal:user_custom.add;11;1;0' => "(DEFAULT, 'in-portal:user_custom.add', 11, 1, 1, 0)",
						'in-portal:user_custom.edit;11;1;0' => "(DEFAULT, 'in-portal:user_custom.edit', 11, 1, 1, 0)",
						'in-portal:user_custom.delete;11;1;0' => "(DEFAULT, 'in-portal:user_custom.delete', 11, 1, 1, 0)",
						'in-portal:user_banlist.view;11;1;0' => "(DEFAULT, 'in-portal:user_banlist.view', 11, 1, 1, 0)",
						'in-portal:user_banlist.add;11;1;0' => "(DEFAULT, 'in-portal:user_banlist.add', 11, 1, 1, 0)",
						'in-portal:user_banlist.edit;11;1;0' => "(DEFAULT, 'in-portal:user_banlist.edit', 11, 1, 1, 0)",
						'in-portal:user_banlist.delete;11;1;0' => "(DEFAULT, 'in-portal:user_banlist.delete', 11, 1, 1, 0)",
						'in-portal:reports.view;11;1;0' => "(DEFAULT, 'in-portal:reports.view', 11, 1, 1, 0)",
						'in-portal:log_summary.view;11;1;0' => "(DEFAULT, 'in-portal:log_summary.view', 11, 1, 1, 0)",
						'in-portal:searchlog.view;11;1;0' => "(DEFAULT, 'in-portal:searchlog.view', 11, 1, 1, 0)",
						'in-portal:searchlog.delete;11;1;0' => "(DEFAULT, 'in-portal:searchlog.delete', 11, 1, 1, 0)",
						'in-portal:sessionlog.view;11;1;0' => "(DEFAULT, 'in-portal:sessionlog.view', 11, 1, 1, 0)",
						'in-portal:sessionlog.delete;11;1;0' => "(DEFAULT, 'in-portal:sessionlog.delete', 11, 1, 1, 0)",
						'in-portal:emaillog.view;11;1;0' => "(DEFAULT, 'in-portal:emaillog.view', 11, 1, 1, 0)",
						'in-portal:emaillog.delete;11;1;0' => "(DEFAULT, 'in-portal:emaillog.delete', 11, 1, 1, 0)",
						'in-portal:visits.view;11;1;0' => "(DEFAULT, 'in-portal:visits.view', 11, 1, 1, 0)",
						'in-portal:visits.delete;11;1;0' => "(DEFAULT, 'in-portal:visits.delete', 11, 1, 1, 0)",
						'in-portal:configure_general.view;11;1;0' => "(DEFAULT, 'in-portal:configure_general.view', 11, 1, 1, 0)",
						'in-portal:configure_general.edit;11;1;0' => "(DEFAULT, 'in-portal:configure_general.edit', 11, 1, 1, 0)",
						'in-portal:modules.view;11;1;0' => "(DEFAULT, 'in-portal:modules.view', 11, 1, 1, 0)",
						'in-portal:mod_status.view;11;1;0' => "(DEFAULT, 'in-portal:mod_status.view', 11, 1, 1, 0)",
						'in-portal:mod_status.edit;11;1;0' => "(DEFAULT, 'in-portal:mod_status.edit', 11, 1, 1, 0)",
						'in-portal:mod_status.advanced:approve;11;1;0' => "(DEFAULT, 'in-portal:mod_status.advanced:approve', 11, 1, 1, 0)",
						'in-portal:mod_status.advanced:decline;11;1;0' => "(DEFAULT, 'in-portal:mod_status.advanced:decline', 11, 1, 1, 0)",
						'in-portal:addmodule.view;11;1;0' => "(DEFAULT, 'in-portal:addmodule.view', 11, 1, 1, 0)",
						'in-portal:addmodule.add;11;1;0' => "(DEFAULT, 'in-portal:addmodule.add', 11, 1, 1, 0)",
						'in-portal:addmodule.edit;11;1;0' => "(DEFAULT, 'in-portal:addmodule.edit', 11, 1, 1, 0)",
						'in-portal:tag_library.view;11;1;0' => "(DEFAULT, 'in-portal:tag_library.view', 11, 1, 1, 0)",
						'in-portal:configure_themes.view;11;1;0' => "(DEFAULT, 'in-portal:configure_themes.view', 11, 1, 1, 0)",
						'in-portal:configure_themes.add;11;1;0' => "(DEFAULT, 'in-portal:configure_themes.add', 11, 1, 1, 0)",
						'in-portal:configure_themes.edit;11;1;0' => "(DEFAULT, 'in-portal:configure_themes.edit', 11, 1, 1, 0)",
						'in-portal:configure_themes.delete;11;1;0' => "(DEFAULT, 'in-portal:configure_themes.delete', 11, 1, 1, 0)",
						'in-portal:configure_styles.view;11;1;0' => "(DEFAULT, 'in-portal:configure_styles.view', 11, 1, 1, 0)",
						'in-portal:configure_styles.add;11;1;0' => "(DEFAULT, 'in-portal:configure_styles.add', 11, 1, 1, 0)",
						'in-portal:configure_styles.edit;11;1;0' => "(DEFAULT, 'in-portal:configure_styles.edit', 11, 1, 1, 0)",
						'in-portal:configure_styles.delete;11;1;0' => "(DEFAULT, 'in-portal:configure_styles.delete', 11, 1, 1, 0)",
						'in-portal:configure_lang.advanced:set_primary;11;1;0' => "(DEFAULT, 'in-portal:configure_lang.advanced:set_primary', 11, 1, 1, 0)",
						'in-portal:configure_lang.advanced:import;11;1;0' => "(DEFAULT, 'in-portal:configure_lang.advanced:import', 11, 1, 1, 0)",
						'in-portal:configure_lang.advanced:export;11;1;0' => "(DEFAULT, 'in-portal:configure_lang.advanced:export', 11, 1, 1, 0)",
						'in-portal:tools.view;11;1;0' => "(DEFAULT, 'in-portal:tools.view', 11, 1, 1, 0)",
						'in-portal:backup.view;11;1;0' => "(DEFAULT, 'in-portal:backup.view', 11, 1, 1, 0)",
						'in-portal:restore.view;11;1;0' => "(DEFAULT, 'in-portal:restore.view', 11, 1, 1, 0)",
						'in-portal:export.view;11;1;0' => "(DEFAULT, 'in-portal:export.view', 11, 1, 1, 0)",
						'in-portal:main_import.view;11;1;0' => "(DEFAULT, 'in-portal:main_import.view', 11, 1, 1, 0)",
						'in-portal:sql_query.view;11;1;0' => "(DEFAULT, 'in-portal:sql_query.view', 11, 1, 1, 0)",
						'in-portal:sql_query.edit;11;1;0' => "(DEFAULT, 'in-portal:sql_query.edit', 11, 1, 1, 0)",
						'in-portal:server_info.view;11;1;0' => "(DEFAULT, 'in-portal:server_info.view', 11, 1, 1, 0)",
						'in-portal:help.view;11;1;0' => "(DEFAULT, 'in-portal:help.view', 11, 1, 1, 0)",
					),
				),

				'SearchConfig' => Array (
					'UniqueField' => 'TableName;FieldName;ModuleName',
					'Records' => Array (
						'Category;NewItem;In-Portal' => "('Category', 'NewItem', 0, 1, 'lu_fielddesc_category_newitem', 'lu_field_newitem', 'In-Portal', 'la_text_category', 18, DEFAULT, 0, 'boolean', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;PopItem;In-Portal' => "('Category', 'PopItem', 0, 1, 'lu_fielddesc_category_popitem', 'lu_field_popitem', 'In-Portal', 'la_text_category', 19, DEFAULT, 0, 'boolean', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;HotItem;In-Portal' => "('Category', 'HotItem', 0, 1, 'lu_fielddesc_category_hotitem', 'lu_field_hotitem', 'In-Portal', 'la_text_category', 17, DEFAULT, 0, 'boolean', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;MetaDescription;In-Portal' => "('Category', 'MetaDescription', 0, 1, 'lu_fielddesc_category_metadescription', 'lu_field_metadescription', 'In-Portal', 'la_text_category', 16, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;ParentPath;In-Portal' => "('Category', 'ParentPath', 0, 1, 'lu_fielddesc_category_parentpath', 'lu_field_parentpath', 'In-Portal', 'la_text_category', 15, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;ResourceId;In-Portal' => "('Category', 'ResourceId', 0, 1, 'lu_fielddesc_category_resourceid', 'lu_field_resourceid', 'In-Portal', 'la_text_category', 14, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;CreatedById;In-Portal' => "('Category', 'CreatedById', 0, 1, 'lu_fielddesc_category_createdbyid', 'lu_field_createdbyid', 'In-Portal', 'la_text_category', 13, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;CachedNavbar;In-Portal' => "('Category', 'CachedNavbar', 0, 1, 'lu_fielddesc_category_cachednavbar', 'lu_field_cachednavbar', 'In-Portal', 'la_text_category', 12, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;CachedDescendantCatsQty;In-Portal' => "('Category', 'CachedDescendantCatsQty', 0, 1, 'lu_fielddesc_category_cacheddescendantcatsqty', 'lu_field_cacheddescendantcatsqty', 'In-Portal', 'la_text_category', 11, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;MetaKeywords;In-Portal' => "('Category', 'MetaKeywords', 0, 1, 'lu_fielddesc_category_metakeywords', 'lu_field_metakeywords', 'In-Portal', 'la_text_category', 10, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;Priority;In-Portal' => "('Category', 'Priority', 0, 1, 'lu_fielddesc_category_priority', 'lu_field_priority', 'In-Portal', 'la_text_category', 9, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;Status;In-Portal' => "('Category', 'Status', 0, 1, 'lu_fielddesc_category_status', 'lu_field_status', 'In-Portal', 'la_text_category', 7, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;EditorsPick;In-Portal' => "('Category', 'EditorsPick', 0, 1, 'lu_fielddesc_category_editorspick', 'lu_field_editorspick', 'In-Portal', 'la_text_category', 6, DEFAULT, 0, 'boolean', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;CreatedOn;In-Portal' => "('Category', 'CreatedOn', 0, 1, 'lu_fielddesc_category_createdon', 'lu_field_createdon', 'In-Portal', 'la_text_category', 5, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;Description;In-Portal' => "('Category', 'Description', 1, 1, 'lu_fielddesc_category_description', 'lu_field_description', 'In-Portal', 'la_text_category', 4, DEFAULT, 2, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;Name;In-Portal' => "('Category', 'Name', 1, 1, 'lu_fielddesc_category_name', 'lu_field_name', 'In-Portal', 'la_text_category', 3, DEFAULT, 2, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;ParentId;In-Portal' => "('Category', 'ParentId', 0, 1, 'lu_fielddesc_category_parentid', 'lu_field_parentid', 'In-Portal', 'la_text_category', 2, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;CategoryId;In-Portal' => "('Category', 'CategoryId', 0, 1, 'lu_fielddesc_category_categoryid', 'lu_field_categoryid', 'In-Portal', 'la_text_category', 0, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;Modified;In-Portal' => "('Category', 'Modified', 0, 1, 'lu_fielddesc_category_modified', 'lu_field_modified', 'In-Portal', 'la_text_category', 20, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'Category;ModifiedById;In-Portal' => "('Category', 'ModifiedById', 0, 1, 'lu_fielddesc_category_modifiedbyid', 'lu_field_modifiedbyid', 'In-Portal', 'la_text_category', 21, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",

						'PortalUser;PortalUserId;In-Portal' => "('PortalUser', 'PortalUserId', -1, 0, 'lu_fielddesc_user_portaluserid', 'lu_field_portaluserid', 'In-Portal', 'la_text_user', 0, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Login;In-Portal' => "('PortalUser', 'Login', -1, 0, 'lu_fielddesc_user_login', 'lu_field_login', 'In-Portal', 'la_text_user', 1, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Password;In-Portal' => "('PortalUser', 'Password', -1, 0, 'lu_fielddesc_user_password', 'lu_field_password', 'In-Portal', 'la_text_user', 2, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;tz;In-Portal' => "('PortalUser', 'tz', -1, 0, 'lu_fielddesc_user_tz', 'lu_field_tz', 'In-Portal', 'la_text_user', 17, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;dob;In-Portal' => "('PortalUser', 'dob', -1, 0, 'lu_fielddesc_user_dob', 'lu_field_dob', 'In-Portal', 'la_text_user', 16, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Modified;In-Portal' => "('PortalUser', 'Modified', -1, 0, 'lu_fielddesc_user_modified', 'lu_field_modified', 'In-Portal', 'la_text_user', 15, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Status;In-Portal' => "('PortalUser', 'Status', -1, 0, 'lu_fielddesc_user_status', 'lu_field_status', 'In-Portal', 'la_text_user', 14, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;ResourceId;In-Portal' => "('PortalUser', 'ResourceId', -1, 0, 'lu_fielddesc_user_resourceid', 'lu_field_resourceid', 'In-Portal', 'la_text_user', 13, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Country;In-Portal' => "('PortalUser', 'Country', -1, 0, 'lu_fielddesc_user_country', 'lu_field_country', 'In-Portal', 'la_text_user', 12, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Zip;In-Portal' => "('PortalUser', 'Zip', -1, 0, 'lu_fielddesc_user_zip', 'lu_field_zip', 'In-Portal', 'la_text_user', 11, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;State;In-Portal' => "('PortalUser', 'State', -1, 0, 'lu_fielddesc_user_state', 'lu_field_state', 'In-Portal', 'la_text_user', 10, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;City;In-Portal' => "('PortalUser', 'City', -1, 0, 'lu_fielddesc_user_city', 'lu_field_city', 'In-Portal', 'la_text_user', 9, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Street;In-Portal' => "('PortalUser', 'Street', -1, 0, 'lu_fielddesc_user_street', 'lu_field_street', 'In-Portal', 'la_text_user', 8, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Phone;In-Portal' => "('PortalUser', 'Phone', -1, 0, 'lu_fielddesc_user_phone', 'lu_field_phone', 'In-Portal', 'la_text_user', 7, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;CreatedOn;In-Portal' => "('PortalUser', 'CreatedOn', -1, 0, 'lu_fielddesc_user_createdon', 'lu_field_createdon', 'In-Portal', 'la_text_user', 6, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;Email;In-Portal' => "('PortalUser', 'Email', -1, 0, 'lu_fielddesc_user_email', 'lu_field_email', 'In-Portal', 'la_text_user', 5, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;LastName;In-Portal' => "('PortalUser', 'LastName', -1, 0, 'lu_fielddesc_user_lastname', 'lu_field_lastname', 'In-Portal', 'la_text_user', 4, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
						'PortalUser;FirstName;In-Portal' => "('PortalUser', 'FirstName', -1, 0, 'lu_fielddesc_user_firstname', 'lu_field_firstname', 'In-Portal', 'la_text_user', 3, DEFAULT, 0, 'text', NULL, NULL, NULL, NULL, NULL, NULL, NULL)",
					),
				),

				'StatItem' => Array (
					'UniqueField' => 'Module;ListLabel',
					'Records' => Array (
						'In-Portal;la_prompt_ActiveCategories' => "(DEFAULT, 'In-Portal', 'SELECT count(*) FROM <%prefix%>Category WHERE Status=1 ', NULL, 'la_prompt_ActiveCategories', '0', '1')",
						'In-Portal;la_prompt_ActiveUsers' => "(DEFAULT, 'In-Portal', 'SELECT count(*) FROM <%prefix%>PortalUser WHERE Status=1 ', NULL, 'la_prompt_ActiveUsers', '0', '1')",
						'In-Portal;la_prompt_CurrentSessions' => "(DEFAULT, 'In-Portal', 'SELECT count(*) FROM <%prefix%>UserSession', NULL, 'la_prompt_CurrentSessions', '0', '1')",
						'In-Portal;la_prompt_TotalCategories' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) as CategoryCount FROM <%prefix%>Category', NULL, 'la_prompt_TotalCategories', 0, 2)",
						'In-Portal;la_prompt_ActiveCategories' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS ActiveCategories FROM <%prefix%>Category WHERE Status = 1', NULL, 'la_prompt_ActiveCategories', 0, 2)",
						'In-Portal;la_prompt_PendingCategories' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS PendingCategories FROM <%prefix%>Category WHERE Status = 2', NULL, 'la_prompt_PendingCategories', 0, 2)",
						'In-Portal;la_prompt_DisabledCategories' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS DisabledCategories FROM <%prefix%>Category WHERE Status = 0', NULL, 'la_prompt_DisabledCategories', 0, 2)",
						'In-Portal;la_prompt_NewCategories' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS NewCategories FROM <%prefix%>Category WHERE (NewItem = 1) OR ( (UNIX_TIMESTAMP() - CreatedOn) <= <%m:config name=\"Category_DaysNew\"%>*86400 AND (NewItem = 2) )', NULL, 'la_prompt_NewCategories', 0, 2)",
						'In-Portal;la_prompt_CategoryEditorsPick' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) FROM <%prefix%>Category WHERE EditorsPick = 1', NULL, 'la_prompt_CategoryEditorsPick', 0, 2)",
						'In-Portal;la_prompt_NewestCategoryDate' => "(DEFAULT, 'In-Portal', 'SELECT <%m:post_format field=\"MAX(CreatedOn)\" type=\"date\"%> FROM <%prefix%>Category', NULL, 'la_prompt_NewestCategoryDate', 0, 2)",
						'In-Portal;la_prompt_LastCategoryUpdate' => "(DEFAULT, 'In-Portal', 'SELECT <%m:post_format field=\"MAX(Modified)\" type=\"date\"%> FROM <%prefix%>Category', NULL, 'la_prompt_LastCategoryUpdate', 0, 2)",
						'In-Portal;la_prompt_TopicsUsers' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS TotalUsers FROM <%prefix%>PortalUser', NULL, 'la_prompt_TopicsUsers', 0, 2)",
						'In-Portal;la_prompt_UsersActive' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS ActiveUsers FROM <%prefix%>PortalUser WHERE Status = 1', NULL, 'la_prompt_UsersActive', 0, 2)",
						'In-Portal;la_prompt_UsersPending' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS PendingUsers FROM <%prefix%>PortalUser WHERE Status = 2', NULL, 'la_prompt_UsersPending', 0, 2)",
						'In-Portal;la_prompt_UsersDisabled' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS DisabledUsers FROM <%prefix%>PortalUser WHERE Status = 0', NULL, 'la_prompt_UsersDisabled', 0, 2)",
						'In-Portal;la_prompt_NewestUserDate' => "(DEFAULT, 'In-Portal', 'SELECT <%m:post_format field=\"MAX(CreatedOn)\" type=\"date\"%> FROM <%prefix%>PortalUser', NULL, 'la_prompt_NewestUserDate', 0, 2)",
						'In-Portal;la_prompt_UsersUniqueCountries' => "(DEFAULT, 'In-Portal', 'SELECT COUNT( DISTINCT LOWER( Country ) ) FROM <%prefix%>PortalUser WHERE LENGTH(Country) > 0', NULL, 'la_prompt_UsersUniqueCountries', 0, 2)",
						'In-Portal;la_prompt_UsersUniqueStates' => "(DEFAULT, 'In-Portal', 'SELECT COUNT( DISTINCT LOWER( State ) ) FROM <%prefix%>PortalUser WHERE LENGTH(State) > 0', NULL, 'la_prompt_UsersUniqueStates', 0, 2)",
						'In-Portal;la_prompt_TotalUserGroups' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS TotalUserGroups FROM <%prefix%>PortalGroup', NULL, 'la_prompt_TotalUserGroups', 0, 2)",
						'In-Portal;la_prompt_BannedUsers' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS BannedUsers FROM <%prefix%>PortalUser WHERE IsBanned = 1', NULL, 'la_prompt_BannedUsers', 0, 2)",
						'In-Portal;la_prompt_NonExpiredSessions' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS NonExipedSessions FROM <%prefix%>UserSession WHERE Status = 1', NULL, 'la_prompt_NonExpiredSessions', 0, 2)",
						'In-Portal;la_prompt_ThemeCount' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS ThemeCount FROM <%prefix%>Theme', NULL, 'la_prompt_ThemeCount', 0, 2)",
						'In-Portal;la_prompt_RegionsCount' => "(DEFAULT, 'In-Portal', 'SELECT COUNT(*) AS RegionsCount FROM <%prefix%>Language', NULL, 'la_prompt_RegionsCount', 0, 2)",
						'In-Portal;la_prompt_TablesCount' => "(DEFAULT, 'In-Portal', '<%m:sql_action sql=\"SHOW+TABLES\" action=\"COUNT\" field=\"*\"%>', NULL, 'la_prompt_TablesCount', 0, 2)",
						'In-Portal;la_prompt_RecordsCount' => "(DEFAULT, 'In-Portal', '<%m:sql_action sql=\"SHOW+TABLE+STATUS\" action=\"SUM\" field=\"Rows\"%>', NULL, 'la_prompt_RecordsCount', 0, 2)",
						'In-Portal;la_prompt_SystemFileSize' => "(DEFAULT, 'In-Portal', '<%m:custom_action sql=\"empty\" action=\"SysFileSize\"%>', NULL, 'la_prompt_SystemFileSize', 0, 2)",
						'In-Portal;la_prompt_DataSize' => "(DEFAULT, 'In-Portal', '<%m:sql_action sql=\"SHOW+TABLE+STATUS\" action=\"SUM\" format_as=\"file\" field=\"Data_length\"%>', NULL, 'la_prompt_DataSize', 0, 2)",
					),
				),

				'StylesheetSelectors' => Array (
					'UniqueField' => 'SelectorId',
					'Records' => Array (
						'169' => "(169, 8, 'Calendar''s selected days', '.calendar tbody .selected', 'a:0:{}', '', 1, 'font-weight: bold;\\\r\\nbackground-color: #9ED7ED;\\r\\nborder: 1px solid #83B2C5;', 0)",
						'118' => "(118, 8, 'Data grid row', 'td.block-data-row', 'a:0:{}', '', 2, 'border-bottom: 1px solid #cccccc;', 48)",
						'81' => "(81, 8, '\"More\" link', 'a.link-more', 'a:0:{}', '', 2, 'text-decoration: underline;', 64)",
						'88' => "(88, 8, 'Block data, separated rows', 'td.block-data-grid', 'a:0:{}', '', 2, 'border: 1px solid #cccccc;', 48)",
						'42' => "(42, 8, 'Block Header', 'td.block-header', 'a:4:{s:5:\"color\";s:16:\"rgb(0, 159, 240)\";s:9:\"font-size\";s:4:\"20px\";s:11:\"font-weight\";s:6:\"normal\";s:7:\"padding\";s:3:\"5px\";}', 'Block Header', 1, 'font-family: Verdana, Helvetica, sans-serif;', 0)",
						'76' => "(76, 8, 'Navigation bar menu', 'tr.head-nav td', 'a:0:{}', '', 1, 'vertical-align: middle;', 0)",
						'48' => "(48, 8, 'Block data', 'td.block-data', 'a:2:{s:9:\"font-size\";s:5:\"12px;\";s:7:\"padding\";s:3:\"5px\";}', '', 1, '', 0)",
						'78' => "(78, 8, 'Body main style', 'body', 'a:0:{}', '', 1, 'padding: 0px; \\r\\nbackground-color: #ffffff; \\r\\nfont-family: arial, verdana, helvetica; \\r\\nfont-size: small;\\r\\nwidth: auto;\\r\\nmargin: 0px;', 0)",
						'58' => "(58, 8, 'Main table', 'table.main-table', 'a:0:{}', '', 1, 'width: 770px;\\r\\nmargin: 0px;\\r\\n/*table-layout: fixed;*/', 0)",
						'79' => "(79, 8, 'Block: header of data block', 'span.block-data-grid-header', 'a:0:{}', '', 1, 'font-family: Arial, Helvetica, sans-serif;\\r\\ncolor: #009DF6;\\r\\nfont-size: 12px;\\r\\nfont-weight: bold;\\r\\nbackground-color: #E6EEFF;\\r\\npadding: 6px;\\r\\nwhite-space: nowrap;', 0)",
						'64' => "(64, 8, 'Link', 'a', 'a:0:{}', '', 1, '', 0)",
						'46' => "(46, 8, 'Product title link', 'a.link-product1', 'a:0:{}', 'Product title link', 1, 'color: #62A1DE;\\r\\nfont-size: 14px;\\r\\nfont-weight: bold;\\r\\nline-height: 20px;\\r\\npadding-bottom: 10px;', 0)",
						'75' => "(75, 8, 'Copy of Main path link', 'table.main-path td a:hover', 'a:0:{}', '', 1, 'color: #ffffff;', 0)",
						'160' => "(160, 8, 'Current item in navigation bar', '.checkout-step-current', 'a:0:{}', '', 1, 'color: #A20303;\\r\\nfont-weight: bold;', 0)",
						'51' => "(51, 8, 'Right block data', 'td.right-block-data', 'a:1:{s:9:\"font-size\";s:4:\"11px\";}', '', 2, 'padding: 7px;\\r\\nbackground: #e3edf6 url(\"/in-commerce4/themes/default/img/bgr_login.jpg\") repeat-y	scroll left top;\\r\\nborder-bottom: 1px solid #64a1df;', 48)",
						'67' => "(67, 8, 'Pagination bar: text', 'table.block-pagination td', 'a:3:{s:5:\"color\";s:7:\"#8B898B\";s:9:\"font-size\";s:4:\"12px\";s:11:\"font-weight\";s:6:\"normal\";}', '', 1, '', 0)",
						'45' => "(45, 8, 'Category link', 'a.subcat', 'a:0:{}', 'Category link', 1, 'color: #2069A4', 0)",
						'68' => "(68, 8, 'Pagination bar: link', 'table.block-pagination td a', 'a:3:{s:5:\"color\";s:7:\"#8B898B\";s:9:\"font-size\";s:5:\"12px;\";s:11:\"font-weight\";s:6:\"normal\";}', '', 1, '', 0)",
						'69' => "(69, 8, 'Product description in product list', '.product-list-description', 'a:2:{s:5:\"color\";s:7:\"#8B898B\";s:9:\"font-size\";s:4:\"12px\";}', '', 1, '', 0)",
						'73' => "(73, 8, 'Main path link', 'table.main-path td a', 'a:0:{}', '', 1, 'color: #d5e231;', 0)",
						'83' => "(83, 8, 'Product title link in list (shopping cart)', 'a.link-product-cart', 'a:0:{}', 'Product title link', 1, 'color: #18559C;\\r\\nfont-size: 12px;\\r\\nfont-weight: bold;\\r\\ntext-decoration: none;\\r\\n\\r\\n', 0)",
						'72' => "(72, 8, 'Main path block text', 'table.main-path td', 'a:0:{}', '', 1, 'color: #ffffff;\\r\\nfont-size: 10px;\\r\\nfont-weight: normal;\\r\\npadding: 1px;\\r\\n', 0)",
						'61' => "(61, 8, 'Block: header of data table', 'td.block-data-grid-header', 'a:6:{s:4:\"font\";s:28:\"Arial, Helvetica, sans-serif\";s:5:\"color\";s:7:\"#009DF6\";s:9:\"font-size\";s:4:\"12px\";s:11:\"font-weight\";s:4:\"bold\";s:16:\"background-color\";s:7:\"#E6EEFF\";s:7:\"padding\";s:3:\"6px\";}', '', 1, 'white-space: nowrap;\\r\\npadding-left: 10px;\\r\\n/*\\r\\nbackground-image: url(/in-commerce4/themes/default/img/bullet1.gif);\\r\\nbackground-position: 10px 12px;\\r\\nbackground-repeat: no-repeat;\\r\\n*/', 0)",
						'65' => "(65, 8, 'Link in product list additional row', 'td.product-list-additional a', 'a:1:{s:5:\"color\";s:7:\"#8B898B\";}', '', 2, '', 64)",
						'55' => "(55, 8, 'Main table, left column', 'td.main-column-left', 'a:0:{}', '', 1, 'width:180px;\\r\\nborder: 1px solid #62A1DE;\\r\\nborder-top: 0px;', 0)",
						'70' => "(70, 8, 'Product title link in list (category)', 'a.link-product-category', 'a:0:{}', 'Product title link', 1, 'color: #18559C;\\r\\nfont-size: 12px;\\r\\nfont-weight: bold;\\r\\ntext-decoration: none;\\r\\n\\r\\n', 0)",
						'66' => "(66, 8, 'Pagination bar block', 'table.block-pagination', 'a:0:{}', '', 1, '', 0)",
						'49' => "(49, 8, 'Bulleted list inside block', 'td.block-data ul li', 'a:0:{}', '', 1, '    list-style-image: url(/in-commerce4/themes/default/img/bullet2.gif);\\r\\n    margin-bottom: 10px;\\r\\n    font-size: 11px;', 0)",
						'87' => "(87, 8, 'Cart item input form element', 'td.cart-item-atributes input', 'a:0:{}', '', 1, 'border: 1px solid #7BB2E6;', 0)",
						'119' => "(119, 8, 'Data grid row header', 'td.block-data-row-hdr', 'a:0:{}', 'Used in order preview', 2, 'background-color: #eeeeee;\\r\\nborder-bottom: 1px solid #dddddd;\\r\\nborder-top: 1px solid #cccccc;\\r\\nfont-weight: bold;', 48)",
						'82' => "(82, 8, '\"More\" link image', 'a.link-more img', 'a:0:{}', '', 2, 'text-decoration: none;\\r\\npadding-left: 5px;', 64)",
						'63' => "(63, 8, 'Additional info under product description in list', 'td.product-list-additional', 'a:5:{s:5:\"color\";s:7:\"#8B898B\";s:9:\"font-size\";s:4:\"11px\";s:11:\"font-weight\";s:6:\"normal\";s:10:\"border-top\";s:18:\"1px dashed #8B898B\";s:13:\"border-bottom\";s:18:\"1px dashed #8B898B\";}', '', 2, '', 48)",
						'43' => "(43, 8, 'Block', 'table.block', 'a:2:{s:16:\"background-color\";s:7:\"#E3EEF9\";s:6:\"border\";s:17:\"1px solid #64A1DF\";}', 'Block', 1, 'border: 0; \\r\\nmargin-bottom: 1px;\\r\\nwidth: 100%;', 0)",
						'84' => "(84, 8, 'Cart item cell', 'td.cart-item', 'a:0:{}', '', 1, 'background-color: #F6FAFF;\\r\\nborder-left: 1px solid #ffffff;\\r\\nborder-bottom: 1px solid #ffffff;\\r\\npadding: 4px;', 0)",
						'57' => "(57, 8, 'Main table, right column', 'td.main-column-right', 'a:0:{}', '', 1, 'width:220px;\\r\\nborder: 1px solid #62A1DE;\\r\\nborder-top: 0px;', 0)",
						'161' => "(161, 8, 'Block for sub categories', 'td.block-data-subcats', 'a:0:{}', '', 2, '  background: #FFFFFF\\r\\nurl(/in-commerce4/themes/default/in-commerce/img/bgr_categories.jpg);\\r\\n  background-repeat: no-repeat;\\r\\n  background-position: top right;\\r\\nborder-bottom: 5px solid #DEEAFF;\\r\\npadding-left: 10px;', 48)",
						'77' => "(77, 8, 'Left block header', 'td.left-block-header', 'a:0:{}', '', 2, 'font-family : verdana, helvetica, sans-serif;\\r\\ncolor : #ffffff;\\r\\nfont-size : 12px;\\r\\nfont-weight : bold;\\r\\ntext-decoration : none;\\r\\nbackground-color: #64a1df;\\r\\npadding: 5px;\\r\\npadding-left: 7px;', 42)",
						'80' => "(80, 8, 'Right block data - text', 'td.right-block-data td', 'a:1:{s:9:\"font-size\";s:5:\"11px;\";}', '', 2, '', 48)",
						'53' => "(53, 8, 'Right block header', 'td.right-block-header', 'a:0:{}', '', 2, 'font-family : verdana, helvetica, sans-serif;\\r\\ncolor : #ffffff;\\r\\nfont-size : 12px;\\r\\nfont-weight : bold;\\r\\ntext-decoration : none;\\r\\nbackground-color: #64a1df;\\r\\npadding: 5px;\\r\\npadding-left: 7px;', 42)",
						'85' => "(85, 8, 'Cart item cell with attributes', 'td.cart-item-attributes', 'a:0:{}', '', 1, 'background-color: #E6EEFF;\\r\\nborder-left: 1px solid #ffffff;\\r\\nborder-bottom: 1px solid #ffffff;\\r\\npadding: 4px;\\r\\ntext-align: center;\\r\\nvertical-align: middle;\\r\\nfont-size: 12px;\\r\\nfont-weight: normal;', 0)",
						'86' => "(86, 8, 'Cart item cell with name', 'td.cart-item-name', 'a:0:{}', '', 1, 'background-color: #F6FAFF;\\r\\nborder-left: 1px solid #ffffff;\\r\\nborder-bottom: 1px solid #ffffff;\\r\\npadding: 3px;', 0)",
						'47' => "(47, 8, 'Block content of featured product', 'td.featured-block-data', 'a:0:{}', '', 1, 'font-family: Arial,Helvetica,sans-serif;\\r\\nfont-size: 12px;', 0)",
						'56' => "(56, 8, 'Main table, middle column', 'td.main-column-center', 'a:0:{}', '', 1, '\\r\\n', 0)",
						'50' => "(50, 8, 'Product title link in list', 'a.link-product2', 'a:0:{}', 'Product title link', 1, 'color: #62A1DE;\\r\\nfont-size: 12px;\\r\\nfont-weight: bold;\\r\\ntext-decoration: none;\\r\\n\\r\\n', 0)",
						'71' => "(71, 8, 'Main path block', 'table.main-path', 'a:0:{}', '', 1, 'background: #61b0ec url(\"/in-commerce4/themes/default/img/bgr_path.jpg\") repeat-y scroll left top;\\r\\nwidth: 100%;\\r\\nmargin-bottom: 1px;\\r\\nmargin-right: 1px; \\r\\nmargin-left: 1px;', 0)",
						'62' => "(62, 8, 'Block: columns header for data table', 'table.block-no-border th', 'a:6:{s:4:\"font\";s:28:\"Arial, Helvetica, sans-serif\";s:5:\"color\";s:7:\"#18559C\";s:9:\"font-size\";s:4:\"11px\";s:11:\"font-weight\";s:4:\"bold\";s:16:\"background-color\";s:7:\"#B4D2EE\";s:7:\"padding\";s:3:\"6px\";}', '', 1, 'text-align: left;', 0)",
						'59' => "(59, 8, 'Block without border', 'table.block-no-border', 'a:0:{}', '', 1, 'border: 0px; \\r\\nmargin-bottom: 10px;\\r\\nwidth: 100%;', 0)",
						'74' => "(74, 8, 'Main path language selector cell', 'td.main-path-language', 'a:0:{}', '', 1, 'vertical-align: middle;\\r\\ntext-align: right;\\r\\npadding-right: 6px;', 0)",
						'171' => "(171, 8, 'Calendar''s highlighted day', '.calendar tbody .hilite', 'a:0:{}', '', 1, 'background-color: #f6f6f6;\\r\\nborder: 1px solid #83B2C5 !important;', 0)",
						'175' => "(175, 8, 'Calendar''s days', '.calendar tbody .day', 'a:0:{}', '', 1, 'text-align: right;\\r\\npadding: 2px 4px 2px 2px;\\r\\nwidth: 2em;\\r\\nborder: 1px solid #fefefe;', 0)",
						'170' => "(170, 8, 'Calendar''s weekends', '.calendar .weekend', 'a:0:{}', '', 1, 'color: #990000;', 0)",
						'173' => "(173, 8, 'Calendar''s control buttons', '.calendar .calendar_button', 'a:0:{}', '', 1, 'color: black;\\r\\nfont-size: 12px;\\r\\nbackground-color: #eeeeee;', 0)",
						'174' => "(174, 8, 'Calendar''s day names', '.calendar thead .name', 'a:0:{}', '', 1, 'background-color: #DEEEF6;\\r\\nborder-bottom: 1px solid #000000;', 0)",
						'172' => "(172, 8, 'Calendar''s top and bottom titles', '.calendar .title', 'a:0:{}', '', 1, 'color: #FFFFFF;\\r\\nbackground-color: #62A1DE;\\r\\nborder: 1px solid #107DC5;\\r\\nborder-top: 0px;\\r\\npadding: 1px;', 0)",
						'60' => "(60, 8, 'Block header for featured product', 'td.featured-block-header', 'a:0:{}', '', 2, '\\r\\n', 42)",
						'54' => "(54, 8, 'Right block', 'table.right-block', 'a:0:{}', '', 2, 'background-color: #E3EEF9;\\r\\nborder: 0px;\\r\\nwidth: 100%;', 43)",
						'44' => "(44, 8, 'Block content', 'td.block-data-big', 'a:0:{}', 'Block content', 1, '  background: #DEEEF6\\r\\nurl(/in-commerce4/themes/default/img/menu_bg.gif);\\r\\n  background-repeat: no-repeat;\\r\\n  background-position: top right;\\r\\n', 0)",
					),
				),

				'Stylesheets' => Array (
					'UniqueField' => 'StylesheetId',
					'Records' => Array (
						'8' => "(8, 'Default', 'In-Portal Default Theme', '', 1124952555, 1)",
					),
				),

				'Counters' => Array (
					'UniqueField' => 'Name',
					'Records' => Array (
						'members_count' => "(DEFAULT, 'members_count', 'SELECT COUNT(*) FROM <%PREFIX%>PortalUser WHERE Status = 1', NULL , NULL , '3600', '0', '|PortalUser|')",
						'members_online' => "(DEFAULT, 'members_online', 'SELECT COUNT(*) FROM <%PREFIX%>UserSession WHERE PortalUserId > 0', NULL , NULL , '3600', '0', '|UserSession|')",
						'guests_online' => "(DEFAULT, 'guests_online', 'SELECT COUNT(*) FROM <%PREFIX%>UserSession WHERE PortalUserId <= 0', NULL , NULL , '3600', '0', '|UserSession|')",
						'users_online' => "(DEFAULT, 'users_online', 'SELECT COUNT(*) FROM <%PREFIX%>UserSession', NULL , NULL , '3600', '0', '|UserSession|')",
					),
				),
			);

			// check & insert if not found defined before data
			foreach ($data as $table_name => $table_info) {
				$unique_fields = explode(';', $table_info['UniqueField']);
				foreach ($table_info['Records'] as $unique_value => $insert_sql) {
					$unique_values = explode(';', $unique_value);

					$where_clause = Array ();
					foreach ($unique_fields as $field_index => $unique_field) {
						$where_clause[] = $unique_field . ' = ' . $this->Conn->qstr($unique_values[$field_index]);
					}

					$sql = 'SELECT ' . implode(', ', $unique_fields) . '
							FROM ' . TABLE_PREFIX . $table_name . '
							WHERE (' . implode(') AND (', $where_clause) . ')';
					$found = $this->Conn->GetRow($sql);
					if ($found) {
						$found = implode(';', $found);
					}

					if ($found != $unique_value) {
						$this->Conn->Query('INSERT INTO ' . TABLE_PREFIX . $table_name . ' VALUES ' . $insert_sql);
					}
				}
			}
		}

		/**
		 * Removes duplicate phrases per language basis (created during proj-base and in-portal shared installation)
		 *
		 */
		function _removeDuplicatePhrases()
		{
			$id_field = $this->Application->getUnitOption('phrases', 'IDField');
			$table_name = $this->Application->getUnitOption('phrases', 'TableName');

			$sql = 'SELECT LanguageId, Phrase, MIN(LastChanged) AS LastChanged, COUNT(*) AS DupeCount
					FROM ' . $table_name . '
					GROUP BY LanguageId, Phrase
					HAVING COUNT(*) > 1';
			$duplicate_phrases = $this->Conn->Query($sql);

			foreach ($duplicate_phrases as $phrase_record) {
				// 1. keep phrase, that was added first, because it is selected in PhrasesCache::LoadPhraseByLabel
				$where_clause = Array (
					'LanguageId = ' . $phrase_record['LanguageId'],
					'Phrase = ' . $this->Conn->qstr($phrase_record['Phrase']),
					'LastChanged' . ' = ' . $phrase_record['LastChanged'],
				);

				$sql = 'SELECT ' . $id_field . '
						FROM ' . $table_name . '
						WHERE (' . implode(') AND (', $where_clause) . ')';
				$phrase_id = $this->Conn->GetOne($sql);

				// 2. delete all other duplicates
				$where_clause = Array (
					'LanguageId = ' . $phrase_record['LanguageId'],
					'Phrase = ' . $this->Conn->qstr($phrase_record['Phrase']),
					$id_field . ' <> ' . $phrase_id,
				);

				$sql = 'DELETE FROM ' . $table_name . '
						WHERE (' . implode(') AND (', $where_clause) . ')';
				$this->Conn->Query($sql);
			}
		}

		function _moveDatabaseFolders()
		{
			// Tables: PageContent, Images
			if ($this->Conn->TableFound('PageContent', true)) {
				// 1. replaces "/kernel/user_files/" references in content blocks
				/** @var kMultiLanguageHelper $ml_helper */
				$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

				$languages = $ml_helper->getLanguages();
				$replace_sql = '%1$s = REPLACE(%1$s, "/kernel/user_files/", "/system/user_files/")';

				$update_sqls = Array ();

				foreach ($languages as $language_id) {
					$update_sqls[] = sprintf($replace_sql, 'l' . $language_id . '_Content');
				}

				if ($update_sqls) {
					$sql = 'UPDATE ' . TABLE_PREFIX . 'PageContent
							SET ' . implode(', ', $update_sqls);
					$this->Conn->Query($sql);
				}
			}

			// 2. replace path of images uploaded via "Images" tab of category items
			$this->_replaceImageFolder('/kernel/images/', '/system/images/');

			// 3. replace path of images uploaded via "Images" tab of category items (when badly formatted)
			$this->_replaceImageFolder('kernel/images/', 'system/images/');

			// 4. replace images uploaded via "In-Bulletin -> Emoticons" section
			$this->_replaceImageFolder('in-bulletin/images/emoticons/', 'system/images/emoticons/');

			// 5. update backup path in config
			$this->_toolkit->saveConfigValues(
				Array (
					'Backup_Path' => FULL_PATH . '/system/backupdata'
				)
			);
		}

		/**
		 * Replaces mentions of "/kernel/images" folder in Images table
		 *
		 * @param string $from
		 * @param string $to
		 */
		function _replaceImageFolder($from, $to)
		{
			$replace_sql = '%1$s = REPLACE(%1$s, "' . $from . '", "' . $to . '")';
			$sql = 'UPDATE ' . TABLE_PREFIX . 'Images
					SET ' . sprintf($replace_sql, 'ThumbPath') . ', ' . sprintf($replace_sql, 'LocalPath');
			$this->Conn->Query($sql);
		}


		/**
		 * Update colors in skin (only if they were not changed manually)
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_0_0($mode)
		{
			if ($mode == 'before') {
				$this->_removeDuplicatePhrases(); // because In-Commerce & In-Link share some phrases with Proj-CMS
				$this->_createProjCMSTables();
				$this->_addMissingConfigurationVariables();
			}

			if ($mode == 'after') {
				$this->_fixSkinColors();
				$this->_restructureCatalog();
				$this->_sortImages();

//				$this->_sortConfigurationVariables('In-Portal', 'in-portal:configure_general');
//				$this->_sortConfigurationVariables('In-Portal', 'in-portal:configure_advanced');
			}
		}

		function _sortConfigurationVariables($module, $section)
		{
			$sql = 'SELECT ca.heading, cv.VariableName
					FROM ' . TABLE_PREFIX . 'ConfigurationAdmin ca
					LEFT JOIN ' . TABLE_PREFIX . 'ConfigurationValues cv USING(VariableName)
					WHERE (cv.ModuleOwner = ' . $this->Conn->qstr($module) . ') AND (cv.Section = ' . $this->Conn->qstr($section) . ')
					ORDER BY ca.DisplayOrder asc, ca.GroupDisplayOrder asc';
			$variables = $this->Conn->GetCol($sql, 'VariableName');

			if (!$variables) {
				return ;
			}

			$variables = $this->_groupRecords($variables);

			$group_number = 0;
			$variable_order = 1;

			$prev_heading = '';

			foreach ($variables as $variable_name => $variable_heading) {
				if ($prev_heading != $variable_heading) {
					$group_number++;
					$variable_order = 1;
				}

				$sql = 'UPDATE ' . TABLE_PREFIX . 'ConfigurationAdmin
						SET DisplayOrder = ' . $this->Conn->qstr($group_number * 10 + $variable_order / 100) . '
						WHERE VariableName = ' . $this->Conn->qstr($variable_name);
				$this->Conn->Query($sql);

				$variable_order++;
				$prev_heading = $variable_heading;
			}
		}

		/**
		 * Group list records by header, saves internal order in group
		 *
		 * @param Array $variables
		 * @return Array
		 */
		function _groupRecords($variables)
		{
			$sorted = Array();

			foreach ($variables as $variable_name => $variable_heading) {
				$sorted[$variable_heading][] = $variable_name;
			}

			$variables = Array();
			foreach ($sorted as $heading => $heading_records) {
				foreach ($heading_records as $variable_name) {
					$variables[$variable_name] = $heading;
				}
			}

			return $variables;
		}

		/**
		 * Returns module root category
		 *
		 * @param string $module_name
		 * @param string $module_prefix
		 * @return int
		 */
		function _getRootCategory($module_name, $module_prefix)
		{
			// don't cache anything here (like in static variables), because database value is changed on the fly !!!
			$sql = 'SELECT RootCat
					FROM ' . TABLE_PREFIX . 'Modules
					WHERE LOWER(Name) = ' . $this->Conn->qstr( strtolower($module_name) );
			$root_category = $this->Conn->GetOne($sql);

			// put to cache too, because CategoriesEventHandler::_prepareAutoPage uses kApplication::findModule
			$this->Application->ModuleInfo[$module_name]['Name'] = $module_name;
			$this->Application->ModuleInfo[$module_name]['RootCat'] = $root_category;
			$this->Application->ModuleInfo[$module_name]['Var'] = $module_prefix;

			return $root_category;
		}

		/**
		 * Move all categories (except "Content") from "Home" to "Content" category and hide them from menu
		 *
		 */
		function _restructureCatalog()
		{
			$root_category = $this->_getRootCategory('Core', 'adm');

			$sql = 'SELECT CategoryId
					FROM ' . TABLE_PREFIX . 'Category
					WHERE ParentId = 0 AND CategoryId <> ' . $root_category;
			$top_categories = $this->Conn->GetCol($sql);

			if ($top_categories) {
				// hide all categories located outside "Content" category from menu
				$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
						SET IsMenu = 0
						WHERE (ParentPath LIKE "|' . implode('|%") OR (ParentPath LIKE "|', $top_categories) . '|%")';
				$this->Conn->Query($sql);

				// move all top level categories under "Content" category and make them visible in menu
				$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
						SET IsMenu = 1, ParentId = ' . $root_category . '
						WHERE ParentId = 0 AND CategoryId <> ' . $root_category;
				$this->Conn->Query($sql);
			}

			// make sure, that all categories have valid value for Priority field

			/** @var kPriorityHelper $priority_helper */
			$priority_helper = $this->Application->recallObject('PriorityHelper');

			$event = new kEvent('c:OnListBuild');

			// update all categories, because they are all under "Content" category now
			$sql = 'SELECT CategoryId
					FROM ' . TABLE_PREFIX . 'Category';
			$categories = $this->Conn->GetCol($sql);

			foreach ($categories as $category_id) {
				$priority_helper->recalculatePriorities($event, 'ParentId = ' . $category_id);
			}

			// create initial theme structure in Category table
			$this->_toolkit->rebuildThemes();

			// make sure, that all system templates have ThemeId set (only possible during platform project upgrade)
			$sql = 'SELECT ThemeId
					FROM ' . TABLE_PREFIX . 'Theme
					WHERE PrimaryTheme = 1';
			$primary_theme_id = $this->Conn->GetOne($sql);

			if ($primary_theme_id) {
				$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
						SET ThemeId = ' . $primary_theme_id . '
						WHERE IsSystem = 1 AND ThemeId = 0';
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Changes skin colors to match new ones (only in case, when they match default values)
		 *
		 */
		function _fixSkinColors()
		{
			/** @var kDBItem $skin */
			$skin = $this->Application->recallObject('skin', null, Array ('skip_autoload' => 1));

			$skin->Load(1, 'IsPrimary');
			if ($skin->isLoaded()) {
				$skin_options = unserialize( $skin->GetDBField('Options') );

				$changes = Array (
					// option: from -> to
					'HeadBgColor' => Array ('#1961B8', '#007BF4'),
					'HeadBarColor' => Array ('#FFFFFF', '#000000'),

					'HeadColor' => Array ('#CCFF00', '#FFFFFF'),
					'TreeColor' => Array ('#006F99', '#000000'),
					'TreeHoverColor' => Array ('', '#009FF0'),
					'TreeHighHoverColor' => Array ('', '#FFFFFF'),
					'TreeHighBgColor' => Array ('#4A92CE', '#4A92CE'),
					'TreeBgColor' => Array ('#FFFFFF', '#DCECF6'),
				);

				$can_change = true;
				foreach ($changes as $option_name => $change) {
					list ($change_from, $change_to) = $change;

					$can_change = $can_change && ($change_from == $skin_options[$option_name]['Value']);
					if ($can_change) {
						$skin_options[$option_name]['Value'] = $change_to;
					}
				}

				if ($can_change) {
					$skin->SetDBField('Options', serialize($skin_options));
					$skin->Update();

					/** @var SkinHelper $skin_helper */
					$skin_helper = $this->Application->recallObject('SkinHelper');

					$skin_file = $skin_helper->getSkinPath();
					if (file_exists($skin_file)) {
						unlink($skin_file);
					}
				}
			}
		}

		/**
		 * 1. Set root category not to generate filename automatically and hide it from catalog
		 * 2. Hide root category of In-Edit and set it's fields
		 *
		 * @param int $category_id
		 */
		function _resetRootCategory($category_id)
		{
			$fields_hash = Array (
				'l1_Name' => 'Content', 'Filename' => 'Content', 'AutomaticFilename' => 0,
				'l1_Description' => 'Content', 'Status' => 4,
			);

			$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'Category', 'CategoryId = ' . $category_id);
		}

		function _createProjCMSTables()
		{
			// 0. make sure, that Content category exists
			$root_category = $this->_getRootCategory('Proj-CMS', 'st');
			if ($root_category) {
				// proj-cms module found -> remove it
				$sql = 'DELETE FROM ' . TABLE_PREFIX . 'Modules
						WHERE Name = "Proj-CMS"';
				$this->Conn->Query($sql);
				unset($this->Application->ModuleInfo['Proj-CMS']);

				$this->_resetRootCategory($root_category);

				// unhide all structure categories
				$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
						SET Status = 1
						WHERE (Status = 4) AND (CategoryId <> ' . $root_category . ')';
				$this->Conn->Query($sql);
			} else {
				$root_category = $this->_getRootCategory('In-Edit', 'cms');
				if ($root_category) {
					// in-edit module found -> remove it
					$sql = 'DELETE FROM ' . TABLE_PREFIX . 'Modules
							WHERE Name = "In-Edit"';
					$this->Conn->Query($sql);
					unset($this->Application->ModuleInfo['In-Edit']);

					$this->_resetRootCategory($root_category);
				}
			}

			if (!$root_category) {
				// create "Content" category when Proj-CMS/In-Edit module was not installed before
				// use direct sql here, because category table structure doesn't yet match table structure in object

				$fields_hash = Array (
					'l1_Name' => 'Content', 'Filename' => 'Content', 'AutomaticFilename' => 0,
					'l1_Description' => 'Content', 'Status' => 4,
				);

				$this->Conn->doInsert($fields_hash, TABLE_PREFIX . 'Category');

				$root_category = $this->Conn->getInsertID();
			}

			$this->_toolkit->deleteCache();
			$this->_toolkit->SetModuleRootCategory('Core', $root_category);

			// 1. process "Category" table
			$structure = $this->Conn->Query('DESCRIBE ' . TABLE_PREFIX . 'Category', 'Field');
			if (!array_key_exists('Template', $structure)) {
				// fields from "Pages" table were not added to "Category" table (like before "Proj-CMS" module install)
				$sql = "ALTER TABLE " . TABLE_PREFIX . "Category
							ADD COLUMN Template varchar(255) default NULL,
							ADD COLUMN l1_Title varchar(255) default '',
							ADD COLUMN l2_Title varchar(255) default '',
							ADD COLUMN l3_Title varchar(255) default '',
							ADD COLUMN l4_Title varchar(255) default '',
							ADD COLUMN l5_Title varchar(255) default '',
							ADD COLUMN l1_MenuTitle varchar(255) NOT NULL default '',
							ADD COLUMN l2_MenuTitle varchar(255) NOT NULL default '',
							ADD COLUMN l3_MenuTitle varchar(255) NOT NULL default '',
							ADD COLUMN l4_MenuTitle varchar(255) NOT NULL default '',
							ADD COLUMN l5_MenuTitle varchar(255) NOT NULL default '',
							ADD COLUMN MetaTitle text,
							ADD COLUMN IndexTools text,
							ADD COLUMN IsIndex tinyint(1) NOT NULL default '0',
							ADD COLUMN IsMenu TINYINT(4) NOT NULL DEFAULT '1',
							ADD COLUMN IsSystem tinyint(4) NOT NULL default '0',
							ADD COLUMN FormId int(11) default NULL,
							ADD COLUMN FormSubmittedTemplate varchar(255) default NULL,
							ADD COLUMN l1_Translated tinyint(4) NOT NULL default '0',
							ADD COLUMN l2_Translated tinyint(4) NOT NULL default '0',
							ADD COLUMN l3_Translated tinyint(4) NOT NULL default '0',
							ADD COLUMN l4_Translated tinyint(4) NOT NULL default '0',
							ADD COLUMN l5_Translated tinyint(4) NOT NULL default '0',
							ADD COLUMN FriendlyURL varchar(255) NOT NULL default '',
							ADD INDEX IsIndex (IsIndex),
							ADD INDEX l1_Translated (l1_Translated),
							ADD INDEX l2_Translated (l2_Translated),
							ADD INDEX l3_Translated (l3_Translated),
							ADD INDEX l4_Translated (l4_Translated),
							ADD INDEX l5_Translated (l5_Translated)";
				$this->Conn->Query($sql);
			}

			if (array_key_exists('Path', $structure)) {
				$sql = 'ALTER TABLE ' . TABLE_PREFIX . 'Category
						DROP Path';
				$this->Conn->Query($sql);
			}

			// 2. process "PageContent" table
			if ($this->Conn->TableFound(TABLE_PREFIX . 'PageContent', true)) {
				$structure = $this->Conn->Query('DESCRIBE ' . TABLE_PREFIX . 'PageContent', 'Field');
				if (!array_key_exists('l1_Translated', $structure)) {
					$sql = "ALTER TABLE " . TABLE_PREFIX . "PageContent
								ADD COLUMN l1_Translated tinyint(4) NOT NULL default '0',
								ADD COLUMN l2_Translated tinyint(4) NOT NULL default '0',
								ADD COLUMN l3_Translated tinyint(4) NOT NULL default '0',
								ADD COLUMN l4_Translated tinyint(4) NOT NULL default '0',
								ADD COLUMN l5_Translated tinyint(4) NOT NULL default '0'";
					$this->Conn->Query($sql);
				}
			}

			// 3. process "FormFields" table
			if ($this->Conn->TableFound(TABLE_PREFIX . 'FormFields', true)) {
				$structure = $this->Conn->Query('DESCRIBE ' . TABLE_PREFIX . 'FormFields', 'Field');
				if (!$structure['FormId']['Key']) {
					$sql = "ALTER TABLE " . TABLE_PREFIX . "FormFields
								CHANGE Validation Validation TINYINT NOT NULL DEFAULT '0',
								ADD INDEX FormId (FormId),
								ADD INDEX Priority (Priority),
								ADD INDEX IsSystem (IsSystem),
								ADD INDEX DisplayInGrid (DisplayInGrid)";
					$this->Conn->Query($sql);
				}
			}
			else {
				$this->Conn->Query("INSERT INTO " . TABLE_PREFIX . "Permissions VALUES (DEFAULT, 'in-portal:forms.view', 11, 1, 1, 0)");
				$this->Conn->Query("INSERT INTO " . TABLE_PREFIX . "Permissions VALUES (DEFAULT, 'in-portal:forms.add', 11, 1, 1, 0)");
				$this->Conn->Query("INSERT INTO " . TABLE_PREFIX . "Permissions VALUES (DEFAULT, 'in-portal:forms.edit', 11, 1, 1, 0)");
				$this->Conn->Query("INSERT INTO " . TABLE_PREFIX . "Permissions VALUES (DEFAULT, 'in-portal:forms.delete', 11, 1, 1, 0)");
			}

			// 4. process "FormSubmissions" table
			if ($this->Conn->TableFound(TABLE_PREFIX . 'FormSubmissions', true)) {
				$structure = $this->Conn->Query('DESCRIBE ' . TABLE_PREFIX . 'FormSubmissions', 'Field');
				if (!$structure['SubmissionTime']['Key']) {
					$sql = "ALTER TABLE " . TABLE_PREFIX . "FormSubmissions
								ADD INDEX SubmissionTime (SubmissionTime)";
					$this->Conn->Query($sql);
				}
			}
			else {
				$this->Conn->Query("INSERT INTO " . TABLE_PREFIX . "Permissions VALUES (DEFAULT, 'in-portal:submissions.view', 11, 1, 1, 0)");
			}

			// 5. add missing event
			$sql = 'SELECT EventId
					FROM ' . TABLE_PREFIX . 'Events
					WHERE (Event = "FORM.SUBMITTED") AND (Type = 1)';
			$event_id = $this->Conn->GetOne($sql);

			if (!$event_id) {
				$sql = "INSERT INTO " . TABLE_PREFIX . "Events VALUES (DEFAULT, 'FORM.SUBMITTED', NULL, 1, 0, 'Core:Category', 'la_event_FormSubmitted', 1)";
				$this->Conn->Query($sql);
			}

			$sql = 'SELECT EventId
					FROM ' . TABLE_PREFIX . 'Events
					WHERE (Event = "FORM.SUBMITTED") AND (Type = 0)';
			$event_id = $this->Conn->GetOne($sql);

			if (!$event_id) {
				$sql = "INSERT INTO " . TABLE_PREFIX . "Events VALUES (DEFAULT, 'FORM.SUBMITTED', NULL, 1, 0, 'Core:Category', 'la_event_FormSubmitted', 0)";
				$this->Conn->Query($sql);
			}
		}

		function _addMissingConfigurationVariables()
		{
			$variables = Array (
				'cms_DefaultDesign' => Array (
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationAdmin VALUES ('cms_DefaultDesign', 'la_Text_General', 'la_prompt_DefaultDesignTemplate', 'text', NULL, NULL, 10.15, 0, 0)",
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationValues VALUES (DEFAULT, 'cms_DefaultDesign', '/platform/designs/general', 'In-Portal', 'in-portal:configure_categories')",
				),

				'Require_AdminSSL' => Array (
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationAdmin VALUES ('Require_AdminSSL', 'la_Text_Website', 'la_config_RequireSSLAdmin', 'checkbox', '', '', 10.105, 0, 1)",
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationValues VALUES (DEFAULT, 'Require_AdminSSL', '', 'In-Portal', 'in-portal:configure_advanced')",
				),

				'UsePopups' => Array (
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationAdmin VALUES ('UsePopups', 'la_Text_Website', 'la_config_UsePopups', 'radio', '', '1=la_Yes,0=la_No', 10.221, 0, 0)",
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationValues VALUES (DEFAULT, 'UsePopups', '1', 'In-Portal', 'in-portal:configure_advanced')",
				),

				'UseDoubleSorting' => Array (
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationAdmin VALUES ('UseDoubleSorting', 'la_Text_Website', 'la_config_UseDoubleSorting', 'radio', '', '1=la_Yes,0=la_No', 10.222, 0, 0)",
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationValues VALUES (DEFAULT, 'UseDoubleSorting', '0', 'In-Portal', 'in-portal:configure_advanced')",
				),

				'MenuFrameWidth' => Array (
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationAdmin VALUES ('MenuFrameWidth', 'la_title_General', 'la_prompt_MenuFrameWidth', 'text', NULL, NULL, 10.31, 0, 0)",
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationValues VALUES (DEFAULT, 'MenuFrameWidth', 200, 'In-Portal', 'in-portal:configure_advanced')",
				),

				'DefaultSettingsUserId' => Array (
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationAdmin VALUES ('DefaultSettingsUserId', 'la_title_General', 'la_prompt_DefaultUserId', 'text', NULL, NULL, '10.06', '0', '0')",
					"INSERT INTO " . TABLE_PREFIX . "ConfigurationValues VALUES (DEFAULT, 'DefaultSettingsUserId', -1, 'In-Portal:Users', 'in-portal:configure_users')",
				),
			);

			foreach ($variables as $variable_name => $variable_sqls) {
				$sql = 'SELECT VariableId
						FROM ' . TABLE_PREFIX . 'ConfigurationValues
						WHERE VariableName = ' . $this->Conn->qstr($variable_name);
				$variable_id = $this->Conn->GetOne($sql);

				if ($variable_id) {
					continue;
				}

				foreach ($variable_sqls as $variable_sql) {
					$this->Conn->Query($variable_sql);
				}
			}
		}

		/**
		 * Sort images in database (update Priority field)
		 *
		 */
		function _sortImages()
		{
			$sql = 'SELECT *
					FROM ' . TABLE_PREFIX . 'Images
					ORDER BY ResourceId ASC , DefaultImg DESC , ImageId ASC';
			$images = $this->Conn->Query($sql);

			$priority = 0;
			$last_resource_id = false;

			foreach ($images as $image) {
				if ($image['ResourceId'] != $last_resource_id) {
					// each item have own priorities among it's images
					$priority = 0;
					$last_resource_id = $image['ResourceId'];
				}

				if (!$image['DefaultImg']) {
					$priority--;
				}

				$sql = 'UPDATE ' . TABLE_PREFIX . 'Images
						SET Priority = ' . $priority . '
						WHERE ImageId = ' . $image['ImageId'];
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Update to 5.0.1
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_0_1($mode)
		{
			if ($mode == 'after') {
				// delete old events
				$events_to_delete = Array ('CATEGORY.MODIFY', 'CATEGORY.DELETE');

				$sql = 'SELECT EventId
						FROM ' . TABLE_PREFIX . 'Events
						WHERE Event IN ("' . implode('","', $events_to_delete) . '")';
				$event_ids = $this->Conn->GetCol($sql);

				if ($event_ids) {
					$this->_deleteEvents($event_ids);

					$sql = 'DELETE FROM ' . TABLE_PREFIX . 'Phrase
							WHERE Phrase IN ("la_event_category.modify", "la_event_category.delete")';
					$this->Conn->Query($sql);
				}

				// partially delete events
				$sql = 'SELECT EventId
						FROM ' . TABLE_PREFIX . 'Events
						WHERE (Event IN ("CATEGORY.APPROVE", "CATEGORY.DENY")) AND (Type = ' . EmailTemplate::TEMPLATE_TYPE_ADMIN . ')';
				$event_ids = $this->Conn->GetCol($sql);

				if ($event_ids) {
					$this->_deleteEvents($event_ids);
				}
			}
		}

		function _deleteEvents($ids)
		{
			$sql = 'DELETE FROM ' . TABLE_PREFIX . 'EmailMessage
					WHERE EventId IN (' . implode(',', $ids) . ')';
			$this->Conn->Query($sql);

			$sql = 'DELETE FROM ' . TABLE_PREFIX . 'Events
					WHERE EventId IN (' . implode(',', $ids) . ')';
			$this->Conn->Query($sql);
		}

		/**
		 * Update to 5.0.2-B2; Transforms IsIndex field values to SymLinkCategoryId field
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_0_2_B2($mode)
		{
			// 0 - Regular, 1 - Category Index, 2 - Container

			if ($mode == 'before') {
				// fix "Content" category
				$fields_hash = Array (
					'CreatedById' => USER_ROOT,
					'CreatedOn' => time(),
					'ResourceId' => $this->Application->NextResourceId(),
				);

				$category_id = $this->Application->findModule('Name', 'Core', 'RootCat');
				$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'Category', 'CategoryId = ' . $category_id);

				// get all categories, marked as category index
				$sql = 'SELECT ParentPath, CategoryId
						FROM ' . TABLE_PREFIX . 'Category
						WHERE IsIndex = 1';
				$category_indexes = $this->Conn->GetCol($sql, 'CategoryId');

				foreach ($category_indexes as $category_id => $parent_path) {
					$parent_path = explode('|', substr($parent_path, 1, -1));

					// set symlink to $category_id for each category, marked as container in given category path
					$sql = 'SELECT CategoryId
							FROM ' . TABLE_PREFIX . 'Category
							WHERE CategoryId IN (' . implode(',', $parent_path) . ') AND (IsIndex = 2)';
					$category_containers = $this->Conn->GetCol($sql);

					if ($category_containers) {
						$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
								SET SymLinkCategoryId = ' . $category_id . '
								WHERE CategoryId IN (' . implode(',', $category_containers) . ')';
						$this->Conn->Query($sql);
					}

				}
			}

			if ($mode == 'after') {
				// scan theme to fill Theme.TemplateAliases and ThemeFiles.TemplateAlias fields
				$this->_toolkit->rebuildThemes();

				$sql = 'SELECT TemplateAliases, ThemeId
						FROM ' . TABLE_PREFIX . 'Theme
						WHERE (Enabled = 1) AND (TemplateAliases <> "")';
				$template_aliases = $this->Conn->GetCol($sql, 'ThemeId');

				$all_template_aliases = Array (); // reversed alias (from real template to alias)

				foreach ($template_aliases as $theme_id => $theme_template_aliases) {
					$theme_template_aliases = unserialize($theme_template_aliases);

					if (!$theme_template_aliases) {
						continue;
					}

					$all_template_aliases = array_merge($all_template_aliases, array_flip($theme_template_aliases));
				}

				$default_design_replaced = false;
				$default_design = trim($this->Application->ConfigValue('cms_DefaultDesign'), '/');

				foreach ($all_template_aliases as $from_template => $to_alias) {
					// replace default design in configuration variable (when matches alias)
					if ($from_template == $default_design) {
						// specific alias matched
						$sql = 'UPDATE ' . TABLE_PREFIX . 'ConfigurationValues
								SET VariableValue = ' . $this->Conn->qstr($to_alias) . '
								WHERE VariableName = "cms_DefaultDesign"';
						$this->Conn->Query($sql);

						$default_design_replaced = true;
					}

					// replace Category.Template and Category.CachedTemplate fields (when matches alias)
					$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
							SET Template = ' . $this->Conn->qstr($to_alias) . '
							WHERE Template IN (' . $this->Conn->qstr('/' . $from_template) . ',' . $this->Conn->qstr($from_template) . ')';
					$this->Conn->Query($sql);

					$sql = 'UPDATE ' . TABLE_PREFIX . 'Category
							SET CachedTemplate = ' . $this->Conn->qstr($to_alias) . '
							WHERE CachedTemplate IN (' . $this->Conn->qstr('/' . $from_template) . ',' . $this->Conn->qstr($from_template) . ')';
					$this->Conn->Query($sql);
				}

				if (!$default_design_replaced) {
					// in case if current default design template doesn't
					// match any of aliases, then set it to #default_design#
					$sql = 'UPDATE ' . TABLE_PREFIX . 'ConfigurationValues
							SET VariableValue = "#default_design#"
							WHERE VariableName = "cms_DefaultDesign"';
					$this->Conn->Query($sql);
				}

				// replace data in category custom fields used for category item template storage
				/** @var kRewriteUrlProcessor $rewrite_processor */
				$rewrite_processor = $this->Application->recallObject('kRewriteUrlProcessor');

				foreach ($this->Application->ModuleInfo as $module_name => $module_info) {
					$custom_field_id = $rewrite_processor->getItemTemplateCustomField($module_info['Var']);

					if (!$custom_field_id) {
						continue;
					}

					foreach ($all_template_aliases as $from_template => $to_alias) {
						$sql = 'UPDATE ' . TABLE_PREFIX . 'CategoryCustomData
								SET l1_cust_' . $custom_field_id . ' = ' . $this->Conn->qstr($to_alias) . '
								WHERE l1_cust_' . $custom_field_id . ' = ' . $this->Conn->qstr($from_template);
						$this->Conn->Query($sql);
					}
				}
			}
		}

		/**
		 * Update to 5.0.3-B2; Moves CATEGORY.* permission from module root categories to Content category
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_0_3_B2($mode)
		{
			if ($mode == 'before') {
				// get permissions
				$sql = 'SELECT PermissionName
						FROM ' . TABLE_PREFIX . 'PermissionConfig
						WHERE PermissionName LIKE "CATEGORY.%"';
				$permission_names = $this->Conn->GetCol($sql);

				// get groups
				$sql = 'SELECT GroupId
						FROM ' . TABLE_PREFIX . 'PortalGroup';
				$user_groups = $this->Conn->GetCol($sql);
				$user_group_count = count($user_groups);

				// get module root categories
				$sql = 'SELECT RootCat
						FROM ' . TABLE_PREFIX . 'Modules';
				$module_categories = $this->Conn->GetCol($sql);

				$module_categories[] = 0;
				$module_categories = implode(',', array_unique($module_categories));

				$permissions = $delete_permission_ids = Array ();

				foreach ($permission_names as $permission_name) {
					foreach ($user_groups as $group_id) {
						$sql = 'SELECT PermissionId
								FROM ' . TABLE_PREFIX . 'Permissions
								WHERE (Permission = ' . $this->Conn->qstr($permission_name) . ') AND (PermissionValue = 1) AND (GroupId = ' . $group_id . ') AND (`Type` = 0) AND (CatId IN (' . $module_categories . '))';
						$permission_ids = $this->Conn->GetCol($sql);

						if ($permission_ids) {
							if (!array_key_exists($permission_name, $permissions)) {
								$permissions[$permission_name] = Array ();
							}

							$permissions[$permission_name][] = $group_id;
							$delete_permission_ids = array_merge($delete_permission_ids, $permission_ids);
						}
					}
				}

				if ($delete_permission_ids) {
					// here we can delete some of permissions that will be added later
					$sql = 'DELETE FROM ' . TABLE_PREFIX . 'Permissions
							WHERE PermissionId IN (' . implode(',', $delete_permission_ids) . ')';
					$this->Conn->Query($sql);
				}

				$home_category = $this->Application->findModule('Name', 'Core', 'RootCat');

				foreach ($permissions as $permission_name => $permission_groups) {
					// optimize a bit
					$has_everyone = in_array(15, $permission_groups);

					if ($has_everyone || (!$has_everyone && count($permission_groups) == $user_group_count - 1)) {
						// has permission for "Everyone" group OR allowed in all groups except "Everyone" group
						// so remove all other explicitly allowed permissions
						$permission_groups = Array (15);
					}

					foreach ($permission_groups as $group_id) {
						$fields_hash = Array (
							'Permission' => $permission_name,
							'GroupId' => $group_id,
							'PermissionValue' => 1,
							'Type' => 0, // category-based permission,
							'CatId' => $home_category,
						);

						$this->Conn->doInsert($fields_hash, TABLE_PREFIX . 'Permissions');
					}
				}

				/** @var kPermCacheUpdater $updater */
				$updater = $this->Application->makeClass('kPermCacheUpdater');

				$updater->OneStepRun();
			}
		}

		/**
		 * Update to 5.1.0-B1; Makes email message fields multilingual
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_1_0_B1($mode)
		{
			if ( $mode == 'before' ) {
				$this->_renameTables('from');

				// migrate email events
				$table_structure = $this->Conn->Query('DESCRIBE ' . TABLE_PREFIX . 'Events', 'Field');

				if (!array_key_exists('Headers', $table_structure)) {
					$sql = 'ALTER TABLE ' . TABLE_PREFIX . 'Events
							ADD `Headers` TEXT NULL AFTER `ReplacementTags`,
							ADD `MessageType` VARCHAR(4) NOT NULL default "text" AFTER `Headers`';
					$this->Conn->Query($sql);
				}

				// alter here, because kMultiLanguageHelper::createFields
				// method, called after will expect that to be in database
				$sql = 'ALTER TABLE ' . TABLE_PREFIX . 'Events
							ADD AllowChangingSender TINYINT NOT NULL DEFAULT "0" AFTER MessageType ,
							ADD CustomSender TINYINT NOT NULL DEFAULT "0" AFTER AllowChangingSender ,
							ADD SenderName VARCHAR(255) NOT NULL DEFAULT "" AFTER CustomSender ,
							ADD SenderAddressType TINYINT NOT NULL DEFAULT "0" AFTER SenderName ,
							ADD SenderAddress VARCHAR(255) NOT NULL DEFAULT "" AFTER SenderAddressType ,
							ADD AllowChangingRecipient TINYINT NOT NULL DEFAULT "0" AFTER SenderAddress ,
							ADD CustomRecipient TINYINT NOT NULL DEFAULT "0" AFTER AllowChangingRecipient ,
							ADD Recipients TEXT AFTER CustomRecipient,
							ADD INDEX (AllowChangingSender),
							ADD INDEX (CustomSender),
							ADD INDEX (SenderAddressType),
							ADD INDEX (AllowChangingRecipient),
							ADD INDEX (CustomRecipient)';
				$this->Conn->Query($sql);

				// create multilingual fields for phrases and email events
				/** @var kMultiLanguageHelper $ml_helper */
				$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

				$ml_helper->createFields('phrases');
				$ml_helper->createFields('email-template');
				$languages = $ml_helper->getLanguages();

				if ($this->Conn->TableFound(TABLE_PREFIX . 'EmailMessage', true)) {
					/** @var kEmailTemplateHelper $email_template_helper */
					$email_template_helper = $this->Application->recallObject('kEmailTemplateHelper');

					foreach ($languages as $language_id) {
						$sql = 'SELECT EmailMessageId, Template, EventId
								FROM ' . TABLE_PREFIX . 'EmailMessage
								WHERE LanguageId = ' . $language_id;
						$translations = $this->Conn->Query($sql, 'EventId');

						foreach ($translations as $event_id => $translation_data) {
							$parsed = $email_template_helper->parseTemplate($translation_data['Template'], 'html');

							$fields_hash = Array (
								'l' . $language_id . '_Subject' => $parsed['Subject'],
								'l' . $language_id . '_Body' => $parsed['HtmlBody'],
							);

							if ( $parsed['Headers'] ) {
								$fields_hash['Headers'] = $parsed['Headers'];
							}

							$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'Events', 'EventId = ' . $event_id);

							$sql = 'DELETE FROM ' . TABLE_PREFIX . 'EmailMessage
									WHERE EmailMessageId = ' . $translation_data['EmailMessageId'];
							$this->Conn->Query($sql);
						}
					}
				}

				// migrate phrases
				$temp_table = $this->Application->GetTempName(TABLE_PREFIX . 'Phrase');

				$sqls = Array (
					'DROP TABLE IF EXISTS ' . $temp_table,
					'CREATE TABLE ' . $temp_table . ' LIKE ' . TABLE_PREFIX . 'Phrase',
					'ALTER TABLE ' . $temp_table . ' DROP LanguageId, DROP Translation',
					'ALTER IGNORE TABLE ' . $temp_table . ' DROP INDEX LanguageId_2',
					'ALTER TABLE ' . $temp_table . ' DROP PhraseId',
					'ALTER TABLE ' . $temp_table . ' ADD PhraseId INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST',
				);

				foreach ($sqls as $sql) {
					$this->Conn->Query($sql);
				}

				$already_added = Array ();
				$primary_language_id = $this->Application->GetDefaultLanguageId();

				foreach ($languages as $language_id) {
					$sql = 'SELECT Phrase, PhraseKey, Translation AS l' . $language_id . '_Translation, PhraseType, LastChanged, LastChangeIP, Module
							FROM ' . TABLE_PREFIX . 'Phrase
							WHERE LanguageId = ' . $language_id;
					$phrases = $this->Conn->Query($sql, 'Phrase');

					foreach ($phrases as $phrase => $fields_hash) {
						if (array_key_exists($phrase, $already_added)) {
							$this->Conn->doUpdate($fields_hash, $temp_table, 'PhraseId = ' . $already_added[$phrase]);
						}
						else {
							$this->Conn->doInsert($fields_hash, $temp_table);
							$already_added[$phrase] = $this->Conn->getInsertID();
						}
					}

					// in case some phrases were found in this language, but not in primary language -> copy them
					if ($language_id != $primary_language_id) {
						$sql = 'UPDATE ' . $temp_table . '
								SET l' . $primary_language_id . '_Translation = l' . $language_id . '_Translation
								WHERE l' . $primary_language_id . '_Translation IS NULL';
						$this->Conn->Query($sql);
					}
				}

				$this->Conn->Query('DROP TABLE IF EXISTS ' . TABLE_PREFIX . 'Phrase');
				$this->Conn->Query('RENAME TABLE ' . $temp_table . ' TO ' . TABLE_PREFIX . 'Phrase');

				$this->_updateCountryStatesTable();
				$this->_replaceConfigurationValueSeparator();

				// save "config.php" in php format, not ini format as before
				$this->_toolkit->SaveConfig();
			}

			if ($mode == 'after') {
				$this->_transformEmailRecipients();
				$this->_fixSkinColors();
			}
		}

		/**
		 * Makes sure we rename tables to legacy names before doing other upgrades before 5.2.0-B1 upgrade
		 *
		 * @param string $name
		 * @param Array $arguments
		 */
		public function __call($name, $arguments)
		{
			if ( substr($name, 0, 12) == 'Upgrade_5_1_' && $arguments[0] == 'before' ) {
				$this->_renameTables('from');
			}

			if ( substr($name, 0, 13) == 'Upgrade_5_2_0' && $arguments[0] == 'before' ) {
				$this->_renameTables('to');
			}
		}

		/**
		 * Move country/state translations from Phrase to CountryStates table
		 *
		 */
		function _updateCountryStatesTable()
		{
			// refactor StdDestinations table
			$sql = 'RENAME TABLE ' . TABLE_PREFIX . 'StdDestinations TO ' . TABLE_PREFIX . 'CountryStates';
			$this->Conn->Query($sql);

			$sql = 'ALTER TABLE ' . TABLE_PREFIX . 'CountryStates
						CHANGE DestId CountryStateId INT(11) NOT NULL AUTO_INCREMENT,
						CHANGE DestType Type INT(11) NOT NULL DEFAULT \'1\',
						CHANGE DestParentId StateCountryId INT(11) NULL DEFAULT NULL,
						CHANGE DestAbbr IsoCode CHAR(3) NOT NULL DEFAULT \'\',
						CHANGE DestAbbr2 ShortIsoCode CHAR(2) NULL DEFAULT NULL,
						DROP INDEX DestType,
						DROP INDEX DestParentId,
						ADD INDEX (`Type`),
						ADD INDEX (StateCountryId)';
			$this->Conn->Query($sql);

			/** @var kMultiLanguageHelper $ml_helper */
			$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

			$ml_helper->createFields('country-state');
			$languages = $ml_helper->getLanguages();

			foreach ($languages as $language_id) {
				$sub_select = '	SELECT l' . $language_id . '_Translation
								FROM ' . TABLE_PREFIX . 'Phrase
								WHERE Phrase = DestName';

				$sql = 'UPDATE ' . TABLE_PREFIX . 'CountryStates
						SET l' . $language_id . '_Name = (' . $sub_select . ')';
				$this->Conn->Query($sql);
			}

			$sql = 'ALTER TABLE ' . TABLE_PREFIX . 'CountryStates
					DROP DestName';
			$this->Conn->Query($sql);

			$sql = 'DELETE FROM ' . TABLE_PREFIX . 'Phrase
					WHERE Phrase LIKE ' . $this->Conn->qstr('la_country_%') . ' OR Phrase LIKE ' . $this->Conn->qstr('la_state_%');
			$this->Conn->Query($sql);
		}

		/**
		 * Makes configuration values dropdowns use "||" as separator
		 *
		 */
		function _replaceConfigurationValueSeparator()
		{
			/** @var InpCustomFieldsHelper $custom_field_helper */
			$custom_field_helper = $this->Application->recallObject('InpCustomFieldsHelper');

			$sql = 'SELECT ValueList, VariableName
					FROM ' . TABLE_PREFIX . 'ConfigurationAdmin
					WHERE ValueList LIKE "%,%"';
			$variables = $this->Conn->GetCol($sql, 'VariableName');

			foreach ($variables as $variable_name => $value_list) {
				$ret = Array ();
				$options = $custom_field_helper->GetValuesHash($value_list, ',', false);

				foreach ($options as $option_key => $option_title) {
					if (substr($option_key, 0, 3) == 'SQL') {
						$ret[] = $option_title;
					}
					else {
						$ret[] = $option_key . '=' . $option_title;
					}
				}

				$fields_hash = Array (
					'ValueList' => implode(VALUE_LIST_SEPARATOR, $ret),
				);

				$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'ConfigurationAdmin', 'VariableName = ' . $this->Conn->qstr($variable_name));
			}
		}

		/**
		 * Transforms "FromUserId" into Sender* and Recipients columns
		 *
		 */
		function _transformEmailRecipients()
		{
			$sql = 'SELECT FromUserId, Type, EventId
					FROM ' . TABLE_PREFIX . 'Events
					WHERE FromUserId IS NOT NULL AND (FromUserId <> ' . USER_ROOT . ')';
			$events = $this->Conn->Query($sql, 'EventId');

			/** @var MInputHelper $minput_helper */
			$minput_helper = $this->Application->recallObject('MInputHelper');

			foreach ($events as $event_id => $event_data) {
				$sql = 'SELECT Login
						FROM ' . TABLE_PREFIX . 'PortalUser
						WHERE PortalUserId = ' . $event_data['FromUserId'];
				$username = $this->Conn->GetOne($sql);

				if (!$username) {
					continue;
				}

				if ($event_data['Type'] == EmailTemplate::TEMPLATE_TYPE_FRONTEND) {
					// from user
					$fields_hash = Array (
						'CustomSender' => 1,
						'SenderAddressType' => EmailTemplate::ADDRESS_TYPE_USER,
						'SenderAddress' => $username
					);
				}

				if ($event_data['Type'] == EmailTemplate::TEMPLATE_TYPE_ADMIN) {
					// to user
					$records = Array (
						Array (
							'RecipientType' => EmailTemplate::RECIPIENT_TYPE_TO,
							'RecipientName' => '',
							'RecipientAddressType' => EmailTemplate::ADDRESS_TYPE_USER,
							'RecipientAddress' => $username
						)
					);

					$fields_hash = Array (
						'CustomRecipient' => 1,
						'Recipients' => $minput_helper->prepareMInputXML($records, array_keys( reset($records) ))
					);
				}

				$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'Events', 'EventId = ' . $event_id);
			}

			$this->Conn->Query('ALTER TABLE ' . TABLE_PREFIX . 'Events DROP FromUserId');
		}

		/**
		 * Update to 5.1.0; Fixes refferer of form submissions
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_1_0($mode)
		{
			if ( $mode == 'before' ) {
				$this->_renameTables('from');
			}

			if ( $mode == 'after' ) {
				$base_url = $this->Application->BaseURL();

				$sql = 'UPDATE ' . TABLE_PREFIX . 'FormSubmissions
						SET ReferrerURL = REPLACE(ReferrerURL, ' . $this->Conn->qstr($base_url) . ', "/")';
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Update to 5.1.1-B1; Transforms DisplayToPublic logic
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_1_1_B1($mode)
		{
			if ( $mode == 'before' ) {
				$this->_renameTables('from');
			}

			if ($mode == 'after') {
				$this->processDisplayToPublic();
			}
		}

		function processDisplayToPublic()
		{
			$profile_mapping = Array (
				'pp_firstname' => 'FirstName',
				'pp_lastname' => 'LastName',
				'pp_dob' => 'dob',
				'pp_email' => 'Email',
				'pp_phone' => 'Phone',
				'pp_street' => 'Street',
				'pp_city' => 'City',
				'pp_state' => 'State',
				'pp_zip' => 'Zip',
				'pp_country' => 'Country',
			);

			$fields = array_keys($profile_mapping);
			$fields = $this->Conn->qstrArray($fields);
			$where_clause = 'VariableName IN (' . implode(',', $fields) . ')';

			// 1. get user, that have saved their profile at least once
			$sql = 'SELECT DISTINCT PortalUserId
					FROM ' . TABLE_PREFIX . 'PersistantSessionData
					WHERE ' . $where_clause;
			$users = $this->Conn->GetCol($sql);

			foreach ($users as $user_id) {
				// 2. convert to new format
				$sql = 'SELECT VariableValue, VariableName
						FROM ' . TABLE_PREFIX . 'PersistantSessionData
						WHERE (PortalUserId = ' . $user_id . ') AND ' . $where_clause;
				$user_variables = $this->Conn->GetCol($sql, 'VariableName');

				// go through mapping to preserve variable order
				$value = Array ();

				foreach ($profile_mapping as $from_name => $to_name) {
					if (array_key_exists($from_name, $user_variables) && $user_variables[$from_name]) {
						$value[] = $to_name;
					}
				}

				if ($value) {
					$fields_hash = Array (
						'DisplayToPublic' => '|' . implode('|', $value) . '|',
					);

					$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'PortalUser', 'PortalUserId = ' . $user_id);
				}

				// 3. delete old style variables
				$sql = 'DELETE FROM ' . TABLE_PREFIX . 'PersistantSessionData
						WHERE (PortalUserId = ' . $user_id . ') AND ' . $where_clause;
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Update to 5.1.3; Merges column and field phrases
		 *
		 * @param string $mode when called mode {before, after)
		 */
		function Upgrade_5_1_3($mode)
		{
			if ( $mode == 'before' ) {
				$this->_renameTables('from');
			}

			if ( $mode == 'after' ) {
				$this->moveTranslation('LA_COL_', 'LA_FLD_', 'ColumnTranslation');
			}
		}

		/**
		 * Makes sure table names match upgrade script
		 *
		 * @param string $key
		 * @return void
		 * @access private
		 */
		private function _renameTables($key)
		{
			foreach ($this->renamedTables as $prefix => $table_info) {
				$this->Application->setUnitOption($prefix, 'TableName', TABLE_PREFIX . $table_info[$key]);
			}
		}

		/**
		 * Update to 5.2.0-B1; Transform list sortings storage
		 *
		 * @param string $mode when called mode {before, after)
		 */
		public function Upgrade_5_2_0_B1($mode)
		{
			if ( $mode == 'before' ) {
				$this->_renameTables('to');
			}

			if ( $mode == 'after' ) {
				$this->transformSortings();
				$this->moveTranslation('LA_COL_', 'LA_FLD_', 'ColumnTranslation'); // because of "la_col_ItemPrefix" phrase
				$this->moveTranslation('LA_HINT_', 'LA_FLD_', 'HintTranslation');
				$this->moveTranslation('LA_HINT_', 'LA_CONFIG_', 'HintTranslation');
				$this->moveTranslation('LA_HINT_', 'LA_TITLE_', 'HintTranslation');
				$this->createPageRevisions();
			}
		}

		/**
		 * Transforms a way, how list sortings are stored
		 *
		 * @return void
		 */
		function transformSortings()
		{
			$sql = 'SELECT VariableName, PortalUserId
					FROM ' . TABLE_PREFIX . 'UserPersistentSessionData
					WHERE VariableName LIKE "%_Sort1.%"';
			$sortings = $this->Conn->Query($sql);

			foreach ($sortings AS $sorting) {
				if ( !preg_match('/^(.*)_Sort1.(.*)$/', $sorting['VariableName'], $regs) ) {
					continue;
				}

				$user_id = $sorting['PortalUserId'];
				$prefix_special = $regs[1] . '_';
				$view_name = '.' . $regs[2];

				$old_variable_names = Array (
					$prefix_special . 'Sort1' . $view_name, $prefix_special . 'Sort1_Dir' . $view_name,
					$prefix_special . 'Sort2' . $view_name, $prefix_special . 'Sort2_Dir' . $view_name,
				);
				$old_variable_names = $this->Conn->qstrArray($old_variable_names);

				$sql = 'SELECT VariableValue, VariableName
						FROM ' . TABLE_PREFIX . 'UserPersistentSessionData
						WHERE PortalUserId = ' . $user_id . ' AND VariableName IN (' . implode(',', $old_variable_names) . ')';
				$sorting_data = $this->Conn->GetCol($sql, 'VariableName');

				// prepare & save new sortings
				$new_sorting = Array (
					'Sort1' => $sorting_data[$prefix_special . 'Sort1' . $view_name],
					'Sort1_Dir' => $sorting_data[$prefix_special . 'Sort1_Dir' . $view_name],
				);

				if ( isset($sorting_data[$prefix_special . 'Sort2' . $view_name]) ) {
					$new_sorting['Sort2'] = $sorting_data[$prefix_special . 'Sort2' . $view_name];
					$new_sorting['Sort2_Dir'] = $sorting_data[$prefix_special . 'Sort2_Dir' . $view_name];
				}

				$fields_hash = Array (
					'PortalUserId' => $user_id,
					'VariableName' => $prefix_special . 'Sortings' . $view_name,
					'VariableValue' => serialize($new_sorting),
				);

				$this->Conn->doInsert($fields_hash, TABLE_PREFIX . 'UserPersistentSessionData');

				// delete sortings, that were already processed
				$sql = 'DELETE FROM ' . TABLE_PREFIX . 'UserPersistentSessionData
						WHERE PortalUserId = ' . $user_id . ' AND VariableName IN (' . implode(',', $old_variable_names) . ')';
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Merges several phrases into one (e.g. la_col_ + la_hint_ into designated columns of la_fld_ phrases)
		 *
		 * @param string $source_prefix
		 * @param string $target_prefix
		 * @param string $db_column
		 * @return void
		 * @access protected
		 */
		public function moveTranslation($source_prefix, $target_prefix, $db_column)
		{
			$source_phrases = $this->getPhrasesByMask($source_prefix . '%');
			$target_phrases = $this->getPhrasesByMask($target_prefix . '%');

			/** @var kMultiLanguageHelper $ml_helper */
			$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

			$delete_ids = Array ();
			$ml_helper->createFields('phrases');
			$languages = $ml_helper->getLanguages();
			$phrase_table = $this->Application->getUnitOption('phrases', 'TableName');

			foreach ($source_phrases as $phrase_key => $phrase_info) {
				$target_phrase_key = $target_prefix . substr($phrase_key, strlen($source_prefix));

				if ( !isset($target_phrases[$target_phrase_key]) ) {
					continue;
				}

				$fields_hash = Array ();

				// copy column phrase main translation into field phrase column translation
				foreach ($languages as $language_id) {
					$fields_hash['l' . $language_id . '_' . $db_column] = $phrase_info['l' . $language_id . '_Translation'];
				}

				$delete_ids[] = $phrase_info['PhraseId'];
				$this->Conn->doUpdate($fields_hash, $phrase_table, 'PhraseId = ' . $target_phrases[$target_phrase_key]['PhraseId']);
			}

			// delete all column phrases, that were absorbed by field phrases
			if ( $delete_ids ) {
				$sql = 'DELETE FROM ' . $phrase_table . '
						WHERE PhraseId IN (' . implode(',', $delete_ids) . ')';
				$this->Conn->Query($sql);

				$sql = 'DELETE FROM ' . TABLE_PREFIX . 'PhraseCache';
				$this->Conn->Query($sql);
			}
		}

		/**
		 * Returns phrases by mask
		 *
		 * @param string $mask
		 * @return Array
		 * @access protected
		 */
		protected function getPhrasesByMask($mask)
		{
			$sql = 'SELECT *
					FROM ' . $this->Application->getUnitOption('phrases', 'TableName') . '
					WHERE PhraseKey LIKE ' . $this->Conn->qstr($mask);

			return $this->Conn->Query($sql, 'PhraseKey');
		}

		protected function createPageRevisions()
		{
			$sql = 'SELECT DISTINCT PageId
					FROM ' . TABLE_PREFIX . 'PageContent';
			$page_ids = $this->Conn->GetCol($sql);

			foreach ($page_ids as $page_id) {
				$fields_hash = Array (
					'PageId' => $page_id,
					'RevisionNumber' => 1,
					'IsDraft' => 0,
					'FromRevisionId' => 0,
					'CreatedById' => USER_ROOT,
					'CreatedOn' => adodb_mktime(),
					'Status' => STATUS_ACTIVE,
				);

				$this->Conn->doInsert($fields_hash, TABLE_PREFIX . 'PageRevisions');

				$fields_hash = Array (
					'RevisionId' => $this->Conn->getInsertID(),
				);

				$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'PageContent', 'PageId = ' . $page_id);
			}
		}

		/**
		 * Update to 5.2.0-B3; Introduces separate field for plain-text e-mail event translations
		 *
		 * @param string $mode when called mode {before, after)
		 */
		public function Upgrade_5_2_0_B3($mode)
		{
			if ( $mode == 'before' ) {
				$this->_renameTables('to');
			}

			if ( $mode == 'after' ) {
				$this->_splitEmailBody();
				$this->_migrateCommonFooter();
			}
		}

		/**
		 * Splits e-mail body into HTML and Text fields
		 *
		 * @return void
		 * @access private
		 */
		private function _splitEmailBody()
		{
			$id_field = $this->Application->getUnitOption('email-template', 'IDField');
			$table_name = $this->Application->getUnitOption('email-template', 'TableName');
			$fields = $this->Conn->Query('DESCRIBE ' . $table_name, 'Field');

			// The "_renameTables" method doesn't rename IDField, so find real one in DESCRIBE result.
			if ( !isset($fields[$id_field]) ) {
				$id_field = 'EventId';
			}

			if ( !isset($fields['l1_Body']) ) {
				// column dropped - nothing to convert anymore
				return;
			}

			/** @var kMultiLanguageHelper $ml_helper */
			$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

			$languages = $ml_helper->getLanguages();
			$ml_helper->createFields('email-template');

			$sql = 'SELECT *
					FROM ' . $table_name;
			$email_events = $this->Conn->Query($sql);

			// 1. move data to new columns
			foreach ($email_events as $email_event) {
				$fields_hash = Array ();
				$translation_field = $email_event['MessageType'] == 'html' ? 'HtmlBody' : 'PlainTextBody';

				foreach ($languages as $language_id) {
					$fields_hash['l' . $language_id . '_' . $translation_field] = $email_event['l' . $language_id . '_Body'];
				}

				if ( $fields_hash ) {
					$this->Conn->doUpdate($fields_hash, $table_name, $id_field . ' = ' . $email_event[$id_field]);
				}
			}

			// 2. drop old columns
			$drops = Array ('DROP COLUMN MessageType');

			foreach ($languages as $language_id) {
				$lang_field = 'l' . $language_id . '_Body';

				if ( isset($fields[$lang_field]) ) {
					$drops[] = 'DROP COLUMN ' . $lang_field;
				}
			}

			$this->Conn->Query('ALTER TABLE ' . $table_name . ' ' . implode(', ', $drops));
		}

		/**
		 * Transforms COMMON.FOOTER e-mail event into new field in Languages table
		 */
		private function _migrateCommonFooter()
		{
			/** @var kMultiLanguageHelper $ml_helper */
			$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

			$languages = $ml_helper->getLanguages();

			$event_table = $this->Application->getUnitOption('email-template', 'TableName');

			$sql = 'SELECT *
					FROM ' . $event_table . '
					WHERE Event = "COMMON.FOOTER"';
			$footer_data = $this->Conn->GetRow($sql);

			if ( !$footer_data ) {
				return;
			}

			$primary_language_id = $this->Application->GetDefaultLanguageId();
			$table_name = $this->Application->getUnitOption('lang', 'TableName');

			foreach ($languages as $language_id) {
				$is_primary = $language_id == $primary_language_id;

				$fields_hash = Array (
					'HtmlEmailTemplate' => $this->_appendEmailDesignBody($footer_data['l' . $language_id . '_HtmlBody'], $is_primary),
					'TextEmailTemplate' => $this->_appendEmailDesignBody($footer_data['l' . $language_id . '_PlainTextBody'], $is_primary),
				);

				$this->Conn->doUpdate($fields_hash, $table_name, 'LanguageId = ' . $language_id);
			}

			$sql = 'DELETE FROM ' . $event_table . '
					WHERE EventId = ' . $footer_data['EventId'];
			$this->Conn->Query($sql);
		}

		/**
		 * Adds "$body" to given string
		 *
		 * @param string $string
		 * @param bool $is_primary for primary language
		 * @return string
		 * @access private
		 */
		private function _appendEmailDesignBody($string, $is_primary)
		{
			if ( !$string ) {
				return $is_primary ? '$body' : $string;
			}

			return '$body' . "\n" . str_replace(Array ("\r\n", "\r"), "\n", $string);
		}

		/**
		 * Update to 5.2.0-RC1
		 *
		 * @param string $mode when called mode {before, after)
		 */
		public function Upgrade_5_2_0_RC1($mode)
		{
			if ( $mode != 'before' ) {
				return;
			}

			/** @var kMultiLanguageHelper $ml_helper */
			$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');

			// make some promo block fields translatable
			$ml_helper->createFields('promo-block');

			$table_name = $this->Application->getUnitOption('promo-block', 'TableName');
			$table_structure = $this->Conn->Query('DESCRIBE ' . $table_name, 'Field');

			if ( isset($table_structure['Title']) ) {
				$sql = 'UPDATE ' . $table_name . '
						SET l' . $this->Application->GetDefaultLanguageId() . '_Title = Title';
				$this->Conn->Query($sql);

				$sql = 'ALTER TABLE ' . $table_name . ' DROP Title';
				$this->Conn->Query($sql);
			}

			// fix e-mail event translations
			$languages = $ml_helper->getLanguages();
			$table_name = $this->Application->getUnitOption('email-template', 'TableName');

			$change_fields = Array ('Subject', 'HtmlBody', 'PlainTextBody');

			foreach ($languages as $language_id) {
				foreach ($change_fields as $change_field) {
					$change_field = 'l' . $language_id . '_' . $change_field;

					$sql = "UPDATE " . $table_name . "
							SET {$change_field} = REPLACE({$change_field}, '<inp2:m_BaseUrl/>', '<inp2:m_Link template=\"index\"/>')
							WHERE  {$change_field} LIKE '%m_BaseURL%'";
					$this->Conn->Query($sql);
				}
			}

			// add new ml columns to phrases/e-mail events
			$ml_helper->createFields('phrases');
			$ml_helper->createFields('email-template');
		}

		/**
		 * Update to 5.2.0
		 *
		 * @param string $mode when called mode {before, after)
		 */
		public function Upgrade_5_2_0($mode)
		{
			if ( $mode != 'after' ) {
				return;
			}

			$table_name = $this->Application->getUnitOption('c', 'TableName');

			$sql = 'SELECT NamedParentPath, CachedTemplate, CategoryId
					FROM ' . $table_name;
			$categories = $this->Conn->GetIterator($sql);

			foreach ($categories as $category_data) {
				$fields_hash = Array (
					'NamedParentPathHash' => kUtil::crc32(mb_strtolower(preg_replace('/^Content\//i', '', $category_data['NamedParentPath']))),
					'CachedTemplateHash' => kUtil::crc32(mb_strtolower($category_data['CachedTemplate'])),
				);

				$this->Conn->doUpdate($fields_hash, $table_name, 'CategoryId = ' . $category_data['CategoryId']);
			}

			$rebuild_mode = $this->Application->ConfigValue('QuickCategoryPermissionRebuild') ? CategoryPermissionRebuild::SILENT : CategoryPermissionRebuild::AUTOMATIC;
			$this->Application->SetConfigValue('CategoryPermissionRebuildMode', $rebuild_mode);

			$sql = 'DELETE FROM ' . TABLE_PREFIX . 'SystemSettings WHERE VariableName = "QuickCategoryPermissionRebuild"';
			$this->Conn->Query($sql);

			$this->_updateScheduledTaskRunSchedule();
		}

		/**
		 * Transforms RunInterval into RunSchedule column for Scheduled Tasks
		 *
		 * @return void
		 * @access protected
		 */
		protected function _updateScheduledTaskRunSchedule()
		{
			// minute hour day_of_month month day_of_week
			$id_field = $this->Application->getUnitOption('scheduled-task', 'IDField');
			$table_name = $this->Application->getUnitOption('scheduled-task', 'TableName');

			$sql = 'SELECT RunInterval, ' . $id_field . '
					FROM ' . $table_name;
			$run_intervals = $this->Conn->GetCol($sql, $id_field);

			$ranges = Array (0 => 'min', 1 => 'hour', 2 => 'day', 3 => 'month');
			$range_values = Array ('min' => 60, 'hour' => 60, 'day' => 24, 'month' => 30);
			$range_masks = Array ('min' => '*/%s * * * *', 'hour' => '0 */%s * * *', 'day' => '0 0 */%s * *', 'month' => '0 0 1 */%s *');

			foreach ($run_intervals as $scheduled_task_id => $interval) {
				$mask_index = 'month';

				foreach ($ranges as $range_index => $range_name) {
					$range_value = $range_values[$range_name];

					if ( $interval >= $range_value ) {
						$interval = ceil($interval / $range_value);
					}
					else {
						$mask_index = $ranges[$range_index - 1];
						break;
					}
				}

				$run_schedule = sprintf($range_masks[$mask_index], $interval);

				if ( $run_schedule == '0 0 */7 * *' ) {
					// once in 7 days = once in a week
					$run_schedule = '0 0 * * 0';
				}

				$run_schedule = preg_replace('/(\*\/1( |$))/', '*\\2', $run_schedule);

				$fields_hash = Array ('RunSchedule' => $run_schedule);
				$this->Conn->doUpdate($fields_hash, $table_name, $id_field . ' = ' . $scheduled_task_id);
			}

			// drop RunInterval column
			$this->Conn->Query('ALTER TABLE ' . $table_name . ' DROP RunInterval');
		}

		/**
		 * Update to 5.2.1-B1
		 *
		 * @param string $mode when called mode {before, after)
		 */
		public function Upgrade_5_2_1_B1($mode)
		{
			if ( $mode != 'after' ) {
				return;
			}

			$this->_updateUserPasswords();
		}

		protected function _updateUserPasswords()
		{
			$user_table = $this->Application->getUnitOption('u', 'TableName');

			$sql = 'SELECT Password, PortalUserId
					FROM ' . $user_table . '
					WHERE PasswordHashingMethod = ' . PasswordHashingMethod::MD5;
			$user_passwords = $this->Conn->GetColIterator($sql, 'PortalUserId');

			if ( !count($user_passwords) ) {
				// no users at all or existing users have converted passwords already
				return;
			}

			kUtil::setResourceLimit();

			/** @var kPasswordFormatter $password_formatter */
			$password_formatter = $this->Application->recallObject('kPasswordFormatter');

			foreach ($user_passwords as $user_id => $user_password) {
				$fields_hash = Array (
					'Password' => $password_formatter->hashPassword($user_password, '', PasswordHashingMethod::MD5_AND_PHPPASS),
					'PasswordHashingMethod' => PasswordHashingMethod::MD5_AND_PHPPASS,
				);

				$this->Conn->doUpdate($fields_hash, TABLE_PREFIX . 'Users', 'PortalUserId = ' . $user_id);
			}
		}

		/**
		 * Update to 5.2.2-B1
		 *
		 * @param string $mode when called mode {before, after)
		 */
		public function Upgrade_5_2_2_B1($mode)
		{
			if ( $mode != 'after' ) {
				return;
			}

			$this->deleteThumbnails();
		}

		/**
		 * Update to 5.2.2-B3
		 *
		 * @param string $mode When called mode {before, after).
		 *
		 * @return void
		 */
		public function Upgrade_5_2_2_B3($mode)
		{
			if ( $mode === 'after' ) {
				$this->migrateExportUserPresets();
				$this->changeSessionKeyFormat();
			}

			if ( $mode != 'before' ) {
				return;
			}

			/** @var kMultiLanguageHelper $ml_helper */
			$ml_helper = $this->Application->recallObject('kMultiLanguageHelper');
			$ml_helper->createFields('page-revision');

			/** @var PageHelper $page_helper */
			$page_helper = $this->Application->recallObject('PageHelper');
			$table_name = TABLE_PREFIX . 'PageRevisions';
			$sql = 'SELECT RevisionId
					FROM ' . $table_name;
			$ids = $this->Conn->GetColIterator($sql);

			foreach ( $ids as $id ) {
				$this->Conn->doUpdate($page_helper->getRevisionContent($id), $table_name, 'RevisionId = ' . $id);
			}
		}

		/**
		 * Deletes folders, containing thumbnails recursively.
		 *
		 * @param string $folder Folder.
		 *
		 * @return void
		 */
		protected function deleteThumbnails($folder = WRITEABLE)
		{
			foreach ( glob($folder . '/*', GLOB_ONLYDIR) as $sub_folder ) {
				if ( $sub_folder === WRITEABLE . '/cache' ) {
					continue;
				}

				if ( basename($sub_folder) === 'resized' ) {
					$files = glob($sub_folder . '/*');
					array_map('unlink', $files);
					rmdir($sub_folder);
				}
				else {
					$this->deleteThumbnails($sub_folder);
				}
			}
		}

		/**
		 * Transforms "export_settings" persistent session variable into "ExportUserPresets" database table.
		 *
		 * @return void
		 */
		protected function migrateExportUserPresets()
		{
			$sql = 'SELECT VariableValue, PortalUserId
			 			FROM ' . TABLE_PREFIX . 'UserPersistentSessionData
			 			WHERE VariableName = "export_settings"';
			$variable_values = $this->Conn->GetColIterator($sql, 'PortalUserId');

			foreach ( $variable_values as $user_id => $variable_value ) {
				if ( !kUtil::IsSerialized($variable_value) ) {
					continue;
				}

				$variable_value = unserialize($variable_value);

				foreach ( $variable_value as $prefix => $prefix_export_settings ) {
					foreach ( $prefix_export_settings as $name => $export_settings ) {
						$fields_hash = array(
							'PortalUserId' => $user_id,
							'ItemPrefix' => $prefix,
							'PresetName' => $name,
							'PresetData' => json_encode($export_settings),
						);
						$this->Conn->doInsert($fields_hash, TABLE_PREFIX . 'ExportUserPresets', 'REPLACE');
					}
				}
			}

			$sql = 'DELETE
					FROM ' . TABLE_PREFIX . 'UserPersistentSessionData
					WHERE VariableName = "export_settings"';
			$this->Conn->Query($sql);
		}

		/**
		 * Transforms the way, how "SessionKey" is stored in the database.
		 *
		 * @return void
		 */
		protected function changeSessionKeyFormat()
		{
			$sql = 'SELECT SessionId, SessionKey
					FROM ' . TABLE_PREFIX . 'UserSessions';
			$session_mapping = $this->Conn->GetCol($sql, 'SessionKey');

			// 1. Replace session key with session id.
			foreach ( $session_mapping as $session_key => $session_id ) {
				// Ignore already converted data.
				if ( !is_numeric($session_key) ) {
					continue;
				}

				$this->Conn->doUpdate(
					array('SessionId' => $session_id),
					TABLE_PREFIX . 'UserSessionData',
					'SessionId = ' . $session_key
				);

				$this->Conn->doUpdate(
					array('SessionId' => $session_id),
					TABLE_PREFIX . 'UserSessionLogs',
					'SessionId = ' . $session_key
				);
			}

			// 2. Rename temporary tables.
			$tables = $this->Conn->GetCol('SHOW TABLES LIKE "%' . TABLE_PREFIX . 'ses_%"');
			$mask_edit_table = '/' . TABLE_PREFIX . 'ses_(.*)_edit_(.*)/';
			$mask_search_table = '/' . TABLE_PREFIX . 'ses_(.*?)_(.*)/';

			foreach ( $tables as $table ) {
				if ( preg_match($mask_edit_table, $table, $rets) || preg_match($mask_search_table, $table, $rets) ) {
					$old_table_name_fragment = $rets[1];
					$new_table_name_fragment = $this->replaceSessionKeyWithSessionId(
						$old_table_name_fragment,
						$session_mapping
					);

					if ( $old_table_name_fragment === $new_table_name_fragment ) {
						continue;
					}

					$new_table = str_replace(
						'_' . $old_table_name_fragment . '_',
						'_' . $new_table_name_fragment . '_',
						$table
					);

					$this->Conn->Query('RENAME TABLE `' . $table . '` TO `' . $new_table . '`');
				}
			}

			// 3. Replace plain-text session key storage with hashed one.
			/** @var SecurityEncrypter $encrypter */
			$encrypter = $this->Application->recallObject('SecurityEncrypter');

			$tables_with_session_keys = array(
				TABLE_PREFIX . 'UserSessions' => array('SessionKey', 'SessionId'),
				TABLE_PREFIX . 'Semaphores' => array('SessionKey', 'SemaphoreId'),
				TABLE_PREFIX . 'UserSessionLogs' => array('SessionKey', 'SessionLogId'),
				TABLE_PREFIX . 'CurlLog' => array('SessionKey', 'LogId'),
				TABLE_PREFIX . 'SystemLog' => array('LogSessionKey', 'LogId'),
			);

			foreach ( $tables_with_session_keys as $table => $fields ) {
				list($session_key_field, $id_field) = $fields;

				$sql = 'SELECT ' . $session_key_field . ', ' . $id_field . '
						FROM ' . $table;
				$table_records = $this->Conn->GetCol($sql, $id_field);

				foreach ( $table_records as $record_id => $session_key ) {
					// Ignore already converted data.
					if ( !is_numeric($session_key) ) {
						continue;
					}

					$this->Conn->doUpdate(
						array($session_key_field => $encrypter->createSignature($session_key)),
						$table,
						$id_field . ' = ' . $record_id
					);
				}
			}
		}

		/**
		 * Replaces session key with session id in the given table name fragment.
		 *
		 * @param string $table_name_fragment Table name fragment.
		 * @param array  $session_mapping     Session mapping.
		 *
		 * @return string
		 */
		protected function replaceSessionKeyWithSessionId($table_name_fragment, array $session_mapping)
		{
			$table_name_fragment_parts = explode('_', $table_name_fragment);
			$session_key = $table_name_fragment_parts[0];

			if ( array_key_exists($session_key, $session_mapping) ) {
				$table_name_fragment_parts[0] = $session_mapping[$session_key];
			}

			return implode('_', $table_name_fragment_parts);
		}

	}