USP_DATALIST_SPONSORSHIPOPPORTUNITYRESERVE_SELECTION

CREATE procedure dbo.USP_DATALIST_SPONSORSHIPOPPORTUNITYRESERVE_SELECTION
(
@GROUPID uniqueidentifier,
@OPPORTUNITYLOCATIONID uniqueidentifier,
@GENDERCODE int,
@SPROPPAGERANGEID uniqueidentifier,
@ISHIVPOSITIVECODE int,
@HASCONDITIONCODE int,
@ISORPHANEDCODE int, 
@KEY int
)
as 
set nocount on;

declare @MATCH int = 0; -- means no matching


declare @SELECTIONTABLE table(CID uniqueidentifier, CGENDERCODE int, CAGERANGEID uniqueidentifier);
declare @RESULTTABLE table(ID uniqueidentifier, GENDERCODE int, AGERANGEID uniqueidentifier)
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

-- table used to calculate the number of opportunities to be reserved

create table #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS (ID uniqueidentifier,
        GENDERCODE tinyint,
        AGERANGEID uniqueidentifier,
        INITIALCOUNT int,
        NEWCOUNT int);

if @GENDERCODE = 3 set @MATCH = 1
if @SPROPPAGERANGEID = '626658D1-E24B-4370-952D-1080B22C33AE' set @MATCH = 2
if @GENDERCODE = 3 and @SPROPPAGERANGEID = '626658D1-E24B-4370-952D-1080B22C33AE' set @MATCH = 3

if @MATCH = 2 or @MATCH = 3
 begin
    insert into @SELECTIONTABLE(CID, CGENDERCODE,CAGERANGEID)
    select SP.ID, C.GENDERCODE, SPA.ID from dbo.SPONSORSHIPOPPORTUNITY SP 
     inner join dbo.SPONSORSHIPOPPORTUNITYCHILD SPC on SP.ID = SPC.ID 
   inner join dbo.CONSTITUENT C on C.ID = SPC.CONSTITUENTID
     left join dbo.SPONSORSHIPOPPORTUNITYGROUP SPP on SPP.ID = SP.SPONSORSHIPOPPORTUNITYGROUPID 
     left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SP.SPONSORSHIPLOCATIONID 
     left outer join dbo.SPONSORSHIPLOCATION PREFERREDLOCATION on PREFERREDLOCATION.ID = @OPPORTUNITYLOCATIONID 
     left outer join dbo.SPONSORSHIPOPPORTUNITYAGERANGE SPA on C.BIRTHDATE<>'00000000' and dbo.UFN_AGEFROMFUZZYDATE(SPC.BIRTHDATE,getdate()) between SPA.MINAGE and SPA.MAXAGE
     where (@GROUPID is null or SPONSORSHIPOPPORTUNITYGROUPID = @GROUPID) 
     and (@OPPORTUNITYLOCATIONID is null or OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PREFERREDLOCATION.HIERARCHYPATH) = 1) 
     and (@GENDERCODE = 0 or SPC.GENDERCODE = case when @GENDERCODE = 3 then 1 else @GENDERCODE end or SPC.GENDERCODE = case when @GENDERCODE = 3 then 2 else @GENDERCODE end)  
     --and SPP.SPONSORSHIPOPPORTUNITYAGERANGEID is not null

     and (@ISHIVPOSITIVECODE = 0 or SPC.ISHIVPOSITIVE = case @ISHIVPOSITIVECODE when 1 then 1 when 2 then 0 end) 
     and (@HASCONDITIONCODE = 0 or (@HASCONDITIONCODE = 1 and SPC.SPROPPCHILDCONDITIONCODEID is not null) or (@HASCONDITIONCODE = 2 and SPC.SPROPPCHILDCONDITIONCODEID is null)) 
     and (@ISORPHANEDCODE = 0 or SPC.ISORPHANED = case @ISORPHANEDCODE when 1 then 1 when 2 then 0 end) 
     and  SP.AVAILABILITYCODE = 0 and SP.ELIGIBILITYCODE = 1
 end         
