Medical, Vaccination and Test Diary (off-shelter)
###
Diary
###
33012/Any rev02
###
The medical, vaccination and test diary for today for off-shelter animals 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 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$'
AND a.Archived = 1 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
WHERE av.DateOfVaccination Is Null
AND av.DateRequired <= '$CURRENT_DATE$'
AND a.Archived = 1 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
WHERE at.DateOfTest Is Null
AND at.DateRequired <= '$CURRENT_DATE$'
AND a.Archived = 1 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$$
&&&
Medical, Vaccination and Test Diary (off-shelter, next week)
###
Diary
###
33012/Any rev02
###
The medical, vaccination and test diary for off-shelter animals for the next 7 days.
###
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 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+7$'
AND a.Archived = 1 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
WHERE av.DateOfVaccination Is Null
AND av.DateRequired <= '$CURRENT_DATE+7$'
AND a.Archived = 1 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
WHERE at.DateOfTest Is Null
AND at.DateRequired <= '$CURRENT_DATE+7$'
AND a.Archived = 1 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$$
