API Overview - quandis/qbo3-Documentation GitHub Wiki

The QBO Application Programming Interface (API) is implemented as a RESTful interface. QBO is typically used as a service layer over existing systems, injecting functionality in instances where a company's existing systems are lacking.

QBO's data tier comprises over 100 classes (tables), each of which has a matching REST endpoint. For example:

• api/contact: endpoint for all Contact-related API calls
• api/message: handler for all Message-related API calls

Every class support a common set of RESTful operations. For example:

-- Persists contact information to the Contact table
/api/contact/save?FirstName=Abby&LastName=Adams

-- Returns a row from the Message table WHERE MessageID=1
/api/message/1
/api/message/select?ID=1

-- Returns all Process rows WHERE DateOpened >= '1/1/2018'
/api/process/search?DateOpened>=1/1/2018

-- Deletes Attachments WHERE AttachmentID IN (2,3,4)
/api/attachment/delete?ID=2,3,4

-- Renders a cube of the Score table based on the CreatedDate and ScoreTemplateID dimensions.
/api/score/dashboard?Dimensions=CreatedDate,ScoreTemplateID

Note: the Document module's primary table name is Attachment. Attachment/Delete removes Attachment row(s) from the Attachment table, and deletes the corresponding file(s) from the imaging store.

Additionally, each module typically supports custom operations, such as:

• /api/contact/geocode?{Parameters}: geocodes a contact
• /api/message/send?{Parameters}: sends an email
• /api/attachment/upload?{Parameters}: uploads a document
• /api/score/calculate?{Parameters}: executes a calculation
• /api/decision/process?{Parameters}: processes a workflow

A QBO method signature represents the combination of a class, an operation, and the parameters passed to the operation, usually written as:

{ClassName}/{Operation}?{Parameters}

Method signatures can be invoked in several ways, most typically via a web browser.

When accessing QBO via a web browser, you must construct a full URL to invoke a method signature, such as:

https://acme.quandis.net/api/contact/search?LastName=Doe

or, more abstractly:

https://{BaseUrl}/api/{ClassName}/{Operation}?{Parameters}

For parameters being used to query data, the following operators are supported:

QBO's Import module supports processing QBO method signature via XML:

<ContactCollection>
  <!-- Save a new Contact -->
  <ContactItem>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
  </ContactItem>
  <!-- Geocode the White House -->
  <ContactItem Method="Geocode">
    <Address>1600 Pennsylvania Ave</Address>
    <City>Washington</City>
    <State>DC</State>
  </ContactItem>
  <!-- Delete ContactID=6 -->
  <ContactItem Method="Delete">
    <ID>6</ID>
  </ContactItem>
</ContactCollection>

The Import module's BatchEngine supports executing QBO method signatures by uploading a spreadsheet.

Note: the {Module} need not be specified when using XML to invoke method signatures.

QBO's queuing infrastructure provides a special queue, implemented as a SQL table, to allow SQL statements to cause method signatures to be invoked.

-- Geocode contacts that are missing a latitude or longitude
INSERT INTO ObjectQueue (ClassName, Operation, Attributes, ObjectQueue, Queue, QueuedDate, Uri, UserName, ExecuteCount, CreatedPersonID, CreatedDate, UpdatedPersonID, UpdatedDate)
SELECT 'Contact', 'Geocode', 'ID=' + ContactID, 'Geocoding ' + Contact, 'DefaultQueue', GETDATE(), dbo.CurrentQueueUri(), '[email protected]', 0, 1, SYSDATETIMEOFFSET(), 1, SYSDATETIMEOFFSET()
FROM Contact
WHERE Latitude IS NULL OR Longitude IS NULL

-- Launch a foreclosure process for all loans that are 90 days delinquent
INSERT INTO ObjectQueue (ClassName, Operation, Attributes, ObjectQueue, Queue, QueuedDate, Uri, UserName, ExecuteCount, CreatedPersonID, CreatedDate, UpdatedPersonID, UpdatedDate)
SELECT 'Foreclosure', 'Save', 'LoanID=' + LoanID, 'Foreclosure for ' + Loan, 'DefaultQueue', GETDATE(), dbo.CurrentQueueUri(), '[email protected]', 0, 1, SYSDATETIMEOFFSET(), 1, SYSDATETIMEOFFSET()
FROM Loan 
WHERE DATEDIFF(DAYS, LastPaymentDate, GETDATE()) >= 90

When dealing with SQL statements, new QBO developers often ask 'why queue a data operation like Contact/Geocode or Foreclosure/Save when SQL can just manipulate the data directly?'. If all the method signature is doing is data manipulation, the queuing infrastructure does not add much value. Many QBO method signatures offer functionality that is in part implemented in the application tier, and cannot be replicated in SQL. In the examples above, Contact/Geocode makes API calls to a mapping provider, and Foreclosure/Save may invoke a rule matrix to determine what workflow to launch for a given foreclosure.

For long-time users of QBO, one may still use traditional .ashx handlers in lieu of route-based API calls:

{Module}/{ClassName}.ashx/{Operation}?{Parameters}

rather than the web-specific:

/api/{ClassName}/{Operation}?{Parameters}

Every method signature may be invoked via HTTP or via other means, regardless of which method signature format the documentation uses.

To access QBO via automation, developers can leverage QBO's mixed authentication. This allows the developer's software to access QBO functionality without the need to explicitly call Security/Login.ashx. The mixed authentication works as follows:

• If there is an X-ApiKey HTTP header present, the server will authenticate against a SecurityAccess row
• If there is no User-Agent HTTP header, basic authentication will be used
• If there is an HTTP header matching any one of the headers specified in qbo.Security.Properties.Settings.BasicAuthenticationHeaders, basic authentication will be used
• If the HTTP User-Agent header contains any of the user agents strings specified in qbo.Security.Properties.Settings.BasicAuthenticationUserAgents, basic authentication will be used
• Otherwise, forms authentication will be used

By default, the configuration settings are set to:

• BasicAuthenticationHeaders: SOAPAction
• BasicAuthenticationUserAgents: Gecko/ /21.0

These may be extended via the UI from Design > Configuration > Modules > Person > Settings. Note that multiple headers and user agents may be specified with a semi-colon delimited list:

• BasicAuthenticationHeaders: SOAPAction;MyCustomHeader
• BasicAuthenticationUserAgents: Gecko/ /21.0;curl

From Design > User Accounts > {User}, the Keys tab will allow administrators to create, activate and deactivate custom ApiKeys, and limit their access to specific IP addresses and date ranges. Once created, an ApiKey can be used from code:

public void LoginWithApiKey()
{
	// Perform a PersonSearch.
	var request = HttpWebRequest.Create("https://demo.quandis.net/Security/Person.ashx/Search?DisplaySize=5");
	request.Headers.Add("X-ApiKey", "mySampleApiKey");
	request.Method = "GET";
	using (var response = request.GetResponse())
	{
		// do something with the response
		// note the response will include Set-Cookie headers for ASP.NET_SessionID and qbo.Security
	}
}

Basic authentication may be used as follows:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace qbo.Sample
{
	class Program
	{
		static void Main(string[] args)
		{
			// Make sure .NET is using TLS 1.2
			ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

			string url = "https://demo.quandis.net/api/organization.ashx/save?Organization=Amce Tool Corp.";
			var request = WebRequest.Create(url);
			var cache = new CredentialCache();
			cache.Add(new Uri("https://demo.quandis.net"), "Basic", new NetworkCredential("myUser", "myPwd"));
			request.Credentials = cache;
			request.PreAuthenticate = true;
			using (WebResponse response = request.GetResponse())
			{
				// do something with the response, if you want
			}
			Console.WriteLine("\n\nPress enter to exit");
			Console.ReadLine();
		}
	}
}

