USP_DATALIST_RESERVATIONBYDATE

CREATE procedure dbo.USP_DATALIST_RESERVATIONBYDATE
(
    @INCLUDEPASTRESERVATIONS bit = 0,
    @INCLUDECOMPLETED bit = 1,
    @INCLUDECANCELLED bit = 0,
    @DATESELECTTYPE tinyint = 2,
    @STARTDATE date = null,
    @ENDDATE date = null
)
as
    set nocount on;

    declare @CURRENTDATE date;
    set @CURRENTDATE = getdate();

    if @DATESELECTTYPE is null
        set @DATESELECTTYPE = 2;

    set @STARTDATE = case @DATESELECTTYPE
        when 0 then @CURRENTDATE 
        when 1 then @CURRENTDATE 
        when 3 then @STARTDATE
        when 4 then @STARTDATE
    end;

    set @ENDDATE = case @DATESELECTTYPE
        when 0 then @CURRENTDATE 
        when 1 then dateadd(week,1, @CURRENTDATE) 
        when 3 then @ENDDATE
        when 4 then @STARTDATE
    end;

    if @INCLUDEPASTRESERVATIONS = 0 and @STARTDATE < @CURRENTDATE
        set @STARTDATE = @CURRENTDATE

    select 
        RESERVATION.ID,
        RESERVATION.NAME,
        RESERVATION.ARRIVALDATE,
        case when ARRIVALTIME = '' then (isnull((select min(ITINERARYITEM.STARTTIME)
                from dbo.ITINERARYITEM
                inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                where ITINERARY.RESERVATIONID = RESERVATION.ID and
                    ITINERARYITEM.STARTDATE = RESERVATION.ARRIVALDATE
                ), '0800')) else RESERVATION.ARRIVALTIME end,
        dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID),
        SALESORDER.STATUS,
        SALESORDER.STATUSCODE,
        TOTALS.BALANCE,
        dbo.UFN_RESERVATION_GETPAYMENTCOUNT(RESERVATION.ID) as PAYMENTCOUNT
    from 
        dbo.RESERVATION
    inner join 
        dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
    outer apply 
        dbo.UFN_SALESORDER_TOTALS(SALESORDER.ID) TOTALS
    where (
            ((RESERVATION.ARRIVALDATE between @STARTDATE and @ENDDATE) and (@DATESELECTTYPE <> 2)) 
            or 
            ((@DATESELECTTYPE = 2 and @INCLUDEPASTRESERVATIONS = 0 and RESERVATION.ARRIVALDATE >= @CURRENTDATE) 
                or (@DATESELECTTYPE = 2 and @INCLUDEPASTRESERVATIONS = 1))
        )
        and ((@INCLUDECOMPLETED = 0 and SALESORDER.STATUSCODE <> 1) or (@INCLUDECOMPLETED = 1))
        and ((@INCLUDECANCELLED = 0 and SALESORDER.STATUSCODE <> 5) or (@INCLUDECANCELLED = 1))                    
    order by RESERVATION.ARRIVALDATE, RESERVATION.ARRIVALTIME, RESERVATION.NAME