Query Acumatica Data using the OData SDK for PHP

The CData API Server, paired with the Acumatica connector (or any of hundreds of supported data sources), exposes Acumatica data as web services. This article walks you through setting up the CData API Server and demonstrates the process to work with Acumatica data as PHP objects.

Setting Up the API Server

If you haven't already, download and install the CData API Server. After installation, run the application, download the Acumatica connector from within the API Server, and configure your Acumatica data connection. Then, configure API Server to generate OData feeds for any tables you want to access in your single-page application (SPA).

Enable CORS

AngularJS requires servers to have CORS (Cross-Origin Resource Sharing) enabled. To enable CORS, go to the Server tab under the Settings section in the API Server and adjust the following settings:

• Click the checkbox to Enable cross-origin resource sharing (CORS).
• Either click the checkbox to Allow all domains without '*' or specify the domain(s) that are allowed to connect in Access-Control-Allow-Origin.
• Set Access-Control-Allow-Methods to "GET, PUT, POST, OPTIONS".
• Set Access-Control-Allow-Headers to "Content-Type, Authorization".
• Click Save to save the changes made.

Connect to Acumatica

To work with Acumatica data using AngularJS, we start by creating and configuring a Acumatica connection. Follow the steps below to configure the API Server to connect to Acumatica data:

1. First, navigate to the Connections page.
2. Click Add Connection and then search for and select the Acumatica connection. 👁 Selecting a data source (SQLite is shown)
   
3. Enter the necessary authentication properties to connect to Acumatica. 👁 Connecting to a datasource (SQLite is shown)
   
4. After configuring the connection, click Save & Test to confirm a successful connection.

Configure API Server Users

Next, create a user to access your Acumatica data through the API Server. You can add and configure users on the Users page. Follow the steps below to configure and create a user:

1. On the Users page, click Add User to open the Add User dialog.
2. Next, set the Role, Username, and Privileges properties and then click Add User. 👁 Configure a new user.
   
3. An Authtoken is then generated for the user. You can find the Authtoken and other information for each user on the Users page: 👁 API Server user settings.
   

Creating API Endpoints for Acumatica

Having created a user, you are ready to create API endpoints for the Acumatica tables:

1. First, navigate to the API page and then click Add Table . 👁 Add tables
   
2. Select the connection you wish to access and click Next. 👁 Select the connection (SQLite is shown)
   
3. With the connection selected, create endpoints by selecting each table and then clicking Confirm. 👁 Adding tables from the connection (SQLite is shown)
   

Gather the OData URL

Having configured a connection to Acumatica data, created a user, and added resources to the API Server, you now have an easily accessible REST API based on the OData protocol for those resources. From the API page in API Server, you can view and copy the API Endpoints for the API:

👁 API Endpoints

Just like with standard OData feeds, you can limit the fields returned by adding a $select parameter to the query. You can also include other standard OData URL parameters, such as $filter, $orderby, $skip, and $top. Refer to the help documentation for more details on supported OData queries.

Save the Authtoken

Select the user you created, then copy and save the authtoken for future use. You can also toggle on 'Token Expiration' and set the number of days based on your use case. Click Save to save the details.

👁 Copy and save authtoken

Install OData SDK for PHP

Run Terminal or Command Prompt as an Administrator, and type in the given commands to install OData SDK for PHP on your system. Change your system's current working directory to the "D:" drive.

cd D:\

Clone the odataphp repository from GitHub into a folder named "odataphp" on the D:\ drive.

git clone https://github.com/hansfn/odataphp.git

Configure PHP

1. Inside the extracted PHP directory, you’ll find the sample configuration files: "php.ini-development" and "php.ini-production".
2. Copy the file that best suits your needs and rename the copy to "php.ini".
3. Open the php.ini file in a text editor and make the following changes:
   • extension_dir = "ext" to tell PHP where to find its extensions.
   • Enable the necessary extensions by removing the semi-colon (;) at the beginning of the line (e.g., remove the ";" from ;extension = mysqli to enable MySQLi support). You'll find the following lines under the Dynamic Extensions section of the php.ini file:
     • extension = php_curl.dll
     • extension = php_xsl.dll
   • Make the following changes under the Path and Directories section of the php.ini file:
     • ; Windows: "\path1;\path2"
     • ;include_path = ".;D:\odataphp"

Add PHP and ODataPHP to the system path

1. Press Win+R on your windows system, type sys.dll, and hit Enter.
2. Go to the Advanced tab and click Environment Variables.
3. In the System variables section, find the "Path" variable and click Edit.
4. Click Newand add the path to your PHP directory (e.g., D:\odataphp and D:\php)
5. Click OK to all the dialogs.
6. Restart the Terminal or Command Prompt to apply the changes.

Work with Acumatica Data using OData API in PHP

