USP_SPONSORSHIPSMARTFIELD_AVAILABLEVALUESPROJECT

CREATE procedure dbo.USP_SPONSORSHIPSMARTFIELD_AVAILABLEVALUESPROJECT(
    @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,
                            SPROPPPROJECTCATEGORYCODEID uniqueidentifier);

    insert into @SEGMENTS
    select
        newid(),
        SPONSORSHIPOPPORTUNITYGROUP.ID,
        SPONSORSHIPLOCATION.ID,
        SPROPPPROJECTCATEGORYCODE.ID
    from SPONSORSHIPOPPORTUNITYGROUP
    left outer join dbo.SPONSORSHIPLOCATION on SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID in(select FIELDID from @LOCATIONDIMENSIONS)
    left outer join dbo.SPROPPPROJECTCATEGORYCODE on 'Project category' in(select FIELDID from @DIMENSIONS)
    where SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYTYPECODE = 2;

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


    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.SPONSORSHIPOPPORTUNITYPROJECT on SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIPOPPORTUNITY.ID
    inner join @SEGMENTS SEGMENT on
        SEGMENT.SPONSORSHIPOPPORTUNITYGROUPID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPOPPORTUNITYGROUPID and
        isnull(SEGMENT.SPONSORSHIPLOCATIONID,@NULLID) = case when (select count(*) from @LOCATIONDIMENSIONS) = 1 then DIMENSIONLOCATION.ID else @NULLID end and
        isnull(SEGMENT.SPROPPPROJECTCATEGORYCODEID,@NULLID) = case when (select count(*) from @DIMENSIONS where FIELDID = 'Project category') = 1 then SPONSORSHIPOPPORTUNITYPROJECT.SPROPPPROJECTCATEGORYCODEID else @NULLID 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