Document API - quandis/qbo3-Documentation GitHub Wiki

The Document module is a management suite that includes:

• Creation: create new documents based on mail merge templates (Word, PDF, Crystal Reports, HTML, etc.).
• Imaging: store documents in one or more file system(s).
• Transfer: move documents between file systems, FTP sites, Amazon S3, Azure, etc.
• Zipping: zip, unzip, password protect documents.
• OCR: make those nasty faxes searchable
• Text extraction: do your documents contain what you think they contain?
• Classification: automate recognizing document types (based on OCR and text extraction)

Templates (AttachmentTemplate) combine:

• a method signature (AttachmentTemplate.Method) to get data,
• an IGenerator plugin (AttachmentTemplate.AttachmentService) to perform a mail merge,
• a location (IFileObject) and filename to save the resulting stream to

Power users may create an unlimited number of template, which may be invoked as follows:

POST /Attachment/Attachment.ashx/Generate?Template=Welcome Letter&Object=Contact&ObjectID=1
Response: {a stream containing the document content}

QBO includes a standard set of IGenerator plugins to facilitate the mail merge, including:

• Aspose.Words: create MS Word templates, and merge to Word, PDF, or RTF.
• ABCPDF: create HTML templates, or manipulate existing PDF files, creating new PDFs.
• Delimited: create delimited files (spreadsheets, CSV, pipe-delimited, etc.)
• DotNetZip: create zip files (and do much more - see Zipping below).
• iTextSharp: use form-based PDFs to create pre-filled PDFs
• Google Maps: pass address data to Google Maps API to create map-based images for inclusion in other documents.

Most QBO installations store documents in a private, secured and encrypted Amazon S3 bucket or Azure blob storage resource. Power users may add additional storage locations via the front end, including sFTP sites, additional Amazon or Azure storage, Google Docs, and more.

The IFileObject interface define a contract for reading, writing, listing and deleting files from some file repository. Standard plugins implementing the IFileObject interface include:

• Amazon S3
• Azure Blob storage
• sFTP
• Internet (HTTP)
• UNC (Windows file system / storage area networks)

Additional IFileObjects may be created for custom imaging systems. etc.

The Attachment class supports dynamic compression of files to help manage space consumed and storage costs. When configuring an IFileObject, if you specify the Compression property to true, all files written will be compressed.

Note: if you change the Compression setting after writing files, you may get errors. For example, if you start with Compression=true, write HelloWorld.txt, change to Compression=false and read HelloWorld.txt, you will receive the compressed version.

These methods can be invoked from the FileObject.ashx endpoint directly:

FileObject.ashx/ReadFile?FileObject=AmazonS3&Path=/Loan/12345/MyLoanDoc.pdf
FileObject.ashx/ListFiles?FileObject=AmazonS3&Path=/Loan/12345/&Pattern=*&Depth=0
FileObject.ashx/DeleteFile?FileObject=AmazonS3&Path=/Loan/12345/MyLoanDoc.pdf

POST /Attachment/Attachment.ashx/UploadChunk?Object=Organization&ObjectID=1&X-File-Name=Test.pdf&X-File-Size={File size}
{raw file stream}
Response: 
{
    "name": "Test.pdf",
    "size": "{File Size}",
    "finish": true,
    "upload_name": "{Guid}.pdf",
    "AttachmentItem": {
        "Object": "Organization",
        "ObjectID": 1,
        "Attachment": "Test.pdf",
        "AttachmentID": 1,
        "FileName": "Test.pdf",
        "FileObjectAssembly": "Scratchpad",
        "PathURL": "{Guid}.pdf"
    },
    ...
}

Note: the response contains finish and upload_name properties. Small files (<500K) can be uploaded in a single chunk, in which case finish will be true. If you have to upload multiple chunks, finish will be false until the last chunk is received, and chunks after the first one must pass X-File-GUID={upload_name}.

POST /Attachment/Attachment.ashx/UploadChunk?Object=Organization&ObjectID=1&X-File-Name=Test.pdf&X-File-Size={File size}
{raw file stream 1st 500K}

POST /Attachment/Attachment.ashx/UploadChunk?Object=Organization&ObjectID=1&X-File-Name=Test.pdf&X-File-Size={File size}&X-File-GUID={upload_name from 1st chunk response}
{raw file stream 2nd 500K}

POST /Attachment/Attachment.ashx/UploadChunk?Object=Organization&ObjectID=1&X-File-Name=Test.pdf&X-File-Size={File size}&X-File-GUID={upload_name from 1st chunk response}
{raw file stream 3rd 500K}

