USP_DATAFORMTEMPLATE_VIEW_FUNDRAISERSUMMARY

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@OPPORTUNITYCOUNT	int	INOUT
@OPPORTUNITYAMOUNT	money	INOUT
@POSITIONTITLE	nvarchar(100)	INOUT
@SITE	nvarchar(100)	INOUT
@STARTDATE	datetime	INOUT
@REQUESTEDAMOUNT	money	INOUT
@CURRENCYID	uniqueidentifier	INOUT
@QUALIFIEDOPPORTUNITYCOUNT	int	INOUT
@QUALIFIEDOPPORTUNITYAMOUNT	money	INOUT
@UNQUALIFIEDOPPORTUNITYCOUNT	int	INOUT
@UNQUALIFIEDOPPORTUNITYAMOUNT	money	INOUT
@RESPONSEPENDINGOPPORTUNITYCOUNT	int	INOUT
@RESPONSEPENDINGOPPORTUNITYAMOUNT	money	INOUT
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FUNDRAISERSUMMARY
(
    @ID uniqueidentifier,     
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,  
    @OPPORTUNITYCOUNT int = null output,
    @OPPORTUNITYAMOUNT money = null output,
    @POSITIONTITLE nvarchar(100) = null output,
    @SITE nvarchar(100) = null output,
    @STARTDATE datetime = null output,
    @REQUESTEDAMOUNT money = null output,
    @CURRENCYID uniqueidentifier = null output,
    @QUALIFIEDOPPORTUNITYCOUNT int = null output,
    @QUALIFIEDOPPORTUNITYAMOUNT money = null output,
    @UNQUALIFIEDOPPORTUNITYCOUNT int = null output,  
    @UNQUALIFIEDOPPORTUNITYAMOUNT money = null output,
    @RESPONSEPENDINGOPPORTUNITYCOUNT int = null output,  
    @RESPONSEPENDINGOPPORTUNITYAMOUNT money = null output
) as begin

    set nocount on;

    declare @CALCULATEPLANDETAIL bit;
    set @CALCULATEPLANDETAIL = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') | dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0a07aa22-ed88-42b7-8c55-d80df4f4cea2') | dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('ab25cd89-7288-4605-b0ea-48961960ec06');

    set @DATALOADED = 0;

    select
        @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(APPUSER.ID)
    from
        dbo.APPUSER
    where
        APPUSER.CONSTITUENTID = @ID;

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

    select
        @DATALOADED = 1,
        @OPPORTUNITYCOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    count(*)
                from
                    dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE in (1,2 )
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
                )
            else
                0
            end
        ),
        @OPPORTUNITYAMOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
                from
                     dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE in (1, 2)
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
            )
            else
                0
            end
        ),
        @REQUESTEDAMOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    coalesce(sum(coalesce(dbo.UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY(FUNDINGREQUEST.ID, @CURRENCYID),0)),0)
                from dbo.FUNDINGREQUEST
                where PRIMARYMANAGERID = CONSTITUENT.ID
                    or SECONDARYMANAGERID = CONSTITUENT.ID
                    or CONSTITUENT.ID in (
                        select SPONSORID
                        from FUNDINGREQUESTSPONSOR
                        where FUNDINGREQUESTID = FUNDINGREQUEST.ID
                    )
                )
            else
                0
            end
        ),
        @QUALIFIEDOPPORTUNITYCOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    count(*)
                from
                    dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE = 1 
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
                )
            else
                0
            end
        ),
        @QUALIFIEDOPPORTUNITYAMOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
                from
                     dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE = 1
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
            )
            else
                0
            end
            ),
        @UNQUALIFIEDOPPORTUNITYCOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    count(*)
                from
                    dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE = 0 
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
                )
            else
                0
            end
        ),
        @UNQUALIFIEDOPPORTUNITYAMOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
                from
                     dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE = 0
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
            )
            else
                0
            end
            ),
        @RESPONSEPENDINGOPPORTUNITYCOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    count(*)
                from
                    dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE = 2 
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
                )
            else
                0
            end
        ),
        @RESPONSEPENDINGOPPORTUNITYAMOUNT = (
            case @CALCULATEPLANDETAIL when 1 then (
                select
                    coalesce(sum(dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(O.ID, @CURRENCYID)),0)
                from
                     dbo.OPPORTUNITY O 
                where
                    O.STATUSCODE = 2
                    and exists (select 1 from dbo.V_OPPORTUNITYSOLICITOR OS where OS.FUNDRAISERID = CONSTITUENT.ID AND OS.OPPORTUNITYID = O.ID)
                    and dbo.UFN_PROSPECTPLAN_USERHASSITEACCESS(@CURRENTAPPUSERID, O.PROSPECTPLANID) = 1
            )
            else
                0
            end
        )
    from dbo.CONSTITUENT
    where
        CONSTITUENT.ID = @ID

    declare @TODAY date = getdate();

    select
        @STARTDATE = ORGANIZATIONPOSITIONHOLDER.DATEFROM,
        @POSITIONTITLE = ORGANIZATIONPOSITION.NAME,
        @SITE = SITE.NAME
    from
        dbo.ORGANIZATIONPOSITIONHOLDER
    inner join
        dbo.ORGANIZATIONPOSITION on ORGANIZATIONPOSITION.ID = ORGANIZATIONPOSITIONHOLDER.POSITIONID
    left join
        dbo.SITE on SITE.ID = ORGANIZATIONPOSITION.SITEID
    where
        ORGANIZATIONPOSITIONHOLDER.CONSTITUENTID = @ID and
        (@TODAY between ORGANIZATIONPOSITIONHOLDER.DATEFROM and coalesce(ORGANIZATIONPOSITIONHOLDER.DATETO, @TODAY));  

    return 0
    end