QBO supports formatting data as XHTML, XML, or JSON. By default, most method signatures invoked via the web tier respond with XHTML format to support the out-of-the-box QBO UI. For example, /api/organization/search?State=CA will return XHTML to render search results for all Organization records in California.

/api/organization/search?State=CA&Output=Xml -- get XML output
/api/organization/search?State=CA&Output=Json -- get JSON output
/api/excel/organization/search?State=CA -- get Excel output

Note: any method signature that returns a Dataset or DataReader can be formatted as Excel by calling: /api/excel/{ClassName}/{Operation}/{Parameters} Method signatures that return XmlReaders cannot be exported to Excel by default, but can be using custom Document or Scoring templates.

QBO leverages SQL views and recursion (via a common table expression) to render all data related to a single object. The Summary method will return an XmlReader over all the data related to the object in question, including ancestors, descendants, and foreign key objects. To build this result, there are a few components:

• Summary method: called ObjectData with the default parameters defined in the Summary statement
• Summary statement: this contains 'default' parameters to be used by the Summary method
• ObjectData method: this takes the output of ObjectTree, and builds a SQL statement concatenating the SelectXml statements for every related object
• ObjectTree statement: this uses a common table expression to generate a list of rows that are a pointer to each record related to a target object

It is possible for the Summary statement to return too much information. By default, all ancestors and children are included in Summary results. If an object has hundreds or thousands of children, and those children are rarely needed in the Summary statement, they can be excluded. To tailor summary results, pass one or more of the following parameters:

• AncestorDepth (int): determines how many generations of ancestors will be returned. Enter 0 to return no ancestors, 1 to return the immediate parent, etc.
• DescendantDepth (int): determines how many generations of descendants will be returned. Enter 0 for no descendants, 1 for children, 2 for grandchildren, etc.
• Ancestors (string): a space-delimited string of ancestors to be included (if NULL, all ancestors will be included)
• Descendants (string): a space-delimited string of descendants to be included (if NULL, all descendants will be included)
• ForeignKeys (string): a space-delimited string of foreign key columns to include in the output (if NULL, no foreign keys will be included)
• MaxCount (int): determines the maximum number of child records of a given table and generation to include

Note: when specifying the Ancestors and Descendants parameters, ensure you prefix and suffix the value with a space. E.g: "Decision DecisionStep Message Process Ledger" will ignore Decision and Ledger because they don't have a space on both sides " Decision DecisionStep Message Process Ledger " will work as intended

Examples:

-- this will not include Property
/api/loan/summary?ID=1&AncestorDepth=0 
-- this will include children but not grand children (e.g. Decision but not DecisionStep)
/api/loan/summary?ID=1&DescendantDepth=1 
-- this would exclude Attachments and Messages
/api/loan/summary?ID=1&Descendants= Decision DecisionStep Process Ledger LedgerItem 

Such parameters can be made default parameters by creating a Summary Statement from Design > Configuration > Installed Modules > {Module} > Statements, and adding one or more of the parameters above to the statement.

When calling Each QBO class implements a Save endpoint, as well as Insert and Update. Save will cascade to Update if an ID is specified or inferred, otherwise Insert will be called.

These method signatures achieve the same result - update ContactID 1 with a LastName of 'Doe Smith':

/api/contact/save?ID=1&LastName=Doe Smith
/api/contact/update?ID=1&LastName=Doe Smith

These method signatures achieve the same result - insert a new contact named Jane Doe:

/api/contact/save?FirstName=Jane&LastName=Doe
/api/contact/insert?FirstName=Jane&LastName=Doe

Third party data sources may identify unique keys to QBO by using a SubscriberID property.

/api/contact/save?FirstName=Jane&LastName=Doe Smith&SubscriberID=www.acme.com-Customer-12345

