USP_REPORT_EVENTDESIGNATION

CREATE procedure dbo.USP_REPORT_EVENTDESIGNATION
(
    @EVENTID uniqueidentifier,
    @CURRENCYCODE smallint = null,
    @INCLUDESUBEVENTS bit = 0,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null
)
as
begin
    declare @SELECTEDCURRENCYID uniqueidentifier = null;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

    if @CURRENCYCODE = 0
      select @SELECTEDCURRENCYID = EVENT.BASECURRENCYID
      from dbo.EVENT
      where EVENT.ID = @EVENTID
    else
        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    select
        @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
    from
        dbo.CURRENCY
    where
        CURRENCY.ID = @SELECTEDCURRENCYID

    declare @EVENTS table
    (
        ID uniqueidentifier
    )
    if @INCLUDESUBEVENTS = 0
    begin
        insert into @EVENTS
        select @EVENTID
    end
    else
    begin
        insert into @EVENTS
            select RELATEDEVENT.ID
            from dbo.EVENTHIERARCHY as RELATEDEVENT
                inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
            where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1
    end;

declare @BASETABLE table (DESIGNATIONID uniqueidentifier, REGISTRANTID uniqueidentifier, LINEITEMID uniqueidentifier);

insert into @BASETABLE (DESIGNATIONID, REGISTRANTID, LINEITEMID)
select
    DESIGNATION.ID,
    REGISTRANT.ID,
    FTLI.ID
from 
    dbo.EVENT
inner join
    dbo.EVENTDESIGNATION on EVENTDESIGNATION.EVENTID = EVENT.ID
inner join
    dbo.DESIGNATION on DESIGNATION.ID = EVENTDESIGNATION.DESIGNATIONID
left join
    dbo.REGISTRANT on REGISTRANT.EVENTID = EVENT.ID
left join 
    dbo.EVENTREGISTRANTPAYMENT ERP on ERP.REGISTRANTID = REGISTRANT.ID
left join
    dbo.REGISTRANTDESIGNATION RD on RD.REGISTRANTID = ERP.REGISTRANTID
left join
    (select FTLI.ID, EXT.DESIGNATIONID, FTLI.TRANSACTIONAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM FTLI inner join dbo.REVENUESPLIT_EXT EXT on FTLI.ID = EXT.ID) FTLI 
        on FTLI.ID = ERP.PAYMENTID and FTLI.DESIGNATIONID = DESIGNATION.ID
where
    EVENTDESIGNATION.EVENTID in (select ID from @EVENTS) and
    (((@STARTDATE is null) or (EVENT.STARTDATE = @STARTDATE)) and
    ((@ENDDATE is null) or (EVENT.ENDDATE = @ENDDATE)))

declare @AMOUNTTABLE table (DESIGNATIONID uniqueidentifier, REGCOUNT int, AMOUNT money);
declare @REFUNDTABLE table (DESIGNATIONID uniqueidentifier, AMOUNT money);

insert into @AMOUNTTABLE (DESIGNATIONID, REGCOUNT, AMOUNT)
select
    B.DESIGNATIONID,
    count(distinct B.REGISTRANTID),
    coalesce(sum(RS.AMOUNTINCURRENCY), 0)
from
    @BASETABLE as B
inner join 
    UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),2,2) as RS on RS.ID = B.LINEITEMID
group by B.DESIGNATIONID

insert into @REFUNDTABLE (DESIGNATIONID, AMOUNT)
select
    B.DESIGNATIONID,
    coalesce(sum(FTLI.TRANSACTIONAMOUNT), 0)
from
    @BASETABLE as B
inner join 
    dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = B.LINEITEMID
inner join
    dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
where
    FT.TYPECODE = 23
group by B.DESIGNATIONID


select
    AMOUNT.DESIGNATIONID as DESIGNATIONID,
    DESIGNATION.NAME as DESIGNATIONNAME,
    coalesce(AMOUNT.REGCOUNT, 0) as COUNT,
    coalesce(sum(AMOUNT.AMOUNT), 0) - coalesce(sum(REFUND.AMOUNT), 0) as AMOUNTRAISED,
    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
    CURRENCYPROPERTIES.CURRENCYSYMBOL,
    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
    CURRENCYPROPERTIES.DECIMALDIGITS
from
    @AMOUNTTABLE as AMOUNT
inner join 
    dbo.DESIGNATION on DESIGNATION.ID = AMOUNT.DESIGNATIONID    
left join
    @REFUNDTABLE as REFUND on REFUND.DESIGNATIONID = AMOUNT.DESIGNATIONID
outer apply 
    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
group by AMOUNT.DESIGNATIONID, DESIGNATION.NAME, AMOUNT.REGCOUNT, CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS

end