Pickup Location Figures
###
Figures
###
Any rev01
###
Induction, movement and euthanasia states broken down by pickup location.
###
en
###
$VAR from DATE Enter from date$
$VAR to DATE Enter to date$

SELECT * FROM (

SELECT pl.LocationName AS coun,
'Intake: ' || er.ReasonName AS heading,
CAST('Cats' AS varchar) AS species,
1 AS theorder,
COUNT(DISTINCT animal.id) AS val
FROM animal
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
INNER JOIN entryreason er ON animal.EntryReasonID = er.ID
WHERE SpeciesID = 2
AND DateBroughtIn >= '$@from$' AND DateBroughtIn <= '$@to$'
GROUP BY coun, heading

UNION SELECT pl.LocationName AS coun,
'Intake: ' || er.ReasonName AS heading,
CAST('Dogs' AS varchar) AS species,
1 AS theorder,
COUNT(DISTINCT animal.id) AS val
FROM animal
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
INNER JOIN entryreason er ON animal.EntryReasonID = er.ID
WHERE SpeciesID = 1
AND DateBroughtIn >= '$@from$' AND DateBroughtIn <= '$@to$'
GROUP BY coun, heading

UNION SELECT pl.LocationName AS coun,
CAST('Reclaimed' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
1 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND movementtype = 5 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Reclaimed' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
1 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND movementtype = 5 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Adopted' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
2 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND movementtype = 1 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Adopted' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
2 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND movementtype = 1 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Transferred' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
3 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND movementtype = 3 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Transferred' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
3 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND movementtype = 3 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Stolen' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
4 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND movementtype = 6 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Stolen' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
4 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND movementtype = 6 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Escaped' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
5 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND movementtype = 4 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Escaped' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
5 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN adoption ON adoption.AnimalID = animal.ID
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND movementtype = 4 
AND movementdate >= '$@from$' AND movementdate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Total Euthanised' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
6 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND DiedOffShelter = 0 AND IsDOA = 0
AND deceaseddate >= '$@from$' AND deceaseddate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Total Euthanised' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
6 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND DiedOffShelter = 0 AND IsDOA = 0
AND deceaseddate >= '$@from$' AND deceaseddate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Total DOA' AS varchar) AS heading, 
CAST('Cats' AS varchar) AS species,
7 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 2 AND DiedOffShelter = 0 AND IsDOA = 1
AND deceaseddate >= '$@from$' AND deceaseddate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
CAST('Total DOA' AS varchar) AS heading, 
CAST('Dogs' AS varchar) AS species,
7 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
WHERE SpeciesID = 1 AND DiedOffShelter = 0 AND IsDOA = 1
AND deceaseddate >= '$@from$' AND deceaseddate <= '$@to$' 
GROUP BY coun

UNION SELECT pl.LocationName AS coun,
'Euthanasia: ' || deathreason.ReasonName AS heading, 
CAST('Cats' AS varchar) AS species,
8 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID
WHERE SpeciesID = 2 AND DiedOffShelter = 0 AND IsDOA = 0
AND deceaseddate >= '$@from$' AND deceaseddate <= '$@to$' 
GROUP BY coun, heading

UNION SELECT pl.LocationName AS coun,
'Euthanasia: ' || deathreason.ReasonName AS heading, 
CAST('Dogs' AS varchar) AS species,
8 AS theorder,
COUNT(DISTINCT animal.id) AS val 
FROM animal 
INNER JOIN pickuplocation pl ON animal.PickupLocationID = pl.ID
INNER JOIN deathreason ON animal.PTSReasonID = deathreason.ID
WHERE SpeciesID = 1 AND DiedOffShelter = 0 AND IsDOA = 0
AND deceaseddate >= '$@from$' AND deceaseddate <= '$@to$' 
GROUP BY coun, heading

) dummy
ORDER BY coun, species, theorder

###

$$HEADER
HEADER$$

$$GROUP_coun
$$HEAD
<h2>$coun</h2>
$$FOOT
GROUP$$

$$GROUP_species
$$HEAD
<p><b>$species</b></p>
<table>
$$FOOT
</table>
GROUP$$

$$BODY
<tr>
<td>$heading</td>
<td>$val</td>
</tr>
BODY$$

$$FOOTER
FOOTER$$
