ASM SUBREPORT NotebookSheet EntryDetails

From Wiki
Jump to: navigation, search

SQL

SELECT
  a.ID,
  IF (LENGTH (oo.OwnerName),
              CONCAT (oo.OwnerName, '<br />',
                      oo.OwnerAddress, '<br />',
                      oo.OwnerPostcode, '<br />',
                      oo.HomeTelephone), 
              '(None Listed)') AS OriginalOwner,
  IF (LENGTH (bb.OwnerName),
              CONCAT (bb.OwnerName, '<br />',
                      bb.OwnerAddress, '<br />',
                      bb.OwnerPostcode, '<br />',
                      bb.HomeTelephone), 
              '(None Listed)') AS BroughtInBy,
  IF (LENGTH (a.ReasonForEntry), 
              a.ReasonForEntry, 
              '(Unknown)') AS EntryReason,
  (SELECT ReasonName FROM entryreason WHERE ID = a.EntryReasonID) AS EntryCategory,
  DATE (a.DateBroughtin) AS DateBroughtIn
FROM 
  animal AS a
LEFT OUTER JOIN 
  owner oo ON 
    oo.ID = a.OriginalOwnerID
LEFT OUTER JOIN 
  owner bb ON 
    bb.ID = a.BroughtInByOwnerID
WHERE 
  a.ID = $PARENTKEY$

HTML

$$HEADER
<h3>Entry Details</h3>
<table rules="all" border="1" cellspacing="5" cellpadding="5">
HEADER$$

$$BODY
<tr>
  <td><b>Original Owner:</b></td>
  <td>$OriginalOwner</td>
  <td><b>Brought In By:</b></td>
  <td>$BroughtInBy</td>
</tr>
<tr>
  <td><b>Entry Reason:</b></td>
  <td>$EntryReason</td>
  <td><b>Entry Category:<b></td>
  <td>$EntryCategory</td>
</tr>
<tr>
  <td><b>Date Brought In:</b></td>
  <td colspan="3">$DateBroughtIn</td>
</tr>
BODY$$

$$FOOTER
</table>
FOOTER$$