Extensions API - quandis/qbo3-Documentation GitHub Wiki

The QBO API can be extended by power users or developers via:

• Statements: custom SQL queries,
• Services: method signatures mapped to plugins that implement the IService interface,
• Classes: create new, customized QBO classes (developer only, requires Visual Studio)
• Extension Methods: extend existing QBO classes with C# extension methods.

The QBO framework evaluates a method signature via a waterfall evaluation. Given a method signature call in the form:

{Class}/{Operation}?{Parameters}

• If a class has a C# method marked with a DbMethodAttribute matching the Operation, the C# method is called,
• If a class has a Service matching the Operation, the Service's plugin is called
• If a class has a Statement matching the Opeation, the Statement is called

To create a new statement-based method signature Contact/MyCustomStatement class:

POST /Application/ObjectConfgiruation.ashx/StatementEdit
Table=Contact
&Name=MyCustomStatement
&ReturnType=DataSet
&Query=SELECT TOP 10 * FROM Contact

Once this statement has been created, the following method signatures is valid:

GET /Contact/Contact.ashx/MyCustomStatement
GET /Contact/Contact.ashx/MyCustomStatement?Output=Xml
GET /Contact/Contact.ashx/MyCustomStatement?Output=Json

Statement may leverage several patterns implements by the QBO SqlPattern class to 'shape' the SQL passed by the application to the database server.

The {Where.Basic} expression will match a method signature's {Parameters} to class properties, generating an appropriate WHERE clause:

Contact/Search?LastName=Patr%&ContactTemplateID=&Status=Valid&Status=Invalid

will generate this SQL:

SELECT * FROM Contact WHERE LastName LIKE 'Patr%' AND Status IN ('Valid', 'Invalid')

Given the Contact table's foreign keys include ParentContactID (key to Contact.ContactID) and ContactTemplateID (key to ContactTemplate.ContactTemplateID), we often do the following:

SELECT
    Contact.ContactID, ...
    ParentContact.Contact AS ParentContact, ...
    ContactTemplate.ContactTemplate AS ContactTemplate, ...
FROM Contact
    LEFT OUTER JOIN Contact AS ParentContact WITH (NOLOCK) ...
    LEFT OUTER JOIN ContactTemplate AS ContactTemplate WITH (NOLOCK) ...

Abstractly, this pattern includes:

• From clause: left outer join to all FK tables, aliasing the table as the FK column name (without the 'ID')
• Select clause: include the FK table's "main column" aliased as the FK column name (without the 'ID')

You may construct a Statement.Query like this:

SELECT {Select.ForeignKey} FROM {From.ForeignKey}

and SqlPattern will replace {Select.ForeignKey} with the list of columns following the select pattern above, and {From.ForeignKey} with the FROM statement following the pattern above.

A nice feature of the SqlPattern class is automatic creation of IN clauses for dimensions. For example, we may want to display all contacts in New England:

... WHERE State IN ('ME', 'MA', 'NH', 'VT', 'CT', 'RI') ...

This can be done over the wire with:

Contact/Search?State=ME,MA,NH,VT,CT,RI

However, there are use cases where we want similar string substitution but are not using dimensions. An example is leveraging SQL's OPENROWSET() function to read Excel files; it does not allow use of variables to identify a file path.

-- This is not valid TSQL
DECLARE @FileName nvarchar(50) = 'MyTestFile.xlsx'
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=' + @FileName + ';HDR=Yes', 'SELECT * FROM [Sheet1$]') 

In such cases, we can use literal substitution with {Param.{ParamName}}. For example:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database={Param.FileName};HDR=Yes', 'SELECT * FROM [Sheet1$]')

Note: do not allow direct user data entry of such parameters; users could enter data that may result in SQL injection.

For tables containing XML columns (such as ImportForm.XmlData), the {Where.Basic} clause support search and order by using SQL XML expressions.

ImportForm.ashx/Search?ImportFormTemplateID=22&XmlData.MyCustomField=SomeValue&OrderBy=XmlData.AnotherCustomField

This would generate a SQL clause like this:

SELECT ...
FROM ImportForm WITH (NOLOCK)
 ...
WHERE 
 [ImportForm].[XmlData].value('(//MyCustomField)[1]', 'nvarchar(255)') IS NOT NULL 
 ...
ORDER BY 
 [ImportForm].XmlData.value('(//AnotherCustomField)[1]', 'nvarchar(255)') DESC

Note that the expression XmlData.MyCustomField translates to //MyCustomField. You can specify a more precise XPath expression using a 'dot' notation:

Search?XmlData.ImportFormXml.MyGroup.MyCustomField=X generates ImportFormXml/MyGroup/MyCustomField
Search?XmlData...MyInnerNode.MyCustomField=X generates //MyInnerNode/MyCustomField

In designing these patterns, some tricky issues with inherited classes arose. For example, any Process-derived class (SkipTrace, Foreclosure, Bankruptcy, etc.) needs to address four issues:

• When inserting or updating, one must insert/update into the base table and derived table (e.g. INSERT INTO Process and INSERT INTO SkipTrace),
• One must ensure that the correct parameters are mapped into the correct table's insert or update statement,
• If there are no specified parameters for a table, the entire statement for the table must be omitted, and
• The where clause for a base table must be based upon primary keys for the derived table

In a configuration file, if a query is marked with Cascade="true", the SQL statement will be built from all non-abstract classes. For example, Abstract.config defines the basic structure of an Insert statement as:

