Creating a Workflow Report and Placing into Default Container

Someone asked if you could use powershell to generate a workflow status report and save it into the workflow's default container..... yes you can!

Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$iseProc = Get-Process -id $pid
$db = New-Object HP.HPTRIM.SDK.Database
$wfs = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, Workflow
$wfs.SearchString = "all"
$report = New-Object HP.HPTRIM.SDK.Report -ArgumentList $db, "Workflow Report"
$docRt = New-Object HP.HPTRIM.SDK.RecordType -ArgumentList $db, "Document"
foreach ( $wf in $wfs ) 
{
    if ( ([HP.HPTRIM.SDK.Workflow]$wf).DefaultContainer -ne $null ) 
    { 
        $wfc = New-Obect HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, Workflow
        $wfc.SearchString = "uri:$(([HP.HPTRIM.SDK.Workflow]$wf).Uri)"
        $pdf = $report.PrintReportToPdf($wfc)
        $pdfRec = New-Object HP.HPTRIM.SDK.Record -ArgumentList $db, $docRt
        $pdfRec.TypedTitle = "Workflow Report"
        $pdfRec.SetDocument($pdf)
        $pdfRec.SetContainer(([HP.HPTRIM.SDK.Workflow]$wf).DefaultContainer)
        $pdfRec.Save()
        $pdfRec.Dispose()
    }
}
$db.Dispose()

Authorizing Documents via DocuSign

There's a pretty nifty new feature in Content Manager: the Document Review process.  This process includes an authorization feature that supports DocuSign.  You can use a more simple process, but I'm focusing on DocuSign at the moment.  With DocuSign you get those cool "sign here" spots in a document (like what my accountant might send me).  

Once signed (or Authorized in the CM terminology), the signed copy can become your final record.  Very cool!  

Starting the Authorization (Signing) Process

As a normal end-user I create a new record of type "Policy Document".  Then I right-click on it and select Document Review->Start Authorization 

In the real world I would have probably done a lot more before getting to this point.  Imagine numerous revisions, actions, meta-data fields, etc.  For simplicity I'm just skipping all of that.  I want this word document to be signed.. and that's it. 

When I Start Authorization, the Content Manager rendering service will hand-off the electronic document for processing.  Once it's been handed off, DocuSign emails the responsible location.  

I clicked the link and then signed the document.

The Content Manager rendering service will routinely check (every 30 minutes by default) for updates to the status of pending request.  After the update is processed I should be able to see a new rendition on my original record.  The screenshot below shows what that would look like.

Success!  Signed rendition via DocuSign

Technically the process isn't done yet.  The authorization has been received and now it's time to finalize the record.  It's an opportunity to update the notes, locations, meta-data, or access controls for the record.  The menu options reflect this state:

Menu options available at last stage of the process

After selecting the Finalize Document feature (not to be confused with the Finalize option under the electronic menu!!) for Document Review, I'm asked to decide how to handle the record.  I'm disappointed that the promotion option is not checked by default, but I can easily check it.  

Once I click OK, all users can now see the digitally signed copy as the final revision.

Appearance of the final record within Content Manager

This has been a very straight-forward process in terms of setup and configuration.  I can see tons of possible uses.  Entirely possible to have external parties digitally signing records without them ever knowing Content Manager is in-use.  You can also setup template for your processes, signature spots, and comment sections.

Configuration of Content Manager

I created one record type named "Policy Document".  I used all of the default settings for the record type, except for the document review tab.  There I've checked the authorization required checkbox, specified "Policy Manager" as the location responsible, DocuSign as my process, and 2 days for my authorization reminder duration.

The Policy Manager is a location with a name, login name and email address:

Dataset Rendering Configuration

