The following php script is hosted on our server and can be tested using the following URL https://api.stanleystella.com/sample/test.php

Parameters can be passed as follows

• user (mandatory) : your user name

• password (mandatory) : your password

• dataset (optional) : can be "products" or "product_images". If none specified, product_images is called.
  

• stylecode (optional) : the style you wish to filter the list on (ex : STTM522)

• colorcode (optional) : the color you wish to filter the call on (ex : C001)

• languagecode (optional) : the language you wish to extract (ex: en_EN, fr_FR, nl_BE, it_IT, de_DE, es_ES). only works for the product call
  

URL Syntax will thus be

https://api.stanleystella.com/sample/test.php?user=user@domain.com&password=pwwd&dataset=products&stylecode=STTM522&colorcode=C001&languagecode=fr_FR

<?php

// Set API Url
// Set different URL according to dataset requested

if ($_GET["dataset"]==="products")
    {
    $url = 'https://api.stanleystella.com/webrequest/products/get_json'; 
    $csvFileName = 'products.csv'; 
    $jsonData = array( 
    'jsonrpc' => '2.0', 
    'method' => 'call', 
    'params' => array( 
        'db_name' => 'production_api', 
        'password' => $_GET["password"], 
        'user' => $_GET["user"], 
        'StyleCode' => $_GET["stylecode"], 
        'ColorCode' => $_GET["colorcode"], 
        'LanguageCode' => $_GET["languagecode"], 

        ), 
    'id' => 0 
    ); 
    } 
else 
    { 
    $url = 'https://api.stanleystella.com/webrequest/products_images/get_json'; 
    $csvFileName = 'products_images.csv'; 
    $jsonData = array( 
    'jsonrpc' => '2.0', 
    'method' => 'call', 
    'params' => array( 
        'db_name' => 'production_api',
    'password' => $_GET["password"], 
        'user' => $_GET["user"], 
        'StyleCode' => $_GET["stylecode"], 
        'ColorCode' => $_GET["colorcode"], 
        ), 
    'id' => 0 
    ); 
    } 

//Initiate cURL 
$ch = curl_init($url); 

//Encode the array into JSON. 
$jsonDataEncoded = json_encode($jsonData); 

// Setup cURL 
    // Do not return the result in the browser 
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 

    // Set https 
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0); 
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0); 

    //Tell cURL that we want to send a POST request. 
    curl_setopt($ch, CURLOPT_POST, 1); 

    //Attach our encoded JSON string to the POST fields. 
    curl_setopt($ch, CURLOPT_POSTFIELDS, $jsonDataEncoded); 

    //Set the content type to application/json 
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json')); 

//Execute the request 
$result = curl_exec($ch); 

//Decode output and isolate the content of the "result" key 
$jsonDataDecoded = json_decode(json_decode($result)->result, true); 

//Close CURL
curl_close($ch); 

// Generate csv 

    $fp = fopen($csvFileName,'w+'); 
    fprintf($fp, chr(0xEF).chr(0xBB).chr(0xBF)); 

    foreach($jsonDataDecoded as $row){ 
        fputcsv($fp,$row); 
        } 

    fclose($fp); 

echo('File generated with following parameters<br>'); 
echo('Style: '. $_GET["stylecode"].'<br>'); 
echo('Color : '. $_GET["colorcode"].'<br>'); 
echo('Language : '. $_GET["languagecode"].'<br>'); 
echo('<a href="https://api.stanleystella.com/sample/'.$csvFileName.'">Click here to download the '.$csvFileName.' file</a><br>'); 

?>

 

$.ajax({
    url: 'https://api.stanleystella.com/webrequest/products/get_json',
    type: 'post',
    dataType: 'json',
    data: JSON.stringify({
         "jsonrpc":"2.0",
         "method":"call",
         "params":{
              "db_name": "production_api",
              "user": "someuser@somedomain.tld",
              "password": "somepassword"
         },
         "id": 0
}),
headers: {'Content-type': 'application/json'}
});

