USP_GLOBALCHANGE_EDITMAXIMUMSPONSORSPEROPPORTUNITY

CREATE procedure dbo.USP_GLOBALCHANGE_EDITMAXIMUMSPONSORSPEROPPORTUNITY
(
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
  @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier = null,
    @SPONSORSPEROPPORTUNITY int = 0,
    @OFFERSOLESPONSORSHIP bit = 0,
    @OVERRIDESPONSORSPEROPPORTUNITY xml = null
)
as            
    set nocount off;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

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

    begin try

        declare @CHANGEINGROUPSIZE tinyint = 0; -- 0 = no change, 1 = decrease, 2 = increase

        declare @SPONSORSHIPLOCATIONID uniqueidentifier = null;
        declare @EXISTINGOVERRIDES xml = null;
        declare @EXISTINGOFFERSOLESPONSORSHIP bit = 0; 

      -- check if the max # of sponsors has changed then take appropriate action. If no change then do nothing. 

    -- this also handles the situation of max sponsors per opportunity being set to unlimited which is a value of 0, therefore 4->0 would be an increase, while 0->4 would be a decrease in group size.

    select    @EXISTINGOVERRIDES = OVERRIDESPONSORSPEROPPORTUNITY,
                @SPONSORSHIPLOCATIONID = SPONSORSHIPLOCATIONID,
                @EXISTINGOFFERSOLESPONSORSHIP = OFFERSOLESPONSORSHIP,
        @CHANGEINGROUPSIZE = case when (@SPONSORSPEROPPORTUNITY > SPONSORSPEROPPORTUNITY or (@SPONSORSPEROPPORTUNITY = 0 and SPONSORSPEROPPORTUNITY > 0)) then 2 when (@SPONSORSPEROPPORTUNITY < SPONSORSPEROPPORTUNITY or (SPONSORSPEROPPORTUNITY = 0 and @SPONSORSPEROPPORTUNITY > 0)) then 1 else 0 end
        from dbo.SPONSORSHIPOPPORTUNITYGROUP 
        where ID = @SPONSORSHIPOPPORTUNITYGROUPID;

   -----------------------------------------

    -- validate new Overrides this should really be done on 'Save' not during process run

        if @OVERRIDESPONSORSPEROPPORTUNITY is not null
        begin
            declare @OVERRIDESTAB table (SPONSORSHIPLOCATIONID uniqueidentifier,
                                            SPONSORSPEROPPORTUNITY smallint,
                                            OFFERSOLESPONSORSHIP bit)
            insert into @OVERRIDESTAB
            select *
            from dbo.UFN_SPONSORSHIPOPPORTUNITYGROUP_OVERRIDESPONSORSPEROPPORTUNITY(@OVERRIDESPONSORSPEROPPORTUNITY);

            declare @BADCOUNT smallint

            -- sponsors per opportunity invalid

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES
            where OVERRIDES.SPONSORSPEROPPORTUNITY < 1

            if @BADCOUNT > 0
                raiserror('BBERR_INVALIDOVERRIDESPONSORSPEROPPORTUNITY',13,1)        

            -- sole sponsorship invalid

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES
            where isnull(OVERRIDES.SPONSORSPEROPPORTUNITY,0) < 2
            and OVERRIDES.OFFERSOLESPONSORSHIP = 1;

            if @BADCOUNT > 0
                raiserror('BBERR_INVALIDOVERRIDEOFFERSOLESPONSORSHIP',13,1)        

            -- duplicate locations

            select @BADCOUNT = count(*)
            from (select SPONSORSHIPLOCATIONID
            from @OVERRIDESTAB
            group by SPONSORSHIPLOCATIONID having count(*) > 1) X;

            if @BADCOUNT > 0
                raiserror('BBERR_DUPLICATELOCATIONS',13,1)        

            -- overlapping locations

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES1
            inner join @OVERRIDESTAB OVERRIDES2 on 1=1
            inner join dbo.SPONSORSHIPLOCATION L1 on L1.ID = OVERRIDES1.SPONSORSHIPLOCATIONID
            inner join dbo.SPONSORSHIPLOCATION L2 on L2.ID = OVERRIDES2.SPONSORSHIPLOCATIONID
            where L1.HIERARCHYPATH.IsDescendantOf(L2.HIERARCHYPATH) = 1
            and L1.ID <> L2.ID;

            if @BADCOUNT > 0
                raiserror('BBERR_OVERLAPPINGLOCATIONS',13,1)        

            if @SPONSORSHIPLOCATIONID is not null
            begin
                -- locations not consistent with group location

                select @BADCOUNT = count(*)
                from @OVERRIDESTAB OVERRIDES
                inner join dbo.SPONSORSHIPLOCATION OVERRIDELOCATION on OVERRIDELOCATION.ID = OVERRIDES.SPONSORSHIPLOCATIONID
                inner join dbo.SPONSORSHIPLOCATION GROUPLOCATION on GROUPLOCATION.ID = @SPONSORSHIPLOCATIONID
                where OVERRIDELOCATION.HIERARCHYPATH.IsDescendantOf(GROUPLOCATION.HIERARCHYPATH) = 0;

                if @BADCOUNT > 0
                    raiserror('BBERR_INVALIDOVERRIDELOCATION',13,1)          

                -- location same as group location (i.e., default would never be used)

                select @BADCOUNT = count(*)
                from @OVERRIDESTAB OVERRIDES
                where OVERRIDES.SPONSORSHIPLOCATIONID = @SPONSORSHIPLOCATIONID;

                if @BADCOUNT > 0
                    raiserror('BBERR_SAMELOCATIONASGROUP',13,1)          
            end

            -- locations w/ same values as default

            select @BADCOUNT = count(*)
            from @OVERRIDESTAB OVERRIDES
            where isnull(OVERRIDES.SPONSORSPEROPPORTUNITY,0) = isnull(@SPONSORSPEROPPORTUNITY,0)
            and OVERRIDES.OFFERSOLESPONSORSHIP = @OFFERSOLESPONSORSHIP

            if @BADCOUNT > 0
                raiserror('BBERR_OVERRIDESWITHSAMEVALUES',13,1) 
        end
        -----------------------------------------

    -- validate solesponsosrships

    if @EXISTINGOFFERSOLESPONSORSHIP = 1 and @OFFERSOLESPONSORSHIP = 0
    begin
      if exists (select 1 from dbo.SPONSORSHIP S inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID where SO.SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID and S.ISSOLESPONSORSHIP = 1)
        raiserror('BBERR_INVALIDSOLESPONSORSHIPCHANGE',13,1)
    end

    --This will likely be the most common change to occur and the simplest update; group size increasing and no new or existing overrides

        if @CHANGEINGROUPSIZE = 2 and @OVERRIDESPONSORSPEROPPORTUNITY is null and @EXISTINGOVERRIDES is null 
        begin
              update 
                  dbo.SPONSORSHIPOPPORTUNITY
              set
                  AVAILABILITYCODE = 0,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
              from dbo.SPONSORSHIPOPPORTUNITY
              where  
                  AVAILABILITYCODE = 2 and  
                  SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID and
                  dbo.UFN_SPONSORSHIPLOCATION_ISACTIVE(SPONSORSHIPLOCATIONID) = 1 and
                  dbo.UFN_SPONSORSHIPOPPORTUNITY_HASSOLESPONSORSHIP(ID) = 0
              set @NUMBEREDITED = @NUMBEREDITED + @@ROWCOUNT
        end    
    else if @CHANGEINGROUPSIZE = 1 or @OVERRIDESPONSORSPEROPPORTUNITY is not null or @EXISTINGOVERRIDES is not null  -- group size decreasing or has/had overrides

    begin
          declare @RESTAB table (ID uniqueidentifier);
      declare @RESKEYID uniqueidentifier = null;

          ;With CTE(ID, ACTIVESPONSORSHIPS, NEWLIMIT, AVAILABILITYCODE, RESERVATIONKEYID, CHANGEDBYID, DATECHANGED) as (
                select 
                    ID,
                    dbo.UFN_SPONSORSHIPOPPORTUNITY_ACTIVESPONSORSHIPS(ID), 
                    coalesce(dbo.UFN_SPONSORSHIPOPPORTUNITY_OVERRIDES_SPONSORSPEROPPORTUNITY(SPONSORSHIPLOCATIONID, @OVERRIDESPONSORSPEROPPORTUNITY), @SPONSORSPEROPPORTUNITY),
                    AVAILABILITYCODE,
                    RESERVATIONKEYID,
                    CHANGEDBYID,
                    DATECHANGED
                from
                    dbo.SPONSORSHIPOPPORTUNITY
                where
                    SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID and
                    dbo.UFN_SPONSORSHIPLOCATION_ISACTIVE(SPONSORSHIPLOCATIONID) = 1 and
                    dbo.UFN_SPONSORSHIPOPPORTUNITY_HASSOLESPONSORSHIP(ID) = 0
            )
              update
                  CTE
              set 
                  AVAILABILITYCODE = case when ACTIVESPONSORSHIPS > NEWLIMIT then -1 when(ACTIVESPONSORSHIPS = NEWLIMIT and AVAILABILITYCODE in (0,1)) then 2 when (ACTIVESPONSORSHIPS < NEWLIMIT and AVAILABILITYCODE = 2) then 0 else AVAILABILITYCODE end,
                  RESERVATIONKEYID = case when ACTIVESPONSORSHIPS = NEWLIMIT and AVAILABILITYCODE = 1 then null else RESERVATIONKEYID end,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
              OUTPUT deleted.RESERVATIONKEYID
              INTO @RESTAB  --get the reservation key id's so we know when to change the status of the reservation group


        set @NUMBEREDITED = @NUMBEREDITED + @@ROWCOUNT;

        declare RESKEY_CURSOR cursor local fast_forward for
        select distinct ID from @RESTAB where ID is not null;

        open RESKEY_CURSOR;
        fetch next from RESKEY_CURSOR into @RESKEYID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.USP_SPONSORSHIPOPPORTUNITYRESERVE_UPDATESTATUS @RESKEYID, @CHANGEAGENTID;   
          fetch next from RESKEY_CURSOR into @RESKEYID
        end
      close RESKEY_CURSOR;
          deallocate RESKEY_CURSOR;
    end  

    --update the group

        update 
            dbo.SPONSORSHIPOPPORTUNITYGROUP
        set
            SPONSORSPEROPPORTUNITY = @SPONSORSPEROPPORTUNITY,
            OFFERSOLESPONSORSHIP = @OFFERSOLESPONSORSHIP,
            OVERRIDESPONSORSPEROPPORTUNITY = @OVERRIDESPONSORSPEROPPORTUNITY,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @SPONSORSHIPOPPORTUNITYGROUPID        

        set @NUMBERADDED = 0;
        set @NUMBERDELETED = 0; 

    end try    
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch