$query.Payments
SELECT 0 AS Type, ma.FirstName, ma.LastName, g.GroupName, ma.Company, DATEDIFF(dd,getdate(),p.CreateDate) AS SortOrder, pt.PaymentType, p.Amount, p.CreateDate, p.PayInfo, NULL AS OldAmount, p.CheckNumber
FROM MasterAccounts ma, Groups g, Payments p, PaymentTypes pt
	WHERE ma.CustomerID = p.CustomerID
	AND ma.GroupID = g.GroupID
	AND p.PaymentTypeID = pt.PaymentTypeID
	AND p.CreateDate >= $!Variables.StartDate
	AND p.CreateDate <= $!Variables.EndDate
	AND ma.GroupID = $-Variables.GroupID
	AND g.OrganizationID = $-Variables.OrganizationID

UNION ALL

SELECT 1 AS Type, ma.FirstName, ma.LastName, g.GroupName, ma.Company, DATEDIFF(dd,getdate(),p.FinalCreateDate) AS SortOrder, pt.PaymentType, p.FinalAmount, p.FinalCreateDate, p.PayInfo, p.Amount AS OldAmount, p.CheckNumber
FROM MasterAccounts ma, Groups g, Payments p, PaymentTypes pt
	WHERE ma.CustomerID = p.CustomerID
	AND ma.GroupID = g.GroupID
	AND p.PaymentTypeID = pt.PaymentTypeID
	AND p.FinalCreateDate >= $!Variables.StartDate
	AND p.FinalCreateDate <= $!Variables.EndDate
	AND ma.GroupID = $-Variables.GroupID
	AND g.OrganizationID = $-Variables.OrganizationID

UNION ALL

SELECT 2 AS Type, NULL AS FirstName, NULL AS LastName, NULL AS GroupName, NULL AS Company, DATEDIFF(dd,getdate(),COALESCE(p.FinalCreateDate,p.CreateDate)) AS SortOrder, pt.PaymentType, 
SUM(CASE WHEN p.FinalAmount IS NOT NULL AND p.FinalCreateDate >= $!Variables.StartDate AND p.FinalCreateDate <= $!Variables.EndDate THEN p.FinalAmount ELSE p.Amount END) AS Amount, NULL AS CreateDate, NULL AS PayInfo, NULL AS OldAmount, NULL AS CheckNumber
FROM MasterAccounts ma, Groups g, Payments p, PaymentTypes pt
	WHERE ma.CustomerID = p.CustomerID
	AND ma.GroupID = g.GroupID
	AND p.PaymentTypeID = pt.PaymentTypeID
	AND ((p.CreateDate >= $!Variables.StartDate AND p.CreateDate <= $!Variables.EndDate)
		OR (p.FinalCreateDate >= $!Variables.StartDate AND p.FinalCreateDate <= $!Variables.EndDate))
	AND ma.GroupID = $-Variables.GroupID
	AND g.OrganizationID = $-Variables.OrganizationID
GROUP BY DATEDIFF(dd,getdate(),COALESCE(p.FinalCreateDate,p.CreateDate)), pt.PaymentType

ORDER BY 6 DESC, 1, 7, 8
$query


$query.PaymentsTotal
SELECT p.Amount
Into #t
FROM MasterAccounts ma, Groups g, Payments p, PaymentTypes pt
	WHERE ma.CustomerID = p.CustomerID
	AND ma.GroupID = g.GroupID
	AND p.PaymentTypeID = pt.PaymentTypeID
	AND p.PaymentTypeID = 8
	AND p.CreateDate >= $!Variables.StartDate
	AND p.CreateDate <= $!Variables.EndDate
	AND ma.GroupID = $-Variables.GroupID
	AND g.OrganizationID = $-Variables.OrganizationID

UNION ALL

SELECT p.FinalAmount
FROM MasterAccounts ma, Groups g, Payments p, PaymentTypes pt
	WHERE ma.CustomerID = p.CustomerID
	AND ma.GroupID = g.GroupID
	AND p.PaymentTypeID = pt.PaymentTypeID
	AND p.PaymentTypeID = 8
	AND p.FinalCreateDate >= $!Variables.StartDate
	AND p.FinalCreateDate <= $!Variables.EndDate
	AND ma.GroupID = $-Variables.GroupID
	AND g.OrganizationID = $-Variables.OrganizationID

