UFN_SMARTQUERY_SPONSORSHIPDELINQUENCY

create function dbo.UFN_SMARTQUERY_SPONSORSHIPDELINQUENCY
(
    @SPONSORSELECTIONID uniqueidentifier = null,
    @SPONSORSHIPSELECTIONID uniqueidentifier = null,
    @SPONSORSHIPPROGRAMID uniqueidentifier = null,
    @FREQUENCYCODE tinyint = null,
    @PASTDUEINSTALLMENTS smallint = null,
    @PASTDUEAMOUNT money = null,
    @LASTPAYMENTCRITERIA tinyint = null,
    @LASTPAYMENTXMONTHSAGO smallint = null,
    @LASTPAYMENTDATE date = null,
    @PASTDUESINCECRITERIA tinyint = null,
    @PASTDUESINCEXMONTHSAGO smallint = null,
    @PASTDUESINCEDATE date = null,
  @CURRENCIES uniqueidentifier = null,
  @MAXROWS int = null
)
returns @T table 
(
    ID uniqueidentifier not null, 
    SPONSORNAME nvarchar(154),
    SPONSORSHIPPROGRAM nvarchar(100),
    OPPORTUNITYNAME nvarchar(154),
    OPPORTUNITYLOCATION nvarchar(200),
    FREQUENCY nvarchar(15),
    AMOUNT money,
    PASTDUEINSTALLMENTS smallint,
    PASTDUEAMOUNT money,
    LASTPAYMENTDATE date,
    PASTDUESINCE date,
  TRANSACTIONCURRENCYID uniqueidentifier
)
as
begin
    declare @CURRENTDATE date;
    set @CURRENTDATE = getdate();

    if @LASTPAYMENTCRITERIA = 0
        set @LASTPAYMENTDATE = dateadd(month,-@LASTPAYMENTXMONTHSAGO,@CURRENTDATE);

    if @PASTDUESINCECRITERIA = 0
        set @PASTDUESINCEDATE = dateadd(month,-@PASTDUESINCEXMONTHSAGO,@CURRENTDATE);

  if @CURRENCIES is null
    set @CURRENCIES = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    /* WI # 359044 AlexLa On the edit form, previously, if you don't select a value, it still comes in with a default value so that it appears as though there is criteria set in the UI when there is not.
       For instance, you may have set past due amount to a number that shouldn't cause anyone to come up, yet Past due installments comes in as 0 when not selected so this statement
       PASTDUESUMMARY.AMOUNT >= @PASTDUEAMOUNT is always true.
       Without changing the function's signature, I think this change will work because if the @value is the default they either a)Did not check the box or 
       b) the UI will enforce that they put something other than the default if the checkbox is checked.
    */
    if @PASTDUEINSTALLMENTS = 0
       set @PASTDUEINSTALLMENTS = null;

    if @PASTDUEAMOUNT = 0.00
       set @PASTDUEAMOUNT = null;

    if @LASTPAYMENTDATE = '01/01/0001'
       set @LASTPAYMENTDATE = null; 

    insert into @T
    select top(@MAXROWS) SPONSORSHIP.ID,
           NF.NAME,
           SPONSORSHIPPROGRAM.NAME,
           dbo.UFN_SPONSORSHIPOPPORTUNITY_TRANSLATIONFUNCTION(SPONSORSHIP.SPONSORSHIPOPPORTUNITYID),
           dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID),
           REVENUESCHEDULE.FREQUENCY,
           REVENUESPLIT.TRANSACTIONAMOUNT as AMOUNT,
           PASTDUESUMMARY.NUMINSTALLMENTS,
           PASTDUESUMMARY.AMOUNT as PASTDUEAMOUNT,
           LASTPAYMENT.DATE,
           PASTDUESUMMARY.SINCEDATE,
           REVENUESPLIT.TRANSACTIONCURRENCYID
    from dbo.SPONSORSHIP
    left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SPONSORSELECTIONID) INCLUDESPONSORS on SPONSORSHIP.CONSTITUENTID = INCLUDESPONSORS.ID
    left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SPONSORSHIPSELECTIONID) INCLUDESPONSORSHIPS on SPONSORSHIP.ID = INCLUDESPONSORSHIPS.ID
    left join dbo.REVENUESPLIT on REVENUESPLIT.ID = SPONSORSHIP.REVENUESPLITID
    left join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
    left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUESPLIT.REVENUEID
    left join
        (
            select
                WRITEOFFINSTALLMENTS.REVENUEID,
                count(*) NUMINSTALLMENTS,
                sum(WRITEOFFINSTALLMENTS.BALANCE) AMOUNT,
                min(WRITEOFFINSTALLMENTS.DATE) SINCEDATE
            from
                dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFINSTALLMENTS_BULK() as WRITEOFFINSTALLMENTS
            where
                WRITEOFFINSTALLMENTS.STATUSCODE = 1
            group by
                WRITEOFFINSTALLMENTS.REVENUEID
        ) PASTDUESUMMARY on REVENUESPLIT.REVENUEID = PASTDUESUMMARY.REVENUEID
    left join
        (
            select
                RECURRINGGIFTACTIVITY.SOURCEREVENUEID,
                max(PAYMENT.DATE) DATE
            from
                dbo.RECURRINGGIFTACTIVITY
                inner join dbo.REVENUESPLIT PAYMENTSPLIT on PAYMENTSPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
                inner join dbo.REVENUE PAYMENT on PAYMENT.ID = PAYMENTSPLIT.REVENUEID
            group by
                RECURRINGGIFTACTIVITY.SOURCEREVENUEID
        ) LASTPAYMENT on REVENUESPLIT.REVENUEID = LASTPAYMENT.SOURCEREVENUEID
    left join dbo.SPONSORSHIPPROGRAM on SPONSORSHIPPROGRAM.ID = SPONSORSHIP.SPONSORSHIPPROGRAMID
    left join dbo.SPONSORSHIPOPPORTUNITY on SPONSORSHIPOPPORTUNITY.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
    where (@SPONSORSELECTIONID is null or INCLUDESPONSORS.ID is not null)
    and (@SPONSORSHIPSELECTIONID is null or INCLUDESPONSORSHIPS.ID is not null)
    and (@SPONSORSHIPPROGRAMID is null or SPONSORSHIP.SPONSORSHIPPROGRAMID = @SPONSORSHIPPROGRAMID)
    and (@FREQUENCYCODE is null or REVENUESCHEDULE.FREQUENCYCODE = @FREQUENCYCODE)
    and ((@PASTDUEINSTALLMENTS is not null and PASTDUESUMMARY.NUMINSTALLMENTS >= @PASTDUEINSTALLMENTS) or
       (@PASTDUEAMOUNT is not null and PASTDUESUMMARY.AMOUNT >= @PASTDUEAMOUNT and REVENUESPLIT.TRANSACTIONCURRENCYID = @CURRENCIES) or
       (@LASTPAYMENTDATE is not null and (LASTPAYMENT.DATE <= @LASTPAYMENTDATE or LASTPAYMENT.DATE is null)) or
       (@PASTDUESINCEDATE is not null and PASTDUESUMMARY.SINCEDATE <= @PASTDUESINCEDATE))
  and SPONSORSHIP.STATUSCODE = 1

    return;
end;