SD4951-p_Reports_iOS-APP-reservation
120 lines
USE [PARKER_ALL]
USE [PARKER_REPORTS]
GO
GO
/****** Object: StoredProcedure [dbo].[p_Reports_iOS-APP-reservation] Script Date: 5/29/2023 3:51:27 PM ******/
/****** Object: StoredProcedure [dbo].[p_Reports_iOS-APP-reservation] Script Date: 5/29/2023 2:59:59 PM ******/
SET ANSI_NULLS ON
SET ANSI_NULLS ON
GO
GO
SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER ON
GO
GO
ALTER PROC [dbo].[p_Reports_iOS-APP-reservation]
ALTER PROC [dbo].[p_Reports_iOS-APP-reservation]
@start_res_date datetime --- reservations created on or after this date
@start_res_date datetime --- reservations created on or after this date
, @end_res_date datetime --- reservations created before this date
, @end_res_date datetime --- reservations created before this date
, @end_close_date datetime --- expected return date
, @end_close_date datetime --- expected return date
, @empl_rep_id int
, @empl_rep_id int
, @loc_rep_id int
, @loc_rep_id int
, @phone_os varchar(10) = 'ios' -- new parameter, switch for ios, android, all
AS /* ** $History: $
AS /* ** $History: $
KL: 2022-11-24 Initial coding to create list for SD 3481 where reservations are missing on the mobile app
KL: 2022-11-24 Initial coding to create list for SD 3481 where reservations are missing on the mobile app
KL: 2022-12-02 Drop the Guest or Rewards member filter and allow null @end_close_date to ignore the reservation end date
KL: 2022-12-02 Drop the Guest or Rewards member filter and allow null @end_close_date to ignore the reservation end date
KL: 2023-05-29 new parameter to choose the reservation types to allow, default=ios. SD 4951
*/
*/
BEGIN
BEGIN
SET NOCOUNT ON
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS OFF
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
SET ANSI_PADDING OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER OFF
SET QUOTED_IDENTIFIER OFF
DECLARE
DECLARE
-- MP: Dec 06.18 add logging
-- MP: Dec 06.18 add logging
@databasename varchar (20)
@databasename varchar (20)
,@parameter_values varchar(MAX) = NULL
,@parameter_values varchar(MAX) = NULL
,@execution_start datetime
,@execution_start datetime
,@execution_end datetime
,@execution_end datetime
,@sp_name nvarchar(max)
,@sp_name nvarchar(max)
SET @databasename = db_name()
SET @databasename = db_name()
SET @sp_name = OBJECT_NAME(@@PROCID)
SET @sp_name = OBJECT_NAME(@@PROCID)
SET @execution_start = getdate()
SET @execution_start = getdate()
if @start_res_date = null
if @start_res_date = null
begin
begin
set @start_res_date = dateadd(DAY, +1, getdate()) -- yesterday
set @start_res_date = dateadd(DAY, +1, getdate()) -- yesterday
end
end
if @end_res_date = null
if @end_res_date = null
begin
begin
set @start_res_date = getdate() -- today
set @start_res_date = getdate() -- today
end
end
SET @start_res_date = dbo.fn_Set_time_of_day(@start_res_date, '00:00:00:000 AM')
SET @start_res_date = dbo.fn_Set_time_of_day(@start_res_date, '00:00:00:000 AM')
SET @end_res_date = dbo.fn_Set_time_of_day(@end_res_date, '00:00:00:000 AM')
SET @end_res_date = dbo.fn_Set_time_of_day(@end_res_date, '00:00:00:000 AM')
if (@end_close_date is not null)
if (@end_close_date is not null)
begin
begin
-- set to beginning of next day since using < in where clause
-- set to beginning of next day since using < in where clause
SET @end_close_date = dateadd(DAY, +1, @end_close_date)
SET @end_close_date = dateadd(DAY, +1, @end_close_date)
SET @end_close_date = dbo.fn_Set_time_of_day(@end_close_date, '00:00:00:000 AM')
SET @end_close_date = dbo.fn_Set_time_of_day(@end_close_date, '00:00:00:000 AM')
end
end
--select @start_res_date as start_res_date, @end_res_date as end_res_date, @end_close_date as end_res_date
--select @start_res_date as start_res_date, @end_res_date as end_res_date, @end_close_date as end_res_date
select
select
isnull(tr.first_name, '') as first_name,
isnull(tr.first_name, '') as first_name,
isnull(tr.last_name, '') as last_name,
isnull(tr.last_name, '') as last_name,
isnull(tr.email, '') as email,
isnull(tr.email, '') as email,
isnull(v.reward_number, '') as reward_number,
isnull(v.reward_number, '') as reward_number,
tr.res_date,
tr.res_date,
tr.open_date,
tr.open_date,
tr.close_date,
tr.close_date,
ticket_reservation_id,
ticket_reservation_id,
tr.location_id,
tr.location_id,
loc.location_name,
loc.location_name,
isnull(convert(varchar, t.location_id), '') as ticket_location_id,
isnull(convert(varchar, t.location_id), '') as ticket_location_id,
isnull(loct.location_name, '') as ticket_location_name,
isnull(loct.location_name, '') as ticket_location_name,
--phone_code, LEN(phone_code),
--phone_code, LEN(phone_code),
isnull(t.bar_code, '') as barcode,
isnull(t.bar_code, '') as barcode,
isnull(convert(varchar, t.ticket_id), '') as ticket_id,
isnull(convert(varchar, t.ticket_id), '') as ticket_id,
isnull(t.ticket_number, '') as ticket_number,
isnull(t.ticket_number, '') as ticket_number,
isnull(t.pin_number, '') as pin_number,
isnull(t.pin_number, '') as pin_number,
isnull(t.issue_number, '') as issue_number,
isnull(t.issue_number, '') as issue_number,
isnull(t.status , '') as ticket_status
isnull(t.status , '') as ticket_status
from ticket_reservations tr with (nolock)
from ticket_reservations tr with (nolock)
inner join app_phone ap with (nolock) on tr.phone_id=ap.phone_id
inner join app_phone ap with (nolock) on tr.phone_id=ap.phone_id
left join View_Active_Loyalty_Members v with (nolock) on v.cust_id=tr.cust_id
left join View_Active_Loyalty_Members v with (nolock) on v.cust_id=tr.cust_id
left join tickets t with (nolock) on t.ticket_id=tr.ticket_id
left join tickets t with (nolock) on t.ticket_id=tr.ticket_id
left join locations loc with (nolock) on loc.location_id = tr.location_id
left join locations loc with (nolock) on loc.location_id = tr.location_id
left join locations locT with (nolock) on locT.location_id = t.location_id
left join locations locT with (nolock) on locT.location_id = t.location_id
where
where
-- (tr.cust_id is null or v.reward_number is not null) -- guest or rewards member --- KL 2022-12-02 drop guest or reward member filter
-- (tr.cust_id is null or v.reward_number is not null) -- guest or rewards member --- KL 2022-12-02 drop guest or reward member filter
-- and
-- and
reservation_type='APP' -- APP reservaions
reservation_type='APP' -- APP reservaions
and LEN(phone_code) >= 32 -- len for an iOS phone
-- and LEN(phone_code) >= 32 -- len for an iOS phone
and ( -- KL: 2023-05-29 SD 4951
(@phone_os = 'ios' and LEN(phone_code) >= 32)
or (@phone_os = 'android' and LEN(phone_code) < 32)
or (@phone_os = 'all')
)
and tr.close_date > getdate()
and tr.close_date > getdate()
and (tr.close_date < @end_close_date -- '2023-01-15' -- expected return
and (tr.close_date < @end_close_date -- '2023-01-15' -- expected return
or @end_close_date is null) -- KL 2022-21-02 ignore resevation end date
or @end_close_date is null) -- KL 2022-21-02 ignore resevation end date
and len(isnull(tr.email, '')) > 0 -- don't send empty email addresses
and len(isnull(tr.email, '')) > 0 -- don't send empty email addresses
and tr.res_date >= @start_res_date --- '2022-11-24' --- reservations created on or after this date
and tr.res_date >= @start_res_date --- '2022-11-24' --- reservations created on or after this date
and tr.res_date < @end_res_date --- '2022-11-28'--- reservations created before this date
and tr.res_date < @end_res_date --- '2022-11-28'--- reservations created before this date
order by
order by
tr.res_date
tr.res_date
SET @execution_end = getdate()
SET @execution_end = getdate()
EXEC [LOGGING].dbo.p_Create_Execution
EXEC [LOGGING].dbo.p_Create_Execution
@databasename = @databasename
@databasename = @databasename
,@sp_name = @sp_name
,@sp_name = @sp_name
,@parameter_values = @parameter_values
,@parameter_values = @parameter_values
,@execution_start = @execution_start
,@execution_start = @execution_start
,@execution_end = @execution_end
,@execution_end = @execution_end
,@employee_id = @empl_rep_id
,@employee_id = @empl_rep_id
RETURN 0
RETURN 0
END
END