Select Count(*) as Numb, Sum(Amount) As Amount
From #t

Drop Table #t
$query


$query.Org
SELECT Organization FROM Organizations WHERE OrganizationID=$-Variables.OrganizationID
$query

$time.now
$time

<STYLE>.break
{
PAGE-BREAK-BEFORE:always;
}.header
{
WIDTH:100%;
}.box
{
FONT-SIZE:12pt;
BORDER-STYLE:solid;
BORDER-COLOR:#000;
BORDER-WIDTH:2px;
}.box TH
{
BACKGROUND-COLOR:#eee;
BORDER-BOTTOM:dotted;
BORDER-WIDTH:1px;
}.label
{
FONT-WEIGHT:bolder;
TEXT-ALIGN:right;
}.money
{
TEXT-ALIGN:right;
}.pay
{
WIDTH:100%;
}.total
{
BACKGROUND-COLOR:#eee;
FONT-WEIGHT:bolder;
}
</STYLE>

$set.language
LanguageID=$Variables.LanguageID
$set

<TABLE CELLSPACING=0 CELLPADDING=0 WIDTH="100%"><TD>
<TABLE CLASS="header">
$check.compare
compare=$configs.560.$Variables.OrganizationID
with=
denied=<TD><IMG SRC="$configs.560.$Variables.OrganizationID"></TD>
$check

<TD ALIGN=RIGHT><TABLE CLASS="box">
<TH COLSPAN=4>${12121:Daily Payments}</TH>
<TR><TD CLASS="label">${12050:Current Date:}</TD><TD COLSPAN=3>$@now.Now</TD>
<TR><TD CLASS="label">${12051:Organization:}</TD><TD>
$check.compare
compare=$Variables.OrganizationID
with=
allowed=N/A
denied=$Org.Organization
$check
</TD>
<TD CLASS="label">${10029:Billing Group:}</TD><TD>
$check.compare
compare=$Variables.GroupID
with=
allowed=${10013:All}
denied=$configs.551.$Variables.GroupID 
$check
</TD>
<TR>
<TD CLASS="label">${12056:Starting:}</TD><TD>$!Variables.StartDate</TD>
<TD CLASS="label">${12057:Ending:}</TD><TD>$!Variables.EndDate</TD>
</TABLE>
</TD></TABLE>

<P><BR><P>

<TABLE CLASS="box pay"><TH>${10058:Date}</TH><TH>${10114:Type}</TH><TH>${12122:Pay Type}</TH><TH>${10095:Billing Group}</TH><TH>${10054:Name}</TH><TH>${10159:Company}</TH><TH>${10078:Amount}</TH>

$floop.Payments

$check.compare
compare=$floop.Type
with=2
denied_inline=$check2.compare
compare=$newt.value
with=1
allowed_inline=</TABLE><P><TABLE CLASS="box pay"><TH>${10058:Date}</TH><TH>${10114:Type}</TH><TH>${12122:Pay Type}</TH><TH>${10095:Billing Group}</TH><TH>${10054:Name}</TH><TH>${10159:Company}</TH><TH>${10078:Amount}</TH>
$tablist.newt
Value
0
$tablist
$check2
$check

$check.compare
compare=$floop.Type
with=2
allowed=<TR CLASS="total">
denied=<TR>
$check

<TD>$floop.CreateDate</TD>
<TD>
$check.compare
compare=$floop.Type
with=0
allowed=${12123:New Payment}
$check
$check.compare
compare=$floop.Type
with=1
allowed=${12124:Changed Payment}
$check
$check.compare
compare=$floop.Type
with=2
allowed=${12125:Payment Total}
$check
</TD>

<TD>$floop.PaymentType $floop.CheckNumber</TD>
<TD>$floop.GroupName</TD>
<TD>$floop.FirstName $floop.LastName</TD>
<TD>$floop.Company</TD>

$check.compare
compare=$floop.Type
with=1
allowed=<TD>$$floop.OldAmount to $$floop.Amount</TD>
denied=<TD CLASS="money">$$floop.Amount</TD>
$check

$check.compare
compare=$floop.Type
with=2
allowed_inline=$tablist.newt
value
1
$tablist
$check

$floop

</TABLE>

<p> $PaymentsTotal.Numb  Payments in Period $!Variables.StartDate - $!Variables.EndDate  Totaling: $PaymentsTotal.Amount </p>

$set.language
$set