INSERT INTO {Table} ({Select.Insert}) VALUES ({From.Insert}) SELECT SCOPE_IDENTITY() AS {Table}ID

For a non-derived class like Contact, this will produce:

INSERT INTO Contact (Contact, FirstName, LastName, ...) VALUES (@Contact, @FirstName, @LastName, ...) SELECT SCOPE_IDENTITY() AS ContactID

For a Process-derived class like SkipTrace, this will produce:

INSERT INTO Process (Process, ...) VALUES (@Process, ...) SELECT SCOPE_IDENTITY() AS ProcessID
INSERT INTO SkipTrace (SkipTrace, ..., ProcessID) VALUES (@SkipTrace, ..., SCOPE_IDENTITY()) SELECT SCOPE_IDENTITY() AS SkipTraceID

An entire statement can be omitted from the formatted results by configuring the DbStatement with RequireParameters=true. For example:

/SkipTrace/Update?SubjectFound=1/1/2012&Status=Found&ID=18

will result in:

UPDATE Process SET Status=@Status WHERE ProcessID IN (SELECT ProcessID FROM SkipTrace WHERE SkipTraceID IN (18))
UPDATE SkipTrace SET SubjectFound=@SubjectFound WHERE SkipTraceID IN (18)

However, if there are no columns specified for the SkipTrace table:

/SkipTrace/Update?Status=Found&ID=18

will result in:

UPDATE Process SET Status=@Status WHERE ProcessID IN (SELECT ProcessID FROM SkipTrace WHERE SkipTraceID IN (18))

Likewise, if there are no columns specified for the Process table:

/SkipTrace/Update?SubjectFound=1/1/2012&ID=18

will result in:

UPDATE SkipTrace SET SubjectFound=@SubjectFound WHERE SkipTraceID IN (18)

Statements support query output caching, particularly useful for dashboard or other "expensive" queries. Caching only applies to statements with a ReturnType of DataSet; caching is ignored for a DataReader or XmlReader. Strictly speaking, caching in this case refers to persisting a copy of a DataSet in cache on a web server, so that subsequent requests don't re-query the database for the information.

When a DataSet is saved to cached, it is stored with a dynamically created key loosely based on {ClassName}/{Operation}_{CacheKey}?{Parameters}. Parameters are rendered in whatever order they are passed on the query string, so the following URLs will result in different cache entries:

Valuation.ashx/Dashboard?Dimension=Status,CreatedDate&SqlFilters=Active
Valuation.ashx/Dashboard?SqlFilters=Active&Dimension=Status,CreatedDate

Thus, it is the developer's responsibility to ensure that different navigation links to the same data call the server in the same way, to maximize caching efficiency.

Statement properties that apply to caching are:

QBO's extranet security requirements mandate some unusual requirements for caching data output. Because the same basic query may return drastically different results based on a UserID, it is often inappropriate to return the same cached DataSet for all users. On the other hand, caching a DataSet for every user does not exactly lighten the load on the servers overall. Breaking down most practical requirements, there is typically a need to cache a DataSet for a set of users, where the set is defined by an Organization, SystemRole, or some other such group.

This is where the CacheKey settings is needed. If no CacheKey is defined, the DataSet will be cached for all users in the same manner. However, the CacheKey may contain a string with a group identity column (e.g OrganizationID, SystemRoleID, BrokerID, etc.). If such a pattern exists in the cache key, it will be replaced with the first such group the logged in user has access to. More precisely:

• AbstractObject/InvokeStatement method calls statement.GetCacheKey(parameters, user)
• GetCacheKey extracts any {Group} expressions from the CacheKey, and for each one, substitutes with user.GetGroupID(group)
• GetGroupID will return 0 if a user has universal access; otherwise it will return the first PersonAccess row where GroupObject = {group}

Thus, in a multi-tenant system, you might see the following in cache for a given DataSet:

• For universal access users: Valuation/Dashboard_Organization_0?Dimension=ClientID,CreatedDate
• For user belonging to Acme Investments (OrganizationID = 51): Loan/Dashboard_Organization_51?Dimension=ClientID,CreatedDate
• For user belonging to Zeta Vendor Outsourcing (OrganizationID = 981): Loan/Dashboard_Organization_982?Dimension=ClientID,CreatedDate

Frequently when searching or creating a dashboard for a table, there a client-driven or performance-driven requirements to filter out a subset of data. Examples include:

• When searching Foreclosures, one typically is worried about 'active' foreclosures
• When searching Loans, one typically is worried about 'active' loans
• For Smart Worklists, one often want to filter out 'completed' or 'skipped' items

The SqlPattern class supports the concept of Filters which can be used to simplify code maintenance from SQL statements up through the application stack to a web client.

All classes can leverage Abstract.config's 'Search' statement, which looks something like:

SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} {Extranet.Default} ORDER BY {OrderBy.Parameters}

We can narrow this result set by defining a filter called "Active" like this:

<Filter Name="Active" Clause="{Table}.UpdatedDate > '1/1/2001'"/>

and then modifying the Search statement to include:

SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} AND {Where.Active} {Extranet.Default} ORDER BY {OrderBy.Parameters}

From there, each class can use the standard Active filter by default, or can override the definition of Active. Foreclosure might look like this:

<Filter Name="Active" Clause="Process.DateClosed IS NULL"/>

