USP_DATALIST_SALESORDER_BYPATRON

        CREATE procedure dbo.USP_DATALIST_SALESORDER_BYPATRON
        (
            @CONSTITUENTID uniqueidentifier,
            @SALESMETHODID uniqueidentifier = null,
            @SHOWDATERANGE tinyint = 0
        )
        as
            set nocount on;

        declare @SALESMETHODTYPECODE tinyint = null;
        select @SALESMETHODTYPECODE = TYPECODE
        from SALESMETHOD
        where ID = @SALESMETHODID;

        declare @CURRENTDATE datetime = null;
        set @CURRENTDATE = getdate();

        declare @STARTDATE date = null;
        if @SHOWDATERANGE = 1
            set @STARTDATE = dateadd(day,-29,@CURRENTDATE);
        if @SHOWDATERANGE = 2
            set @STARTDATE = dateadd(day,-89,@CURRENTDATE);
        if @SHOWDATERANGE = 3
            set @STARTDATE = dateadd(day,1,dateadd(month,-12,@CURRENTDATE));

        select
            SALESORDER.ID,
            case SALESORDER.SALESMETHODTYPECODE
                when 3 then
                    SALESORDER.DATEADDED
                else
                    SALESORDER.TRANSACTIONDATE
            end as [ORDERDATE],
            convert(nvarchar(20),SALESORDER.SEQUENCEID) as ORDERNUMBER,
            SALESORDER.AMOUNT as ORDERTOTAL,
            (
                select sum(SALESORDERPAYMENT.AMOUNT)
                from SALESORDERPAYMENT
                where SALESORDERID = SALESORDER.ID
            ) 
            as AMOUNTPAID,
            SALESORDER.SALESMETHODTYPE as SALESMETHOD,
            case SALESORDER.SALESMETHODTYPECODE
                when 3 then
                    RESERVATION.ARRIVALDATE
                else
                    (
                        select top 1 EVENT.STARTDATE
                        from dbo.EVENT
                        inner join dbo.SALESORDERITEMTICKET
                            on EVENT.ID = SALESORDERITEMTICKET.EVENTID
                        inner join dbo.SALESORDERITEM
                            on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                        where SALESORDERITEM.SALESORDERID = SALESORDER.ID
                        order by EVENT.STARTDATE ASC
                    )
            end as EVENTDATE,
            case SALESORDER.SALESMETHODTYPECODE
                when 3 then
                    case
                        when len(RESERVATION.ARRIVALTIME) > 0 then
                            RESERVATION.ARRIVALTIME
                        else
                            dbo.UFN_HOURMINUTE_GETFROMDATE(RESERVATION.STARTDATETIME)
                    end
                else
                    (
                        select top 1 EVENT.STARTTIME
                        from dbo.EVENT
                        inner join dbo.SALESORDERITEMTICKET
                            on EVENT.ID = SALESORDERITEMTICKET.EVENTID
                        inner join dbo.SALESORDERITEM
                            on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                        where SALESORDERITEM.SALESORDERID = SALESORDER.ID
                        order by EVENT.STARTDATE ASC
                    )
            end as EVENTTIME,
            dbo.UFN_SALESORDER_GETITEMDESCRIPTION(SALESORDER.ID) as ITEMDESCRIPTION,
            SALESORDER.STATUS,
            SALESORDER.SALESMETHODTYPECODE,
            SALESORDER.STATUSCODE
        from
            dbo.SALESORDER
        left outer join
            dbo.RESERVATION on SALESORDER.ID = RESERVATION.ID
        where
            SALESORDER.CONSTITUENTID = @CONSTITUENTID and
            (
                @SHOWDATERANGE = 0
                or (SALESORDER.SALESMETHODTYPECODE <> 3 and TRANSACTIONDATE >= @STARTDATE)
                or (SALESORDER.SALESMETHODTYPECODE = 3 and SALESORDER.DATEADDED >= @STARTDATE)
            ) and
            (SALESMETHODTYPECODE = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null) and
            (SALESORDER.STATUSCODE not in (0, 7) or SALESORDER.SALESMETHODTYPECODE = 3)
        order by 
            ORDERDATE desc