USP_SPONSORSHIPSMARTFIELD_AVAILABLEVALUESCHILD

CREATE procedure dbo.USP_SPONSORSHIPSMARTFIELD_AVAILABLEVALUESCHILD(
    @DIMENSIONLIST xml,
    @CALCULATIONTYPE tinyint = 0,
    @ASOF datetime
)
as
begin
    set nocount on;

    declare @DIMENSIONS table (FIELDID nvarchar(50))
    declare @LOCATIONDIMENSIONS table (FIELDID nvarchar(36))

    declare @NULLID uniqueidentifier
    set @NULLID = cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)

    insert into @DIMENSIONS
    select *
    from dbo.UFN_SPONSORSHIPOPPORTUNITYDIMENSIONLIST_FROMITEMLISTXML(@DIMENSIONLIST)

    declare @CHECKERROR int
    select @CHECKERROR = count(*)
    from (select FIELDID from @DIMENSIONS group by FIELDID having count(*)>1) X;

    if @CHECKERROR > 1
    begin
      raiserror('BBERR_DUPLICATEDIMENSION', 13, 1);
      return 1
    end

    insert into @LOCATIONDIMENSIONS
    select D.FIELDID
    from @DIMENSIONS D
    inner join SPONSORSHIPLOCATIONTYPECODE on D.FIELDID = cast(SPONSORSHIPLOCATIONTYPECODE.ID as nvarchar(36))

    if @@ROWCOUNT > 1
    begin
      raiserror('Process could not finish. More than one location type listed.', 13, 1);
      return 1
    end

    delete from @DIMENSIONS
    where FIELDID in(select cast(FIELDID as nvarchar(36)) from @LOCATIONDIMENSIONS)

    declare @SEGMENTS table(ID uniqueidentifier,
                            SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
                            SPONSORSHIPLOCATIONID uniqueidentifier,
                            SPROPPAGERANGEID uniqueidentifier,
                            SPROPPCONDITIONCODEID uniqueidentifier,
                            GENDERCODE tinyint);

    insert into @SEGMENTS
    select
        newid(),
        SPONSORSHIPOPPORTUNITYGROUP.ID,
        SPONSORSHIPLOCATION.ID,
        SPONSORSHIPOPPORTUNITYAGERANGE.ID,
        SPROPPCHILDCONDITIONCODE.ID,
        GENDER.GENDERCODE
    from SPONSORSHIPOPPORTUNITYGROUP
    left outer join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID in(select FIELDID from @LOCATIONDIMENSIONS)
    left outer join dbo.SPONSORSHIPOPPORTUNITYAGERANGE on 'Child age range' in(select FIELDID from @DIMENSIONS)
    left outer join dbo.SPROPPCHILDCONDITIONCODE on 'Child disability/illness' in(select FIELDID from @DIMENSIONS)
    left outer join (select 0 GENDERCODE union all select 1 union all select 2) GENDER on GENDER.GENDERCODE = 0 or 'Child gender' in(select FIELDID from @DIMENSIONS)
    where SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE = 1;

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


    declare @OPPORTUNITIES table (OPPORTUNITYID uniqueidentifier,
                                  SEGMENTID uniqueidentifier,
                                  AVAILABLE bit)

    insert into @OPPORTUNITIES
    select SPONSORSHIPOPPORTUNITY.ID, SEGMENT.ID, case when AVAILABILITYCODE = 0 then 1 else 0 end
    from dbo.SPONSORSHIPOPPORTUNITY
    inner join dbo.SPONSORSHIPLOCATION OPPORTUNITYLOCATION on OPPORTUNITYLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID
    left outer join dbo.SPONSORSHIPLOCATION DIMENSIONLOCATION on OPPORTUNITYLOCATION.HIERARCHYPATH.IsDescendantOf(DIMENSIONLOCATION.HIERARCHYPATH) = 1 and DIMENSIONLOCATION.SPONSORSHIPLOCATIONTYPECODEID in(select FIELDID from @LOCATIONDIMENSIONS)
    inner join dbo.SPONSORSHIPOPPORTUNITYCHILD on SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIPOPPORTUNITY.ID
  inner join dbo.CONSTITUENT on CONSTITUENT.ID = SPONSORSHIPOPPORTUNITYCHILD.CONSTITUENTID
    inner join @SEGMENTS SEGMENT on
        SEGMENT.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID and
        isnull(SEGMENT.SPONSORSHIPLOCATIONID,@NULLID) = isnull(DIMENSIONLOCATION.ID,@NULLID) and
        isnull(SEGMENT.SPROPPAGERANGEID,@NULLID) = case when (select count(*) from @DIMENSIONS where FIELDID = 'Child age range') = 1 then (select ID from dbo.SPONSORSHIPOPPORTUNITYAGERANGE where CONSTITUENT.BIRTHDATE<>'00000000' and dbo.UFN_AGEFROMFUZZYDATE(CONSTITUENT.BIRTHDATE,getdate()) between SPONSORSHIPOPPORTUNITYAGERANGE.MINAGE and SPONSORSHIPOPPORTUNITYAGERANGE.MAXAGE) else @NULLID end and
        isnull(SEGMENT.SPROPPCONDITIONCODEID,@NULLID) = case when (select count(*) from @DIMENSIONS where FIELDID = 'Child disability/illness') = 1 then SPONSORSHIPOPPORTUNITYCHILD.SPROPPCHILDCONDITIONCODEID else @NULLID end and
        SEGMENT.GENDERCODE = case when (select count(*) from @DIMENSIONS where FIELDID = 'Child gender') = 1 then CONSTITUENT.GENDERCODE else 0 end
    where SPONSORSHIPOPPORTUNITY.ELIGIBILITYCODE = 1
    and (@CALCULATIONTYPE = 1 or SPONSORSHIPOPPORTUNITY.AVAILABILITYCODE = 0);


    if @CALCULATIONTYPE = 0  -- count

        select O.OPPORTUNITYID, S.VAL
        from @OPPORTUNITIES O
        inner join (select SEGMENTID, count(*) VAL
                    from @OPPORTUNITIES
                    group by SEGMENTID) S on S.SEGMENTID = O.SEGMENTID;

    else if @CALCULATIONTYPE = 1    -- percent available

        select O.OPPORTUNITYID, S.VAL
        from @OPPORTUNITIES O
        inner join (select SEGMENTID,
                           cast(sum(cast(AVAILABLE as tinyint)) as float)/count(*)*100 VAL
                    from @OPPORTUNITIES
                    group by SEGMENTID) S on S.SEGMENTID = O.SEGMENTID;

end