USP_SALESORDER_ORDERPICKUPSEARCH

CREATE procedure dbo.USP_SALESORDER_ORDERPICKUPSEARCH
(
    @MAXROWS integer = 100,
    @FROM date,
    @TO date,
    @PATRONFILTERPRESENT bit = 0,
    @PATRONGROUPORGKEYNAME nvarchar(100) = null,
    @PATRONKEYNAME nvarchar(100) = null,
    @PATRONFIRSTNAME nvarchar(50) = null,
    @RECIPIENTFILTERPRESENT bit = 0,
    @RECIPIENTGROUPORGKEYNAME nvarchar(100) = null,
    @RECIPIENTKEYNAME nvarchar(100) = null,
    @RECIPIENTFIRSTNAME nvarchar(50) = null,
    @INCLUDEALIAS bit = 0,
    @INCLUDENICKNAME bit = 0,
    @CARDNUMBER nvarchar(5) = null,
    @SEQUENCEID int = null,
    @MEMBERSHIPLOOKUPID nvarchar(20) = null,
    @DELIVERYMETHODID uniqueidentifier = null,
    @PROGRAMNAME nvarchar(25) = null
)
as        
    set nocount on;

    declare @CARDNUMBERPRESENT bit = 0;
    declare @CREDITCARDMATCHSALESORDERIDS table (ID uniqueidentifier);

    if @CARDNUMBER is not null and @CARDNUMBER <> ''
    begin
        set @CARDNUMBERPRESENT = 1;

        insert into @CREDITCARDMATCHSALESORDERIDS
        select SALESORDERPAYMENT.SALESORDERID from dbo.SALESORDERPAYMENT
        inner join dbo.[REVENUEPAYMENTMETHOD] on SALESORDERPAYMENT.PAYMENTID = [REVENUEPAYMENTMETHOD].REVENUEID 
        inner join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [REVENUEPAYMENTMETHOD].PAYMENTMETHODCODE = 2 and [CREDITCARDPAYMENTMETHODDETAIL].ID = [REVENUEPAYMENTMETHOD].ID 
        where [CREDITCARDPAYMENTMETHODDETAIL].CREDITCARDPARTIALNUMBER like @CARDNUMBER escape '\'
    end

    declare @PATRONCONSTITUENTMATCHES table (ID uniqueidentifier)
    declare @RECIPIENTCONSTITUENTMATCHES table (ID uniqueidentifier)

    insert into @PATRONCONSTITUENTMATCHES
    select C.ID from dbo.CONSTITUENT C
    left outer join dbo.ALIAS A on A.CONSTITUENTID = C.ID
    where
    (
        @PATRONGROUPORGKEYNAME is not null and @PATRONGROUPORGKEYNAME <> ''
        and (
            ([ISORGANIZATION] = 1 or [ISGROUP] = 1)
            and (
                [C].[KEYNAME] like @PATRONGROUPORGKEYNAME escape '\'
                or (@INCLUDEALIAS = 1 and [A].[KEYNAME] like @PATRONGROUPORGKEYNAME escape '\')
            )
        )
    )
    or (
        @PATRONKEYNAME is not null and @PATRONKEYNAME <> ''
        and (
            ([ISORGANIZATION] = 0 and [ISGROUP] = 0)
            and (
                [C].[KEYNAME] like @PATRONKEYNAME escape '\'
                or (@INCLUDEALIAS = 1 and [A].[KEYNAME] like @PATRONKEYNAME escape '\')
            )
        )
    )
    or (
        @PATRONFIRSTNAME is not null and @PATRONFIRSTNAME <> ''
        and (
            [C].[FIRSTNAME] like @PATRONFIRSTNAME escape '\'
            or (@INCLUDEALIAS = 1 and [A].[FIRSTNAME] like @PATRONFIRSTNAME escape '\')
            or (@INCLUDENICKNAME = 1 and [C].[NICKNAME] like @PATRONFIRSTNAME escape '\')
        )
    );

    insert into @RECIPIENTCONSTITUENTMATCHES
    select C.ID from dbo.CONSTITUENT C
    left outer join dbo.ALIAS A on A.CONSTITUENTID = C.ID
    where
    (
        @RECIPIENTGROUPORGKEYNAME is not null and @RECIPIENTGROUPORGKEYNAME <> ''
        and (
            ([ISORGANIZATION] = 1 or [ISGROUP] = 1)
            and (
                [C].[KEYNAME] like @RECIPIENTGROUPORGKEYNAME escape '\'
                or (@INCLUDEALIAS = 1 and [A].[KEYNAME] like @RECIPIENTGROUPORGKEYNAME escape '\')
            )
        )
    )
    or (
        @RECIPIENTKEYNAME is not null and @RECIPIENTKEYNAME <> ''
        and (
            ([ISORGANIZATION] = 0 and [ISGROUP] = 0)
            and (
                [C].[KEYNAME] like @RECIPIENTKEYNAME escape '\'
                or (@INCLUDEALIAS = 1 and [A].[KEYNAME] like @RECIPIENTKEYNAME escape '\')
            )
        )
    )
    or (
        @RECIPIENTFIRSTNAME is not null and @RECIPIENTFIRSTNAME <> ''
        and (
            [C].[FIRSTNAME] like @RECIPIENTFIRSTNAME escape '\'
            or (@INCLUDEALIAS = 1 and [A].[FIRSTNAME] like @RECIPIENTFIRSTNAME escape '\')
            or (@INCLUDENICKNAME = 1 and [C].[NICKNAME] like @RECIPIENTFIRSTNAME escape '\')
        )
    );

    select distinct top(@MAXROWS)
        [SALESORDER].[ID], 
        RECIPIENTNAMEFORMAT.NAME as [RECIPIENTNAME],
        PATRONNAMEFORMAT.NAME as [PATRONNAME],
        [SALESORDER].[TRANSACTIONDATE], 
        convert(nvarchar(20), [SALESORDER].[SEQUENCEID]) as [SEQUENCEID], 
        [SALESORDER].[AMOUNT], 
        case when SALESORDER.STATUSCODE = 6 then [SALESORDER].[AMOUNT] else null end AMOUNTDUE, 
        (select dbo.UDA_BUILDLIST(SALESORDERITEM.DESCRIPTION) from (select top 10 [SALESORDERID], [TYPECODE], [DESCRIPTION] from dbo.[SALESORDERITEM] where [SALESORDERID] = [SALESORDER].[ID] and [TYPECODE] not in (3,4) order by [TYPECODE], [SALESORDERITEM].[DESCRIPTION]) as [SALESORDERITEM]) as [ITEMS] 
    from dbo.[SALESORDER] with (nolock)
    inner join dbo.[SALESORDERITEM] with (nolock) on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
    left join dbo.[SALESORDERITEMTICKET] with (nolock) on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    left outer join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID and [TICKET].STATUSCODE not in (2, 3)
    left outer join dbo.[EVENT] with (nolock) on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
    left outer join dbo.[PROGRAM] with (nolock) on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID] or [EVENT].[PROGRAMID] = [PROGRAM].[ID]
    left outer join dbo.PROGRAMDOCUMENT with (nolock) on PROGRAMDOCUMENT.PROGRAMID = PROGRAM.ID
    left outer join dbo.TICKETPRINTINFO with (nolock) on TICKETPRINTINFO.TICKETID = TICKET.ID
    left outer join dbo.[MEMBER] on [MEMBER].[CONSTITUENTID] = [SALESORDER].[CONSTITUENTID]
    left outer join dbo.[MEMBERSHIP] on [MEMBERSHIP].[ID] = [MEMBER].[MEMBERSHIPID]
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.RECIPIENTID) RECIPIENTNAMEFORMAT
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.CONSTITUENTID) PATRONNAMEFORMAT
    where
        (@PATRONFILTERPRESENT = 0 or [SALESORDER].[CONSTITUENTID] in (select ID from @PATRONCONSTITUENTMATCHES))
        and (@RECIPIENTFILTERPRESENT = 0 or [SALESORDER].[RECIPIENTID] in (select ID from @RECIPIENTCONSTITUENTMATCHES))
        and (@SEQUENCEID is null or [SALESORDER].[SEQUENCEID] = @SEQUENCEID)
        and (@MEMBERSHIPLOOKUPID is null or @MEMBERSHIPLOOKUPID = '' or [MEMBERSHIP].[LOOKUPID] like @MEMBERSHIPLOOKUPID escape '\')
        and (@CARDNUMBERPRESENT = 0 or [SALESORDER].[ID] in (select ID from @CREDITCARDMATCHSALESORDERIDS))
        and (@DELIVERYMETHODID is null or [SALESORDER].[DELIVERYMETHODID] = @DELIVERYMETHODID)
        and (@PROGRAMNAME is null or @PROGRAMNAME = '' or [PROGRAM].[NAME] like @PROGRAMNAME escape '\') 
        and [SALESORDER].[SALESMETHODTYPECODE] in (1,2)
        and convert(date, [SALESORDER].[TRANSACTIONDATE]) between @FROM and @TO
        and (
            [SALESORDER].[STATUSCODE] = 6
            or (
                PROGRAMDOCUMENT.ID is not null
                and TICKET.ID is not null
                and TICKETPRINTINFO.TICKETID is null
            )
        )
    order by [RECIPIENTNAME], [PATRONNAME], SALESORDER.TRANSACTIONDATE desc;

    return 0;