This enables all classes to leverage the generic {ClassName}/Search method signature, while tweaking it's WHERE clause for class-specific purposes. Importantly, if we change the generic Search statement, all classes leveraging this will inherit this change, but retain their customized definition of Active.

Pre-defining {Where.Active} in the Search statement does not meet all use cases, however. Examples include:

• Show all Foreclosures opened in the last 90 days, even if they're currently closed (not "active")
• Show all "Started" Smart Worklist Members assigned to the QC Review team
• Show all Valuations that are not "cancelled" or "hidden"

In this case, one can simplify the parameters passed to a query by:

• using {Where.Filters} instead of hard-coding {Where.Active}
• passing a SqlFilters parameter containing the names of the filters you want applied

So, the generic Search statement is changed to:

SELECT ... FROM {From.ForeignKey} WHERE {Where.Basic} AND {Where.Filters} {Extranet.Default} ORDER BY {OrderBy.Parameters}

Taking the Smart Worklist Member class as an example, we can define the following filters:

<Filter Name="Active" Clause="SmartWorklist.CompletionDate IS NULL AND SmartWorklist.CancelledDate IS NULL"/>
<Filter Name="Pending" Clause="SmartWorklistMember.StartDate IS NULL AND ... AND SmartWorklistMember.SkipDate IS NULL"/>
<Filter Name="Started" Clause="SmartWorklistMember.StartDate IS NOT NULL AND ... AND SmartWorklistMember.SkipDate IS NULL"/>
<Filter Name="Skipped" Clause="SmartWorklistMember.SkipDate IS NOT NULL AND ... AND SmartWorklist.CancelledDate IS NULL"/>

At this point, one can invoke:

-- everything assigned to [email protected]
SmartWorklistMember/Search?AssignedPersonID=1 
-- everything part of an active worklist assigned to [email protected]
SmartWorklistMember/Search?AssignedPersonID=1&SqlFilters=Active 
-- everything part of an active worklist started by [email protected]
SmartWorklistMember/Search?AssignedPersonID=1&SqlFilters=Active,Started 

If you want to apply the Active filter by default, you may define the "default" SqlFilters used by a query with a Parameter:

<Statement Name="Search">
 <Parameters>
   <Parameter Name="SqlFilters" DbType="String" Value="Active"/>
 </Parameters>
</Statement>

This modifies the system behavior as follows:

-- everything part of an active worklist assigned to [email protected]
SmartWorklistMember/Search?AssignedPersonID=1 
-- same as above
SmartWorklistMember/Search?AssignedPersonID=1&SqlFilters=Active 
-- everything part of an active worklist started by [email protected]
SmartWorklistMember/Search?AssignedPersonID=1&SqlFilters=Active,Start 
-- ignore the default Parameter; return everything assigned to [email protected]
SmartWorklistMember/Search?AssignedPersonID=1&SqlFilters= 

If a Filter includes Parameters, and each of the specified parameters is present in the dictionary of parameters, a {Where.Basic} clause will automatically include a parameter. This is useful in situation where you wish to join to data is that is not part of the {From.ForeignKey} clause. For example:

<Filter Name="InDecisionID" Clause="{Table}.{Table}ID IN (SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}')" Parameters="InDecisionID" />

With such a filter, the following URL will limit results to ImportForm records that are part of workflow with ID 2401:

ImportForm/Search?InDecisonID=2401

will result in a WHERE clause that includes:

WHERE ... AND ImportForm.ImportFormID IN (
  SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}'
)

From a performance perspective, this is preferable to:

WHERE ...
  AND (@InDecisionID IS NULL OR ImportForm.ImportFormID IN (
    SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}'
  ))

The presence of the ImportForm.ImportFormID clause may contribute to parameter sniffing, causing the SQL optimizer to choose an execution plan that works well with the presents of an @InDecisionID parameter, but poorly without one. By including this clause dynamically with a filter, a different optimization plan is guaranteed for the query with and without the @InDecisionID parameter.

When building complex WHERE clauses, it is preferable to build statements with {Where.Basic} and several parameter-based filters, versus a single complex statement that handles the same parameters that may be NULL.

AbstractObject.config contains some standard, useful filters.

<!-- Limit results matching a linked record -->
<Filter Name="LinkedFrom" Clause="{Table}.{Table}ID IN (SELECT LinkedObjectID FROM ObjectLink WHERE ObjectLink = @LinkedFrom)" Parameters="LinkedFrom" />
<Filter Name="LinkedTo" Clause="{Table}.{Table}ID IN (SELECT PrimaryObjectID FROM ObjectLink WHERE ObjectLink = @LinkedTo)" Parameters="LinkedTo" />
 
<!-- Limit results matching items in a workflow -->
<Filter Name="InDecisionID" Clause="{Table}.{Table}ID IN (SELECT ObjectID FROM DecisionStep WHERE DecisionID = @InDecisionID AND Object = '{Table}')" Parameters="InDecisionID" />

Generating cubes and pivot tables in QBO is a snap. Examples are available in all QBO systems from:

• Workflow home page (/Decision/Decision.ashx/Home)
• Worklist home page (/Decision/SmartWorklist.ashx/Home)

The standard Dashboard statement handles a huge amount of work for you. It will handle N number of dimensions, and return the results as a SQL CUBE. You can simply invoke the following:

{ClassName}/Dashboard?Dimension={Dimension1},{Dimension2},...,{DimensionN}&{Other Parameters}

Decision.ashx/Dashboard?Dimension=DecisionTemplateID&Output=Xml