This section outlines the process of accessing and manipulating Acumatica data using a PHP application and an OData API obtained from the CData API Server. The workflow involves defining PHP classes that mirror Acumatica entities, utilizing an API client to communicate with the OData API server, creating new records, and retrieving data for display.

Metadata and PHP Class Generation

The initial step involves obtaining metadata from the CData API Server, which reflects the structure of the Acumatica entities (tables). To obtain the metadata, open your browser and go to the metadata URL of the CData API Server: http://localhost:8080/api.rsc/$metadata

The given URL will provide you with metadata in XML format about all the available tables and data models exposed by the CData API Server. This metadata is then used to define corresponding PHP classes. The Demo_Db_Crm_Account.php file exemplifies this, where the Demo_Db_Crm_Account class is created to represent the DEMO_DB_CRM_Account entity in Acumatica. Each property of the class corresponds to a column in the Acumatica table.

Demo_Db_Crm_Account.php

class Demo_Db_Crm_Account {
 public $Id;
 public $AccountNumber;
 public $AccountSource;
 public $Active__c;
 public $AnnualRevenue;
 public $BillingCity;
 public $BillingCountry;
 public $BillingGeocodeAccuracy;
 public $BillingLatitude;
 public $BillingLongitude;
 public $BillingPostalCode;
 public $BillingState;
 public $BillingStreet;
 public $CleanStatus;
 public $CreatedById;
 public $CreatedDate;
 public $CustomerPriority__c;
 public $DandbCompanyId;
 public $Description;
 public $DunsNumber;
 public $Fax;
 public $Industry;
 public $IsDeleted;
 public $Jigsaw;
 public $JigsawCompanyId;
 public $LastActivityDate;
 public $LastModifiedById;
 public $LastModifiedDate;
 public $LastReferencedDate;
 public $LastViewedDate;
 public $MasterRecordId;
 public $NaicsCode;
 public $NaicsDesc;
 public $Name;
 public $NumberOfEmployees;
 public $NumberofLocations__c;
 public $OwnerId;
 public $Ownership;
 public $ParentId;
 public $Phone;
 public $PhotoUrl;
 public $Rating;
 public $ShippingCity;
 public $ShippingCountry;
 public $ShippingGeocodeAccuracy;
 public $ShippingLatitude;
 public $ShippingLongitude;
 public $ShippingPostalCode;
 public $ShippingState;
 public $ShippingStreet;
 public $Sic;
 public $SicDesc;
 public $Site;
 public $SLA__c;
 public $SLAExpirationDate__c;
 public $SLASerialNumber__c;
 public $SystemModstamp;
 public $TickerSymbol;
 public $Tradestyle;
 public $Type;
 public $UpsellOpportunity__c;
 public $Website;
 public $YearStarted;

 public function toArray() {
 $reflection = new ReflectionClass($this);
 $props = $reflection->getProperties(ReflectionProperty::IS_PUBLIC);
 $array = [];

 foreach ($props as $prop) {
 $name = $prop->getName();
 $array[$name] = $this->$name;
 }

 return $array;
 }
}

Creating Records in Acumatica

The add_account.php script demonstrates the creation of new records in the Acumatica database. It instantiates the Demo_Db_Crm_Account class, populates its properties with the data for the new record, and then uses the API client to send this data to the CData API Server. The API server translates this request into the necessary Acumatica operations to insert the new record into the DEMO_DB_CRM_Account table.

add_account.php

require_once 'ApiClient.php'; // Adjust path if needed
require_once 'Demo_Db_Crm_Account.php';

// API setup
$client = new ApiClient("http://localhost:8080/api.rsc", "your_username", "your_authtoken");

// Create a new account object
$account = new Demo_Db_Crm_Account();
$account->Id = uniqid(); // or use fixed ID like "1"
$account->AccountNumber = "123891";
$account->AccountSource = "Direct";
$account->BillingCity = "New York";
$account->BillingPostalCode = "14561";
$account->Name = "Company1";
$account->Phone = "9889543434";
$account->AnnualRevenue = 67890566;

// Convert object to array using Reflection
$data = $account->toArray();

// Show request payload (optional)
echo "Request JSON:\n";
echo json_encode($data, JSON_PRETTY_PRINT);

// Post to Acumatica through CData OData API Server
$response = $client->post("DEMO_DB_CRM_Account", $data);

echo "\n\nResponse:\n";
print_r($response);

Create a new record in Acumatica using the given command:

php D:\odataphp\add_account.php

API Client Abstraction

The ApiClient.php file defines the ApiClient class, which serves as an abstraction layer for interacting with the CData API Server. This class encapsulates the logic for making GET, POST, PUT, and DELETE requests, handling authentication, and processing responses. By using the ApiClient, the application code is shielded from the complexities of the OData protocol and the underlying HTTP communication. This promotes cleaner, more maintainable code.

ApiClient.php

