Medical, Vaccination and Tests due in the next two weeks
###
Medical
###
3309/Any rev02
###
Medical, vaccination and tests due in the next two weeks in one report
###
en
###
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.DateRequired, 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 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
WHERE amt.DateGiven Is Null 
AND amt.DateRequired <= '$CURRENT_DATE+14$'
AND a.Archived = 0 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.DateRequired, 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
INNER JOIN internallocation il ON il.ID = a.ShelterLocation
WHERE av.DateOfVaccination Is Null
AND av.DateRequired <= '$CURRENT_DATE+14$'
AND a.Archived = 0 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, '1' AS Dosage, '1' AS TreatmentNumber,
'1' AS TotalTreatments, at.DateRequired, 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 internallocation il ON il.ID = a.ShelterLocation
WHERE at.DateOfTest Is Null
AND at.DateRequired <= '$CURRENT_DATE+14$'
AND a.Archived = 0 AND a.DeceasedDate Is Null

) dummy
ORDER BY DateRequired
###
$$HEADER
<table border=1>
<thead>
<tr>
<th><b>Animal</b></th>
<th><b>Type</b></th>
<th><b>Species</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 Required</b></th>
<th><b>Comments</b></th>
</tr>
</thead>
<tbody>
HEADER$$

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

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