USP_DATAFORMTEMPLATE_VIEW_DAILYSALESDATA

The load procedure used by the view dataform template "Daily Sales Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SALESORDERID	uniqueidentifier	INOUT	ID
@ORDERNUMBER	int	INOUT	Order number
@CAPTUREMARKETINGINFO	bit	INOUT	Capture marketing info
@CONSTITUENTID	uniqueidentifier	INOUT	Order patron
@PRIMARYMEMBERSHIPID	uniqueidentifier	INOUT	Patron's primary membership
@CONSTITUENTEXISTS	bit	INOUT	Constituent exists on order
@PRIMARYMEMBERSHIPDESCRIPTION	nvarchar(200)	INOUT	Description of membership
@CONSTITUENTNAME	nvarchar(255)	INOUT	Constituent name
@COMPLETEMARKETINGSURVEY	bit	INOUT	COMPLETEMARKETINGSURVEY
@CONSTITUENTHASADDRESS	bit	INOUT	CONSTITUENTHASADDRESS
@CONSTITUENTPOSTCODE	nvarchar(20)	INOUT	CONSTITUENTPOSTCODE
@CONSTITUENTCOUNTRYID	uniqueidentifier	INOUT	CONSTITUENTCOUNTRYID
@CONSTITUENTHASUSADDRESS	bit	INOUT	CONSTITUENTHASUSADDRESS
@USER_GRANTED_CONSTITPAGE	bit	INOUT	USER_GRANTED_CONSTITPAGE

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESDATA
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @SALESORDERID uniqueidentifier = null output,
                    @ORDERNUMBER int = null output,
                    @CAPTUREMARKETINGINFO bit = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @PRIMARYMEMBERSHIPID uniqueidentifier = null output,
                    @CONSTITUENTEXISTS bit = null output,
                    @PRIMARYMEMBERSHIPDESCRIPTION nvarchar(200) = null output,
                    @CONSTITUENTNAME nvarchar(255) = null output,
                    @COMPLETEMARKETINGSURVEY bit = null output,
                    @CONSTITUENTHASADDRESS bit = null output,
                    @CONSTITUENTPOSTCODE nvarchar(20) = null output,
                    @CONSTITUENTCOUNTRYID uniqueidentifier = null output,
                    @CONSTITUENTHASUSADDRESS bit = null output,
                    @USER_GRANTED_CONSTITPAGE bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 begin
                        set @USER_GRANTED_CONSTITPAGE = 1
                    end
                    else begin
                        set @USER_GRANTED_CONSTITPAGE = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'0c836902-a398-47a0-91eb-8b66e434148e')
                    end

                    declare @DAILYSALESMETHODID uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(0);

                    select @CAPTUREMARKETINGINFO = SMMC.CAPTUREMARKETINGINFO
                    from dbo.SALESMETHODMARKETINGCONFIGURATION as SMMC
                    where SMMC.ID = @DAILYSALESMETHODID;

                    if exists(
                        select 1 from dbo.SALESMETHODMARKETINGSURVEYCONFIGURATION SMMSC
                        where SMMSC.SALESMETHODID = @DAILYSALESMETHODID
                        and SMMSC.ISACTIVE = 1
                        )
                        set @COMPLETEMARKETINGSURVEY = 1
                    else
                        set @COMPLETEMARKETINGSURVEY = 0

                    exec dbo.USP_DATAFORMTEMPLATE_ADD_ORDERCREATECHECK @ID = @SALESORDERID output, @SALESMETHODTYPECODE = 0, @CURRENTAPPUSERID = @CURRENTAPPUSERID

                    select 
                        @DATALOADED = 1,
                        @ORDERNUMBER = SALESORDER.SEQUENCEID,
                        @CONSTITUENTID = SALESORDER.CONSTITUENTID,
                        @CONSTITUENTEXISTS = case when SALESORDER.CONSTITUENTID is not null then 1 else 0 end,
                        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME([SALESORDER].[CONSTITUENTID])
                    from
                        dbo.SALESORDER
                    where
                        SALESORDER.ID = @SALESORDERID;

                    if @CONSTITUENTEXISTS = 1
                    begin
                        select top(1)
                            @PRIMARYMEMBERSHIPID = MEMBER.ID,
                            @PRIMARYMEMBERSHIPDESCRIPTION = '' + MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPLEVEL.NAME
                        from dbo.MEMBER
                            inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                            inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                            inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                        where
                            MEMBER.CONSTITUENTID = @CONSTITUENTID and
                            MEMBER.ISDROPPED = 0 and
                            getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE) and
                            MEMBERSHIP.STATUSCODE = 0
                        order by
                            MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS asc

                        select
                            @CONSTITUENTHASADDRESS = case
                                when A.ID is null then 0
                                else 1
                            end,
                            @CONSTITUENTPOSTCODE = A.POSTCODE,
                            @CONSTITUENTCOUNTRYID = CTY.ID,
                            @CONSTITUENTHASUSADDRESS = case
                                when CTY.ISO3166 = 'US' then 1
                                else 0
                            end
                        from dbo.CONSTITUENT C
                        left outer join dbo.ADDRESS A on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                        left outer join dbo.COUNTRY CTY on CTY.ID = A.COUNTRYID
                        where C.ID = @CONSTITUENTID
                    end

                return 0;