Skip to main content
knowledgecenter.avangate.com

Instant Order Search Export (ISE)

Overview

Export order data for your account from the Avangate platform.

What is ISE?

Instant Order Search Export (ISE) works as a data portability service designed to let you export order information from your Avangate account packaged as either CSV or XML files. 

Method and URL

GET or POST: https://secure.avangate.com/action/ise/

Parameters

Avangate captures the parameters you send and exports the data to the client.

Field Description Required Used in HASH validation

MERCHANT

Your Avangate merchant code. The merchant code and secret key for your account are available here.

Required, cannot be empty

YES

STARTDATE

The start date, in format Y-m-d (ex: 2010-05-22)

Required, cannot be empty

YES

ENDDATE

The end date, in format Y-m-d (ex: 2010-05-22)

Required, cannot be empty

YES

ORDERSTATUS

The order status:

  • ALL 
  • COMPLETE 
  • REFUND 
  • UNFINISHED

Required, cannot be empty

YES

REQ_DATE

The time of request (UTC), in format YmdHis:

Y = year, 4 digits

m = month, 2 digits

d = day, 2 digits

H = hour, 2 digits

i = minute, 2 digits

s = seconds, 2 digits

Required, cannot be empty

YES

PRODUCT_ID

Unique, system-generated Avangate product identifier

Required, can be empty

YES

COUNTRY_CODE

Two letter country code (download a complete list from via this link)

Required, can be empty

YES

FILTER_STRING

Query value that depends on the value of the FILTER_FIELD. See FILTER_FIELD for more info.

Required, can be empty

YES

FILTER_FIELD

Filter field designed to lower the search result. Use in conjunction with FILTER_STRING.

Possible values:

  • REFNO (Avangate order reference) 
  • REFNOEXT (vendor order reference) 
  • NAME (customer name or company) 
  • EMAIL (customer billing email) 
  • COUPONCODE (order coupon code)

To search only a particular order, the value of FILTER_FIELD needs to be REFNO, while the value of the FILTER_STRING should be the unique order reference from the Avangate system.

Required, can be empty

YES

HASH

The MD5 HMAC key for the request

Required

N/A

INCLUDE_DELIVERED_CODES

Includes delivered codes

Optional

NO

INCLUDE_FINANCIAL_DETAILS

Includes financial details. (Net profit,Backup Media Cost,Backup Media Profit, DIS COST, DIS Profit)

Optional

NO

INCLUDE_EXCHANGE_RATES

Includes exchange rates

Optional

NO

INCLUDE_PRICING_OPTIONS

Includes pricing options (pricing option text, pricing options value)

Optional

NO

EXPORT_FORMAT

This parameter controls the format of file used for the export process. Possible values:

  • CSV
  • XML

Optional

NO

INCLUDE_CLIENTIP

Include end user IP address

Optional

NO

INCLUDE_NETINCOMEPERPRODUCT

Include net income per product

Optional

NO

USE_REPORT_MODE

  • YES - the search process returns orders in accordance with the date when they reached the Finished status. Finish date needs to be within the STARTDATE and ENDDATE interval.
  • NO (default) - the search process returns orders in accordance with the date when they were placed. Place order date need to be within the STARTDATE and ENDDATE interval.

Optional

NO

EXPORT_TEST_ORDERS

Possible values:

  • YES - Include test orders along with real order.
  • NO (default) - Do not include test order data and export only details for actual purchases.
  • ONLY - Export only test order data.

Optional

NO

EXPORT_TEMPLATE_ID

Return order search information specific to templates configured for Order Search Export by using their unique IDs. For eStore, navigate to Order search under Orders &customers and make sure that the eStore orders tab is selected. Run a search to identify the orders you're looking for, and on the right hand side of the screen click the Export button, and the Edit templates link in the popup dialog box. The template identifier is visible in the URL as the IdTemplate parameter. 

When using EXPORT_TEMPLATE_ID, you can exclude INCLUDE_DELIVERED_CODES, INCLUDE_FINANCIAL_DETAILS, INCLUDE_EXCHANGE_RATES, INCLUDE_PRICING_OPTIONS, INCLUDE_CLIENTIP and INCLUDE_NETINCOMEPERPRODUCT, if the Order search Export template already contains such information.

Data in the Order Search Export template takes precedence over INCLUDE_DELIVERED_CODES, INCLUDE_FINANCIAL_DETAILS, INCLUDE_EXCHANGE_RATES, INCLUDE_PRICING_OPTIONS, INCLUDE_CLIENTIP and INCLUDE_NETINCOMEPERPRODUCT - this means that these parameters are ignored if they overlap with template fields.

Optional

NO

INCLUDE_CHARGEBACKS_STATUS_AND_REASON

Includes the status and reason provided for chargebacks. Available values:

  • YES
  • NO
Optional. NO