// Repeat until all chunks are delivered

-- Download document where Attachment.AttachmentID = 7
/Attachment/Attachment.ashx/Download?ID=7

Note: the Attachment/Download method signature sends the documents with headers telling the browser to save the document.

-- View document where Attachment.AttachmentID = 7
/Attachment/Attachment.ashx/View?ID=7

Note: the Attachment/View method signature sends the document's mime type to the browser, and it's up to the browser whether or not to display the file or download it. Typically, browsers will display PDF and HTML files. For many other files types, such as spreadsheets, the browser will choose to prompt the user to save the file.

QBO installation may have an unlimited number of IFileObject entries configured, each of which represents a different 'folder'. For the examples below, assume the following IFileObject are configured:

Key method signatures include:

/Attachment/Attachment.ashx/CopyFile?ID=1988,1989&FileObject=SAN&PathURL=/{Object}/{ObjectID}/{Guid}.{Uri.Extension}

/Attachment/Attachment.ashx/TransferFile?ID=1988,1989&FileObject=SAN&PathURL=/{Uri.Host}/{Year}-{Month}/{Uri.FileName}

Note that the original documents will be left alone; a copy of them will be on the SAN, and the Attachment table will now point to the SAN, and future attempts to download the files will read from the SAN.

/Attachment/Attachment.ashx/MoveFile?ID=1988,1989&FileObject=AmazonS3

Note that the original files will be deleted from their original location, and the Attachment table will now point to AmazonS3 for document retrieval.

Monitor a remote location, and import any matching documents as attachments.

Attachment/Attachment.ashx/Watch?FileObject=ClientAOut&Path=/ToQBO/&TransferTo=AmazonS3&Statement=BindByPath&Object=Loan

When specifying a PathURL to write attachments to, curly-braced string substitution may be used:

• {Guid}: replace with a globally unique identifier; guarantees a unique name.
• {DateTimeStamp}: replaced with a date time stamp in the format: yyyy-MM-dd-hh-mi-ss
• {Year}: replaced with the current year.
• {Month}: replaced with the current month.
• {Day}: replaced with the current day of the month (e.g. 17).
• {Uri.FileName}: replaced with the original filename of the document.
• {Uri.LocalPath}: replaced with the relative path of the original document as stored in an IFileObject
• {Uri.Host}: replaced with the host name of the IFileObject (E.g. AmazonS3)
• {Uri.Extension}: replaced with the file's extension (e.g. PDF, CSV, XSLX, etc.).
• {any column from the Attachment table}: replaced with the column value from the Attachment table

The DotNetZip plugin supports the following features:

Creates a zip file including one or more attachments, and streams the zip file directly to the browser. The zip file is not retained by QBO.

/Attachment.ashx/Zip?ID={list of ids}&Password={Password}

Note: Password is optional; if specified will set the password on the zip file.

Creates a zip file and saves as a new Attachment. Password is optional.

Attachment/ZipSave?ID={list of ids}&Password={Password}

Creates a zip file, and emails the file to one or more recipients. This is a good scenario to set a password!

Attachment/ZipEmail?ID={list of ids}&[email protected]&Password={password}

Parameters for the ZipEmail method include:

Attachment/Unzip?ID={ID of zip file}&BindStatement={Statement}&BindCondition=Save

By default, each document extracted will be bound to the same parent as the zip file. Specifying a BindStatement parameters allow dynamic binding of zipped documents to other parents. For example, a BindByFileName statement might look like this:

SELECT TOP 1 AttachmentID FROM Attachment WHERE FileName = @FileName AND AttachmentType=@AttachmentType ORDER BY 1 DESC

The BindCondition option controls whether inserts or updates are allowed:

