USP_DATALIST_FAFHOUSEHOLDTASKS

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

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

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

             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 @ACKNOWLEDGEGIFT table (
                        CONSTITUENTID uniqueidentifier,
                        DONATIONDATE datetime,
                        DONATIONAMOUNT money,
                        ISACKNOWLEDGED bit,
                        REVENUEID uniqueidentifier,
                        REVENUESPLITID uniqueidentifier,
              TYPE varchar(20),
                        NAME varchar(250)
                        )

             DECLARE @MONEYRAISED money
             DECLARE @TEAMID uniqueidentifier
             DECLARE @REGISTRANTID uniqueidentifier
             DECLARE @PREVIOUSEVENT uniqueidentifier
             DECLARE @TEAMMEMBERS int
             DECLARE @EVENTSTATUS bit -- 1:active, 0:not active

             DECLARE @STORYSTATUS bit
             DECLARE @IMAGESTATUS bit
             DECLARE @PASTDONORS integer

             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


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

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

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

             INSERT INTO @TASKLIST
             VALUES('Remind household members to ask new donors',0)

             INSERT INTO @TASKLIST
             VALUES('Remind household members to ask past donors',0)

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

             INSERT INTO @TASKLIST
             VALUES('Ask household members to recruit more members',0)

             INSERT INTO @TASKLIST
             VALUES('Acknowledge household members',0)


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

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

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

             SELECT @PASTDONORS = count(*) from @MYADDRESSBOOK where groupname = 'Donors - previous'  

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

              IF EXISTS (Select 1 from dbo.STORY (NOLOCK) WHERE EVENTID = @EVENTID AND TEAMFUNDRAISINGTEAMID = @TEAMID AND ISNULL(STORYTEXT,'') > '')
                  SET @STORYSTATUS = 0
              ELSE 
                  SET @STORYSTATUS = 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.TEAMFUNDRAISINGTEAMID = @TEAMID AND DS.REGISTRANTID IS NULL AND DS.TEAMFUNDRAISINGTEAMID IS NULL 
                          AND DS.FAFIMAGESID IS NOT NULL AND DS.TYPECODE = 1)
                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.TEAMFUNDRAISINGTEAMID = @TEAMID AND DS.REGISTRANTID IS NULL AND DS.TEAMFUNDRAISINGTEAMID IS NULL 
                           AND DS.STORYTEXT IS NOT NULL AND DS.TYPECODE = 1)
                SET @STORYSTATUS = 1


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

             SELECT @TEAMMEMBERS = count(*) from dbo.UFN_FAF_TEAMMEMBERLIST(@CONSTITUENTID,@EVENTID)
             SET @TEAMMEMBERS = ISNULL(@TEAMMEMBERS,0)+1

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

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

             IF EXISTS (SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAM (NOLOCK) 
                      where ID = @TEAMID and GOAL > 0 and GOAL > ISNULL(@MONEYRAISED,0))
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Remind household members to ask new donors'

             IF @PREVIOUSEVENT IS NOT NULL
             IF EXISTS (SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAM (NOLOCK) 
                      where ID = @TEAMID and GOAL > 0 and GOAL > ISNULL(@MONEYRAISED,0)) and @PASTDONORS > 0
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Remind household members to ask past donors'

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

             IF EXISTS (SELECT 1 FROM dbo.TEAMEXTENSION (NOLOCK) 
                      where TEAMFUNDRAISINGTEAMID = @TEAMID and TEAMMEMBERGOAL > 0 and TEAMMEMBERGOAL > ISNULL(@TEAMMEMBERS,0))
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Ask household members to recruit more members'

             IF @EVENTSTATUS = 0  -- need to store a flag if email is sent

             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Acknowledge household members'


            select TASKNAME,
                DISPLAYFLAG
            from @TASKLIST