USP_DATALIST_BENEFITSLIST

CREATE procedure dbo.USP_DATALIST_BENEFITSLIST(
    @DATEFROM datetime,
    @DATETO   datetime,
    @GROUPBY integer,
    @CURRENCYCODE tinyint, 
    @CURRENTAPPUSERID uniqueidentifier = null
    )
as
    set nocount on;
    declare @ISADMIN bit;
    declare @APPUSER_IN_NONRACROLE bit;
    declare @APPUSER_IN_NOSECGROUPROLE bit;
    declare @APPUSER_IN_NONSITEROLE bit;
    declare @APPUSER_IN_NOSITEROLE bit;

    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
    set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

    set @DATEFROM = dbo.UFN_DATE_GETEARLIESTTIME(@DATEFROM);
    set @DATETO = dbo.UFN_DATE_GETLATESTTIME(@DATETO);

    declare @SELECTEDCURRENCYID uniqueidentifier;
    if coalesce(@CURRENCYCODE, 1) = 1
    begin
        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    end

    set @CURRENCYCODE = coalesce(@CURRENCYCODE, 0);

    with BENEFITS_CTE as (
        select 
            NF.NAME as CONSTITUENTNAME,
            CONSTITUENT.ID as CONSTITUENTID,
            CONSTITUENT.KEYNAME as CONSTITUENTKEYNAME,
            BENEFIT.NAME as BENEFITNAME,
            BENEFIT.DESCRIPTION as BENEFITDESCRIPTION,
            BENEFIT.ID as BENEFITID,
            REVENUEBENEFIT.QUANTITY as QUANTITY,
            case  
                when (@CURRENCYCODE = 0 or REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then REVENUEBENEFIT.UNITVALUE 
                else dbo.UFN_CURRENCY_CONVERT(REVENUEBENEFIT.UNITVALUE, REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID)
            end as UNITVALUE,
            case
                when BENEFIT.USEPERCENT = 0 then
                    case
                        when (@CURRENCYCODE = 0 or REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then (REVENUEBENEFIT.QUANTITY*REVENUEBENEFIT.UNITVALUE) 
                        else (REVENUEBENEFIT.QUANTITY*dbo.UFN_CURRENCY_CONVERT(REVENUEBENEFIT.UNITVALUE, REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID))    
                    end
                when BENEFIT.USEPERCENT = 1 then --JamesWill WI174128 2011-11-18 Calculate a total for percent benefits.

                    case 
                        when (@CURRENCYCODE = 0 or REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then ((REVENUEBENEFIT.VALUEPERCENT/100) * REVENUEBENEFIT.PERCENTAPPLICABLEAMOUNT)
                        else ((REVENUEBENEFIT.VALUEPERCENT/100) * dbo.UFN_CURRENCY_CONVERT(REVENUEBENEFIT.PERCENTAPPLICABLEAMOUNT, REVENUEBENEFIT.ORGANIZATIONEXCHANGERATEID))
                    end
            end as TOTAL,
            cast(REVENUE.DATE as datetime) as DATE,
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
            CURRENCYPROPERTIES.DECIMALDIGITS,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
            REVENUEBENEFIT.BASECURRENCYID
        from
            dbo.REVENUEBENEFIT
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUEBENEFIT.REVENUEID = REVENUE.ID
            inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
            inner join dbo.BENEFIT on REVENUEBENEFIT.BENEFITID = BENEFIT.ID
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, REVENUEBENEFIT.BASECURRENCYID)) as CURRENCYPROPERTIES
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        where
            REVENUE.DELETEDON is null
            and cast(REVENUE.DATE as datetime) >= @DATEFROM
            and cast(REVENUE.DATE as datetime) <= @DATETO
            and (@ISADMIN = 1 or 
                    (@APPUSER_IN_NONRACROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                    and
                    (@APPUSER_IN_NONSITEROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                )
        union

        select 
            NF.NAME as CONSTITUENTNAME,
            CONSTITUENT.ID as CONSTITUENTID,
            CONSTITUENT.KEYNAME as CONSTITUENTKEYNAME,
            BENEFIT.NAME as BENEFITNAME,
            BENEFIT.DESCRIPTION as BENEFITDESCRIPTION,
            BENEFIT.ID as BENEFITID,
            REGISTRANTBENEFIT.QUANTITY as QUANTITY,
            case
                when (@CURRENCYCODE = 0 or REGISTRANTBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then REGISTRANTBENEFIT.UNITVALUE 
                else dbo.UFN_CURRENCY_CONVERT(REGISTRANTBENEFIT.UNITVALUE, REGISTRANTBENEFIT.ORGANIZATIONEXCHANGERATEID)
            end as UNITVALUE,
            case
                when (@CURRENCYCODE = 0 or REGISTRANTBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then (REGISTRANTBENEFIT.QUANTITY*REGISTRANTBENEFIT.UNITVALUE) 
                else (REGISTRANTBENEFIT.QUANTITY*dbo.UFN_CURRENCY_CONVERT(REGISTRANTBENEFIT.UNITVALUE, REGISTRANTBENEFIT.ORGANIZATIONEXCHANGERATEID))    
            end as TOTAL,
            EVENT.STARTDATE as DATE,
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
            CURRENCYPROPERTIES.DECIMALDIGITS,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
            REGISTRANTBENEFIT.BASECURRENCYID
        from
            dbo.REGISTRANTBENEFIT
            inner join dbo.REGISTRANT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
            inner join dbo.CONSTITUENT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
            inner join dbo.[EVENT] on REGISTRANT.EVENTID = EVENT.ID
            inner join dbo.BENEFIT on REGISTRANTBENEFIT.BENEFITID = BENEFIT.ID
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, REGISTRANTBENEFIT.BASECURRENCYID)) as CURRENCYPROPERTIES
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        where
            EVENT.STARTDATE >= @DATEFROM
            and
            EVENT.STARTDATE <= @DATETO
            and (@ISADMIN = 1 or 
                    (@APPUSER_IN_NONRACROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                    and
                    (@APPUSER_IN_NONSITEROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                )
        union

        select 
            NF.NAME as CONSTITUENTNAME,
            CONSTITUENT.ID as CONSTITUENTID,
            CONSTITUENT.KEYNAME as CONSTITUENTKEYNAME,
            BENEFIT.NAME as BENEFITNAME,
            BENEFIT.DESCRIPTION as BENEFITDESCRIPTION,
            BENEFIT.ID as BENEFITID,
            CONSTITUENTRECOGNITIONBENEFIT.QUANTITY as QUANTITY,
            case
                when (@CURRENCYCODE = 0 or CONSTITUENTRECOGNITIONBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE 
                else dbo.UFN_CURRENCY_CONVERT(CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE, CONSTITUENTRECOGNITIONBENEFIT.ORGANIZATIONEXCHANGERATEID)
            end as UNITVALUE,
            case
                when (@CURRENCYCODE = 0 or CONSTITUENTRECOGNITIONBENEFIT.ORGANIZATIONEXCHANGERATEID is null) then (CONSTITUENTRECOGNITIONBENEFIT.QUANTITY*CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE) 
                else (CONSTITUENTRECOGNITIONBENEFIT.QUANTITY*dbo.UFN_CURRENCY_CONVERT(CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE, CONSTITUENTRECOGNITIONBENEFIT.ORGANIZATIONEXCHANGERATEID))    
            end as TOTAL,
            CONSTITUENTRECOGNITION.JOINDATE as DATE,
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
            CURRENCYPROPERTIES.DECIMALDIGITS,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
            CONSTITUENTRECOGNITIONBENEFIT.BASECURRENCYID
        from
            dbo.CONSTITUENTRECOGNITIONBENEFIT
            inner join dbo.BENEFIT on CONSTITUENTRECOGNITIONBENEFIT.BENEFITID = BENEFIT.ID
            inner join dbo.CONSTITUENTRECOGNITION on CONSTITUENTRECOGNITIONBENEFIT.CONSTITUENTRECOGNITIONID = CONSTITUENTRECOGNITION.ID
            inner join dbo.CONSTITUENT on CONSTITUENTRECOGNITION.CONSTITUENTID = CONSTITUENT.ID
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, CONSTITUENTRECOGNITIONBENEFIT.BASECURRENCYID)) as CURRENCYPROPERTIES
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
        where
            CONSTITUENTRECOGNITION.JOINDATE >= @DATEFROM
            and
            CONSTITUENTRECOGNITION.JOINDATE <= @DATETO
            and (@ISADMIN = 1 or 
                    (@APPUSER_IN_NONRACROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                    and
                    (@APPUSER_IN_NONSITEROLE = 1 or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                )
    )
    select
        CONSTITUENTNAME,
        CONSTITUENTID,
        CONSTITUENTKEYNAME,
        BENEFITNAME,
        BENEFITDESCRIPTION,
        BENEFITID,
        sum(QUANTITY) as QUANTITY,
        UNITVALUE,
        sum(TOTAL) as TOTAL,
        DATE,
        CURRENCYSYMBOL,
        ISOCURRENCYCODE,
        DECIMALDIGITS,
        CURRENCYSYMBOLDISPLAYSETTINGCODE,
        BASECURRENCYID
    from BENEFITS_CTE
    group by
        CONSTITUENTNAME,
        CONSTITUENTID,
        CONSTITUENTKEYNAME,
        BENEFITNAME,
        BENEFITDESCRIPTION,
        BENEFITID,
        UNITVALUE,
        DATE,
        CURRENCYSYMBOL,
        ISOCURRENCYCODE,
        DECIMALDIGITS,
        CURRENCYSYMBOLDISPLAYSETTINGCODE,
        BASECURRENCYID