Medical, Vaccination and Tests given between two dates by animal
###
Medical
###
3309/Any rev02
###
Medical, vaccination and tests given to shelter animals between two dates in one report by animal
###
en
###
$VAR from DATE Enter from date$
$VAR to DATE Enter to date$

SELECT * FROM
(

SELECT
'Medical' AS mtype, a.ShelterCode, a.AnimalName, a.ID AS AID,
t.AnimalType, s.SpeciesName, a.DisplayLocation, 
am.TreatmentName, am.Dosage, amt.TreatmentNumber, 
amt.TotalTreatments, amt.DateGiven, amt.GivenBy, am.Comments
FROM animal a
INNER JOIN animaltype t ON t.ID = a.AnimalTypeID
INNER JOIN species s ON s.ID = a.SpeciesID
INNER JOIN animalmedical am ON a.ID = am.AnimalID
INNER JOIN animalmedicaltreatment amt ON amt.AnimalMedicalID = am.ID
WHERE amt.DateGiven Is Not Null 
AND amt.DateGiven >= '$@from$' AND amt.DateGiven <= '$@to$'
AND a.DeceasedDate Is Null

UNION SELECT
'Vaccination' AS mtype, a.ShelterCode, a.AnimalName, a.ID AS AID, 
t.AnimalType, sp.SpeciesName, a.DisplayLocation,
v.VaccinationType AS TreatmentName, '1' AS Dosage, '1' AS TreatmentNumber,
'1' AS TotalTreatments, av.DateOfVaccination AS DateGiven, av.LastChangedBy AS GivenBy, av.Comments
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
WHERE av.DateOfVaccination Is Not Null
AND av.DateOfVaccination >= '$@from$' AND av.DateOfVaccination <= '$@to$'
AND a.DeceasedDate Is Null

UNION SELECT
'Test' AS mtype, a.ShelterCode, a.AnimalName, a.ID AS AID, 
t.AnimalType, sp.SpeciesName, a.DisplayLocation,
tt.TestName AS TreatmentName, tr.ResultName AS Dosage, '1' AS TreatmentNumber,
'1' AS TotalTreatments, at.DateOfTest AS DateGiven, at.LastChangedBy AS GivenBy, at.Comments
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 tr.ID = at.TestResultID
WHERE at.DateOfTest Is Not Null
AND at.DateOfTest >= '$@from$' AND at.DateOfTest <= '$@to$'
AND a.DeceasedDate Is Null

) dummy
ORDER BY AnimalName, DateGiven

###
$$HEADER
HEADER$$

$$GROUP_AnimalName
$$HEAD
<h2>$ShelterCode - $AnimalName</h2>
<table border=1>
<thead>
<tr>
<th><b>Animal</b></th>
<th><b>Type</b></th>
<th><b>Location</b></th>
<th><b>Therapy</b></th>
<th><b>Dosage</b></th>
<th><b>Number</b></th>
<th><b>Date Given</b></th>
<th><b>By</b></th>
<th><b>Comments</b></th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
<p>Total $AnimalName: {COUNT.AnimalName}</p>
GROUP$$

$$BODY<tr>
<td><a target="_blank" href="animal?id=$AID">$SHELTERCODE $ANIMALNAME</a></td>
<td>$ANIMALTYPE</td>
<td>$DISPLAYLOCATION</td>
<td><b>$MTYPE: $TREATMENTNAME</b></td>
<td>$DOSAGE</td>
<td>$TREATMENTNUMBER / $TOTALTREATMENTS</td>
<td>$DATEGIVEN</td>
<td>$GIVENBY</td>
<td>$COMMENTS</td>
</tr>
BODY$$

$$FOOTER
FOOTER$$
