Location: PHPKode > scripts > HosterGo > zingiri-hoster/tools/clientexec_export.php
<?php
/*
 * Copy this file in the root directory of your Clientexec installation and access it via a browser.
 * It will create a file CE.txt in the root directory, upload this file via your Hostergo admin menu.
 */
require("./config.php");
echo 'Migrate data from Clientexec to Hostergo<br />';

// connect to db

$db = @mysql_connect($hostname,$dbuser,$dbpass) or die("<h1>Could not connect to the database. Please check your settings</h1>");
@mysql_select_db($database,$db) or die("<h1>Could not connect to the database. Please check your settings</h1>");
// open file
$myFile = "CE.txt";
$fh = fopen($myFile, 'w') or die("Can't open file");

//users
$query = "SELECT * FROM `".$dbtablesprefix."users` where `groupid` not in (-1) and `status` >= 0";
$sql = mysql_query($query) or die(mysql_error());
while (($user = mysql_fetch_array($sql)) && $i < 99999) {
	$r=array();
	$i++;
	$id=$user['id'];

	//user custom fields
	$query = "SELECT customuserfields.name,user_customuserfields.value FROM `".$dbtablesprefix."user_customuserfields`,  `".$dbtablesprefix."customuserfields` where customuserfields.id=user_customuserfields.customid and `userid`=".$id;
	$sql2 = mysql_query($query) or die(mysql_error());
	while ($user_cf = mysql_fetch_array($sql2)) {
		$field=$user_cf[0];
		$value=$user_cf[1];
		$user[$field]=$value;
	}


	$customer=array();
	$customer['loginname']=$user['email'];
	$customer['password']=$user['password'];
	$customer['lastname']=$user['lastname'];
	$customer['middlename']='';
	$customer['initials']=$user['firstname'];
	$customer['company']=$user['organisation'];
	$customer['email']=$user['email'];
	$customer['address']=$user['Address'];
	$customer['zip']=$user['Zipcode'];
	$customer['state']=$user['State'];
	$customer['city']=$user['City'];
	$customer['country']=$user['Country'];
	$customer['phone']=$user['Phone'];
	$customer['group']=group($user['groupid']);
	$customer['joindate']=$user['dateActivated'];
	$customer['newsletter']=$user['Receive Email Announcements'];
	//$user['status']
	//$user['currency']
	//$user['lastseen']
	//$user['clienttype']
	//$user['paymenttype']
	$r['type']='CE';
	$r['customer']=$customer;

	//alternate emails
	//----------------
	$query = "SELECT * FROM `".$dbtablesprefix."altuseremail` where `userid`=".$id;
	$sql2 = mysql_query($query) or die(mysql_error());
	while ($altemail = mysql_fetch_array($sql2)) {
	}

	//client notes
	//------------
	$query = "SELECT * FROM `".$dbtablesprefix."clients_notes` where `target_id`=".$id;
	$sql2 = mysql_query($query) or die(mysql_error());
	while ($user_domain = mysql_fetch_array($sql2)) {
	}


	//registered domains
	//------------------
	$domains=array();
	$query = "SELECT * FROM `".$dbtablesprefix."users_domains` where `userid`=".$id;
	$sql2 = mysql_query($query) or die(mysql_error());
	while ($user_domain = mysql_fetch_array($sql2)) {
		$domain=array();
		//		$domain['date_created']=
		//		$domain['date_updated']=
		$domain['name']=$user_domain['domain'];
		$domain['period']=$user_domain['period'];
		$domain['registration']=1;
		$domain['duedate']=$user_domain['nextbilldate'];
		switch ($user_domain['status']) {
			case 1:
				$domain['status']=20;
				break;
			default:
				die("Unrecognised domain status: ".$$user_domain['status']);
				break;
		}
		//		$domain['statusdate']=
		$domains[]=$domain;
	}
	$r['domains']=$domains;

	//packages
	//--------
	$packages=array();
	$query = "SELECT * FROM `".$dbtablesprefix."domains` where `customerid`=".$id;
	$sql2 = mysql_query($query) or die(mysql_error());
	while ($domain = mysql_fetch_array($sql2)) {
		$package=array();
		$package['date_created']=$domain['dateActivated'];
		$package['tempid']=$domain['id'];
		//		$package['date_updated']=
		//		$package['orderid']=
		$package['productname']=plan($domain['Plan']);
		$package['hostname']=server($domain['serverid']);
		switch ($domain['status']) {
			case 0:
				$package['status']=10;
				break;
			case 1:
				$package['status']=20;
				break;
			case 2:
				$package['status']=30;
				break;
			case 3:
				$package['status']=90;
				break;
			default:
				die("Unrecognised package status: ".$domain['status']);
				break;
		}
		$package['domain']=$domain['DomainName'];
		$package['username']=$domain['UserName'];
		$package['password']=$domain['password'];
		$package['frequency']=$domain['paymentterm'];
		if ($domain['use_custom_price']) $package['price']=$domain['customer_price'];
		$package['duedate']=$domain['nextbilldate'];
		//		$package['statusdate']=
		$packages[]=$package;
	}
	$r['packages']=$packages;

	//invoices
	//--------
	$invoices=array();
	$query = "SELECT * FROM `".$dbtablesprefix."invoice` where `customerid`=".$id;
	$sql2 = mysql_query($query) or die(mysql_error());
	while ($inv = mysql_fetch_array($sql2)) {
		$invoice=array();
		$invoicelines=array();
		$query = "SELECT * FROM `".$dbtablesprefix."invoiceentry` where `invoiceid`=".$inv['id'];
		$sql3 = mysql_query($query) or die(mysql_error());
		while ($entry = mysql_fetch_array($sql3)) {
			$invoiceline=array();
			$invoiceline['date_created']=$entry['date'];
			$invoiceline['appliestoid']=$entry['appliestoid'];
			$invoiceline['description']=$entry['description'].' ('.$entry['detail'].')';
			switch ($entry['billingtypeid']) {
				case -1:
					$invoiceline['type']=10; //hosting
					break;
				case -2:
					$invoiceline['type']=20; //registration
					break;
				default:
					$invoiceline['type']=30; //other
					break;

			}
			$invoiceline['price']=$entry['price'];
			$invoicelines[]=$invoiceline;
		}
		$invoice['lines']=$invoicelines;
		$invoice['date_created']=$inv['sentdate'];
		$invoice['duedate']=$inv['billdate'];
		if ($inv['status']==0 && $inv['sent']==0) $invoice['status']=10; //issued
		elseif ($inv['status']==0 && $inv['sent']==1) $invoice['status']=20; //sent
		elseif ($inv['status']==1) $invoice['status']=30; //paid
		elseif ($inv['status']==2) $invoice['status']=80; //void
		elseif ($inv['status']==3) $invoice['status']=40; //refunded
		else error("Couldn't determine status for invoice ".$inv['id']." (".$inv['status'].'/'.$inv['sent'].')');

		$invoice['topay']=$inv['amount'];
		if ($inv['datepaid']) $invoice['paid']=$inv['amount'];
		$invoice['currency']=strtoupper($user['currency']);
		//$invoice['reminder']=
		//$invoice['webid']=
		//$invoice['pdf']=
		$invoices[]=$invoice;
	}
	$r['invoices']=$invoices;

	//	print_r($r);
	//	echo '<br />----------------------';
	//	echo '<br />';
	$stringData=json_encode($r)."\r\n";
	//	echo '<br />----------------------';
	//	echo '<br />';
	fwrite($fh, $stringData);

}

