Announcement

Collapse
No announcement yet.

Importing Orders (SOAP, API Advanced)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Importing Orders (SOAP, API Advanced)

    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.

  • #2
    Hi RTS,

    I think you have the basic process down, so I only have a few suggestions that I can add.

    The REST API is actually in Beta release right now and does have a service to POST orders. Let me know if you would like me to add access for your store.

    However, if you do want to continue using the SOAP API, it's possible to send multiple queries in each request by separating each query with |;;| This is limited by a process timeout, so I can't give you an exact number that can be included in each request, but limiting this to 100 should get you going. You can adjust this number up/down as needed.

    Also, you can import direct from a CSV file using PHP's fgetcsv() function and skip the import to your local database with a few changes to your script.

    I wouldn't worry too much about using mysql_connect on a local server, but if you plan on exposing this app externally, I would recommend using PDO PHP: PDO::__construct - Manual

    Comment


    • #3
      I didn't have "Default Thread Subscription Mode" set to "Instant e-mail notification" in my profile options so I wasn't aware that someone had replied to my thread.

      Thank you, Dan, for your comments.

      Comment


      • #4
        Thank you, Dan. I've been off the grid for awhile and I don't seem to be getting notices when somebody replies to my posts.

        If I use the REST API, it seems the code would have to be more complex. The sample PHP request is for importing one order. I guess I'd have to loop it to step through a file/database. And I'd have call multiple tables for one order. For bulk importing I'll stick with SOAP - unless someone has a REST example for something like this.

        I tried to get the fgetcsv() function to work but I was having trouble. I thought, too, that pulling from a local database I would have less trouble with text conversion, mishandled escape characters, etc. It also allows me to easily selectively import modifiying the SELECT sql statement. Here is an example:

        There were times that the would script time out. In this case I would change the select statement to continue where it left off:

        $SQL = "SELECT FROM orders WHERE orderid BETWEEN 34230 AND 85349";
        __________________________________________________ ______________
        If I messed up my import and wanted to clear the entire table, this is the entire code needed in a php file, which I can run on my own server or upload to any other server and run it (although it won't run on 3dcart's server of course):

        <!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 PhP Interface to 3D Advanced API SOAP service</title>
        </head>
        <body>
        <?php

        $psql = "DELETE * FROM orders";

        $client = new soapclient('http://api.3dcart.com/cart_advanced.asmx?WSDL', array('trace' => 1,'soap_version' => SOAP_1_2));
        $param = array('storeUrl'=>"robbiestech.3dcartstores.com", 'userKey'=>"XXXXXXXXXXXXXXXXXXXXXX", 'sqlStatement'=>$psql);
        $result = $client->runQuery($param);
        print_r($result)
        ?>
        </body>

        I'll get a response that x number of records were deleted.

        Thank you for your feedback.

        Comment

        Working...
        X