AbstractObject

Background

AbstractObject serves as the base class for all table-driven QBO3 classes. It implements the following key concepts:

• • Standard methods such as Save, Select, Summary, Insert, Update, Delete, Import, Export and more

  • Generic invocation methods that use configuration and reflection to late-bind URL calls to any method, statement or service

  • Queuing methods enable asynchronous calls via the IQueue interface

  • Static methods to instantiate via a factory pattern

  • Serialization methods to support output as XML and JSON

Each AbstractObject-derived class has a configuration file associated with it, controlled by qbo.Application.Configuration.ObjectConfiguration.cs. These configuration files may contain the following sections:

• • Statements: SQL statements associated with the class

  • Filters: SQL snippets that can be reused by Statements that leverage {Where.Filters} or {Where.Basic}

  • SearchTerms: SQL snippets that are injected into {SmartSearch} to provide intuitive searching from a string of text

  • CustomDimensions: SQL snippets that act as custom dimensions for Statements using {*.Dashboard} 

  • ChildClasses: configuration-driven list of child classes

  • Services: collection of plugins that extend a class using the IService interface

Status and Types

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 table include a column called 'FinalState', enabling 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.

Saving Data

Saving data for a single row in a table is simple.  Assume we're dealing with our Contact table, and wish to change the Address and Postal Code for ContactID 12:

Contact.ashx/Save?ID=12&Address=123 New Address Street&PostalCode=90210

With the same Save method, you can also save data in related tables.  Assume the Contact's parent is a Bankruptcy, and we wish to update the Bankruptcy.Status column to be 'Debtor Deceased'.  By using a {Parent}_{Column} pattern, you can update the parent table's data in the same call:

Contact.ashx/Save?ID=12&Address=123 New Address Street&PostalCode=90210&Bankruptcy_Status=Debtor Deceased

Child records may also be updated using a {Child}_{Index}_{Column} pattern:

Contact.ashx/Save?ID=12&Address=123 New Address Street&Messages_0_Subject=Mailing address updated based on email from borrower.

For child records that are templated, you may use the template name in lieu of an index.  Assume we have created a Message Template called "Current Address":

Contact.ashx/Save?ID=12&Address=123 New Address Street&Messages_CurrentAddress_Subject=Mailing address updated based on email from borower.

State Transitions

For clients with advanced requirements surrounding Status, QBO supports a 'state diagram' by creating rows in the ObjectState table. This allows power users to define allowed 'transitions', and this is enforced in the data tier by triggers.  For example, the Loan table may allow:

• • Current > Collections

  • Collections > Foreclosure

  • Foreclosure > REO

  • Collections > Short Sale

  • Collections > Loan Modification

  • Short Sale > Liquidated

  • etc.

By defining these transitions in the ObjectState table, if an attempt is made to change a Loan.Status from 'Current' to 'REO', the database will throw an error, ensuing that you cannot 'skip' a loan from an 'early' status like Current to an end status like 'REO'

Furthermore, if a state transition is marked as a Final State, the status may not be updated once set. This prevents 'inactive' records from being made active again.

If there are no ObjectState rows for a particular table, no status enforcement is done by the data tier transition trigger.

Standard Methods

AutoBind

Typically, an ID property is used to uniquely identify a record in QBO. For example, if you wish to modify a Process where ProcessID = 17, you can call:

Process/Save?ID=17&Status=In Progress

By default, we assume that no compound keys constitute a unique key.

However, there are use cases where another field, or set of other fields, constitute a compound key sufficient to uniquely identify a record. In these cases, an AutoBind statement may be configured that will be invoked when setting properties to identify an ID.

For a Bankruptcy, the combination of a Court and CaseNumber is sufficiently to uniquely identify a Bankruptcy case. Thus, given a BankruptcyID = 22, CourtID = 1, and BKCaseNumber = 12345:

Bankruptcy/Save?CourtID=1&BKCaseNumber=12345

In this case, the AutoBind statement would be:

SELECT BankruptcyID FROM Bankruptcy WHERE CourtID = @CourtID AND BKCaseNumber = @BKCaseNumber

