USP_DATALIST_AUCTIONPACKAGEAVAILABLEITEM

Returns a list of auction items available for a package.
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.
@PACKAGEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CATEGORYID	uniqueidentifier	IN	Category
@SUBCATEGORYID	uniqueidentifier	IN	Subcategory
@ITEMNAME	nvarchar(100)	IN	Name
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_AUCTIONPACKAGEAVAILABLEITEM
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @PACKAGEID uniqueidentifier,
                    @CATEGORYID uniqueidentifier = null,
                    @SUBCATEGORYID uniqueidentifier = null,
                    @ITEMNAME nvarchar(100) = null
                )
                as
                    set nocount on;

                    declare @PACKAGEEVENTAUCTIONID uniqueidentifier = null;

                    select @PACKAGEEVENTAUCTIONID = EVENTAUCTIONID
                    from dbo.AUCTIONITEM where AUCTIONITEM.ID = @PACKAGEID

                    declare @EVENTAUCTIONSITEID table
                    (
                        SITEID uniqueidentifier
                    )

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

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

                    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
                            (
                                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
                            (
                                (@EVENTAUCTIONSITECOUNT = 0 and coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) is null)
                                or
                                exists(select 1 from @EVENTAUCTIONSITEID where SITEID = coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID))

                            )

                    )
                    select
     AUCTIONITEM.ID,
                        AUCTIONITEM.NAME,
                        [CATEGORY].NAME as CATEGORYNAME,
                        [SUBCATEGORY].NAME as SUBCATEGORYNAME,
                        AUCTIONITEM.TRANSACTIONVALUE,
                        EVENT.NAME as AUCTIONNAME,
                        AUCTIONITEM_SITE_CTE.SITENAME as DESIGNATIONSITENAME,
                        AUCTIONITEM.BASECURRENCYID,
                        AUCTIONITEM.TRANSACTIONCURRENCYID,
                        cast(AUCTIONITEM.ID as nvarchar(36)) + ';' + cast(@PACKAGEID as nvarchar(36)) IDANDPACKAGEID /*This is used on the auction item page when building a package so it can know which package to add the auction item*/
                    from 
                        dbo.AUCTIONITEM
                        inner join AUCTIONITEM_SITE_CTE on AUCTIONITEM.ID = AUCTIONITEM_SITE_CTE.AUCTIONITEMID
                        left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.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.AUCTIONITEMRESERVATION on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
                        left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.PARENTID = AUCTIONITEM.REVENUEAUCTIONDONATIONID 
                            and FINANCIALTRANSACTION.DELETEDON is null 
                            and FINANCIALTRANSACTION.TYPECODE = 20 --write-off code

                    where
                        AUCTIONITEM.TYPECODE = 0
                        and
                        AUCTIONITEM.PACKAGEID is null
                        and
                        (@CATEGORYID is null or [CATEGORY].ID = @CATEGORYID)
                        and
                        (@SUBCATEGORYID is null or [SUBCATEGORY].ID = @SUBCATEGORYID)
                        and
                        (AUCTIONITEM.EVENTAUCTIONID is null or AUCTIONITEM.EVENTAUCTIONID = @PACKAGEEVENTAUCTIONID)
                        and
                        AUCTIONITEMRESERVATION.ID is null
                        and
                        AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is null
                        and
                        FINANCIALTRANSACTION.ID is null --item is not written off

                        and
                        (@ITEMNAME is null or @ITEMNAME = '' or AUCTIONITEM.NAME like @ITEMNAME + '%')
                    order by AUCTIONITEM.NAME