Requirements

HASH validation

  • To validate the MD5 hash signature, you're required to include all mandatory parameters. Avangate throws an error if one of these parameters is missing.
  • Include PRODUCT_ID and COUNTRY_CODE. Use empty values to return all results. 
  • The interval between STARTDATE and ENDDATE can not be greater than 45 days (for performance purposes).
  • Use UTC for the time zone of REQ_DATE. The difference between the value of REQ_DATE and the moment when you send the request to Avangate must be smaller than 5 minutes, or Avangate will respond with the 'Request expired' error message. 

Authentication

Authenticate requests using:

  • HASH. This ia a MD5 Hash-based message authentication code (HMAC) that you create using your account's secret key and the parameters marked as mandatory for HASH validation.  To build the HMAC_MD5 source string, prepend each value with its own length in bytes. Use 0 for null or empty values without prepending their length. However, when the value is 0 (zero), you do need to prepend its length (1). Note that for UTF-8 characters the length in bytes can be longer that the string length.
  • REQ_DATE. Use UTC time zone. 

Optional: the authentication can be restricted by IP or range of IPs from firewall (Accounts settings ->User access -> Firewall) for the special service user. If the IP from where you make the request is not configured in Avangate, the authentication fails.

Response

Provided that your request is valid, you will receive the information in line, in the requested format (parameter EXPORT_FORMAT), with HTTP code 200.

Errors

Avangate returns errors in XML format. This includes authentication errors and no results after a search messages. Avangate throws errors with a 400 HTTP error code for the result page. Sample response XML:

<EPAYMENT> 
<RESPONSE_CODE>1</RESPONSE_CODE> 
<RESPONSE_MSG>Request has expired</RESPONSE_MSG> 
<RESPONSE_DATE>20110209144927</RESPONSE_DATE> 
<HASH>2f69dda102b2a29e077452f64f4dca1f</HASH> 
</EPAYMENT>

The XML can be validated using the same MD5 HMAC signature algorithm, using these fields: RESPONSE_CODE, RESPONSE_MSG, RESPONSE_DATE and using the account hash key.

Field

Description

RESPONSE_CODE

The response code (two digits)

RESPONSE_MSG

A more explanatory response message

RESPONSE_DATE

The time of response (UTC), in format YmdHis:

Y = year, 4 digits

m = month, 2 digits

d = day, 2 digits

H = hour, 2 digits

i = minute, 2 digits

s = seconds, 2 digits

 

Possible error codes and values:

Message code

Message description

Resolution

0

No result found for the searched criteria

Check search filters

1

Request has expired

Check REQ_DATE

2

Not all the mandatory variables are present

See required fields above

3

The selected interval is greater than 45 days

Check STARTDATE and ENDDATE

4

MERCHANT is missing or incorrect

Check MERCHANT value

5

ORDERSTATUS is missing or invalid

Check ORDERSTATUS value

6

Ip not allowed by firewall

Check firewall rules for special service user

7

HASH is missing or invalid

Double check how MD5 hash signature is calculated

8

REQ_DATE is missing or invalid

REQ_DATE is missing or not in the right format

9

FILTER_FIELD is invalid

Check possible values of FILTER_FIELD

10

FILTER_STRING is missing or invalid

FILTER_FIELD is set to limit search. Check FILTER_STRING value.

11

Module is not active for your account

Instant Search Export service is not active for your account. Please contact account manager.

12 EXPORT_TEMPLATE_ID is invalid Check the EXPORT_TEMPLATE_ID value.
13 Country code is incorrect. Provide a valid country code.

 

Code sample

<?php
/*
 Name: Example for Instant Search Export in Avangate (www.avangate.com)
 GET OR POST SENT
*/

$secret_key = 'SECRET_KEY_ACCOUNT'; //secret key is available on this page https://secure.avangate.com/cpanel/account_settings.php
$base_link = 'https://secure.avangate.com/action/ise.php';
$proxy_user = 'PROXY_USER_IF_NEEDED';
$proxy_pass= 'PROXY_PASSWORD_IF_NEEDED';
date_default_timezone_set('UTC');


//*********SETTING PARAMETERS*********
$link_params = array();
$not_in_hash = array('HASH','INCLUDE_DELIVERED_CODES','INCLUDE_FINANCIAL_DETAILS','INCLUDE_EXCHANGE_RATES','INCLUDE_PRICING_OPTIONS','EXPORT_FORMAT');

//REQUIRED, CANNOT BE EMPTY:
$link_params['MERCHANT'] = 'MERCHANT_CODE'; //merchant code is available on this page https://secure.avangate.com/cpanel/account_settings.php
$link_params['STARTDATE'] = date("Y-m-d", strtotime('-1 month',strtotime(date('Y').'/'.date('m').'/01'.' 00:00:00'))); //first day from last month
$link_params['ENDDATE'] = date("Y-m-d", strtotime('-1 second',strtotime(date('Y').'/'.date('m').'/01'.' 00:00:00'))); //last day from last month

