<?php
/*
* Copyright (c) 2009-2010 ATMAIL. All rights reserved
* See http://atmail.com/license.php for license agreement
*/

/**
 * Migrate from Atmail 5.x to Atmail 6.x system
 *
 * @author Brad Kowalczyk <brad@staff.atmail.com>
 */

// *********** INIT / SETUP ************* //
define('APP_ROOT', dirname(__FILE__) . DIRECTORY_SEPARATOR);
set_include_path(APP_ROOT . "../../library/" . PATH_SEPARATOR . APP_ROOT . "../../library/Atmail" . PATH_SEPARATOR . "library/" . PATH_SEPARATOR . get_include_path());
require_once 'Zend/Loader/Autoloader.php';
$loader = Zend_Loader_Autoloader::getInstance();
$loader->setFallbackAutoloader(true);
$loader->suppressNotFoundWarnings(false);
require_once 'lib/migrate.php';
require_once 'lib/stats.php';

$overallTotal = 0;
$overallTime = microtime_float();

function displayHelp($options)
{
?>
	This utility will migrate all your data from an existing
	Atmail 5 database to your current Atmail 6 installation.
	 
	 Usage: <?php echo $_SERVER['argv'][0]; ?> <options>
		 --help, -help, -h, or -? :	to get this help.
		 --version : to return the version of this file.
<?php
}

$options = array(
array("force", "skip any sql errors and continue", "false"),
array("a5_db", "Atmail 5 database tablename", "atmail5"),
array("a5_host", "Atmail 5 database host", "localhost"),
array("a5_user", "Atmail 5 database username", "root"),
array("a5_pass", "Atmail 5 database password", ""),
array("a6_db", "Atmail 6 database tablename", "atmail6"),
array("a6_host", "Atmail 6 database host", "localhost"),
array("a6_user", "Atmail 6 database username", "root"),
array("a6_pass", "Atmail 6 database password", ""),
array("userCacheSize", "Amount of users to cache when verifying entries", "20000"),
array("skipGroups", "skip migrating groups", "false"),
array("skipRelays", "skip migrating mail relays", "false"),
array("skipDomains", "skip migrating domains", "false"),
array("skipAliases", "skip migrating mail aliases", "false"),
array("skipWhiteLists", "skip migrating auto white lists", "false"),
array("skipSpamSettings", "skip migrating spam settings", "false"),
array("skipUserSessions", "skip migrating user sessions", "false"),
array("skipAccounts", "skip migrating accounts", "false"),
array("skipAdminUsers", "skip migrating admin users", "false"),
array("skipAdminGroups", "skip migrating admin groups", "false"),
array("skipUserSettings", "skip migrating user settings", "false"),
array("skipAddressBooks", "skip migrating address books", "false"),
array("skipAddressBookGroups", "skip migrating address book groups", "false"),
array("skipAddressBookShared", "skip migrating shared address book", "false"),
array("skipUsers", "skip migrating users", "false"),
array("skipNonthreadable", "skip all non-threadable tasks (use on second or more instance of script)", "false"),
array("rangeStart", "Letter to start migrating from for this instance", "a"),
array("rangeEnd", "Letter to migrate to for this instance", "z"),
array("rangeOther", "Include the 'other' users to migrate for this instance", "true"),
);

$arguments = processOptions($_SERVER['argc'], $_SERVER['argv'], $options);

if($arguments['skipNonthreadable'])
{
	$arguments['skipGroups'] = 1;
	$arguments['skipRelays'] = 1;
	$arguments['skipDomains'] = 1;
	$arguments['skipAliases'] = 1;
	$arguments['skipWhiteLists'] = 1;
	$arguments['skipSpamSettings'] = 1;
	$arguments['skipAccounts'] = 1;
	$arguments['skipAdminUsers'] = 1;
	$arguments['skipAdminGroups'] = 1;
	$arguments['skipAddressBookShared'] = 1;
	$arguments['skipUsers'] = 1;
}

// *********** CONNECT DATABASES ************* //
$pdoParams = array(
	PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
	PDO::ATTR_EMULATE_PREPARES => true,
	PDO::ATTR_PERSISTENT => false
);