$.ajax({
    url: 'https://api.stanleystella.com/webrequest/products/get_json',
    type: 'post',
    dataType: 'json',
    data: JSON.stringify({
         "jsonrpc":"2.0",
         "method":"call",
         "params":{
              "db_name": "production_api",
              "user": "someuser@somedomain.tld",
              "password": "somepassword",
              "LanguageCode": "fr_FR",
              "StyleCode": "STTM528",
              "ColorCode": "C001",
              "SizeCode": "XXL"
         },
         "id": 0
}),
headers: {'Content-type': 'application/json'}
});

' Sample VBA Implementation for the calling the API from excel
' Code can be fine-tuned to adapt your needs

Option Explicit 

Public Function CallAPI()

' Variables declaration

' Basics

Dim EndpointURL As String
Dim RequestURL As String
Dim CallName As String
Dim Body As String

' Filters and parameters 

Dim DBName As String
Dim Login As String
Dim Password As String
Dim StyleCode As String
Dim ColorCode As String
Dim SizeCode As String
Dim LanguageCode As String 
Dim FilterList As String 

' Reponses 

    ' Response to the Call 
    Dim CallResponse As String
    Dim CallResponseArray() As String 

    ' Result 
    Dim ResponseResult As String 
    Dim ResponseResultArray() As String 

    ' Result Line 
    Dim ResponseResultLine As Variant 

    Dim ResponseUpdateArray() As String 
    Dim ResponseUpdateItem As Variant 
    Dim UpdateItem() As String 

' SQL String 

Dim SQL As String
Dim SQLFields As String
Dim SQLValues As String 

' Output to Excel Sheet 

Dim Column As Integer Column = 1
Dim Line As Integer Line = 2
Dim CheckWorksheet As Worksheet 

Dim oHttp As Object 

' Variables initialization 

' Initialize Call Parameters 

EndpointURL = "https://api.stanleystella.com/webrequest/"
DBName = "production_api"

' Uncomment the appropriate line for the call you want to perform

CallName ="products" 
' CallName = "products_images"

' Initialize Filters with Filters Tab values 

Login = "Insert.your@login.here"
Password = "password"
StyleCode = "STSU807"
ColorCode = "C582"
SizeCode = "S" 
LanguageCode = "en_US" 

' Uncomment to check in the Immediate Window
' Debug.Print "+++ Style Code : " & StyleCode 
' Debug.Print "+++ Color Code : " & ColorCode 
' Debug.Print "+++ Size Code : " & SizeCode 
' Debug.Print "+++ Language Code : " & LanguageCode 

' Select Filters depending on the call to perform 

If CallName = "products" Then 

    FilterList = StyleCode & "," & ColorCode & "," & SizeCode & "," & LanguageCode 

Else 

    FilterList = StyleCode & "," & ColorCode 

End If 

' Uncomment to check in the immediate window
' Debug.Print = "+++ FILTER LIST : " & FilterList 