For those that find a SQL CUBE confusing, consider the Pivot method, which transforms a 2-dimensional cube into, well, a 'normal' table.

When specifying date-based dimensions (such as CreatedDate), the Dashboard statement will accept three 'extra' parameters per date dimension:

• {Dimension}Start: (optional, defaults to NULL) specifies the earlier date to consider
• {Dimension}End: (optional, defaults to DateTime.Now) specifies the latest date to consider
• {Dimension}Intervals: (optional, defaults to the application setting TimeDimensionIntervalDefault, 50 by default): specified the maximum number of 'ticks' to render

The Dashboard statement will pass these parameters to dbo.TimeDimensionInterval(start, end, ticks) to produce a table containing a row of each date range. Examples:

SELECT * FROM dbo.TimeDimensionInterval('1/1/2014', '2/1/2014', 50) 
-- Less than 50 days, so days are returned

TimeSpan BeginDate EndDate
2014-01-01 2014-01-01 00:00:00.000 2014-01-02 00:00:00.000
2014-01-02 2014-01-02 00:00:00.000 2014-01-03 00:00:00.000
2014-01-03 2014-01-03 00:00:00.000 2014-01-04 00:00:00.000
2014-01-04 2014-01-04 00:00:00.000 2014-01-05 00:00:00.000
2014-01-05 2014-01-05 00:00:00.000 2014-01-06 00:00:00.000
...Removed rows for brevity...
2014-01-29 2014-01-29 00:00:00.000 2014-01-30 00:00:00.000
2014-01-30 2014-01-30 00:00:00.000 2014-01-31 00:00:00.000
2014-01-31 2014-01-31 00:00:00.000 2014-02-01 00:00:00.000

SELECT * FROM dbo.TimeDimensionInterval('1/1/2014', '4/1/2014', 50)
-- More than 50 days, less than 50 weeks, so weeks are returned
TimeSpan BeginDate EndDate
2014-01-01 2014-01-01 00:00:00.000 2014-01-08 00:00:00.000
2014-01-08 2014-01-08 00:00:00.000 2014-01-15 00:00:00.000
2014-01-15 2014-01-15 00:00:00.000 2014-01-22 00:00:00.000
2014-01-22 2014-01-22 00:00:00.000 2014-01-29 00:00:00.000
2014-01-29 2014-01-29 00:00:00.000 2014-02-05 00:00:00.000
2014-02-05 2014-02-05 00:00:00.000 2014-02-12 00:00:00.000
...Removed rows for brevity...
2014-03-19 2014-03-19 00:00:00.000 2014-03-26 00:00:00.000
2014-03-26 2014-03-26 00:00:00.000 2014-04-02 00:00:00.000

SELECT * FROM dbo.TimeDimensionInterval('1/1/2012', '1/1/2014', 50)
-- More than 50 weeks, less than 50 months, so months are returned 
TimeSpan BeginDate EndDate
2012-01 2012-01-01 00:00:00.000 2012-02-01 00:00:00.000
2012-02 2012-02-01 00:00:00.000 2012-03-01 00:00:00.000
2012-03 2012-03-01 00:00:00.000 2012-04-01 00:00:00.000
...Removed rows for brevity...
2013-11 2013-11-01 00:00:00.000 2013-12-01 00:00:00.000
2013-12 2013-12-01 00:00:00.000 2014-01-01 00:00:00.000

SELECT * FROM dbo.TimeDimensionInterval('1/1/2000', '1/1/2014', 50)
-- More than 50 months, so years are returned
TimeSpan BeginDate EndDate
2000 2000-01-01 00:00:00.000 2001-01-01 00:00:00.000
2001 2001-01-01 00:00:00.000 2002-01-01 00:00:00.000
2002 2002-01-01 00:00:00.000 2003-01-01 00:00:00.000
...Removed rows for brevity...
2012 2012-01-01 00:00:00.000 2013-01-01 00:00:00.000
2013 2013-01-01 00:00:00.000 2014-01-01 00:00:00.000

This function will compare the difference between the start and end dates to maximum ticks requested and return a row for each hour, four-hour block, day, week, month or year.

If you don't care much about ticks, and just want Days, Weeks, Months or Years, you can set the {Dimension}Intervals=Hours|Days|Weeks|Months|Years.

The SmartSearch method enables Google-like searching across records based on search terms. Each module can have it's own set of SearchTerms defined. For example:

• Loan: users may enter text including loan number (Loan.Loan), address (Property.Address), borrower name (Borrower.Borrower), or social security number last 4 (Borrower.USSSN)
• Person: users may enter text include a username (Person.Person) or a last name (Person.LastName)
• Contact: users may enter a name (Contact.Contact), address (Contact.Address), phone number (ContactMethod.ContactValue), or email address (ContactMethod.ContactValue)
• etc.

Assume loan #987654321 includes a John Smith living at 123 Main Street. Calling:

Loan/SmartSearch?SmartSearch=Smith 123 Main Street

• The following search terms will be searched against the Loan, Address, and Borrower tables:
  • Smith (match Borrower.Borrower)
  • 123 (match Loan.LoanID, Loan.Loan)
  • Main
  • Street
  • 123 Main (will match Property.Address)
  • Main Street
  • 123 Main Street (will match Property.Address)
  • ...
• Loans with a borrower named Smith will be included in the results
• Loans with an address starting with 123 Main will be included in the results
• Loans with a LoanID of 123 will be included in the results
• Loans matching Smith and 123 Main Street will show up at the top (because they match more terms than other loans)

