Writing a Report to Show the Electronic Signatures in SpiraTeam

Friday, December 15, 2017
Avatar

In SpiraTeam v5.0 we added native support for electronic / digital signatures. This was to allow our customers in regulated industries such as healthcare and finance be able to ensure that workflow changes were only performed by the appropriate personnel and that there was a signed audit trail of the change.

A customer asked us how to write a custom report to display the electronic signatures in a project.

2 Replies
Friday, December 15, 2017
Avatar
re: inflectra.adam Friday, December 15, 2017

The following Entity SQL (ESQL) query can be used to display such reports:

select H.CHANGESET_ID, H.ARTIFACT_TYPE_NAME, H.ARTIFACT_ID, H.ARTIFACT_DESC, H.CHANGETYPE_NAME, H.USER_NAME, H.SIGNATURE_HASH, C.COMMENT_TEXT as MEANING
from SpiraTestEntities.R_HistoryChangeSets as H
join SpiraTestEntities.R_Comments as C on C.CREATION_DATE = H.CHANGE_DATE
and H.ARTIFACT_ID = C.ARTIFACT_ID
and H.ARTIFACT_TYPE_ID = C.ARTIFACT_TYPE_ID
and H.SIGNATURE_HASH is not null

You enter that in the 'Query' section, then click the 'Create Default Template' and click Save.

Friday, December 15, 2017
Avatar
re: inflectra.adam Friday, December 15, 2017

If you want to also include the date on the report, please use this modified version:

select H.CHANGESET_ID, H.ARTIFACT_TYPE_NAME, H.ARTIFACT_ID, H.ARTIFACT_DESC, H.CHANGETYPE_NAME, H.USER_NAME, H.SIGNATURE_HASH, C.COMMENT_TEXT as MEANING, H.CHANGE_DATE
from SpiraTestEntities.R_HistoryChangeSets as H
join SpiraTestEntities.R_Comments as C on C.CREATION_DATE = H.CHANGE_DATE
and H.ARTIFACT_ID = C.ARTIFACT_ID
and H.ARTIFACT_TYPE_ID = C.ARTIFACT_TYPE_ID
and H.SIGNATURE_HASH is not null

To format the XML date as something more readable, you could use the following XSLT template:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/RESULTS">
    <table class="DataGrid"><tr><th>CHANGESET_ID</th><th>ARTIFACT_TYPE_NAME</th><th>ARTIFACT_ID</th><th>ARTIFACT_DESC</th><th>CHANGETYPE_NAME</th><th>USER_NAME</th><th>SIGNATURE_HASH</th><th>MEANING</th><th>CHANGE_DATE</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="CHANGESET_ID"/></td><td><xsl:value-of select="ARTIFACT_TYPE_NAME"/></td><td><xsl:value-of select="ARTIFACT_ID"/></td><td><xsl:value-of select="ARTIFACT_DESC"/></td><td><xsl:value-of select="CHANGETYPE_NAME"/></td><td><xsl:value-of select="USER_NAME"/></td><td><xsl:value-of select="SIGNATURE_HASH"/></td><td><xsl:value-of select="MEANING"/></td>
<td>
  <xsl:call-template name="format-date">
    <xsl:with-param name="datetime" select="CreationDate" />
  </xsl:call-template>
</td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
  <xsl:template name="format-date">
    <xsl:param name="datetime"/>
    <xsl:variable name="date" select="substring-before($datetime, 'T')" />
    <xsl:variable name="year" select="substring-before($date, '-')" />
    <xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
    <xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
    <xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
    <xsl:variable name="monthname">
      <xsl:choose>
        <xsl:when test="$month='01'">
          <xsl:value-of select="'Jan'"/>
        </xsl:when>
        <xsl:when test="$month='02'">
          <xsl:value-of select="'Feb'"/>
        </xsl:when>
        <xsl:when test="$month='03'">
          <xsl:value-of select="'Mar'"/>
        </xsl:when>
        <xsl:when test="$month='04'">
          <xsl:value-of select="'Apr'"/>
        </xsl:when>
        <xsl:when test="$month='05'">
          <xsl:value-of select="'May'"/>
        </xsl:when>
        <xsl:when test="$month='06'">
          <xsl:value-of select="'Jun'"/>
        </xsl:when>
        <xsl:when test="$month='07'">
          <xsl:value-of select="'Jul'"/>
        </xsl:when>
        <xsl:when test="$month='08'">
          <xsl:value-of select="'Aug'"/>
        </xsl:when>
        <xsl:when test="$month='09'">
          <xsl:value-of select="'Sep'"/>
        </xsl:when>
        <xsl:when test="$month='10'">
          <xsl:value-of select="'Oct'"/>
        </xsl:when>
        <xsl:when test="$month='11'">
          <xsl:value-of select="'Nov'"/>
        </xsl:when>
        <xsl:when test="$month='12'">
          <xsl:value-of select="'Dec'"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="''" />
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
  </xsl:template>
</xsl:stylesheet>

 

Statistics
  • Started: Friday, December 15, 2017
  • Last Reply: Friday, December 15, 2017
  • Replies: 2
  • Views: 768