import logging
import os
import sys
import itertools
import urllib2
from collections import defaultdict, namedtuple
from contextlib import closing, contextmanager

import parallels.common.migrator
from parallels.common import run_command, checking, MigrationError
from parallels.common.utils import ssh_utils, migrator_utils, database_utils, windows_utils
from parallels.common.migrator import trace
from parallels.common.utils.yaml_utils import read_yaml, write_yaml
from parallels.common.utils.config_utils import ConfigSection
from parallels.common.migrator_config import is_transfer_resource_limits_enabled
from parallels.common.logging_context import log_context
from parallels.common import version_tuple
from parallels.common.utils.migrator_utils import get_package_scripts_file_path
from parallels.utils import \
		obj, cached, group_by, format_list, safe_string_repr, \
		group_by_id, find_only,\
		partition_list
from parallels.hsphere_migrator.migration_list import HSphereMigrationList
from parallels.hsphere_migrator import resource_limits
from parallels.hsphere_migrator.converter import HSphereEntityConverter
from parallels.hsphere_migrator.content.mail import HSphereCopyMailContent
from parallels.plesks_migrator.infrastructure_checks import checks as  infrastructure_checks
from parallels.common.migrator import Migrator as CommonMigrator
from .backup_agent import BackupAgent
from .remote_script import HsphereRunner
from .billing.backup_agent import BillingBackupAgent, ClientToFetch
from .billing.importer import BillingImporter, PPAData
from .billing.data_model import SSLSubscription, SSLProducts
from parallels.hsphere_migrator.source_mail_server import HSphereSourceMailServer
from parallels.plesks_migrator.infrastructure_checks.checks import check_windows_copy_content_rsync_connection
import parallels.plesks_migrator
import connections
from parallels.hsphere_migrator.workflow import FromHSphereWorkflow
from parallels.hsphere_migrator import hsphere_utils


