USP_SPONSORSHIPOPPORTUNITYGROUP_VALIDATEOVERRIDES

create procedure dbo.USP_SPONSORSHIPOPPORTUNITYGROUP_VALIDATEOVERRIDES(
  @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier = null,
  @SPONSORSPEROPPORTUNITY smallint = 0,
  @OFFERSOLESPONSORSHIP bit = 0,
  @OVERRIDESPONSORSPEROPPORTUNITY xml = null,
  @ERRORMSG nvarchar(50) output
)
as
  set nocount off;
  begin try


    declare @SPONSORSHIPLOCATIONID uniqueidentifier = null;

      select @SPONSORSHIPLOCATIONID = SPONSORSHIPLOCATIONID
    from dbo.SPONSORSHIPOPPORTUNITYGROUP 
        where ID = @SPONSORSHIPOPPORTUNITYGROUPID;   

    -- validate new overrides

        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);


      set @ERRORMSG = 'Test Inside'
      --select @ERRORMSG = cast(SPONSORSHIPLOCATIONID as nvarchar(50)) from @OVERRIDESTAB


            declare @BADCOUNT smallint

            -- sponsors per opportunity invalid

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

            if @BADCOUNT > 0
                set @ERRORMSG = 'BBERR_INVALIDOVERRIDESPONSORSPEROPPORTUNITY'
        --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
                set @ERRORMSG = 'BBERR_INVALIDOVERRIDEOFFERSOLESPONSORSHIP'
        --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
                set @ERRORMSG = 'BBERR_DUPLICATELOCATIONS'
        --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
                set @ERRORMSG = 'BBERR_OVERLAPPINGLOCATIONS'
        --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
                    set @ERRORMSG = 'BBERR_INVALIDOVERRIDELOCATION'
          --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
                    set @ERRORMSG = 'BBERR_SAMELOCATIONASGROUP'
          --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
                set @ERRORMSG = 'BBERR_OVERRIDESWITHSAMEVALUES'
        --raiserror('BBERR_OVERRIDESWITHSAMEVALUES',13,1)



        end

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