USP_DATAFORMTEMPLATE_ADD_PROGRAM

The save procedure used by the add dataform template "Program Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@LOCATIONS	xml	IN	Location
@PRICELISTID	uniqueidentifier	IN	Load price list
@PRICES	xml	IN	Prices
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CAPACITY	int	IN	Capacity
@PROGRAMCATEGORYCODEID	uniqueidentifier	IN	Category
@RESOURCES	xml	IN	Supplies/Equipment resources
@STAFFRESOURCES	xml	IN	Staffing resources
@ISPREREGISTERED	bit	IN	Requires registration

Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROGRAM
        (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier = null,
            @NAME nvarchar(100) = '',
            @DESCRIPTION nvarchar(255) = '',
            @LOCATIONS xml = null,
            --@HOLDLISTID uniqueidentifier = null,
            @PRICELISTID uniqueidentifier = null,
            @PRICES xml = null,
            @CURRENTAPPUSERID uniqueidentifier,
            @CAPACITY int = 0,
            @PROGRAMCATEGORYCODEID uniqueidentifier = null,
            @RESOURCES xml = null,
            @STAFFRESOURCES xml = null,
            @ISPREREGISTERED bit = 0
        )
        as

        set nocount on;

        if @ID is null
            set @ID = newid()

        if @CHANGEAGENTID is null  
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

        declare @CURRENTDATE datetime;
        set @CURRENTDATE = getdate();

        -- Default 0 for SEQUENCE since it is a new field and may not be passed in
        set @LOCATIONS = (
            select
                T.c.value('(CAPACITY)[1]','int') as 'CAPACITY',
                T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') as 'EVENTLOCATIONID',
                T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
                isnull(T.c.value('(SEQUENCE)[1]','int'), 0) as 'SEQUENCE'
            from
                @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)
            for xml raw('ITEM'),type,elements,root('LOCATIONS'),binary base64
        );

        if dbo.UFN_PRICELIST_ISEQUALTOPRICES(@PRICELISTID, @PRICES, 0) = 0 begin
            set @PRICELISTID = null;
        end

        begin try
            -- handle inserting the data
            insert into dbo.PROGRAM
            (
                ID, 
                NAME,
                DESCRIPTION,
                --HOLDLISTID,
                CAPACITY,
                PRICELISTID,
                PROGRAMCATEGORYCODEID,
                ISPREREGISTERED,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @ID, 
                @NAME,
                @DESCRIPTION,
                --@HOLDLISTID,
                @CAPACITY,
                @PRICELISTID,
                @PROGRAMCATEGORYCODEID,
                @ISPREREGISTERED,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            );

            exec dbo.USP_PROGRAM_GETSEQUENCEDLOCATIONS_ADDFROMXML @ID, @LOCATIONS, @CHANGEAGENTID;

            -- Handle updating the resources for the program
            exec dbo.USP_PROGRAMRESOURCE_GETRESOURCES_ADDFROMXML @ID, @RESOURCES, @CHANGEAGENTID;
            exec dbo.USP_PROGRAMSTAFFRESOURCE_GETRESOURCES_ADDFROMXML @ID, @STAFFRESOURCES, @CHANGEAGENTID;


          --populate the Daily, Advanced and Group sales with the program end sale time = program start time + 20 min
          insert into dbo.PROGRAMSALESMETHOD
          (
            ID,
            PROGRAMID,
            SALESMETHODID,
            ONSALETIME,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            ONSALEENDTYPECODE,
            ONSALEENDINTERVAL
          )
          select
            newid(),
            @ID,
            SALESMETHOD.ID,
            '0000',
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            3,
            20
          from
            dbo.SALESMETHOD
          where
            ISACTIVE = 1
      and 
        TYPECODE in (0,1,3);

          --populate Online Sales with the program end sale time  = program start time
          insert into dbo.PROGRAMSALESMETHOD
          (
            ID,
            PROGRAMID,
            SALESMETHODID,
            ONSALETIME,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          select
            newid(),
            @ID,
            SALESMETHOD.ID,
            '0000',
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          from
            dbo.SALESMETHOD
          where
            ISACTIVE = 1
            and TYPECODE = 2;

            exec dbo.USP_PROGRAM_GETPRICES_ADDFROMXML @ID, @PRICES, @CHANGEAGENTID, @CURRENTDATE;
        end try

        begin catch
            exec dbo.USP_RAISE_ERROR
            return 1
        end catch

        return 0