else
 begin
    insert into @SELECTIONTABLE(CID, CGENDERCODE,CAGERANGEID)
    select SP.ID, C.GENDERCODE, SPA.ID from dbo.SPONSORSHIPOPPORTUNITY SP 
     inner join dbo.SPONSORSHIPOPPORTUNITYCHILD SPC on SP.ID = SPC.ID 
   inner join dbo.CONSTITUENT C on C.ID = SPC.CONSTITUENTID
     left join dbo.SPONSORSHIPOPPORTUNITYGROUP SPP on SPP.ID = SP.SPONSORSHIPOPPORTUNITYGROUPID 
     left outer join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SP.SPONSORSHIPLOCATIONID 
     left outer join dbo.SPONSORSHIPLOCATION PREFERREDLOCATION on PREFERREDLOCATION.ID = @OPPORTUNITYLOCATIONID 
     left outer join dbo.SPONSORSHIPOPPORTUNITYAGERANGE SPA on C.BIRTHDATE<>'00000000' and dbo.UFN_AGEFROMFUZZYDATE(SPC.BIRTHDATE,getdate()) between SPA.MINAGE and SPA.MAXAGE
     where (@GROUPID is null or SPONSORSHIPOPPORTUNITYGROUPID = @GROUPID) 
     and (@OPPORTUNITYLOCATIONID is null or OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(PREFERREDLOCATION.HIERARCHYPATH) = 1) 
     and (@GENDERCODE = 0 or SPC.GENDERCODE = case when @GENDERCODE = 3 then 1 else @GENDERCODE end or SPC.GENDERCODE = case when @GENDERCODE = 3 then 2 else @GENDERCODE end)  
     and (@SPROPPAGERANGEID is null or SPA.ID =  @SPROPPAGERANGEID ) 
     and (@ISHIVPOSITIVECODE = 0 or SPC.ISHIVPOSITIVE = case @ISHIVPOSITIVECODE when 1 then 1 when 2 then 0 end) 
     and (@HASCONDITIONCODE = 0 or (@HASCONDITIONCODE = 1 and SPC.SPROPPCHILDCONDITIONCODEID is not null) or (@HASCONDITIONCODE = 2 and SPC.SPROPPCHILDCONDITIONCODEID is null)) 
     and (@ISORPHANEDCODE = 0 or SPC.ISORPHANED = case @ISORPHANEDCODE when 1 then 1 when 2 then 0 end) 
     and  SP.AVAILABILITYCODE = 0 and SP.ELIGIBILITYCODE = 1
 end 

if @KEY = 0 
  begin
    select CID as CHILDID, CGENDERCODE as GENDER from  @SELECTIONTABLE
  end
else
begin
if @MATCH = 0 -- no balancing

 begin 
    select top(@KEY) CID as CHILDID, CGENDERCODE as GENDER from  @SELECTIONTABLE
 end
