USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPADDED

create procedure dbo.USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPADDED (
  @SPONSORSHIPOPPORTUNITYID uniqueidentifier,
  @ISSOLESPONSORSHIP bit,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTDATE datetime = null,
  @UNLOCKOPPORTUNITY bit = 1
)
as
begin
  if @CURRENTDATE is null
    set @CURRENTDATE = getdate();

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @SPONSORSPEROPPORTUNITY int;
  declare @RESERVATIONKEYID uniqueidentifier;

  if @ISSOLESPONSORSHIP = 1
    set @SPONSORSPEROPPORTUNITY = 1;
  else
    select @SPONSORSPEROPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(SPONSORSHIPOPPORTUNITYGROUPID,SPONSORSHIPLOCATIONID),
           @RESERVATIONKEYID = RESERVATIONKEYID
    from dbo.SPONSORSHIPOPPORTUNITY
    where ID = @SPONSORSHIPOPPORTUNITYID;

  if @SPONSORSPEROPPORTUNITY > 0
  begin
    -- there is a limit to the number of sponsors for this opportunity

    declare @SPONSORCOUNT int

    if @SPONSORSPEROPPORTUNITY > 1
      -- plus 1 b/c we haven't inserted the current sponsorship yet

      select @SPONSORCOUNT = SPONSORCOUNT + 1
      from dbo.SPONSORSHIPOPPORTUNITYLOCK
      where ID = @SPONSORSHIPOPPORTUNITYID;
    else
      set @SPONSORCOUNT = 1;

    if @SPONSORCOUNT >= @SPONSORSPEROPPORTUNITY
    begin
      -- this sponsorship has hit the limit for the opportunity,

      -- update the opportunity to unavailable

      update dbo.SPONSORSHIPOPPORTUNITY
      set AVAILABILITYCODE = 2,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE,
          RESERVATIONKEYID = null
      where ID = @SPONSORSHIPOPPORTUNITYID
      and AVAILABILITYCODE <> 2;

      if @@ROWCOUNT = 0
      begin
        raiserror('BBERR_OPPORTUNITYNOTAVAILABLE',13,1);
        return 1;
      end
      else if @RESERVATIONKEYID is not null
        exec dbo.USP_SPONSORSHIPOPPORTUNITYRESERVE_UPDATESTATUS @RESERVATIONKEYID, @CHANGEAGENTID
    end
  end

  if @UNLOCKOPPORTUNITY = 1
    exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @SPONSORSHIPOPPORTUNITYID, 1, @CHANGEAGENTID;

  return 0;
end