Specifying a globally unique string (in this case, www.acme.com-Customer-12345) as a SubscriberID will cause QBO to:

• Look in the ObjectSubscription table to see if the string already exists
• If it exists, the current ID for the record will be retrieved, and Save will cascade to Update
• If it does not exists, Save will cascade to Insert, and a new ObjectSubscription row will be added for the new record

When contemplating how to construct a SubscriberID, a recommended pattern is:

<domain>-<application>-<table>-<primary key>

where:

Autobind statements may be used to configure alternate or compound keys on any QBO class.

Create a Contact/AutoBindBySSN statement with a USSSN parameter as:

SELECT ContactID FROM Contact WHERE USSSN = @USSSN

Assume the following row in the Contact table:

The following method signatures are equivalent: they will change ContactID 1's LastName to 'Doe Smith'

/api/contact/update?ID=1&LastName=Doe Smith
/api/contact/update?USSSN=123456789&LastName=Doe Smith

Create a Loan/AutoBindByAgency statement with Agency and AgencyLoanID parameters as:

SELECT LoanID FROM Loan WHERE AgencyLoanID = @AgencyLoanID AND AgencyID = (
  SELECT OrganizationID FROM Organization WHERE Organization = @Agency
)

and

The following method signatures are equivalent:

/api/loan/save?ID=1&UPBAmount=0
/api/loan/save?Agency=Freddie Mac&AgencyLoanID=001122334455&UPBAmount=0

Note: QBO classes may have multiple AutoBind statements; they are evaluated in alphabetic order until an ID property is returned.

The Save method supports saving data in related records. Foreign key records (including parent records) can be saved by passing parameters in the format {ForeignKey without 'ID'}_{property}:

-- Save to Loan.PropertyID
/api/loan/save?ID=1&Status=Delinquent&Property_VacancyStatus=Vacant

-- Save to Ledger.PayeeID
/api/ledger/save?Ledger=Invoice&Payee_Company=My Favorite Client&Payee_Phone=800.555.1212

Child records may be saved by passing parameters in the format {Child}_{Index}_{property}:

-- Create a new Organization with a child Message
/api/organization/save?Organization=My Favorite Client&Messages_0_Message=Welcome to the jungle!

-- Update a property as Listed with a child Contact record representing the realtor
/api/property/save?ID=16&Status=Listed&Contacts_0_Contact=Realtor&[email protected]

Note: if passing more than 1 child record, the {Index} value must be 0-based, and may not skip numbers.

Each QBO table includes a Status and {Table}Type column. The values for these columns are defined by power users from Design > Configuration > {Module}, via the Status or Types tabs. Technically, each possible defined Status is stored in the ObjectStatus table, and each possible defined Type is defined in the ObjectType table. A standard UI pattern is to render a select list from the ObjectStatus or ObjectType table.

It is also a common practice (but certainly not required) for a business unit to define 'Active' records as that are in a list of status, or not in a list of statuses. For example:

• Active Loans: loans that are not in a status of 'Paid in Full', 'Liquidated', 'Charged Off', or 'Transferred'
• Active Workflow templates: templates that are not in a status of 'Inactive' or 'Deprecated'
• Active Brokers: Brokers in a status of 'Active' or 'Pending Approval'

There may be many queries that need to re-use this concept of 'active' records. The ObjectStatus.FinalState column enables a more generic query:

• Active records: records where the status is not a Final State
  • Loans: Paid in Full | Liquidated | Charged Off | Transferred would all be marked as final state
  • Workflow templates: Inactive | Deprecated would be marked as final state
  • Brokers: everything except Active | Pending Approval would be marked as final state

This allows use of a generic Active filter by many queries, so that if the business unit adds new Status options, they can choose whether records in that Status are considered part of the active filter or not.

Developers who prefer learning with real-world example can use Fiddler to inspect the API calls made as one navigates through the QBO user interface. In the image below, Fiddler is used to inspect what happens when saving a Message: