Tweet
Who wouldn't want to import their orders if migrating from another cart? And what about wishlists, RMA's, transactions, existing shopping cart contents, etc? Anybody else think this should be a built-in function or a standard service?
For myself, I don't want to pay thousands of dollars and weeks of development to get these imported.
Fortunately, 3dcart has the SOAP API (and REST on its way). Unfortunately there isn't much documentation or practical examples. "msidler" got me going with his post: (http://forums.3dcart.com/general-configuration-questions/6259-advanced-api-example-php.html).
But I don't want something to add just one record - I want all my orders, returns etc. imported. I thought assigning a csv to an array and passing it to the SOAP would work ok, but I though of another solution. I imported the data into my localhost MySQL server (not hard to do at all) on my WAMP installation. From there the code wasn't too difficult.
Here it is in PHP:
_______________________________________
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Example 3D Advanced API SOAP Import orders</title>
</head>
<body>
<?php
//assigns 3dcart soap connection script to $client to call upon later
$client = new soapclient('http://api.3dcart.com/cart_advanced.asmx?WSDL', array('trace' => 1, 'soap_version' => SOAP_1_1));
//Defines list of fields for later use in sql statement passed to the 3dcart API
$fieldnames = "osc_chcid, osc_chccustid, ocustomerid, odate, orderamount, ofirstname, olastname, oemail, oaddress, oaddress2, ocity, ozip, ostate, ocountry, ophone, ocompany, ocomment, ointernalcomment, otax, oshippeddate, oshipmethod, oshipcost, oshipfirstname, oshiplastname, oshipcompany, oshipaddress, oshipaddress2, oshipcity, oshipzip, oshipstate, oshipcountry, opaymethod, opaymethodinfo, otime, odiscount, ostatus, coupon, trackingcode, invoicenum_prefix, invoicenum, order_status, salesperson";
//declare variable for localhost mysql database
$user_name = "root";
$password = "*******";
$database = "mysql";
$server = "localhost";
//Assign connection to localhost database to variable $db_handle
$db_handle = mysql_connect($server, $user_name, $password);
//Assign boolean connection status to variabl $db_found
$db_found = mysql_select_db($database, $db_handle);
//Start IF statement - basically "If connection is good, then do the following statements, if not (else) print "Database Not Found" and close connection".
if ($db_found) {
//Assigns SELECT sql statement to variable
$SQL = "SELECT * FROM orders";
//sends SQL statement to the mysql database and assigns the results to $result variable
$result = mysql_query($SQL);
//Starts loop to go through each row of the returned array
while ( $db_field = mysql_fetch_assoc($result) ) {
//Assigns column names to variable to use to then assign the whole list to $localhostrow variable in the next step
$osc_chcid = $db_field['osc_chcid'];
$osc_chccustid = $db_field['osc_chccustid'];
$ocustomerid = $db_field['ocustomerid'];
$odate = $db_field['odate'];
$orderamount = $db_field['orderamount'];
$ofirstname = $db_field['ofirstname'];
$olastname = $db_field['olastname'];
$oemail = $db_field['oemail'];
$oaddress = $db_field['oaddress'];
$oaddress2 = $db_field['oaddress2'];
$ocity = $db_field['ocity'];
$ozip = $db_field['ozip'];
$ostate = $db_field['ostate'];
$ocountry = $db_field['ocountry'];
$ophone = $db_field['ophone'];
$ocompany = $db_field['ocompany'];
$ocomment = $db_field['ocomment'];
$ointernalcomment = $db_field['ointernalcomment'];
$otax = $db_field['otax'];
$oshippeddate = $db_field['oshippeddate'];
$oshipmethod = $db_field['oshipmethod'];
$oshipcost = $db_field['oshipcost'];
$oshipfirstname = $db_field['oshipfirstname'];
$oshiplastname = $db_field['oshiplastname'];
$oshipcompany = $db_field['oshipcompany'];
$oshipaddress = $db_field['oshipaddress'];
$oshipaddress2 = $db_field['oshipaddress2'];
$oshipcity = $db_field['oshipcity'];
$oshipzip = $db_field['oshipzip'];
$oshipstate = $db_field['oshipstate'];
$oshipcountry = $db_field['oshipcountry'];
$opaymethod = $db_field['opaymethod'];
$opaymethodinfo = $db_field['opaymethodinfo'];
$otime = $db_field['otime'];
$odiscount = $db_field['odiscount'];
$ostatus = $db_field['ostatus'];
$coupon = $db_field['coupon'];
$trackingcode = $db_field['trackingcode'];
$invoicenum_prefix = $db_field['invoicenum_prefix'];
$invoicenum = $db_field['invoicenum'];
$order_status = $db_field['order_status'];
$salesperson = $db_field['salesperson'];
//Just some field separators to use next:
$b1 = "'";
$b2 = "','";
//Assigns the csv row to $localhost to be further put into $psql which will be the sql statement passed to 3dcart API
$localhostrow = "$b1$osc_chcid$b2$osc_chccustid$b2$ocustomerid$b2$ odate$b2$orderamount$b2$ofirstname$b2$olastname$b2 $oemail$b2$oaddress$b2$oaddress2$b2$ocity$b2$ozip$ b2$ostate$b2$ocountry$b2$ophone$b2$ocompany$b2$oco mment$b2$ointernalcomment$b2$otax$b2$oshippeddate$ b2$oshipmethod$b2$oshipcost$b2$oshipfirstname$b2$o shiplastname$b2$oshipcompany$b2$oshipaddress$b2$os hipaddress2$b2$oshipcity$b2$oshipzip$b2$oshipstate $b2$oshipcountry$b2$opaymethod$b2$opaymethodinfo$b 2$otime$b2$odiscount$b2$ostatus$b2$coupon$b2$track ingcode$b2$invoicenum_prefix$b2$invoicenum$b2$orde r_status$b2$salesperson$b1";
//convert $localhost to utf8 encoding. I was getting errors importing if there was strange characters in my database. My database was latin1_swedish so here I convert it to utf8
//**** NOTE: Comments with single quotes, otherwise known as apostrophes - ' - and last names like O'brien, the SOAP request would error out. Solution: I replaced all single quotes in my database with TWO single quotes.
$localhostrow = utf8_encode($localhostrow);
/* I used the following lines for development so I could see what was going to be passed to the SOAP API.
print $localhostrow;
print "<br><br>";
*/
//prepares SQL statement to pass to API
$psql = "INSERT IGNORE INTO orders (" . $fieldnames . ") VALUES (" . $localhostrow . ");";
/* I used the following lines for development so I could see what was going to be passed to the SOAP API.
print $psql;
print "<br><br>";
*/
//assigns 3dcart API credentials AND sets variable $psql
$param = array('storeUrl'=>"mystore.3dcartstores.com", 'userKey'=>"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", 'sqlStatement'=>$psql);
//sends the formatted rows to 3dcart for insert into database
$soapresult = $client->runQuery($param);
//new line, insert order # and the print the Soap Result
print "<BR>";
print "Importing order #" . $osc_chcid . ", ";
print_r($soapresult);
//Start Loop Again
}
//Close mysql connection - this is after the WHILE loop but before the ELSE --- or is it?
mysql_close($db_handle);
//end if statement
}
//Else - supposed to output "Database not found" if not valid or server down
else {
print "Database NOT Found ";
mysql_close($db_handle);
}
?>
</body>
_______________________________________
I am not an experienced programmer and I accept all criticism. I realize that PHP mysql_connect has been deprecated, but it was simple enough and was the first practical example I found. If anyone can tell me if mysqli would be better and how to convert my code, I would appreciate it.
Using this code I imported 75000+ orders. It took about two hours.
For myself, I don't want to pay thousands of dollars and weeks of development to get these imported.
Fortunately, 3dcart has the SOAP API (and REST on its way). Unfortunately there isn't much documentation or practical examples. "msidler" got me going with his post: (http://forums.3dcart.com/general-configuration-questions/6259-advanced-api-example-php.html).
But I don't want something to add just one record - I want all my orders, returns etc. imported. I thought assigning a csv to an array and passing it to the SOAP would work ok, but I though of another solution. I imported the data into my localhost MySQL server (not hard to do at all) on my WAMP installation. From there the code wasn't too difficult.
Here it is in PHP:
_______________________________________
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Example 3D Advanced API SOAP Import orders</title>
</head>
<body>
<?php
//assigns 3dcart soap connection script to $client to call upon later
$client = new soapclient('http://api.3dcart.com/cart_advanced.asmx?WSDL', array('trace' => 1, 'soap_version' => SOAP_1_1));
//Defines list of fields for later use in sql statement passed to the 3dcart API
$fieldnames = "osc_chcid, osc_chccustid, ocustomerid, odate, orderamount, ofirstname, olastname, oemail, oaddress, oaddress2, ocity, ozip, ostate, ocountry, ophone, ocompany, ocomment, ointernalcomment, otax, oshippeddate, oshipmethod, oshipcost, oshipfirstname, oshiplastname, oshipcompany, oshipaddress, oshipaddress2, oshipcity, oshipzip, oshipstate, oshipcountry, opaymethod, opaymethodinfo, otime, odiscount, ostatus, coupon, trackingcode, invoicenum_prefix, invoicenum, order_status, salesperson";
//declare variable for localhost mysql database
$user_name = "root";
$password = "*******";
$database = "mysql";
$server = "localhost";
//Assign connection to localhost database to variable $db_handle
$db_handle = mysql_connect($server, $user_name, $password);
//Assign boolean connection status to variabl $db_found
$db_found = mysql_select_db($database, $db_handle);
//Start IF statement - basically "If connection is good, then do the following statements, if not (else) print "Database Not Found" and close connection".
if ($db_found) {
//Assigns SELECT sql statement to variable
$SQL = "SELECT * FROM orders";
//sends SQL statement to the mysql database and assigns the results to $result variable
$result = mysql_query($SQL);
//Starts loop to go through each row of the returned array
while ( $db_field = mysql_fetch_assoc($result) ) {
//Assigns column names to variable to use to then assign the whole list to $localhostrow variable in the next step
$osc_chcid = $db_field['osc_chcid'];
$osc_chccustid = $db_field['osc_chccustid'];
$ocustomerid = $db_field['ocustomerid'];
$odate = $db_field['odate'];
$orderamount = $db_field['orderamount'];
$ofirstname = $db_field['ofirstname'];
$olastname = $db_field['olastname'];
$oemail = $db_field['oemail'];
$oaddress = $db_field['oaddress'];
$oaddress2 = $db_field['oaddress2'];
$ocity = $db_field['ocity'];
$ozip = $db_field['ozip'];
$ostate = $db_field['ostate'];
$ocountry = $db_field['ocountry'];
$ophone = $db_field['ophone'];
$ocompany = $db_field['ocompany'];
$ocomment = $db_field['ocomment'];
$ointernalcomment = $db_field['ointernalcomment'];
$otax = $db_field['otax'];
$oshippeddate = $db_field['oshippeddate'];
$oshipmethod = $db_field['oshipmethod'];
$oshipcost = $db_field['oshipcost'];
$oshipfirstname = $db_field['oshipfirstname'];
$oshiplastname = $db_field['oshiplastname'];
$oshipcompany = $db_field['oshipcompany'];
$oshipaddress = $db_field['oshipaddress'];
$oshipaddress2 = $db_field['oshipaddress2'];
$oshipcity = $db_field['oshipcity'];
$oshipzip = $db_field['oshipzip'];
$oshipstate = $db_field['oshipstate'];
$oshipcountry = $db_field['oshipcountry'];
$opaymethod = $db_field['opaymethod'];
$opaymethodinfo = $db_field['opaymethodinfo'];
$otime = $db_field['otime'];
$odiscount = $db_field['odiscount'];
$ostatus = $db_field['ostatus'];
$coupon = $db_field['coupon'];
$trackingcode = $db_field['trackingcode'];
$invoicenum_prefix = $db_field['invoicenum_prefix'];
$invoicenum = $db_field['invoicenum'];
$order_status = $db_field['order_status'];
$salesperson = $db_field['salesperson'];
//Just some field separators to use next:
$b1 = "'";
$b2 = "','";
//Assigns the csv row to $localhost to be further put into $psql which will be the sql statement passed to 3dcart API
$localhostrow = "$b1$osc_chcid$b2$osc_chccustid$b2$ocustomerid$b2$ odate$b2$orderamount$b2$ofirstname$b2$olastname$b2 $oemail$b2$oaddress$b2$oaddress2$b2$ocity$b2$ozip$ b2$ostate$b2$ocountry$b2$ophone$b2$ocompany$b2$oco mment$b2$ointernalcomment$b2$otax$b2$oshippeddate$ b2$oshipmethod$b2$oshipcost$b2$oshipfirstname$b2$o shiplastname$b2$oshipcompany$b2$oshipaddress$b2$os hipaddress2$b2$oshipcity$b2$oshipzip$b2$oshipstate $b2$oshipcountry$b2$opaymethod$b2$opaymethodinfo$b 2$otime$b2$odiscount$b2$ostatus$b2$coupon$b2$track ingcode$b2$invoicenum_prefix$b2$invoicenum$b2$orde r_status$b2$salesperson$b1";
//convert $localhost to utf8 encoding. I was getting errors importing if there was strange characters in my database. My database was latin1_swedish so here I convert it to utf8
//**** NOTE: Comments with single quotes, otherwise known as apostrophes - ' - and last names like O'brien, the SOAP request would error out. Solution: I replaced all single quotes in my database with TWO single quotes.
$localhostrow = utf8_encode($localhostrow);
/* I used the following lines for development so I could see what was going to be passed to the SOAP API.
print $localhostrow;
print "<br><br>";
*/
//prepares SQL statement to pass to API
$psql = "INSERT IGNORE INTO orders (" . $fieldnames . ") VALUES (" . $localhostrow . ");";
/* I used the following lines for development so I could see what was going to be passed to the SOAP API.
print $psql;
print "<br><br>";
*/
//assigns 3dcart API credentials AND sets variable $psql
$param = array('storeUrl'=>"mystore.3dcartstores.com", 'userKey'=>"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", 'sqlStatement'=>$psql);
//sends the formatted rows to 3dcart for insert into database
$soapresult = $client->runQuery($param);
//new line, insert order # and the print the Soap Result
print "<BR>";
print "Importing order #" . $osc_chcid . ", ";
print_r($soapresult);
//Start Loop Again
}
//Close mysql connection - this is after the WHILE loop but before the ELSE --- or is it?
mysql_close($db_handle);
//end if statement
}
//Else - supposed to output "Database not found" if not valid or server down
else {
print "Database NOT Found ";
mysql_close($db_handle);
}
?>
</body>
_______________________________________
I am not an experienced programmer and I accept all criticism. I realize that PHP mysql_connect has been deprecated, but it was simple enough and was the first practical example I found. If anyone can tell me if mysqli would be better and how to convert my code, I would appreciate it.
Using this code I imported 75000+ orders. It took about two hours.
Comment