USP_DATAFORMTEMPLATE_VIEW_RATESCALE

The load procedure used by the view dataform template "Rate Scale View Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@PRICES	xml	INOUT	PRICES
@INCLUDEALLPROGRAMS	bit	INOUT	Include all programs
@PROGRAMS	xml	INOUT	Programs
@USEPERTICKETAFTERMAX	bit	INOUT	Uses per ticket pricing after max

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RATESCALE
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @PRICES xml = null output,
                @INCLUDEALLPROGRAMS bit = null output,
                @PROGRAMS xml = null output,
                @USEPERTICKETAFTERMAX bit = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;

                select 
                    @DATALOADED = 1,
                    @INCLUDEALLPROGRAMS = INCLUDEALLPROGRAMS,
                    @USEPERTICKETAFTERMAX = USEPERTICKETAFTERMAX
                from dbo.RATESCALE
                where ID = @ID

                if @DATALOADED = 1
                begin
                    set @PRICES = (
                        select
                            case
                                when GROUPMINIMUM = 0 and GROUPMAXIMUM = -1 then
                                    ''
                                when (GROUPMINIMUM = 0 and GROUPMAXIMUM <> -1) or (GROUPMINIMUM = GROUPMAXIMUM) then
                                    cast(GROUPMAXIMUM as nvarchar(6))
                                when (GROUPMINIMUM <> 0 and GROUPMAXIMUM = -1) Then
                                    cast(GROUPMINIMUM as nvarchar(6))
                                else
                                    cast(GROUPMINIMUM as nvarchar(6)) + ' - ' + cast(GROUPMAXIMUM as nvarchar(6))
                                end as SIZE,
                            AMOUNT,
                            case
                                when GROUPMINIMUM = 0 and GROUPMAXIMUM = -1 then 0
                                when GROUPMINIMUM = 0 and GROUPMAXIMUM <> -1 then 1
                                when GROUPMINIMUM <> 0 and GROUPMAXIMUM = -1 then 2
                                else 3
                                end as FIELDTYPE,
                            cast(1 as nvarchar(1)) as DISPLAYAMOUNT
                        from dbo.RATESCALEPRICE
                        where RATESCALEID = @ID
                        order by GROUPMINIMUM                
                        for xml raw('ITEM'),type,elements,root('PRICES'),binary base64
                    )    

                    if @INCLUDEALLPROGRAMS = 0
                    begin
                        set @PROGRAMS = (
                            select
                                PROGRAM.NAME as PROGRAMNAME
                            from dbo.RATESCALEPROGRAM
                            inner join dbo.PROGRAM on
                                PROGRAM.ID = RATESCALEPROGRAM.PROGRAMID
                            where RATESCALEID = @ID
                            for xml raw('ITEM'),type,elements,root('PROGRAMS'),binary base64
                        )
                    end                    
                end

                return 0;