USP_DATALIST_AUCTIONAVAILABLEITEMS

Returns a list of auction items available for an auction.
Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@NAME	nvarchar(100)	IN	Item name
@SITEID	uniqueidentifier	IN	Site
@EVENTAUCTIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CATEGORYID	uniqueidentifier	IN	Category
@SUBCATEGORYID	uniqueidentifier	IN	Subcategory
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_AUCTIONAVAILABLEITEMS
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @NAME nvarchar(100) = null,
                    @SITEID uniqueidentifier = null,
                    @EVENTAUCTIONID uniqueidentifier = null,
                    @CATEGORYID uniqueidentifier = null,
                    @SUBCATEGORYID uniqueidentifier = null
                )
                as
                    set nocount on;

                    /*declare @EVENTAUCTIONSITEID table
                    (
                        SITEID uniqueidentifier
                    )

                    insert into @EVENTAUCTIONSITEID(SITEID)
                    select SITEID from dbo.EVENTSITE where EVENTSITE.EVENTID = @EVENTAUCTIONID;

                    declare @EVENTAUCTIONSITECOUNT int = 0;
                    select @EVENTAUCTIONSITECOUNT = count(SITEID) from @EVENTAUCTIONSITEID;
                    */


                    declare @EVENTAUCTIONBASECURRENCYID uniqueidentifier = null;
                    if @EVENTAUCTIONID is not null
                        select @EVENTAUCTIONBASECURRENCYID = EVENT.BASECURRENCYID
                        from dbo.EVENT
                        where EVENT.ID = @EVENTAUCTIONID;


                    with AUCTIONITEM_SITE_CTE(AUCTIONITEMID, SITENAME) as
                    (
                        select
                            AUCTIONITEM.ID,
                            SITE.NAME
                        from
                            dbo.AUCTIONITEM
                            left join dbo.REVENUESPLIT on AUCTIONITEM.REVENUEAUCTIONDONATIONID = REVENUESPLIT.REVENUEID
                            left join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                            left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID
                            left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID
                            left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID
                            left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID
                            left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID
                            left join dbo.SITE on coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) = SITE.ID
                        where
                            AUCTIONITEM.TYPECODE = 0
                            and
                            (@SITEID is null or @SITEID = coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID))
                            and
                            (
                                dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                                or
                                exists(
                                        select 1 
                                        from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) 
                                        where 
                                            SITEID=SITE.ID
                                            or
                                            (
                                                SITEID is null and SITE.ID is null
                                            )
                                      )
                            )
                            and (@EVENTAUCTIONBASECURRENCYID is null or AUCTIONITEM.TRANSACTIONCURRENCYID = @EVENTAUCTIONBASECURRENCYID)                        
                    )

              select
                        AUCTIONITEM.ID,
                        AUCTIONITEM.NAME,
                        [CATEGORY].NAME as CATEGORYNAME,
                        [SUBCATEGORY].NAME as SUBCATEGORYNAME,
                        AUCTIONITEM.TRANSACTIONVALUE,
                        AUCTIONITEM_SITE_CTE.SITENAME as DESIGNATIONSITENAME,
                        AUCTIONITEM.TRANSACTIONCURRENCYID
                    from 
                        dbo.AUCTIONITEM
                        inner join AUCTIONITEM_SITE_CTE on AUCTIONITEM.ID = AUCTIONITEM_SITE_CTE.AUCTIONITEMID
                        left join dbo.AUCTIONITEMCATEGORY [CATEGORY] on AUCTIONITEM.AUCTIONITEMCATEGORYID = [CATEGORY].ID
                        left join dbo.AUCTIONITEMSUBCATEGORY [SUBCATEGORY] on AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = [SUBCATEGORY].ID
                        left join dbo.EVENT on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
                        left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.PARENTID = AUCTIONITEM.REVENUEAUCTIONDONATIONID 
                            and FINANCIALTRANSACTION.DELETEDON is null 
                            and FINANCIALTRANSACTION.TYPECODE = 20 --write-off code

                    where
                        AUCTIONITEM.PACKAGEID is null
                        and (@CATEGORYID is null or [CATEGORY].ID = @CATEGORYID)
                        and (@SUBCATEGORYID is null or [SUBCATEGORY].ID = @SUBCATEGORYID)
                        and (@NAME is null or AUCTIONITEM.NAME like @NAME + '%')
                        and AUCTIONITEM.EVENTAUCTIONID is null
                        and FINANCIALTRANSACTION.ID is null --item is not written off




                    order by AUCTIONITEM.NAME