class Migrator(CommonMigrator):
	logger = logging.getLogger(__name__)

	def __init__(self, config):
		super(Migrator, self).__init__(config)
		self.iis_nodes = connections.read_iis_nodes_settings(config)
		self.mssql_nodes = connections.read_mssql_nodes_settings( config)
		# { vhost_id: config_content } - apache configs for one account,
		# preserved before suspending it, and used to resume the apache sites
		# after they are suspended
		self.preserved_apache_configs = {}
		# remember what accounts were suspended and blanked by migrator, to restore them when rolling back a hsphere user
		self.suspended_accounts = set()
		self.blanked_accounts = set()

	def _get_webmail_ipv4(self):
		""" Skip this method due to we do not transfer DNS for H-Sphere"""
		pass

	def _load_connections_configuration(self):
		return connections.HsphereMigratorConnections(self.config, self._get_target_panel())

	def _create_workflow(self):
		return FromHSphereWorkflow()

	@trace('fetch-source', u'fetch info from source servers')
	def _fetch_source(self, options, overwrite):
		self.logger.info(u"Fetch H-Sphere servers information.")
		backup_filename = self.get_path_to_raw_plesk_backup_by_settings(
			self.conn.hsphere.conn_settings
		)
		if not overwrite and os.path.exists(backup_filename):
			self.logger.info(u"Data files for Source Panel already exist, skip loading")
		else:
			report = checking.Report(u"Fetched Accounts/Subscriptions Issues", None)
			self.logger.info(u"Fetch configuration data from Source Panel")
			with closing(self.conn.hsphere.db()) as cursor, \
				self._get_hsphere_cp_runner() as cp_runner, self.conn.target.main_node_runner() as ppa_runner:
					backup_agent = BackupAgent(
						cursor,
						cp_runner,
						ppa_runner,
						self.iis_nodes,
						self.conn.target.windows_session_dir,
						lambda x: self._get_session_file_path(x),
						lambda x: self._get_hsphere_script_local_path(x),
						report
					)
					backup_agent.make_backup()
					if report.has_errors_or_warnings() and not options.ignore_fetch_source_errors:
						self.print_report(report, "fetch_source_report_tree", show_no_issue_branches=False)
						raise MigrationError(
							u"Unable to continue migration until there are no issues at 'error' or 'warning' level in backup creation step. \n"
							u"Please review report tree above and fix the errors and warnings. You can also use --ignore-fetch-source-errors command-line option if you're sure what you are doing."
						)
					backup_agent.save_backup(backup_filename)

					# converter will need to know whether a subscription is unix-based or windows-based
					# the backup, fetched above, does not have this information
					# so we fetch it separately, for all domains, including the subscriptions' domains
					domain_os_type = {}
					with closing(self.conn.hsphere.db()) as cursor:
						cursor.execute("""
							SELECT
								domains.name, CASE WHEN iis_vhost.id IS NOT NULL THEN 'windows' ELSE 'unix' END
							FROM domains
								JOIN parent_child ON parent_child.child_id = domains.id		-- to select only domains of specified types
								LEFT JOIN parent_child pc_apache ON pc_apache.parent_id = domains.id AND pc_apache.child_type = 9
								LEFT JOIN apache_vhost ON apache_vhost.id = pc_apache.child_id
								LEFT JOIN parent_child pc_iis ON pc_iis.parent_id = domains.id AND pc_iis.child_type = 9
								LEFT JOIN iis_vhost ON iis_vhost.id = pc_iis.child_id
							WHERE
								parent_child.child_type IN (2, 34, 35)
						""")
						# 2 - domain
						# 34 - service domain
						# 35 - third level domain
						# 9 - hosting
						for row in cursor.fetchall():
							domain_os_type[row[0]] = row[1]
					write_yaml(self._get_session_file_path('domain_os_type.yaml'), domain_os_type)

					# We are not able to put first name and last name of a customer to backup separately
					# as there is only one field in Plesk backup format - "contact".
					# However we are able to put these fields separately to POA - as first name and last name.
					# So, we write this information to a separate file that is used in Converter.
					contact_information = {}
					with closing(self.conn.hsphere.db()) as cursor:
						cursor.execute("""
							SELECT 
								users.username, contact_info.name as first_name, contact_info.last_name
							FROM users 
								JOIN user_account ON user_account.user_id = users.id
								JOIN accounts ON accounts.id = user_account.account_id
								JOIN contact_info ON contact_info.id = accounts.ci_id
							ORDER BY accounts.id
						""")
						for row in cursor.fetchall():
							login, first_name, last_name = row
							# Each user can have multiple accounts, each account has its own contact information
							# We use contact information from the first (first by ID) account
							if login not in contact_information: 
								contact_information[login] = {
									'first_name': first_name,
									'last_name': last_name
								}

					write_yaml(self._get_session_file_path('contact_information.yaml'), contact_information)

	def ssh_connect_hsphere(self):
		return ssh_utils.connect(self.source_plesks['hsphere'])

	def _get_rsync(self, source_server, target_server, source_ip=None):
		return self.global_context.rsync_pool.get(
			source=None,
			target=target_server,
			source_ip=source_ip
		)

	def _copy_mail_content_single_subscription(self, migrator_server, subscription, issues):
		return HSphereCopyMailContent().copy_mail(self.global_context, migrator_server, subscription, issues)

	def _get_source_mail_node(self, subscription_name):
		subscription = find_only(
			self._load_ppa_model().iter_all_subscriptions(), 
			lambda s: s.name == subscription_name,
			error_message="Failed to find subscription by name"
		)

		backup = self.load_raw_plesk_backup(
			self.source_plesks[subscription.source]
		)
		backup_subscription = backup.get_subscription(subscription.name)
		if backup_subscription.mailsystem is None:
			return None # subscription has no mail
		ips = backup_subscription.mailsystem.ips
		ip_address = ips.v4 or ips.v6
		if ip_address is None:
			# unexpected situation - consider subscription has no mail
			return None 

		return HSphereSourceMailServer(self.conn, ip_address)

	def _get_hsphere_script_local_path(self, path):
		dirs = [p for p in parallels.hsphere_migrator.__path__]
		assert all(d == dirs[0] for d in dirs)
		root = dirs[0]

		return os.path.join(root, 'scripts', path)


	def _get_domain_dns_server_mapping(self, subscription_domains):
		# mapping in format: domain_name: [dns_server_ip1, dns_server_ip2, ..]
		domain_dns_mapping = defaultdict(list)
		with closing(self.conn.hsphere.db()) as cursor:
			cursor.execute(u"SELECT DISTINCT l_server_id, ip FROM l_server_ips")
			dns_server_id_ip = {id:ip for id, ip in cursor.fetchall()}

			def _is_subdomain(domain_name):
				cursor.execute(u""" SELECT parent_type FROM parent_child 
					JOIN domains ON domains.id = parent_child.parent_id
					WHERE 
						domains.name = '%s'
				""" % domain_name)
				fetch_result = cursor.fetchone()
				if fetch_result is None:
					raise MigrationError(u"Unable to find '%s' domain in Hsphere database." % domain_name)
				return True if fetch_result[0] == 31 else False #31 - Subdomain

			def _get_subdomain_parent_name(subdomain_name):
				cursor.execute(u""" SELECT parent_id, parent_type FROM parent_child
					JOIN domains ON parent_child.child_id = domains.id
					WHERE 
						domains.name = '%s'""" % (subdomain_name))
				parent_id, parent_type = cursor.fetchone()
				if parent_type == 31: # Subdomain of subdomain -> go next cycle
					return _get_subdomain_parent_name(parent_id)
				else:
					cursor.execute(u"SELECT name FROM domains WHERE id = %s" % parent_id)
					return cursor.fetchone()[0]

			def _get_dns_server_ip(domain_name):
				cursor.execute(u"SELECT master, slave1, slave2 FROM dns_zones WHERE name = '%s'" % domain_name)  
				return cursor.fetchone()

			for domain_names_list in subscription_domains.itervalues():
				for domain_name in domain_names_list:
					row_zones = None
					if not _is_subdomain(domain_name):
						row_zones = _get_dns_server_ip(domain_name)
					else:
						parent_name = _get_subdomain_parent_name(domain_name)
						row_zones = _get_dns_server_ip(parent_name)
					if row_zones is not None:
						for row_item in row_zones:
							if row_item != 0 and row_item in dns_server_id_ip.keys():
								domain_dns_mapping[domain_name].append(dns_server_id_ip[row_item])

		# mapping in format: dns_server_ip: [domain_name1, domain_name2, ...]
		dns_domains_mapping = defaultdict(list)
		for domain_name, dns_ips in domain_dns_mapping.iteritems():
			for ip in dns_ips:
				dns_domains_mapping[ip].append(domain_name)

		return dns_domains_mapping

	def _forward_dns(self, root_report):
		self.logger.info(u"Get mapping of subscriptions to PPA DNS servers")

		def _is_ip_local(ssh_runner, server_ip):
			exit_code, _, _ = ssh_runner.sh_unchecked('/sbin/ifconfig | grep %s' % server_ip)
			return True if exit_code == 0 else False

		subscription_domains = defaultdict(list)
		with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
			for subscr in backup.iter_all_subscriptions():
				subscription_domains[subscr.name] = (
					[subscr.name] +
					[site.name for site in backup.iter_sites(subscr.name)] + 
					[alias.name for alias in backup.iter_aliases(subscr.name)]
				)
		dns_domain_mapping = self._get_domain_dns_server_mapping(subscription_domains)
		script_filename = 'zone_forward_set.sh'

		subscription_to_server_map = {}
		for s in subscription_domains:
			ppa_dns_ip = self._get_subscriptions_first_dns_info(s)['ip_address']
			subscription_to_server_map[s] = ppa_dns_ip

		for dns_server in dns_domain_mapping:
			self.logger.info(u"Set DNS forwarding on server '%s' for %d subscription(s)", dns_server, len(dns_domain_mapping[dns_server]))
			try:
				zone_to_server_map = {}
				for domain in dns_domain_mapping[dns_server]:
					if domain not in subscription_domains.keys():
						continue
					zone_to_server_map.update({ d:subscription_to_server_map[domain] for d in subscription_domains[domain] if d in dns_domain_mapping[dns_server]})
				job = '\n'.join(u'%s %s' % (zone, server) for zone, server in zone_to_server_map.iteritems())
				job += '\n'
				with self._get_hsphere_cp_runner() as cp_runner:
					if _is_ip_local(cp_runner, dns_server):
						source_server = self._get_source_node('hsphere')
						source_temp_filepath=source_server.get_session_file_path(script_filename)
						cp_runner.upload_file(self._get_hsphere_script_local_path(script_filename), source_temp_filepath)
						cp_runner.run('chmod', ['+x', source_temp_filepath])
						cp_runner.run(source_temp_filepath, stdin_content=job)
					else:
						hsphere_runner = HsphereRunner(cp_runner, dns_server)
						script_content = self._read_file(self._get_hsphere_script_local_path(script_filename))
						hsphere_runner.run_script(script_content, stdin_content=job)
			except Exception as e:
				self.logger.debug("Exception: ", exc_info=True)
				plain_report = checking.PlainReport(root_report, *self._extract_source_objects_info())
				for subscription in dns_domain_mapping[dns_server]:
					for domain in subscription_domains.get(subscription, []):
						plain_report.add_subscription_issue(
							dns_server, subscription,
							checking.Problem(
								'hsphere_dns_forwarding_issue', checking.Problem.ERROR, 
								u"Failed to configure DNS forwarding for domain '%s': %s" % (domain, unicode(e))
							),
							u"Configure this zone on the source server to be served in slave mode, with the corresponding PPA DNS server as master."
						)

	def _convert_accounts(self, converted_resellers, existing_objects, subscriptions_mapping, customers_mapping, report, options):
		domain_os_type = read_yaml(self._get_session_file_path('domain_os_type.yaml'))
		contact_information = read_yaml(self._get_session_file_path('contact_information.yaml'))
		entity_converter = HSphereEntityConverter(existing_objects, domain_os_type, contact_information,)
		converter = self._get_converter_class()(self.conn.target.panel_admin_password, existing_objects, options, self.multiple_webspaces, entity_converter)
		plain_report = checking.PlainReport(report, *self._extract_source_objects_info())
		converter.convert_plesks(self._get_plesk_infos(), plain_report, subscriptions_mapping, customers_mapping, converted_resellers, self.global_context.password_holder)
		converter.fix_emails_of_aux_users()
		return converter.get_ppa_model()

	def _subscription_is_windows(self, subscription_name, plesk_id=None):
		"""Return True if subscription is windows-based - has IIS web hosting,
		   and False otherwise.
		"""
		domain_os_type = read_yaml(self._get_session_file_path('domain_os_type.yaml'))
		return domain_os_type.get(subscription_name, '') == 'windows'

	####################################### copy-content ####################################
	# H-Sphere differences from Plesk:
	# 1) different paths => different implementation of _list_files_to_copy_windows()
	# 2) different source IP address for each subscription => different implementation of _get_subscription_content_ip()
	# The rest (way to copy files, way to reset permissions on PPA service node) - used from Plesks migrator without change

	def _get_subscription_content_ip(self, sub):
		return self._get_domain_content_properties(sub.name).ip_address

	@cached
	def _get_domain_content_properties(self, domain_name):
		"""Return obj(ip_address, www_root)
		   of the specified domain
		"""

		with closing(self.conn.hsphere.db()) as cursor:
			cursor.execute(u"""
				SELECT 
					apache_vhost.host_id, apache_vhost.dir, iis_vhost.host_id, iis_vhost.dir
				FROM domains
					JOIN parent_child ON parent_child.child_id = domains.id		-- to select only domains of specified types
					LEFT JOIN parent_child pc_apache ON pc_apache.parent_id = domains.id AND pc_apache.child_type = 9
					LEFT JOIN apache_vhost ON apache_vhost.id = pc_apache.child_id
					LEFT JOIN parent_child pc_iis ON pc_iis.parent_id = domains.id AND pc_iis.child_type = 9
					LEFT JOIN iis_vhost ON iis_vhost.id = pc_iis.child_id
				WHERE
					parent_child.child_type IN (2, 31, 34, 35)
					AND domains.name = '%s'
			""" % domain_name)
			# 2 - domain
			# 31 - subdomain
			# 34 - service domain
			# 35 - third level domain
			# 9 - hosting

			row = cursor.fetchone()
			if row is None:
				return None

			host_id = row[0] or row[2]
			if host_id is None:
				return None

			www_root = row[1] or row[3]

			cursor.execute(u"""SELECT ps.ip1 FROM p_server ps JOIN l_server ls ON ls.p_server_id = ps.id WHERE ls.id = %s""" % host_id)
			ip_row = cursor.fetchone()
			if ip_row is None:
				return None
			else:
				ip_address = ip_row[0]

			return obj(ip_address=ip_address, www_root=www_root)

	def _get_domain_mail_server_ip(self, domain_name):
		"""Return the physical IP address of domain's mail server (as opposed to the logical IP of this domain's mailsystem).
		   Useful for any manipulations with content.
		"""
		with closing(self.conn.hsphere.db()) as cursor:
			cursor.execute(u"""
				SELECT ps.ip1
				FROM p_server ps
				JOIN l_server ls ON ls.p_server_id = ps.id
				JOIN mail_services ms ON ms.mail_server = ls.id
				JOIN parent_child pc ON pc.child_id = ms.id
				JOIN domains d ON d.id = pc.parent_id
				WHERE d.name = '%s'"""
			% domain_name)

			ip_row = cursor.fetchone()
			if ip_row is None:
				return None
			else:
				return ip_row[0]

	def _get_src_db_server(self, db, backup):
		if db.dbtype == 'mssql':
			if self.mssql_nodes.has_key(db.host) and self.mssql_nodes[db.host].instance is not None:
				db.password = self.mssql_nodes[db.host].password
				db.login = self.mssql_nodes[db.host].username
				db.host = self.mssql_nodes[db.host].instance
				return db
			return db	# use the db object itself - it has credentials (db user's credentials, but we don't have admin's credentials anyway)
		else:
			return super(Migrator, self)._get_src_db_server(db, backup)

	####################################### restore-hosting ####################################
	def _transfer_ip_access_restrictions(self):
		"""Transfer IP access restriction settings
		"""
		site_infos = []	# [obj(site_name, subscription_name, plesk_id)]
		for plesk_id, subscription_names in self._get_all_windows_subscriptions_by_source().iteritems():
			for subscription_name in subscription_names:
				subscription = self._create_migrated_subscription(subscription_name)
				for site in subscription.converted_backup.iter_domains():
					if site.hosting_type not in ('phosting', 'vrt_hst', 'sub_hst'):
						self.logger.debug(u"Skip transfer of access restriction settings for site '%s' of subscription '%s' as is has no virtual hosting" % (site.name, subscription.name))
						continue

					site_infos.append(obj(site_name=site.name, subscription_name=subscription_name, plesk_id=plesk_id))

		self.logger.info(u"Transfer access restriction settings for %d sites in %d subscriptions" % (len(site_infos), len(group_by(site_infos, lambda si: si.subscription_name))))

		safe = self._get_safe_lazy()
		for subscription_name, subscription_site_infos in group_by(site_infos, lambda si: si.subscription_name).items(): # group by subscriptions just for better progress display
			self.logger.debug(u"Transfer access restriction settings for sites of subscription '%s'" % subscription_name)
			for site_info in subscription_site_infos:
				with safe.try_subscription(site_info.subscription_name, u"Transfer access restriction settings of site '%s'" % site_info.site_name, is_critical=False):
					restrictions = self._get_access_restrictions(site_info.site_name, site_info.plesk_id)
					if restrictions is not None:
						ip_allow, ip_deny = restrictions
						self._get_target_panel_api().set_ip_access_restrictions(site_info.site_name, ip_allow, ip_deny)

	def _get_access_restrictions(self, site_name, plesk_id):
		"""Get the ip-deny and ip-allow addresses of Web Access Control setting of specified site
		"""
		with closing(self.conn.hsphere.db()) as cursor:
			vhost_id = hsphere_utils.get_iis_vhost_id(cursor, site_name)
			if vhost_id is None:	# if domain has no IIS hosting, skip transfer of web access control
				return None

			cursor.execute(u"""
				SELECT wac.id, wac.order_allow_deny
				FROM web_access_control wac
				JOIN parent_child pc_wac ON pc_wac.child_id = wac.id
				WHERE wac.dir = '/' AND pc_wac.parent_id = %s
			""" % vhost_id) 
			row = cursor.fetchone()
			if row is None:
				return None
			(wac_id, default_to_allow) = row

			def extract_wac_entries(cursor):
				entries = []
				for (name, mask) in cursor.fetchall():
					if mask is None or mask == '':
						entry = name
					else:
						entry = u"%s/%s" % (name, mask)
					entries.append(entry)
				return entries

			cursor.execute(u"""
				SELECT name, mask
				FROM web_access_control_entry
				WHERE dir_id = %s AND type = 1		-- IP or subnet. Other possible type is 0 - domain name, we don't transfer it
			""" % wac_id)

			if default_to_allow == 1:
				ip_allow = "*"
				ip_deny = u" ".join(extract_wac_entries(cursor))
			else:	# default_to_allow == 0:
				ip_allow = u" ".join(extract_wac_entries(cursor))
				ip_deny = "*"

			return (ip_allow, ip_deny)

	@contextmanager
	def _get_hsphere_cp_runner(self):
		with self.ssh_connect_hsphere() as hsphere_ssh:
			yield run_command.SSHRunner(hsphere_ssh, "H-Sphere control panel server '%s'" % (self.source_plesks['hsphere'].ip,))

	####################################### check-infrastructure ####################################
	def _check_windows_copy_mssql_db_content(self, checks, report):
		self.logger.info("Checking Microsoft SQL connections between source SQL servers and PPA SQL service nodes")

		def check_function(nodes_pair_info, mssql_connection_data):
			checker = infrastructure_checks.MSSQLConnectionChecker()

			script_filename = 'check_mssql_connection.ps1' 
			local_script_filepath = get_package_scripts_file_path(
				parallels.plesks_migrator, script_filename
			)

			return checker.check(
				nodes_pair_info, 
				mssql_connection_data, 
				local_script_filepath,
				script_filename
			)

		subscriptions = defaultdict(list)

		# XXX H-Sphere migrator uses individual credentials for each database.
		# When checking connections, we want to check each database server once.
		# There is no need to check connection for every database, so we 
		# store credentials of the first database found for the host in the 
		# following dictionary.
		credentials_by_host = dict()

		for database_info in self._list_databases_to_copy():

			source = database_info.source_database_server
			target = database_info.target_database_server

			if source.type() == 'mssql':
				host = windows_utils.get_dbbackup_mssql_host(
					source.host(),
					source.ip()
				)
				if host in credentials_by_host:
					login, password = credentials_by_host[host]
				else:
					login = source.user()
					password = source.password()
					credentials_by_host[host] = (login, password)

				source_mssql_connection_data = infrastructure_checks.MSSQLConnectionData(
					host=host, login=login, password=password
				)
				nodes_pair = infrastructure_checks.NodesPair(
					source=self._get_source_node(source.access_settings.id),
					target=target.panel_server
				)
				subscriptions[(nodes_pair, source_mssql_connection_data)].append(dict(
					db_name=database_info.database_name,
					db_type=source.type(),
					subscription=database_info.subscription_name,
				))
		
		checks.check_mssql_connection(
			subscriptions=subscriptions.items(),
			report=report,
			check_function=check_function
		)

	def _check_windows_copy_web_content_rsync_connections(self, checks, report):
		"""Since we do not have a shell access to the source IIS server,
		   replace standard upload-receive-compare check with
		   attempt to retrieve the list of web files of each subscription with IIS hosting.
		"""
		self.logger.info("Checking rsync connections between source windows servers and PPA IIS service nodes")
		lister = self._get_infrastructure_check_lister()
		subscriptions_to_target = lister.get_subscriptions_to_target_web_node()
		subscriptions_info = self._get_subscriptions_to_check_copy_content_connection('iis', subscriptions_to_target)
		check_windows_copy_content_rsync_connection(subscriptions_info, self._get_rsync, "web", report)

	def _check_unix_copy_web_content_rsync_connections(self, checks, report):
		self.logger.info("Checking rsync/ssh connections between source unix servers and PPA Apache service nodes")
		lister = self._get_infrastructure_check_lister()
		subscriptions_to_target = lister.get_subscriptions_to_target_web_node()
		subscriptions_info = self._get_subscriptions_to_check_copy_content_connection('apache', subscriptions_to_target)
		self._do_check_copy_content_rsync_ssh_connection('apache', subscriptions_info, report)

	def _check_unix_copy_mail_content_rsync_connections(self, checks, report):
		self.logger.info("Checking rsync/ssh connections between source unix servers and PPA Postfix service nodes")
		lister = self._get_infrastructure_check_lister()
		subscriptions_to_target = lister.get_subscriptions_to_target_mail_node()
		subscriptions_info = self._get_subscriptions_to_check_copy_content_connection('mail', subscriptions_to_target)
		self._do_check_copy_content_rsync_ssh_connection('mail', subscriptions_info, report)

	def _check_unix_copy_db_content_scp_connections(self, checks, report):
		self.logger.info("Checking scp/ssh connections between source unix servers and PPA MySQL service nodes")
		subscriptions_to_target_nodes = {}
		for database_info in self._list_databases_to_copy():
			source = database_info.source_database_server
			target = database_info.target_database_server
			if source.type() == 'mysql' and not target.is_windows():
				subscriptions_to_target_nodes[database_info.subscription_name] = target

		subscriptions_info = self._get_subscriptions_to_check_copy_content_connection('mysql', subscriptions_to_target_nodes)
		self._do_check_copy_content_scp_connection('MySQL databases', subscriptions_info, report)

	def _get_subscriptions_to_check_copy_content_connection(self, content_type, subscriptions_to_target_nodes):
		subscriptions_info = []
		with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
			for subscription_name, target_node in subscriptions_to_target_nodes.iteritems():
				backup_sub = backup.get_subscription(subscription_name)
				# filter subscriptions by type, for iis/apache connection checks
				if (
					content_type == 'iis' and not self._subscription_is_windows(subscription_name)
					or content_type == 'apache' and self._subscription_is_windows(subscription_name)
				):
					continue

				# obtain the source ips of to access subscription's content of specified type
				source_ips = []
				sysuser = backup_sub.get_phosting_sysuser_name()
				if content_type in ['iis', 'apache']:
					if sysuser is not None:
						source_ips = [ self._get_domain_content_properties(subscription_name).ip_address ]
				elif content_type == 'mail':
					if backup_sub.mail_service_ips.v4 is not None:
						source_ips = [ backup_sub.mail_service_ips.v4 ]
				elif content_type == 'mysql':
					source_ips = [ db.host for db in backup_sub.get_databases() ]

				for source_ip in source_ips:
					subscriptions_info.append(
						infrastructure_checks.SubscriptionsInfo(
							subscription_name=subscription_name,
							subscription_dir=sysuser,
							target_node=target_node,
							source_ip=source_ip
						)
					)

		return subscriptions_info

	def _do_check_copy_content_scp_connection(self, content_type_description, subscriptions_info, report):
		"""Check connection by creating a temporary file on source and copying it to target.
		"""

		class CmdExecutionError(BaseException):
			def __init__(self, cmd, args, stderr, runner):
				self.cmd = cmd
				self.args = args
				self.stderr = stderr
				self.runner = runner

		def run_check_command(cmd, args, runner):
			exit_code, stdout, stderr = runner.sh_unchecked(cmd, args)
			if exit_code != 0:
				raise CmdExecutionError(cmd, args, stderr, runner)
			else:
				return stdout

		NodesPair = namedtuple('NodesPair', ('target_node', 'source_ip'))
		subscriptions_by_nodes_pair = group_by(subscriptions_info, lambda si: NodesPair(target_node=si.target_node, source_ip=si.source_ip))
		for nodes_info, subscriptions in subscriptions_by_nodes_pair.iteritems():
			target_node = nodes_info.target_node
			with target_node.runner() as runner_target, self._get_hsphere_cp_runner() as cp_runner:
				hsphere_runner = HsphereRunner(cp_runner, nodes_info.source_ip)
				with ssh_utils.public_key_ssh_access_runner(runner_target, hsphere_runner) as key_pathname:

					try:
						filename_src = run_check_command("mktemp /tmp/scp_test_fileXXXXXX", {}, hsphere_runner).split('\n')[0]
						filename_dst = run_check_command("mktemp /tmp/scp_test_fileXXXXXX", {}, runner_target).split('\n')[0]

						run_check_command("scp -i {key} -o StrictHostKeyChecking=no -o GSSAPIAuthentication=no root@{source_ip}:{filename_src} {filename_dst}",
							{
								'key': key_pathname,
								'source_ip': nodes_info.source_ip,
								'filename_src': filename_src,
								'filename_dst': filename_dst,
							},
							runner_target
						)

						run_check_command("rm {filename_src}", {'filename_src': filename_src}, hsphere_runner)
						run_check_command("rm {filename_dst}", {'filename_dst': filename_dst}, runner_target)
					except CmdExecutionError as e:
						report.add_issue(
							checking.Problem('infrastructure-databases', checking.Problem.ERROR,
								u"Unable to check the scp connection between %s and the source server at %s:\n%s\nWhile this problem persists, it may not be possible to copy the %s content of the following subscriptions:%s" % (
									target_node.description(),
									nodes_info.source_ip,
									safe_string_repr("\n".join(e.stderr.strip().split("\n")[-5:])),
									content_type_description,
									format_list([s.subscription_name for s in subscriptions])
								)
							),
							u"""Make sure that:
1) There are no firewall rules blocking SSH connections from the PPA node to the source server:
the incoming connections on the TCP port 22 of the source server are allowed, the outgoing connections on the TCP port 22 of the PPA service node are allowed.
2) The SSH service on the source server is configured to allow logging in as the user 'root'.
3) The SSH service on the source server is running.
4) The program 'scp' is installed on the source server.
5) The following command can be successfully executed on {host}:
{cmd}""".format(
							host=(
								"the source server %s" % nodes_info.source_ip
								if e.runner == hsphere_runner
								else target_node.description()
							),
							cmd=windows_utils.format_command(e.cmd, **e.args))
						)
						break

					report.add_issue(
						checking.Problem('infrastructure-databases', checking.Problem.INFO,
							u"Successfully checked the scp connection between %s and the source server at %s" % (
								target_node.description(), nodes_info.source_ip
							)
						),
						u""
					)

	def _do_check_copy_content_rsync_ssh_connection(self, content_type, subscriptions_info, report):
		"""Check connection by listing content of the directory above the actual subscription related objects.
		"""
		content_type_to_directory = {
			'apache': '/hsphere/local/home/',	# XXX same assumption that is used in copy-content, but wrong. Some - or all - unix users in H-Sphere can live in a different directory.
			'mail': '/hsphere/local/var/vpopmail/domains/',
		}
		assert content_type in content_type_to_directory, "Internal error: request to check the connection for unknown content type: '%s'" % content_type

		issue_id_by_ctype = {
			'apache': 'infrastructure-web',
			'mail': 'infrastructure-mail',
		}

		ctype_description_by_ctype = {
			'apache': 'web',
			'mail': 'mail',
		}

		NodesPair = namedtuple('NodesPair', ('target', 'source_ip'))
		subscriptions_by_nodes_pair = group_by(subscriptions_info, lambda si: NodesPair(target=si.target_node, source_ip=si.source_ip))
		for nodes_info, subscriptions in subscriptions_by_nodes_pair.iteritems():
			with nodes_info.target.runner() as runner_target, self._get_hsphere_cp_runner() as cp_runner:
				hsphere_runner = HsphereRunner(cp_runner, nodes_info.source_ip)
				with ssh_utils.public_key_ssh_access_runner(runner_target, hsphere_runner) as key_pathname:

					cmd = "/usr/bin/rsync -r --list-only -e 'ssh -i {key} -o StrictHostKeyChecking=no -o GSSAPIAuthentication=no' root@{source_ip}:{directory}"
					args = dict(
						directory=content_type_to_directory[content_type],
						key=key_pathname,
						source_ip=nodes_info.source_ip,
					)
					exit_code, _, stderr = runner_target.sh_unchecked(cmd, args)
					if exit_code != 0:
						report.add_issue(
							checking.Problem(issue_id_by_ctype[content_type], checking.Problem.ERROR,
								u"Unable to check the rsync connection between %s and the source server at %s:\n%s\nWhile this problem persists, it may not be possible to copy the %s content of the following subscriptions:%s" % (
									nodes_info.target.description(),
									nodes_info.source_ip,
									safe_string_repr("\n".join(stderr.strip().split("\n")[-5:])),
									ctype_description_by_ctype[content_type],
									format_list([s.subscription_name for s in subscriptions])
								)
							),
							u"""Make sure that:
1) There are no firewall rules blocking SSH connections from the PPA node to the source server:
the incoming connections on the TCP port 22 of the source server are allowed, the outgoing connections on the TCP port 22 of the PPA service node are allowed.
2) The SSH service on the source server is configured to allow logging in as the user 'root'.
3) The SSH service on the source server is running.
4) The program 'rsync' is in the PATH of user 'root' on the source server.
5) The following command can be successfully executed on the PPA service node:
{cmd}""".format(cmd=windows_utils.format_command(cmd, **args))
						)
					else:
						report.add_issue(
							checking.Problem(issue_id_by_ctype[content_type], checking.Problem.INFO,
								u"Successfully checked the rsync connection between %s and the source server at %s" % (
									nodes_info.target.description(),
									nodes_info.source_ip
								)
							),
							u""
						)

	def _get_mysql_disk_usage(self, server_ip, db_names):
		"""Return the dictionary with disk usages: {db_name: disk_usage}. The disk_usage's measurement unit is: byte.
		   If the migration tool was unable to retrieve the disk usage for particular database, this database won't
		   be in the dictionary (as opposed to being there with zero disk usage).
		"""
		db_usages = {}	# { db_name: disk_usage }
		with self._get_hsphere_cp_runner() as cp_runner:
			hsphere_runner = HsphereRunner(cp_runner, server_ip)
			for db_name in db_names:
				cmd = "/hsphere/shared/scripts/mysql-db-size {db_name} {server_ip}"
				args = {
					'db_name': db_name,
					'server_ip': server_ip,
				}
				exit_code, stdout, stderr = hsphere_runner.sh_unchecked(cmd, args)
				if exit_code == 0:
					db_usages[db_name] = int(float(stdout) * 1024 * 1024)	# mysql db size is reported in megabytes, with floating point
				else:
					self.logger.warning("Unable to calculate the size of MySQL database %s on source server at %s:\n%s\nThe migration tool will skip checking available disk space for this database." % (db_name, server_ip, safe_string_repr("\n".join(stderr.strip().split("\n")[-5:]))) )
		return db_usages

	def _get_mssql_disk_usage(self, server_ip, server_port, server_login, server_password, db_names):
		db_usages = {}	# { db_name: disk_usage }
		with self._get_hsphere_cp_runner() as cp_runner:
			for classfile in ['GetDbDiskUsage.class', 'NoCP.class']:
				cp_runner.upload_file(self._get_hsphere_extras_local_path(classfile), "/tmp/%s" % classfile)
			for db_names_chunk in partition_list(db_names, 100):
				cmd = "su - cpanel -c \"java -cp `ls -1 /hsphere/local/home/cpanel/hsphere/WEB-INF/lib/*.jar|tr -s '\n' ':'`/tmp:/hsphere/local/home/cpanel/hsphere/WEB-INF/classes/ GetDbDiskUsage {server_ip} {server_port} {server_login} {server_password} %s\"" % " ".join(db_names_chunk)
				args = {
					'server_ip': server_ip,
					'server_port': server_port,
					'server_login': server_login,
					'server_password': server_password,
				}
				exit_code, stdout, stderr = cp_runner.sh_unchecked(cmd, args)
				if exit_code == 0:
					for line in stdout.strip().split("\n"):
						db_name, usage_str = line.split(" ")
						usage = int(float(usage_str) * 1024 * 1024)	# mssql db size is reported in megabytes
						db_usages[db_name] = usage
				else:
					self.logger.warning("Unable to calculate the size of the following Microsoft SQL databases:\n%s\n on the source server at %s:\n%s\nThe migration tool will skip checking available disk space for these databases." % (" ".join(db_names_chunk), server_ip, safe_string_repr("\n".join(stderr.strip().split("\n")[-5:]))) )
		return db_usages

	def _get_iis_disk_usage(self, server_ip, server_port, server_login, server_password, sysuser_names):
		web_usages = {}	# { sysuser_name: disk_usage }
		with self._get_hsphere_cp_runner() as cp_runner:
			for classfile in ['GetWebDiskUsage.class', 'NoCP.class']:
				cp_runner.upload_file(self._get_hsphere_extras_local_path(classfile), "/tmp/%s" % classfile)
			for sysuser_names_chunk in partition_list(sysuser_names, 100):
				cmd = "su - cpanel -c \"java -cp `ls -1 /hsphere/local/home/cpanel/hsphere/WEB-INF/lib/*.jar|tr -s '\n' ':'`/tmp:/hsphere/local/home/cpanel/hsphere/WEB-INF/classes/ GetWebDiskUsage {server_ip} {server_port} {server_login} {server_password} %s\"" % " ".join(sysuser_names_chunk)
				args = {
					'server_ip': server_ip,
					'server_port': server_port,
					'server_login': server_login,
					'server_password': server_password,
				}
				exit_code, stdout, stderr = cp_runner.sh_unchecked(cmd, args)
				if exit_code == 0:
					for line in stdout.strip().split("\n"):
						sysuser_name, usage_str = line.split(" ")
						usage = int(float(usage_str) * 1024)	# IIS disk usage is reported in kilobytes
						web_usages[sysuser_name] = usage
				else:
					self.logger.warning("Unable to calculate the disk space used by the following Windows system users:\n%s\n on the source server at %s:\n%s\nThe migration tool will skip checking available disk space for the corresponding subscriptions." % (" ".join(sysuser_names_chunk), server_ip, safe_string_repr("\n".join(stderr.strip().split("\n")[-5:]))) )
		return web_usages

	def _get_apache_disk_usage(self, server_ip, sysuser_names):
		web_usages = {}	# { sysuser_name: disk_usage }
		with self._get_hsphere_cp_runner() as cp_runner, closing(self.conn.hsphere.db()) as cursor:
			hsphere_runner = HsphereRunner(cp_runner, server_ip)
			for sysuser_name in sysuser_names:
				cursor.execute("SELECT user_id, dir FROM unix_user WHERE login = '%s'" % sysuser_name)
				row = cursor.fetchone()
				assert row is not None, "H-Sphere CP database inconsistency: no record for unix_user %s" % sysuser_name
				(uid, home_directory) = row

				cmd = "/hsphere/shared/scripts/getquota {uid} {home_directory}"
				args = {
					'uid': uid,
					'home_directory': home_directory,
				}
				exit_code, stdout, stderr = hsphere_runner.sh_unchecked(cmd, args)
				# stdout example (two lines):
				# 0
				# 1000
				# first line is usage, in megabytes
				# second line is quota, in megabytes
				if exit_code == 0:
					web_usages[sysuser_name] = int(stdout.strip().split('\n')[0]) * 1024 * 1024
				else:
					self.logger.warning("Unable to calculate the disk space used by web sites of user %s on source server at %s:\n%s\nThe migration tool will skip checking available disk space for the corresponding subscription." % (sysuser_name, server_ip, safe_string_repr("\n".join(stderr.strip().split("\n")[-5:]))) )
		return web_usages

	def _get_mail_disk_usage(self, server_ip, domain_names):
		mail_usages = {} # { domain_name: disk_usage }
		with self._get_hsphere_cp_runner() as cp_runner:
			hsphere_runner = HsphereRunner(cp_runner, server_ip)
			for domain_name in domain_names:
				cmd = "/hsphere/shared/scripts/get-domains-mbox-quotas '{domain_name}'"
				args = {
					'domain_name': domain_name,
				}
				exit_code, stdout, stderr = hsphere_runner.sh_unchecked(cmd, args)
				# stdout example (two lines):
				# webmaster|95.37|1000
				# postmaster|0.00|10
				# format is: mail_user|usage|quota
				# where usage and quota are in megabytes.
				if exit_code == 0:
					usage = 0
					for line in stdout.strip().split('\n'):
						if '|' in line:
							(_, usage_str, _) = line.split('|')
							usage += int(float(usage_str) * 1024 * 1024)
					mail_usages[domain_name] = usage
				else:
					self.logger.warning("Unable to calculate the disk space used by mailboxes of domain %s on source server at %s:\n%s\nThe migration tool will skip checking available disk space for the corresponding subscription." % (domain_name, server_ip, safe_string_repr("\n".join(stderr.strip().split("\n")[-5:]))) )
		return mail_usages

	def _check_disk_space_unix(self, report):
		self.logger.info("Calculating disk space usage on the source unix servers")
		lister = self._get_infrastructure_check_lister()
		wsi = [
			obj(
				subscription_name=subscription_name,
				web_ip=self._get_domain_content_properties(subscription_name).ip_address,
				target_node=nodes.target
			)
			for nodes, subscriptions in lister.get_subscriptions_by_web_nodes().iteritems()
			for subscription_name in subscriptions
			if self._get_domain_content_properties(subscription_name) is not None
			and not self._subscription_is_windows(subscription_name)
		]

		web_usages = {}	# { subscription_name: usage }
		with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
			for web_ip, subscription_infos in group_by(wsi, lambda si: si.web_ip).iteritems():
				for subscription_info in subscription_infos:
					backup_sub = backup.get_subscription(subscription_info.subscription_name)
					sysuser_name = backup_sub.get_phosting_sysuser_name()
					sysusers_disk_usage = self._get_apache_disk_usage(web_ip, [ sysuser_name ])
					web_usages[subscription_info.subscription_name] = sysusers_disk_usage.get(sysuser_name, 0)

		msi = [
			obj(
				subscription_name=subscription_name,
				mail_ip=self._get_domain_mail_server_ip(subscription_name),
				target_node=nodes.target
			)
			for nodes, subscriptions in lister.get_subscriptions_by_unix_mail_nodes().iteritems()
			for subscription_name in subscriptions
			if self._get_domain_mail_server_ip(subscription_name) is not None
		]

		mail_usages = {} # { subscription_name: usage }
		with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
			for mail_ip, subscription_infos in group_by(msi, lambda si: si.mail_ip).iteritems():
				domain_names = []
				for subscription_info in subscription_infos:
					domain_names += [
						domain.name for domain in itertools.chain(
							[backup.get_subscription(subscription_info.subscription_name)],
							backup.iter_addon_domains(subscription_info.subscription_name),
						)
					]
				mail_usages.update(self._get_mail_disk_usage(mail_ip, domain_names))

		dsi = []
		for database_info in self._list_databases_to_copy():
			source = database_info.source_database_server
			target = database_info.target_database_server
			if source.type() == 'mysql' and not target.is_windows():
				dsi.append(obj(
					subscription_name=database_info.subscription_name,
					db=obj(  # this data structure is required by checker function
						db_target_node=target,
						db_name=database_info.database_name
					),
					db_ip=source.host(),
					target_node=target.panel_server
				))

		db_usages = []  # [ obj(target_node, usage) ]
		for db_ip, db_infos in group_by(dsi, lambda si: si.db_ip).iteritems():
			db_to_target_node = { di.db.db_name: di.target_node for di in db_infos }
			for db_name, usage in self._get_mysql_disk_usage(db_ip, db_to_target_node.keys()).iteritems():
				db_usages.append(obj(target_node=db_to_target_node[db_name], usage=usage))

		self.logger.info("Checking if there is enough disk space on PPA unix service nodes")
		checker_target = infrastructure_checks.UnixDiskSpaceChecker()
		for target_node in set([ subs_info.target_node for subs_info in wsi + msi + dsi ]):
			web_subscription_names = [subs_info.subscription_name for subs_info in wsi if subs_info.target_node == target_node]
			usage_source_web = sum([ web_usages.get(subscription_name, 0) for subscription_name in web_subscription_names ])

			mail_subscription_names = [subs_info.subscription_name for subs_info in msi if subs_info.target_node == target_node]
			usage_source_mail = sum([ mail_usages.get(subscription_name, 0) for subscription_name in mail_subscription_names ])

			usage_source_mysql_db = sum([dbu.usage for dbu in db_usages if dbu.target_node == target_node])
			max_usage_source_mysql_db = max([0] + [dbu.usage for dbu in db_usages if dbu.target_node == target_node])

			checker_target.check_with_source_usages(
				target_node,
				report,
				usage_source_web, usage_source_mail, usage_source_mysql_db, max_usage_source_mysql_db,
				web_usages.keys(), mail_usages.keys(), [ di.db for di in dsi ]
			)

	@cached
	def _get_winbox_api_port(self):
		with self._get_hsphere_cp_runner() as cp_runner:
			custom_soap_port_str = cp_runner.sh("grep '^SOAP_PORT[ \t]*=' /hsphere/local/home/cpanel/hsphere/WEB-INF/classes/psoft_config/hsphere.properties | sed -e 's/SOAP_PORT[ \t]*=[ \t]*//'").strip()
			if custom_soap_port_str != "":
				try:
					return int(custom_soap_port_str)
				except ValueError as e:
					self.logger.debug(u'Exception:', exc_info=e)
					return 10125
			else:
					return 10125

	def _check_disk_space_windows(self, report):
		@cached
		def get_pserver_creds_by_lserver_ip(l_server_ip):
			with closing(self.conn.hsphere.db()) as cursor:
				cursor.execute("""
					SELECT ps.login, ps.password
					FROM p_server ps JOIN l_server ls ON ls.p_server_id = ps.id JOIN l_server_ips lsi ON lsi.l_server_id = ls.id
					WHERE lsi.ip = '%s'
				""" % l_server_ip)
				row = cursor.fetchone()
				assert row is not None, "H-Sphere CP database inconsistency: a physical server corresponding to logical server IP %s does not exist" % l_server_ip
				return obj(login=row[0], password=row[1])

		self.logger.info("Calculating disk space usage on the source windows servers")
		lister = self._get_infrastructure_check_lister()
		wsi = [
			obj(
				subscription_name=subscription_name,
				web_ip=self._get_domain_content_properties(subscription_name).ip_address,
				target_node=nodes.target,
			)
			for nodes, subscriptions in lister.get_subscriptions_by_web_nodes().iteritems()
			for subscription_name in subscriptions
			if self._get_domain_content_properties(subscription_name) is not None
			and self._subscription_is_windows(subscription_name)
		]

		soap_port = self._get_winbox_api_port()
		web_usages = {}  # { subscription_name: usage }
		with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
			for web_ip, subscription_infos in group_by(wsi, lambda si: si.web_ip).iteritems():
				for subscription_info in subscription_infos:
					backup_sub = backup.get_subscription(subscription_info.subscription_name)
					sysuser_name = backup_sub.get_phosting_sysuser_name()
					pserver_creds = get_pserver_creds_by_lserver_ip(web_ip)
					sysusers_disk_usage = self._get_iis_disk_usage(web_ip, soap_port, pserver_creds.login, pserver_creds.password, [sysuser_name])
					web_usages[subscription_info.subscription_name] = sysusers_disk_usage.get(sysuser_name, 0)

		dsi = []
		for database_info in self._list_databases_to_copy():
			source = database_info.source_database_server
			target = database_info.target_database_server
			if source.type() == 'mssql' and target.is_windows():
				dsi.append(obj(
					subscription_name=database_info.subscription_name,
					target_node=target.panel_server,
					db=obj(
						db_name=database_info.database_name,
						db_target_node=target
					),
					db_ip=target.host(),
				))

		db_usages = []  # [ obj(dst_host, dst_port, target_node, usage) ]
		for db_ip, db_infos in group_by(dsi, lambda si: si.db_ip).iteritems():
			db_to_dst_server = {di.db.db_name: (di.db.db_target_node.host(), di.db.db_target_node.port(), di.target_node) for di in db_infos}
			pserver_creds = get_pserver_creds_by_lserver_ip(db_ip)
			for db_name, usage in self._get_mssql_disk_usage(db_ip, soap_port, pserver_creds.login, pserver_creds.password, db_to_dst_server.keys()).iteritems():
				dst_host, dst_port, target_node = db_to_dst_server[db_name]
				db_usages.append(obj(dst_host=dst_host, dst_port=dst_port, target_node=target_node, usage=usage))

		self.logger.info("Checking if there is enough disk space on PPA windows service nodes")
		disk_usage_script_name = 'folder_disk_usage.vbs'
		mssql_disk_usage_script = 'mssql_disk_usage.ps1'
		checker_target = infrastructure_checks.WindowsDiskSpaceChecker()
		for target_node in set([subs_info.target_node for subs_info in wsi + dsi]):
			subscription_names = [subs_info.subscription_name for subs_info in wsi if subs_info.target_node == target_node]
			usage_source_web = sum([web_usages.get(subscription_name, 0) for subscription_name in subscription_names])
			usages_source_mssql_db = defaultdict(list)  # { db_server: [ usage ] }
			for dbu in db_usages:
				if dbu.target_node == target_node:
					usages_source_mssql_db[dbu.dst_host].append(dbu.usage)

			checker_target.check_with_source_usages(
				target_node,
				mysql_bin=database_utils.get_windows_mysql_client(target_node),
				du_local_script_path=get_package_scripts_file_path(
					parallels.plesks_migrator, disk_usage_script_name
				),
				mssql_disk_usage_local_script_path=migrator_utils.get_package_scripts_file_path(
					parallels.plesks_migrator, mssql_disk_usage_script
				),
				usage_source_web=usage_source_web, usages_source_mysql_db={},
				usages_source_mssql_db=usages_source_mssql_db,
				domains=subscription_names, mysql_databases=[], 
				mssql_databases=[ di.db for di in dsi if di.target_node == target_node],
				report=report
			)

	def _get_hsphere_extras_local_path(self, filename):
		return os.path.join(self._get_hsphere_migrator_root_path(), 'extras', filename)

	def _get_hsphere_migrator_root_path(self):
		# Get path to package root directory.
		dirs = [p for p in parallels.hsphere_migrator.__path__]
		assert all(d == dirs[0] for d in dirs)
		return dirs[0]

	@classmethod
	def _get_migration_list_class(cls):
		return HSphereMigrationList

	####################################### billing migration ####################################

	def transfer_resource_limits(self, options):
		self._transfer_resource_limits(options)
		self._finalize(finalize=True, options=options)

	@trace('transfer-resource-limits', u'transfer account resource limits and usage from H-Sphere')
	def _transfer_resource_limits(self, options):
		if not is_transfer_resource_limits_enabled(
			self.global_context.config, 
			self.global_context.target_panel_obj
		):
			return

		safe = self._get_safe_lazy()
		model = self._load_ppa_model()
		with closing(self.conn.hsphere.db()) as connection_cursor:
			backup_agent = resource_limits.BackupAgent(connection_cursor, try_subscription=safe.try_subscription)
			
			subscription_usages = []
			subscription_names = [
				subscription.name for client in model.clients.itervalues() for subscription in client.subscriptions
			]
			with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
				for subscription_name in subscription_names:
					domain_count = 1 # subscription itself should be included
					subdomain_count = 0
					mailbox_count = 0

					subscription = backup.get_subscription(subscription_name)
					addons = list(backup.iter_addon_domains(subscription_name))
					domain_count += len(addons)
					for domain in itertools.chain([subscription], addons):
						subdomains = backup.iter_subdomains(subscription.name, domain.name)
						subdomain_count += len(list(subdomains))
						mailbox_count += len(list(domain.iter_mailboxes()))

					subscription_usages.append(resource_limits.SubscriptionUsages(
						name=subscription_name,
						domain=domain_count,
						subdomain=subdomain_count,
						mailbox=mailbox_count,
					))

			model = backup_agent.make_backup(subscription_usages)

		# dump resource limits data to a file to simplify debugging
		write_yaml(self._get_session_file_path('resource-limits.yaml'), model)
		importer = resource_limits.Importer(self.conn.target.poa_api(), try_subscription=safe.try_subscription)
		importer.import_resource_limits(model)

	def transfer_billing(self, options):
		self._transfer_billing(options, test_mode=False)

	def check_billing(self, options):
		self._transfer_billing(options, test_mode=True)

	def _transfer_billing(self, options, test_mode):
		self._load_billing_configuration()
		self._check_hsphere_billing()
		self._check_billing()  # check billing API connection and billing version

		if not test_mode and not self.staging:
			print(
				"You are about to finally transfer the selected accounts to PPA Billing. "
				"This move cannot be reverted.\nEnter 'yes' to confirm the transfer, 'no' to cancel it."
			)
			while True:
				choice = raw_input().lower()
				if choice == 'yes':
					self.logger.info("Final transfer to PPA Billing was confirmed.")
					break
				elif choice == 'no':
					self.logger.info("Final transfer to PPA Billing was cancelled.")
					sys.exit(0)
				else:
					print "Invalid choice: %s. Please enter yes or no." % choice

		self._fetch(options, options.reload_source_data)
		self._read_migration_list_lazy(options)
		self.convert(
			options, 
			# we do not want to show pre-migration and conversion warning messages
			# when executing the 'transfer-billing' command,
			# because user must have seen these messages when
			# he executed 'transfer-accounts', or 'check' commands.
			merge_pre_migration_report=False 
		)

		report = checking.Report(u"Billing Transfer Issues", None)

		# 1.1 retrieve source info: model
		(target_model, source_model) = self._retrieve_billing_info(report)
		self._clarify_ssl_certificate_details(target_model)
		write_yaml(self._get_session_file_path('billing-target.yaml'), target_model)
		write_yaml(self._get_session_file_path('billing-source.yaml'), source_model)

		# 1.2 retrieve source info: costs
		hsphere_costs = self._estimate_hsphere_payments(source_model, report)

		# 2.1 prepare source for accounts suspension
		try:
			self._create_bck_tables_for_billing()
		except Exception as e:
			self.logger.debug(u'Exception:', exc_info=e)
			self.logger.warning(u"Error creating the tables for billing/contact information backup: %s" % e)

		# 2 transfer users and accounts
		if test_mode:
			self.check_billing_impl(source_model, target_model, hsphere_costs, options, report)
		else:
			self.transfer_billing_impl(source_model, target_model, hsphere_costs, options, report)

		# 3 print report and summary
		if report.has_errors_or_warnings():
			self.print_report(report, 'billing_import_report_tree', show_no_issue_branches=False)
			if test_mode:
				pass  # the issues are already displayed, got nothing to add
			if not test_mode:
				# add summary: how much transferred successfully, how much not transferred because of issues
				failed_customers = []
				succeeded_customers = []
				for billing_account in target_model.accounts:
					customer_report = report.subtarget('Customer', billing_account.login)
					if customer_report.has_errors_or_warnings():
						failed_customers.append(billing_account)
					else:
						succeeded_customers.append(billing_account)
				if len(succeeded_customers) > 0:
					print "%d H-Sphere users with %d accounts were transferred successfully." % (
						len(succeeded_customers),
						sum([len(account.hosting_subscriptions) for account in succeeded_customers])
					)
				print "%d H-Sphere users with %d accounts were not transferred because of the issues listed above" % (
					len(failed_customers),
					sum([len(account.hosting_subscriptions) for account in failed_customers])
				)
		else:
			if test_mode:
				print "No issues found."
			else:
				accounts_qty = sum([len(account.hosting_subscriptions) for account in target_model.accounts])
				print "All %d H-Sphere users with %d accounts were transferred successfully." % (
					len(target_model.accounts), accounts_qty
				)

		self.action_runner.run(
			self.workflow.get_shared_action('cleanup')
		)

	def _load_billing_configuration(self):
		section = ConfigSection(self.config, 'billing-domain-plans')
		self.domain_subscription_plans = dict(section.items())

		self.ssl_product_to_plan_id = {}
		ppab_ssl_section = ConfigSection(self.config, 'billing-ssl-plans')
		for ssl_product in SSLProducts.__dict__.itervalues():
			value = ppab_ssl_section.get(ssl_product)
			if value is not None:
				self.ssl_product_to_plan_id[ssl_product] = int(value)

		billing_section = ConfigSection(self.config, 'ppa-billing')
		self.increase_limit = float(billing_section.get('increase-limit', 0))
		self.decrease_limit = float(billing_section.get('decrease-limit', 0))

		supported_billing_modes = ['staging', 'production']
		billing_mode = billing_section.get('mode')
		if billing_mode not in supported_billing_modes:
			raise MigrationError(
				u"""An invalid value "%s" is specified for the "mode" setting """
				u"""in the ppa-billing section of tool's configuration. Specify one of these values: %s.""" % (
					billing_mode, ", ".join(supported_billing_modes)
				)
			)
		self.staging = billing_mode == 'staging'

	def _check_billing(self):
		try:
			ppab_api = self.conn.target.billing_api()
		except Exception as e:
			self.logger.debug(u"Exception:", exc_info=e)
			raise MigrationError(
				u"Failed to obtain the PPA Billing API URL and credentials: %s.\n"
				u"Make sure that the PPA Billing is registered in PPA." % e
			)
		proto, path = ppab_api.url.split('//')
		full_url = "%s//%s:%s@%s" % (proto, ppab_api.user, ppab_api.password, path)
		self.logger.info("PPA Billing URL: %s" % full_url)

		try:
			ppab_version = ppab_api.get_version().Version
		except Exception as e:
			self.logger.debug(u"Exception:", exc_info=e)
			raise MigrationError(
				u"A test call of PPA Billing API failed: %s.\n"
				u"Make sure that the PPA Billing is started and there "
				u"are no firewall rules blocking access to its API." % e
			)
		self.logger.debug("PPA Billing version: %s" % ppab_version)

		required_version = "5.7.6"
		if version_tuple(ppab_version) < version_tuple(required_version):
			raise MigrationError(
				u"PPA Billing version %s is less than required: %s.\nUpgrade your PPA Billing." % (
					ppab_version, required_version
				)
			)

	def _get_hsphere_api_url(self, custom_ip=None):
		"""Return the base URL to H-Sphere XML API, e.g. http://10.52.37.70:8180/psoft/servlet/services/
		The users of API must add the name of service they're calling, to this URL, the complete URL will be e.g.:
		http://10.52.37.70:8180/psoft/servlet/services/AdminServices

		Initially, just form up the base URL here, allowing to alter only the port number.
		Later, maybe, read this URL from config, if there'll be customers setting up API in a nonstandard way.
		"""
		ip = custom_ip or self.source_plesks['hsphere'].ip
		return "http://%s:%s/psoft/servlet/services/" % (ip, self.source_plesks['hsphere'].api_port)

	def _check_hsphere_billing(self):
		services_url = self._get_hsphere_api_url() + 'AdminServices'
		self.logger.info(u"H-Sphere AdminServices API URL: %s" % services_url)
		schema_url = services_url + '?wsdl'
		try:
			# download the schema
			response = urllib2.urlopen(schema_url)
			schema = response.read()
		except Exception as e:
			self.logger.debug(u"Exception:", exc_info=e)
			raise MigrationError(u"""Failed to download the schema of XML API AdminServices: %s.
Make sure that:
1. The H-Sphere XML API is enabled according to the PPA migration guide.
2. The H-Sphere CP is running.
3. There are no firewall rules blocking access to the H-Sphere XML API from PPA management node.""" % e)
		self.logger.debug("The received H-Sphere XML API schema is: %s" % schema)

		def methods_present_in_schema(methods, schema):
			for method in methods:
				if ("""<wsdl:operation name="%s">""" % method) not in schema:
					return False
			return True

		required_methods = ['refreshContactInfo', 'refreshBillingInfo']
		if not methods_present_in_schema(required_methods, schema):
			raise MigrationError(u"""One or more of the required API methods are absent in the H-Sphere XML API.
The required methods are: %s
They must be present in schema which is downloadable from %s

Make sure that you have installed the new API methods according to the PPA migration guide.""" % (
				u", ".join(required_methods), schema_url)
			)


	@trace('transfer-billing', 'transfer H-Sphere users and their accounts into PPA Billing')
	def transfer_billing_impl(self, source_model, target_model, hsphere_costs, options, report):
		self._transfer_users_and_accounts(source_model, target_model, hsphere_costs, options, report, False)

	@trace('check-billing', 'check if H-Sphere users and their accounts can be transferred into PPA Billing')
	def check_billing_impl(self, source_model, target_model, hsphere_costs, options, report):
		self._transfer_users_and_accounts(source_model, target_model, hsphere_costs, options, report, True)

	def _transfer_users_and_accounts(self, source_model, target_model, hsphere_costs, options, report, test_mode):
		migration_list_data = self._read_migration_list_data(
			options, lambda fileobj: self._get_migration_list_class().read_billing_info(fileobj)
		)
		ppa_data = self._read_ppa_data()
		ppab_api = self.conn.target.billing_api()

		hsphere_users_by_login = group_by_id(source_model.users, lambda u: u.login)
		for billing_account in target_model.accounts:
			assert \
				billing_account.login in hsphere_users_by_login, \
				"Internal error: user %s exists in target model, but does not exist in source model" % (
					billing_account.login,
				)
			customer_report = report.subtarget('Customer', billing_account.login)
			if billing_account.login not in ppa_data.customers:
				issue = checking.Issue(
					checking.Problem(
						'skip_transferring_user', checking.Problem.WARNING, 
						u"There is no customer with the username %s in PPA. "
						u"Unable to import this customer into PPA Billing." % (
							billing_account.login
						)
					),
					u"Transfer this customer to PPA before transferring them to PPA Billing."
				)
				self.logger.warning(issue.problem.description)
				customer_report.add_issue_obj(issue)
			else:
				customer_id = ppa_data.customers[billing_account.login]

				hsphere_user = hsphere_users_by_login[billing_account.login]
				self._transfer_hsphere_user(
					customer_id, hsphere_user, billing_account, hsphere_costs, self.increase_limit,
					self.decrease_limit, ppab_api, migration_list_data, customer_report,
					staging=self.staging, test_mode=test_mode,
					accept_automatic_deletion=options.accept_automatic_deletion
				)

	def _transfer_hsphere_user(
		self, customer_id, hsphere_user, billing_account, hsphere_costs, increase_limit,
		decrease_limit, ppab_api, migration_list_data, customer_report, staging=False,
		test_mode=False, accept_automatic_deletion=False
	):
		class UserMigrationFailed(Exception):
			"""The exception for internal use - to trigger user
			rollback code when there were issues and in the test mode"""
			pass

		self.logger.info(u"Transferring H-Sphere user %s" % hsphere_user.login)
		with log_context(hsphere_user.login):
			hsphere_user_accounts = group_by_id(hsphere_user.accounts, lambda acc: acc.main_domain_name)
			try:
				importer = BillingImporter(ppab_api)
				ppab_api.begin_transaction()
				try:
					importer.import_general_information(billing_account, customer_id, customer_report)
				except Exception as e:
					self.logger.debug(u'Exception:', exc_info=e)
					issue = checking.Issue(
						checking.Problem(
							'unable_import_general_information', checking.Problem.ERROR, 
							u"Unable to import the customer with the username %s into PPA Billing: %s" % (
								billing_account.login, e
							)
						),
						e.solution if hasattr(e, 'solution') else u""
					)
					self.logger.error(issue.problem.description)
					customer_report.add_issue_obj(issue)
					raise UserMigrationFailed(
						"User %s migration failed on import of its general info" % billing_account.login
					)

				successfully_transferred_accounts = []
				for subscription in billing_account.hosting_subscriptions:  # for each account
					assert \
						subscription.name in hsphere_user_accounts, \
						"Internal error: account %s exists in target model, but does not exist in source model" % (
							subscription.name
						)
					hsphere_account = hsphere_user_accounts[subscription.name]
					hsphere_account_ssl_certificates = [cert.domain_name for cert in hsphere_account.ssl_certificates]
					hsphere_account_registered_domains = [reg.domain_name for reg in hsphere_account.registered_domains]

					transfer_success = self._transfer_account(
						customer_id,
						hsphere_account, subscription,
						[
							ssl_s for ssl_s in billing_account.ssl_subscriptions
							if ssl_s.domain_name in hsphere_account_ssl_certificates
						],
						[
							dom_s for dom_s in billing_account.domain_subscriptions
							if dom_s.name in hsphere_account_registered_domains
						],
						hsphere_costs, increase_limit, decrease_limit, importer, migration_list_data, customer_report,
						staging, test_mode, accept_automatic_deletion
					)
					if transfer_success:
						successfully_transferred_accounts.append(hsphere_account)

				if customer_report.has_errors_or_warnings():
					for hsphere_account in successfully_transferred_accounts:
						issue = checking.Issue(
							checking.Problem(
								'will_not_transfer_account', checking.Problem.WARNING,
								u"The H-Sphere account #%s (domain %s) will not be transferred because of "
								u"errors with other accounts belonging to this customer." % (
									hsphere_account.account_id, hsphere_account.main_domain_name
								)
							),
							u"Resolve the errors with other accounts of this customer."
						)
						self.logger.warning(issue.problem.description)
						customer_report.add_issue_obj(issue)
					raise UserMigrationFailed(
						u"Assuming user %s migration failed because there were issues" % hsphere_user.login
					)
				if test_mode:
					raise UserMigrationFailed(
						u"Assuming user migration failed to roll everything back (migrator running in the test mode)"
					)
					
				ppab_api.commit_transaction()
				self.logger.debug("Enable billing tab in PPA customer's panel")
				self.conn.target.poa_api().account_added_to_pba(customer_id)
			except Exception as e:
				if type(e) != UserMigrationFailed:
					self.logger.debug(u'Exception:', exc_info=e)
					issue = checking.Issue(
						checking.Problem(
							'unable_to_transfer_user', checking.Problem.ERROR, 
							u"Unable to transfer the H-Sphere user %s to PPA Billing: %s\nAll H-Sphere accounts of this user will not be transferred to PPA Billing." % (billing_account.login, e)
						),
						e.solution if hasattr(e, 'solution') else u""
					)
					self.logger.error(issue.problem.description)
					customer_report.add_issue_obj(issue)

				if ppab_api.api.trnsId is not None:  # if there's a transaction in progress, roll it back
					ppab_api.rollback_transaction()

				for account_id in [
					hsphere_account.account_id for hsphere_account in hsphere_user.accounts
					if self._was_account_suspended(hsphere_account.account_id)
				]:
					self._resume_account(account_id, customer_report)
				for account_id in [
					hsphere_account.account_id for hsphere_account in hsphere_user.accounts
					if self._was_account_info_blanked_out(hsphere_account.account_id)
				]:
					self._restore_account_info(account_id, customer_report)

	def _transfer_account(
		self,
		customer_id,  # where to import
		hsphere_account, hosting_subscription, ssl_subscriptions, domain_subscriptions,	 # what to import
		hsphere_costs, increase_limit, decrease_limit,  # data for filter by cost
		importer, migration_list_data,  # service data
		customer_report,  # place for issues
		staging=False, test_mode=False, accept_automatic_deletion=False  # conditions
	):
		"""Transfer one H-Sphere account.
		On any error, roll back changes in H-Sphere, add issues to customer_report, and return False
		If no errors, return True
		"""
		class AccountMigrationFailed(Exception):
			"""The exception for internal use - to trigger account rollback code when there were issues"""
			pass

		self.logger.info("Transferring H-Sphere account #%s (domain %s)" % (hsphere_account.account_id, hosting_subscription.name))
		with log_context(hosting_subscription.name):
			try:
				(source_hosting_cost, source_domains_cost, source_ssl_cost) = hsphere_costs.get(hsphere_account.account_id)

				issues = []

				try:
					if not staging:
						self._blank_out_account_info(hsphere_account.account_id)
						if hsphere_account.is_active:
							suspend_issues = self._check_if_account_may_be_suspended(hsphere_account.account_id, accept_automatic_deletion)
							if len(suspend_issues) == 0 and not test_mode:
								self._suspend_account(hsphere_account.account_id)
							else:
								issues += suspend_issues
				except Exception as e:
					self.logger.debug(u'Exception:', exc_info=e)
					if "java.net.ConnectException: Connection refused" in str(e):
						error_msg = u"Unable to suspend the H-Sphere account #%s (domain %s): connection refused" % (hsphere_account.account_id, hosting_subscription.name)
						solution_msg = u"Make sure that:\n1. You have enabled H-Sphere XML API.\n2. You have specified the actual TCP port the API is enabled on, in migration tool's configuration.\n3. The H-Sphere CP is running.\n4.The account in H-Sphere is in the expected status (active or suspended)."
					else:
						error_msg = u"Unable to suspend the H-Sphere account #%s (domain %s): %s" % (hsphere_account.account_id, hosting_subscription.name, e)
						solution_msg = u"Check the account status in H-Sphere and resume the account if necessary."
					issue = checking.Issue(
						checking.Problem(
							'unable_suspend_account', checking.Problem.ERROR, 
							error_msg
						),
						solution_msg
					)
					self.logger.error(issue.problem.description)
					issues.append(issue)

				def form_up_import_issue(hosting_type, account_id, domain_name, exc):
					return checking.Issue(
						checking.Problem(
							'unable_import_account_to_billing', checking.Problem.ERROR, 
							u"Unable to import to PPA Billing the %s of H-Sphere account #%s (domain %s): %s\nThe H-Sphere account will not be transferred to PPA Billing." % (hosting_type, account_id, domain_name, exc)
						),
						u""
					)

				def form_up_cost_issue(account_id, domain_name, source_cost, target_cost, limit, limit_type, param_name, hosting_type, subscription_type, plan_type):
					return checking.Issue(
						checking.Problem(
							'cost_differs_too_much', checking.Problem.ERROR, 
							u"The cost %s is too big.\nThe difference between estimated costs of %s of account #%s (domain %s) in H-Sphere (%f) and corresponding %s in PPA Billing (%f) exceeds the acceptable %s (%f).\nThe H-Sphere account will not be transferred to PPA Billing." % (limit_type, hosting_type, account_id, domain_name, source_cost, subscription_type, target_cost, limit_type, limit)
						),
						u"Adjust either of: \n* the price on %s plan in PPA Billing\n* the %s value in migration tool's configuration" % (plan_type, param_name)
					)

				def check_costs(account_id, subscription_name, src_cost, dst_cost, increase_limit, decrease_limit, hosting_type, subscription_type, plan_type):
					if src_cost - dst_cost > decrease_limit:
						issue = form_up_cost_issue(account_id, subscription_name, src_cost, dst_cost, decrease_limit, "decrease", "decrease-limit", hosting_type, subscription_type, plan_type)
						self.logger.error(issue.problem.description)
						issues.append(issue)
					if dst_cost - src_cost > increase_limit:
						issue = form_up_cost_issue(account_id, subscription_name, src_cost, dst_cost, increase_limit, "increase", "increase-limit", hosting_type, subscription_type, plan_type)
						self.logger.error(issue.problem.description)
						issues.append(issue)

				self.logger.info("Creating subscriptions in PPA Billing")
				try:
					hosting_cost = importer.import_hosting_subscription(hosting_subscription, customer_id, customer_report, self._read_ppa_data(), migration_list_data)
					check_costs(hsphere_account.account_id, hosting_subscription.name, source_hosting_cost, hosting_cost, increase_limit, decrease_limit, "hosting", "hosting subscription", "hosting")
				except Exception as e:
					self.logger.debug(u'Exception:', exc_info=e)
					issue = form_up_import_issue("hosting settings", hsphere_account.account_id, hosting_subscription.name, e)
					self.logger.error(issue.problem.description)
					issues.append(issue)

				# import SSL subscriptions, check cost
				ssl_cost_complete = True
				ssl_cost = float(0)
				for subscription in ssl_subscriptions:
					try:
						ssl_cost += importer.import_ssl_subscription(subscription, self.ssl_product_to_plan_id, customer_id, customer_report)
					except Exception as e:
						ssl_cost_complete = False
						self.logger.debug(u'Exception:', exc_info=e)
						issue = form_up_import_issue("SSL certificates", hsphere_account.account_id, hosting_subscription.name, e)
						self.logger.error(issue.problem.description)
						issues.append(issue)

				if ssl_cost_complete:
					check_costs(hsphere_account.account_id, hosting_subscription.name, source_ssl_cost, ssl_cost, increase_limit, decrease_limit, "SSL certificates", "SSL subscriptions", "SSL")

				# import domain subscriptions, check cost
				domain_cost_complete = True
				domains_cost = float(0)
				for subscription in domain_subscriptions:
					try:
						domains_cost += importer.import_domain_subscription(customer_id, subscription, self.domain_subscription_plans, customer_report)
					except Exception as e:
						domain_cost_complete = False
						self.logger.debug(u'Exception:', exc_info=e)
						issue = form_up_import_issue("registered domains", hsphere_account.account_id, hosting_subscription.name, e)
						self.logger.error(issue.problem.description)
						issues.append(issue)

				if domain_cost_complete:
					check_costs(hsphere_account.account_id, hosting_subscription.name, source_domains_cost, domains_cost, increase_limit, decrease_limit, "registered domains", "domain subscriptions", "domain")

				if len(issues) > 0:
					for issue in issues:
						customer_report.add_issue_obj(issue)
					raise AccountMigrationFailed("Assuming account #%s (domain %s) migration failed because there were issues" % (hsphere_account.account_id, hosting_subscription.name))
				else:
					return True
			except Exception as e:
				if type(e) != AccountMigrationFailed:
					self.logger.debug(u'Exception:', exc_info=e)
					issue = checking.Issue(
						checking.Problem(
							'unable_transfer_account_to_billing', checking.Problem.ERROR, 
							u"Unable to transfer the H-Sphere account #%s (domain %s) to PPA Billing: %s" % (hsphere_account.account_id, hosting_subscription.name, e)
						),
						u"",
					)
					self.logger.error(issue.problem.description)
					customer_report.add_issue_obj(issue)
				if self._was_account_suspended(hsphere_account.account_id):
					self._resume_account(hsphere_account.account_id, customer_report)
				if self._was_account_info_blanked_out(hsphere_account.account_id):
					self._restore_account_info(hsphere_account.account_id, customer_report)
				return False

	@trace('retrieve-billing-info', u'retrieve billing information from H-Sphere')
	def _retrieve_billing_info(self, report):
		model = self._load_ppa_model()

		clients_to_fetch = []
		for client in model.clients.itervalues():
			clients_to_fetch.append(ClientToFetch(login=client.login, subscriptions=[subscription.name for subscription in client.subscriptions]))

		with closing(self.conn.hsphere.db()) as connection_cursor:
			backup_agent = BillingBackupAgent(connection_cursor, report)
			return backup_agent.make_backup(clients_to_fetch)

	def _clarify_ssl_certificate_details(self, model):
		def get_certificate_date(ppa_runner, certificate, date_name):
			date_str = ppa_runner.sh("openssl x509 -{date_name} -noout | cut -d '=' -f 2".format(date_name=date_name), stdin_content=certificate)
			return int(ppa_runner.sh(u"date -u -d {date_str} +\"%s\"", { 'date_str': date_str.strip('\n') }))

		# extract the certificate information by running the openssl program at PPA MN
		with self.conn.target.main_node_runner() as ppa_runner:
			for account in model.accounts:
				clarified_ssl_subscriptions = []
				for ssl_subscription in account.ssl_subscriptions:
					cert_plus_pkey = ssl_subscription.certificate + '\n' + ssl_subscription.pkey
					csr = ppa_runner.sh("openssl x509 -x509toreq -signkey /dev/stdin", stdin_content=cert_plus_pkey)
					# strip text information from the output, leaving only the encoded request
					csr = csr[csr.index('-----BEGIN CERTIFICATE REQUEST-----') : csr.index('-----END CERTIFICATE REQUEST-----') + len('-----END CERTIFICATE REQUEST-----')]

					start_date = get_certificate_date(ppa_runner, ssl_subscription.certificate, 'startdate')
					expiration_date = get_certificate_date(ppa_runner, ssl_subscription.certificate, 'enddate')

					subject_str = ppa_runner.sh("openssl x509 -subject -noout", stdin_content=ssl_subscription.certificate)
					# "subject= /C=AF/ST=NA/L=test/O=signup/OU=Not specified/CN=razdvatri.ru/emailAddress=bill@mailinator.com"
					csr_attrs = {}
					for attr in subject_str.lstrip('subject= /').split('/'):
						(attr_name, attr_value) = attr.split('=')
						csr_attrs[attr_name] = attr_value

					subscr_values_dict = ssl_subscription._asdict()
					subscr_values_dict.update({
						'csr': csr,
						'start_date': start_date,
						'expiration_date': expiration_date,
						'csr_attrs': csr_attrs
					})
					# In H-Sphere, values from certificate have precedence over account contact info.
					# Here we override the values that were taken from contact info.
					if 'L' in csr_attrs:
						subscr_values_dict['address1'] = csr_attrs['L']
					if 'O' in csr_attrs:
						subscr_values_dict['address2'] = csr_attrs['O']
					if 'emailAddress' in csr_attrs:
						subscr_values_dict['approver_email'] = csr_attrs['emailAddress']

					clarified_ssl_subscriptions.append(SSLSubscription(**subscr_values_dict))
				del account.ssl_subscriptions[:]
				account.ssl_subscriptions.extend(clarified_ssl_subscriptions)

	@staticmethod
	def _is_account_suspended(cursor, account_id):
		cursor.execute("""SELECT 1 FROM accounts WHERE id = %s AND suspended IS NOT NULL""" % account_id)
		return cursor.fetchone() is not None

	@cached
	def _upload_java_class_files(self, cp_runner, class_files):
		for class_file in class_files:
			cp_runner.upload_file(self._get_hsphere_extras_local_path(class_file), "/tmp/%s" % class_file)

	@staticmethod
	def _make_complete_cmd_for_java_class_run(cmd):
		"""The various java classes are executed in the same way: under cpanel user and with the specific classpaths.
		Given a command line with just the class file and its arguments,
		Return the complete command line.
		Note that arguments specified in the command line are not expanded by this function.
		"""
		return u"su - cpanel -c \"java -cp `ls -1 /hsphere/local/home/cpanel/hsphere/WEB-INF/lib/*.jar|tr -s '\n' ':'`/tmp:/hsphere/local/home/cpanel/hsphere/WEB-INF/classes/ %s\"" % cmd

	def _run_cp_api_cmd(self, cp_runner, cursor, cmd_class_name, cmd_args):
		"""Run a Java class working with H-Sphere CP over API.
		The extra arguments to this class - cmd_args - are of type list(tuple(name, value)), to preserve arguments order
		"""
		cursor.execute("""SELECT password FROM users WHERE username = 'admin'""")
		(admin_password,) = cursor.get_one_row()
		api_url = self._get_hsphere_api_url('127.0.0.1')
		cmd = "%s '{api_url}' {admin_password}" % cmd_class_name
		args = {
			'api_url': api_url,
			'admin_password': admin_password,
		}
		for (key, value) in cmd_args:
			cmd += " {%s}" % key
			args[key] = value
		complete_cmd = self._make_complete_cmd_for_java_class_run(cmd)

		self._upload_java_class_files(cp_runner, ('%s.class' % cmd_class_name,))
		return cp_runner.sh(complete_cmd, args)

	def _preserve_apache_configs(self, cursor, subscription_name):
		def get_config(site_name):
			cursor.execute(u"""
				SELECT av.id, av.entry
				FROM apache_vhost av
				JOIN parent_child pc ON pc.child_id = av.id AND pc.child_type = 9	-- hosting
				JOIN domains d ON pc.parent_id = d.id
				WHERE d.name = '%s'
			""" % site_name)
			row = cursor.fetchone()
			if row is None:
				return None, None
			else:
				(vhost_id, config) = row
				return vhost_id, config

		self.preserved_apache_configs.clear()
		with closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
			backup_sub = backup.get_subscription(subscription_name)
			site_names = []
			if backup_sub.hosting_type in [ 'phosting', 'vrt_hst', 'sub_hst' ]:
				site_names.append(subscription_name)
			site_names += [ site.name for site in backup.iter_sites(subscription_name) if site.hosting_type in [ 'phosting', 'vrt_hst', 'sub_hst' ] ]
			site_names += [ alias.name for alias in backup.iter_aliases(subscription_name) if alias.parent_domain_name in site_names ]

			for site_name in site_names:
				vhost_id, config = get_config(site_name)
				if vhost_id is not None:
					self.preserved_apache_configs[vhost_id] = config

	@staticmethod
	def _domain_has_mail_hosting(cursor, domain_name):
		cursor.execute("""
			SELECT 1 FROM domains d JOIN parent_child pc ON pc.parent_id = d.id AND pc.child_type = 1000	-- Mail service
			WHERE d.name = '%s'
		""" % domain_name)
		return cursor.fetchone() is not None

	@staticmethod
	def _domain_has_web_hosting(cursor, domain_name):
		cursor.execute("""
			SELECT 1 FROM domains d JOIN parent_child pc ON pc.parent_id = d.id AND pc.child_type = 9	-- hosting
			WHERE d.name = '%s'
		""" % domain_name)
		return cursor.fetchone() is not None

	@staticmethod
	def get_pserver_creds_by_ip(cursor, p_server_ip):
		cursor.execute("""
			SELECT ps.login, ps.password
			FROM p_server ps 
			WHERE ps.ip1 = '%s'
		""" % p_server_ip)
		row = cursor.fetchone()
		assert row is not None, "H-Sphere CP database inconsistency: a physical server with IP %s does not exist" % p_server_ip
		return obj(login=row[0], password=row[1])

	def _run_winbox_api_cmd(self, cp_runner, cursor, winbox_server_ip, cmd_class_name, cmd_args):
		"""Run a Java class working with the specified winbox over API.
		The extra arguments to this class - cmd_args - are of type list(tuple(name, value)), to preserve arguments order
		"""
		cmd = "%s {server_ip} {server_port} {server_login} {server_password}"% cmd_class_name
		winbox_server_creds = self.get_pserver_creds_by_ip(cursor, winbox_server_ip)
		args = {
			'server_ip': winbox_server_ip,
			'server_port': self._get_winbox_api_port(),
			'server_login': winbox_server_creds.login,
			'server_password': winbox_server_creds.password,
		}
		for (key, value) in cmd_args:
			cmd += " {%s}" % key
			args[key] = value
		complete_cmd = self._make_complete_cmd_for_java_class_run(cmd)

		self._upload_java_class_files(cp_runner, ('NoCP.class', '%s.class' % cmd_class_name))
		return cp_runner.sh(complete_cmd, args)

	def _resume_services(self, cursor, subscription_name):
		with self._get_hsphere_cp_runner() as cp_runner, closing(self.load_raw_plesk_backup(self.source_plesks['hsphere'])) as backup:
		# determine if web hosting is on => resume the system user, resume the website
			backup_sub = backup.get_subscription(subscription_name)
			sysuser = backup_sub.get_phosting_sysuser()
			if sysuser is not None:
				web_server_ip = self._get_domain_content_properties(subscription_name).ip_address
				if self._subscription_is_windows(subscription_name):
					# resume Windows user
					# <resume xmlns="resourcemanager"><resourcename>unixuser</resourcename><username>winwin</username></resume>
					self._run_winbox_api_cmd(cp_runner, cursor, web_server_ip, "ResumeWindowsUser", [('user_name', sysuser.name)])
					# resume web sites
					# <resume xmlns="resourcemanager"><resourcename>hosting</resourcename><hostname>win-db.tst</hostname></resume>
					for domain_name in [subscription_name] + [site.name for site in backup.iter_sites(subscription_name)] + [alias.name for alias in backup.iter_aliases(subscription_name)]:
						if self._domain_has_web_hosting(cursor, domain_name):
							self._run_winbox_api_cmd(cp_runner, cursor, web_server_ip, "ResumeIISSite", [('site_name', domain_name)])

				else:
					web_server_runner = HsphereRunner(cp_runner, web_server_ip)
					# resume unix user
					# /hsphere/shared/scripts/user-resume mailset1 'ifgrfc'
					web_server_runner.sh(u"/hsphere/shared/scripts/user-resume {username} {password}", { 'username': sysuser.name, 'password': "'%s'" % sysuser.password.text })
					# resume web sites
					# /hsphere/shared/scripts/apache-saveconf 1109.conf
					# /hsphere/shared/scripts/apache-reconfig
					for vhost_id, config in self.preserved_apache_configs.iteritems():
						web_server_runner.sh(u"/hsphere/shared/scripts/apache-saveconf {vhost_id}.conf", { 'vhost_id': vhost_id }, config)
					if len(self.preserved_apache_configs) > 0:
						web_server_runner.sh(u"/hsphere/shared/scripts/apache-reconfig")


			# find the domains with mail hosting => resume mail on them, resume their mailboxes, resume their mailing lists
			# /hsphere/shared/scripts/domain-resume 'mailset1.tld'
			# /hsphere/shared/scripts/domain-resume 'postmaster@mailset1.tld'
			# XXX fell out of scope - /hsphere/shared/scripts/mlist-resume mailset1.tld maillist1
			# XXX fell out of scope - mailboxes on subdomains won't be resumed
			if backup_sub.mailsystem is not None:
				mail_server_ip = self._get_domain_mail_server_ip(subscription_name)
				mail_server_runner = HsphereRunner(cp_runner, mail_server_ip)
				for domain_name in [subscription_name] + [site.name for site in backup.iter_sites(subscription_name)] + [alias.name for alias in backup.iter_aliases(subscription_name)]:
					if self._domain_has_mail_hosting(cursor, domain_name):
						mail_server_runner.sh(u"/hsphere/shared/scripts/domain-resume {domain_name}", { 'domain_name': domain_name })

				for mailbox in backup_sub.iter_mailboxes():
					if mailbox.enabled:
						mail_server_runner.sh(u"/hsphere/shared/scripts/domain-resume {mailbox_name}", { 'mailbox_name': mailbox.full_name })

		# determine if there are mysql databases => resume mysql users
		# /hsphere/shared/scripts/mysql-resume-user mailset_baddi '111qqqQQ' 10.52.69.79
			for dbu in backup_sub.get_overall_database_users():
				if dbu.dbtype == 'mysql':
					db_server_runner = HsphereRunner(cp_runner, dbu.host)
					db_server_runner.sh(u"/hsphere/shared/scripts/mysql-resume-user {username} {password} {host}", { 'username': dbu.name, 'password': dbu.password, 'host': dbu.host })

		# XXX fell out of scope - determine if there are postgresql databases => resume postgresql users
		# /hsphere/shared/scripts/pgsql-resume-user PASSWORD mailset_rood

		# MSSQL databases/users don't need to be resumed

		# DNS doesn't need to be resumed

	def _create_bck_tables_for_billing(self):
		"""Create the bck_ tables to save billing and contact information of transferred accounts:
		bck_billing_info
		bck_credit_card
		bck_contact_info
		"""
		with closing(self.conn.hsphere.db_connection()) as conn:
			cursor = conn.cursor()
			cursor.execute("""SELECT 1 FROM information_schema.tables WHERE table_catalog = 'hsphere' AND table_schema = 'public' AND table_name = 'bck_billing_info'""")
			if cursor.fetchone() is None:
				cursor.execute("""
					CREATE TABLE bck_billing_info (
						id INT NOT NULL,
						name VARCHAR(80) NOT NULL,
						last_name VARCHAR(100),
						company VARCHAR(100),
						address1 VARCHAR(255) NOT NULL,
						address2 VARCHAR(255),
						city VARCHAR(60) NOT NULL,
						state VARCHAR(20) NOT NULL,
						postal_code VARCHAR(16) NOT NULL,
						country VARCHAR(2) NOT NULL,
						phone VARCHAR(30) NOT NULL,
						email VARCHAR(128) NOT NULL,
						type VARCHAR(20),
						state2 VARCHAR(255),
						stop_date DATE,
						reason VARCHAR(1024),
						negative_date DATE,
						exemption_code VARCHAR(60),
						ec_approved TIMESTAMPTZ,
						ec_rejected TIMESTAMPTZ
					)
				""")

			cursor.execute("""SELECT 1 FROM information_schema.tables WHERE table_catalog = 'hsphere' AND table_schema = 'public' AND table_name = 'bck_credit_card'""")
			if cursor.fetchone() is None:
				cursor.execute("""
					CREATE TABLE bck_credit_card (
						id INT NOT NULL,
						cc_number VARCHAR(20),
						hidden_cc_number VARCHAR(20) NOT NULL,
						encrypted_cc_number VARCHAR(172),
						name VARCHAR(60) NOT NULL,
						exp_year VARCHAR(4) NOT NULL,
						exp_month VARCHAR(10) NOT NULL,
						exp_day VARCHAR(2),
						issueno VARCHAR(2),
						start_year VARCHAR(4),
						start_month VARCHAR(2),
						start_day VARCHAR(2),
						created DATE NOT NULL,
						type VARCHAR(8) NOT NULL,
						fatts INT,
						last_fatt TIMESTAMPTZ,
						cvv_checked SMALLINT
					)
				""")

			cursor.execute("""SELECT 1 FROM information_schema.tables WHERE table_catalog = 'hsphere' AND table_schema = 'public' AND table_name = 'bck_contact_info'""")
			if cursor.fetchone() is None:
				cursor.execute("""
					CREATE TABLE bck_contact_info (
						id INT NOT NULL,
						name VARCHAR(80) NOT NULL,
						last_name VARCHAR(100),
						company VARCHAR(100),
						address1 VARCHAR(255) NOT NULL,
						address2 VARCHAR(255),
						city VARCHAR(60) NOT NULL,
						state VARCHAR(20) NOT NULL,
						postal_code VARCHAR(16) NOT NULL,
						country VARCHAR(2) NOT NULL,
						phone VARCHAR(30) NOT NULL,
						email VARCHAR(128) NOT NULL,
						state2 VARCHAR(255)
					)
				""")
			conn.commit()

	@staticmethod
	def _get_account_main_domain_name(cursor, account_id):
		cursor.execute("""SELECT d.name FROM domains d JOIN parent_child pc ON pc.child_id = d.id WHERE pc.account_id = %s ORDER BY d.id""" % account_id)
		(domain_name,) = cursor.get_one_row()
		return domain_name

	def _resume_account(self, account_id, customer_report):
		"""Resume the specified account.
		Does not raise
		"""
		with closing(self.conn.hsphere.db()) as cursor:
			subscription_name = self._get_account_main_domain_name(cursor, account_id)
			self.logger.info("Resuming H-Sphere account #%s (domain %s)" % (account_id, subscription_name))
			try:
				self._resume_account_api(cursor, account_id)
				self.suspended_accounts.discard(account_id)
			except Exception as e:
				self.logger.debug(u'Exception:', exc_info=e)
				if "java.net.ConnectException: Connection refused" in str(e):
					error_msg = u"Unable to resume the account #%s (domain %s): connection refused." % (account_id, subscription_name)
					solution_msg = u"Make sure that:\n1. You have enabled H-Sphere XML API.\n2. You have specified the actual TCP port the API is enabled on, in migration tool's configuration.\n3. The H-Sphere CP is running.\n4. The account is in expected state (active or suspended) in H-Sphere."
				else:
					error_msg = u"Unable to resume the account #%s (domain %s): %s" % (account_id, subscription_name, e)
					solution_msg = "Check the account status in H-Sphere CP and resume the account, if necessary."
				self.logger.error(error_msg)
				customer_report.add_issue(
					checking.Problem(
						'unable_resume_account', checking.Problem.ERROR,
						error_msg
					),
					solution_msg
				)

	def _resume_account_api(self, cursor, account_id):
		with self._get_hsphere_cp_runner() as cp_runner:
			self._run_cp_api_cmd(cp_runner, cursor, 'ResumeAccount', [('account_id', account_id)])

	def _check_if_account_may_be_suspended(self, account_id, accept_automatic_deletion=False):
		"""Return the list of issues preventing account suspension.
		It may not be suspended, if H-Sphere billing configuration will automatically resume it, or automatically delete it.
		"""
		with closing(self.conn.hsphere.db()) as cursor:
			subscription_name = self._get_account_main_domain_name(cursor, account_id)
			if self._will_accounts_be_auto_resumed(cursor, account_id):
				self.logger.debug(u"The account #%s (domain %s) will be automatically resumed shortly after we suspend it. Skipped suspending it." % (account_id, subscription_name))
				return [checking.Issue(
					checking.Problem(
						'suspend_wont_work', checking.Problem.ERROR, 
						u"Unable to safely suspend the account #%s (domain %s). According to the suspension rules of its vendor, it will be automatically resumed in a short while, and will continue to be charged the recurrent fees." % (account_id, subscription_name)
					),
					u"Disable the Auto Resuming rule in this account's vendor suspension rules, and re-run the migration tool's transfer-billing command. Please note that with Auto Resuming disabled, accounts of the other clients of this vendor will not be automatically resumed upon paying their debts."
				)]
			elif self._calculate_auto_delete_days(cursor, account_id) is not None and not accept_automatic_deletion:
				days = self._calculate_auto_delete_days(cursor, account_id)
				self.logger.debug(u"The account #%s (domain %s) will be automatically deleted after %s days if we suspend it. Skipped suspending it." % (account_id, subscription_name, days))
				return [checking.Issue(
					checking.Problem(
						'suspend_dangerous', checking.Problem.ERROR, 
						u"Unable to safely suspend the account #%s (domain %s). According to the suspension rules of its vendor, it will be automatically deleted after %s days of being in the suspended state." % (account_id, subscription_name, days)
					),
					u"""Disable the Account Deletion rule in this account's vendor Suspension Rules/Managing Debtors menu.
Alternatively, if automatic deletion of transferred accounts from H-Sphere, with all their content and hosting settings, is OK with you, run the migration tool with --accept-automatic-deletion option."""
				)]
			else:
				return []

	@staticmethod
	def _will_accounts_be_auto_resumed(cursor, account_id):
		"""Will the accounts of the given client be automatically resumed from suspended status?
		"""
		cursor.execute("""SELECT u.reseller_id FROM users u JOIN user_account ua ON ua.user_id = u.id WHERE ua.account_id = %s""" % account_id)
		(reseller_id,) = cursor.get_one_row()
		cursor.execute("""SELECT 1 FROM settings WHERE reseller_id = %s AND name = 'auto_resume' AND value = '1'""" % reseller_id)
		row = cursor.fetchone()
		if row is not None:
			return True
		else:
			return False

	@staticmethod
	def _calculate_auto_delete_days(cursor, account_id):
		"""This function does not consider the number of days for which the accounts of given client were suspended already.
		If an account is suspended, don't call this function - you should have notified provider about auto-deletion of this account earlier.
		"""
		cursor.execute("""SELECT u.reseller_id FROM users u JOIN user_account ua ON ua.user_id = u.id WHERE ua.account_id = %s""" % account_id)
		reseller_id = cursor.fetchvalue()
		# check if account will be deleted automatically
		cursor.execute("""SELECT 1 FROM settings WHERE reseller_id = '%s' AND name = 'is_del_reason' AND value = 'true'""" % reseller_id)
		if cursor.fetchone() is None:
			return None	# auto-deletion is not set for this account, return None days

		# calculate days till auto-delete
		cursor.execute("""SELECT value FROM settings WHERE reseller_id = %s AND name = 'del_term'""" % reseller_id)
		days = int(cursor.fetchvalue())
		return days

	def _was_account_info_blanked_out(self, account_id):
		return account_id in self.blanked_accounts

	def _blank_out_account_info(self, account_id):
		# TODO remember that account is blanked before blanking it
		self.blanked_accounts.update({account_id})
		with closing(self.conn.hsphere.db_connection()) as conn:
			cursor = conn.cursor()
			subscription_name = self._get_account_main_domain_name(cursor, account_id)
			self.logger.info("Blanking out H-Sphere account #%s (domain %s) billing info" % (account_id, subscription_name))
			self._blank_out_account_info_impl(cursor, account_id)
			conn.commit()
			# now that the account infos are changed, need to pull H-Sphere to reread them.
			with self._get_hsphere_cp_runner() as cp_runner:
				self._run_cp_api_cmd(cp_runner, cursor, 'RefreshAccountInfos', [('account_id', account_id)])

	@staticmethod
	def _blank_out_account_info_impl(cursor, account_id):
		"""Erase account's contact and billing e-mail addresses, to disable sending any notifications to the account.
		Erase account's credit card information - if chosen payment method is through a CC - to disable any automatic payments.
		Save the original values in the bck_* tables.
		"""
		email_to_set = 'N/A'

		cursor.execute("""SELECT bi_id FROM accounts WHERE id = %s""" % account_id)
		(bi_id,) = cursor.get_one_row()
		cursor.execute("""SELECT 1 FROM credit_card WHERE id = %s""" % bi_id)
		if cursor.fetchone() is not None:
			cursor.execute("""DELETE FROM bck_credit_card WHERE id = %s""" % bi_id)
			cursor.execute("""
				INSERT INTO bck_credit_card(id, cc_number, hidden_cc_number, encrypted_cc_number, name, exp_year, exp_month, exp_day, issueno, start_year, start_month, start_day, created, type, fatts, last_fatt, cvv_checked)
				SELECT id, cc_number, hidden_cc_number, encrypted_cc_number, name, exp_year, exp_month, exp_day, issueno, start_year, start_month, start_day, created, type, fatts, last_fatt, cvv_checked
				FROM credit_card WHERE id = %s
			""" % bi_id)
			cursor.execute("""DELETE FROM credit_card WHERE id = %s""" % bi_id)
		cursor.execute("""SELECT email, type FROM billing_info WHERE id = %s""" % bi_id)
		(email, bi_type) = cursor.get_one_row()
		if '@' in email or bi_type == 'CC':
			cursor.execute("""DELETE FROM bck_billing_info WHERE id = %s""" % bi_id)
			cursor.execute("""
				INSERT INTO bck_billing_info (id, name, last_name, company, address1, address2, city, state, postal_code, country, phone, email, type, state2, stop_date, reason, negative_date, exemption_code, ec_approved, ec_rejected)
				SELECT id, name, last_name, company, address1, address2, city, state, postal_code, country, phone, email, type, state2, stop_date, reason, negative_date, exemption_code, ec_approved, ec_rejected
				FROM billing_info WHERE id = %s
			""" % bi_id)
			new_bi_type = 'Check' if bi_type == 'CC' else bi_type
			cursor.execute("""UPDATE billing_info SET email = '%s', type = '%s' WHERE id = %s""" % (email_to_set, new_bi_type, bi_id))

		cursor.execute("""SELECT ci.id, ci.email FROM contact_info ci JOIN accounts a ON a.ci_id = ci.id WHERE a.id = %s""" % account_id)
		(ci_id, contact_email) = cursor.get_one_row()
		if '@' in contact_email:
			cursor.execute("""DELETE FROM bck_contact_info WHERE id = %s""" % ci_id)
			cursor.execute("""
				INSERT INTO bck_contact_info (id, name, last_name, company, address1, address2, city, state, postal_code, country, phone, email, state2)
				SELECT id, name, last_name, company, address1, address2, city, state, postal_code, country, phone, email, state2
				FROM contact_info WHERE id = %s
			""" % ci_id)
			cursor.execute("""UPDATE contact_info SET email = '%s' WHERE id = %s""" % (email_to_set, ci_id))

	def _restore_account_info(self, account_id, customer_report):
		with closing(self.conn.hsphere.db_connection()) as conn:
			cursor = conn.cursor()
			subscription_name = self._get_account_main_domain_name(cursor, account_id)
			self.logger.info("Restoring H-Sphere account #%s (domain %s) billing info" % (account_id, subscription_name))
			self._restore_account_info_impl(cursor, account_id)
			conn.commit()
			# now that the account infos are changed, need to pull H-Sphere to reread them.
			try:
				with self._get_hsphere_cp_runner() as cp_runner:
					self._run_cp_api_cmd(cp_runner, cursor, 'RefreshAccountInfos', [('account_id', account_id)])
			except Exception as e:
				self.logger.debug(u'Exception:', exc_info=e)
				if "java.net.ConnectException: Connection refused" in str(e):
					error_msg = u"Unable to apply the changes to the contact information of the account #%s (domain %s): connection refused." % (account_id, subscription_name)
					solution_msg = u"Make sure that:\n1. You have enabled H-Sphere XML API.\n2. You have specified the actual TCP port the API is enabled on, in migration tool's configuration.\n3. The H-Sphere CP is running.\nRestart the H-Sphere CP to apply the changes."
				else:
					error_msg = u"Unable to apply the changes to the contact information of the account #%s (domain %s): %s" % (account_id, subscription_name, e)
					solution_msg = u"Restart the H-Sphere CP to apply the changes."
				self.logger.error(error_msg)
				customer_report.add_issue(
					checking.Problem(
						'unable_restore_account_info', checking.Problem.ERROR,
						error_msg
					),
					solution_msg
				)
			else:  # if no exceptions
				self.blanked_accounts.discard(account_id)

	@staticmethod
	def _restore_account_info_impl(cursor, account_id):
		cursor.execute("""SELECT bi_id, ci_id FROM accounts WHERE id = %s""" % account_id)
		(bi_id, ci_id) = cursor.get_one_row()
		cursor.execute("""SELECT 1 FROM credit_card WHERE id = %s""" % bi_id)
		if cursor.fetchone() is None:
			cursor.execute("""
				INSERT INTO credit_card(id, cc_number, hidden_cc_number, encrypted_cc_number, name, exp_year, exp_month, exp_day, issueno, start_year, start_month, start_day, created, type, fatts, last_fatt, cvv_checked)
				SELECT id, cc_number, hidden_cc_number, encrypted_cc_number, name, exp_year, exp_month, exp_day, issueno, start_year, start_month, start_day, created, type, fatts, last_fatt, cvv_checked
				FROM bck_credit_card WHERE id = %s
			""" % bi_id)
		cursor.execute("""SELECT email, type FROM billing_info WHERE id = %s""" % bi_id)
		(bi_email, bi_type) = cursor.get_one_row()
		cursor.execute("""SELECT email, type FROM bck_billing_info WHERE id = %s""" % bi_id)
		if cursor.rowcount() == 1:
			(bck_bi_email, bck_bi_type) = cursor.get_one_row()
			if '@' not in bi_email and '@' in bck_bi_email:
				cursor.execute("""UPDATE billing_info SET email = '%s' WHERE id = %s""" % (bck_bi_email, bi_id))
			if bi_type != 'CC' and bck_bi_type == 'CC':
				cursor.execute("""UPDATE billing_info SET type = '%s' WHERE id = %s""" % (bck_bi_type, bi_id))

		cursor.execute("""SELECT email FROM contact_info WHERE id = %s""" % ci_id)
		(ci_email,) = cursor.get_one_row()
		cursor.execute("""SELECT email FROM bck_contact_info WHERE id = %s""" % ci_id)
		(bck_ci_email,) = cursor.get_one_row()
		if '@' not in ci_email and '@' in bck_ci_email:
			cursor.execute("""UPDATE contact_info SET email = '%s' WHERE id = %s""" % (bck_ci_email, ci_id))

	def _was_account_suspended(self, account_id):
		return account_id in self.suspended_accounts

	def _suspend_account(self, account_id):
		# TODO remember that account is suspended before suspending it
		self.suspended_accounts.update({account_id})
		with closing(self.conn.hsphere.db()) as cursor:
			subscription_name = self._get_account_main_domain_name(cursor, account_id)
			self.logger.info("Suspending the H-Sphere account #%s (domain %s)" % (account_id, subscription_name))
			self._preserve_apache_configs(cursor, subscription_name)
			self._suspend_account_api(cursor, account_id)
			self._resume_services(cursor, subscription_name)

	def _suspend_account_api(self, cursor, account_id):
		with self._get_hsphere_cp_runner() as cp_runner:
			self._run_cp_api_cmd(cp_runner, cursor, 'SuspendAccount', [('account_id', account_id), ('reason', u"This account was transferred to PPA.")])

	@cached
	def _read_ppa_data(self):
		existing_objects = read_yaml(self._get_session_file_path('ppa_existing_objects.yaml'))
		ppa_model = self._load_ppa_model()
		return PPAData(
			customers={login: customer.id for login, customer in existing_objects.customers.iteritems()},
			subscriptions={
				subscription.name: subscription.group_id 
				for client in ppa_model.clients.itervalues() 
				for subscription in client.subscriptions
			}
		)

	@cached
	def _upload_tools_class_files(self, cp_runner, class_files):
		"""Unlike _upload_java_class_files, this function uploads the class files into H-Sphere tools directory.
		These classes should have appropriate implementation, sufficient to be called like:
		java psoft.hsphere.tools.Estimator 2493
		"""
		for class_file in class_files:
			cp_runner.upload_file(self._get_hsphere_extras_local_path(class_file), "/hsphere/local/home/cpanel/hsphere/WEB-INF/classes/psoft/hsphere/tools/%s" % class_file)

	def _estimate_hsphere_payments(self, source_model, report):
		"""Return dictionary: {account_id: (hosting_price, domains_price, ssl_price)}
		"""
		with self._get_hsphere_cp_runner() as cp_runner:
			estimated_payments = {}
			for user in source_model.users:
				for account in user.accounts:
					self._upload_tools_class_files(cp_runner, ('Estimator.class',))
					output = cp_runner.sh("su - cpanel -c \"java psoft.hsphere.tools.Estimator {account_id}\"", {'account_id': account.account_id})
	
					estimated_payments_one_account = {}
					for line in output.strip().split('\n'):
						assert ': ' in line, "Malformed output from psoft.hsphere.tools.Estimator utility: %s" % output
						name, value = line.split(': ')
						estimated_payments_one_account[name] = float(value)
					estimated_payments[account.account_id] = (
						estimated_payments_one_account.get('Estimated monthly payment')
						+ estimated_payments_one_account.get('Estimated billing period payment'),
						estimated_payments_one_account.get('Estimated SRS payment'),
						estimated_payments_one_account.get('Estimated SSL payment')
					)
			return estimated_payments
