USP_DATALIST_WIDGET_EXTENSION

CREATE procedure dbo.USP_DATALIST_WIDGET_EXTENSION
(    
    --@ID uniqueidentifier= NULL

  @EVENTWIDGETID uniqueidentifier = null,
  @PARTICIPANTWIDGETID uniqueidentifier = null,
  @TEAMWIDGETID uniqueidentifier = null,
  @EVENTID uniqueidentifier = null
) with execute as owner
as
    set nocount on;

  declare @script as nvarchar(4000)
    declare @PARAMDEF as nvarchar(2000)


  set @script = 
    'select 
        WIDGETEXTENSION.[ID],
        WIDGETEXTENSION.[PARTICIPANTWIDGETID],
        WIDGETEXTENSION.[EVENTWIDGETID],
        WIDGETEXTENSION.[TEAMWIDGETID],
        WIDGETEXTENSION.[ALLOWOTHERAMOUNT],
        WIDGETEXTENSION.[SUGGESTAMOUNT],
        WIDGETEXTENSION.[GIVINGLEVELDATA],
        WIDGETEXTENSION.[ALLOWDONOROTHERAMOUNT],
        WIDGETEXTENSION.[DISPLAYMINIMUMFUNDRAISINGGOAL],
        WIDGETEXTENSION.[DISPLAYTARGETFUNDRAISINGGOAL],
        WIDGETEXTENSION.[ALLOWPARTICIPANTTOCHANGEJOINTEAMLABEL],
        WIDGETEXTENSION.[ALLOWPARTICIPANTTOCHANGEJOINCOMPANYLABEL],
        WIDGETEXTENSION.[ALLOWPARTICIPANTTOCHANGEJOINEVENTLABEL],
        WIDGETEXTENSION.[JOINMYTEAMLABEL],
        WIDGETEXTENSION.[JOINMYCOMPANYLABEL],
        WIDGETEXTENSION.[JOINMYEVENTLABEL]

    from 
        dbo.WIDGETEXTENSION'

  IF @PARTICIPANTWIDGETID is not null  OR @TEAMWIDGETID is not null OR @EVENTWIDGETID is not null  
  BEGIN
    declare @CHANGEAGENTID uniqueidentifier 
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @new_Id as uniqueidentifier
    set @new_id = NEWID()

    declare @widget_id as int

    IF @PARTICIPANTWIDGETID IS NOT NULL 
    BEGIN
        set @script = @script + ' WHERE WIDGETEXTENSION.[PARTICIPANTWIDGETID] = @PARTICIPANTWIDGETID '
      SET @widget_id = (SELECT widgetid from Participantwidget PW where PW.ID = @PARTICIPANTWIDGETID)

      IF NOT EXISTS (SELECT 1 FROM WIDGETEXTENSION WHERE PARTICIPANTWIDGETID = @PARTICIPANTWIDGETID)
      BEGIN
      If @widget_id = 13 
          INSERT INTO WIDGETEXTENSION (ID,PARTICIPANTWIDGETID,addedbyid,changedbyid,dateadded,datechanged,givingleveldata)  
          SELECT NEWID(),@PARTICIPANTWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE, WE.GIVINGLEVELDATA     
          FROM PARTICIPANTWIDGET PW
          INNER JOIN EVENTWIDGET EW ON EW.WIDGETID = PW.WIDGETID AND ROLECODE = 0
          INNER JOIN WIDGETEXTENSION WE ON EW.ID = WE.EVENTWIDGETID
          WHERE PW.ID = @PARTICIPANTWIDGETID
          AND EW.EVENTID = @EVENTID
      ELSE IF @widget_id = 14
          INSERT INTO WIDGETEXTENSION (ID,PARTICIPANTWIDGETID,addedbyid,changedbyid,dateadded,datechanged,ALLOWOTHERAMOUNT,ALLOWDONOROTHERAMOUNT,SUGGESTAMOUNT)  
          SELECT NEWID(),@PARTICIPANTWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,WE.ALLOWOTHERAMOUNT,WE.ALLOWDONOROTHERAMOUNT,WE.SUGGESTAMOUNT
          FROM PARTICIPANTWIDGET PW
          INNER JOIN EVENTWIDGET EW ON EW.WIDGETID = PW.WIDGETID AND ROLECODE = 0
          INNER JOIN WIDGETEXTENSION WE ON EW.ID = WE.EVENTWIDGETID
          WHERE PW.ID = @PARTICIPANTWIDGETID
          AND EW.EVENTID = @EVENTID
      ELSE IF @widget_id = 11
          INSERT INTO WIDGETEXTENSION (ID,PARTICIPANTWIDGETID,addedbyid,changedbyid,dateadded,datechanged,DISPLAYMINIMUMFUNDRAISINGGOAL,DISPLAYTARGETFUNDRAISINGGOAL)  
          SELECT NEWID(),@PARTICIPANTWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,WE.DISPLAYMINIMUMFUNDRAISINGGOAL,WE.DISPLAYTARGETFUNDRAISINGGOAL
          FROM PARTICIPANTWIDGET PW
          INNER JOIN EVENTWIDGET EW ON EW.WIDGETID = PW.WIDGETID AND ROLECODE = 0
          INNER JOIN WIDGETEXTENSION WE ON EW.ID = WE.EVENTWIDGETID
          WHERE PW.ID = @PARTICIPANTWIDGETID       
          AND EW.EVENTID = @EVENTID
      ELSE 
          INSERT INTO WIDGETEXTENSION (ID,PARTICIPANTWIDGETID,addedbyid,changedbyid,dateadded,datechanged)  
          SELECT @new_id,@PARTICIPANTWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE 
      END
    END

    IF @TEAMWIDGETID IS NOT NULL 
    BEGIN
        set @script = @script + ' WHERE WIDGETEXTENSION.[TEAMWIDGETID] = @TEAMWIDGETID '
      SET @widget_id = (SELECT widgetid from TEAMWIDGET TW where TW.ID = @TEAMWIDGETID)

      IF NOT EXISTS (SELECT 1 FROM WIDGETEXTENSION WHERE TEAMWIDGETID = @TEAMWIDGETID)
      BEGIN
      If @widget_id = 13 
          INSERT INTO WIDGETEXTENSION (ID,TEAMWIDGETID,addedbyid,changedbyid,dateadded,datechanged,givingleveldata)  
          SELECT NEWID(),@TEAMWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,WE.GIVINGLEVELDATA     
              FROM Teamwidget TW
              INNER JOIN TEAMEXTENSION TE ON TW.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID
              INNER JOIN EVENTWIDGET EW ON EW.EVENTID = TE.EVENTID AND EW.ROLECODE = TE.TYPECODE  AND EW.WIDGETID = TW.WIDGETID
              INNER JOIN WIDGETEXTENSION WE ON EW.ID = WE.EVENTWIDGETID
              WHERE TW.ID = @TEAMWIDGETID
          AND EW.EVENTID = @EVENTID
              AND TW.WIDGETID = 13          
      ELSE IF @widget_id = 14
          INSERT INTO WIDGETEXTENSION (ID,TEAMWIDGETID,addedbyid,changedbyid,dateadded,datechanged,ALLOWOTHERAMOUNT,ALLOWDONOROTHERAMOUNT,SUGGESTAMOUNT)  
          SELECT NEWID(),@TEAMWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,WE.ALLOWOTHERAMOUNT,WE.ALLOWDONOROTHERAMOUNT,WE.SUGGESTAMOUNT
              FROM Teamwidget TW
              INNER JOIN TEAMEXTENSION TE ON TW.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID
              INNER JOIN EVENTWIDGET EW ON EW.EVENTID = TE.EVENTID AND EW.ROLECODE = TE.TYPECODE  AND EW.WIDGETID = TW.WIDGETID
              INNER JOIN WIDGETEXTENSION WE ON EW.ID = WE.EVENTWIDGETID
              WHERE TW.ID = @TEAMWIDGETID
          AND EW.EVENTID = @EVENTID
              AND TW.WIDGETID = 14     
      ELSE IF @widget_id = 11
          INSERT INTO WIDGETEXTENSION (ID,TEAMWIDGETID,addedbyid,changedbyid,dateadded,datechanged,DISPLAYMINIMUMFUNDRAISINGGOAL,DISPLAYTARGETFUNDRAISINGGOAL)  
          SELECT NEWID(),@TEAMWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,WE.DISPLAYMINIMUMFUNDRAISINGGOAL,WE.DISPLAYTARGETFUNDRAISINGGOAL
              FROM Teamwidget TW
              INNER JOIN TEAMEXTENSION TE ON TW.TEAMFUNDRAISINGTEAMID = TE.TEAMFUNDRAISINGTEAMID
              INNER JOIN EVENTWIDGET EW ON EW.EVENTID = TE.EVENTID AND EW.ROLECODE = TE.TYPECODE  AND EW.WIDGETID = TW.WIDGETID
              INNER JOIN WIDGETEXTENSION WE ON EW.ID = WE.EVENTWIDGETID
              WHERE TW.ID = @TEAMWIDGETID
          AND EW.EVENTID = @EVENTID
              AND TW.WIDGETID = 11            
      ELSE 
          INSERT INTO WIDGETEXTENSION (ID,TEAMWIDGETID,addedbyid,changedbyid,dateadded,datechanged)  
          SELECT @new_id,@TEAMWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE  
      END      
    END 

    IF @EVENTWIDGETID IS NOT NULL 
    BEGIN
        set @script = @script + ' WHERE WIDGETEXTENSION.[EVENTWIDGETID] = @EVENTWIDGETID '
      IF NOT EXISTS (SELECT 1 FROM WIDGETEXTENSION WHERE EVENTWIDGETID = @EVENTWIDGETID)
      INSERT INTO WIDGETEXTENSION (ID,eventwidgetid,addedbyid,changedbyid,dateadded,datechanged)  
      SELECT @new_id,@EVENTWIDGETID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE  
    END
  END
  ELSE
    set @script = @script + ' WHERE 1 = 2'

  SET @PARAMDEF = '@EVENTWIDGETID uniqueidentifier = null, @PARTICIPANTWIDGETID uniqueidentifier = null, @TEAMWIDGETID uniqueidentifier = null'

    exec sp_executesql @script,@PARAMDEF,@EVENTWIDGETID,@PARTICIPANTWIDGETID,@TEAMWIDGETID