Medical, Vaccination and Test Diary
###
Diary
###
33012/Any rev04
###
The medical, vaccination and test diary for today 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 = 0

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 = 0

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 = 0

) 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 (next week)
###
Diary
###
33012/Any rev03
###
The medical, vaccination and test diary 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 = 0

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 = 0

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 = 0

) 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$$
