Fosters by Date with Outcomes
###
Movements
###
Any rev05
###
Foster animals between two dates with their final outcomes
###
en
###
SELECT
a.AnimalName, a.ID AS AID, a.ShelterCode, a.DateOfBirth,
e.ReasonName, o.OwnerName, o.ID AS OID, m.MovementDate, m.ReturnDate, 
o.OwnerAddress AS FostererAddress, o.MobileTelephone AS FostererPhone, o.EmailAddress AS FostererEmail,
a.NeuteredDate,
CASE WHEN a.DeceasedDate Is Not Null THEN 'Died' WHEN a.ActiveMovementDate Is Not Null AND a.ActiveMovementType > 0 THEN mt.MovementType ELSE 'None' END AS OutcomeName,
CASE WHEN a.DeceasedDate Is Not Null THEN a.DeceasedDate WHEN a.ActiveMovementDate Is Not Null AND a.ActiveMovementType = 1 THEN a.ActiveMovementDate ELSE Null END AS OutcomeDate,
no.OwnerName AS OutcomePerson, no.ID AS NOID, no.OwnerAddress AS OutcomeAddress, no.OwnerTown AS OutcomeTown, no.OwnerCounty AS OutcomeCounty,
no.OwnerPostcode AS OutcomePostcode, no.HomeTelephone AS OutcomeHomeTelephone, no.WorkTelephone AS OutcomeWorkTelephone,
no.MobileTelephone AS OutcomeMobileTelephone, no.EmailAddress AS OutcomeEmail
FROM animal a
INNER JOIN adoption m ON m.AnimalID = a.ID
INNER JOIN owner o ON m.OwnerID = o.ID 
INNER JOIN entryreason e ON a.EntryReasonID = e.ID 
LEFT OUTER JOIN lksmovementtype mt ON a.ActiveMovementType = mt.ID
LEFT OUTER JOIN adoption ma ON ma.ID = a.ActiveMovementID
LEFT OUTER JOIN owner no ON no.ID = ma.OwnerID
WHERE m.MovementType = 2 AND m.MovementDate >= '$ASK DATE Fostered between$' AND m.MovementDate <= '$ASK DATE and$' 
ORDER BY m.MovementDate
###
$$HEADER
<table border="1">
<thead>
<tr>
<th>Animal</th>
<th>Code</th>
<th>DOB</th>
<th>Entry category</th>
<th>Fosterer</th>
<th>Foster Date</th>
<th>Foster Ended</th>
<th>Neutered/Spayed</th>
<th>Outcome</th>
<th>Outcome date</th>
<th>Outcome person</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$SHELTERCODE</td>
<td>$DATEOFBIRTH</td>
<td>$REASONNAME</td>
<td><b><a target="_blank" href="person?id=$OID">$OWNERNAME</a></b><br/>
$FostererAddress, $FostererPhone $FostererEmail
</td>
<td>$MOVEMENTDATE</td>
<td>$RETURNDATE</td>
<td>$NEUTEREDDATE</td>
<td>$OUTCOMENAME</td>
<td>$OUTCOMEDATE</td>
<td><b><a target="_blank" href="person?id=$NOID">$OUTCOMEPERSON</a></b><br/>
$OUTCOMEADDRESS<br/>
$OUTCOMETOWN $OUTCOMECOUNTY $OUTCOMEPOSTCODE<br/>
$OUTCOMEHOMETELEPHONE $OUTCOMEWORKTELEPHONE $OUTCOMEMOBILETELEPHONE
<i>$OUTCOMEEMAIL</i></td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
<h2>Total Fosters: {COUNT.SHELTERCODE}</h2>
<h2> Total Animals: {COUNT.SHELTERCODE.DISTINCT}</h2>
FOOTER$$
&&&
Fosters by Date and Fosterer with Outcomes
###
Auditing
###
Any rev06
###
Foster animals between two dates with their final outcomes by fosterer
###
en
###
SELECT
a.AnimalName, a.ID AS AID, a.ShelterCode, a.DateOfBirth,
e.ReasonName, o.OwnerName, o.ID AS OID, m.MovementDate, m.ReturnDate, 
o.OwnerAddress AS FostererAddress, o.MobileTelephone AS FostererPhone, o.EmailAddress AS FostererEmail,
a.NeuteredDate,
CASE WHEN a.DeceasedDate Is Not Null THEN 'Died' WHEN a.ActiveMovementDate Is Not Null AND a.ActiveMovementType > 0 THEN mt.MovementType ELSE 'None' END AS OutcomeName,
CASE WHEN a.DeceasedDate Is Not Null THEN a.DeceasedDate WHEN a.ActiveMovementDate Is Not Null AND a.ActiveMovementType = 1 THEN a.ActiveMovementDate ELSE Null END AS OutcomeDate,
no.OwnerName AS OutcomePerson, no.ID AS OOID, no.OwnerAddress AS OutcomeAddress, no.OwnerTown AS OutcomeTown, no.OwnerCounty AS OutcomeCounty,
no.OwnerPostcode AS OutcomePostcode, no.HomeTelephone AS OutcomeHomeTelephone, no.WorkTelephone AS OutcomeWorkTelephone,
no.MobileTelephone AS OutcomeMobileTelephone, no.EmailAddress AS OutcomeEmail
FROM animal a
INNER JOIN adoption m ON m.AnimalID = a.ID
INNER JOIN owner o ON m.OwnerID = o.ID 
INNER JOIN entryreason e ON a.EntryReasonID = e.ID 
LEFT OUTER JOIN lksmovementtype mt ON a.ActiveMovementType = mt.ID
LEFT OUTER JOIN adoption ma ON ma.ID = a.ActiveMovementID
LEFT OUTER JOIN owner no ON no.ID = ma.OwnerID
WHERE m.MovementType = 2 AND m.MovementDate >= '$ASK DATE Fostered between$' AND m.MovementDate <= '$ASK DATE and$' 
ORDER BY o.OwnerName, m.MovementDate
###
$$HEADER
HEADER$$