$oldParams = array(
	'host'		   => $arguments['a5_host'],
	'username'	   => $arguments['a5_user'],
	'password'	   => $arguments['a5_pass'],
	'dbname'		 => $arguments['a5_db'],
	'driver_options' => $pdoParams
);
	
$newParams = array(
	'host'		   => $arguments['a6_host'],
	'username'	   => $arguments['a6_user'],
	'password'	   => $arguments['a6_pass'],
	'dbname'		 => $arguments['a6_db'],
	'driver_options' => $pdoParams
);

$oldDb = Zend_Db::Factory('Pdo_Mysql', $oldParams, $pdoParams);
$newDb = Zend_Db::Factory('Pdo_Mysql', $newParams, $pdoParams);

try
{
	$oldDb->getConnection();
}
catch (Exception $e)
{
	die("Could not connect to database " . $arguments['a5_db'] . "\n");	
}

try
{
	$newDb->getConnection();
}
catch (Exception $e)
{
	die("Could not connect to database " . $arguments['a6_db'] . "\n");	
}

checkA5Schema($oldDb);
$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);
$range = getSchemaRange($oldDb, $arguments['rangeStart'], $arguments['rangeEnd'], $arguments['rangeOther']);

// ********** MIGRATE GROUP DATA ************ //
if(!$arguments['skipGroups'])
{
	$oldDb->getConnection();
	$newDb->getConnection();

	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Groups");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating groups... (" . $total . ")\n");

	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select * from Groups");
	
	resetStats();
	
	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			if(strtolower($row->GroupName) == 'default')
			{
				// lets migrate the default group settings as best we can
				// the new database SHOULD already have a default group setting
				// so lets remove and rewrite
				if($row->GroupPrice == '')
					$row->GroupPrice = NULL;
				$newDb->query("delete from Groups where GroupName = 'default'");
				$newDb->query("insert into Groups (GroupName, GroupDescription, GroupPrice, GroupQuota, MailForwarding, SMSSupport, POP3Support, IMAPSupport, MultiSupport, PersonalSpam, GlobalAbookRead, AV, SpamSupport, Sync, PushSupport) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", array('default', $row->GroupDescription, $row->GroupPrice, $row->GroupQuota, $row->MailForwarding, $row->SMSSupport, $row->POP3Support, $row->IMAPSupport, $row->MultiSupport, $row->PersonalSpam, $row->GlobalAbookRead, $row->AV, $row->SpamSupport, $row->Sync, $row->PushSupport));				
			}
			else
			{
				$newDb->query("insert into Groups (GroupName, GroupDescription, GroupPrice, GroupQuota, MailForwarding, SMSSupport, POP3Support, IMAPSupport, MultiSupport, PersonalSpam, GlobalAbookRead, AV, SpamSupport, Sync, PushSupport) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", array($row->GroupName, $row->GroupDescription, $row->GroupPrice, $row->GroupQuota, $row->MailForwarding, $row->SMSSupport, $row->POP3Support, $row->IMAPSupport, $row->MultiSupport, $row->PersonalSpam, $row->GlobalAbookRead, $row->AV, $row->SpamSupport, $row->Sync, $row->PushSupport));
			}
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE RELAY DATA ************ //
if(!$arguments['skipRelays'])
{
	$oldDb->getConnection();
	$newDb->getConnection();

	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from MailRelay");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating mail relays... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select * from MailRelay");
	
	resetStats();
	
	while ($row = $stmt->fetch())
	{
		try
		{		
			displayStats($count, $total);
			$newDb->query("insert into MailRelay (IPaddress, DateAdded, Account) values (?,?,?)", array($row->IPaddress, $row->DateAdded, $row->Account));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE DOMAIN DATA ************ //
if(!$arguments['skipDomains'])
{
	$oldDb->getConnection();
	$newDb->getConnection();

	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Domains");
	$total = $stmt->fetch();
	$total = $total->total;

	fwrite(STDOUT, "Migrating domains... (" . $total . ")\n");
	$overallTotal += $total;

	$count = 0;

	unset($stmt);
	$stmt = $oldDb->query("select * from Domains");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into Domains (Hostname, Enable) values (?,?)", array($row->Hostname, '1'));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}


// ********** MIGRATE MAIL ALIAS DATA ************ //
if(!$arguments['skipAliases'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from MailAliases");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;
	
	fwrite(STDOUT, "Migrating mail aliases... (" . $total . ")\n");
	$overallTotal += $total;
	
	unset($stmt);
	$stmt = $oldDb->query("select * from MailAliases");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into MailAliases (AliasName, AliasTo, Domain, DateCreate, AliasMailDir) values (?,?,?,?,?)", array($row->AliasName, $row->AliasTo, $row->Domain, $row->DateCreate, $row->AliasMailDir));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE AUTO WHITE LIST DATA ************ //
if(!$arguments['skipWhiteLists'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from awl");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating auto white lists... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select * from awl");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into awl (username, email, ip, count, totscore, IndexDate) values (?,?,?,?,?,?)", array($row->username, $row->email, $row->ip, $row->count, $row->totscore, $row->IndexDate));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE SPAM SETTINGS DATA ************ //
if(!$arguments['skipSpamSettings'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from SpamSettings where username in (select Account from UserSession)");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating spam settings... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select username, preference, value, domain from SpamSettings where username in (select Account from UserSession)");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into SpamSettings (username, preference, value, domain) values (?,?,?,?)", array($row->username, $row->preference, $row->value, $row->domain));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");

	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from SpamSettings where username not in (select Account from UserSession)");
	$orphaned = $stmt->fetch();
	$orphaned = $orphaned->total;
	
	if($orphaned)
	{
		fwrite(STDOUT, "Found " . $orphaned . " orphaned entries\n");
	}
}

// ********** MIGRATE USER SESSION DATA ************ //
if(!$arguments['skipUserSessions'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from UserSession");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating user sessions... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select * from UserSession");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into UserSession (Account, Password, SessionID, LastLogin, SessionData, ChangePass, modified, lifetime, CalUser) values (?,?,?,?,?,?,?,?,?,?)", array($row->Account, $row->Password, $row->SessionID, $row->LastLogin, $row->SessionData, '0', '0' ,'0' , '0'));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE ACCOUNT DATA ************ //
if(!$arguments['skipAccounts'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Accounts");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating accounts... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select * from Accounts");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into Accounts (UserAccount, Account, Type, MailServer, UseSSL) values (?,?,?,?,?)", array($row->UserAccount, $row->Account, $row->Type, $row->MailServer, $row->UseSSL));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE ADMIN USERS DATA ************ //
if(!$arguments['skipAdminUsers'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from AdminUsers");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating administrator users... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$stmt = $oldDb->query("select * from AdminUsers");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			// Convert quota from KB to MB
			$quota = round($row->NumQuota / 1024);

			$newDb->query("insert into AdminUsers (Username, Password, sessionData, UAdd, UDelete, UModify, UPurge, USearch, UList, UMigrate, ULogs, UAlias, NumUsers, Company, Fullname, DateCreate, SessionID, LastLogin, modified, ipAddress, NumQuota, UBrand, BrandDomain, UGroupAssign, UAll, UMasterAdmin) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", array($row->Username, md5($row->Password), '', $row->UAdd, $row->UDelete, $row->UModify, $row->UPurge, $row->USearch, $row->UList, $row->UMigrate, $row->ULogs, $row->UAlias, $row->NumUsers, $row->Company, $row->Fullname, $row->DateCreate, $row->SessionID, $row->LastLogin, '0', '0', $quota, $row->UBrand, $row->BrandDomain, $row->UGroupAssign, $row->UAll, '0'));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}

// ********** MIGRATE ADMIN GROUPS DATA ************ //
if(!$arguments['skipAdminGroups'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from AdminGroup");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;
	
	fwrite(STDOUT, "Migrating administrator groups... (" . $total . ")\n");
	$overallTotal += $total;
	
	unset($stmt);
	$stmt = $oldDb->query("select Username, Ugroup, Domain from AdminGroup");

	resetStats();

	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$newDb->query("insert into AdminGroup (Username, Ugroup, Domain) values (?,?,?)", array($row->Username, $row->Ugroup, $row->Domain));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
	}
	fwrite(STDOUT, "\n");
}


// ********** MIGRATE USER SETTINGS DATA ************ //
if(!$arguments['skipUserSettings'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	// Select the CalDavURL
	$caldavUrl = $newDb->fetchOne("select keyValue from Config where keyName = 'CalDavURL'");
	$langConversionLookup = array(
		'german' => 'de',
		'english' => 'en',
		'spanish' => 'sp',
		'french' => 'fr',
		'italian' => 'it'
	);
	
	// *** COUNT TOTAL *** //
	$total=0;
	$count=0;
	foreach ($range as $a) 
	{

		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select count(*) as total from UserSettings_$a where Account in (select Account from UserSession)");
		$total_temp = $stmt->fetch();
		$total += $total_temp->total;
	}

	fwrite(STDOUT, "Migrating user settings... (" . $total . ")\n");
	$overallTotal += $total;

	resetStats();

	foreach ($range as $a) 
	{
		$oldDb->getConnection();
		$newDb->getConnection();
		
		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select * from UserSettings_$a where Account in (select Account from UserSession)"); 
		
		while ($row = $stmt->fetch()) 
		{
			// revert to english if language is not supported in Atmail6
			if( array_key_exists(strtolower($row->Language), $langConversionLookup) )
			{
				$row->Language = $langConversionLookup[strtolower($row->Language)];	
			}
			else
			{
				$row->Language = 'en';	
			}

			try
			{
				displayStats($count, $total);
				$newDb->query("insert into UserSettings (Account, RealName, Refresh, TimeZone, MsgNum, HtmlEditor, ViewThreads, ReplyTo, Signature, Language, AutoTrash, MailAuth, AbookTrusted, DateFormat, TimeFormat, AutoComplete, EmailEncoding, DisplayImages, UseSSL, CalDavUrl) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", array($row->Account, $row->RealName, $row->Refresh, $row->TimeZone, $row->MsgNum, $row->HtmlEditor, 1, $row->ReplyTo, $row->Signature, $row->Language, $row->AutoTrash, $row->MailAuth, $row->AbookTrusted, $row->DateFormat, $row->TimeFormat, $row->AutoComplete, $row->EmailEncoding, $row->DisplayImages, $row->UseSSL, $caldavUrl));
			}
			catch (Exception $e)
			{
				if(!$arguments['force'])
				{
					testExit($e);
				}
			}
			$count++;
                        
                        // HACK! keep $oldDb connection alive
                        pingOldDb();
		}
	}
	fwrite(STDOUT, "\n");
	
	$orphaned=0;
	foreach ($range as $a) 
	{

		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select count(*) as total from UserSettings_$a where Account not in (select Account from UserSession)");
		$total_temp = $stmt->fetch();
		$orphaned += $total_temp->total;
	}
	
	if($orphaned)
	{
		fwrite(STDOUT, "Found " . $orphaned . " orphaned entries.\n");
	}
}


// ********** MIGRATE ADDRESS BOOK DATA ************ //
if(!$arguments['skipAddressBooks'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	// *** COUNT TOTAL *** //
	$total=0;
	$count=0;
	foreach ($range as $a) 
	{

		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select count(*) as total from Abook_$a  where Account in (select Account from UserSession)");
		$total_temp = $stmt->fetch();
		$total += $total_temp->total;
	}
	
	fwrite(STDOUT, "Migrating address books... (" . $total . ")\n");
	$overallTotal += $total;

	resetStats();

	foreach ($range as $a) 
	{
		$oldDb->getConnection();
		$newDb->getConnection();
		
		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select * from Abook_$a where Account in (select Account from UserSession)");
		while ($row = $stmt->fetch()) 
		{
			if (!strpos($row->UserEmail, '@'))
			{
				$row->UserEmail = '';
			}

			$row->id = null;
			unset($row->UserPgpKey);
			unset($row->UserSMIMEcert);

			// Convert to array
			$arr = array();
			foreach ((array)$row as $v)
			{
				$arr[] = $v;	
			}

			try 
			{
				displayStats($count, $total);
				$data = array($row->UserEmail, $row->UserEmail2, $row->UserEmail3, $row->UserEmail4, $row->UserEmail5,
					  $row->UserFirstName, $row->UserMiddleName, $row->UserLastName, $row->UserTitle,
								  $row->UserGender, $row->UserDOB, $row->UserHomeAddress, $row->UserHomeCity, $row->UserHomeState,
								  $row->UserHomeZip, $row->UserHomeCountry, $row->UserHomePhone, $row->UserHomeMobile,
								  $row->UserHomeFax, $row->UserURL, $row->UserWorkCompany, $row->UserWorkTitle, $row->UserWorkDept,
								  $row->UserWorkOffice, $row->UserWorkAddress, $row->UserWorkCity, $row->UserWorkState, $row->UserWorkZip,
								  $row->UserWorkCountry, $row->UserWorkPhone, $row->UserWorkMobile, $row->UserWorkFax, $row->UserType,
								  $row->UserInfo, $row->Account, $row->DateAdded, $row->DateModified, $row->UserPhoto, $row->UserFileAs, 0, 0);

							$newDb->query(	"insert into Abook ( UserEmail, UserEmail2, UserEmail3, UserEmail4, UserEmail5, "
					.	"UserFirstName, UserMiddleName, UserLastName, UserTitle, UserGender, UserDOB, "
					.	"UserHomeAddress, UserHomeCity, UserHomeState, UserHomeZip, UserHomeCountry, UserHomePhone, UserHomeMobile, UserHomeFax, "
					.	"UserURL, UserWorkCompany, UserWorkTitle, UserWorkDept, UserWorkOffice, UserWorkAddress, UserWorkCity, UserWorkState, UserWorkZip, UserWorkCountry, "
					.	"UserWorkPhone, UserWorkMobile, UserWorkFax, UserType, UserInfo, Account, DateAdded, DateModified, UserPhoto, UserFileAs, "
				.	"Global, Shared) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", $data);
			}
			catch (Exception $e)
			{
				if(!$arguments['force'])
				{
					testExit($e);
				}
			}
			$count++;

                        // HACK! keep $oldDb connection alive
                        pingOldDb();
		}
	}
	fwrite(STDOUT, "\n");
	// *** COUNT TOTAL *** //
	$orphaned=0;
	foreach ($range as $a) 
	{

		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select count(*) as total from Abook_$a  where Account not in (select Account from UserSession)");
		$total_temp = $stmt->fetch();
		$orphaned += $total_temp->total;
	}
	if($orphaned)
	{
		fwrite(STDOUT, "Found " . $orphaned . " orphaned entries.\n");
	}
}

// ********** MIGRATE ADDRESS BOOK GROUP DATA ************ //
if(!$arguments['skipAddressBookGroups'])
{
	$oldDb->getConnection();
	$newDb->getConnection();

$oldDb->query("select 1");
	
	// *** COUNT TOTAL *** //
	$total=0;
	$count=0;
	foreach ($range as $a) 
	{

		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select count(*) as total from AbookGroup_$a where Account in (select Account from UserSession)");
		$total_temp = $stmt->fetch();
		$total += $total_temp->total;
	}
	
	fwrite(STDOUT, "Migrating address book groups... (" . $total . ")\n");
	$overallTotal += $total;

	resetStats();

	foreach ($range as $a) 
	{
		$oldDb->getConnection();
		$newDb->getConnection();
		
		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		$doneGroups = array();
		$gid;
		unset($stmt);
		$stmt = $oldDb->query("select GroupName, Account, GroupEmail from AbookGroup_$a where Account in (select Account from UserSession)");
		while ($row = $stmt->fetch())
		{
			// Convert to array
			$arr = array();
			foreach ((array)$row as $v)
			{
				$arr[] = $v;	
			}

			if (!in_array("$arr[0]:$arr[1]", $doneGroups))
			{
				$newDb->query("insert into AbookGroupNames (GroupName, Account) values (?,?)", array($arr[0], $arr[1]));
				$doneGroups[] = "$arr[0]:$arr[1]";
			}

			$q = $newDb->query("select id from AbookGroupNames where GroupName = ? and Account = ?", array($arr[0], $arr[1]));
			$gid = $q->fetchColumn();
			$q = null;
			$q = $newDb->query("select id from Abook where UserEmail = ? and Account = ?", array($arr[2], $arr[1]));
			$uid = $q->fetchColumn();

			try 
			{
				displayStats($count, $total);
				$newDb->query("insert into AbookGroup (Account, AbookID, GroupID) values (?,?,?)", array($arr[1], $uid, $gid));
			}
			catch (Exception $e)
			{
				if(!$arguments['force'])
				{
					testExit($e);
				}
			}
			$count++;

                        // HACK! keep $oldDb connection alive
                        pingOldDb();
		}
	}
	fwrite(STDOUT, "\n");
	
	// *** COUNT TOTAL *** //
	$orphaned=0;
	foreach ($range as $a) 
	{

		$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);

		unset($stmt);
		$stmt = $oldDb->query("select count(*) as total from AbookGroup_$a where Account not in (select Account from UserSession)");
		$total_temp = $stmt->fetch();
		$orphaned += $total_temp->total;
	}
	
	if($orphaned)
	{
		fwrite(STDOUT, "Found " . $orphaned . " orphaned entries.\n");
	}
}


// ********** MIGRATE ADMIN GROUPS DATA ************ //
if(!$arguments['skipAddressBookShared'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Abook_shared where Account in (select Account from UserSession)");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating address book shared... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);

	$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);
	unset($stmt);
	$stmt = $oldDb->query("select * from Abook_shared where Account in (select Account from UserSession)");

	resetStats();

	while ($row = $stmt->fetch())
	{
		if (!strpos($row->UserEmail, '@'))
		{
			$row->UserEmail = '';
		}

		$id = $row->id;
		$row->id = null;
		unset($row->UserPgpKey);
		unset($row->UserSMIMEcert);

		// Convert to array
		$arr = array();
		foreach ((array)$row as $v)
		{
			$arr[] = $v;	
		}

		try
		{
			displayStats($count, $total);
			$data = array($row->UserEmail, $row->UserEmail2, $row->UserEmail3, $row->UserEmail4, $row->UserEmail5,
				$row->UserFirstName, $row->UserMiddleName, $row->UserLastName, $row->UserTitle,
				$row->UserGender, $row->UserDOB, $row->UserHomeAddress, $row->UserHomeCity, $row->UserHomeState,
				$row->UserHomeZip, $row->UserHomeCountry, $row->UserHomePhone, $row->UserHomeMobile,
				$row->UserHomeFax, $row->UserURL, $row->UserWorkCompany, $row->UserWorkTitle, $row->UserWorkDept,
				$row->UserWorkOffice, $row->UserWorkAddress, $row->UserWorkCity, $row->UserWorkState, $row->UserWorkZip,
				$row->UserWorkCountry, $row->UserWorkPhone, $row->UserWorkMobile, $row->UserWorkFax, $row->UserType,
				$row->UserInfo, $row->Account, $row->DateAdded, $row->DateModified, $row->UserPhoto, $row->UserFileAs, 0, 1);

			$newDb->query(	"insert into Abook ( UserEmail, UserEmail2, UserEmail3, UserEmail4, UserEmail5, "
				.	"UserFirstName, UserMiddleName, UserLastName, UserTitle, UserGender, UserDOB, "
				.	"UserHomeAddress, UserHomeCity, UserHomeState, UserHomeZip, UserHomeCountry, UserHomePhone, UserHomeMobile, UserHomeFax, "
				.	"UserURL, UserWorkCompany, UserWorkTitle, UserWorkDept, UserWorkOffice, UserWorkAddress, UserWorkCity, UserWorkState, UserWorkZip, UserWorkCountry, "
				.	"UserWorkPhone, UserWorkMobile, UserWorkFax, UserType, UserInfo, Account, DateAdded, DateModified, UserPhoto, UserFileAs, "
			.	"Global, Shared) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", $data); 

			$q = null;
			$q = $newDb->query("select last_insert_id()");
			$newId = $q->fetchColumn();

			// Get AbookPermissions
			unset($stmt2);
			try
			{
				$stmt2 = $oldDb->query("select Account, Permissions, Type, Domain from AbookPermissions where AbookID = ?", $id);
			}
			catch(Exception $e)
			{			
				$stmt2 = $oldDb->query("select Account, Permissions, Type from AbookPermissions where AbookID = ?", $id);
			}
			while ($row2 = $stmt2->fetch())
			{
				$newDb->query("insert into AbookPermissions (AbookID, Account, Permissions, Type, Domain) values (?,?,?,?,?)", array($newId, $row2->Account, $row2->Permissions, $row2->Type, (isset($row2->Domain) ? $row2->Domain : NULL)));
			}
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;

                // HACK! keep $oldDb connection alive
                pingOldDb();
	}

	fwrite(STDOUT, "\n");
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Abook_shared where Account not in (select Account from UserSession)");
	$orphaned = $stmt->fetch();
	$orphaned = $orphaned->total;
	$count = 0;
	if( $orphaned )
	{
		fwrite(STDOUT, "Found " . $orphaned . " orphaned entries.\n");
	}
}

// ********** MIGRATE USER DATA ************ //
if(!$arguments['skipUsers'])
{
	$oldDb->getConnection();
	$newDb->getConnection();
	
	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Users where Account in (select Account from UserSession)");
	$total = $stmt->fetch();
	$total = $total->total;
	$count = 0;

	fwrite(STDOUT, "Migrating users... (" . $total . ")\n");
	$overallTotal += $total;

	unset($stmt);
	$oldDb->setFetchMode(Zend_Db::FETCH_OBJ);
	$stmt = $oldDb->query("select * from Users where Account in (select Account from UserSession)");

	resetStats();
	$orphaned = 0;
	while ($row = $stmt->fetch())
	{
		try
		{
			displayStats($count, $total);
			$quota = round($row->UserQuota / 1024);

			$newDb->query("insert into Users (Account, PasswordQuestion, DateModified, DateCreate, Ugroup, UserStatus, MailDir, Forward, AutoReply, UserQuota) values (?,?,NOW(),?,?,?,?,?,?,?)", array($row->Account, $row->PasswordQuestion, $row->DateCreate, $row->Ugroup, $row->UserStatus, $row->MailDir, $row->Forward, base64_encode($row->AutoReply), $quota));
				$newDb->query("insert into Abook (UserEmail, UserFirstName, UserLastName, UserGender, UserDOB, UserHomeAddress, UserHomeCity, UserHomeState, UserHomeZip, UserHomeCountry, UserHomePhone, UserHomeMobile, UserHomeFax, UserWorkPhone, UserWorkFax, Account, DateAdded, Global) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?)", array($row->Account, $row->FirstName, $row->LastName, $row->Gender, $row->BirthDay . '/' . $row->BirthMonth . '/' . $row->BirthYear, $row->Address, $row->City, $row->State, $row->PostCode, $row->Country, $row->TelHome, $row->TelMobile, $row->FaxHome, $row->TelWork, $row->FaxWork, $row->Account, 1));
		}
		catch (Exception $e)
		{
			if(!$arguments['force'])
			{
				testExit($e);
			}
		}
		$count++;
                // HACK! keep $oldDb connection alive
                pingOldDb();
	}

	$newDb->query("update Users set Ugroup = 'default' where (select count(*) from Groups where Ugroup = GroupName) = 0");
	$newDb->query("update Users set Ugroup = 'default' where Ugroup = 'Default'");
	fwrite(STDOUT, "\n");

	unset($stmt);
	$stmt = $oldDb->query("select count(*) as total from Users where Account not in (select Account from UserSession)");
	$orphaned = $stmt->fetch();
	$orphaned = $orphaned->total;

	if($orphaned)
	{
		fwrite(STDOUT, "Found " . $orphaned . " orphaned entries.\n");
	}
}

fwrite(STDOUT, "Migration completed.\n");
displayElaped("Total migration time for " . $overallTotal . " items is ", $overallTime, 'human');

function pingOldDb()
{
    global $oldDb;

    static $c;

    if (is_null($c)) {
       $c = 1;
    }

    if ($c % 1000 == 0) {
        $oldDb->query("select 1");
    }

    $c++;
}
