Fosterers Active between two Dates
###
Movements
###
3000/Any rev05
###
Fosterers with active fosters between two dates
###
en
###
$VAR from DATE Enter from date$
$VAR to DATE Enter to date$

SELECT
OwnerName, owner.ID AS OID,
OwnerAddress, OwnerTown, OwnerCounty, OwnerPostcode, EmailAddress, HomeTelephone, MobileTelephone,
Comments,
AdditionalFlags,
(SELECT COUNT(*) FROM adoption 
 	INNER JOIN animal ON animal.ID = adoption.AnimalID 
 	WHERE MovementType=2
 	AND adoption.OwnerID = owner.ID
    AND (MovementDate <= '$@to$') 
    AND (ReturnDate Is Null OR ReturnDate >= '$@from$') 
    AND (DeceasedDate Is Null OR DeceasedDate >= '$@from$')) AS ActiveFosters,
FosterCapacity
FROM owner
WHERE IsFosterer = 1
AND EXISTS(SELECT adoption.ID FROM adoption 
    INNER JOIN animal ON animal.ID = adoption.AnimalID 
    WHERE MovementType = 2 
    AND adoption.OwnerID = owner.ID 
    AND (MovementDate <= '$@to$') 
    AND (ReturnDate Is Null OR ReturnDate >= '$@from$') 
    AND (DeceasedDate Is Null OR DeceasedDate >= '$@from$'))
ORDER BY OwnerName
###
$$HEADER
<table border="1">
<thead>
<tr>
<th>Fosterer</th>
<th>Contact</th>
<th>Flags</th>
<th>Comments</th>
<th>Fosters</th>
</tr>
</thead>
<tbody>
HEADER$$

$$BODY
<tr>
<td><a target="_blank" href="person?id=$OID">$OWNERNAME</a></td>
<td>$OWNERADDRESS<br/>
$OWNERTOWN $OWNERCOUNTY $OWNERPOSTCODE<br/>
E: $EMAILADDRESS<br/>
H: $HOMETELEPHONE<br/>
M: $MOBILETELEPHONE</td>
<td>$ADDITIONALFLAGS</td>
<td>$COMMENTS</td>
<td>$ACTIVEFOSTERS / $FOSTERCAPACITY</td>
</tr>
BODY$$

$$FOOTER
</tbody>
</table>
<h2>Total Active Fosterers: {COUNT.OWNERNAME}</h2>
FOOTER$$