• Insert: if the BindStatement returns no ID, the document is saved. (It's a new document!)
• Update: if the BindStatement returns an ID, the document is saved. (Overwriting an existing document.)
• {anything else}: the document is saved regardless of BindStatement results.

The qbo.Attachment.GoogleDrive IService plugin supports Optical Character Recognition (OCR) of documents stored in a QBO system.

Storing the text of a document to the Attachment.Description column of the database enables SQL queries against the text of the document. To fully leverage complex searching, full text indexing of the Attachment.Description field is recommended. Large volume full-text searching can be measurable performance drain on the database server, so use this feature judiciously.

-- OCR text is saved to Attachment.Description
Attachment/OcrToDescription?ID={list of AttachmentIDs}

Storing the text of a document as another Attachment allows leveraging third party document searching engines, such as Amazon CloudWatch, or internal corporate search appliances. It is more complicated to fully configure than OcrToDescription because you must also orchestrate delivery of the text documents to a third party document searching engine, but it's scalable horizontally.

Attachment/OcrToAttachment?ID={list of AttachmentIDs}

Attachment/Ocr?ID={list of AttachmentIDs} 

The following method signature will OCR, parse the text, and set Attachment.AttachmentType and/or Attachment.AttachmentTemplateID from a set of rules:

Attachment.ashx/Classify?ID={an AttachmentID}&Model={a ScoreTemplate}

This is a complex operation encompassing the following operations:

Attachment/Ocr?ID={ID}
Score/Calculate?Object=Attachment&ObjectID={ID}&Template={Model}
Matrix/Lookup?Matrix={Model}&
Attachment/Save?ID={ID}&{Output parameters from the Matrix/Lookup}

The qbo.Score.TextParsingEngine can extract text from OCR data.

OCR generally stores the results in Attachment.Description.

Given a document type (e.g. a Mortgage Note):

• Create a ScoreTemplate that used the TextParsingEngine, and applies to an Attachment
• For each data point to be extracted, create a ScoreItem, with the Prompt field specifying the pattern of text to be extracted
  • each item should specify a ValueType: Money, Date, Float, Boolean, String
• Call Score/Calculate?Object=Attachment&ObjectID={AttachmentID}&Template={Score Template Name}

The trick to extracting text is to set up patterns to look for. For example, given the following text:

I promise to pay U.S. $432,987.65 (this amount is called 'Principle'). I will pay interest at a yearly rate of 3.97%. My monthly payment will be in the amount of U.S. $1,507.55. Payments start on 1/12/17, with late charges starting 15 days later. Mortgage is issued to John Doe, known as the mortgagor. PMI Required: yes.

Create items:

The engine will look for the Prompt string of text, where the {Value} can be any string matching the item's value type.

To match new lines/carriage returns that may be in Attachment.Description as the result of the OCR process, include {Newline} in your pattern in Prompt. You can determine where these may be by viewing the value of Attachment.Description from the Attachment's summary page.

Important: If viewing Attachment.Description from a database management tool, or from somewhere else, the new lines/carriage returns actually present in the value may look like spaces instead, giving the wrong impression on how to set up your Prompt pattern to match correctly!

For example, given the prompt:

I promise to pay U.S. ${Value} (this amount is called 'Principle')

the following results would be obtained:

• "I promise to pay U.S. $350,500.25 (this amount is called 'Principle')" => 350000.25
• "I promise to pay U.S. $350500.25 (this amount is called 'Principle')" => 350000.25
• "I promise to pay U.S. $3505,00.25 (this amount is called 'Principle')" => no match because 3505,00.25 is not a valid monetary amount
• "I promise to pay U.S. 2/25/2017 (this amount is called 'Principle')" => no match
• "I promise to pay U.S. $abc123 (this amount is called 'Principle')" => no match
• "I promise to pay U.S. 350,500.25 (this amount is called 'Principle')" => no match
  • note the Prompt expects a $ before {Value}, and this string does not contain the $
  • you can omit $ from money expressions; the engine is smart enough to equate 350,500.25 with $350,500.25
  • a better prompt would be "I promise to pay U.S. {Value} (this amount is called 'Principle')"

The following regular expressions are used to parse this data:

• money: (\s)*(\${0,1})(\d+|\d{1,3}(,\d{3})*)(\.\d+)(\s)*
• float: (\s)*(\d+|\d{1,3}(,\d{3})*)(\.\d+)(\s)*
• date: (\s)*(\d{1,2})[/-](\d{1,2})[/-](\d{2,4})(\s)*
• boolean: (\s)*(true|false|1|0|yes|no|y|n)(\s)*
• string: (.*?)

You can test these expressions against text using a regex website.

Matrix/Lookup is used to evaluated data associated with the document, and return Attachment properties such as AttachmentType or AttachmentTemplateID. Typically, the matrix inputs include ScoreItem from the text extracted from the document. This allows you to associated different expressions with different document types.

The IDelivery interface enables plugins to deal with document delivery by third party vendors. QBO includes a USPSCertified plugin that generates certified mailing numbers, which may in turn be used as merge data for producing documents and mailing data for the US Postal Service.

The USPSCertified delivery creates a unique certified mailing number based on an organizational code received from the post office, and makes that data available in the merge data. When used, the following occurs:

• Attachment.OutboundTrackingID is set to be the certified mailing number
• If a OutboundTrackingID column exists in the merge data, it will be overwritten with the certified mailing number
• If a CertifiedBarCode column exists in the merge data, it will be overwritten with a USPS-compliant bar code values
• A Contact record is created and bound to the Attachment being created, containing the following information (provided this information is available in the merge data):
  • Contact, Address, City, State, PostalCode

Dynamic creation of the certified mailing number during document generation provides the following benefits when compared to most certified mailing providers:

• the certified mailing number may be embedded anywhere in your document; it need not be limited to specific coordinates
• the data is available in the Attachment.OutboundTrackingID immediately, and can thus be used to reconcile final delivery provided by third party print shops
• persistence of Contact records provides an audit trail of every address mailed to

The IPrinter interface enables creation of plugins to enable QBO to print directly to printers. QBO includes a plugin based on Google Cloud Print. When configured, users and workflow may invoke:

Attachment/Print?ID=X,Y,Z[&Printer=P]

which will stream the documents identified to the specified printer (or the default printer, if no Printer parameter is specified).

The qbo.Attachment module has a feature set to calculate, store and make use of the hash values of file data. With this data, qbo installations can do things such as comparing two attachments to see if their hash values match, and are therefore seen to be duplicates.

DefaultFileHasher - The hashing algorithm to use if not dictated. Must be either one of the default algorithms, or something that was mapped using the new file hasher configuration classes. Please see the list of algorithms at https://docs.microsoft.com/en-us/dotnet/api/system.security.cryptography.hashalgorithm.create?view=netframework-4.7.2

TrySettingFileHashAfterWrite - Attempt to set the file hash after a write operation is completed. This will not throw exceptions incase anything is awry, but instead will log as a Warning.

RejectIdenticallyHashedVersions - For versioning usecases, if a versioned file is to be saved, raise an error if the file hash of the new file matches the old one, therefore interrupting any save or related action. This will attempt to clean up after itself and delete the newly written file before an exception is thrown.

GetDuplicatesAsync - Given an ID, return any attachment in a list that has an identical file hash value.

Example usage:

/Attachment/Attachment.ashx/GetDuplicates?ID=6&CompareID=13,27,28,276,277,279,280&Output=Xml

SaveFileHash - Given an ID, and an optional Algorithm, calculate the hash value of the Attachment's file and save the Attachment.

Basic example usage relying on value of new property DefaultFileHasher:

Attachment/Attachment.ashx/SaveFileHash?ID=6

Advanced example usage to use another algorithm:

Attachment/Attachment.ashx/SaveFileHash?ID=6&Algorithm=SHA1

The underlying implementation that Algorithm relies on the standard library classes System.Security.Cryptography.HashAlgorithm, as well as System.Security.Cryptography.CryptoConfig. These classes rely on a factory method that takes a string to determine which instance to create. HashAlgorithm.Create("name") . The algorithms available by default are listed in Microsoft's documentation of the method here: https://docs.microsoft.com/en-us/dotnet/api/system.security.cryptography.hashalgorithm.create?view=netframework-4.7.2 .

Adding custom algorithms and adding them to the underlying mapping is exposed through a new set of configuration classes for this feature.

The configuration structure takes the following form in FileHasher.config:

<FileHasher>
	<FileHashers>
		<FileHasher Name="CRC" Type="qbo.Attachment.FileHashers.CRC32, qbo.Attachment" />
	</FileHashers>
</FileHasher>

Where the Type is an assembly qualified reference to a subclass of System.Security.HashAlgorithm, and Name is a unique name to call the algorithm by. When this is set up, SaveFileHash can use this name as Algorithm, and it will create the specified type as an instance to hash with.

Configuration Entry example:

<ConfigurationEntryItem>
​      <ConfigurationEntry>CRC</ConfigurationEntry>
​      <Source>FileHasher.config</Source>
​      <ConfigurationType>qbo.Attachment.Configuration.FileHasherCollection</ConfigurationType>
​      <ConfigurationKey>CRC</ConfigurationKey>
​      <ConfigurationXml>
​        <FileHasher Name="CRC"
​               Type="qbo.Attachment.FileHashers.CRC32, qbo.Attachment, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
​      </ConfigurationXml>
​    </ConfigurationEntryItem>