fclose($fh);
if ($error) {
	echo "Errors occured, please fix them and try again";
	$fh = fopen($myFile, 'w') or die("Can't open file");
	fclose($fh);
} else {
	echo 'Exported '.$i.' clients';
}



//helper functions

function group($groupid) {
	$query = "SELECT * FROM `".$dbtablesprefix."groups` where `id`=".$groupid;
	$sql = mysql_query($query) or die(mysql_error());
	$group = mysql_fetch_array($sql) or die("Group ".$groupid." not found");
	if ($group['issuperadmin']) return 'ADMIN';
	if ($group['admin']) return 'USER';
	return 'CUSTOMER';
}
function plan($planid) {
	global $domain;
	$query = "SELECT planname FROM `".$dbtablesprefix."package` where `id`=".$planid;
	$sql = mysql_query($query) or die(mysql_error());
	$plan = mysql_fetch_array($sql) or die("Plan ".$planid." not found for ".$domain['DomainName']);
	return $plan['planname'];
}

function server($serverid) {
	global $domain;
	$query = "SELECT hostname FROM `".$dbtablesprefix."server` where `id`=".$serverid;
	$sql = mysql_query($query) or die(mysql_error());
	$server = mysql_fetch_array($sql) or die("Server ".$serverid." not found for ".$domain['DomainName']);
	return $server['hostname'];
}

function error($msg) {
	global $error;
	$error=true;
	echo $msg."<br />";
}

?>
Return current item: HosterGo