You can configure customized search terms. For example, assume we wanted to allow searching by an investor loan number (Loan.InvestorLoanID). From Design > Configuration > Modules > Loan, click on the Search Terms tab and create a new term like this:

SELECT 
 Loan.LoanID, -- This is the ID of a matching Loan row
 Term.Row, -- This tells us which term was matched
 9, -- This is a power-user defined 'weight' of the term
 'Loan', -- This is the table that holds the matching term
 LoanID, -- This is the ID of the row of the table that holds the matching term
 'InvestorLoanID', -- This is the column that holds the matching term
 Loan.InvestorLoanID -- This is the matching term
FROM @Term AS Term -- @Term is build automatically by QBO from the words the user entered
 INNER JOIN Loan  -- Join to whatever you want to here
 ON Loan.InvestorLoanID LIKE Term.HintValue + '%' -- Assume a % for this use case
WHERE 
 Term.Words = 1 -- For InvestorLoanID, we don't need to try to match terms that have spaces
 AND LEN(Term.Term) > 5 -- Let's not try to match '123' from an address - too many matches! 

Let's assume we wish to allow a user to search for a loan by a title committment number (Title.CommittmentNumber):

SELECT 
 Loan.LoanID, 
 Term.Row, 
 7, -- Less important than an InvestorLoanID when calculating weight
 'Title', -- This is the table that holds the matching term
 TitleID, -- This is the ID of the row of the table that holds the matching term
 'CommittmentNumber',  -- This is the column that holds the matching term
 Title.CommittmentNumber  -- This is the matching term
FROM @Term AS Term 
 INNER JOIN Title
 ON Title.CommittmentNumber = Term.HintValue -- Exact match in this case
 AND Title.Object = 'Loan'
 INNER JOIN Loan
 ON Loan.LoanID = Title.ObjectID
WHERE 
 Term.Words = 1 -- For CommittmentNumber, we don't need to try to match terms that have spaces

Now our title users are very happy; they can search by loan number or committment number. However, our non-title users are now annoyed, because the committment numbers happen to look a lot like loan numbers. For example, you may have a Loan # 987654321 and a Title # 987654321. Both will show up in the search results, though the InvestorLoanID will be weighted higher than the Title. One can require a user to provide a hint in order to trigger a search term by changing the WHERE clause a bit:

WHERE 
 Term.Words = 1 -- For CommittmentNumber, we don't need to try to match terms that have spaces
 AND ISNULL(Term.HintTerm, 'Other') = 'Title'

For a user to search for loans by committment number, they must type in 'title:987654321'.

Method listeners are called at the end of executing native operations. Method listeners should be used when real-time or near-real time performance is needed.

For example:

• Email a user each time their password has been changed
• Post a payment to a servicing system each time a payment is made

If you do not need immediate performance, consider scheduling an ImportFile/Apply job.

QBO module includes two standard classes that implement the IMethodListener interface:

• OperationListener: executes some method signature synchronously
• QueueListener: queues some method signature for asynchronous execution

For security reasons, it may be useful to email a user each time their password is changed, just in case they did not initiate the password change. To configure this use case:

• Create a MessageTemplate called 'Password Change Email'
• From Design > Configuration > Modules > Person > Method Listeners, create a new Listener called PasswordChange:
  • Name: Password Change Email
  • Type: qbo.Application.Listeners.QueueListener, qbo.Application
  • Methods: PasswordReset
  • MethodSignature:

Message/Send?Template=Password Change Email&Object=Person&ObjectID={PersonID}&ToAddress={Person}

When an import is successfully complete, we may wish to send an email an administrator. To configure this use case:

• From Design > Configuration > Modules > ImportFile > Method Listeners, create a new Listener called EmailOnSuccess:
  • Name: Email On Success
  • Type: qbo.Application.Listeners.QueueListener, qbo.Application
  • Method: Save
  • Parameters: Status=Complete
  • MethodSignature:

Message/[email protected]&Subject=Import {ImportFile} Completed on {UpdatedDate}&BodyText=All

Done!

This may look nearly identical to the Password Change Emails use case above, with a notable difference: use of the Parameters property of a method listener. When specified, the Parameters are used to restrict when a MethodListener is fired. Specifically, each parameter required by a method listener is compared to the parameters passed to the Method (in this case: Save):

• If a Listener.Parameter is specified and not present in the parameters passed to the Method, the listener is not fired
• If a Listener.Parameter is specified and matches the parameters passed to the Method, the listener is fired
• If a Listener.Parameter is specified as *, and it is present in the parameters passed to the Method, the listener is fired

Thus, in the example above:

• calling ImportFile/Save?ID=X&Status=Awaiting Import would not fire the listener, since Status != "Complete"
• calling ImportFile/Delete?ID=X&Status=Complete would not fire the listener, since Method != "Save"
• calling ImportFile/Save?ID=X&Status=Complete would fire the listener

All QBO classes derived from AbstractObject can be extended to invoke third party components or plugins via the IService interface. When calling AbstractObject.Invoke* methods, AbstractObject will check the configuration file for Service entries matching the operation. If one is found, a corresponding IService plugin is instantiated, and invoked with the method's parameters.

Use cases include:

• Invoke an IService from the front end (e.g. Options > Order Credit Report)
• Invoke an IService from a workflow
• Invoke an IService from a batch import

Please see our IService walkthough for more details.

Valuations systems can order appraisals from LPS via QDS as follows:

Valuation/LPSAppraisal?ID={ValuationID}

An appraisal order is not an instantaneous product; it will typically be a long running transaction involving steps like:

• receiving a confirmation message from LPS
• exchanging messages with LPS as the order progresses
• receiving a final product, or
• cancelling the order

From the perspective of the third party, they should be able to submit data to QBO in an easy to understand RESTful manner, like this:

{some url}/Confirmation?{some parameters}
{some url}/Message?{some parameters}
{some url}/Product?{some parameters}

Though a series of pipes, levers, and valves, setting an IService configuration entry to use the 'qbo.Service.QDS.QDSExchange' class will result in:

• creation of a ServiceRequest record as the third party is being invoked,
• enabling postback to ServiceRequest.ashx/{Step} from the third party (QDS, in this case)
• leveraging Import File Templates to handle importing the data received

The Valuation configuration section contains a Service entry along these lines:

<Service Name="LPSAppraisal"
    Type="qbo.Service.QDS.QDSExchange, qbo.Service.QDS"
    ReturnType="Object"
    RequestTransform="Templates/Mortgage/Valuation/Services/LPSAppraisal.xslt"
    RequestMethod="Valuation/Select?ID={ID}"
    ImportFileTemplate="QDS.LPS.OrderRequest.Response">
    <Steps>
        <Step Name="Confirmation" Type="qbo.Service.QDS.QDSExchange, qbo.Service.QDS"
            ImportFileTemplate="QDS.LPS.Confirmation"
            ResponseMethod="ImportFile/Response?ID={ImportFileID}"/>
        <Step Name="Message" Type="qbo.Service.QDS.QDSExchange, qbo.Service.QDS"
            ImportFileTemplate="QDS.LPS.Message"
            ResponseMethod="ImportFile/Response?ID={ImportFileID}"/>
        <Step Name="Product" Type="qbo.Service.QDS.QDSExchange, qbo.Service.QDS"
            ImportFileTemplate="QDS.LPS.Product"
            CompleteStep="true"
            ResponseMethod="ImportFile/Response?ID={ImportFileID}"/>
    </Steps>
</Service>

LCI provides a web service for conducting bankruptcy searches and retrieving bankruptcy docket information. For this integration, we implemented two classes:

• LCIReadObject: invoke the LCI bankruptcy search web service, and returns their native XML
• LCIObject: invoke the LCI bankruptcy search web service, and routes the results through the QBO import framework via an ImportFileTemplate

To support these classes, we created the following:

• Setup.LciBankruptcySearch.xml: setup package including Contact IService entries for the two classes noted above, credentials, and the import file template mapping LCI data structures to QBO data structures

Using the LCI IService plugin:

-- Calling LCI directly from a QBO website with an ad-hoc user
Contact/LCIBKSearchRead?FirstName=John&LastName=Doe&USSSN=123456789&Address=123 Main Street&City=Anywhere&State=Ca&PostalCode=90210&Output=Xml

-- Calling LCI directly from a QBO website with an existing QBO contact
Contact/LCIBKSearchRead?ID={ContactID}&Output=Xml

-- Calling LCI directly, but wrap it in a custom QBO UI
Theme.ashx/Wrap?ClassName=Contact&Method=LCIBKSearchRead&ID={ContactID}&Transform=Templates/Service/Bankruptcy/Bankruptcy.LCISelect.xslt

-- Calling LCI directly, transforming the results into QBO import-framework-ready XML
Contact/LCIBKSearchRead?ID={ContactID}&Output=Xml&Transform=Templates/Service/Bankruptcy/LCI.BankruptcySearch.XmlResponse.Import.xslt

-- Okay those are some decent examples. Now let's really USE it.
-- Search an actual borrower, and persist the Bankruptcy record to QBO
Contact/LCIBKSearch?ID={ContactID}&ParentObject=Loan&ParentObjectID={LoanID}

-- Do the same thing, but don't make the user wait around for results
/Contact/Contact.ashx/Queue/LCIBKSearch?ID={ContactID}&ParentObject=Loan&ParentObjectID={LoanID}

-- Let's schedule this search every month
/Contact/Contact.ashx/Schedule/LCIBKSearch?ID={ContactID}&ParentObject=Loan&ParentObjectID={LoanID}&Schedule=Monthly on the 1st at 3:00am

-- Let's schedule a monthly sweep of all our contacts that don't have an existing BK
Contact/Contact.ashx/Schedule/BatchQueue?Filter=NoActiveBK&Operation=LCIBKSearch&Schedule=Monthly on the 1st at 3:00amasdf

There are many uses cases where QBO needs to invoke an operation that may take minutes, hours or days to complete. Examples include:

• ImportFile.Import: batch imports may process rows of data one-by-one, completing the import process many minutes after Import is called
• Contact.SCRA: a military search may take several minutes before QDS posts search results back to the requesting system
• Valuation.LPSAppraisal: an appraisal order via a third party will typically take days before being completed

In such situations, it would be useful for a workflow step (or any other code) to register a callback to know when the long-running operation is complete.

The AbstractObject class includes three methods relevant to async callbacks:

• BeginInvoke(operation, parameters, callback)
• EndInvoke(operation, parameters)
• EndInvoke(operation, parameters, callback)

BeginInvoke works just like Invoke (in fact, it calls Invoke), but it will push a callback to a queue. More precisely, it will insert a row matching the callback into the ObjectQueue table, without any QueuedDate. At the end of BeginInvoke, there is a database record waiting to be processed explicitly by some method.

