USP_DATALIST_RESERVATIONSTATUSCODE

CREATE procedure dbo.USP_DATALIST_RESERVATIONSTATUSCODE
(
    @STATUSCODE tinyint = null,
    @SHOWPASTDUEONLY bit = 0,
    @HIDECOMPLETED bit = 0,
    @SHOWCANCELLED bit = 0,
    @STARTDATE date = null,
    @ENDDATE date = null
)
as
    set nocount on;

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

    if @SHOWPASTDUEONLY = 0 begin
        select
            RESERVATION.ID,
            NAME,
            case when ARRIVALTIME = '' then (isnull((select top 1
                    ITINERARYITEM.STARTTIME
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                    where ITINERARY.RESERVATIONID = RESERVATION.ID and
                        ITINERARYITEM.STARTDATE = RESERVATION.ARRIVALDATE
                    order by ITINERARYITEM.STARTDATETIME asc
                    ), '0800')) else ARRIVALTIME end STARTTIME,
            isnull((select top 1
                    ITINERARYITEM.ENDTIME
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                    where ITINERARY.RESERVATIONID = RESERVATION.ID
                    order by ITINERARYITEM.ENDDATETIME desc
                    ), case when ARRIVALTIME = '' then '0900' else ARRIVALTIME end) ENDTIME,
            ARRIVALDATE as STARTDATE,
            isnull((select top 1 ITINERARYITEM.ENDDATE
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                    where ITINERARY.RESERVATIONID = RESERVATION.ID
                    order by ENDDATE desc), ARRIVALDATE) ENDDATE,
            SALESORDER.STATUSCODE STATUSCODE,
            (select count(ID) from dbo.[SALESORDERPAYMENT] where [SALESORDERPAYMENT].[SALESORDERID] = SALESORDER.ID) as PAYMENTCOUNT,
            RESERVATION.DATEADDED as DATEADDED,
            CHANGEAGENT.USERNAME,
            SALESORDER.STATUS,
            TOTALS.TOTAL,
            SALESORDER.SEQUENCEID,
            FORMATTEDDESCRIPTION.VALUE as DESCRIPTION
        from dbo.RESERVATION
        inner join dbo.CHANGEAGENT on CHANGEAGENT.ID = RESERVATION.ADDEDBYID
        inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
        outer apply dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
        outer apply (
            select isnull(dbo.UFN_RESERVATION_BUILDITINERARYITEMSSTRING(RESERVATION.ID) , '') as VALUE
        ) as RESERVATIONDETAILS
        outer apply (
            select convert(nvarchar(10), dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID)) + ' Visitors' + 
                char(10) + 'Status: ' + SALESORDER.STATUS + char(10) + RESERVATIONDETAILS.VALUE as VALUE
        ) as FORMATTEDDESCRIPTION
        where (STATUSCODE = @STATUSCODE or @STATUSCODE is null) 
            and ((@HIDECOMPLETED = 1 and STATUSCODE <> 1) or (@HIDECOMPLETED = 0) or (@HIDECOMPLETED is null))
            and ((@SHOWCANCELLED = 0 and STATUSCODE <> 5) OR (@SHOWCANCELLED = 1) or (@SHOWCANCELLED is null))
            and ((@STARTDATE is null and @ENDDATE is null)
                or
                (
                    ((RESERVATION.ARRIVALDATE <= @ENDDATE) and (@ENDDATE is not null))
                    and
                    ((RESERVATION.ARRIVALDATE >= @STARTDATE) and (@STARTDATE is not null))
                ))
        order by RESERVATION.DATEADDED desc
    end
    else begin
        select
            RESERVATION.ID,
            NAME,
            case when ARRIVALTIME = '' then (isnull((select top 1
                    ITINERARYITEM.STARTTIME
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                    where ITINERARY.RESERVATIONID = RESERVATION.ID and
                        ITINERARYITEM.STARTDATE = RESERVATION.ARRIVALDATE
                    order by ITINERARYITEM.STARTDATETIME asc
                    ), '0800')) else ARRIVALTIME end STARTTIME,
            isnull((select top 1
                    ITINERARYITEM.ENDTIME
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                    where ITINERARY.RESERVATIONID = RESERVATION.ID
                    order by ITINERARYITEM.ENDDATETIME desc
                    ), case when ARRIVALTIME = '' then '0900' else ARRIVALTIME end) ENDTIME,
            ARRIVALDATE as STARTDATE,
            isnull((select top 1 ITINERARYITEM.ENDDATE
                    from dbo.ITINERARYITEM
                    inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                    where ITINERARY.RESERVATIONID = RESERVATION.ID
                    order by ENDDATE desc), ARRIVALDATE) ENDDATE,
            SALESORDER.STATUSCODE STATUSCODE,
            (select count(ID) from dbo.[SALESORDERPAYMENT] where [SALESORDERPAYMENT].[SALESORDERID] = SALESORDER.ID) as PAYMENTCOUNT,
            RESERVATION.DATEADDED as DATEADDED,
            CHANGEAGENT.USERNAME,
            SALESORDER.STATUS,
            TOTALS.TOTAL,
            SALESORDER.SEQUENCEID,
            FORMATTEDDESCRIPTION.VALUE as DESCRIPTION
        from dbo.RESERVATION
        inner join dbo.CHANGEAGENT on CHANGEAGENT.ID = RESERVATION.ADDEDBYID
        inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
        outer apply dbo.UFN_SALESORDER_TOTALS(RESERVATION.ID) as TOTALS
        outer apply (
            select isnull(dbo.UFN_RESERVATION_BUILDITINERARYITEMSSTRING(RESERVATION.ID) , '') as VALUE
        ) as RESERVATIONDETAILS
        outer apply (
            select convert(nvarchar(10), dbo.UFN_RESERVATION_TOTALVISITORCOUNT(RESERVATION.ID)) + ' Visitors' + 
                char(10) + 'Status: ' + SALESORDER.STATUS + char(10) + RESERVATIONDETAILS.VALUE as VALUE
        ) as FORMATTEDDESCRIPTION
        where (STATUSCODE = @STATUSCODE or @STATUSCODE is null) 
            and    ((dbo.UFN_RESERVATION_DEPOSITPASTDUE(RESERVATION.ID, @CURRENTDATE) = 1) 
            or (dbo.UFN_RESERVATION_FINALPAYMENTPASTDUE(RESERVATION.ID, @CURRENTDATE) = 1) 
            or (dbo.UFN_RESERVATION_CONTRACTPASTDUE(RESERVATION.ID) = 1) 
            or (dbo.UFN_RESERVATION_FINALCOUNTPASTDUE(RESERVATION.ID) = 1)) 
            and ((@HIDECOMPLETED = 1 and STATUSCODE <> 1) or (@HIDECOMPLETED = 0) or (@HIDECOMPLETED is null))
            and ((@SHOWCANCELLED = 0 and STATUSCODE <> 5) OR (@SHOWCANCELLED = 1) or (@SHOWCANCELLED is null))
            and ((@STARTDATE is null and @ENDDATE is null)
                or
                (
                    ((RESERVATION.ARRIVALDATE <= @ENDDATE) and (@ENDDATE is not null))
                    and
                    ((RESERVATION.ARRIVALDATE >= @STARTDATE) and (@STARTDATE is not null))
                ))
        order by RESERVATION.DATEADDED desc
    end

    return 0;