UFN_BILLINGTRANSACTIONTYPETOTAL

CREATE function dbo.UFN_BILLINGTRANSACTIONTYPETOTAL
(
    @TRANSACTIONTYPECODE tinyint,
    @CONSTITUENTID uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @INCLUDEAPPLICATIONS bit = 1,
    @USEDUEDATE bit = 0,
    @IGNOREAPPLICATIONDATE bit = 0
)
returns money
with execute as caller
as
begin
  if (not @STARTDATE is null)
    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);

  if (not @ENDDATE is null)
    set @ENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(@ENDDATE);
  else if ((@ENDDATE is null) and (@USEDUEDATE = 1))
    set @ENDDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  declare @TOTAL money = 0;
  select
    @TOTAL = sum(isnull(FINANCIALTRANSACTION.TRANSACTIONAMOUNT, 0))
  from dbo.FINANCIALTRANSACTION
  left outer join dbo.CHARGE on (CHARGE.ID = FINANCIALTRANSACTION.ID) and (@USEDUEDATE = 1)
  where (FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPECODE) and
        (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID) and
        --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

        ((@STARTDATE is null) or (((@USEDUEDATE = 1) and (cast(CHARGE.DUEDATE as date) >= @STARTDATE)) or (cast(FINANCIALTRANSACTION.DATE as date) >= @STARTDATE))) and
        ((@ENDDATE is null) or (((@USEDUEDATE = 1) and (cast(CHARGE.DUEDATE as date) < @ENDDATE)) or 
                                (((FINANCIALTRANSACTION.TYPECODE != 104) or (@USEDUEDATE = 0)) and (cast(FINANCIALTRANSACTION.DATE as date) <= @ENDDATE))));

  if (@TOTAL is null)
    set @TOTAL = 0;

  declare @TOTALAPPLIED money = 0;

  if (@INCLUDEAPPLICATIONS = 1)
  begin
    select
      @TOTALAPPLIED = sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))
    from dbo.FINANCIALTRANSACTION
    left outer join dbo.CHARGE
        on (CHARGE.ID = FINANCIALTRANSACTION.ID) and
           (@USEDUEDATE = 1)
    inner join dbo.FINANCIALTRANSACTIONLINEITEM
        on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    left outer join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
        on ((FINANCIALTRANSACTIONLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID) and
            (FINANCIALTRANSACTION.TYPECODE = 104)) or
            ((FINANCIALTRANSACTIONLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID) and
            (FINANCIALTRANSACTION.TYPECODE in (105, 106)))
    inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT
        on FINANCIALTRANSACTIONLINEITEM_APPLICATION.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_CHARGE
        on FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID
    inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_CHARGE
        on FINANCIALTRANSACTION_CHARGE.ID = FINANCIALTRANSACTIONLINEITEM_CHARGE.FINANCIALTRANSACTIONID
    where (FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPECODE) and
          (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID) and
          (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON IS NULL) and
          --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

          ((@STARTDATE is null) or (((@USEDUEDATE = 1) and (cast(CHARGE.DUEDATE as date) >= @STARTDATE)) or (cast(FINANCIALTRANSACTION.DATE as date) >= @STARTDATE))) and
          ((@ENDDATE is null) or (((@USEDUEDATE = 1) and (cast(CHARGE.DUEDATE as date) < @ENDDATE)) or 
                                  (((FINANCIALTRANSACTION.TYPECODE != 104) or (@USEDUEDATE = 0)) and (cast(FINANCIALTRANSACTION.DATE as date) <= @ENDDATE)))) and
          ((@IGNOREAPPLICATIONDATE = 1) or
            --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

            ((@STARTDATE is null) or 
            ((cast(FINANCIALTRANSACTION_CHARGE.DATE as date) >= cast(FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE as date)) and (cast(FINANCIALTRANSACTION_CHARGE.DATE as date) >= @STARTDATE)) or 
            ((cast(FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE as date) > cast(FINANCIALTRANSACTION_CHARGE.DATE as date)) and (cast(FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE as date) >= @STARTDATE))) and 
            ((@ENDDATE is null) or 
            ((cast(FINANCIALTRANSACTION_CHARGE.DATE as date) >= cast(FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE as date)) and (cast(FINANCIALTRANSACTION_CHARGE.DATE as date) <= @ENDDATE)) or
            ((cast(FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE as date) > cast(FINANCIALTRANSACTION_CHARGE.DATE as date)) and (cast(FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE as date) <= @ENDDATE))));

    if (@TOTALAPPLIED is null)
      set @TOTALAPPLIED = 0;
  end

  return @TOTAL - @TOTALAPPLIED;
end