EndInvoke is used to pop a callback from a queue. More precisely, it will update the corresponding ObjectQueue record's QueuedDate to DateTime.Now, allowing the Queue Processor to process the row.

A key considering is how AbstractObject identifies the 'corresponding' ObjectQueue record. When pushing a callback, three key fields are determined:

• ObjectQueue: {ClassName}/{Operation}?ID={ID}
• Object: {ClassName}
• ObjectID: {ID}

The EndInvoke method will search the ObjectQueue table for matching records, and process any found.

Examples:

• ImportFile/Import?ID=106
• Contact/SCRA?ID=7842
• Valuation/LPSAppraisal?ID=5524

Normally, async code should simply call EndInvoke(operation, parameters). Advanced use cases may have classes proxy completion of async calls (e.g. QDS hooks), which require using the EndInvoke(operation, parameters, callback) override.

To invoke an async method, you should call BeginInvoke(operation, parameter, callback), where the callback is a MethodSignature. For example, the DecisionStep class will invoke any async operations with a callback to DecisionStep.Complete, as follows:

if (item.Configuration.IsAsync(DecisionStepTemplate.Operation))
{
  item.BeginInvoke(DecisionStepTemplate.Operation, parameters, new MethodSignature(string.Format("DecisionStep/Complete?ID={0}", ID)));
  ...
  Status = "Pending";
}
else
{
  item.Invoke(DecisionStepTemplate.Operation, parameters);
  ...
  Complete();
}

Any QBO3 generic method you implement that may be a long-running transaction can be wired to handle async callbacks as follows:

• Add Async=true to the method's DbMethodAttribute
• Create a corresponding 'end' method
• Invoke the corresponding 'end' method by calling EndInvoke(endMethod, properties)

The ImportFile/Import method uses an ImportFileQueueEngine plugin to queue ImportFileQueue records for processing. When all ImportFileQueue records have been processed, the Import method can be considered to be complete.

[DbVoidMethod(Async=true)]
public void Import(IDictionary<string, object> parameters)
{
  ...
  ImportStart = DateTime.Now;
  ...
  bool complete = ImportEngine.Import(parameters);
  if (complete)
    EndInvoke("EndImport", string.Format("ID={0}", ID).ToProperties());
  else
  {
    Status = "Pending";
    Save();
  }
}

// Called by ImportFileQueue whenever a row is done processing
public bool CompletionCheck()
{
  using (IDataReader reader = InvokeDataReader("QueueCompletionCheck", new Dictionary<string, object>() { { "ID", ID } }))
  {
    while (reader.Read())
    {
      SetProperties(reader);
      if (ImportComplete.HasValue)
      {
        EndInvoke("EndImport", string.Format("ID={0}", ID).ToProperties());
        return true;
      }
    }
  }
  return false;
}

// Called by AbstractObject's EndInvoke
[DbVoidMethod]
public void EndImport(IDictionary<string, object> parameters)
{
  foreach (Int64 id in IdList(parameters))
  {
    ImportFileObject import = new ImportFileObject(User);
    import.Select(id);
    import.EndImport();
  }
}

// Called by the late-bound EndImport above
public AbstractObject EndImport()
{
  Status = "Complete";
  if (!ImportComplete.HasValue) {
    ImportComplete = DateTime.Now;
    Save();
  }
  return this;
}

Notes:

• the Import method's DbMethodAttribute (DbVoidMethod) is marked as Async.
• Instead of calling the EndImport method directly, EndInvoke("EndImport", parameters) is called instead
• EndImport implements a DbMethodAttribute (DbVoidMethod), but does not need to be marked as async (when we call EndImport, we're done)
• AbstractObject.BeginInvoke and EndInvoke are called

Decision processing is a perfect example of the need for long-running operations. To process a batch of Decisions correctly with ImportFileQueue, an async method must be called. Decision/Process is a poor candidate for this use case, because it is already designed for a multi-threaded operation against multiple Decision IDs. Thus, we introduce a new ProcessAsync method, with a matching EndProcessAsync.

Following the execution trail, we have:

• ImportFileQueue.Process
• calls Decision.BeginInvoke("ProcessAsync", {ID=X}, "ImportFileQueue/EndProcess?ID=Y")
• Decision.BeginInvoke will
  • create an ObjectQueue record where:
    • ClassName = ImportFileQueue
    • Operation = EndProcess
    • Attributes = {ID=Y)
    • ObjectQueue = Decision/ProcessAsync?ID=X
    • Object=Decision
    • ObjectID=X
  • call Decision/ProcessAsync

Later, on another thread (perhaps months later!) Decision/Process will evaluate DecisionSteps, and eventually call:

• Decision/Complete
• calls Decision.EndInvoke("EndProcessAsync", {ID=X})
  • invoke Decision/EndProcessAsync
  • pops any ObjectQueue records matching:
    • Object=Decision
    • ObjectID=X
    • ObjectQueue = Decision/ProcessAsync?ID=X
  • finds ImportFileQueue/EndProcess?ID=Y (based on matching Object/ObjectID/ObjectQueue to Decision/X/Decision.ProcessAsync?ID=X)
    • processes the ObjectQueue record (releases it into the regular queues)
• ImportFileQueue/EndProcess?ID=Y is now eligible to be processed by regular queues

Developers may create custom classes based on the QBO framework. Such classes are typically part of a client-specific module: group of classes that share related functionality within a single namespace. Module examples include:

• qbo.Attachment: handle document imaging and document generation
• qbo.Contact: handles contact and address management, including geo-coding and mapping
• qbo.Message: handles message tracking, including email and SMS

Each module within QBO 3 comprises:

• an application-tier C# Class Library,
• a web-tier ASP.NET Web Application,
• one or more configuration entries in web.config

Class libraries comprise at least two partial class files:

• {Table}.cs: contains constructors, custom methods, and non-database fields.
• {Table}.Fields.cs: contains properties mapping to database fields.

Class libraries can be generated from:

-- Base class
/Application/Database.ashx/Class?Table={Table}&Namespace={Namespace}
-- Fields: 
/Application/Database.ashx/ClassFields?Table={Table}&Namespace={Namespace}
-- ASP.NET HttpHandlers (.ashx file) 
/Application/Database.ashx/HttpHandler?Table={Table}&Namespace={Namespace}

For each AbstractObject-derived class, there will be corresponding configuration entries:

• a section entry in web.config:
  • e.g. <section name="Message" type="qbo.Application.Configuration.ObjectConfiguration, qbo.Application"/>
• a configuration entry in web.config under the qbo node:
  • e.g. <Mesasge configSource="config\Message.config"/>
• a configuration file in the Config folder

The configuration file follows this pattern:

<?xml version="1.0"?>
<{Table} Table="{Table}" Type="qbo.{Namespace}.{Table}Object, qbo.{Namespace}" BaseTransform="Templates/{Namespace}/">
 <Statements>
 </Statements>
</{Table}>

At least one {Table}.Home.xslt must be created per class, if the class is to have a standard GUI. The home layout acts as a "dashboard" for the class, and typically contains one or more 'panels' of data.

Javascript class libraries are used to communication with the server using AJAX (REST). One javascript file per namespace should be created to contain all of the javascript class libraries. For example:

• Attachment.js: contains qbo3.AttachmentObject, qbo3.AttachmentTemplateObject, qbo3.FileObject, etc.
• Message.js: contains qbo3.MessageObject, qbo3.MessageTemplateObject, qbo3.MessageRecipientObject, etc.
• {Namespace}.js: contains a class for each matching HttpHandler endpoint in the ASP.NET Web Application for the namespace

Each javascript class library should extend the qbo3.AbstractObject class, overriding the options.url to point to the appropriate HttpHandler.

// Attachment class.
qbo3.AttachmentObject = new Class({
  Extends: qbo3.AbstractObject,
  options: {
    url: 'Attachment/Attachment.ashx/'
  }
});

// Message class.
qbo3.MessageObject = new Class({
  Extends: qbo3.AbstractObject,
  options: {
    url: 'Message/Message.ashx/'
  }
});

Once the javascript file has been created, the Application/Config/Javascript.config file should be modified to include the new javascript:

<?xml version="1.0"?>
<Javascript UseMinified="false">
  <Scripts>
  <Script Name="mootools.js" Path="mootools.js" Minified="mootools.min.js"/>
  <Script Name="qbo3.js" Path="qbo3.js"/>
  ...
  <Script Name="Attachment.js" Path="Attachment.js"/>
  <Script Name="Message.js" Path="Message.js"/>
  ...
  <Script Name="{Namespace}.js" Path="{Namespace}.js"/>
  ...
  </Scripts>
</Javascript>

QBO will automatically detect C# extension methods that extend AbstractObject. For example to extend the OrganizationObject class to invoke a third-party API along these lines:

using qbo.Contact;

private static string _apiUrl = "https://www.alphavantage.co/query?apikey={0}&function={1}&symbol={2}";
private static NetworkCredential _credential = ApplicationStartup.Instance.Services.GetRequiredService<CredentialCache>()?.GetCredential(new Uri(ApiUrl), "Basic");

[DbDataReaderMethod]
public static async Task<JsonReader> StockQuoteAsync(this OrganizationObject company, IDictionary<string, object> parameters)
{
	if (_credential == null)
		throw new ArgumentNullException(nameof(_credential), "No credentials have been set up for www.alphavantage.co");
	var symbol = parameters.GetEntry<string>("Symbol");
	if (symbol == null)
		throw new ArgumentNullException(nameof(symbol), "You must pass a Symbol parameter, such as Symbol=IBM");

	var request = WebRequest.Create($""https://www.alphavantage.co/query?apikey={_credential?.Password}&function=GLOBAL_QUOTE&symbol={symbol}"));
	using (var response = await request.GetResponseAsync())
	using (var stream = response.GetResponseStream())
	using (var reader = new StreamReader(stream))
	{
		return new JsonTextReader(reader);
	}
}

Once deployed, user may invoke:

api/organization/stockquote?Symbol=MSFT

Notes:

• Any AbstractObject-derived class may be extended, including AbstractObject or GenericObject.
• ApplicationStartup.Instance.Services is a .netcore IServiceProvider. CredentialCache is automatically registered for you.
  • custom services may be registered via qbo4.Configuration.ServiceCollectionExtensionMethod attributes.
• The extension method signature must have 2 parameters: an AbstractObject-derived class, and a dictionary of parameters:

public static async Task<T> {Method}Async(this {AbstractObject}, IDictionary<string, object> parameters)
{ ... }

• The method must be marked with a qbo3 DbMethodAttribute, which must match the Task<T> return type as follows:

QBO's core framework includes the following interfaces: