USP_DATALIST_AUCTIONITEMS_2

                CREATE procedure dbo.USP_DATALIST_AUCTIONITEMS_2
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @DISPLAYCURRENCYTYPECODE tinyint = 0,
                    @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 = 1,
                    @ROWLIMIT int = null
                )
                as
                    set nocount on;

                    if @ROWLIMIT is null
                        set @ROWLIMIT = 1000;

                    -- 05/15/2012 CEV - if auction parameter is not empty, don't only show items without auction because nothing will show up

                    if @AUCTIONID is not null
                        set @ONLYSHOWWITHOUTAUCTION = 0;

                    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