Print Medical, Vaccination and Test History
###
Auditing
###
34004/Any rev04
###
The combined medical, vaccination and test history for an animal
###
en
###
SELECT * FROM
(

SELECT
'Medical' AS mtype, a.ShelterCode, a.AnimalName, a.ID AS AID, a.Weight, 
t.AnimalType, s.SpeciesName, il.LocationName, 
am.TreatmentName, '' AS ResultName, am.Dosage, amt.TreatmentNumber, 
amt.TotalTreatments, amt.DateRequired, amt.DateGiven, am.Comments, o.ownername, o.ID AS OID, o.membershipnumber
FROM animal a
INNER JOIN animaltype t ON t.ID = a.AnimalTypeID
INNER JOIN species s ON s.ID = a.SpeciesID
INNER JOIN internallocation il ON il.ID = a.ShelterLocation
INNER JOIN animalmedical am ON a.ID = am.AnimalID
INNER JOIN animalmedicaltreatment amt ON amt.AnimalMedicalID = am.ID
LEFT JOIN owner o ON o.id=amt.AdministeringvetID

UNION SELECT
'Vaccination' AS mtype, a.ShelterCode, a.AnimalName, a.ID AS AID, a.Weight, 
t.AnimalType, sp.SpeciesName, il.LocationName, 
v.VaccinationType AS TreatmentName, '' AS ResultName, '1' AS Dosage, '1' AS TreatmentNumber,
'1' AS TotalTreatments, av.DateRequired, av.DateOfVaccination AS DateGiven, av.Comments, o.ownername, o.ID AS OID, o.membershipnumber
FROM animal a
INNER JOIN animaltype t ON t.ID = a.AnimalTypeID
INNER JOIN animalvaccination av ON a.ID = av.AnimalID
INNER JOIN species sp ON sp.ID = a.SpeciesID
INNER JOIN vaccinationtype v ON av.VaccinationID = v.ID
LEFT JOIN owner o ON o.id=av.administeringvetid
INNER JOIN internallocation il ON il.ID = a.ShelterLocation

UNION SELECT
'Test' AS mtype, a.ShelterCode, a.AnimalName, a.ID AS AID, a.Weight, 
t.AnimalType, sp.SpeciesName, il.LocationName, 
tt.TestName AS TreatmentName, tr.ResultName, '1' AS Dosage, '1' AS TreatmentNumber,
'1' AS TotalTreatments, at.DateRequired, at.DateOfTest AS DateGiven, at.Comments, o.ownername, o.ID AS OID, o.membershipnumber
FROM animal a
INNER JOIN animaltype t ON t.ID = a.AnimalTypeID
INNER JOIN animaltest at ON a.ID = at.AnimalID
INNER JOIN species sp ON sp.ID = a.SpeciesID
INNER JOIN testtype tt ON at.TestTypeID = tt.ID
INNER JOIN testresult tr ON at.TestResultID = tr.ID
LEFT JOIN owner o ON o.id=at.administeringvetid
INNER JOIN internallocation il ON il.ID = a.ShelterLocation

) dummy
WHERE AID = $ASK ANIMAL$
ORDER BY DateGiven
###
$$HEADER
<h2><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a> - $SHELTERCODE ($ANIMALTYPE / $SPECIESNAME). Weight: $WEIGHT</h2>
<table border=1>
<thead>
<tr>
<th><b>Therapy</b></th>
<th><b>Dosage</b></th>
<th><b>Number</b></th>
<th><b>Required</b></th>
<th><b>Given</b></th>
<th><b>Result</b></th>
<th><b>Vet Name</b></th>
<th><b>License Number</b></th>
<th><b>Comments</b></th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY<tr>
<td>
<b>$MTYPE: $TREATMENTNAME</b></td>
<td>$DOSAGE</td>
<td>$TREATMENTNUMBER / $TOTALTREATMENTS</td>
<td>$DATEREQUIRED</td>
<td>$DATEGIVEN</td>
<td>$RESULTNAME</td>
<td><a target="_blank" href="person?id=$OID">$OWNERNAME</a></td>
<td>$MEMBERSHIPNUMBER</td>
<td>$COMMENTS</td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
FOOTER$$