USP_DATALIST_FAFPERSONALTASKS

        CREATE procedure dbo.USP_DATALIST_FAFPERSONALTASKS
          (
             @CLIENTUSERSID int,
             @EVENTID uniqueidentifier,
             @ROLETYPE tinyint = 0 -- 0:Individual,  1:Team Leader,  2:Team Member,  3: Company Leader, 5:Household Leader, 6:Household Mmeber

          )
        as


          DECLARE  @CONSTITUENTID uniqueidentifier 
          SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

          DECLARE @MYADDRESSBOOK table (
                          GROUPNAME nvarchar(100),
                CATEGORYSTATUS nvarchar(100),
                          ADDRESSBOOKID uniqueidentifier null,
                          NAME varchar(256) null,
                          CONSTITUENTID uniqueidentifier null,
                          EMAILADDRESS varchar(200) null,
                REGISTRANTID uniqueidentifier null
                          )

           DECLARE @TASKLIST table (
                      TASKNAME nvarchar(100),
                      DISPLAYFLAG bit
                      )

           DECLARE @DONORLIST table(
            ID uniqueidentifier,
            DONORRETENTIONGOAL money,
            CURRENTDONORS int,
            PREVIOUSDONORS int)

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

           DECLARE @ABCOUNT int
           DECLARE @SOCIALNETWORKPOSTCOUNT int
           DECLARE @PREVIOUSDONORS int
           DECLARE @PREVIOUSTEAMMEMBERS int
           DECLARE @CURRENTDONORS int
           DECLARE @CONTACTS int
           DECLARE @TEAMMEMBERS int
           DECLARE @TEAMID uniqueidentifier
           DECLARE @REGISTRANTID uniqueidentifier
           DECLARE @TEAMMEMRETENTION decimal(18,2)
           DECLARE @DONORRETENTION decimal(18,2)
           DECLARE @PREVIOUSEVENT uniqueidentifier
           DECLARE @MONEYRAISED money
           DECLARE @EVENTSTATUS bit -- 1:active, 0:not active

           DECLARE @STORYSTATUS bit
           DECLARE @IMAGESTATUS bit
           DECLARE @SOCIALNETWORKSTATUS bit

           IF EXISTS (Select 1 from dbo.EVENT (NOLOCK) WHERE ID = @EVENTID AND ENDDATE IS NOT NULL AND ENDDATE < getdate())
           SET @EVENTSTATUS = 0
           ELSE SET @EVENTSTATUS = 1

           SELECT @PREVIOUSEVENT = PRIORYEAREVENTID from dbo.EVENTEXTENSION (NOLOCK) WHERE EVENTID = @EVENTID

           Insert into @MYADDRESSBOOK
           exec dbo.usp_datalist_fafaddressbookcontactsearch @CLIENTUSERSID,@EVENTID

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

           INSERT INTO @TASKLIST
           VALUES('Upload a personal photo',0)

           INSERT INTO @TASKLIST
           VALUES('Write your personal story',0)

           INSERT INTO @TASKLIST
           VALUES('Add contacts',0)

           INSERT INTO @TASKLIST
           VALUES('Post to social networks', 0)

           INSERT INTO @TASKLIST
           VALUES('Invite contacts to join your team',0)

           INSERT INTO @TASKLIST
           VALUES('Invite contacts to join your household',0)

           INSERT INTO @TASKLIST
           VALUES('Encourage past household members to join',0)

           INSERT INTO @TASKLIST
           VALUES('Ask contacts to donate',0)

           INSERT INTO @TASKLIST
           VALUES('Solicit previous donors',0)

           INSERT INTO @TASKLIST
           VALUES('Acknowledge personal gifts',0)

           SELECT @ABCOUNT = count(*) from @MYADDRESSBOOK

           ---Select @ABCOUNT = COUNT(ID)  from ADDRESSBOOKFAF 

          --- where CLIENTUSERSID = @CLIENTUSERSID and CONSTITUENTID is null


           insert into @DONORLIST
           exec dbo.USP_DATALIST_FAFDONORRETENTION @CLIENTUSERSID,@EVENTID

           SELECT @PREVIOUSDONORS = PREVIOUSDONORS, @CURRENTDONORS = CURRENTDONORS
           from @DONORLIST

           IF @PREVIOUSDONORS > 0 
           SET @DONORRETENTION = (@CURRENTDONORS/@PREVIOUSDONORS)*100

           SET @DONORRETENTION = ISNULL(@DONORRETENTION,0)

           SELECT @CONTACTS = count(*) from @MYADDRESSBOOK where groupname = 'Contacts'

           IF @PREVIOUSEVENT IS NOT NULL

           SELECT @PREVIOUSTEAMMEMBERS = count(*) from dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID,@PREVIOUSEVENT) -- 1=team


           IF ISNULL(@PREVIOUSTEAMMEMBERS,0) > 0
              SET @PREVIOUSTEAMMEMBERS = @PREVIOUSTEAMMEMBERS + 1
           ELSE SET @PREVIOUSTEAMMEMBERS = 0

           SELECT @TEAMMEMBERS = count(*) from dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID,@EVENTID)  -- 1 = team

           SET @TEAMMEMBERS = ISNULL(@TEAMMEMBERS,0)+1

           IF @PREVIOUSTEAMMEMBERS > 0
           SET @TEAMMEMRETENTION = (@TEAMMEMBERS/@PREVIOUSTEAMMEMBERS)*100

           SET @TEAMMEMRETENTION = ISNULL(@TEAMMEMRETENTION,0)

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

           SELECT @MONEYRAISED = dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@REGISTRANTID, @EVENTID)


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

            IF EXISTS (Select 1 from dbo.STORY (NOLOCK) WHERE EVENTID = @EVENTID AND REGISTRANTID = @REGISTRANTID AND ISNULL(STORYTEXT,'') > '' )
                SET @STORYSTATUS = 0
            ELSE 
                SET @STORYSTATUS = 1

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

            IF @IMAGESTATUS = 0
            IF EXISTS (SELECT 1 FROM dbo.STORY S (NOLOCK) INNER JOIN dbo.STORY DS (NOLOCK)
                        ON S.EVENTID = DS.EVENTID AND S.FAFIMAGESID = DS.FAFIMAGESID AND S.TYPECODE = DS.TYPECODE 
                        WHERE S.REGISTRANTID = @REGISTRANTID AND DS.REGISTRANTID IS NULL AND DS.TEAMFUNDRAISINGTEAMID IS NULL 
                        AND DS.FAFIMAGESID IS NOT NULL AND DS.TYPECODE = 0)
              SET @IMAGESTATUS = 1

           IF @STORYSTATUS = 0
            IF EXISTS (SELECT 1 FROM dbo.STORY S (NOLOCK) INNER JOIN dbo.STORY DS (NOLOCK)
                        ON S.EVENTID = DS.EVENTID AND S.STORYTEXT = DS.STORYTEXT AND S.TYPECODE = DS.TYPECODE 
                        WHERE S.REGISTRANTID = @REGISTRANTID AND DS.REGISTRANTID IS NULL AND DS.TEAMFUNDRAISINGTEAMID IS NULL 
                         AND DS.STORYTEXT IS NOT NULL AND DS.TYPECODE = 0)
              SET @STORYSTATUS = 1

           IF @IMAGESTATUS = 1
              UPDATE @TASKLIST 
              SET DISPLAYFLAG = 1
              WHERE TASKNAME = 'Upload a personal photo'

           IF @SOCIALNETWORKSTATUS = 1
              UPDATE @TASKLIST 
              SET DISPLAYFLAG = 1
              WHERE TASKNAME = 'Post to social networks'

           IF @STORYSTATUS = 1
              UPDATE @TASKLIST 
              SET DISPLAYFLAG = 1
              WHERE TASKNAME = 'Write your personal story'


           IF ISNULL(@ABCOUNT,0) = 0    -- if addressbook count is 0

           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Add contacts'

           IF @CONTACTS > 0 AND @ROLETYPE IN (1,2,3)
           IF EXISTS (SELECT 1 FROM dbo.REGISTRANTEXTENSION (NOLOCK) 
                      where REGISTRANTID = @REGISTRANTID and MEMBERECRUITMENTGOAL > 0 and MEMBERECRUITMENTGOAL > @TEAMMEMBERS)
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Invite contacts to join your team'

           IF @CONTACTS > 0 AND @ROLETYPE IN (5,6)
           IF EXISTS (SELECT 1 FROM dbo.REGISTRANTEXTENSION (NOLOCK) 
                      where REGISTRANTID = @REGISTRANTID and MEMBERECRUITMENTGOAL > 0 and MEMBERECRUITMENTGOAL > @TEAMMEMBERS)
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Invite contacts to join your household'

           IF @PREVIOUSTEAMMEMBERS > 0 AND @ROLETYPE IN (1,2,3)
           IF EXISTS (SELECT 1 FROM dbo.TEAMEXTENSION (NOLOCK) 
                    WHERE TEAMFUNDRAISINGTEAMID = @TEAMID AND PCTTEAMMEMBERRETENSION > 0 AND PCTTEAMMEMBERRETENSION > @TEAMMEMRETENTION)
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Encourage past teammates to join'

           IF @PREVIOUSTEAMMEMBERS > 0 AND @ROLETYPE IN (5,6)
           IF EXISTS (SELECT 1 FROM dbo.TEAMEXTENSION (NOLOCK) 
                    WHERE TEAMFUNDRAISINGTEAMID = @TEAMID AND PCTTEAMMEMBERRETENSION > 0 AND PCTTEAMMEMBERRETENSION > @TEAMMEMRETENTION)
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Encourage past household members to join'

           IF @CONTACTS > 0
           IF EXISTS (SELECT 1 FROM dbo.REGISTRANTEXTENSION (NOLOCK) 
                      where REGISTRANTID = @REGISTRANTID and TARGETFUNDRAISINGGOAL > 0 and TARGETFUNDRAISINGGOAL > ISNULL(@MONEYRAISED,0))
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Ask contacts to donate'

           IF @PREVIOUSDONORS > 0
           IF EXISTS (SELECT 1 FROM dbo.REGISTRANTEXTENSION (NOLOCK) 
                      where REGISTRANTID = @REGISTRANTID and DONORRETENTIONGOAL > 0 and DONORRETENTIONGOAL > @DONORRETENTION)
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Solicit previous donors'

           IF EXISTS (select 1 from @ACKNOWLEDGEGIFT  WHERE CONSTITUENTID = @CONSTITUENTID AND ISACKNOWLEDGED = 0)
           UPDATE @TASKLIST 
           SET DISPLAYFLAG = 1
           WHERE TASKNAME = 'Acknowledge personal gifts'


          select TASKNAME,
              DISPLAYFLAG
          from @TASKLIST