USP_DATAFORMTEMPLATE_VIEW_EVENTAUCTIONSUMMARY

The load procedure used by the view dataform template "Auction Summary View"

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.
@NAME	nvarchar(100)	INOUT	Name
@DATE	datetime	INOUT	Event date
@PARENTEVENT	nvarchar(100)	INOUT	Parent event
@SITES	nvarchar(max)	INOUT	Sites
@TOTALITEMS	int	INOUT	Total items
@ITEMSSOLD	int	INOUT	Total items sold
@TOTALITEMSFORBID	int	INOUT	Total items for bid
@ITEMSFORBIDSOLD	int	INOUT	Total items for bid sold
@TOTALVALUE	money	INOUT	Total fair market value
@TOTALSALES	money	INOUT	Total sales
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTAUCTIONSUMMARY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(100) = null output,
                    @DATE datetime = null output,
                    @PARENTEVENT nvarchar(100) = null output,
                    @SITES nvarchar(max) = null output,
                    @TOTALITEMS integer = null output,
                    @ITEMSSOLD integer = null output,
                    @TOTALITEMSFORBID integer = null output,
                    @ITEMSFORBIDSOLD integer = null output,
                    @TOTALVALUE money = null output,
                    @TOTALSALES money = null output,
                    @BASECURRENCYID uniqueidentifier = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    select @DATALOADED = 1,
                           @NAME = EVENT.NAME,
                           @DATE = EVENT.STARTDATE,
                           @PARENTEVENT = PARENTEVENT.NAME,
                           @SITES = dbo.UFN_EVENTAUCTION_GETSITELIST(@ID),
                           @TOTALITEMS = (select count(*) from AUCTIONITEM
                                          where EVENTAUCTIONID = @ID
                                          and TYPECODE = 0),
                           @ITEMSSOLD = (select count(distinct AUCTIONITEM.ID) from AUCTIONITEM left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                                         where EVENTAUCTIONID = @ID
                                         and TYPECODE = 0
                                         and AUCTIONITEMPURCHASE.PURCHASEID is not null),
                           @TOTALITEMSFORBID = (select count(*) from AUCTIONITEM
                                                where EVENTAUCTIONID = @ID
                                                and PACKAGEID is null),
                           @ITEMSFORBIDSOLD = (select count(distinct AUCTIONITEM.ID) from AUCTIONITEM left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                                               where EVENTAUCTIONID = @ID
                                               and PACKAGEID is null
                                               and AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null),
                           @TOTALVALUE = (select sum(TRANSACTIONVALUE) from AUCTIONITEM
                                          where EVENTAUCTIONID = @ID
                                          and TYPECODE = 0),
                           @TOTALSALES = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) 
                                              from dbo.AUCTIONITEM
                                              inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                                              inner join dbo.REVENUESPLIT on AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
                                              where AUCTIONITEM.EVENTAUCTIONID = @ID
                                              and AUCTIONITEM.TYPECODE = 0) + 0,
                           @BASECURRENCYID = EVENT.BASECURRENCYID
                    from dbo.EVENT
                    left join dbo.EVENT as PARENTEVENT
                        on PARENTEVENT.ID = EVENT.MAINEVENTID
                    where EVENT.ID = @ID

                    return 0;