I setup my Render service configuration to reflect my DocuSign account details.  The help file directs you to use "docusign.com", but since I'm using a demo account I couldn't use that.  I got HTTP 301 errors when I tried it.  To figure it out I went to the DocuSign REST API Explorer (https://apiexplorer.docusign.com), looked at the URL those worked with and then plugged that into the configuration.  Screenshot below:

Rendering Service Configuration

As you can also see, I lowered my polling interval from 1800 seconds (30 minutes) to 30 seconds.  Be careful with that though, as your terms of service with DocuSign are important to adhere to.  Don't get locked out! :)

Building a CM SQL Facade

I'm in the middle of an upgrade for a customer for whom I need to recompile an SQL project I previously created.  At the same time, another customer needed this exact same type of solution.  In both cases they had separate systems which needed to query TRIM/Content Manager via SQL statement.  This is not provided out-of-the-box.

Although you can query Content Manger directly via SQL, you most definitely should not.  To maintain certification and remain secure, all access to Content Manager should go through the provided SDKs. Therefore, we need scaffold up an interface ontop of the SDK which SQL can leverage.

Programmers call this a facade.

Setting the goal

I'd like to be able to do two things:

1. Execute a stored procedure which gives me a list of customers
2. Execute a stored procedure which gives me a list of documents for a given customer

To achieve this goal I need to have a record type defined for my documents and a property on them called customers.  To keep things simple for the blog posting I'll simply fetch all locations and present those as my customers.  Then I'll return all documents that location has authored.  

For this blog posting I'm only going to show the list of customers.  The logic for documents is something you can test on your end.

Defining the procedures

Based on my goals I'll need to create two procedures: GetCustomers, GetCustomerDocuments. Let's retrieve each customer's URI and name.  For the documents, let's retrieve the number, title, and web URL for each record of type document where the record's author matches a given location's uri.  

I'll need method signatures which look like this:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomers()
{
 
}
 
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomerDocuments(long customerUri)
{
}

Coding the procedures

We can output directly to the sql server pipe and send meta-data for each object in Content Manager.  To get an object we'll need to execute a search.  That requires a connection to the dataset though, so the TRIMSDK must be loaded into the solution (and SQL server).  I've chosen the COM SDK because I'm more familiar with it.  The .Net SDK, ServiceAPI, or webservice could also be used.

Note: I'm providing the skeleton implementation, which is no where near production ready.  Use at your own risk.

[Microsoft.SqlServer.Server.SqlProcedure]
  public static void GetCustomers()
  {
      Database trimDatabase = null;
      Locations customerLocations = null;
      try
      {
          RemoveLogFile();
 
          Log("Creating Database Object");
          trimDatabase = new TRIMSDK.Database();
          trimDatabase.Id = "CM";
          trimDatabase.WorkgroupServerName = "APPS";
          Log("Connecting");
          trimDatabase.Connect();
          Log("Making Locations");
          customerLocations= trimDatabase.MakeLocations();
          customerLocations.SearchString = "all";
          Location customerLocation = null;
          if (customerLocations.Count > 0)
          {
              SqlMetaData[] md = new SqlMetaData[2];
              md[0] = new SqlMetaData("uri", SqlDbType.BigInt);
              md[1] = new SqlMetaData("customerName", SqlDbType.NVarChar, 50);
              SqlDataRecord row = new SqlDataRecord(md);
              SqlContext.Pipe.SendResultsStart(row);
              while ((customerLocation = customerLocations.Next()) != null) {
                  Log($"Location Found: {customerLocation.FullFormattedName}");
                  row.SetInt64(0, (long)customerLocation.Uri);
                  row.SetSqlString(1, customerLocation.FullFormattedName);
                  SqlContext.Pipe.SendResultsRow(row);
                  ReleaseObject(customerLocation);
                  customerLocation = null;
              }
              SqlContext.Pipe.SendResultsEnd();
              Log("Done");
          } else
          {
              Log("No Locations Found");
          }
 
      }
      catch ( Exception ex )
      {
          Log("Error: " + ex.Message);
          Log(ex.ToString());
      }
      finally
      {
          ReleaseObject(customerLocations);
          customerLocations = null;
          ReleaseObject(trimDatabase);
          trimDatabase = null;
      }
  }

 

Installing the assemblies

After compiling the solution you have to load the assembly and COM SDK Interop into the SQL Server database, like shown below:

Add the assemblies with unrestricted access and dbo as owner.  

Creating the SQL procedure

After the assemblies have been loaded you can create the SQL CLR procedure by executing the statement below:

CREATE PROCEDURE [dbo].[GetCustomers]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CMFacade].[StoredProcedures].[GetCustomers]
GO

Executing the SQL procedure

With everything installed and wired-up, we can execute the procedure by using this statement:

USE [CMFacade]
GO
DECLARE	@return_value int
EXEC	@return_value = [dbo].[GetCustomers]
SELECT	'Return Value' = @return_value
GO

Example results:

Retrieving Customer Documents

Now that there's a procedure for getting all of the customers, I applied the same approach and created a procedure returning the documents.  As shown in the screen shot below I'm only returning the fields needed.  This is exactly why a facade is important: it exposes only what is needed.