from parallels.ppa.source.hsphere import messages
import logging
import os
import sys
import itertools
import urllib2
from collections import defaultdict, namedtuple
from contextlib import closing, contextmanager

import parallels.core.migrator
from parallels.core import run_command, checking, MigrationError
from parallels.core.utils import ssh_utils, database_utils, windows_utils
from parallels.core.migrator import trace
from parallels.core.utils.yaml_utils import read_yaml, write_yaml
from parallels.core.utils.config_utils import ConfigSection
from parallels.core.migrator_config import is_transfer_resource_limits_enabled
from parallels.core.logging_context import log_context, subscription_context
from parallels.core import version_tuple
from parallels.core.utils.migrator_utils import get_package_scripts_file_path
from parallels.core.utils.common import \
		obj, cached, group_by, format_list, safe_string_repr, \
		group_by_id, find_only,\
		partition_list
from parallels.ppa.source.hsphere.client_subscription_converter import HSphereToPPAClientSubscriptionConverterAdapter
from parallels.ppa.source.hsphere.migration_list import HSphereMigrationList
from parallels.ppa.source.hsphere import resource_limits
from parallels.ppa.source.hsphere.content.mail import HSphereCopyMailContent
from parallels.plesk.source.plesk.infrastructure_checks import checks as infrastructure_checks
from parallels.core.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.ppa.source.hsphere.source_mail_server import HSphereSourceMailServer
from parallels.plesk.source.plesk.infrastructure_checks.checks import check_windows_copy_content_rsync_connection
import parallels.plesk.source.plesk
import connections
from parallels.ppa.source.hsphere 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()

	@staticmethod
	def is_dns_migration_enabled():
		"""Whether DNS migration for that source panel is enabled or not.

		We do not transfer DNS for H-Sphere so this method returns False.

		:rtype: bool
		"""
		return False

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

	@trace('fetch-source', messages.FETCH_INFO_FROM_SOURCE_SERVERS)
	def _fetch_source(self, options, overwrite):
		self.logger.info(messages.FETCH_HSPHERE_SERVERS_INFORMATION)
		backup_filename = self.get_raw_dump_filename(self.conn.hsphere.conn_settings.id)
		if not overwrite and os.path.exists(backup_filename):
			self.logger.info(messages.SKIP_LOADING_CACHED_SOURCE_PANEL_DATA)
		else:
			report = checking.Report(messages.FETCHED_ACCOUNT_SUBSCRIPTION_ISSUES, None)
			self.logger.info(messages.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(
							messages.UNABLE_TO_CONTINUE_MIGRATION)
					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_servers['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_target_model().iter_all_subscriptions(),
			lambda s: s.name == subscription_name,
			error_message=messages.FAILED_TO_FIND_SUBSCRIPTION_BY_NAME)

		backup = self.load_raw_dump(self.source_servers[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.ppa.source.hsphere.__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(messages.UNABLE_FIND_DOMAIN_IN_HSPHERE % 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(messages.GET_MAPPING_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_dump(self.source_servers['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_subscription_first_dns_ip(s, s)
			subscription_to_server_map[s] = ppa_dns_ip

		for dns_server in dns_domain_mapping:
			self.logger.info(messages.LOG_SET_DNS_FORWARDING, 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, 
								messages.FAILED_TO_SET_DNS_FORWARDING % (
									domain, unicode(e)
								)
							),
							messages.CONFIGURE_ZONE_ON_SOURCE_SERVER_SLAVE)

	def _convert_accounts(self, converted_resellers, report):
		converter_adapter = HSphereToPPAClientSubscriptionConverterAdapter()
		return converter_adapter.convert(self.global_context, converted_resellers, report)

	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_dump.iter_domains():
					if site.hosting_type not in ('phosting', 'vrt_hst', 'sub_hst'):
						self.logger.debug(messages.SKIP_TRANSFER_ACCESS_RESTRICTION_SETTINGS % (site.name, subscription.name))
						continue

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

		self.logger.info(messages.TRANSFER_ACCESS_RESTRICTION_SETTINGS % (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(messages.TRANSFER_ACCESS_RESTRICTION_SETTINGS_FOR_SITES % subscription_name)
			for site_info in subscription_site_infos:
				with safe.try_subscription(site_info.subscription_name, messages.TRANSFER_ACCESS_RESTRICTION_SETTINGS_OF_SITE % 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_servers['hsphere'].ip,))

	####################################### check-infrastructure ####################################
	def _check_windows_copy_mssql_db_content(self, checks, report):
		self.logger.info(messages.CHECKING_MSSQL_CONNECTIONS)

		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.plesk.source.plesk, 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('hsphere'),
					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(messages.CHECKING_RSYNC_CONNECTIONS_WINDOWS)
		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(messages.CHECK_RSYNC_AND_SSH_CONNECTIONS_APACHE)
		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(messages.CHECKING_RSYNC_AND_SSH_CONNECTIONS_POSTFIX)
		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(messages.CHECKING_SCP_AND_SSH_CONNECTIONS)
		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() and not source.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_dump(self.source_servers['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() if db.dbtype =='mysql']

				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,
								messages.UNABLE_TO_CHECK_SCP_CONNECTION % (
									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])
								)
							),
							messages.CONNECTION_ISSUE_SOLUTION_SSH.format(
								host=(
									messages.SOURCE_SERVER_DESCRIPTION % 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,
							messages.SUCCESSFULLY_CHECKED_SCP_CONNECTION % (
								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, messages.INTERNAL_ERROR_CHECK_CONNECTION_FOR_UNKNOWN_CONTENT_TYPE % 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,
								messages.UNABLE_TO_CHECK_RSYNC_CONNECTION % (
									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])
								)
							),
							messages.CONNECTION_ISSUE_SOLUTION.format(cmd=windows_utils.format_command(cmd, **args))
						)
					else:
						report.add_issue(
							checking.Problem(issue_id_by_ctype[content_type], checking.Problem.INFO,
								messages.CHECK_RSYNC_CONNECTION_OK % (
									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(
						messages.UNABLE_TO_CALCULATE_SIZE_OF_MYSQL_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(messages.UNABLE_TO_CALCULATE_MSSQL_DBS_SIZE % (" ".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(messages.UNABLE_CALCULATE_DISK_SPACE_FOR_WINDOWS_USERS % (" ".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(messages.UNABLE_TO_CALCULATE_WEB_DISK_SPACE % (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(messages.UNABLE_CALCULATE_DISK_SPACE_MAILBOXES % (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(messages.CALCULATING_DISK_SPACE_USAGE_SOURCE_UNIX)
		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_dump(self.source_servers['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_dump(self.source_servers['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 = []
		mysql_unix_databases = []
		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
				))
				mysql_unix_databases.append(database_info)

		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(messages.CHECKING_IF_THERE_IS_ENOUGH_DISK_UNIX)
		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(), mysql_unix_databases
			)

	@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:
					self.logger.debug(messages.LOG_EXCEPTION, exc_info=True)
					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, messages.HSPHERE_INCONSISTENCY_PHYSICAL_SERVER_FOR_LOGICAL_SERVER_IP_DOES_NOT_EXIST % l_server_ip
				return obj(login=row[0], password=row[1])

		self.logger.info(messages.CALCULATING_DISK_SPACE_USAGE_SOURCE_WINDOWS)
		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_dump(self.source_servers['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
					),
					source_db_ip=source.host(),
				))

		db_usages = []  # [ obj(dst_host, dst_port, target_node, usage) ]
		for source_db_ip, db_infos in group_by(dsi, lambda si: si.source_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(source_db_ip)
			source_mssql_usage = self._get_mssql_disk_usage(
				source_db_ip, soap_port, pserver_creds.login, pserver_creds.password, db_to_dst_server.keys()
			)
			for db_name, usage in source_mssql_usage.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(messages.CHECK_DISK_SPACE_TARGET_WINDOWS)
		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),
				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.ppa.source.hsphere.__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', messages.TRANSFER_ACCOUNT_RESOURCE_LIMITS_AND_USAGE)
	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_target_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_dump(self.source_servers['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(
				messages.BILLING_MOVE_CANNOT_BE_REVERTED_WARNING)
			while True:
				choice = raw_input().lower()
				if choice == 'yes':
					self.logger.info(messages.FINAL_BILLING_TRANSFER_CONFIRMED)
					break
				elif choice == 'no':
					self.logger.info(messages.FINAL_TRANSFER_OF_PPA_BILLING_WAS_CANCELLED)
					sys.exit(0)
				else:
					print messages.INVALID_CHOICE_ENTER_YES_OR_NO % choice

		self._fetch(options)
		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(messages.REPORT_BILLING_TRANSFER, 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(messages.LOG_EXCEPTION, exc_info=True)
			self.logger.warning(messages.ERROR_MAKING_BILLING_AND_CONTACT_INFO_BACKUP % 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 messages.HSPHERE_USERS_AND_ACCOUNTS_STATS % (
						len(succeeded_customers),
						sum([len(account.hosting_subscriptions) for account in succeeded_customers])
					)
				print messages.NOT_TRANSFERRED_STATS % (
					len(failed_customers),
					sum([len(account.hosting_subscriptions) for account in failed_customers])
				)
		else:
			if test_mode:
				print messages.NO_ISSUES_FOUND
			else:
				accounts_qty = sum([len(account.hosting_subscriptions) for account in target_model.accounts])
				print messages.TRANSFER_SUCCESS_STATS % (
					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(
				messages.INVALID_VALUE_FOR_BILLING_MODE % (
					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(messages.LOG_EXCEPTION, exc_info=True)
			raise MigrationError(
				messages.FAILED_TO_OBTAIN_PPA_BILLING_API_URL % e
			)
		proto, path = ppab_api.url.split('//')
		full_url = "%s//%s:%s@%s" % (proto, ppab_api.user, ppab_api.password, path)
		self.logger.info(messages.LOG_BILLING_URL, full_url)

		try:
			ppab_version = ppab_api.get_version().Version
		except Exception as e:
			self.logger.debug(messages.LOG_EXCEPTION, exc_info=True)
			raise MigrationError(
				messages.TEST_CALL_PPA_BILLING_API_FAILED % e
			)
		self.logger.debug(messages.BILLING_VERSION % ppab_version)

		required_version = "5.7.6"
		if version_tuple(ppab_version) < version_tuple(required_version):
			raise MigrationError(
				messages.PPA_BILLING_VERSION_IS_LESS_THAN_REQUIRED % (
					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_servers['hsphere'].ip
		return "http://%s:%s/psoft/servlet/services/" % (ip, self.source_servers['hsphere'].api_port)

	def _check_hsphere_billing(self):
		services_url = self._get_hsphere_api_url() + 'AdminServices'
		self.logger.info(messages.DEBUG_HSPHERE_ADMIN_SERVICES_API_URL % 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(messages.LOG_EXCEPTION, exc_info=True)
			raise MigrationError(messages.FAILED_TO_DOWNLOAD_SCHEMA_OF_XML_API % e)
		self.logger.debug(messages.LOG_XML_API_SCHEMA, 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(messages.REQUIRED_API_METHODS_ARE_ABSENT % (
				u", ".join(required_methods), schema_url)
			)

	@trace('transfer-billing', messages.TRANSFER_HSPHERE_USERS_AND_THEIR_ACCOUNTS)
	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', messages.CHECK_IF_USERS_AND_ACCOUNTS_CAN_BE_TRANSFERRED)
	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, \
				messages.INTERNAL_ERROR_USER_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, 
						messages.NO_CUSTOMER_IN_PPA % (
							billing_account.login
						)
					),
					messages.TRANSFER_CUSTOMER_TO_PPA_BEFORE_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(messages.LOG_TRANSFER_HSPHERE_USER, 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(messages.LOG_EXCEPTION, exc_info=True)
					issue = checking.Issue(
						checking.Problem(
							'unable_import_general_information', checking.Problem.ERROR, 
							messages.UNABLE_IMPORT_CUSTOMER_INTO_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)
					raise UserMigrationFailed(
						messages.FAILED_IMPORT_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, \
						messages.INTERNAL_ERROR_ACCOUNT_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,
								messages.HSPHERE_ACCOUNT_NOT_TRANSFERRED_BECAUSE_OF_OTHER_ACCOUNTS_ERRORS % (
									hsphere_account.account_id, hsphere_account.main_domain_name
								)
							),
							messages.RESOLVE_ERRORS_WITH_OTHER_ACCOUNTS_OF_CUSTOMER)
						self.logger.warning(issue.problem.description)
						customer_report.add_issue_obj(issue)
					raise UserMigrationFailed(
						messages.ASSUMING_USER_MIGRATION_FAILED % hsphere_user.login
					)
				if test_mode:
					raise UserMigrationFailed(
						messages.FAILED_TO_ROLL_BACK)
					
				ppab_api.commit_transaction()
				self.logger.debug(messages.ENABLE_BILLING_TAB)
				self.conn.target.poa_api().account_added_to_pba(customer_id)
			except Exception as e:
				if type(e) != UserMigrationFailed:
					self.logger.debug(messages.LOG_EXCEPTION, exc_info=True)
					issue = checking.Issue(
						checking.Problem(
							'unable_to_transfer_user', checking.Problem.ERROR, 
							messages.UNABLE_TO_TRANSFER_HSPHERE_USER_TO_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(messages.LOG_TRANSFER_HSPHERE_ACCOUNT, hsphere_account.account_id, hosting_subscription.name)
		with subscription_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(messages.LOG_EXCEPTION, exc_info=True)
					if "java.net.ConnectException: Connection refused" in str(e):
						error_msg = messages.UNABLE_TO_SUSPEND_ACCOUNT_CONNECTION_REFUSED % (
							hsphere_account.account_id, hosting_subscription.name
						)
						solution_msg = messages.HSPHERE_API_SOLUTION
					else:
						error_msg = messages.UNABLE_TO_SUSPEND_HSPHERE_ACCOUNT % (
							hsphere_account.account_id, hosting_subscription.name, e
						)
						solution_msg = messages.CHECK_ACCOUNT_STATUS_IN_HSPHERE_AND_AND_RESUME
					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, 
							messages.UNABLE_TO_IMPORT_ACCOUNT % (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,
							messages.COST_TOO_BIG % (
								limit_type, hosting_type, account_id, domain_name, source_cost,
								subscription_type, target_cost, limit_type, limit
							)
						),
						messages.ADJUST_PRICE_PLAN_IN_PPA_BILLING_OR_VALUE_IN_MIGRATOR_CONFIG % (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(messages.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(messages.LOG_EXCEPTION, exc_info=True)
						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(messages.ACCOUNT_MIGRATION_FAILED % (
						hsphere_account.account_id, hosting_subscription.name
					))
				else:
					return True
			except Exception as e:
				if type(e) != AccountMigrationFailed:
					self.logger.debug(messages.LOG_EXCEPTION, exc_info=True)
					issue = checking.Issue(
						checking.Problem(
							'unable_transfer_account_to_billing', checking.Problem.ERROR, 
							messages.UNABLE_TO_TRANSFER_ACCOUNT % (
								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', messages.RETRIEVE_BILLING_INFORMATION_FROM_HSPHERE)
	def _retrieve_billing_info(self, report):
		model = self._load_target_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_dump(self.source_servers['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, messages.HSPHERE_INCONSISTENCY_PHYSICAL_SERVER_WITH_IP_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_dump(self.source_servers['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(messages.LOG_RESUME_ACCOUNT, 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(messages.LOG_EXCEPTION, exc_info=True)
				if "java.net.ConnectException: Connection refused" in str(e):
					error_msg = messages.UNABLE_TO_RESUME_ACCOUNT_CONNECTION_REFUSED % (account_id, subscription_name)
					solution_msg = messages.CHECK_HSPHERE_API_RESUME_ACCOUNT
				else:
					error_msg = messages.UNABLE_TO_RESUME_ACCOUNT % (account_id, subscription_name, e)
					solution_msg = messages.CHECK_ACCOUNT_STATUS_IN_HSPHERE_CP_AND_RESUME
				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(messages.ACCOUNT_AUTOMATICALLY_RESUMED % (account_id, subscription_name))
				return [checking.Issue(
					checking.Problem(
						'suspend_wont_work', checking.Problem.ERROR, 
						messages.UNABLE_TO_SUSPEND_ACCOUNT % (account_id, subscription_name)
					),
					messages.DISABLE_AUTO_RESUMING_RULE)]
			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(messages.ACCOUNT_WILL_BE_AUTOMATICALY_DELETED % (account_id, subscription_name, days))
				return [checking.Issue(
					checking.Problem(
						'suspend_dangerous', checking.Problem.ERROR, 
						messages.UNABLE_TO_SAFELY_SUSPEND_ACCOUNT % (account_id, subscription_name, days)
					),
					messages.DISABLE_ACCOUNT_DELETION_RULE)]
			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(messages.LOG_BLANK_OUT_ACCOUNT, 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(messages.LOG_RESTORE_ACCOUNT % (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 = messages.UNABLE_TO_APPLY_CHANGES_TO_CONTACT_INFO % (account_id, subscription_name)
					solution_msg = messages.CHECK_HSPHERE_API_RESTORE_ACCOUNT_INFO
				else:
					error_msg = messages.UNABLE_TO_CHANGE_CONTACT_DATA % (account_id, subscription_name, e)
					solution_msg = messages.RESTART_HSPHERE_CP_TO_APPLY_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', messages.ACCOUNT_WAS_TRANSFERRED_PPA)]
			)

	@cached
	def _read_ppa_data(self):
		existing_objects = read_yaml(self.session_files.get_path_to_existing_objects_model())
		ppa_model = self._load_target_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, messages.BAD_OUTPUT_ESTIMATOR % 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(messages.ESTIMATED_BILLING_PERIOD_PAYMENT),
						estimated_payments_one_account.get('Estimated SRS payment'),
						estimated_payments_one_account.get('Estimated SSL payment')
					)
			return estimated_payments
