UFN_REGISTRANT_GETFUNDRAISINGAMOUNTRAISED

create function dbo.UFN_REGISTRANT_GETFUNDRAISINGAMOUNTRAISED
   (  
    @REGISTRANTID uniqueidentifier,
    @EVENTID uniqueidentifier  
   )   
   returns money   
   with execute as caller  
   as  
   begin  
       --Tu Phan 01/20/2010 my bday :)    

       --Input Parameter: REGISTRANTID and EVENTID

       --Return the AMOUNT RAISED for Individual/Team/Company 


       ----Info and build--

        DECLARE @TEAMID uniqueidentifier
        DECLARE @HOSTCONSTITUENTID uniqueidentifier
        DECLARE @AmountRaised money
        DECLARE @RecordType nvarchar(100)

        --May not need all of these var

        DECLARE @TOTALDONATION money  
        DECLARE @REGISTRANTRAISED money  
        DECLARE @TEAMRAISED money  
        DECLARE @COMPANIESRAISED money
        DECLARE @TEAMDONATION money  
        DECLARE @REGISTRANTDONATION money  
        DECLARE @GOAL money  

        SET @HOSTCONSTITUENTID = (SELECT REGISTRANT.CONSTITUENTID FROM dbo.REGISTRANT WHERE REGISTRANT.ID=@REGISTRANTID)
        SET @AmountRaised = 0

        SET @RecordType = dbo.UFN_FAFEVENTREGISTRANT_GETREGISTRANTRECORDTYPE(@REGISTRANTID)

        IF (@RecordType = 'A-Individual Participants' AND @REGISTRANTID IS NOT NULL)
            BEGIN
                SELECT @GOAL = FUNDRAISINGGOAL from dbo.REGISTRANTEXTENSION WHERE REGISTRANTID = @REGISTRANTID  

                SELECT @REGISTRANTDONATION = ISNULL(VOLUNTARYDONATION,0)   
                FROM dbo.REGISTRANTDONATION WHERE REGISTRANTID = @REGISTRANTID  

                SELECT @REGISTRANTRAISED = SUM(ISNULL(DONATIONAMOUNT,0))  --Amount Raised for Individual Participant

                FROM dbo.EVENTDONOR WHERE REGISTRANTID = @REGISTRANTID  

                SET @TOTALDONATION = ISNULL(@REGISTRANTDONATION,0) + ISNULL(@REGISTRANTRAISED,0)  

                SET @AmountRaised = coalesce(@REGISTRANTRAISED,0)
            END
        ELSE IF (@RecordType = 'B-Teams' AND @REGISTRANTID IS NOT NULL)
            BEGIN
                --Identifies this is the Host record for team, else return 0 for team members

                IF @HOSTCONSTITUENTID IS NOT NULL
                    BEGIN
                        SET @TEAMID=(SELECT ID AS TEAMFUNDRAISEID FROM TEAMFUNDRAISER WHERE CONSTITUENTID=@HOSTCONSTITUENTID) 
                        IF @TEAMID IS NOT NULL --1-is he/she is a team/company record (host/team leader record) ?

                            BEGIN
                                --May not need all of these but may be useful in the future

                                    SELECT @GOAL = GOAL from dbo.TEAMFUNDRAISINGTEAM WHERE ID = @TEAMID  

                                    SELECT @REGISTRANTDONATION = ISNULL(RD.VOLUNTARYDONATION,0)   
                                    FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM  
                                    INNER JOIN dbo.TEAMFUNDRAISER TF   
                                    ON TM.TEAMFUNDRAISERID = TF.ID  
                                    INNER JOIN dbo.REGISTRANT R  
                                    ON TF.CONSTITUENTID = R.CONSTITUENTID  
                                    INNER JOIN dbo.REGISTRANTDONATION RD  
                                    ON RD.REGISTRANTID = R.ID  
                                    WHERE TM.TEAMFUNDRAISINGTEAMID = @TEAMID  
                                    AND R.EVENTID = @EVENTID  

                                    SELECT @TEAMRAISED = SUM(ISNULL(ED.DONATIONAMOUNT,0))  --AMOUNTRAISED FOR TEAM

                                    FROM dbo.TEAMFUNDRAISINGTEAMMEMBER TM  
                                    INNER JOIN dbo.TEAMFUNDRAISER TF   
                                    ON TM.TEAMFUNDRAISERID = TF.ID  
                                    INNER JOIN dbo.REGISTRANT R  
                                    ON TF.CONSTITUENTID = R.CONSTITUENTID  
                                    INNER JOIN dbo.EVENTDONOR ED  
                                    ON ED.REGISTRANTID = R.ID  
                                    WHERE TM.TEAMFUNDRAISINGTEAMID = @TEAMID  
                                    AND R.EVENTID = @EVENTID  

                                    SELECT @TEAMDONATION = SUM(ISNULL(DONATIONAMOUNT,0))  
                                    FROM dbo.EVENTDONOR WHERE EVENTID = @EVENTID AND TEAMFUNDRAISINGTEAMID = @TEAMID AND REGISTRANTID IS NULL                                    
                                    SET @TOTALDONATION = ISNULL(@REGISTRANTDONATION,0) + ISNULL(@TEAMRAISED,0) + ISNULL(@TEAMDONATION,0)  

                                    SET @AmountRaised = coalesce(@TEAMRAISED,0)                                
                            END
                        ELSE
                            SET @AmountRaised = 0    
                    END
                ELSE
                    SET @AmountRaised = 0    
            END    
        ELSE IF (@RecordType = 'C-Companies' AND @REGISTRANTID IS NOT NULL)  --Amount Raised for Company till not in correct DB design yet include in Frontend BBNC

            BEGIN
                SELECT @COMPANIESRAISED = SUM(ISNULL(DONATIONAMOUNT,0))  --For now the Amount Raised is calculated similar the Individual Participants

                FROM dbo.EVENTDONOR WHERE REGISTRANTID = @REGISTRANTID  

                SET @AmountRaised = coalesce(@COMPANIESRAISED,0)            
            END

        IF @AmountRaised IS NULL 
            SET    @AmountRaised = 0

    return @AmountRaised
   end