For QBO mortgage installations, a question that frequently arises is whether or not a loan number is unique. Most small banks immediately claim that their loan numbers are unique. However, QBO does not assume this out of the box (we've worked with too many banks that purchased another portfolio, at which point the unique key becomes Portfolio + Loan Number). 

In this case, the AutoBind statement would be:

SELECT LoanID FROM Loan WHERE Loan = @Loan

For a slight variation on this theme, the AgencyID and AgencyLoanID fields are a compound key (Agency represents the GSE: Freddie Mac or Fannie Mae).  AutoBind for this example would be:

SELECT LoanID FROM Loan WHERE AgencyID = @AgencyID AND AgencyLoanID = @AgencyLoanID

or a slightly more user-friendly version:

SELECT LoanID, Agency.OrganizationID AS AgencyID

FROM Organization AS Agency

  INNER JOIN Loan ON Loan.AgencyLoanID = @AgencyLoanID AND Loan.AgencyID = Agency.OrganizationID

WHERE Agency.Organization = @Agency

For both examples, an AutoBind statement may be created.

Pivot

The Pivot method will take the SQL CUBE output, and create a pivot table around any row/column combination desired. This is useful when the number of columns is not known when designing a query. Examples:

// This will render Decision/Dashboard with CreatedDate as rows, DecisionTemplate as columns, and DecisionCount as the fact values for each 'cell'.

Decision.ashx/Pivot?Dimension=CreatedDate,DecisionTemplate&CreatedDateStart=1/1/2014

// This will render Decision/MyCustomCube?MyParam=A&MyOtherParam=B with DecisionTemplate as rows, Status as columns, DecisionCount,MyCustomCal as fact values for each 'cell'.

Decision.ashx/Pivot?From=MyCustomCube&Row=DecisionTemplate&Column=Status&Facts=DecisionCount,MyCustomCalc&MyParam=A&MyOtherParam=B

Parameters to the Pivot statement include:

• • From: (optional, defaults to Dashboard) the statement to execute that generates a SQL CUBE (WITH CUBE)

  • Dimension: (optional) passed to the {From} statement, specifying Dimensions to use

  • Row: (optional, defaults to first Dimension) the data column to use as row labels

  • Column: (optional, defaults to second Dimension) the data column to use as column labels

  • TopX: (optional, defaults to 0 / all) the maximum number of X rows to render; all others will be lumped into an 'Other' label

  • TopY: (optional, defaults to 0 / all) the maximum number of Y rows to render; all others will be lumped into an 'Other' label

  • Facts: (optional, defaults to {Table}Count) the facts to render in each table 'cell'

  • (other paramers): all other parameters will be passed to the underlying {From} statement

To render pivot results in a 'generic' HTML table, you can use the Standard.xslt transform:

Process/Process.ashx/Pivot?Dimension=AssignedPersonID,ProcessTemplateID

&CreatedDateStart=1/1/2013

&Column=ProcessTemplate

&Row=AssignedPerson

&Transform=Templates/Report/Standard.xslt

This outputs the Pivot results in an HTML table, but without any CSS or Javascript applied. To render a pivot table in a 'nice' UI, you can use the Theme.ashx/Wrap endpoint:

Theme.ashx/Wrap?ClassName=Process

&Method=Pivot

&Dimension=AssignedPersonID,ProcessTemplateID

&CreatedDateStart=1/1/2013

&Column=ProcessTemplate

&Row=AssignedPerson

&Transform=Templates/Report/Standard.xslt

(Note the carriage returns are added for readability; they should not be used in a real URL.)

Evaluate

The AbstractObject/Evaluate method attempts to create a child object based on the output of a Matrix. Use cases include:

• • Choosing a workflow to launch

  • Choosing a task to create

  • Choosing a document to generate

The Matrix may access any data point associated with the object being evaluated. For example, to launch a workflow based on the characteristics of a Loan, one could:

• • Create a Matrix with input columns including State, Investor, and UPBRange, and an output column called DecisionTemplate

  • Call Loan/Evaluate?ID=123&Matrix=MyMatrix&TemplateClass=DecisionTemplate will pick the top DecisionTemplate returned by the Matrix, given the Loan inputs.