USP_DATALIST_FAFTASKS

CREATE procedure dbo.USP_DATALIST_FAFTASKS
     (
         @CLIENTUSERSID   int,
         @EVENTID         uniqueidentifier,
         @ROLEID          int
     )
as
    set nocount on;

  DECLARE @TASKLIST table (
                        TASKID    uniqueidentifier,
              TASKNAME  varchar(50),
              TYPE      varchar(20)
                 )
    /***********************************************
        RoleID:
          1: Team Leader and household leader
          2. Company Leader
          3. Personal
          4. Team and household member
    ************************************************/

   DECLARE @CONSTITUENTID uniqueidentifier 
   DECLARE @TEAMID uniqueidentifier
   DECLARE @TEAMIMAGESTATUS bit
   DECLARE @PERSONALIMAGESTATUS bit
   DECLARE @TEAMSTORYSTATUS bit
   DECLARE @PERSONALSTORYSTATUS bit
   DECLARE @SOCIALNETWORKSTATUS bit
   DECLARE @REGISTRANTID uniqueidentifier
   DECLARE @SOCIALNETWORKPOSTCOUNT int
   DECLARE @ABCONTACTCOUNT int
   --DECLARE @TEAMLEADERS int 

   DECLARE @TEAMMEMBERS int
   DECLARE @PREVIOUSEVENT uniqueidentifier  
   DECLARE @EVENTSTATUS bit -- 1:active, 0:not active  

   DECLARE @MONEYRAISED money 

   SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)
   SELECT @TEAMID = TEAMID, @REGISTRANTID = RegistrantID 
             from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)

   -- check event status

   IF EXISTS (Select 1 from dbo.EVENT (NOLOCK) WHERE ID = @EVENTID AND ENDDATE IS NOT NULL AND ENDDATE < getdate() AND DATEADD(dd,7,ENDDATE) > getdate())  
        SET @EVENTSTATUS = 0  
   Else
        SET @EVENTSTATUS = 1

   --check image status          

   IF EXISTS (Select 1 from dbo.STORY (NOLOCK) WHERE EVENTID = @EVENTID AND TEAMFUNDRAISINGTEAMID = @TEAMID AND FAFIMAGESID IS NOT NULL)
       SET @TEAMIMAGESTATUS = 0
   ELSE 
       SET @TEAMIMAGESTATUS = 1

   IF EXISTS (Select 1 from dbo.STORY (NOLOCK) WHERE EVENTID = @EVENTID AND REGISTRANTID = @REGISTRANTID AND FAFIMAGESID IS NOT NULL)
       SET @PERSONALIMAGESTATUS = 0
   ELSE 
       SET @PERSONALIMAGESTATUS = 1

   -- check story status

   IF EXISTS (Select 1 from dbo.STORY (NOLOCK) WHERE EVENTID = @EVENTID AND TEAMFUNDRAISINGTEAMID = @TEAMID AND ISNULL(STORYTEXT,'') > '' AND isnull(STORYTEXT,'') not in (select STORYTEXT from dbo.STORY where EVENTID=@EVENTID and TEAMFUNDRAISINGTEAMID is null AND TYPECODE <> 0) AND isnull(STORYTEXT,'') <> ('Thanks for your support! Your contribution is greatly appreciated.'))
       SET @TEAMSTORYSTATUS = 0
   ELSE 
       SET @TEAMSTORYSTATUS = 1

  IF EXISTS (Select 1 from dbo.STORY (NOLOCK) WHERE EVENTID = @EVENTID AND REGISTRANTID = @REGISTRANTID AND ISNULL(STORYTEXT,'') > '' AND isnull(STORYTEXT,'') <> isnull((select STORYTEXT from dbo.STORY where EVENTID=@EVENTID and REGISTRANTID is null AND TYPECODE=0),'') AND isnull(STORYTEXT,'') <> ('Thanks for your support! Your contribution is greatly appreciated.'))
        SET @PERSONALSTORYSTATUS = 0
   ELSE 
        SET @PERSONALSTORYSTATUS = 1

   --check social status

   SELECT @SOCIALNETWORKPOSTCOUNT = COUNT(ID) FROM dbo.FAFEVENTSOCIALNETWORKPOST (NOLOCK) where REGISTRANTID = @REGISTRANTID
   IF @SOCIALNETWORKPOSTCOUNT > 0
       SET @SOCIALNETWORKSTATUS = 0
   ELSE
       SET @SOCIALNETWORKSTATUS = 1

   --SELECT @TEAMLEADERS = count(*) from dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID,@EVENTID)  

   SELECT @TEAMMEMBERS = count(ID) from dbo.TEAMFUNDRAISINGTEAMMEMBER where TEAMFUNDRAISINGTEAMID = @TEAMID

   SELECT @ABCONTACTCOUNT = count(ID) from AddressBookFAF Where CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID)) and CONSTITUENTID is null

   -- get team's goal and raised

   SELECT @MONEYRAISED = dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(@TEAMID, @EVENTID)  

   DECLARE @ACKNOWLEDGEGIFT table (  
               CONSTITUENTID uniqueidentifier,  
               DONATIONDATE datetime,  
               DONATIONAMOUNT money,  
               ISACKNOWLEDGED bit,  
               REVENUEID uniqueidentifier,  
               REVENUESPLITID uniqueidentifier,  
               TYPE varchar(20),  
               NAME varchar(250)  
         )  

   Insert into @ACKNOWLEDGEGIFT  
             exec USP_DATALIST_FAF_GIFT_ACKNOWLEDGEMENT_TEAM @EVENTID,@TEAMID,@CLIENTUSERSID  

   -- load tasks based on the role

   If @ROLEID = 1
      Begin
            INSERT INTO @TASKLIST
          Select ID, Name, Type From FAFTASK 
            Where TypeCode in (1, 3) Order By TypeCode
      End
   Else If @ROLEID = 2
      Begin
            INSERT INTO @TASKLIST
          Select ID, Name, Type From FAFTASK 
            Where TypeCode in (2, 3) Order By TypeCode
      End
   Else 
      Begin
            INSERT INTO @TASKLIST
          Select ID, Name, Type From FAFTASK 
            Where TypeCode =3 Order By TypeCode
      End

   -- for team and family member

   If @ROLEID = 4 Begin
          INSERT INTO @TASKLIST
            Select ID, Name, Type From FAFTASK Where ID = '4381BC96-7888-4F63-A022-D443AFD43730'
      End

 -- check for business logic to make sure that task is valid 

   if @TEAMIMAGESTATUS = 0 Begin
          Delete @TASKLIST Where TaskName in ('Upload a team photo', 'Upload a company photo')
      End 

   if @PERSONALIMAGESTATUS = 0 Begin
          Delete @TASKLIST Where TaskName = 'Upload a personal photo'
      End 

   IF @TEAMSTORYSTATUS = 0 Begin
          Delete @TASKLIST Where TaskName in ('Write your company story', 'Write your team story')
      End

   IF @PERSONALSTORYSTATUS = 0 Begin
          Delete @TASKLIST Where TaskName = 'Write your personal story'
      End

   if @SOCIALNETWORKSTATUS = 0 Begin
          Delete @TASKLIST Where TaskName = 'Post to social networks'
      End

   if @ABCONTACTCOUNT > 0 Begin
          Delete @TASKLIST Where TaskName = 'Add contacts'
      End
   Else Begin
          Delete @TASKLIST Where TaskName = 'Follow up - ask again to donate'
          Delete @TASKLIST Where TaskName = 'Follow up - ask again to join'
      End

   --check Recruit team leaders

   Declare @NumParticipants   int
   Select @NumParticipants = COUNT(*) from dbo.UFN_FAF_COMPANYMEMBERLIST(@CONSTITUENTID, @EventID)   
   IF not EXISTS (SELECT 1 FROM dbo.TEAMEXTENSION (NOLOCK) where TEAMFUNDRAISINGTEAMID = @TEAMID and PARTICIPANTGOAL > 0 and PARTICIPANTGOAL > @NumParticipants)  
      Begin
         Delete @TASKLIST Where TaskName = 'Recruit team leaders'
      End

   IF not EXISTS (SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAM (NOLOCK)   
                      where ID = @TEAMID and GOAL > 0 and GOAL >= ISNULL(@MONEYRAISED,0))  
      Begin
          Delete @TASKLIST Where TaskName = 'Remind leaders to ask new donors'
          Delete @TASKLIST Where TaskName = 'Help team leaders reach their goal'
          Delete @TASKLIST Where TaskName = 'Remind members to ask new donors'
      End

   SELECT @PREVIOUSEVENT = PRIORYEAREVENTID from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID  
   IF @PREVIOUSEVENT IS NULL Begin
         Delete @TASKLIST Where TaskName = 'Remind leaders to ask prior event donors'
         Delete @TASKLIST Where TaskName = 'Invite prior event members to join your team'
         Delete @TASKLIST Where TaskName = 'Ask prior event donors to donate'
      End


   IF not EXISTS (select 1 from @ACKNOWLEDGEGIFT  WHERE ISACKNOWLEDGED = 0) or 
      not EXISTS (select 1 from @ACKNOWLEDGEGIFT) Begin
         Delete @TASKLIST Where TaskName = 'Thank company donors'
         Delete @TASKLIST Where TaskName = 'Thank team donors'
         Delete @TASKLIST Where TaskName = 'Congratulate members on gifts received'

      End

   IF Not EXISTS (Select 1 from dbo.TEAMEXTENSION (NOLOCK) Where TEAMFUNDRAISINGTEAMID = @TEAMID and TEAMMEMBERGOAL > 0 and TEAMMEMBERGOAL > @TEAMMEMBERS)
       Begin
           Delete @TASKLIST Where TaskName = 'Ask leaders to recruit more members'
       End

   If @EVENTSTATUS = 1 Begin
               Delete @TASKLIST Where TaskName = 'Thank your team leaders'
               Delete @TASKLIST Where TaskName = 'Thank your team members'
            End

   If @RoleID = 1 or @RoleID = 4   --team specific

      Begin
         if Not EXISTS (SELECT 1 FROM dbo.TEAMEXTENSION (NOLOCK)   
                            where TEAMFUNDRAISINGTEAMID = @TEAMID and TEAMMEMBERGOAL > 0 and TEAMMEMBERGOAL >= ISNULL(@TEAMMEMBERS,0))  
             Begin
                 Delete @TASKLIST Where TaskName = 'Ask members to recruit more members'
                 Delete @TASKLIST Where TaskName = 'Invite contacts to join your team'
             End

         DECLARE @TeamMemberGoalRaised table (    
              MEMBERNAME   nvarchar(100),    
              GOAL         money,
              RAISED       money
         )  

         Insert @TeamMemberGoalRaised
            Select  c.NAME, rge.FUNDRAISINGGOAL As Goal,
                    IsNull(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(rg.ID, @EventID), 0) As Raised
                   from REGISTRANT rg
                      inner join REGISTRANTEXTENSION rge on rg.ID = rge.REGISTRANTID
                      inner join CONSTITUENT c on c.ID = rg.CONSTITUENTID 
                      inner join TEAMFUNDRAISER tr on c.ID = tr.CONSTITUENTID
                      inner join TEAMFUNDRAISINGTEAMMEMBER tra on tra.TEAMFUNDRAISERID = tr.ID
                      inner join TEAMFUNDRAISINGTEAM trt on trt.ID = tra.TEAMFUNDRAISINGTEAMID
                   Where trt.ID = @TeamID

          if not exists(Select 1 from @TeamMemberGoalRaised where Goal > 0 and Goal > Raised)
               Delete @TASKLIST Where TaskName = 'Help members reach their goal'

      End

    -- personal tasks

     DECLARE @PersonalGoalRaised table (    
              GOAL         money,
              RAISED       money
         )  

      Insert into @PersonalGoalRaised
           Select re.FUNDRAISINGGOAL, IsNull(dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(rg.ID, @EventID), 0) As Raised 
            from Registrant rg
            Inner Join REGISTRANTEXTENSION re on rg.ID = re.RegistrantID
            where rg.CONSTITUENTID = @ConstituentID
            and rg.EVENTID = @EventID

     if not exists(Select 1 from @PersonalGoalRaised where Goal >= Raised) 
            Delete @TASKLIST Where TaskName = 'Ask contacts to donate' 

    DECLARE @PERSONALACKNOWLEDGEGIFT table (  
             DONORCONSTITUENTID uniqueidentifier,  
             DONATIONDATE datetime,  
             DONATIONAMOUNT money,  
             CONSTITUENTID uniqueidentifier,  
             ISACKNOWLEDGED bit,  
             REVENUEID uniqueidentifier,  
             REVENUESPLITID uniqueidentifier,  
             NAME varchar(250),  
            TRANSACTIONTYPE varchar(250)  
       ) 

   Insert into @PERSONALACKNOWLEDGEGIFT
           exec dbo.USP_DATALIST_FAF_GIFT_ACKNOWLEDGEMENT @CLIENTUSERSID,@EVENTID  

   IF Not EXISTS (select 1 from @PERSONALACKNOWLEDGEGIFT WHERE CONSTITUENTID = @CONSTITUENTID AND ISACKNOWLEDGED = 0)  
      Begin
          Delete @TASKLIST Where TaskName = 'Thank donors'
      End


   -- remove new message

   Delete @TASKLIST where TaskName = 'Send new message'

   -- final task load

   Select TASKID, TaskName, Type from @TASKLIST