else
 begin 
  declare @MULTIPLIER int
  declare @MIN int  
  declare @GEN int
  declare @AGEID uniqueidentifier
  declare @NEWCOUNT int
  declare CURSOR_SELECTION cursor local forward_only for      
    select GENDERCODE, AGERANGEID, NEWCOUNT from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS    

  --delete from SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS 

  if @MATCH = 1 -- gender balanced    

    begin
        insert into #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS(ID, GENDERCODE, AGERANGEID, INITIALCOUNT, NEWCOUNT)
        select newid(), CGENDERCODE, null, COUNT(*) cnt, 0
        from @SELECTIONTABLE
        group by CGENDERCODE
        order by cnt

        select @MULTIPLIER = @@ROWCOUNT from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
        select @MIN = MIN(INITIALCOUNT) from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

        exec dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION @KEY, @MIN, @MULTIPLIER             

        open CURSOR_SELECTION;
            fetch next from CURSOR_SELECTION into
             @GEN, @AGEID, @NEWCOUNT;     
        while (@@FETCH_STATUS = 0)
            begin             
             insert into @RESULTTABLE(ID, GENDERCODE, AGERANGEID)
             select top(@NEWCOUNT) CID, CGENDERCODE, CAGERANGEID
             from @SELECTIONTABLE
             where CGENDERCODE = @GEN      

             fetch next from CURSOR_SELECTION into   
             @GEN, @AGEID, @NEWCOUNT;
            end       
          close CURSOR_SELECTION;            

        select ID as CHILDID, GENDERCODE AS GENDER from @RESULTTABLE
        --select top(@KEY) CID, CAGERANGEID, CGENDERCODE from @SELECTIONTABLE, SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

        --where CGENDERCODE = GENDERCODE 

        --order by CGENDERCODE            

    end
  if @MATCH = 2 -- age range balanced    

    begin
        insert into #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS(ID, GENDERCODE, AGERANGEID, INITIALCOUNT, NEWCOUNT)
        select newid(), 0, CAGERANGEID, COUNT(*) cnt, 0  
        from @SELECTIONTABLE
        group by CAGERANGEID
        order by cnt

        select @MULTIPLIER = @@ROWCOUNT from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
        select @MIN = MIN(INITIALCOUNT) from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

        exec dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION @KEY, @MIN, @MULTIPLIER     

        open CURSOR_SELECTION;
            fetch next from CURSOR_SELECTION into
             @GEN, @AGEID, @NEWCOUNT;     
        while (@@FETCH_STATUS = 0)
            begin             
             insert into @RESULTTABLE(ID, GENDERCODE, AGERANGEID)
             select top(@NEWCOUNT) CID, CGENDERCODE, CAGERANGEID
             from @SELECTIONTABLE
             where CAGERANGEID = @AGEID

             fetch next from CURSOR_SELECTION into
             @GEN, @AGEID, @NEWCOUNT;
            end       
          close CURSOR_SELECTION;          

        select ID as CHILDID, GENDERCODE AS GENDER from @RESULTTABLE        

        --select top(@KEY) CID, CAGERANGEID, CGENDERCODE from @SELECTIONTABLE, SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

        --where CAGERANGEID = AGERANGEID    

        --order by CAGERANGEID

    end    
  if @MATCH = 3 -- gender and age range balanced

    begin
        insert into #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS(ID, GENDERCODE, AGERANGEID, INITIALCOUNT, NEWCOUNT)
        select newid(), CGENDERCODE, CAGERANGEID, COUNT(*) cnt, 0
        from @SELECTIONTABLE
        group by CGENDERCODE, CAGERANGEID
        order by cnt

        select @MULTIPLIER = @@ROWCOUNT from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS
        select @MIN = MIN(INITIALCOUNT) from #SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

        exec dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE_SELECTION @KEY, @MIN, @MULTIPLIER     

        open CURSOR_SELECTION;
            fetch next from CURSOR_SELECTION into
             @GEN, @AGEID, @NEWCOUNT;     
        while (@@FETCH_STATUS = 0)
            begin             
             insert into @RESULTTABLE(ID, GENDERCODE, AGERANGEID)
             select top(@NEWCOUNT) CID, CGENDERCODE, CAGERANGEID
             from @SELECTIONTABLE
             where CAGERANGEID = @AGEID
             and CGENDERCODE = @GEN

             fetch next from CURSOR_SELECTION into
             @GEN, @AGEID, @NEWCOUNT;
            end       
          close CURSOR_SELECTION;          

        select ID as CHILDID, GENDERCODE AS GENDER from @RESULTTABLE        

        --select top(@KEY) CID, CAGERANGEID, CGENDERCODE from @SELECTIONTABLE, SPONSORSHIPOPPORTUNITYRESERVATIONCOUNTS

        --where CGENDERCODE = GENDERCODE and CAGERANGEID = AGERANGEID

        --order by CAGERANGEID, CGENDERCODE

     end  
      deallocate CURSOR_SELECTION;  
 end         
end