USP_DATALIST_FAFCOMPANYLEADERTASKS

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

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

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

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

             DECLARE @TEAMRAISED table (
                        TEAMID uniqueidentifier,
              MONEYRAISED money,
              GOAL money
                        )

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

              DECLARE @STORYSTATUS bit
              DECLARE @IMAGESTATUS bit

             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 @TEAMID = TEAMID, @REGISTRANTID = RegistrantID 
             from dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID)

             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 = 2)
                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 = 2)
                SET @STORYSTATUS = 1

             SELECT @COMPANYMEMBERS = count(*) from dbo.UFN_FAF_COMPANYMEMBERLIST(@CONSTITUENTID,@EVENTID)

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

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

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

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

             Insert into @TEAMRAISED
             SELECT TEAMID,
               dbo.UFN_REVENUE_GETTEAMRAISEDTOTAL(TL.TEAMID, @EVENTID),
               T.GOAL
             FROM dbo.UFN_FAF_COMPANYTEAMLEADERLIST(@CONSTITUENTID,@EVENTID) TL
             INNER JOIN dbo.TEAMFUNDRAISINGTEAM T
             ON T.ID = TL.TEAMID

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

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

             INSERT INTO @TASKLIST
             VALUES('Recruit team leaders',0)

             INSERT INTO @TASKLIST
             VALUES('Encourage leaders to find new donors',0)

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

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

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

              INSERT INTO @TASKLIST
             VALUES('Help underperforming team leaders',0)

             INSERT INTO @TASKLIST
             VALUES('Acknowledge team leaders',0)

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

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

             IF EXISTS (SELECT 1 FROM dbo.TEAMEXTENSION (NOLOCK) 
                      where TEAMFUNDRAISINGTEAMID = @TEAMID and PARTICIPANTGOAL > 0 and PARTICIPANTGOAL > ISNULL(@TEAMLEADERS,0))
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Recruit team leaders'

             IF EXISTS (SELECT 1 FROM dbo.TEAMFUNDRAISINGTEAM (NOLOCK) 
                      where ID = @TEAMID and GOAL > 0 and GOAL > ISNULL(@MONEYRAISED,0)  ) and @TEAMLEADERS > 0
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Encourage leaders to find 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 @TEAMLEADERS > 0
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Remind leaders to ask past donors'

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

             IF EXISTS (Select 1 from @TEAMRAISED where GOAL > 0 AND GOAL > MONEYRAISED) and @TEAMLEADERS > 0 
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Help underperforming team leaders'

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

             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Acknowledge team leaders'                          

             IF EXISTS (Select 1 from dbo.TEAMEXTENSION (NOLOCK) 
                    where TEAMFUNDRAISINGTEAMID = @TEAMID and TEAMMEMBERGOAL > 0 ) and @TEAMLEADERS > 0 
             UPDATE @TASKLIST 
             SET DISPLAYFLAG = 1
             WHERE TASKNAME = 'Ask team to recruit more members'

            select TASKNAME,
                DISPLAYFLAG
            from @TASKLIST