USP_REPORT_CAMPAIGNRECOGNITIONCREDITINCURRENCY

CREATE procedure dbo.USP_REPORT_CAMPAIGNRECOGNITIONCREDITINCURRENCY
(
    @CAMPAIGNID uniqueidentifier,
    @GROUPBY tinyint,
    @GOALID uniqueidentifier,
    @RECOGNITIONTYPEID uniqueidentifier,
    @CURRENCYCODE tinyint = null --0 = Record base, (null, 1) = Organization

)
as begin

    --Retrieve campaign goal information we'll need later

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;
    declare @CAMPAIGNGOAL money;
    declare @PARAMGOALNAME varchar(100);
    declare @CAMPAIGNHIERARCHYID uniqueidentifier;
    select 
        @STARTDATE=STARTDATE,
        @ENDDATE=ENDDATE,
        @CAMPAIGNGOAL=AMOUNT,
        @PARAMGOALNAME=NAME,
        @CAMPAIGNHIERARCHYID = CAMPAIGNID
    from CAMPAIGNHIERARCHYGOAL
    where ID=@GOALID;

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

    --Retrieve the name of the type of recognition credits we are filtering by

    declare @PARAMRECOGNITIONTYPENAME nvarchar(100)
    if @RECOGNITIONTYPEID is null
    begin
        set @PARAMRECOGNITIONTYPENAME = 'All';
    end
    else
    begin
        select @PARAMRECOGNITIONTYPENAME = DESCRIPTION from REVENUERECOGNITIONTYPECODE where ID=@RECOGNITIONTYPEID;
    end

    declare @HIERARCHYPATH hierarchyid;
    select @HIERARCHYPATH = CAMPAIGN.HIERARCHYPATH from dbo.CAMPAIGN where CAMPAIGN.ID = @CAMPAIGNID;

    if coalesce(@GROUPBY, 0) = 0
    begin
        -- Handle no grouping

        with CAMPAIGNGOAL_CTE as (
            -- pull back campaign hierarchy goal amounts for the root campaign

            select
                CAMPAIGNHIERARCHYGOAL.CAMPAIGNID,
                case @CURRENCYCODE
                    when 0 then CAMPAIGNHIERARCHYGOAL.AMOUNT
                    else CAMPAIGNHIERARCHYGOAL.ORGANIZATIONAMOUNT
                end [AMOUNT]
            from
                dbo.CAMPAIGNHIERARCHYGOAL
            where
                CAMPAIGNHIERARCHYGOAL.ID = @GOALID

            union all

            -- pull back campaign goal amounts for subcampaigns

            select
                CAMPAIGNGOAL.CAMPAIGNID,
                case @CURRENCYCODE
                    when 0 then CAMPAIGNGOAL.AMOUNT
                    else CAMPAIGNGOAL.ORGANIZATIONAMOUNT
                end
            from
                dbo.CAMPAIGNGOAL
            where
                CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = @GOALID
        )
        select
            CAMPAIGN.NAME CAMPAIGNNAME,
            CAMPAIGNGOAL_CTE.AMOUNT [GOAL],
            TOTALRECEIVED.TOTALRECEIVED,
            TOTALEXPECTED.TOTALRECEIVED [TOTALEXPECTED],
            TOTALRECEIVED.TOTALRECEIVED as [OVERALLTOTALRECEIVED],
            TOTALEXPECTED.TOTALRECEIVED as [OVERALLTOTALEXPECTED],
            case when CAMPAIGN.ID=@CAMPAIGNID then 1 else 0 end [ROOT],
            '' as PERIOD,
            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
            @PARAMGOALNAME PARAMGOALNAME,
            @STARTDATE GOALSTARTDATE,
            @ENDDATE GOALENDDATE,
            @PARAMRECOGNITIONTYPENAME PARAMRECOGNITIONTYPENAME
        from dbo.CAMPAIGN
        left outer join
            CAMPAIGNGOAL_CTE on CAMPAIGN.ID = CAMPAIGNGOAL_CTE.CAMPAIGNID
        outer apply
            dbo.UFN_CAMPAIGN_GETTOTALRECOGNITIONRECEIVEDINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, @RECOGNITIONTYPEID, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) TOTALRECEIVED
        outer apply
            dbo.UFN_CAMPAIGN_GETTOTALPLEDGERECOGNITIONINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, @RECOGNITIONTYPEID, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) TOTALEXPECTED
        outer apply
            dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID)) CURRENCYPROPERTIES
        where
            (CAMPAIGN.ID = @CAMPAIGNID or CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = @HIERARCHYPATH)
        order by 
            ROOT desc, PERIOD;
    end
    else
    begin
        -- Handle grouping by quarter and year

        with CAMPAIGNGOAL_CTE as (
            -- pull back campaign hierarchy goal amounts for the root campaign

            select
                CAMPAIGNHIERARCHYGOAL.CAMPAIGNID,
                case @CURRENCYCODE
                    when 0 then CAMPAIGNHIERARCHYGOAL.AMOUNT
                    else CAMPAIGNHIERARCHYGOAL.ORGANIZATIONAMOUNT
                end [AMOUNT]
            from
                dbo.CAMPAIGNHIERARCHYGOAL
            where
                CAMPAIGNHIERARCHYGOAL.ID = @GOALID

            union all

            -- pull back campaign goal amounts for subcampaigns

            select
                CAMPAIGNGOAL.CAMPAIGNID,
                case @CURRENCYCODE
                    when 0 then CAMPAIGNGOAL.AMOUNT
                    else CAMPAIGNGOAL.ORGANIZATIONAMOUNT
                end
            from
                dbo.CAMPAIGNGOAL
            where
                CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID = @GOALID
        )
        select
            CAMPAIGN.NAME CAMPAIGNNAME,
            CAMPAIGNGOAL_CTE.AMOUNT [GOAL],
            TOTALRECEIVED.TOTALRECEIVED,
            TOTALEXPECTED.TOTALRECEIVED [TOTALEXPECTED],
            OVERALLTOTALRECEIVED.TOTALRECEIVED as [OVERALLTOTALRECEIVED],
            OVERALLTOTALEXPECTED.TOTALRECEIVED as [OVERALLTOTALEXPECTED],
            case when CAMPAIGN.ID=@CAMPAIGNID then 1 else 0 end [ROOT],
            case @GROUPBY 
                when 2 
                    then 'Q' + convert(nvarchar(10),PERIODS.SEQUENCE) + ', ' 
                        + convert(nvarchar(10),PERIODS.PERIODYEAR)
                when 3
                    then convert(nvarchar(10),PERIODS.PERIODYEAR)
                else '' end PERIOD,
            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
            @PARAMGOALNAME PARAMGOALNAME,
            @STARTDATE GOALSTARTDATE,
            @ENDDATE GOALENDDATE,
            @PARAMRECOGNITIONTYPENAME PARAMRECOGNITIONTYPENAME
        from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@STARTDATE, @ENDDATE, @GROUPBY) PERIODS
        cross join
            dbo.CAMPAIGN
        left outer join
            CAMPAIGNGOAL_CTE on CAMPAIGN.ID = CAMPAIGNGOAL_CTE.CAMPAIGNID
        outer apply
            dbo.UFN_CAMPAIGN_GETTOTALRECOGNITIONRECEIVEDINCURRENCY(CAMPAIGN.HIERARCHYPATH, PERIODS.STARTDATE, PERIODS.ENDDATE, @RECOGNITIONTYPEID, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) TOTALRECEIVED
        outer apply
            dbo.UFN_CAMPAIGN_GETTOTALPLEDGERECOGNITIONINCURRENCY(CAMPAIGN.HIERARCHYPATH, PERIODS.STARTDATE, PERIODS.ENDDATE, @RECOGNITIONTYPEID, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) TOTALEXPECTED
        outer apply
            dbo.UFN_CAMPAIGN_GETTOTALRECOGNITIONRECEIVEDINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, @RECOGNITIONTYPEID, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) OVERALLTOTALRECEIVED
        outer apply
            dbo.UFN_CAMPAIGN_GETTOTALPLEDGERECOGNITIONINCURRENCY(CAMPAIGN.HIERARCHYPATH, @STARTDATE, @ENDDATE, @RECOGNITIONTYPEID, coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID),@ORGANIZATIONCURRENCYID) OVERALLTOTALEXPECTED
        outer apply
            dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, CAMPAIGN.BASECURRENCYID)) CURRENCYPROPERTIES
        where
            (CAMPAIGN.ID = @CAMPAIGNID or CAMPAIGN.HIERARCHYPATH.GetAncestor(1) = @HIERARCHYPATH)
        order by 
            ROOT desc, PERIODYEAR, PERIOD;
    end

end