$link_params['ORDERSTATUS'] = 'ALL'; // replace with any of  ALL, COMPLETE, REFUNDED, UNFINISHED
$link_params['REQ_DATE'] = date('YmdHis');

//CAN BE EMPTY:
$link_params['PRODUCT_ID'] = '';
$link_params['COUNTRY_CODE'] = '';
$link_params['FILTER_STRING'] = '';

//REQUIRED, CAN BE EMPTY:
$link_params['FILTER_FIELD'] = ''; // EMPTY OR: REFNO, REFNOEXT, NAME, EMAIL, COUPONCODE

//REQUIRED:
$link_params['HASH'] = '';

//OPTIONAL:
$link_params['INCLUDE_DELIVERED_CODES'] = '';
$link_params['INCLUDE_FINANCIAL_DETAILS'] = '';
$link_params['INCLUDE_EXCHANGE_RATES'] = '';
$link_params['INCLUDE_PRICING_OPTIONS'] = '';
$link_params['EXPORT_FORMAT'] = ''; //possible values CSV or XML -    if you’re using this sample, please specify the desired export format


//*********GET Base string for HMAC_MD5 calculation:*********
$result = '';
while(list($key, $val) = each($link_params)){
    $$key=$val;
    /* get values */
    if(!in_array($key,$not_in_hash)){

        if(is_array($val)) $result .= ArrayExpand($val);
        else{
            $size = strlen(StripSlashes($val));
            $result .= $size.StripSlashes($val);
        }

    }
}

//*********Calculated HMAC_MD5 signature:*********
$hash =  hmac($secret_key, $result);
$link_params['HASH']=$hash;

$get_vars = http_build_query($link_params, '', '&');


//*********MAKE POST CALL to get ISE results*********
$ch = curl_init($base_link.'?'.$get_vars);
curl_setopt($ch, CURLOPT_POSTFIELDS, null);
curl_setopt($ch, CURLOPT_POST, FALSE);
curl_setopt($ch, CURLOPT_HTTPGET, TRUE);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_HEADER, 0);
//**use the following 3 lines only if you have a proxy set!**
//curl_setopt($ch, CURLOPT_HTTPPROXYTUNNEL, 1);
//curl_setopt($ch, CURLOPT_PROXY, 'YOUR_PROXY_ADDRESS:PORT');
//curl_setopt($ch, CURLOPT_PROXYUSERPWD, $proxy_user.':'.$proxy_pass);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);  // RETURN THE CONTENTS OF THE CALL
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$responseData = curl_exec($ch);
$headerCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
$contentType = curl_getinfo($ch, CURLINFO_CONTENT_TYPE);
//echo 'Curl error: '.curl_error($ch);
curl_close($ch);


//*********PROCESS RESULTS*********
if ($headerCode==200){
    // do something with the csv or xml received
    //the format of the export file is set using $link_params['EXPORT_FORMAT']
    $exportType = strtolower($link_params['EXPORT_FORMAT']);
    $headerType = 'Content-type: application/'.$exportType.';charset=UTF-8';
    $headerDisposition = 'Content-Disposition: attachment; filename="ise.'.$exportType.'"';
    header($headerType);
    header($headerDisposition);
    echo $responseData;
}
else{
    //no valid answer received: request period is too big, etc.
    if ( strpos($contentType,'xml')===false){
        echo 'Header returned: '.$headerCode;        
        echo $responseData;
    }
    else{
        //YOUR CODE HERE AFTER RECEIVING the xml with one of the codes from Instant Search Export Handbook
        $xml = $responseData;
        $xml = simplexml_load_string($xml);
        $response = array();
        $i=0;
        foreach ($xml->children() as $child) {
            $response[$i] = $child;
            $i++;
        }
        echo $xml->asXML();
    }
}

//*********FUNCTIONS FOR HMAC*********
function ArrayExpand($array){
    $retval = "";
    foreach($array as $i => $value){
        if(is_array($value)){
            $retval .= ArrayExpand($value);
        }
        else{
            $size        = strlen(StripSlashes($value));
            $retval    .= $size.StripSlashes($value);
        }
    }    

    return $retval;
}
function hmac ($key, $data){
   $b = 64; // byte length for md5
   if (strlen($key) > $b) {
       $key = pack("H*",md5($key));
   }
   $key  = str_pad($key, $b, chr(0x00));
   $ipad = str_pad('', $b, chr(0x36));
   $opad = str_pad('', $b, chr(0x5c));
   $k_ipad = $key ^ $ipad ;
   $k_opad = $key ^ $opad;
   return md5($k_opad  . pack("H*",md5($k_ipad . $data)));
}

?>