USP_DATALIST_AUCTIONITEMS
Auction item inventory.
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 |
| @AUCTIONID | uniqueidentifier | IN | Auction |
| @AUCTIONITEMCATEGORYID | uniqueidentifier | IN | Category |
| @AUCTIONITEMSUBCATEGORYID | uniqueidentifier | IN | Subcategory |
| @ONLYSHOWUNSOLD | bit | IN | Show only unsold items |
| @ONLYSHOWWITHOUTAUCTION | bit | IN | Show only items without an auction |
| @ROWLIMIT | int | IN | Row limit |
| @DISPLAYCURRENCYTYPECODE | tinyint | IN | Display currency |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_AUCTIONITEMS
(
@CURRENTAPPUSERID uniqueidentifier,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@NAME nvarchar(100) = null,
@SITEID uniqueidentifier = null,
@AUCTIONID uniqueidentifier = null,
@AUCTIONITEMCATEGORYID uniqueidentifier = null,
@AUCTIONITEMSUBCATEGORYID uniqueidentifier = null,
@ONLYSHOWUNSOLD bit = 0,
@ONLYSHOWWITHOUTAUCTION bit = 0,
@ROWLIMIT int = null,
@DISPLAYCURRENCYTYPECODE tinyint = 0
)
as
set nocount on;
if @ROWLIMIT is null
set @ROWLIMIT = 1000;
with [FILTERED_AUCTIONITEM_CTE](AUCTIONITEMID, ITEMNAME, SITE, AUCTIONITEMCATEGORYID, AUCTIONITEMSUBCATEGORYID,
VALUE, PACKAGEID, TYPECODE, ISPACKAGE, EVENTAUCTIONID, REVENUEAUCTIONDONATIONID,
VALUECURRENCYID)
as
(
select top(@ROWLIMIT)
AUCTIONITEM.ID as [AUCTIONITEMID],
AUCTIONITEM.NAME as ITEMNAME,
SITE.NAME as [SITE],
AUCTIONITEM.AUCTIONITEMCATEGORYID,
AUCTIONITEM.AUCTIONITEMSUBCATEGORYID,
case when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONVALUE
when @DISPLAYCURRENCYTYPECODE = 2 then AUCTIONITEM.ORGANIZATIONVALUE
else AUCTIONITEM.VALUE
end as [VALUE],
AUCTIONITEM.PACKAGEID,
AUCTIONITEM.TYPECODE as TYPECODE,
AUCTIONITEM.TYPECODE as ISPACKAGE,
AUCTIONITEM.EVENTAUCTIONID,
AUCTIONITEM.REVENUEAUCTIONDONATIONID,
case when @DISPLAYCURRENCYTYPECODE = 0 then AUCTIONITEM.TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else AUCTIONITEM.BASECURRENCYID
end as [VALUECURRENCYID]
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 (@AUCTIONID is null or AUCTIONITEM.EVENTAUCTIONID = @AUCTIONID)
and (@AUCTIONITEMCATEGORYID is null or @AUCTIONITEMCATEGORYID = AUCTIONITEM.AUCTIONITEMCATEGORYID)
and (@AUCTIONITEMSUBCATEGORYID is null or @AUCTIONITEMSUBCATEGORYID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID)
and (@NAME is null or AUCTIONITEM.NAME like @NAME + '%')
and (@ONLYSHOWWITHOUTAUCTION = 0 or AUCTIONITEM.EVENTAUCTIONID is null)
order by AUCTIONITEM.NAME
union all
select distinct top(@ROWLIMIT)
AUCTIONITEM.ID,
AUCTIONITEM.NAME as ITEMNAME,
dbo.UFN_AUCTIONPACKAGE_GETSITELIST(AUCTIONITEM.ID) as [SITE],
AUCTIONITEM.AUCTIONITEMCATEGORYID,
AUCTIONITEM.AUCTIONITEMSUBCATEGORYID,
coalesce((
select
case when @DISPLAYCURRENCYTYPECODE = 0 then sum([ITEM].TRANSACTIONVALUE)
when @DISPLAYCURRENCYTYPECODE = 2 then sum([ITEM].ORGANIZATIONVALUE)
else sum([ITEM].VALUE)
end
from dbo.AUCTIONITEM [ITEM]
where [ITEM].PACKAGEID = AUCTIONITEM.ID
), 0)as [VALUE],
AUCTIONITEM.PACKAGEID,
AUCTIONITEM.TYPECODE as TYPECODE,
AUCTIONITEM.TYPECODE as ISPACKAGE,
AUCTIONITEM.EVENTAUCTIONID,
AUCTIONITEM.REVENUEAUCTIONDONATIONID,
coalesce(( --Coalesce this with a similar case statement so it works for packages with no items
select top 1
case when @DISPLAYCURRENCYTYPECODE = 0 then [ITEM].TRANSACTIONCURRENCYID
when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
else [ITEM].TRANSACTIONCURRENCYID -- Packages do not have a base currency, so this makes as much sense as anything
end
from dbo.AUCTIONITEM [ITEM]
where [ITEM].PACKAGEID = AUCTIONITEM.ID
), case when @DISPLAYCURRENCYTYPECODE = 2 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() else EVENT.BASECURRENCYID end) as [VALUECURRENCYID]
from
dbo.AUCTIONITEM
inner join dbo.EVENT on EVENT.ID = EVENTAUCTIONID --Since all packages MUST belong to events, this join is okay
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
(
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
)
)
)
and (@AUCTIONID is null or AUCTIONITEM.EVENTAUCTIONID = @AUCTIONID)
and (@AUCTIONITEMCATEGORYID is null or @AUCTIONITEMCATEGORYID = AUCTIONITEM.AUCTIONITEMCATEGORYID)
and (@AUCTIONITEMSUBCATEGORYID is null or @AUCTIONITEMSUBCATEGORYID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID)
and (@NAME is null or AUCTIONITEM.NAME like @NAME + '%')
and (@ONLYSHOWWITHOUTAUCTION = 0 or AUCTIONITEM.EVENTAUCTIONID is null)
order by AUCTIONITEM.NAME
)
select distinct top(@ROWLIMIT)
[FILTERED_AUCTIONITEM_CTE].AUCTIONITEMID,
[FILTERED_AUCTIONITEM_CTE].ITEMNAME,
[FILTERED_AUCTIONITEM_CTE].SITE,
EVENT.NAME as AUCTIONNAME,
AUCTIONITEMCATEGORY.NAME as CATEGORY,
AUCTIONITEMSUBCATEGORY.NAME as SUBCATEGORY,
[FILTERED_AUCTIONITEM_CTE].VALUE,
case
when AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is null then 0
else 1
end as [WASSOLD],
[FILTERED_AUCTIONITEM_CTE].PACKAGEID,
[FILTERED_AUCTIONITEM_CTE].TYPECODE as TYPECODE,
[FILTERED_AUCTIONITEM_CTE].TYPECODE as ISPACKAGE,
case
when REVENUEPOSTED.ID is null then 0
else 1
end as ISPOSTED,
case
when [FILTERED_AUCTIONITEM_CTE].TYPECODE = 0 and [FILTERED_AUCTIONITEM_CTE].PACKAGEID is null and AUCTIONITEMRESERVATION.ID is null then 0
when [FILTERED_AUCTIONITEM_CTE].TYPECODE = 0 and [FILTERED_AUCTIONITEM_CTE].PACKAGEID is not null and [RESERVEDPACKAGEITEM].ID is null then 0
when [FILTERED_AUCTIONITEM_CTE].TYPECODE = 1 and AUCTIONITEMRESERVATION.ID is null then 0
else 1
end as ISRESERVED,
[FILTERED_AUCTIONITEM_CTE].VALUECURRENCYID,
cast(@DISPLAYCURRENCYTYPECODE as nvarchar(1)) + '|' + cast([FILTERED_AUCTIONITEM_CTE].AUCTIONITEMID as nvarchar(36)) as [DETAILFORMID]
from [FILTERED_AUCTIONITEM_CTE]
left join dbo.AUCTIONITEMREVENUEPURCHASE on [FILTERED_AUCTIONITEM_CTE].AUCTIONITEMID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
left join dbo.AUCTIONITEMCATEGORY on [FILTERED_AUCTIONITEM_CTE].AUCTIONITEMCATEGORYID = AUCTIONITEMCATEGORY.ID
left join dbo.AUCTIONITEMSUBCATEGORY on [FILTERED_AUCTIONITEM_CTE].AUCTIONITEMSUBCATEGORYID = AUCTIONITEMSUBCATEGORY.ID
left join dbo.EVENT on [FILTERED_AUCTIONITEM_CTE].EVENTAUCTIONID = EVENT.ID
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = [FILTERED_AUCTIONITEM_CTE].REVENUEAUCTIONDONATIONID
left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEMRESERVATION.AUCTIONITEMID = [FILTERED_AUCTIONITEM_CTE].AUCTIONITEMID
left join dbo.AUCTIONITEMRESERVATION [RESERVEDPACKAGEITEM] on [RESERVEDPACKAGEITEM].AUCTIONITEMID = [FILTERED_AUCTIONITEM_CTE].PACKAGEID
where
(@ONLYSHOWUNSOLD = 0 or (AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is null and AUCTIONITEMRESERVATION.ID is null))
order by [FILTERED_AUCTIONITEM_CTE].ITEMNAME