Integration of NetSuite with MySQL

Recently we integrated Netsuite with a custom designed portal. Our client wanted to use NetSuite data in real-time to prepare custom quotes on the portal. A key requirement was to sync data between NetSuite and the custom MySQL Database without errors and with full data integrity. The purpose of this module was to eliminate manual and redundant data entry across systems. We implemented the solution in PHP.

Solution:

Database:

Developer analyzed NetSuite Record structure from SOAP response and designed schema in MYSQL to map the required data. A database relationship was created after traversing XML obtained against each record.

Communication Link between NetSuite and Application Server:

NetSuite Suitescript provides a variety of internal and external communication packages. User Event scripting engine was used to notify the application server with all the changes or updates applied to record in NetSuite. Each script was deployed in associated record and bound by event type (Edit, Delete, Create, Append, and View).

response = nlapiRequestURL (url, data, header, “POST”);

NetSuite NLAPI was used to ping the application server if record updated in NetSuite, JSON encoded securing data was captured on restful service consist of a record type and NetSuite id. All pings were logged into a queue for further processing.

Synchronization Process:

All Pings were handed over to the Ping Manager which was running every minute as a Cron Job and was looking for an unprocessed queue. Each ping contain NetSuite primary key of the record called an ‘Internal id’. Object oriented Cron Management process was also implemented to control the processing speed of the queue and prioritizing records within queues.

$objCronMaster->Sync($server);

Communication Link between Application Server and NetSuite:

Connection with NetSuite remained open using SOAP if the queue was unprocessed.

$wsdl=”https://webservices.netsuite.com/wsdl/v2013_1_0/netsuite.wsdl”;
$connection=new SoapClient ($host,$wsdl);

Finalizing Synchronization:

NetSuite search criteria was used to extract SOAP response of the record using NetSuite equivalent keys stored in the queue, after parsing the response, the record was finally updated in the database. Data pull operations were dedicated for this purpose.

$searchItems = new nsComplexObject(“TransactionSearchBasic”);
$searchItems->setFields(array(“tranId” => array(“operator” => “is”,”searchValue”=> $itemid)));
$response=$connection->search($searchItems);
$database->synchronize($response);