ASM SUBREPORT NotebookSheet Vet

From Wiki
Jump to: navigation, search

SQL

SELECT
  a.ID,
  (SELECT Name FROM lksyesno WHERE ID = a.HasSpecialNeeds) AS HasSpecialNeeds,
  IF (LENGTH (a.HealthProblems), 
              a.HealthProblems, 
              'No') AS HealthProblems,
  IF (LENGTH (a.RabiesTag), 
              a.RabiesTag, 
              '(Unknown)') AS RabiesTag,
  IF (LENGTH (cv.OwnerName), 
              CONCAT (cv.OwnerName, '<br />',
                      cv.OwnerAddress, '<br />',
                      cv.OwnerPostcode ,'<br />',
                      cv.HomeTelephone), 
              '(None Listed)') AS CurrentVet
FROM 
  animal AS a
LEFT OUTER JOIN 
  owner cv ON 
    cv.ID = a.CurrentVetID
WHERE 
  a.ID = $PARENTKEY$

HTML

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

$$BODY
<tr>
  <td><b>Special Needs?</b></td>
  <td>$HasSpecialNeeds</td>
  <td><b>Health Problems:</b></td>
  <td>$HealthProblems</td>
</tr>
<tr>
  <td><b>Rabies Tag:</b></td>
  <td>$RabiesTag</td>
  <td><b>Owner's Vet:</b></td>
  <td>$CurrentVet</td>
</tr>
BODY$$

$$FOOTER
</table>
FOOTER$$