USP_REPORT_CHILDINVENTORY_PROCESSDIMENSION

CREATE procedure dbo.USP_REPORT_CHILDINVENTORY_PROCESSDIMENSION
(
    @DIMENSION nvarchar(100) = '',
    @VALUE nvarchar(255) = '',
    @FROM nvarchar(255) output,
    @COLUMN nvarchar(255) output,
    @WHERE nvarchar(255) output,
    @COLUMNALIAS nvarchar(255) = '',
    @HAVING nvarchar(255) output
)
as
begin

    -- program

    if @DIMENSION = 0 
    begin
        set @FROM = ' inner join dbo.SPONSORSHIPPROGRAM PRG on PRG.SPONSORSHIPOPPORTUNITYGROUPID = OPP.SPONSORSHIPOPPORTUNITYGROUPID ';
        set @COLUMN = ' PRG.NAME as ' + @COLUMNALIAS;
        -- need to fix this.  where clause not working.  @value needs to be sent in as variable

        -- and on usp_report_childinventory, when calling sqltoexec, send value is as param.

        set @WHERE = ' and (' + @VALUE + ' is null or PRG.ID = ' + @VALUE + ')';
    end
    -- Location types

    -- @DIMENSION = 1

    -- handled in USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION

    --Date of last update (not handled here)

    --if @DIMENSION = 6

    --begin

      --set @FROM = '';

      --set @COLUMN = ' SOC.DATECHANGED as ' + @COLUMNALIAS;

        --set @WHERE = ' and (' + @VALUE + ' is not null or SOC.DATECHANGED = ' + @VALUE + ') ';

    --end

    -- Eligibility (eligibility)

    if @DIMENSION = 11
    begin
      set @COLUMN = ' OPP.ELIGIBILITY as ' + @COLUMNALIAS;
        set @FROM = '';
        set @WHERE = ' and (' + @VALUE + ' is null or OPP.ELIGIBILITYCODE = ' + @VALUE + ') ';
    end
    -- Age

    if @DIMENSION = 8 
    begin
      set @COLUMN = ' (select displayname from dbo.SPONSORSHIPOPPORTUNITYAGERANGE SOAR '
      set @COLUMN = @COLUMN + ' where  dbo.UFN_AGEFROMFUZZYDATE(CON.BIRTHDATE,getdate()) between SOAR.MINAGE and SOAR.MAXAGE '
        set @COLUMN = @COLUMN + '    AND (' + @VALUE + '= SOAR.ID or ' + @VALUE + ' is null))  as ' + @COLUMNALIAS;
      set @FROM = ''
        set @WHERE = ''
        set @HAVING = ' HAVING ' + @COLUMNALIAS + ' is not null'
    end
        -- Gender

    if @DIMENSION = 9
    begin
        set @FROM = '';
        set @COLUMN = ' CON.GENDER as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or '+ @VALUE + ' = CON.GENDERCODE) ';
    end
    -- Availability 

    if @DIMENSION = 10 or @DIMENSION = 22
    begin
      set @COLUMN = ' OPP.AVAILABILITY as ' + @COLUMNALIAS;
        set @FROM = '';
        set @WHERE = ' and (' + @VALUE + ' is null or OPP.AVAILABILITYCODE = ' + @VALUE + ') ';
    end
    -- Project Status

    if @DIMENSION = 16
    begin
      set @COLUMN = ' (CASE OPP.ELIGIBILITY when ''Eligible'' then ''Open'' '+
                                ' when ''Ineligible'' then ''Closed'' end) as ' + @COLUMNALIAS;
        set @FROM = '';
        set @WHERE = ' and (' + @VALUE + ' is null or OPP.ELIGIBILITYCODE = ' + @VALUE + ') ';
    end
    -- Group

    if @DIMENSION = 19
    begin
        set @FROM = ''-- inner join dbo.SPONSORSHIPOPPORTUNITYGROUP SOG on OPP.SPONSORSHIPOPPORTUNITYGROUPID = SOG.ID ';

        set @COLUMN = ' SOG.NAME as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or SOG.ID = ' + @VALUE + ')';
    end
    -- Project Category

    if @DIMENSION = 20
    begin
     set @FROM = ' left join dbo.SPROPPPROJECTCATEGORYCODE SOCAT on SOCAT.ID = SOP.SPROPPPROJECTCATEGORYCODEID '
   set @COLUMN = ' SOCAT.DESCRIPTION as ' + @COLUMNALIAS;
     set @WHERE = ' and (' + @VALUE + ' is null or SOCAT.ID = ' + @VALUE + ')';
    end
end