if (!class_exists('ApiClient')) {
 class ApiClient {
 private $baseUrl;
 private $username;
 private $authtoken;

 public function __construct($baseUrl, $username, $authtoken) {
 $this->baseUrl = rtrim($baseUrl, '/');
 $this->username = $username;
 $this->authtoken = $authtoken;
 }

 private function getHeaders() {
 return [
 "Content-Type: application/json",
 "Accept: application/json",
 "Authorization: Basic " . base64_encode("{$this->username}:{$this->authtoken}")
 ];
 }

 public function post($entity, $data) {
 $url = "{$this->baseUrl}/{$entity}";
 $payload = json_encode($data);

 $ch = curl_init($url);
 curl_setopt($ch, CURLOPT_HTTPHEADER, $this->getHeaders());
 curl_setopt($ch, CURLOPT_POST, true);
 curl_setopt($ch, CURLOPT_POSTFIELDS, $payload);
 curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

 $response = curl_exec($ch);

 if (curl_errno($ch)) {
 throw new Exception("cURL Error: " . curl_error($ch));
 }

 curl_close($ch);

 // *** DEBUG: Log the raw response ***
 error_log("Raw POST Response: " . $response);

 // Ensure we decode the JSON response
 $decodedResponse = json_decode($response, true);
 if ($decodedResponse === null && json_last_error() !== JSON_ERROR_NONE) {
 throw new Exception("JSON Decode Error: " . json_last_error_msg());
 }
 return $decodedResponse;
 }

 public function get($entity) {
 $url = "{$this->baseUrl}/{$entity}";

 $ch = curl_init($url);
 curl_setopt($ch, CURLOPT_HTTPHEADER, $this->getHeaders());
 curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

 $response = curl_exec($ch);

 if (curl_errno($ch)) {
 throw new Exception("cURL Error: " . curl_error($ch));
 }

 curl_close($ch);

 // *** DEBUG: Log the raw response ***
 error_log("Raw GET Response: " . $response);

 // Ensure we decode the JSON response
 $decodedResponse = json_decode($response, true);
 if ($decodedResponse === null && json_last_error() !== JSON_ERROR_NONE) {
 throw new Exception("JSON Decode Error: " . json_last_error_msg());
 }
 return $decodedResponse;
 }
 }
}

Data Retrieval and Display

The get_data.php script retrieves data from the Acumatica DEMO_DB_CRM_Account entity and displays it. It uses the ApiClient to send a GET request to the CData API Server. The server retrieves the data from Acumatica, and the script then processes the response, mapping the data to Demo_Db_Crm_Account objects. Finally, the script iterates through these objects to display the account information.

get_data.php

require_once 'ApiClient.php';
require_once 'Demo_Db_Crm_Account.php';

// API setup
$baseUrl = "http://localhost:8080/api.rsc";
$username = "your_username";
$authtoken = "your_authtoken";

// Entity to retrieve
$entity = "DEMO_DB_CRM_Account";

// Fetch data
try {
 $client = new ApiClient($baseUrl, $username, $authtoken);
 $response = $client->get($entity);

 if ($response && is_array($response)) {
 echo "<h2>Data from \{$entity}</h2>\n";

 // Map the data to Account objects
 $accounts = [];
 foreach ($response as $accountData) {
 $account = new Demo_Db_Crm_Account();
 foreach ($accountData as $key => $value) {
 if (property_exists($account, $key)) {
 $account->$key = $value;
 }
 }
 $accounts[] = $account;
 }

 // Output the Account objects (for demonstration)
 foreach ($accounts as $account) {
 echo "Account Name: " . $account->Name . "<br>\n";
 echo "Account Number: " . $account->AccountNumber . "<br>\n";
 // ... output other properties as needed
 echo "<br>\n";
 }
 } else {
 echo "No data received or invalid data format.\n";
 if ($response === null && json_last_error() !== JSON_ERROR_NONE) {
 echo "JSON Decode Error: " . json_last_error_msg() . "\n";
 } elseif ($response === null) {
 echo "Response is NULL.\n";
 } else {
 echo "Response is not an array.\n";
 echo "Response Type: " . gettype($response) . "\n"; // Add this line
 echo "Response: " . print_r($response, true) . "\n";
 }
 }
} catch (Exception $e) {
 echo "Error: " . $e->getMessage() . "\n";
}

Retrieve all records from the Acumatica entity, including the newly added record, using the following command:

php D:\odataphp\get_data.php

Free Trial & More Information

Accessing Acumatica data from PHP can be greatly simplified by leveraging an OData API. CData API Server exposes Acumatica data as an OData service, enabling you to perform CRUD operations (Create, Read, Update, Delete) using standard HTTP requests. This allows you to work with your Acumatica data in a more structured and standardized way.

To see how API Server can simplify your data access, download a free, 30-day trial of CData API Server today! For more general information on our API Server and to see what other data sources we support, refer to our API Server page.