USP_SEARCHLIST_AUCTIONITEM
Provides search functionality for the auction item search list.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @MAXROWS | smallint | IN | Input parameter indicating the maximum number of rows to return. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @NAME | nvarchar(100) | IN | Name |
| @AUCTIONITEMCATEGORYID | uniqueidentifier | IN | Category |
| @AUCTIONITEMSUBCATEGORYID | uniqueidentifier | IN | Subcategory |
| @SITEID | uniqueidentifier | IN | Site |
| @AUCTIONID | uniqueidentifier | IN | Auction |
| @ONLYSHOWUNSOLD | bit | IN | Only show unsold items |
| @ONLYSHOWAVAILABLEFORAUCTION | uniqueidentifier | IN | Only show valid items for this auction |
Definition
Copy
CREATE procedure dbo.USP_SEARCHLIST_AUCTIONITEM
(
@MAXROWS smallint = 500,
@CURRENTAPPUSERID uniqueidentifier,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@NAME nvarchar(100) = null,
@AUCTIONITEMCATEGORYID uniqueidentifier = null,
@AUCTIONITEMSUBCATEGORYID uniqueidentifier = null,
@SITEID uniqueidentifier = null,
@AUCTIONID uniqueidentifier = null,
@ONLYSHOWUNSOLD bit = 0,
@ONLYSHOWAVAILABLEFORAUCTION uniqueidentifier = null
)
as
set @NAME = COALESCE(@NAME,'') + '%' ;
declare @SITESFORAUCTION table
(
SITEID uniqueidentifier
)
declare @SITESFORAUCTIONCOUNT int = 0;
declare @EVENTAUCTIONBASECURRENCYID uniqueidentifier = null;
if @ONLYSHOWAVAILABLEFORAUCTION is not null
begin
insert into @SITESFORAUCTION(SITEID) (select SITEID from dbo.EVENTSITE where EVENTSITE.EVENTID = @ONLYSHOWAVAILABLEFORAUCTION);
select @SITESFORAUCTIONCOUNT = count(SITEID) from @SITESFORAUCTION;
select @EVENTAUCTIONBASECURRENCYID = EVENT.BASECURRENCYID
from dbo.EVENT
where EVENT.ID = @ONLYSHOWAVAILABLEFORAUCTION
end;
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
(@ONLYSHOWAVAILABLEFORAUCTION is null or (@ONLYSHOWAVAILABLEFORAUCTION is not null and SITE.ID is null and @SITESFORAUCTIONCOUNT = 0) or (exists(select 1 from @SITESFORAUCTION where SITEID = SITE.ID)))
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)
union all
select distinct
AUCTIONITEM.ID,
dbo.UFN_AUCTIONPACKAGE_GETSITELIST(AUCTIONITEM.ID) as SITELIST
from
dbo.AUCTIONITEM
left join dbo.EVENTSITE on AUCTIONITEM.EVENTAUCTIONID = EVENTSITE.EVENTID
left join dbo.SITE on EVENTSITE.SITEID = SITE.ID
where
AUCTIONITEM.TYPECODE = 1
and
(@SITEID is null or @SITEID = EVENTSITE.SITEID)
and
(@ONLYSHOWAVAILABLEFORAUCTION is null or (@ONLYSHOWAVAILABLEFORAUCTION is not null and SITE.ID is null and @SITESFORAUCTIONCOUNT = 0) or (exists(select 1 from @SITESFORAUCTION where SITEID = SITE.ID)))
and
(
dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
or
exists(
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
where
SITEID=EVENTSITE.SITEID
or
(
SITEID is null and EVENTSITE.SITEID is null
)
)
)
)
select distinct top(@MAXROWS)
AUCTIONITEM.ID,
AUCTIONITEM.NAME as NAME,
AUCTIONITEM_SITE_CTE.SITENAME as SITE,
EVENT.NAME as AUCTIONNAME,
AUCTIONITEMCATEGORY.NAME as CATEGORY,
AUCTIONITEMSUBCATEGORY.NAME as SUBCATEGORY,
case
when AUCTIONITEM.TYPECODE = 0 then AUCTIONITEM.TRANSACTIONVALUE
else coalesce((select sum(TRANSACTIONVALUE) from dbo.AUCTIONITEM [ITEM] where [ITEM].PACKAGEID = AUCTIONITEM.ID),0)
end as VALUE,
AUCTIONITEM.TRANSACTIONCURRENCYID
from
dbo.AUCTIONITEM
inner join AUCTIONITEM_SITE_CTE on AUCTIONITEM.ID = AUCTIONITEM_SITE_CTE.AUCTIONITEMID
left join dbo.AUCTIONITEMCATEGORY on AUCTIONITEM.AUCTIONITEMCATEGORYID = AUCTIONITEMCATEGORY.ID
left join dbo.AUCTIONITEMSUBCATEGORY on AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = AUCTIONITEMSUBCATEGORY.ID
left join dbo.EVENT on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
left join dbo.AUCTIONITEMRESERVATION [RESERVEDPACKAGEITEM] on AUCTIONITEM.PACKAGEID = [RESERVEDPACKAGEITEM].AUCTIONITEMID
left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.PARENTID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
and FINANCIALTRANSACTION.DELETEDON is null
and FINANCIALTRANSACTION.TYPECODE = 20 --code for write-off
where
(@AUCTIONID is null or AUCTIONITEM.EVENTAUCTIONID = @AUCTIONID)
and
(AUCTIONITEM.NAME like @NAME + '%')
and
(@AUCTIONITEMCATEGORYID is null or AUCTIONITEM.AUCTIONITEMCATEGORYID = @AUCTIONITEMCATEGORYID)
and
(@AUCTIONITEMSUBCATEGORYID is null or AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = @AUCTIONITEMSUBCATEGORYID)
and
(@ONLYSHOWAVAILABLEFORAUCTION is null or AUCTIONITEM.EVENTAUCTIONID is null)
and
(@ONLYSHOWAVAILABLEFORAUCTION is null or FINANCIALTRANSACTION.ID is null) --not written off
and
(@ONLYSHOWUNSOLD = 0 or (AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is null and [RESERVEDPACKAGEITEM].ID is null and AUCTIONITEMRESERVATION.ID is null))
order by
AUCTIONITEM.NAME asc