USP_DATALIST_WIDGET_EXTENSION_2

create procedure dbo.USP_DATALIST_WIDGET_EXTENSION_2
(
  @EVENTID uniqueidentifier,
  @PARTICIPANTID uniqueidentifier = null,
  @TEAMID uniqueidentifier = null
) 
as
    set nocount on;

  declare @CHANGEAGENTID uniqueidentifier 
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  -- make sure event widget extension records exist

  insert into WIDGETEXTENSION (ID,eventwidgetid,addedbyid,changedbyid,dateadded,datechanged)  
  select NEWID(),EW.ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE  
  from dbo.EVENTWIDGET EW (nolock)
  left outer join dbo.WIDGETEXTENSION WE (nolock)
  on WE.EVENTWIDGETID = EW.ID 
  where EW.EVENTID = @EVENTID and WE.ID is null

  if @PARTICIPANTID is not null
    begin   
      -- create extension records if not existed

      insert into WIDGETEXTENSION (ID,PARTICIPANTWIDGETID,addedbyid,changedbyid,dateadded,datechanged,givingleveldata,ALLOWOTHERAMOUNT,ALLOWDONOROTHERAMOUNT,SUGGESTAMOUNT,DISPLAYMINIMUMFUNDRAISINGGOAL,DISPLAYTARGETFUNDRAISINGGOAL)  
      select NEWID(),PW.ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE, WX.GIVINGLEVELDATA, WX.ALLOWOTHERAMOUNT,WX.ALLOWDONOROTHERAMOUNT,WX.SUGGESTAMOUNT,WX.DISPLAYMINIMUMFUNDRAISINGGOAL,WX.DISPLAYTARGETFUNDRAISINGGOAL
      from dbo.PARTICIPANTWIDGET PW (nolock)
      inner join dbo.EVENTWIDGET EW (nolock) 
          on EW.EVENTID = @EVENTID and EW.ROLECODE=0 and EW.WIDGETID = PW.WIDGETID 
      inner join dbo.WIDGETEXTENSION WX (nolock) 
          on WX.EVENTWIDGETID = EW.ID 
      left outer join dbo.WIDGETEXTENSION WX2 (nolock) 
          on WX2.PARTICIPANTWIDGETID = PW.ID 
      where PW.REGISTRANTID = @PARTICIPANTID and WX2.ID is null

        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 (nolock) 
        inner join PARTICIPANTWIDGET (nolock) on PARTICIPANTWIDGET.ID = WIDGETEXTENSION.PARTICIPANTWIDGETID 
        where PARTICIPANTWIDGET.REGISTRANTID = @PARTICIPANTID 
    end
  else if @TEAMID is not null
    begin   
      -- create extension records if not existed

      insert into WIDGETEXTENSION (ID,TEAMWIDGETID,addedbyid,changedbyid,dateadded,datechanged,givingleveldata,ALLOWOTHERAMOUNT,ALLOWDONOROTHERAMOUNT,SUGGESTAMOUNT,DISPLAYMINIMUMFUNDRAISINGGOAL,DISPLAYTARGETFUNDRAISINGGOAL)  
      select NEWID(),TW.ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE, WX.GIVINGLEVELDATA, WX.ALLOWOTHERAMOUNT,WX.ALLOWDONOROTHERAMOUNT,WX.SUGGESTAMOUNT,WX.DISPLAYMINIMUMFUNDRAISINGGOAL,WX.DISPLAYTARGETFUNDRAISINGGOAL
      from dbo.TEAMWIDGET TW (nolock)
      inner join dbo.TEAMEXTENSION TM (nolock)
          on TM.TEAMFUNDRAISINGTEAMID = @TEAMID
      inner join dbo.EVENTWIDGET EW (nolock) 
          on EW.EVENTID = @EVENTID and EW.ROLECODE= TM.TYPECODE and EW.WIDGETID = TW.WIDGETID 
      inner join dbo.WIDGETEXTENSION WX (nolock) 
          on WX.EVENTWIDGETID = EW.ID 
      left outer join dbo.WIDGETEXTENSION WX2 (nolock) 
          on WX2.TEAMWIDGETID = TW.ID 
      where TW.TEAMFUNDRAISINGTEAMID=@TEAMID and WX2.ID is null

        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 (nolock) 
        inner join TEAMWIDGET (nolock) on TEAMWIDGET.ID = WIDGETEXTENSION.TEAMWIDGETID 
        where TEAMWIDGET.TEAMFUNDRAISINGTEAMID = @TEAMID 
    end
  else
    begin   
        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 (nolock) 
        inner join EVENTWIDGET (nolock) on EVENTWIDGET.ID = WIDGETEXTENSION.EVENTWIDGETID
        where EVENTWIDGET.EVENTID = @EVENTID 
    end