Body = "{""jsonrpc"": ""2.0"", ""method"": ""call"", ""params"": { ""db_name"" :""" & DBName & """, ""user"" : """ & Login & """, ""password"": """ & Password & """, " & FilterList & " } }" 
RequestURL = EndpointURL & CallName & "/get_json" 

' Uncomment to check in the Immediate Window
' Debug.Print "+++ POST Endpoint : " & RequestURL 
' Debug.Print "+++ POST Message : " & Body 

' Create the WorkSheet to receive data 

Set CheckWorksheet = Nothing
On Error Resume Next 

Set CheckWorksheet = Worksheets(CallName) 
On Error GoTo 0 

    ' Check If the Worksheet Exists 

    If CheckWorksheet Is Nothing Then 

        ' If not, create it 

        Worksheets.Add.Name = CallName 

    Else 

        ' If Yes, flush it to avoid duplicates 

        Worksheets(CallName).Cells.Clear 

    End If 

' Post the call 

Set oHttp = CreateObject("MSXML2.XMLHTTP") 
oHttp.Open "POST", RequestURL, False oHttp.SetRequestHeader "Content-type", "application/json" 
oHttp.SetRequestHeader "Accept", "application/json" 
oHttp.Send Body 

' Get the call reponse and perform some cleaning 

CallResponse = Replace(oHttp.ResponseText, "\""", """") 

    ' Split Reponse and get Result array 

    CallResponseArray() = Split(CallResponse, "[{") 

    ' Set the ResponseResult String 

    ResponseResult = CallResponseArray(1) 

    ' Remove trailing Characters and perform additionnal cleaning 

    ResponseResult = Replace(ResponseResult, "}]", "") 
    ResponseResult = Replace(ResponseResult, """: ", "|") 
    ResponseResult = Replace(ResponseResult, """", "") 
    ResponseResult = Replace(ResponseResult, "}", "") 

        ' This is not really nice and is needed because excel sadly interpretates 
        ' UTF-8 encoding. 
        ' This should be replaced by a proper conversion table in PROD environments 

        ResponseResult = Replace(ResponseResult, "\\u00b0", "°") 

        ResponseResult = Replace(ResponseResult, "\\u00e0", "à") 
        ResponseResult = Replace(ResponseResult, "\\u00e4", "ä") 
        ResponseResult = Replace(ResponseResult, "\\u00c4", "Ä") 

        ResponseResult = Replace(ResponseResult, "\\u00e9", "é") 
        ResponseResult = Replace(ResponseResult, "\\u00e8", "è") 
        ResponseResult = Replace(ResponseResult, "\\u00ea", "ê") 

        ResponseResult = Replace(ResponseResult, "\\u00f4", "ô") 

        ResponseResult = Replace(ResponseResult, "\\u00fb", "û") 

        ResponseResult = Replace(ResponseResult, "\\n", " ") ' This one should be <br> 

    ' Define Response lines 

    ResponseResultArray() = Split(ResponseResult, "{") 

    For Each ResponseResultLine In ResponseResultArray() 

        ' Debug.Print "*** Writing line #" & Line & " ***" 

        ' Reset Fields and Values 

        SQLFields = """" 
        SQLValues = """" 

        ' Start writing the line at column 1 

        Column = 1 

        ' Get the binomial array Field|FieldValue 

        ResponseUpdateArray() = Split(ResponseResultLine, ",") 

            ' And for each pairing found, extract Field and Value 

            For Each ResponseUpdateItem In ResponseUpdateArray() 

                ' Split the pair between field name and field value 
                ' Field has id (0) 
                ' Value has id (1) 

                UpdateItem() = Split(ResponseUpdateItem, "|") 

                ' Set the SQL Field 

                SQLFields = Trim(UpdateItem(0)) 

                ' Add the field on line one, in the appropriate column 

                Sheets(CallName).Cells(1, Column).Value = SQLFields 
                ' Debug.Print " -> SQLFields : " & SQLFields 


                ' Build SQL Value Chain 

                    ' Check if list contains more than one item 

                    If (UBound(UpdateItem) - LBound(UpdateItem) + 1) > 1 Then 

                        ' If Yes, add value to the chain 
                        SQLValues = Trim(UpdateItem(1)) 

                    Else 

                        ' If Not just add empty value 
                        SQLValues = "" 

                    End If 

            ' Enable this line to check the log of data added in the Immediate Window 
            ' Debug.Print " -> SQLFields : " & SQLFields & " -> " & SQLValues 

            ' Write the value somewhere, for instance in an excel sheet 
            Sheets(CallName).Cells(Line, Column).Value = SQLValues 

            Column = Column + 1 

            Next ResponseUpdateItem 

        ' DEBUG : Let's check the values in the Immediate Window 
        ' Debug.Print "**************************" 
        ' Debug.Print "Resulting SQL Field list :" & SQLFields 
        ' Debug.Print "Resulting SQL Value list :" & SQLValues 

    Line = Line + 1 

    Next ResponseResultLine 

End Function