Import Roundcube Contact from DirectAdmin to VestaCP

Export users & contacts from DirectAdmin Roundcube Database
1. ค้นหา user, pass ของ mysql ของ DirectAdmin
cat /usr/local/directadmin/conf/mysql.conf
2. เปิดเว็บ www.domain.com/phpmyadmin
3. คลิก  da_roundcube
4. คลิก table users คลิกเมนู More > Operation
5. เลือก Copy table to users_da
6. คลิก table contacts คลิกเมนู More > Operation
7. เลือก Copy table to contacts_da
8. คลิก database แล้วเลือก users_da กับ contacts_da แล้วเลือก export

Import users & contacts to VestaCP Roundcube Database
1. ค้นหา user, pass ของ mysql ของ VestaCP
cat /usr/local/vesta/conf/mysql.conf
2. เปิดเว็บ www.domain.com/phpmyadmin
3. คลิก roundcube
4. คลิก Import แล้วเลือกไฟล์ da_roundcube.sql ที่เรา export มาจาก DirectAdmin
อย่าลืม ใน da_roundcube.sql จะต้องมีแค่ 2 table คือ users_da กับ contacts_da
5. nano /root/rc_import.php
อย่าลืมแก้ user, pass
 

<?php
// Import ROUNDCUBE Contacts from DirectAdmin to VestaCP
// usage : php rc_import.php domain.com

$dbuser = "root";
$dbpass = "demo";
$db = "roundcube";

$connect = mysqli_connect("localhost", $dbuser , $dbpass, $db);
if (!$connect) { exit; }

mysqli_set_charset($connect, "utf8");

$table_user_vesta="users";
$table_user_da="users_da";
$table_contact_vesta="contacts";
$table_contact_da="contacts_da";

$d='';
if (isset($_GET['d'])) { $d=$_GET['d']; }
if (isset($argc)) { $d=@$argv[1]; }
if ($d==''){ echo 'please use "php rc_import.php domain.com"'."\n"; exit; }

// reset auto increment
$sql = "alter table $table_user_vesta AUTO_INCREMENT =1 ";
$result = mysqli_query($connect, $sql) or die(mysqli_error($connect));
$sql = "alter table $table_contact_vesta AUTO_INCREMENT =1 ";
$result = mysqli_query($connect, $sql) or die(mysqli_error($connect));

// count "user da" to be add to "user vesta"
$sql = "SELECT username FROM $table_user_da
	WHERE username NOT IN
		(SELECT username FROM $table_user_vesta WHERE locate('@$d', username)>0) ";
$result = mysqli_query($connect, $sql) or die (mysqli_error($connect));
$total=mysqli_num_rows($result);

// add user da to user vesta
if ($total>0) {
	$sql = "INSERT INTO $table_user_vesta 
			(username, mail_host, created, last_login, failed_login, failed_login_counter, language, preferences)
		SELECT
			username, mail_host, created, last_login, failed_login, failed_login_counter, language, preferences
		FROM $table_user_da
		WHERE locate('@$d', username)>0 AND username NOT IN
			(SELECT username FROM $table_user_vesta WHERE locate('@$d', username)>0 ) ";
	$result = mysqli_query($connect, $sql) or die (mysqli_error($connect));
	$total = mysqli_affected_rows($connect);
}
echo "\nCopy $total roundcube users from DirectAdmin to Vesta";

$sql = "select * from $table_user_vesta where locate('@$d', username)>0 ";
$result = mysqli_query($connect, $sql) or die (mysqli_error($connect));
while ($row = mysqli_fetch_array($result)) { $user_vesta[$row['username']]=$row['user_id']; }

$sql = "select * from $table_user_da where locate('@$d', username)>0 ";
$result = mysqli_query($connect, $sql) or die (mysqli_error($connect));
while ($row = mysqli_fetch_array($result)) { 

	$email=$row['username'];
	$id_da=$row['user_id'];
	$id_vesta=$user_vesta[$email];

	// count "contct da" to be add to "contact vesta"
	$sql2 = "SELECT email FROM $table_contact_da
		WHERE user_id = '$id_da' AND email NOT IN
			(SELECT email FROM $table_contact_vesta WHERE user_id = '$id_vesta') ";
	$result2 = mysqli_query($connect, $sql2) or die (mysqli_error($connect));
	$total=mysqli_num_rows($result2);

	echo "\n$email - $id_vesta - $id_da - $total - ";

	if ($total>0) {
		$sql2 = "INSERT INTO $table_contact_vesta 
				(changed, del, name, email, firstname, surname, vcard, words, user_id)
			SELECT
				changed, del, name, email, firstname, surname, vcard, words, '$id_vesta'
			FROM $table_contact_da
			WHERE user_id = '$id_da' AND email NOT IN
				(SELECT email FROM $table_contact_vesta WHERE user_id = '$id_vesta') ";

		$result2 = mysqli_query($connect, $sql2) or die (mysqli_error($connect));
		$total = mysqli_affected_rows($connect);

		echo " ... import $total contacts";
	}else{
		$sql2 = "SELECT email FROM $table_contact_vesta WHERE user_id = '$id_vesta' ";
		$result2 = mysqli_query($connect, $sql2) or die (mysqli_error($connect));
		$total=mysqli_num_rows($result2);

		echo " ... have $total contacts";
	}
}
echo "\n\n";
mysqli_close($connect);
?>

6. ใช้คำสั่งนี้เพื่อ import แค่ 1 domain
php rc_contact.php domain.com
หรือใช้คำสั่งนี้เพื่อ import ทุก domain
php rc_contact.php

5 ส.ค. 62