USP_DATALIST_TEAMWIDGET

CREATE procedure dbo.USP_DATALIST_TEAMWIDGET
                            (    @EVENTID uniqueidentifier, 
                                @TEAMFUNDRAISINGTEAMID uniqueidentifier,
                                @WIDGETID int = NULL,
                                @ROLECODE tinyint = 1
                            )with execute as caller
    as
            set nocount on;

            declare @CHANGEAGENTID uniqueidentifier 
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            declare @CURRENTDATE datetime
            set @CURRENTDATE = getdate()

      --Bug 195562 - copying down the admin story results in an inability to know if the registrant actually customized it      

            --IF NOT EXISTS (SELECT 1 FROM TEAMWIDGET WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND WIDGETID = 3)

            --BEGIN

                --IF NOT EXISTS (SELECT 1 FROM dbo.STORY WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND TYPECODE = @ROLECODE)

                    --INSERT INTO STORY (ID,EVENTID,STORYTEXT,FAFIMAGESID,IMAGECODE,TEAMFUNDRAISINGTEAMID,TYPECODE,ADDEDBYID,CHANGEDBYID,DATECHANGED,DATEADDED)

                    --SELECT TOP 1 NEWID(),EVENTID,STORYTEXT,FAFIMAGESID,IMAGECODE,@TEAMFUNDRAISINGTEAMID,@ROLECODE,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE

                    --FROM dbo.STORY WHERE EVENTID = @EVENTID AND TYPECODE = @ROLECODE AND REGISTRANTID IS NULL AND TEAMFUNDRAISINGTEAMID IS NULL

            --END


            IF NOT EXISTS (SELECT 1 FROM TEAMWIDGET WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND WIDGETID = 9)
            BEGIN
                IF NOT EXISTS (SELECT 1 FROM dbo.FAFRSSFEED WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND TYPECODE = @ROLECODE)
                    INSERT INTO FAFRSSFEED (ID,EVENTID,TEAMFUNDRAISINGTEAMID,TYPECODE,RSSFEEDURL,ADDEDBYID,CHANGEDBYID,DATECHANGED,DATEADDED)
                    SELECT NEWID(),EVENTID,@TEAMFUNDRAISINGTEAMID,@ROLECODE,RSSFEEDURL,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
                    FROM dbo.FAFRSSFEED WHERE EVENTID = @EVENTID AND TYPECODE = @ROLECODE AND REGISTRANTID IS NULL AND TEAMFUNDRAISINGTEAMID IS NULL
            END

            IF NOT EXISTS (SELECT 1 FROM TEAMWIDGET WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID AND WIDGETID = 7)
            BEGIN
        DECLARE @CONSTITUENTID uniqueidentifier
        SET @CONSTITUENTID = (SELECT TEAMCONSTITUENTID FROM dbo.TEAMEXTENSION WHERE TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID)

                IF NOT EXISTS (SELECT 1 FROM dbo.VIDEO WHERE CONSTITUENTID = @CONSTITUENTID AND TYPECODE = @ROLECODE)
                    INSERT INTO VIDEO (ID,EVENTID,VIDEOID,NAME,CAPTION,URL,STATUS,FILEPATH,CONSTITUENTID,TYPECODE,ADDEDBYID,CHANGEDBYID,DATECHANGED,DATEADDED)
                    SELECT NEWID(),EVENTID,VIDEOID,NAME,CAPTION,URL,STATUS,FILEPATH,@CONSTITUENTID,@ROLECODE,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
                    FROM dbo.VIDEO WHERE EVENTID = @EVENTID AND TYPECODE = @ROLECODE AND CONSTITUENTID IS NULL 
            END

            INSERT INTO TEAMWIDGET (ID,widgetid,TEAMFUNDRAISINGTEAMID,displaytext,isactive,addedbyid,changedbyid,dateadded,datechanged)
            SELECT NEWID(),EW.WIDGETID,@TEAMFUNDRAISINGTEAMID,EW.DISPLAYTEXT,EW.ISACTIVE,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
            FROM dbo.EVENTWIDGET EW WITH (NOLOCK)
            LEFT OUTER JOIN dbo.TEAMWIDGET PW WITH (NOLOCK)
            ON EW.WIDGETID = PW.WIDGETID
            AND PW.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
            WHERE PW.WIDGETID IS NULL
            AND EW.ROLECODE = @ROLECODE
            AND EW.EVENTID = @EVENTID
            AND (EW.ISACTIVE = 1 OR EW.ALLOWREMOVE = 1)

            IF @WIDGETID IS NULL
            SELECT PW.ID,
                   PW.WIDGETID,
                   WG.NAME,
                   WG.DESCRIPTION,
                   PW.DISPLAYTEXT,
                   PW.ISACTIVE,
                   WG.PARENTWIDGETID,
                   EW.ALLOWEDIT,
                   EW.ALLOWMOVE,
                   EW.ALLOWREMOVE
            FROM dbo.EVENTWIDGET EW WITH (NOLOCK)
            INNER JOIN dbo.TEAMWIDGET PW WITH (NOLOCK)
            ON PW.WIDGETID = EW.WIDGETID
            INNER JOIN dbo.WIDGETGALLERY WG WITH (NOLOCK)
            ON WG.WIDGETID = EW.WIDGETID
            WHERE EW.EVENTID = @EVENTID
            AND EW.ROLECODE = @ROLECODE
            AND PW.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
            --AND WG.PARENTWIDGETID IS NULL

      -- Remove the filtering here to get all team widget information in one call

      -- to reduce the # of web service calls, filtering logic will move to front end

            AND (EW.ISACTIVE = 1 OR EW.ALLOWREMOVE = 1)
        ELSE    -- Get the Widget and Child widgets

            SELECT PW.ID,
                   PW.WIDGETID,
                   WG.NAME,
                   WG.DESCRIPTION,
                   PW.DISPLAYTEXT,
                   PW.ISACTIVE,
                   WG.PARENTWIDGETID,
                   EW.ALLOWEDIT,
                   EW.ALLOWMOVE,
                   EW.ALLOWREMOVE
            FROM dbo.EVENTWIDGET EW WITH (NOLOCK)
            INNER JOIN dbo.TEAMWIDGET PW WITH (NOLOCK)
            ON PW.WIDGETID = EW.WIDGETID
            INNER JOIN dbo.WIDGETGALLERY WG WITH (NOLOCK)
            ON WG.WIDGETID = EW.WIDGETID
            WHERE EW.EVENTID = @EVENTID
            AND EW.ROLECODE = @ROLECODE
            AND PW.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
            AND (EW.ISACTIVE = 1 OR EW.ALLOWREMOVE = 1)
            AND (PW.WIDGETID = @WIDGETID OR WG.PARENTWIDGETID = @WIDGETID)