USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGIVINGBYDESIGNATION

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGIVINGBYDESIGNATION
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @SELECTEDCURRENCYID uniqueidentifier = null
                    )
                    as
                        set nocount on;

                        declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                        declare @CURRENCYROUNDINGTYPECODE tinyint;
                        declare @CURRENCYDECIMALDIGITS tinyint = 0;
                        select
                            @CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                            @CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
                        from
                            dbo.CURRENCY
                        where
                            CURRENCY.ID = @SELECTEDCURRENCYID;

                        if @ISVISIBLE = 1
                        begin
                            declare @ISGROUP bit
                            select @ISGROUP = ISGROUP
                            from dbo.CONSTITUENT where ID = @CONSTITUENTID;

                            declare @CURRENTDATE datetime;
                            set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                            if @ISGROUP = 1
                            begin
                                select
                                    NAME,
                                    sum(CONSTITUENTAMOUNT) as CONSTITUENTAMOUNT,
                                    sum(CONSTITUENTNUMBER) as CONSTITUENTNUMBER,
                                    sum(MEMBERAMOUNT) as MEMBERAMOUNT,
                                    sum(MEMBERNUMBER) as MEMBERNUMBER,
                                    0 as ASSOCIATEDGROUPSAMOUNT,
                                    0 as ASSOCIATEDGROUPSNUMBER
                                from
                                    (select 
                                        D.ID,
                                        D.NAME,
                                        sum(RS.AMOUNTINCURRENCY) as CONSTITUENTAMOUNT,
                                        count(R.ID) as CONSTITUENTNUMBER,
                                        0 as MEMBERAMOUNT,
                                        0 as MEMBERNUMBER
                                    from dbo.REVENUE R
                                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) RS on R.ID = RS.REVENUEID
                                    inner join dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
                                    where 
                                        R.CONSTITUENTID = @CONSTITUENTID and
                                        (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                        R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3)) and --Payment (Gift or Recurring gift payment)

                                        dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATIONID,@CURRENTAPPUSERID) = 1
                                    group by D.ID, D.NAME

                                    union all

                                    select 
                                        D.ID,
                                        D.NAME,
                                        0 as CONSTITUENTAMOUNT,
                                        0 as CONSTITUENTNUMBER,
                                        sum(RS.AMOUNTINCURRENCY) as MEMBERAMOUNT,
                                        count(R.ID) as MEMBERNUMBER    
                                    from dbo.REVENUE R
                                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) RS on R.ID = RS.REVENUEID
                                    inner join dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
                                    where 
                                        R.CONSTITUENTID in 
                                        (
                                            select MEMBERID 
                                            from dbo.GROUPMEMBER GM 
                                            left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID 
                                            where 
                                                GROUPID = @CONSTITUENTID and
                                                ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                                                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                                        ) and
                                        (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                        R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3)) and --Payment (Gift or Recurring gift payment)

                                        dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATIONID,@CURRENTAPPUSERID) = 1
                                    group by D.ID, D.NAME) as G
                                group by ID, NAME
                                order by NAME
                            end
                            else
                            begin
                                select
                                    NAME,
                                    sum(CONSTITUENTAMOUNT) as CONSTITUENTAMOUNT,
                                    sum(CONSTITUENTNUMBER) as CONSTITUENTNUMBER,
                                    0 as MEMBERAMOUNT,
                                    0 as MEMBERNUMBER,
                                    sum(ASSOCIATEDGROUPSAMOUNT) as ASSOCIATEDGROUPSAMOUNT,
                                    sum(ASSOCIATEDGROUPSNUMBER) as ASSOCIATEDGROUPSNUMBER
                                from
                                    (select 
                                        D.ID,
                                        D.NAME,
                                        sum(RS.AMOUNTINCURRENCY) as CONSTITUENTAMOUNT,
                                        count(R.ID) as CONSTITUENTNUMBER,
                                        0 as ASSOCIATEDGROUPSAMOUNT,
                                        0 as ASSOCIATEDGROUPSNUMBER
                                    from dbo.REVENUE R
                                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) RS on R.ID = RS.REVENUEID
                                    inner join dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
                                    where 
                                        R.CONSTITUENTID = @CONSTITUENTID and
                                        (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                        R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3)) and --Payment (Gift or Recurring gift payment)

                                        dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATIONID,@CURRENTAPPUSERID) = 1
                                    group by D.ID, D.NAME

                                    union all

                                    select 
                                        D.ID,
                                        D.NAME,
                                        0 as CONSTITUENTAMOUNT,
                                        0 as CONSTITUENTNUMBER,
                                        sum(RS.AMOUNTINCURRENCY) as ASSOCIATEDGROUPSNUMBER,
                                        count(R.ID) as ASSOCIATEDGROUPSNUMBER    
                                    from dbo.REVENUE R
                                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) RS on R.ID = RS.REVENUEID
                                    inner join dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
                                    where 
                                        R.CONSTITUENTID in 
                                        (
                                            select GROUPID 
                                            from dbo.GROUPMEMBER GM 
                                            left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID 
                                            where 
                                                MEMBERID = @CONSTITUENTID and
                                                ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                                                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))        
                                        ) and
                                        (R.TRANSACTIONTYPECODE = 1 or --Pledge

                                        R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3)) and --Payment (Gift or Recurring gift payment)

                                        dbo.UFN_DESIGNATION_USERHASSITEACCESS(DESIGNATIONID,@CURRENTAPPUSERID) = 1
                                    group by D.ID, D.NAME) as G
                                group by ID, NAME
                                order by NAME
                            end                            
                        end