Shelter Inventory with Original Owner Addresses
###
Inventories
###
Any rev03
###
Shelter animals, broken down by species/location with original owner addresses.
###
en
###
SELECT a.ShelterCode, a.AnimalName, a.ID As AID, a.DateBroughtIn,
a.TimeOnShelter,
y.AnimalType AS theType,
s.SpeciesName,
a.BreedName,
CASE WHEN a.ActiveMovementType = 2 THEN 'Foster' ELSE i.LocationName END AS LocationName,
oo.OwnerName AS OOName, oo.ID AS OID, oo.OwnerAddress AS OOAddress, oo.OwnerTown AS OOTown, oo.OwnerCounty AS OOCounty, oo.OwnerPostcode AS OOPostcode
FROM animal a
LEFT OUTER JOIN animaltype y ON y.ID = a.AnimalTypeID
LEFT OUTER JOIN species s ON s.ID = a.SpeciesID
LEFT OUTER JOIN internallocation i ON i.ID = a.ShelterLocation
LEFT OUTER JOIN owner oo ON a.OriginalOwnerID = oo.ID
WHERE a.Archived = 0 
ORDER BY 
CASE WHEN a.ActiveMovementType = 2 THEN 'Foster' ELSE i.LocationName END, s.SpeciesName, a.DateBroughtIn
###
$$HEADER
HEADER$$

$$GROUP_LocationName
$$HEAD
<h2>$LocationName</h2>
$$FOOT
<h3>Total $LocationName: {COUNT.ShelterCode}</h3>
GROUP$$

$$GROUP_SpeciesName
$$HEAD
<h3>$SpeciesName</h3>
<table border=1>
<thead>
<tr>
<th><b>Code</b></th>
<th><b>Name</b></th>
<th><b>Type</b></th>
<th><b>Breed</b></th>
<th><b>Brought In</b></th>
<th><b>By</b></th>
<th><b>Time On Shelter</b></th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
<p>Total $SpeciesName: {COUNT.ShelterCode}</p>
GROUP$$

$$BODY
<tr>
<td>$ShelterCode</td>
<td><a target="_blank" href="animal?id=$AID">$AnimalName</a></td>
<td>$theType</td>
<td>$BreedName</td>
<td>$DateBroughtIn</td>
<td><i><a target="_blank" href="person?id=$OID">$OOName</a></i><br />
$OOAddress<br />
$OOTown<br />
$OOCounty<br />
$OOPostcode
</td>
<td>$TimeOnShelter</td>
</tr>
BODY$$

$$FOOTER
<h2>Total animals on shelter: {SQL.SELECT COUNT(*) FROM animal WHERE Archived = 0}</h2>
FOOTER$$

