USP_DATALIST_RESERVATIONSTATUSOPEN

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

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

                    if @SHOWPASTDUEONLY = 0
                    begin
                        with RESERVATIONS_CTE as
                        (
                            select RESERVATION.ID,
                                NAME,
                                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 ARRIVALTIME end STARTTIME,
                                isnull(LATESTITINERARYENDDATESANDTIMES.ENDTIME, case when RESERVATION.ARRIVALTIME = '' then '0900' else RESERVATION.ARRIVALTIME end) as ENDTIME,
                                ARRIVALDATE as STARTDATE,
                                isnull(LATESTITINERARYENDDATESANDTIMES.ENDDATE, RESERVATION.ARRIVALDATE) as ENDDATE,
                                SALESORDER.STATUSCODE STATUSCODE,
                                SALESORDER.STATUS STATUS,
                                dbo.UFN_RESERVATION_GETPAYMENTCOUNT(RESERVATION.ID) as PAYMENTCOUNT,
                                isnull(dbo.UFN_RESERVATION_BUILDITINERARYITEMSSTRING(RESERVATION.ID) , '') as DESCRIPTION
                            from dbo.RESERVATION
                            inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
                            outer apply (
                                select top 1 ITINERARYITEM.ENDDATE, ITINERARYITEM.ENDTIME
                                from dbo.ITINERARY
                                inner join dbo.ITINERARYITEM on ITINERARYITEM.ITINERARYID = ITINERARY.ID
                                where ITINERARY.RESERVATIONID = RESERVATION.ID
                                order by ITINERARYITEM.ENDDATETIME desc
                            ) LATESTITINERARYENDDATESANDTIMES
                            where
                                (
                                    (@STATUSCODE is null and STATUSCODE in (0,2,3,4))
                                    or STATUSCODE = @STATUSCODE
                                )
                                and (@STARTDATE is null or RESERVATION.ARRIVALDATE >= @STARTDATE)
                                and (@ENDDATE is null or RESERVATION.ARRIVALDATE <= @ENDDATE)
                        )
                        select
                            ID,
                            NAME,
                            STARTTIME,
                            ENDTIME,
                            STARTDATE,
                            ENDDATE,
                            STATUSCODE,
                            PAYMENTCOUNT,
                            convert(nvarchar(10), dbo.UFN_RESERVATION_TOTALVISITORCOUNT(ID)) + ' Visitors' + char(10) + 'Status: ' + 
                                STATUS + char(10) + DESCRIPTION
                        from RESERVATIONS_CTE
                        option (recompile);
                    end
                    else
                    begin
                        with RESERVATIONS_CTE as
                        (                    
                            select RESERVATION.ID,
                                NAME,
                                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 ARRIVALTIME end STARTTIME,
                                isnull(LATESTITINERARYENDDATESANDTIMES.ENDTIME, case when RESERVATION.ARRIVALTIME = '' then '0900' else RESERVATION.ARRIVALTIME end) as ENDTIME,
                                ARRIVALDATE as STARTDATE,
                                isnull(LATESTITINERARYENDDATESANDTIMES.ENDDATE, RESERVATION.ARRIVALDATE) as ENDDATE,
                                SALESORDER.STATUSCODE STATUSCODE,
                                SALESORDER.STATUS STATUS,
                                dbo.UFN_RESERVATION_GETPAYMENTCOUNT(RESERVATION.ID) as PAYMENTCOUNT,
                                isnull(dbo.UFN_RESERVATION_BUILDITINERARYITEMSSTRING(RESERVATION.ID) , '') as DESCRIPTION
                            from dbo.RESERVATION
                            inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
                            outer apply (
                                select top 1 ITINERARYITEM.ENDDATE, ITINERARYITEM.ENDTIME
                                from dbo.ITINERARY
                                inner join dbo.ITINERARYITEM on ITINERARYITEM.ITINERARYID = ITINERARY.ID
                                where ITINERARY.RESERVATIONID = RESERVATION.ID
                                order by ITINERARYITEM.ENDDATETIME desc
                            ) LATESTITINERARYENDDATESANDTIMES
                            where
                                (
                                    (@STATUSCODE is null and STATUSCODE in (0,2,3,4))
                                    or STATUSCODE = @STATUSCODE
                                )
                                and (@STARTDATE is null or RESERVATION.ARRIVALDATE >= @STARTDATE)
                                and (@ENDDATE is null or RESERVATION.ARRIVALDATE <= @ENDDATE)
                                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
                                )
                        )
                        select
                            ID,
                            NAME,
                            STARTTIME,
                            ENDTIME,
                            STARTDATE,
                            ENDDATE,
                            STATUSCODE,
                            PAYMENTCOUNT,
                            convert(nvarchar(10), dbo.UFN_RESERVATION_TOTALVISITORCOUNT(ID)) + ' Visitors' + char(10) + 'Status: ' + 
                                STATUS + char(10) + DESCRIPTION
                        from RESERVATIONS_CTE
                        option (recompile);
                    end

                    return 0;