$$GROUP_OwnerName
$$HEAD
<h2><a target="_blank" href="person?id=$OID">$OwnerName</a></h2>
<p>$FostererAddress, $FostererPhone $FostererEmail</p>
<table border="1">
<thead>
<tr>
<th>Animal</th>
<th>Code</th>
<th>DOB</th>
<th>Entry category</th>
<th>Foster Date</th>
<th>Foster Ended</th>
<th>Neutered/Spayed</th>
<th>Outcome</th>
<th>Outcome date</th>
<th>Outcome person</th>
</tr>
</thead>
<tbody>
$$FOOT
</tbody>
</table>
<p>Total $OwnerName: {COUNT.OwnerName}</p>
GROUP$$

$$BODY
<tr>
<td><a target="_blank" href="animal?id=$AID">$ANIMALNAME</a></td>
<td>$SHELTERCODE</td>
<td>$DATEOFBIRTH</td>
<td>$REASONNAME</td>
<td>$MOVEMENTDATE</td>
<td>$RETURNDATE</td>
<td>$NEUTEREDDATE</td>
<td>$OUTCOMENAME</td>
<td>$OUTCOMEDATE</td>
<td><b><a target="_blank" href="person?id=$OOID">$OUTCOMEPERSON</a></b><br/>
$OUTCOMEADDRESS<br/>
$OUTCOMETOWN $OUTCOMECOUNTY $OUTCOMEPOSTCODE<br/>
$OUTCOMEHOMETELEPHONE $OUTCOMEWORKTELEPHONE $OUTCOMEMOBILETELEPHONE
<i>$OUTCOMEEMAIL</i></td>
</tr>
BODY$$

$$FOOTER
FOOTER$$