USP_DATAFORMTEMPLATE_VIEW_TICKETINGWORKFLOW

The load procedure used by the view dataform template "Ticketing Workflow Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@STATUSTAXENTITIES	nvarchar(3)	INOUT	tax entities available
@STATUSTAXES	nvarchar(3)	INOUT	taxes available
@STATUSFEES	nvarchar(3)	INOUT	fees available
@STATUSDELIVERYMETHODS	nvarchar(3)	INOUT	delivery methods available
@STATUSPRICETYPES	nvarchar(3)	INOUT	price types available
@STATUSSALESMETHODS	nvarchar(max)	INOUT	missing information
@STATUSPOSDONATIONS	nvarchar(512)	INOUT	Default designation
@STATUSMEMBERSHIPS	nvarchar(3)	INOUT	membership programs available
@STATUSCONSTITUENCIES	nvarchar(5)	INOUT	user-defined constituencies available
@STATUSCOUNTIES	nvarchar(5)	INOUT	counties available
@STATUSDISCOUNTS	nvarchar(5)	INOUT	discounts available
@STATUSLOCATIONS	nvarchar(6)	INOUT	locations available
@STATUSPRICELISTS	nvarchar(3)	INOUT	price lists available
@STATUSPROGRAMCATAGORIES	nvarchar(3)	INOUT	program categories available
@STATUSDAILYPROGRAMS	nvarchar(6)	INOUT	daily admission programs available
@STATUSRESOURCES	nvarchar(6)	INOUT	supply/equipment resources available
@STATUSEVENTPROGRAMS	nvarchar(6)	INOUT	scheduled programs available
@STATUSEVENTS	nvarchar(6)	INOUT	events scheduled
@STATUSMEMBERSHIPLEVELS	nvarchar(3)	INOUT	membership levels available
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	MEMBERSHIPPROGRAMID
@USERGRANTEDGOTOBASICMEMBERSHIPACTION	bit	INOUT	USERGRANTEDGOTOBASICMEMBERSHIPACTION
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@USERGRANTEDTAXENTITYCODETABLEADD	bit	INOUT	USERGRANTEDTAXENTITYCODETABLEADD
@USERGRANTEDTAXDATALIST	bit	INOUT	USERGRANTEDTAXDATALIST
@USERGRANTEDFEEDATALIST	bit	INOUT	USERGRANTEDFEEDATALIST
@USERGRANTEDDELIVERYMETHODSDATALIST	bit	INOUT	USERGRANTEDDELIVERYMETHODSDATALIST
@USERGRANTEDPRICETYPECODETABLEADD	bit	INOUT	USERGRANTEDPRICETYPECODETABLEADD
@USERGRANTEDMEMBERSHIPDATALIST	bit	INOUT	USERGRANTEDMEMBERSHIPDATALIST
@USERGRANTEDBASICMEMBERSHIPDATALIST	bit	INOUT	USERGRANTEDBASICMEMBERSHIPDATALIST
@USERGRANTEDCONSTITUENCYCODETABLEADD	bit	INOUT	USERGRANTEDCONSTITUENCYCODETABLEADD
@USERGRANTEDCOUNTYCODETABLEADD	bit	INOUT	USERGRANTEDCOUNTYCODETABLEADD
@USERGRANTEDDISCOUNTDATALIST	bit	INOUT	USERGRANTEDDISCOUNTDATALIST
@USERGRANTEDLOCATIONDATALIST	bit	INOUT	USERGRANTEDLOCATIONDATALIST
@USERGRANTEDPRICELISTDATALIST	bit	INOUT	USERGRANTEDPRICELISTDATALIST
@USERGRANTEDPROGRAMCATEGORYCODETABLEADD	bit	INOUT	USERGRANTEDPROGRAMCATEGORYCODETABLEADD
@USERGRANTEDDAILYPROGRAMSFORM	bit	INOUT	USERGRANTEDDAILYPROGRAMSFORM
@USERGRANTEDRESOURCEDATALIST	bit	INOUT	USERGRANTEDRESOURCEDATALIST
@USERGRANTEDEVENTPROGRAMSFORM	bit	INOUT	USERGRANTEDEVENTPROGRAMSFORM
@USERGRANTEDEVENTSFORM	bit	INOUT	USERGRANTEDEVENTSFORM
@STATUSSTAFFRESOURCES	nvarchar(6)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_TICKETINGWORKFLOW
(
    @DATALOADED bit = 0 output,
    @STATUSTAXENTITIES nvarchar(3) = null output,
    @STATUSTAXES nvarchar(3) = null output,
    @STATUSFEES nvarchar(3) = null output,
    @STATUSDELIVERYMETHODS nvarchar(3) = null output,
    @STATUSPRICETYPES nvarchar(3) = null output,
    @STATUSSALESMETHODS nvarchar(max) = null output,
    @STATUSPOSDONATIONS nvarchar(512) = null output,
    @STATUSMEMBERSHIPS nvarchar(3) = null output,
    @STATUSCONSTITUENCIES nvarchar(5) = null output,
    @STATUSCOUNTIES nvarchar(5) = null output,
    @STATUSDISCOUNTS nvarchar(5) = null output,
    @STATUSLOCATIONS nvarchar(6) = null output,
    @STATUSPRICELISTS nvarchar(3) = null output,
    @STATUSPROGRAMCATAGORIES nvarchar(3) = null output,
    @STATUSDAILYPROGRAMS nvarchar(6) = null output,
    @STATUSRESOURCES nvarchar(6) = null output,
    @STATUSEVENTPROGRAMS nvarchar(6) = null output,
    @STATUSEVENTS nvarchar(6) = null output,
    @STATUSMEMBERSHIPLEVELS nvarchar(3) = null output,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
    @USERGRANTEDGOTOBASICMEMBERSHIPACTION bit = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @USERGRANTEDTAXENTITYCODETABLEADD bit = null output,
    @USERGRANTEDTAXDATALIST bit = null output,
    @USERGRANTEDFEEDATALIST bit = null output,
    @USERGRANTEDDELIVERYMETHODSDATALIST bit = null output,
    @USERGRANTEDPRICETYPECODETABLEADD bit = null output,
    @USERGRANTEDMEMBERSHIPDATALIST bit = null output,
    @USERGRANTEDBASICMEMBERSHIPDATALIST bit = null output,
    @USERGRANTEDCONSTITUENCYCODETABLEADD bit = null output,
    @USERGRANTEDCOUNTYCODETABLEADD bit = null output,
    @USERGRANTEDDISCOUNTDATALIST bit = null output,
    @USERGRANTEDLOCATIONDATALIST bit = null output,
    @USERGRANTEDPRICELISTDATALIST bit = null output,
    @USERGRANTEDPROGRAMCATEGORYCODETABLEADD bit = null output,
    @USERGRANTEDDAILYPROGRAMSFORM bit = null output,
    @USERGRANTEDRESOURCEDATALIST bit = null output,
    @USERGRANTEDEVENTPROGRAMSFORM bit = null output,
    @USERGRANTEDEVENTSFORM bit = null output,
    @STATUSSTAFFRESOURCES nvarchar(6) = null output
)
as
    set nocount on;

    --Many variables are nvarchar instead of int; this is to facilitate string replacement in the UI

    set @DATALOADED = 1
    set @STATUSTAXENTITIES = (select count(*) from dbo.TAXENTITYCODE where ACTIVE = 1);
    set @STATUSTAXES = (select count(*) from dbo.TAX where ISACTIVE = 1);
    set @STATUSFEES = (select count(*) from dbo.FEE where ISACTIVE = 1);
    set @STATUSDELIVERYMETHODS = (select count(*) from dbo.DELIVERYMETHOD where ISACTIVE = 1);
    set @STATUSPRICETYPES = (select count(*) from dbo.PRICETYPECODE where ACTIVE = 1);
    set @STATUSMEMBERSHIPS = (select count(*) from dbo.MEMBERSHIPPROGRAM where ISACTIVE = 1);
    set @STATUSMEMBERSHIPLEVELS = (select count(*) from dbo.MEMBERSHIPLEVEL where ISACTIVE = 1);
    set @STATUSCONSTITUENCIES = (select count(*) from dbo.CONSTITUENCYCODE where ACTIVE = 1);
    set @STATUSCOUNTIES = (select count(*) from dbo.COUNTYCODE where ACTIVE = 1);
    set @STATUSDISCOUNTS = (select count(*) from dbo.DISCOUNT where ISACTIVE = 1);
    set @STATUSLOCATIONS = (select count(*) from dbo.EVENTLOCATION);
    set @STATUSPRICELISTS = (select count(*) from dbo.PRICELIST where ISACTIVE = 1);
    set @STATUSPROGRAMCATAGORIES = (select count(*) from dbo.PROGRAMCATEGORYCODE where ACTIVE = 1);
    set @STATUSDAILYPROGRAMS = (select count(*) from dbo.PROGRAM where ISDAILYADMISSION = 1 and ISACTIVE = 1);
    set @STATUSRESOURCES = (select count(*) from dbo.RESOURCE where ISACTIVE = 1);
    set @STATUSEVENTPROGRAMS = (select count(*) from dbo.PROGRAM where ISDAILYADMISSION = 0 and ISACTIVE = 1);
    set @STATUSEVENTS = (select count(*) from dbo.EVENT where not (PROGRAMID is null) and ISACTIVE = 1);
    set @STATUSSTAFFRESOURCES = (select count(*) from dbo.VOLUNTEERTYPE);

    --since there's already a record there, make sure it's defined
    set @STATUSPOSDONATIONS = (select top(1) dbo.UFN_DESIGNATION_BUILDNAME(DESIGNATIONID) from DONATIONPOS);

    --Get the incomplete sales methods
    set @STATUSSALESMETHODS =  (
                                select dbo.UDA_BUILDLIST([TYPE]) 
                                from dbo.SALESMETHOD SM
                                where
                                    SM.ISACTIVE = 1 and
                                    (
                                        not exists (
                                                            select SMDM.ID 
                                                            from dbo.SALESMETHODDELIVERYMETHOD SMDM
                                                            where 
                                                                SM.TYPECODE = 3 or
                                                                (
                                                                    SMDM.SALESMETHODID = SM.ID and
                                                                    SMDM.ISDEFAULT = 1 
                                                                )
                                                        ) or
                                        not exists (
                                                            select SMPM.ID
                                                            from dbo.SALESMETHODPAYMENTMETHOD SMPM
                                                            where SMPM.SALESMETHODID = SM.ID 
                                                        ) or
                                        not exists (
                                                            select PTC.ID
                                                            from dbo.PRICETYPECODE PTC
                                                            where PTC.ID not in (
                                                                select PRICETYPECODEID 
                                                                 from dbo.SALESMETHODEXCLUDEDPRICETYPE SMEPT 
                                                                 where SMEPT.SALESMETHODID = SM.ID
                                                            )
                                                        )
                                        )
                                    )

    set @MEMBERSHIPPROGRAMID = (select top(1) ID from dbo.MEMBERSHIPPROGRAM where ISACTIVE = 1 order by NAME asc);
    if @MEMBERSHIPPROGRAMID is null
        set @MEMBERSHIPPROGRAMID = '00000000-0000-0000-0000-000000000000'

    set @USERGRANTEDGOTOBASICMEMBERSHIPACTION = 0;

    declare @USERISSYSADMIN bit = 0;
    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        set @USERISSYSADMIN = 1;

    -- Ensure that user has rights to membership page expression view and that 'Membership' flag is not on
    if (    
        [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '7714bf1f-2f96-45a8-a6de-c1ad36a2752c') = 1
        or 
        @USERISSYSADMIN = 1
    ) and 
    not exists (
        select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '5E696818-62EE-4C90-8C28-B1F52D77176E'
    )
    set @USERGRANTEDGOTOBASICMEMBERSHIPACTION = 1;

    -- Move security checks from UIModel to view form load to reduce server calls
    if @USERISSYSADMIN = 1
    begin
        select
            @USERGRANTEDTAXENTITYCODETABLEADD = 1,
            @USERGRANTEDTAXDATALIST = 1,
            @USERGRANTEDFEEDATALIST = 1,
            @USERGRANTEDDELIVERYMETHODSDATALIST = 1,
            @USERGRANTEDPRICETYPECODETABLEADD = 1,
            @USERGRANTEDMEMBERSHIPDATALIST = 1,
            @USERGRANTEDBASICMEMBERSHIPDATALIST = 1,
            @USERGRANTEDCONSTITUENCYCODETABLEADD = 1,
            @USERGRANTEDCOUNTYCODETABLEADD = 1,
            @USERGRANTEDDISCOUNTDATALIST = 1,
            @USERGRANTEDLOCATIONDATALIST = 1,
            @USERGRANTEDPRICELISTDATALIST = 1,
            @USERGRANTEDPROGRAMCATEGORYCODETABLEADD = 1,
            @USERGRANTEDDAILYPROGRAMSFORM = 1,
            @USERGRANTEDRESOURCEDATALIST = 1,
            @USERGRANTEDEVENTPROGRAMSFORM = 1,
            @USERGRANTEDEVENTSFORM = 1
    end
    else
    begin
        select
            @USERGRANTEDTAXENTITYCODETABLEADD = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@CURRENTAPPUSERID,'fcb1cc57-2a60-43e0-8e68-cf2728ce4970'),
            @USERGRANTEDTAXDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'ad115bc0-9576-47af-aa28-28138d288d51'),
            @USERGRANTEDFEEDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'dbbd488c-b9eb-474c-a5db-196709c5558c'),
            @USERGRANTEDDELIVERYMETHODSDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'b89e4c58-b42e-462a-a018-f1a4a729a476'),
            @USERGRANTEDPRICETYPECODETABLEADD = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@CURRENTAPPUSERID,'25bd634e-a93b-4f96-9ed0-64240ebcb4b2'),
            @USERGRANTEDMEMBERSHIPDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'a12f173d-01f9-43eb-a7ae-a96d65296a53'),
            @USERGRANTEDBASICMEMBERSHIPDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'7ec8baba-9630-446e-ad2c-0db76607e8f1'),
            @USERGRANTEDCONSTITUENCYCODETABLEADD = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@CURRENTAPPUSERID,'c5b35c5a-7ef0-47c6-aa54-7a1fdf32fda1'),
            @USERGRANTEDCOUNTYCODETABLEADD = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@CURRENTAPPUSERID,'131124bf-d081-4530-ba6d-8c53e5060632'),
            @USERGRANTEDDISCOUNTDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'588508fd-a596-41e9-bfa0-409edd2abe3f'),
            @USERGRANTEDLOCATIONDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'158f03aa-5e89-4c81-9cfd-41bf9b5b37ea'),
            @USERGRANTEDPRICELISTDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'b2f01ce7-549a-4a14-bde0-6d4f67f1595d'),
            @USERGRANTEDPROGRAMCATEGORYCODETABLEADD = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@CURRENTAPPUSERID,'b2132964-cb24-4380-b919-cf80e643b180'),
            @USERGRANTEDDAILYPROGRAMSFORM = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'f183ff39-59fb-4afc-b0ff-1a2a86d8c43f'),
            @USERGRANTEDRESOURCEDATALIST = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'43587f2b-75d2-4b49-9369-95de987e1b82'),
            @USERGRANTEDEVENTPROGRAMSFORM = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4faa1f19-cd78-496c-a2ce-b48403e469a5'),
            @USERGRANTEDEVENTSFORM = 
                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'7d0c38c3-8e71-486c-ab06-c982db38a4c8')
    end

    return 0;