USP_REPORT_PROCESSDIMENSION

CREATE procedure dbo.USP_REPORT_PROCESSDIMENSION
(
    @DIMENSION nvarchar(100) = null,
    @VALUE nvarchar(255) = null,
    @FROM nvarchar(255) = null output,
    @COLUMN nvarchar(max) = null output,
    @WHERE nvarchar(max) = null output,
    @COLUMNALIAS nvarchar(255) = null
)
as
begin

    -- program

    if @DIMENSION in (0,16,18,22) 
    begin
        set @FROM = ' inner join dbo.SPONSORSHIPPROGRAM PRG on PRG.ID = SP.SPONSORSHIPPROGRAMID ';
        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_acquisition, when calling sqltoexec, send value is as param.

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

    if @DIMENSION = 1
    begin
    set @FROM =''
        set @COLUMN = ', (select DESCRIPTION from dbo.CHANNELCODE where ID = RV.CHANNELCODEID) as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or RV.CHANNELCODEID = ' + @VALUE + ')';
    end

    -- Location types

    -- @DIMENSION = 2

    -- handled in USP_REPORT_PROCESSLOCATIONTYPE_DIMENSION


    -- Appeal

    if @DIMENSION = 3
    begin
    set @FROM =''
        set @COLUMN = ', (select DESCRIPTION from dbo.APPEAL where ID = RV.APPEALID) as ' + @COLUMNALIAS;
        set @WHERE = ' and  (' + @VALUE + ' is null or RV.APPEALID = ' + @VALUE + ')';
    end
    -- Frequency

    if @DIMENSION = 4
    begin
        set @FROM = ' inner join dbo.REVENUESCHEDULE SCH on SCH.ID = RV.ID ';
        set @COLUMN = ', SCH.FREQUENCY as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or SCH.FREQUENCYCODE = ' + @VALUE + ') ';
    end
    -- Payment Method

    if @DIMENSION in (5, 17)
    begin
        set @FROM = ' inner join dbo.REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = RV.ID ';
        set @COLUMN = ', RPM.PAYMENTMETHOD as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or RPM.PAYMENTMETHODCODE = ' + @VALUE + ') ';
    end
  -- Reason

    if @DIMENSION = 6
    begin
        set @FROM = ' inner join dbo.SPONSORSHIPREASON SR on SR.ID = ST.SPONSORSHIPREASONID ';
        set @COLUMN = ', dbo.UFN_SPONSORSHIPREASON_GETNAME(SR.ID) as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or SR.ID = ' + @VALUE + ') ';
    end
  -- Tenure

  if @DIMENSION = 7
    begin
    set @FROM = ''
        --set @COLUMN = ', dbo.UFN_SPONSORSHIPCANCELLATIONREPORTTENURE_GETNAME(' + @VALUE +') as ' +  @COLUMNALIAS ;

    set @COLUMN =', (select DESCRIPTION from dbo.SPONSORSHIPCANCELLATIONREPORTTENURECODE where dbo.UFN_SPONSORSHIPCANCELLATIONREPORT_CHECKTENURE(ID,SP.ID,SP.ENDDATE)=1) as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null  or dbo.UFN_SPONSORSHIPCANCELLATIONREPORT_CHECKTENURE('+@VALUE+',SP.ID,SP.ENDDATE)=1) ';
    end
  -- Channel for acquisition report

    if @DIMENSION = 12
    begin
        set @COLUMN = ', (select DESCRIPTION from dbo.CHANNELCODE where ID = CASE RGDFH.SEQUENCE when 0 then RGDFH.CHANNELCODEID else RV.CHANNELCODEID end) as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or ' + @VALUE + ' =  CASE RGDFH.SEQUENCE when 0 then RGDFH.CHANNELCODEID else RV.CHANNELCODEID end)';
    end
    -- Appeal for acquisition report

    if @DIMENSION = 13
    begin
        set @COLUMN = ', (select DESCRIPTION from dbo.APPEAL where ID = case RGDFH.SEQUENCE when 0 then RGDFH.APPEALID else RV.APPEALID end) as ' + @COLUMNALIAS;
        set @WHERE = ' and  (' + @VALUE + ' is null or ' + @VALUE + ' = case RGDFH.SEQUENCE when 0 then RGDFH.APPEALID else RV.APPEALID end)';
    end
    -- Frequency for acquisition report

    if @DIMENSION = 14
    begin
        set @FROM = ' inner join dbo.REVENUESCHEDULE SCH on SCH.ID = RV.ID ';
        set @COLUMN = ', (case RGDFH.SEQUENCE when 0 then RGDFH.FREQUENCY else SCH.FREQUENCY end) as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or ' + @VALUE + ' = case RGDFH.SEQUENCE when 0 then RGDFH.FREQUENCYCODE else SCH.FREQUENCYCODE end) ';
    end
  -- Reason for operations report

    if @DIMENSION = 15
    begin
        set @FROM = ' inner join dbo.SPONSORSHIPREASON SR on SR.ID = SPT1.SPONSORSHIPREASONID ';
        set @COLUMN = ', dbo.UFN_SPONSORSHIPREASON_GETNAME(SR.ID) as ' + @COLUMNALIAS;
        set @WHERE = ' and (' + @VALUE + ' is null or SR.ID = ' + @VALUE + ') ';
    end
  -- Number of sponsorships

  if @DIMENSION = 21
  begin
  set @COLUMN = ', case dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(SP.CONSTITUENTID) ' +
                ' when 1 then ''1 sponsorship'''+
                ' when 2 then ''2 sponsorships'''+
                ' when 3 then ''3 sponsorships'''+
                ' else ''4+ sponsorships'' end'+
                ' as ' + @COLUMNALIAS
      set @WHERE  = ' and (' + @VALUE+' is null or ((dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(SP.CONSTITUENTID) >=4 and '+@VALUE+'=''261295FA-C69B-4669-8A22-B489E445C485'') or ' +
                    ' (dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(SP.CONSTITUENTID) = 3  and '+@VALUE+'=''29C7D09B-87BB-4a6c-9499-330F0405BFEE'') or ' +
                    ' (dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(SP.CONSTITUENTID) = 2 and '+@VALUE+'=''DB2A0940-01D0-4bdf-BABD-E35D7397CD60'') or ' +
                    ' (dbo.UFN_SPONSORSHIP_GETACTIVESPONSORSHIPSFORSPONSOR(SP.CONSTITUENTID) = 1 and '+@VALUE+'=''15220822-AE9C-472a-A16A-1523AB107030'')))';  

  end
  if @DIMENSION = 23
  begin
    set @FROM = ' inner join dbo.REVENUESCHEDULE SCH on SCH.ID = RGA.SOURCEREVENUEID ';
    set @COLUMN = ', SCH.FREQUENCY as ' + @COLUMNALIAS;
    set @WHERE = ' and (' + @VALUE + ' is null or SCH.FREQUENCYCODE = ' + @VALUE + ') ';
  end

   --Mailings

  if @DIMENSION = 24
  begin
      set @FROM = ''
      set @COLUMN = ', (select NAME from dbo.MKTSEGMENTATION where ID= SEGACTIVE.SEGMENTATIONID) as  ' + @COLUMNALIAS
      set @WHERE = ' and ('+@VALUE+' is null or exists(select ID from dbo.UFN_IDSETREADER_GETRESULTS('+@VALUE+') where ID= SEGACTIVE.SEGMENTATIONID)) '     
  end

  --Appeals

  if @DIMENSION = 25
  begin
      set @FROM = ''
      set @COLUMN = ', (select NAME from dbo.APPEAL where SEGACTIVE.APPEALSYSTEMID= ID) as  ' + @COLUMNALIAS
      set @WHERE = ' and ('+@VALUE+' is null or exists(select ID from dbo.UFN_IDSETREADER_GETRESULTS('+@VALUE+') where ID= SEGACTIVE.APPEALSYSTEMID)) '     
  end

  --Segments

  if @DIMENSION=26
  begin
    set @FROM =' inner join dbo.MKTSEGMENTATIONSEGMENT MKTSS on MKTSS.SEGMENTATIONID = SEGACTIVE.SEGMENTATIONID ';
    set @COLUMN =', (select NAME from dbo.MKTSEGMENT where ID = MKTSS.SEGMENTID) as '+ @COLUMNALIAS;
    set @WHERE =' and ('+@VALUE+' is null or MKTSS.SEGMENTID = '+@VALUE+')';
  end

  --Packages

  if @DIMENSION=27
  begin
    set @FROM =' inner join dbo.MKTSEGMENTATIONPACKAGE MKTSP on MKTSP.SEGMENTATIONID = SEGACTIVE.SEGMENTATIONID ';
    set @COLUMN =', (select NAME from dbo.MKTPACKAGE where ID = MKTSP.PACKAGEID) as '+ @COLUMNALIAS;
    set @WHERE =' and ('+@VALUE+' is null or MKTSP.PACKAGEID = '+@VALUE+')';
  end
end